diff options
Diffstat (limited to 'src/sql')
| -rw-r--r-- | src/sql/mysql/createTables.sql | 37 | ||||
| -rw-r--r-- | src/sql/mysql/updates/update_1_to_2.sql | 33 | ||||
| -rw-r--r-- | src/sql/sqlite/createTables.sql | 30 | ||||
| -rw-r--r-- | src/sql/sqlite/updates/update_1_to_2.sql | 30 |
4 files changed, 121 insertions, 9 deletions
diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql index 9eda05a9..5d412095 100644 --- a/src/sql/mysql/createTables.sql +++ b/src/sql/mysql/createTables.sql @@ -156,9 +156,9 @@ CREATE TABLE IF NOT EXISTS `tmw_world_states` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('database_version', NULL,'1', 1226042339); +INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,UNIX_TIMESTAMP()); +INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,UNIX_TIMESTAMP()); +INSERT INTO tmw_world_states VALUES('database_version', NULL,'2', UNIX_TIMESTAMP()); -- -- table: `tmw_guilds` @@ -286,6 +286,7 @@ AUTO_INCREMENT=1 ; -- -- table: `tmw_post_attachements` -- + CREATE TABLE IF NOT EXISTS `tmw_post_attachments` ( `attachment_id` int(10) unsigned NOT NULL auto_increment, `letter_id` int(10) unsigned NOT NULL, @@ -305,3 +306,33 @@ CREATE TABLE IF NOT EXISTS `tmw_post_attachments` ( DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; +-- +-- table: `tmw_online_list` +-- + +CREATE TABLE IF NOT EXISTS `tmw_online_list` ( + `char_id` int(10) unsigned NOT NULL, + `login_date` int(10) NOT NULL, + -- + PRIMARY KEY (`char_id`), + FOREIGN KEY (`char_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8 ; + +-- create a view to show more details about 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; + +
\ No newline at end of file diff --git a/src/sql/mysql/updates/update_1_to_2.sql b/src/sql/mysql/updates/update_1_to_2.sql new file mode 100644 index 00000000..76ba63df --- /dev/null +++ b/src/sql/mysql/updates/update_1_to_2.sql @@ -0,0 +1,33 @@ + +-- add table tmw_online_list to store online users +CREATE TABLE IF NOT EXISTS `tmw_online_list` ( + `char_id` int(10) unsigned NOT NULL, + `login_date` int(10) NOT NULL, + -- + PRIMARY KEY (`char_id`), + FOREIGN KEY (`char_id`) + REFERENCES `tmw_characters` (`id`) + ON DELETE CASCADE +) ENGINE=InnoDB +DEFAULT CHARSET=utf8 ; + +-- create a view to show more details about 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; + +-- update the database version, and set date of update +UPDATE tmw_world_states + SET value = '2', + moddate = UNIX_TIMESTAMP() + WHERE state_name = 'database_version'; +
\ No newline at end of file diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index d22ba99b..68d4ed58 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -140,9 +140,9 @@ CREATE TABLE tmw_world_states moddate INTEGER NOT NULL ); -INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,1226042339); -INSERT INTO tmw_world_states VALUES('database_version', NULL,'1', 1226042339); +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,'2', strftime('%s','now')); CREATE TABLE tmw_auctions ( @@ -198,9 +198,6 @@ CREATE TABLE tmw_post CREATE INDEX tmw_post_sender ON tmw_post ( sender_id ); CREATE INDEX tmw_post_receiver ON tmw_post ( receiver_id ); --- --- table: `tmw_post_attachements` --- CREATE TABLE tmw_post_attachments ( @@ -215,3 +212,24 @@ CREATE TABLE tmw_post_attachments 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_online_list +( + char_id INTEGER PRIMARY KEY, + login_date INTEGER NOT NULL, + -- + FOREIGN KEY (char_id) REFERENCES tmw_characters(id) +); + +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; diff --git a/src/sql/sqlite/updates/update_1_to_2.sql b/src/sql/sqlite/updates/update_1_to_2.sql new file mode 100644 index 00000000..8187cd34 --- /dev/null +++ b/src/sql/sqlite/updates/update_1_to_2.sql @@ -0,0 +1,30 @@ + +-- add table tmw_online_list to store online users +CREATE TABLE tmw_online_list +( + char_id INTEGER PRIMARY KEY, + login_date INTEGER NOT NULL, + -- + FOREIGN KEY (char_id) REFERENCES tmw_characters(id) +); + +-- create a view to show more details about 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; + +-- update the database version, and set date of update +UPDATE tmw_world_states + SET value = '2', + moddate = strftime('%s','now') + WHERE state_name = 'database_version'; +
\ No newline at end of file |