Documentation is available at pg-schema-defs.php
- <?php
- /* ******************************************************************** */
- /* CATALYST PHP Source Code */
- /* -------------------------------------------------------------------- */
- /* This program is free software; you can redistribute it and/or modify */
- /* it under the terms of the GNU General Public License as published by */
- /* the Free Software Foundation; either version 2 of the License, or */
- /* (at your option) any later version. */
- /* */
- /* This program is distributed in the hope that it will be useful, */
- /* but WITHOUT ANY WARRANTY; without even the implied warranty of */
- /* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the */
- /* GNU General Public License for more details. */
- /* */
- /* You should have received a copy of the GNU General Public License */
- /* along with this program; if not, write to: */
- /* The Free Software Foundation, Inc., 59 Temple Place, Suite 330, */
- /* Boston, MA 02111-1307 USA */
- /* -------------------------------------------------------------------- */
- /* */
- /* Filename: pg-schema-defs.php */
- /* Author: Paul Waite */
- /* Description: Definitions for managing Postgres DATABASE SCHEMAS. */
- /* The pgschema class can be used to read in a Postgres */
- /* database table-by-table, or all at once. All field */
- /* attributes are acquired, as well as constraints (eg. */
- /* foreign key references, primary keys etc.), triggers, */
- /* functions, and sequences. */
- /* */
- /* The main use of this class is to read in database info */
- /* for other utilities, such as table maintenance scripts. */
- /* */
- /* ******************************************************************** */
- /** @package database */
- include_once("schema-defs.php");
- // ----------------------------------------------------------------------
- /** Defines a database sequence.
- * @package database
- */
- class DB_dbsequence extends dbsequence {
- /** Sequence start */
- var $start = 1;
- /** Sequence increment */
- var $increment = 1;
- /** Sequence minimum value */
- var $minvalue = 1;
- /** Sequence cache size */
- var $cache = 1;
- /** Sequence max value */
- var $maxvalue;
- // ....................................................................
- function DB_dbsequence(&$schema, $name, $start=1, $inc=1, $min=1, $cache=1, $max=-1) {
- $this->dbsequence($schema, $name);
- $this->start = $start;
- $this->increment = $inc;
- $this->minvalue = $min;
- $this->cache = $cache;
- if ($max != -1) {
- $this->maxvalue = $max;
- }
- }
- // ....................................................................
- /** Get schema info for sequence */
- function getschema() {
- }
- // ....................................................................
- /** Return Postgres SQL required to create this sequence. */
- function create() {
- $s .= "create sequence \"$this->name\"";
- $s .= " start $this->start";
- $s .= " increment $this->increment";
- $s .= " minvalue $this->minvalue";
- $s .= " cache $this->cache";
- if (isset($this->maxvalue)) {
- $s .= " maxvalue $this->maxvalue";
- }
- $s .= ";\n";
- return $s;
- }
- } // class DB_dbsequence
- // ----------------------------------------------------------------------
- /** Defines a database function (procedure).
- * @package database
- */
- class DB_dbfunction extends dbfunction {
- var $volatile = "v";
- var $strict = false;
- // ....................................................................
- function DB_dbfunction(&$schema, $name, $returns="", $src="", $args="", $lang="plpgsql",
- $volatile="v", $strict=false) {
- $this->dbfunction($scheme, $name, $returns, $src, $args, $lang);
- $this->volatile = $volatile;
- $this->strict = $strict;
- }
- // ....................................................................
- /** Obtain function schema information. */
- function getschema() {
- $funQ = "SELECT";
- $funQ .= " p.proisstrict as strict,";
- if ($this->schema->database_version >= 7.3) {
- $funQ .= " p.provolatile as volatile,";
- }
- $funQ .= " p.prosrc as src,";
- $funQ .= " p.proargtypes as argoids,";
- $funQ .= " l.lanname as language,";
- $funQ .= " t.typname as returns";
- $funQ .= " FROM pg_proc p, pg_language l, pg_type t";
- $funQ .= " WHERE p.proname='$this->name'";
- $funQ .= " AND l.oid=p.prolang";
- $funQ .= " AND t.oid=p.prorettype";
- $func = dbrecordset($funQ);
- if ($func->hasdata) {
- $strict = $func->istrue("strict");
- if ($this->schema->database_version >= 7.3) {
- $volatile = $func->field("volatile");
- }
- else {
- $volatile = "v";
- }
- $src = $func->field("src");
- $lang = $func->field("language");
- $returns = $func->field("returns");
- $argoids = explode(" ", $func->field("argoids"));
- $arg_types = array();
- foreach ($argoids as $oid) {
- $oid = trim($oid);
- if ($oid != "") {
- $argQ = dbrecordset("SELECT typname FROM pg_type WHERE oid=$oid");
- if ($argQ->hasdata) {
- $arg_types[] = $argQ->field("typname");
- }
- }
- }
- // Set values..
- $this->set($returns, $src, $arg_types, $lang);
- $this->volatile = $volatile;
- $this->strict = $strict;
- }
- }
- // ....................................................................
- /** Return SQL required to create this function. */
- function create() {
- $s .= "create function $this->name";
- $s .= $this->parameters();
- $s .= " returns $this->return_type";
- $s .= " as '$this->src'";
- $s .= " language '$this->language'";
- switch ($this->volatile) {
- case "i": $s .= " immutable"; break;
- case "s": $s .= " stable"; break;
- }
- if ($this->strict) $s .= " strict";
- $s .= ";\n";
- return $s;
- }
- // ....................................................................
- /** Return SQL to drop this function. */
- function drop() {
- $s .= "drop function $this->name";
- $s .= $this->parameters();
- $s .= " cascade;\n";
- return $s;
- }
- } // class DB_dbfunction
- // ----------------------------------------------------------------------
- /** Defines a database index.
- * @package database
- */
- class DB_dbindex extends dbindex {
- /** True if index is clustered */
- var $clustered = false;
- /** Access method, eg. btree, hash etc. */
- var $access_method = "";
- /** Field numbers of fields in index */
- var $fieldnums = array();
- // ....................................................................
- function DB_dbindex(&$schema, $name, $tablename, $flds="", $primary=false,
- $unique=false, $clustered=false, $access="") {
- $this->dbindex($schema, $name, $tablename, $flds, $primary, $unique);
- $this->clustered = $clustered;
- $this->access_method = $access;
- }
- // ....................................................................
- /** Obtain index schema information.
- * NB: For indexes this only retrieves the fields as numeric offsets
- * in the list of fields on the table the index is for. The $fieldnames
- * array of this index must be populated by the dbtable descendant.
- */
- function getschema() {
- $indQ = "SELECT";
- $indQ .= " i.indexrelid,";
- $indQ .= " i.indrelid,";
- $indQ .= " i.indkey as fieldnums,";
- $indQ .= " i.indisclustered,";
- $indQ .= " i.indisunique,";
- $indQ .= " i.indisprimary,";
- $indQ .= " i.indclass,";
- $indQ .= " pgam.amname";
- $indQ .= " FROM pg_index i,pg_class pgcix,pg_class pgctb,pg_am pgam";
- $indQ .= " WHERE pgcix.relname='$this->name'";
- $indQ .= " AND pgctb.relname='$this->tablename'";
- $indQ .= " AND pgcix.oid=i.indexrelid";
- $indQ .= " AND pgctb.oid=i.indrelid";
- $indQ .= " AND pgam.oid=pgcix.relam";
- $index = dbrecordset($indQ);
- if ($index->hasdata) {
- $this->primary = $index->istrue("indisprimary");
- $this->unique = $index->istrue("indisunique");
- $this->clustered = $index->istrue("indisclustered");
- $this->fieldnums = explode(" ", $index->field("fieldnums"));
- $this->access_method = $index->field("amname");
- }
- }
- // ....................................................................
- /** Return SQL required to create this index. */
- function create() {
- $s = "";
- $s .= "create";
- if ($this->unique) $s .= " unique";
- $s .= " index $this->name on " . $this->tablename;
- if ($this->access_method != "") {
- $s .= " using $this->access_method";
- }
- $s .= " (";
- if (count($this->fieldnames) > 0) {
- $flds = implode(",", $this->fieldnames);
- $s .= $flds;
- }
- $s .= ");\n";
- if ($this->clustered) {
- $s .= "cluster $this->name on $this->tablename;\n";
- }
- return $s;
- }
- // ....................................................................
- // Return SQL to drop this index.
- function drop() {
- $s = "drop index $this->name";
- if ($this->schema->database_version >= 7.3) {
- $s .= " cascade";
- }
- return "$s;\n";
- }
- } // class DB_dbindex
- // ----------------------------------------------------------------------
- /** Defines a database constraint.
- * @package database
- */
- class DB_dbconstraint extends dbconstraint {
- /** Field positions for fields constraint applies to */
- var $fieldnums = array();
- /** Field positions for fields FK constraint refers to */
- var $fk_fieldnums = array();
- /** Table (object) to apply constraint to */
- var $table = "";
- /** Table (object) constraint refers to */
- var $fk_table = "";
- // ....................................................................
- function DB_dbconstraint(&$schema, $name, $type="p", $table="", $fktable="", $flds="",
- $fkflds="", $updact="", $delact="", $match="", $cksrc="") {
- $this->dbconstraint($schema, $name, $type, $table, $fktable, $flds, $fkflds, $updact, $delact, $match, $cksrc);
- }
- // ....................................................................
- /** Obtain constraint schema information. */
- function getschema() {
- // Constraints on this table. These include primary keys, and
- // foreign key references to other tables/fields..
- $conQ = "SELECT * FROM pg_constraint";
- $conQ .= " WHERE conname='$this->name'";
- $constraint = dbrecordset($conQ);
- if ($constraint->hasdata) {
- // Constraint information..
- $conname = $constraint->field("conname");
- $contype = $constraint->field("contype");
- $conrelid = $constraint->field("conrelid");
- $confrelid = $constraint->field("confrelid");
- $conkey = $constraint->field("conkey");
- $confkey = $constraint->field("confkey");
- $confupdtype = $constraint->field("confupdtype");
- $confdeltype = $constraint->field("confdeltype");
- $confmatchtype = $constraint->field("confmatchtype");
- $consrc = $constraint->field("consrc");
- $tablename = "";
- $fk_tablename = "";
- if ($contype == "p" || $contype == "f" || $contype == "c") {
- // Get table for primary or foreign key constraints..
- $tQ = dbrecordset("SELECT relname FROM pg_class WHERE oid=$conrelid");
- if ($tQ->hasdata) {
- $tablename = $tQ->field("relname");
- }
- // Weird data for field numbers to identify the fields.
- // The array of integers is returned inside curly brackets
- // as in: '{1,3}'. So we get rid of the brackets and then
- // unpack the comma-delimited values..
- $conkey = preg_replace("/[\{\}]/", "", $conkey);
- $this->fieldnums = explode(",", $conkey);
- // Get foreign key table for foreign key constraints..
- if ($contype == "f") {
- $tQ = dbrecordset("SELECT relname FROM pg_class WHERE oid=$confrelid");
- if ($tQ->hasdata) {
- $fk_tablename = $tQ->field("relname");
- }
- $confkey = preg_replace("/[\{\}]/", "", $confkey);
- $this->fk_fieldnums = explode(",", $confkey);
- }
- }
- // Set up the vars..
- $this->set(
- $contype,
- $tablename,
- $fk_tablename,
- "",
- "",
- $confupdtype,
- $confdeltype,
- $confmatchtype,
- $consrc
- );
- }
- }
- } // class DB_dbconstraint
- // ----------------------------------------------------------------------
- /** Defines a database trigger.
- * @package database
- */
- class DB_dbtrigger extends dbtrigger {
- // ....................................................................
- function DB_dbtrigger(&$schema, $name, $bitmask=0, $table="", $funcname="", $args="") {
- $this->dbtrigger($schema, $name, $bitmask, $table, $funcname, $args);
- }
- // ....................................................................
- /** Obtain trigger schema information. */
- function getschema() {
- // We are intersted here, in triggers which are only on
- // our user tables, and NOT RI constraints..
- $trgQ = "SELECT";
- $trgQ .= " pgt.tgtype as trigbitmask,";
- $trgQ .= " pgt.tgnargs as numargs,";
- $trgQ .= " pgt.tgargs as args,";
- $trgQ .= " pgc.relname as trigtable,";
- $trgQ .= " pgp.proname as funcname";
- $trgQ .= " FROM pg_trigger pgt, pg_class pgc, pg_proc pgp";
- $trgQ .= " WHERE pgt.tgname='$this->name'";
- $trgQ .= " AND pgc.oid=pgt.tgrelid";
- $trgQ .= " AND pgp.oid=tgfoid";
- $trg = dbrecordset($trgQ);
- if ($trg->hasdata) {
- //$trig->get_last();
- $tablename = $trg->field("trigtable");
- $funcname = $trg->field("funcname");
- if ($trg->field("numargs") > 0) {
- $args = explode("\0", $trg->field("args"));
- }
- else {
- $args = array();
- }
- // Postgres stores trigger setup in a bitmask..
- $bitmask = $trg->field("trigbitmask");
- $eachrow = (($bitmask & 0x01) != 0);
- $before = (($bitmask & 0x02) != 0);
- $oninsert = (($bitmask & 0x04) != 0);
- $ondelete = (($bitmask & 0x08) != 0);
- $onupdate = (($bitmask & 0x10) != 0);
- // Store it all..
- $this->set(
- $before,
- $oninsert,
- $ondelete,
- $onupdate,
- $eachrow,
- $tablename,
- $funcname,
- $args
- );
- }
- }
- // ....................................................................
- /** Return SQL required to create this trigger. */
- function create() {
- $s = "";
- $s .= "create";
- $s .= " trigger $this->name";
- if ($this->before) $s .= " before ";
- else $s .= " after ";
- $event = array();
- if ($this->oninsert) $event[] = "insert";
- if ($this->ondelete) $event[] = "delete";
- if ($this->onupdate) $event[] = "update";
- $s .= implode(" or ", $event);
- $s .= " on " . $this->tablename;
- if ($this->eachrow) $s .= " for each row";
- else $s = " for each statement";
- $s .= " execute procedure $this->funcname";
- $s .= " (";
- if (count($this->args) > 0) {
- foreach ($this->args as $arg) {
- if ($arg != "00") {
- $s .= "'$arg',";
- }
- }
- $s = substr($s, 0, -1);
- }
- $s .= ");\n";
- return $s;
- }
- } // class DB_dbtrigger
- // ----------------------------------------------------------------------
- /** Class describing a database field of a Postgres table.
- * @package database
- */
- class DB_dbfield extends dbfield {
- /** Whether this field is an array of values */
- var $isarray = false;
- // ....................................................................
- function DB_dbfield(&$schema, $name, $num, $type, $default="", $notnull=false, $ispkey=false, $isarray=false) {
- $this->dbfield($schema, $name, $num, $type, $default, $notnull, $ispkey);
- $this->isarray = $isarray;
- }
- // ....................................................................
- /** Dump field description to stdout. */
- function dump() {
- $s = "$this->name $this->type";
- if ($this->default != "") $s .= " DEFAULT $this->default";
- if ($this->notnull) $s .= " NOT NULL";
- if ($this->isarray) $s .= " (array)";
- if ($this->ispkey) $s .= " (pk)";
- return "$s\n";
- }
- // ....................................................................
- /**
- * Return the generic type of the field. The generic types are as
- * follows:
- * text Fixed or varying length strings
- * numeric Integers, real numbers or money
- * datetime Times, dates date-times
- * logical Boolean or bit field (true/false)
- *
- * You should override this method to return the appropriate generic
- * field types from this list, for your database type.
- * NB: Override this function to get schema info per DB type.
- */
- function generic_type() {
- $gtype = "";
- $typematch = array(
- "text" => "text|char|varchar|inet",
- "numeric" => "int2|int4|int8|serial|serial8|float4|float8",
- "datetime" => "abstime|datetime|interval|reltime|timespan|timetz|timestamp|timestamptz",
- "date" => "date",
- "logical" => "bool",
- "" => ".*"
- );
- foreach ($typematch as $gentype => $pattern) {
- if (preg_match("/$pattern/i", $this->type)) {
- $gtype = $gentype;
- break;
- }
- }
- return $gtype;
- }
- // ....................................................................
- /**
- * Return true if the field is of an integer class. We exclude the serial
- * types due to the initial purpose of this method - to determine fields
- * which are standalone integer types, without attached sequences.
- * NB: Override this function to get schema info per DB type.
- */
- function is_integer_class() {
- $pattern = "int2|int4|int8|integer";
- return preg_match("/$pattern/i", $this->type);
- }
- // ....................................................................
- /**
- * Return true if the field is of a 'serial' class. This is a pseudo
- * class of types which encapsulates integer fields which are able
- * to auto-increment themselves when records are inserted.
- * NB: Override this function to get schema info per DB type.
- */
- function is_serial_class() {
- $pattern = "serial";
- return preg_match("/$pattern/i", $this->type);
- }
- } // class DB_dbfield
- // ----------------------------------------------------------------------
- /** Class describing a Postgres database table. Inherits the standard
- * dbtable class properties and methods, but adds in the getschema
- * specifics for acquiring Postgres table info from the metadata, and
- * provides a specific create() method.
- * @package database
- */
- class DB_dbtable extends dbtable {
- // ....................................................................
- /** Construct a table of given name and array of primary key fields.
- * @param string $name The name of the table
- * @param integer $dbversion Optional database version information
- */
- function DB_dbtable(&$schema, $name) {
- $this->dbtable($schema, $name);
- }
- // ....................................................................
- /**
- * Acquires the table fields and constraints which apply to it.
- * NB: This function is apt for Postgres. Over-ride for other db types
- * @param mixed $schema Schema this table is in, or false if n/a
- */
- function getschema($mode=ALL) {
- $this->fields = array();
- $this->pkey = array();
- $q = "SELECT pgc.oid as tableoid,";
- $q .= " pgc.relhaspkey as tablehaspkey,";
- $q .= " pga.attname as fieldname,";
- $q .= " pga.attnum as fieldnum,";
- $q .= " pga.atthasdef as fieldhasdef,";
- $q .= " pga.attnotnull as fieldnotnull,";
- $q .= " pga.attndims as fieldarrdims,";
- $q .= " pgt.typname as fieldtype";
- $q .= " FROM pg_class pgc,pg_attribute pga, pg_type pgt";
- $q .= " WHERE pgc.relname='$this->name'";
- $q .= " AND pgc.relkind='r'";
- $q .= " AND pga.attrelid=pgc.oid";
- $q .= " AND pga.attnum > 0";
- if ($this->schema->database_version >= 7.3) {
- $q .= " AND NOT pga.attisdropped";
- }
- $q .= " AND pgt.oid=pga.atttypid";
- $q .= " ORDER BY pga.attnum";
- $tableinfo = dbrecordset($q);
- if ($tableinfo->hasdata) {
- // Table information..
- $tableoid = $tableinfo->field("tableoid");
- $tablehaspkey = $tableinfo->istrue("tablehaspkey");
- // Table primary key fields..
- $pklist = "";
- if ($tablehaspkey) {
- $q = "SELECT * FROM pg_index";
- $q .= " WHERE indrelid=$tableoid::oid";
- $q .= " AND indisprimary";
- $pk = dbrecordset($q);
- if ($pk->hasdata) {
- $pklist = $pk->field("indkey");
- $this->pkey = explode(" ", $pklist);
- }
- }
- // Field info..
- do {
- // Field information..
- $fieldname = $tableinfo->field("fieldname");
- $fieldnum = $tableinfo->field("fieldnum");
- $fieldtype = $tableinfo->field("fieldtype");
- $fieldisarray = ($tableinfo->field("fieldarrdims") > 0);
- $fieldnotnull = $tableinfo->istrue("fieldnotnull");
- $fieldhasdef = $tableinfo->istrue("fieldhasdef");
- // Field default..
- $fielddefault = "";
- if ($fieldhasdef) {
- $q = "SELECT adsrc as fielddefault FROM pg_attrdef";
- $q .= " WHERE adrelid=$tableoid::oid";
- $q .= " AND adnum=$fieldnum";
- $def = dbrecordset($q);
- if ($def->hasdata) {
- $fielddefault = $def->field("fielddefault");
- }
- }
- // Create field of table..
- $this->newfield(
- $fieldname,
- $fieldnum,
- $fieldtype,
- $fielddefault,
- $fieldnotnull,
- $fieldisarray
- );
- } while ($tableinfo->get_next());
- }
- if ($mode != FIELDS_ONLY) {
- // Table Constraints. We have two different methods for getting
- // the required details: one for Postgres v7.2 and earlier, and
- // one for the much better-designed Postgres v7.3+.
- $this->constraints = array();
- if ($this->schema->database_version < 7.3) {
- // Primary key constraints..
- $q = "SELECT pgi.*,pgci.relname as indexname";
- $q .= " FROM pg_class pgc, pg_class pgci, pg_index pgi";
- $q .= " WHERE pgc.relname='$this->name'";
- $q .= " AND pgi.indrelid=pgc.oid";
- $q .= " AND pgi.indisprimary";
- $q .= " AND pgci.oid=pgi.indexrelid";
- $pks = new dbrecords($q);
- if ($pks->hasdata) {
- debugbr("[$this->name] $pks->rowcount PKs found");
- do {
- $conname = $pks->field("indexname");
- $fieldnums = explode(" ", $pks->field("indkey"));
- $con = new dbconstraint(
- $this->schema,
- $conname,
- "p",
- $this->name,
- "",
- $fieldnums
- );
- // Stash constraint..
- $this->constraints[$conname] = $con;
- debugbr("[$this->name] PK: $conname");
- } while ($pks->get_next());
- }
- else debugbr("[$this->name] no PKs found.");
- if ($mode != PRIMARY_KEY_ONLY) {
- // Check constraints stored on-table..
- $q = "SELECT pgr.*";
- $q .= " FROM pg_class pgc, pg_relcheck pgr";
- $q .= " WHERE pgc.relname='$this->name'";
- $q .= " AND pgr.rcrelid=pgc.oid";
- $checks = new dbrecords($q);
- if ($checks->hasdata) {
- debugbr("[$this->name] $checks->rowcount check constraints found.");
- do {
- $conname = $checks->field("rcname");
- $consrc = $checks->field("rcsrc");
- $conbin = $checks->field("rcbin");
- if (preg_match("/.*?varattno ([0-9]+).*?/", $conbin, $matches)) {
- $attnum = $matches[1];
- $con = new dbconstraint($this->schema, $conname, "c");
- $con->cksrc = $consrc;
- $con->tablename = $this->name;
- // Store constraint..
- $field = $this->getfieldbynum($attnum);
- $field->constraints[$conname] = $con;
- $this->addfield($field);
- debugbr("[$this->name] check constraint on field: $field->name $consrc");
- }
- } while ($checks->get_next());
- }
- // FK constraints are stored globally..
- $q = "SELECT pgt.*, pgp.prosrc";
- $q .= " FROM pg_class pgc, pg_trigger pgt, pg_proc pgp";
- $q .= " WHERE pgc.relname='$this->name'";
- $q .= " AND pgt.tgconstrrelid=pgc.oid";
- $q .= " AND pgt.tgisconstraint";
- $q .= " AND pgp.oid=pgt.tgfoid";
- $trigs = new dbrecords($q);
- if ($trigs->hasdata) {
- debugbr("[$this->name] $trigs->rowcount FK triggers found");
- do {
- // Constraint information..
- $consrc = $trigs->field("prosrc");
- $ribits = explode("_", $consrc);
- if ($ribits[1] == "FKey") {
- // cascade, restrict, setnull or setdefault
- $operation = strtolower($ribits[2]);
- // Only interested in FK type constraints..
- if ($operation != "check") {
- // ins, upd or del
- $event = strtolower($ribits[3]);
- //echo "op=$operation : event=$event\n";
- // Constraint operation & event..
- $cksrc = "!";
- switch ($operation) {
- case "cascade": $act = "c"; break;
- case "setnull": $act = "n"; break;
- case "restrict": $act = "r"; break;
- case "setdefault": $act = "d"; break;
- default: $act = "a";
- }
- // Constraint details..
- $fk_tablename = "!";
- $fieldnums = "!";
- $fk_fieldnums = "!";
- $updact = "!";
- $delact = "!";
- switch ($event) {
- case "upd":
- $updact = $act;
- break;
- case "del":
- $delact = $act;
- break;
- }
- // Get the constraint..
- $conname = $trigs->field("tgconstrname");
- if (isset($this->constraints[$conname])) {
- $con = $this->constraints[$conname];
- }
- else {
- $con = new dbconstraint($this->schema, $conname, "f");
- }
- // PK & FK table, and field numbers..
- $tgargs = $trigs->rawfield("tgargs");
- $argbits = explode("\\000", $tgargs);
- $fk_tablename = trim(strtolower($argbits[2]));
- // Get named foreign key table..
- $fk_table = new DB_dbtable($this->schema, $fk_tablename);
- debugbr("getting Fk table $fk_tablename data pkonly..");
- $fk_table->getschema(PRIMARY_KEY_ONLY);
- $fieldnums = array();
- $fieldnames = array();
- $fk_fieldnums = array();
- $fk_fieldnames = array();
- for ($ix=4; $ix < count($argbits); $ix+=2) {
- $fname = trim(strtolower($argbits[$ix]));
- $fk_fname = trim(strtolower($argbits[$ix + 1]));
- if ($fname != "" && $fk_fname != "") {
- $fieldnames[] = $fname;
- $fieldnums[] = $this->getfieldnum($fname);
- $fk_fieldnames[] = $fk_fname;
- $fk_fieldnums[] = $fk_table->getfieldnum($fk_fname);
- }
- }
- // Deferred settings..
- $deferrable = $trigs->istrue("tgdeferrable");
- $deferred = $trigs->istrue("tginitdeferred");
- // Set constraint vars..
- $con->set(
- "f",
- $this->name,
- $fk_tablename,
- $fieldnames,
- $fk_fieldnames,
- $updact,
- $delact,
- "", // match type
- $cksrc,
- $deferrable,
- $deferred
- );
- $con->fieldnums = $fieldnums;
- $con->fk_fieldnums = $fk_fieldnums;
- // Stash constraint back in table..
- $this->constraints[$conname] = $con;
- debugbr("[$this->name] FK: ##" . $con->create() . "##");
- }
- } // if foreign key
- } while ($trigs->get_next());
- }
- else debugbr("[$this->name] no FKs found.");
- } // pkonly
- }
- else {
- // Constraints on this table. These include primary keys, and
- // foreign key references to other tables/fields..
- $q = "SELECT pcon.conname FROM pg_constraint pcon, pg_class pgc";
- $q .= " WHERE pgc.oid=pcon.conrelid";
- $q .= " AND pgc.relname='$this->name'";
- $constraints = dbrecordset($q);
- if ($constraints->hasdata) {
- do {
- // Constraint..
- $con = new DB_dbconstraint($this->schema, $constraints->field("conname"));
- $con->getschema();
- if ($con->type == "p" || ($mode != PRIMARY_KEY_ONLY && $con->type == "f")) {
- $con->fieldnames = array();
- if (isset($con->fieldnums) && count($con->fieldnums) > 0) {
- foreach ($con->fieldnums as $num) {
- $field = $this->getfieldbynum($num);
- if ($field !== false) {
- $con->fieldnames[] = $field->name;
- }
- }
- }
- }
- // Get foreign key table for foreign key constraints..
- if ($mode != PRIMARY_KEY_ONLY && $con->type == "f") {
- $fk_table = new DB_dbtable($this->schema, $con->fk_tablename);
- $fk_table->getschema(FIELDS_ONLY);
- $con->fk_fieldnames = array();
- if (isset($con->fk_fieldnums) && count($con->fk_fieldnums) > 0) {
- foreach ($con->fk_fieldnums as $num) {
- $field = $fk_table->getfieldbynum($num);
- if ($field !== false) {
- $con->fk_fieldnames[] = $field->name;
- }
- }
- }
- }
- // Add table constraint..
- $this->constraints[$con->name] = $con;
- } while ($constraints->get_next());
- } // have constraints
- }
- // Table indexes..
- // Only want indexes on table which belong to the user, and
- // which are not Postgres-created tables..
- $indQ = "SELECT";
- $indQ .= " pgcix.relname as indexname";
- $indQ .= " FROM pg_index i,pg_class pgcix,pg_class pgctb";
- $indQ .= " WHERE pgctb.relname='$this->name'";
- $indQ .= " AND i.indrelid=pgctb.oid";
- $indQ .= " AND pgcix.oid=i.indexrelid";
- $indexes = dbrecordset($indQ);
- if ($indexes->hasdata) {
- do {
- $index = new DB_dbindex($this->schema, $indexes->field("indexname"), $this->name);
- $index->getschema();
- $fieldnames = array();
- foreach ($index->fieldnums as $fieldnum) {
- $field = $this->getfieldbynum($fieldnum);
- $fieldnames[] = $field->name;
- }
- $index->fieldnames = $fieldnames;
- $this->indexes[$index->name] = $index;
- } while ($indexes->get_next());
- }
- } // fields_only
- }
- // ....................................................................
- /** Create a new field in the table with given parameters. */
- function newfield($name, $num, $type, $default="", $notnull=false, $isarray=false) {
- $ispkey = (in_array($num, $this->pkey));
- $this->fields[$name] =
- new DB_dbfield(
- $this->schema,
- $name,
- $num,
- $type,
- $default,
- $notnull,
- $ispkey,
- $isarray
- );
- }
- // ....................................................................
- /** Return the SQL which will create this Postgres table.
- * NB: We also create the indexes and constraints which pertain to
- * this table at the same time.
- */
- function create() {
- $s = "";
- $s .= "create table $this->name (\n";
- foreach ($this->fields as $field) {
- $s .= $field->create() . ",\n";
- }
- if ($this->schema->database_version < 7.3 && count($this->pkey > 0)) {
- $s .= " constraint pk_$this->name primary key (";
- foreach ($this->fields as $field) {
- if ($field->ispkey) {
- $s .= "$field->name,";
- }
- }
- $s = substr($s, 0, -1);
- $s .= ")\n";
- }
- else {
- $s = substr($s, 0, -2);
- }
- $s .= "\n);\n";
- // Indexes..
- $s .= $this->create_indexes();
- return $s;
- }
- // ....................................................................
- /** Return SQL which will create a column in this table. The $column
- * passed in is actually a field object.
- */
- function addcolumn($column) {
- $s = "alter table $this->name\n";
- $s .= " add column $column->name $column->type;\n";
- if ($column->default != "") {
- $s .= $this->setdefault($column);
- }
- if ($column->notnull) {
- if ($this->schema->database_version < 7.3) {
- $s .= "UPDATE pg_attribute SET attnotnull=true";
- $s .= " WHERE attname='$column->name'";
- $s .= " AND attrelid = (SELECT oid FROM pg_class WHERE relname='$this->name');\n";
- }
- else {
- $s .= $this->setnullconstraint($column);
- }
- }
- if (count($column->constraints) > 0) {
- foreach ($column->constraints as $con) {
- $s .= $con->create() . "\n";
- }
- }
- return $s;
- }
- // ....................................................................
- /** Return SQL to set the NULL/NOT NULL constraint.. */
- function setnullconstraint($column) {
- if ($this->schema->database_version < 7.3) {
- $nullsetting = ($column->notnull) ? "true" : "false";
- $s .= "update pg_attribute set attnotnull=$nullsetting";
- $s .= " where attname='$column->name'";
- $s .= " and attrelid = (select oid from pg_class where relname='$this->name');\n";
- }
- else {
- $s = "alter table $this->name\n";
- $s .= " alter column $column->name";
- if ($column->notnull) {
- $s .= " set not null;\n";
- }
- else {
- $s .= " drop not null;\n";
- }
- }
- return $s;
- }
- // ....................................................................
- /** Return the SQL which will create the constraints on this table.
- * Usually this SQL has to come after all table creates have been
- * done, so that references to tables are all honoured, hence a
- * separate method here.
- */
- function create_constraints() {
- $s = "";
- foreach ($this->constraints as $conname => $con) {
- $s .= $con->create();
- }
- return $s;
- }
- // ....................................................................
- /** Return the SQL which will create the indexes on this table.
- */
- function create_indexes() {
- $s = "";
- foreach ($this->indexes as $indexname => $index) {
- if ( !($this->schema->capable_of("unique_index_with_constraint") && ($index->unique || $index->primary)) ) {
- $s .= $index->create();
- }
- }
- return $s;
- }
- } // class DB_dbtable
- // ----------------------------------------------------------------------
- /**
- * Class describing a Postgres database schema.
- * @package database
- */
- class DB_schema extends schema {
- // ....................................................................
- /**
- * Create a schema (database) of given name. The name should be a
- * valid existing database name that is currently connected.
- */
- function DB_schema($name) {
- $this->schema($name);
- }
- // ....................................................................
- /**
- * Return database capabilities. There are specific capabilities which
- * the diff code needs to query, and this method should be overridden
- * in the specific database module to answer those questions.
- */
- function capable_of($capability="") {
- $cando = false;
- switch ($capability) {
- // Supports the ALTER <tablename> DROP <colname> SQL
- // statement to remove table columns.
- case "alter_table_drop_column":
- $cando = ($this->database_version >= 7.3);
- break;
- // Supports functions or stored procedures.
- case "stored_procedures":
- $cando = true;
- break;
- // Can define check constraints on table columns.
- case "check_constraints":
- $cando = true;
- break;
- // Can define RI constraints between table/columns to
- // support foreign-keys.
- case "RI_constraints":
- $cando = true;
- break;
- // Supports indexes on table columns.
- case "indexes":
- $cando = true;
- break;
- // Unique indexes are auto-generated with unique constraints. Ie.
- // when a primary key constraint is added to a table a unique
- // index is automatically built for it.
- case "unique_index_with_constraint":
- $cando = true;
- break;
- // Supports triggers on table update, delete, insert.
- case "triggers":
- $cando = true;
- break;
- // Supports named sequences.
- case "named_sequences":
- $cando = true;
- break;
- default:
- $cando = false;
- }
- return $cando;
- }
- // ....................................................................
- /**
- * Populates our array of tables with all tables in this schema.
- */
- function gettables() {
- $tQ = "SELECT relname";
- $tQ .= " FROM pg_class";
- $tQ .= " WHERE relkind='r'";
- $tQ .= " AND relname !~* '^pg_'";
- $tQ .= " ORDER BY relname";
- $this->tables = array();
- $tables = dbrecordset($tQ);
- if ($tables->hasdata) {
- do {
- $table = new DB_dbtable($this, $tables->field("relname"));
- $table->getschema();
- debugbr(">>ADDING $table->name", DBG_DEBUG);
- $this->addtable($table);
- } while ($tables->get_next());
- }
- }
- // ....................................................................
- /**
- * Populates our array of triggers with all user triggers in this schema.
- */
- function gettriggers() {
- $trgQ = "SELECT tgname";
- $trgQ .= " FROM pg_trigger";
- $trgQ .= " WHERE tgname !~* 'pg_'";
- $trgQ .= " AND NOT tgisconstraint";
- $trgQ .= " AND tgenabled";
- $this->triggers = array();
- $trigs = dbrecordset($trgQ);
- if ($trigs->hasdata) {
- do {
- $trig = new DB_dbtrigger($this, $trigs->field("tgname"));
- $trig->getschema();
- $this->addtrigger($trig);
- } while ($trigs->get_next());
- }
- }
- // ....................................................................
- /**
- * Populates our array of functions with all user functions in this schema.
- */
- function getfunctions() {
- $funQ = "SELECT proname";
- $funQ .= " FROM pg_proc";
- $funQ .= " WHERE proowner > 31";
- $this->functions = array();
- $funcs = dbrecordset($funQ);
- if ($funcs->hasdata) {
- do {
- $func = new DB_dbfunction($this, $funcs->field("proname"));
- $func->getschema();
- $this->addfunction($func);
- } while ($funcs->get_next());
- }
- }
- // ....................................................................
- /**
- * Populates our array of sequences with all user sequences in this schema.
- */
- function getsequences() {
- $seqQ = "SELECT relname";
- $seqQ .= " FROM pg_class";
- $seqQ .= " WHERE relkind='S'";
- $seqQ .= " AND relowner > 31";
- $this->sequences = array();
- $seqs = dbrecordset($seqQ);
- if ($seqs->hasdata) {
- do {
- $seq = new DB_dbsequence($this, $seqs->field("relname"));
- $seq->getschema();
- $this->addsequence($seq);
- } while ($seqs->get_next());
- }
- }
- // ....................................................................
- /**
- * Acquire the schema details of a specific Postgres table. This method
- * is provided to cater for the common requirement of acquiring details
- * for a specific table, without having to endure the overhead of reading
- * all of the database schema metadata to get it.
- *
- * @param string $tablename Name of the table to acquire schema of
- */
- function getschema_table($tablename) {
- if (!isset($this->tables[$tablename])) {
- $table = new DB_dbtable($this, $tablename);
- $table->getschema();
- $this->addtable($table);
- }
- } // get
- // ....................................................................
- /** Acquire the Postgres database version. We make some gross assumptions
- * here with regard to standard local Postgres setup. Change as necessary.
- * Currently we have a shot at either the Debian standard location which
- * is '/usr/lib/postgresql', or the 'other' standard of '/usr/bin'.
- * To-do: acquire Postgres binaries location in a more robust way.
- */
- function getversion() {
- $vstr = 7.3;
- $pg_bin = "";
- $pg_paths = array(
- "/usr/lib/postgresql/bin",
- "/usr/bin",
- "/usr/local/pgsql/bin"
- );
- // Find Postgres executable directory..
- foreach ($pg_paths as $pg_path) {
- if (file_exists("$pg_path/pg_ctl")) {
- $pg_bin = $pg_path;
- break;
- }
- }
- // Get the database version..
- if (file_exists("$pg_bin/pg_config")) {
- $vbits = explode(" ", shell_exec("$pg_bin/pg_config --version"));
- if ($vbits[1] != "") {
- $vstr = $vbits[1];
- }
- }
- elseif (file_exists("$pg_bin/psql")) {
- $vbits = explode(" ", shell_exec("$pg_bin/psql --version"));
- if ($vbits[2] != "") {
- $vvbits = explode(".", $vbits[2]);
- $vstr = $vvbits[0] . "." . (isset($vvbits[1]) ? $vvbits[1] : "0");
- }
- }
- $this->set_dbversion( (float) $vstr );
- debugbr("Postgres database version set to $this->database_version");
- return $this->database_version;
- }
- } // class DB_schema
- // ----------------------------------------------------------------------
- ?>
Documentation generated by phpDocumentor 1.3.0RC3