location.install

<?php
// $Id$

/**
 * @file
 * Installation / uninstallation routines.
 */

/**
 * Implementation of hook_install().
 */
function location_install() {
  drupal_set_message('Installing location');
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      db_query("
      CREATE TABLE {location} (
        lid int unsigned NOT NULL default '0',
        name varchar(255) default NULL,
        street varchar(255) default NULL,
        additional varchar(255) default NULL,
        city varchar(255) default NULL,
        province varchar(16) default NULL,
        postal_code varchar(16) default NULL,
        country char(2) default NULL,
        latitude decimal(10,6) default NULL,
        longitude decimal(10,6) default NULL,
        source tinyint default '0',
        is_primary tinyint NOT NULL default '0',
        PRIMARY KEY  (lid)
      ) /*!40100 DEFAULT CHARACTER SET utf8 */;
      ");

      db_query("
      CREATE TABLE {location_instance} (
        nid int UNSIGNED DEFAULT NULL,
        vid int UNSIGNED DEFAULT NULL,
        uid int UNSIGNED DEFAULT NULL,
        genid varchar(255) NOT NULL default '',
        lid int UNSIGNED NOT NULL DEFAULT '0',
        INDEX {location_instance}_nid_idx (nid),
        INDEX {location_instance}_vid_idx (vid),
        INDEX {location_instance}_uid_idx (uid),
        INDEX {location_instance}_genid_idx (genid),
        INDEX {location_instance}_lid_idx (lid)
      ) /*!40100 DEFAULT CHARACTER SET utf8 */");

      db_query("
      CREATE TABLE {zipcodes} (
        zip varchar(16) NOT NULL default '0',
        city varchar(30) NOT NULL default '',
        state varchar(30) NOT NULL default '',
        latitude decimal(10,6) NOT NULL default '0.000000',
        longitude decimal(10,6) NOT NULL default '0.000000',
        timezone tinyint NOT NULL default '0',
        dst tinyint NOT NULL default '0',
        country char(2) default '',
        PRIMARY KEY (country, zip),
        KEY pc (country, zip),
        KEY zip (zip),
        KEY latitude (latitude),
        KEY longitude (longitude),
        KEY country (country)
      ) /*!40100 DEFAULT CHARACTER SET utf8 */;
      ");

      db_query("CREATE TABLE {cache_location} (
        cid varchar(255) NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");

      $success = TRUE;
      break;
    case 'pgsql':
      db_query("CREATE TABLE {location} (
        lid serial CHECK (lid >= 0),
        name varchar(255) default NULL,
        street varchar(255) default NULL,
        additional varchar(255) default NULL,
        city varchar(255) default NULL,
        province varchar(16) default NULL,
        postal_code varchar(16) default NULL,
        country char(2) default NULL,
        latitude decimal(10,6) default NULL,
        longitude decimal(10,6) default NULL,
        source smallint default '0',
        is_primary smallint default '0',
        PRIMARY KEY (lid)
      )");

      db_query("
      CREATE TABLE {location_instance} (
        nid int DEFAULT NULL CHECK (nid >= 0 OR nid IS NULL),
        vid int DEFAULT NULL CHECK (vid >= 0 OR vid IS NULL),
        uid int DEFAULT NULL CHECK (uid >= 0 OR uid IS NULL),
        genid varchar(255) NOT NULL default '',
        lid int NOT NULL DEFAULT '0' CHECK (lid >= 0)
      )");
      db_query('CREATE INDEX {location_instance}_nid_idx ON {location_instance} (nid)');
      db_query('CREATE INDEX {location_instance}_vid_idx ON {location_instance} (vid)');
      db_query('CREATE INDEX {location_instance}_uid_idx ON {location_instance} (uid)');
      db_query('CREATE INDEX {location_instance}_lid_idx ON {location_instance} (lid)');
      db_query('CREATE INDEX {location_instance}_genid_idx ON {location_instance} (genid)');

      db_query("CREATE TABLE {zipcodes} (
        zip varchar(16) NOT NULL default '0',
        city varchar(30) NOT NULL default '',
        state varchar(30) NOT NULL default '',
        latitude decimal(10,6) NOT NULL default '0.000000',
        longitude decimal(10,6) NOT NULL default '0.000000',
        timezone smallint NOT NULL default '0',
        dst smallint NOT NULL default '0',
        country char(2) default '',
        PRIMARY KEY (country, zip)
      )");
      db_query("CREATE INDEX {zipcodes}_pc_idx ON {zipcodes} (country, zip)");
      db_query("CREATE INDEX {zipcodes}_zip_idx ON {zipcodes} (zip)");
      db_query("CREATE INDEX {zipcodes}_latitude_idx ON {zipcodes} (latitude)");
      db_query("CREATE INDEX {zipcodes}_longitude_idx ON {zipcodes} (longitude)");
      db_query("CREATE INDEX {zipcodes}_country_idx ON {zipcodes} (country)");

      db_query("CREATE TABLE {cache_location} (
        cid varchar(255) NOT NULL default '',
        data bytea,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid)
      )");
      db_query("CREATE INDEX {cache_location}_expire_idx ON {cache_location} (expire)");


      $success = TRUE;
      break;
    default:
      break;
  } // End case

  if ($success) {
    drupal_set_message(t('Location module installed tables successfully. If you would also like a database of zip codes, please manually import the appropriate zipcode.XX.YYYY file(s) in the %module directory.', array('%module' => drupal_get_path('module', 'location') .'/database')));
  }
  else {
    drupal_set_message(t('The installation of Location module was unsuccessful.'), 'error');
  }
}


/**
 * Implentation of hook_uninstall().
 */
function location_uninstall() {
  // Delete tables.
  if (db_table_exists('location')) {
    db_query('DROP TABLE {location}');
  }
  if (db_table_exists('zipcodes')) {
    db_query('DROP TABLE {zipcodes}');
  }
  if (db_table_exists('cache_location')) {
    db_query('DROP TABLE {cache_location}');
  }
  if (db_table_exists('location_instance')) {
    db_query('DROP TABLE {location_instance}');
  }

  // Delete variables.
  variable_del('location_configured_countries');
  variable_del('location_default_country');
  variable_del('location_display_location');
  variable_del('location_search_distance_unit');
  variable_del('location_suppress_country');
  variable_del('location_usegmap');
  variable_del('location_garbagecollect');
}

/**
 * Legacy update 1.
 * Convert tables to utf8.
 */
function location_update_1() {
  return _system_update_utf8(array('location', 'zipcodes'));
}

/**
 * Legacy update 2.
 * Fix a bug with the "us" entry in the "location_configured_countries" var.
 */
function location_update_2() {
  $configured_countries = variable_get('location_configured_countries', array());
  if ($configured_countries['us']) {
    $configured_countries['us'] = 'us';
    variable_set('location_configured_countries', $configured_countries);
  }
  return array();
}

/**
 * Legacy update 3.
 * Allow for postgresql support by renaming the oid column, which is a reserved
 * name on postgresql.
 */
function location_update_3() {
  $ret = array();

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {location} CHANGE oid eid int unsigned NOT NULL default '0'");
      break;
  }

  drupal_set_message("The schema for location module has been updated.  The update is such that you may want to re-resave any views you have that may include locations.");

  if (module_exists('views')) {
    views_invalidate_cache();
  }
  return $ret;
}

/***************************************************************
  PostgreSQL must be supported in all updates after this comment
 ***************************************************************/

/**
 * Legacy update 4.
 * Add "lid" as the new location key.
 */
function location_update_4() {
  $ret = array();

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {location} ADD COLUMN lid int(10) unsigned NOT NULL default '0' AFTER eid");

      $result = db_query("SELECT eid, type FROM {location}");
      $next_id = 0;
      while ($row = db_fetch_object($result)) {
        $next_id++;
        db_query("UPDATE {location} SET lid = %d WHERE eid = %d AND type = '%s'", $next_id, $row->eid, $row->type);
      }

      $ret[] = update_sql("ALTER TABLE {location} DROP PRIMARY KEY");
      $ret[] = update_sql("ALTER TABLE {location} ADD PRIMARY KEY (lid)");

      db_query("INSERT INTO {sequences} (name, id) VALUES ('{location}_lid', %d)", $next_id);
      $ret[] = update_sql("ALTER TABLE {location} ADD COLUMN is_primary tinyint NOT NULL default '0'");
      $ret[] = update_sql("UPDATE {location} SET is_primary = 1 WHERE type = 'user'");

      break;
    case 'pgsql':
      // help me
      break;
  }

  foreach (node_get_types() as $type => $name) {
    $new_setting = variable_get('location_'. $type, 0) ? 1 : 0;
    variable_del('location_'. $type);
    variable_set('location_maxnum_'. $type, $new_setting);
    variable_set('location_defaultnum_'. $type, $new_setting);
  }
  return $ret;
}

/**
 * Legacy update 5.
 * Postgresql support that was missing from previous update.
 */
function location_update_5() {
  $ret = array();

  switch ($GLOBALS['db_type']) {
    case 'pgsql':
      $ret[] = update_sql("ALTER TABLE {location} DROP CONSTRAINT {location}_pkey");
      $ret[] = update_sql("ALTER TABLE {location} RENAME TO {location}_old");
      $ret[] = update_sql("CREATE TABLE {location} (
        lid serial CHECK (lid >= 0),
        eid int NOT NULL default '0' CHECK (eid >= 0),
        type varchar(6) NOT NULL default '',
        name varchar(255) default NULL,
        street varchar(255) default NULL,
        additional varchar(255) default NULL,
        city varchar(255) default NULL,
        province varchar(16) default NULL,
        postal_code varchar(16) default NULL,
        country char(2) default NULL,
        latitude decimal(10,6) default NULL,
        longitude decimal(10,6) default NULL,
        source smallint default '0',
        is_primary smallint default '0',
        PRIMARY KEY (lid)
      )");
      $ret[] = update_sql("INSERT INTO {location}
        (eid, type, name, street, additional, city, province, postal_code, country, latitude,
        longitude, source) SELECT eid, type, name, street, additional, city, province,
        postal_code, country, latitude, longitude, source FROM {location}_old");
      $ret[] = update_sql("DROP TABLE {location}_old");
      $ret[] = update_sql("UPDATE {location} SET is_primary = 1 WHERE type = 'user'");
      break;
  }
  return $ret;
}

/**
 * Legacy update 6.
 * Use correct country code for Sweeden.
 */
function location_update_6() {
  $ret = array();

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("UPDATE {location} SET country = 'se' WHERE country = 'sw'");

      break;
    case 'pgsql':
      $ret[] = update_sql("UPDATE {location} SET country = 'se' WHERE country = 'sw'");

      break;
  }
  return $ret;
}

/**
 * Update 7 (Location 2.x)
 * Generalize google geocoding so you don't have to enter the api key over and over.
 */
function location_update_7() {
  $ret = array();

  $services = array('google');
  $general_geocoders_in_use = array();

  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $result = db_query('SELECT * FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]$\'');

      while ($row = db_fetch_object($result)) {
        $value_decoded = unserialize($row->value);
        if (!in_array($value_decoded, $services)) {
          $ret[] = update_sql('UPDATE {variable} SET value = \''. serialize($value_decoded .'|'. substr($row->name, 17)) .'\' WHERE name = \''. $row->name .'\'');
        }
        else {
          $general_geocoders_in_use[$value_decoded] = $value_decoded;
        }
      }

      $key = db_result(db_query('SELECT value FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]_google_apikey$\' LIMIT 1'));

      $ret[] = update_sql('DELETE FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]_google_apikey$\'');
      $ret[] = update_sql('INSERT INTO {variable} (name, value) VALUES (\'location_geocode_google_apikey\', \''. $key .'\')');
      $ret[] = update_sql('DELETE FROM {cache} WHERE cid = \'variables\'');

      variable_set('location_general_geocoders_in_use', $general_geocoders_in_use);
      break;
    case 'pgsql':
      $result = db_query('SELECT * FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]$\'');

      while ($row = db_fetch_object($result)) {
        $value_decoded = unserialize($row->value);
        if (!in_array($value_decoded, $services)) {
          $ret[] = update_sql('UPDATE {variable} SET value = \''. serialize($value_decoded .'|'. substr($row->name, 17)) .'\' WHERE name = \''. $row->name .'\'');
        }
        else {
          $general_geocoders_in_use[$value_decoded] = $value_decoded;
        }
      }

      $key = db_result(db_query('SELECT value FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]_google_apikey$\' LIMIT 1'));

      $ret[] = update_sql('DELETE FROM {variable} WHERE name REGEXP \'^location_geocode_[a-z][a-z]_google_apikey$\'');
      $ret[] = update_sql('INSERT INTO {variable} (name, value) VALUES (\'location_geocode_google_apikey\', \''. $key .'\')');
      $ret[] = update_sql('DELETE FROM {cache} WHERE cid = \'variables\'');

      variable_set('location_general_geocoders_in_use', $general_geocoders_in_use);
      break;
  }
  return $ret;
}

/**
 * Location 3.x update 1.
 * Add location specific cache table.
 */
function location_update_5300() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("CREATE TABLE {cache_location} (
        cid varchar(255) NOT NULL default '',
        data longblob,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid),
        INDEX expire (expire)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");
      break;
    case 'pgsql':
      $ret[] = update_sql("CREATE TABLE {cache_location} (
        cid varchar(255) NOT NULL default '',
        data bytea,
        expire int NOT NULL default '0',
        created int NOT NULL default '0',
        headers text,
        PRIMARY KEY (cid)
      )");
      $ret[] = update_sql("CREATE INDEX {cache_location}_expire_idx ON {cache_location} (expire)");
      break;
  }
  return $ret;
}

/**
 * Location 3.x update 2.
 * Normalize the location table.
 * This allows:
 *   - Making the loading and saving code cleaner.
 *   - Fixing a longstanding bug with revisions.
 *   - Having the same location on multiple nodes/users/both.
 *   - Garbage collecting unused locations periodically.
 *   - Having full support for deletions.
 *   - Full revisions support.
 * Note that the location_instance table does NOT have a primary key.
 * This is on purpose. It's a N:M join table.
 */
function location_update_5301() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("
      CREATE TABLE {location_instance} (
        nid int UNSIGNED DEFAULT NULL,
        vid int UNSIGNED DEFAULT NULL,
        uid int UNSIGNED DEFAULT NULL,
        genid varchar(255) NOT NULL default '',
        lid int UNSIGNED NOT NULL DEFAULT '0',
        INDEX {location_instance}_nid_idx (nid),
        INDEX {location_instance}_vid_idx (vid),
        INDEX {location_instance}_uid_idx (uid),
        INDEX {location_instance}_genid_idx (genid),
        INDEX {location_instance}_lid_idx (lid)
      ) /*!40100 DEFAULT CHARACTER SET utf8 */");
      break;
    case 'pgsql':
      $ret[] = update_sql("
      CREATE TABLE {location_instance} (
        nid int DEFAULT NULL CHECK (nid >= 0 OR nid IS NULL),
        vid int DEFAULT NULL CHECK (vid >= 0 OR vid IS NULL),
        uid int DEFAULT NULL CHECK (uid >= 0 OR uid IS NULL),
        genid varchar(255) NOT NULL default '',
        lid int NOT NULL DEFAULT '0' CHECK (lid >= 0)
      )");
      $ret[] = update_sql('CREATE INDEX {location_instance}_nid_idx ON {location_instance} (nid)');
      $ret[] = update_sql('CREATE INDEX {location_instance}_vid_idx ON {location_instance} (vid)');
      $ret[] = update_sql('CREATE INDEX {location_instance}_uid_idx ON {location_instance} (uid)');
      $ret[] = update_sql('CREATE INDEX {location_instance}_lid_idx ON {location_instance} (lid)');
      $ret[] = update_sql('CREATE INDEX {location_instance}_genid_idx ON {location_instance} (genid)');
      break;
  }

  // Synthesise node location data based on what we have.
  // Storage of locations was previously stored against node revision, BUT the
  // data was not properly duplicated by revision (i.e. only the latest revision
  // carried the data.)
  // Joining like this allows us to backfill all the old revisions with the current
  // data, which is not nice but better than having no data at all when viewing
  // old revisions.
  $ret[] = update_sql("INSERT INTO {location_instance} (nid,vid,lid) (SELECT nr.nid, nr.vid, l.lid FROM {node_revisions} nr INNER JOIN {node_revisions} nr2 ON nr.nid = nr2.nid INNER JOIN {location} l ON nr2.vid = l.eid AND l.type = 'node')");

  // Users is much simpler.
  $ret[] = update_sql("INSERT INTO {location_instance} (uid,lid) (SELECT eid, lid FROM {location} WHERE type = 'user')");

  // Aug 18 2008:
  // Save everything else in genid.
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("INSERT INTO {location_instance} (genid, lid) (SELECT CONCAT(type, ':', eid), lid FROM {location} WHERE type <> 'user' AND type <> 'node')");
      break;
    case 'pgsql':
      $ret[] = update_sql("INSERT INTO {location_instance} (genid, lid) (SELECT type||':'||eid, lid FROM {location} WHERE type <> 'user' AND type <> 'node')");
      break;
  }

  // Remove now unused columns.
  $ret[] = update_sql("ALTER TABLE {location} DROP COLUMN type");
  $ret[] = update_sql("ALTER TABLE {location} DROP COLUMN eid");

  // General cleanup.
  variable_del('location_user'); // Removed in favor of permission check.

  // Variable consolidation (as part of the element based system)
  // We're doing this "raw" so we can be sure we got everything moved over,
  // INCLUDING content types that were deleted in the past.
  // This will let us do better cleanup sometime in the future.
  $data = array();
  $todelete = array();
  foreach(array('name', 'street', 'additional', 'city', 'province', 'postal_code', 'country', 'phone', 'fax') as $field) {
    $result = db_query("SELECT name, value FROM {variable} WHERE name LIKE 'location_%s%%'", $field);
    while ($row = db_fetch_object($result)) {
      $data[substr($row->name, strlen($field) + 10)][$field] = (string)(int)unserialize($row->value);
      $todelete[] = $row->name;
    }
  }
  foreach ($data as $type => $value) {
    // We aren't going to trust that hook_locationapi is operational.
    // So, stick with some conservative defaults.
    $value = array_merge(array(
      'name' => '1',
      'street' => '1',
      // additional is left out of this list intentionally.
      'city' => '0',
      'province' => '0',
      'postal_code' => '0',
      'country' => '1',
    ), $value);
    if (!isset($value['additional'])) {
      // Initialize additional to match street.
      $value['additional'] = $value['street'];
    }
    variable_set('location_fields_'. $type, $value);
  }
  foreach ($todelete as $key) {
    variable_del($key);
  }

  // This update was retrofitted on Aug 18, 2008. Set a flag for use by
  // the next update in order to handle the case where someone has already
  // updated to EXACTLY schema revision 5301 before the retrofit took effect.
  // People who migrated past this point before that date may have the following
  // inconsistencies:
  // A) location_{field}_{type} variables were not collected for content types
  // that had been deleted in the past.
  // B) Any locations with the 'type' field set to something other than 'node'
  // or 'user' did not get entries in {location_instance}.
  variable_set('location_update_5301_retrofit', TRUE);
  return $ret;
}

/**
 * Location 3.x update 3.
 * Add genid to {location_instance}.
 */
function location_update_5302() {
  $ret = array();
  // OK, here's the deal. I retrofitted 5301 on Aug 18 2008 to integrate the genid.
  // This was needed to fix the pre location 3.x todo item regarding keeping non
  // user, non node data intact. People doing an update after Aug 18 will already
  // have the genid column in place, so it can be safely skipped.
  if (!variable_get('location_update_5301_retrofit', FALSE)) {
    switch ($GLOBALS['db_type']) {
      case 'mysql':
      case 'mysqli':
        $ret[] = update_sql("ALTER TABLE {location_instance} ADD COLUMN genid varchar(255) NOT NULL default '' AFTER uid");
        $ret[] = update_sql('CREATE INDEX {location_instance}_genid_idx ON {location_instance} (genid)');
        break;
      case 'pgsql':
        db_add_column($ret, 'location_instance', 'genid', 'varchar(255)', array('not null' => TRUE, 'default' => "''"));
        $ret[] = update_sql('CREATE INDEX {location_instance}_genid_idx ON {location_instance} (genid)');
        break;
    }
  }
  return $ret;
}

/**
 * Location 3.x update 4.
 * Shuffle more variables around.
 */
function location_update_5303() {
  $ret = array();

  $types = array();
  $result = db_query("SELECT name FROM {variable} WHERE name LIKE 'location_display_teaser_%'");
  while ($row = db_fetch_object($result)) {
    $type = substr($row->name, 24);
    $types[$type]['teaser'] = variable_get('location_display_teaser_'. $type, TRUE);
    $types[$type]['full'] = variable_get('location_display_full_'. $type, TRUE);
    $types[$type]['weight'] = variable_get('location_display_weight_'. $type, 0);
    // @@@ Combine location_suppress_country and country require settings to set this up?
    $types[$type]['hide'] = array();
  }
  foreach ($types as $type => $value) {
    variable_set("location_display_$type", $value);
    variable_del("location_display_teaser_$type");
    variable_del("location_display_full_$type");
    variable_del("location_display_weight_$type");
  }

  return $ret;
}

/**
 * Location 3.x update 3.
 * 3.0 province renames.
 */
/* (temporarily disabled so we can run all of these updates at once)
function location_update_5302() {
  // Italy/Forlì-Cesena
  // FO -> FC
  // Italy/Pesaro e Urbino
  // PS -> PU

  // Sync location defaults -- especially the missing 'additional' field.

}
 */

/**
 * Location 3.x update 1.
 * Add index on eid column.
 *
 * @note Following the new code standards for D6, all update
 * functions will be composed by: XYZZ pattern, where:
 * X  is the main Drupal version
 * Y  is the main Location module version
 * ZZ is the 2-digit unique update ID
 */
/*
function location_update_5301() {
  $ret = array();
  // @todo before 3.0 release:
  // Verify that this works across databases.
  $ret[] = update_sql("CREATE INDEX {location}_eid ON {location} (eid)");
  return $ret;
}
 */

// @@@ variable_del('location_configured_countries');