• Main Page
  • Related Pages
  • Namespaces
  • Classes
  • Files
  • Examples
  • File List

system/schema/pgsql/connection.php

00001 <?php
00008 namespace Habari;
00009 
00010 class PGSQLConnection extends DatabaseConnection
00011 {
00021   public function connect( $connect_string, $db_user, $db_pass )
00022   {
00023     // If something went wrong, we don't need to exec the specific commands.
00024     if ( !parent::connect( $connect_string, $db_user, $db_pass ) ) {
00025       return false;
00026     }
00027 
00028     return true;
00029   }
00030 
00040   function sql_t( $sql )
00041   {
00042     $sql = preg_replace_callback( '%concat\(([^)]+?)\)%i', array( &$this, 'replace_concat' ), $sql );
00043     $sql = preg_replace( '%DATE_SUB\s*\(\s*NOW\(\s*\)\s*,\s*INTERVAL\s+([0-9]+)\s+DAY\s*\)%ims', 'NOW() - INTERVAL \'${1} DAYS\'', $sql );
00044     $sql = preg_replace( '%OPTIMIZE TABLE ([^ ]*)%i', 'VACUUM ${1};', $sql );
00045     //$sql= preg_replace( '%YEAR\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'year\', ${1})', $sql );
00046     //$sql= preg_replace( '%MONTH\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'month\', ${1})', $sql );
00047     //$sql= preg_replace( '%DAY\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'day\', ${1})', $sql );
00048     $sql = preg_replace( '%YEAR\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'year\', \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
00049     $sql = preg_replace( '%MONTH\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'month\',  \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
00050     $sql = preg_replace( '%DAY\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'day\',  \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
00051     $sql = preg_replace('%LIKE\s+((\'|").*\2)%iUms', 'ILIKE \1', $sql);
00052     $sql = preg_replace( '%RAND\s*\(\s*\)%i', 'RANDOM()', $sql );
00053     return $sql;
00054   }
00055 
00063   function replace_concat( $matches )
00064   {
00065     $innards = explode( ',', $matches[1] );
00066     return implode( ' || ', $innards );
00067   }
00068 
00079   function dbdelta( $queries, $execute = true, $silent = true, $doinserts = false )
00080   {
00081     if ( !is_array( $queries ) ) {
00082       $queries = explode( ';', $queries );
00083       if ( '' == $queries[count( $queries ) - 1] ) {
00084         array_pop( $queries );
00085       }
00086     }
00087 
00088     $cseqqueries = array();
00089     $cqueries = array();
00090     $alterseqqueries = array();
00091     $indexqueries = array();
00092     $iqueries = array();
00093     $for_update = array();
00094     $indices = array();
00095 
00096     foreach ( $queries as $qry ) {
00097       if ( preg_match( "|CREATE TABLE\s+(\w*)|", $qry, $matches ) ) {
00098         $cqueries[strtolower( $matches[1] )] = $qry;
00099         $for_update[$matches[1]] = 'Created table ' . $matches[1];
00100       }
00101       else if ( preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*) ON ([^ ]*)|", $qry, $matches ) ) {
00102         $indexqueries[strtolower( $matches[3] )] = $qry;
00103       }
00104       else if ( preg_match( "|CREATE DATABASE ([^ ]*)|", $qry, $matches ) ) {
00105         array_unshift( $cqueries, $qry );
00106       }
00107       else if ( preg_match( "|CREATE SEQUENCE ([^ ;]*)|", $qry, $matches ) ) {
00108         $cseqqueries[strtolower( $matches[1] )] = $qry;
00109       }
00110       else if ( preg_match( "|ALTER SEQUENCE ([^ ]*)|", $qry, $matches ) ) {
00111         $alterseqqueries[] = $qry;
00112       }
00113       else if ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) {
00114         $iqueries[] = $qry;
00115       }
00116       else if ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) {
00117         $iqueries[] = $qry;
00118       }
00119       else {
00120         // Unrecognized query type
00121       }
00122     }
00123 
00124     // Checking sequence
00125     if ( $seqnames = $this->get_results(
00126       "SELECT c.relname as name,
00127         CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS type
00128          FROM pg_catalog.pg_class c
00129          JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
00130         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
00131         WHERE c.relkind IN ('S','')
00132         AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
00133         AND pg_catalog.pg_table_is_visible(c.oid)
00134         ORDER BY 1,2;" ) ) {
00135       foreach ( ( array ) $seqnames as $seqname ) {
00136         if ( array_key_exists( strtolower( $seqname->name ), $cseqqueries ) ) {
00137           unset( $cseqqueries[$seqname->name] );
00138         }
00139       }
00140     }
00141 
00142     if ( $tables = $this->get_column(
00143       "SELECT table_name
00144          FROM information_schema.tables
00145          WHERE table_type = 'BASE TABLE'
00146          AND table_schema NOT IN ('pg_catalog', 'information_schema');" ) ) {
00147       foreach ( $tables as $table ) {
00148         if ( array_key_exists( strtolower( $table ), $cqueries ) ) {
00149           unset( $cfields );
00150           $cfields = array();
00151           unset( $indices );
00152           $indices = array();
00153           preg_match( "|\((.*)\)|ms", $cqueries[strtolower( $table )], $match2 );
00154           $qryline = trim( $match2[1] );
00155 
00156           $flds = explode( "\n", $qryline );
00157           foreach ( $flds as $fld ) {
00158             preg_match( "|^([^ ]*)|", trim( $fld ), $fvals );
00159             $fieldname = $fvals[1];
00160             $validfield = true;
00161             switch ( strtolower( $fieldname ) ) {
00162               case '':
00163               case 'primary':
00164               case 'index':
00165               case 'fulltext':
00166               case 'unique':
00167               case 'key':
00168                 $validfield = false;
00169                 $indices[] = trim( trim( $fld ), ", \n" );
00170                 break;
00171             }
00172             $fld = trim( $fld );
00173             if ( $validfield ) {
00174               $cfields[strtolower( $fieldname )] = trim( $fld, ", \n" );
00175             }
00176           }
00177           if ( isset( $indexqueries[$table] ) ) {
00178             preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*) ON ([^ ]*) \((.*)\)|ms", $indexqueries[$table], $matches );
00179             if ( $matches ) {
00180               $indices[] = ' (' . preg_replace( '/\s/ms', '', $matches[4] ) . ')';
00181             }
00182           }
00183           $tablefields = $this->get_results(
00184             "SELECT column_name AS field,
00185                   data_type AS type,
00186                   column_default AS default,
00187                   is_nullable AS null,
00188                   character_maximum_length AS length,
00189                   numeric_precision
00190                FROM information_schema.columns
00191                WHERE table_name = '{$table}'
00192                ORDER BY ordinal_position;" );
00193 
00194           foreach ( ( array ) $tablefields as $tablefield ) {
00195             if ( array_key_exists( strtolower( $tablefield->field ), $cfields ) ) {
00196               preg_match( '/^(.*) (.*)( |$)/U', $cfields[strtolower( $tablefield->field )], $matches );
00197               $cfieldname = $matches[1];
00198               $cfieldtype = $matches[2];
00199               $fieldtype = $tablefield->type;
00200 
00201               if ( stripos( $fieldtype, 'character' ) === false ) {
00202                 // do nothing
00203               }
00204               else {
00205                 if ( stripos( $fieldtype, 'varying' ) > 0 ) {
00206                   $fieldtype = 'varchar(' . $tablefield->length . ')';
00207                 }
00208                 else {
00209                   $fieldtype = 'char(' . $tablefield->length . ')';
00210                 }
00211               }
00212               if ( stripos( $fieldtype, 'timestamp' ) === false ) {
00213                 // do nothing
00214               }
00215               else {
00216                 $fieldtype = 'timestamp';
00217               }
00218               if ( strtolower( $fieldtype ) != strtolower( $cfieldtype ) ) {
00219                 $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN " . $cfieldname . " TYPE " . $cfieldtype;
00220                 $for_update[$table.'.'.$tablefield->field] = "Changed type of {$table}.{$tablefield->field} from {$tablefield->type} to {$fieldtype}";
00221               }
00222               if ( preg_match( "| DEFAULT ([^ ]*)|i", $cfields[strtolower( $tablefield->field )], $matches ) ) {
00223                 $default_value = $matches[1];
00224                 if ( strpos( '::', $tablefield->default) === false ) {
00225                   $tablefield_default = $tablefield->default;
00226                 }
00227                 else {
00228                   preg_match( '|(.*)::|', $tablefield->default, $matches );
00229                   $tablefield_default = $matches[1] . ( preg_match( '|^nextval|i', $matches[1] ) > 0 ? ')' : '' );
00230                 }
00231                 if ( $tablefield_default != $default_value ) {
00232                   $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->field} SET DEFAULT {$default_value}";
00233                   $for_update[$table.'.'.$tablefield->field] = "Changed default value of {$table}.{$tablefield->field} from {$tablefield->default} to {$default_value}";
00234                 }
00235               }
00236               elseif ( strlen( $tablefield->default) > 0 ) {
00237                 $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->field} DROP DEFAULT";
00238                 $for_update[$table.'.'.$tablefield->field] = "Dropped default value of {$table}.{$tablefield->field}";
00239               }
00240               unset( $cfields[strtolower( $tablefield->field )] );
00241             }
00242             else {
00243               // This field exists in the table, but not in the creation queries?
00244             }
00245           }
00246           foreach ( $cfields as $fieldname => $fielddef ) {
00247             $cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef";
00248             $for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname;
00249           }
00250           $tableindices = $this->get_results(
00251             "SELECT c2.relname AS key_name,
00252                   i.indisprimary AS is_primary,
00253                   i.indisunique AS is_unique,
00254                   i.indisclustered AS is_clustered,
00255                   i.indisvalid AS is_valid,
00256                   pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS index_def,
00257                   c2.reltablespace
00258                FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
00259                   pg_catalog.pg_index i
00260                WHERE c.oid = (
00261                 SELECT c.oid
00262                   FROM pg_catalog.pg_class c
00263                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
00264                   WHERE c.relname = '{$table}'
00265                   AND pg_catalog.pg_table_is_visible(c.oid)
00266                 )
00267                 AND c.oid = i.indrelid AND i.indexrelid = c2.oid
00268                 ORDER BY i.indisprimary DESC, i.indisunique DESC,
00269                   c2.relname;" );
00270 
00271           if ( $tableindices ) {
00272             unset( $index_ary );
00273             $index_ary = array();
00274             foreach ( ( array ) $tableindices as $tableindex ) {
00275               $keyname = $tableindex->key_name;
00276               preg_match( '/\((.*)\)/', $tableindex->index_def, $matches );
00277               $fieldnames = str_replace( '"', '', str_replace( ' ', '', $matches[1] ) );
00278               $index_ary[$keyname]['fieldnames'] = $fieldnames;
00279               $index_ary[$keyname]['unique'] = ( $tableindex->is_unique == true ) ? true : false;
00280               $index_ary[$keyname]['primary'] = ( $tableindex->is_primary == true ) ? true : false;
00281             }
00282 
00283             foreach ( $index_ary as $index_name => $index_data ) {
00284               $index_string = '';
00285               if ( $index_data['primary'] ) {
00286                 $index_string .= 'PRIMARY KEY ';
00287               }
00288               else if ( $index_data['unique'] ) {
00289                 $index_string .= 'UNIQUE ';
00290               }
00291               $index_columns = $index_data['fieldnames'];
00292 
00293               $index_string = rtrim( $index_string, ' ' );
00294               $index_string .= ' (' . $index_columns . ')';
00295               if ( !( ( $aindex = array_search( $index_string, $indices ) ) === false ) ) {
00296                 unset( $indices[$aindex] );
00297                 unset( $indexqueries[$table] );
00298               }
00299               else {
00300                 if ( $index_data['unique'] ) {
00301                   $cqueries[] = "ALTER TABLE {$table} DROP CONSTRAINT {$index_name}";
00302                 }
00303                 else {
00304                   $cqueries[] = "DROP INDEX {$index_name}";
00305                 }
00306               }
00307             }
00308           }
00309           foreach ( $indices as $index ) {
00310             $cqueries[] = "ALTER TABLE {$table} ADD $index";
00311             $for_update[$table . '.' . $fieldname] = 'Added index ' . $table . ' ' . $index;
00312           }
00313           unset( $cqueries[strtolower( $table )] );
00314           unset( $for_update[strtolower( $table )] );
00315         }
00316         else {
00317         }
00318       }
00319     }
00320 
00321     $allqueries = array_merge( $cseqqueries, $cqueries, $alterseqqueries );
00322     if ( $doinserts ) {
00323       $allqueries = array_merge( $allqueries, $iqueries );
00324     }
00325     foreach ( $indexqueries as $tablename => $indexquery ) {
00326       $allqueries = array_merge( $allqueries, ( array ) $indexquery );
00327     }
00328 
00329     if ( $execute ) {
00330       foreach ( $allqueries as $query ) {
00331         if ( !$this->exec( $query ) ) {
00332           $this->get_errors();
00333           return false;
00334         }
00335       }
00336     }
00337 
00338     if ( !$silent ) {
00339       if ( count( $for_update ) > 0 ) {
00340         echo "<ul>\n";
00341         foreach ( $for_update as $upgrade ) {
00342           echo "<li>{$upgrade}</li>\n";
00343         }
00344         echo "</ul>\n";
00345       }
00346       else {
00347         echo "<ul><li>" . _t('No Upgrades') . "</li></ul>";
00348       }
00349     }
00350     return $for_update;
00351   }
00352 
00359   public function upgrade_pre( $old_version, $upgrade_path = '' )
00360   {
00361     return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/pre');
00362   }
00363 
00370   public function upgrade_post( $old_version, $upgrade_path = '' )
00371   {
00372     return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/post');
00373   }
00374 }
00375 ?>

Generated on Sun Aug 4 2013 12:51:44 for Habari by  doxygen 1.7.1