Source code
Revision control
Copy as Markdown
Other Tools
/*
This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
// Every time the schema or the underlying data changes, you must bump up the
// schema version.
// Remember to:
// 1. Bump up the version number
// 2. Add a migration function to migrate the data to the new schema.
// 3. Update #createDatabaseEntities and #checkDatabaseHealth
// 4. Add a test to check that the migration works correctly.
// Note: migrations should be reasonably re-entry-friendly. If the user
// downgrades, the schema version is decreased, and upon a subsequent upgrade,
// the migration step is reapplied.
// This ensures that any necessary conversions are performed, even for entries
// added after the downgrade.
// In practice, schema changes should be additive, allowing newer versions to
// operate on older schemas, albeit with potentially reduced functionality.
export const AITAB_PAGES_TABLE = `
CREATE TABLE aitab_pages (
uuid TEXT PRIMARY KEY,
conv_id TEXT NOT NULL,
slug TEXT NOT NULL,
version INTEGER NOT NULL DEFAULT 1,
title TEXT NOT NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
context_jsonb BLOB,
components_jsonb BLOB,
localstate_jsonb BLOB
) WITHOUT ROWID;
`;
export const AITAB_PAGE_INSERT = `
INSERT INTO aitab_pages (
uuid, conv_id, slug, version, title, created_at, updated_at,
context_jsonb, components_jsonb, localstate_jsonb
) VALUES (
:uuid, :conv_id, :slug, :version, :title, :created_at, :updated_at,
jsonb(:context), jsonb(:components), jsonb(:localstate)
);
`;
// Compound index on (slug, version): serves both "latest version for a slug"
// (walk the index backwards, no separate sort) and "a specific slug + version"
// (direct seek). By the leftmost-prefix rule it also covers plain slug-only
// lookups, so no separate single-column slug index is needed.
export const AITAB_PAGES_SLUG_VERSION_INDEX = `
CREATE INDEX idx_aitab_pages_slug_version ON aitab_pages (slug, version);
`;
export const GET_NEXT_VERSION = `
SELECT COALESCE(MAX(version), 0) + 1 AS next_version
FROM aitab_pages
WHERE conv_id = :conv_id;
`;
const AITAB_PAGE_COLUMNS = `
uuid, conv_id, slug, version, title, created_at, updated_at,
json(context_jsonb) AS context,
json(components_jsonb) AS components,
json(localstate_jsonb) AS localstate
`;
// Latest version for a slug: the common "load the page" query, since a
// slug-only URL implies the newest version. Uses the (slug, version) index.
export const GET_AITAB_BY_SLUG = `
SELECT ${AITAB_PAGE_COLUMNS}
FROM aitab_pages
WHERE slug = :slug
ORDER BY version DESC
LIMIT 1;
`;
export const GET_AITAB_BY_SLUG_AND_VERSION = `
SELECT ${AITAB_PAGE_COLUMNS}
FROM aitab_pages
WHERE slug = :slug AND version = :version;
`;
// Available version numbers for a slug, newest first. Selects only `version`,
// so the (slug, version) index fully covers it (no table access).
export const GET_AITAB_VERSIONS_BY_SLUG = `
SELECT version
FROM aitab_pages
WHERE slug = :slug
ORDER BY version DESC;
`;
export const GET_AITAB_PAGES_BY_CONV_ID = `
SELECT ${AITAB_PAGE_COLUMNS}
FROM aitab_pages
WHERE conv_id = :conv_id
ORDER BY version ASC;
`;