Issue status update for http://drupal.org/node/29082 Post a follow up: http://drupal.org/project/comments/add/29082 Project: Drupal Version: cvs Component: database system Category: tasks Priority: critical Assigned to: Cvbge Reported by: Cvbge Updated by: Cvbge Status: patch (code needs review) Attachment: http://drupal.org/files/issues/drupal-head-db_add_column-29082_1.diff (9.16 KB) Updated patch based on http://drupal.org/node/7582 Following functions are defined: db_add_key(&$ret, $table, $column); db_drop_column(&$ret, $table, $column); db_add_primary_key(&$ret, $table, $column); db_drop_primary_key(&$ret, $table); db_add_column(&$ret, $table, $column, $type, $attributes = array()); db_change_column(&$ret, $table, $column, $column_new, $type, $attributes = array()); drupal_pg_version(); db_change_column() still need some work, the rest should work as documented. Cvbge Previous comments: ------------------------------------------------------------------------ Wed, 17 Aug 2005 21:25:43 +0000 : Cvbge Postgres, since 6.4, has version() function so you can check db version from SQL. This can be used in updates.inc for example for conditional dropping of the column (which is available since 7.3, not 7.4 as stated in updates.inc). I need to check how the version string is returned in different versions before making changes. ------------------------------------------------------------------------ Thu, 18 Aug 2005 10:08:25 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal_pg_version2.diff (1.01 KB) Here's a function returning postgres db version. I'm not sure if it fits in updates.inc, maybe it should be somewhere else. ------------------------------------------------------------------------ Fri, 19 Aug 2005 14:33:32 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal_pg_version_and_other.diff (2.76 KB) Added 2 new functions to add or change column definition. The take care about mysql/pgsql. Code not tested, will test and update old code when I hear that this has a chanse to go in ;) ------------------------------------------------------------------------ Fri, 19 Aug 2005 17:03:39 +0000 : Cvbge Attachment: http://drupal.org/files/issues/updates.inc-pgsql_fix.diff (9.97 KB) Still more work needed. ------------------------------------------------------------------------ Sat, 20 Aug 2005 14:28:13 +0000 : Cvbge Attachment: http://drupal.org/files/issues/screenshot_3.jpg (119.33 KB) With attached patch I could make all updates without errors (my LOCKs patch was needed - http://drupal.org/node/22911). There are still some issues that should be addressed (see the patch). Also mysql update is not tested - please test. There are some problems when updating from 4.6 to cvs. Maybe the update should be made by 4.6 base itself? So I've started with fresh 4.6.3. After changing the base to cvs, patching with the LOCKs patch and simple debug helper patch, on update.php?op=update I get following errors: warning: pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71. Warning: pg_query(): Query failed: ERROR: column "referer" of relation "watchdog" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71 Bad query: Array ( [0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status < 3 LIMIT 1 OFFSET 0 [1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1 [2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [4] => SELECT data, created, headers, expire FROM cache WHERE cid = 'variables' [5] => SELECT COUNT(pid) FROM url_alias [6] => SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 [7] => UPDATE sessions SET uid = 1, cache = 0, hostname = '127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124545039 WHERE sid = 'd5b1aa940eaeab37fe690ce9cc500396' [8] => INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124545039) ) Fatal error: ERROR: column "referer" of relation "watchdog" does not exist query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "cache" of relation "sessions" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124545039) in /var/www/dt/d/includes/database.pgsql.inc on line 94 Warning: Unknown(): A session is active. You cannot change the session module's ini settings at this time. in Unknown on line 0 As we can see the problem is that there is no cache column is session table - that's update_130. Also there's no column 'referer' in watchdog table - that's update_142. So I've commented out update_130 and 142, added the columns by hand in the db and started again from the begining. After that I got another error: warning: pg_query(): Query failed: ERROR: column "access" of relation "users" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71. Bad query: Array ( [0] => SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 'd5b1aa940eaeab37fe690ce9cc500396' AND u.status < 3 LIMIT 1 OFFSET 0 [1] => SELECT r.rid, r.name FROM role r INNER JOIN users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1 [2] => SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [3] => SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) [4] => SELECT data, created, headers, expire FROM cache WHERE cid = 'variables' [5] => SELECT COUNT(pid) FROM url_alias [6] => SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 [7] => UPDATE sessions SET uid = 1, cache = 0, hostname = '127.0.0.1', session = 'messages|a:0:{}', timestamp = 1124547147 WHERE sid = 'd5b1aa940eaeab37fe690ce9cc500396' [8] => UPDATE users SET access = 1124547147 WHERE uid = 1 [9] => INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'pg_query(): Query failed: ERROR: column "access" of relation "users" does not exist in /var/www/dt/d/includes/database.pgsql.inc on line 71.', 2, '', '/dt/d/update.php?op=update', 'http://localhost/dt/d/update.php', '127.0.0.1', 1124547147) ) user error: query: UPDATE users SET access = 1124547147 WHERE uid = 1 in /var/www/dt/d/includes/database.pgsql.inc on line 94. That's update_136. So, disable update_136 and do it by hand and start again. After all that, when accessing update.php?op=update I noticed no errors. After pressing Update all updates were applied without any errors. But when accessed main page I got a screen as on attached screenshot. ------------------------------------------------------------------------ Sat, 20 Aug 2005 14:32:27 +0000 : Cvbge Attachment: http://drupal.org/files/issues/updates.inc-pgsql_fix4.diff (12.86 KB) And the mentioned patch. Critical because otherwise upgrades (at least for postgres) are impossibile. ------------------------------------------------------------------------ Sat, 20 Aug 2005 14:36:40 +0000 : Cvbge I need people to test mysql updates, as I changed mysql part too. ------------------------------------------------------------------------ Fri, 26 Aug 2005 11:40:59 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal-head-db_add_column.2.diff (7.44 KB) A diffrent patch. Only adds 3 functions for adding and changing table columns, does not modifies any old updates. There are still some issues to be solved, for example: - when changing a column in mysql the PRIMARY KEY attribute is retained. In Postgres, due to the nature of changing process (i.e. dropping the column) this attribute is dropped. How to workaround it? Need to check other attributes too (e.g. NOT NULL, index etc). - add more attributes? Like primary key, key (mysql: synonim dla unique), unique, index, auto increment (mysql: must be indexed and must have a default) ? - in CHANGE when setting NOT NOLL, and DEFAULT is set, update the table to default WHERE column IS NULL ?