- Patch #7498 by nereocystis: PostgreSQL updates for HEAD

2 files changed, 300 insertions, 131 deletions

diff --git a/database/database.pgsql b/database/database.pgsql
index 58649530c..317324bd8 100644
--- a/database/database.pgsql
+++ b/database/database.pgsql
@@ -86,18 +86,76 @@ CREATE TABLE boxes (
 );
 
 --
--- Table structure for bundle
+-- Table structure for table 'aggregator_category'
 --
 
-CREATE TABLE bundle (
-  bid SERIAL,
+CREATE TABLE aggregator_category (
+  cid serial,
   title varchar(255) NOT NULL default '',
-  attributes varchar(255) NOT NULL default '',
-  PRIMARY KEY  (bid),
+  description text NOT NULL,
+  block smallint NOT NULL default '0',
+  PRIMARY KEY  (cid),
+  UNIQUE (title)
+);
+
+--
+-- Table structure for table 'aggregator_category_feed'
+--
+
+CREATE TABLE aggregator_category_feed (
+  fid integer NOT NULL default '0',
+  cid integer NOT NULL default '0',
+  PRIMARY KEY  (fid,cid)
+);
+
+--
+-- Table structure for table 'aggregator_category_item'
+--
+
+CREATE TABLE aggregator_category_item (
+  iid integer NOT NULL default '0',
+  cid integer NOT NULL default '0',
+  PRIMARY KEY  (iid,cid)
+);
+
+--
+-- Table structure for table 'aggregator_feed'
+--
+
+CREATE TABLE aggregator_feed (
+  fid serial,
+  title varchar(255) NOT NULL default '',
+  url varchar(255) NOT NULL default '',
+  refresh integer NOT NULL default '0',
+  checked integer NOT NULL default '0',
+  link varchar(255) NOT NULL default '',
+  description text NOT NULL,
+  image text NOT NULL,
+  etag varchar(255) NOT NULL default '',
+  modified integer NOT NULL default '0',
+  block smallint NOT NULL default '0',
+  PRIMARY KEY  (fid),
+  UNIQUE (url),
   UNIQUE (title)
 );
 
 --
+-- Table structure for table 'aggregator_item'
+--
+
+CREATE TABLE aggregator_item (
+  iid integer NOT NULL auto_increment,
+  fid integer NOT NULL default '0',
+  title varchar(255) NOT NULL default '',
+  link varchar(255) NOT NULL default '',
+  author varchar(255) NOT NULL default '',
+  description text NOT NULL,
+  timestamp integer default NULL,
+  PRIMARY KEY  (iid)
+);
+
+
+--
 -- Table structure for cache
 --
 
@@ -146,27 +204,6 @@ CREATE TABLE directory (
 );
 
 --
--- Table structure for feed
---
-
-CREATE TABLE feed (
-  fid SERIAL,
-  title varchar(255) NOT NULL default '',
-  url varchar(255) NOT NULL default '',
-  refresh integer NOT NULL default '0',
-  checked integer NOT NULL default '0',
-  attributes varchar(255) NOT NULL default '',
-  link varchar(255) NOT NULL default '',
-  description text NOT NULL default '',
-  image text NOT NULL default '',
-  etag varchar(255) NOT NULL default '',
-  modified integer NOT NULL default '0',
-  PRIMARY KEY  (fid),
-  UNIQUE (title),
-  UNIQUE (url)
-);
-
---
 -- Table structure for table 'filters'
 --
 
@@ -200,22 +237,6 @@ CREATE TABLE history (
 );
 
 --
--- Table structure for item
---
-
-CREATE TABLE item (
-  iid SERIAL,
-  fid integer NOT NULL default '0',
-  title varchar(255) NOT NULL default '',
-  link varchar(255) NOT NULL default '',
-  author varchar(255) NOT NULL default '',
-  description text NOT NULL default '',
-  timestamp integer NOT NULL default '0',
-  attributes varchar(255) NOT NULL default '',
-  PRIMARY KEY  (iid)
-);
-
---
 -- Table structure for locales
 --
 
@@ -235,6 +256,21 @@ CREATE TABLE locales (
 );
 
 --
+-- Table structure for table 'menu'
+--
+
+CREATE TABLE menu (
+  mid integer NOT NULL default '0',
+  pid integer NOT NULL default '0',
+  path varchar(255) NOT NULL default '',
+  title varchar(255) NOT NULL default '',
+  weight smallint NOT NULL default '0',
+  visibility smallint NOT NULL default '0',
+  status smallint NOT NULL default '0',
+  PRIMARY KEY  (mid)
+);
+
+--
 -- Table structure for table 'moderation_filters'
 --
 
@@ -331,6 +367,35 @@ CREATE INDEX page_nid_idx ON page(nid);
 -- Table structure for table 'url_alias'
 --
 
+CREATE TABLE profile_fields (
+  fid serial,
+  title varchar(255) default NULL,
+  name varchar(128) default NULL,
+  explanation TEXT default NULL,
+  category varchar(255) default NULL,
+  page varchar(255) default NULL,
+  type varchar(128) default NULL,
+  weight smallint DEFAULT '0' NOT NULL,
+  required smallint DEFAULT '0' NOT NULL,
+  overview smallint DEFAULT '0' NOT NULL,
+  options text,
+  UNIQUE (name),
+  PRIMARY KEY (fid)
+);
+CREATE INDEX profile_fields_category ON profile_fields (category);
+
+--
+-- Table structure for table 'profile_values'
+--
+
+CREATE TABLE profile_values (
+  fid integer default '0',
+  uid integer default '0',
+  value text
+);
+CREATE INDEX profile_values_uid ON profile_values (uid);
+CREATE INDEX profile_values_fid ON profile_values (fid);
+
 CREATE TABLE url_alias (
   pid serial,
   dst varchar(128) NOT NULL default '',
diff --git a/database/updates.inc b/database/updates.inc
index e19ed3813..b96d29863 100644
--- a/database/updates.inc
+++ b/database/updates.inc
@@ -772,42 +772,17 @@ function update_79() {
 
 function update_80() {
 
-  // Add a 'created' field to the users table:
-  $ret[] = update_sql('ALTER TABLE {users} ADD created INT(11) NOT NULL');
-  $ret[] = update_sql('ALTER TABLE {users} CHANGE timestamp changed INT(11) NOT NULL');
 
-  // Add some indices to speed up the update process:
-  $ret[] = update_sql('ALTER TABLE {comments} ADD index (timestamp)');
-  $ret[] = update_sql('ALTER TABLE {node} ADD index (created)');
-
-  // Assign everyone a created timestamp to begin with:
-  $ret[] = update_sql("UPDATE {users} SET created = changed WHERE created = ''");
-
-  // Print a status message:"
-  print '<p>Note: this might take a while ...</p>';
-
-  // Try updating the user records using the comment table:
-  $result = db_query('SELECT DISTINCT(u.uid) FROM {comments} c LEFT JOIN {users} u ON c.uid = u.uid WHERE c.timestamp < u.created');
-  while ($account = db_fetch_object($result)) {
-    // Retrieve the proper timestamp:
-    $timestamp = db_result(db_query('SELECT MIN(timestamp) FROM {comments} WHERE uid = %d', $account->uid));
 
-    // Update this user record as well as older records with an older timestamp:
-    db_query('UPDATE {users} SET created = %d WHERE created > %d AND uid <= %d', $timestamp, $timestamp, $account->uid);
-  }
+  if ($GLOBALS["db_type"] == "mysql") {
+    // Add a 'created' field to the users table:
+    $ret[] = update_sql('ALTER TABLE {users} ADD created INT(11) NOT NULL');
+    $ret[] = update_sql('ALTER TABLE {users} CHANGE timestamp changed INT(11) NOT NULL');
 
-  // Try updating the user records using the node table:
-  $result = db_query('SELECT DISTINCT(u.uid) FROM {node} n LEFT JOIN {users} u ON n.uid = u.uid WHERE n.created < u.created');
-  while ($account = db_fetch_object($result)) {
-    // Retrieve the proper timestamp:
-    $timestamp = db_result(db_query('SELECT MIN(created) FROM {node} WHERE uid = %d', $account->uid));
 
-    // Update this user record as well as older records with an older timestamp:
-    db_query('UPDATE {users} SET created = %d WHERE created > %d AND uid <= %d', $timestamp, $timestamp, $account->uid);
-  }
 
-  // Add profile module related tables:
-  $ret[] = update_sql("CREATE TABLE {profile_fields} (
+    // Add profile module related tables:
+    $ret[] = update_sql("CREATE TABLE {profile_fields} (
     fid int(10) NOT NULL auto_increment,
     title varchar(255) default NULL,
     name varchar(128) default NULL,
@@ -822,84 +797,175 @@ function update_80() {
     PRIMARY KEY (fid)
    );");
 
-  $ret[] = update_sql("CREATE TABLE {profile_values} (
+    $ret[] = update_sql("CREATE TABLE {profile_values} (
     fid int(11) unsigned default '0',
     uid int(11) unsigned default '0',
     value text,
     KEY uid (uid),
     KEY fid (fid)
    );");
+    // Add some indices to speed up the update process:
+    $ret[] = update_sql('ALTER TABLE {comments} ADD index (timestamp)');
+    $ret[] = update_sql('ALTER TABLE {node} ADD index (created)');
+    // Assign everyone a created timestamp to begin with:
+    $ret[] = update_sql("UPDATE {users} SET created = changed WHERE created = ''");
+
+   }
+  else {// pgsql
+
+    $ret[] = update_sql('ALTER TABLE {users} RENAME timestamp TO changed');
+    $ret[] = update_sql('ALTER TABLE {users} ADD created integer');
+    $ret[] = update_sql('UPDATE {users} SET created = 0');
+    $ret[] = update_sql('ALTER TABLE {users} ALTER COLUMN created SET NOT NULL');
+    $ret[] = update_sql('ALTER TABLE {users} ALTER COLUMN created SET DEFAULT 0');
+    // Add profile module related tables:
+    $ret[] = update_sql("CREATE TABLE {profile_fields} (
+    fid serial,
+    title varchar(255) default NULL,
+    name varchar(128) default NULL,
+    explanation TEXT default NULL,
+    category varchar(255) default NULL,
+    type varchar(128) default NULL,
+    weight smallint DEFAULT '0' NOT NULL,
+    overview smallint DEFAULT '0' NOT NULL,
+    options text,
+    UNIQUE (name),
+    PRIMARY KEY (fid)
+  );");
+    $ret[] = update_sql("CREATE INDEX profile_fields_category ON {profile_fields} (category);");
+    $ret[] = update_sql("CREATE TABLE {profile_values} (
+    fid integer default '0',
+    uid integer default '0',
+    value text
+  );");
+    $ret[] = update_sql("CREATE INDEX profile_values_uid ON profile_values (uid);");
+    $ret[] = update_sql("CREATE INDEX profile_values_fid ON profile_values (fid);");
+
+  // Add some indices to speed up the update process:
+    $ret[] = update_sql('CREATE INDEX comments_timestamp ON {comments} (timestamp)');
+    $ret[] = update_sql('CREATE INDEX node_created on {node} (created)');
+    // Assign everyone a created timestamp to begin with:
+    $ret[] = update_sql("UPDATE {users} SET created = changed WHERE created = 0");
+
+  }
 
-  // Migrate the old profile data to the new scheme:
-  $fields = array(
-    array("Name", "realname", "textfield", NULL, 0),
-    array("Address", "address", "textfield", NULL, 0),
-    array("City", "city", "textfield", NULL, 0),
-    array("State, province or region", "state", "textfield", NULL, 0),
-    array("Zip or postal code", "zip", "textfield", NULL, 0),
-    array("Country", "country", "textfield", NULL, 1),
-    array("Gender", "gender", "selection", "male\nfemale", 1),
-    array("Job title", "job", "textfield", NULL, 0),
-    array("ICQ messenger ID", "icq", "textfield", NULL, 0),
-    array("MSN messenger ID", "msn", "textfield", NULL, 0),
-    array("Yahoo messenger ID", "yahoo", "textfield", NULL, 0),
-    array("AIM messenger ID", "aim", "textfield", NULL, 0),
-    array("URL of homepage", "homepage", "url", NULL, 1),
-    array("Biography", "biography", "textarea", NULL, 0),
-    array("Interests", "interests", "textarea", NULL, 0),
-    array("Public key", "publickey", "textarea", NULL, 0)
-  );
-
-  // Remove existing data (debug mode):
-  db_query('DELETE FROM {profile_fields}');
-  db_query('DELETE FROM {profile_values}');
-
-  foreach ($fields as $field) {
-    db_query("INSERT INTO {profile_fields} (title, name, type, category, options, overview) VALUES ('%s', '%s', '%s', 'Personal information', '%s', %d)", $field[0], $field[1], $field[2], $field[3], $field[4]);
-  }
-  db_query("ALTER TABLE {users} ADD picture varchar(255) NOT NULL DEFAULT ''");
-
-  $result = db_query("SELECT uid FROM {users}");
+
+  // Print a status message:"
+  print '<p>Note: this might take a while ...</p>';
+
+  // Try updating the user records using the comment table:
+  $result = db_query('SELECT DISTINCT(u.uid) FROM {comments} c LEFT JOIN {users} u ON c.uid = u.uid WHERE c.timestamp < u.created');
   while ($account = db_fetch_object($result)) {
-    // Load the user record:
-    $account = user_load(array('uid' => $account->uid));
-    $edit = array();
+    // Retrieve the proper timestamp:
+    $timestamp = db_result(db_query('SELECT MIN(timestamp) FROM {comments} WHERE uid = %d', $account->uid));
+    // Try updating the user records using the node table:
+    $result = db_query('SELECT DISTINCT(u.uid) FROM {node} n LEFT JOIN {users} u ON n.uid = u.uid WHERE n.created < u.created');
+    while ($account = db_fetch_object($result)) {
+      // Retrieve the proper timestamp:
+      $timestamp = db_result(db_query('SELECT MIN(created) FROM {node} WHERE uid = %d', $account->uid));
+
+      // Update this user record as well as older records with an older timestamp:
+      db_query('UPDATE {users} SET created = %d WHERE created > %d AND uid <= %d', $timestamp, $timestamp, $account->uid);
+    }
 
-    // Modify the user record:
-    foreach ($fields as $field) {
-      $old = "profile_". $field[1];
-      $new = $field[1];
-      if ($account->$old) {
-        $edit[$new] = $account->$old;
-      }
-      unset($account->$old);
+      // Update this user record as well as older records with an older timestamp:
+      db_query('UPDATE {users} SET created = %d WHERE created > %d AND uid <= %d', $timestamp, $timestamp, $account->uid);
     }
 
-    // Gender specific changes:
-    if ($edit['gender'] == 'f') $edit['gender'] = 'female';
-    if ($edit['gender'] == 'm') $edit['gender'] = 'male';
 
-    // Avatar specific changes:
-    if ($account->profile_avatar) {
-      $edit['picture'] = $account->profile_avatar;
+   // Migrate the old profile data to the new scheme:
+    $fields = array(
+        array("Name", "realname", "textfield", NULL, 0),
+        array("Address", "address", "textfield", NULL, 0),
+        array("City", "city", "textfield", NULL, 0),
+        array("State, province or region", "state", "textfield", NULL, 0),
+        array("Zip or postal code", "zip", "textfield", NULL, 0),
+        array("Country", "country", "textfield", NULL, 1),
+        array("Gender", "gender", "selection", "male\nfemale", 1),
+        array("Job title", "job", "textfield", NULL, 0),
+        array("ICQ messenger ID", "icq", "textfield", NULL, 0),
+        array("MSN messenger ID", "msn", "textfield", NULL, 0),
+        array("Yahoo messenger ID", "yahoo", "textfield", NULL, 0),
+        array("AIM messenger ID", "aim", "textfield", NULL, 0),
+        array("URL of homepage", "homepage", "url", NULL, 1),
+        array("Biography", "biography", "textarea", NULL, 0),
+        array("Interests", "interests", "textarea", NULL, 0),
+        array("Public key", "publickey", "textarea", NULL, 0)
+        );
+
+    // Remove existing data (debug mode):
+    db_query('DELETE FROM {profile_fields}');
+    db_query('DELETE FROM {profile_values}');
+
+    foreach ($fields as $field) {
+      db_query("INSERT INTO {profile_fields} (title, name, type, category, options, overview) VALUES ('%s', '%s', '%s', 'Personal information', '%s', %d)", $field[0], $field[1], $field[2], $field[3], $field[4]);
+    }
+    if ($GLOBALS["db_type"] == "mysql") {
+
+      db_query("ALTER TABLE {users} ADD picture varchar(255) NOT NULL DEFAULT ''");
+    }
+    else {
+      db_query("ALTER TABLE {users} ADD picture varchar(255)");
+      db_query("UPDATE {users} SET picture = ''");
+      db_query("ALTER TABLE {users} ALTER COLUMN picture SET NOT NULL");
+      db_query("ALTER TABLE {users} ALTER COLUMN picture SET DEFAULT ''");
     }
-    unset($account->profile_avatar);
 
-    // Save the update record:
-    user_save($account, $edit);
+    $result = db_query("SELECT uid FROM {users}");
+    while ($account = db_fetch_object($result)) {
+      // Load the user record:
+      $account = user_load(array('uid' => $account->uid));
+      $edit = array();
+
+      // Modify the user record:
+      foreach ($fields as $field) {
+  $old = "profile_". $field[1];
+  $new = $field[1];
+  if ($account->$old) {
+    $edit[$new] = $account->$old;
   }
+  unset($account->$old);
+      }
+
+      // Gender specific changes:
+      if ($edit['gender'] == 'f') $edit['gender'] = 'female';
+      if ($edit['gender'] == 'm') $edit['gender'] = 'male';
+
+      // Avatar specific changes:
+      if ($account->profile_avatar) {
+  $edit['picture'] = $account->profile_avatar;
+      }
+      unset($account->profile_avatar);
+
+      // Save the update record:
+      user_save($account, $edit);
+    }
 
   return $ret;
 }
 
 function update_81() {
-  $ret[] = update_sql('ALTER TABLE {profile_fields} ADD page varchar(255) default NULL');
+  if ($GLOBALS['db_type'] == 'mysql') {
+    $ret[] = update_sql('ALTER TABLE {profile_fields} ADD page varchar(255) default NULL');
+  }
+  else {
+    $ret[] = update_sql('ALTER TABLE {profile_fields} ADD page varchar(255)');
+    $ret[] = update_sql('ALTER TABLE {profile_fields} ALTER COLUMN page SET default NULL');
+  }
   $ret[] = update_sql("UPDATE {profile_fields} SET type = 'url' WHERE name = 'homepage'");
   return $ret;
 }
 
 function update_82() {
-  $ret[] = update_sql("ALTER TABLE {profile_fields} ADD required tinyint(1) DEFAULT '0' NOT NULL");
+  if ($GLOBALS['db_type'] == 'mysql') {
+    $ret[] = update_sql("ALTER TABLE {profile_fields} ADD required tinyint(1) DEFAULT '0' NOT NULL");
+  }
+  else {
+    $ret[] = update_sql("ALTER TABLE {profile_fields} ADD required smallint");
+    $ret[] = update_sql("ALTER TABLE {profile_fields} ALTER COLUMN required SET DEFAULT '0'");
+    $ret[] = update_sql("UPDATE {profile_fields} SET required = '0'");
+    $ret[] = update_sql("ALTER TABLE {profile_fields} ALTER COLUMN required SET NOT NULL");
+  }
   return $ret;
 }
 
@@ -916,10 +982,20 @@ function update_83() {
       status int(1) unsigned NOT NULL default '0',
       PRIMARY KEY  (mid)
      );");
-    $ret[] = update_sql("INSERT INTO sequences (name, id) VALUES ('menu_mid', 1)");
+    $ret[] = update_sql("INSERT INTO {sequences} (name, id) VALUES ('menu_mid', 1)");
   }
   else {
-    /* Needs PostgreSQL equivalent */
+    $ret[] = update_sql("CREATE TABLE menu (
+  mid integer NOT NULL default '0',
+  pid integer NOT NULL default '0',
+  path varchar(255) NOT NULL default '',
+  title varchar(255) NOT NULL default '',
+  weight smallint NOT NULL default '0',
+  visibility smallint NOT NULL default '0',
+  status smallint NOT NULL default '0',
+  PRIMARY KEY  (mid)
+);");
+
   }
   return $ret;
 }
@@ -927,10 +1003,13 @@ function update_83() {
 function update_84() {
   $ret = array();
   if ($GLOBALS["db_type"] == "mysql") {
-    $ret[] = update_sql("ALTER TABLE vocabulary ADD help VARCHAR(255) NOT NULL DEFAULT '' AFTER description;");
+    $ret[] = update_sql("ALTER TABLE {vocabulary} ADD help VARCHAR(255) NOT NULL DEFAULT '' AFTER description;");
   }
   else {
-    /* Needs PostgreSQL equivalent */
+    $ret[] = update_sql("ALTER TABLE {vocabulary} ADD help VARCHAR(255)");
+    $ret[] = update_sql("ALTER TABLE {vocabulary} ALTER help SET DEFAULT ''");
+    $ret[] = update_sql("UPDATE {vocabulary} SET help = ''");
+    $ret[] = update_sql("ALTER TABLE {vocabulary} ALTER help SET NOT NULL");
   }
   return $ret;
 }
@@ -966,7 +1045,32 @@ function update_85() {
      )");
   }
   else {
-    /* Needs PostgreSQL equivalent */
+    $ret[] = update_sql("ALTER TABLE {bundle} RENAME TO {aggregator_category}");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} DROP attributes");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} RENAME bid TO cid");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} ADD description text");
+    $ret[] = update_sql("UPDATE {aggregator_category} SET description = ''");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} ALTER COLUMN description SET NOT NULL");
+    $ret[] = update_sql("ALTER TABLE {feed} RENAME TO {aggregator_feed}");
+    $ret[] = update_sql("ALTER TABLE {aggregator_feed} DROP attributes");
+    $ret[] = update_sql("ALTER TABLE {aggregator_feed} ADD block smallint");
+    $ret[] = update_sql("UPDATE {aggregator_feed} SET block = 0");
+    $ret[] = update_sql("ALTER TABLE {aggregator_feed} ALTER COLUMN block SET NOT NULL");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} ADD block smallint");
+    $ret[] = update_sql("UPDATE {aggregator_category} SET block = 0");
+    $ret[] = update_sql("ALTER TABLE {aggregator_category} ALTER COLUMN block SET NOT NULL");
+    $ret[] = update_sql("ALTER TABLE {item} RENAME TO {aggregator_item}");
+    $ret[] = update_sql("ALTER TABLE {aggregator_item} DROP attributes");
+    $ret[] = update_sql("CREATE TABLE {aggregator_category_feed} (
+      fid integer NOT NULL default '0',
+      cid integer NOT NULL default '0',
+      PRIMARY KEY  (fid,cid)
+    );");
+    $ret[] = update_sql("CREATE TABLE {aggregator_category_item} (
+      iid integer NOT NULL default '0',
+      cid integer NOT NULL default '0',
+      PRIMARY KEY  (iid,cid)
+     )");
   }
   return $ret;
 }