Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

datadict-postgres.inc.php @ 8222

Revision 8222, 15.5 KB checked in by angelo, 11 years ago (diff)
Ticket #3491 - Compatibilizar Expresso com novas versoes do PHP
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2
3	/**
4	V5.18 3 Sep 2012 (c) 2000-2012 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
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) && !empty($fieldobj->primary_key) && !empty($fieldobj->unique) &&
34	!empty($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 'TS':
104	case 'T': return 'TIMESTAMP';
105
106	case 'L': return 'BOOLEAN';
107	case 'I': return 'INTEGER';
108	case 'I1': return 'SMALLINT';
109	case 'I2': return 'INT2';
110	case 'I4': return 'INT4';
111	case 'I8': return 'INT8';
112
113	case 'F': return 'FLOAT8';
114	case 'N': return 'NUMERIC';
115	default:
116	return $meta;
117	}
118	}
119
120	/**
121	* Adding a new Column
122	*
123	* reimplementation of the default function as postgres does NOT allow to set the default in the same statement
124	*
125	* @param string $tabname table-name
126	* @param string $flds column-names and types for the changed columns
127	* @return array with SQL strings
128	*/
129	function AddColumnSQL($tabname, $flds)
130	{
131	$tabname = $this->TableName ($tabname);
132	$sql = array();
133	$not_null = false;
134	list($lines,$pkey) = $this->_GenFields($flds);
135	$alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
136	foreach($lines as $v) {
137	if (($not_null = preg_match('/NOT NULL/i',$v))) {
138	$v = preg_replace('/NOT NULL/i','',$v);
139	}
140	if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'\|\"[^\"]+\"\|[^ ]+)/',$v,$matches)) {
141	list(,$colname,$default) = $matches;
142	$sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
143	$sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
144	$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
145	} else {
146	$sql[] = $alter . $v;
147	}
148	if ($not_null) {
149	list($colname) = explode(' ',$v);
150	$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
151	}
152	}
153	return $sql;
154	}
155
156
157	function DropIndexSQL ($idxname, $tabname = NULL)
158	{
159	return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
160	}
161
162	/**
163	* Change the definition of one column
164	*
165	* Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
166	* to allow, recreating the table and copying the content over to the new table
167	* @param string $tabname table-name
168	* @param string $flds column-name and type for the changed column
169	* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
170	* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
171	* @return array with SQL strings
172	*/
173	/*
174	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
175	{
176	if (!$tableflds) {
177	if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
178	return array();
179	}
180	return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
181	}*/
182
183	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
184	{
185	// Check if alter single column datatype available - works with 8.0+
186	$has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
187
188	if ($has_alter_column) {
189	$tabname = $this->TableName($tabname);
190	$sql = array();
191	list($lines,$pkey) = $this->_GenFields($flds);
192	$set_null = false;
193	$alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
194	foreach($lines as $v) {
195	if ($not_null = preg_match('/NOT NULL/i',$v)) {
196	$v = preg_replace('/NOT NULL/i','',$v);
197	}
198	// this next block doesn't work - there is no way that I can see to
199	// explicitly ask a column to be null using $flds
200	else if ($set_null = preg_match('/NULL/i',$v)) {
201	// if they didn't specify not null, see if they explicitely asked for null
202	$v = preg_replace('/\sNULL/i','',$v);
203	}
204
205	if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'\|\"[^\"]+\"\|[^ ]+)/',$v,$matches)) {
206	$existing = $this->MetaColumns($tabname);
207	list(,$colname,$default) = $matches;
208	if ($this->connection) $old_coltype = $this->connection->MetaType($existing[strtoupper($colname)]);
209	else $old_coltype = $t;
210	$v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
211	$t = trim(str_replace('DEFAULT '.$default,'',$v));
212
213	// Type change from bool to int
214	if ( $old_coltype == 'L' && $t == 'INTEGER' ) {
215	$sql[] = $alter . $colname . ' DROP DEFAULT';
216	$sql[] = $alter . $colname . " TYPE $t USING ($colname::BOOL)::INT";
217	$sql[] = $alter . $colname . " SET DEFAULT $default";
218	}
219	// Type change from int to bool
220	else if ( $old_coltype == 'I' && $t == 'BOOLEAN' ) {
221	$sql[] = $alter . $colname . ' DROP DEFAULT';
222	$sql[] = $alter . $colname . " TYPE $t USING CASE WHEN $colname = 0 THEN false ELSE true END";
223	$sql[] = $alter . $colname . " SET DEFAULT " . $this->connection->qstr($default);
224	}
225	// Any other column types conversion
226	else {
227	$sql[] = $alter . $colname . " TYPE $t";
228	$sql[] = $alter . $colname . " SET DEFAULT $default";
229	}
230
231
232	}
233	else {
234	// drop default?
235	preg_match ('/^\s(\S+)\s+(.)$/',$v,$matches);
236	list (,$colname,$rest) = $matches;
237	$sql[] = $alter . $colname . ' TYPE ' . $rest;
238	}
239
240	# list($colname) = explode(' ',$v);
241	if ($not_null) {
242	// this does not error out if the column is already not null
243	$sql[] = $alter . $colname . ' SET NOT NULL';
244	}
245	if ($set_null) {
246	// this does not error out if the column is already null
247	$sql[] = $alter . $colname . ' DROP NOT NULL';
248	}
249	}
250	return $sql;
251	}
252
253	// does not have alter column
254	if (!$tableflds) {
255	if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
256	return array();
257	}
258	return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
259	}
260
261	/**
262	* Drop one column
263	*
264	* Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
265	* to allow, recreating the table and copying the content over to the new table
266	* @param string $tabname table-name
267	* @param string $flds column-name and type for the changed column
268	* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
269	* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
270	* @return array with SQL strings
271	*/
272	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
273	{
274	$has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
275	if (!$has_drop_column && !$tableflds) {
276	if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
277	return array();
278	}
279	if ($has_drop_column) {
280	return ADODB_DataDict::DropColumnSQL($tabname, $flds);
281	}
282	return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
283	}
284
285	/**
286	* Save the content into a temp. table, drop and recreate the original table and copy the content back in
287	*
288	* We also take care to set the values of the sequenz and recreate the indexes.
289	* All this is done in a transaction, to not loose the content of the table, if something went wrong!
290	* @internal
291	* @param string $tabname table-name
292	* @param string $dropflds column-names to drop
293	* @param string $tableflds complete defintion of the new table, eg. for postgres
294	* @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
295	* @return array with SQL strings
296	*/
297	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
298	{
299	if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
300	$copyflds = array();
301	foreach($this->MetaColumns($tabname) as $fld) {
302	if (!$dropflds \|\| !in_array($fld->name,$dropflds)) {
303	// we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
304	if (preg_match('/'.$fld->name.' (I\|I2\|I4\|I8\|N\|F)/i',$tableflds,$matches) &&
305	in_array($fld->type,array('varchar','char','text','bytea'))) {
306	$copyflds[] = "to_number($fld->name,'S9999999999999D99')";
307	} else {
308	$copyflds[] = $fld->name;
309	}
310	// identify the sequence name and the fld its on
311	if ($fld->primary_key && $fld->has_default &&
312	preg_match("/nextval$'([^']+)'::text$/",$fld->default_value,$matches)) {
313	$seq_name = $matches[1];
314	$seq_fld = $fld->name;
315	}
316	}
317	}
318	$copyflds = implode(', ',$copyflds);
319
320	$tempname = $tabname.'_tmp';
321	$aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table
322	$aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
323	$aSql = array_merge($aSql,$this->DropTableSQL($tabname));
324	$aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
325	$aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
326	if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again
327	$seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence
328	$aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
329	}
330	$aSql[] = "DROP TABLE $tempname";
331	// recreate the indexes, if they not contain one of the droped columns
332	foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
333	{
334	if (substr($idx_name,-5) != '_pkey' && (!$dropflds \|\| !count(array_intersect($dropflds,$idx_data['columns'])))) {
335	$aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
336	$idx_data['unique'] ? array('UNIQUE') : False));
337	}
338	}
339	$aSql[] = 'COMMIT';
340	return $aSql;
341	}
342
343	function DropTableSQL($tabname)
344	{
345	$sql = ADODB_DataDict::DropTableSQL($tabname);
346
347	$drop_seq = $this->_DropAutoIncrement($tabname);
348	if ($drop_seq) $sql[] = $drop_seq;
349
350	return $sql;
351	}
352
353	// return string must begin with space
354	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
355	{
356	if ($fautoinc) {
357	$ftype = 'SERIAL';
358	return '';
359	}
360	$suffix = '';
361	if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
362	if ($fnotnull) $suffix .= ' NOT NULL';
363	if ($fconstraint) $suffix .= ' '.$fconstraint;
364	return $suffix;
365	}
366
367	// search for a sequece for the given table (asumes the seqence-name contains the table-name!)
368	// if yes return sql to drop it
369	// this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
370	function _DropAutoIncrement($tabname)
371	{
372	$tabname = $this->connection->quote('%'.$tabname.'%');
373
374	$seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
375
376	// check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
377	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'")) {
378	return False;
379	}
380	return "DROP SEQUENCE ".$seq;
381	}
382
383	function RenameTableSQL($tabname,$newname)
384	{
385	if (!empty($this->schema)) {
386	$rename_from = $this->TableName($tabname);
387	$schema_save = $this->schema;
388	$this->schema = false;
389	$rename_to = $this->TableName($newname);
390	$this->schema = $schema_save;
391	return array (sprintf($this->renameTable, $rename_from, $rename_to));
392	}
393
394	return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
395	}
396
397	/*
398	CREATE [ [ LOCAL ] { TEMPORARY \| TEMP } ] TABLE table_name (
399	{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
400	\| table_constraint } [, ... ]
401	)
402	[ INHERITS ( parent_table [, ... ] ) ]
403	[ WITH OIDS \| WITHOUT OIDS ]
404	where column_constraint is:
405	[ CONSTRAINT constraint_name ]
406	{ NOT NULL \| NULL \| UNIQUE \| PRIMARY KEY \|
407	CHECK (expression) \|
408	REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL \| MATCH PARTIAL ]
409	[ ON DELETE action ] [ ON UPDATE action ] }
410	[ DEFERRABLE \| NOT DEFERRABLE ] [ INITIALLY DEFERRED \| INITIALLY IMMEDIATE ]
411	and table_constraint is:
412	[ CONSTRAINT constraint_name ]
413	{ UNIQUE ( column_name [, ... ] ) \|
414	PRIMARY KEY ( column_name [, ... ] ) \|
415	CHECK ( expression ) \|
416	FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
417	[ MATCH FULL \| MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
418	[ DEFERRABLE \| NOT DEFERRABLE ] [ INITIALLY DEFERRED \| INITIALLY IMMEDIATE ]
419	*/
420
421
422	/*
423	CREATE [ UNIQUE ] INDEX index_name ON table
424	[ USING acc_method ] ( column [ ops_name ] [, ...] )
425	[ WHERE predicate ]
426	CREATE [ UNIQUE ] INDEX index_name ON table
427	[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
428	[ WHERE predicate ]
429	*/
430	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
431	{
432	$sql = array();
433
434	if ( isset($idxoptions['REPLACE']) \|\| isset($idxoptions['DROP']) ) {
435	$sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
436	if ( isset($idxoptions['DROP']) )
437	return $sql;
438	}
439
440	if ( empty ($flds) ) {
441	return $sql;
442	}
443
444	$unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
445
446	$s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
447
448	if (isset($idxoptions['HASH']))
449	$s .= 'USING HASH ';
450
451	if ( isset($idxoptions[$this->upperName]) )
452	$s .= $idxoptions[$this->upperName];
453
454	if ( is_array($flds) )
455	$flds = implode(', ',$flds);
456	$s .= '(' . $flds . ')';
457	$sql[] = $s;
458
459	return $sql;
460	}
461
462	function _GetSize($ftype, $ty, $fsize, $fprec)
463	{
464	if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty != 'I' && strpos($ftype,'(') === false) {
465	$ftype .= "(".$fsize;
466	if (strlen($fprec)) $ftype .= ",".$fprec;
467	$ftype .= ')';
468	}
469	return $ftype;
470	}
471	}
472	?>

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

Context Navigation

source: sandbox/2.5.1-evolucao/phpgwapi/inc/adodb/datadict/datadict-postgres.inc.php @ 8222

Download in other formats: