view release on metacpan or  search on metacpan

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

    if( @errors )
    {
        warn( "The options '", join( ', ', @errors ), "' were either not recognized or malformed and thus were ignored.\n" );
    }
    # Check statement
    my $select = '';
    if( $sth && ref( $sth ) && ( $sth->isa( "DB::Object::Statement" ) || $sth->can( 'as_string' ) ) )
    {
        $select = $sth->as_string();
        if( $select !~ /^\s*(?:IGNORE|REPLACE)*\s*\bSELECT\s+/ )
        {
            return( $self->error( "SELECT statement to use to create table is invalid:\n$select" ) );
        }
    }
    if( $self->exists() == 0 )
    {
        my $query = 'CREATE ' . ( $temp ? 'TEMPORARY ' : '' ) . "TABLE $table ";
        # Structure of table if any - 
        # structure may very well be provided using a select statement, such as:
        # CREATE TEMPORARY TABLE ploppy TYPE=HEAP COMMENT='this is kewl' MAX_ROWS=10 SELECT * FROM some_table LIMIT 0,0
        my $def    = "(\n" . CORE::join( ",\n", @$data ) . "\n)" if( $data && ref( $data ) && @$data );
        my $tdef   = CORE::join( ' ', map{ "\U$_\E = $opt->{ $_ }" } @options );
        if( !$def && !$select )
        {
            return( $self->error( "Lacking table '$table' structure information to create it." ) );
        }
        $query .= join( ' ', $def, $tdef, $select );
        my $new = $self->prepare( $query ) ||
            return( $self->error( "Error while preparing query to create table '$table':\n$query", $self->errstr() ) );
        # Trick so other method may follow, such as as_string(), fetchrow(), rows()
        if( !defined( wantarray() ) )
        {
            # print( STDERR "create(): wantarrays in void context.\n" );
            $new->execute ||
                return( $self->error( "Error while executing query to create table '$table':\n$query", $new->errstr() ) );
        }
        $self->reset_structure;
        return( $new );
    }
    else
    {
        return( $self->error( "Table '$table' already exists." ) );
    }
}

sub create_info
{
    my $self    = shift( @_ );
    my $table   = $self->{table};
    $self->structure || return( $self->pass_error );
    my $struct  = $self->{structure};
    my $fields  = $self->{fields};
    my $default = $self->{default};
    my $primary = $self->{primary};
    my @output = ();
    foreach my $field ( sort{ $fields->{ $a } <=> $fields->{ $b } } keys( %$fields ) )
    {
        push( @output, "$field $struct->{ $field }" );
    }
    push( @output, "PRIMARY KEY(" . CORE::join( ',', @$primary ) . ")" ) if( $primary && @$primary );
    my $info = $self->stat( $table );
    my @opt  = ();
    push( @opt, "TYPE = $info->{type}" ) if( $info->{type} );
    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

sub drop
{
    my $self  = shift( @_ );
    my $table = $self->{table} || 
        return( $self->error( "No table was provided to drop." ) );
    my $query = "DROP TABLE $table";
    my $sth = $self->prepare( $query ) ||
        return( $self->error( "Error while preparing query to drop table '$table':\n$query", $self->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 );
}

sub exists
{
    return( shift->table_exists( shift( @_ ) ) );
}

sub lock
{
    my $self = shift( @_ );
    # There is two arguments, the first one does not look like an exiting table name and the second is a number...
    # It pretty much looks like a statement lock
    if( @_ == 2 && ( !$self->exists( $_[ 0 ] ) || $_[ 1 ] =~ /^\d+$/ ) )
    {
        return( $self->SUPER::lock( @_ ) );
    }
    my @tables = ();
    my $chk_opt = sub
    {
        my $self  = shift( @_ );
        my $table = shift( @_ );
        my $opt   = shift( @_ );
        my $alias = shift( @_ );
        if( $opt !~ /^(READ|READ\s+LOCAL|(LOW_PRIORITY\s+)?WRITE)$/i )
        {
            return( $self->error( "Bad table '$table' locking option '$opt'." ) );
        }

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

    my $sth = $t->optimize; # OPTIMIZE TABLE t

This will prepare a query to C<optimize> the table. If it is called in void context, the statement handler returned is executed immediately.

See L<MyQL documentation for more information|https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html>

=head2 qualified_name

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

Note that in MySQL there is no meaning of schema like in other modern drivers like PostgreSQL. In MySQL a C<schema> is equivalent to a C<database>. See this L<StackOverflow discussion|https://stackoverflow.com/questions/11618277/difference-between-sc...

If L<DB::Object::Tables/prefixed> is greater than 2, the database name will be added.

At minimum, the table name is added.

    $tbl->prefixed(2);
    $tbl->qualified_name;
    # Would return something like: mydb.my_table

    $tbl->prefixed(1);
    $tbl->qualified_name;
    # Would return only: my_table

See L<MyQL documentation for more information|https://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html>

=head2 rename

Provided with a new table name, and this will prepare the necessary query to rename the table and return the statement handler.

If it is called in void context, the statement handler is executed immediately.

    # Get the prefs table object
    my $tbl = $dbh->pref;
    $tbl->rename( 'prefs' );
    # Would issue a statement handler for the query: ALTER TABLE pref RENAME TO prefs

It returns the statement handler created.

See L<PostgreSQL documentation for more information|https://dev.mysql.com/doc/refman/5.7/en/alter-table.html>

=head2 repair

Provided with an optional hash or hash reference of parameter, and this will prepare a query to C<repair> the MySQL table.

    my $tbl = $dbh->my_table || die( $dbh->error );
    my $sth = $tbl->repair || die( $tbl->error );
    $sth->exec || die( $sth->error );

If it is called in void context, the statement handler is executed immediately.

It returns the statement handler created.

See L<PostgreSQL documentation for more information|https://dev.mysql.com/doc/refman/5.7/en/repair-table.html>

=head2 stat

Provided with a table name and this will prepare a C<SHOW TABLE STATUS> MySQL query. If no table explicitly specified, then this will prepare a stat query for all tables in the database.

    $tbl->stat( 'my_table' );
    # SHOW TABLE STATUS FROM my_database LIKE 'my_table'
    $tbl->stat;
    # SHOW TABLE STATUS FROM my_database

The stat statement will be executed and an hash reference of property-value pairs in lower case will be retrieved for each table. Each table hash is stored in another hash reference of table name-properties hash reference pairs.

If only one table was the subject of the stat, in list context, this returns an hash of those table stat properties, and in scalar context its hash reference.

If the stat was done for the entire database, in list context, this returns an hash of all those tables to properties pairs, or an hash reference in scalar context.

=head2 structure

This returns in list context an hash and in scalar context an hash reference of the table structure.

The hash, or hash reference returned contains the column name and its definition.

This method will also set the following object properties:

=over 4

=item L<DB::Object::Tables/type>

The table type.

=item L<DB::Object::Tables/schema>

No such thing in MySQL, so this is unavailable.

=item I<default>

A column name to default value hash reference

=item I<fields>

A column name to field position (integer) hash reference

=item I<null>

A column name to a boolean representing whether the column is nullable or not.

=item L<DB::Object::Tables/primary>

An array reference of column names that are used as primary key for the table.

=item I<structure>

A column name to its sql definition

=item I<types>

A column name to column data type hash reference

=back

=head2 table_info

This is an alias for L<DB::Object::Mysql/table_info>

=head2 unlock