/**
* Override {@link epDbPort::checkIndex()}
* @param epClassMap $cm
* @param epDb $db
* @return false|array
*/
public function checkIndex($cm, $db)
{
// reset counter and return value
$ret = array(array(), array());
// get all columns in the pg_attribute and pg_class table
$sql = 'SELECT a.attname, a.attnum ' . ' FROM pg_attribute a, pg_class c ' . ' WHERE c.relname = ' . $db->quote($cm->getTable()) . ' AND a.attrelid = c.oid AND a.attnum > 0' . ' ORDER BY a.attnum';
// execute the query
if (!$db->execute($sql)) {
return false;
}
// array to collect all columns
$columns = array();
// go through reach record
$okay = $this->db->rsRestart();
while ($okay) {
$num = $db->rsGetCol('attnum');
$name = $db->rsGetCol('attname');
$columns[$num] = $name;
$okay = $this->db->rsNext();
}
// get all the indexes in the table (indkey has a list, space separated)
$sql = 'SELECT c2.relname AS indexname, i.indisprimary, i.indisunique, i.indkey AS indkey' . ' FROM pg_class c, pg_class c2, pg_index i' . ' WHERE c.relname = ' . $db->quote($cm->getTable()) . ' AND c.oid = i.indrelid AND i.indexrelid = c2.oid';
// execute above query
if (!$db->execute($sql)) {
return false;
}
// go through reach record
$okay = $db->rsRestart();
while ($okay) {
// skip the primary index
if ($this->db->rsGetCol('indisprimary') == 't') {
// next row
$okay = $this->db->rsNext();
continue;
}
// get index name
$name = $this->db->rsGetCol('indexname');
$unique = $this->db->rsGetCol('indisunique');
// $unique is t if unique
// $unique is f if index
$unique = $unique == 't' ? 0 : 1;
$indexes = explode(' ', $this->db->rsGetCol('indkey'));
foreach ($indexes as $index) {
$ret[$unique][$name][] = $columns[$index];
}
// next row
$okay = $db->rsNext();
}
return $ret;
}
/**
* Override {@link epDbPort::createTable()}
*
* Generate SQL code to create table
*
* @param epClassMap $cm
* @param string $indent
* @param epDb $db
* @return string|array (of strings)
*/
public function createTable($cm, $db, $indent = ' ')
{
// start create table
$sql = "CREATE TABLE \"" . $cm->getTable() . "\" (";
$sql = $sql . "CONSTRAINT " . $cm->getTable() . "_" . $cm->getOidColumn() . " PRIMARY KEY (" . $cm->getOidColumn() . "),";
// the oid field
$fstr = $this->_defineField($cm->getOidColumn(), 'Integer', '', false, true);
$sql .= $fstr . ",";
// write sql for each field
foreach ($cm->getAllFields() as $fname => $fm) {
if ($fm->isPrimitive()) {
// get the field definition
$fstr = $this->_defineField($db->quoteId($fm->getColumnName()), $fm->getType(), $fm->getTypeParams(), $fm->getDefaultValue(), false);
$sql .= $fstr . ",";
}
}
// remove the last ','
$sql = substr($sql, 0, strlen($sql) - 1);
// end of table creation
// WITH OIDS - see http://www.ezpdo.net/forum/viewtopic.php?pid=750#p750
$sql .= ") WITH OIDS;";
return $sql;
}
/**
* Override {@link epDbPort::checkIndex()}
* @param epClassMap $cm
* @param epDb $db
* @return false|array
*/
public function checkIndex($cm, $db)
{
// get index list
$sql = 'PRAGMA index_list(' . $db->quoteId($cm->getTable()) . ')';
if (!$db->execute($sql)) {
return false;
}
// reset counter and return value
$ret = array(array(), array());
$indexes = array();
$uniques = array();
// go through reach record
$okay = $db->rsRestart();
while ($okay) {
// get index name
$name = $db->rsGetCol('name');
$unique = $db->rsGetCol('unique');
// $unique is 1 if unique
// $unique is 0 if index
$unique = !$unique;
// ???
// store the index name for further information
$indexes[] = $name;
$uniques[$name] = $unique;
$ret[$unique][$name] = array();
// next row
$okay = $db->rsNext();
}
// go through each index
foreach ($indexes as $index) {
$sql = 'PRAGMA index_info(' . $db->quoteId($index) . ')';
if (!$db->execute($sql)) {
return false;
}
// go through reach record
$okay = $db->rsRestart();
while ($okay) {
// get index name
$column = $db->rsGetCol('name');
$ret[$uniques[$index]][$index][] = $column;
// next row
$okay = $db->rsNext();
}
}
return $ret;
}
/**
* Generate SQL code to create table
*
* Sometimes extra procedure may be needed for some fields to work.
* For example, some database does not have a direct auto-incremental
* keyword and, to make it work, you need extra procedure after
* creating the table. Here is an example, for a table column in
* an Oracle database to be auto-incremental, we need to insert
* a sequence and a trigger (See this link for more info
* {@link http://webxadmin.free.fr/article.php?i=134}). If this is
* the case the subclass should override this method and return both
* "create table" statement and the extra.
*
* @param epClassMap $cm
* @param epDb $db
* @param string $indent
* @return string|array (of strings)
*/
public function createTable($cm, $db, $indent = ' ')
{
// start create table
$sql = "CREATE TABLE " . $db->quoteId($cm->getTable()) . " (\n";
// the oid field
$fstr = $this->_defineField($db->quoteId($cm->getOidColumn()), 'integer', '12', false, true);
$sql .= $indent . $fstr . ",\n";
// write sql for each field
foreach ($cm->getAllFields() as $fname => $fm) {
if ($fm->isPrimitive()) {
// get the field definition
$fstr = $this->_defineField($db->quoteId($fm->getColumnName()), $fm->getType(), $fm->getTypeParams(), $fm->getDefaultValue(), false);
$sql .= $indent . $fstr . ",\n";
}
}
// write primary key
$sql .= $indent . "PRIMARY KEY (" . $db->quoteId($cm->getOidColumn()) . ")\n";
// end of table creation
$sql .= ");\n";
return $sql;
}
/**
* Override {@link epDbPort::createTable()}
*
* Generate SQL code to create table
*
* @param epClassMap $cm
* @param string $indent
* @param epDb $db
* @return string|array (of strings)
*/
public function createTable($cm, $db, $indent = ' ')
{
// start create table
$sql = "CREATE TABLE " . $db->quoteId($cm->getTable()) . " (";
// the oid field
$fstr = $this->_defineField($db->quoteId($cm->getOidColumn()), 'INTEGER', '12', false, true);
$sql .= $fstr . ",";
// write sql for each field
foreach ($cm->getAllFields() as $fname => $fm) {
if ($fm->isPrimitive()) {
// get the field definition
$fstr = $this->_defineField($db->quoteId($fm->getColumnName()), $fm->getType(), $fm->getTypeParams(), $fm->getDefaultValue(), false);
$sql .= $fstr . ",";
}
}
// remove the last ','
$sql = substr($sql, 0, strlen($sql) - 1);
// end of table creation
$sql .= ");";
return $sql;
}
/**
* Return the '<table> as <alias>' part for the SQL FROM clause
* @return string
*/
public function getTableAlias()
{
// quote ids
$table = $this->quoteId($this->cm->getTable());
$alias = $this->quoteId($this->alias);
// return sql
return "{$table} AS {$alias}";
}
/**
* SQL to create uniques
* @param epClassMap $cm
* @return string
*/
public function createUnique($cm, $db, $curIndex = array())
{
$sqls = array('drop' => array(), 'create' => array());
foreach ($cm->getUniqueKeys() as $name => $key) {
$indexname = $this->_indexName($name, $cm->getTable());
if (in_array($indexname, array_keys($curIndex))) {
// check to see if the columns are the same
if (count($key) == count($curIndex[$indexname]) && count(array_diff($key, $curIndex[$indexname]) == 0)) {
unset($curIndex[$indexname]);
continue;
}
unset($curIndex[$indexname]);
// otherwise, we need to drop the index
$sql = $this->dropIndex($indexname, $cm->getTable(), $db);
$sqls['drop'][] = $sql;
}
// quote keys
foreach ($key as $k => $v) {
$key[$k] = $db->quoteId($v);
}
// make CREATE UNIQUE INDEX stmt
$sql = "CREATE UNIQUE INDEX ";
$sql .= $db->quoteId($indexname);
$sql .= " ON ";
$sql .= $db->quoteId($cm->getTable()) . " (" . join(', ', $key) . ");";
// collect stmt
$sqls['create'][] = $sql;
}
// drop all the old ones
// that weren't messed with above
foreach ($curIndex as $name => $key) {
// otherwise, we need to drop the index
$sql = $this->dropIndex($name, $cm->getTable(), $db);
$sqls['drop'][] = $sql;
}
return $sqls;
}
/**
* Create indexes and uniques specified in class map
* @param epClassMap $cm The class map
* @param bool Whether to force to create table or not
* @return bool
*/
public function index($cm, $create = false)
{
// check if class is abstract
if ($cm->isAbstract()) {
// if so, no need to actually create
return true;
}
// tabe not exists?
if (!$this->_tableExists($cm->getTable())) {
// done if -not- forced to create
if (!$force) {
return true;
}
// create (includes index creation. done.)
return $this->create($cm, true);
}
// check if index exists already
if (!($curIndexes = $this->checkIndex($cm))) {
return false;
}
// preapre sql statement for creating index
$sqls = epObj2Sql::sqlCreateIndex($this, $cm, $curIndexes);
if (!$sqls) {
return false;
}
// execute sql
return $r = $this->_execute($sqls);
}
