function getTableDefPrefix($table, $clean = false)
{
// Fetch table
$t = $this->getTable($table);
if (!is_object($t) || $t->recordCount() != 1) {
$this->rollbackTransaction();
return null;
}
$this->fieldClean($t->fields['relname']);
$this->fieldClean($t->fields['nspname']);
// Fetch attributes
$atts = $this->getTableAttributes($table);
if (!is_object($atts)) {
$this->rollbackTransaction();
return null;
}
// Fetch constraints
$cons = $this->getConstraints($table);
if (!is_object($cons)) {
$this->rollbackTransaction();
return null;
}
// Output a reconnect command to create the table as the correct user
$sql = $this->getChangeUserSQL($t->fields['relowner']) . "\n\n";
// Set schema search path
$sql .= "SET search_path = \"{$t->fields['nspname']}\", pg_catalog;\n\n";
// Begin CREATE TABLE definition
$sql .= "-- Definition\n\n";
// DROP TABLE must be fully qualified in case a table with the same name exists
// in pg_catalog.
if (!$clean) {
$sql .= "-- ";
}
$sql .= "DROP TABLE ";
$sql .= "\"{$t->fields['nspname']}\".\"{$t->fields['relname']}\";\n";
$sql .= "CREATE TABLE \"{$t->fields['nspname']}\".\"{$t->fields['relname']}\" (\n";
// Output all table columns
$col_comments_sql = '';
// Accumulate comments on columns
$num = $atts->recordCount() + $cons->recordCount();
$i = 1;
while (!$atts->EOF) {
$this->fieldClean($atts->fields['attname']);
$sql .= " \"{$atts->fields['attname']}\"";
// Dump SERIAL and BIGSERIAL columns correctly
if ($this->phpBool($atts->fields['attisserial']) && ($atts->fields['type'] == 'integer' || $atts->fields['type'] == 'bigint')) {
if ($atts->fields['type'] == 'integer') {
$sql .= " SERIAL";
} else {
$sql .= " BIGSERIAL";
}
} else {
$sql .= " " . $this->formatType($atts->fields['type'], $atts->fields['atttypmod']);
// Add NOT NULL if necessary
if ($this->phpBool($atts->fields['attnotnull'])) {
$sql .= " NOT NULL";
}
// Add default if necessary
if ($atts->fields['adsrc'] !== null) {
$sql .= " DEFAULT {$atts->fields['adsrc']}";
}
}
// Output comma or not
if ($i < $num) {
$sql .= ",\n";
} else {
$sql .= "\n";
}
// Does this column have a comment?
if ($atts->fields['comment'] !== null) {
$this->clean($atts->fields['comment']);
$col_comments_sql .= "COMMENT ON COLUMN \"{$t->fields['relname']}\".\"{$atts->fields['attname']}\" IS '{$atts->fields['comment']}';\n";
}
$atts->moveNext();
$i++;
}
// Output all table constraints
while (!$cons->EOF) {
$this->fieldClean($cons->fields['conname']);
$sql .= " CONSTRAINT \"{$cons->fields['conname']}\" ";
// Nasty hack to support pre-7.4 PostgreSQL
if ($cons->fields['consrc'] !== null) {
$sql .= $cons->fields['consrc'];
} else {
switch ($cons->fields['contype']) {
case 'p':
$keys = $this->getAttributeNames($table, explode(' ', $cons->fields['indkey']));
$sql .= "PRIMARY KEY (" . join(',', $keys) . ")";
break;
case 'u':
$keys = $this->getAttributeNames($table, explode(' ', $cons->fields['indkey']));
$sql .= "UNIQUE (" . join(',', $keys) . ")";
break;
default:
// Unrecognised constraint
$this->rollbackTransaction();
return null;
}
}
// Output comma or not
if ($i < $num) {
$sql .= ",\n";
} else {
$sql .= "\n";
}
$cons->moveNext();
$i++;
}
$sql .= ")";
// @@@@ DUMP CLUSTERING INFORMATION
// Inherits
/*
* XXX: This is currently commented out as handling inheritance isn't this simple.
* You also need to make sure you don't dump inherited columns and defaults, as well
* as inherited NOT NULL and CHECK constraints. So for the time being, we just do
* not claim to support inheritance.
$parents = $this->getTableParents($table);
if ($parents->recordCount() > 0) {
$sql .= " INHERITS (";
while (!$parents->EOF) {
$this->fieldClean($parents->fields['relname']);
// Qualify the parent table if it's in another schema
if ($parents->fields['schemaname'] != $this->_schema) {
$this->fieldClean($parents->fields['schemaname']);
$sql .= "\"{$parents->fields['schemaname']}\".";
}
$sql .= "\"{$parents->fields['relname']}\"";
$parents->moveNext();
if (!$parents->EOF) $sql .= ', ';
}
$sql .= ")";
}
*/
// Handle WITHOUT OIDS
if ($this->hasObjectID($table)) {
$sql .= " WITH OIDS";
} else {
$sql .= " WITHOUT OIDS";
}
$sql .= ";\n";
// Column storage and statistics
$atts->moveFirst();
$first = true;
while (!$atts->EOF) {
$this->fieldClean($atts->fields['attname']);
// Statistics first
if ($atts->fields['attstattarget'] >= 0) {
if ($first) {
$sql .= "\n";
$first = false;
}
$sql .= "ALTER TABLE ONLY \"{$t->fields['nspname']}\".\"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STATISTICS {$atts->fields['attstattarget']};\n";
}
// Then storage
if ($atts->fields['attstorage'] != $atts->fields['typstorage']) {
switch ($atts->fields['attstorage']) {
case 'p':
$storage = 'PLAIN';
break;
case 'e':
$storage = 'EXTERNAL';
break;
case 'm':
$storage = 'MAIN';
break;
case 'x':
$storage = 'EXTENDED';
break;
default:
// Unknown storage type
$this->rollbackTransaction();
return null;
}
$sql .= "ALTER TABLE ONLY \"{$t->fields['nspname']}\".\"{$t->fields['relname']}\" ALTER COLUMN \"{$atts->fields['attname']}\" SET STORAGE {$storage};\n";
}
$atts->moveNext();
}
// Comment
if ($t->fields['relcomment'] !== null) {
$this->clean($t->fields['relcomment']);
$sql .= "\n-- Comment\n\n";
$sql .= "COMMENT ON TABLE \"{$t->fields['nspname']}\".\"{$t->fields['relname']}\" IS '{$t->fields['relcomment']}';\n";
}
// Add comments on columns, if any
if ($col_comments_sql != '') {
$sql .= $col_comments_sql;
}
// Privileges
$privs = $this->getPrivileges($table, 'table');
if (!is_array($privs)) {
$this->rollbackTransaction();
return null;
}
if (sizeof($privs) > 0) {
$sql .= "\n-- Privileges\n\n";
/*
* Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
* wire-in knowledge about the default public privileges for different
* kinds of objects.
*/
$sql .= "REVOKE ALL ON TABLE \"{$t->fields['nspname']}\".\"{$t->fields['relname']}\" FROM PUBLIC;\n";
foreach ($privs as $v) {
// Get non-GRANT OPTION privs
$nongrant = array_diff($v[2], $v[4]);
// Skip empty or owner ACEs
if (sizeof($v[2]) == 0 || $v[0] == 'user' && $v[1] == $t->fields['relowner']) {
continue;
}
// Change user if necessary
if ($this->hasGrantOption() && $v[3] != $t->fields['relowner']) {
$grantor = $v[3];
$this->clean($grantor);
$sql .= "SET SESSION AUTHORIZATION '{$grantor}';\n";
}
// Output privileges with no GRANT OPTION
$sql .= "GRANT " . join(', ', $nongrant) . " ON TABLE \"{$t->fields['relname']}\" TO ";
switch ($v[0]) {
case 'public':
$sql .= "PUBLIC;\n";
break;
case 'user':
$this->fieldClean($v[1]);
$sql .= "\"{$v[1]}\";\n";
break;
case 'group':
$this->fieldClean($v[1]);
$sql .= "GROUP \"{$v[1]}\";\n";
break;
default:
// Unknown privilege type - fail
$this->rollbackTransaction();
return null;
}
// Reset user if necessary
if ($this->hasGrantOption() && $v[3] != $t->fields['relowner']) {
$sql .= "RESET SESSION AUTHORIZATION;\n";
}
// Output privileges with GRANT OPTION
// Skip empty or owner ACEs
if (!$this->hasGrantOption() || sizeof($v[4]) == 0) {
continue;
}
// Change user if necessary
if ($this->hasGrantOption() && $v[3] != $t->fields['relowner']) {
$grantor = $v[3];
$this->clean($grantor);
$sql .= "SET SESSION AUTHORIZATION '{$grantor}';\n";
}
$sql .= "GRANT " . join(', ', $v[4]) . " ON \"{$t->fields['relname']}\" TO ";
switch ($v[0]) {
case 'public':
$sql .= "PUBLIC";
break;
case 'user':
$this->fieldClean($v[1]);
$sql .= "\"{$v[1]}\"";
break;
case 'group':
$this->fieldClean($v[1]);
$sql .= "GROUP \"{$v[1]}\"";
break;
default:
// Unknown privilege type - fail
return null;
}
$sql .= " WITH GRANT OPTION;\n";
// Reset user if necessary
if ($this->hasGrantOption() && $v[3] != $t->fields['relowner']) {
$sql .= "RESET SESSION AUTHORIZATION;\n";
}
}
}
// Add a newline to separate data that follows (if any)
$sql .= "\n";
return $sql;
}
