Pelzini

This is the code documentation for the Pelzini project

source of /processor/postgresql_outputter.php

Contains the PostgresqlOutputter class
  1. <?php
  2. /*
  3. Copyright 2008 Josh Heidenreich
  4.  
  5. This file is part of Pelzini.
  6.  
  7. Pelzini is free software: you can redistribute it and/or modify
  8. it under the terms of the GNU General Public License as published by
  9. the Free Software Foundation, either version 3 of the License, or
  10. (at your option) any later version.
  11.  
  12. Pelzini is distributed in the hope that it will be useful,
  13. but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15. GNU General Public License for more details.
  16.  
  17. You should have received a copy of the GNU General Public License
  18. along with Pelzini. If not, see <http://www.gnu.org/licenses/>.
  19. */
  20.  
  21.  
  22. /**
  23.  * Contains the {@link PostgresqlOutputter} class
  24.  *
  25.  * @package Outputters
  26.  * @author Josh
  27.  * @since 0.2
  28.  **/
  29.  
  30. /**
  31.  * Outputs the tree to a PostgreSQL database
  32.  **/
  33. class PostgresqlOutputter extends DatabaseOutputter {
  34. private $username;
  35. private $password;
  36. private $server;
  37. private $database;
  38. private $db;
  39.  
  40. /**
  41.   * Connects to the db
  42.   */
  43. public function __construct($username, $password, $server, $database)
  44. {
  45. $this->username = $username;
  46. $this->password = $password;
  47. $this->server = $server;
  48. $this->database = $database;
  49. }
  50.  
  51.  
  52. /**
  53.   * Closes connection to the db
  54.   */
  55. public function __destruct()
  56. {
  57. if ($this->db) pg_close ($this->db);
  58. }
  59.  
  60.  
  61.  
  62. /**
  63.   * Connects to the MySQL database
  64.   **/
  65. protected function connect()
  66. {
  67. $connect = '';
  68. if (isset($this->server)) $connect .= "host='{$this->server}' ";
  69. if (isset($this->username)) $connect .= "user='{$this->username}' ";
  70. if (isset($this->password)) $connect .= "password='{$this->password}' ";
  71. if (isset($this->database)) $connect .= "dbname='{$this->database}' ";
  72.  
  73. $this->db = pg_connect($connect);
  74. if ($this->db == false) return false;
  75. return true;
  76. }
  77.  
  78.  
  79. /**
  80.   * Executes a MySQL query
  81.   */
  82. protected function query($query)
  83. {
  84. // If the query begins with CREATE or ALTER, make some small changes:
  85. // - remove 'unsigned', its not supported in postgres.
  86. // - replace 'mediumtext' with 'text'
  87. if (strncasecmp('CREATE', $query, 6) == 0 or strncasecmp('ALTER', $query, 5) == 0) {
  88. $query = str_replace('unsigned', '', $query);
  89. $query = str_replace('mediumtext', 'text', $query);
  90. }
  91.  
  92. $return = @pg_query ($query);
  93. if ($return === false) {
  94. echo "<p>Error in query:<br><em>{$query}</em><br><br>PostgreSQL reported the following:<br><em>" . pg_last_error() . "</em></p>";
  95. }
  96. return $return;
  97. }
  98.  
  99.  
  100. /**
  101.   * Safens some input
  102.   * @param string $input The input to safen
  103.   **/
  104. protected function sql_safen($input)
  105. {
  106. if ($input === null) {
  107. return 'NULL';
  108. } else if (is_integer($input)) {
  109. return $input;
  110. } else {
  111. return "'" . pg_escape_string($input) . "'";
  112. }
  113. }
  114.  
  115.  
  116. /**
  117.   * Fetches a row from the database (numerical)
  118.   **/
  119. protected function fetch_row($res)
  120. {
  121. return pg_fetch_row ($res);
  122. }
  123.  
  124.  
  125. /**
  126.   * Fetches a row from the database (assoc)
  127.   **/
  128. protected function fetch_assoc($res)
  129. {
  130. return pg_fetch_assoc ($res);
  131. }
  132.  
  133.  
  134. /**
  135.   * Returns the number of rows affected in the last query
  136.   **/
  137. protected function affected_rows($res)
  138. {
  139. return pg_affected_rows($res);
  140. }
  141.  
  142.  
  143. /**
  144.   * Returns the autogenerated id created in the last query
  145.   **/
  146. protected function insert_id()
  147. {
  148. $res = $this->query ('SELECT LASTVAL()');
  149. $row = $this->fetch_row ($res);
  150. return $row[0];
  151. }
  152.  
  153.  
  154.  
  155. /**
  156.   * Returns an array of the tables in this database
  157.   **/
  158. protected function get_table_list()
  159. {
  160. $q = "SELECT c.relname AS name
  161. FROM pg_catalog.pg_class AS c
  162. LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
  163. WHERE c.relkind IN ('r','')
  164. AND n.nspname <> 'pg_catalog'
  165. AND n.nspname !~ '^pg_toast'
  166. AND pg_catalog.pg_table_is_visible(c.oid)
  167. ORDER BY 1";
  168. $res = $this->query ($q);
  169.  
  170. $tables = array();
  171. while ($row = $this->fetch_assoc($res)) {
  172. $tables[] = $row['name'];
  173. }
  174.  
  175. return $tables;
  176. }
  177.  
  178.  
  179. /**
  180.   * Converts an internal type into the database-specific SQL type.
  181.   * The defined internal types are:
  182.   * - serial: a number that automatically increments whenever a record is added
  183.   * - smallnum: a small number. needs to be able to hold at least 32,767 possible values (e.g. a 16-bit signed integer)
  184.   * - largenum: a large number. needs to be the same size or larger than a serial type
  185.   * - string: a character field long enough to hold identifiers of objects (e.g. function names)
  186.   * - text: a field that can hold arbitary pieces of text larger than 65536 chars in length.
  187.   *
  188.   * @param string $internal_type_name The internal type name.
  189.   * @return string The name used by the SQL database.
  190.   **/
  191. protected function get_sql_type($internal_type_name)
  192. {
  193. switch ($internal_type_name) {
  194. case 'serial': return 'serial';
  195. case 'smallnum': return 'smallint';
  196. case 'largenum': return 'integer';
  197. case 'string': return 'character varying(255)';
  198. case 'text': return 'text';
  199. default:
  200. throw new Exception ("Undefined type '{$internal_type_name}' specified");
  201. break;
  202. }
  203. }
  204.  
  205.  
  206. /**
  207.   * Should return a multi-dimentional array of the column details
  208.   * Format:
  209.   * Array [
  210.   * [0] => Array [
  211.   * 'Field' => field name
  212.   * 'Type' => field type, (e.g. 'serial', 'smallnum' or 'identifier')
  213.   * 'NotNull' => nullable?, (true or false)
  214.   * 'Key' => indexed?, ('PRI' for primary key)
  215.   * ]
  216.   * [1] => ...
  217.   * [n] => ...
  218.   **/
  219. protected function get_column_details($table_name)
  220. {
  221.  
  222. $q = "SELECT c.oid, n.nspname, c.relname
  223. FROM pg_catalog.pg_class c
  224. LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  225. WHERE c.relname ~ '^({$table_name})$'
  226. AND pg_catalog.pg_table_is_visible(c.oid)
  227. ORDER BY 2, 3";
  228. $res = $this->query ($q);
  229. $row = $this->fetch_assoc ($res);
  230.  
  231. $q = "SELECT a.attname,
  232. pg_catalog.format_type(a.atttypid, a.atttypmod),
  233. (
  234. SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
  235. FROM pg_catalog.pg_attrdef d
  236. WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
  237. ) as extra,
  238. a.attnotnull, a.attnum
  239. FROM pg_catalog.pg_attribute a
  240. WHERE a.attrelid = '{$row['oid']}' AND a.attnum > 0 AND NOT a.attisdropped
  241. ORDER BY a.attnum";
  242. $res = $this->query ($q);
  243.  
  244. $columns = array();
  245. while ($row = $this->fetch_assoc ($res)) {
  246. $item = array();
  247.  
  248. $item['Field'] = $row['attname'];
  249. $item['Type'] = $row['format_type'];
  250. if ($row['attnotnull'] == 't') {
  251. $item['NotNull'] = true;
  252. } else {
  253. $item['NotNull'] = false;
  254. }
  255.  
  256. // Remap the SQL types back to Pelzini type
  257. $row['format_type'] = preg_replace('/\(.+\)/', '', $row['format_type']);
  258. $row['format_type'] = strtolower($row['format_type']);
  259. switch ($row['format_type']) {
  260. case 'smallint': $item['Type'] = 'smallnum'; break;
  261. case 'integer': $item['Type'] = 'largenum'; break;
  262. case 'character varying': $item['Type'] = 'string'; break;
  263. case 'text': $item['Type'] = 'text'; break;
  264. }
  265.  
  266. if (strpos($row['extra'], 'nextval') !== false) {
  267. $item['Key'] = 'PRI';
  268. $item['Type'] = 'serial';
  269. $item['NotNull'] = true;
  270. }
  271.  
  272. $columns[] = $item;
  273. }
  274.  
  275. return $columns;
  276. }
  277.  
  278.  
  279. /**
  280.   * Gets the query that alters a column to match the new SQL definition
  281.   **/
  282. protected function get_alter_column_query($table, $column_name, $new_type, $not_null)
  283. {
  284. $new_type = $this->get_sql_type($new_type);
  285.  
  286. echo "NOT NULL: $not_null\n";
  287.  
  288. $q = "ALTER TABLE {$table} ALTER COLUMN {$column_name} TYPE {$new_type}";
  289. $q .= ";\n";
  290.  
  291. if (! $not_null) {
  292. $q .= "ALTER TABLE {$table} ALTER COLUMN {$column_name} SET NOT NULL";
  293. } else {
  294. $q .= "ALTER TABLE {$table} ALTER COLUMN {$column_name} DROP NOT NULL";
  295. }
  296. return $q;
  297. }
  298.  
  299.  
  300. /**
  301.   * Creates a table
  302.   **/
  303. protected function create_table($table_name, $dest_table)
  304. {
  305. $q = "CREATE TABLE {$table_name} (\n";
  306. foreach ($dest_table['Columns'] as $col_name => $col_def) {
  307. $dest_sql = $this->get_sql_type($col_def['Type']);
  308. if ($col_def['NotNull']) $dest_sql .= ' not null';
  309.  
  310. $q .= " {$col_name} {$dest_sql},\n";
  311. }
  312. $q .= " PRIMARY KEY ({$dest_table['PK']})\n";
  313. $q .= ")";
  314. echo "<b>Query:\n{$q}</b>\n";
  315.  
  316. $res = $this->query ($q);
  317. if ($res) echo 'Affected rows: ', $this->affected_rows($res), "\n";
  318. }
  319.  
  320.  
  321. /**
  322.   * The database engine should start a transaction. If transactions are not supported, it should do nothing.
  323.   **/
  324. protected function start_transaction()
  325. {
  326. $this->query ('BEGIN');
  327. }
  328.  
  329.  
  330. /**
  331.   * The database engine should commit a transaction. If transactions are not supported, it should do nothing.
  332.   **/
  333. protected function commit_transaction()
  334. {
  335. $this->query ('COMMIT');
  336. }
  337.  
  338.  
  339. /**
  340.   * The database engine should rollback a transaction. If transactions are not supported, it should do nothing.
  341.   **/
  342. protected function rollback_transaction()
  343. {
  344. $this->query ('ROLLBACK');
  345. }
  346.  
  347.  
  348. }
  349.  
  350.  
  351. ?>
  352.