96 lines
2.3 KiB
Go
96 lines
2.3 KiB
Go
package kvstore
|
|
|
|
const sqlSchema = `
|
|
BEGIN IMMEDIATE;
|
|
|
|
CREATE TABLE IF NOT EXISTS data(
|
|
SeqNum INTEGER NOT NULL PRIMARY KEY,
|
|
Deleted INTEGER NOT NULL DEFAULT 0,
|
|
Data BLOB NOT NULL
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE INDEX IF NOT EXISTS data_deleted_index ON data(Deleted,SeqNum);
|
|
|
|
CREATE TABLE IF NOT EXISTS log(
|
|
SeqNum INTEGER NOT NULL PRIMARY KEY,
|
|
CreatedAt INTEGER NOT NULL,
|
|
Collection TEXT NOT NULL,
|
|
ID INTEGER NOT NULL,
|
|
Store INTEGER NOT NULL
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE INDEX IF NOT EXISTS log_created_at_index ON log(CreatedAt);
|
|
|
|
CREATE TABLE IF NOT EXISTS kv(
|
|
Collection TEXT NOT NULL,
|
|
ID INTEGER NOT NULL,
|
|
SeqNum INTEGER NOT NULL,
|
|
PRIMARY KEY (Collection, ID)
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE VIEW IF NOT EXISTS kvdata AS
|
|
SELECT
|
|
kv.Collection,
|
|
kv.ID,
|
|
data.Data
|
|
FROM kv
|
|
JOIN data ON kv.SeqNum=data.SeqNum;
|
|
|
|
CREATE VIEW IF NOT EXISTS logdata AS
|
|
SELECT
|
|
log.SeqNum,
|
|
log.Collection,
|
|
log.ID,
|
|
log.Store,
|
|
data.data
|
|
FROM log
|
|
LEFT JOIN data on log.SeqNum=data.SeqNum;
|
|
|
|
COMMIT;`
|
|
|
|
// ----------------------------------------------------------------------------
|
|
|
|
const sqlInsertData = `INSERT INTO data(SeqNum,Data) VALUES(?,?)`
|
|
|
|
const sqlInsertKV = `INSERT INTO kv(Collection,ID,SeqNum) VALUES (?,?,?)
|
|
ON CONFLICT(Collection,ID) DO UPDATE SET SeqNum=excluded.SeqNum
|
|
WHERE ID=excluded.ID`
|
|
|
|
// ----------------------------------------------------------------------------
|
|
|
|
const sqlDeleteKV = `DELETE FROM kv WHERE Collection=? AND ID=?`
|
|
|
|
const sqlDeleteData = `UPDATE data SET Deleted=1
|
|
WHERE SeqNum=(
|
|
SELECT SeqNum FROM kv WHERE Collection=? AND ID=?)`
|
|
|
|
// ----------------------------------------------------------------------------
|
|
|
|
const sqlInsertLog = `INSERT INTO log(SeqNum,CreatedAt,Collection,ID,Store)
|
|
VALUES(?,?,?,?,?)`
|
|
|
|
// ----------------------------------------------------------------------------
|
|
|
|
const sqlKVIterate = `SELECT ID,Data FROM kvdata WHERE Collection=?`
|
|
|
|
const sqlLogIterate = `
|
|
SELECT SeqNum,Collection,ID,Store,Data
|
|
FROM logdata
|
|
WHERE SeqNum > ?
|
|
ORDER BY SeqNum ASC`
|
|
|
|
const sqlMaxSeqNumGet = `SELECT COALESCE(MAX(SeqNum),0) FROM log`
|
|
|
|
const sqlCleanQuery = `
|
|
DELETE FROM
|
|
log
|
|
WHERE
|
|
CreatedAt < ? AND
|
|
SeqNum < (SELECT MAX(SeqNum) FROM log;
|
|
|
|
DELETE FROM
|
|
data
|
|
WHERE
|
|
Deleted != 0 AND
|
|
SeqNum < (SELECT MIN(SeqNum) FROM log;`
|