Context Navigation

pgsql.php @ 20764

Visit:

Revision 20764, 34.0 KB checked in by nanasess, 15 years ago (diff)
#601 (コピーライトの更新)
Property svn:eol-style set to `LF` Property svn:mime-type set to `text/x-httpd-php; charset=UTF-8`

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	// \| Author: Paul Cooper <[email protected]> \|
43	// +----------------------------------------------------------------------+
44	//
45	// $Id: pgsql.php,v 1.87 2008/11/29 14:09:59 afz Exp $
46
47	require_once 'MDB2/Driver/Manager/Common.php';
48
49	/**
50	* MDB2 MySQL driver for the management modules
51	*
52	* @package MDB2
53	* @category Database
54	* @author Paul Cooper <[email protected]>
55	*/
56	class MDB2_Driver_Manager_pgsql extends MDB2_Driver_Manager_Common
57	{
58	// {{{ createDatabase()
59
60	/**
61	* create a new database
62	*
63	* @param string $name name of the database that should be created
64	* @param array $options array with charset info
65	*
66	* @return mixed MDB2_OK on success, a MDB2 error on failure
67	* @access public
68	*/
69	function createDatabase($name, $options = array())
70	{
71	$db =& $this->getDBInstance();
72	if (PEAR::isError($db)) {
73	return $db;
74	}
75
76	$name = $db->quoteIdentifier($name, true);
77	$query = 'CREATE DATABASE ' . $name;
78	if (!empty($options['charset'])) {
79	$query .= ' WITH ENCODING ' . $db->quote($options['charset'], 'text');
80	}
81	return $db->standaloneQuery($query, null, true);
82	}
83
84	// }}}
85	// {{{ alterDatabase()
86
87	/**
88	* alter an existing database
89	*
90	* @param string $name name of the database that is intended to be changed
91	* @param array $options array with name, owner info
92	*
93	* @return mixed MDB2_OK on success, a MDB2 error on failure
94	* @access public
95	*/
96	function alterDatabase($name, $options = array())
97	{
98	$db =& $this->getDBInstance();
99	if (PEAR::isError($db)) {
100	return $db;
101	}
102
103	$query = 'ALTER DATABASE '. $db->quoteIdentifier($name, true);
104	if (!empty($options['name'])) {
105	$query .= ' RENAME TO ' . $options['name'];
106	}
107	if (!empty($options['owner'])) {
108	$query .= ' OWNER TO ' . $options['owner'];
109	}
110	return $db->standaloneQuery($query, null, true);
111	}
112
113	// }}}
114	// {{{ dropDatabase()
115
116	/**
117	* drop an existing database
118	*
119	* @param string $name name of the database that should be dropped
120	* @return mixed MDB2_OK on success, a MDB2 error on failure
121	* @access public
122	*/
123	function dropDatabase($name)
124	{
125	$db =& $this->getDBInstance();
126	if (PEAR::isError($db)) {
127	return $db;
128	}
129
130	$name = $db->quoteIdentifier($name, true);
131	$query = "DROP DATABASE $name";
132	return $db->standaloneQuery($query, null, true);
133	}
134
135	// }}}
136	// {{{ _getAdvancedFKOptions()
137
138	/**
139	* Return the FOREIGN KEY query section dealing with non-standard options
140	* as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
141	*
142	* @param array $definition
143	* @return string
144	* @access protected
145	*/
146	function _getAdvancedFKOptions($definition)
147	{
148	$query = '';
149	if (!empty($definition['match'])) {
150	$query .= ' MATCH '.$definition['match'];
151	}
152	if (!empty($definition['onupdate'])) {
153	$query .= ' ON UPDATE '.$definition['onupdate'];
154	}
155	if (!empty($definition['ondelete'])) {
156	$query .= ' ON DELETE '.$definition['ondelete'];
157	}
158	if (!empty($definition['deferrable'])) {
159	$query .= ' DEFERRABLE';
160	} else {
161	$query .= ' NOT DEFERRABLE';
162	}
163	if (!empty($definition['initiallydeferred'])) {
164	$query .= ' INITIALLY DEFERRED';
165	} else {
166	$query .= ' INITIALLY IMMEDIATE';
167	}
168	return $query;
169	}
170
171	// }}}
172	// {{{ truncateTable()
173
174	/**
175	* Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
176	* it falls back to a DELETE FROM TABLE query)
177	*
178	* @param string $name name of the table that should be truncated
179	* @return mixed MDB2_OK on success, a MDB2 error on failure
180	* @access public
181	*/
182	function truncateTable($name)
183	{
184	$db =& $this->getDBInstance();
185	if (PEAR::isError($db)) {
186	return $db;
187	}
188
189	$name = $db->quoteIdentifier($name, true);
190	return $db->exec("TRUNCATE TABLE $name");
191	}
192
193	// }}}
194	// {{{ vacuum()
195
196	/**
197	* Optimize (vacuum) all the tables in the db (or only the specified table)
198	* and optionally run ANALYZE.
199	*
200	* @param string $table table name (all the tables if empty)
201	* @param array $options an array with driver-specific options:
202	* - timeout [int] (in seconds) [mssql-only]
203	* - analyze [boolean] [pgsql and mysql]
204	* - full [boolean] [pgsql-only]
205	* - freeze [boolean] [pgsql-only]
206	*
207	* @return mixed MDB2_OK success, a MDB2 error on failure
208	* @access public
209	*/
210	function vacuum($table = null, $options = array())
211	{
212	$db =& $this->getDBInstance();
213	if (PEAR::isError($db)) {
214	return $db;
215	}
216	$query = 'VACUUM';
217
218	if (!empty($options['full'])) {
219	$query .= ' FULL';
220	}
221	if (!empty($options['freeze'])) {
222	$query .= ' FREEZE';
223	}
224	if (!empty($options['analyze'])) {
225	$query .= ' ANALYZE';
226	}
227
228	if (!empty($table)) {
229	$query .= ' '.$db->quoteIdentifier($table, true);
230	}
231	return $db->exec($query);
232	}
233
234	// }}}
235	// {{{ alterTable()
236
237	/**
238	* alter an existing table
239	*
240	* @param string $name name of the table that is intended to be changed.
241	* @param array $changes associative array that contains the details of each type
242	* of change that is intended to be performed. The types of
243	* changes that are currently supported are defined as follows:
244	*
245	* name
246	*
247	* New name for the table.
248	*
249	* add
250	*
251	* Associative array with the names of fields to be added as
252	* indexes of the array. The value of each entry of the array
253	* should be set to another associative array with the properties
254	* of the fields to be added. The properties of the fields should
255	* be the same as defined by the MDB2 parser.
256	*
257	*
258	* remove
259	*
260	* Associative array with the names of fields to be removed as indexes
261	* of the array. Currently the values assigned to each entry are ignored.
262	* An empty array should be used for future compatibility.
263	*
264	* rename
265	*
266	* Associative array with the names of fields to be renamed as indexes
267	* of the array. The value of each entry of the array should be set to
268	* another associative array with the entry named name with the new
269	* field name and the entry named Declaration that is expected to contain
270	* the portion of the field declaration already in DBMS specific SQL code
271	* as it is used in the CREATE TABLE statement.
272	*
273	* change
274	*
275	* Associative array with the names of the fields to be changed as indexes
276	* of the array. Keep in mind that if it is intended to change either the
277	* name of a field and any other properties, the change array entries
278	* should have the new names of the fields as array indexes.
279	*
280	* The value of each entry of the array should be set to another associative
281	* array with the properties of the fields to that are meant to be changed as
282	* array entries. These entries should be assigned to the new values of the
283	* respective properties. The properties of the fields should be the same
284	* as defined by the MDB2 parser.
285	*
286	* Example
287	* array(
288	* 'name' => 'userlist',
289	* 'add' => array(
290	* 'quota' => array(
291	* 'type' => 'integer',
292	* 'unsigned' => 1
293	* )
294	* ),
295	* 'remove' => array(
296	* 'file_limit' => array(),
297	* 'time_limit' => array()
298	* ),
299	* 'change' => array(
300	* 'name' => array(
301	* 'length' => '20',
302	* 'definition' => array(
303	* 'type' => 'text',
304	* 'length' => 20,
305	* ),
306	* )
307	* ),
308	* 'rename' => array(
309	* 'sex' => array(
310	* 'name' => 'gender',
311	* 'definition' => array(
312	* 'type' => 'text',
313	* 'length' => 1,
314	* 'default' => 'M',
315	* ),
316	* )
317	* )
318	* )
319	*
320	* @param boolean $check indicates whether the function should just check if the DBMS driver
321	* can perform the requested table alterations if the value is true or
322	* actually perform them otherwise.
323	* @access public
324	*
325	* @return mixed MDB2_OK on success, a MDB2 error on failure
326	*/
327	function alterTable($name, $changes, $check)
328	{
329	$db =& $this->getDBInstance();
330	if (PEAR::isError($db)) {
331	return $db;
332	}
333
334	foreach ($changes as $change_name => $change) {
335	switch ($change_name) {
336	case 'add':
337	case 'remove':
338	case 'change':
339	case 'name':
340	case 'rename':
341	break;
342	default:
343	return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
344	'change type "'.$change_name.'\" not yet supported', __FUNCTION__);
345	}
346	}
347
348	if ($check) {
349	return MDB2_OK;
350	}
351
352	$name = $db->quoteIdentifier($name, true);
353
354	if (!empty($changes['remove']) && is_array($changes['remove'])) {
355	foreach ($changes['remove'] as $field_name => $field) {
356	$field_name = $db->quoteIdentifier($field_name, true);
357	$query = 'DROP ' . $field_name;
358	$result = $db->exec("ALTER TABLE $name $query");
359	if (PEAR::isError($result)) {
360	return $result;
361	}
362	}
363	}
364
365	if (!empty($changes['rename']) && is_array($changes['rename'])) {
366	foreach ($changes['rename'] as $field_name => $field) {
367	$field_name = $db->quoteIdentifier($field_name, true);
368	$result = $db->exec("ALTER TABLE $name RENAME COLUMN $field_name TO ".$db->quoteIdentifier($field['name'], true));
369	if (PEAR::isError($result)) {
370	return $result;
371	}
372	}
373	}
374
375	if (!empty($changes['add']) && is_array($changes['add'])) {
376	foreach ($changes['add'] as $field_name => $field) {
377	$query = 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);
378	$result = $db->exec("ALTER TABLE $name $query");
379	if (PEAR::isError($result)) {
380	return $result;
381	}
382	}
383	}
384
385	if (!empty($changes['change']) && is_array($changes['change'])) {
386	foreach ($changes['change'] as $field_name => $field) {
387	$field_name = $db->quoteIdentifier($field_name, true);
388	if (!empty($field['definition']['type'])) {
389	$server_info = $db->getServerVersion();
390	if (PEAR::isError($server_info)) {
391	return $server_info;
392	}
393	if (is_array($server_info) && $server_info['major'] < 8) {
394	return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
395	'changing column type for "'.$change_name.'\" requires PostgreSQL 8.0 or above', __FUNCTION__);
396	}
397	$db->loadModule('Datatype', null, true);
398	$type = $db->datatype->getTypeDeclaration($field['definition']);
399	$query = "ALTER $field_name TYPE $type USING CAST($field_name AS $type)";
400	$result = $db->exec("ALTER TABLE $name $query");
401	if (PEAR::isError($result)) {
402	return $result;
403	}
404	}
405	if (array_key_exists('default', $field['definition'])) {
406	$query = "ALTER $field_name SET DEFAULT ".$db->quote($field['definition']['default'], $field['definition']['type']);
407	$result = $db->exec("ALTER TABLE $name $query");
408	if (PEAR::isError($result)) {
409	return $result;
410	}
411	}
412	if (!empty($field['definition']['notnull'])) {
413	$query = "ALTER $field_name ".($field['definition']['notnull'] ? 'SET' : 'DROP').' NOT NULL';
414	$result = $db->exec("ALTER TABLE $name $query");
415	if (PEAR::isError($result)) {
416	return $result;
417	}
418	}
419	}
420	}
421
422	if (!empty($changes['name'])) {
423	$change_name = $db->quoteIdentifier($changes['name'], true);
424	$result = $db->exec("ALTER TABLE $name RENAME TO ".$change_name);
425	if (PEAR::isError($result)) {
426	return $result;
427	}
428	}
429
430	return MDB2_OK;
431	}
432
433	// }}}
434	// {{{ listDatabases()
435
436	/**
437	* list all databases
438	*
439	* @return mixed array of database names on success, a MDB2 error on failure
440	* @access public
441	*/
442	function listDatabases()
443	{
444	$db =& $this->getDBInstance();
445	if (PEAR::isError($db)) {
446	return $db;
447	}
448
449	$query = 'SELECT datname FROM pg_database';
450	$result2 = $db->standaloneQuery($query, array('text'), false);
451	if (!MDB2::isResultCommon($result2)) {
452	return $result2;
453	}
454
455	$result = $result2->fetchCol();
456	$result2->free();
457	if (PEAR::isError($result)) {
458	return $result;
459	}
460	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
461	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
462	}
463	return $result;
464	}
465
466	// }}}
467	// {{{ listUsers()
468
469	/**
470	* list all users
471	*
472	* @return mixed array of user names on success, a MDB2 error on failure
473	* @access public
474	*/
475	function listUsers()
476	{
477	$db =& $this->getDBInstance();
478	if (PEAR::isError($db)) {
479	return $db;
480	}
481
482	$query = 'SELECT usename FROM pg_user';
483	$result2 = $db->standaloneQuery($query, array('text'), false);
484	if (!MDB2::isResultCommon($result2)) {
485	return $result2;
486	}
487
488	$result = $result2->fetchCol();
489	$result2->free();
490	return $result;
491	}
492
493	// }}}
494	// {{{ listViews()
495
496	/**
497	* list all views in the current database
498	*
499	* @return mixed array of view names on success, a MDB2 error on failure
500	* @access public
501	*/
502	function listViews()
503	{
504	$db =& $this->getDBInstance();
505	if (PEAR::isError($db)) {
506	return $db;
507	}
508
509	$query = "SELECT viewname
510	FROM pg_views
511	WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
512	AND viewname !~ '^pg_'";
513	$result = $db->queryCol($query);
514	if (PEAR::isError($result)) {
515	return $result;
516	}
517	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
518	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
519	}
520	return $result;
521	}
522
523	// }}}
524	// {{{ listTableViews()
525
526	/**
527	* list the views in the database that reference a given table
528	*
529	* @param string table for which all referenced views should be found
530	* @return mixed array of view names on success, a MDB2 error on failure
531	* @access public
532	*/
533	function listTableViews($table)
534	{
535	$db =& $this->getDBInstance();
536	if (PEAR::isError($db)) {
537	return $db;
538	}
539
540	$query = 'SELECT viewname FROM pg_views NATURAL JOIN pg_tables';
541	$query.= ' WHERE tablename ='.$db->quote($table, 'text');
542	$result = $db->queryCol($query);
543	if (PEAR::isError($result)) {
544	return $result;
545	}
546	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
547	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
548	}
549	return $result;
550	}
551
552	// }}}
553	// {{{ listFunctions()
554
555	/**
556	* list all functions in the current database
557	*
558	* @return mixed array of function names on success, a MDB2 error on failure
559	* @access public
560	*/
561	function listFunctions()
562	{
563	$db =& $this->getDBInstance();
564	if (PEAR::isError($db)) {
565	return $db;
566	}
567
568	$query = "
569	SELECT
570	proname
571	FROM
572	pg_proc pr,
573	pg_type tp
574	WHERE
575	tp.oid = pr.prorettype
576	AND pr.proisagg = FALSE
577	AND tp.typname <> 'trigger'
578	AND pr.pronamespace IN
579	(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
580	$result = $db->queryCol($query);
581	if (PEAR::isError($result)) {
582	return $result;
583	}
584	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
585	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
586	}
587	return $result;
588	}
589
590	// }}}
591	// {{{ listTableTriggers()
592
593	/**
594	* list all triggers in the database that reference a given table
595	*
596	* @param string table for which all referenced triggers should be found
597	* @return mixed array of trigger names on success, a MDB2 error on failure
598	* @access public
599	*/
600	function listTableTriggers($table = null)
601	{
602	$db =& $this->getDBInstance();
603	if (PEAR::isError($db)) {
604	return $db;
605	}
606
607	$query = 'SELECT trg.tgname AS trigger_name
608	FROM pg_trigger trg,
609	pg_class tbl
610	WHERE trg.tgrelid = tbl.oid';
611	if (!is_null($table)) {
612	$table = $db->quote(strtoupper($table), 'text');
613	$query .= " AND tbl.relname = $table";
614	}
615	$result = $db->queryCol($query);
616	if (PEAR::isError($result)) {
617	return $result;
618	}
619	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
620	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
621	}
622	return $result;
623	}
624
625	// }}}
626	// {{{ listTables()
627
628	/**
629	* list all tables in the current database
630	*
631	* @return mixed array of table names on success, a MDB2 error on failure
632	* @access public
633	*/
634	function listTables()
635	{
636	$db =& $this->getDBInstance();
637	if (PEAR::isError($db)) {
638	return $db;
639	}
640
641	// gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
642	$query = 'SELECT c.relname AS "Name"'
643	. ' FROM pg_class c, pg_user u'
644	. ' WHERE c.relowner = u.usesysid'
645	. " AND c.relkind = 'r'"
646	. ' AND NOT EXISTS'
647	. ' (SELECT 1 FROM pg_views'
648	. ' WHERE viewname = c.relname)'
649	. " AND c.relname !~ '^(pg_\|sql_)'"
650	. ' UNION'
651	. ' SELECT c.relname AS "Name"'
652	. ' FROM pg_class c'
653	. " WHERE c.relkind = 'r'"
654	. ' AND NOT EXISTS'
655	. ' (SELECT 1 FROM pg_views'
656	. ' WHERE viewname = c.relname)'
657	. ' AND NOT EXISTS'
658	. ' (SELECT 1 FROM pg_user'
659	. ' WHERE usesysid = c.relowner)'
660	. " AND c.relname !~ '^pg_'";
661	$result = $db->queryCol($query);
662	if (PEAR::isError($result)) {
663	return $result;
664	}
665	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
666	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
667	}
668	return $result;
669	}
670
671	// }}}
672	// {{{ listTableFields()
673
674	/**
675	* list all fields in a table in the current database
676	*
677	* @param string $table name of table that should be used in method
678	* @return mixed array of field names on success, a MDB2 error on failure
679	* @access public
680	*/
681	function listTableFields($table)
682	{
683	$db =& $this->getDBInstance();
684	if (PEAR::isError($db)) {
685	return $db;
686	}
687
688	list($schema, $table) = $this->splitTableSchema($table);
689
690	$table = $db->quoteIdentifier($table, true);
691	if (!empty($schema)) {
692	$table = $db->quoteIdentifier($schema, true) . '.' .$table;
693	}
694	$db->setLimit(1);
695	$result2 = $db->query("SELECT * FROM $table");
696	if (PEAR::isError($result2)) {
697	return $result2;
698	}
699	$result = $result2->getColumnNames();
700	$result2->free();
701	if (PEAR::isError($result)) {
702	return $result;
703	}
704	return array_flip($result);
705	}
706
707	// }}}
708	// {{{ listTableIndexes()
709
710	/**
711	* list all indexes in a table
712	*
713	* @param string $table name of table that should be used in method
714	* @return mixed array of index names on success, a MDB2 error on failure
715	* @access public
716	*/
717	function listTableIndexes($table)
718	{
719	$db =& $this->getDBInstance();
720	if (PEAR::isError($db)) {
721	return $db;
722	}
723
724	list($schema, $table) = $this->splitTableSchema($table);
725
726	$table = $db->quote($table, 'text');
727	$subquery = "SELECT indexrelid
728	FROM pg_index
729	LEFT JOIN pg_class ON pg_class.oid = pg_index.indrelid
730	LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
731	WHERE pg_class.relname = $table
732	AND indisunique != 't'
733	AND indisprimary != 't'";
734	if (!empty($schema)) {
735	$subquery .= ' AND pg_namespace.nspname = '.$db->quote($schema, 'text');
736	}
737	$query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
738	$indexes = $db->queryCol($query, 'text');
739	if (PEAR::isError($indexes)) {
740	return $indexes;
741	}
742
743	$result = array();
744	foreach ($indexes as $index) {
745	$index = $this->_fixIndexName($index);
746	if (!empty($index)) {
747	$result[$index] = true;
748	}
749	}
750
751	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
752	$result = array_change_key_case($result, $db->options['field_case']);
753	}
754	return array_keys($result);
755	}
756
757	// }}}
758	// {{{ dropConstraint()
759
760	/**
761	* drop existing constraint
762	*
763	* @param string $table name of table that should be used in method
764	* @param string $name name of the constraint to be dropped
765	* @param string $primary hint if the constraint is primary
766	*
767	* @return mixed MDB2_OK on success, a MDB2 error on failure
768	* @access public
769	*/
770	function dropConstraint($table, $name, $primary = false)
771	{
772	$db =& $this->getDBInstance();
773	if (PEAR::isError($db)) {
774	return $db;
775	}
776
777	// is it an UNIQUE index?
778	$query = 'SELECT relname
779	FROM pg_class
780	WHERE oid IN (
781	SELECT indexrelid
782	FROM pg_index, pg_class
783	WHERE pg_class.relname = '.$db->quote($table, 'text').'
784	AND pg_class.oid = pg_index.indrelid
785	AND indisunique = \'t\')
786	EXCEPT
787	SELECT conname
788	FROM pg_constraint, pg_class
789	WHERE pg_constraint.conrelid = pg_class.oid
790	AND relname = '. $db->quote($table, 'text');
791	$unique = $db->queryCol($query, 'text');
792	if (PEAR::isError($unique) \|\| empty($unique)) {
793	// not an UNIQUE index, maybe a CONSTRAINT
794	return parent::dropConstraint($table, $name, $primary);
795	}
796
797	if (in_array($name, $unique)) {
798	return $db->exec('DROP INDEX '.$db->quoteIdentifier($name, true));
799	}
800	$idxname = $db->getIndexName($name);
801	if (in_array($idxname, $unique)) {
802	return $db->exec('DROP INDEX '.$db->quoteIdentifier($idxname, true));
803	}
804	return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
805	$name . ' is not an existing constraint for table ' . $table, __FUNCTION__);
806	}
807
808	// }}}
809	// {{{ listTableConstraints()
810
811	/**
812	* list all constraints in a table
813	*
814	* @param string $table name of table that should be used in method
815	* @return mixed array of constraint names on success, a MDB2 error on failure
816	* @access public
817	*/
818	function listTableConstraints($table)
819	{
820	$db =& $this->getDBInstance();
821	if (PEAR::isError($db)) {
822	return $db;
823	}
824
825	list($schema, $table) = $this->splitTableSchema($table);
826
827	$table = $db->quote($table, 'text');
828	$query = 'SELECT conname
829	FROM pg_constraint
830	LEFT JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
831	LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
832	WHERE relname = ' .$table;
833	if (!empty($schema)) {
834	$query .= ' AND pg_namespace.nspname = ' . $db->quote($schema, 'text');
835	}
836	$query .= '
837	UNION DISTINCT
838	SELECT relname
839	FROM pg_class
840	WHERE oid IN (
841	SELECT indexrelid
842	FROM pg_index
843	LEFT JOIN pg_class ON pg_class.oid = pg_index.indrelid
844	LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
845	WHERE pg_class.relname = '.$table.'
846	AND indisunique = \'t\'';
847	if (!empty($schema)) {
848	$query .= ' AND pg_namespace.nspname = ' . $db->quote($schema, 'text');
849	}
850	$query .= ')';
851	$constraints = $db->queryCol($query);
852	if (PEAR::isError($constraints)) {
853	return $constraints;
854	}
855
856	$result = array();
857	foreach ($constraints as $constraint) {
858	$constraint = $this->_fixIndexName($constraint);
859	if (!empty($constraint)) {
860	$result[$constraint] = true;
861	}
862	}
863
864	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
865	&& $db->options['field_case'] == CASE_LOWER
866	) {
867	$result = array_change_key_case($result, $db->options['field_case']);
868	}
869	return array_keys($result);
870	}
871
872	// }}}
873	// {{{ createSequence()
874
875	/**
876	* create sequence
877	*
878	* @param string $seq_name name of the sequence to be created
879	* @param string $start start value of the sequence; default is 1
880	* @return mixed MDB2_OK on success, a MDB2 error on failure
881	* @access public
882	*/
883	function createSequence($seq_name, $start = 1)
884	{
885	$db =& $this->getDBInstance();
886	if (PEAR::isError($db)) {
887	return $db;
888	}
889
890	$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
891	return $db->exec("CREATE SEQUENCE $sequence_name INCREMENT 1".
892	($start < 1 ? " MINVALUE $start" : '')." START $start");
893	}
894
895	// }}}
896	// {{{ dropSequence()
897
898	/**
899	* drop existing sequence
900	*
901	* @param string $seq_name name of the sequence to be dropped
902	* @return mixed MDB2_OK on success, a MDB2 error on failure
903	* @access public
904	*/
905	function dropSequence($seq_name)
906	{
907	$db =& $this->getDBInstance();
908	if (PEAR::isError($db)) {
909	return $db;
910	}
911
912	$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
913	return $db->exec("DROP SEQUENCE $sequence_name");
914	}
915
916	// }}}
917	// {{{ listSequences()
918
919	/**
920	* list all sequences in the current database
921	*
922	* @return mixed array of sequence names on success, a MDB2 error on failure
923	* @access public
924	*/
925	function listSequences()
926	{
927	$db =& $this->getDBInstance();
928	if (PEAR::isError($db)) {
929	return $db;
930	}
931
932	$query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
933	$query.= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
934	$table_names = $db->queryCol($query);
935	if (PEAR::isError($table_names)) {
936	return $table_names;
937	}
938	$result = array();
939	foreach ($table_names as $table_name) {
940	$result[] = $this->_fixSequenceName($table_name);
941	}
942	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
943	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
944	}
945	return $result;
946	}
947	}
948	?>

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

Context Navigation

source: branches/version-2_11-dev/data/module/MDB2/Driver/Manager/pgsql.php @ 20764

Download in other formats: