117 lines
2.7 KiB
Go
117 lines
2.7 KiB
Go
|
package kvstore
|
||
|
|
||
|
/*
|
||
|
Copyright (c) 2022, John David Lee
|
||
|
All rights reserved.
|
||
|
|
||
|
This source code is licensed under the BSD-style license found in the
|
||
|
LICENSE file in the root directory of this source tree.
|
||
|
*/
|
||
|
|
||
|
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;
|
||
|
|
||
|
CREATE TRIGGER IF NOT EXISTS deletedata AFTER UPDATE OF SeqNum ON kv
|
||
|
FOR EACH ROW
|
||
|
WHEN OLD.SeqNum != NEW.SeqNum
|
||
|
BEGIN
|
||
|
UPDATE data SET Deleted=1 WHERE SeqNum=OLD.SeqNum;
|
||
|
END;
|
||
|
|
||
|
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 sqlLogInfoGet = `SELECT
|
||
|
COALESCE(SeqNum, 0),
|
||
|
COALESCE(CreatedAt, 0)
|
||
|
FROM log
|
||
|
ORDER BY SeqNum DESC LIMIT 1`
|
||
|
|
||
|
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);`
|