1W3KK5UR

1DROP TABLE IF EXISTS users;
2DROP TABLE IF EXISTS units;
3DROP TABLE IF EXISTS chats;
4DROP TABLE IF EXISTS bots;
5DROP TABLE IF EXISTS messages;
6DROP TABLE IF EXISTS msg_types;
7DROP TABLE IF EXISTS rooms;
8DROP TABLE IF EXISTS branches;
9DROP TABLE IF EXISTS private_pools;
10DROP TABLE IF EXISTS conference_pools;
11DROP TABLE IF EXISTS unit_types;
12
13
14CREATE TABLE users (
15id INTEGER NOT NULL UNIQUE,
16domain VARCHAR(32) NOT NULL UNIQUE,
17name VARCHAR(32) NOT NULL,
18PRIMARY KEY (id,domain));
19
20CREATE TABLE units (
21id INTEGER NOT NULL UNIQUE,
22domain VARCHAR(32) NOT NULL UNIQUE,
23type INTEGER NOT NULL,
24PRIMARY KEY (id,domain));
25
26CREATE TABLE chats (
27id INTEGER NOT NULL UNIQUE,
28domain VARCHAR(32) NOT NULL UNIQUE,
29name VARCHAR(32) NOT NULL,
30owner_id INTEGER NOT NULL,
31desc VARCHAR(32) NOT NULL DEFAULT 'Chat',
32PRIMARY KEY (id,domain));
33
34CREATE TABLE bots (
35id INTEGER NOT NULL UNIQUE,
36domain VARCHAR(32) NOT NULL UNIQUE,
37name VARCHAR(32) NOT NULL,
38owner_id INTEGER NOT NULL,
39PRIMARY KEY (id,domain));
40
41CREATE TABLE messages (
42id INTEGER NOT NULL UNIQUE PRIMARY KEY,
43reply_to INTEGER NOT NULL,
44from_id INTEGER NOT NULL,
45type INTEGER NOT NULL);
46
47CREATE TABLE msg_types (
48id INTEGER NOT NULL UNIQUE PRIMARY KEY,
49type VARCHAR(32) NOT NULL);
50
51CREATE TABLE rooms (
52id INTEGER NOT NULL UNIQUE PRIMARY KEY,
53chat_id INTEGER NOT NULL,
54name VARCHAR(32) NOT NULL,
55desc VARCHAR(32) NOT NULL DEFAULT 'Room');
56
57CREATE TABLE branches (
58id INTEGER NOT NULL UNIQUE PRIMARY KEY,
59room_id INTEGER NOT NULL,
60name VARCHAR(32) NOT NULL,
61desc VARCHAR(32) NOT NULL DEFAULT 'Branch');
62
63CREATE TABLE private_pools (
64id INTEGER NOT NULL UNIQUE PRIMARY KEY,
65owner_id INTEGER NOT NULL,
66message_id INTEGER NOT NULL);
67
68CREATE TABLE conference_pools (
69id INTEGER NOT NULL UNIQUE PRIMARY KEY,
70owner_id INTEGER NOT NULL,
71message_id INTEGER NOT NULL);
72
73CREATE TABLE unit_types (
74id INTEGER NOT NULL UNIQUE PRIMARY KEY,
75type VARCHAR(32) NOT NULL);
76
77ALTER TABLE users ADD CONSTRAINT users_id_units_id FOREIGN KEY (id) REFERENCES units(id);
78ALTER TABLE users ADD CONSTRAINT users_domain_units_domain FOREIGN KEY (domain) REFERENCES units(domain);
79ALTER TABLE units ADD CONSTRAINT units_type_unit_types_id FOREIGN KEY (type) REFERENCES unit_types(id);
80ALTER TABLE chats ADD CONSTRAINT chats_id_units_id FOREIGN KEY (id) REFERENCES units(id);
81ALTER TABLE chats ADD CONSTRAINT chats_domain_units_domain FOREIGN KEY (domain) REFERENCES units(domain);
82ALTER TABLE chats ADD CONSTRAINT chats_owner_id_users_id FOREIGN KEY (owner_id) REFERENCES users(id);
83ALTER TABLE bots ADD CONSTRAINT bots_id_units_id FOREIGN KEY (id) REFERENCES units(id);
84ALTER TABLE bots ADD CONSTRAINT bots_domain_units_domain FOREIGN KEY (domain) REFERENCES units(domain);
85ALTER TABLE bots ADD CONSTRAINT bots_owner_id_users_id FOREIGN KEY (owner_id) REFERENCES users(id);
86ALTER TABLE messages ADD CONSTRAINT messages_reply_to_messages_id FOREIGN KEY (reply_to) REFERENCES messages(id);
87ALTER TABLE messages ADD CONSTRAINT messages_from_id_units_id FOREIGN KEY (from_id) REFERENCES units(id);
88ALTER TABLE messages ADD CONSTRAINT messages_type_msg_types_id FOREIGN KEY (type) REFERENCES msg_types(id);
89ALTER TABLE rooms ADD CONSTRAINT rooms_id_units_id FOREIGN KEY (id) REFERENCES units(id);
90ALTER TABLE rooms ADD CONSTRAINT rooms_chat_id_chats_id FOREIGN KEY (chat_id) REFERENCES chats(id);
91ALTER TABLE branches ADD CONSTRAINT branches_id_units_id FOREIGN KEY (id) REFERENCES units(id);
92ALTER TABLE branches ADD CONSTRAINT branches_room_id_rooms_id FOREIGN KEY (room_id) REFERENCES rooms(id);
93ALTER TABLE private_pools ADD CONSTRAINT private_pools_owner_id_units_id FOREIGN KEY (owner_id) REFERENCES units(id);
94ALTER TABLE private_pools ADD CONSTRAINT private_pools_message_id_messages_id FOREIGN KEY (message_id) REFERENCES messages(id);
95ALTER TABLE conference_pools ADD CONSTRAINT conference_pools_owner_id_units_id FOREIGN KEY (owner_id) REFERENCES units(id);
96ALTER TABLE conference_pools ADD CONSTRAINT conference_pools_message_id_messages_id FOREIGN KEY (message_id) REFERENCES messages(id);
97