From 104be5479d7c1192a73711b932e87c571e434985 Mon Sep 17 00:00:00 2001
From: Andreas Habel <mail@exceptionfault.de>
Date: Fri, 17 Oct 2008 12:57:33 +0000
Subject: Added SQL table definitions for item auctions.

---
 src/sql/mysql/createTables.sql  |  69 ++++++++++++++++++++++++++++++++++++++++
 src/sql/sqlite/createTables.sql |  48 ++++++++++++++++++++++++++++
 src/sql/sqlite/tmw.db           | Bin 24576 -> 35840 bytes
 3 files changed, 117 insertions(+)

(limited to 'src/sql')

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index 4db80e82..a5172245 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -73,6 +73,25 @@ CREATE TABLE IF NOT EXISTS `tmw_char_skills` (
 ) ENGINE=InnoDB
 DEFAULT CHARSET=utf8;
 
+--
+-- table: `tmw_items`
+--
+CREATE TABLE IF NOT EXISTS `tmw_items` (
+    `id`           int(10)      unsigned NOT NULL auto_increment,
+    `name`         varchar(100)          NOT NULL,
+    `description`  varchar(255)          NOT NULL,
+    `image`        varchar(50)           NOT NULL,
+    `weight`       smallint(5)  unsigned NOT NULL,
+    `itemtype`     varchar(50)           NOT NULL,
+    `effect`       varchar(100)              NULL,
+    `dyestring`    varchar(50)               NULL,
+    --
+    PRIMARY KEY (`id`),
+    KEY `itemtype` (`itemtype`)
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8 
+AUTO_INCREMENT=1 ;
+
 --
 -- table: `tmw_inventories`
 --
@@ -155,3 +174,53 @@ CREATE TABLE IF NOT EXISTS `tmw_quests` (
 		ON DELETE CASCADE
 ) ENGINE=InnoDB
 DEFAULT CHARSET=utf8;
+
+--
+-- table: `tmw_auctions`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_auctions` (
+	`auction_id`    int(10)      unsigned  NOT NULL auto_increment,
+	`auction_state` tinyint(3)   unsigned  NOT NULL,
+	`char_id`       int(10)      unsigned  NOT NULL,
+	`itemclass_id`  int(10)      unsigned  NOT NULL,
+	`amount`        int(10)      unsigned  NOT NULL,
+	`start_time`    int(10)      unsigned  NOT NULL,
+	`end_time`      int(10)      unsigned  NOT NULL,
+	`start_price`   int(10)      unsigned  NOT NULL,
+	`min_price`     int(10)      unsigned      NULL,
+	`buyout_price`  int(10)      unsigned      NULL,
+	`description`   varchar(255)               NULL,
+	--
+	PRIMARY KEY (`auction_id`),
+	KEY (`auction_state`),
+	KEY (`itemclass_id`),
+	KEY (`char_id`),
+	FOREIGN KEY (`char_id`)
+		REFERENCES `tmw_characters` (`id`)
+		ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8
+AUTO_INCREMENT=1 ;
+
+--
+-- table: `tmw_auction_bids`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_auction_bids` (
+	`bid_id`        int(10)      unsigned  NOT NULL auto_increment,
+	`auction_id`    int(10)      unsigned  NOT NULL,
+	`char_id`       int(10)      unsigned  NOT NULL,
+	`bid_time`      int(10)      unsigned  NOT NULL,
+	`bid_price`     int(10)      unsigned  NOT NULL,
+	--
+	PRIMARY KEY (`bid_id`),
+	KEY (`auction_id`),
+	KEY (`char_id`),
+	FOREIGN KEY (`char_id`)
+		REFERENCES `tmw_characters` (`id`)
+		ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8
+AUTO_INCREMENT=1 ;
+
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index ba7fa5e2..8a3e0479 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -52,6 +52,20 @@ CREATE TABLE tmw_char_skills
 
 CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( 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_inventories 
 (
    id           INTEGER     PRIMARY KEY,
@@ -102,4 +116,38 @@ CREATE TABLE tmw_world_states
 INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910);
 INSERT INTO "tmw_world_states" VALUES('accountserver_version',NULL,NULL,1221633910);
 
+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 );
diff --git a/src/sql/sqlite/tmw.db b/src/sql/sqlite/tmw.db
index a9f72284..020e2296 100644
Binary files a/src/sql/sqlite/tmw.db and b/src/sql/sqlite/tmw.db differ
-- 
cgit v1.2.3-70-g09d2