0 AND relname=%s AND nspname=%s END; $res = $this->db->query( sprintf( $q, $this->db->addQuotes( $table ), $this->db->addQuotes( $this->db->getCoreSchema() ) ), __METHOD__ ); if ( !$res ) { return null; } $cols = []; foreach ( $res as $r ) { $cols[] = [ "name" => $r[0], "ord" => $r[1], ]; } return $cols; } protected function describeIndex( $idx ) { // first fetch the key (which is a list of columns ords) and // the table the index applies to (an oid) $q = <<db->query( sprintf( $q, $this->db->addQuotes( $this->db->getCoreSchema() ), $this->db->addQuotes( $idx ) ), __METHOD__ ); if ( !$res ) { return null; } $r = $res->fetchRow(); if ( !$r ) { return null; } $indkey = $r[0]; $relid = intval( $r[1] ); $indkeys = explode( ' ', $indkey ); $colnames = []; foreach ( $indkeys as $rid ) { $query = <<db->query( sprintf( $query, $rid ), __METHOD__ ); if ( !$r2 ) { return null; } $row2 = $r2->fetchRow(); if ( !$row2 ) { return null; } $colnames[] = $row2[0]; } return $colnames; } protected function fkeyDeltype( $fkey ) { $q = <<db->query( sprintf( $q, $this->db->addQuotes( $this->db->getCoreSchema() ), $this->db->addQuotes( $fkey ) ), __METHOD__ ); $row = $r->fetchRow(); if ( !$row ) { return null; } return $row[0]; } protected function ruleDef( $table, $rule ) { $q = <<db->query( sprintf( $q, $this->db->addQuotes( $this->db->getCoreSchema() ), $this->db->addQuotes( $table ), $this->db->addQuotes( $rule ) ), __METHOD__ ); $row = $r->fetchRow(); if ( !$row ) { return null; } $d = $row[0]; return $d; } protected function addSequence( $table, $pkey, $ns ) { if ( !$this->db->sequenceExists( $ns ) ) { $this->output( "Creating sequence $ns\n" ); if ( $pkey !== false ) { $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "CREATE SEQUENCE $ns OWNED BY $table.$pkey", __METHOD__ ); $this->setDefault( $table, $pkey, '"nextval"(\'"' . $ns . '"\'::"regclass")' ); } else { $this->db->query( "CREATE SEQUENCE $ns", __METHOD__ ); } } } protected function dropSequence( $table, $ns ) { if ( $this->db->sequenceExists( $ns ) ) { $this->output( "Dropping sequence $ns\n" ); $this->db->query( "DROP SEQUENCE $ns CASCADE", __METHOD__ ); } } protected function renameSequence( $old, $new ) { if ( $this->db->sequenceExists( $new ) ) { $this->output( "...sequence $new already exists.\n" ); return; } if ( $this->db->sequenceExists( $old ) ) { $this->output( "Renaming sequence $old to $new\n" ); $this->db->query( "ALTER SEQUENCE $old RENAME TO $new", __METHOD__ ); } } protected function setSequenceOwner( $table, $pkey, $seq ) { if ( $this->db->sequenceExists( $seq ) ) { $this->output( "Setting sequence $seq owner to $table.$pkey\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER SEQUENCE $seq OWNED BY $table.$pkey", __METHOD__ ); } } protected function renameTable( $old, $new, $patch = false ) { if ( $this->db->tableExists( $old, __METHOD__ ) ) { $this->output( "Renaming table $old to $new\n" ); $old = $this->db->addIdentifierQuotes( $old ); $new = $this->db->addIdentifierQuotes( $new ); $this->db->query( "ALTER TABLE $old RENAME TO $new", __METHOD__ ); if ( $patch !== false ) { $this->applyPatch( $patch ); } } } protected function renameIndex( $table, $old, $new, $skipBothIndexExistWarning = false, $a = false, $b = false ) { // First requirement: the table must exist if ( !$this->db->tableExists( $table, __METHOD__ ) ) { $this->output( "...skipping: '$table' table doesn't exist yet.\n" ); return true; } // Second requirement: the new index must be missing if ( $this->db->indexExists( $table, $new, __METHOD__ ) ) { $this->output( "...index $new already set on $table table.\n" ); if ( !$skipBothIndexExistWarning && $this->db->indexExists( $table, $old, __METHOD__ ) ) { $this->output( "...WARNING: $old still exists, despite it has been " . "renamed into $new (which also exists).\n" . " $old should be manually removed if not needed anymore.\n" ); } return true; } // Third requirement: the old index must exist if ( !$this->db->indexExists( $table, $old, __METHOD__ ) ) { $this->output( "...skipping: index $old doesn't exist.\n" ); return true; } $this->db->query( "ALTER INDEX $old RENAME TO $new", __METHOD__ ); return true; } protected function dropPgField( $table, $field ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { $this->output( "...$table table does not contain $field field.\n" ); } else { $this->output( "Dropping column '$table.$field'\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table DROP COLUMN $field", __METHOD__ ); } } protected function addPgField( $table, $field, $type ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi !== null ) { $this->output( "...column '$table.$field' already exists\n" ); } else { $this->output( "Adding column '$table.$field'\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table ADD $field $type", __METHOD__ ); } } protected function changeField( $table, $field, $newtype, $default ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { $this->output( "...ERROR: expected column $table.$field to exist\n" ); exit( 1 ); } if ( $fi->type() === strtolower( $newtype ) ) { $this->output( "...column '$table.$field' is already of type '$newtype'\n" ); } else { $this->output( "Changing column type of '$table.$field' from '{$fi->type()}' to '$newtype'\n" ); $table = $this->db->addIdentifierQuotes( $table ); $sql = "ALTER TABLE $table ALTER $field TYPE $newtype"; if ( strlen( $default ) ) { $res = []; if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) { $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]"; $this->db->query( $sqldef, __METHOD__ ); $default = preg_replace( '/\s*DEFAULT .+/', '', $default ); } $sql .= " USING $default"; } $this->db->query( $sql, __METHOD__ ); } } protected function changeFieldPurgeTable( $table, $field, $newtype, $default ) { # # For a cache table, empty it if the field needs to be changed, because the old contents # # may be corrupted. If the column is already the desired type, refrain from purging. $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { $this->output( "...ERROR: expected column $table.$field to exist\n" ); exit( 1 ); } if ( $fi->type() === $newtype ) { $this->output( "...column '$table.$field' is already of type '$newtype'\n" ); } else { $this->output( "Purging data from cache table '$table'\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "DELETE from $table", __METHOD__ ); $this->output( "Changing column type of '$table.$field' from '{$fi->type()}' to '$newtype'\n" ); $sql = "ALTER TABLE $table ALTER $field TYPE $newtype"; if ( strlen( $default ) ) { $res = []; if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) { $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]"; $this->db->query( $sqldef, __METHOD__ ); $default = preg_replace( '/\s*DEFAULT .+/', '', $default ); } $sql .= " USING $default"; } $this->db->query( $sql, __METHOD__ ); } } protected function setDefault( $table, $field, $default ) { $info = $this->db->fieldInfo( $table, $field ); if ( $info && $info->defaultValue() !== $default ) { $this->output( "Changing '$table.$field' default value\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table ALTER $field SET DEFAULT " . $this->db->addQuotes( $default ), __METHOD__ ); } } /** * Drop a default value from a field * @since 1.32 * @param string $table * @param string $field */ protected function dropDefault( $table, $field ) { $info = $this->db->fieldInfo( $table, $field ); if ( $info->defaultValue() !== false ) { $this->output( "Removing '$table.$field' default value\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table ALTER $field DROP DEFAULT", __METHOD__ ); } } protected function changeNullableField( $table, $field, $null, $update = false ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { return; } if ( $fi->isNullable() ) { # # It's NULL - does it need to be NOT NULL? if ( $null === 'NOT NULL' ) { $this->output( "Changing '$table.$field' to not allow NULLs\n" ); $table = $this->db->addIdentifierQuotes( $table ); if ( $update ) { $this->db->query( "UPDATE $table SET $field = DEFAULT WHERE $field IS NULL", __METHOD__ ); } $this->db->query( "ALTER TABLE $table ALTER $field SET NOT NULL", __METHOD__ ); } else { $this->output( "...column '$table.$field' is already set as NULL\n" ); } } else { # # It's NOT NULL - does it need to be NULL? if ( $null === 'NULL' ) { $this->output( "Changing '$table.$field' to allow NULLs\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table ALTER $field DROP NOT NULL", __METHOD__ ); } else { $this->output( "...column '$table.$field' is already set as NOT NULL\n" ); } } } protected function addPgIndex( $table, $index, $type, $unique = false ) { if ( $this->db->indexExists( $table, $index, __METHOD__ ) ) { $this->output( "...index '$index' on table '$table' already exists\n" ); } else { $this->output( "Creating index '$index' on table '$table' $type\n" ); $table = $this->db->addIdentifierQuotes( $table ); $unique = $unique ? 'UNIQUE' : ''; $this->db->query( "CREATE $unique INDEX $index ON $table $type", __METHOD__ ); } } protected function addPgExtIndex( $table, $index, $type ) { if ( $this->db->indexExists( $table, $index, __METHOD__ ) ) { $this->output( "...index '$index' on table '$table' already exists\n" ); } elseif ( preg_match( '/^\(/', $type ) ) { $this->output( "Creating index '$index' on table '$table'\n" ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "CREATE INDEX $index ON $table $type", __METHOD__ ); } else { $this->applyPatch( $type, true, "Creating index '$index' on table '$table'" ); } } /** * Add a value to an existing PostgreSQL enum type * @since 1.31 * @param string $type Type name. Must be in the core schema. * @param string $value Value to add. */ protected function addPgEnumValue( $type, $value ) { $row = $this->db->selectRow( [ 't' => 'pg_catalog.pg_type', 'n' => 'pg_catalog.pg_namespace', 'e' => 'pg_catalog.pg_enum', ], [ 't.typname', 't.typtype', 'e.enumlabel' ], [ 't.typname' => $type, 'n.nspname' => $this->db->getCoreSchema(), ], __METHOD__, [], [ 'n' => [ 'JOIN', 't.typnamespace = n.oid' ], 'e' => [ 'LEFT JOIN', [ 'e.enumtypid = t.oid', 'e.enumlabel' => $value ] ], ] ); if ( !$row ) { $this->output( "...Type $type does not exist, skipping modify enum.\n" ); } elseif ( $row->typtype !== 'e' ) { $this->output( "...Type $type does not seem to be an enum, skipping modify enum.\n" ); } elseif ( $row->enumlabel === $value ) { $this->output( "...Enum type $type already contains value '$value'.\n" ); } else { $this->output( "...Adding value '$value' to enum type $type.\n" ); $etype = $this->db->addIdentifierQuotes( $type ); $evalue = $this->db->addQuotes( $value ); $this->db->query( "ALTER TYPE $etype ADD VALUE $evalue", __METHOD__ ); } } protected function dropFkey( $table, $field ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { $this->output( "WARNING! Column '$table.$field' does not exist but it should! " . "Please report this.\n" ); return; } if ( $this->dropConstraint( $table, $field, 'foreignkey', $fi->conname() ) ) { $this->output( "Dropping foreign key constraint on '$table.$field'\n" ); } else { $this->output( "...foreign key constraint on '$table.$field' already does not exist\n" ); } } protected function changeFkeyDeferrable( $table, $field, $clause ) { $fi = $this->db->fieldInfo( $table, $field ); if ( $fi === null ) { $this->output( "WARNING! Column '$table.$field' does not exist but it should! " . "Please report this.\n" ); return; } if ( $fi->is_deferred() && $fi->is_deferrable() ) { return; } $this->output( "Altering column '$table.$field' to be DEFERRABLE INITIALLY DEFERRED\n" ); $conname = $fi->conname(); $conclause = "CONSTRAINT \"$conname\""; if ( !$this->dropConstraint( $table, $field, 'foreignkey', $conname ) ) { $this->output( "Column '$table.$field' does not have a foreign key " . "constraint, will be added\n" ); $conclause = ""; } $command = "ALTER TABLE $table ADD $conclause " . "FOREIGN KEY ($field) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED"; $this->db->query( $command, __METHOD__ ); } protected function dropPgIndex( $table, $index ) { if ( $this->db->indexExists( $table, $index, __METHOD__ ) ) { $this->output( "Dropping obsolete index '$index'\n" ); $this->db->query( "DROP INDEX \"" . $index . "\"", __METHOD__ ); } } protected function checkIndex( $index, $should_be, $good_def ) { $pu = $this->db->indexAttributes( $index ); if ( !empty( $pu ) && $pu != $should_be ) { $this->output( "Dropping obsolete version of index '$index'\n" ); $this->db->query( "DROP INDEX \"" . $index . "\"", __METHOD__ ); $pu = []; } else { $this->output( "...no need to drop index '$index'\n" ); } if ( empty( $pu ) ) { $this->output( "Creating index '$index'\n" ); $this->db->query( $good_def, __METHOD__ ); } else { $this->output( "...index '$index' exists\n" ); } } protected function changePrimaryKey( $table, $shouldBe, $constraintName = null ) { // https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns $result = $this->db->query( "SELECT a.attname as column " . "FROM pg_index i " . "JOIN pg_attribute a ON a.attrelid = i.indrelid " . "AND a.attnum = ANY(i.indkey) " . "WHERE i.indrelid = '\"$table\"'::regclass " . "AND i.indisprimary", __METHOD__ ); $currentColumns = []; foreach ( $result as $row ) { $currentColumns[] = $row->column; } if ( $currentColumns == $shouldBe ) { $this->output( "...no need to change primary key of '$table'\n" ); return true; } $this->dropConstraint( $table, '', 'primary', $constraintName ); $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table" . " ADD PRIMARY KEY (" . implode( ',', $shouldBe ) . ');', __METHOD__ ); } /** * Drop generic constraint. If the constraint was created with a custom name, * then the name must be queried and supplied as $conname, otherwise standard * system suffixes and format would be assumed. * * @param string $table * @param string $field * @param string $type * @param string|null $conname * @return bool */ protected function dropConstraint( $table, $field, $type, $conname = null ) { if ( $conname === null ) { if ( $type == 'primary' ) { $conname = "{$table}_pkey"; } else { $map = [ 'unique' => 'key', 'check' => 'check', 'foreignkey' => 'fkey' ]; $conname = "{$table}_{$field}_{$map[$type]}"; } } if ( $this->db->constraintExists( $table, $conname ) ) { $table = $this->db->addIdentifierQuotes( $table ); $this->db->query( "ALTER TABLE $table DROP CONSTRAINT $conname;", __METHOD__ ); return true; } return false; } }