diff options
Diffstat (limited to 'src/sql/sqlite/updates/update_6_to_7.sql')
| -rw-r--r-- | src/sql/sqlite/updates/update_6_to_7.sql | 122 |
1 files changed, 122 insertions, 0 deletions
diff --git a/src/sql/sqlite/updates/update_6_to_7.sql b/src/sql/sqlite/updates/update_6_to_7.sql new file mode 100644 index 0000000..2a8b90e --- /dev/null +++ b/src/sql/sqlite/updates/update_6_to_7.sql @@ -0,0 +1,122 @@ + +-- rename tables to new prefix + +ALTER TABLE tmw_accounts RENAME TO mana_accounts; +ALTER TABLE tmw_characters RENAME TO mana_characters; +ALTER TABLE tmw_char_skills RENAME TO mana_char_skills; +ALTER TABLE tmw_char_status_effects RENAME TO mana_char_status_effects; +ALTER TABLE tmw_items RENAME TO mana_items; +ALTER TABLE tmw_item_instances RENAME TO mana_item_instances; +ALTER TABLE tmw_item_attributes RENAME TO mana_item_attributes; +ALTER TABLE tmw_inventories RENAME TO mana_inventories; +ALTER TABLE tmw_guilds RENAME TO mana_guilds; +ALTER TABLE tmw_guild_members RENAME TO mana_guild_members; +ALTER TABLE tmw_quests RENAME TO mana_quests; +ALTER TABLE tmw_world_states RENAME TO mana_world_states; +ALTER TABLE tmw_auctions RENAME TO mana_auctions; +ALTER TABLE tmw_auction_bids RENAME TO mana_auction_bids; +ALTER TABLE tmw_post RENAME TO mana_post; +ALTER TABLE tmw_post_attachments RENAME TO mana_post_attachments; +ALTER TABLE tmw_transaction_codes RENAME TO mana_transaction_codes; +ALTER TABLE tmw_transactions RENAME TO mana_transactions; +ALTER TABLE tmw_online_list RENAME TO mana_online_list; + +-- rename indexes (apparently have to drop and recreate) + +DROP INDEX tmw_accounts_username; +DROP INDEX tmw_accounts_email; +DROP INDEX tmw_characters_user; +DROP INDEX tmw_characters_name; +DROP INDEX tmw_char_skills_char; +DROP INDEX tmw_char_status_char; +DROP INDEX tmw_items_type; +DROP INDEX tmw_item_instances_typ; +DROP INDEX tmw_item_attributes_item; +DROP INDEX tmw_inventories_owner; +DROP INDEX tmw_guild_members_g; +DROP INDEX tmw_guild_members_m; +DROP INDEX tmw_auctions_owner; +DROP INDEX tmw_auctions_state; +DROP INDEX tmw_auctions_item; +DROP INDEX tmw_auction_bids_auction; +DROP INDEX tmw_auction_bids_owner; +DROP INDEX tmw_post_sender; +DROP INDEX tmw_post_receiver; +DROP INDEX tmw_post_attachments_ltr; +DROP INDEX tmw_post_attachments_itm; +DROP INDEX tmw_transaction_codes_cat; +DROP INDEX tmw_transactions_char; +DROP INDEX tmw_transactions_action; +DROP INDEX tmw_transactions_time; + +CREATE UNIQUE INDEX mana_accounts_username ON mana_accounts ( username ); +CREATE UNIQUE INDEX mana_accounts_email ON mana_accounts ( email ); +CREATE INDEX mana_characters_user ON mana_characters ( user_id ); +CREATE UNIQUE INDEX mana_characters_name ON mana_characters ( name ); +CREATE INDEX mana_char_skills_char ON mana_char_skills ( char_id ); +CREATE INDEX mana_char_status_char on mana_char_status_effects ( char_id ); +CREATE INDEX mana_items_type ON mana_items (itemtype); +CREATE INDEX mana_item_instances_typ ON mana_item_instances ( itemclass_id ); +CREATE INDEX mana_item_attributes_item ON mana_item_attributes ( item_id ); +CREATE INDEX mana_inventories_owner ON mana_inventories ( owner_id ); +CREATE INDEX mana_guild_members_g ON mana_guild_members ( guild_id ); +CREATE INDEX mana_guild_members_m ON mana_guild_members ( member_id ); +CREATE INDEX mana_auctions_owner ON mana_auctions ( char_id ); +CREATE INDEX mana_auctions_state ON mana_auctions ( auction_state ); +CREATE INDEX mana_auctions_item ON mana_auctions ( itemclass_id ); +CREATE INDEX mana_auction_bids_auction ON mana_auction_bids ( auction_id ); +CREATE INDEX mana_auction_bids_owner ON mana_auction_bids ( char_id ); +CREATE INDEX mana_post_sender ON mana_post ( sender_id ); +CREATE INDEX mana_post_receiver ON mana_post ( receiver_id ); +CREATE INDEX mana_post_attachments_ltr ON mana_post_attachments ( letter_id ); +CREATE INDEX mana_post_attachments_itm ON mana_post_attachments ( item_id ); +CREATE INDEX mana_transaction_codes_cat ON mana_transaction_codes ( category ); +CREATE INDEX mana_transactions_char ON mana_transactions ( char_id ); +CREATE INDEX mana_transactions_action ON mana_transactions ( action ); +CREATE INDEX mana_transactions_time ON mana_transactions ( time ); + +-- rename views (have to drop and recreate as well) + +DROP VIEW tmw_v_online_chars; +DROP VIEW tmw_v_transactions; + +CREATE VIEW mana_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 mana_online_list l + JOIN mana_characters c + ON l.char_id = c.id; + +CREATE VIEW mana_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 mana_transactions t + JOIN mana_characters c + ON t.char_id = c.id + JOIN mana_accounts a + ON c.user_id = a.id + JOIN mana_transaction_codes tc + ON t.action = tc.id; + + +-- update the database version, and set date of update +UPDATE mana_world_states + SET value = '7', + moddate = strftime('%s','now') + WHERE state_name = 'database_version'; + |