90 lines
4.7 KiB
SQL
90 lines
4.7 KiB
SQL
-- Generated by pnpm --filter @luzzle/web.db schema:generate. Do not edit.
|
|
-- Statement boundaries let the test helper load complete triggers without parsing SQL.
|
|
|
|
CREATE TABLE "job_progress" ("job_id" text not null, "phase" text not null, "status" text not null, "started_at" integer not null, "finished_at" integer, "message" text, constraint "job_progress_pk" primary key ("job_id", "phase"));
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "job_progress_logs" ("job_id" text not null, "phase" text not null, "line_number" integer not null, "ts" integer not null, "level" text not null, "message" text not null, constraint "job_progress_logs_pk" primary key ("job_id", "phase", "line_number"));
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "pieces_cache" ("id" text not null primary key, "file_path" text not null, "content_hash" text, "date_added" datetime default ((julianday('now') - 2440587.5)*86400000) not null, "date_updated" datetime, constraint "unique-files" unique ("file_path"));
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "pieces_items" ("id" text not null primary key, "date_added" datetime default ((julianday('now') - 2440587.5)*86400000) not null, "date_updated" datetime, "file_path" text not null, "type" text not null, "note_markdown" text, "frontmatter_json" text, "assets_json_array" text, constraint "unique-files" unique ("file_path"));
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "pieces_manager" ("id" text not null primary key, "date_added" datetime default ((julianday('now') - 2440587.5)*86400000) not null, "date_updated" datetime, "name" text not null unique, "schema" text not null);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "web_pieces" ("id" text not null primary key, "key" text not null, "slug" text not null, "type" text not null, "file_path" text not null, "title" text, "summary" text, "note" text, "keywords" text, "json_metadata" text, "date_added" datetime, "date_updated" datetime, "date_consumed" datetime, "content_hash" text, "last_published_at" integer, constraint "slug-type" unique ("slug", "type"));
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "web_pieces_assets" (
|
|
piece_file_path TEXT NOT NULL,
|
|
piece_key TEXT NOT NULL,
|
|
piece_asset_path TEXT DEFAULT NULL,
|
|
transformation TEXT NOT NULL,
|
|
asset_path TEXT,
|
|
piece_field_path TEXT,
|
|
asset_key TEXT NOT NULL DEFAULT '',
|
|
mime_type TEXT NOT NULL,
|
|
is_embedded BOOLEAN DEFAULT 0,
|
|
content TEXT,
|
|
PRIMARY KEY (piece_file_path, transformation, piece_asset_path)
|
|
);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE VIRTUAL TABLE "web_pieces_fts5" USING fts5(id UNINDEXED, key UNINDEXED, slug, type UNINDEXED, title, summary, note, keywords, json_metadata, date_added UNINDEXED, date_updated UNINDEXED, date_consumed UNINDEXED, file_path UNINDEXED, tokenize = 'porter ascii', prefix='3 4 5', content = 'web_pieces', content_rowid="rowid");
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TABLE "web_pieces_tags" ("piece_type" text not null, "piece_slug" text not null, "piece_id" text not null, "tag" text not null, "slug" text not null);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE INDEX web_pieces_date_added_index ON web_pieces (date_added DESC);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE INDEX web_pieces_date_consumed_date_added_index ON web_pieces (date_consumed DESC, date_added DESC);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE INDEX web_pieces_type_date_consumed_date_added_index ON web_pieces (type, date_consumed DESC, date_added DESC);
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TRIGGER web_pieces_after_delete AFTER DELETE ON web_pieces
|
|
BEGIN
|
|
INSERT INTO web_pieces_fts5(web_pieces_fts5, rowid, key, slug, title, summary, note, keywords, json_metadata)
|
|
VALUES('delete', old.rowid, old.key, old.slug, old.title, old.summary, old.note, old.keywords, old.json_metadata);
|
|
|
|
DELETE FROM web_pieces_tags WHERE piece_id = old.id;
|
|
DELETE FROM web_pieces_assets WHERE piece_file_path = old.file_path;
|
|
END;
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TRIGGER web_pieces_after_insert AFTER INSERT ON web_pieces BEGIN
|
|
INSERT INTO web_pieces_fts5(rowid, key, slug, type, title, summary, note, keywords, json_metadata, date_added, date_updated, date_consumed)
|
|
VALUES(new.rowid, new.key, new.slug, new.type, new.title, new.summary, new.note, new.keywords, new.json_metadata, new.date_added, new.date_updated, new.date_consumed);
|
|
END;
|
|
|
|
-- statement-breakpoint
|
|
|
|
CREATE TRIGGER web_pieces_after_update AFTER UPDATE ON web_pieces
|
|
BEGIN
|
|
INSERT INTO web_pieces_fts5(web_pieces_fts5, rowid, key, slug, title, summary, note, keywords, json_metadata)
|
|
VALUES('delete', old.rowid, old.key, old.slug, old.title, old.summary, old.note, old.keywords, old.json_metadata);
|
|
|
|
INSERT INTO web_pieces_fts5(rowid, key, slug, title, summary, note, keywords, json_metadata)
|
|
VALUES (new.rowid, new.key, new.slug, new.title, new.summary, new.note, new.keywords, new.json_metadata);
|
|
END;
|