cvs: pear /MDB/MDB mssql.php

lsmith Mon Sep 15 08:44:47 2003 EDT Modified files: /pear/MDB/MDB mssql.php Log: ported tableinfo from mssql PEAR::DB driver (untested) Index: pear/MDB/MDB/mssql.php diff -u pear/MDB/MDB/mssql.php:1.36 pear/MDB/MDB/mssql.php:1.37 --- pear/MDB/MDB/mssql.php:1.36 Mon Sep 8 13:47:21 2003 +++ pear/MDB/MDB/mssql.php Mon Sep 15 08:44:47 2003 @@ -43,7 +43,7 @@ // | Author: Frank M. Kromann <frank@kromann.info> | // +----------------------------------------------------------------------+ // -// $Id: mssql.php,v 1.36 2003/09/08 17:47:21 lsmith Exp $ +// $Id: mssql.php,v 1.37 2003/09/15 12:44:47 lsmith Exp $ // require_once 'MDB/Common.php'; @@ -719,19 +719,24 @@ // }}} // {{{ tableInfo() - /** - * returns meta data about the result set - * - * @param resource $result result identifier - * @param mixed $mode depends on implementation - * @return array an nested array, or a MDB error - * @access public - */ + /** + + * Returns information about a table or a result set + * + * NOTE: doesn't support table name and flags if called from a db_result + * + * @param mixed $resource SQL Server result identifier or table name + * @param int $mode A valid tableInfo mode (MDB_TABLEINFO_ORDERTABLE or + * MDB_TABLEINFO_ORDER) + * + * @return array An array with all the information + */ function tableInfo($result, $mode = null) { + $count = 0; - $id = 0; - $res = array(); + $id = 0; + $res = array(); /* * depending on $mode, metadata returns the following values: @@ -746,7 +751,7 @@ * * - mode is MDB_TABLEINFO_ORDER * $result[]: - * ['num_fields'] number of metadata records + * ["num_fields"] number of metadata records * [0]['table'] table name * [0]['name'] field name * [0]['type'] field type @@ -758,8 +763,8 @@ * * - mode is MDB_TABLEINFO_ORDERTABLE * the same as above. but additionally - * ['ordertable'][table name][field name] index of field - * named 'field name' + * ["ordertable"][table name][field name] index of field + * named "field name" * * this is, because if you have fields from different * tables with the same field name * they override each @@ -772,9 +777,12 @@ // if $result is a string, then we want information about a // table without a resultset + if (is_string($result)) { - $id = @mssql_list_fields($this->database_name, - $result, $this->connection); + if (!@mssql_select_db($this->database_name, $this->connection)) { + return $this->mssqlRaiseError(MDB_ERROR_NODBSELECTED); + } + $id = mssql_query("SELECT * FROM $result", $this->connection); if (empty($id)) { return $this->mssqlRaiseError(); } @@ -789,22 +797,26 @@ // made this IF due to performance (one if is faster than $count if's) if (empty($mode)) { - for ($i = 0; $i<$count; $i++) { - $res[$i]['table'] = @mssql_field_table ($id, $i); - $res[$i]['name'] = @mssql_field_name ($id, $i); - $res[$i]['type'] = @mssql_field_type ($id, $i); - $res[$i]['len'] = @mssql_field_len ($id, $i); - $res[$i]['flags'] = @mssql_field_flags ($id, $i); + + for ($i=0; $i<$count; $i++) { + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = @mssql_field_name($id, $i); + $res[$i]['type'] = @mssql_field_type($id, $i); + $res[$i]['len'] = @mssql_field_length($id, $i); + // We only support flags for tables + $res[$i]['flags'] = is_string($result) ? $this->_mssql_field_flags($result, $res[$i]['name']) : ''; } + } else { // full - $res['num_fields'] = $count; + $res['num_fields']= $count; - for ($i = 0; $i<$count; $i++) { - $res[$i]['table'] = @mssql_field_table ($id, $i); - $res[$i]['name'] = @mssql_field_name ($id, $i); - $res[$i]['type'] = @mssql_field_type ($id, $i); - $res[$i]['len'] = @mssql_field_len ($id, $i); - $res[$i]['flags'] = @mssql_field_flags ($id, $i); + for ($i=0; $i<$count; $i++) { + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = @mssql_field_name($id, $i); + $res[$i]['type'] = @mssql_field_type($id, $i); + $res[$i]['len'] = @mssql_field_length($id, $i); + // We only support flags for tables + $res[$i]['flags'] = is_string($result) ? $this->_mssql_field_flags($result, $res[$i]['name']) : ''; if ($mode & MDB_TABLEINFO_ORDER) { $res['order'][$res[$i]['name']] = $i; } @@ -820,6 +832,47 @@ } return $res; } + + // }}} + // {{{ _mssql_field_flags() + /** + * Get the flags for a field, currently only supports "isnullable" and "primary_key" + * + * @param string The table name + * @param string The field + * @access private + */ + function _mssql_field_flags($table, $column) + { + static $flags = false; + // At the first call we discover the flags for all fields + if ($flags === false) { + $flags = array(); + // find nullable fields + $q_nulls = "SELECT syscolumns.name, syscolumns.isnullable + FROM sysobjects + INNER JOIN syscolumns ON sysobjects.id = syscolumns.id + WHERE sysobjects.name ='$table' AND syscolumns.isnullable = 1"; + $res = $this->getAll($q_nulls, DB_FETCHMODE_ASSOC); + foreach ($res as $data) { + if ($data['isnullable'] == 1) { + $flags[$data['name']][] = 'isnullable'; + } + } + // find primary keys + $res2 = $this->getAll("EXEC SP_PKEYS[$table]", DB_FETCHMODE_ASSOC); + foreach ($res2 as $data) { + if (!empty($data['COLUMN_NAME'])) { + $flags[$data['COLUMN_NAME']][] = 'primary_key'; + } + } + } + if (isset($flags[$column])) { + return implode(',', $flags[$column]); + } + return ''; + } + // }}} } ?>