pke-at: second-pass sql storage

4 files changed, 12 insertions, 13 deletions

diff --git a/src/sql/schema-000-000.sql b/src/sql/schema-000-000.sql
index f80977f..f1881cc 100644
--- a/src/sql/schema-000-000.sql
+++ b/src/sql/schema-000-000.sql
@@ -8,7 +8,7 @@ CREATE TABLE [db_version] (
 );
 
 CREATE TABLE [at_setlist] (
-	uuid BLOB PRIMARY KEY
+	uuid NCHAR(56) PRIMARY KEY
 	,title nvarchar(128) DEFAULT ''
 	,is_deleted INTEGER NOT NULL DEFAULT 0
 	,creation_dt INTEGER DEFAULT (unixepoch('now'))
@@ -16,7 +16,7 @@ CREATE TABLE [at_setlist] (
 );
 
 CREATE TABLE [at_song] (
-	uuid BLOB PRIMARY KEY
+	uuid NCHAR(56) PRIMARY KEY
 	,ccli INTEGER
 	,title nvarchar(128) DEFAULT ''
 	,arrangement nvarchar(128) DEFAULT ''
@@ -27,17 +27,17 @@ CREATE TABLE [at_song] (
 );
 
 CREATE TABLE [at_setlist_song] (
-	setlist_uuid BLOB
-	,song_uuid BLOB
+	setlist_uuid NCHAR(56)
+	,song_uuid NCHAR(56)
 	,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
+	song_uuid NCHAR(56)
 	,sequence INTEGER
+	,section_type INTEGER
 	,beats_per_bar INTEGER
 	,bar_count INTEGER
 	,is_deleted INTEGER NOT NULL DEFAULT 0
diff --git a/src/sql/section_get.sql b/src/sql/section_get.sql
index bbd0a73..81c5ca8 100644
--- a/src/sql/section_get.sql
+++ b/src/sql/section_get.sql
@@ -1 +1 @@
-SELECT uuid,section_type,sequence,beats_per_bar,bar_count FROM [at_setlist]
+SELECT uuid,sequence,section_type,beats_per_bar,bar_count FROM [at_setlist]
diff --git a/src/sql/section_upsert.sql b/src/sql/section_upsert.sql
index 4ca1025..659d66a 100644
--- a/src/sql/section_upsert.sql
+++ b/src/sql/section_upsert.sql
@@ -1,10 +1,9 @@
-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
+INSERT INTO [at_section] (uuid,sequence,section_type,beats_per_bar,bar_count)
+	VALUES('%s','%i','%u','%u','%u')
+		ON CONFLICT(song_uuid,sequence) 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;
+		WHERE song_uuid=excluded.uuid AND sequence=excluded.sequence;
diff --git a/src/sql/song_upsert.sql b/src/sql/song_upsert.sql
index d0e4fcc..4f2a165 100644
--- a/src/sql/song_upsert.sql
+++ b/src/sql/song_upsert.sql
@@ -1,5 +1,5 @@
 INSERT INTO [at_song] (uuid,ccli,title,arrangement,beats_per_minute)
-	VALUES('%s','%s','%s','%s','%s')
+	VALUES('%s','%lu','%s','%s','%u')
 		ON CONFLICT(uuid) DO UPDATE SET
 			ccli=excluded.ccli
 			,title=excluded.title