source: branches/comu-ver2/data/module/adodb/datadict/datadict-postgres.inc.php @ 18701

Revision 18701, 14.7 KB checked in by nanasess, 14 years ago (diff)

Copyright の更新(#601)
  • Property svn:eol-style set to LF
  • Property svn:keywords set to Id Revision Date
  • Property svn:mime-type set to text/x-httpd-php
Line 
1<?php
2
3/**
4  v4.992 10 Nov 2009  (c) 2000-2010 John Lim (jlim#natsoft.com). All rights reserved.
5  Released under both BSD license and Lesser GPL library license.
6  Whenever there is any discrepancy between the two licenses,
7  the BSD license will take precedence.
8   
9  Set tabs to 4 for best viewing.
10 
11*/
12
13// security - hide paths
14if (!defined('ADODB_DIR')) die();
15
16class ADODB2_postgres extends ADODB_DataDict {
17   
18    var $databaseType = 'postgres';
19    var $seqField = false;
20    var $seqPrefix = 'SEQ_';
21    var $addCol = ' ADD COLUMN';
22    var $quote = '"';
23    var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
24    var $dropTable = 'DROP TABLE %s CASCADE';
25   
26    function MetaType($t,$len=-1,$fieldobj=false)
27    {
28        if (is_object($t)) {
29            $fieldobj = $t;
30            $t = $fieldobj->type;
31            $len = $fieldobj->max_length;
32        }
33        $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique &&
34            $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval(';
35       
36        switch (strtoupper($t)) {
37            case 'INTERVAL':
38            case 'CHAR':
39            case 'CHARACTER':
40            case 'VARCHAR':
41            case 'NAME':
42            case 'BPCHAR':
43                if ($len <= $this->blobSize) return 'C';
44           
45            case 'TEXT':
46                return 'X';
47   
48            case 'IMAGE': // user defined type
49            case 'BLOB': // user defined type
50            case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
51            case 'VARBIT':
52            case 'BYTEA':
53                return 'B';
54           
55            case 'BOOL':
56            case 'BOOLEAN':
57                return 'L';
58           
59            case 'DATE':
60                return 'D';
61           
62            case 'TIME':
63            case 'DATETIME':
64            case 'TIMESTAMP':
65            case 'TIMESTAMPTZ':
66                return 'T';
67           
68            case 'INTEGER': return !$is_serial ? 'I' : 'R';
69            case 'SMALLINT':
70            case 'INT2': return !$is_serial ? 'I2' : 'R';
71            case 'INT4': return !$is_serial ? 'I4' : 'R';
72            case 'BIGINT':
73            case 'INT8': return !$is_serial ? 'I8' : 'R';
74               
75            case 'OID':
76            case 'SERIAL':
77                return 'R';
78           
79            case 'FLOAT4':
80            case 'FLOAT8':
81            case 'DOUBLE PRECISION':
82            case 'REAL':
83                return 'F';
84               
85             default:
86                return 'N';
87        }
88    }
89   
90    function ActualType($meta)
91    {
92        switch($meta) {
93        case 'C': return 'VARCHAR';
94        case 'XL':
95        case 'X': return 'TEXT';
96       
97        case 'C2': return 'VARCHAR';
98        case 'X2': return 'TEXT';
99       
100        case 'B': return 'BYTEA';
101           
102        case 'D': return 'DATE';
103        case 'T': return 'TIMESTAMP';
104       
105        case 'L': return 'BOOLEAN';
106        case 'I': return 'INTEGER';
107        case 'I1': return 'SMALLINT';
108        case 'I2': return 'INT2';
109        case 'I4': return 'INT4';
110        case 'I8': return 'INT8';
111       
112        case 'F': return 'FLOAT8';
113        case 'N': return 'NUMERIC';
114        default:
115            return $meta;
116        }
117    }
118   
119    /**
120     * Adding a new Column
121     *
122     * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
123     *
124     * @param string $tabname table-name
125     * @param string $flds column-names and types for the changed columns
126     * @return array with SQL strings
127     */
128    function AddColumnSQL($tabname, $flds)
129    {
130        $tabname = $this->TableName ($tabname);
131        $sql = array();
132        list($lines,$pkey) = $this->_GenFields($flds);
133        $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
134        foreach($lines as $v) {
135            if (($not_null = preg_match('/NOT NULL/i',$v))) {
136                $v = preg_replace('/NOT NULL/i','',$v);
137            }
138            if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
139                list(,$colname,$default) = $matches;
140                $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
141                $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
142                $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
143            } else {               
144                $sql[] = $alter . $v;
145            }
146            if ($not_null) {
147                list($colname) = explode(' ',$v);
148                $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
149            }
150        }
151        return $sql;
152    }
153   
154
155    function DropIndexSQL ($idxname, $tabname = NULL)
156    {
157       return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
158    }
159   
160    /**
161     * Change the definition of one column
162     *
163     * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
164     * to allow, recreating the table and copying the content over to the new table
165     * @param string $tabname table-name
166     * @param string $flds column-name and type for the changed column
167     * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
168     * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
169     * @return array with SQL strings
170     */
171    /*function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
172    {
173        if (!$tableflds) {
174            if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
175            return array();
176        }
177        return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
178    }*/
179   
180    function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
181    {
182       // Check if alter single column datatype available - works with 8.0+
183       $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
184   
185       if ($has_alter_column) {
186          $tabname = $this->TableName($tabname);
187          $sql = array();
188          list($lines,$pkey) = $this->_GenFields($flds);
189          $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
190          foreach($lines as $v) {
191             if ($not_null = preg_match('/NOT NULL/i',$v)) {
192                $v = preg_replace('/NOT NULL/i','',$v);
193             }
194             // this next block doesn't work - there is no way that I can see to
195             // explicitly ask a column to be null using $flds
196             else if ($set_null = preg_match('/NULL/i',$v)) {
197                // if they didn't specify not null, see if they explicitely asked for null
198                $v = preg_replace('/\sNULL/i','',$v);
199             }
200             
201             if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
202                list(,$colname,$default) = $matches;
203                $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
204                $sql[] = $alter . $colname . ' TYPE ' . str_replace('DEFAULT '.$default,'',$v);
205                $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
206             }
207             else {
208                // drop default?
209                preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
210                list (,$colname,$rest) = $matches;
211                $sql[] = $alter . $colname . ' TYPE ' . $rest;
212             }
213   
214             list($colname) = explode(' ',$v);
215             if ($not_null) {
216                // this does not error out if the column is already not null
217                $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
218             }
219             if ($set_null) {
220                // this does not error out if the column is already null
221                $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' DROP NOT NULL';
222             }
223          }
224          return $sql;
225       }
226   
227       // does not have alter column
228       if (!$tableflds) {
229          if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
230          return array();
231       }
232       return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
233    }
234   
235    /**
236     * Drop one column
237     *
238     * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
239     * to allow, recreating the table and copying the content over to the new table
240     * @param string $tabname table-name
241     * @param string $flds column-name and type for the changed column
242     * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
243     * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
244     * @return array with SQL strings
245     */
246    function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
247    {
248        $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
249        if (!$has_drop_column && !$tableflds) {
250            if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
251        return array();
252    }
253        if ($has_drop_column) {
254            return ADODB_DataDict::DropColumnSQL($tabname, $flds);
255        }
256        return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
257    }
258   
259    /**
260     * Save the content into a temp. table, drop and recreate the original table and copy the content back in
261     *
262     * We also take care to set the values of the sequenz and recreate the indexes.
263     * All this is done in a transaction, to not loose the content of the table, if something went wrong!
264     * @internal
265     * @param string $tabname table-name
266     * @param string $dropflds column-names to drop
267     * @param string $tableflds complete defintion of the new table, eg. for postgres
268     * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
269     * @return array with SQL strings
270     */
271    function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
272    {
273        if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
274        $copyflds = array();
275        foreach($this->MetaColumns($tabname) as $fld) {
276            if (!$dropflds || !in_array($fld->name,$dropflds)) {
277                // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
278                if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
279                    in_array($fld->type,array('varchar','char','text','bytea'))) {
280                    $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
281                } else {
282                    $copyflds[] = $fld->name;
283                }
284                // identify the sequence name and the fld its on
285                if ($fld->primary_key && $fld->has_default &&
286                    preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
287                    $seq_name = $matches[1];
288                    $seq_fld = $fld->name;
289                }
290            }
291        }
292        $copyflds = implode(', ',$copyflds);
293       
294        $tempname = $tabname.'_tmp';
295        $aSql[] = 'BEGIN';      // we use a transaction, to make sure not to loose the content of the table
296        $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
297        $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
298        $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
299        $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
300        if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
301            $seq_name = $tabname.'_'.$seq_fld.'_seq';   // has to be the name of the new implicit sequence
302            $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
303        }
304        $aSql[] = "DROP TABLE $tempname";
305        // recreate the indexes, if they not contain one of the droped columns
306        foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
307        {
308            if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
309                $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
310                    $idx_data['unique'] ? array('UNIQUE') : False));
311            }
312        }
313        $aSql[] = 'COMMIT';
314        return $aSql;
315    }
316   
317    function DropTableSQL($tabname)
318    {
319        $sql = ADODB_DataDict::DropTableSQL($tabname);
320       
321        $drop_seq = $this->_DropAutoIncrement($tabname);
322        if ($drop_seq) $sql[] = $drop_seq;
323       
324        return $sql;
325    }
326
327    // return string must begin with space
328    function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
329    {
330        if ($fautoinc) {
331            $ftype = 'SERIAL';
332            return '';
333        }
334        $suffix = '';
335        if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
336        if ($fnotnull) $suffix .= ' NOT NULL';
337        if ($fconstraint) $suffix .= ' '.$fconstraint;
338        return $suffix;
339    }
340   
341    // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
342    // if yes return sql to drop it
343    // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
344    function _DropAutoIncrement($tabname)
345    {
346        $tabname = $this->connection->quote('%'.$tabname.'%');
347
348        $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
349
350        // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
351        if (!$seq || $this->connection->GetOne("SELECT relname FROM pg_class JOIN pg_depend ON pg_class.relfilenode=pg_depend.objid WHERE relname='$seq' AND relkind='S' AND deptype='i'")) {
352            return False;
353        }
354        return "DROP SEQUENCE ".$seq;
355    }
356   
357    function RenameTableSQL($tabname,$newname)
358    {
359        if (!empty($this->schema)) {
360            $rename_from = $this->TableName($tabname);
361            $schema_save = $this->schema;
362            $this->schema = false;
363            $rename_to = $this->TableName($newname);
364            $this->schema = $schema_save;
365            return array (sprintf($this->renameTable, $rename_from, $rename_to));
366        }
367
368        return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
369    }
370   
371    /*
372    CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
373    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
374    | table_constraint } [, ... ]
375    )
376    [ INHERITS ( parent_table [, ... ] ) ]
377    [ WITH OIDS | WITHOUT OIDS ]
378    where column_constraint is:
379    [ CONSTRAINT constraint_name ]
380    { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
381    CHECK (expression) |
382    REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
383    [ ON DELETE action ] [ ON UPDATE action ] }
384    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
385    and table_constraint is:
386    [ CONSTRAINT constraint_name ]
387    { UNIQUE ( column_name [, ... ] ) |
388    PRIMARY KEY ( column_name [, ... ] ) |
389    CHECK ( expression ) |
390    FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
391    [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
392    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
393    */
394   
395   
396    /*
397    CREATE [ UNIQUE ] INDEX index_name ON table
398[ USING acc_method ] ( column [ ops_name ] [, ...] )
399[ WHERE predicate ]
400CREATE [ UNIQUE ] INDEX index_name ON table
401[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
402[ WHERE predicate ]
403    */
404    function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
405    {
406        $sql = array();
407       
408        if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
409            $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
410            if ( isset($idxoptions['DROP']) )
411                return $sql;
412        }
413       
414        if ( empty ($flds) ) {
415            return $sql;
416        }
417       
418        $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
419       
420        $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
421       
422        if (isset($idxoptions['HASH']))
423            $s .= 'USING HASH ';
424       
425        if ( isset($idxoptions[$this->upperName]) )
426            $s .= $idxoptions[$this->upperName];
427       
428        if ( is_array($flds) )
429            $flds = implode(', ',$flds);
430        $s .= '(' . $flds . ')';
431        $sql[] = $s;
432       
433        return $sql;
434    }
435   
436    function _GetSize($ftype, $ty, $fsize, $fprec)
437    {
438        if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
439            $ftype .= "(".$fsize;
440            if (strlen($fprec)) $ftype .= ",".$fprec;
441            $ftype .= ')';
442        }
443        return $ftype;
444    }
445}
446?>
Note: See TracBrowser for help on using the repository browser.