Adding pgsql support to location module. Also re-naming the 'oid' column

to 'eid' since 'oid' is a reserved word in pgsql.  Patch courtesy of drupal.org
user 'sammys' (see http://drupal.org/node/37006 ).

diff --git a/location.install b/location.install
index ed7e2fc..d3ef61c 100644 (file)
--- a/location.install
+++ b/location.install
@@ -7,7 +7,7 @@ function location_install() {
     case 'mysqli':
       db_query("
       CREATE TABLE {location} (
-        oid int(10) unsigned NOT NULL default '0',
+        eid int unsigned NOT NULL default '0',
         type varchar(6) NOT NULL default '',
         name varchar(255) default NULL,
         street varchar(255) default NULL,
@@ -18,8 +18,8 @@ function location_install() {
         country char(2) default NULL,
         latitude decimal(10,6) default NULL,
         longitude decimal(10,6) default NULL,
-        source tinyint(4) default '0',
-        PRIMARY KEY  (type,oid)
+        source tinyint default '0',
+        PRIMARY KEY  (type,eid)
       ) /*!40100 DEFAULT CHARACTER SET utf8 */;
       ");
     
@@ -30,8 +30,8 @@ function location_install() {
         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(2) NOT NULL default '0',
-        dst tinyint(1) NOT NULL default '0',
+        timezone tinyint NOT NULL default '0',
+        dst tinyint NOT NULL default '0',
         country char(2) default '',
         PRIMARY KEY (country, zip),
         KEY pc (country, zip),
@@ -44,14 +44,46 @@ function location_install() {
       $success = TRUE;
       break;
     case 'pgsql':
-
-       break;
+      db_query("CREATE TABLE {location} (
+        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',
+        PRIMARY KEY (type,eid)
+      )");
+    
+      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)");
+      $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.mysql file(s) in the %module directory.', array('%module' => drupal_get_path('module', 'location') . '/database')));
+    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');
@@ -69,3 +101,21 @@ function location_update_2() {
     variable_set('location_configured_countries', $configured_countries);
   }
 }
+
+function location_update_3() {
+  $ret = array();
+
+  switch ($GLOBALS['db_type']) {
+    case 'mysql':
+    case 'mysqli':
+      $ret[] = db_query("ALTER TABLE {location} CHANGE oid eid int unsigned NOT NULL default '0'");
+      break;
+  }
+  
+  return $ret;
+}
+
+/***************************************************************
+  PostgreSQL must be supported in all updates after this comment
+ ***************************************************************/
+

diff --git a/location.module b/location.module
index 62e6397..0d6a527 100644 (file)
--- a/location.module
+++ b/location.module
@@ -135,13 +135,13 @@ function location_search_get_results($edit = NULL) {
       if (count($postal_codes)) {
         $query_fragment = "'". implode(array_keys($postal_codes), ',') ."'";
         
-        $sql = "SELECT n.nid, a.postal_code, a.city, a.province, a.country, n.title, r.teaser, n.type, n.created, n.nid, u.name, FIND_IN_SET(CONCAT(country, postal_code), ". $query_fragment .") AS search_rank FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {node_revisions} r ON n.vid = r.vid WHERE a.type = 'node' AND FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0 ORDER BY search_rank ASC, created DESC";
+        $sql = "SELECT n.nid, a.postal_code, a.city, a.province, a.country, n.title, r.teaser, n.type, n.created, n.nid, u.name, FIND_IN_SET(CONCAT(country, postal_code), ". $query_fragment .") AS search_rank FROM {location} a INNER JOIN {node} n ON a.eid = n.nid INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {node_revisions} r ON n.vid = r.vid WHERE a.type = 'node' AND FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0 ORDER BY search_rank ASC, created DESC";
         $sql = db_rewrite_sql($sql);
 
-        $count_sql = "SELECT COUNT(*) FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE a.type = 'node' AND FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0";
+        $count_sql = "SELECT COUNT(*) FROM {location} a INNER JOIN {node} n ON a.eid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE a.type = 'node' AND FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0";
         $count_sql = db_rewrite_sql($count_sql);
 
-        $count_display_sql = "SELECT COUNT(*) as result_count FROM {location} a INNER JOIN {node} n ON a.oid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0";
+        $count_display_sql = "SELECT COUNT(*) as result_count FROM {location} a INNER JOIN {node} n ON a.eid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE FIND_IN_SET(CONCAT(a.country, a.postal_code), ". $query_fragment .") > 0";
         $count_display_sql = db_rewrite_sql($count_display_sql);
         
         $res = pager_query($sql, 15, 0, $count_sql);
@@ -749,7 +749,7 @@ function location_nodeapi(&$node, $op, $teaser = NULL, $page = NULL) {
         // If the lat/lon was determined from geocoding, then we will want to update the lat/lon using postal codes.
         // If the lat/lon was user-submitted, then we don't want to allow the current editor to change the lat/lon
         if ($node->nid) {
-          $result = db_query("SELECT * FROM {location} WHERE type = 'node' AND oid = %d", $node->nid);
+          $result = db_query("SELECT * FROM {location} WHERE type = 'node' AND eid = %d", $node->nid);
           if ($location = db_fetch_object($result)) {
             if ($location->source != LOCATION_LATLON_USER_SUBMITTED) {
               if ($data = location_latlon_exact($node->location)) {
@@ -809,7 +809,7 @@ function location_nodeapi(&$node, $op, $teaser = NULL, $page = NULL) {
       break;
       
     case 'load':
-      $res = db_query("SELECT * FROM {location} WHERE type = 'node' AND oid = %d", $node->vid);
+      $res = db_query("SELECT * FROM {location} WHERE type = 'node' AND eid = %d", $node->vid);
       $location = array();
       if ($node_location = db_fetch_object($res)) {
         //$location = object2array($node_location);
@@ -1009,7 +1009,7 @@ function location_user($op, &$edit, &$user, $category = NULL) {
   }
   
   if ($op == 'load' && $user_setting != LOCATION_USER_DONT_COLLECT) {
-    $res = db_query("SELECT * FROM {location} WHERE type = 'user' AND oid = %d", $user->uid);
+    $res = db_query("SELECT * FROM {location} WHERE type = 'user' AND eid = %d", $user->uid);
     if ($location = db_fetch_object($res)) {
       $user->location = (array)$location;
     }
@@ -1094,8 +1094,8 @@ function _location_save($posted_location, &$user_or_node, $type = 'node') {
   $field = ($type == 'node' ? 'vid': 'uid');
   if (isset($posted_location) && count($posted_location)) {
     if (isset($posted_location['lat']) && isset($posted_location['lon'])) {
-       db_query("DELETE FROM {location} WHERE type = '%s' AND oid = %d", $type, $user_or_node->$field);
-       db_query("INSERT INTO {location} (type, oid, name, street, additional, city, province, postal_code, country, latitude, longitude, source) VALUES ('%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%f', '%f', %d)",
+       db_query("DELETE FROM {location} WHERE type = '%s' AND eid = %d", $type, $user_or_node->$field);
+       db_query("INSERT INTO {location} (type, eid, name, street, additional, city, province, postal_code, country, latitude, longitude, source) VALUES ('%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%f', '%f', %d)",
               $type, 
               $user_or_node->$field, 
               (!isset($posted_location['name']) || is_null($posted_location['name'])) ? '' : $posted_location['name'],
@@ -1111,8 +1111,8 @@ function _location_save($posted_location, &$user_or_node, $type = 'node') {
             );     
     }
     else {
-      db_query("DELETE FROM {location} WHERE type = '%s' AND oid = %d", $type, $user_or_node->$field);
-      db_query("INSERT INTO {location} (type, oid, name, street, additional, city, province, postal_code, country, source) VALUES ('%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d)",
+      db_query("DELETE FROM {location} WHERE type = '%s' AND eid = %d", $type, $user_or_node->$field);
+      db_query("INSERT INTO {location} (type, eid, name, street, additional, city, province, postal_code, country, source) VALUES ('%s', %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d)",
               $type,
               $user_or_node->$field, 
               (!isset($posted_location['name']) || is_null($posted_location['name'])) ? '' : $posted_location['name'],