Caravel Data Model

caravel.sql

CREATE TABLE ab_view_menu (
	id INTEGER NOT NULL,
	name VARCHAR(100) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (name)
);
CREATE TABLE ab_permission (
	id INTEGER NOT NULL,
	name VARCHAR(100) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (name)
);
CREATE TABLE ab_user (
	id INTEGER NOT NULL,
	first_name VARCHAR(64) NOT NULL,
	last_name VARCHAR(64) NOT NULL,
	username VARCHAR(64) NOT NULL,
	password VARCHAR(256),
	active BOOLEAN,
	email VARCHAR(64) NOT NULL,
	last_login DATETIME,
	login_count INTEGER,
	fail_login_count INTEGER,
	created_on DATETIME,
	changed_on DATETIME,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	PRIMARY KEY (id),
	UNIQUE (username),
	CHECK (active IN (0, 1)),
	UNIQUE (email),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE ab_register_user (
	id INTEGER NOT NULL,
	first_name VARCHAR(64) NOT NULL,
	last_name VARCHAR(64) NOT NULL,
	username VARCHAR(64) NOT NULL,
	password VARCHAR(256),
	email VARCHAR(64) NOT NULL,
	registration_date DATETIME,
	registration_hash VARCHAR(256),
	PRIMARY KEY (id),
	UNIQUE (username)
);
CREATE TABLE ab_role (
	id INTEGER NOT NULL,
	name VARCHAR(64) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (name)
);
CREATE TABLE ab_permission_view (
	id INTEGER NOT NULL,
	permission_id INTEGER,
	view_menu_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(permission_id) REFERENCES ab_permission (id),
	FOREIGN KEY(view_menu_id) REFERENCES ab_view_menu (id)
);
CREATE TABLE ab_user_role (
	id INTEGER NOT NULL,
	user_id INTEGER,
	role_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES ab_user (id),
	FOREIGN KEY(role_id) REFERENCES ab_role (id)
);
CREATE TABLE ab_permission_view_role (
	id INTEGER NOT NULL,
	permission_view_id INTEGER,
	role_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(permission_view_id) REFERENCES ab_permission_view (id),
	FOREIGN KEY(role_id) REFERENCES ab_role (id)
);
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL
);
CREATE TABLE clusters (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	cluster_name VARCHAR(250),
	coordinator_host VARCHAR(256),
	coordinator_port INTEGER,
	coordinator_endpoint VARCHAR(256),
	broker_host VARCHAR(256),
	broker_port INTEGER,
	broker_endpoint VARCHAR(256),
	metadata_last_refreshed DATETIME,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	PRIMARY KEY (id),
	UNIQUE (cluster_name),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE dashboards (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	dashboard_title VARCHAR(500),
	position_json TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER, css TEXT, description TEXT, slug VARCHAR(255), json_metadata TEXT,
	PRIMARY KEY (id),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE dbs (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	database_name VARCHAR(250),
	sqlalchemy_uri VARCHAR(1024),
	created_by_fk INTEGER,
	changed_by_fk INTEGER, password BLOB, cache_timeout INTEGER, extra TEXT,
	PRIMARY KEY (id),
	UNIQUE (database_name),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE datasources (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	datasource_name VARCHAR(256),
	is_featured BOOLEAN,
	is_hidden BOOLEAN,
	description TEXT,
	default_endpoint TEXT,
	user_id INTEGER,
	cluster_name VARCHAR(250),
	created_by_fk INTEGER,
	changed_by_fk INTEGER, "offset" INTEGER, cache_timeout INTEGER,
	PRIMARY KEY (id),
	UNIQUE (datasource_name),
	CHECK (is_featured IN (0, 1)),
	CHECK (is_hidden IN (0, 1)),
	FOREIGN KEY(user_id) REFERENCES ab_user (id),
	FOREIGN KEY(cluster_name) REFERENCES clusters (cluster_name),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE columns (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	datasource_name VARCHAR(250),
	column_name VARCHAR(256),
	is_active BOOLEAN,
	type VARCHAR(32),
	groupby BOOLEAN,
	count_distinct BOOLEAN,
	sum BOOLEAN,
	max BOOLEAN,
	min BOOLEAN,
	filterable BOOLEAN,
	description TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(column_name) REFERENCES datasources (datasource_name),
	CHECK (is_active IN (0, 1)),
	CHECK (groupby IN (0, 1)),
	CHECK (count_distinct IN (0, 1)),
	CHECK (sum IN (0, 1)),
	CHECK (max IN (0, 1)),
	CHECK (min IN (0, 1)),
	CHECK (filterable IN (0, 1)),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE metrics (
	id INTEGER NOT NULL,
	metric_name VARCHAR(512),
	verbose_name VARCHAR(1024),
	metric_type VARCHAR(32),
	datasource_name VARCHAR(250),
	json TEXT,
	description TEXT, changed_by_fk INTEGER, changed_on DATETIME, created_by_fk INTEGER, created_on DATETIME,
	PRIMARY KEY (id),
	FOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name),
	FOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name)
);
CREATE TABLE slices (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	slice_name VARCHAR(250),
	druid_datasource_id INTEGER,
	table_id INTEGER,
	datasource_type VARCHAR(200),
	datasource_name VARCHAR(2000),
	viz_type VARCHAR(250),
	params TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER, description TEXT, cache_timeout INTEGER, perm VARCHAR(2000),
	PRIMARY KEY (id),
	FOREIGN KEY(druid_datasource_id) REFERENCES datasources (id),
	FOREIGN KEY(table_id) REFERENCES tables (id),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE sql_metrics (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	metric_name VARCHAR(512),
	verbose_name VARCHAR(1024),
	metric_type VARCHAR(32),
	table_id INTEGER,
	expression TEXT,
	description TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(table_id) REFERENCES tables (id),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE table_columns (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	table_id INTEGER,
	column_name VARCHAR(256),
	is_dttm BOOLEAN,
	is_active BOOLEAN,
	type VARCHAR(32),
	groupby BOOLEAN,
	count_distinct BOOLEAN,
	sum BOOLEAN,
	max BOOLEAN,
	min BOOLEAN,
	filterable BOOLEAN,
	description TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER, expression TEXT, verbose_name VARCHAR(1024),
	PRIMARY KEY (id),
	FOREIGN KEY(table_id) REFERENCES tables (id),
	CHECK (is_dttm IN (0, 1)),
	CHECK (is_active IN (0, 1)),
	CHECK (groupby IN (0, 1)),
	CHECK (count_distinct IN (0, 1)),
	CHECK (sum IN (0, 1)),
	CHECK (max IN (0, 1)),
	CHECK (min IN (0, 1)),
	CHECK (filterable IN (0, 1)),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE dashboard_slices (
	id INTEGER NOT NULL,
	dashboard_id INTEGER,
	slice_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(dashboard_id) REFERENCES dashboards (id),
	FOREIGN KEY(slice_id) REFERENCES slices (id)
);
CREATE TABLE logs (
	id INTEGER NOT NULL,
	action VARCHAR(512),
	user_id INTEGER,
	json TEXT,
	dttm DATETIME, dashboard_id INTEGER, slice_id INTEGER, dt DATE,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES ab_user (id)
);
CREATE TABLE "tables" (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	table_name VARCHAR(250),
	main_dttm_col VARCHAR(250),
	default_endpoint TEXT,
	database_id INTEGER NOT NULL,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	"offset" INTEGER,
	description TEXT,
	is_featured BOOLEAN,
	user_id INTEGER, cache_timeout INTEGER, schema VARCHAR(256),
	PRIMARY KEY (id),
	CHECK (is_featured IN (0, 1)),
	CONSTRAINT user_id FOREIGN KEY(user_id) REFERENCES ab_user (id),
	FOREIGN KEY(database_id) REFERENCES dbs (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id),
	UNIQUE (table_name),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE url (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	url TEXT,
	created_by_fk INTEGER,
	changed_by_fk INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE css_templates (
	created_on DATETIME NOT NULL,
	changed_on DATETIME NOT NULL,
	id INTEGER NOT NULL,
	template_name VARCHAR(250),
	css TEXT,
	changed_by_fk INTEGER,
	created_by_fk INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id),
	FOREIGN KEY(created_by_fk) REFERENCES ab_user (id)
);
CREATE TABLE favstar (
	id INTEGER NOT NULL,
	user_id INTEGER,
	class_name VARCHAR(50),
	obj_id INTEGER,
	dttm DATETIME,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES ab_user (id)
);
CREATE TABLE dashboard_user (
	id INTEGER NOT NULL,
	user_id INTEGER,
	dashboard_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(dashboard_id) REFERENCES dashboards (id),
	FOREIGN KEY(user_id) REFERENCES ab_user (id)
);
CREATE TABLE slice_user (
	id INTEGER NOT NULL,
	user_id INTEGER,
	slice_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(slice_id) REFERENCES slices (id),
	FOREIGN KEY(user_id) REFERENCES ab_user (id)
);

Specifically this file