/**
* Return the meta data of a certain table.
*
* @param string $table the table name
* @param bool $full all meta data or not
*
* @return array with meta data
*/
public function metadata($table, $full = false)
{
/* first connect */
if ($this->connect() == self::DB_SUCCESS) {
$ddl = Ddl::create('MySqli');
/* list fields */
Tools::atkdebug("Retrieving metadata for {$table}");
/* The tablename may also contain a schema. If so we check for it. */
if (strpos($table, '.') !== false) {
list($dbname, $tablename) = explode('.', $table);
/* get meta data */
$id = @$this->_query("SELECT * FROM `{$dbname}`.`{$tablename}` LIMIT 0", true);
} else {
/* get meta data */
$id = $this->_query("SELECT * FROM `{$table}` LIMIT 0", true);
}
// table type
$tableType = $this->_getTableType(isset($tablename) ? $tablename : $table);
if (!$id) {
Tools::atkdebug('Metadata query failed.');
return [];
}
$i = 0;
$result = [];
while ($finfo = mysqli_fetch_field($id)) {
$result[$i]['table'] = $finfo->table;
$result[$i]['table_type'] = $tableType;
$result[$i]['name'] = $finfo->name;
$result[$i]['type'] = $finfo->type;
$result[$i]['gentype'] = $ddl->getGenericType($finfo->type);
$result[$i]['len'] = $finfo->length;
$result[$i]['flags'] = 0;
// if the connection character set is UTF8 MySQL returns the length multiplied
// by 3, probably because the max length of an UTF8 character is 3 bytes, we need
// the real size in characters, so we divide the length by 3
if (strtoupper($this->m_charset) == 'UTF8' && ($result[$i]['gentype'] == 'string' || $result[$i]['gentype'] == 'text')) {
$result[$i]['len'] /= 3;
} else {
if ($result[$i]['gentype'] == 'decimal') {
// for a mysql type DECIMAL, the length is returned as M+2 (signed) or M+1 (unsigned)
$offset = $finfo->flags & MYSQLI_UNSIGNED_FLAG ? 1 : 2;
$result[$i]['len'] -= $offset + $finfo->decimals;
$result[$i]['len'] .= ',' . $finfo->decimals;
// TODO we should also save the "unsigned" flag in $result[$i]["flags"]
}
}
if ($finfo->flags & MYSQLI_PRI_KEY_FLAG) {
$result[$i]['flags'] |= Db::MF_PRIMARY;
}
if ($finfo->flags & MYSQLI_UNIQUE_KEY_FLAG) {
$result[$i]['flags'] |= Db::MF_UNIQUE;
}
if ($finfo->flags & MYSQLI_NOT_NULL_FLAG) {
$result[$i]['flags'] |= Db::MF_NOT_NULL;
}
if ($finfo->flags & MYSQLI_AUTO_INCREMENT_FLAG) {
$result[$i]['flags'] |= Db::MF_AUTO_INCREMENT;
}
if ($full) {
$result['meta'][$result[$i]['name']] = $i;
}
++$i;
}
if ($full) {
$result['num_fields'] = $i;
}
mysqli_free_result($id);
Tools::atkdebug("Metadata for {$table} complete");
return $result;
}
return [];
}
/**
* Build a CREATE TABLE query and return it as a string.
*
* @return string The CREATE TABLE query.
*/
public function buildCreate()
{
$query = parent::buildCreate();
if (!empty($this->m_db->m_charset)) {
$query .= ' DEFAULT CHARACTER SET ' . $this->m_db->m_charset;
if (!empty($this->m_db->m_collate)) {
$query .= ' COLLATE ' . $this->m_db->m_collate;
}
}
if (!empty($query) && !empty($this->m_table_type)) {
$query .= ' TYPE=' . $this->m_table_type;
}
return $query;
}
/**
* Create an atkDdl object for constructing ddl queries.
*
* @return Ddl Ddl object
*/
public function createDdl()
{
$ddl = Ddl::create($this->m_type);
$ddl->m_db = $this;
return $ddl;
}
/**
* Return the meta data of a certain table.
*
* @param string $table the table name
* @param bool $full all meta data or not
*
* @return array with meta data
*/
public function metadata($table, $full = false)
{
$ddl = Ddl::create('PgSql');
if (strpos($table, '.') != false) {
// there is a period in the table, so we split out the schema name.
$schema = substr($table, 0, strpos($table, '.'));
$table = substr($table, strpos($table, '.') + 1);
$schema_condition = "AND n.nspname = '{$schema}' ";
$schema_join = ' LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)';
} else {
//no period in the name, so there is no schema
$schema_condition = '';
$schema_join = '';
}
// Get meta data from system tables.
// See developer manual (www.postgresql.org)
// for system table specification.
$sql = "SELECT\n a.attnum AS i,\n a.attname AS name,\n t.typname AS type,\n (CASE WHEN LOWER(t.typname) = 'varchar' AND a.attlen = -1 THEN a.atttypmod - 4 ELSE a.attlen END) AS length,\n (CASE WHEN a.attnotnull THEN 1 ELSE 0 END) AS is_not_null,\n (\n SELECT COUNT(1)\n FROM pg_index i\n WHERE i.indrelid = c.oid\n AND i.indisprimary = true\n AND a.attnum IN (\n i.indkey[0], i.indkey[1], i.indkey[2],\n i.indkey[3], i.indkey[4], i.indkey[5],\n i.indkey[6], i.indkey[7], i.indkey[8]\n )\n LIMIT 1\n ) AS is_primary,\n (\n SELECT COUNT(1)\n FROM pg_index i\n WHERE i.indrelid = c.oid\n AND i.indisunique = true\n AND i.indnatts = 1\n AND i.indkey[0] = a.attnum\n LIMIT 1\n ) AS is_unique,\n (CASE WHEN ad.adsrc LIKE 'nextval(%::text)' THEN 1 ELSE 0 END) AS is_auto_inc,\n (CASE WHEN ad.adsrc LIKE 'nextval(%::text)' THEN SUBSTRING(ad.adsrc, '''(.*?)''') END) AS sequence,\n (CASE WHEN t.typname = 'varchar' THEN SUBSTRING(ad.adsrc FROM '^''(.*)''.*\$') ELSE ad.adsrc END) AS default\n FROM pg_class c\n JOIN pg_attribute a ON (a.attrelid = c.oid AND a.attnum > 0)\n JOIN pg_type t ON (t.oid = a.atttypid)\n LEFT JOIN pg_attrdef ad ON (ad.adrelid = c.oid AND ad.adnum = a.attnum)\n {$schema_join}\n WHERE c.relname = '{$table}'\n {$schema_condition}\n ORDER BY a.attnum";
$meta = [];
$rows = $this->getRows($sql);
foreach ($rows as $i => $row) {
$meta[$i]['table'] = $table;
$meta[$i]['type'] = $row['type'];
$meta[$i]['gentype'] = $ddl->getGenericType($row['type']);
$meta[$i]['name'] = $row['name'];
$meta[$i]['len'] = $row['length'];
$meta[$i]['flags'] = ($row['is_primary'] == 1 ? Db::MF_PRIMARY : 0) | ($row['is_unique'] == 1 ? Db::MF_UNIQUE : 0) | ($row['is_not_null'] == 1 ? Db::MF_NOT_NULL : 0) | ($row['is_auto_inc'] == 1 ? Db::MF_AUTO_INCREMENT : 0);
if ($row['is_auto_inc'] == 1) {
$meta[$i]['sequence'] = $row['sequence'];
} else {
if (Tools::atk_strlen($row['default']) > 0) {
// date/time/datetime
if (strtolower($row['default']) == 'now' && in_array($meta[$i]['gentype'], array('date', 'time', 'datetime'))) {
$meta[$i]['default'] = 'NOW';
} else {
if (in_array($meta[$i]['gentype'], array('number', 'decimal'))) {
$meta[$i]['default'] = $row['default'];
} else {
if (in_array($meta[$i]['gentype'], array('string', 'text'))) {
$meta[$i]['default'] = $row['default'];
} else {
if ($meta[$i]['gentype'] == 'boolean') {
$meta[$i]['default'] = strtolower($row['default']) == 't' ? 1 : 0;
}
}
}
}
}
}
if ($full) {
$meta['meta'][$row['name']] =& $meta[$i];
}
}
if ($full) {
$meta['num_fields'] = count($rows);
}
return $meta;
}
/**
* Constructor.
*/
public function __construct()
{
parent::__construct();
}
