- Rewrote the profile module:

   + Added a 'created' field to the users table and renamed the 'timestamp'
     fied to 'changed' (cfr. node table).  Update.php will try to determine
     a 'created' timestamp for existing users.

   + The profile module no longer uses serialized data but has its own set
     of tables.  Known existing profile data is migrated by these new tables.
       TODO: migrate the birthday field.

   + The profile fields can be grouped, and within each group, profile fields
     can be sorted using weights.

   + The profile pages can be themed.

   + The profiles can be browsed based on certain properties/settings.

   + Change the _user hook: (i) 'private_view' and 'public_view' are merged
     into 'view' as there are no private fields and (ii) 'edit_form' has
     been renamed to 'edit'.

   + Avatar handling has been refactored and is now part of the user module.
     The users table has a dedicted 'picture' field.

   + Simplified the way themes should use display/visualize pictures or
     avatars.

   + Made it possible for administrators to replace or delete avatars.

   + ...

  I hope this make for a good base to build on collectively.

3 files changed, 161 insertions, 4 deletions

diff --git a/database/database.mysql b/database/database.mysql
index e5cdab7a3..10f3ff9dc 100644
--- a/database/database.mysql
+++ b/database/database.mysql
@@ -282,13 +282,13 @@ CREATE TABLE node (
   uid int(10) NOT NULL default '0',
   status int(4) NOT NULL default '1',
   created int(11) NOT NULL default '0',
+  changed int(11) NOT NULL default '0',
   comment int(2) NOT NULL default '0',
   promote int(2) NOT NULL default '0',
   moderate int(2) NOT NULL default '0',
   users longtext NOT NULL,
   teaser longtext NOT NULL,
   body longtext NOT NULL,
-  changed int(11) NOT NULL default '0',
   revisions longtext NOT NULL,
   static int(2) NOT NULL default '0',
   PRIMARY KEY  (nid),
@@ -313,6 +313,38 @@ CREATE TABLE page (
 ) TYPE=MyISAM;
 
 --
+-- Table structure for table 'profile_fields'
+--
+
+CREATE TABLE profile_fields (
+  fid int(10) NOT NULL auto_increment,
+  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 tinyint(1) DEFAULT '0' NOT NULL,
+  overview tinyint(1) DEFAULT '0' NOT NULL,
+  options text,
+  KEY category (category),
+  UNIQUE KEY name (name),
+  PRIMARY KEY (fid)
+);
+
+--
+-- Table structure for table 'profile_values'
+--
+
+CREATE TABLE profile_values (
+  fid int(11) unsigned default '0',
+  uid int(11) unsigned default '0',
+  value text,
+  KEY uid (uid),
+  KEY fid (fid)
+);
+
+
+--
 -- Table structure for table 'url_alias'
 --
 
@@ -514,10 +546,12 @@ CREATE TABLE users (
   threshold tinyint(1) default '0',
   theme varchar(255) NOT NULL default '',
   signature varchar(255) NOT NULL default '',
-  timestamp int(11) NOT NULL default '0',
+  created int(11) NOT NULL default '0',
+  changed int(11) NOT NULL default '0',
   status tinyint(4) NOT NULL default '0',
   timezone varchar(8) default NULL,
   language char(2) NOT NULL default '',
+  picture varchar(255) NOT NULL DEFAULT '',
   init varchar(64) default '',
   data longtext,
   rid int(10) unsigned NOT NULL default '0',
diff --git a/database/database.pgsql b/database/database.pgsql
index 12975aa29..213f71a93 100644
--- a/database/database.pgsql
+++ b/database/database.pgsql
@@ -511,10 +511,12 @@ CREATE TABLE users (
   threshold smallint default '0',
   theme varchar(255) NOT NULL default '',
   signature varchar(255) NOT NULL default '',
-  timestamp integer NOT NULL default '0',
+  created integer NOT NULL default '0',
+  changed integer NOT NULL default '0',
   status smallint NOT NULL default '0',
   timezone varchar(8) default NULL,
   language char(2) NOT NULL default '',
+  picture varchar(255) NOT NULL DEFAULT '',
   init varchar(64) default '',
   data text default '',
   rid integer NOT NULL default '0',
diff --git a/database/updates.inc b/database/updates.inc
index e39267ef4..79b257352 100644
--- a/database/updates.inc
+++ b/database/updates.inc
@@ -50,7 +50,8 @@ $sql_updates = array(
   "2004-01-11" => "update_76",
   "2004-01-13" => "update_77",
   "2004-02-03" => "update_78",
-  "2004-02-21" => "update_79"
+  "2004-02-21" => "update_79",
+  "2004-03-11: first update since Drupal 4.4.0 release" => "update_80"
 );
 
 function update_32() {
@@ -760,6 +761,126 @@ function update_79() {
   return $ret;
 }
 
+function update_80() {
+
+  // Add a 'created' field to the users table:
+  update_sql('ALTER TABLE {users} ADD created INT(11) NOT NULL');
+  update_sql('ALTER TABLE {users} CHANGE timestamp changed INT(11) NOT NULL');
+
+  // Add some indices to speed up the update process:
+  update_sql('ALTER TABLE {comments} ADD index (timestamp)');
+  update_sql('ALTER TABLE {node} ADD index (created)');
+
+  // Assign everyone a created timestamp to begin with:
+  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);
+  }
+
+  // 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:
+  update_sql("CREATE TABLE {profile_fields} (
+    fid int(10) NOT NULL auto_increment,
+    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 tinyint(1) DEFAULT '0' NOT NULL,
+    overview tinyint(1) DEFAULT '0' NOT NULL,
+    options text,
+    KEY category (category),
+    UNIQUE KEY name (name),
+    PRIMARY KEY (fid)
+   );");
+
+  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)
+   );");
+
+  // 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", "textfield", 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}");
+  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);
+  }
+}
+
 function update_sql($sql) {
   $edit = $_POST["edit"];
   $result = db_query($sql);