From af789040b8880f908df0a26d5239d07d77c34124 Mon Sep 17 00:00:00 2001 From: Chris Smith Date: Wed, 7 Jul 2010 23:23:11 +0100 Subject: [feature/new-tz-handling] Modify database schemas. - Dropped the user_dst column which is no longer required. - Modified the user_timezone column to take a string, max length 100. PHPBB3-9558 --- phpBB/install/schemas/firebird_schema.sql | 3 +-- phpBB/install/schemas/mssql_schema.sql | 3 +-- phpBB/install/schemas/mysql_40_schema.sql | 3 +-- phpBB/install/schemas/mysql_41_schema.sql | 3 +-- phpBB/install/schemas/oracle_schema.sql | 3 +-- phpBB/install/schemas/postgres_schema.sql | 3 +-- phpBB/install/schemas/sqlite_schema.sql | 3 +-- 7 files changed, 7 insertions(+), 14 deletions(-) (limited to 'phpBB/install') diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql index daeba45864..f4b1c624d7 100644 --- a/phpBB/install/schemas/firebird_schema.sql +++ b/phpBB/install/schemas/firebird_schema.sql @@ -1290,8 +1290,7 @@ CREATE TABLE phpbb_users ( user_inactive_time INTEGER DEFAULT 0 NOT NULL, user_posts INTEGER DEFAULT 0 NOT NULL, user_lang VARCHAR(30) CHARACTER SET NONE DEFAULT '' NOT NULL, - user_timezone DOUBLE PRECISION DEFAULT 0 NOT NULL, - user_dst INTEGER DEFAULT 0 NOT NULL, + user_timezone VARCHAR(100) CHARACTER SET NONE DEFAULT 'UTC' NOT NULL, user_dateformat VARCHAR(30) CHARACTER SET UTF8 DEFAULT 'd M Y H:i' NOT NULL COLLATE UNICODE, user_style INTEGER DEFAULT 0 NOT NULL, user_rank INTEGER DEFAULT 0 NOT NULL, diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql index 736917fdcb..fc1afcbbff 100644 --- a/phpBB/install/schemas/mssql_schema.sql +++ b/phpBB/install/schemas/mssql_schema.sql @@ -1576,8 +1576,7 @@ CREATE TABLE [phpbb_users] ( [user_inactive_time] [int] DEFAULT (0) NOT NULL , [user_posts] [int] DEFAULT (0) NOT NULL , [user_lang] [varchar] (30) DEFAULT ('') NOT NULL , - [user_timezone] [float] DEFAULT (0) NOT NULL , - [user_dst] [int] DEFAULT (0) NOT NULL , + [user_timezone] [varchar] (100) DEFAULT ('UTC') NOT NULL , [user_dateformat] [varchar] (30) DEFAULT ('d M Y H:i') NOT NULL , [user_style] [int] DEFAULT (0) NOT NULL , [user_rank] [int] DEFAULT (0) NOT NULL , diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql index 97c378621b..76b9294987 100644 --- a/phpBB/install/schemas/mysql_40_schema.sql +++ b/phpBB/install/schemas/mysql_40_schema.sql @@ -914,8 +914,7 @@ CREATE TABLE phpbb_users ( user_inactive_time int(11) UNSIGNED DEFAULT '0' NOT NULL, user_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, user_lang varbinary(30) DEFAULT '' NOT NULL, - user_timezone decimal(5,2) DEFAULT '0' NOT NULL, - user_dst tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, + user_timezone varbinary(100) DEFAULT 'UTC' NOT NULL, user_dateformat varbinary(90) DEFAULT 'd M Y H:i' NOT NULL, user_style mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, user_rank mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql index 9615905625..30fb7a8623 100644 --- a/phpBB/install/schemas/mysql_41_schema.sql +++ b/phpBB/install/schemas/mysql_41_schema.sql @@ -914,8 +914,7 @@ CREATE TABLE phpbb_users ( user_inactive_time int(11) UNSIGNED DEFAULT '0' NOT NULL, user_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, user_lang varchar(30) DEFAULT '' NOT NULL, - user_timezone decimal(5,2) DEFAULT '0' NOT NULL, - user_dst tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, + user_timezone varchar(100) DEFAULT 'UTC' NOT NULL, user_dateformat varchar(30) DEFAULT 'd M Y H:i' NOT NULL, user_style mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, user_rank mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql index 5d60d2a19e..a4b8686948 100644 --- a/phpBB/install/schemas/oracle_schema.sql +++ b/phpBB/install/schemas/oracle_schema.sql @@ -1702,8 +1702,7 @@ CREATE TABLE phpbb_users ( user_inactive_time number(11) DEFAULT '0' NOT NULL, user_posts number(8) DEFAULT '0' NOT NULL, user_lang varchar2(30) DEFAULT '' , - user_timezone number(5, 2) DEFAULT '0' NOT NULL, - user_dst number(1) DEFAULT '0' NOT NULL, + user_timezone varchar2(100) DEFAULT 'UTC' NOT NULL, user_dateformat varchar2(90) DEFAULT 'd M Y H:i' NOT NULL, user_style number(8) DEFAULT '0' NOT NULL, user_rank number(8) DEFAULT '0' NOT NULL, diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql index d7377ac2e6..7138c818ff 100644 --- a/phpBB/install/schemas/postgres_schema.sql +++ b/phpBB/install/schemas/postgres_schema.sql @@ -1172,8 +1172,7 @@ CREATE TABLE phpbb_users ( user_inactive_time INT4 DEFAULT '0' NOT NULL CHECK (user_inactive_time >= 0), user_posts INT4 DEFAULT '0' NOT NULL CHECK (user_posts >= 0), user_lang varchar(30) DEFAULT '' NOT NULL, - user_timezone decimal(5,2) DEFAULT '0' NOT NULL, - user_dst INT2 DEFAULT '0' NOT NULL CHECK (user_dst >= 0), + user_timezone varchar(100) DEFAULT 'UTC' NOT NULL, user_dateformat varchar(30) DEFAULT 'd M Y H:i' NOT NULL, user_style INT4 DEFAULT '0' NOT NULL CHECK (user_style >= 0), user_rank INT4 DEFAULT '0' NOT NULL CHECK (user_rank >= 0), diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql index 257937275c..5066ac97df 100644 --- a/phpBB/install/schemas/sqlite_schema.sql +++ b/phpBB/install/schemas/sqlite_schema.sql @@ -886,8 +886,7 @@ CREATE TABLE phpbb_users ( user_inactive_time INTEGER UNSIGNED NOT NULL DEFAULT '0', user_posts INTEGER UNSIGNED NOT NULL DEFAULT '0', user_lang varchar(30) NOT NULL DEFAULT '', - user_timezone decimal(5,2) NOT NULL DEFAULT '0', - user_dst INTEGER UNSIGNED NOT NULL DEFAULT '0', + user_timezone varchar(100) NOT NULL DEFAULT 'UTC', user_dateformat varchar(30) NOT NULL DEFAULT 'd M Y H:i', user_style INTEGER UNSIGNED NOT NULL DEFAULT '0', user_rank INTEGER UNSIGNED NOT NULL DEFAULT '0', -- cgit v1.2.1 From b672fc7ae113c0e01f1d7ce4ffae3eb26e57b586 Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Tue, 26 Apr 2011 22:11:45 -0400 Subject: [feature/new-tz-handling] Started on database updater changes. The changes are tricky since we need to drop user_dst column from users table, but we need it during the data migration process to properly calculate effective timezones for all users. The change here converts user_timezone to vchar and computes timezone identifiers from the offsets. It uses database-specific functions for building SQL conditionals and concatenations which need to be implemented, probably in a separate ticket. As a result the current code is not functional. PHPBB3-9558 --- phpBB/install/database_update.php | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'phpBB/install') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index fa0fba9dc7..9d550cf938 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1090,6 +1090,9 @@ function database_update_info() GROUPS_TABLE => array( 'group_legend' => array('UINT', 0), ), + USERS_TABLE => array( + 'user_timezone' => array('VCHAR:100', ''), + ), ), 'drop_columns' => array( STYLES_TABLE => array( @@ -2352,6 +2355,25 @@ function change_database_data(&$no_updates, $version) set_config('teampage_memberships', '1'); } + // Update timezones + // user_dst is 0 if not in dst and 1 if in dst; + // this happens to be exactly the correction that should be added to the timezone offset + // to obtain dst offset. + // Parenthesize here because we operate on this value later. + $active_offset = '(user_timezone + user_dst)'; + // Now we have a tricky problem of forcing the plus sign into the expression. + // Build it via a conditional since there cannot be a portable printf equivalent in databases. + // Note that active offset is not an absolute value here - it is an expression that will + // be evaluated by the database during query execution. + // We don't print - here because it will come from active offset. + $sign = $db->conditional_sql("$active_offset < 0", '', '+'); + // Use database-specific escaping because strings are quoted differently by different databases. + $new_value = $db->concatenate_sql($db->sql_escape('GMT'), $sign, $active_offset); + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_timezone = ' . $new_value; + _sql($sql, $errored, $error_ary); + // After we have calculated the timezones we can delete user_dst column from user table. + $no_updates = false; break; } -- cgit v1.2.1