path: root/sql/create_pg/create_tables.sql

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     NUMERIC(20) default 0,
  client_month_download   NUMERIC(20) default 0,
  client_total_upload     NUMERIC(20) default 0,
  client_total_download   NUMERIC(20) 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   NUMERIC(20) Default 0,
  server_month_download NUMERIC(20) Default 0,
  server_total_upload   NUMERIC(20) Default 0,
  server_total_download NUMERIC(20) 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_hash           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_hash                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);