Spaces:
Sleeping
Sleeping
CREATE TABLE IF NOT EXISTS tenants ( | |
id TEXT PRIMARY KEY, | |
UNIQUE (id) | |
); | |
CREATE TABLE IF NOT EXISTS databases ( | |
id TEXT PRIMARY KEY, -- unique globally | |
name TEXT NOT NULL, -- unique per tenant | |
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, | |
UNIQUE (tenant_id, name) -- Ensure that a tenant has only one database with a given name | |
); | |
CREATE TABLE IF NOT EXISTS collections_tmp ( | |
id TEXT PRIMARY KEY, -- unique globally | |
name TEXT NOT NULL, -- unique per database | |
topic TEXT NOT NULL, | |
dimension INTEGER, | |
database_id TEXT NOT NULL REFERENCES databases(id) ON DELETE CASCADE, | |
UNIQUE (name, database_id) | |
); | |
-- Create default tenant and database | |
INSERT OR REPLACE INTO tenants (id) VALUES ('default_tenant'); -- The default tenant id is 'default_tenant' others are UUIDs | |
INSERT OR REPLACE INTO databases (id, name, tenant_id) VALUES ('00000000-0000-0000-0000-000000000000', 'default_database', 'default_tenant'); | |
INSERT OR REPLACE INTO collections_tmp (id, name, topic, dimension, database_id) | |
SELECT id, name, topic, dimension, '00000000-0000-0000-0000-000000000000' FROM collections; | |
DROP TABLE collections; | |
ALTER TABLE collections_tmp RENAME TO collections; | |