summaryrefslogtreecommitdiffstats
path: root/sql/create_pg
diff options
context:
space:
mode:
Diffstat (limited to 'sql/create_pg')
-rw-r--r--sql/create_pg/create_tables.sql239
-rw-r--r--sql/create_pg/drop_tables.sql22
2 files changed, 261 insertions, 0 deletions
diff --git a/sql/create_pg/create_tables.sql b/sql/create_pg/create_tables.sql
new file mode 100644
index 0000000..9923c67
--- /dev/null
+++ b/sql/create_pg/create_tables.sql
@@ -0,0 +1,239 @@
+CREATE TABLE channel_properties (
+ server_id bigint,
+ id bigint,
+ ident TEXT NOT NULL,
+ value TEXT
+);
+CREATE INDEX index_channel_properties_id ON channel_properties (id);
+CREATE INDEX index_channel_properties_serverid ON channel_properties (server_id);
+
+CREATE TABLE channels (
+ channel_id SERIAL PRIMARY KEY NOT NULL,
+ channel_parent_id bigint,
+ server_id bigint NOT NULL
+);
+
+CREATE INDEX index_channels_id ON channels (channel_id);
+CREATE INDEX index_channels_serverid ON channels (server_id);
+
+CREATE TABLE client_properties (
+ server_id bigint,
+ id bigint,
+ ident text NOT NULL,
+ value text
+);
+CREATE INDEX index_client_properties_id ON client_properties (id);
+CREATE INDEX index_client_properties_serverid ON client_properties (server_id);
+
+CREATE TABLE clients (
+ client_id SERIAL PRIMARY KEY NOT NULL,
+ server_id bigint,
+ client_unique_id text,
+ client_nickname text,
+ client_login_name text UNIQUE,
+ client_login_password text,
+ client_lastconnected bigint,
+ client_totalconnections bigint default 0,
+ client_month_upload bigint default 0,
+ client_month_download bigint default 0,
+ client_total_upload bigint default 0,
+ client_total_download bigint default 0,
+ client_lastip text
+);
+CREATE INDEX index_clients_id ON clients (client_id);
+CREATE INDEX index_clients_serverid ON clients (server_id);
+CREATE INDEX index_clients_lastconnectedserverid ON clients (client_lastconnected, server_id);
+CREATE INDEX index_clients_uid ON clients (client_unique_id, server_id);
+
+CREATE TABLE groups_channel (
+ group_id SERIAL PRIMARY KEY NOT NULL,
+ server_id bigint NOT NULL,
+ name text NOT NULL,
+ type integer NOT NULL
+);
+CREATE INDEX index_groups_channel_id ON groups_channel (group_id);
+CREATE INDEX index_groups_channel_serverid ON groups_channel (server_id);
+
+CREATE TABLE groups_server (
+ group_id SERIAL PRIMARY KEY NOT NULL,
+ server_id bigint NOT NULL,
+ name text NOT NULL,
+ type integer NOT NULL
+);
+CREATE INDEX index_groups_server_id ON groups_server (group_id);
+CREATE INDEX index_groups_server_serverid ON groups_server (server_id);
+
+CREATE TABLE group_server_to_client (
+ group_id bigint NOT NULL,
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL
+);
+CREATE INDEX index_group_server_to_client_id ON group_server_to_client (group_id);
+CREATE INDEX index_group_server_to_client_serverid ON group_server_to_client (server_id);
+CREATE INDEX index_group_server_to_client_id1 ON group_server_to_client (id1);
+
+CREATE TABLE group_channel_to_client (
+ group_id bigint NOT NULL,
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL
+);
+CREATE INDEX index_group_channel_to_client_id ON group_channel_to_client (group_id);
+CREATE INDEX index_group_channel_to_client_serverid ON group_channel_to_client (server_id);
+CREATE INDEX index_group_channel_to_client_id1 ON group_channel_to_client (id1);
+CREATE INDEX index_group_channel_to_client_id2 ON group_channel_to_client (id2);
+
+CREATE TABLE perm_channel (
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL,
+ perm_id text NOT NULL,
+ perm_value integer,
+ perm_negated integer,
+ perm_skip integer
+);
+CREATE INDEX index_perm_channel_serverid ON perm_channel (server_id);
+
+CREATE TABLE perm_channel_clients (
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL,
+ perm_id text NOT NULL,
+ perm_value integer,
+ perm_negated integer,
+ perm_skip integer
+);
+CREATE INDEX index_perm_channel_clients_serverid ON perm_channel_clients (server_id);
+
+CREATE TABLE perm_channel_groups (
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL,
+ perm_id text NOT NULL,
+ perm_value integer,
+ perm_negated integer,
+ perm_skip integer
+);
+CREATE INDEX index_perm_channel_groups_serverid ON perm_channel_groups (server_id);
+
+CREATE TABLE perm_client (
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL,
+ perm_id text NOT NULL,
+ perm_value integer,
+ perm_negated integer,
+ perm_skip integer
+);
+CREATE INDEX index_perm_client_serverid ON perm_client (server_id);
+
+CREATE TABLE perm_server_group (
+ server_id bigint NOT NULL,
+ id1 bigint NOT NULL,
+ id2 bigint NOT NULL,
+ perm_id text NOT NULL,
+ perm_value integer,
+ perm_negated integer,
+ perm_skip integer
+);
+CREATE INDEX index_perm_server_group_serverid ON perm_server_group (server_id);
+
+CREATE TABLE bindings (
+ binding_id SERIAL PRIMARY KEY NOT NULL,
+ ip text NOT NULL,
+ type integer
+);
+
+CREATE TABLE server_properties (
+ server_id bigint,
+ id bigint,
+ ident text NOT NULL,
+ value text
+);
+CREATE INDEX index_server_properties_id ON server_properties (id);
+CREATE INDEX index_server_properties_serverid ON server_properties (server_id);
+
+CREATE TABLE servers (
+ server_id SERIAL PRIMARY KEY NOT NULL,
+ server_port bigint,
+ server_autostart bigint,
+ server_machine_id text,
+ server_month_upload bigint Default 0,
+ server_month_download bigint Default 0,
+ server_total_upload bigint Default 0,
+ server_total_download bigint Default 0
+);
+CREATE INDEX index_servers_serverid ON servers (server_id);
+CREATE INDEX index_servers_port ON servers (server_port);
+
+CREATE TABLE tokens (
+ server_id bigint,
+ token_key text NOT NULL,
+ token_type integer,
+ token_id1 bigint,
+ token_id2 bigint,
+ token_created bigint,
+ token_description text,
+ token_customset text
+ );
+
+CREATE TABLE messages (
+ message_id SERIAL PRIMARY KEY NOT NULL,
+ server_id bigint,
+ message_from_client_id bigint,
+ message_from_client_uid text,
+ message_to_client_id bigint,
+ message_subject text,
+ message_msg text,
+ message_timestamp bigint,
+ message_flag_read integer default 0
+);
+CREATE INDEX index_messages_serverid ON messages (server_id);
+CREATE INDEX index_messages_msgidtoclid_read ON messages (message_to_client_id, message_flag_read);
+
+CREATE TABLE complains (
+ server_id bigint,
+ complain_from_client_id bigint,
+ complain_to_client_id bigint,
+ complain_message text,
+ complain_timestamp bigint
+);
+CREATE INDEX index_complains_serverid ON complains (server_id);
+
+CREATE TABLE bans (
+ ban_id SERIAL PRIMARY KEY NOT NULL,
+ server_id bigint,
+ ban_ip text,
+ ban_name text,
+ ban_uid text,
+ ban_timestamp bigint,
+ ban_length bigint,
+ ban_invoker_client_id bigint,
+ ban_invoker_uid text,
+ ban_invoker_name text,
+ ban_reason text,
+ ban_enforcements bigint Default 0
+);
+CREATE INDEX index_bans_serverid ON bans (server_id);
+
+CREATE TABLE instance_properties (
+ server_id bigint,
+ string_id text,
+ id bigint,
+ ident text NOT NULL,
+ value text
+);
+CREATE INDEX index_instance_properties_id ON instance_properties (id);
+CREATE INDEX index_instance_properties_string_id ON instance_properties (string_id);
+CREATE INDEX index_instance_properties_serverid ON instance_properties (server_id);
+
+CREATE TABLE custom_fields (
+ server_id bigint,
+ client_id bigint,
+ ident text NOT NULL,
+ value text
+);
+CREATE INDEX index_custom_fields_serverid ON custom_fields (server_id);
+CREATE INDEX index_custom_fields_client_id ON custom_fields (client_id);
+CREATE INDEX index_custom_fields_ident ON custom_fields (ident);
diff --git a/sql/create_pg/drop_tables.sql b/sql/create_pg/drop_tables.sql
new file mode 100644
index 0000000..0383bb5
--- /dev/null
+++ b/sql/create_pg/drop_tables.sql
@@ -0,0 +1,22 @@
+DROP TABLE IF EXISTS channel_properties;
+DROP TABLE IF EXISTS channels;
+DROP TABLE IF EXISTS client_properties;
+DROP TABLE IF EXISTS clients;
+DROP TABLE IF EXISTS groups_channel;
+DROP TABLE IF EXISTS groups_server;
+DROP TABLE IF EXISTS group_server_to_client;
+DROP TABLE IF EXISTS group_channel_to_client;
+DROP TABLE IF EXISTS perm_channel;
+DROP TABLE IF EXISTS perm_channel_clients;
+DROP TABLE IF EXISTS perm_channel_groups;
+DROP TABLE IF EXISTS perm_client;
+DROP TABLE IF EXISTS perm_server_group;
+DROP TABLE IF EXISTS bindings;
+DROP TABLE IF EXISTS server_properties;
+DROP TABLE IF EXISTS servers;
+DROP TABLE IF EXISTS tokens;
+DROP TABLE IF EXISTS messages;
+DROP TABLE IF EXISTS complains;
+DROP TABLE IF EXISTS bans;
+DROP TABLE IF EXISTS instance_properties;
+DROP TABLE IF EXISTS custom_fields;