sections: internals:internals-internal-schema
This data as json
| id | page | ref | title | content | breadcrumbs | references |
|---|---|---|---|---|---|---|
| internals:internals-internal-schema | internals | internals-internal-schema | Internal database schema | The internal database schema is as follows: [[[cog from metadata_doc import internal_schema internal_schema(cog) ]]] CREATE TABLE "_sqlite_migrations" ( "id" INTEGER PRIMARY KEY, "migration_set" TEXT, "name" TEXT, "applied_at" TEXT ); CREATE UNIQUE INDEX "idx__sqlite_migrations_migration_set_name" ON "_sqlite_migrations" ("migration_set", "name"); CREATE TABLE catalog_databases ( database_name TEXT PRIMARY KEY, path TEXT, is_memory INTEGER, schema_version INTEGER ); CREATE TABLE catalog_tables ( database_name TEXT, table_name TEXT, rootpage INTEGER, sql TEXT, PRIMARY KEY (database_name, table_name), FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name) ); CREATE TABLE catalog_views ( database_name TEXT, view_name TEXT, rootpage INTEGER, sql TEXT, PRIMARY KEY (database_name, view_name), FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name) ); CREATE TABLE catalog_columns ( database_name TEXT, table_name TEXT, cid INTEGER, name TEXT, type TEXT, "notnull" INTEGER, default_value TEXT, -- renamed from dflt_value is_pk INTEGER, -- renamed from pk hidden INTEGER, PRIMARY KEY (database_name, table_name, name), FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name), FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name) ); CREATE TABLE catalog_indexes ( database_name TEXT, table_name TEXT, seq INTEGER, name TEXT, "unique" INTEGER, origin TEXT, partial INTEGER, PRIMARY KEY (database_name, table_name, name), FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name), FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name) ); CREATE TABLE catalog_foreign_keys ( database_name TEXT, table_name TEXT, id INTEGER, seq INTEGER, "table" TEXT, "from" TEXT, "to" TEXT, on_update TEXT, on_delete TEXT, match TEXT, PRIMARY KEY (database_name, table_name, id, seq), FOREIGN KEY (database_name) REFERENCES catalog_databases(database_name), FOREIGN KEY (database_name, table_name) REFERENCES catalog_tables(database_name, table_name) ); CREATE TABLE metadata_instance ( key text, value text, unique(key) ); CREATE TABLE metadata_databases ( database_name text, key text, value text, unique(database_name, key) ); CREATE TABLE metadata_resources ( database_name text, resource_name text, key text, value text, unique(database_name, resource_name, key) ); CREATE TABLE metadata_columns ( database_name text, resource_name text, column_name text, key text, value text, unique(database_name, resource_name, column_name, key) ); CREATE TABLE column_types ( database_name TEXT NOT NULL, resource_name TEXT NOT NULL, column_name TEXT NOT NULL, column_type TEXT NOT NULL, config TEXT, PRIMARY KEY (database_name, resource_name, column_name) ); CREATE TABLE queries ( database_name TEXT NOT NULL, name TEXT NOT NULL, sql TEXT NOT NULL, title TEXT, description TEXT, description_html TEXT, options TEXT NOT NULL DEFAULT '{}', parameters TEXT NOT NULL DEFAULT '[]', is_write INTEGER NOT NULL DEFAULT 0 CHECK (is_write IN (0, 1)), is_private INTEGER NOT NULL DEFAULT 0 CHECK (is_private IN (0, 1)), is_trusted INTEGER NOT NULL DEFAULT 0 CHECK (is_trusted IN (0, 1)), source TEXT NOT NULL DEFAULT 'user', owner_id TEXT, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (database_name, name) ); CREATE INDEX queries_owner_idx ON queries(owner_id); [[[end]]] | ["Internals for plugins", "Datasette's internal database"] | [] |