cmk-checklist

init.sql

-- init.sql for SQLite3

CREATE TABLE IF NOT EXISTS "user" (
    "id" integer NOT NULL PRIMARY KEY,
    "slack_user_id" varchar(254) NOT NULL,
    "slack_team_id" varchar(254) NOT NULL,
    "slack_user_name" varchar(254)NOT NULL
);

CREATE TABLE IF NOT EXISTS "cmk_list" (
    "id" integer NOT NULL PRIMARY KEY,
    "event_name" varchar(254),
    "encoding" varchar(254),
    "program_signature" varchar(254)
);

CREATE TABLE IF NOT EXISTS "cmk_list_circle" (
    "id" integer NOT NULL PRIMARY KEY,
    "parent_list_id" integer NOT NULL REFERENCES "cmk_list" ("id"),
    "added_by_id" integer NOT NULL REFERENCES "user" ("id"),

    "serial_number" integer unsinged NOT NULL,
    "color_number" smallint unsigned,
    "page_number" integer unsigned,
    "cut_index" integer unsigned,
    "week" varchar(1),
    "area" varchar(1),
    "block" varchar(1),
    "space_number" smallint unsigned,
    "genre_code" smallint unsigned,
    "circle_name" varchar(254) NOT NULL,
    "circle_name_yomi" varchar(254) NOT NULL,
    "pen_name" varchar(254),
    "book_name" varchar(254),
    "url" varchar(254),
    "mail_address" varchar(254),
    "description" text,
    "memo" text,
    "map_x" integer,
    "map_y" integer,
    "map_layout" integer,
    "space_number_sub" varchar(1),
    "web_catalog_url" varchar(254),
    "update_data" text,
    "circlems_url" varchar(254),
    "rss" varchar(254),
    "rss_data" text,
    "twitter_url" varchar(254),
    "pixiv_url" varchar(254)
);

CREATE TABLE IF NOT EXISTS "cmk_list_unknown" (
    "id" integer NOT NULL PRIMARY KEY,
    "parent_list_id" integer NOT NULL REFERENCES "cmk_list" ("id"),

    "circle_name" varchar(254) NOT NULL,
    "circle_name_yomi" varchar(254) NOT NULL,
    "pen_name" varchar(254),
    "memo" text,
    "color_number" smallint unsigned,
    "book_name" varchar(254),
    "url" varchar(254),
    "mail_address" varchar(254),
    "description" text,
    "update_data" text,
    "circlems_url" varchar(254),
    "rss" varchar(254)
);

CREATE TABLE IF NOT EXISTS "cmk_list_color" (
    "id" integer NOT NULL PRIMARY KEY,
    "parent_list_id" integer NOT NULL REFERENCES "cmk_list" ("id"),

    "color_number" smallint unsigned NOT NULL,
    "check_color" varchar(7) NOT NULL,
    "print_color" varchar(7) NOT NULL,
    "color_description" text
);