Only in JMDB/: MDB.patch diff -C 3 MDB/manager.php JMDB/manager.php iff -C 3 MDB/manager_pgsql.php JMDB/manager_pgsql.php *** MDB/manager_pgsql.php Mon Sep 9 07:25:39 2002 --- JMDB/manager_pgsql.php Mon Sep 9 07:30:23 2002 *************** *** 349,368 **** */ function listTableFields(&$db, $table) { ! $result = $db->query("SELECT * FROM $table"); if(MDB::isError($result)) { return $result; } $columns = $db->getColumnNames($result); if(MDB::isError($columns)) { $db->freeResult($columns); } ! return $columns; } // }}} // {{{ getTableFieldDefinition() // }}} // {{{ listViews() --- 349,626 ---- */ function listTableFields(&$db, $table) { ! $result = $db->query(" ! SELECT ! a.attname AS field ! FROM ! pg_class c, ! pg_attribute a ! WHERE ! c.relname = '$table' ! and a.attnum > 0 ! and a.attrelid = c.oid ! ORDER BY a.attnum ! ! "); ! if(MDB::isError($result)) { return $result; } + $columns = $db->getColumnNames($result); if(MDB::isError($columns)) { $db->freeResult($columns); } ! if(!isset($columns['field'])) { ! $db->freeResult($result); ! return $db->raiseError(DB_ERROR_MANAGER, '', '', ! 'List table fields: show columns does not return the table field names'); ! } ! $field_column = $columns['field']; ! for($fields = array(), $field = 0; !$db->endOfResult($result); ++$field) { ! $field_name = $db->fetch($result, $field, $field_column); ! if ($field_name != $db->dummy_primary_key) ! $fields[] = $field_name; ! } ! $db->freeResult($result); ! return ($fields); ! } // }}} // {{{ getTableFieldDefinition() + /** + * get the stucture of a field into an array + * + * @param $dbs (reference) array where database names will be stored + * @param string $table name of table that should be used in method + * @param string $field name of field that should be used in method + * @return mixed data array on success, a DB error on failure + * @access public + */ + function getTableFieldDefinition(&$db, $table, $field) + { + $field_name = strtolower($field); + if ($field_name == $db->dummy_primary_key) { + return $db->raiseError(DB_ERROR_MANAGER, '', '', + 'Get table field definiton: '.$db->dummy_primary_key.' is an hidden column'); + } + // $result = $db->query("SHOW COLUMNS FROM $table"); + $result = $db->query(" + SELECT + a.attnum, + a.attname AS field, + t.typname AS type, + a.attlen AS length, + a.atttypmod AS lengthvar, + a.attnotnull AS notnull + FROM + pg_class c, + pg_attribute a, + pg_type t + WHERE + c.relname = '$table' + and a.attnum > 0 + and a.attrelid = c.oid + and a.atttypid = t.oid + ORDER BY a.attnum + + "); + + if(MDB::isError($result)) { + return $result; + } + $columns = $db->getColumnNames($result); + if(MDB::isError($columns)) { + $db->freeResult($columns); + return $columns; + } + if (!isset($columns[$column = 'field']) + || !isset($columns[$column = 'type'])) + { + $db->freeResult($result); + return $db->raiseError(DB_ERROR_MANAGER, '', '', + 'Get table field definition: show columns does not return the column '.$column); + } + + $field_num = $columns['attnum']; + $field_column = $columns['field']; + $type_column = $columns['type']; + $length_column = $columns['length']; + $decimal_column = $columns['lengthvar']; + + + while (is_array($row = $db->fetchInto($result))) { + if ($field_name == strtolower($row[$field_column])) { + $db_type = strtolower($row[$type_column]); + if ($row[$length_column]==-1) { + $length = $row[$decimal_column]-4; + } else { + $length = $row[$length_column]; + } + $decimal = $row[$decimal_column]; + $type = array(); + // The following still needs some adjusting for + // postgres + switch($db_type) { + case 'tinyint': + case 'smallint': + case 'mediumint': + case 'int': + case 'int4': + case 'int8': + case 'integer': + case 'bigint': + $type[0] = 'integer'; + if($length == '1') { + $type[1] = 'boolean'; + } + break; + case 'tinytext': + case 'mediumtext': + case 'longtext': + case 'text': + case 'char': + case 'bpchar': + case 'varchar': + $type[0] = 'text'; + if($decimal == 'binary') { + $type[1] = 'blob'; + } elseif($length == '1') { + $type[1] = 'boolean'; + } elseif(strstr($db_type, 'text')) + $type[1] = 'clob'; + break; + case 'enum': + preg_match_all('/\'.+\'/U',"enum('active','nonactive')", $matches); + $length = 0; + if(is_array($matches)) { + foreach($matches[0] as $value) { + $length = max($length, strlen($value)-2); + } + } + unset($decimal); + case 'set': + $type[0] = 'text'; + $type[1] = 'integer'; + break; + case 'date': + $type[0] = 'date'; + break; + case 'datetime': + case 'timestamp': + $type[0] = 'timestamp'; + break; + case 'time': + $type[0] = 'time'; + break; + case 'float': + case 'double': + case 'real': + $type[0] = 'float'; + break; + case 'decimal': + case 'numeric': + $type[0] = 'decimal'; + break; + case 'tinyblob': + case 'mediumblob': + case 'longblob': + case 'blob': + $type[0] = 'blob'; + break; + case 'year': + $type[0] = 'integer'; + $type[1] = 'date'; + break; + default: + return $db->raiseError(DB_ERROR_MANAGER, '', '', + 'List table fields: unknown database attribute type: '.$db_type); + } + unset($notnull); + if (isset($columns['notnull']) + && $row[$columns['notnull']] == 't') + { + $notnull = 1; + } + unset($default); + if (isset($columns['default']) + && isset($row[$columns['default']])) + { + $default = $row[$columns['default']]; + } + $definition = array(); + for($field_choices = array(), $datatype = 0; $datatype < count($type); $datatype++) { + $field_choices[$datatype] = array('type' => $type[$datatype]); + if(isset($notnull)) { + $field_choices[$datatype]['notnull'] = 1; + } + + // if(isset($default)) { + // $field_choices[$datatype]['default'] = $default; + // } + $dresult = $db->query("SELECT d.adsrc AS rowdefault + FROM pg_attrdef d, pg_class c + WHERE + c.relname = '$table' AND + c.oid = d.adrelid AND + d.adnum = $row[$field_num]"); + + if(MDB::isError($dresult)) { + return $dresult; + } + + $defarow = $db->fetchInto($dresult); + if(!isset($defarow[0])) { + $field_choices[$datatype]['default'] = ""; + } + else { + $field_choices[$datatype]['default'] = $defarow[0]; + } + + if(strlen($length)) { + $field_choices[$datatype]['length'] = $length; + } + } + $definition[0] = $field_choices; + if (isset($columns['extra']) + && isset($row[$columns['extra']]) + && $row[$columns['extra']] == 'auto_increment') + { + $implicit_sequence = array(); + $implicit_sequence['on'] = array(); + $implicit_sequence['on']['table'] = $table; + $implicit_sequence['on']['field'] = $field; + $definition[1]['name'] = $table.'_'.$field_name; + $definition[1]['definition'] = $implicit_sequence; + } + if (isset($columns['key']) + && isset($row[$columns['key']]) + && $row[$columns['key']] == 'PRI') + { + $implicit_index = array(); + $implicit_index['unique'] = 1; + $implicit_index['FIELDS'][$field] = ''; + $definition[2]['name'] = $field_name; + $definition[2]['definition'] = $implicit_index; + } + $db->freeResult($result); + return ($definition); + } + } + if(!$db->options['autofree']) { + $db->freeResult($result); + } + if(MDB::isError($row)) { + return($row); + } + return $db->raiseError(DB_ERROR_MANAGER, '', '', + 'Get table field definition: it was not specified an existing table column'); + } + + + // }}} // {{{ listViews() *************** *** 381,389 **** --- 639,771 ---- // }}} // {{{ listTableIndexes() + function listTableIndexes(&$db, $table) + { + + $sql_pri_keys = " + SELECT + ic.relname AS key_name, + bc.relname AS tab_name, + ta.attname AS column_name, + i.indisunique AS unique_key, + i.indisprimary AS primary_key + FROM + pg_class bc, + pg_class ic, + pg_index i, + pg_attribute ta, + pg_attribute ia + WHERE + bc.oid = i.indrelid + AND ic.oid = i.indexrelid + AND ia.attrelid = i.indexrelid + AND ta.attrelid = bc.oid + AND bc.relname = '$table' + AND ta.attrelid = i.indrelid + AND ta.attnum = i.indkey[ia.attnum-1] + ORDER BY + key_name, tab_name, column_name + "; + + if(MDB::isError($result = $db->query($sql_pri_keys))) { + return($result); + } + if(MDB::isError($columns = $db->getColumnNames($result))) + { + $db->freeResult($result); + return($columns); + } + if(!isset($columns['key_name'])) + { + $db->freeResult($result); + return $db->raiseError(DB_ERROR_MANAGER, '', '', 'List table indexes: show index does not return the table index names'); + } + $indexes_all = $db->fetchCol($result, DB_FETCHMODE_ORDERED, $columns['key_name']); + for($found = $indexes = array(), $index = 0; $index < count($indexes_all); $index++) + { + $indexes[$index] = $indexes_all[$index]; + } + $db->freeResult($result); + return($indexes_all); + } + + // }}} // {{{ getTableIndexDefinition() + function getTableIndexDefinition(&$db, $table, $index) + { + $index_name = strtolower($index); + if($index == 'PRIMARY') { + return $db->raiseError(DB_ERROR_MANAGER, '', '', 'Get table index definition: PRIMARY is an hidden index'); + } + + $sql_pri_keys = " + SELECT + ic.relname AS key_name, + bc.relname AS tab_name, + ta.attname AS column_name, + i.indisunique AS unique_key, + i.indisprimary AS primary_key, + 'A' as collation + FROM + pg_class bc, + pg_class ic, + pg_index i, + pg_attribute ta, + pg_attribute ia + WHERE + bc.oid = i.indrelid + AND ic.oid = i.indexrelid + AND ia.attrelid = i.indexrelid + AND ta.attrelid = bc.oid + AND bc.relname = '$table' + AND ta.attrelid = i.indrelid + AND ta.attnum = i.indkey[ia.attnum-1] + AND ic.relname = '$index' + ORDER BY + key_name, tab_name, column_name + "; + + if(MDB::isError($result = $db->query($sql_pri_keys))) { + return($result); + } + + if(MDB::isError($columns = $db->getColumnNames($result))) + { + $db->freeResult($result); + return($columns); + } + if(!isset($columns['unique_key']) + || !isset($columns['key_name']) + || !isset($columns['column_name']) + || !isset($columns['collation'])) + { + $db->freeResult($result); + return $db->raiseError(DB_ERROR_MANAGER, '', '', 'Get table index definition: show index does not return the column '.$column); + } + $unique_column = $columns['unique_key']; + $key_name_column = $columns['key_name']; + $column_name_column = $columns['column_name']; + $collation_column = $columns['collation']; + $definition = array(); + $row = $db->fetchInto($result); + $key_name = strtolower($row[$key_name_column]); + if($row[$unique_column]=='t') { + $definition['unique'] = 1; + } + $column_name = $row[$column_name_column]; + $definition['FIELDS'][$column_name] = array(); + if(isset($row[$collation_column])) { + $definition['FIELDS'][$column_name]['sorting'] = ($row[$collation_column] == 'A' ? 'ascending' : 'descending'); + } + $db->freeResult($result); + if (!isset($definition['FIELDS'])) { + return $db->raiseError(DB_ERROR_MANAGER, '', '', 'Get table index definition: it was not specified an existing table index'); + } + return ($definition); + } + // }}} // {{{ createSequence() *************** *** 433,455 **** function listSequences(&$db) { // gratuitously stolen and adapted from PEAR DB _getSpecialQuery in pgsql.php ! $sql = 'SELECT c.relname as "Name" ! FROM pg_class c, pg_user u ! WHERE c.relowner = u.usesysid AND c.relkind = \'S\' ! AND not exists (select 1 from pg_views where viewname = c.relname) ! AND c.relname !~ \'^pg_\' ! UNION ! SELECT c.relname as "Name" ! FROM pg_class c ! WHERE c.relkind = \'S\' ! AND not exists (select 1 from pg_views where viewname = c.relname) ! AND not exists (select 1 from pg_user where usesysid = c.relowner) ! AND c.relname !~ \'^pg_\''; return $db->queryCol($sql, NULL, DB_FETCHMODE_ORDERED); } // }}} // {{{ getSequenceDefinition() } }; --- 815,852 ---- function listSequences(&$db) { // gratuitously stolen and adapted from PEAR DB _getSpecialQuery in pgsql.php ! $sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='S' ORDER BY relname"; return $db->queryCol($sql, NULL, DB_FETCHMODE_ORDERED); } // }}} // {{{ getSequenceDefinition() + /** + * get the stucture of a sequence into an array + * + * @param $dbs (reference) array where database names will be stored + * @param string $sequence name of sequence that should be used in method + * @return mixed data array on success, a DB error on failure + * @access public + */ + function getSequenceDefinition(&$db, $sequence) + { + $sqn=$db->_isSequenceName($sequence); + $start = $db->currId($sqn); + if (MDB::isError($start)) { + return ($start); + } + if ($db->support('CurrId')) { + $start++; + } else { + $db->warnings[] = 'database does not support getting current + sequence value,the sequence value was incremented'; + } + $definition = array('start' => $start); + return($definition); + return $db->raiseError(DB_ERROR_MANAGER, '', '', 'Get sequence definition: it was not specified an existing sequence'); + } + } }; diff -C 3 MDB/pgsql.php JMDB/pgsql.php *** MDB/pgsql.php Mon Sep 9 07:25:39 2002 --- JMDB/pgsql.php Mon Sep 9 07:23:56 2002 *************** *** 1183,1189 **** */ function currId($name) { ! $seqname = $this->getSequenceName($seq_name); if (MDB::isError($result = $this->query("SELECT last_value FROM $seqname"))) { return $this->raiseError(DB_ERROR, NULL, NULL, 'currId: Unable to select from ' . $seqname) ; } --- 1183,1189 ---- */ function currId($name) { ! $seqname = $this->getSequenceName($name); if (MDB::isError($result = $this->query("SELECT last_value FROM $seqname"))) { return $this->raiseError(DB_ERROR, NULL, NULL, 'currId: Unable to select from ' . $seqname) ; }