diff options
| author | Jonathan Bradley <jcb@pikum.xyz> | 2025-12-10 15:01:13 -0500 |
|---|---|---|
| committer | Jonathan Bradley <jcb@pikum.xyz> | 2025-12-10 15:01:13 -0500 |
| commit | 6c3559c51bf01ab10346d575e8b26903028c7251 (patch) | |
| tree | a15699c99b5a5374a9a45a9d24cb407a7711b26c /src/sql | |
| parent | 5807565be93e8b813627fb8f52747d8d6b8a1504 (diff) | |
Diffstat (limited to 'src/sql')
| -rw-r--r-- | src/sql/ext_mapping_delete.sql | 3 | ||||
| -rw-r--r-- | src/sql/ext_mapping_get.sql | 1 | ||||
| -rw-r--r-- | src/sql/ext_mapping_upsert.sql | 7 | ||||
| -rw-r--r-- | src/sql/schema-000-000.sql | 22 |
4 files changed, 30 insertions, 3 deletions
diff --git a/src/sql/ext_mapping_delete.sql b/src/sql/ext_mapping_delete.sql new file mode 100644 index 0000000..62177d9 --- /dev/null +++ b/src/sql/ext_mapping_delete.sql @@ -0,0 +1,3 @@ +UPDATE [ext_mapping] SET + is_deleted=1 + ,modification_dt=unixepoch('now') diff --git a/src/sql/ext_mapping_get.sql b/src/sql/ext_mapping_get.sql new file mode 100644 index 0000000..26dbba5 --- /dev/null +++ b/src/sql/ext_mapping_get.sql @@ -0,0 +1 @@ +SELECT source_id,id_external_integer,uuid FROM [ext_mapping] diff --git a/src/sql/ext_mapping_upsert.sql b/src/sql/ext_mapping_upsert.sql new file mode 100644 index 0000000..db9f44b --- /dev/null +++ b/src/sql/ext_mapping_upsert.sql @@ -0,0 +1,7 @@ +INSERT INTO [ext_mapping] (source_id,id_external_integer,uuid) + VALUES('%i','%i','%s') + ON CONFLICT(source_id,id_external_integer) DO UPDATE SET + uuid=excluded.uuid + ,is_deleted=0 + ,modification_dt=unixepoch('now') + WHERE source_id=excluded.source_id AND id_external_integer=excluded.id_external_integer diff --git a/src/sql/schema-000-000.sql b/src/sql/schema-000-000.sql index 3e96dcf..b57b977 100644 --- a/src/sql/schema-000-000.sql +++ b/src/sql/schema-000-000.sql @@ -9,7 +9,7 @@ CREATE TABLE [db_version] ( CREATE TABLE [at_setlist] ( uuid NCHAR(56) PRIMARY KEY - ,title nvarchar(128) DEFAULT '' + ,title NVARCHAR(128) DEFAULT '' ,is_deleted INTEGER NOT NULL DEFAULT 0 ,creation_dt INTEGER DEFAULT (unixepoch('now')) ,modification_dt INTEGER DEFAULT (unixepoch('now')) @@ -18,8 +18,8 @@ CREATE TABLE [at_setlist] ( CREATE TABLE [at_song] ( uuid NCHAR(56) PRIMARY KEY ,ccli INTEGER - ,title nvarchar(128) DEFAULT '' - ,arrangement nvarchar(128) DEFAULT '' + ,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')) @@ -46,4 +46,20 @@ CREATE TABLE [at_section] ( ,modification_dt INTEGER DEFAULT (unixepoch('now')) ); +CREATE TABLE [ext_source] ( + id INTEGER PRIMARY KEY + ,title NVARCHAR(255) NOT NULL +); + +CREATE TABLE [ext_mapping] ( + source_id INTEGER + ,id_external_integer INTEGER NOT NULL DEFAULT 0 + ,uuid NCHAR(56) NOT NULL + ,is_deleted INTEGER NOT NULL DEFAULT 0 + ,creation_dt INTEGER DEFAULT (unixepoch('now')) + ,modification_dt INTEGER DEFAULT (unixepoch('now')) + ,PRIMARY KEY (source_id, id_external_integer) +); + +INSERT INTO [ext_source](id,title) VALUES(1,'Planning Center Services'); INSERT INTO [db_version](id,version_maj,version_min) VALUES(0,1,0); |