declare a new empty Pix Table
| public static newEmptyTable ( $table_name = null ) : Pix_Table | ||
| return | Pix_Table | |
/**
* get Pix_Table object from db schema
*
* @param string $table_name
* @param string $pix_table_name
* @access public
* @return Pix_Table
*/
public function getTableFromDb($db_table_name, $pix_table_name = null)
{
$table = Pix_Table::newEmptyTable($pix_table_name);
$table->_name = $table_name;
$table->setDb($this);
$res = $this->query("DESCRIBE " . $this->column_quote($table_name));
while ($row = $res->fetch_assoc()) {
$field = $row['Field'];
$table->_columns[$field] = array();
// check type & size & unsigned
$type = $row['Type'];
if (preg_match('#^([a-z]+)\\((\\d+)\\)\\s?(unsigned)?$#', $type, $matches)) {
$table->_columns[$field]['type'] = $matches[1];
if (array_key_exists(1, $matches) and in_array($matches[1], array('binary', 'varchar', 'char'))) {
$table->_columns[$field]['size'] = $matches[2];
}
if (array_key_exists(3, $matches) and $matches[3] == 'unsigned') {
$table->_columns[$field]['unsigned'] = 1;
}
} elseif (preg_match('#^([a-z]+)\\s?(unsigned)?$#', $type, $matches)) {
$table->_columns[$field]['type'] = $matches[1];
if (array_key_exists(2, $matches) and $matches[2] == 'unsigned') {
$table->_columns[$field]['unsigned'] = 1;
}
} elseif (preg_match('#^enum\\((.*)\\)$#', $type, $matches)) {
$table->_columns[$field]['type'] = 'enum';
$table->_columns[$field]['list'] = $matches[1];
} elseif (preg_match('#^set\\((.*)\\)$#', $type, $matches)) {
$table->_columns[$field]['type'] = 'set';
$table->_columns[$field]['list'] = $matches[1];
} else {
// XXX: unknown type
}
// check autoincrement
if ($row['Extra'] == 'auto_increment') {
$table->_columns[$field]['auto_increment'] = 1;
}
// check default
if (!is_null($row['Default'])) {
$table->_columns[$field]['default'] = $row['Default'];
}
}
$res->free_result();
// check INDEX
$res = $this->query("SHOW INDEXES FROM " . $this->column_quote($table->_name));
$db_indexes = array();
while ($row = $res->fetch_assoc()) {
if (!array_key_exists($row['Key_name'], $db_indexes)) {
$db_indexes[$row['Key_name']] = array('type' => $row['Non_unique'] ? 'index' : 'unique', 'columns' => array());
}
$db_indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
}
$res->free_result();
foreach ($db_indexes as $name => $options) {
if ('PRIMARY' == $name) {
$table->_primary = $options['columns'];
} else {
$table->addIndex($name, $options['columns'], $options['type']);
}
}
return $table;
}
/**
* get Pix_Table object from db schema
*
* @param string $db_table_name
* @param string $pix_table_name
* @access public
* @return Pix_Table
*/
public function getTableFromDb($db_table_name, $pix_table_name = null)
{
$table = Pix_Table::newEmptyTable($pix_table_name);
$table->_name = $db_table_name;
$table->setDb($this);
$res = $this->query("SP_PKEYS " . $this->column_quote($db_table_name));
$table->_primary = array();
while ($row = $res->fetch_object()) {
$table->_primary[] = $row->COLUMN_NAME;
}
$res->free_result();
$res = $this->query("SP_COLUMNS " . $this->column_quote($db_table_name));
// http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs225.htm
$data_types = array(1 => 'char', 3 => 'int', 8 => 'double', 6 => 'float', 4 => 'int', 2 => 'int', 7 => 'double', 5 => 'smallint', 12 => 'varchar', -5 => 'bigint', -2 => 'binary', 9 => 'string', -1 => 'text', -4 => 'blob');
while ($row = $res->fetch_object()) {
$field = $row->COLUMN_NAME;
$table->_columns[$field] = array();
if (!array_key_exists($row->DATA_TYPE, $data_types)) {
throw new Pix_Table_Exception("Unknown DATA_TYPE: " . $row->DATA_TYPE);
}
$table->_columns[$field]['type'] = $data_types[$row->DATA_TYPE];
if ($row->TYPE_NAME == 'int identity') {
$table->_columns[$field]['auto_increment'] = 1;
}
// check default
if (!is_null($row->COLUMN_DEFAULT)) {
$table->_columns[$field]['default'] = $row->COLUMN_DEFAULT;
}
}
$res->free_result();
return $table;
}