Adds a populate.sql script

We need a script to run for populating and bootstrapping the database.
This is the start of such a script.

The syntax here is suitable for mariadb at present.

The comments above "populate" are assumed to have been carried out by
the root user (e.g. sudo mariabdb).

2 files changed, 31 insertions, 0 deletions

diff --git a/Makefile b/Makefile
index 46c7d5c..ec80b91 100644
--- a/Makefile
+++ b/Makefile
@@ -6,3 +6,8 @@ build:
 
 stop:
 	@docker stop ded-core
+
+# assume GRANT SELECT, INDEX, CREATE, INSERT, UPDATE, DROP, DELETE ON ded.* TO 'web'@'localhost'; 
+# assume CREATE DATABASE ded CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+populate:
+	mariadb -u web ded -p <  populate.sql
diff --git a/populate.sql b/populate.sql
new file mode 100644
index 0000000..bb7821b
--- /dev/null
+++ b/populate.sql
@@ -0,0 +1,26 @@
+/* CREATE OR REPLACE DATABASE ded CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; */
+
+/* Switch to using it. */
+USE ded;
+
+/* Create the Organisations table. */
+DROP TABLE IF EXISTS organisations;
+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());
+
+/* /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'; */