-
Notifications
You must be signed in to change notification settings - Fork 104
psi.sqlite schema (Photos 6)
Rhet Turnbull edited this page Aug 25, 2020
·
1 revision
CREATE TABLE word_embedding(word TEXT, extended_word TEXT, score DOUBLE);
CREATE INDEX word_embedding_index ON word_embedding(word);
CREATE VIRTUAL TABLE word_embedding_prefix USING fts5(extended_word)
/* word_embedding_prefix(extended_word) */
;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE groups(
category INT2,
owning_groupid INT,
content_string TEXT,
normalized_string TEXT,
lookup_identifier TEXT,
token_ranges_0 INT8,
token_ranges_1 INT8,
UNIQUE(
category,
owning_groupid,
content_string,
lookup_identifier,
token_ranges_0,
token_ranges_1
)
);
CREATE TABLE assets(
uuid_0 INT,
uuid_1 INT,
creationDate INT,
UNIQUE(uuid_0, uuid_1)
);
CREATE TABLE ga(
groupid INT,
assetid INT,
PRIMARY KEY(groupid, assetid)
);
CREATE TABLE collections(
uuid_0 INT,
uuid_1 INT,
startDate INT,
endDate INT,
title TEXT,
subtitle TEXT,
keyAssetUUID_0 INT,
keyAssetUUID_1 INT,
typeAndNumberOfAssets INT32,
sortDate DOUBLE,
UNIQUE(uuid_0, uuid_1)
);
CREATE TABLE gc(
groupid INT,
collectionid INT,
PRIMARY KEY(groupid, collectionid)
);
CREATE VIRTUAL TABLE prefix USING fts5(
content = 'groups',
normalized_string,
category UNINDEXED,
tokenize = 'PSITokenizer'
);
CREATE TABLE IF NOT EXISTS 'prefix_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE lookup(identifier TEXT PRIMARY KEY, category INT2);
CREATE TRIGGER trigger_groups_insert
AFTER
INSERT
ON groups BEGIN
INSERT INTO
prefix(rowid, normalized_string, category)
VALUES
(new.rowid, new.normalized_string, new.category);
END;
CREATE TRIGGER trigger_groups_delete
AFTER
DELETE ON groups BEGIN
INSERT INTO
prefix(prefix, rowid, normalized_string, category)
VALUES
(
'delete',
old.rowid,
old.normalized_string,
old.category
);
END;
CREATE INDEX group_pk ON groups(
category,
content_string,
normalized_string,
lookup_identifier
);
CREATE INDEX asset_pk ON assets(uuid_0, uuid_1);
CREATE INDEX ga_assetid ON ga(assetid, groupid);
CREATE INDEX collection_pk ON collections(uuid_0, uuid_1);
CREATE INDEX gc_collectionid ON gc(collectionid);