IndexTrait::alreadyClustered() - Code Metrics - Inspection of "testing new scrutinizer" - HuasoFoundries/phpPgAdmin6 - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( c2019a...2cab77 )

by Felipe

created 2018-06-19 16:14 UTC

IndexTrait::alreadyClustered() A

↳ Parent: IndexTrait

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	17
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	8
nc	1
nop	1

<?php

/**
 * PHPPgAdmin v6.0.0-beta.48
 */

namespace PHPPgAdmin\Database\Traits;

/**
 * Common trait for indexes and constraints manipulation.
 */
trait IndexTrait
{
    /**
     * Test if a table has been clustered on an index.
     *
     * @param string $table The table to test
     *
     * @return bool true if the table has been already clustered
     */
    public function alreadyClustered($table)
    {
        $c_schema = $this->_schema;
        $this->clean($c_schema);
        $this->clean($table);

        $sql = "SELECT i.indisclustered
			FROM pg_catalog.pg_class c, pg_catalog.pg_index i
			WHERE c.relname = '{$table}'
				AND c.oid = i.indrelid AND i.indisclustered
				AND c.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
					WHERE nspname='{$c_schema}')
				";

        $v = $this->selectSet($sql);

        return !($v->RecordCount() == 0);
    }

    /**
     * Creates an index.
     *
     * @param string       $name         The index name (can be blank)
     * @param string       $table        The table on which to add the index
     * @param array|string $columns      An array of columns that form the index  or a string expression for a functional index
     * @param string       $type         The index type
     * @param bool         $unique       True if unique, false otherwise
     * @param string       $where        Index predicate ('' for none)
     * @param string       $tablespace   The tablespaces ('' means none/default)
     * @param bool         $concurrently true to create index concurrently
     *
     * @return array status (0 if operation was successful) and sql sentence
     */
    public function createIndex($name, $table, $columns, $type, $unique, $where, $tablespace, $concurrently)
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($name);
        $this->fieldClean($table);

        $sql = 'CREATE ';

        $sql .= $unique ? ' UNIQUE ' : '';

        $sql .= ' INDEX ';

        $sql .= $concurrently ? ' CONCURRENTLY ' : '';

        $sql .= $name ? "  \"{$name}\" " : '';

        $sql .= " ON \"{$f_schema}\".\"{$table}\" USING {$type} ";

        if (is_array($columns)) {
            $this->arrayClean($columns);
            $sql .= '("'.implode('","', $columns).'")';
        } else {
            $sql .= '('.$columns.')';
        }

        // Tablespace
        if ($this->hasTablespaces() && $tablespace != '') {
            $this->fieldClean($tablespace);
            $sql .= " TABLESPACE \"{$tablespace}\"";
        }

        // Predicate
        if (trim($where) != '') {
            $sql .= " WHERE ({$where})";
        }

        $status = $this->execute($sql);

        return [$status, $sql];
    }

    /**
     * Removes an index from the database.
     *
     * @param string $index   The index to drop
     * @param bool   $cascade True to cascade drop, false to restrict
     *
     * @return array<integer,mixed|string> 0 if operation was successful
     */
    public function dropIndex($index, $cascade)
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($index);

        $sql = "DROP INDEX \"{$f_schema}\".\"{$index}\"";
        if ($cascade) {
            $sql .= ' CASCADE';
        }

        $status = $this->execute($sql);

        return [$status, $sql];
    }

    /**
     * Rebuild indexes.
     *
     * @param string $type  'DATABASE' or 'TABLE' or 'INDEX'
     * @param string $name  The name of the specific database, table, or index to be reindexed
     * @param bool   $force If true, recreates indexes forcedly in PostgreSQL 7.0-7.1, forces rebuild of system indexes in
     *                      7.2-7.3, ignored in >=7.4
     *
     * @return int 0 if operation was successful
     */
    public function reindex($type, $name, $force = false)
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($name);
        switch ($type) {
            case 'DATABASE':
                $sql = "REINDEX {$type} \"{$name}\"";
                if ($force) {
                    $sql .= ' FORCE';
                }

                break;
            case 'TABLE':
            case 'INDEX':
                $sql = "REINDEX {$type} \"{$f_schema}\".\"{$name}\"";
                if ($force) {
                    $sql .= ' FORCE';
                }

                break;
            default:
                return -1;
        }

        return $this->execute($sql);
    }

    /**
     * Clusters an index.
     *
     * @param string $table The table the index is on
     * @param string $index The name of the index
     *
     * @return array<integer,mixed|string> 0 if operation was successful
     */
    public function clusterIndex($table = '', $index = '')
    {
        $sql = 'CLUSTER';

        // We don't bother with a transaction here, as there's no point rolling
        // back an expensive cluster if a cheap analyze fails for whatever reason

        if (!empty($table)) {
            $f_schema = $this->_schema;
            $this->fieldClean($f_schema);
            $this->fieldClean($table);
            $sql .= " \"{$f_schema}\".\"{$table}\"";

            if (!empty($index)) {
                $this->fieldClean($index);
                $sql .= " USING \"{$index}\"";
            }
        }

        $status = $this->execute($sql);

        return [$status, $sql];
    }

    /**
     * Returns a list of all constraints on a table,
     * including constraint name, definition, related col and referenced namespace,
     * table and col if needed.
     *
     * @param string $table the table where we are looking for fk
     *
     * @return \PHPPgAdmin\ADORecordSet A recordset
     */
    public function getConstraintsWithFields($table)
    {
        $c_schema = $this->_schema;
        $this->clean($c_schema);
        $this->clean($table);

        // get the max number of col used in a constraint for the table
        $sql = "SELECT DISTINCT
			max(SUBSTRING(array_dims(c.conkey) FROM  \$patern\$^\\[.*:(.*)\\]$\$patern\$)) as nb
		FROM pg_catalog.pg_constraint AS c
			JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid)
			JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid)
		WHERE
			r.relname = '{$table}' AND ns.nspname='{$c_schema}'";

        $rs = $this->selectSet($sql);

        if ($rs->EOF) {
            $max_col = 0;
        } else {
            $max_col = $rs->fields['nb'];
        }

        $sql = '
			SELECT
				c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc,
				ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema,
				r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field,
				f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment,
				c.conrelid, c.confrelid
			FROM
				pg_catalog.pg_constraint AS c
				JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid)
				JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]';
        for ($i = 2; $i <= $rs->fields['nb']; ++$i) {
            $sql .= " OR f1.attnum=c.conkey[${i}]";
        }
        $sql .= '))
				JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid
				LEFT JOIN (
					pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid)
				) ON (c.confrelid=r2.oid)
				LEFT JOIN pg_catalog.pg_attribute AS f2 ON
					(f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)';
        for ($i = 2; $i <= $rs->fields['nb']; ++$i) {
            $sql .= " OR (c.confkey[${i}]=f2.attnum AND c.conkey[${i}]=f1.attnum)";
        }

        $sql .= sprintf("))
			WHERE
				r1.relname = '%s' AND ns1.nspname='%s'
			ORDER BY 1", $table, $c_schema);

        return $this->selectSet($sql);
    }

    /**
     * Adds a primary key constraint to a table.
     *
     * @param string $table      The table to which to add the primery key
     * @param array  $fields     (array) An array of fields over which to add the primary key
     * @param string $name       (optional) The name to give the key, otherwise default name is assigned
     * @param string $tablespace (optional) The tablespace for the schema, '' indicates default
     *
     * @return int 0 if operation was successful
     */
    public function addPrimaryKey($table, $fields, $name = '', $tablespace = '')
    {
        if (!is_array($fields) || sizeof($fields) == 0) {
            return -1;
        }

        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($table);
        $this->fieldArrayClean($fields);
        $this->fieldClean($name);
        $this->fieldClean($tablespace);

        $sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
        if ($name != '') {
            $sql .= "CONSTRAINT \"{$name}\" ";
        }

        $sql .= 'PRIMARY KEY ("'.join('","', $fields).'")';

        if ($tablespace != '' && $this->hasTablespaces()) {
            $sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
        }

        return $this->execute($sql);
    }

    /**
     * Adds a unique constraint to a table.
     *
     * @param string      $table      The table to which to add the unique key
     * @param array|mixed $fields     (array) An array of fields over which to add the unique key
     * @param string      $name       (optional) The name to give the key, otherwise default name is assigned
     * @param string      $tablespace (optional) The tablespace for the schema, '' indicates default
     *
     * @return int 0 if operation was successful
     */
    public function addUniqueKey($table, $fields, $name = '', $tablespace = '')
    {
        if (!is_array($fields) || sizeof($fields) == 0) {
            return -1;
        }

        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($table);
        $this->fieldArrayClean($fields);
        $this->fieldClean($name);
        $this->fieldClean($tablespace);

        $sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
        if ($name != '') {
            $sql .= "CONSTRAINT \"{$name}\" ";
        }

        $sql .= 'UNIQUE ("'.join('","', $fields).'")';

        if ($tablespace != '' && $this->hasTablespaces()) {
            $sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
        }

        return $this->execute($sql);
    }

    // Function functions

    /**
     * Adds a check constraint to a table.
     *
     * @param string $table      The table to which to add the check
     * @param string $definition The definition of the check
     * @param string $name       (optional) The name to give the check, otherwise default name is assigned
     *
     * @return int 0 if operation was successful
     */
    public function addCheckConstraint($table, $definition, $name = '')
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($table);
        $this->fieldClean($name);
        // @@ How the heck do you clean a definition???

        $sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
        if ($name != '') {
            $sql .= "CONSTRAINT \"{$name}\" ";
        }

        $sql .= "CHECK ({$definition})";

        return $this->execute($sql);
    }

    /**
     * Drops a check constraint from a table.
     *
     * @param string $table The table from which to drop the check
     * @param string $name  The name of the check to be dropped
     *
     * @return bool|int 0 success
     */
    public function dropCheckConstraint($table, $name)
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $c_schema = $this->_schema;
        $this->clean($c_schema);
        $c_table = $table;
        $this->fieldClean($table);
        $this->clean($c_table);
        $this->clean($name);

        // Begin transaction
        $status = $this->beginTransaction();
        if ($status != 0) {
            return -2;
        }

        // Properly lock the table
        $sql    = "LOCK TABLE \"{$f_schema}\".\"{$table}\" IN ACCESS EXCLUSIVE MODE";
        $status = $this->execute($sql);
        if ($status != 0) {
            $this->rollbackTransaction();

            return -3;
        }

        // Delete the check constraint
        $sql = "DELETE FROM pg_relcheck WHERE rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$c_table}'
			AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
			nspname = '{$c_schema}')) AND rcname='{$name}'";
        $status = $this->execute($sql);
        if ($status != 0) {
            $this->rollbackTransaction();

            return -4;
        }

        // Update the pg_class catalog to reflect the new number of checks
        $sql = "UPDATE pg_class SET relchecks=(SELECT COUNT(*) FROM pg_relcheck WHERE
					rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$c_table}'
						AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
						nspname = '{$c_schema}')))
					WHERE relname='{$c_table}'";
        $status = $this->execute($sql);
        if ($status != 0) {
            $this->rollbackTransaction();

            return -4;
        }

        // Otherwise, close the transaction
        return $this->endTransaction();
    }

    /**
     * Adds a foreign key constraint to a table.
     *
     * @param string $table      The table on which to add an FK
     * @param string $targschema The schema that houses the target table to which to add the foreign key
     * @param string $targtable  The table to which to add the foreign key
     * @param array  $sfields    (array) An array of source fields over which to add the foreign key
     * @param array  $tfields    (array) An array of target fields over which to add the foreign key
     * @param string $upd_action The action for updates (eg. RESTRICT)
     * @param string $del_action The action for deletes (eg. RESTRICT)
     * @param string $match      The match type (eg. MATCH FULL)
     * @param string $deferrable The deferrability (eg. NOT DEFERRABLE)
     * @param string $initially  The initially parameter for the FK (eg. INITIALLY IMMEDIATE)
     * @param string $name       [optional] The name to give the key, otherwise default name is assigned
     *
     * @return int 0 if operation was successful
     *
     * @internal param \PHPPgAdmin\Database\The $target table that contains the target columns
     * @internal param \PHPPgAdmin\Database\The $intially initial deferrability (eg. INITIALLY IMMEDIATE)
     */
    public function addForeignKey(
        $table,
        $targschema,
        $targtable,
        $sfields,
        $tfields,
        $upd_action,
        $del_action,
        $match,
        $deferrable,
        $initially,
        $name = ''
    ) {
        if (!is_array($sfields) || sizeof($sfields) == 0 ||
            !is_array($tfields) || sizeof($tfields) == 0) {
            return -1;
        }

        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($table);
        $this->fieldClean($targschema);
        $this->fieldClean($targtable);
        $this->fieldArrayClean($sfields);
        $this->fieldArrayClean($tfields);
        $this->fieldClean($name);

        $sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
        if ($name != '') {
            $sql .= "CONSTRAINT \"{$name}\" ";
        }

        $sql .= 'FOREIGN KEY ("'.join('","', $sfields).'") ';
        // Target table needs to be fully qualified
        $sql .= "REFERENCES \"{$targschema}\".\"{$targtable}\"(\"".join('","', $tfields).'") ';
        if ($match != $this->fkmatches[0]) {
            $sql .= " {$match}";
        }

        if ($upd_action != $this->fkactions[0]) {
            $sql .= " ON UPDATE {$upd_action}";
        }

        if ($del_action != $this->fkactions[0]) {
            $sql .= " ON DELETE {$del_action}";
        }

        if ($deferrable != $this->fkdeferrable[0]) {
            $sql .= " {$deferrable}";
        }

        if ($initially != $this->fkinitial[0]) {
            $sql .= " {$initially}";
        }

        return $this->execute($sql);
    }

    /**
     * Removes a constraint from a relation.
     *
     * @param string $constraint The constraint to drop
     * @param string $relation   The relation from which to drop
     * @param string $type       The type of constraint (c, f, u or p)
     * @param bool   $cascade    True to cascade drop, false to restrict
     *
     * @return int 0 if operation was successful
     */
    public function dropConstraint($constraint, $relation, $type, $cascade)
    {
        $f_schema = $this->_schema;
        $this->fieldClean($f_schema);
        $this->fieldClean($constraint);
        $this->fieldClean($relation);

        $sql = "ALTER TABLE \"{$f_schema}\".\"{$relation}\" DROP CONSTRAINT \"{$constraint}\"";
        if ($cascade) {
            $sql .= ' CASCADE';
        }

        return $this->execute($sql);
    }

    /**
     * A function for getting all columns linked by foreign keys given a group of tables.
     *
     * @param array $tables multi dimensional assoc array that holds schema and table name
     *
     * @return int|\PHPPgAdmin\ADORecordSet recordset of linked tables and columns or -1 if $tables isn't an array
     */
    public function getLinkingKeys($tables)
    {
        if (!is_array($tables)) {
            return -1;
        }

        $this->clean($tables[0]['tablename']);
        $this->clean($tables[0]['schemaname']);
        $tables_list        = "'{$tables[0]['tablename']}'";
        $schema_list        = "'{$tables[0]['schemaname']}'";
        $schema_tables_list = "'{$tables[0]['schemaname']}.{$tables[0]['tablename']}'";
        $tablescount        = sizeof($tables);

        for ($i = 1; $i < $tablescount; ++$i) {
            $this->clean($tables[$i]['tablename']);
            $this->clean($tables[$i]['schemaname']);
            $tables_list .= ", '{$tables[$i]['tablename']}'";
            $schema_list .= ", '{$tables[$i]['schemaname']}'";
            $schema_tables_list .= ", '{$tables[$i]['schemaname']}.{$tables[$i]['tablename']}'";
        }

        $maxDimension = 1;

        $sql = "
			SELECT DISTINCT
				array_dims(pc.conkey) AS arr_dim,
				pgc1.relname AS p_table
			FROM
				pg_catalog.pg_constraint AS pc,
				pg_catalog.pg_class AS pgc1
			WHERE
				pc.contype = 'f'
				AND (pc.conrelid = pgc1.relfilenode OR pc.confrelid = pgc1.relfilenode)
				AND pgc1.relname IN (${tables_list})
			";

        //parse our output to find the highest dimension of foreign keys since pc.conkey is stored in an array
        $rs = $this->selectSet($sql);
        while (!$rs->EOF) {
            $arrData      = explode(':', $rs->fields['arr_dim']);
            $strdimension = trim(substr($arrData[1], 0, strlen($arrData[1]) - 1));
            $tmpDimension = (int) $strdimension;
            $maxDimension = $tmpDimension > $maxDimension ? $tmpDimension : $maxDimension;
            $rs->MoveNext();
        }

        //we know the highest index for foreign keys that conkey goes up to, expand for us in an IN query
        $cons_str = '( (pfield.attnum = conkey[1] AND cfield.attnum = confkey[1]) ';
        for ($i = 2; $i <= $maxDimension; ++$i) {
            $cons_str .= "OR (pfield.attnum = conkey[{$i}] AND cfield.attnum = confkey[{$i}]) ";
        }
        $cons_str .= ') ';

        $sql = "
			SELECT
				pgc1.relname AS p_table,
				pgc2.relname AS f_table,
				pfield.attname AS p_field,
				cfield.attname AS f_field,
				pgns1.nspname AS p_schema,
				pgns2.nspname AS f_schema
			FROM
				pg_catalog.pg_constraint AS pc,
				pg_catalog.pg_class AS pgc1,
				pg_catalog.pg_class AS pgc2,
				pg_catalog.pg_attribute AS pfield,
				pg_catalog.pg_attribute AS cfield,
				(SELECT oid AS ns_id, nspname FROM pg_catalog.pg_namespace WHERE nspname IN (${schema_list}) ) AS pgns1,
 				(SELECT oid AS ns_id, nspname FROM pg_catalog.pg_namespace WHERE nspname IN (${schema_list}) ) AS pgns2
			WHERE
				pc.contype = 'f'
				AND pgc1.relnamespace = pgns1.ns_id
 				AND pgc2.relnamespace = pgns2.ns_id
				AND pc.conrelid = pgc1.relfilenode
				AND pc.confrelid = pgc2.relfilenode
				AND pfield.attrelid = pc.conrelid
				AND cfield.attrelid = pc.confrelid
				AND ${cons_str}
				AND pgns1.nspname || '.' || pgc1.relname IN (${schema_tables_list})
				AND pgns2.nspname || '.' || pgc2.relname IN (${schema_tables_list})
		";

        return $this->selectSet($sql);
    }

    /**
     * Finds the foreign keys that refer to the specified table.
     *
     * @param string $table The table to find referrers for
     *
     * @return int|\PHPPgAdmin\ADORecordSet A recordset or -1 in case of error
     */
    public function getReferrers($table)
    {
        $this->clean($table);

        $status = $this->beginTransaction();
        if ($status != 0) {
            return -1;
        }

        $c_schema = $this->_schema;
        $this->clean($c_schema);

        $sql = "
			SELECT
				pn.nspname,
				pl.relname,
				pc.conname,
				pg_catalog.pg_get_constraintdef(pc.oid) AS consrc
			FROM
				pg_catalog.pg_constraint pc,
				pg_catalog.pg_namespace pn,
				pg_catalog.pg_class pl
			WHERE
				pc.connamespace = pn.oid
				AND pc.conrelid = pl.oid
				AND pc.contype = 'f'
				AND confrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}'
					AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
					WHERE nspname='{$c_schema}'))
			ORDER BY 1,2,3
		";

        return $this->selectSet($sql);
    }

    abstract public function fieldClean(&$str);

    abstract public function beginTransaction();

    abstract public function rollbackTransaction();

    abstract public function endTransaction();

    abstract public function execute($sql);

    abstract public function setComment($obj_type, $obj_name, $table, $comment, $basetype = null);

    abstract public function selectSet($sql);

    abstract public function clean(&$str);

    abstract public function hasTablespaces();

    abstract public function arrayClean(&$arr);

    abstract public function fieldArrayClean(&$arr);
}


1			<?php
2
3			/**
4			* PHPPgAdmin v6.0.0-beta.48
5			*/
6
7			namespace PHPPgAdmin\Database\Traits;
8
9			/**
10			* Common trait for indexes and constraints manipulation.
11			*/
12			trait IndexTrait
13			{
14			/**
15			* Test if a table has been clustered on an index.
16			*
17			* @param string $table The table to test
18			*
19			* @return bool true if the table has been already clustered
20			*/
21			public function alreadyClustered($table)
22			{
23			$c_schema = $this->_schema;
24			$this->clean($c_schema);
25			$this->clean($table);
26
27			$sql = "SELECT i.indisclustered
28			FROM pg_catalog.pg_class c, pg_catalog.pg_index i
29			WHERE c.relname = '{$table}'
30			AND c.oid = i.indrelid AND i.indisclustered
31			AND c.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
32			WHERE nspname='{$c_schema}')
33			";
34
35			$v = $this->selectSet($sql);
36
37			return !($v->RecordCount() == 0);
38			}
39
40			/**
41			* Creates an index.
42			*
43			* @param string $name The index name (can be blank)
44			* @param string $table The table on which to add the index
45			* @param array\|string $columns An array of columns that form the index or a string expression for a functional index
46			* @param string $type The index type
47			* @param bool $unique True if unique, false otherwise
48			* @param string $where Index predicate ('' for none)
49			* @param string $tablespace The tablespaces ('' means none/default)
50			* @param bool $concurrently true to create index concurrently
51			*
52			* @return array status (0 if operation was successful) and sql sentence
53			*/
54			public function createIndex($name, $table, $columns, $type, $unique, $where, $tablespace, $concurrently)
55			{
56			$f_schema = $this->_schema;
57			$this->fieldClean($f_schema);
58			$this->fieldClean($name);
59			$this->fieldClean($table);
60
61			$sql = 'CREATE ';
62
63			$sql .= $unique ? ' UNIQUE ' : '';
64
65			$sql .= ' INDEX ';
66
67			$sql .= $concurrently ? ' CONCURRENTLY ' : '';
68
69			$sql .= $name ? " \"{$name}\" " : '';
70
71			$sql .= " ON \"{$f_schema}\".\"{$table}\" USING {$type} ";
72
73			if (is_array($columns)) {
74			$this->arrayClean($columns);
75			$sql .= '("'.implode('","', $columns).'")';
76			} else {
77			$sql .= '('.$columns.')';
78			}
79
80			// Tablespace
81			if ($this->hasTablespaces() && $tablespace != '') {
82			$this->fieldClean($tablespace);
83			$sql .= " TABLESPACE \"{$tablespace}\"";
84			}
85
86			// Predicate
87			if (trim($where) != '') {
88			$sql .= " WHERE ({$where})";
89			}
90
91			$status = $this->execute($sql);
92
93			return [$status, $sql];
94			}
95
96			/**
97			* Removes an index from the database.
98			*
99			* @param string $index The index to drop
100			* @param bool $cascade True to cascade drop, false to restrict
101			*
102			* @return array<integer,mixed\|string> 0 if operation was successful
103			*/
104			public function dropIndex($index, $cascade)
105			{
106			$f_schema = $this->_schema;
107			$this->fieldClean($f_schema);
108			$this->fieldClean($index);
109
110			$sql = "DROP INDEX \"{$f_schema}\".\"{$index}\"";
111			if ($cascade) {
112			$sql .= ' CASCADE';
113			}
114
115			$status = $this->execute($sql);
116
117			return [$status, $sql];
118			}
119
120			/**
121			* Rebuild indexes.
122			*
123			* @param string $type 'DATABASE' or 'TABLE' or 'INDEX'
124			* @param string $name The name of the specific database, table, or index to be reindexed
125			* @param bool $force If true, recreates indexes forcedly in PostgreSQL 7.0-7.1, forces rebuild of system indexes in
126			* 7.2-7.3, ignored in >=7.4
127			*
128			* @return int 0 if operation was successful
129			*/
130			public function reindex($type, $name, $force = false)
131			{
132			$f_schema = $this->_schema;
133			$this->fieldClean($f_schema);
134			$this->fieldClean($name);
135			switch ($type) {
136			case 'DATABASE':
137			$sql = "REINDEX {$type} \"{$name}\"";
138			if ($force) {
139			$sql .= ' FORCE';
140			}
141
142			break;
143			case 'TABLE':
144			case 'INDEX':
145			$sql = "REINDEX {$type} \"{$f_schema}\".\"{$name}\"";
146			if ($force) {
147			$sql .= ' FORCE';
148			}
149
150			break;
151			default:
152			return -1;
153			}
154
155			return $this->execute($sql);
156			}
157
158			/**
159			* Clusters an index.
160			*
161			* @param string $table The table the index is on
162			* @param string $index The name of the index
163			*
164			* @return array<integer,mixed\|string> 0 if operation was successful
165			*/
166			public function clusterIndex($table = '', $index = '')
167			{
168			$sql = 'CLUSTER';
169
170			// We don't bother with a transaction here, as there's no point rolling
171			// back an expensive cluster if a cheap analyze fails for whatever reason
172
173			if (!empty($table)) {
174			$f_schema = $this->_schema;
175			$this->fieldClean($f_schema);
176			$this->fieldClean($table);
177			$sql .= " \"{$f_schema}\".\"{$table}\"";
178
179			if (!empty($index)) {
180			$this->fieldClean($index);
181			$sql .= " USING \"{$index}\"";
182			}
183			}
184
185			$status = $this->execute($sql);
186
187			return [$status, $sql];
188			}
189
190			/**
191			* Returns a list of all constraints on a table,
192			* including constraint name, definition, related col and referenced namespace,
193			* table and col if needed.
194			*
195			* @param string $table the table where we are looking for fk
196			*
197			* @return \PHPPgAdmin\ADORecordSet A recordset
198			*/
199			public function getConstraintsWithFields($table)
200			{
201			$c_schema = $this->_schema;
202			$this->clean($c_schema);
203			$this->clean($table);
204
205			// get the max number of col used in a constraint for the table
206			$sql = "SELECT DISTINCT
207			max(SUBSTRING(array_dims(c.conkey) FROM \$patern\$^\\[.:(.)\\]$\$patern\$)) as nb
208			FROM pg_catalog.pg_constraint AS c
209			JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid)
210			JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid)
211			WHERE
212			r.relname = '{$table}' AND ns.nspname='{$c_schema}'";
213
214			$rs = $this->selectSet($sql);
215
216			if ($rs->EOF) {
217			$max_col = 0;
218			} else {
219			$max_col = $rs->fields['nb'];
220			}
221
222			$sql = '
223			SELECT
224			c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc,
225			ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema,
226			r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field,
227			f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment,
228			c.conrelid, c.confrelid
229			FROM
230			pg_catalog.pg_constraint AS c
231			JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid)
232			JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]';
233			for ($i = 2; $i <= $rs->fields['nb']; ++$i) {
234			$sql .= " OR f1.attnum=c.conkey[${i}]";
235			}
236			$sql .= '))
237			JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid
238			LEFT JOIN (
239			pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid)
240			) ON (c.confrelid=r2.oid)
241			LEFT JOIN pg_catalog.pg_attribute AS f2 ON
242			(f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)';
243			for ($i = 2; $i <= $rs->fields['nb']; ++$i) {
244			$sql .= " OR (c.confkey[${i}]=f2.attnum AND c.conkey[${i}]=f1.attnum)";
245			}
246
247			$sql .= sprintf("))
248			WHERE
249			r1.relname = '%s' AND ns1.nspname='%s'
250			ORDER BY 1", $table, $c_schema);
251
252			return $this->selectSet($sql);
253			}
254
255			/**
256			* Adds a primary key constraint to a table.
257			*
258			* @param string $table The table to which to add the primery key
259			* @param array $fields (array) An array of fields over which to add the primary key
260			* @param string $name (optional) The name to give the key, otherwise default name is assigned
261			* @param string $tablespace (optional) The tablespace for the schema, '' indicates default
262			*
263			* @return int 0 if operation was successful
264			*/
265			public function addPrimaryKey($table, $fields, $name = '', $tablespace = '')
266			{
267			if (!is_array($fields) \|\| sizeof($fields) == 0) {
268			return -1;
269			}
270
271			$f_schema = $this->_schema;
272			$this->fieldClean($f_schema);
273			$this->fieldClean($table);
274			$this->fieldArrayClean($fields);
275			$this->fieldClean($name);
276			$this->fieldClean($tablespace);
277
278			$sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
279			if ($name != '') {
280			$sql .= "CONSTRAINT \"{$name}\" ";
281			}
282
283			$sql .= 'PRIMARY KEY ("'.join('","', $fields).'")';
284
285			if ($tablespace != '' && $this->hasTablespaces()) {
286			$sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
287			}
288
289			return $this->execute($sql);
290			}
291
292			/**
293			* Adds a unique constraint to a table.
294			*
295			* @param string $table The table to which to add the unique key
296			* @param array\|mixed $fields (array) An array of fields over which to add the unique key
297			* @param string $name (optional) The name to give the key, otherwise default name is assigned
298			* @param string $tablespace (optional) The tablespace for the schema, '' indicates default
299			*
300			* @return int 0 if operation was successful
301			*/
302			public function addUniqueKey($table, $fields, $name = '', $tablespace = '')
303			{
304			if (!is_array($fields) \|\| sizeof($fields) == 0) {
305			return -1;
306			}
307
308			$f_schema = $this->_schema;
309			$this->fieldClean($f_schema);
310			$this->fieldClean($table);
311			$this->fieldArrayClean($fields);
312			$this->fieldClean($name);
313			$this->fieldClean($tablespace);
314
315			$sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
316			if ($name != '') {
317			$sql .= "CONSTRAINT \"{$name}\" ";
318			}
319
320			$sql .= 'UNIQUE ("'.join('","', $fields).'")';
321
322			if ($tablespace != '' && $this->hasTablespaces()) {
323			$sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
324			}
325
326			return $this->execute($sql);
327			}
328
329			// Function functions
330
331			/**
332			* Adds a check constraint to a table.
333			*
334			* @param string $table The table to which to add the check
335			* @param string $definition The definition of the check
336			* @param string $name (optional) The name to give the check, otherwise default name is assigned
337			*
338			* @return int 0 if operation was successful
339			*/
340			public function addCheckConstraint($table, $definition, $name = '')
341			{
342			$f_schema = $this->_schema;
343			$this->fieldClean($f_schema);
344			$this->fieldClean($table);
345			$this->fieldClean($name);
346			// @@ How the heck do you clean a definition???
347
348			$sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
349			if ($name != '') {
350			$sql .= "CONSTRAINT \"{$name}\" ";
351			}
352
353			$sql .= "CHECK ({$definition})";
354
355			return $this->execute($sql);
356			}
357
358			/**
359			* Drops a check constraint from a table.
360			*
361			* @param string $table The table from which to drop the check
362			* @param string $name The name of the check to be dropped
363			*
364			* @return bool\|int 0 success
365			*/
366			public function dropCheckConstraint($table, $name)
367			{
368			$f_schema = $this->_schema;
369			$this->fieldClean($f_schema);
370			$c_schema = $this->_schema;
371			$this->clean($c_schema);
372			$c_table = $table;
373			$this->fieldClean($table);
374			$this->clean($c_table);
375			$this->clean($name);
376
377			// Begin transaction
378			$status = $this->beginTransaction();
379			if ($status != 0) {
380			return -2;
381			}
382
383			// Properly lock the table
384			$sql = "LOCK TABLE \"{$f_schema}\".\"{$table}\" IN ACCESS EXCLUSIVE MODE";
385			$status = $this->execute($sql);
386			if ($status != 0) {
387			$this->rollbackTransaction();
388
389			return -3;
390			}
391
392			// Delete the check constraint
393			$sql = "DELETE FROM pg_relcheck WHERE rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$c_table}'
394			AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
395			nspname = '{$c_schema}')) AND rcname='{$name}'";
396			$status = $this->execute($sql);
397			if ($status != 0) {
398			$this->rollbackTransaction();
399
400			return -4;
401			}
402
403			// Update the pg_class catalog to reflect the new number of checks
404			$sql = "UPDATE pg_class SET relchecks=(SELECT COUNT(*) FROM pg_relcheck WHERE
405			rcrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$c_table}'
406			AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
407			nspname = '{$c_schema}')))
408			WHERE relname='{$c_table}'";
409			$status = $this->execute($sql);
410			if ($status != 0) {
411			$this->rollbackTransaction();
412
413			return -4;
414			}
415
416			// Otherwise, close the transaction
417			return $this->endTransaction();
418			}
419
420			/**
421			* Adds a foreign key constraint to a table.
422			*
423			* @param string $table The table on which to add an FK
424			* @param string $targschema The schema that houses the target table to which to add the foreign key
425			* @param string $targtable The table to which to add the foreign key
426			* @param array $sfields (array) An array of source fields over which to add the foreign key
427			* @param array $tfields (array) An array of target fields over which to add the foreign key
428			* @param string $upd_action The action for updates (eg. RESTRICT)
429			* @param string $del_action The action for deletes (eg. RESTRICT)
430			* @param string $match The match type (eg. MATCH FULL)
431			* @param string $deferrable The deferrability (eg. NOT DEFERRABLE)
432			* @param string $initially The initially parameter for the FK (eg. INITIALLY IMMEDIATE)
433			* @param string $name [optional] The name to give the key, otherwise default name is assigned
434			*
435			* @return int 0 if operation was successful
436			*
437			* @internal param \PHPPgAdmin\Database\The $target table that contains the target columns
438			* @internal param \PHPPgAdmin\Database\The $intially initial deferrability (eg. INITIALLY IMMEDIATE)
439			*/
440			public function addForeignKey(
441			$table,
442			$targschema,
443			$targtable,
444			$sfields,
445			$tfields,
446			$upd_action,
447			$del_action,
448			$match,
449			$deferrable,
450			$initially,
451			$name = ''
452			) {
453			if (!is_array($sfields) \|\| sizeof($sfields) == 0 \|\|
454			!is_array($tfields) \|\| sizeof($tfields) == 0) {
455			return -1;
456			}
457
458			$f_schema = $this->_schema;
459			$this->fieldClean($f_schema);
460			$this->fieldClean($table);
461			$this->fieldClean($targschema);
462			$this->fieldClean($targtable);
463			$this->fieldArrayClean($sfields);
464			$this->fieldArrayClean($tfields);
465			$this->fieldClean($name);
466
467			$sql = "ALTER TABLE \"{$f_schema}\".\"{$table}\" ADD ";
468			if ($name != '') {
469			$sql .= "CONSTRAINT \"{$name}\" ";
470			}
471
472			$sql .= 'FOREIGN KEY ("'.join('","', $sfields).'") ';
473			// Target table needs to be fully qualified
474			$sql .= "REFERENCES \"{$targschema}\".\"{$targtable}\"(\"".join('","', $tfields).'") ';
475			if ($match != $this->fkmatches[0]) {
476			$sql .= " {$match}";
477			}
478
479			if ($upd_action != $this->fkactions[0]) {
480			$sql .= " ON UPDATE {$upd_action}";
481			}
482
483			if ($del_action != $this->fkactions[0]) {
484			$sql .= " ON DELETE {$del_action}";
485			}
486
487			if ($deferrable != $this->fkdeferrable[0]) {
488			$sql .= " {$deferrable}";
489			}
490
491			if ($initially != $this->fkinitial[0]) {
492			$sql .= " {$initially}";
493			}
494
495			return $this->execute($sql);
496			}
497
498			/**
499			* Removes a constraint from a relation.
500			*
501			* @param string $constraint The constraint to drop
502			* @param string $relation The relation from which to drop
503			* @param string $type The type of constraint (c, f, u or p)
504			* @param bool $cascade True to cascade drop, false to restrict
505			*
506			* @return int 0 if operation was successful
507			*/
508			public function dropConstraint($constraint, $relation, $type, $cascade)
509			{
510			$f_schema = $this->_schema;
511			$this->fieldClean($f_schema);
512			$this->fieldClean($constraint);
513			$this->fieldClean($relation);
514
515			$sql = "ALTER TABLE \"{$f_schema}\".\"{$relation}\" DROP CONSTRAINT \"{$constraint}\"";
516			if ($cascade) {
517			$sql .= ' CASCADE';
518			}
519
520			return $this->execute($sql);
521			}
522
523			/**
524			* A function for getting all columns linked by foreign keys given a group of tables.
525			*
526			* @param array $tables multi dimensional assoc array that holds schema and table name
527			*
528			* @return int\|\PHPPgAdmin\ADORecordSet recordset of linked tables and columns or -1 if $tables isn't an array
529			*/
530			public function getLinkingKeys($tables)
531			{
532			if (!is_array($tables)) {
533			return -1;
534			}
535
536			$this->clean($tables[0]['tablename']);
537			$this->clean($tables[0]['schemaname']);
538			$tables_list = "'{$tables[0]['tablename']}'";
539			$schema_list = "'{$tables[0]['schemaname']}'";
540			$schema_tables_list = "'{$tables[0]['schemaname']}.{$tables[0]['tablename']}'";
541			$tablescount = sizeof($tables);
542
543			for ($i = 1; $i < $tablescount; ++$i) {
544			$this->clean($tables[$i]['tablename']);
545			$this->clean($tables[$i]['schemaname']);
546			$tables_list .= ", '{$tables[$i]['tablename']}'";
547			$schema_list .= ", '{$tables[$i]['schemaname']}'";
548			$schema_tables_list .= ", '{$tables[$i]['schemaname']}.{$tables[$i]['tablename']}'";
549			}
550
551			$maxDimension = 1;
552
553			$sql = "
554			SELECT DISTINCT
555			array_dims(pc.conkey) AS arr_dim,
556			pgc1.relname AS p_table
557			FROM
558			pg_catalog.pg_constraint AS pc,
559			pg_catalog.pg_class AS pgc1
560			WHERE
561			pc.contype = 'f'
562			AND (pc.conrelid = pgc1.relfilenode OR pc.confrelid = pgc1.relfilenode)
563			AND pgc1.relname IN (${tables_list})
564			";
565
566			//parse our output to find the highest dimension of foreign keys since pc.conkey is stored in an array
567			$rs = $this->selectSet($sql);
568			while (!$rs->EOF) {
569			$arrData = explode(':', $rs->fields['arr_dim']);
570			$strdimension = trim(substr($arrData[1], 0, strlen($arrData[1]) - 1));
571			$tmpDimension = (int) $strdimension;
572			$maxDimension = $tmpDimension > $maxDimension ? $tmpDimension : $maxDimension;
573			$rs->MoveNext();
574			}
575
576			//we know the highest index for foreign keys that conkey goes up to, expand for us in an IN query
577			$cons_str = '( (pfield.attnum = conkey[1] AND cfield.attnum = confkey[1]) ';
578			for ($i = 2; $i <= $maxDimension; ++$i) {
579			$cons_str .= "OR (pfield.attnum = conkey[{$i}] AND cfield.attnum = confkey[{$i}]) ";
580			}
581			$cons_str .= ') ';
582
583			$sql = "
584			SELECT
585			pgc1.relname AS p_table,
586			pgc2.relname AS f_table,
587			pfield.attname AS p_field,
588			cfield.attname AS f_field,
589			pgns1.nspname AS p_schema,
590			pgns2.nspname AS f_schema
591			FROM
592			pg_catalog.pg_constraint AS pc,
593			pg_catalog.pg_class AS pgc1,
594			pg_catalog.pg_class AS pgc2,
595			pg_catalog.pg_attribute AS pfield,
596			pg_catalog.pg_attribute AS cfield,
597			(SELECT oid AS ns_id, nspname FROM pg_catalog.pg_namespace WHERE nspname IN (${schema_list}) ) AS pgns1,
598			(SELECT oid AS ns_id, nspname FROM pg_catalog.pg_namespace WHERE nspname IN (${schema_list}) ) AS pgns2
599			WHERE
600			pc.contype = 'f'
601			AND pgc1.relnamespace = pgns1.ns_id
602			AND pgc2.relnamespace = pgns2.ns_id
603			AND pc.conrelid = pgc1.relfilenode
604			AND pc.confrelid = pgc2.relfilenode
605			AND pfield.attrelid = pc.conrelid
606			AND cfield.attrelid = pc.confrelid
607			AND ${cons_str}
608			AND pgns1.nspname \|\| '.' \|\| pgc1.relname IN (${schema_tables_list})
609			AND pgns2.nspname \|\| '.' \|\| pgc2.relname IN (${schema_tables_list})
610			";
611
612			return $this->selectSet($sql);
613			}
614
615			/**
616			* Finds the foreign keys that refer to the specified table.
617			*
618			* @param string $table The table to find referrers for
619			*
620			* @return int\|\PHPPgAdmin\ADORecordSet A recordset or -1 in case of error
621			*/
622			public function getReferrers($table)
623			{
624			$this->clean($table);
625
626			$status = $this->beginTransaction();
627			if ($status != 0) {
628			return -1;
629			}
630
631			$c_schema = $this->_schema;
632			$this->clean($c_schema);
633
634			$sql = "
635			SELECT
636			pn.nspname,
637			pl.relname,
638			pc.conname,
639			pg_catalog.pg_get_constraintdef(pc.oid) AS consrc
640			FROM
641			pg_catalog.pg_constraint pc,
642			pg_catalog.pg_namespace pn,
643			pg_catalog.pg_class pl
644			WHERE
645			pc.connamespace = pn.oid
646			AND pc.conrelid = pl.oid
647			AND pc.contype = 'f'
648			AND confrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}'
649			AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
650			WHERE nspname='{$c_schema}'))
651			ORDER BY 1,2,3
652			";
653
654			return $this->selectSet($sql);
655			}
656
657			abstract public function fieldClean(&$str);
658
659			abstract public function beginTransaction();
660
661			abstract public function rollbackTransaction();
662
663			abstract public function endTransaction();
664
665			abstract public function execute($sql);
666
667			abstract public function setComment($obj_type, $obj_name, $table, $comment, $basetype = null);
668
669			abstract public function selectSet($sql);
670
671			abstract public function clean(&$str);
672
673			abstract public function hasTablespaces();
674
675			abstract public function arrayClean(&$arr);
676
677			abstract public function fieldArrayClean(&$arr);
678			}
679

HuasoFoundries / phpPgAdmin6

Push — develop ( c2019a...2cab77 )

IndexTrait::alreadyClustered() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like