path: root/src/sql/sqlite/createTables.sql

--
-- The Mana World Server
-- Copyright 2009 The Mana World Development Team
--
-- This file is part of The Mana World.
--
-- The Mana World  is free software; you can redistribute  it and/or modify it
-- under the terms of the GNU General  Public License as published by the Free
-- Software Foundation; either version 2 of the License, or any later version.
--
-- The Mana  World is  distributed in  the hope  that it  will be  useful, but
-- WITHOUT ANY WARRANTY; without even  the implied warranty of MERCHANTABILITY
-- or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
-- more details.
--
-- You should  have received a  copy of the  GNU General Public  License along
-- with The Mana  World; if not, write to the  Free Software Foundation, Inc.,
-- 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
--

-----------------------------------------------------------------------------
-- Tables
-----------------------------------------------------------------------------

CREATE TABLE tmw_accounts
(
   id            INTEGER     PRIMARY KEY,
   username      TEXT        NOT NULL UNIQUE,
   password      TEXT        NOT NULL,
   email         TEXT        NOT NULL,
   level         INTEGER     NOT NULL,
   banned        INTEGER     NOT NULL,
   registration  INTEGER     NOT NULL,
   lastlogin     INTEGER     NOT NULL,
   authorization TEXT            NULL,
   expiration    INTEGER         NULL
);

CREATE UNIQUE INDEX tmw_accounts_username ON tmw_accounts ( username );
CREATE UNIQUE INDEX tmw_accounts_email    ON tmw_accounts ( email );

-----------------------------------------------------------------------------

CREATE TABLE tmw_characters
(
   id           INTEGER     PRIMARY KEY,
   user_id      INTEGER     NOT NULL,
   name         TEXT        NOT NULL UNIQUE,
   gender       INTEGER     NOT NULL,
   hair_style   INTEGER     NOT NULL,
   hair_color   INTEGER     NOT NULL,
   level        INTEGER     NOT NULL,
   char_pts     INTEGER     NOT NULL,
   correct_pts  INTEGER     NOT NULL,
   money        INTEGER     NOT NULL,
   x            INTEGER     NOT NULL,
   y            INTEGER     NOT NULL,
   map_id       INTEGER     NOT NULL,
   str          INTEGER     NOT NULL,
   agi          INTEGER     NOT NULL,
   dex          INTEGER     NOT NULL,
   vit          INTEGER     NOT NULL,
   int          INTEGER     NOT NULL,
   will         INTEGER     NOT NULL,
   --
   FOREIGN KEY (user_id) REFERENCES tmw_accounts(id)
);

CREATE INDEX tmw_characters_user ON tmw_characters ( user_id );
CREATE UNIQUE INDEX tmw_characters_name ON tmw_characters ( name );

-----------------------------------------------------------------------------

CREATE TABLE tmw_char_skills
(
    char_id     INTEGER     NOT NULL,
    skill_id    INTEGER     NOT NULL,
    skill_exp   INTEGER     NOT NULL,
    --
    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( char_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_char_status_effects
(
    char_id     INTEGER     NOT NULL,
    status_id   INTEGER     NOT NULL,
    status_time INTEGER     NOT NULL,
    --
    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_char_status_char on tmw_char_status_effects ( char_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_items
(
    id           INTEGER    PRIMARY KEY,
    name         TEXT       NOT NULL,
    description  TEXT       NOT NULL,
    image        TEXT       NOT NULL,
    weight       INTEGER    NOT NULL,
    itemtype     TEXT       NOT NULL,
    effect       TEXT,
    dyestring    TEXT
);

CREATE INDEX tmw_items_type ON tmw_items (itemtype);

-----------------------------------------------------------------------------

CREATE TABLE tmw_item_instances
(
    item_id       INTEGER    PRIMARY KEY,
    itemclass_id  INTEGER    NOT NULL,
    amount        INTEGER    NOT NULL,
    --
    FOREIGN KEY (itemclass_id) REFERENCES tmw_items(id)
);

CREATE INDEX tmw_item_instances_typ ON tmw_item_instances ( itemclass_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_item_attributes
(
    attribute_id    INTEGER    PRIMARY KEY,
    item_id         INTEGER    NOT NULL,
    attribute_class INTEGER    NOT NULL,
    attribute_value TEXT,
    --
    FOREIGN KEY (item_id) REFERENCES tmw_item_instances(item_id)
);

CREATE INDEX tmw_item_attributes_item ON tmw_item_attributes ( item_id );

-----------------------------------------------------------------------------

-- todo: remove class_id and amount and reference on tmw_item_instances
CREATE TABLE tmw_inventories
(
   id           INTEGER     PRIMARY KEY,
   owner_id     INTEGER     NOT NULL,
   slot         INTEGER     NOT NULL,
   class_id     INTEGER     NOT NULL,
   amount       INTEGER     NOT NULL,
   --
   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_inventories_owner ON tmw_inventories ( owner_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_guilds
(
   id           INTEGER     PRIMARY KEY,
   name 		TEXT        NOT NULL UNIQUE
);

-----------------------------------------------------------------------------

CREATE TABLE tmw_guild_members
(
   guild_id     INTEGER     NOT NULL,
   member_id    INTEGER     NOT NULL,
   rights       INTEGER     NOT NULL,
   --
   FOREIGN KEY (guild_id)  REFERENCES tmw_guilds(id),
   FOREIGN KEY (member_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id );
CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_quests
(
   owner_id     INTEGER     NOT NULL,
   name         TEXT        NOT NULL,
   value        TEXT        NOT NULL,
   --
   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
);

-----------------------------------------------------------------------------

CREATE TABLE tmw_world_states
(
   state_name   TEXT        PRIMARY KEY,
   map_id       INTEGER     NULL,
   value        TEXT        NULL,
   moddate      INTEGER     NOT NULL
);

-----------------------------------------------------------------------------

CREATE TABLE tmw_auctions
(
   auction_id    INTEGER     PRIMARY KEY,
   auction_state INTEGER     NOT NULL,
   char_id       INTEGER     NOT NULL,
   itemclass_id  INTEGER     NOT NULL,
   amount        INTEGER     NOT NULL,
   start_time    INTEGER     NOT NULL,
   end_time      INTEGER     NOT NULL,
   start_price   INTEGER     NOT NULL,
   min_price     INTEGER,
   buyout_price  INTEGER,
   description   TEXT,
   --
   FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_auctions_owner ON tmw_auctions ( char_id );
CREATE INDEX tmw_auctions_state ON tmw_auctions ( auction_state );
CREATE INDEX tmw_auctions_item  ON tmw_auctions ( itemclass_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_auction_bids
(
   bid_id        INTEGER     PRIMARY KEY,
   auction_id    INTEGER     NOT NULL,
   char_id       INTEGER     NOT NULL,
   bid_time      INTEGER     NOT NULL,
   bid_price     INTEGER     NOT NULL,
   --
   FOREIGN KEY (auction_id) REFERENCES tmw_auctions(auction_id),
   FOREIGN KEY (char_id)    REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_auction_bids_auction ON tmw_auction_bids ( auction_id );
CREATE INDEX tmw_auction_bids_owner   ON tmw_auction_bids ( char_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_post
(
   letter_id        INTEGER   PRIMARY KEY,
   sender_id        INTEGER   NOT NULL,
   receiver_id      INTEGER   NOT NULL,
   letter_type      INTEGER   NOT NULL,
   expiration_date  INTEGER   NOT NULL,
   sending_date     INTEGER   NOT NULL,
   letter_text      TEXT          NULL,
   --
   FOREIGN KEY (sender_id)   REFERENCES tmw_characters(id),
   FOREIGN KEY (receiver_id) REFERENCES tmw_characters(id)
);

CREATE INDEX tmw_post_sender   ON tmw_post ( sender_id );
CREATE INDEX tmw_post_receiver ON tmw_post ( receiver_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_post_attachments
(
   attachment_id    INTEGER   PRIMARY KEY,
   letter_id        INTEGER   NOT NULL,
   item_id          INTEGER   NOT NULL,
   --
   FOREIGN KEY (letter_id) REFERENCES tmw_post(letter_id),
   FOREIGN KEY (item_id)   REFERENCES tmw_item_instances(item_id)
);

CREATE INDEX tmw_post_attachments_ltr ON tmw_post_attachments ( letter_id );
CREATE INDEX tmw_post_attachments_itm ON tmw_post_attachments ( item_id );

-----------------------------------------------------------------------------

CREATE TABLE tmw_transaction_codes
(
   id            INTEGER     PRIMARY KEY,
   description   TEXT        NOT NULL,
   category      TEXT        NOT NULL
);

CREATE INDEX tmw_transaction_codes_cat    ON tmw_transaction_codes ( category );

-----------------------------------------------------------------------------

CREATE TABLE tmw_transactions
(
    id          INTEGER     PRIMARY KEY,
    char_id     INTEGER     NOT NULL,
    action      INTEGER     NOT NULL,
    message     TEXT,
    time        INTEGER     NOT NULL,
    --
    FOREIGN KEY (char_id) REFERENCES tmw_characters(id),
    FOREIGN KEY (action)  REFERENCES tmw_transaction_codes(id)
);

CREATE INDEX tmw_transactions_char    ON tmw_transactions ( char_id );
CREATE INDEX tmw_transactions_action  ON tmw_transactions ( action );
CREATE INDEX tmw_transactions_time    ON tmw_transactions ( time );

-----------------------------------------------------------------------------

CREATE TABLE tmw_online_list
(
    char_id     INTEGER     PRIMARY KEY,
    login_date  INTEGER     NOT NULL,
    --
    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
);

-----------------------------------------------------------------------------
-- Views
-----------------------------------------------------------------------------

-- List all online users

CREATE VIEW tmw_v_online_chars
AS
   SELECT l.char_id    as char_id,
          l.login_date as login_date,
          c.user_id    as user_id,
          c.name       as name,
          c.gender     as gender,
          c.level      as level,
          c.map_id     as map_id
     FROM tmw_online_list l
     JOIN tmw_characters c
       ON l.char_id = c.id;


-- Show all stored transactions

CREATE VIEW tmw_v_transactions
AS
   SELECT t.id           as transaction_id,
          t.time         as transacition_time,
          a.id           as user_id,
          a.username     as username,
          c.id           as char_id,
          c.name         as charname,
          tc.id          as action_id,
          tc.description as action,
          tc.category    as category,
          t.message      as message
     FROM tmw_transactions t
     JOIN tmw_characters c
       ON t.char_id = c.id
     JOIN tmw_accounts a
       ON c.user_id = a.id
     JOIN tmw_transaction_codes tc
       ON t.action = tc.id;

-----------------------------------------------------------------------------
-- Initial data
-----------------------------------------------------------------------------

-- initial world states and database version

INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL, strftime('%s','now'));
INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL, strftime('%s','now'));
INSERT INTO tmw_world_states VALUES('database_version',     NULL,'6',  strftime('%s','now'));

-- all known transaction codes

INSERT INTO tmw_transaction_codes VALUES (  1, 'Character created',        'Character' );
INSERT INTO tmw_transaction_codes VALUES (  2, 'Character selected',       'Character' );
INSERT INTO tmw_transaction_codes VALUES (  3, 'Character deleted',        'Character' );
INSERT INTO tmw_transaction_codes VALUES (  4, 'Public message sent',      'Chat' );
INSERT INTO tmw_transaction_codes VALUES (  5, 'Public message annouced',  'Chat' );
INSERT INTO tmw_transaction_codes VALUES (  6, 'Private message sent',     'Chat' );
INSERT INTO tmw_transaction_codes VALUES (  7, 'Channel joined',           'Chat' );
INSERT INTO tmw_transaction_codes VALUES (  8, 'Channel kicked',           'Chat' );
INSERT INTO tmw_transaction_codes VALUES (  9, 'Channel MODE',             'Chat' );
INSERT INTO tmw_transaction_codes VALUES ( 10, 'Channel QUIT',             'Chat' );
INSERT INTO tmw_transaction_codes VALUES ( 11, 'Channel LIST',             'Chat' );
INSERT INTO tmw_transaction_codes VALUES ( 12, 'Channel USERLIST',         'Chat' );
INSERT INTO tmw_transaction_codes VALUES ( 13, 'Channel TOPIC',            'Chat' );
INSERT INTO tmw_transaction_codes VALUES ( 14, 'Command BAN',              'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 15, 'Command DROP',             'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 16, 'Command ITEM',             'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 17, 'Command MONEY',            'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 18, 'Command SETGROUP',         'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 19, 'Command SPAWN',            'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 20, 'Command WARP',             'Commands' );
INSERT INTO tmw_transaction_codes VALUES ( 21, 'Item picked up',           'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 22, 'Item used',                'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 23, 'Item dropped',             'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 24, 'Item moved',               'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 25, 'Target attacked',          'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 26, 'ACTION Changed',           'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 27, 'Trade requested',          'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 28, 'Trade ended',              'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 29, 'Trade money',              'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 30, 'Trade items',              'Actions' );
INSERT INTO tmw_transaction_codes VALUES ( 31, 'Attribute increased',      'Character' );
INSERT INTO tmw_transaction_codes VALUES ( 32, 'Attribute decreased',      'Character' );