/**
* Load the current selections from the database. Will return an array
* of field IDs.
*
* @return array
*/
public function load()
{
$userRefs = $this->getUserReferenceValues();
$select = $this->selectForLoad();
foreach ($userRefs as $column => $value) {
$select->where(sprintf('%s = ?', $this->dbAdapter->quoteIdentifier($column)), $value);
}
$selections = $this->dbAdapter->fetchCol($select);
// Fall back to global selections, if no per-user selections are available
if (count($userRefs) && !count($selections)) {
$select = $this->selectForLoad();
foreach ($userRefs as $column => $value) {
$select->where(sprintf('%s IS NULL', $this->dbAdapter->quoteIdentifier($column)));
}
$selections = $this->dbAdapter->fetchCol($select);
}
return is_array($selections) ? $selections : [];
}
public function listMissingForeignKeyIndexes($tableName)
{
$missingIndexes = $this->adapter->fetchCol('SELECT ARRAY_TO_JSON(column_name_list) AS columns
FROM (
SELECT DISTINCT
conrelid,
ARRAY_AGG(attname) AS column_name_list,
ARRAY_AGG(attnum) AS column_list
FROM pg_attribute
JOIN (
SELECT conrelid::regclass,
conname,
UNNEST(conkey) as column_index
FROM (
SELECT DISTINCT
conrelid,
conname,
conkey
FROM pg_constraint
JOIN pg_class ON pg_class.oid = pg_constraint.conrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname !~ \'^pg_\'
AND nspname <> \'information_schema\'
AND contype = \'f\'
) fkey
) fkey
ON fkey.conrelid = pg_attribute.attrelid
AND fkey.column_index = pg_attribute.attnum
GROUP BY conrelid, conname
) candidate_index
JOIN pg_class ON pg_class.oid = candidate_index.conrelid
LEFT JOIN pg_index ON pg_index.indrelid = conrelid
AND indkey::text = ARRAY_TO_STRING(column_list, \' \')
WHERE indexrelid IS NULL
AND relname = ?', [$tableName]);
$out = [];
foreach ($missingIndexes as $columnsJson) {
$out[] = json_decode($columnsJson, true);
}
sort($out);
return $out;
}
/**
* @expectedException \Dewdrop\Exception
*/
public function testBadFetchColThrowsException()
{
$this->db->fetchCol('not even close to valid sql');
}