evolution-cms / evolution

     *

     * @todo Where is this used? Which information should be retrieved?

     */

    public function getListTableConstraintsSQL($table)

    {

        $user = '';

        if (strpos($table, '.') !== false) {

            list($user, $table) = explode('.', $table);

            $user = $this->quoteStringLiteral($user);

            $table = $this->quoteStringLiteral($table);

        } else {

            $table = $this->quoteStringLiteral($table);

        }

        return "SELECT con.*

                FROM   SYS.SYSCONSTRAINT AS con

                JOIN   SYS.SYSTAB AS tab ON con.table_object_id = tab.object_id

                WHERE  tab.table_name = $table

                AND    tab.creator = USER_ID($user)";

    }

    /**

     * {@inheritdoc}

    /**

     * {@inheritdoc}

     */

    public function getListTableForeignKeysSQL($table)

    {

        $user = '';

        if (strpos($table, '.') !== false) {

            list($user, $table) = explode('.', $table);

            $user = $this->quoteStringLiteral($user);

            $table = $this->quoteStringLiteral($table);

        } else {

            $table = $this->quoteStringLiteral($table);

        }

        return "SELECT    fcol.column_name AS local_column,

                          ptbl.table_name AS foreign_table,

                          pcol.column_name AS foreign_column,

                          idx.index_name,

                          IF fk.nulls = 'N'

                              THEN 1

                              ELSE NULL

                          ENDIF AS notnull,

                          CASE ut.referential_action

                              WHEN 'C' THEN 'CASCADE'

                              WHEN 'D' THEN 'SET DEFAULT'

                              WHEN 'N' THEN 'SET NULL'

                              WHEN 'R' THEN 'RESTRICT'

                              ELSE NULL

                          END AS  on_update,

                          CASE dt.referential_action

                              WHEN 'C' THEN 'CASCADE'

                              WHEN 'D' THEN 'SET DEFAULT'

                              WHEN 'N' THEN 'SET NULL'

                              WHEN 'R' THEN 'RESTRICT'

                              ELSE NULL

                          END AS on_delete,

                          IF fk.check_on_commit = 'Y'

                              THEN 1

                              ELSE NULL

                          ENDIF AS check_on_commit, -- check_on_commit flag

                          IF ftbl.clustered_index_id = idx.index_id

                              THEN 1

                              ELSE NULL

                          ENDIF AS 'clustered', -- clustered flag

                          IF fk.match_type = 0

                              THEN NULL

                              ELSE fk.match_type

                          ENDIF AS 'match', -- match option

                          IF pidx.max_key_distance = 1

                              THEN 1

                              ELSE NULL

                          ENDIF AS for_olap_workload -- for_olap_workload flag

                FROM      SYS.SYSFKEY AS fk

                JOIN      SYS.SYSIDX AS idx

                ON        fk.foreign_table_id = idx.table_id

                AND       fk.foreign_index_id = idx.index_id

                JOIN      SYS.SYSPHYSIDX pidx

                ON        idx.table_id = pidx.table_id

                AND       idx.phys_index_id = pidx.phys_index_id

                JOIN      SYS.SYSTAB AS ptbl

                ON        fk.primary_table_id = ptbl.table_id

                JOIN      SYS.SYSTAB AS ftbl

                ON        fk.foreign_table_id = ftbl.table_id

                JOIN      SYS.SYSIDXCOL AS idxcol

                ON        idx.table_id = idxcol.table_id

                AND       idx.index_id = idxcol.index_id

                JOIN      SYS.SYSTABCOL AS pcol

                ON        ptbl.table_id = pcol.table_id

                AND       idxcol.primary_column_id = pcol.column_id

                JOIN      SYS.SYSTABCOL AS fcol

                ON        ftbl.table_id = fcol.table_id

                AND       idxcol.column_id = fcol.column_id

                LEFT JOIN SYS.SYSTRIGGER ut

                ON        fk.foreign_table_id = ut.foreign_table_id

                AND       fk.foreign_index_id = ut.foreign_key_id

                AND       ut.event = 'C'

                LEFT JOIN SYS.SYSTRIGGER dt

                ON        fk.foreign_table_id = dt.foreign_table_id

                AND       fk.foreign_index_id = dt.foreign_key_id

                AND       dt.event = 'D'

                WHERE     ftbl.table_name = $table

                AND       ftbl.creator = USER_ID($user)

                ORDER BY  fk.foreign_index_id ASC, idxcol.sequence ASC";

    }

    /**

     * {@inheritdoc}

    /**

     * {@inheritdoc}

     */

    public function getListTableIndexesSQL($table, $currentDatabase = null)

    {

        $user = '';

        if (strpos($table, '.') !== false) {

            list($user, $table) = explode('.', $table);

            $user = $this->quoteStringLiteral($user);

            $table = $this->quoteStringLiteral($table);

        } else {

            $table = $this->quoteStringLiteral($table);

        }

        return "SELECT   idx.index_name AS key_name,

                         IF idx.index_category = 1

                             THEN 1

                             ELSE 0

                         ENDIF AS 'primary',

                         col.column_name,

                         IF idx.\"unique\" IN(1, 2, 5)

                             THEN 0

                             ELSE 1

                         ENDIF AS non_unique,

                         IF tbl.clustered_index_id = idx.index_id

                             THEN 1

                             ELSE NULL

                         ENDIF AS 'clustered', -- clustered flag

                         IF idx.\"unique\" = 5

                             THEN 1

                             ELSE NULL

                         ENDIF AS with_nulls_not_distinct, -- with_nulls_not_distinct flag

                         IF pidx.max_key_distance = 1

                              THEN 1

                              ELSE NULL

                          ENDIF AS for_olap_workload -- for_olap_workload flag

                FROM     SYS.SYSIDX AS idx

                JOIN     SYS.SYSPHYSIDX pidx

                ON       idx.table_id = pidx.table_id

                AND      idx.phys_index_id = pidx.phys_index_id

                JOIN     SYS.SYSIDXCOL AS idxcol

                ON       idx.table_id = idxcol.table_id AND idx.index_id = idxcol.index_id

                JOIN     SYS.SYSTABCOL AS col

                ON       idxcol.table_id = col.table_id AND idxcol.column_id = col.column_id

                JOIN     SYS.SYSTAB AS tbl

                ON       idx.table_id = tbl.table_id

                WHERE    tbl.table_name = $table

                AND      tbl.creator = USER_ID($user)

                AND      idx.index_category != 2 -- exclude indexes implicitly created by foreign key constraints

                ORDER BY idx.index_id ASC, idxcol.sequence ASC";

    }

    /**

     * {@inheritdoc}