model: Switch to very basic database schema

This simple model is used to test if it is helpful to implement the four
CRUD methods create, read, update and delete all inside the model
package.

The model package should also provide the datastructures for these
operations (well suited for the required views) aswell as tests for
them.

With this approach it should be possible to easily implement the view
and controller package because most of the logic is already inside the
model package and is tested.

2 files changed, 11 insertions, 23 deletions

diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
index 6ca4d0f..c548724 100644
--- a/model/sql/migrate.sql
+++ b/model/sql/migrate.sql
@@ -1,14 +1,9 @@
-CREATE TABLE 'recipes' (
-	'id' integer PRIMARY KEY AUTOINCREMENT,
-	'title' text DEFAULT '',
-	'portions' text DEFAULT '',  -- FIXME has to be integer
-	'url' text DEFAULT '',
-	'notes' text DEFAULT ''
-);
-
-CREATE TABLE 'recipe_steps' (
-	'id' integer PRIMARY KEY,
-	'recipe_id' integer DEFAULT NULL,
-	'rank' integer DEFAULT NULL,
-	'text' text DEFAULT ''
+CREATE TABLE recipes (
+	id           INTEGER PRIMARY KEY,
+	title        TEXT    NOT NULL,
+	portions     INTEGER NOT NULL,
+	url          TEXT    NOT NULL,
+	notes        TEXT    NOT NULL,
+	created      INTEGER NOT NULL, -- unix timestamp
+	last_changed INTEGER NOT NULL  -- unix timestamp
 );
diff --git a/model/sql/testdata.sql b/model/sql/testdata.sql
index f2cd6e3..e47bde9 100644
--- a/model/sql/testdata.sql
+++ b/model/sql/testdata.sql
@@ -1,13 +1,6 @@
 INSERT INTO 'recipes'
-	('id', 'title', 'portions', 'url', 'notes')
+	(id, title, portions, url, notes, created, last_changed)
 VALUES
-	(1, 'Pancakes', 4, 'https://example.org', 'Very fluffy'),
-	(2, 'Burger', 2, 'https://xengineering.eu/git/ceres', 'Delicious!')
-;
-
-INSERT INTO 'recipe_steps'
-	('id', 'recipe_id', 'rank', 'text')
-VALUES
-	(1, 1, 1, 'Stir the dough'),
-	(2, 1, 2, 'Fry pancakes')
+	(1, 'Pancakes', 4, 'https://example.org', 'Very fluffy', strftime('%s', 'now'), strftime('%s', 'now')),
+	(2, 'Burger', 2, 'https://xengineering.eu/git/ceres', 'Delicious!', strftime('%s', 'now'), strftime('%s', 'now'))
 ;