Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

pgsql.php @ 18754

Visit:

Revision 18754, 24.5 KB checked in by nanasess, 16 years ago (diff)
PEAR::MDB2 のライブラリ追加(#564)

Line
1	<?php
2	// +----------------------------------------------------------------------+
3	// \| PHP versions 4 and 5 \|
4	// +----------------------------------------------------------------------+
5	// \| Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox, \|
6	// \| Stig. S. Bakken, Lukas Smith \|
7	// \| All rights reserved. \|
8	// +----------------------------------------------------------------------+
9	// \| MDB2 is a merge of PEAR DB and Metabases that provides a unified DB \|
10	// \| API as well as database abstraction for PHP applications. \|
11	// \| This LICENSE is in the BSD license style. \|
12	// \| \|
13	// \| Redistribution and use in source and binary forms, with or without \|
14	// \| modification, are permitted provided that the following conditions \|
15	// \| are met: \|
16	// \| \|
17	// \| Redistributions of source code must retain the above copyright \|
18	// \| notice, this list of conditions and the following disclaimer. \|
19	// \| \|
20	// \| Redistributions in binary form must reproduce the above copyright \|
21	// \| notice, this list of conditions and the following disclaimer in the \|
22	// \| documentation and/or other materials provided with the distribution. \|
23	// \| \|
24	// \| Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, \|
25	// \| Lukas Smith nor the names of his contributors may be used to endorse \|
26	// \| or promote products derived from this software without specific prior\|
27	// \| written permission. \|
28	// \| \|
29	// \| THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \|
30	// \| "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT \|
31	// \| LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS \|
32	// \| FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE \|
33	// \| REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, \|
34	// \| INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, \|
35	// \| BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS\|
36	// \| OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED \|
37	// \| AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT \|
38	// \| LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY\|
39	// \| WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE \|
40	// \| POSSIBILITY OF SUCH DAMAGE. \|
41	// +----------------------------------------------------------------------+
42	// \| Authors: Paul Cooper <[email protected]> \|
43	// \| Lorenzo Alberton <[email protected]> \|
44	// +----------------------------------------------------------------------+
45	//
46	// $Id: pgsql.php,v 1.75 2008/08/22 16:36:20 quipo Exp $
47
48	require_once 'MDB2/Driver/Reverse/Common.php';
49
50	/**
51	* MDB2 PostGreSQL driver for the schema reverse engineering module
52	*
53	* @package MDB2
54	* @category Database
55	* @author Paul Cooper <[email protected]>
56	* @author Lorenzo Alberton <[email protected]>
57	*/
58	class MDB2_Driver_Reverse_pgsql extends MDB2_Driver_Reverse_Common
59	{
60	// {{{ getTableFieldDefinition()
61
62	/**
63	* Get the structure of a field into an array
64	*
65	* @param string $table_name name of table that should be used in method
66	* @param string $field_name name of field that should be used in method
67	* @return mixed data array on success, a MDB2 error on failure
68	* @access public
69	*/
70	function getTableFieldDefinition($table_name, $field_name)
71	{
72	$db =& $this->getDBInstance();
73	if (PEAR::isError($db)) {
74	return $db;
75	}
76
77	$result = $db->loadModule('Datatype', null, true);
78	if (PEAR::isError($result)) {
79	return $result;
80	}
81
82	list($schema, $table) = $this->splitTableSchema($table_name);
83
84	$query = "SELECT a.attname AS name,
85	t.typname AS type,
86	CASE a.attlen
87	WHEN -1 THEN
88	CASE t.typname
89	WHEN 'numeric' THEN (a.atttypmod / 65536)
90	WHEN 'decimal' THEN (a.atttypmod / 65536)
91	WHEN 'money' THEN (a.atttypmod / 65536)
92	ELSE CASE a.atttypmod
93	WHEN -1 THEN NULL
94	ELSE a.atttypmod - 4
95	END
96	END
97	ELSE a.attlen
98	END AS length,
99	CASE t.typname
100	WHEN 'numeric' THEN (a.atttypmod % 65536) - 4
101	WHEN 'decimal' THEN (a.atttypmod % 65536) - 4
102	WHEN 'money' THEN (a.atttypmod % 65536) - 4
103	ELSE 0
104	END AS scale,
105	a.attnotnull,
106	a.atttypmod,
107	a.atthasdef,
108	(SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)
109	FROM pg_attrdef d
110	WHERE d.adrelid = a.attrelid
111	AND d.adnum = a.attnum
112	AND a.atthasdef
113	) as default
114	FROM pg_attribute a,
115	pg_class c,
116	pg_type t
117	WHERE c.relname = ".$db->quote($table, 'text')."
118	AND a.atttypid = t.oid
119	AND c.oid = a.attrelid
120	AND NOT a.attisdropped
121	AND a.attnum > 0
122	AND a.attname = ".$db->quote($field_name, 'text')."
123	ORDER BY a.attnum";
124	$column = $db->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
125	if (PEAR::isError($column)) {
126	return $column;
127	}
128
129	if (empty($column)) {
130	return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
131	'it was not specified an existing table column', __FUNCTION__);
132	}
133
134	$column = array_change_key_case($column, CASE_LOWER);
135	$mapped_datatype = $db->datatype->mapNativeDatatype($column);
136	if (PEAR::isError($mapped_datatype)) {
137	return $mapped_datatype;
138	}
139	list($types, $length, $unsigned, $fixed) = $mapped_datatype;
140	$notnull = false;
141	if (!empty($column['attnotnull']) && $column['attnotnull'] == 't') {
142	$notnull = true;
143	}
144	$default = null;
145	if ($column['atthasdef'] === 't'
146	&& !preg_match("/nextval\('([^']+)'/", $column['default'])
147	) {
148	$pattern = '/^\'(.*)\'::[\w ]+$/i';
149	$default = $column['default'];#substr($column['adsrc'], 1, -1);
150	if (is_null($default) && $notnull) {
151	$default = '';
152	} elseif (!empty($default) && preg_match($pattern, $default)) {
153	//remove data type cast
154	$default = preg_replace ($pattern, '\\1', $default);
155	}
156	}
157	$autoincrement = false;
158	if (preg_match("/nextval\('([^']+)'/", $column['default'], $nextvals)) {
159	$autoincrement = true;
160	}
161	$definition[0] = array('notnull' => $notnull, 'nativetype' => $column['type']);
162	if (!is_null($length)) {
163	$definition[0]['length'] = $length;
164	}
165	if (!is_null($unsigned)) {
166	$definition[0]['unsigned'] = $unsigned;
167	}
168	if (!is_null($fixed)) {
169	$definition[0]['fixed'] = $fixed;
170	}
171	if ($default !== false) {
172	$definition[0]['default'] = $default;
173	}
174	if ($autoincrement !== false) {
175	$definition[0]['autoincrement'] = $autoincrement;
176	}
177	foreach ($types as $key => $type) {
178	$definition[$key] = $definition[0];
179	if ($type == 'clob' \|\| $type == 'blob') {
180	unset($definition[$key]['default']);
181	}
182	$definition[$key]['type'] = $type;
183	$definition[$key]['mdb2type'] = $type;
184	}
185	return $definition;
186	}
187
188	// }}}
189	// {{{ getTableIndexDefinition()
190
191	/**
192	* Get the structure of an index into an array
193	*
194	* @param string $table_name name of table that should be used in method
195	* @param string $index_name name of index that should be used in method
196	* @return mixed data array on success, a MDB2 error on failure
197	* @access public
198	*/
199	function getTableIndexDefinition($table_name, $index_name)
200	{
201	$db =& $this->getDBInstance();
202	if (PEAR::isError($db)) {
203	return $db;
204	}
205
206	list($schema, $table) = $this->splitTableSchema($table_name);
207
208	$query = 'SELECT relname, indkey FROM pg_index, pg_class';
209	$query.= ' WHERE pg_class.oid = pg_index.indexrelid';
210	$query.= " AND indisunique != 't' AND indisprimary != 't'";
211	$query.= ' AND pg_class.relname = %s';
212	$index_name_mdb2 = $db->getIndexName($index_name);
213	$row = $db->queryRow(sprintf($query, $db->quote($index_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
214	if (PEAR::isError($row) \|\| empty($row)) {
215	// fallback to the given $index_name, without transformation
216	$row = $db->queryRow(sprintf($query, $db->quote($index_name, 'text')), null, MDB2_FETCHMODE_ASSOC);
217	}
218	if (PEAR::isError($row)) {
219	return $row;
220	}
221
222	if (empty($row)) {
223	return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
224	'it was not specified an existing table index', __FUNCTION__);
225	}
226
227	$row = array_change_key_case($row, CASE_LOWER);
228
229	$db->loadModule('Manager', null, true);
230	$columns = $db->manager->listTableFields($table_name);
231
232	$definition = array();
233
234	$index_column_numbers = explode(' ', $row['indkey']);
235
236	$colpos = 1;
237	foreach ($index_column_numbers as $number) {
238	$definition['fields'][$columns[($number - 1)]] = array(
239	'position' => $colpos++,
240	'sorting' => 'ascending',
241	);
242	}
243	return $definition;
244	}
245
246	// }}}
247	// {{{ getTableConstraintDefinition()
248
249	/**
250	* Get the structure of a constraint into an array
251	*
252	* @param string $table_name name of table that should be used in method
253	* @param string $constraint_name name of constraint that should be used in method
254	* @return mixed data array on success, a MDB2 error on failure
255	* @access public
256	*/
257	function getTableConstraintDefinition($table_name, $constraint_name)
258	{
259	$db =& $this->getDBInstance();
260	if (PEAR::isError($db)) {
261	return $db;
262	}
263
264	list($schema, $table) = $this->splitTableSchema($table_name);
265
266	$query = "SELECT c.oid,
267	c.conname AS constraint_name,
268	CASE WHEN c.contype = 'c' THEN 1 ELSE 0 END AS \"check\",
269	CASE WHEN c.contype = 'f' THEN 1 ELSE 0 END AS \"foreign\",
270	CASE WHEN c.contype = 'p' THEN 1 ELSE 0 END AS \"primary\",
271	CASE WHEN c.contype = 'u' THEN 1 ELSE 0 END AS \"unique\",
272	CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS deferrable,
273	CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS initiallydeferred,
274	--array_to_string(c.conkey, ' ') AS constraint_key,
275	t.relname AS table_name,
276	t2.relname AS references_table,
277	CASE confupdtype
278	WHEN 'a' THEN 'NO ACTION'
279	WHEN 'r' THEN 'RESTRICT'
280	WHEN 'c' THEN 'CASCADE'
281	WHEN 'n' THEN 'SET NULL'
282	WHEN 'd' THEN 'SET DEFAULT'
283	END AS onupdate,
284	CASE confdeltype
285	WHEN 'a' THEN 'NO ACTION'
286	WHEN 'r' THEN 'RESTRICT'
287	WHEN 'c' THEN 'CASCADE'
288	WHEN 'n' THEN 'SET NULL'
289	WHEN 'd' THEN 'SET DEFAULT'
290	END AS ondelete,
291	CASE confmatchtype
292	WHEN 'u' THEN 'UNSPECIFIED'
293	WHEN 'f' THEN 'FULL'
294	WHEN 'p' THEN 'PARTIAL'
295	END AS match,
296	--array_to_string(c.confkey, ' ') AS fk_constraint_key,
297	consrc
298	FROM pg_constraint c
299	LEFT JOIN pg_class t ON c.conrelid = t.oid
300	LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
301	WHERE c.conname = %s
302	AND t.relname = " . $db->quote($table, 'text');
303	$constraint_name_mdb2 = $db->getIndexName($constraint_name);
304	$row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
305	if (PEAR::isError($row) \|\| empty($row)) {
306	// fallback to the given $index_name, without transformation
307	$constraint_name_mdb2 = $constraint_name;
308	$row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
309	}
310	if (PEAR::isError($row)) {
311	return $row;
312	}
313	$uniqueIndex = false;
314	if (empty($row)) {
315	// We might be looking for a UNIQUE index that was not created
316	// as a constraint but should be treated as such.
317	$query = 'SELECT relname AS constraint_name,
318	indkey,
319	0 AS "check",
320	0 AS "foreign",
321	0 AS "primary",
322	1 AS "unique",
323	0 AS deferrable,
324	0 AS initiallydeferred,
325	NULL AS references_table,
326	NULL AS onupdate,
327	NULL AS ondelete,
328	NULL AS match
329	FROM pg_index, pg_class
330	WHERE pg_class.oid = pg_index.indexrelid
331	AND indisunique = \'t\'
332	AND pg_class.relname = %s';
333	$constraint_name_mdb2 = $db->getIndexName($constraint_name);
334	$row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
335	if (PEAR::isError($row) \|\| empty($row)) {
336	// fallback to the given $index_name, without transformation
337	$constraint_name_mdb2 = $constraint_name;
338	$row = $db->queryRow(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null, MDB2_FETCHMODE_ASSOC);
339	}
340	if (PEAR::isError($row)) {
341	return $row;
342	}
343	if (empty($row)) {
344	return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
345	$constraint_name . ' is not an existing table constraint', __FUNCTION__);
346	}
347	$uniqueIndex = true;
348	}
349
350	$row = array_change_key_case($row, CASE_LOWER);
351
352	$definition = array(
353	'primary' => (boolean)$row['primary'],
354	'unique' => (boolean)$row['unique'],
355	'foreign' => (boolean)$row['foreign'],
356	'check' => (boolean)$row['check'],
357	'fields' => array(),
358	'references' => array(
359	'table' => $row['references_table'],
360	'fields' => array(),
361	),
362	'deferrable' => (boolean)$row['deferrable'],
363	'initiallydeferred' => (boolean)$row['initiallydeferred'],
364	'onupdate' => $row['onupdate'],
365	'ondelete' => $row['ondelete'],
366	'match' => $row['match'],
367	);
368
369	if ($uniqueIndex) {
370	$db->loadModule('Manager', null, true);
371	$columns = $db->manager->listTableFields($table_name);
372	$index_column_numbers = explode(' ', $row['indkey']);
373	$colpos = 1;
374	foreach ($index_column_numbers as $number) {
375	$definition['fields'][$columns[($number - 1)]] = array(
376	'position' => $colpos++,
377	'sorting' => 'ascending',
378	);
379	}
380	return $definition;
381	}
382
383	$query = 'SELECT a.attname
384	FROM pg_constraint c
385	LEFT JOIN pg_class t ON c.conrelid = t.oid
386	LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.conkey)
387	WHERE c.conname = %s
388	AND t.relname = ' . $db->quote($table, 'text');
389	$fields = $db->queryCol(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null);
390	if (PEAR::isError($fields)) {
391	return $fields;
392	}
393	$colpos = 1;
394	foreach ($fields as $field) {
395	$definition['fields'][$field] = array(
396	'position' => $colpos++,
397	'sorting' => 'ascending',
398	);
399	}
400
401	if ($definition['foreign']) {
402	$query = 'SELECT a.attname
403	FROM pg_constraint c
404	LEFT JOIN pg_class t ON c.confrelid = t.oid
405	LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.confkey)
406	WHERE c.conname = %s
407	AND t.relname = ' . $db->quote($definition['references']['table'], 'text');
408	$foreign_fields = $db->queryCol(sprintf($query, $db->quote($constraint_name_mdb2, 'text')), null);
409	if (PEAR::isError($foreign_fields)) {
410	return $foreign_fields;
411	}
412	$colpos = 1;
413	foreach ($foreign_fields as $foreign_field) {
414	$definition['references']['fields'][$foreign_field] = array(
415	'position' => $colpos++,
416	);
417	}
418	}
419
420	if ($definition['check']) {
421	$check_def = $db->queryOne("SELECT pg_get_constraintdef(" . $row['oid'] . ", 't')");
422	// ...
423	}
424	return $definition;
425	}
426
427	// }}}
428	// {{{ getTriggerDefinition()
429
430	/**
431	* Get the structure of a trigger into an array
432	*
433	* EXPERIMENTAL
434	*
435	* WARNING: this function is experimental and may change the returned value
436	* at any time until labelled as non-experimental
437	*
438	* @param string $trigger name of trigger that should be used in method
439	* @return mixed data array on success, a MDB2 error on failure
440	* @access public
441	*
442	* @TODO: add support for plsql functions and functions with args
443	*/
444	function getTriggerDefinition($trigger)
445	{
446	$db =& $this->getDBInstance();
447	if (PEAR::isError($db)) {
448	return $db;
449	}
450
451	$query = "SELECT trg.tgname AS trigger_name,
452	tbl.relname AS table_name,
453	CASE
454	WHEN p.proname IS NOT NULL THEN 'EXECUTE PROCEDURE ' \|\| p.proname \|\| '();'
455	ELSE ''
456	END AS trigger_body,
457	CASE trg.tgtype & cast(2 as int2)
458	WHEN 0 THEN 'AFTER'
459	ELSE 'BEFORE'
460	END AS trigger_type,
461	CASE trg.tgtype & cast(28 as int2)
462	WHEN 16 THEN 'UPDATE'
463	WHEN 8 THEN 'DELETE'
464	WHEN 4 THEN 'INSERT'
465	WHEN 20 THEN 'INSERT, UPDATE'
466	WHEN 28 THEN 'INSERT, UPDATE, DELETE'
467	WHEN 24 THEN 'UPDATE, DELETE'
468	WHEN 12 THEN 'INSERT, DELETE'
469	END AS trigger_event,
470	CASE trg.tgenabled
471	WHEN 'O' THEN 't'
472	ELSE trg.tgenabled
473	END AS trigger_enabled,
474	obj_description(trg.oid, 'pg_trigger') AS trigger_comment
475	FROM pg_trigger trg,
476	pg_class tbl,
477	pg_proc p
478	WHERE trg.tgrelid = tbl.oid
479	AND trg.tgfoid = p.oid
480	AND trg.tgname = ". $db->quote($trigger, 'text');
481	$types = array(
482	'trigger_name' => 'text',
483	'table_name' => 'text',
484	'trigger_body' => 'text',
485	'trigger_type' => 'text',
486	'trigger_event' => 'text',
487	'trigger_comment' => 'text',
488	'trigger_enabled' => 'boolean',
489	);
490	return $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
491	}
492
493	// }}}
494	// {{{ tableInfo()
495
496	/**
497	* Returns information about a table or a result set
498	*
499	* NOTE: only supports 'table' and 'flags' if <var>$result</var>
500	* is a table name.
501	*
502	* @param object\|string $result MDB2_result object from a query or a
503	* string containing the name of a table.
504	* While this also accepts a query result
505	* resource identifier, this behavior is
506	* deprecated.
507	* @param int $mode a valid tableInfo mode
508	*
509	* @return array an associative array with the information requested.
510	* A MDB2_Error object on failure.
511	*
512	* @see MDB2_Driver_Common::tableInfo()
513	*/
514	function tableInfo($result, $mode = null)
515	{
516	if (is_string($result)) {
517	return parent::tableInfo($result, $mode);
518	}
519
520	$db =& $this->getDBInstance();
521	if (PEAR::isError($db)) {
522	return $db;
523	}
524
525	$resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
526	if (!is_resource($resource)) {
527	return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
528	'Could not generate result resource', __FUNCTION__);
529	}
530
531	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
532	if ($db->options['field_case'] == CASE_LOWER) {
533	$case_func = 'strtolower';
534	} else {
535	$case_func = 'strtoupper';
536	}
537	} else {
538	$case_func = 'strval';
539	}
540
541	$count = @pg_num_fields($resource);
542	$res = array();
543
544	if ($mode) {
545	$res['num_fields'] = $count;
546	}
547
548	$db->loadModule('Datatype', null, true);
549	for ($i = 0; $i < $count; $i++) {
550	$res[$i] = array(
551	'table' => function_exists('pg_field_table') ? @pg_field_table($resource, $i) : '',
552	'name' => $case_func(@pg_field_name($resource, $i)),
553	'type' => @pg_field_type($resource, $i),
554	'length' => @pg_field_size($resource, $i),
555	'flags' => '',
556	);
557	$mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
558	if (PEAR::isError($mdb2type_info)) {
559	return $mdb2type_info;
560	}
561	$res[$i]['mdb2type'] = $mdb2type_info[0][0];
562	if ($mode & MDB2_TABLEINFO_ORDER) {
563	$res['order'][$res[$i]['name']] = $i;
564	}
565	if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
566	$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
567	}
568	}
569
570	return $res;
571	}
572	}
573	?>

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

Context Navigation

source: branches/version-2_5-dev/data/module/MDB2/Driver/Reverse/pgsql.php @ 18754

Download in other formats: