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($schema, $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, $defaultval="", $notnull=false, $ispkey=false, $isarray=false) {
  451. // Strip out any postgres typecast..
  452. //$bits = explode("::", $defaultval);
  453. //$defaultval = $bits[0];
  454. $this->dbfield($schema, $name, $num, $type, $defaultval, $notnull, $ispkey);
  455. $this->isarray = $isarray;
  456. }
  457. // ....................................................................
  458. /** Dump field description to stdout. */
  459.  
  460. function dump() {
  461. $s = "$this->name $this->type";
  462. if ($this->defaultval != "") $s .= " DEFAULT $this->defaultval";
  463. if ($this->notnull) $s .= " NOT NULL";
  464. if ($this->isarray) $s .= " (array)";
  465. if ($this->ispkey) $s .= " (pk)";
  466. return "$s\n";
  467. }
  468. // ....................................................................
  469. /**
  470. * Return the generic type of the field. The generic types are as
  471. * follows:
  472. * text Fixed or varying length strings
  473. * numeric Integers, real numbers or money
  474. * datetime Times, dates date-times
  475. * logical Boolean or bit field (true/false)
  476. *
  477. * You should override this method to return the appropriate generic
  478. * field types from this list, for your database type.
  479. * NB: Override this function to get schema info per DB type.
  480. */
  481. function generic_type() {
  482. $gtype = "";
  483. $typematch = array(
  484. "text" => "text|char|varchar|inet",
  485. "numeric" => "int2|int4|int8|serial|serial8|float4|float8",
  486. "datetime" => "abstime|datetime|interval|reltime|timespan|timetz|timestamp|timestamptz",
  487. "date" => "date",
  488. "logical" => "bool",
  489. "" => ".*"
  490. );
  491.  
  492. foreach ($typematch as $gentype => $pattern) {
  493. if (preg_match("/$pattern/i", $this->type)) {
  494. $gtype = $gentype;
  495. break;
  496. }
  497. }
  498. return $gtype;
  499. }
  500. // ....................................................................
  501. /**
  502. * Return true if the field is of an integer class. We exclude the serial
  503. * types due to the initial purpose of this method - to determine fields
  504. * which are standalone integer types, without attached sequences.
  505. * NB: Override this function to get schema info per DB type.
  506. */
  507. function is_integer_class() {
  508. $pattern = "int2|int4|int8|integer";
  509. return preg_match("/$pattern/i", $this->type);
  510. }
  511. // ....................................................................
  512. /**
  513. * Return true if the field is of a 'serial' class. This is a pseudo
  514. * class of types which encapsulates integer fields which are able
  515. * to auto-increment themselves when records are inserted.
  516. * NB: Override this function to get schema info per DB type.
  517. */
  518. function is_serial_class() {
  519. $pattern = "serial";
  520. return preg_match("/$pattern/i", $this->type);
  521. }
  522. } // class DB_dbfield
  523. // ----------------------------------------------------------------------
  524.  
  525. /** Class describing a Postgres database table. Inherits the standard
  526. * dbtable class properties and methods, but adds in the getschema
  527. * specifics for acquiring Postgres table info from the metadata, and
  528. * provides a specific create() method.
  529. * @package database
  530. */
  531. class DB_dbtable extends dbtable {
  532. // ....................................................................
  533. /** Construct a table of given name and array of primary key fields.
  534. * @param string $name The name of the table
  535. * @param integer $dbversion Optional database version information
  536. */
  537. function DB_dbtable(&$schema, $name) {
  538. $this->dbtable($schema, $name);
  539. }
  540. // ....................................................................
  541. /**
  542. * Acquires the table fields and constraints which apply to it.
  543. * NB: This function is apt for Postgres. Over-ride for other db types
  544. * @param mixed $schema Schema this table is in, or false if n/a
  545. */
  546. function getschema($mode=ALL) {
  547. $this->fields = array();
  548. $this->pkey = array();
  549. $q = "SELECT pgc.oid as tableoid,";
  550. $q .= " pgc.relhaspkey as tablehaspkey,";
  551. $q .= " pga.attname as fieldname,";
  552. $q .= " pga.attnum as fieldnum,";
  553. $q .= " pga.atthasdef as fieldhasdef,";
  554. $q .= " pga.attnotnull as fieldnotnull,";
  555. $q .= " pga.attndims as fieldarrdims,";
  556. $q .= " pgt.typname as fieldtype";
  557. $q .= " FROM pg_class pgc,pg_attribute pga, pg_type pgt";
  558. $q .= " WHERE pgc.relname='$this->name'";
  559. $q .= " AND pgc.relkind='r'";
  560. $q .= " AND pga.attrelid=pgc.oid";
  561. $q .= " AND pga.attnum > 0";
  562. if ($this->schema->database_version >= 7.3) {
  563. $q .= " AND NOT pga.attisdropped";
  564. }
  565. $q .= " AND pgt.oid=pga.atttypid";
  566. $q .= " ORDER BY pga.attnum";
  567. $tableinfo = dbrecordset($q);
  568. if ($tableinfo->hasdata) {
  569. // Table information..
  570. $tableoid = $tableinfo->field("tableoid");
  571. $tablehaspkey = $tableinfo->istrue("tablehaspkey");
  572. // Table primary key fields..
  573. $pklist = "";
  574. if ($tablehaspkey) {
  575. $q = "SELECT * FROM pg_index";
  576. $q .= " WHERE indrelid=$tableoid::oid";
  577. $q .= " AND indisprimary";
  578. $pk = dbrecordset($q);
  579. if ($pk->hasdata) {
  580. $pklist = $pk->field("indkey");
  581. $this->pkey = explode(" ", $pklist);
  582. }
  583. }
  584. // Field info..
  585. do {
  586. // Field information..
  587. $fieldname = $tableinfo->field("fieldname");
  588. $fieldnum = $tableinfo->field("fieldnum");
  589. $fieldtype = $tableinfo->field("fieldtype");
  590. $fieldisarray = ($tableinfo->field("fieldarrdims") > 0);
  591. $fieldnotnull = $tableinfo->istrue("fieldnotnull");
  592. $fieldhasdef = $tableinfo->istrue("fieldhasdef");
  593.  
  594. // Field default..
  595. $fielddefault = "";
  596. if ($fieldhasdef) {
  597. $q = "SELECT adsrc as fielddefault FROM pg_attrdef";
  598. $q .= " WHERE adrelid=$tableoid::oid";
  599. $q .= " AND adnum=$fieldnum";
  600. $def = dbrecordset($q);
  601. if ($def->hasdata) {
  602. $fielddefault = $def->field("fielddefault");
  603. }
  604. }
  605. // Create field of table..
  606. $this->newfield(
  607. $fieldname,
  608. $fieldnum,
  609. $fieldtype,
  610. $fielddefault,
  611. $fieldnotnull,
  612. $fieldisarray
  613. );
  614. } while ($tableinfo->get_next());
  615. }
  616.  
  617. if ($mode != FIELDS_ONLY) {
  618. // Table Constraints. We have two different methods for getting
  619. // the required details: one for Postgres v7.2 and earlier, and
  620. // one for the much better-designed Postgres v7.3+.
  621. $this->constraints = array();
  622. if ($this->schema->database_version < 7.3) {
  623. // Primary key constraints..
  624. $q = "SELECT pgi.*,pgci.relname as indexname";
  625. $q .= " FROM pg_class pgc, pg_class pgci, pg_index pgi";
  626. $q .= " WHERE pgc.relname='$this->name'";
  627. $q .= " AND pgi.indrelid=pgc.oid";
  628. $q .= " AND pgi.indisprimary";
  629. $q .= " AND pgci.oid=pgi.indexrelid";
  630. $pks = new dbrecords($q);
  631. if ($pks->hasdata) {
  632. debugbr("[$this->name] $pks->rowcount PKs found");
  633. do {
  634. $conname = $pks->field("indexname");
  635. $fieldnums = explode(" ", $pks->field("indkey"));
  636. $con = new dbconstraint(
  637. $this->schema,
  638. $conname,
  639. "p",
  640. $this->name,
  641. "",
  642. $fieldnums
  643. );
  644. // Stash constraint..
  645. $this->constraints[$conname] = $con;
  646. debugbr("[$this->name] PK: $conname");
  647. } while ($pks->get_next());
  648. }
  649. else debugbr("[$this->name] no PKs found.");
  650.  
  651. if ($mode != PRIMARY_KEY_ONLY) {
  652. // Check constraints stored on-table..
  653. $q = "SELECT pgr.*";
  654. $q .= " FROM pg_class pgc, pg_relcheck pgr";
  655. $q .= " WHERE pgc.relname='$this->name'";
  656. $q .= " AND pgr.rcrelid=pgc.oid";
  657. $checks = new dbrecords($q);
  658. if ($checks->hasdata) {
  659. debugbr("[$this->name] $checks->rowcount check constraints found.");
  660. do {
  661. $conname = $checks->field("rcname");
  662. $consrc = $checks->field("rcsrc");
  663. $conbin = $checks->field("rcbin");
  664. $matches = array();
  665. if (preg_match("/.*?varattno ([0-9]+).*?/", $conbin, $matches)) {
  666. $attnum = $matches[1];
  667. $con = new dbconstraint($this->schema, $conname, "c");
  668. $con->cksrc = $consrc;
  669. $con->tablename = $this->name;
  670. // Store constraint..
  671. $field = $this->getfieldbynum($attnum);
  672. $field->constraints[$conname] = $con;
  673. $this->addfield($field);
  674. debugbr("[$this->name] check constraint on field: $field->name $consrc");
  675. }
  676. } while ($checks->get_next());
  677. }
  678.  
  679. // FK constraints are stored globally..
  680. $q = "SELECT pgt.*, pgp.prosrc";
  681. $q .= " FROM pg_class pgc, pg_trigger pgt, pg_proc pgp";
  682. $q .= " WHERE pgc.relname='$this->name'";
  683. $q .= " AND pgt.tgconstrrelid=pgc.oid";
  684. $q .= " AND pgt.tgisconstraint";
  685. $q .= " AND pgp.oid=pgt.tgfoid";
  686. $trigs = new dbrecords($q);
  687. if ($trigs->hasdata) {
  688. debugbr("[$this->name] $trigs->rowcount FK triggers found");
  689. do {
  690. // Constraint information..
  691. $consrc = $trigs->field("prosrc");
  692. $ribits = explode("_", $consrc);
  693. if ($ribits[1] == "FKey") {
  694.  
  695. // cascade, restrict, setnull or setdefault
  696. $operation = strtolower($ribits[2]);
  697.  
  698. // Only interested in FK type constraints..
  699. if ($operation != "check") {
  700. // ins, upd or del
  701. $event = strtolower($ribits[3]);
  702.  
  703. //echo "op=$operation : event=$event\n";
  704.  
  705. // Constraint operation & event..
  706. $cksrc = "!";
  707. switch ($operation) {
  708. case "cascade": $act = "c"; break;
  709. case "setnull": $act = "n"; break;
  710. case "restrict": $act = "r"; break;
  711. case "setdefault": $act = "d"; break;
  712. default: $act = "a";
  713. }
  714.  
  715. // Constraint details..
  716. $fk_tablename = "!";
  717. $fieldnums = "!";
  718. $fk_fieldnums = "!";
  719. $updact = "!";
  720. $delact = "!";
  721.  
  722. switch ($event) {
  723. case "upd":
  724. $updact = $act;
  725. break;
  726. case "del":
  727. $delact = $act;
  728. break;
  729. }
  730.  
  731. // Get the constraint..
  732. $conname = $trigs->field("tgconstrname");
  733. if (isset($this->constraints[$conname])) {
  734. $con = $this->constraints[$conname];
  735. }
  736. else {
  737. $con = new dbconstraint($this->schema, $conname, "f");
  738. }
  739.  
  740. // PK & FK table, and field numbers..
  741. $tgargs = $trigs->rawfield("tgargs");
  742. $argbits = explode("\\000", $tgargs);
  743. $fk_tablename = trim(strtolower($argbits[2]));
  744.  
  745. // Get named foreign key table..
  746. $fk_table = new DB_dbtable($this->schema, $fk_tablename);
  747. debugbr("getting Fk table $fk_tablename data pkonly..");
  748. $fk_table->getschema(PRIMARY_KEY_ONLY);
  749. $fieldnums = array();
  750. $fieldnames = array();
  751. $fk_fieldnums = array();
  752. $fk_fieldnames = array();
  753. for ($ix=4; $ix < count($argbits); $ix+=2) {
  754. $fname = trim(strtolower($argbits[$ix]));
  755. $fk_fname = trim(strtolower($argbits[$ix + 1]));
  756. if ($fname != "" && $fk_fname != "") {
  757. $fieldnames[] = $fname;
  758. $fieldnums[] = $this->getfieldnum($fname);
  759. $fk_fieldnames[] = $fk_fname;
  760. $fk_fieldnums[] = $fk_table->getfieldnum($fk_fname);
  761. }
  762. }
  763.  
  764. // Deferred settings..
  765. $deferrable = $trigs->istrue("tgdeferrable");
  766. $deferred = $trigs->istrue("tginitdeferred");
  767.  
  768. // Set constraint vars..
  769. $con->set(
  770. "f",
  771. $this->name,
  772. $fk_tablename,
  773. $fieldnames,
  774. $fk_fieldnames,
  775. $updact,
  776. $delact,
  777. "", // match type
  778. $cksrc,
  779. $deferrable,
  780. $deferred
  781. );
  782. $con->fieldnums = $fieldnums;
  783. $con->fk_fieldnums = $fk_fieldnums;
  784.  
  785. // Stash constraint back in table..
  786. $this->constraints[$conname] = $con;
  787. debugbr("[$this->name] FK: ##" . $con->create() . "##");
  788. }
  789. } // if foreign key
  790. } while ($trigs->get_next());
  791. }
  792. else debugbr("[$this->name] no FKs found.");
  793. } // pkonly
  794. }
  795. else {
  796. // Constraints on this table. These include primary keys, and
  797. // foreign key references to other tables/fields..
  798. $q = "SELECT pcon.conname FROM pg_constraint pcon, pg_class pgc";
  799. $q .= " WHERE pgc.oid=pcon.conrelid";
  800. $q .= " AND pgc.relname='$this->name'";
  801. $constraints = dbrecordset($q);
  802. if ($constraints->hasdata) {
  803. do {
  804. // Constraint..
  805. $con = new DB_dbconstraint($this->schema, $constraints->field("conname"));
  806. $con->getschema();
  807.  
  808. if ($con->type == "p" || ($mode != PRIMARY_KEY_ONLY && $con->type == "f")) {
  809. $con->fieldnames = array();
  810. if (isset($con->fieldnums) && count($con->fieldnums) > 0) {
  811. foreach ($con->fieldnums as $num) {
  812. $field = $this->getfieldbynum($num);
  813. if ($field !== false) {
  814. $con->fieldnames[] = $field->name;
  815. }
  816. }
  817. }
  818. }
  819.  
  820. // Get foreign key table for foreign key constraints..
  821. if ($mode != PRIMARY_KEY_ONLY && $con->type == "f") {
  822. $fk_table = new DB_dbtable($this->schema, $con->fk_tablename);
  823. $fk_table->getschema(FIELDS_ONLY);
  824. $con->fk_fieldnames = array();
  825. if (isset($con->fk_fieldnums) && count($con->fk_fieldnums) > 0) {
  826. foreach ($con->fk_fieldnums as $num) {
  827. $field = $fk_table->getfieldbynum($num);
  828. if ($field !== false) {
  829. $con->fk_fieldnames[] = $field->name;
  830. }
  831. }
  832. }
  833. }
  834. // Add table constraint..
  835. $this->constraints[$con->name] = $con;
  836.  
  837. } while ($constraints->get_next());
  838. } // have constraints
  839. }
  840.  
  841. // Table indexes..
  842. // Only want indexes on table which belong to the user, and
  843. // which are not Postgres-created tables..
  844. $indQ = "SELECT";
  845. $indQ .= " pgcix.relname as indexname";
  846. $indQ .= " FROM pg_index i,pg_class pgcix,pg_class pgctb";
  847. $indQ .= " WHERE pgctb.relname='$this->name'";
  848. $indQ .= " AND i.indrelid=pgctb.oid";
  849. $indQ .= " AND pgcix.oid=i.indexrelid";
  850. $indexes = dbrecordset($indQ);
  851. if ($indexes->hasdata) {
  852. do {
  853. $index = new DB_dbindex($this->schema, $indexes->field("indexname"), $this->name);
  854. $index->getschema();
  855. $fieldnames = array();
  856. foreach ($index->fieldnums as $fieldnum) {
  857. $field = $this->getfieldbynum($fieldnum);
  858. $fieldnames[] = $field->name;
  859. }
  860. $index->fieldnames = $fieldnames;
  861. $this->indexes[$index->name] = $index;
  862. } while ($indexes->get_next());
  863. }
  864. } // fields_only
  865. }
  866. // ....................................................................
  867. /** Create a new field in the table with given parameters. */
  868.  
  869. function newfield($name, $num, $type, $defaultval="", $notnull=false, $isarray=false) {
  870. $ispkey = (in_array($num, $this->pkey));
  871. $this->fields[$name] =
  872. new DB_dbfield(
  873. $this->schema,
  874. $name,
  875. $num,
  876. $type,
  877. $defaultval,
  878. $notnull,
  879. $ispkey,
  880. $isarray
  881. );
  882. }
  883. // ....................................................................
  884. /** Return the SQL which will create this Postgres table.
  885. * NB: We also create the indexes and constraints which pertain to
  886. * this table at the same time.
  887. */
  888. function create() {
  889. $s = "";
  890. $s .= "create table $this->name (\n";
  891. foreach ($this->fields as $field) {
  892. $s .= $field->create() . ",\n";
  893. }
  894. if ($this->schema->database_version < 7.3 && count($this->pkey > 0)) {
  895. $s .= " constraint pk_$this->name primary key (";
  896. foreach ($this->fields as $field) {
  897. if ($field->ispkey) {
  898. $s .= "$field->name,";
  899. }
  900. }
  901. $s = substr($s, 0, -1);
  902. $s .= ")\n";
  903. }
  904. else {
  905. $s = substr($s, 0, -2);
  906. }
  907. $s .= "\n);\n";
  908.  
  909. // Indexes..
  910. $s .= $this->create_indexes();
  911. return $s;
  912. }
  913.  
  914. // ....................................................................
  915. /** Return SQL which will create a column in this table. The $column
  916. * passed in is actually a field object.
  917. */
  918. function addcolumn($column) {
  919. $s = "alter table $this->name\n";
  920. $s .= " add column $column->name $column->type;\n";
  921. if ($column->defaultval != "") {
  922. $s .= $this->setdefault($column);
  923. }
  924. if ($column->notnull) {
  925. if ($this->schema->database_version < 7.3) {
  926. $s .= "UPDATE pg_attribute SET attnotnull=true";
  927. $s .= " WHERE attname='$column->name'";
  928. $s .= " AND attrelid = (SELECT oid FROM pg_class WHERE relname='$this->name');\n";
  929. }
  930. else {
  931. $s .= $this->setnullconstraint($column);
  932. }
  933. }
  934. if (count($column->constraints) > 0) {
  935. foreach ($column->constraints as $con) {
  936. $s .= $con->create() . "\n";
  937. }
  938. }
  939. return $s;
  940. }
  941.  
  942. // ....................................................................
  943. /** Return SQL to set the NULL/NOT NULL constraint.. */
  944.  
  945. function setnullconstraint($column) {
  946. if ($this->schema->database_version < 7.3) {
  947. $nullsetting = ($column->notnull) ? "true" : "false";
  948. $s .= "update pg_attribute set attnotnull=$nullsetting";
  949. $s .= " where attname='$column->name'";
  950. $s .= " and attrelid = (select oid from pg_class where relname='$this->name');\n";
  951. }
  952. else {
  953. $s = "alter table $this->name\n";
  954. $s .= " alter column $column->name";
  955. if ($column->notnull) {
  956. $s .= " set not null;\n";
  957. }
  958. else {
  959. $s .= " drop not null;\n";
  960. }
  961. }
  962. return $s;
  963. }
  964.  
  965. // ....................................................................
  966. /** Return the SQL which will create the constraints on this table.
  967. * Usually this SQL has to come after all table creates have been
  968. * done, so that references to tables are all honoured, hence a
  969. * separate method here.
  970. */
  971. function create_constraints() {
  972. $s = "";
  973. foreach ($this->constraints as $conname => $con) {
  974. $s .= $con->create();
  975. }
  976. return $s;
  977. }
  978.  
  979. // ....................................................................
  980. /** Return the SQL which will create the indexes on this table.
  981. */
  982. function create_indexes() {
  983. $s = "";
  984. foreach ($this->indexes as $indexname => $index) {
  985. if ( !($this->schema->capable_of("unique_index_with_constraint") && ($index->unique || $index->primary)) ) {
  986. $s .= $index->create();
  987. }
  988. }
  989. return $s;
  990. }
  991. } // class DB_dbtable
  992. // ----------------------------------------------------------------------
  993.  
  994. /**
  995. * Class describing a Postgres database schema.
  996. * @package database
  997. */
  998. class DB_schema extends schema {
  999. // ....................................................................
  1000. /**
  1001. * Create a schema (database) of given name & type. The name should be a
  1002. * valid existing database name that is currently connected.
  1003. * @param string $name Name of this particular database
  1004. */
  1005. function DB_schema($name) {
  1006. $this->schema($name, "Postgresql");
  1007. }
  1008. // ....................................................................
  1009. /**
  1010. * Return database capabilities. There are specific capabilities which
  1011. * the diff code needs to query, and this method should be overridden
  1012. * in the specific database module to answer those questions.
  1013. */
  1014. function capable_of($capability="") {
  1015. $cando = false;
  1016. switch ($capability) {
  1017. // Supports the ALTER <tablename> DROP <colname> SQL
  1018. // statement to remove table columns.
  1019. case "alter_table_drop_column":
  1020. $cando = ($this->database_version >= 7.3);
  1021. break;
  1022. // Supports functions or stored procedures.
  1023. case "stored_procedures":
  1024. $cando = true;
  1025. break;
  1026. // Can define check constraints on table columns.
  1027. case "check_constraints":
  1028. $cando = true;
  1029. break;
  1030. // Can define RI constraints between table/columns to
  1031. // support foreign-keys.
  1032. case "RI_constraints":
  1033. $cando = true;
  1034. break;
  1035. // Supports indexes on table columns.
  1036. case "indexes":
  1037. $cando = true;
  1038. break;
  1039. // Unique indexes are auto-generated with unique constraints. Ie.
  1040. // when a primary key constraint is added to a table a unique
  1041. // index is automatically built for it.
  1042. case "unique_index_with_constraint":
  1043. $cando = true;
  1044. break;
  1045. // Supports triggers on table update, delete, insert.
  1046. case "triggers":
  1047. $cando = true;
  1048. break;
  1049. // Supports named sequences.
  1050. case "named_sequences":
  1051. $cando = true;
  1052. break;
  1053. default:
  1054. $cando = false;
  1055. }
  1056. return $cando;
  1057. }
  1058. // ....................................................................
  1059. /**
  1060. * Populates our array of tables with all tables in this schema.
  1061. */
  1062. function gettables() {
  1063. $tQ = "SELECT relname";
  1064. $tQ .= " FROM pg_class";
  1065. $tQ .= " WHERE relkind='r'";
  1066. $tQ .= " AND relname !~* '^pg_'";
  1067. $tQ .= " ORDER BY relname";
  1068. $this->tables = array();
  1069. $tables = dbrecordset($tQ);
  1070. if ($tables->hasdata) {
  1071. do {
  1072. $table = new DB_dbtable($this, $tables->field("relname"));
  1073. $table->getschema();
  1074. debugbr(">>ADDING $table->name", DBG_DEBUG);
  1075. $this->addtable($table);
  1076. } while ($tables->get_next());
  1077. }
  1078. }
  1079. // ....................................................................
  1080. /**
  1081. * Populates our array of triggers with all user triggers in this schema.
  1082. */
  1083. function gettriggers() {
  1084. $trgQ = "SELECT tgname";
  1085. $trgQ .= " FROM pg_trigger";
  1086. $trgQ .= " WHERE tgname !~* 'pg_'";
  1087. $trgQ .= " AND NOT tgisconstraint";
  1088. $trgQ .= " AND tgenabled";
  1089. $this->triggers = array();
  1090. $trigs = dbrecordset($trgQ);
  1091. if ($trigs->hasdata) {
  1092. do {
  1093. $trig = new DB_dbtrigger($this, $trigs->field("tgname"));
  1094. $trig->getschema();
  1095. $this->addtrigger($trig);
  1096. } while ($trigs->get_next());
  1097. }
  1098. }
  1099. // ....................................................................
  1100. /**
  1101. * Populates our array of functions with all user functions in this schema.
  1102. */
  1103. function getfunctions() {
  1104. $funQ = "SELECT proname";
  1105. $funQ .= " FROM pg_proc";
  1106. $funQ .= " WHERE proowner > 31";
  1107. $this->functions = array();
  1108. $funcs = dbrecordset($funQ);
  1109. if ($funcs->hasdata) {
  1110. do {
  1111. $func = new DB_dbfunction($this, $funcs->field("proname"));
  1112. $func->getschema();
  1113. $this->addfunction($func);
  1114. } while ($funcs->get_next());
  1115. }
  1116. }
  1117. // ....................................................................
  1118. /**
  1119. * Populates our array of sequences with all user sequences in this schema.
  1120. */
  1121. function getsequences() {
  1122. $seqQ = "SELECT relname";
  1123. $seqQ .= " FROM pg_class";
  1124. $seqQ .= " WHERE relkind='S'";
  1125. $seqQ .= " AND relowner > 31";
  1126. $this->sequences = array();
  1127. $seqs = dbrecordset($seqQ);
  1128. if ($seqs->hasdata) {
  1129. do {
  1130. $seq = new DB_dbsequence($this, $seqs->field("relname"));
  1131. $seq->getschema();
  1132. $this->addsequence($seq);
  1133. } while ($seqs->get_next());
  1134. }
  1135. }
  1136. // ....................................................................
  1137. /**
  1138. * Acquire the schema details of a specific Postgres table. This method
  1139. * is provided to cater for the common requirement of acquiring details
  1140. * for a specific table, without having to endure the overhead of reading
  1141. * all of the database schema metadata to get it.
  1142. *
  1143. * @param string $tablename Name of the table to acquire schema of
  1144. */
  1145. function getschema_table($tablename) {
  1146. if (!isset($this->tables[$tablename])) {
  1147. $table = new DB_dbtable($this, $tablename);
  1148. $table->getschema();
  1149. $this->addtable($table);
  1150. }
  1151. } // get
  1152. // ....................................................................
  1153. /** Acquire the Postgres database version. We make some gross assumptions
  1154. * here with regard to standard local Postgres setup. Change as necessary.
  1155. * Currently we have a shot at either the Debian standard location which
  1156. * is '/usr/lib/postgresql', or the 'other' standard of '/usr/bin'.
  1157. * To-do: acquire Postgres binaries location in a more robust way.
  1158. */
  1159. function getversion() {
  1160. $vstr = 7.3;
  1161. $pg_bin = "";
  1162. $pg_paths = array(
  1163. "/usr/lib/postgresql/bin",
  1164. "/usr/bin",
  1165. "/usr/local/pgsql/bin"
  1166. );
  1167. // Find Postgres executable directory..
  1168. foreach ($pg_paths as $pg_path) {
  1169. if (file_exists("$pg_path/pg_ctl")) {
  1170. $pg_bin = $pg_path;
  1171. break;
  1172. }
  1173. }
  1174. // Get the database version..
  1175. if (file_exists("$pg_bin/pg_config")) {
  1176. $vbits = explode(" ", shell_exec("$pg_bin/pg_config --version"));
  1177. if ($vbits[1] != "") {
  1178. $vstr = $vbits[1];
  1179. }
  1180. }
  1181. elseif (file_exists("$pg_bin/psql")) {
  1182. $vbits = explode(" ", shell_exec("$pg_bin/psql --version"));
  1183. if ($vbits[2] != "") {
  1184. $vvbits = explode(".", $vbits[2]);
  1185. $vstr = $vvbits[0] . "." . (isset($vvbits[1]) ? $vvbits[1] : "0");
  1186. }
  1187. }
  1188. $this->set_dbversion( (float) $vstr );
  1189. debugbr("$this->database_server version detection", DBG_DEBUG);
  1190. debugbr("database version set to $this->database_version", DBG_DEBUG);
  1191. return $this->database_version;
  1192. }
  1193.  
  1194. } // class DB_schema
  1195. // ----------------------------------------------------------------------
  1196.  
  1197. ?>

Documentation generated by phpDocumentor 1.3.0RC3