Source for file Pgsql.php

Documentation is available at Pgsql.php

  1. <?php
  2. /*
  3.  *  $Id: Pgsql.php 1889 2007-06-28 12:11:55Z zYne $
  4.  *
  5.  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  6.  * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  7.  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  8.  * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  9.  * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10.  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11.  * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12.  * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13.  * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14.  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15.  * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16.  *
  17.  * This software consists of voluntary contributions made by many individuals
  18.  * and is licensed under the LGPL. For more information, see
  19.  * <http://www.phpdoctrine.com>.
  20.  */
  21. Doctrine::autoload('Doctrine_Import');
  22. /**
  23.  * @package     Doctrine
  24.  * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
  25.  * @author      Konsta Vesterinen <kvesteri@cc.hut.fi>
  26.  * @author      Paul Cooper <pgc@ucecom.com>
  27.  * @author      Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
  28.  * @version     $Revision: 1889 $
  29.  * @category    Object Relational Mapping
  30.  * @link        www.phpdoctrine.com
  31.  * @since       1.0
  32.  */
  33. {
  34.  
  35.     protected $sql = array(
  36.                         'listDatabases' => 'SELECT datname FROM pg_database',
  37.                         'listFunctions' => "SELECT
  38.                                                 proname
  39.                                             FROM
  40.                                                 pg_proc pr,
  41.                                                 pg_type tp
  42.                                             WHERE
  43.                                                 tp.oid = pr.prorettype
  44.                                                 AND pr.proisagg = FALSE
  45.                                                 AND tp.typname <> 'trigger'
  46.                                                 AND pr.pronamespace IN
  47.                                                     (SELECT oid FROM pg_namespace
  48.                                                      WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema'",
  49.                         'listSequences' => "SELECT
  50.                                                 relname
  51.                                             FROM
  52.                                                 pg_class
  53.                                             WHERE relkind = 'S' AND relnamespace IN
  54.                                                 (SELECT oid FROM pg_namespace
  55.                                                  WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')",
  56.                         'listTables'    => "SELECT
  57.                                                 c.relname AS table_name
  58.                                             FROM pg_class c, pg_user u
  59.                                             WHERE c.relowner = u.usesysid
  60.                                                 AND c.relkind = 'r'
  61.                                                 AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname)
  62.                                                 AND c.relname !~ '^(pg_|sql_)'
  63.                                             UNION
  64.                                             SELECT c.relname AS table_name
  65.                                             FROM pg_class c
  66.                                             WHERE c.relkind = 'r'
  67.                                                 AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname)
  68.                                                 AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner)
  69.                                                 AND c.relname !~ '^pg_'",
  70.                         'listViews'     => 'SELECT viewname FROM pg_views',
  71.                         'listUsers'     => 'SELECT usename FROM pg_user',
  72.                         'listTableConstraints' => "SELECT
  73.                                                         relname
  74.                                                    FROM
  75.                                                         pg_class
  76.                                                    WHERE oid IN (
  77.                                                         SELECT indexrelid
  78.                                                         FROM pg_index, pg_class
  79.                                                         WHERE pg_class.relname = %s
  80.                                                             AND pg_class.oid = pg_index.indrelid
  81.                                                             AND (indisunique = 't' OR indisprimary = 't')
  82.                                                         )",
  83.                         'listTableIndexes'     => "SELECT
  84.                                                         relname
  85.                                                    FROM
  86.                                                         pg_class
  87.                                                    WHERE oid IN (
  88.                                                         SELECT indexrelid
  89.                                                         FROM pg_index, pg_class
  90.                                                         WHERE pg_class.relname = %s
  91.                                                             AND pg_class.oid=pg_index.indrelid
  92.                                                             AND indisunique != 't'
  93.                                                             AND indisprimary != 't'
  94.                                                         )",
  95.                         'listTableColumns'     => "SELECT
  96.                                                         a.attnum,
  97.                                                         a.attname AS field,
  98.                                                         t.typname AS type,
  99.                                                         format_type(a.atttypid, a.atttypmod) AS complete_type,
  100.                                                         a.attnotnull AS isnotnull,
  101.                                                         (SELECT 't'
  102.                                                           FROM pg_index
  103.                                                           WHERE c.oid = pg_index.indrelid
  104.                                                           AND pg_index.indkey[0] = a.attnum
  105.                                                           AND pg_index.indisprimary = 't'
  106.                                                         ) AS pri,
  107.                                                         (SELECT pg_attrdef.adsrc
  108.                                                           FROM pg_attrdef
  109.                                                           WHERE c.oid = pg_attrdef.adrelid
  110.                                                           AND pg_attrdef.adnum=a.attnum
  111.                                                         ) AS default
  112.                                                   FROM pg_attribute a, pg_class c, pg_type t
  113.                                                   WHERE c.relname = %s
  114.                                                         AND a.attnum > 0
  115.                                                         AND a.attrelid = c.oid
  116.                                                         AND a.atttypid = t.oid
  117.                                                   ORDER BY a.attnum",
  118.                         );
  119.     /**
  120.      * lists all database triggers
  121.      *
  122.      * @param string|null$database 
  123.      * @return array 
  124.      */
  125.     public function listTriggers($database null)
  126.     {
  127.  
  128.     }
  129.     /**
  130.      * lists table constraints
  131.      *
  132.      * @param string $table     database table name
  133.      * @return array 
  134.      */
  135.     public function listTableConstraints($table)
  136.     {
  137.         $table $this->conn->quote($table);
  138.         $query sprintf($this->sql['listTableConstraints']$table);
  139.  
  140.         return $this->conn->fetchColumn($query);
  141.     }
  142.     /**
  143.      * lists table constraints
  144.      *
  145.      * @param string $table     database table name
  146.      * @return array 
  147.      */
  148.     public function listTableColumns($table)
  149.     {
  150.         $table $this->conn->quote($table);
  151.         $query sprintf($this->sql['listTableColumns']$table);
  152.         $result $this->conn->fetchAssoc($query);
  153.  
  154.         $columns     array();
  155.         foreach ($result as $key => $val{
  156.             $val array_change_key_case($valCASE_LOWER);
  157.  
  158.             if (strtolower($val['type']=== 'varchar'{
  159.                 // get length from varchar definition
  160.                 $length preg_replace('~.*\(([0-9]*)\).*~''$1'$val['complete_type']);
  161.                 $val['length'$length;
  162.             }
  163.             
  164.             $decl $this->conn->dataDict->getPortableDeclaration($val);
  165.  
  166.             $description array(
  167.                 'name'      => $val['field'],
  168.                 'ntype'     => $val['type'],
  169.                 'type'      => $decl['type'][0],
  170.                 'alltypes'  => $decl['type'],
  171.                 'length'    => $decl['length'],
  172.                 'fixed'     => $decl['fixed'],
  173.                 'unsigned'  => $decl['unsigned'],
  174.                 'notnull'   => ($val['isnotnull'== ''),
  175.                 'default'   => $val['default'],
  176.                 'primary'   => ($val['pri'== 't'),
  177.             );
  178.             $columns[$val['field']] $description;
  179.         }
  180.         return $columns;
  181.     }
  182.     /**
  183.      * list all indexes in a table
  184.      *
  185.      * @param string $table     database table name
  186.      * @return array 
  187.      */
  188.     public function listTableIndexes($table)
  189.     {
  190.         $table $this->conn->quote($table);
  191.         $query sprintf($this->sql['listTableIndexes']$table);
  192.  
  193.         return $this->conn->fetchColumn($query);
  194.     }
  195.     /**
  196.      * lists tables
  197.      *
  198.      * @param string|null$database 
  199.      * @return array 
  200.      */
  201.     public function listTables($database null)
  202.     {
  203.         return $this->conn->fetchColumn($this->sql['listTables']);
  204.     }
  205.     /**
  206.      * lists table triggers
  207.      *
  208.      * @param string $table     database table name
  209.      * @return array 
  210.      */
  211.     public function listTableTriggers($table)
  212.     {
  213.         $query 'SELECT trg.tgname AS trigger_name
  214.                     FROM pg_trigger trg,
  215.                          pg_class tbl
  216.                    WHERE trg.tgrelid = tbl.oid';
  217.         if ($table !== null{
  218.             $table $this->conn->quote(strtoupper($table)'string');
  219.             $query .= " AND tbl.relname = $table";
  220.         }
  221.         return $this->conn->fetchColumn($query);
  222.     }
  223.     /**
  224.      * list the views in the database that reference a given table
  225.      *
  226.      * @param string $table     database table name
  227.      * @return array 
  228.      */
  229.     public function listTableViews($table)
  230.     {
  231.         return $this->conn->fetchColumn($query);
  232.     }
  233. }