Experiment syncing FIDs (feature ids) in a GeoPackage (gpkg) for unique fids across all layers

experiment_synced_primarykey__uuid.sql

/* primary key autoincrement syncing across tables */

/* fids table: holds all primary keys of synced tables */
DROP TABLE IF EXISTS fids;
CREATE TABLE fids (
	fid INTEGER PRIMARY KEY AUTOINCREMENT,
	rel TEXT NOT NULL
);

/* fids table trigger: updates the autoincrement sequence number of synced tables */
DROP TRIGGER IF EXISTS trigger_after_insert_on_fids;
CREATE TRIGGER trigger_after_insert_on_fids
    AFTER INSERT ON fids
BEGIN
	UPDATE sqlite_sequence SET seq=NEW.ROWID WHERE name <> 'fids';
END;
/* fids table trigger: updates the autoincrement sequence number of synced tables */
DROP TRIGGER IF EXISTS trigger_after_update_on_fids;
CREATE TRIGGER trigger_after_update_on_fids
    AFTER UPDATE ON fids
BEGIN
	UPDATE sqlite_sequence SET seq=NEW.ROWID WHERE name <> 'fids';
END;

DROP table if exists my_table;
CREATE TABLE my_table (
	fid INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT,
    created DATE NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT check_my_table_uuid CHECK ((uuid REGEXP '[a-f0-9]{8}-([a-f0-9]{4}-){3}[a-f0-9]{12}') OR (uuid IS NULL))
);
/* need to have an entry else trigger with update fails */
INSERT INTO sqlite_sequence VALUES ('my_table', 0);

DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table_insert_fid;
CREATE TRIGGER trigger_after_insert_on_my_table_insert_fid
    AFTER INSERT ON my_table
BEGIN
	INSERT INTO fids VALUES(NEW.ROWID, 'my_table');
END;

DROP TRIGGER IF EXISTS trigger_after_update_on_my_table_update_fid;
CREATE TRIGGER trigger_after_update_on_my_table_update_fid
    AFTER UPDATE OF fid ON my_table
BEGIN
	UPDATE fids SET fid=NEW.ROWID WHERE fid=OLD.ROWID;
END;

DROP TRIGGER IF EXISTS trigger_after_delete_on_my_table_delete_fid;
CREATE TRIGGER trigger_after_delete_on_my_table_delete_fid
    AFTER DELETE ON my_table
BEGIN
	DELETE FROM fids WHERE fid=OLD.ROWID;
END;

DROP table if exists my_table2;
CREATE TABLE my_table2 (
	fid INTEGER PRIMARY KEY AUTOINCREMENT
    );
INSERT INTO sqlite_sequence VALUES ('my_table2', 0);

DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table2_insert_fid;
CREATE TRIGGER trigger_after_insert_on_my_table2_insert_fid
    AFTER INSERT ON my_table2
BEGIN
	INSERT INTO fids VALUES(NEW.ROWID, 'my_table2');
END;

DROP TRIGGER IF EXISTS trigger_after_update_on_my_table2_update_fid;
CREATE TRIGGER trigger_after_update_on_my_table2_update_fid
    AFTER UPDATE OF fid ON my_table2
BEGIN
	UPDATE fids SET fid=NEW.ROWID WHERE fid=OLD.ROWID;
END;

DROP TRIGGER IF EXISTS trigger_after_delete_on_my_table2_delete_fid;
CREATE TRIGGER trigger_after_delete_on_my_tabl2e_delete_fid
    AFTER DELETE ON my_table2
BEGIN
	DELETE FROM fids WHERE fid=OLD.ROWID;
END;

/* This great example is taken from https://gist.github.com/fabiolimace/e3c3d354d1afe0b3175f65be2d962523 */
DROP VIEW IF EXISTS uuid7;
CREATE VIEW uuid7 AS
WITH unixtime AS (
	SELECT CAST((STRFTIME('%s') * 1000) + ((STRFTIME('%f') * 1000) % 1000) AS INTEGER) AS time
	-- SELECT CAST((UNIXEPOCH('subsec') * 1000) AS INTEGER) AS time -- for SQLite v3.38.0 (2022)
)
SELECT FORMAT('%08x-%04x-%04x-%04x-%012x', 
       (select time from unixtime) >> 16,
       (select time from unixtime) & 0xffff,
       ABS(RANDOM()) % 0x0fff + 0x7000,
       ABS(RANDOM()) % 0x3fff + 0x8000,
       ABS(RANDOM()) >> 16) AS next;

DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table_set_uuid;
CREATE TRIGGER trigger_after_insert_on_my_table_insert_uuid
    AFTER INSERT ON my_table WHEN NEW.uuid IS NULL
BEGIN
	UPDATE my_table SET uuid = (SELECT next FROM uuid7) WHERE ROWID = NEW.ROWID;
END;

DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table_set_createddate;
CREATE TRIGGER trigger_after_insert_on_my_table_set_createddate
    AFTER INSERT ON my_table WHEN NEW.created IS NULL
BEGIN
	UPDATE my_table SET created = DATETIME() WHERE ROWID = NEW.ROWID;
END;