4 files changed, 69 insertions, 30 deletions

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index fc873712..c15c005b 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -179,21 +179,6 @@ DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
 --
--- table: `mana_char_equips`
---
-CREATE TABLE IF NOT EXISTS `mana_char_equips` (
-    `id`               int(10)    unsigned NOT NULL auto_increment,
-    `owner_id`         int(10)    unsigned NOT NULL,
-    `slot_type`        int(10)    unsigned NOT NULL,
-    `item_id`          int(10)    unsigned NOT NULL,
-    `item_instance`    int(10)    unsigned NOT NULL,
-    --
-    PRIMARY KEY (`id`),
-    FOREIGN KEY (`owner_id`) REFERENCES `mana_characters` (`id`)
-) ENGINE=InnoDB
-DEFAULT CHARSET=utf8;
-
---
 -- table: `mana_inventories`
 -- todo: remove class_id and amount and reference on mana_item_instances
 --
@@ -203,6 +188,7 @@ CREATE TABLE IF NOT EXISTS `mana_inventories` (
     `slot`         tinyint(3)   unsigned NOT NULL,
     `class_id`     int(10)      unsigned NOT NULL,
     `amount`       tinyint(3)   unsigned NOT NULL,
+    `equipped`     tinyint(3)   unsigned NOT NULL,
     --
     PRIMARY KEY (`id`),
     UNIQUE KEY `owner_id` (`owner_id`, `slot`),
@@ -420,7 +406,7 @@ AUTO_INCREMENT=0 ;
 
 INSERT INTO mana_world_states VALUES('accountserver_startup',-1,'0', NOW());
 INSERT INTO mana_world_states VALUES('accountserver_version',-1,'0', NOW());
-INSERT INTO mana_world_states VALUES('database_version',     -1,'24', NOW());
+INSERT INTO mana_world_states VALUES('database_version',     -1,'25', NOW());
 
 -- all known transaction codes
 
diff --git a/src/sql/mysql/updates/update_24_to_25.sql b/src/sql/mysql/updates/update_24_to_25.sql
new file mode 100644
index 00000000..8f5ea24e
--- /dev/null
+++ b/src/sql/mysql/updates/update_24_to_25.sql
@@ -0,0 +1,18 @@
+START TRANSACTION;
+
+ALTER TABLE mana_inventories ADD COLUMN equipped tinyint(3) unsigned NOT NULL;
+
+INSERT INTO mana_inventories (owner_id, slot, class_id, amount, equipped)
+SELECT owner_id, (SELECT IF COUNT(slot) = 0 THEN 1 ELSE MAX(slot) + 1 END IF FROM mana_inventories
+        WHERE owner_id=owner_id),
+    item_id, 1, 1 FROM mana_char_equips;
+
+DROP TABLE mana_char_equips;
+
+-- Update database version.
+UPDATE mana_world_states
+    SET value = '25',
+        moddate = UNIX_TIMESTAMP()
+    WHERE state_name = 'database_version';
+
+COMMIT;
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index 9d7d1c2c..95dc5a23 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -173,19 +173,6 @@ CREATE TABLE mana_floor_items
 
 -----------------------------------------------------------------------------
 
-CREATE TABLE mana_char_equips
-(
-    id               INTEGER    PRIMARY KEY,
-    owner_id         INTEGER    NOT NULL,
-    slot_type        INTEGER    NOT NULL,
-    item_id          INTEGER    NOT NULL,
-    item_instance    INTEGER    NOT NULL,
-    --
-    FOREIGN KEY (owner_id) REFERENCES mana_characters(id)
-);
-
------------------------------------------------------------------------------
-
 -- todo: remove class_id and amount and reference on mana_item_instances
 CREATE TABLE mana_inventories
 (
@@ -194,6 +181,7 @@ CREATE TABLE mana_inventories
    slot         INTEGER     NOT NULL,
    class_id     INTEGER     NOT NULL,
    amount       INTEGER     NOT NULL,
+   equipped     INTEGER     NOT NULL,
    --
    FOREIGN KEY (owner_id) REFERENCES mana_characters(id)
 );
@@ -408,7 +396,7 @@ AS
 
 INSERT INTO mana_world_states VALUES('accountserver_startup',-1,'0', strftime('%s','now'));
 INSERT INTO mana_world_states VALUES('accountserver_version',-1,'0', strftime('%s','now'));
-INSERT INTO mana_world_states VALUES('database_version',     -1,'24', strftime('%s','now'));
+INSERT INTO mana_world_states VALUES('database_version',     -1,'25', strftime('%s','now'));
 
 -- all known transaction codes
 
diff --git a/src/sql/sqlite/updates/update_24_to_25.sql b/src/sql/sqlite/updates/update_24_to_25.sql
new file mode 100644
index 00000000..955d9c59
--- /dev/null
+++ b/src/sql/sqlite/updates/update_24_to_25.sql
@@ -0,0 +1,47 @@
+BEGIN;
+
+CREATE TABLE mana_inventories_backup
+(
+   id           INTEGER     PRIMARY KEY,
+   owner_id     INTEGER     NOT NULL,
+   slot         INTEGER     NOT NULL,
+   class_id     INTEGER     NOT NULL,
+   amount       INTEGER     NOT NULL,
+   equipped     INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES mana_characters(id)
+);
+
+INSERT INTO mana_inventories_backup SELECT
+    id, owner_id, slot, class_id, amount, 0 FROM mana_inventories;
+
+DROP TABLE mana_inventories;
+
+CREATE TABLE mana_inventories
+(
+   id           INTEGER     PRIMARY KEY AUTOINCREMENT,
+   owner_id     INTEGER     NOT NULL,
+   slot         INTEGER     NOT NULL,
+   class_id     INTEGER     NOT NULL,
+   amount       INTEGER     NOT NULL,
+   equipped     INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES mana_characters(id)
+);
+
+INSERT INTO mana_inventories SELECT * FROM mana_inventories_backup;
+DROP TABLE mana_inventories_backup;
+
+
+INSERT INTO mana_inventories (owner_id, slot, class_id, amount, equipped)
+SELECT owner_id, (SELECT CASE WHEN COUNT(slot) = 0 THEN 1 ELSE MAX(slot) + 1 END as slot FROM mana_inventories
+        WHERE owner_id=owner_id),
+    item_id, 1, 1 FROM mana_char_equips;
+
+-- Update the database version, and set date of update
+UPDATE mana_world_states
+   SET value      = '25',
+       moddate    = strftime('%s','now')
+   WHERE state_name = 'database_version';
+
+END;