diff options
Diffstat (limited to 'src/sql/sqlite')
| -rw-r--r-- | src/sql/sqlite/createTables.sql | 16 | ||||
| -rw-r--r-- | src/sql/sqlite/updates/update_24_to_25.sql | 47 |
2 files changed, 49 insertions, 14 deletions
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; |