Context Navigation

source: trunk/phpgwapi/inc/adodb/datadict/datadict-postgres.inc.php @ 34

Revision 34, 11.8 KB checked in by niltonneto, 17 years ago (diff)
Versão nova do ADODB (4.5 para 4.95)
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2
3	/**
4	V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). 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
14	if (!defined('ADODB_DIR')) die();
15
16	class 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	* Change the definition of one column
156	*
157	* Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
158	* to allow, recreating the table and copying the content over to the new table
159	* @param string $tabname table-name
160	* @param string $flds column-name and type for the changed column
161	* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
162	* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
163	* @return array with SQL strings
164	*/
165	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
166	{
167	if (!$tableflds) {
168	if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
169	return array();
170	}
171	return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
172	}
173
174	/**
175	* Drop one column
176	*
177	* Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
178	* to allow, recreating the table and copying the content over to the new table
179	* @param string $tabname table-name
180	* @param string $flds column-name and type for the changed column
181	* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
182	* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
183	* @return array with SQL strings
184	*/
185	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
186	{
187	$has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
188	if (!$has_drop_column && !$tableflds) {
189	if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
190	return array();
191	}
192	if ($has_drop_column) {
193	return ADODB_DataDict::DropColumnSQL($tabname, $flds);
194	}
195	return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
196	}
197
198	/**
199	* Save the content into a temp. table, drop and recreate the original table and copy the content back in
200	*
201	* We also take care to set the values of the sequenz and recreate the indexes.
202	* All this is done in a transaction, to not loose the content of the table, if something went wrong!
203	* @internal
204	* @param string $tabname table-name
205	* @param string $dropflds column-names to drop
206	* @param string $tableflds complete defintion of the new table, eg. for postgres
207	* @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
208	* @return array with SQL strings
209	*/
210	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
211	{
212	if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
213	$copyflds = array();
214	foreach($this->MetaColumns($tabname) as $fld) {
215	if (!$dropflds \|\| !in_array($fld->name,$dropflds)) {
216	// we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
217	if (preg_match('/'.$fld->name.' (I\|I2\|I4\|I8\|N\|F)/i',$tableflds,$matches) &&
218	in_array($fld->type,array('varchar','char','text','bytea'))) {
219	$copyflds[] = "to_number($fld->name,'S9999999999999D99')";
220	} else {
221	$copyflds[] = $fld->name;
222	}
223	// identify the sequence name and the fld its on
224	if ($fld->primary_key && $fld->has_default &&
225	preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
226	$seq_name = $matches[1];
227	$seq_fld = $fld->name;
228	}
229	}
230	}
231	$copyflds = implode(', ',$copyflds);
232
233	$tempname = $tabname.'_tmp';
234	$aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table
235	$aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
236	$aSql = array_merge($aSql,$this->DropTableSQL($tabname));
237	$aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
238	$aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
239	if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again
240	$seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence
241	$aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
242	}
243	$aSql[] = "DROP TABLE $tempname";
244	// recreate the indexes, if they not contain one of the droped columns
245	foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
246	{
247	if (substr($idx_name,-5) != '_pkey' && (!$dropflds \|\| !count(array_intersect($dropflds,$idx_data['columns'])))) {
248	$aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
249	$idx_data['unique'] ? array('UNIQUE') : False));
250	}
251	}
252	$aSql[] = 'COMMIT';
253	return $aSql;
254	}
255
256	function DropTableSQL($tabname)
257	{
258	$sql = ADODB_DataDict::DropTableSQL($tabname);
259
260	$drop_seq = $this->_DropAutoIncrement($tabname);
261	if ($drop_seq) $sql[] = $drop_seq;
262
263	return $sql;
264	}
265
266	// return string must begin with space
267	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
268	{
269	if ($fautoinc) {
270	$ftype = 'SERIAL';
271	return '';
272	}
273	$suffix = '';
274	if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
275	if ($fnotnull) $suffix .= ' NOT NULL';
276	if ($fconstraint) $suffix .= ' '.$fconstraint;
277	return $suffix;
278	}
279
280	// search for a sequece for the given table (asumes the seqence-name contains the table-name!)
281	// if yes return sql to drop it
282	// this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
283	function _DropAutoIncrement($tabname)
284	{
285	$tabname = $this->connection->quote('%'.$tabname.'%');
286
287	$seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
288
289	// check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
290	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'")) {
291	return False;
292	}
293	return "DROP SEQUENCE ".$seq;
294	}
295
296	/*
297	CREATE [ [ LOCAL ] { TEMPORARY \| TEMP } ] TABLE table_name (
298	{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
299	\| table_constraint } [, ... ]
300	)
301	[ INHERITS ( parent_table [, ... ] ) ]
302	[ WITH OIDS \| WITHOUT OIDS ]
303	where column_constraint is:
304	[ CONSTRAINT constraint_name ]
305	{ NOT NULL \| NULL \| UNIQUE \| PRIMARY KEY \|
306	CHECK (expression) \|
307	REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL \| MATCH PARTIAL ]
308	[ ON DELETE action ] [ ON UPDATE action ] }
309	[ DEFERRABLE \| NOT DEFERRABLE ] [ INITIALLY DEFERRED \| INITIALLY IMMEDIATE ]
310	and table_constraint is:
311	[ CONSTRAINT constraint_name ]
312	{ UNIQUE ( column_name [, ... ] ) \|
313	PRIMARY KEY ( column_name [, ... ] ) \|
314	CHECK ( expression ) \|
315	FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
316	[ MATCH FULL \| MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
317	[ DEFERRABLE \| NOT DEFERRABLE ] [ INITIALLY DEFERRED \| INITIALLY IMMEDIATE ]
318	*/
319
320
321	/*
322	CREATE [ UNIQUE ] INDEX index_name ON table
323	[ USING acc_method ] ( column [ ops_name ] [, ...] )
324	[ WHERE predicate ]
325	CREATE [ UNIQUE ] INDEX index_name ON table
326	[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
327	[ WHERE predicate ]
328	*/
329	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
330	{
331	$sql = array();
332
333	if ( isset($idxoptions['REPLACE']) \|\| isset($idxoptions['DROP']) ) {
334	$sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
335	if ( isset($idxoptions['DROP']) )
336	return $sql;
337	}
338
339	if ( empty ($flds) ) {
340	return $sql;
341	}
342
343	$unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
344
345	$s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
346
347	if (isset($idxoptions['HASH']))
348	$s .= 'USING HASH ';
349
350	if ( isset($idxoptions[$this->upperName]) )
351	$s .= $idxoptions[$this->upperName];
352
353	if ( is_array($flds) )
354	$flds = implode(', ',$flds);
355	$s .= '(' . $flds . ')';
356	$sql[] = $s;
357
358	return $sql;
359	}
360
361	function _GetSize($ftype, $ty, $fsize, $fprec)
362	{
363	if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) {
364	$ftype .= "(".$fsize;
365	if (strlen($fprec)) $ftype .= ",".$fprec;
366	$ftype .= ')';
367	}
368	return $ftype;
369	}
370	}
371	?>

Note: See TracBrowser for help on using the repository browser.

Download in other formats: