diff options
Diffstat (limited to 'sql/create_pg/create_tables.sql')
| -rw-r--r-- | sql/create_pg/create_tables.sql | 239 |
1 files changed, 239 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); |