/**
* @param $schemas
*/
public function fillTriggerTables(&$schemas)
{
$this->em->clear();
//Triggers
$sql = "SELECT t.tgname as name,\n t.oid as oid,\n pg_catalog.pg_get_triggerdef(t.oid, true) as \"creationQuery\",\n t.tgenabled as \"isEnabled\",\n tgfoid as \"functionOid\",\n p.proname as \"functionName\",\n t.tgrelid as table,\n n.nspname as schema,\n c.relname as table_name\n FROM pg_catalog.pg_trigger t\n LEFT JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid\n INNER JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n WHERE NOT t.tgisinternal\n AND c.relkind = 'r'\n AND n.nspname <> 'pg_catalog'\n AND n.nspname <> 'information_schema'\n AND n.nspname !~ '^pg_toast'\n AND n.nspname !~ '^pg_temp'\n AND n.nspname <> 'londiste'\n AND n.nspname <> 'pgq'\n --AND pg_catalog.pg_table_is_visible(c.oid)\n ORDER BY 1";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('creationQuery', 'creationQuery');
$rsm->addScalarResult('isEnabled', 'isEnabled');
$rsm->addScalarResult('functionOid', 'functionOid');
$rsm->addScalarResult('functionName', 'functionName');
$rsm->addScalarResult('schema', 'schema');
$rsm->addScalarResult('table', 'table');
$rsm->addScalarResult('table_name', 'table_name');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME);
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$trigger = new Trigger($row['schema'], $row['table']);
foreach ($row as $key => $value) {
$trigger->__set($key, $value);
}
if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
$schemas[$row['schema']]->addTableTrigger($row['table'], $trigger);
} elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
$schemas[$row['schema']]->addTableTrigger($row['table_name'], $trigger);
}
unset($trigger);
}
unset($stmt);
}
/**
* @param $schema
* @param $tableOid
* @return array
*/
public function getTriggers($schema, $tableOid)
{
//Triggers
$sql = "SELECT t.tgname as name,\n t.oid as oid,\n pg_catalog.pg_get_triggerdef(t.oid, true) as \"creationQuery\",\n t.tgenabled as \"isEnabled\",\n tgfoid as \"functionOid\",\n p.proname as \"functionName\"\n FROM pg_catalog.pg_trigger t\n LEFT JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid\n WHERE t.tgrelid = '" . pg_escape_string($tableOid) . "'\n AND NOT t.tgisinternal\n ORDER BY 1";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('creationQuery', 'creationQuery');
$rsm->addScalarResult('isEnabled', 'isEnabled');
$rsm->addScalarResult('functionOid', 'functionOid');
$rsm->addScalarResult('functionName', 'functionName');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, self::CACHE_LIFETIME);
$triggers = [];
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$trigger = new Trigger($schema, $tableOid);
foreach ($row as $key => $value) {
$trigger->__set($key, $value);
}
if ($this->index_type == self::INDEX_TYPE_OID) {
$triggers[$row['oid']] = $trigger;
} elseif ($this->index_type == self::INDEX_TYPE_NAME) {
$triggers[$row['name']] = $trigger;
}
}
return $triggers;
}
