public function fillIndexesTables(&$schemas)
{
$this->em->clear();
//Filling Indexes
$sql = "SELECT c2.relname as name,\n c2.relname as oid,\n i.indisprimary as \"isPrimary\",\n i.indisunique as \"isUnique\",\n i.indisclustered as \"isClustered\",\n i.indisvalid as \"isValid\",\n pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as \"creationQuery\",\n pg_catalog.pg_get_constraintdef(con.oid, true) as \"constraintDef\",\n contype,\n condeferrable,\n condeferred,\n c2.reltablespace,\n c.oid as table,\n n.nspname as schema,\n c.relname as table_name\n FROM pg_catalog.pg_class c\n INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid\n INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid\n LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n WHERE 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 i.indisprimary DESC,\n i.indisunique DESC,\n c2.relname";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('isPrimary', 'isPrimary');
$rsm->addScalarResult('isUnique', 'isUnique');
$rsm->addScalarResult('isClustered', 'isClustered');
$rsm->addScalarResult('isValid', 'isValid');
$rsm->addScalarResult('creationQuery', 'creationQuery');
$rsm->addScalarResult('constraintDef', 'constraintDef');
$rsm->addScalarResult('contype', 'contype');
$rsm->addScalarResult('condeferrable', 'condeferrable');
$rsm->addScalarResult('condeferred', 'condeferred');
$rsm->addScalarResult('reltablespace', 'reltablespace');
$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) {
$index = new Index($row['schema'], $row['table']);
foreach ($row as $key => $value) {
if ($key == 'creationQuery') {
$data = explode('USING', $value);
if (count($data) == 2) {
$infos = explode(' ', trim($data[1]));
$type = $infos[0];
$columns = trim(substr($data[1], strlen($infos[0]) + 2));
$index->__set('type', $type);
$index->__set('columns', $columns);
} else {
$index->__set('type', $value);
}
} else {
$index->__set($key, $value);
}
}
if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
$schemas[$row['schema']]->addTableIndex($row['table'], $index);
} elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
$schemas[$row['schema']]->addTableIndex($row['table_name'], $index);
}
unset($index);
}
unset($stmt);
}
/**
* @param $schema
* @param $tableOid
* @return array
*/
public function getIndexes($schema, $tableOid)
{
//Filling Indexes
$sql = "SELECT c2.relname as name,\n c2.relname as oid,\n i.indisprimary as \"isPrimary\",\n i.indisunique as \"isUnique\",\n i.indisclustered as \"isClustered\",\n i.indisvalid as \"isValid\",\n pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as \"creationQuery\",\n pg_catalog.pg_get_constraintdef(con.oid, true) as \"constraintDef\",\n contype,\n condeferrable,\n condeferred,\n c2.reltablespace\n FROM pg_catalog.pg_class c\n INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid\n INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid\n LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n WHERE c.oid = '" . pg_escape_string($tableOid) . "'\n ORDER BY i.indisprimary DESC,\n i.indisunique DESC,\n c2.relname";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('isPrimary', 'isPrimary');
$rsm->addScalarResult('isUnique', 'isUnique');
$rsm->addScalarResult('isClustered', 'isClustered');
$rsm->addScalarResult('isValid', 'isValid');
$rsm->addScalarResult('creationQuery', 'creationQuery');
$rsm->addScalarResult('constraintDef', 'constraintDef');
$rsm->addScalarResult('contype', 'contype');
$rsm->addScalarResult('condeferrable', 'condeferrable');
$rsm->addScalarResult('condeferred', 'condeferred');
$rsm->addScalarResult('reltablespace', 'reltablespace');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, self::CACHE_LIFETIME);
$indexes = [];
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$index = new Index($schema, $tableOid);
foreach ($row as $key => $value) {
if ($key == 'creationQuery') {
$data = explode('USING', $value);
if (count($data) == 2) {
$infos = explode(' ', trim($data[1]));
$type = $infos[0];
$columns = trim(substr($data[1], strlen($infos[0]) + 2));
$index->__set('type', $type);
$index->__set('columns', $columns);
} else {
$index->__set('type', $value);
}
} else {
$index->__set($key, $value);
}
}
if ($this->index_type == self::INDEX_TYPE_OID) {
$indexes[$row['oid']] = $index;
} elseif ($this->index_type == self::INDEX_TYPE_NAME) {
$indexes[$row['name']] = $index;
}
}
return $indexes;
}
