Tigase Database Minor but Useful Schema Change in Version 5.1.0

Artur Hefczyc <artur.hefczyc@tigase.net> v2.0, June 2014: Reformatted for AsciiDoc. :toc: :numbered: :website: http://tigase.net :Date: 2012-06-05 02:38

We have recently made a simple but very useful change to the DB schema in Tigase. It does preserve backward compatibility and it is not required to change your existing schema, even if you upgrade your installation to the most recent 5.1.0 version.

However, the change is very useful to track new accounts creation, therefore this article shows how to make modifications to you schema manually.

The change is related to adding a new field: acc_create_time which stores exact time of user account creation. The time is recorded automatically by database when a new record in the user table is created. So there is no extra overhead or resource usage on the Tigase server.

While the new field was added I have also slightly modifies types of 2 other fields: last_login and last_logout.

Here is how it looks now:

-- Time the account has been created
acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP,
-- Time of the last user login
last_login timestamp DEFAULT 0,
-- Time of the last user logout
last_logout timestamp DEFAULT 0,

You can easily update your schema with above changes using a simple SQL query. Enter the MySQL shell and copy-paste following query:

alter table tig_users
  modify last_login timestamp DEFAULT 0,
  modify last_logout timestamp DEFAULT 0,
  add acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP;

Please note, after executing query above the column acc_create_time will have meaningless value. To put some meaning for existing records I just copy last_login value to acc_create_time:

update tig_users set acc_create_time = last_login;