00001 <?php
00008 namespace Habari;
00009
00010 class PGSQLConnection extends DatabaseConnection
00011 {
00021 public function connect( $connect_string, $db_user, $db_pass )
00022 {
00023
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
00046
00047
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
00121 }
00122 }
00123
00124
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
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
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
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 ?>