sxcybot

Discord bot for OSRS based channels
git clone git://git.wimdupont.com/sxcybot.git
Log | Files | Refs | README | LICENSE

V1_0__db-init.sql (2444B)


      1 CREATE TABLE rule (
      2     id VARCHAR(36) primary key NOT NULL,
      3     `number` SMALLINT(6) NOT NULL UNIQUE,
      4     description VARCHAR(500) DEFAULT NULL
      5 );
      6 
      7 CREATE TABLE user (
      8     id VARCHAR(36) primary key NOT NULL,
      9     name VARCHAR(100) NOT NULL UNIQUE,
     10     description VARCHAR(1000) DEFAULT NULL,
     11     banned TINYINT(1) DEFAULT 0,
     12     created_date DATE,
     13     created_by VARCHAR(50),
     14     last_modified_date DATE,
     15     last_modified_by VARCHAR(50)
     16 );
     17 
     18 CREATE TABLE poll (
     19     id VARCHAR(36) primary key NOT NULL,
     20     message_id VARCHAR(100) NOT NULL UNIQUE,
     21     created_date DATE,
     22     last_modified_date DATE
     23 );
     24 
     25 CREATE TABLE guild_role (
     26     id VARCHAR(36) primary key NOT NULL,
     27     name VARCHAR(100) NOT NULL UNIQUE,
     28     order_value INT,
     29     elevation INT NOT NULL
     30 );
     31 
     32 CREATE TABLE channel_detail (
     33     id VARCHAR(36) primary key NOT NULL,
     34     forum_url VARCHAR(500),
     35     bot_update_channel VARCHAR(100),
     36     pvm_role_channel VARCHAR(100),
     37     pvm_role_general VARCHAR(100),
     38     pvm_role_raids VARCHAR(100),
     39     pvm_role_wilderness VARCHAR(100)
     40 );
     41 
     42 CREATE TABLE osrs_hiscore_stat (
     43     id VARCHAR(36) primary key NOT NULL,
     44     name VARCHAR(100) NOT NULL,
     45     order_value INT NOT NULL
     46 );
     47 
     48 CREATE TABLE osrs_hiscore_boss (
     49     id VARCHAR(36) primary key NOT NULL,
     50     name VARCHAR(100) NOT NULL,
     51     multiplier DECIMAL DEFAULT 0,
     52     order_value INT NOT NULL,
     53     pvm_role INT DEFAULT 0
     54 );
     55 
     56 CREATE TABLE pvm_kc_snapshot (
     57     id VARCHAR(36) primary key NOT NULL,
     58     created_date DATETIME,
     59     last_modified_date DATETIME
     60 );
     61 
     62 CREATE TABLE pvm_role_user (
     63     id VARCHAR(36) primary key NOT NULL,
     64     discord_name VARCHAR(100) NOT NULL UNIQUE,
     65     rsn VARCHAR(100) NOT NULL UNIQUE,
     66     created_date DATETIME,
     67     created_by VARCHAR(50),
     68     last_modified_date DATETIME,
     69     last_modified_by VARCHAR(50)
     70 );
     71 
     72 CREATE TABLE pvm_user_kc (
     73     id VARCHAR(36) primary key NOT NULL,
     74     pvm_role_user_id VARCHAR(36),
     75     CONSTRAINT fk_pvmroleuser FOREIGN KEY (pvm_role_user_id) REFERENCES pvm_role_user (id),
     76     osrs_hiscore_boss_id VARCHAR(36),
     77     CONSTRAINT fk_osrshiscoreboss FOREIGN KEY (osrs_hiscore_boss_id) REFERENCES osrs_hiscore_boss (id),
     78     pvm_kc_snapshot_id VARCHAR(36),
     79     CONSTRAINT fk_pvmkcsnapshot FOREIGN KEY (pvm_kc_snapshot_id) REFERENCES pvm_kc_snapshot (id),
     80     created_date DATETIME,
     81     last_modified_date DATETIME
     82 );
     83 
     84 CREATE TABLE guild_event_dmer (
     85     id VARCHAR(36) primary key NOT NULL,
     86     name VARCHAR(100) NOT NULL UNIQUE
     87 );