Source for file pg-schema-defs.php

Documentation is available at pg-schema-defs.php

  1. <?php
  2. /* ******************************************************************** */
  3. /* CATALYST PHP Source Code */
  4. /* -------------------------------------------------------------------- */
  5. /* This program is free software; you can redistribute it and/or modify */
  6. /* it under the terms of the GNU General Public License as published by */
  7. /* the Free Software Foundation; either version 2 of the License, or */
  8. /* (at your option) any later version. */
  9. /* */
  10. /* This program is distributed in the hope that it will be useful, */
  11. /* but WITHOUT ANY WARRANTY; without even the implied warranty of */
  12. /* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the */
  13. /* GNU General Public License for more details. */
  14. /* */
  15. /* You should have received a copy of the GNU General Public License */
  16. /* along with this program; if not, write to: */
  17. /* The Free Software Foundation, Inc., 59 Temple Place, Suite 330, */
  18. /* Boston, MA 02111-1307 USA */
  19. /* -------------------------------------------------------------------- */
  20. /* */
  21. /* Filename: pg-schema-defs.php */
  22. /* Author: Paul Waite */
  23. /* Description: Definitions for managing Postgres DATABASE SCHEMAS. */
  24. /* The pgschema class can be used to read in a Postgres */
  25. /* database table-by-table, or all at once. All field */
  26. /* attributes are acquired, as well as constraints (eg. */
  27. /* foreign key references, primary keys etc.), triggers, */
  28. /* functions, and sequences. */
  29. /* */
  30. /* The main use of this class is to read in database info */
  31. /* for other utilities, such as table maintenance scripts. */
  32. /* */
  33. /* ******************************************************************** */
  34. /** @package database */
  35. include_once("schema-defs.php");
  36.  
  37. // ----------------------------------------------------------------------
  38. /** Defines a database sequence.
  39. * @package database
  40. */
  41. class DB_dbsequence extends dbsequence {
  42. /** Sequence start */
  43.  
  44. var $start = 1;
  45. /** Sequence increment */
  46.  
  47. var $increment = 1;
  48. /** Sequence minimum value */
  49.  
  50. var $minvalue = 1;
  51. /** Sequence cache size */
  52.  
  53. var $cache = 1;
  54. /** Sequence max value */
  55.  
  56. var $maxvalue;
  57. // ....................................................................
  58. function DB_dbsequence(&$schema, $name, $start=1, $inc=1, $min=1, $cache=1, $max=-1) {
  59. $this->dbsequence($schema, $name);
  60. $this->start = $start;
  61. $this->increment = $inc;
  62. $this->minvalue = $min;
  63. $this->cache = $cache;
  64. if ($max != -1) {
  65. $this->maxvalue = $max;
  66. }
  67. }
  68. // ....................................................................
  69. /** Get schema info for sequence */
  70.  
  71. function getschema() {
  72. }
  73. // ....................................................................
  74. /** Return Postgres SQL required to create this sequence. */
  75.  
  76. function create() {
  77. $s .= "create sequence \"$this->name\"";
  78. $s .= " start $this->start";
  79. $s .= " increment $this->increment";
  80. $s .= " minvalue $this->minvalue";
  81. $s .= " cache $this->cache";
  82. if (isset($this->maxvalue)) {
  83. $s .= " maxvalue $this->maxvalue";
  84. }
  85. $s .= ";\n";
  86. return $s;
  87. }
  88. } // class DB_dbsequence
  89. // ----------------------------------------------------------------------
  90.  
  91. /** Defines a database function (procedure).
  92. * @package database
  93. */
  94. class DB_dbfunction extends dbfunction {
  95. var $volatile = "v";
  96. var $strict = false;
  97. // ....................................................................
  98. function DB_dbfunction(&$schema, $name, $returns="", $src="", $args="", $lang="plpgsql",
  99. $volatile="v", $strict=false) {
  100. $this->dbfunction($scheme, $name, $returns, $src, $args, $lang);
  101. $this->volatile = $volatile;
  102. $this->strict = $strict;
  103. }
  104. // ....................................................................
  105. /** Obtain function schema information. */
  106.  
  107. function getschema() {
  108. $funQ = "SELECT";
  109. $funQ .= " p.proisstrict as strict,";
  110. if ($this->schema->database_version >= 7.3) {
  111. $funQ .= " p.provolatile as volatile,";
  112. }
  113. $funQ .= " p.prosrc as src,";
  114. $funQ .= " p.proargtypes as argoids,";
  115. $funQ .= " l.lanname as language,";
  116. $funQ .= " t.typname as returns";
  117. $funQ .= " FROM pg_proc p, pg_language l, pg_type t";
  118. $funQ .= " WHERE p.proname='$this->name'";
  119. $funQ .= " AND l.oid=p.prolang";
  120. $funQ .= " AND t.oid=p.prorettype";
  121. $func = dbrecordset($funQ);
  122. if ($func->hasdata) {
  123. $strict = $func->istrue("strict");
  124. if ($this->schema->database_version >= 7.3) {
  125. $volatile = $func->field("volatile");
  126. }
  127. else {
  128. $volatile = "v";
  129. }
  130. $src = $func->field("src");
  131. $lang = $func->field("language");
  132. $returns = $func->field("returns");
  133. $argoids = explode(" ", $func->field("argoids"));
  134. $arg_types = array();
  135. foreach ($argoids as $oid) {
  136. $oid = trim($oid);
  137. if ($oid != "") {
  138. $argQ = dbrecordset("SELECT typname FROM pg_type WHERE oid=$oid");
  139. if ($argQ->hasdata) {
  140. $arg_types[] = $argQ->field("typname");
  141. }
  142. }
  143. }
  144. // Set values..
  145. $this->set($returns, $src, $arg_types, $lang);
  146. $this->volatile = $volatile;
  147. $this->strict = $strict;
  148. }
  149. }
  150. // ....................................................................
  151. /** Return SQL required to create this function. */
  152.  
  153. function create() {
  154. $s .= "create function $this->name";
  155. $s .= $this->parameters();
  156. $s .= " returns $this->return_type";
  157. $s .= " as '$this->src'";
  158. $s .= " language '$this->language'";
  159. switch ($this->volatile) {
  160. case "i": $s .= " immutable"; break;
  161. case "s": $s .= " stable"; break;
  162. }
  163. if ($this->strict) $s .= " strict";
  164. $s .= ";\n";
  165. return $s;
  166. }
  167. // ....................................................................
  168. /** Return SQL to drop this function. */
  169.  
  170. function drop() {
  171. $s .= "drop function $this->name";
  172. $s .= $this->parameters();
  173. $s .= " cascade;\n";
  174. return $s;
  175. }
  176. } // class DB_dbfunction
  177. // ----------------------------------------------------------------------
  178.  
  179. /** Defines a database index.
  180. * @package database
  181. */
  182. class DB_dbindex extends dbindex {
  183. /** True if index is clustered */
  184.  
  185. var $clustered = false;
  186. /** Access method, eg. btree, hash etc. */
  187.  
  188. var $access_method = "";
  189. /** Field numbers of fields in index */
  190.  
  191. var $fieldnums = array();
  192. // ....................................................................
  193. function DB_dbindex(&$schema, $name, $tablename, $flds="", $primary=false,
  194. $unique=false, $clustered=false, $access="") {
  195. $this->dbindex($schema, $name, $tablename, $flds, $primary, $unique);
  196. $this->clustered = $clustered;
  197. $this->access_method = $access;
  198. }
  199.  
  200. // ....................................................................
  201. /** Obtain index schema information.
  202. * NB: For indexes this only retrieves the fields as numeric offsets
  203. * in the list of fields on the table the index is for. The $fieldnames
  204. * array of this index must be populated by the dbtable descendant.
  205. */
  206. function getschema() {
  207. $indQ = "SELECT";
  208. $indQ .= " i.indexrelid,";
  209. $indQ .= " i.indrelid,";
  210. $indQ .= " i.indkey as fieldnums,";
  211. $indQ .= " i.indisclustered,";
  212. $indQ .= " i.indisunique,";
  213. $indQ .= " i.indisprimary,";
  214. $indQ .= " i.indclass,";
  215. $indQ .= " pgam.amname";
  216. $indQ .= " FROM pg_index i,pg_class pgcix,pg_class pgctb,pg_am pgam";
  217. $indQ .= " WHERE pgcix.relname='$this->name'";
  218. $indQ .= " AND pgctb.relname='$this->tablename'";
  219. $indQ .= " AND pgcix.oid=i.indexrelid";
  220. $indQ .= " AND pgctb.oid=i.indrelid";
  221. $indQ .= " AND pgam.oid=pgcix.relam";
  222. $index = dbrecordset($indQ);
  223. if ($index->hasdata) {
  224. $this->primary = $index->istrue("indisprimary");
  225. $this->unique = $index->istrue("indisunique");
  226. $this->clustered = $index->istrue("indisclustered");
  227. $this->fieldnums = explode(" ", $index->field("fieldnums"));
  228. $this->access_method = $index->field("amname");
  229. }
  230. }
  231.  
  232. // ....................................................................
  233. /** Return SQL required to create this index. */
  234.  
  235. function create() {
  236. $s = "";
  237. $s .= "create";
  238. if ($this->unique) $s .= " unique";
  239. $s .= " index $this->name on " . $this->tablename;
  240. if ($this->access_method != "") {
  241. $s .= " using $this->access_method";
  242. }
  243. $s .= " (";
  244. if (count($this->fieldnames) > 0) {
  245. $flds = implode(",", $this->fieldnames);
  246. $s .= $flds;
  247. }
  248. $s .= ");\n";
  249. if ($this->clustered) {
  250. $s .= "cluster $this->name on $this->tablename;\n";
  251. }
  252. return $s;
  253. }
  254.  
  255. // ....................................................................
  256. // Return SQL to drop this index.
  257. function drop() {
  258. $s = "drop index $this->name";
  259. if ($this->schema->database_version >= 7.3) {
  260. $s .= " cascade";
  261. }
  262. return "$s;\n";
  263. }
  264.  
  265. } // class DB_dbindex
  266. // ----------------------------------------------------------------------
  267.  
  268. /** Defines a database constraint.
  269. * @package database
  270. */
  271. class DB_dbconstraint extends dbconstraint {
  272. /** Field positions for fields constraint applies to */
  273.  
  274. var $fieldnums = array();
  275. /** Field positions for fields FK constraint refers to */
  276.  
  277. var $fk_fieldnums = array();
  278. /** Table (object) to apply constraint to */
  279.  
  280. var $table = "";
  281. /** Table (object) constraint refers to */
  282.  
  283. var $fk_table = "";
  284. // ....................................................................
  285. function DB_dbconstraint(&$schema, $name, $type="p", $table="", $fktable="", $flds="",
  286. $fkflds="", $updact="", $delact="", $match="", $cksrc="") {
  287. $this->dbconstraint($schema, $name, $type, $table, $fktable, $flds, $fkflds, $updact, $delact, $match, $cksrc);
  288. }
  289. // ....................................................................
  290. /** Obtain constraint schema information. */
  291.  
  292. function getschema() {
  293. // Constraints on this table. These include primary keys, and
  294. // foreign key references to other tables/fields..
  295. $conQ = "SELECT * FROM pg_constraint";
  296. $conQ .= " WHERE conname='$this->name'";
  297. $constraint = dbrecordset($conQ);
  298. if ($constraint->hasdata) {
  299. // Constraint information..
  300. $conname = $constraint->field("conname");
  301. $contype = $constraint->field("contype");
  302. $conrelid = $constraint->field("conrelid");
  303. $confrelid = $constraint->field("confrelid");
  304. $conkey = $constraint->field("conkey");
  305. $confkey = $constraint->field("confkey");
  306. $confupdtype = $constraint->field("confupdtype");
  307. $confdeltype = $constraint->field("confdeltype");
  308. $confmatchtype = $constraint->field("confmatchtype");
  309. $consrc = $constraint->field("consrc");
  310.  
  311. $tablename = "";
  312. $fk_tablename = "";
  313. if ($contype == "p" || $contype == "f" || $contype == "c") {
  314. // Get table for primary or foreign key constraints..
  315. $tQ = dbrecordset("SELECT relname FROM pg_class WHERE oid=$conrelid");
  316. if ($tQ->hasdata) {
  317. $tablename = $tQ->field("relname");
  318. }
  319. // Weird data for field numbers to identify the fields.
  320. // The array of integers is returned inside curly brackets
  321. // as in: '{1,3}'. So we get rid of the brackets and then
  322. // unpack the comma-delimited values..
  323. $conkey = preg_replace("/[\{\}]/", "", $conkey);
  324. $this->fieldnums = explode(",", $conkey);
  325.  
  326. // Get foreign key table for foreign key constraints..
  327. if ($contype == "f") {
  328. $tQ = dbrecordset("SELECT relname FROM pg_class WHERE oid=$confrelid");
  329. if ($tQ->hasdata) {
  330. $fk_tablename = $tQ->field("relname");
  331. }
  332. $confkey = preg_replace("/[\{\}]/", "", $confkey);
  333. $this->fk_fieldnums = explode(",", $confkey);
  334. }
  335. }
  336. // Set up the vars..
  337. $this->set(
  338. $contype,
  339. $tablename,
  340. $fk_tablename,
  341. "",
  342. "",
  343. $confupdtype,
  344. $confdeltype,
  345. $confmatchtype,
  346. $consrc
  347. );
  348. }
  349. }
  350. } // class DB_dbconstraint
  351. // ----------------------------------------------------------------------
  352.  
  353. /** Defines a database trigger.
  354. * @package database
  355. */
  356. class DB_dbtrigger extends dbtrigger {
  357. // ....................................................................
  358. function DB_dbtrigger(&$schema, $name, $bitmask=0, $table="", $funcname="", $args="") {
  359. $this->dbtrigger($schema, $name, $bitmask, $table, $funcname, $args);
  360. }
  361. // ....................................................................
  362. /** Obtain trigger schema information. */
  363.  
  364. function getschema() {
  365. // We are intersted here, in triggers which are only on
  366. // our user tables, and NOT RI constraints..
  367. $trgQ = "SELECT";
  368. $trgQ .= " pgt.tgtype as trigbitmask,";
  369. $trgQ .= " pgt.tgnargs as numargs,";
  370. $trgQ .= " pgt.tgargs as args,";
  371. $trgQ .= " pgc.relname as trigtable,";
  372. $trgQ .= " pgp.proname as funcname";
  373. $trgQ .= " FROM pg_trigger pgt, pg_class pgc, pg_proc pgp";
  374. $trgQ .= " WHERE pgt.tgname='$this->name'";
  375. $trgQ .= " AND pgc.oid=pgt.tgrelid";
  376. $trgQ .= " AND pgp.oid=tgfoid";
  377. $trg = dbrecordset($trgQ);
  378. if ($trg->hasdata) {
  379. //$trig->get_last();
  380. $tablename = $trg->field("trigtable");
  381. $funcname = $trg->field("funcname");
  382. if ($trg->field("numargs") > 0) {
  383. $args = explode("\0", $trg->field("args"));
  384. }
  385. else {
  386. $args = array();
  387. }
  388. // Postgres stores trigger setup in a bitmask..
  389. $bitmask = $trg->field("trigbitmask");
  390. $eachrow = (($bitmask & 0x01) != 0);
  391. $before = (($bitmask & 0x02) != 0);
  392. $oninsert = (($bitmask & 0x04) != 0);
  393. $ondelete = (($bitmask & 0x08) != 0);
  394. $onupdate = (($bitmask & 0x10) != 0);
  395.  
  396. // Store it all..
  397. $this->set(
  398. $before,
  399. $oninsert,
  400. $ondelete,
  401. $onupdate,
  402. $eachrow,
  403. $tablename,
  404. $funcname,
  405. $args
  406. );
  407. }
  408. }
  409. // ....................................................................
  410. /** Return SQL required to create this trigger. */
  411.  
  412. function create() {
  413. $s = "";
  414. $s .= "create";
  415. $s .= " trigger $this->name";
  416. if ($this->before) $s .= " before ";
  417. else $s .= " after ";
  418. $event = array();
  419. if ($this->oninsert) $event[] = "insert";
  420. if ($this->ondelete) $event[] = "delete";
  421. if ($this->onupdate) $event[] = "update";
  422. $s .= implode(" or ", $event);
  423. $s .= " on " . $this->tablename;
  424. if ($this->eachrow) $s .= " for each row";
  425. else $s = " for each statement";
  426. $s .= " execute procedure $this->funcname";
  427. $s .= " (";
  428. if (count($this->args) > 0) {
  429. foreach ($this->args as $arg) {
  430. if ($arg != "00") {
  431. $s .= "'$arg',";
  432. }
  433. }
  434. $s = substr($s, 0, -1);
  435. }
  436. $s .= ");\n";
  437. return $s;
  438. }
  439. } // class DB_dbtrigger
  440. // ----------------------------------------------------------------------
  441.  
  442. /** Class describing a database field of a Postgres table.
  443. * @package database
  444. */
  445. class DB_dbfield extends dbfield {
  446. /** Whether this field is an array of values */
  447.  
  448. var $isarray = false;
  449. // ....................................................................
  450. function DB_dbfield(&$schema, $name, $num, $type, $default="", $notnull=false, $ispkey=false, $isarray=false) {
  451. $this->dbfield($schema, $name, $num, $type, $default, $notnull, $ispkey);
  452. $this->isarray = $isarray;
  453. }
  454. // ....................................................................
  455. /** Dump field description to stdout. */
  456.  
  457. function dump() {
  458. $s = "$this->name $this->type";
  459. if ($this->default != "") $s .= " DEFAULT $this->default";
  460. if ($this->notnull) $s .= " NOT NULL";
  461. if ($this->isarray) $s .= " (array)";
  462. if ($this->ispkey) $s .= " (pk)";
  463. return "$s\n";
  464. }
  465. // ....................................................................
  466. /**
  467. * Return the generic type of the field. The generic types are as
  468. * follows:
  469. * text Fixed or varying length strings
  470. * numeric Integers, real numbers or money
  471. * datetime Times, dates date-times
  472. * logical Boolean or bit field (true/false)
  473. *
  474. * You should override this method to return the appropriate generic
  475. * field types from this list, for your database type.
  476. * NB: Override this function to get schema info per DB type.
  477. */
  478. function generic_type() {
  479. $gtype = "";
  480. $typematch = array(
  481. "text" => "text|char|varchar|inet",
  482. "numeric" => "int2|int4|int8|serial|serial8|float4|float8",
  483. "datetime" => "abstime|datetime|interval|reltime|timespan|timetz|timestamp|timestamptz",
  484. "date" => "date",
  485. "logical" => "bool",
  486. "" => ".*"
  487. );
  488.  
  489. foreach ($typematch as $gentype => $pattern) {
  490. if (preg_match("/$pattern/i", $this->type)) {
  491. $gtype = $gentype;
  492. break;
  493. }
  494. }
  495. return $gtype;
  496. }
  497. // ....................................................................
  498. /**
  499. * Return true if the field is of an integer class. We exclude the serial
  500. * types due to the initial purpose of this method - to determine fields
  501. * which are standalone integer types, without attached sequences.
  502. * NB: Override this function to get schema info per DB type.
  503. */
  504. function is_integer_class() {
  505. $pattern = "int2|int4|int8|integer";
  506. return preg_match("/$pattern/i", $this->type);
  507. }
  508. // ....................................................................
  509. /**
  510. * Return true if the field is of a 'serial' class. This is a pseudo
  511. * class of types which encapsulates integer fields which are able
  512. * to auto-increment themselves when records are inserted.
  513. * NB: Override this function to get schema info per DB type.
  514. */
  515. function is_serial_class() {
  516. $pattern = "serial";
  517. return preg_match("/$pattern/i", $this->type);
  518. }
  519. } // class DB_dbfield
  520. // ----------------------------------------------------------------------
  521.  
  522. /** Class describing a Postgres database table. Inherits the standard
  523. * dbtable class properties and methods, but adds in the getschema
  524. * specifics for acquiring Postgres table info from the metadata, and
  525. * provides a specific create() method.
  526. * @package database
  527. */
  528. class DB_dbtable extends dbtable {
  529. // ....................................................................
  530. /** Construct a table of given name and array of primary key fields.
  531. * @param string $name The name of the table
  532. * @param integer $dbversion Optional database version information
  533. */
  534. function DB_dbtable(&$schema, $name) {
  535. $this->dbtable($schema, $name);
  536. }
  537. // ....................................................................
  538. /**
  539. * Acquires the table fields and constraints which apply to it.
  540. * NB: This function is apt for Postgres. Over-ride for other db types
  541. * @param mixed $schema Schema this table is in, or false if n/a
  542. */
  543. function getschema($mode=ALL) {
  544. $this->fields = array();
  545. $this->pkey = array();
  546. $q = "SELECT pgc.oid as tableoid,";
  547. $q .= " pgc.relhaspkey as tablehaspkey,";
  548. $q .= " pga.attname as fieldname,";
  549. $q .= " pga.attnum as fieldnum,";
  550. $q .= " pga.atthasdef as fieldhasdef,";
  551. $q .= " pga.attnotnull as fieldnotnull,";
  552. $q .= " pga.attndims as fieldarrdims,";
  553. $q .= " pgt.typname as fieldtype";
  554. $q .= " FROM pg_class pgc,pg_attribute pga, pg_type pgt";
  555. $q .= " WHERE pgc.relname='$this->name'";
  556. $q .= " AND pgc.relkind='r'";
  557. $q .= " AND pga.attrelid=pgc.oid";
  558. $q .= " AND pga.attnum > 0";
  559. if ($this->schema->database_version >= 7.3) {
  560. $q .= " AND NOT pga.attisdropped";
  561. }
  562. $q .= " AND pgt.oid=pga.atttypid";
  563. $q .= " ORDER BY pga.attnum";
  564. $tableinfo = dbrecordset($q);
  565. if ($tableinfo->hasdata) {
  566. // Table information..
  567. $tableoid = $tableinfo->field("tableoid");
  568. $tablehaspkey = $tableinfo->istrue("tablehaspkey");
  569. // Table primary key fields..
  570. $pklist = "";
  571. if ($tablehaspkey) {
  572. $q = "SELECT * FROM pg_index";
  573. $q .= " WHERE indrelid=$tableoid::oid";
  574. $q .= " AND indisprimary";
  575. $pk = dbrecordset($q);
  576. if ($pk->hasdata) {
  577. $pklist = $pk->field("indkey");
  578. $this->pkey = explode(" ", $pklist);
  579. }
  580. }
  581. // Field info..
  582. do {
  583. // Field information..
  584. $fieldname = $tableinfo->field("fieldname");
  585. $fieldnum = $tableinfo->field("fieldnum");
  586. $fieldtype = $tableinfo->field("fieldtype");
  587. $fieldisarray = ($tableinfo->field("fieldarrdims") > 0);
  588. $fieldnotnull = $tableinfo->istrue("fieldnotnull");
  589. $fieldhasdef = $tableinfo->istrue("fieldhasdef");
  590.  
  591. // Field default..
  592. $fielddefault = "";
  593. if ($fieldhasdef) {
  594. $q = "SELECT adsrc as fielddefault FROM pg_attrdef";
  595. $q .= " WHERE adrelid=$tableoid::oid";
  596. $q .= " AND adnum=$fieldnum";
  597. $def = dbrecordset($q);
  598. if ($def->hasdata) {
  599. $fielddefault = $def->field("fielddefault");
  600. }
  601. }
  602. // Create field of table..
  603. $this->newfield(
  604. $fieldname,
  605. $fieldnum,
  606. $fieldtype,
  607. $fielddefault,
  608. $fieldnotnull,
  609. $fieldisarray
  610. );
  611. } while ($tableinfo->get_next());
  612. }
  613.  
  614. if ($mode != FIELDS_ONLY) {
  615. // Table Constraints. We have two different methods for getting
  616. // the required details: one for Postgres v7.2 and earlier, and
  617. // one for the much better-designed Postgres v7.3+.
  618. $this->constraints = array();
  619. if ($this->schema->database_version < 7.3) {
  620. // Primary key constraints..
  621. $q = "SELECT pgi.*,pgci.relname as indexname";
  622. $q .= " FROM pg_class pgc, pg_class pgci, pg_index pgi";
  623. $q .= " WHERE pgc.relname='$this->name'";
  624. $q .= " AND pgi.indrelid=pgc.oid";
  625. $q .= " AND pgi.indisprimary";
  626. $q .= " AND pgci.oid=pgi.indexrelid";
  627. $pks = new dbrecords($q);
  628. if ($pks->hasdata) {
  629. debugbr("[$this->name] $pks->rowcount PKs found");
  630. do {
  631. $conname = $pks->field("indexname");
  632. $fieldnums = explode(" ", $pks->field("indkey"));
  633. $con = new dbconstraint(
  634. $this->schema,
  635. $conname,
  636. "p",
  637. $this->name,
  638. "",
  639. $fieldnums
  640. );
  641. // Stash constraint..
  642. $this->constraints[$conname] = $con;
  643. debugbr("[$this->name] PK: $conname");
  644. } while ($pks->get_next());
  645. }
  646. else debugbr("[$this->name] no PKs found.");
  647.  
  648. if ($mode != PRIMARY_KEY_ONLY) {
  649. // Check constraints stored on-table..
  650. $q = "SELECT pgr.*";
  651. $q .= " FROM pg_class pgc, pg_relcheck pgr";
  652. $q .= " WHERE pgc.relname='$this->name'";
  653. $q .= " AND pgr.rcrelid=pgc.oid";
  654. $checks = new dbrecords($q);
  655. if ($checks->hasdata) {
  656. debugbr("[$this->name] $checks->rowcount check constraints found.");
  657. do {
  658. $conname = $checks->field("rcname");
  659. $consrc = $checks->field("rcsrc");
  660. $conbin = $checks->field("rcbin");
  661. if (preg_match("/.*?varattno ([0-9]+).*?/", $conbin, $matches)) {
  662. $attnum = $matches[1];
  663. $con = new dbconstraint($this->schema, $conname, "c");
  664. $con->cksrc = $consrc;
  665. $con->tablename = $this->name;
  666. // Store constraint..
  667. $field = $this->getfieldbynum($attnum);
  668. $field->constraints[$conname] = $con;
  669. $this->addfield($field);
  670. debugbr("[$this->name] check constraint on field: $field->name $consrc");
  671. }
  672. } while ($checks->get_next());
  673. }
  674.  
  675. // FK constraints are stored globally..
  676. $q = "SELECT pgt.*, pgp.prosrc";
  677. $q .= " FROM pg_class pgc, pg_trigger pgt, pg_proc pgp";
  678. $q .= " WHERE pgc.relname='$this->name'";
  679. $q .= " AND pgt.tgconstrrelid=pgc.oid";
  680. $q .= " AND pgt.tgisconstraint";
  681. $q .= " AND pgp.oid=pgt.tgfoid";
  682. $trigs = new dbrecords($q);
  683. if ($trigs->hasdata) {
  684. debugbr("[$this->name] $trigs->rowcount FK triggers found");
  685. do {
  686. // Constraint information..
  687. $consrc = $trigs->field("prosrc");
  688. $ribits = explode("_", $consrc);
  689. if ($ribits[1] == "FKey") {
  690.  
  691. // cascade, restrict, setnull or setdefault
  692. $operation = strtolower($ribits[2]);
  693.  
  694. // Only interested in FK type constraints..
  695. if ($operation != "check") {
  696. // ins, upd or del
  697. $event = strtolower($ribits[3]);
  698.  
  699. //echo "op=$operation : event=$event\n";
  700.  
  701. // Constraint operation & event..
  702. $cksrc = "!";
  703. switch ($operation) {
  704. case "cascade": $act = "c"; break;
  705. case "setnull": $act = "n"; break;
  706. case "restrict": $act = "r"; break;
  707. case "setdefault": $act = "d"; break;
  708. default: $act = "a";
  709. }
  710.  
  711. // Constraint details..
  712. $fk_tablename = "!";
  713. $fieldnums = "!";
  714. $fk_fieldnums = "!";
  715. $updact = "!";
  716. $delact = "!";
  717.  
  718. switch ($event) {
  719. case "upd":
  720. $updact = $act;
  721. break;
  722. case "del":
  723. $delact = $act;
  724. break;
  725. }
  726.  
  727. // Get the constraint..
  728. $conname = $trigs->field("tgconstrname");
  729. if (isset($this->constraints[$conname])) {
  730. $con = $this->constraints[$conname];
  731. }
  732. else {
  733. $con = new dbconstraint($this->schema, $conname, "f");
  734. }
  735.  
  736. // PK & FK table, and field numbers..
  737. $tgargs = $trigs->rawfield("tgargs");
  738. $argbits = explode("\\000", $tgargs);
  739. $fk_tablename = trim(strtolower($argbits[2]));
  740.  
  741. // Get named foreign key table..
  742. $fk_table = new DB_dbtable($this->schema, $fk_tablename);
  743. debugbr("getting Fk table $fk_tablename data pkonly..");
  744. $fk_table->getschema(PRIMARY_KEY_ONLY);
  745. $fieldnums = array();
  746. $fieldnames = array();
  747. $fk_fieldnums = array();
  748. $fk_fieldnames = array();
  749. for ($ix=4; $ix < count($argbits); $ix+=2) {
  750. $fname = trim(strtolower($argbits[$ix]));
  751. $fk_fname = trim(strtolower($argbits[$ix + 1]));
  752. if ($fname != "" && $fk_fname != "") {
  753. $fieldnames[] = $fname;
  754. $fieldnums[] = $this->getfieldnum($fname);
  755. $fk_fieldnames[] = $fk_fname;
  756. $fk_fieldnums[] = $fk_table->getfieldnum($fk_fname);
  757. }
  758. }
  759.  
  760. // Deferred settings..
  761. $deferrable = $trigs->istrue("tgdeferrable");
  762. $deferred = $trigs->istrue("tginitdeferred");
  763.  
  764. // Set constraint vars..
  765. $con->set(
  766. "f",
  767. $this->name,
  768. $fk_tablename,
  769. $fieldnames,
  770. $fk_fieldnames,
  771. $updact,
  772. $delact,
  773. "", // match type
  774. $cksrc,
  775. $deferrable,
  776. $deferred
  777. );
  778. $con->fieldnums = $fieldnums;
  779. $con->fk_fieldnums = $fk_fieldnums;
  780.  
  781. // Stash constraint back in table..
  782. $this->constraints[$conname] = $con;
  783. debugbr("[$this->name] FK: ##" . $con->create() . "##");
  784. }
  785. } // if foreign key
  786. } while ($trigs->get_next());
  787. }
  788. else debugbr("[$this->name] no FKs found.");
  789. } // pkonly
  790. }
  791. else {
  792. // Constraints on this table. These include primary keys, and
  793. // foreign key references to other tables/fields..
  794. $q = "SELECT pcon.conname FROM pg_constraint pcon, pg_class pgc";
  795. $q .= " WHERE pgc.oid=pcon.conrelid";
  796. $q .= " AND pgc.relname='$this->name'";
  797. $constraints = dbrecordset($q);
  798. if ($constraints->hasdata) {
  799. do {
  800. // Constraint..
  801. $con = new DB_dbconstraint($this->schema, $constraints->field("conname"));
  802. $con->getschema();
  803.  
  804. if ($con->type == "p" || ($mode != PRIMARY_KEY_ONLY && $con->type == "f")) {
  805. $con->fieldnames = array();
  806. if (isset($con->fieldnums) && count($con->fieldnums) > 0) {
  807. foreach ($con->fieldnums as $num) {
  808. $field = $this->getfieldbynum($num);
  809. if ($field !== false) {
  810. $con->fieldnames[] = $field->name;
  811. }
  812. }
  813. }
  814. }
  815.  
  816. // Get foreign key table for foreign key constraints..
  817. if ($mode != PRIMARY_KEY_ONLY && $con->type == "f") {
  818. $fk_table = new DB_dbtable($this->schema, $con->fk_tablename);
  819. $fk_table->getschema(FIELDS_ONLY);
  820. $con->fk_fieldnames = array();
  821. if (isset($con->fk_fieldnums) && count($con->fk_fieldnums) > 0) {
  822. foreach ($con->fk_fieldnums as $num) {
  823. $field = $fk_table->getfieldbynum($num);
  824. if ($field !== false) {
  825. $con->fk_fieldnames[] = $field->name;
  826. }
  827. }
  828. }
  829. }
  830. // Add table constraint..
  831. $this->constraints[$con->name] = $con;
  832.  
  833. } while ($constraints->get_next());
  834. } // have constraints
  835. }
  836.  
  837. // Table indexes..
  838. // Only want indexes on table which belong to the user, and
  839. // which are not Postgres-created tables..
  840. $indQ = "SELECT";
  841. $indQ .= " pgcix.relname as indexname";
  842. $indQ .= " FROM pg_index i,pg_class pgcix,pg_class pgctb";
  843. $indQ .= " WHERE pgctb.relname='$this->name'";
  844. $indQ .= " AND i.indrelid=pgctb.oid";
  845. $indQ .= " AND pgcix.oid=i.indexrelid";
  846. $indexes = dbrecordset($indQ);
  847. if ($indexes->hasdata) {
  848. do {
  849. $index = new DB_dbindex($this->schema, $indexes->field("indexname"), $this->name);
  850. $index->getschema();
  851. $fieldnames = array();
  852. foreach ($index->fieldnums as $fieldnum) {
  853. $field = $this->getfieldbynum($fieldnum);
  854. $fieldnames[] = $field->name;
  855. }
  856. $index->fieldnames = $fieldnames;
  857. $this->indexes[$index->name] = $index;
  858. } while ($indexes->get_next());
  859. }
  860. } // fields_only
  861. }
  862. // ....................................................................
  863. /** Create a new field in the table with given parameters. */
  864.  
  865. function newfield($name, $num, $type, $default="", $notnull=false, $isarray=false) {
  866. $ispkey = (in_array($num, $this->pkey));
  867. $this->fields[$name] =
  868. new DB_dbfield(
  869. $this->schema,
  870. $name,
  871. $num,
  872. $type,
  873. $default,
  874. $notnull,
  875. $ispkey,
  876. $isarray
  877. );
  878. }
  879. // ....................................................................
  880. /** Return the SQL which will create this Postgres table.
  881. * NB: We also create the indexes and constraints which pertain to
  882. * this table at the same time.
  883. */
  884. function create() {
  885. $s = "";
  886. $s .= "create table $this->name (\n";
  887. foreach ($this->fields as $field) {
  888. $s .= $field->create() . ",\n";
  889. }
  890. if ($this->schema->database_version < 7.3 && count($this->pkey > 0)) {
  891. $s .= " constraint pk_$this->name primary key (";
  892. foreach ($this->fields as $field) {
  893. if ($field->ispkey) {
  894. $s .= "$field->name,";
  895. }
  896. }
  897. $s = substr($s, 0, -1);
  898. $s .= ")\n";
  899. }
  900. else {
  901. $s = substr($s, 0, -2);
  902. }
  903. $s .= "\n);\n";
  904.  
  905. // Indexes..
  906. $s .= $this->create_indexes();
  907. return $s;
  908. }
  909.  
  910. // ....................................................................
  911. /** Return SQL which will create a column in this table. The $column
  912. * passed in is actually a field object.
  913. */
  914. function addcolumn($column) {
  915. $s = "alter table $this->name\n";
  916. $s .= " add column $column->name $column->type;\n";
  917. if ($column->default != "") {
  918. $s .= $this->setdefault($column);
  919. }
  920. if ($column->notnull) {
  921. if ($this->schema->database_version < 7.3) {
  922. $s .= "UPDATE pg_attribute SET attnotnull=true";
  923. $s .= " WHERE attname='$column->name'";
  924. $s .= " AND attrelid = (SELECT oid FROM pg_class WHERE relname='$this->name');\n";
  925. }
  926. else {
  927. $s .= $this->setnullconstraint($column);
  928. }
  929. }
  930. if (count($column->constraints) > 0) {
  931. foreach ($column->constraints as $con) {
  932. $s .= $con->create() . "\n";
  933. }
  934. }
  935. return $s;
  936. }
  937.  
  938. // ....................................................................
  939. /** Return SQL to set the NULL/NOT NULL constraint.. */
  940.  
  941. function setnullconstraint($column) {
  942. if ($this->schema->database_version < 7.3) {
  943. $nullsetting = ($column->notnull) ? "true" : "false";
  944. $s .= "update pg_attribute set attnotnull=$nullsetting";
  945. $s .= " where attname='$column->name'";
  946. $s .= " and attrelid = (select oid from pg_class where relname='$this->name');\n";
  947. }
  948. else {
  949. $s = "alter table $this->name\n";
  950. $s .= " alter column $column->name";
  951. if ($column->notnull) {
  952. $s .= " set not null;\n";
  953. }
  954. else {
  955. $s .= " drop not null;\n";
  956. }
  957. }
  958. return $s;
  959. }
  960.  
  961. // ....................................................................
  962. /** Return the SQL which will create the constraints on this table.
  963. * Usually this SQL has to come after all table creates have been
  964. * done, so that references to tables are all honoured, hence a
  965. * separate method here.
  966. */
  967. function create_constraints() {
  968. $s = "";
  969. foreach ($this->constraints as $conname => $con) {
  970. $s .= $con->create();
  971. }
  972. return $s;
  973. }
  974.  
  975. // ....................................................................
  976. /** Return the SQL which will create the indexes on this table.
  977. */
  978. function create_indexes() {
  979. $s = "";
  980. foreach ($this->indexes as $indexname => $index) {
  981. if ( !($this->schema->capable_of("unique_index_with_constraint") && ($index->unique || $index->primary)) ) {
  982. $s .= $index->create();
  983. }
  984. }
  985. return $s;
  986. }
  987. } // class DB_dbtable
  988. // ----------------------------------------------------------------------
  989.  
  990. /**
  991. * Class describing a Postgres database schema.
  992. * @package database
  993. */
  994. class DB_schema extends schema {
  995. // ....................................................................
  996. /**
  997. * Create a schema (database) of given name. The name should be a
  998. * valid existing database name that is currently connected.
  999. */
  1000. function DB_schema($name) {
  1001. $this->schema($name);
  1002. }
  1003. // ....................................................................
  1004. /**
  1005. * Return database capabilities. There are specific capabilities which
  1006. * the diff code needs to query, and this method should be overridden
  1007. * in the specific database module to answer those questions.
  1008. */
  1009. function capable_of($capability="") {
  1010. $cando = false;
  1011. switch ($capability) {
  1012. // Supports the ALTER <tablename> DROP <colname> SQL
  1013. // statement to remove table columns.
  1014. case "alter_table_drop_column":
  1015. $cando = ($this->database_version >= 7.3);
  1016. break;
  1017. // Supports functions or stored procedures.
  1018. case "stored_procedures":
  1019. $cando = true;
  1020. break;
  1021. // Can define check constraints on table columns.
  1022. case "check_constraints":
  1023. $cando = true;
  1024. break;
  1025. // Can define RI constraints between table/columns to
  1026. // support foreign-keys.
  1027. case "RI_constraints":
  1028. $cando = true;
  1029. break;
  1030. // Supports indexes on table columns.
  1031. case "indexes":
  1032. $cando = true;
  1033. break;
  1034. // Unique indexes are auto-generated with unique constraints. Ie.
  1035. // when a primary key constraint is added to a table a unique
  1036. // index is automatically built for it.
  1037. case "unique_index_with_constraint":
  1038. $cando = true;
  1039. break;
  1040. // Supports triggers on table update, delete, insert.
  1041. case "triggers":
  1042. $cando = true;
  1043. break;
  1044. // Supports named sequences.
  1045. case "named_sequences":
  1046. $cando = true;
  1047. break;
  1048. default:
  1049. $cando = false;
  1050. }
  1051. return $cando;
  1052. }
  1053. // ....................................................................
  1054. /**
  1055. * Populates our array of tables with all tables in this schema.
  1056. */
  1057. function gettables() {
  1058. $tQ = "SELECT relname";
  1059. $tQ .= " FROM pg_class";
  1060. $tQ .= " WHERE relkind='r'";
  1061. $tQ .= " AND relname !~* '^pg_'";
  1062. $tQ .= " ORDER BY relname";
  1063. $this->tables = array();
  1064. $tables = dbrecordset($tQ);
  1065. if ($tables->hasdata) {
  1066. do {
  1067. $table = new DB_dbtable($this, $tables->field("relname"));
  1068. $table->getschema();
  1069. debugbr(">>ADDING $table->name", DBG_DEBUG);
  1070. $this->addtable($table);
  1071. } while ($tables->get_next());
  1072. }
  1073. }
  1074. // ....................................................................
  1075. /**
  1076. * Populates our array of triggers with all user triggers in this schema.
  1077. */
  1078. function gettriggers() {
  1079. $trgQ = "SELECT tgname";
  1080. $trgQ .= " FROM pg_trigger";
  1081. $trgQ .= " WHERE tgname !~* 'pg_'";
  1082. $trgQ .= " AND NOT tgisconstraint";
  1083. $trgQ .= " AND tgenabled";
  1084. $this->triggers = array();
  1085. $trigs = dbrecordset($trgQ);
  1086. if ($trigs->hasdata) {
  1087. do {
  1088. $trig = new DB_dbtrigger($this, $trigs->field("tgname"));
  1089. $trig->getschema();
  1090. $this->addtrigger($trig);
  1091. } while ($trigs->get_next());
  1092. }
  1093. }
  1094. // ....................................................................
  1095. /**
  1096. * Populates our array of functions with all user functions in this schema.
  1097. */
  1098. function getfunctions() {
  1099. $funQ = "SELECT proname";
  1100. $funQ .= " FROM pg_proc";
  1101. $funQ .= " WHERE proowner > 31";
  1102. $this->functions = array();
  1103. $funcs = dbrecordset($funQ);
  1104. if ($funcs->hasdata) {
  1105. do {
  1106. $func = new DB_dbfunction($this, $funcs->field("proname"));
  1107. $func->getschema();
  1108. $this->addfunction($func);
  1109. } while ($funcs->get_next());
  1110. }
  1111. }
  1112. // ....................................................................
  1113. /**
  1114. * Populates our array of sequences with all user sequences in this schema.
  1115. */
  1116. function getsequences() {
  1117. $seqQ = "SELECT relname";
  1118. $seqQ .= " FROM pg_class";
  1119. $seqQ .= " WHERE relkind='S'";
  1120. $seqQ .= " AND relowner > 31";
  1121. $this->sequences = array();
  1122. $seqs = dbrecordset($seqQ);
  1123. if ($seqs->hasdata) {
  1124. do {
  1125. $seq = new DB_dbsequence($this, $seqs->field("relname"));
  1126. $seq->getschema();
  1127. $this->addsequence($seq);
  1128. } while ($seqs->get_next());
  1129. }
  1130. }
  1131. // ....................................................................
  1132. /**
  1133. * Acquire the schema details of a specific Postgres table. This method
  1134. * is provided to cater for the common requirement of acquiring details
  1135. * for a specific table, without having to endure the overhead of reading
  1136. * all of the database schema metadata to get it.
  1137. *
  1138. * @param string $tablename Name of the table to acquire schema of
  1139. */
  1140. function getschema_table($tablename) {
  1141. if (!isset($this->tables[$tablename])) {
  1142. $table = new DB_dbtable($this, $tablename);
  1143. $table->getschema();
  1144. $this->addtable($table);
  1145. }
  1146. } // get
  1147. // ....................................................................
  1148. /** Acquire the Postgres database version. We make some gross assumptions
  1149. * here with regard to standard local Postgres setup. Change as necessary.
  1150. * Currently we have a shot at either the Debian standard location which
  1151. * is '/usr/lib/postgresql', or the 'other' standard of '/usr/bin'.
  1152. * To-do: acquire Postgres binaries location in a more robust way.
  1153. */
  1154. function getversion() {
  1155. $vstr = 7.3;
  1156. $pg_bin = "";
  1157. $pg_paths = array(
  1158. "/usr/lib/postgresql/bin",
  1159. "/usr/bin",
  1160. "/usr/local/pgsql/bin"
  1161. );
  1162. // Find Postgres executable directory..
  1163. foreach ($pg_paths as $pg_path) {
  1164. if (file_exists("$pg_path/pg_ctl")) {
  1165. $pg_bin = $pg_path;
  1166. break;
  1167. }
  1168. }
  1169. // Get the database version..
  1170. if (file_exists("$pg_bin/pg_config")) {
  1171. $vbits = explode(" ", shell_exec("$pg_bin/pg_config --version"));
  1172. if ($vbits[1] != "") {
  1173. $vstr = $vbits[1];
  1174. }
  1175. }
  1176. elseif (file_exists("$pg_bin/psql")) {
  1177. $vbits = explode(" ", shell_exec("$pg_bin/psql --version"));
  1178. if ($vbits[2] != "") {
  1179. $vvbits = explode(".", $vbits[2]);
  1180. $vstr = $vvbits[0] . "." . (isset($vvbits[1]) ? $vvbits[1] : "0");
  1181. }
  1182. }
  1183. $this->set_dbversion( (float) $vstr );
  1184. debugbr("Postgres database version set to $this->database_version");
  1185. return $this->database_version;
  1186. }
  1187.  
  1188. } // class DB_schema
  1189. // ----------------------------------------------------------------------
  1190.  
  1191. ?>

Documentation generated by phpDocumentor 1.3.0RC3