HXtFMjFS

1START TRANSACTION;
2
3-- SET foreign_key_checks=0;
4
5DROP DATABASE IF EXISTS corg;
6CREATE DATABASE corg;
7USE corg;
8
9CREATE TABLE employee (
10    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
11    nickname VARCHAR(80) NOT NULL UNIQUE,
12    password VARCHAR(255) NOT NULL,
13
14    PRIMARY KEY(id)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16
17CREATE TABLE customer (
18    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
19    uid BIGINT UNSIGNED NULL UNIQUE,
20
21    family_name VARCHAR(80) NOT NULL,
22    given_name VARCHAR(80) NOT NULL,
23    additional_name VARCHAR(80),
24    honorific_prefix VARCHAR(80),
25    honorific_suffix VARCHAR(80),
26
27    role VARCHAR(80) NOT NULL,
28    org VARCHAR(80) NOT NULL,
29
30    post_office_box VARCHAR(80),
31    street_address VARCHAR(80),
32    extended_address VARCHAR(80),
33    locality VARCHAR(80),
34    region VARCHAR(80),
35    postal_code VARCHAR(80),
36    country_name VARCHAR(80),
37
38    tel VARCHAR(80),
39    email VARCHAR(80),
40    url VARCHAR(80),
41
42    rev TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
43
44    employee_id INT UNSIGNED NOT NULL,
45
46    PRIMARY KEY(id),
47    INDEX(employee_id),
48
49    FOREIGN KEY(employee_id)
50        REFERENCES employee(id)
51        ON DELETE NO ACTION ON UPDATE CASCADE
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53
54CREATE TABLE association (
55    customer_id_1 INT UNSIGNED NOT NULL,
56    customer_id_2 INT UNSIGNED NOT NULL,
57
58    PRIMARY KEY(customer_id_1, customer_id_2),
59
60    FOREIGN KEY(customer_id_1)
61        REFERENCES customer(id)
62        ON DELETE NO ACTION ON UPDATE CASCADE,
63
64    FOREIGN KEY(customer_id_2)
65        REFERENCES customer(id)
66        ON DELETE NO ACTION ON UPDATE CASCADE
67) ENGINE=InnoDB DEFAULT CHARSET=utf8;
68
69CREATE TABLE activity (
70    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
71    name VARCHAR(80) NOT NULL,
72    description TEXT,
73    ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
74    customer_id INT UNSIGNED NOT NULL,
75    employee_id INT UNSIGNED NOT NULL,
76
77    PRIMARY KEY(id),
78    INDEX(customer_id),
79    INDEX(employee_id),
80
81    FOREIGN KEY(customer_id)
82        REFERENCES customer(id)
83        ON DELETE CASCADE ON UPDATE CASCADE,
84    FOREIGN KEY(employee_id)
85        REFERENCES employee(id)
86        ON DELETE NO ACTION ON UPDATE CASCADE
87) ENGINE=InnoDB DEFAULT CHARSET=utf8;
88
89CREATE TABLE file (
90    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
91    uid BIGINT UNSIGNED NULL UNIQUE,
92    name VARCHAR(80) NOT NULL,
93    description TEXT,
94    size BIGINT UNSIGNED DEFAULT 0,
95
96    PRIMARY KEY(id)
97) ENGINE=InnoDB DEFAULT CHARSET=utf8;
98
99CREATE TABLE reference (
100    activity_id INT UNSIGNED NOT NULL,
101    file_id INT UNSIGNED NOT NULL,
102
103    PRIMARY KEY(activity_id, file_id),
104
105    FOREIGN KEY(activity_id)
106        REFERENCES activity(id)
107        ON DELETE CASCADE ON UPDATE CASCADE,
108    FOREIGN KEY(file_id)
109        REFERENCES file(id)
110        ON DELETE NO ACTION ON UPDATE CASCADE
111) ENGINE=InnoDB DEFAULT CHARSET=utf8;
112
113-- STORED PROCS AND FUNCS
114-- TRIGGERS.
115
116DELIMITER ;;
117
118CREATE TRIGGER before_insert_customer
119BEFORE INSERT ON customer
120FOR EACH ROW
121BEGIN
122    IF new.uid IS NULL THEN
123        SET new.uid = UUID_SHORT();
124    END IF;
125END;;
126
127CREATE TRIGGER before_insert_file
128BEFORE INSERT ON file
129FOR EACH ROW
130BEGIN
131    IF new.uid IS NULL THEN
132        SET new.uid = UUID_SHORT();
133    END IF;
134END;;
135
136DELIMITER ;
137
138-- SET foreign_key_checks=1;
139
140COMMIT;
141
142--  vim: set ft=sql :