class.ilDBPdoManagerPostgres.php

Go to the documentation of this file.

<?php

class ilDBPdoManagerPostgres extends ilDBPdoManager
{

    public function getQueryUtils()
    {
        if (!$this->query_utils) {
            $this->query_utils = new ilPostgresQueryUtils($this->db_instance);
        }

        return $this->query_utils;
    }


    public function getTableCreationQuery($name, $fields, $options = array())
    {
        return $this->getQueryUtils()->createTable($name, $fields, $options);
    }


    public function createTable($name, $fields, $options = array())
    {
        return $this->pdo->exec($this->getQueryUtils()->createTable($name, $fields, $options));
    }


    public function listTables($database = null)
    {
        $db = $this->db_instance;

        // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
        $query = 'SELECT c.relname AS "Name"' . ' FROM pg_class c, pg_user u' . ' WHERE c.relowner = u.usesysid' . " AND c.relkind = 'r'"
            . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views' . '  WHERE viewname = c.relname)' . " AND c.relname !~ '^(pg_|sql_)'" . ' UNION'
            . ' SELECT c.relname AS "Name"' . ' FROM pg_class c' . " WHERE c.relkind = 'r'" . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views'
            . '  WHERE viewname = c.relname)' . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_user' . '  WHERE usesysid = c.relowner)'
            . " AND c.relname !~ '^pg_'";
        $result = $db->queryCol($query, ilDBConstants::FETCHMODE_ASSOC);

        if ($db->options['portability']) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        sort($result); // FSX Patch
        return $result;
    }


    public function createDatabase($name)
    {
        $db = $this->db_instance;
        $name = $db->quoteIdentifier($name, true);

        return $db->manipulate("CREATE DATABASE $name");
    }


    public function dropDatabase($name)
    {
        $db = $this->db_instance;

        $name = $db->quoteIdentifier($name, true);

        return $db->manipulate("DROP DATABASE $name");
    }


    public function alterTable($name, $changes, $check)
    {
        $db = $this->db_instance;
        $reverse = $db->loadModule(ilDBConstants::MODULE_REVERSE);
        foreach ($changes as $change_name => $change) {
            switch ($change_name) {
                case 'add':
                case 'remove':
                case 'change':
                case 'name':
                case 'rename':
                    break;
                default:
                    throw new ilDatabaseException('change type "' . $change_name . '\" not yet supported');
            }
        }

        if ($check) {
            return true;
        }

        if (!empty($changes['add']) && is_array($changes['add'])) {
            foreach ($changes['add'] as $field_name => $field) {
                $query = 'ADD ' . $db->getFieldDefinition()->getDeclaration($field['type'], $field_name, $field);
                $result = $db->manipulate("ALTER TABLE $name $query");
            }
        }

        if (!empty($changes['remove']) && is_array($changes['remove'])) {
            foreach ($changes['remove'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                $query = 'DROP ' . $field_name;
                $result = $db->manipulate("ALTER TABLE $name $query");
            }
        }

        if (!empty($changes['change']) && is_array($changes['change'])) {
            foreach ($changes['change'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                if (!empty($field['type'])) {
                    $server_info = $db->getServerVersion();

                    if (is_array($server_info) && $server_info['major'] < 8) {
                        throw new ilDatabaseException('changing column type for "' . $change_name . '\" requires PostgreSQL 8.0 or above');
                    }

                    $query = "ALTER $field_name TYPE " . $db->getFieldDefinition()->getTypeDeclaration($field);
                    $result = $db->manipulate("ALTER TABLE $name $query");
                }
                if (array_key_exists('default', $field)) {
                    $query = "ALTER $field_name SET DEFAULT " . $db->quote($field['definition']['default'], $field['definition']['type']);
                    $result = $db->manipulate("ALTER TABLE $name $query");
                }
                if (!empty($field['notnull'])) {
                    $query = "ALTER $field_name " . ($field['definition']['notnull'] ? "SET" : "DROP") . ' NOT NULL';
                    $result = $db->manipulate("ALTER TABLE $name $query");
                }
            }
        }

        if (!empty($changes['rename']) && is_array($changes['rename'])) {
            foreach ($changes['rename'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                $result = $db->manipulate("ALTER TABLE $name RENAME COLUMN $field_name TO " . $db->quoteIdentifier($field['name'], true));
            }
        }

        if (!empty($changes['name'])) {
            $result = $db->manipulate("ALTER TABLE " . $db->quoteIdentifier($name, true) . " RENAME TO " . $db->quoteIdentifier($changes['name']));

            $idx = array_merge($this->listTableIndexes($changes['name']), $this->listTableConstraints($changes['name']));
            foreach ($idx as $index_name) {
                $index_newname = preg_replace("/^$name/", $changes['name'], $index_name);
                $result = $db->manipulate("ALTER INDEX " . $this->getIndexName($index_name) . " RENAME TO " . $this->getIndexName($index_newname));
            }
        }
        
        return true;
    }


    public function listTableFields($table)
    {
        $db = $this->db_instance;

        $table = $db->quoteIdentifier($table, true);
        $res = $this->pdo->query("select * from $table");
        for ($i = 0; $i < $res->columnCount(); $i++) {
            $data[] = $res->getColumnMeta($i)["name"];
        }
        return $data;
    }


    public function listTableIndexes($table)
    {
        $db = $this->db_instance;

        $table = $db->quote($table, 'text');
        $subquery = "SELECT indexrelid FROM pg_index, pg_class";
        $subquery .= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'";
        $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
        $indexes = $db->queryCol($query, 'text');

        $result = array();
        foreach ($indexes as $index) {
            $index = $this->fixIndexName($index);
            if (!empty($index)) {
                $result[$index] = true;
            }
        }

        if ($db->options['portability']) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }

        return array_keys($result);
    }


    public function listTableConstraints($table)
    {
        $db = $this->db_instance;

        $table = $db->quote($table, 'text');
        $subquery = "SELECT indexrelid FROM pg_index, pg_class";
        $subquery .= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND (indisunique = 't' OR indisprimary = 't')";
        $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
        $constraints = $db->queryCol($query);

        $result = array();
        foreach ($constraints as $constraint) {
            $constraint = $this->fixIndexName($constraint);
            if (!empty($constraint)) {
                $result[$constraint] = true;
            }
        }

        if ($db->options['portability']
            && $db->options['field_case'] == CASE_LOWER
        ) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }

        return array_keys($result);
    }


    public function createSequence($seq_name, $start = 1, $options = array())
    {
        $db = $this->db_instance;

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);

        return $db->manipulate("CREATE SEQUENCE $sequence_name INCREMENT 1" . ($start < 1 ? " MINVALUE $start" : '') . " START $start");
    }


    public function dropSequence($seq_name)
    {
        $db = $this->db_instance;

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);

        return $db->manipulate("DROP SEQUENCE $sequence_name");
    }


    public function dropIndex($table, $name)
    {
        $db = $this->db_instance;

        $name = $this->getIndexName($name);
        $name = $db->quoteIdentifier($this->getDBInstance()->constraintName($table, $name), true);

        return $db->manipulate("DROP INDEX $name");
    }


    protected function fixIndexName($idx)
    {
        $idx_pattern = '/^' . preg_replace('/%s/', '([a-z0-9_]+)', ilDBPdoFieldDefinition::INDEX_FORMAT) . '$/i';
        $idx_name = preg_replace($idx_pattern, '\\1', $idx);
        if ($idx_name && !strcasecmp($idx, $this->db_instance->getIndexName($idx_name))) {
            return $idx_name;
        }

        return $idx;
    }


    public function listSequences($database = null)
    {
        $db = $this->db_instance;

        $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
        $query .= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
        $table_names = $db->queryCol($query);

        $result = array();
        foreach ($table_names as $table_name) {
            $result[] = $this->fixSequenceName($table_name);
        }
        if ($db->options['portability']) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        sort($result); // FSX patch

        return $result;
    }


    public function dropConstraint($table, $name, $primary = false)
    {
        $table_quoted = $this->getDBInstance()->quoteIdentifier($table, true);
        $name = $this->getDBInstance()->quoteIdentifier($table . '_' . $this->getDBInstance()->getIndexName($name), true);

        return $this->pdo->exec("ALTER TABLE $table_quoted DROP CONSTRAINT $name");
    }
}