Source for file Pgsql.php

Documentation is available at Pgsql.php

  1. <?php
  2. /*
  3.  *  $Id: Pgsql.php 2033 2007-07-21 15:17:17Z romanb $
  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_DataDict');
  22. /**
  23.  * @package     Doctrine
  24.  * @subpackage  Doctrine_DataDict
  25.  * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
  26.  * @author      Konsta Vesterinen <kvesteri@cc.hut.fi>
  27.  * @author      Paul Cooper <pgc@ucecom.com>
  28.  * @author      Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
  29.  * @version     $Revision: 2033 $
  30.  * @category    Object Relational Mapping
  31.  * @link        www.phpdoctrine.com
  32.  * @since       1.0
  33.  */
  34. {
  35.     /**
  36.      * @param array $reservedKeyWords     an array of reserved keywords by pgsql
  37.      */
  38.     protected static $reservedKeyWords array(
  39.                                         'abort',
  40.                                         'absolute',
  41.                                         'access',
  42.                                         'action',
  43.                                         'add',
  44.                                         'after',
  45.                                         'aggregate',
  46.                                         'all',
  47.                                         'alter',
  48.                                         'analyse',
  49.                                         'analyze',
  50.                                         'and',
  51.                                         'any',
  52.                                         'as',
  53.                                         'asc',
  54.                                         'assertion',
  55.                                         'assignment',
  56.                                         'at',
  57.                                         'authorization',
  58.                                         'backward',
  59.                                         'before',
  60.                                         'begin',
  61.                                         'between',
  62.                                         'bigint',
  63.                                         'binary',
  64.                                         'bit',
  65.                                         'boolean',
  66.                                         'both',
  67.                                         'by',
  68.                                         'cache',
  69.                                         'called',
  70.                                         'cascade',
  71.                                         'case',
  72.                                         'cast',
  73.                                         'chain',
  74.                                         'char',
  75.                                         'character',
  76.                                         'characteristics',
  77.                                         'check',
  78.                                         'checkpoint',
  79.                                         'class',
  80.                                         'close',
  81.                                         'cluster',
  82.                                         'coalesce',
  83.                                         'collate',
  84.                                         'column',
  85.                                         'comment',
  86.                                         'commit',
  87.                                         'committed',
  88.                                         'constraint',
  89.                                         'constraints',
  90.                                         'conversion',
  91.                                         'convert',
  92.                                         'copy',
  93.                                         'create',
  94.                                         'createdb',
  95.                                         'createuser',
  96.                                         'cross',
  97.                                         'current_date',
  98.                                         'current_time',
  99.                                         'current_timestamp',
  100.                                         'current_user',
  101.                                         'cursor',
  102.                                         'cycle',
  103.                                         'database',
  104.                                         'day',
  105.                                         'deallocate',
  106.                                         'dec',
  107.                                         'decimal',
  108.                                         'declare',
  109.                                         'default',
  110.                                         'deferrable',
  111.                                         'deferred',
  112.                                         'definer',
  113.                                         'delete',
  114.                                         'delimiter',
  115.                                         'delimiters',
  116.                                         'desc',
  117.                                         'distinct',
  118.                                         'do',
  119.                                         'domain',
  120.                                         'double',
  121.                                         'drop',
  122.                                         'each',
  123.                                         'else',
  124.                                         'encoding',
  125.                                         'encrypted',
  126.                                         'end',
  127.                                         'escape',
  128.                                         'except',
  129.                                         'exclusive',
  130.                                         'execute',
  131.                                         'exists',
  132.                                         'explain',
  133.                                         'external',
  134.                                         'extract',
  135.                                         'false',
  136.                                         'fetch',
  137.                                         'float',
  138.                                         'for',
  139.                                         'force',
  140.                                         'foreign',
  141.                                         'forward',
  142.                                         'freeze',
  143.                                         'from',
  144.                                         'full',
  145.                                         'function',
  146.                                         'get',
  147.                                         'global',
  148.                                         'grant',
  149.                                         'group',
  150.                                         'handler',
  151.                                         'having',
  152.                                         'hour',
  153.                                         'ilike',
  154.                                         'immediate',
  155.                                         'immutable',
  156.                                         'implicit',
  157.                                         'in',
  158.                                         'increment',
  159.                                         'index',
  160.                                         'inherits',
  161.                                         'initially',
  162.                                         'inner',
  163.                                         'inout',
  164.                                         'input',
  165.                                         'insensitive',
  166.                                         'insert',
  167.                                         'instead',
  168.                                         'int',
  169.                                         'integer',
  170.                                         'intersect',
  171.                                         'interval',
  172.                                         'into',
  173.                                         'invoker',
  174.                                         'is',
  175.                                         'isnull',
  176.                                         'isolation',
  177.                                         'join',
  178.                                         'key',
  179.                                         'lancompiler',
  180.                                         'language',
  181.                                         'leading',
  182.                                         'left',
  183.                                         'level',
  184.                                         'like',
  185.                                         'limit',
  186.                                         'listen',
  187.                                         'load',
  188.                                         'local',
  189.                                         'localtime',
  190.                                         'localtimestamp',
  191.                                         'location',
  192.                                         'lock',
  193.                                         'match',
  194.                                         'maxvalue',
  195.                                         'minute',
  196.                                         'minvalue',
  197.                                         'mode',
  198.                                         'month',
  199.                                         'move',
  200.                                         'names',
  201.                                         'national',
  202.                                         'natural',
  203.                                         'nchar',
  204.                                         'new',
  205.                                         'next',
  206.                                         'no',
  207.                                         'nocreatedb',
  208.                                         'nocreateuser',
  209.                                         'none',
  210.                                         'not',
  211.                                         'nothing',
  212.                                         'notify',
  213.                                         'notnull',
  214.                                         'null',
  215.                                         'nullif',
  216.                                         'numeric',
  217.                                         'of',
  218.                                         'off',
  219.                                         'offset',
  220.                                         'oids',
  221.                                         'old',
  222.                                         'on',
  223.                                         'only',
  224.                                         'operator',
  225.                                         'option',
  226.                                         'or',
  227.                                         'order',
  228.                                         'out',
  229.                                         'outer',
  230.                                         'overlaps',
  231.                                         'overlay',
  232.                                         'owner',
  233.                                         'partial',
  234.                                         'password',
  235.                                         'path',
  236.                                         'pendant',
  237.                                         'placing',
  238.                                         'position',
  239.                                         'precision',
  240.                                         'prepare',
  241.                                         'primary',
  242.                                         'prior',
  243.                                         'privileges',
  244.                                         'procedural',
  245.                                         'procedure',
  246.                                         'read',
  247.                                         'real',
  248.                                         'recheck',
  249.                                         'references',
  250.                                         'reindex',
  251.                                         'relative',
  252.                                         'rename',
  253.                                         'replace',
  254.                                         'reset',
  255.                                         'restrict',
  256.                                         'returns',
  257.                                         'revoke',
  258.                                         'right',
  259.                                         'rollback',
  260.                                         'row',
  261.                                         'rule',
  262.                                         'schema',
  263.                                         'scroll',
  264.                                         'second',
  265.                                         'security',
  266.                                         'select',
  267.                                         'sequence',
  268.                                         'serializable',
  269.                                         'session',
  270.                                         'session_user',
  271.                                         'set',
  272.                                         'setof',
  273.                                         'share',
  274.                                         'show',
  275.                                         'similar',
  276.                                         'simple',
  277.                                         'smallint',
  278.                                         'some',
  279.                                         'stable',
  280.                                         'start',
  281.                                         'statement',
  282.                                         'statistics',
  283.                                         'stdin',
  284.                                         'stdout',
  285.                                         'storage',
  286.                                         'strict',
  287.                                         'substring',
  288.                                         'sysid',
  289.                                         'table',
  290.                                         'temp',
  291.                                         'template',
  292.                                         'temporary',
  293.                                         'then',
  294.                                         'time',
  295.                                         'timestamp',
  296.                                         'to',
  297.                                         'toast',
  298.                                         'trailing',
  299.                                         'transaction',
  300.                                         'treat',
  301.                                         'trigger',
  302.                                         'trim',
  303.                                         'true',
  304.                                         'truncate',
  305.                                         'trusted',
  306.                                         'type',
  307.                                         'unencrypted',
  308.                                         'union',
  309.                                         'unique',
  310.                                         'unknown',
  311.                                         'unlisten',
  312.                                         'until',
  313.                                         'update',
  314.                                         'usage',
  315.                                         'user',
  316.                                         'using',
  317.                                         'vacuum',
  318.                                         'valid',
  319.                                         'validator',
  320.                                         'values',
  321.                                         'varchar',
  322.                                         'varying',
  323.                                         'verbose',
  324.                                         'version',
  325.                                         'view',
  326.                                         'volatile',
  327.                                         'when',
  328.                                         'where',
  329.                                         'with',
  330.                                         'without',
  331.                                         'work',
  332.                                         'write',
  333.                                         'year',
  334.                                         'zone'
  335.                                         );
  336.  
  337.     /**
  338.      * Obtain DBMS specific SQL code portion needed to declare an text type
  339.      * field to be used in statements like CREATE TABLE.
  340.      *
  341.      * @param array $field  associative array with the name of the properties
  342.      *       of the field being declared as array indexes. Currently, the types
  343.      *       of supported field properties are as follows:
  344.      *
  345.      *       length
  346.      *           Integer value that determines the maximum length of the text
  347.      *           field. If this argument is missing the field should be
  348.      *           declared to have the longest length allowed by the DBMS.
  349.      *
  350.      *       default
  351.      *           Text value to be used as default for this field.
  352.      *
  353.      *       notnull
  354.      *           Boolean flag that indicates whether this field is constrained
  355.      *           to not be set to null.
  356.      *
  357.      * @return string  DBMS specific SQL code portion that should be used to
  358.      *       declare the specified field.
  359.      */
  360.     public function getNativeDeclaration(array $field)
  361.     {
  362.         if isset($field['type'])) {
  363.             throw new Doctrine_DataDict_Exception('Missing column type.');
  364.         }
  365.         switch ($field['type']{
  366.             case 'char':
  367.             case 'string':
  368.             case 'array':
  369.             case 'object':
  370.             case 'varchar':   
  371.             case 'gzip':
  372.                 $length (isset($field['length']&& $field['length']$field['length'null;
  373.                         // TODO:  $this->conn->options['default_text_field_length'];
  374.  
  375.                 $fixed  ((isset($field['fixed']&& $field['fixed']|| $field['type'== 'char'true false;
  376.  
  377.                 return $fixed ($length 'CHAR('.$length.')' 'CHAR('.$this->conn->options['default_text_field_length'].')')
  378.                     : ($length 'VARCHAR('.$length.')' 'TEXT');
  379.  
  380.             case 'clob':
  381.                 return 'TEXT';
  382.             case 'blob':
  383.                 return 'BYTEA';
  384.             case 'enum':
  385.             case 'integer':
  386.             case 'int':
  387.                 if (!empty($field['autoincrement'])) {
  388.                     if (!empty($field['length'])) {
  389.                         $length $field['length'];
  390.                         if ($length 4{
  391.                             return 'BIGSERIAL';
  392.                         }
  393.                     }
  394.                     return 'SERIAL';
  395.                 }
  396.                 if (!empty($field['length'])) {
  397.                     $length $field['length'];
  398.                     if ($length <= 2{
  399.                         return 'SMALLINT';
  400.                     elseif ($length == || $length == 4{
  401.                         return 'INT';
  402.                     elseif ($length 4{
  403.                         return 'BIGINT';
  404.                     }
  405.                 }
  406.                 return 'INT';
  407.             case 'boolean':
  408.                 return 'BOOLEAN';
  409.             case 'date':
  410.                 return 'DATE';
  411.             case 'time':
  412.                 return 'TIME without time zone';
  413.             case 'timestamp':
  414.                 return 'TIMESTAMP without time zone';
  415.             case 'float':
  416.             case 'double':
  417.                 return 'FLOAT8';
  418.             case 'decimal':
  419.                 $length !empty($field['length']$field['length'18;
  420.                 $scale !empty($field['scale']$field['scale'$this->conn->getAttribute(Doctrine::ATTR_DECIMAL_PLACES);
  421.                 return 'NUMERIC('.$length.','.$scale.')';
  422.         }
  423.         throw new Doctrine_DataDict_Exception('Unknown field type \'' $field['type'.  '\'.');
  424.     }
  425.     /**
  426.      * Maps a native array description of a field to a portable Doctrine datatype and length
  427.      *
  428.      * @param array  $field native field description
  429.      *
  430.      * @return array containing the various possible types, length, sign, fixed
  431.      */
  432.     public function getPortableDeclaration(array $field)
  433.     {
  434.  
  435.         $length (isset($field['length'])) $field['length'null;
  436.         if ($length == '-1' && isset($field['atttypmod'])) {
  437.             $length $field['atttypmod'4;
  438.         }
  439.         if ((int)$length <= 0{
  440.             $length null;
  441.         }
  442.         $type array();
  443.         $unsigned $fixed null;
  444.  
  445.         if isset($field['name'])) {
  446.             $field['name''';
  447.         }
  448.  
  449.         $dbType strtolower($field['type']);
  450.  
  451.         switch ($dbType{
  452.             case 'smallint':
  453.             case 'int2':
  454.                 $type['integer';
  455.                 $unsigned false;
  456.                 $length 2;
  457.                 if ($length == '2'{
  458.                     $type['boolean';
  459.                     if (preg_match('/^(is|has)/'$field['name'])) {
  460.                         $type array_reverse($type);
  461.                     }
  462.                 }
  463.                 break;
  464.             case 'int':
  465.             case 'int4':
  466.             case 'integer':
  467.             case 'serial':
  468.             case 'serial4':
  469.                 $type['integer';
  470.                 $unsigned false;
  471.                 $length 4;
  472.                 break;
  473.             case 'bigint':
  474.             case 'int8':
  475.             case 'bigserial':
  476.             case 'serial8':
  477.                 $type['integer';
  478.                 $unsigned false;
  479.                 $length 8;
  480.                 break;
  481.             case 'bool':
  482.             case 'boolean':
  483.                 $type['boolean';
  484.                 $length 1;
  485.                 break;
  486.             case 'text':
  487.             case 'varchar':
  488.                 $fixed false;
  489.             case 'unknown':
  490.             case 'char':
  491.             case 'bpchar':
  492.                 $type['string';
  493.                 if ($length == '1'{
  494.                     $type['boolean';
  495.                     if (preg_match('/^(is|has)/'$field['name'])) {
  496.                         $type array_reverse($type);
  497.                     }
  498.                 elseif (strstr($dbType'text')) {
  499.                     $type['clob';
  500.                 }
  501.                 if ($fixed !== false{
  502.                     $fixed true;
  503.                 }
  504.                 break;
  505.             case 'date':
  506.                 $type['date';
  507.                 $length null;
  508.                 break;
  509.             case 'datetime':
  510.             case 'timestamp':
  511.                 $type['timestamp';
  512.                 $length null;
  513.                 break;
  514.             case 'time':
  515.                 $type['time';
  516.                 $length null;
  517.                 break;
  518.             case 'float':
  519.             case 'double':
  520.             case 'real':
  521.                 $type['float';
  522.                 break;
  523.             case 'decimal':
  524.             case 'money':
  525.             case 'numeric':
  526.                 $type['decimal';
  527.                 break;
  528.             case 'tinyblob':
  529.             case 'mediumblob':
  530.             case 'longblob':
  531.             case 'blob':
  532.             case 'bytea':
  533.                 $type['blob';
  534.                 $length null;
  535.                 break;
  536.             case 'oid':
  537.                 $type['blob';
  538.                 $type['clob';
  539.                 $length null;
  540.                 break;
  541.             case 'year':
  542.                 $type['integer';
  543.                 $type['date';
  544.                 $length null;
  545.                 break;
  546.             default:
  547.                 throw new Doctrine_DataDict_Exception('unknown database attribute type: '.$dbType);
  548.         }
  549.  
  550.         return array('type'     => $type,
  551.                      'length'   => $length,
  552.                      'unsigned' => $unsigned,
  553.                      'fixed'    => $fixed);
  554.     }
  555.     /**
  556.      * Obtain DBMS specific SQL code portion needed to declare an integer type
  557.      * field to be used in statements like CREATE TABLE.
  558.      *
  559.      * @param string $name name the field to be declared.
  560.      * @param array $field associative array with the name of the properties
  561.      *        of the field being declared as array indexes. Currently, the types
  562.      *        of supported field properties are as follows:
  563.      *
  564.      *        unsigned
  565.      *            Boolean flag that indicates whether the field should be
  566.      *            declared as unsigned integer if possible.
  567.      *
  568.      *        default
  569.      *            Integer value to be used as default for this field.
  570.      *
  571.      *        notnull
  572.      *            Boolean flag that indicates whether this field is constrained
  573.      *            to not be set to null.
  574.      * @return string DBMS specific SQL code portion that should be used to
  575.      *        declare the specified field.
  576.      */
  577.     public function getIntegerDeclaration($name$field)
  578.     {
  579.         /**
  580.         if (!empty($field['unsigned'])) {
  581.             $this->conn->warnings[] = "unsigned integer field \"$name\" is being declared as signed integer";
  582.         }
  583.         */
  584.  
  585.         if empty($field['autoincrement'])) {
  586.             $name $this->conn->quoteIdentifier($nametrue);
  587.             return $name ' ' $this->getNativeDeclaration($field);
  588.         }
  589.  
  590.         $default '';
  591.         if (array_key_exists('default'$field)) {
  592.             if ($field['default'=== ''{
  593.                 $field['default'empty($field['notnull']null 0;
  594.             }
  595.             $default ' DEFAULT '.$this->conn->quote($field['default']$field['type']);
  596.         }
  597.         /**
  598.         TODO: is this needed ?
  599.         elseif (empty($field['notnull'])) {
  600.             $default = ' DEFAULT NULL';
  601.         }
  602.         */
  603.  
  604.         $notnull empty($field['notnull']'' ' NOT NULL';
  605.         $name $this->conn->quoteIdentifier($nametrue);
  606.         return $name ' ' $this->getNativeDeclaration($field$default $notnull;
  607.     }
  608.     /**
  609.      * parseBoolean
  610.      * parses a literal boolean value and returns
  611.      * proper sql equivalent
  612.      *
  613.      * @param string $value     boolean value to be parsed
  614.      * @return string           parsed boolean value
  615.      */
  616.     public function parseBoolean($value)
  617.     {
  618.         return $value;
  619.     }
  620. }