diff options
| -rw-r--r-- | Makefile | 3 | ||||
| -rw-r--r-- | src/level-import.cpp | 35 | ||||
| -rw-r--r-- | src/pke-at-setlist-types.hpp | 27 | ||||
| -rw-r--r-- | src/pke-at-storage-interface.hpp | 3 | ||||
| -rw-r--r-- | src/pke-at-storage-sql.cpp | 139 | ||||
| -rw-r--r-- | src/pke-at-storage-sql.hpp | 3 | ||||
| -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 |
10 files changed, 232 insertions, 11 deletions
@@ -32,6 +32,9 @@ endef FILES_BIN = \ src/sql/schema-000-000.sql \ + src/sql/ext_mapping_delete.sql \ + src/sql/ext_mapping_get.sql \ + src/sql/ext_mapping_upsert.sql \ src/sql/section_delete.sql \ src/sql/section_get.sql \ src/sql/section_upsert.sql \ diff --git a/src/level-import.cpp b/src/level-import.cpp index 5801cac..c0d6910 100644 --- a/src/level-import.cpp +++ b/src/level-import.cpp @@ -160,23 +160,46 @@ void pke_at_level_import_exit_clicked(void*,void*,void*) { void pke_at_level_import_import_clicked(void*,void*,void*) { uint32_t u; - - pke_at_setlist_details setlist_details; - setlist_details.uuid = pk_uuid_new_v7(); + pke_at_setlist_details setlist_details{}; + pke_at_song_details song_details{}; + pke_at_setlist_song_details setlist_song_details{}; + pke_at_ext_mapping_details deets{}; + + deets.mapping_source = PKE_AT_EXT_MAPPING_SOURCE_PLANNING_CENTER_SERVICES; + deets.mapping_type = PKE_AT_EXT_MAPPING_TYPE_LONG; + deets.id.id_long = lvl_mstr.data.selected_plan->details.id.id_long; + auto mapping_resp = g_at.storage->pke_at_storage_interface_ext_mapping_get(deets); + + if (mapping_resp.result_code == pke_at_storage_interface_result_code_success && mapping_resp.value != nullptr && mapping_resp.value->details.next > 0) { + setlist_details.uuid = mapping_resp.value->details[0].uuid; + } else { + setlist_details.uuid = pk_uuid_new_v7(); + } setlist_details.title = lvl_mstr.data.selected_plan->details.title; g_at.storage->pke_at_storage_interface_setlist_upsert(setlist_details); + deets.uuid = setlist_details.uuid; + g_at.storage->pke_at_storage_interface_ext_mapping_upsert(deets); for (u = 0; u < lvl_mstr.data.plan_items.next; ++u) { di_plan_item *plan_item = &lvl_mstr.data.plan_items[u]; - pke_at_song_details song_details{}; - song_details.uuid = pk_uuid_new_v7(); + + deets.id.id_long = plan_item->details.id.id_long; + mapping_resp = g_at.storage->pke_at_storage_interface_ext_mapping_get(deets); + if (mapping_resp.result_code == pke_at_storage_interface_result_code_success && mapping_resp.value != nullptr && mapping_resp.value->details.next > 0) { + song_details.uuid = mapping_resp.value->details[0].uuid; + } else { + song_details.uuid = pk_uuid_new_v7(); + } + deets.uuid = song_details.uuid; + song_details.key = plan_item->details.key; song_details.ccli = plan_item->song.details.ccli; song_details.title = plan_item->song.details.title; song_details.arrangement = plan_item->arrangement.details.title; song_details.bpm = plan_item->arrangement.details.beats_per_minute; g_at.storage->pke_at_storage_interface_song_upsert(song_details); - pke_at_setlist_song_details setlist_song_details{}; + g_at.storage->pke_at_storage_interface_ext_mapping_upsert(deets); + setlist_song_details.setlist_uuid = setlist_details.uuid; setlist_song_details.song_uuid = song_details.uuid; g_at.storage->pke_at_storage_interface_setlist_song_upsert(setlist_song_details); diff --git a/src/pke-at-setlist-types.hpp b/src/pke-at-setlist-types.hpp index 242df69..8e09cf0 100644 --- a/src/pke-at-setlist-types.hpp +++ b/src/pke-at-setlist-types.hpp @@ -3,6 +3,33 @@ #include <pke/pke.hpp> +enum PKE_AT_EXT_MAPPING_TYPE { + PKE_AT_EXT_MAPPING_TYPE_NONE, + PKE_AT_EXT_MAPPING_TYPE_LONG, + PKE_AT_EXT_MAPPING_TYPE_COUNT, +}; + +enum PKE_AT_EXT_MAPPING_SOURCE { + PKE_AT_EXT_MAPPING_SOURCE_NONE, + PKE_AT_EXT_MAPPING_SOURCE_PLANNING_CENTER_SERVICES, + PKE_AT_EXT_MAPPING_SOURCE_COUNT, +}; + +struct pke_at_ext_mapping_details { + PKE_AT_EXT_MAPPING_TYPE mapping_type; + PKE_AT_EXT_MAPPING_SOURCE mapping_source; + union pke_at_ext_mapping_details_extern_id { + long id_long; + } id; + pk_uuid uuid; +}; +struct pke_at_ext_mapping { + struct pke_at_ext_mapping_details details; +}; +struct pke_at_ext_mappings { + pk_arr_t<struct pke_at_ext_mapping_details> details; +}; + enum PKE_AT_SECTION_TYPE_INDEX { PKE_AT_SECTION_TYPE_INDEX_NONE, PKE_AT_SECTION_TYPE_INDEX_VERSE, diff --git a/src/pke-at-storage-interface.hpp b/src/pke-at-storage-interface.hpp index 5cbe5a7..65b423e 100644 --- a/src/pke-at-storage-interface.hpp +++ b/src/pke-at-storage-interface.hpp @@ -36,6 +36,9 @@ struct pke_at_storage_interface { virtual ~pke_at_storage_interface() = default; virtual void init() const = 0; virtual void teardown() const = 0; + virtual PASIRA_DEL(ext_mapping, pke_at_ext_mapping_details) const = 0; + virtual PASIRA_GET(ext_mapping, pke_at_ext_mappings, pke_at_ext_mapping_details) const = 0; + virtual PASIR_UPS(ext_mapping, pke_at_ext_mapping_details) const = 0; virtual PASIR_DEL(section) const = 0; virtual PASIR_GET(section, pke_at_section_details) const = 0; virtual PASIR_UPS(section, pke_at_section_details) const = 0; diff --git a/src/pke-at-storage-sql.cpp b/src/pke-at-storage-sql.cpp index 8efdd8c..6689314 100644 --- a/src/pke-at-storage-sql.cpp +++ b/src/pke-at-storage-sql.cpp @@ -3,15 +3,18 @@ #include "pke-at-settings.hpp" #include "sql/schema_000_000_sql.h" +#include "sql/ext_mapping_delete_sql.h" +#include "sql/ext_mapping_get_sql.h" +#include "sql/ext_mapping_upsert_sql.h" #include "sql/section_delete_sql.h" #include "sql/section_get_sql.h" #include "sql/section_upsert_sql.h" #include "sql/setlist_delete_sql.h" #include "sql/setlist_get_sql.h" -#include "sql/setlist_upsert_sql.h" #include "sql/setlist_song_delete_sql.h" #include "sql/setlist_song_get_sql.h" #include "sql/setlist_song_upsert_sql.h" +#include "sql/setlist_upsert_sql.h" #include "sql/song_delete_sql.h" #include "sql/song_get_sql.h" #include "sql/song_upsert_sql.h" @@ -25,7 +28,7 @@ #define PASIS_RES(R) { .result_code = pke_at_storage_interface_result_code(R) } #define PASIS_REST(R,V) { .result_code = pke_at_storage_interface_result_code(R), .value = V } -constexpr unsigned long query_text_length = 1024; +constexpr unsigned long query_text_length = 2048; static const char *SQLITE_DB_PATH = "pke-at.sqlite3"; @@ -593,3 +596,135 @@ cleanup: if (db != nullptr) sqlite3_close(db); return PASIS_RES(ret); } + +pke_at_storage_interface_response +pke_at_storage_sql::pke_at_storage_interface_ext_mapping_delete(pke_at_ext_mapping_details extern_id) +const { + int res, ret = 1; + size_t len = 0; + sqlite3 *db = nullptr; + char *sql_err_string = nullptr; + res = sqlite3_open(SQLITE_DB_PATH, &db); + assert(res == SQLITE_OK); + { + len = snprintf(sql_mstr.query_text, query_text_length, + "%.*s WHERE source_id='%u'", + src_sql_ext_mapping_delete_sql_len, src_sql_ext_mapping_delete_sql, extern_id.mapping_source); + switch (extern_id.mapping_type) { + case PKE_AT_EXT_MAPPING_TYPE_LONG: + len += snprintf(sql_mstr.query_text + len, query_text_length - len, " AND id_external_integer='%li'", extern_id.id.id_long); + break; + default: + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_song_delete] Failed, Unhandled PKE_AT_EXT_MAPPING_TYPE: %i\n", extern_id.mapping_type); + ret= 2; + goto cleanup; + } + res = sqlite3_exec(db, sql_mstr.query_text, nullptr, nullptr, &sql_err_string); + if (res != SQLITE_OK) { + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_song_delete] Failed, sqlite err: %i, %s\n", res, sql_err_string); + ret = 2; + goto cleanup; + } + } +cleanup: + if (db != nullptr) sqlite3_close(db); + return PASIS_RES(ret); +} + +pke_at_storage_interface_response_t<pke_at_ext_mappings> +pke_at_storage_sql::pke_at_storage_interface_ext_mapping_get(pke_at_ext_mapping_details extern_id) +const { + int res, ret = 1; + size_t len = 0; + sqlite3 *db = nullptr; + char *sql_err_string = nullptr; + pke_at_ext_mappings *details = nullptr; + res = sqlite3_open(SQLITE_DB_PATH, &db); + assert(res == SQLITE_OK); + { + len = snprintf(sql_mstr.query_text, query_text_length, + "%.*s WHERE source_id='%u'", + src_sql_ext_mapping_get_sql_len, src_sql_ext_mapping_get_sql, extern_id.mapping_source); + switch (extern_id.mapping_type) { + case PKE_AT_EXT_MAPPING_TYPE_LONG: + len += snprintf(sql_mstr.query_text + len, query_text_length - len, " AND id_external_integer='%li'", extern_id.id.id_long); + break; + default: + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_ext_mapping_get] Failed, Unhandled PKE_AT_EXT_MAPPING_TYPE: %i\n", extern_id.mapping_type); + ret= 2; + goto cleanup; + } + auto fn = [](void *user_data, int column_count, char **argv, char **colname) -> int { + unsigned int u; + int i; + (void)colname; + PK_STN_RES stn_res; + auto **deets = reinterpret_cast<pke_at_ext_mappings**>(user_data); + if (*deets == nullptr) { + *deets = pk_new<pke_at_ext_mappings>(pkeSettings.mem_bkt.game_transient); + (*deets)->details = pk_arr_t<pke_at_ext_mapping_details>(pkeSettings.mem_bkt.game_transient); + } + pke_at_ext_mapping_details d{}; + for (i = 0; i < column_count; ++i) { + if (i == 0) { + stn_res = pk_stn(&u, argv[i], nullptr); + if (stn_res != PK_STN_RES_SUCCESS) { + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_ext_mapping_get::fn] source_id STN err: %i.\n", stn_res); + return SQLITE_ERROR; + } + d.mapping_source = (PKE_AT_EXT_MAPPING_SOURCE)u; + } + if (i == 1) { + stn_res = pk_stn(&u, argv[i], nullptr); + if (stn_res != PK_STN_RES_SUCCESS) { + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_ext_mapping_get::fn] id_external_integer STN err: %i.\n", stn_res); + return SQLITE_ERROR; + } + if (u != 0) { + d.mapping_type = PKE_AT_EXT_MAPPING_TYPE_LONG; + d.id.id_long = (PKE_AT_EXT_MAPPING_TYPE)u; + } + } + if (i == 2) { + d.uuid << argv[i]; + } + } + pk_arr_append_t<pke_at_ext_mapping_details>(&(*deets)->details, d); + return SQLITE_OK; + }; + res = sqlite3_exec(db, sql_mstr.query_text, fn, &details, &sql_err_string); + if (res != SQLITE_OK) { + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_ext_mapping_get] Failed, sqlite err: %i, %s\n", res, sql_err_string); + ret = 2; + goto cleanup; + } + } +cleanup: + if (db != nullptr) sqlite3_close(db); + return PASIS_REST(ret, details); +} + +pke_at_storage_interface_response +pke_at_storage_sql::pke_at_storage_interface_ext_mapping_upsert(const pke_at_ext_mapping_details &external_id) +const { + int res, ret = 1; + sqlite3 *db = nullptr; + char *sql_err_string = nullptr; + res = sqlite3_open(SQLITE_DB_PATH, &db); + assert(res == SQLITE_OK); + { + char uuid[59]; + snprintf(uuid, 59, pk_uuid_printf_format, pk_uuid_printf_var(external_id.uuid)); + snprintf(sql_mstr.query_text, query_text_length, + (char*)src_sql_ext_mapping_upsert_sql, external_id.mapping_source, external_id.id.id_long, uuid); + res = sqlite3_exec(db, sql_mstr.query_text, nullptr, nullptr, &sql_err_string); + if (res != SQLITE_OK) { + fprintf(stderr, "[pke_at_storage_sql::pke_at_storage_interface_ext_mapping_upsert] Failed, sqlite err: %i, %s\n", res, sql_err_string); + ret = 2; + goto cleanup; + } + } +cleanup: + if (db != nullptr) sqlite3_close(db); + return PASIS_RES(ret); +} diff --git a/src/pke-at-storage-sql.hpp b/src/pke-at-storage-sql.hpp index e050929..27a20f0 100644 --- a/src/pke-at-storage-sql.hpp +++ b/src/pke-at-storage-sql.hpp @@ -6,6 +6,9 @@ struct pke_at_storage_sql : public pke_at_storage_interface { void init() const override; void teardown() const override; + PASIRA_DEL(ext_mapping, pke_at_ext_mapping_details) const override; + PASIRA_GET(ext_mapping, pke_at_ext_mappings, pke_at_ext_mapping_details) const override; + PASIR_UPS(ext_mapping, pke_at_ext_mapping_details) const override; PASIR_DEL(section) const override; PASIR_GET(section, pke_at_section_details) const override; PASIR_UPS(section, pke_at_section_details) const override; 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); |