Add existing work

2 files changed, 50 insertions, 0 deletions

diff --git a/sql/0001_migration.sql b/sql/0001_migration.sql
new file mode 100644
index 0000000..b04a754
--- /dev/null
+++ b/sql/0001_migration.sql
@@ -0,0 +1,25 @@
+
+--------------------------------------------------------------------------------
+
+-- This migration script adds the ceres database and user and sets
+-- permissions.
+
+-- Run this migration via `sudo mariadb -u root < 0001_migration.sql`. Mind
+-- that you also have to create the ceres Linux user. This is possibly done
+-- by the systemd *.sysusers file.
+
+-- create database for ceres, add ceres user and set privileges
+CREATE DATABASE IF NOT EXISTS ceres;
+CREATE USER IF NOT EXISTS 'ceres'@'localhost' IDENTIFIED VIA unix_socket;
+GRANT ALL PRIVILEGES on ceres.* to 'ceres'@'localhost';
+FLUSH PRIVILEGES;
+
+-- select correct database for the rest of this script
+USE ceres;
+
+-- create meta table and set database schema version
+CREATE TABLE IF NOT EXISTS meta (
+	identifier varchar(80) PRIMARY KEY NOT NULL UNIQUE,
+	value varchar(80) NOT NULL
+);
+INSERT INTO meta (identifier,value) VALUES ('version', '1');
diff --git a/sql/0002_migration.sql b/sql/0002_migration.sql
new file mode 100644
index 0000000..7c99616
--- /dev/null
+++ b/sql/0002_migration.sql
@@ -0,0 +1,25 @@
+
+--------------------------------------------------------------------------------
+
+-- This migration creates the initial version of the database layout.
+
+-- Run this command as Linux user `ceres` with this command:
+-- `mariadb < 0002_migration.sql`.
+
+-- set database for this script
+USE ceres;
+
+-- create recipe table
+CREATE TABLE IF NOT EXISTS recipes (
+	id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
+	title varchar(80) NOT NULL DEFAULT '(no title)',
+	upstream_url varchar(200) NOT NULL DEFAULT '',
+	description_markdown text NOT NULL DEFAULT ''
+);
+
+-- update database schema version
+UPDATE meta
+SET
+	value='2'
+WHERE
+	identifier='version';