Context Navigation

source: github/program/lib/MDB2/Driver/Manager/mssql.php @ d1403fd

Visit:

HEADcourier-fixdev-browser-capabilitiespdorelease-0.6release-0.7release-0.8

Last change on this file since d1403fd was d1403fd, checked in by alecpl <alec@…>, 5 years ago
fixed #1485032 and updated MDB2 package+drivers
Property mode set to `100644`
File size: 39.5 KB

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: Frank M. Kromann <frank@kromann.info> \|
43	// \| David Coallier <davidc@php.net> \|
44	// \| Lorenzo Alberton <l.alberton@quipo.it> \|
45	// +----------------------------------------------------------------------+
46	//
47	// $Id: mssql.php,v 1.109 2008/03/05 12:55:57 afz Exp $
48	//
49
50	require_once 'MDB2/Driver/Manager/Common.php';
51
52	// {{{ class MDB2_Driver_Manager_mssql
53
54	/**
55	* MDB2 MSSQL driver for the management modules
56	*
57	* @package MDB2
58	* @category Database
59	* @author Frank M. Kromann <frank@kromann.info>
60	* @author David Coallier <davidc@php.net>
61	* @author Lorenzo Alberton <l.alberton@quipo.it>
62	*/
63	class MDB2_Driver_Manager_mssql extends MDB2_Driver_Manager_Common
64	{
65	// {{{ createDatabase()
66	/**
67	* create a new database
68	*
69	* @param string $name name of the database that should be created
70	* @param array $options array with collation info
71	*
72	* @return mixed MDB2_OK on success, a MDB2 error on failure
73	* @access public
74	*/
75	function createDatabase($name, $options = array())
76	{
77	$db =& $this->getDBInstance();
78	if (PEAR::isError($db)) {
79	return $db;
80	}
81
82	$name = $db->quoteIdentifier($name, true);
83	$query = "CREATE DATABASE $name";
84	if ($db->options['database_device']) {
85	$query.= ' ON '.$db->options['database_device'];
86	$query.= $db->options['database_size'] ? '=' .
87	$db->options['database_size'] : '';
88	}
89	if (!empty($options['collation'])) {
90	$query .= ' COLLATE ' . $options['collation'];
91	}
92	return $db->standaloneQuery($query, null, true);
93	}
94
95	// }}}
96	// {{{ alterDatabase()
97
98	/**
99	* alter an existing database
100	*
101	* @param string $name name of the database that is intended to be changed
102	* @param array $options array with name, collation info
103	*
104	* @return mixed MDB2_OK on success, a MDB2 error on failure
105	* @access public
106	*/
107	function alterDatabase($name, $options = array())
108	{
109	$db =& $this->getDBInstance();
110	if (PEAR::isError($db)) {
111	return $db;
112	}
113
114	$query = '';
115	if (!empty($options['name'])) {
116	$query .= ' MODIFY NAME = ' .$db->quoteIdentifier($options['name'], true);
117	}
118	if (!empty($options['collation'])) {
119	$query .= ' COLLATE ' . $options['collation'];
120	}
121	if (!empty($query)) {
122	$query = 'ALTER DATABASE '. $db->quoteIdentifier($name, true) . $query;
123	return $db->standaloneQuery($query, null, true);
124	}
125	return MDB2_OK;
126	}
127
128	// }}}
129	// {{{ dropDatabase()
130
131	/**
132	* drop an existing database
133	*
134	* @param string $name name of the database that should be dropped
135	*
136	* @return mixed MDB2_OK on success, a MDB2 error on failure
137	* @access public
138	*/
139	function dropDatabase($name)
140	{
141	$db =& $this->getDBInstance();
142	if (PEAR::isError($db)) {
143	return $db;
144	}
145
146	$name = $db->quoteIdentifier($name, true);
147	return $db->standaloneQuery("DROP DATABASE $name", null, true);
148	}
149
150	// }}}
151	// {{{ _getTemporaryTableQuery()
152
153	/**
154	* Override the parent method.
155	*
156	* @return string The string required to be placed between "CREATE" and "TABLE"
157	* to generate a temporary table, if possible.
158	*/
159	function _getTemporaryTableQuery()
160	{
161	return '';
162	}
163
164	// }}}
165	// {{{ _getAdvancedFKOptions()
166
167	/**
168	* Return the FOREIGN KEY query section dealing with non-standard options
169	* as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
170	*
171	* @param array $definition
172	*
173	* @return string
174	* @access protected
175	*/
176	function _getAdvancedFKOptions($definition)
177	{
178	$query = '';
179	if (!empty($definition['onupdate'])) {
180	$query .= ' ON UPDATE '.$definition['onupdate'];
181	}
182	if (!empty($definition['ondelete'])) {
183	$query .= ' ON DELETE '.$definition['ondelete'];
184	}
185	return $query;
186	}
187
188	// }}}
189	// {{{ createTable()
190
191	/**
192	* create a new table
193	*
194	* @param string $name Name of the database that should be created
195	* @param array $fields Associative array that contains the definition of each field of the new table
196	* The indexes of the array entries are the names of the fields of the table an
197	* the array entry values are associative arrays like those that are meant to be
198	* passed with the field definitions to get[Type]Declaration() functions.
199	*
200	* Example
201	* array(
202	*
203	* 'id' => array(
204	* 'type' => 'integer',
205	* 'unsigned' => 1,
206	* 'notnull' => 1,
207	* 'default' => 0,
208	* ),
209	* 'name' => array(
210	* 'type' => 'text',
211	* 'length' => 12,
212	* ),
213	* 'description' => array(
214	* 'type' => 'text',
215	* 'length' => 12,
216	* )
217	* );
218	* @param array $options An associative array of table options:
219	* array(
220	* 'comment' => 'Foo',
221	* 'temporary' => true\|false,
222	* );
223	*
224	* @return mixed MDB2_OK on success, a MDB2 error on failure
225	* @access public
226	*/
227	function createTable($name, $fields, $options = array())
228	{
229	if (!empty($options['temporary'])) {
230	$name = '#'.$name;
231	}
232	return parent::createTable($name, $fields, $options);
233	}
234
235	// }}}
236	// {{{ truncateTable()
237
238	/**
239	* Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
240	* it falls back to a DELETE FROM TABLE query)
241	*
242	* @param string $name name of the table that should be truncated
243	* @return mixed MDB2_OK on success, a MDB2 error on failure
244	* @access public
245	*/
246	function truncateTable($name)
247	{
248	$db =& $this->getDBInstance();
249	if (PEAR::isError($db)) {
250	return $db;
251	}
252
253	$name = $db->quoteIdentifier($name, true);
254	return $db->exec("TRUNCATE TABLE $name");
255	}
256
257	// }}}
258	// {{{ vacuum()
259
260	/**
261	* Optimize (vacuum) all the tables in the db (or only the specified table)
262	* and optionally run ANALYZE.
263	*
264	* @param string $table table name (all the tables if empty)
265	* @param array $options an array with driver-specific options:
266	* - timeout [int] (in seconds) [mssql-only]
267	* - analyze [boolean] [pgsql and mysql]
268	* - full [boolean] [pgsql-only]
269	* - freeze [boolean] [pgsql-only]
270	*
271	* NB: you have to run the NSControl Create utility to enable VACUUM
272	*
273	* @return mixed MDB2_OK success, a MDB2 error on failure
274	* @access public
275	*/
276	function vacuum($table = null, $options = array())
277	{
278	$db =& $this->getDBInstance();
279	if (PEAR::isError($db)) {
280	return $db;
281	}
282	$timeout = isset($options['timeout']) ? (int)$options['timeout'] : 300;
283
284	$query = 'NSControl Create';
285	$result = $db->exec($query);
286	if (PEAR::isError($result)) {
287	return $result;
288	}
289
290	return $db->exec('EXEC NSVacuum '.$timeout);
291	}
292
293	// }}}
294	// {{{ alterTable()
295
296	/**
297	* alter an existing table
298	*
299	* @param string $name name of the table that is intended to be changed.
300	* @param array $changes associative array that contains the details of each type
301	* of change that is intended to be performed. The types of
302	* changes that are currently supported are defined as follows:
303	*
304	* name
305	*
306	* New name for the table.
307	*
308	* add
309	*
310	* Associative array with the names of fields to be added as
311	* indexes of the array. The value of each entry of the array
312	* should be set to another associative array with the properties
313	* of the fields to be added. The properties of the fields should
314	* be the same as defined by the MDB2 parser.
315	*
316	*
317	* remove
318	*
319	* Associative array with the names of fields to be removed as indexes
320	* of the array. Currently the values assigned to each entry are ignored.
321	* An empty array should be used for future compatibility.
322	*
323	* rename
324	*
325	* Associative array with the names of fields to be renamed as indexes
326	* of the array. The value of each entry of the array should be set to
327	* another associative array with the entry named name with the new
328	* field name and the entry named Declaration that is expected to contain
329	* the portion of the field declaration already in DBMS specific SQL code
330	* as it is used in the CREATE TABLE statement.
331	*
332	* change
333	*
334	* Associative array with the names of the fields to be changed as indexes
335	* of the array. Keep in mind that if it is intended to change either the
336	* name of a field and any other properties, the change array entries
337	* should have the new names of the fields as array indexes.
338	*
339	* The value of each entry of the array should be set to another associative
340	* array with the properties of the fields to that are meant to be changed as
341	* array entries. These entries should be assigned to the new values of the
342	* respective properties. The properties of the fields should be the same
343	* as defined by the MDB2 parser.
344	*
345	* Example
346	* array(
347	* 'name' => 'userlist',
348	* 'add' => array(
349	* 'quota' => array(
350	* 'type' => 'integer',
351	* 'unsigned' => 1
352	* )
353	* ),
354	* 'remove' => array(
355	* 'file_limit' => array(),
356	* 'time_limit' => array()
357	* ),
358	* 'change' => array(
359	* 'name' => array(
360	* 'length' => '20',
361	* 'definition' => array(
362	* 'type' => 'text',
363	* 'length' => 20,
364	* ),
365	* )
366	* ),
367	* 'rename' => array(
368	* 'sex' => array(
369	* 'name' => 'gender',
370	* 'definition' => array(
371	* 'type' => 'text',
372	* 'length' => 1,
373	* 'default' => 'M',
374	* ),
375	* )
376	* )
377	* )
378	*
379	* @param boolean $check indicates whether the function should just check if the DBMS driver
380	* can perform the requested table alterations if the value is true or
381	* actually perform them otherwise.
382	*
383	* @return mixed MDB2_OK on success, a MDB2 error on failure
384	* @access public
385	*/
386	function alterTable($name, $changes, $check)
387	{
388	$db =& $this->getDBInstance();
389	if (PEAR::isError($db)) {
390	return $db;
391	}
392	$name_quoted = $db->quoteIdentifier($name, true);
393
394	foreach ($changes as $change_name => $change) {
395	switch ($change_name) {
396	case 'remove':
397	case 'rename':
398	case 'add':
399	case 'change':
400	case 'name':
401	break;
402	default:
403	return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
404	'change type "'.$change_name.'" not yet supported', __FUNCTION__);
405	}
406	}
407
408	if ($check) {
409	return MDB2_OK;
410	}
411
412	$idxname_format = $db->getOption('idxname_format');
413	$db->setOption('idxname_format', '%s');
414
415	if (!empty($changes['remove']) && is_array($changes['remove'])) {
416	$result = $this->_dropConflictingIndices($name, array_keys($changes['remove']));
417	if (PEAR::isError($result)) {
418	$db->setOption('idxname_format', $idxname_format);
419	return $result;
420	}
421	$result = $this->_dropConflictingConstraints($name, array_keys($changes['remove']));
422	if (PEAR::isError($result)) {
423	$db->setOption('idxname_format', $idxname_format);
424	return $result;
425	}
426
427	$query = '';
428	foreach ($changes['remove'] as $field_name => $field) {
429	if ($query) {
430	$query.= ', ';
431	}
432	$field_name = $db->quoteIdentifier($field_name, true);
433	$query.= 'COLUMN ' . $field_name;
434	}
435
436	$result = $db->exec("ALTER TABLE $name_quoted DROP $query");
437	if (PEAR::isError($result)) {
438	$db->setOption('idxname_format', $idxname_format);
439	return $result;
440	}
441	}
442
443	if (!empty($changes['rename']) && is_array($changes['rename'])) {
444	foreach ($changes['rename'] as $field_name => $field) {
445	$field_name = $db->quoteIdentifier($field_name, true);
446	$result = $db->exec("sp_rename '$name_quoted.$field_name', '".$field['name']."', 'COLUMN'");
447	if (PEAR::isError($result)) {
448	$db->setOption('idxname_format', $idxname_format);
449	return $result;
450	}
451	}
452	}
453
454	if (!empty($changes['add']) && is_array($changes['add'])) {
455	$query = '';
456	foreach ($changes['add'] as $field_name => $field) {
457	if ($query) {
458	$query.= ', ';
459	} else {
460	$query.= 'ADD ';
461	}
462	$query.= $db->getDeclaration($field['type'], $field_name, $field);
463	}
464
465	$result = $db->exec("ALTER TABLE $name_quoted $query");
466	if (PEAR::isError($result)) {
467	$db->setOption('idxname_format', $idxname_format);
468	return $result;
469	}
470	}
471
472	$dropped_indices = array();
473	$dropped_constraints = array();
474
475	if (!empty($changes['change']) && is_array($changes['change'])) {
476	$dropped = $this->_dropConflictingIndices($name, array_keys($changes['change']));
477	if (PEAR::isError($dropped)) {
478	$db->setOption('idxname_format', $idxname_format);
479	return $dropped;
480	}
481	$dropped_indices = array_merge($dropped_indices, $dropped);
482	$dropped = $this->_dropConflictingConstraints($name, array_keys($changes['change']));
483	if (PEAR::isError($dropped)) {
484	$db->setOption('idxname_format', $idxname_format);
485	return $dropped;
486	}
487	$dropped_constraints = array_merge($dropped_constraints, $dropped);
488
489	foreach ($changes['change'] as $field_name => $field) {
490	//MSSQL doesn't allow multiple ALTER COLUMNs in one query
491	$query = 'ALTER COLUMN ';
492
493	//MSSQL doesn't allow changing the DEFAULT value of a field in altering mode
494	if (array_key_exists('default', $field['definition'])) {
495	unset($field['definition']['default']);
496	}
497
498	$query .= $db->getDeclaration($field['definition']['type'], $field_name, $field['definition']);
499	$result = $db->exec("ALTER TABLE $name_quoted $query");
500	if (PEAR::isError($result)) {
501	$db->setOption('idxname_format', $idxname_format);
502	return $result;
503	}
504	}
505	}
506
507	// restore the dropped conflicting indices and constraints
508	foreach ($dropped_indices as $index_name => $index) {
509	$result = $this->createIndex($name, $index_name, $index);
510	if (PEAR::isError($result)) {
511	$db->setOption('idxname_format', $idxname_format);
512	return $result;
513	}
514	}
515	foreach ($dropped_constraints as $constraint_name => $constraint) {
516	$result = $this->createConstraint($name, $constraint_name, $constraint);
517	if (PEAR::isError($result)) {
518	$db->setOption('idxname_format', $idxname_format);
519	return $result;
520	}
521	}
522
523	$db->setOption('idxname_format', $idxname_format);
524
525	if (!empty($changes['name'])) {
526	$new_name = $db->quoteIdentifier($changes['name'], true);
527	$result = $db->exec("sp_rename '$name_quoted', '$new_name'");
528	if (PEAR::isError($result)) {
529	return $result;
530	}
531	}
532
533	return MDB2_OK;
534	}
535
536	// }}}
537	// {{{ _dropConflictingIndices()
538
539	/**
540	* Drop the indices that prevent a successful ALTER TABLE action
541	*
542	* @param string $table table name
543	* @param array $fields array of names of the fields affected by the change
544	*
545	* @return array dropped indices definitions
546	*/
547	function _dropConflictingIndices($table, $fields)
548	{
549	$db =& $this->getDBInstance();
550	if (PEAR::isError($db)) {
551	return $db;
552	}
553
554	$dropped = array();
555	$index_names = $this->listTableIndexes($table);
556	if (PEAR::isError($index_names)) {
557	return $index_names;
558	}
559	$db->loadModule('Reverse');
560	$indexes = array();
561	foreach ($index_names as $index_name) {
562	$idx_def = $db->reverse->getTableIndexDefinition($table, $index_name);
563	if (!PEAR::isError($idx_def)) {
564	$indexes[$index_name] = $idx_def;
565	}
566	}
567	foreach ($fields as $field_name) {
568	foreach ($indexes as $index_name => $index) {
569	if (!isset($dropped[$index_name]) && array_key_exists($field_name, $index['fields'])) {
570	$dropped[$index_name] = $index;
571	$result = $this->dropIndex($table, $index_name);
572	if (PEAR::isError($result)) {
573	return $result;
574	}
575	}
576	}
577	}
578
579	return $dropped;
580	}
581
582	// }}}
583	// {{{ _dropConflictingConstraints()
584
585	/**
586	* Drop the constraints that prevent a successful ALTER TABLE action
587	*
588	* @param string $table table name
589	* @param array $fields array of names of the fields affected by the change
590	*
591	* @return array dropped constraints definitions
592	*/
593	function _dropConflictingConstraints($table, $fields)
594	{
595	$db =& $this->getDBInstance();
596	if (PEAR::isError($db)) {
597	return $db;
598	}
599
600	$dropped = array();
601	$constraint_names = $this->listTableConstraints($table);
602	if (PEAR::isError($constraint_names)) {
603	return $constraint_names;
604	}
605	$db->loadModule('Reverse');
606	$constraints = array();
607	foreach ($constraint_names as $constraint_name) {
608	$cons_def = $db->reverse->getTableConstraintDefinition($table, $constraint_name);
609	if (!PEAR::isError($cons_def)) {
610	$constraints[$constraint_name] = $cons_def;
611	}
612	}
613	foreach ($fields as $field_name) {
614	foreach ($constraints as $constraint_name => $constraint) {
615	if (!isset($dropped[$constraint_name]) && array_key_exists($field_name, $constraint['fields'])) {
616	$dropped[$constraint_name] = $constraint;
617	$result = $this->dropConstraint($table, $constraint_name);
618	if (PEAR::isError($result)) {
619	return $result;
620	}
621	}
622	}
623	// also drop implicit DEFAULT constraints
624	$default = $this->_getTableFieldDefaultConstraint($table, $field_name);
625	if (!PEAR::isError($default) && !empty($default)) {
626	$result = $this->dropConstraint($table, $default);
627	if (PEAR::isError($result)) {
628	return $result;
629	}
630	}
631	}
632
633	return $dropped;
634	}
635
636	// }}}
637	// {{{ _getTableFieldDefaultConstraint()
638
639	/**
640	* Get the default constraint for a table field
641	*
642	* @param string $table name of table that should be used in method
643	* @param string $field name of field that should be used in method
644	*
645	* @return mixed name of default constraint on success, a MDB2 error on failure
646	* @access private
647	*/
648	function _getTableFieldDefaultConstraint($table, $field)
649	{
650	$db =& $this->getDBInstance();
651	if (PEAR::isError($db)) {
652	return $db;
653	}
654
655	$table = $db->quoteIdentifier($table, true);
656	$field = $db->quote($field, 'text');
657	$query = "SELECT OBJECT_NAME(syscolumns.cdefault)
658	FROM syscolumns
659	WHERE syscolumns.id = object_id('$table')
660	AND syscolumns.name = $field
661	AND syscolumns.cdefault <> 0";
662	return $db->queryOne($query);
663	}
664
665	// }}}
666	// {{{ listTables()
667
668	/**
669	* list all tables in the current database
670	*
671	* @return mixed array of table names on success, a MDB2 error on failure
672	* @access public
673	*/
674	function listTables()
675	{
676	$db =& $this->getDBInstance();
677
678	if (PEAR::isError($db)) {
679	return $db;
680	}
681
682	$query = 'EXEC sp_tables @table_type = "\'TABLE\'"';
683	$table_names = $db->queryCol($query, null, 2);
684	if (PEAR::isError($table_names)) {
685	return $table_names;
686	}
687	$result = array();
688	foreach ($table_names as $table_name) {
689	if (!$this->_fixSequenceName($table_name, true)) {
690	$result[] = $table_name;
691	}
692	}
693	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
694	$result = array_map(($db->options['field_case'] == CASE_LOWER ?
695	'strtolower' : 'strtoupper'), $result);
696	}
697	return $result;
698	}
699
700	// }}}
701	// {{{ listTableFields()
702
703	/**
704	* list all fields in a table in the current database
705	*
706	* @param string $table name of table that should be used in method
707	*
708	* @return mixed array of field names on success, a MDB2 error on failure
709	* @access public
710	*/
711	function listTableFields($table)
712	{
713	$db =& $this->getDBInstance();
714	if (PEAR::isError($db)) {
715	return $db;
716	}
717
718	$table = $db->quoteIdentifier($table, true);
719	$columns = $db->queryCol("SELECT c.name
720	FROM syscolumns c
721	LEFT JOIN sysobjects o ON c.id = o.id
722	WHERE o.name = '$table'");
723	if (PEAR::isError($columns)) {
724	return $columns;
725	}
726	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
727	$columns = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $columns);
728	}
729	return $columns;
730	}
731
732	// }}}
733	// {{{ listTableIndexes()
734
735	/**
736	* list all indexes in a table
737	*
738	* @param string $table name of table that should be used in method
739	*
740	* @return mixed array of index names on success, a MDB2 error on failure
741	* @access public
742	*/
743	function listTableIndexes($table)
744	{
745	$db =& $this->getDBInstance();
746	if (PEAR::isError($db)) {
747	return $db;
748	}
749
750	$key_name = 'INDEX_NAME';
751	$pk_name = 'PK_NAME';
752	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
753	if ($db->options['field_case'] == CASE_LOWER) {
754	$key_name = strtolower($key_name);
755	$pk_name = strtolower($pk_name);
756	} else {
757	$key_name = strtoupper($key_name);
758	$pk_name = strtoupper($pk_name);
759	}
760	}
761	$table = $db->quote($table, 'text');
762	$query = "EXEC sp_statistics @table_name=$table";
763	$indexes = $db->queryCol($query, 'text', $key_name);
764	if (PEAR::isError($indexes)) {
765	return $indexes;
766	}
767	$query = "EXEC sp_pkeys @table_name=$table";
768	$pk_all = $db->queryCol($query, 'text', $pk_name);
769	$result = array();
770	foreach ($indexes as $index) {
771	if (!in_array($index, $pk_all) && ($index = $this->_fixIndexName($index))) {
772	$result[$index] = true;
773	}
774	}
775
776	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
777	$result = array_change_key_case($result, $db->options['field_case']);
778	}
779	return array_keys($result);
780	}
781
782	// }}}
783	// {{{ listDatabases()
784
785	/**
786	* list all databases
787	*
788	* @return mixed array of database names on success, a MDB2 error on failure
789	* @access public
790	*/
791	function listDatabases()
792	{
793	$db =& $this->getDBInstance();
794	if (PEAR::isError($db)) {
795	return $db;
796	}
797
798	$result = $db->queryCol('SELECT name FROM sys.databases');
799	if (PEAR::isError($result)) {
800	return $result;
801	}
802	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
803	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
804	}
805	return $result;
806	}
807
808	// }}}
809	// {{{ listUsers()
810
811	/**
812	* list all users
813	*
814	* @return mixed array of user names on success, a MDB2 error on failure
815	* @access public
816	*/
817	function listUsers()
818	{
819	$db =& $this->getDBInstance();
820	if (PEAR::isError($db)) {
821	return $db;
822	}
823
824	$result = $db->queryCol('SELECT DISTINCT loginame FROM master..sysprocesses');
825	if (PEAR::isError($result) \|\| empty($result)) {
826	return $result;
827	}
828	foreach (array_keys($result) as $k) {
829	$result[$k] = trim($result[$k]);
830	}
831	return $result;
832	}
833
834	// }}}
835	// {{{ listFunctions()
836
837	/**
838	* list all functions in the current database
839	*
840	* @return mixed array of function names on success, a MDB2 error on failure
841	* @access public
842	*/
843	function listFunctions()
844	{
845	$db =& $this->getDBInstance();
846	if (PEAR::isError($db)) {
847	return $db;
848	}
849
850	$query = "SELECT name
851	FROM sysobjects
852	WHERE objectproperty(id, N'IsMSShipped') = 0
853	AND (objectproperty(id, N'IsTableFunction') = 1
854	OR objectproperty(id, N'IsScalarFunction') = 1)";
855	/*
856	SELECT ROUTINE_NAME
857	FROM INFORMATION_SCHEMA.ROUTINES
858	WHERE ROUTINE_TYPE = 'FUNCTION'
859	*/
860	$result = $db->queryCol($query);
861	if (PEAR::isError($result)) {
862	return $result;
863	}
864	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
865	$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
866	}
867	return $result;
868	}
869
870	// }}}
871	// {{{ listTableTriggers()
872
873	/**
874	* list all triggers in the database that reference a given table
875	*
876	* @param string table for which all referenced triggers should be found
877	*
878	* @return mixed array of trigger names on success, otherwise, false which
879	* could be a db error if the db is not instantiated or could
880	* be the results of the error that occured during the
881	* querying of the sysobject module.
882	* @access public
883	*/
884	function listTableTriggers($table = null)
885	{
886	$db =& $this->getDBInstance();
887	if (PEAR::isError($db)) {
888	return $db;
889	}
890
891	$table = $db->quote($table, 'text');
892	$query = "SELECT o.name
893	FROM sysobjects o
894	WHERE xtype = 'TR'
895	AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0";
896	if (!is_null($table)) {
897	$query .= " AND object_name(parent_obj) = $table";
898	}
899
900	$result = $db->queryCol($query);
901	if (PEAR::isError($result)) {
902	return $result;
903	}
904
905	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE &&
906	$db->options['field_case'] == CASE_LOWER)
907	{
908	$result = array_map(($db->options['field_case'] == CASE_LOWER ?
909	'strtolower' : 'strtoupper'), $result);
910	}
911	return $result;
912	}
913
914	// }}}
915	// {{{ listViews()
916
917	/**
918	* list all views in the current database
919	*
920	* @param string database, the current is default
921	*
922	* @return mixed array of view names on success, a MDB2 error on failure
923	* @access public
924	*/
925	function listViews()
926	{
927	$db =& $this->getDBInstance();
928	if (PEAR::isError($db)) {
929	return $db;
930	}
931
932	$query = "SELECT name
933	FROM sysobjects
934	WHERE xtype = 'V'";
935	/*
936	SELECT *
937	FROM sysobjects
938	WHERE objectproperty(id, N'IsMSShipped') = 0
939	AND objectproperty(id, N'IsView') = 1
940	*/
941
942	$result = $db->queryCol($query);
943	if (PEAR::isError($result)) {
944	return $result;
945	}
946
947	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE &&
948	$db->options['field_case'] == CASE_LOWER)
949	{
950	$result = array_map(($db->options['field_case'] == CASE_LOWER ?
951	'strtolower' : 'strtoupper'), $result);
952	}
953	return $result;
954	}
955
956	// }}}
957	// {{{ dropIndex()
958
959	/**
960	* drop existing index
961	*
962	* @param string $table name of table that should be used in method
963	* @param string $name name of the index to be dropped
964	*
965	* @return mixed MDB2_OK on success, a MDB2 error on failure
966	* @access public
967	*/
968	function dropIndex($table, $name)
969	{
970	$db =& $this->getDBInstance();
971	if (PEAR::isError($db)) {
972	return $db;
973	}
974
975	$table = $db->quoteIdentifier($table, true);
976	$name = $db->quoteIdentifier($db->getIndexName($name), true);
977	return $db->exec("DROP INDEX $table.$name");
978	}
979
980	// }}}
981	// {{{ listTableConstraints()
982
983	/**
984	* list all constraints in a table
985	*
986	* @param string $table name of table that should be used in method
987	*
988	* @return mixed array of constraint names on success, a MDB2 error on failure
989	* @access public
990	*/
991	function listTableConstraints($table)
992	{
993	$db =& $this->getDBInstance();
994	if (PEAR::isError($db)) {
995	return $db;
996	}
997	$table = $db->quoteIdentifier($table, true);
998
999	$query = "SELECT c.constraint_name
1000	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
1001	WHERE c.constraint_catalog = DB_NAME()
1002	AND c.table_name = '$table'";
1003	$constraints = $db->queryCol($query);
1004	if (PEAR::isError($constraints)) {
1005	return $constraints;
1006	}
1007
1008	$result = array();
1009	foreach ($constraints as $constraint) {
1010	$constraint = $this->_fixIndexName($constraint);
1011	if (!empty($constraint)) {
1012	$result[$constraint] = true;
1013	}
1014	}
1015
1016	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
1017	$result = array_change_key_case($result, $db->options['field_case']);
1018	}
1019	return array_keys($result);
1020	}
1021
1022	// }}}
1023	// {{{ createSequence()
1024
1025	/**
1026	* create sequence
1027	*
1028	* @param string $seq_name name of the sequence to be created
1029	* @param string $start start value of the sequence; default is 1
1030	*
1031	* @return mixed MDB2_OK on success, a MDB2 error on failure
1032	* @access public
1033	*/
1034	function createSequence($seq_name, $start = 1)
1035	{
1036	$db =& $this->getDBInstance();
1037	if (PEAR::isError($db)) {
1038	return $db;
1039	}
1040
1041	$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
1042	$seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);
1043	$query = "CREATE TABLE $sequence_name ($seqcol_name " .
1044	"INT PRIMARY KEY CLUSTERED IDENTITY($start,1) NOT NULL)";
1045
1046	$res = $db->exec($query);
1047	if (PEAR::isError($res)) {
1048	return $res;
1049	}
1050
1051	$query = "SET IDENTITY_INSERT $sequence_name ON ".
1052	"INSERT INTO $sequence_name ($seqcol_name) VALUES ($start)";
1053	$res = $db->exec($query);
1054
1055	if (!PEAR::isError($res)) {
1056	return MDB2_OK;
1057	}
1058
1059	$result = $db->exec("DROP TABLE $sequence_name");
1060	if (PEAR::isError($result)) {
1061	return $db->raiseError($result, null, null,
1062	'could not drop inconsistent sequence table', __FUNCTION__);
1063	}
1064
1065	return $db->raiseError($res, null, null,
1066	'could not create sequence table', __FUNCTION__);
1067	}
1068
1069	// }}}
1070	// {{{ dropSequence()
1071
1072	/**
1073	* This function drops an existing sequence
1074	*
1075	* @param string $seq_name name of the sequence to be dropped
1076	*
1077	* @return mixed MDB2_OK on success, a MDB2 error on failure
1078	* @access public
1079	*/
1080	function dropSequence($seq_name)
1081	{
1082	$db =& $this->getDBInstance();
1083	if (PEAR::isError($db)) {
1084	return $db;
1085	}
1086
1087	$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
1088	return $db->exec("DROP TABLE $sequence_name");
1089	}
1090
1091	// }}}
1092	// {{{ listSequences()
1093
1094	/**
1095	* list all sequences in the current database
1096	*
1097	* @return mixed array of sequence names on success, a MDB2 error on failure
1098	* @access public
1099	*/
1100	function listSequences()
1101	{
1102	$db =& $this->getDBInstance();
1103	if (PEAR::isError($db)) {
1104	return $db;
1105	}
1106
1107	$query = "SELECT name FROM sysobjects WHERE xtype = 'U'";
1108	$table_names = $db->queryCol($query);
1109	if (PEAR::isError($table_names)) {
1110	return $table_names;
1111	}
1112	$result = array();
1113	foreach ($table_names as $table_name) {
1114	if ($sqn = $this->_fixSequenceName($table_name, true)) {
1115	$result[] = $sqn;
1116	}
1117	}
1118	if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
1119	$result = array_map(($db->options['field_case'] == CASE_LOWER ?
1120	'strtolower' : 'strtoupper'), $result);
1121	}
1122	return $result;
1123	}
1124
1125	// }}}
1126	}
1127
1128	// }}}
1129	?>

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

Download in other formats: