Context Navigation

pgsql.php @ 23022

Visit:

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

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

Context Navigation

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

Download in other formats: