X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=lib%2Fpgsqlschema.php;h=272f7eff683f354e5ca72a42401e7e0ddd0c5c4c;hb=3dd734b2c3ea49c55467cfbfd4b3a5fb38456e87;hp=91bc09667c0976d78ed7026aef14b00e3a9d770f;hpb=c4557d4d0700c09742b9d2e002c2d2b0161558f3;p=quix0rs-gnu-social.git

diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php
index 91bc09667c..272f7eff68 100644
--- a/lib/pgsqlschema.php
+++ b/lib/pgsqlschema.php
@@ -41,6 +41,7 @@ if (!defined('STATUSNET')) {
  * @category Database
  * @package  StatusNet
  * @author   Evan Prodromou <evan@status.net>
+ * @author   Brenda Wallace <shiny@cpan.org>
  * @license  http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0
  * @link     http://status.net/
  */
@@ -61,7 +62,8 @@ class PgsqlSchema extends Schema
 
     public function getTableDef($name)
     {
-        $res = $this->conn->query("select *, column_default as default, is_nullable as Null, udt_name as Type, column_name AS Field from INFORMATION_SCHEMA.COLUMNS where table_name = '$name'");
+        $res = $this->conn->query("SELECT *, column_default as default, is_nullable as Null,
+        udt_name as Type, column_name AS Field from INFORMATION_SCHEMA.COLUMNS where table_name = '$name'");
 
         if (PEAR::isError($res)) {
             throw new Exception($res->getMessage());
@@ -72,10 +74,12 @@ class PgsqlSchema extends Schema
         $td->name    = $name;
         $td->columns = array();
 
+        if ($res->numRows() == 0 ) {
+          throw new Exception('no such table'); //pretend to be the msyql error. yeah, this sucks.
+        }
         $row = array();
 
         while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
-//             var_dump($row);
             $cd = new ColumnDef();
 
             $cd->name = $row['field'];
@@ -139,6 +143,7 @@ class PgsqlSchema extends Schema
         $uniques = array();
         $primary = array();
         $indices = array();
+	$onupdate = array();
 
         $sql = "CREATE TABLE $name (\n";
 
@@ -151,7 +156,6 @@ class PgsqlSchema extends Schema
             }
 
             $sql .= $this->_columnSql($cd);
-
             switch ($cd->key) {
             case 'UNI':
                 $uniques[] = $cd->name;
@@ -166,23 +170,23 @@ class PgsqlSchema extends Schema
         }
 
         if (count($primary) > 0) { // it really should be...
-            $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")";
+            $sql .= ",\n PRIMARY KEY (" . implode(',', $primary) . ")";
         }
 
+        $sql .= "); ";
+
+
         foreach ($uniques as $u) {
-            $sql .= ",\nunique index {$name}_{$u}_idx ($u)";
+            $sql .= "\n CREATE index {$name}_{$u}_idx ON {$name} ($u); ";
         }
 
         foreach ($indices as $i) {
-            $sql .= ",\nindex {$name}_{$i}_idx ($i)";
+            $sql .= "CREATE index {$name}_{$i}_idx ON {$name} ($i)";
         }
-
-        $sql .= "); ";
-
         $res = $this->conn->query($sql);
 
         if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
+            throw new Exception($res->getMessage(). ' SQL was '. $sql);
         }
 
         return true;
@@ -209,6 +213,22 @@ class PgsqlSchema extends Schema
         return true;
     }
 
+    /**
+     * Translate the (mostly) mysql-ish column types into somethings more standard
+     * @param string column type
+     *
+     * @return string postgres happy column type
+     */
+    private function _columnTypeTranslation($type) {
+      $map = array(
+      'datetime' => 'timestamp',
+      );
+      if(!empty($map[$type])) {
+        return $map[$type];
+      }
+      return $type;
+    }
+
     /**
      * Adds an index to a table.
      *
@@ -302,7 +322,7 @@ class PgsqlSchema extends Schema
 
     public function modifyColumn($table, $columndef)
     {
-        $sql = "ALTER TABLE $table MODIFY COLUMN " .
+        $sql = "ALTER TABLE $table ALTER COLUMN TYPE " .
           $this->_columnSql($columndef);
 
         $res = $this->conn->query($sql);
@@ -359,6 +379,7 @@ class PgsqlSchema extends Schema
 
         try {
             $td = $this->getTableDef($tableName);
+            
         } catch (Exception $e) {
             if (preg_match('/no such table/', $e->getMessage())) {
                 return $this->createTable($tableName, $columns);
@@ -374,16 +395,17 @@ class PgsqlSchema extends Schema
         $todrop = array_diff($cur, $new);
         $same   = array_intersect($new, $cur);
         $tomod  = array();
-
         foreach ($same as $m) {
             $curCol = $this->_byName($td->columns, $m);
             $newCol = $this->_byName($columns, $m);
+            
 
             if (!$newCol->equals($curCol)) {
-                $tomod[] = $newCol->name;
+            // BIG GIANT TODO!
+            // stop it detecting different types and trying to modify on every page request
+//                 $tomod[] = $newCol->name;
             }
         }
-
         if (count($toadd) + count($todrop) + count($tomod) == 0) {
             // nothing to do
             return true;
@@ -407,11 +429,12 @@ class PgsqlSchema extends Schema
         foreach ($tomod as $columnName) {
             $cd = $this->_byName($columns, $columnName);
 
-            $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd);
+	/* brute force */
+            $phrase[] = 'DROP COLUMN ' . $columnName;
+            $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd);
         }
 
         $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase);
-
         $res = $this->conn->query($sql);
 
         if (PEAR::isError($res)) {
@@ -473,15 +496,25 @@ class PgsqlSchema extends Schema
      *
      * @return string correct SQL for that column
      */
-
     private function _columnSql($cd)
     {
         $sql = "{$cd->name} ";
+        $type = $this->_columnTypeTranslation($cd->type);
+
+        //handle those mysql enum fields that postgres doesn't support
+        if (preg_match('!^enum!', $type)) {
+          $allowed_values = preg_replace('!^enum!', '', $type);
+          $sql .= " text check ({$cd->name} in $allowed_values)";
+          return $sql;
+        }
+        if (!empty($cd->auto_increment)) {
+	    $type = "bigserial"; // FIXME: creates the wrong name for the sequence for some internal sequence-lookup function, so better fix this to do the real 'create sequence' dance.
+        }
 
         if (!empty($cd->size)) {
-            $sql .= "{$cd->type}({$cd->size}) ";
+            $sql .= "{$type}({$cd->size}) ";
         } else {
-            $sql .= "{$cd->type} ";
+            $sql .= "{$type} ";
         }
 
         if (!empty($cd->default)) {
@@ -489,14 +522,10 @@ class PgsqlSchema extends Schema
         } else {
             $sql .= ($cd->nullable) ? "null " : "not null ";
         }
-        
-        if (!empty($cd->auto_increment)) {
-            $sql .= " auto_increment ";
-        }
 
-        if (!empty($cd->extra)) {
-            $sql .= "{$cd->extra} ";
-        }
+//         if (!empty($cd->extra)) {
+//             $sql .= "{$cd->extra} ";
+//         }
 
         return $sql;
     }