PageRenderTime 21ms CodeModel.GetById 12ms app.highlight 5ms RepoModel.GetById 1ms app.codeStats 0ms

/inc/MDB2/Driver/Manager/mssql.php

https://github.com/chregu/fluxcms
PHP | 364 lines | 134 code | 17 blank | 213 comment | 22 complexity | d85f2b0fc1fa8d2bd4ceb4bb02c6eb48 MD5 | raw file
  1<?php
  2// +----------------------------------------------------------------------+
  3// | PHP versions 4 and 5                                                 |
  4// +----------------------------------------------------------------------+
  5// | Copyright (c) 1998-2004 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: Frank M. Kromann <frank@kromann.info>                        |
 43// +----------------------------------------------------------------------+
 44//
 45// $Id$
 46//
 47
 48require_once 'MDB2/Driver/Manager/Common.php';
 49
 50/**
 51 * MDB2 MSSQL driver for the management modules
 52 *
 53 * @package MDB2
 54 * @category Database
 55 * @author  Frank M. Kromann <frank@kromann.info>
 56 */
 57class MDB2_Driver_Manager_mssql extends MDB2_Driver_Manager_Common
 58{
 59    // }}}
 60    // {{{ createDatabase()
 61
 62    /**
 63     * create a new database
 64     *
 65     * @param string $name name of the database that should be created
 66     * @return mixed MDB2_OK on success, a MDB2 error on failure
 67     * @access public
 68     */
 69    function createDatabase($name)
 70    {
 71        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
 72        $database_device = (isset($db->options['database_device'])) ? $db->options['database_device'] : 'DEFAULT';
 73        $database_size = (isset($db->options['database_size'])) ? '='.$db->options['database_size'] : '';
 74        return $db->standaloneQuery("CREATE DATABASE $name ON $database_device$database_size");
 75    }
 76
 77    // }}}
 78    // {{{ dropDatabase()
 79
 80    /**
 81     * drop an existing database
 82     *
 83     * @param string $name name of the database that should be dropped
 84     * @return mixed MDB2_OK on success, a MDB2 error on failure
 85     * @access public
 86     */
 87    function dropDatabase($name)
 88    {
 89        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
 90        return $db->standaloneQuery("DROP DATABASE $name");
 91    }
 92
 93    // }}}
 94    // {{{ alterTable()
 95
 96    /**
 97     * alter an existing table
 98     *
 99     * @param string $name         name of the table that is intended to be changed.
100     * @param array $changes     associative array that contains the details of each type
101     *                             of change that is intended to be performed. The types of
102     *                             changes that are currently supported are defined as follows:
103     *
104     *                             name
105     *
106     *                                New name for the table.
107     *
108     *                            added_fields
109     *
110     *                                Associative array with the names of fields to be added as
111     *                                 indexes of the array. The value of each entry of the array
112     *                                 should be set to another associative array with the properties
113     *                                 of the fields to be added. The properties of the fields should
114     *                                 be the same as defined by the Metabase parser.
115     *
116     *                                Additionally, there should be an entry named Declaration that
117     *                                 is expected to contain the portion of the field declaration already
118     *                                 in DBMS specific SQL code as it is used in the CREATE TABLE statement.
119     *
120     *                            removed_fields
121     *
122     *                                Associative array with the names of fields to be removed as indexes
123     *                                 of the array. Currently the values assigned to each entry are ignored.
124     *                                 An empty array should be used for future compatibility.
125     *
126     *                            renamed_fields
127     *
128     *                                Associative array with the names of fields to be renamed as indexes
129     *                                 of the array. The value of each entry of the array should be set to
130     *                                 another associative array with the entry named name with the new
131     *                                 field name and the entry named Declaration that is expected to contain
132     *                                 the portion of the field declaration already in DBMS specific SQL code
133     *                                 as it is used in the CREATE TABLE statement.
134     *
135     *                            changed_fields
136     *
137     *                                Associative array with the names of the fields to be changed as indexes
138     *                                 of the array. Keep in mind that if it is intended to change either the
139     *                                 name of a field and any other properties, the changed_fields array entries
140     *                                 should have the new names of the fields as array indexes.
141     *
142     *                                The value of each entry of the array should be set to another associative
143     *                                 array with the properties of the fields to that are meant to be changed as
144     *                                 array entries. These entries should be assigned to the new values of the
145     *                                 respective properties. The properties of the fields should be the same
146     *                                 as defined by the Metabase parser.
147     *
148     *                                If the default property is meant to be added, removed or changed, there
149     *                                 should also be an entry with index ChangedDefault assigned to 1. Similarly,
150     *                                 if the notnull constraint is to be added or removed, there should also be
151     *                                 an entry with index ChangedNotNull assigned to 1.
152     *
153     *                                Additionally, there should be an entry named Declaration that is expected
154     *                                 to contain the portion of the field changed declaration already in DBMS
155     *                                 specific SQL code as it is used in the CREATE TABLE statement.
156     *                            Example
157     *                                array(
158     *                                    'name' => 'userlist',
159     *                                    'added_fields' => array(
160     *                                        'quota' => array(
161     *                                            'type' => 'integer',
162     *                                            'unsigned' => 1
163     *                                            'declaration' => 'quota INT'
164     *                                        )
165     *                                    ),
166     *                                    'removed_fields' => array(
167     *                                        'file_limit' => array(),
168     *                                        'time_limit' => array()
169     *                                        ),
170     *                                    'changed_fields' => array(
171     *                                        'gender' => array(
172     *                                            'default' => 'M',
173     *                                            'change_default' => 1,
174     *                                            'declaration' => "gender CHAR(1) DEFAULT 'M'"
175     *                                        )
176     *                                    ),
177     *                                    'renamed_fields' => array(
178     *                                        'sex' => array(
179     *                                            'name' => 'gender',
180     *                                            'declaration' => "gender CHAR(1) DEFAULT 'M'"
181     *                                        )
182     *                                    )
183     *                                )
184     *
185     * @param boolean $check     indicates whether the function should just check if the DBMS driver
186     *                             can perform the requested table alterations if the value is true or
187     *                             actually perform them otherwise.
188     * @access public
189     *
190     * @return mixed MDB2_OK on success, a MDB2 error on failure
191     */
192    function alterTable($name, $changes, $check)
193    {
194        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
195        if ($check) {
196            foreach ($changes as $change_name => $change) {
197                switch ($change_name) {
198                case 'added_fields':
199                    break;
200                case 'removed_fields':
201                case 'name':
202                case 'renamed_fields':
203                case 'changed_fields':
204                default:
205                    return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
206                        'alterTable: change type "'.$change_name.'" not yet supported');
207                }
208            }
209            return MDB2_OK;
210        } else {
211            if (isset($changes[$change = 'removed_fields'])
212                || isset($changes[$change = 'name'])
213                || isset($changes[$change = 'renamed_fields'])
214                || isset($changes[$change = 'changed_fields'])
215            ) {
216                return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
217                    'alterTable: change type "'.$change.'" is not supported by the server"');
218            }
219            $query = '';
220            if (isset($changes['added_fields'])) {
221                if ($query) {
222                    $query.= ', ';
223                }
224                $query.= 'ADD ';
225                $fields = $changes['added_fields'];
226                foreach ($fields as $field) {
227                    if ($query) {
228                        $query.= ', ';
229                    }
230                    $query.= $field['declaration'];
231                }
232            }
233            return $query ? $db->query("ALTER TABLE $name $query") : MDB2_OK;
234        }
235    }
236
237    // }}}
238    // {{{ listTables()
239
240    /**
241     * list all tables in the current database
242     *
243     * @return mixed data array on success, a MDB error on failure
244     * @access public
245     */
246    function listTables()
247    {
248        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
249        $query = 'EXECUTE sp_tables @table_type = "\'TABLE\'"';
250        $table_names = $db->queryCol($query, null, 2);
251        if (MDB2::isError($table_names)) {
252            return $table_names;
253        }
254        $tables = array();
255        for ($i = 0, $j = count($table_names); $i <$j; ++$i) {
256            if (!$this->_isSequenceName($db, $table_names[$i])) {
257                $tables[] = $table_names[$i];
258            }
259        }
260        return $tables;
261    }
262
263    // }}}
264    // {{{ listTableFields()
265
266    /**
267     * list all fields in a tables in the current database
268     *
269     * @param string $table name of table that should be used in method
270     * @return mixed data array on success, a MDB error on failure
271     * @access public
272     */
273    function listTableFields($table)
274    {
275        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
276        $result = $db->query("SELECT * FROM $table");
277        if (MDB2::isError($result)) {
278            return $result;
279        }
280        $columns = $result->getColumnNames();
281        $result->free();
282        if (MDB2::isError($columns)) {
283            return $columns;
284        }
285        return array_flip($columns);
286    }
287
288    // }}}
289    // {{{ listTableIndexes()
290
291    /**
292     * list all indexes in a table
293     *
294     * @param string    $table     name of table that should be used in method
295     * @return mixed data array on success, a MDB error on failure
296     * @access public
297     */
298    function listTableIndexes($table)
299    {
300        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
301        $key_name = 'INDEX_NAME';
302        $pk_name = 'PK_NAME';
303        if ($db->options['portability'] & MDB2_PORTABILITY_LOWERCASE) {
304            $key_name = strtolower($key_name);
305            $pk_name = strtolower($pk_name);
306        }
307        $query = "EXEC sp_statistics @table_name='$table'";
308        $indexes_all = $db->query($query, 'text', $key_name);
309        if (MDB2::isError($indexes_all)) {
310            return $indexes_all;
311        }
312        $query = "EXEC sp_pkeys @table_name='$table'";
313        $pk_all = $db->queryCol($query, 'text', $pk_name);
314        $found = $indexes = array();
315        for ($index = 0, $j = count($indexes_all); $index < $j; ++$index) {
316            if (!in_array($indexes_all[$index], $pk_all)
317                && $indexes_all[$index] != null
318                && !isset($found[$indexes_all[$index]])
319            ) {
320                $indexes[] = $indexes_all[$index];
321                $found[$indexes_all[$index]] = 1;
322            }
323        }
324        return $indexes;
325    }
326
327    // }}}
328    // {{{ createSequence()
329
330    /**
331     * create sequence
332     *
333     * @param string    $seq_name     name of the sequence to be created
334     * @param string    $start         start value of the sequence; default is 1
335     * @return mixed MDB2_OK on success, a MDB2 error on failure
336     * @access public
337     */
338    function createSequence($seq_name, $start = 1)
339    {
340        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
341        $sequence_name = $db->getSequenceName($seq_name);
342        $query = "CREATE TABLE $sequence_name (".$db->options['seqname_col_name']
343            ." INT NOT NULL IDENTITY($start,1) PRIMARY KEY CLUSTERED)";
344        return $db->query($query);
345    }
346
347    // }}}
348    // {{{ dropSequence()
349
350    /**
351     * drop existing sequence
352     *
353     * @param string    $seq_name     name of the sequence to be dropped
354     * @return mixed MDB2_OK on success, a MDB2 error on failure
355     * @access public
356     */
357    function dropSequence($seq_name)
358    {
359        $db =& $GLOBALS['_MDB2_databases'][$this->db_index];
360        $sequence_name = $db->getSequenceName($seq_name);
361        return $db->Query("DROP TABLE $sequence_name");
362    }
363}
364?>