/**
* @see QueryWriter::queryRecordRelated
*/
public function queryRecordRelated($sourceType, $destType, $linkIDs, $addSql = '', $bindings = array())
{
$addSql = $this->glueSQLCondition($addSql, QueryWriter::C_GLUE_WHERE);
list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType);
$key = $this->getCacheKey(array($sourceType, $destType, implode(',', $linkIDs), $addSql, $bindings));
if ($this->flagUseCache && ($cached = $this->getCached($destType, $key))) {
return $cached;
}
$inClause = $this->getParametersForInClause($linkIDs, $bindings);
$sqlFilterStr = '';
if (count(self::$sqlFilters)) {
$sqlFilterStr = $this->getSQLFilterSnippet($destType);
}
if ($sourceType === $destType) {
$inClause2 = $this->getParametersForInClause($linkIDs, $bindings, count($bindings));
//for some databases
$sql = "\n\t\t\tSELECT\n\t\t\t\t{$destTable}.* {$sqlFilterStr} ,\n\t\t\t\tCOALESCE(\n\t\t\t\tNULLIF({$linkTable}.{$sourceCol}, {$destTable}.id),\n\t\t\t\tNULLIF({$linkTable}.{$destCol}, {$destTable}.id)) AS linked_by\n\t\t\tFROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) ) OR\n\t\t\t( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} IN ({$inClause2}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
$linkIDs = array_merge($linkIDs, $linkIDs);
} else {
$sql = "\n\t\t\tSELECT\n\t\t\t\t{$destTable}.* {$sqlFilterStr},\n\t\t\t\t{$linkTable}.{$sourceCol} AS linked_by\n\t\t\tFROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
}
$bindings = array_merge($linkIDs, $bindings);
$rows = $this->adapter->get($sql, $bindings);
$this->putResultInCache($destType, $key, $rows);
return $rows;
}
/**
* @see QueryWriter::getColumns
*/
public function getColumns($table)
{
$columnsRaw = $this->adapter->get("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $this->esc($table) . "'");
$columns = array();
foreach ($columnsRaw as $r) {
$columns[$r['COLUMN_NAME']] = $r['DATA_TYPE'] . (is_numeric($r['CHARACTER_MAXIMUM_LENGTH']) ? '(' . $r['CHARACTER_MAXIMUM_LENGTH'] . ')' : '');
}
return $columns;
}
/**
* @see QueryWriter::getColumns
*/
public function getColumns($table)
{
$columnsRaw = $this->adapter->get("DESCRIBE " . $this->esc($table));
$columns = array();
foreach ($columnsRaw as $r) {
$columns[$r['Field']] = $r['Type'];
}
return $columns;
}
/**
* @see QueryWriter::getColumns
*/
public function getColumns($table)
{
$table = $this->esc($table, TRUE);
$columnsRaw = $this->adapter->get("SELECT column_name, data_type FROM information_schema.columns WHERE table_name='{$table}' AND table_schema = ANY( current_schemas( FALSE ) )");
$columns = array();
foreach ($columnsRaw as $r) {
$columns[$r['column_name']] = $r['data_type'];
}
return $columns;
}
/**
* @see QueryWriter::getColumns
*/
public function getColumns($table)
{
$table = $this->esc($table);
$columnsRaw = $this->adapter->get("SHOW COLUMNS FROM {$table}");
$columns = array();
foreach ($columnsRaw as $r) {
$columns[$r['Field']] = $r['Type'];
}
return $columns;
}
/**
* @see QueryWriter::getColumns
*/
public function getColumns($table)
{
$table = $this->esc($table, TRUE);
$columnsRaw = $this->adapter->get("PRAGMA table_info('{$table}')");
$columns = array();
foreach ($columnsRaw as $r) {
$columns[$r['name']] = $r['type'];
}
return $columns;
}
/**
* @see QueryWriter::queryTagged
*/
public function queryTagged($type, $tagList, $all = FALSE, $addSql = '', $bindings = array())
{
$assocType = $this->getAssocTable(array($type, 'tag'));
$assocTable = $this->esc($assocType);
$assocField = $type . '_id';
$table = $this->esc($type);
$slots = implode(',', array_fill(0, count($tagList), '?'));
$score = $all ? count($tagList) : 1;
$sql = "\n\t\t\tSELECT {$table}.*, count({$table}.id) FROM {$table}\n\t\t\tINNER JOIN {$assocTable} ON {$assocField} = {$table}.id\n\t\t\tINNER JOIN tag ON {$assocTable}.tag_id = tag.id\n\t\t\tWHERE tag.title IN ({$slots})\n\t\t\tGROUP BY {$table}.id\n\t\t\tHAVING count({$table}.id) >= ?\n\t\t\t{$addSql}\n\t\t";
$bindings = array_merge($tagList, array($score), $bindings);
$rows = $this->adapter->get($sql, $bindings);
return $rows;
}
/**
* @see QueryWriter::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->esc($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column);
try {
foreach ($this->adapter->get("SHOW INDEX FROM {$table} ") as $ind) {
if ($ind['Key_name'] === $name) {
return;
}
}
$this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
} catch (\Exception $e) {
}
}
/**
* @see QueryWriter::addIndex
*/
public function addIndex($type, $name, $column)
{
$table = $type;
$table = $this->esc($table);
$name = preg_replace('/\\W/', '', $name);
$column = $this->esc($column, TRUE);
try {
foreach ($this->adapter->get("PRAGMA INDEX_LIST({$table}) ") as $ind) {
if ($ind['name'] === $name) {
return;
}
}
$t = $this->getTable($type);
$t['indexes'][$name] = array('name' => $column);
$this->putTable($t);
} catch (\Exception $exception) {
//do nothing
}
}
/**
* @see QueryWriter::addUniqueIndex
*/
public function addUniqueIndex($table, $columns)
{
$table = $this->esc($table, TRUE);
sort($columns);
//else we get multiple indexes due to order-effects
foreach ($columns as $k => $v) {
$columns[$k] = $this->esc($v);
}
$r = $this->adapter->get("SELECT i.relname AS index_name\n\t\t\tFROM pg_class t,pg_class i,pg_index ix,pg_attribute a\n\t\t\tWHERE t.oid = ix.indrelid\n\t\t\t\tAND i.oid = ix.indexrelid\n\t\t\t\tAND a.attrelid = t.oid\n\t\t\t\tAND a.attnum = ANY(ix.indkey)\n\t\t\t\tAND t.relkind = 'r'\n\t\t\t\tAND t.relname = '{$table}'\n\t\t\tORDER BY t.relname, i.relname;");
$name = "UQ_" . sha1($table . implode(',', $columns));
if ($r) {
foreach ($r as $i) {
if (strtolower($i['index_name']) == strtolower($name)) {
return;
}
}
}
$sql = "ALTER TABLE \"{$table}\"\n ADD CONSTRAINT {$name} UNIQUE (" . implode(',', $columns) . ")";
$this->adapter->exec($sql);
}
