pke-at: first-pass storage interface

13 files changed, 97 insertions, 0 deletions

diff --git a/src/sql/schema-000-000.sql b/src/sql/schema-000-000.sql
new file mode 100644
index 0000000..f80977f
--- /dev/null
+++ b/src/sql/schema-000-000.sql
@@ -0,0 +1,48 @@
+CREATE TABLE [db_version] (
+	id INTEGER PRIMARY KEY
+	,version_maj INTEGER NOT NULL
+	,version_min INTEGER NOT NULL
+	,is_deleted INTEGER NOT NULL DEFAULT 0
+	,creation_dt INTEGER DEFAULT (unixepoch('now'))
+	,modification_dt INTEGER DEFAULT (unixepoch('now'))
+);
+
+CREATE TABLE [at_setlist] (
+	uuid BLOB PRIMARY KEY
+	,title nvarchar(128) DEFAULT ''
+	,is_deleted INTEGER NOT NULL DEFAULT 0
+	,creation_dt INTEGER DEFAULT (unixepoch('now'))
+	,modification_dt INTEGER DEFAULT (unixepoch('now'))
+);
+
+CREATE TABLE [at_song] (
+	uuid BLOB PRIMARY KEY
+	,ccli INTEGER
+	,title nvarchar(128) DEFAULT ''
+	,arrangement nvarchar(128) DEFAULT ''
+	,beats_per_minute INTEGER
+	,is_deleted INTEGER NOT NULL DEFAULT 0
+	,creation_dt INTEGER DEFAULT (unixepoch('now'))
+	,modification_dt INTEGER DEFAULT (unixepoch('now'))
+);
+
+CREATE TABLE [at_setlist_song] (
+	setlist_uuid BLOB
+	,song_uuid BLOB
+	,is_deleted INTEGER NOT NULL DEFAULT 0
+	,creation_dt INTEGER DEFAULT (unixepoch('now'))
+	,modification_dt INTEGER DEFAULT (unixepoch('now'))
+);
+
+CREATE TABLE [at_section] (
+	song_uuid BLOB
+	,section_type INTEGER
+	,sequence INTEGER
+	,beats_per_bar INTEGER
+	,bar_count INTEGER
+	,is_deleted INTEGER NOT NULL DEFAULT 0
+	,creation_dt INTEGER DEFAULT (unixepoch('now'))
+	,modification_dt INTEGER DEFAULT (unixepoch('now'))
+);
+
+INSERT INTO [db_version](id,version_maj,version_min) VALUES(0,1,0);
diff --git a/src/sql/section_delete.sql b/src/sql/section_delete.sql
new file mode 100644
index 0000000..7668cfd
--- /dev/null
+++ b/src/sql/section_delete.sql
@@ -0,0 +1,3 @@
+UPDATE [at_section] SET
+	is_deleted = 1
+	,modification_dt = unixepoch('now')
diff --git a/src/sql/section_get.sql b/src/sql/section_get.sql
new file mode 100644
index 0000000..bbd0a73
--- /dev/null
+++ b/src/sql/section_get.sql
@@ -0,0 +1 @@
+SELECT uuid,section_type,sequence,beats_per_bar,bar_count FROM [at_setlist]
diff --git a/src/sql/section_upsert.sql b/src/sql/section_upsert.sql
new file mode 100644
index 0000000..4ca1025
--- /dev/null
+++ b/src/sql/section_upsert.sql
@@ -0,0 +1,10 @@
+INSERT INTO [at_section] (uuid,section_type,sequence,beats_per_bar,bar_count)
+	VALUES('%s','%s','%s','%s','%s')
+		ON CONFLICT(uuid) DO UPDATE SET
+			section_type=excluded.section_type
+			,sequence=excluded.sequence
+			,beats_per_bar=excluded.beats_per_bar
+			,bar_count=excluded.bar_count
+			,is_deleted=0
+			,modification_dt=unixepoch('now')
+		WHERE uuid=excluded.uuid;
diff --git a/src/sql/setlist_delete.sql b/src/sql/setlist_delete.sql
new file mode 100644
index 0000000..d77dc3f
--- /dev/null
+++ b/src/sql/setlist_delete.sql
@@ -0,0 +1,3 @@
+UPDATE [at_setlist] SET
+	is_deleted = 1
+	,modification_dt = unixepoch('now')
diff --git a/src/sql/setlist_get.sql b/src/sql/setlist_get.sql
new file mode 100644
index 0000000..917df25
--- /dev/null
+++ b/src/sql/setlist_get.sql
@@ -0,0 +1 @@
+SELECT uuid,title FROM [at_setlist]
diff --git a/src/sql/setlist_song_delete.sql b/src/sql/setlist_song_delete.sql
new file mode 100644
index 0000000..51e9284
--- /dev/null
+++ b/src/sql/setlist_song_delete.sql
@@ -0,0 +1,3 @@
+UPDATE [at_setlist_song] SET
+	is_deleted = 1
+	,modification_dt = unixepoch('now')
diff --git a/src/sql/setlist_song_get.sql b/src/sql/setlist_song_get.sql
new file mode 100644
index 0000000..1278ff7
--- /dev/null
+++ b/src/sql/setlist_song_get.sql
@@ -0,0 +1 @@
+SELECT setlist_uuid,song_uuid FROM [at_setlist_song]
diff --git a/src/sql/setlist_song_upsert.sql b/src/sql/setlist_song_upsert.sql
new file mode 100644
index 0000000..ec0682e
--- /dev/null
+++ b/src/sql/setlist_song_upsert.sql
@@ -0,0 +1,6 @@
+INSERT INTO [at_setlist_song] (setlist_uuid,song_uuid)
+	VALUES('%s','%s')
+		ON CONFLICT(setlist_uuid,song_uuid) DO UPDATE SET
+			is_deleted=0
+			,modification_dt=unixepoch('now')
+		WHERE setlist_uuid=excluded.setlist_uuid AND song_uuid=excluded.song_uuid;
diff --git a/src/sql/setlist_upsert.sql b/src/sql/setlist_upsert.sql
new file mode 100644
index 0000000..b10f783
--- /dev/null
+++ b/src/sql/setlist_upsert.sql
@@ -0,0 +1,7 @@
+INSERT INTO [at_setlist] (uuid,title)
+	VALUES('%s','%s')
+		ON CONFLICT(uuid) DO UPDATE SET
+			title=excluded.title
+			,is_deleted=0
+			,modification_dt=unixepoch('now')
+		WHERE uuid=excluded.uuid;
diff --git a/src/sql/song_delete.sql b/src/sql/song_delete.sql
new file mode 100644
index 0000000..5ddea19
--- /dev/null
+++ b/src/sql/song_delete.sql
@@ -0,0 +1,3 @@
+UPDATE [at_song] SET
+	is_deleted = 1
+	,modification_dt = unixepoch('now')
diff --git a/src/sql/song_get.sql b/src/sql/song_get.sql
new file mode 100644
index 0000000..c03a59f
--- /dev/null
+++ b/src/sql/song_get.sql
@@ -0,0 +1 @@
+SELECT uuid,ccli,title,arrangement,beats_per_minute FROM [at_song]
diff --git a/src/sql/song_upsert.sql b/src/sql/song_upsert.sql
new file mode 100644
index 0000000..d0e4fcc
--- /dev/null
+++ b/src/sql/song_upsert.sql
@@ -0,0 +1,10 @@
+INSERT INTO [at_song] (uuid,ccli,title,arrangement,beats_per_minute)
+	VALUES('%s','%s','%s','%s','%s')
+		ON CONFLICT(uuid) DO UPDATE SET
+			ccli=excluded.ccli
+			,title=excluded.title
+			,arrangement=excluded.arrangement
+			,beats_per_minute=excluded.beats_per_minute
+			,is_deleted=0
+			,modification_dt=unixepoch('now')
+		WHERE uuid=excluded.uuid;