1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
/* CREATE OR REPLACE DATABASE ded CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; */
/* Switch to using it. */
USE ded;
/* Create the Operations table */
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE organisations;
DROP TABLE operations;
SET FOREIGN_KEY_CHECKS = 1;
/* Create the Organisations table. */
CREATE TABLE organisations (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
created DATETIME NOT NULL
);
/* Add index to created column. */
CREATE INDEX idx_organsations_created ON organisations(created);
-- Let's add some placeholder data to organisations table.
INSERT INTO organisations (name, created) VALUES ("Random Organisation 1", UTC_TIMESTAMP());
INSERT INTO organisations (name, created) VALUES ("Random Organisation 2", UTC_TIMESTAMP());
INSERT INTO organisations (name, created) VALUES ("Random Organisation 3", UTC_TIMESTAMP());
CREATE TABLE operations (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL,
organisation_id INT NOT NULL,
created DATETIME NOT NULL,
CONSTRAINT fk_operation_organisation
FOREIGN KEY(organisation_id)
REFERENCES organisations(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
/* Add index to created column. */
CREATE INDEX idx_operations_created ON operations(created);
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 1", UTC_TIMESTAMP(), "Operation 1 Description", 1);
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 2", UTC_TIMESTAMP(), "Operation 2 Description", 1);
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 3", UTC_TIMESTAMP(), "Operation 3 Description", 2);
/* /1* The following should be carried out on the database server *1/ */
/* -- CREATE USER 'web'@'localhost'; */
/* -- GRANT SELECT, INSERT, UPDATE, DELETE ON ded.* TO 'web'@'localhost'; */
/* /1* Important: Make sure to swap 'pass' with a password of your own choosing. *1/ */
/* -- ALTER USER 'web'@'localhost' IDENTIFIED BY 'dedpassword'; */
|
