WackoWiki

View source for SQLite Table Creation Script

%%(hl sql)

CREATE TABLE "acl" (
	"acl_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"privilege" VARCHAR(10) NOT NULL DEFAULT '' ,
	"list" TEXT NOT NULL
);

CREATE UNIQUE INDEX "acl_idx_page_id" ON "acl" ("page_id", "privilege");


CREATE TABLE "auth_token" (
	"auth_token_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"selector" CHARACTER(12) NOT NULL DEFAULT '' ,
	"token" CHARACTER(64) NOT NULL DEFAULT '' ,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"token_expires" DATETIME NULL
);

CREATE INDEX "auth_token_idx_user_id" ON "auth_token" ("user_id");

CREATE UNIQUE INDEX "auth_token_idx_selector" ON "auth_token" ("selector");


CREATE TABLE "cache" (
	"cache_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"hash" CHARACTER(40) NOT NULL DEFAULT '' ,
	"method" VARCHAR(20) NOT NULL DEFAULT '' ,
	"query" VARCHAR(255) NOT NULL DEFAULT '' ,
	"cache_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"cache_time" DATETIME NULL
);

CREATE INDEX "cache_idx_hash" ON "cache" ("hash");

CREATE INDEX "cache_idx_cache_time" ON "cache" ("cache_time");


CREATE TABLE "category" (
	"category_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"parent_id" INTEGER NOT NULL DEFAULT '0' ,
	"category_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"category" VARCHAR(255) NOT NULL DEFAULT '' ,
	"category_description" TEXT NOT NULL
);

CREATE UNIQUE INDEX "category_idx_category" ON "category" ("category_lang", "category");


CREATE TABLE "category_assignment" (
	"assignment_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"category_id" INTEGER NOT NULL DEFAULT '0' ,
	"object_type_id" INTEGER NOT NULL DEFAULT '0' ,
	"object_id" INTEGER NOT NULL DEFAULT '0'
);

CREATE INDEX "category_assignment_idx_object_type_id" ON "category_assignment" ("object_type_id");

CREATE INDEX "category_assignment_idx_object_id" ON "category_assignment" ("object_id");

CREATE INDEX "category_assignment_idx_category_id" ON "category_assignment" ("category_id");

CREATE UNIQUE INDEX "category_assignment_idx_assignment" ON "category_assignment" ("category_id", "object_type_id", "object_id");


CREATE TABLE "config" (
	"config_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"config_name" VARCHAR(100) NOT NULL DEFAULT '' ,
	"config_value" TEXT NULL
);

CREATE UNIQUE INDEX "config_idx_config_name" ON "config" ("config_name");


CREATE TABLE "external_link" (
	"link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"link" TEXT NOT NULL
);

CREATE INDEX "external_link_idx_page_id" ON "external_link" ("page_id");


CREATE TABLE "file" (
	"file_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"file_name" VARCHAR(255) NOT NULL DEFAULT '' ,
	"file_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"file_description" VARCHAR(255) NOT NULL DEFAULT '' ,
	"caption" TEXT NULL  ,
	"author" VARCHAR(255) NOT NULL DEFAULT '' ,
	"source" VARCHAR(255) NOT NULL DEFAULT '' ,
	"source_url" VARCHAR(255) NOT NULL DEFAULT '' ,
	"license_id" INTEGER NOT NULL DEFAULT '0' ,
	"created" DATETIME NULL  ,
	"modified" DATETIME NULL  ,
	"file_size" INTEGER NOT NULL DEFAULT '0' ,
	"picture_w" INTEGER NOT NULL DEFAULT '0' ,
	"picture_h" INTEGER NOT NULL DEFAULT '0' ,
	"file_ext" VARCHAR(10) NOT NULL DEFAULT '' ,
	"mime_type" VARCHAR(255) NOT NULL DEFAULT '' ,
	"file_hash" CHARACTER(40) NOT NULL DEFAULT '' ,
	"deleted" TINYINT NULL DEFAULT '0'
);

CREATE INDEX "file_idx_user_id" ON "file" ("user_id");

CREATE INDEX "file_idx_page_id_2" ON "file" ("page_id", "created");

CREATE UNIQUE INDEX "file_idx_page_id" ON "file" ("page_id", "file_name");

CREATE INDEX "file_idx_file_name" ON "file" ("file_name");

CREATE INDEX "file_idx_file_hash" ON "file" ("file_hash");

CREATE INDEX "file_idx_deleted" ON "file" ("deleted");


CREATE TABLE "file_link" (
	"file_link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"file_id" INTEGER NOT NULL DEFAULT '0'
);

CREATE INDEX "file_link_idx_page_id" ON "file_link" ("page_id");

CREATE INDEX "file_link_idx_file_id" ON "file_link" ("file_id");


CREATE TABLE "log" (
	"log_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"log_time" DATETIME NULL  ,
	"level" TINYINT NOT NULL DEFAULT '0' ,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"ip" VARCHAR(45) NOT NULL DEFAULT '' ,
	"message" TEXT NOT NULL
);

CREATE INDEX "log_idx_user_id" ON "log" ("user_id");

CREATE INDEX "log_idx_time" ON "log" ("log_time");

CREATE INDEX "log_idx_level" ON "log" ("level");

CREATE INDEX "log_idx_ip" ON "log" ("ip");


CREATE TABLE "menu" (
	"menu_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"menu_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"menu_title" VARCHAR(255) NOT NULL DEFAULT '' ,
	"menu_position" SMALLINT NOT NULL DEFAULT '0'
);

CREATE INDEX "menu_idx_user_id" ON "menu" ("user_id");

CREATE INDEX "menu_idx_page_id" ON "menu" ("page_id");

CREATE UNIQUE INDEX "menu_idx_menu" ON "menu" ("user_id", "page_id", "menu_lang");

CREATE INDEX "menu_idx_lang" ON "menu" ("menu_lang");


CREATE TABLE "page" (
	"page_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"version_id" INTEGER NOT NULL DEFAULT '1' ,
	"owner_id" INTEGER NOT NULL DEFAULT '0' ,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"tag" VARCHAR(255) NOT NULL DEFAULT '' ,
	"title" VARCHAR(255) NOT NULL DEFAULT '' ,
	"menu_tag" VARCHAR(255) NOT NULL DEFAULT '' ,
	"depth" INTEGER NOT NULL DEFAULT '0' ,
	"parent_id" INTEGER NOT NULL DEFAULT '0' ,
	"created" DATETIME NULL  ,
	"modified" DATETIME NULL  ,
	"body" TEXT NOT NULL  ,
	"body_r" TEXT NOT NULL  ,
	"body_toc" TEXT NOT NULL  ,
	"formatting" VARCHAR(20) NOT NULL DEFAULT 'wacko' ,
	"edit_note" VARCHAR(255) NOT NULL DEFAULT '' ,
	"minor_edit" TINYINT NULL DEFAULT '0' ,
	"page_size" INTEGER NOT NULL DEFAULT '0' ,
	"license_id" INTEGER NOT NULL DEFAULT '0' ,
	"reviewed" TINYINT NOT NULL DEFAULT '0' ,
	"reviewed_time" DATETIME NULL  ,
	"reviewer_id" INTEGER NOT NULL DEFAULT '0' ,
	"ip" VARCHAR(45) NOT NULL DEFAULT '' ,
	"latest" TINYINT NULL DEFAULT '1' ,
	"handler" VARCHAR(30) NOT NULL DEFAULT 'page' ,
	"comment_on_id" INTEGER NOT NULL DEFAULT '0' ,
	"comments" INTEGER NOT NULL DEFAULT '0' ,
	"files" INTEGER NOT NULL DEFAULT '0' ,
	"revisions" INTEGER NOT NULL DEFAULT '0' ,
	"hits" INTEGER NOT NULL DEFAULT '0' ,
	"theme" VARCHAR(20) NULL  ,
	"page_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"commented" DATETIME NULL  ,
	"description" VARCHAR(255) NOT NULL DEFAULT '' ,
	"keywords" VARCHAR(255) NOT NULL DEFAULT '' ,
	"footer_comments" TINYINT NULL  ,
	"footer_files" TINYINT NULL  ,
	"hide_toc" TINYINT NULL  ,
	"hide_index" TINYINT NULL  ,
	"tree_level" TINYINT NOT NULL DEFAULT '0' ,
	"show_menu_tag" TINYINT NOT NULL DEFAULT '0' ,
	"allow_rawhtml" TINYINT NULL  ,
	"disable_safehtml" TINYINT NULL  ,
	"typografica" TINYINT NULL  ,
	"noindex" TINYINT NULL DEFAULT '0' ,
	"deleted" TINYINT NULL DEFAULT '0'
);

CREATE INDEX "page_idx_user_id" ON "page" ("user_id");

CREATE INDEX "page_idx_title" ON "page" ("title");

CREATE UNIQUE INDEX "page_idx_tag" ON "page" ("tag");

CREATE INDEX "page_idx_reviewed" ON "page" ("reviewed");

CREATE INDEX "page_idx_owner_id" ON "page" ("owner_id");

CREATE INDEX "page_idx_modified" ON "page" ("modified");

CREATE INDEX "page_idx_minor_edit" ON "page" ("minor_edit");

CREATE INDEX "page_idx_depth" ON "page" ("depth");

CREATE INDEX "page_idx_deleted" ON "page" ("deleted");

CREATE INDEX "page_idx_created" ON "page" ("created");

CREATE INDEX "page_idx_comment_on_id" ON "page" ("comment_on_id");

CREATE INDEX "page_idx_commented" ON "page" ("commented");


DELIMITER ;;
CREATE TRIGGER page_ai AFTER INSERT ON page
    BEGIN
        INSERT INTO page_fts (rowid, title, body)
        VALUES (new.id, new.title, new.body);
    END;;
CREATE TRIGGER page_ad AFTER DELETE ON page
	BEGIN
		INSERT INTO page_fts (page_fts, rowid, title, body)
		VALUES ('delete', old.id, old.title, old.body);
	END;;
CREATE TRIGGER page_au AFTER UPDATE ON page BEGIN INSERT INTO page_fts (rowid, title, body)
		VALUES (new.page_id, new.title, new.body); END;;

DELIMITER ;

CREATE VIRTUAL TABLE page_fts USING fts5(
		page_id,
		owner_id,
		user_id,
		created, 
		modified,
		tag,
		title, 
		body,comment_on_id, page_lang,
		page_size UNINDEXED,
		comments UNINDEXED,
		content='page', 
		content_rowid='page_id');


CREATE TABLE "page_link" (
	"link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"from_page_id" INTEGER NOT NULL DEFAULT '0' ,
	"to_page_id" INTEGER NOT NULL DEFAULT '0' ,
	"to_tag" VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE INDEX "page_link_idx_to" ON "page_link" ("to_tag");

CREATE INDEX "page_link_idx_from_tag" ON "page_link" ("from_page_id", "to_tag");

CREATE INDEX "page_link_idx_from_page_id" ON "page_link" ("from_page_id");


CREATE TABLE "referrer" (
	"referrer_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"referrer" VARCHAR(2083) NOT NULL DEFAULT '' ,
	"referrer_time" DATETIME NULL  ,
	"ip" VARCHAR(45) NOT NULL DEFAULT '' ,
	"user_agent" TEXT NULL
);

CREATE INDEX "referrer_idx_referrer_time" ON "referrer" ("referrer_time");

CREATE INDEX "referrer_idx_page_id" ON "referrer" ("page_id");


CREATE TABLE "revision" (
	"revision_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"version_id" INTEGER NOT NULL DEFAULT '0' ,
	"owner_id" INTEGER NOT NULL DEFAULT '0' ,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"tag" VARCHAR(255) NOT NULL DEFAULT '' ,
	"title" VARCHAR(255) NOT NULL DEFAULT '' ,
	"menu_tag" VARCHAR(255) NOT NULL DEFAULT '' ,
	"created" DATETIME NULL  ,
	"modified" DATETIME NULL  ,
	"body" TEXT NOT NULL  ,
	"body_r" TEXT NOT NULL  ,
	"formatting" VARCHAR(20) NOT NULL DEFAULT '' ,
	"edit_note" VARCHAR(255) NOT NULL DEFAULT '' ,
	"minor_edit" TINYINT NULL DEFAULT '0' ,
	"page_size" INTEGER NOT NULL DEFAULT '0' ,
	"reviewed" TINYINT NOT NULL DEFAULT '0' ,
	"reviewed_time" DATETIME NULL  ,
	"reviewer_id" INTEGER NOT NULL DEFAULT '0' ,
	"latest" TINYINT NULL DEFAULT '0' ,
	"ip" VARCHAR(45) NOT NULL DEFAULT '' ,
	"handler" VARCHAR(30) NOT NULL DEFAULT 'page' ,
	"comment_on_id" INTEGER NOT NULL DEFAULT '0' ,
	"page_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"description" VARCHAR(255) NOT NULL DEFAULT '' ,
	"keywords" VARCHAR(255) NOT NULL DEFAULT '' ,
	"deleted" TINYINT NULL DEFAULT '0'
);

CREATE INDEX "revision_idx_version_id" ON "revision" ("version_id");

CREATE INDEX "revision_idx_user_id" ON "revision" ("user_id");

CREATE INDEX "revision_idx_tag" ON "revision" ("tag");

CREATE INDEX "revision_idx_reviewed" ON "revision" ("reviewed");

CREATE INDEX "revision_idx_page_id" ON "revision" ("page_id");

CREATE INDEX "revision_idx_owner_id" ON "revision" ("owner_id");

CREATE INDEX "revision_idx_modified" ON "revision" ("modified");

CREATE INDEX "revision_idx_minor_edit" ON "revision" ("minor_edit");

CREATE INDEX "revision_idx_deleted" ON "revision" ("deleted");

CREATE INDEX "revision_idx_comment_on_id" ON "revision" ("comment_on_id");


CREATE TABLE "user" (
	"user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"user_name" VARCHAR(80) NOT NULL DEFAULT '' ,
	"real_name" VARCHAR(80) NOT NULL DEFAULT '' ,
	"password" VARCHAR(255) NOT NULL DEFAULT '' ,
	"email" VARCHAR(254) NOT NULL DEFAULT '' ,
	"account_status" TINYINT NOT NULL DEFAULT '0' ,
	"account_type" TINYINT NOT NULL DEFAULT '0' ,
	"enabled" TINYINT NOT NULL DEFAULT '1' ,
	"signup_time" DATETIME NULL  ,
	"change_password" VARCHAR(64) NOT NULL DEFAULT '' ,
	"user_ip" VARCHAR(45) NOT NULL DEFAULT '' ,
	"email_confirm" VARCHAR(64) NOT NULL DEFAULT '' ,
	"last_visit" DATETIME NULL  ,
	"last_mark" DATETIME NULL  ,
	"login_count" INTEGER NOT NULL DEFAULT '0' ,
	"password_request_count" SMALLINT NOT NULL DEFAULT '0' ,
	"failed_login_count" SMALLINT NOT NULL DEFAULT '0' ,
	"total_pages" INTEGER NOT NULL DEFAULT '0' ,
	"total_revisions" INTEGER NOT NULL DEFAULT '0' ,
	"total_comments" INTEGER NOT NULL DEFAULT '0' ,
	"total_uploads" INTEGER NOT NULL DEFAULT '0'
);

CREATE UNIQUE INDEX "user_idx_user_name" ON "user" ("user_name");

CREATE INDEX "user_idx_signup_time" ON "user" ("signup_time");

CREATE INDEX "user_idx_enabled" ON "user" ("enabled");

CREATE INDEX "user_idx_account_type" ON "user" ("account_type");


CREATE TABLE "user_setting" (
	"setting_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"theme" VARCHAR(20) NULL  ,
	"user_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
	"list_count" INTEGER NOT NULL DEFAULT '50' ,
	"menu_items" INTEGER NOT NULL DEFAULT '5' ,
	"dont_redirect" TINYINT NULL  ,
	"send_watchmail" TINYINT NULL  ,
	"show_files" TINYINT NULL  ,
	"show_comments" TINYINT NOT NULL DEFAULT '1' ,
	"doubleclick_edit" TINYINT NOT NULL DEFAULT '1' ,
	"show_spaces" TINYINT NOT NULL DEFAULT '1' ,
	"autocomplete" TINYINT NOT NULL DEFAULT '0' ,
	"numerate_links" TINYINT NOT NULL DEFAULT '1' ,
	"diff_mode" TINYINT NOT NULL DEFAULT '3' ,
	"notify_minor_edit" TINYINT NOT NULL DEFAULT '1' ,
	"notify_page" TINYINT NOT NULL DEFAULT '2' ,
	"notify_comment" TINYINT NOT NULL DEFAULT '1' ,
	"allow_intercom" TINYINT NOT NULL DEFAULT '0' ,
	"allow_massemail" TINYINT NOT NULL DEFAULT '0' ,
	"hide_lastsession" TINYINT NULL  ,
	"validate_ip" TINYINT NULL  ,
	"noid_pubs" TINYINT NOT NULL DEFAULT '0' ,
	"session_length" TINYINT NULL  ,
	"timezone" VARCHAR(100) NOT NULL DEFAULT 'UTC' ,
	"date_preference" VARCHAR(10) NULL DEFAULT 'default' ,
	"sorting_comments" TINYINT NOT NULL DEFAULT '0' ,
	"comments_offset" TINYINT NOT NULL DEFAULT '0'
);

CREATE UNIQUE INDEX "user_setting_idx_user_id" ON "user_setting" ("user_id");

CREATE INDEX "user_setting_idx_send_watchmail" ON "user_setting" ("send_watchmail");


CREATE TABLE "usergroup" (
	"group_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"group_name" VARCHAR(100) NOT NULL DEFAULT '' ,
	"description" VARCHAR(255) NOT NULL DEFAULT '' ,
	"moderator_id" INTEGER NOT NULL DEFAULT '0' ,
	"created" DATETIME NULL  ,
	"is_system" TINYINT NOT NULL DEFAULT '0' ,
	"open" TINYINT NOT NULL DEFAULT '0' ,
	"active" TINYINT NOT NULL DEFAULT '0'
);

CREATE UNIQUE INDEX "usergroup_idx_name" ON "usergroup" ("group_name");


CREATE TABLE "usergroup_member" (
	"group_member_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"group_id" INTEGER NOT NULL DEFAULT '0' ,
	"user_id" INTEGER NOT NULL DEFAULT '0'
);

CREATE UNIQUE INDEX "usergroup_member_idx_group_id" ON "usergroup_member" ("group_id", "user_id");


CREATE TABLE "watch" (
	"watch_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"user_id" INTEGER NOT NULL DEFAULT '0' ,
	"page_id" INTEGER NOT NULL DEFAULT '0' ,
	"comment_id" INTEGER NOT NULL DEFAULT '0' ,
	"pending" TINYINT NOT NULL DEFAULT '0' ,
	"watch_time" DATETIME NULL
);

CREATE INDEX "watch_idx_user_id" ON "watch" ("user_id");

CREATE INDEX "watch_idx_page_id" ON "watch" ("page_id");


CREATE TABLE "word" (
	"word_id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"word" VARCHAR(255) NOT NULL DEFAULT '' ,
	"replacement" VARCHAR(255) NOT NULL DEFAULT ''
);
%%