diff options
Diffstat (limited to 'src/sql/sqlite/updates')
| -rw-r--r-- | src/sql/sqlite/updates/update_1_to_2.sql | 30 |
1 files changed, 30 insertions, 0 deletions
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 0000000..8187cd3 --- /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 |