DB-Object
3 results

 view release on metacpan or  search on metacpan

lib/DB/Object/Postgres/Tables.pm  view on Meta::CPAN 

    push( @output, "PRIMARY KEY(" . CORE::join( ',', @$primary ) . ")" ) if( $primary && @$primary );
    my $info = $self->stat( $table );
    my @opt  = ();
    my $addons = $info->{create_options};
    if( $addons )
    {
        $addons =~ s/(\A|\s+)([\w\_]+)\s*=\s*/$1\U$2\E=/g;
        push( @opt, $addons );
    }
    push( @opt, "COMMENT='" . quotemeta( $info->{ 'comment' } ) . "'" ) if( $info->{comment} );
    my $str = "CREATE TABLE $table (\n\t" . CORE::join( ",\n\t", @output ) . "\n)";
    $str   .= ' ' . CORE::join( ' ', @opt ) if( @opt );
    $str   .= ';';
    return( @output ? $str : undef() );
}

# NOTE: sub default is inherited from DB::Object::Tables
# sub default

# <https://www.postgresql.org/docs/10/sql-altertable.html>
sub disable_trigger
{
    my $self  = shift( @_ );
    my $table = $self->{table} || 
        return( $self->error( "No table was provided to disable trigger." ) );
    my $opts  = $self->_get_args_as_hash( @_ );
    $opts->{all} //= 0;
    # This feature exists only since version 8.1
    unless( $self->database_object->version >= version->declare( '8.1' ) )
    {
        return( $self->error( "Disabling trigger on a table requires PostgreSQL version 8.1 or higher." ) );
    }
    my $query = 'ALTER TABLE ' . $table . ' DISABLE TRIGGER ';
    if( defined( $opts->{name} ) && length( $opts->{name} ) )
    {
        $query .= $opts->{name};
    }
    else
    {
        $query .= $opts->{all} ? 'ALL' : 'USER';
    }
    my $sth = $self->database_object->prepare( $query ) ||
        return( $self->error( "Error while preparing query to disable trigger for table '$table':\n$query", $self->database_object->errstr() ) );
    if( !defined( wantarray() ) )
    {
        $sth->execute() ||
        return( $self->error( "Error while executing query to disable trigger for table '$table':\n$query", $sth->errstr() ) );
    }
    return( $sth );
}

sub drop
{
    my $self  = shift( @_ );
    my $table = $self->{table} || 
    return( $self->error( "No table was provided to drop." ) );
    my $opts  = $self->_get_args_as_hash( @_ );
    my $query = 'DROP TABLE';
    $query   .= ' IF EXISTS' if( $opts->{ 'if-exists' } || $opts->{if_exists} );
    $query   .= " $table";
    if( $opts->{cascade} )
    {
        $query .= ' CASCADE';
    }
    ## Default Postgres behavior
    elsif( $opts->{restrict} )
    {
        $query .= ' RESTRICT';
    }
    my $sth = $self->database_object->prepare( $query ) ||
    return( $self->error( "Error while preparing query to drop table '$table':\n$query", $self->database_object->errstr() ) );
    if( !defined( wantarray() ) )
    {
        $sth->execute() ||
        return( $self->error( "Error while executing query to drop table '$table':\n$query", $sth->errstr() ) );
    }
    $self->reset_structure;
    return( $sth );
}

# <https://www.postgresql.org/docs/10/sql-altertable.html>
sub enable_trigger
{
    my $self  = shift( @_ );
    my $table = $self->{table} || 
    return( $self->error( "No table was provided to enable trigger." ) );
    my $opts  = $self->_get_args_as_hash( @_ );
    $opts->{all} //= 0;
    # This feature exists only since version 8.1
    unless( $self->database_object->version >= version->declare( '8.1' ) )
    {
        return( $self->error( "Enabling trigger on a table requires PostgreSQL version 8.1 or higher." ) );
    }
    my $query = 'ALTER TABLE ' . $table . ' ENABLE TRIGGER ';
    if( defined( $opts->{name} ) && length( $opts->{name} ) )
    {
        $query .= $opts->{name};
    }
    else
    {
        $query .= $opts->{all} ? 'ALL' : 'USER';
    }
    my $sth = $self->database_object->prepare( $query ) ||
    return( $self->error( "Error while preparing query to disable trigger for table '$table':\n$query", $self->database_object->errstr() ) );
    if( !defined( wantarray() ) )
    {
        $sth->execute() ||
        return( $self->error( "Error while executing query to disable trigger for table '$table':\n$query", $sth->errstr() ) );
    }
    return( $sth );
}

sub exists
{
    my $self = shift( @_ );
    return( $self->table_exists( @_ ? shift( @_ ) : $self->name ) );
}

# NOTE: sub fields is inherited from DB::Object::Tables
# sub fields

lib/DB/Object/Postgres/Tables.pm  view on Meta::CPAN 

SELECT 
     n.nspname AS "schema_name"
    ,c.relname AS "table_name"
    ,CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 's' THEN 'special'
        WHEN 'f' THEN 'foreign table'
        WHEN 'p' THEN 'table'
     END as "table_type"
    ,a.attname AS "field"
    ,a.attnum AS "field_num"
    ,CASE
        WHEN a.atttypmod = -1 THEN null
        WHEN t.oid IN (1042, 1043) THEN a.atttypmod - 4
        WHEN t.oid IN (1560, 1562) THEN a.atttypmod
        ELSE NULL
     END AS "character_maximum_length"
    ,CASE SUBSTRING(t.typname,1,1)
        WHEN '_' THEN SUBSTRING(t.typname,2)
        ELSE t.typname
     END AS "data_type"
    ,pg_catalog.format_type(a.atttypid,a.atttypmod) AS "format_type"
    ,a.attndims > 0 AS "is_array"
    ,CASE a.attnotnull
        WHEN FALSE THEN TRUE
        ELSE FALSE
     END AS "is_nullable"
    ,COALESCE(i.indisprimary,false) AS "is_primary"
    ,COALESCE(i.indisunique,false) AS "is_unique"
    ,r.oid IS NOT NULL AS "is_foreign"
    ,r2.oid IS NOT NULL AS "is_check"
    ,(SELECT pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid) FROM pg_attrdef 
        WHERE pg_attrdef.adrelid = c.oid 
        AND pg_attrdef.adnum = a.attnum) AS "default"
    ,(SELECT pg_description.description FROM pg_description
        WHERE pg_description.objoid = c.oid AND pg_description.objsubid = a.attnum) AS "comment"
    ,(SELECT c2.relname FROM pg_inherits LEFT JOIN pg_class c2 ON c2.oid = pg_inherits.inhparent
        WHERE pg_inherits.inhrelid = c.oid) AS "table_parent"
    ,c2.relname AS "index_name"
    ,i.indnatts AS "index_n_columns"
    ,i.indkey AS "index_columns"
    ,ARRAY(
        SELECT pg_get_indexdef(i.indexrelid, k, TRUE)
        FROM generate_series(1,array_upper(string_to_array(i.indkey::text, ' ' )::int2[],1)) AS s(k)
        ORDER BY k
    ) AS "index_keys"
    ,r.conname AS "foreign_name"
    ,CASE
        WHEN r.conindid > 0 THEN (SELECT c3.relname FROM pg_catalog.pg_class c3 WHERE c3.oid = r.conindid)
        ELSE NULL
     END AS "foreign_index_name"
    ,CASE
        WHEN r.confrelid > 0 THEN (SELECT c4.relname FROM pg_catalog.pg_class c4 WHERE c4.oid = r.confrelid)
        ELSE NULL
     END AS "foreign_table"
    ,CASE r.confupdtype
        WHEN 'a' THEN 'nothing'
        WHEN 'r' THEN 'restrict'
        WHEN 'c' THEN 'cascade'
        WHEN 'n' THEN 'null'
        WHEN 'd' THEN 'default'
        ELSE NULL
     END AS "foreign_update_action"
    ,CASE r.confdeltype
        WHEN 'a' THEN 'nothing'
        WHEN 'r' THEN 'restrict'
        WHEN 'c' THEN 'cascade'
        WHEN 'n' THEN 'null'
        WHEN 'd' THEN 'default'
        ELSE NULL
     END AS "foreign_delete_action"
    ,CASE r.confmatchtype
        WHEN 'f' THEN 'full'
        WHEN 'p' THEN 'partial'
        WHEN 's' THEN 'simple'
        ELSE NULL
     END AS "foreign_match"
    ,r.conkey AS "foreign_columns"
    ,ARRAY(
        SELECT a2.attname
        FROM generate_series(1,array_upper(r.conkey,1)) AS z(j), pg_catalog.pg_attribute a2
        WHERE a2.attrelid = c.oid AND a2.attnum = r.conkey[j]
    ) AS "foreign_keys"
    ,CASE
        WHEN r.oid IS NOT NULL THEN pg_get_constraintdef(r.oid,TRUE)
        ELSE NULL
     END AS "foreign_expression"
    ,r2.conname AS "check_name"
    ,CASE
        WHEN r2.conindid > 0 THEN (SELECT c3.relname FROM pg_catalog.pg_class c3 WHERE c3.oid = r2.conindid)
        ELSE NULL
     END AS "check_index_name"
    ,r2.conkey AS "check_columns"
    ,ARRAY(
        SELECT a2.attname
        FROM generate_series(1,array_upper(r2.conkey,1)) AS z(j), pg_catalog.pg_attribute a2
        WHERE a2.attrelid = c.oid AND a2.attnum = r2.conkey[j]
    ) AS "check_keys"
    ,CASE
        WHEN r2.oid IS NOT NULL THEN pg_get_constraintdef(r2.oid,TRUE)
        ELSE NULL
     END AS "check_expression"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
LEFT JOIN pg_catalog.pg_index i ON i.indrelid = c.oid AND a.attnum = ANY (i.indkey::int[])
LEFT JOIN pg_catalog.pg_class c2 ON c2.oid = i.indexrelid
LEFT JOIN pg_catalog.pg_constraint r ON r.conrelid = c.oid AND r.contype = 'f' AND a.attnum = ANY (r.conkey)
LEFT JOIN pg_catalog.pg_constraint r2 ON r2.conrelid = c.oid AND r2.contype = 'c' AND a.attnum = ANY (r2.conkey)
WHERE
    c.relname = ? AND
    a.attnum > 0 AND
    NOT a.attisdropped
ORDER BY a.attnum
EOT
    # http://www.postgresql.org/docs/9.3/interactive/infoschema-columns.html
    # select * from information_schema.columns where table_name = 'address'
    my $sth = $self->database_object->prepare_cached( $query ) ||
        return( $self->error( "Error while preparing query to get table '$table' columns specification: ", $self->database_object->errstr ) );
    $sth->execute( $table ) ||
        return( $self->error( "Error while executing query to get table '$table' columns specification: ", $sth->errstr ) );
    my $check = {};
    my $foreign = {};
    my $index = {};
    my @primary = ();
    my $ref = '';

lib/DB/Object/Postgres/Tables.pm  view on Meta::CPAN 

If provided, this will create a temporary table.

=item * C<with oids>

If true, this will enable table oid

=item * C<without oids>

If true, this will disable table oid

=back

This will return an error if the table already exists, so best to check beforehand with L</exists>.

Upon success, it will return the new statement to create the table. However, if L</create> is called in void context, then the statement is executed right away and returned.

=head2 create_info

This returns the create info for the current table object as a string representing the sql script necessary to recreate the table.

=head2 disable_trigger

    my $sth = $tbl->disable_trigger;
    my $sth = $tbl->disable_trigger( all => 1 );
    my $sth = $tbl->disable_trigger( name => 'my_trigger' );

Provided with some optional parameters and this will return a statement handler to disable all triggers or a given trigger on the table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

    $tbl->disable_trigger;
    # would issue immediately the following query:
    ALTER TABLE my_table DISABLE TRIGGER USER

It takes the following options:

=over 4

=item * C<all>

If true, this will disable all trigger on the table. Please note that, as per the L<PostgreSQL documentation|https://www.postgresql.org/docs/10/sql-altertable.html> this requires super user privilege.

If false, this will disable only the user triggers, i.e. not including the system ones.

=item * C<name>

If a trigger name is provided, it will be used to specifically disable this trigger.

=back

=head2 drop

This will prepare a drop statement to drop the current table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

It takes the following options:

=over 4

=item * C<cascade>

If true, C<CASCADE> will be added to the C<DROP> query.

=item * C<if_exists>

If true, this will add a C<IF EXISTS> to the C<DROP> query.

You can also use I<if-exists>

=item * C<restrict>

If true, C<RESTRICT> will be added to the C<DROP> query.

=back

See L<PostgreSQL documentation for more information|https://www.postgresql.org/docs/9.5/sql-droptable.html>

=head2 enable_trigger

    my $sth = $tbl->enable_trigger;
    my $sth = $tbl->enable_trigger( all => 1 );
    my $sth = $tbl->enable_trigger( name => 'my_trigger' );

Provided with some optional parameters and this will return a statement handler to enable all triggers or a given trigger on the table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

    $tbl->enable_trigger;
    # would issue immediately the following query:
    ALTER TABLE my_table ENABLE TRIGGER USER

It takes the following options:

=over 4

=item * C<all>

If true, this will enable all trigger on the table. Please note that, as per the L<PostgreSQL documentation|https://www.postgresql.org/docs/10/sql-altertable.html> this requires super user privilege.

If false, this will enable only the user triggers, i.e. not including the system ones.

=item * C<name>

If a trigger name is provided, it will be used to specifically enable this trigger.

=back

=head2 exists

Returns true if the current table exists, or false otherwise.

=head2 foreign

Sets or gets an hash reference of foreign key constraint name to an hash of properties for that constraint.

Available properties are:

=over 4

=item * C<expr>

The foreign key expression used when creating the table schema.

=item * C<match>

Typical value is C<full>, C<partial> and C<simple>

=item * C<on_delete>

The action the database is to take upon deletion. For example: C<nothing>, C<restrict>, C<cascade>, C<null> or C<default>

=item * C<on_update>

The action the database is to take upon update. For example: C<nothing>, C<restrict>, C<cascade>, C<null> or C<default>

=item * C<table>

The table name of the foreign key.

=item * C<fields>

The associated table column names for this foreign key constraint.

=item * C<name>

The foreign key constraint name.

=back

=head2 lock

This will prepare a query to lock the table and return the statement handler. If it is called in void context, the statement handler returned is executed immediately.

It takes an optional lock type and an optional C<NOWAIT> parameter.

Supported lock types are:

=over 4

=item C<ACCESS SHARE>

=item C<ROW SHARE>

=item C<ROW EXCLUSIVE>

=item C<SHARE UPDATE EXCLUSIVE>

=item C<SHARE>

=item C<SHARE ROW EXCLUSIVE>

=item C<EXCLUSIVE>

=item C<ACCESS EXCLUSIVE>

=back

See L<PostgreSQL documentation for more information|https://www.postgresql.org/docs/9.5/explicit-locking.html>

=head2 on_conflict

A convenient wrapper to L<DB::Object::Postgres::Query/on_conflict>

=head2 optimize

Not implemented in PostgreSQL.

=head2 parent

This will return the parent table if the current table inherits from another table.

=head2 qualified_name

This return a fully qualified name to be used as a prefix to columns in queries.



( run in 0.657 second using v1.01-cache-2.11-cpan-39bf76dae61 )