Pelzini

source of /processor/postgresql_outputter.php

Contains the PostgresqlOutputter class

<?php
/*
Copyright 2008 Josh Heidenreich
 
This file is part of Pelzini.
 
Pelzini is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
Pelzini is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with Pelzini.  If not, see <http://www.gnu.org/licenses/>.
*/
 
 
/**
 * Contains the {@link PostgresqlOutputter} class
 *
 * @package Outputters
 * @author Josh
 * @since 0.2
 **/
 
/**
 * Outputs the tree to a PostgreSQL database
 **/
class PostgresqlOutputter extends DatabaseOutputter {
    private $username;
    private $password;
    private $server;
    private $database;
    private $db;
 
    /**
     * Connects to the db
     */
    public function __construct($username, $password, $server, $database)
    {
        $this->username = $username;
        $this->password = $password;
        $this->server = $server;
        $this->database = $database;
    }
 
 
    /**
     * Closes connection to the db
     */
    public function __destruct()
    {
        if ($this->db) pg_close ($this->db);
    }
 
 
 
    /**
     * Connects to the MySQL database
     **/
    protected function connect()
    {
        $connect = '';
        if (isset($this->server)) $connect .= "host='{$this->server}' ";
        if (isset($this->username)) $connect .= "user='{$this->username}' ";
        if (isset($this->password)) $connect .= "password='{$this->password}' ";
        if (isset($this->database)) $connect .= "dbname='{$this->database}' ";
 
        $this->db = pg_connect($connect);
        if ($this->db == false) return false;
        return true;
    }
 
 
    /**
     * Executes a MySQL query
     */
    protected function query($query)
    {
        // If the query begins with CREATE or ALTER, make some small changes:
        // - remove 'unsigned', its not supported in postgres.
        // - replace 'mediumtext' with 'text'
        if (strncasecmp('CREATE', $query, 6) == 0 or strncasecmp('ALTER', $query, 5) == 0) {
            $query = str_replace('unsigned', '', $query);
            $query = str_replace('mediumtext', 'text', $query);
        }
 
        $return = @pg_query ($query);
        if ($return === false) {
            echo "<p>Error in query:<br><em>{$query}</em><br><br>PostgreSQL reported the following:<br><em>" . pg_last_error() . "</em></p>";
        }
        return $return;
    }
 
 
    /**
     * Safens some input
     * @param string $input The input to safen
     **/
    protected function sql_safen($input)
    {
        if ($input === null) {
            return 'NULL';
        } else if (is_integer($input)) {
            return $input;
        } else {
            return "'" . pg_escape_string($input) . "'";
        }
    }
 
 
    /**
     * Fetches a row from the database (numerical)
     **/
    protected function fetch_row($res)
    {
        return pg_fetch_row ($res);
    }
 
 
    /**
     * Fetches a row from the database (assoc)
     **/
    protected function fetch_assoc($res)
    {
        return pg_fetch_assoc ($res);
    }
 
 
    /**
     * Returns the number of rows affected in the last query
     **/
    protected function affected_rows($res)
    {
        return pg_affected_rows($res);
    }
 
 
    /**
     * Returns the autogenerated id created in the last query
     **/
    protected function insert_id()
    {
        $res = $this->query ('SELECT LASTVAL()');
        $row = $this->fetch_row ($res);
        return $row[0];
    }
 
 
 
    /**
     * Returns an array of the tables in this database
     **/
    protected function get_table_list()
    {
        $q = "SELECT c.relname AS name
      FROM pg_catalog.pg_class AS c
      LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
      WHERE c.relkind IN ('r','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname !~ '^pg_toast'
        AND pg_catalog.pg_table_is_visible(c.oid)
      ORDER BY 1";
        $res = $this->query ($q);
 
        $tables = array();
        while ($row = $this->fetch_assoc($res)) {
            $tables[] = $row['name'];
        }
 
        return $tables;
    }
 
 
    /**
     * Converts an internal type into the database-specific SQL type.
     * The defined internal types are:
     *   - serial: a number that automatically increments whenever a record is added
     *   - smallnum: a small number. needs to be able to hold at least 32,767 possible values (e.g. a 16-bit signed integer)
     *   - largenum: a large number. needs to be the same size or larger than a serial type
     *   - string: a character field long enough to hold identifiers of objects (e.g. function names)
     *   - text: a field that can hold arbitary pieces of text larger than 65536 chars in length.
     *
     * @param string $internal_type_name The internal type name.
     * @return string The name used by the SQL database.
     **/
    protected function get_sql_type($internal_type_name)
    {
        switch ($internal_type_name) {
        case 'serial': return 'serial';
        case 'smallnum': return 'smallint';
        case 'largenum': return 'integer';
        case 'string': return 'character varying(255)';
        case 'text': return 'text';
        default:
            throw new Exception ("Undefined type '{$internal_type_name}' specified");
            break;
        }
    }
 
 
    /**
     * Should return a multi-dimentional array of the column details
     * Format:
     * Array [
     *   [0] => Array [
     *      'Field' => field name
     *      'Type' => field type, (e.g. 'serial', 'smallnum' or 'identifier')
     *      'NotNull' => nullable?, (true or false)
     *      'Key' => indexed?, ('PRI' for primary key)
     *      ]
     *    [1] => ...
     *    [n] => ...
     **/
    protected function get_column_details($table_name)
    {
 
        $q = "SELECT c.oid, n.nspname, c.relname
      FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE c.relname ~ '^({$table_name})$'
        AND pg_catalog.pg_table_is_visible(c.oid)
      ORDER BY 2, 3";
        $res = $this->query ($q);
        $row = $this->fetch_assoc ($res);
 
        $q = "SELECT a.attname,
      pg_catalog.format_type(a.atttypid, a.atttypmod),
      (
        SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
        FROM pg_catalog.pg_attrdef d
        WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
      ) as extra,
      a.attnotnull, a.attnum
      FROM pg_catalog.pg_attribute a
      WHERE a.attrelid = '{$row['oid']}' AND a.attnum > 0 AND NOT a.attisdropped
      ORDER BY a.attnum";
        $res = $this->query ($q);
 
        $columns = array();
        while ($row = $this->fetch_assoc ($res)) {
            $item = array();
 
            $item['Field'] = $row['attname'];
            $item['Type'] = $row['format_type'];
            if ($row['attnotnull'] == 't') {
                $item['NotNull'] = true;
            } else {
                $item['NotNull'] = false;
            }
 
            // Remap the SQL types back to Pelzini type
            $row['format_type'] = preg_replace('/\(.+\)/', '', $row['format_type']);
            $row['format_type'] = strtolower($row['format_type']);
            switch ($row['format_type']) {
            case 'smallint': $item['Type'] = 'smallnum'; break;
            case 'integer': $item['Type'] = 'largenum'; break;
            case 'character varying': $item['Type'] = 'string'; break;
            case 'text': $item['Type'] = 'text'; break;
            }
 
            if (strpos($row['extra'], 'nextval') !== false) {
                $item['Key'] = 'PRI';
                $item['Type'] = 'serial';
                $item['NotNull'] = true;
            }
 
            $columns[] = $item;
        }
 
        return $columns;
    }
 
 
    /**
     * Gets the query that alters a column to match the new SQL definition
     **/
    protected function get_alter_column_query($table, $column_name, $new_type, $not_null)
    {
        $new_type = $this->get_sql_type($new_type);
 
        echo "NOT NULL: $not_null\n";
 
        $q = "ALTER TABLE {$table} ALTER COLUMN {$column_name} TYPE {$new_type}";
        $q .= ";\n";
 
        if (! $not_null) {
            $q .= "ALTER TABLE {$table} ALTER COLUMN {$column_name} SET NOT NULL";
        } else {
            $q .= "ALTER TABLE {$table} ALTER COLUMN {$column_name} DROP NOT NULL";
        }
        return $q;
    }
 
 
    /**
     * Creates a table
     **/
    protected function create_table($table_name, $dest_table)
    {
        $q = "CREATE TABLE {$table_name} (\n";
        foreach ($dest_table['Columns'] as $col_name => $col_def) {
            $dest_sql = $this->get_sql_type($col_def['Type']);
            if ($col_def['NotNull']) $dest_sql .= ' not null';
 
            $q .= "  {$col_name} {$dest_sql},\n";
        }
        $q .= "  PRIMARY KEY ({$dest_table['PK']})\n";
        $q .= ")";
        echo "<b>Query:\n{$q}</b>\n";
 
        $res = $this->query ($q);
        if ($res) echo 'Affected rows: ', $this->affected_rows($res), "\n";
    }
 
 
    /**
     * The database engine should start a transaction. If transactions are not supported, it should do nothing.
     **/
    protected function start_transaction()
    {
        $this->query ('BEGIN');
    }
 
 
    /**
     * The database engine should commit a transaction. If transactions are not supported, it should do nothing.
     **/
    protected function commit_transaction()
    {
        $this->query ('COMMIT');
    }
 
 
    /**
     * The database engine should rollback a transaction. If transactions are not supported, it should do nothing.
     **/
    protected function rollback_transaction()
    {
        $this->query ('ROLLBACK');
    }
 
 
}
 
 
?>