view release on metacpan or  search on metacpan

lib/SQL/Admin/Driver/Pg/DBI.pm  view on Meta::CPAN

            'S.relnamespace' => \ ' = N.oid',
        },
    );
}


######################################################################
######################################################################
sub _query_constraint_primary_key {      # ;
    my ($self, @schemas) = @_;

    $self->sqla->select (
        [ 'pg_constraint' ],
        [
            'conrelid as table_oid',
            'conkey   as column_list',
        ],
        {
            contype => 'p',
        },
    );

}


######################################################################
######################################################################
sub _query_constraint_unique {           # ;
    my ($self, @schemas) = @_;

    $self->sqla->select (
        [ 'pg_constraint' ],
        [
            'conrelid as table_oid',
            'conkey   as column_list',
        ],
        {
            contype => 'u',
        },
    );

}


######################################################################
######################################################################
sub _query_constraint_foreign_key {      # ;
    my ($self, @schemas) = @_;
    $_ = uc for @schemas;
    $self->sqla->select (
        [ 'syscat.references fk', 'syscat.keycoluse tc', 'syscat.keycoluse rc' ],
        [
            'fk.constname    as constraint_name',
            'fk.tabschema    as table_schema',
            'fk.tabname      as table_name',
            'tc.colname      as column_name',
            'fk.reftabschema as reftable_schema',
            'fk.reftabname   as reftable_name',
            'rc.colname      as refcolumn_name',
            'case when fk.deleterule = \'A\' then \'no_action\'
                  when fk.deleterule = \'C\' then \'cascade\'
                  when fk.deleterule = \'N\' then \'set_null\'
                  when fk.deleterule = \'R\' then \'restrict\'
             end as delete_rule',
            'case when fk.updaterule = \'A\' then \'no_action\'
                  when fk.updaterule = \'C\' then \'cascade\'
                  when fk.updaterule = \'N\' then \'set_null\'
                  when fk.updaterule = \'R\' then \'restrict\'
             end as update_rule',

            'tc.colseq       as colno',
            'fk.colcount',
        ],
        {
            'fk.tabschema'  => ( @schemas ? \ @schemas : { not_like => 'SYS%' }),
            'fk.constname'  => \ ' = tc.constname',
            'fk.refkeyname' => \ ' = rc.constname',
            'tc.colseq'     => \ ' = rc.colseq',
        },
        [ 'fk.tabschema, fk.tabname, fk.constname, tc.colseq' ],
    );
}


######################################################################
######################################################################
sub _query_index {                       # ;
    my ($self, @schemas) = @_;
    $_ = uc for @schemas;
    $self->sqla->select (
        [ 'syscat.indexes ix', 'syscat.indexcoluse cu' ],
        [
            'ix.tabschema    as table_schema',
            'ix.tabname      as table_name',
            'ix.indschema    as index_schema',
            'ix.indname      as index_name',
            'cu.colname      as column_name',
            'case when cu.colorder = \'A\' then \'ASC\'
                  when cu.colorder = \'D\' then \'DESC\'
                  when cu.colorder = \'I\' then null
             end as column_order',
            'case when ix.uniquerule = \'U\' then 1
                  when ix.uniquerule = \'D\' then 0
                  else null
             end as unique',
            'case when ix.pctfree > -1 then ix.pctfree
                  else null
             end as hint_db2_pctfree',

            'cu.colseq       as colno',
            'ix.colcount     as colcount',
        ],
        {
            'ix.indschema'  => ( @schemas ? \ @schemas : { not_like => 'SYS%' }),
            'cu.indschema'  => \ ' = ix.indschema',
            'cu.indname'    => \ ' = ix.indname',
            'ix.uniquerule' => [ 'U', 'D' ],
            'ix.user_defined' => 1,
        },
        [ 'ix.indschema, ix.indname, cu.colseq' ],
    );
}


######################################################################
######################################################################