The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
App-DBBrowser-1.053
River stage zero No dependents
14 ++
/ App::DBBrowser::DB

NAME

App::DBBrowser::DB - Database plugin documentation.

VERSION

Version 1.053

DESCRIPTION

A database plugin provides the database specific methods. App::DBBrowser considers a module whose name matches /^App::DBBrowser::DB::[^:']+\z/ and which is located in one of the @INC directories as a database plugin. Plugins with the name App::DBBrowser::DB::$database_driver should be for general use of $database_driver databases.

The user can add an installed database plugin to the available plugins in the option menu (db-browser -h) by selecting DB and then DB Plugins.

A suitable database plugin provides the methods named in this documentation.

Column names passed as arguments to plugin methods are already quoted with the DBI quote_identifier method.

PLUGIN API VERSION

This documentation describes the plugin API version 1.5.

Supported plugin API version: 1.5.

METHODS

new

The constructor method.

Arguments

A reference to a hash. The hash entries are:

        app_dir             # path to the application directoriy
        home_dir
        db_plugin           # name of the database plugin
        add_metadata        # true or false

        # SQLite only:
        sqlite_search       # if true, don't use cached database names
        db_cache_file       # path to the file with the cached database names
return

The object.

plugin_api_version

Arguments

none

return

The version of the plugin-API to which the plugin refers.

See "PLUGIN API VERSION" for the plugin API version described by this documentation.

db_driver

Arguments

none

return

The name of the DBI database driver used by the plugin.

driver_prefix

Arguments

none

return

The driver-private prefix.

Example for the database driver Pg:

    sub driver_prefix {
        return 'pg';
    }

read_arguments

Arguments

none

return

A reference to an array of hashes. The hashes have two or three key-value pairs:

    { name => 'string', prompt => 'string', keep_secret => true/false }

name holds the field name for example like "user" or "host".

The value of prompt is used as the prompt string, when the user is asked for the data. The prompt entry is optional. If prompt doesn't exist, the value of name is used instead.

If keep_secret is true, the user input should not be echoed to the terminal. Also the data is not stored in the plugin configuration file if keep_secret is true.

An example read_arguments method:

    sub read_arguments {
        my ( $self ) = @_;
        return [
            { name => 'host', prompt => "Host",     keep_secret => 0 },
            { name => 'port', prompt => "Port",     keep_secret => 0 },
            { name => 'user', prompt => "User",     keep_secret => 0 },
            { name => 'pass', prompt => "Password", keep_secret => 1 },
        ];
    }

The information returned by the method read_arguments is used to build the entries of the db-browser options Fields and Login Data.

    read_arguments()  =>  option "Fields"      =>  $connect_parameter->{required}
                          option "Login Data"  =>  $connect_parameter->{read_arg}
                                               =>  $connect_parameter->{keep_secret}

environment_variables

Arguments

none

return

A reference to an array of environment variables.

An example environment_variables method:

    sub environment_variables {
        my ( $self ) = @_;
        return [ qw( DBI_DSN DBI_HOST DBI_PORT DBI_USER DBI_PASS ) ];
    }

See the db-browser option ENV Variables.

    environment_variables()  =>  option "ENV Variables"  =>  $connect_parameter->{use_env_var}

choose_arguments

Arguments

none

return

A reference to an array of hashes. The hashes have three or four key-value pairs:

    { name => 'string', prompt => 'string', default_index => index, avail_values => [ value_1, value_2, value_3, ... ] }

The value of name is the name of the database connection attribute.

The value of prompt is used as the prompt string. The prompt entry is optional. If prompt doesn't exist, the value of name is used instead.

avail_values holds the available values for that attribute as an array reference.

The avail_values array entry of the index position default_index is used as the default value.

Example form the plugin App::DBBrowser::DB::SQLite:

    sub choose_arguments {
        my ( $self ) = @_;
        return [
            { name => 'sqlite_unicode',             default_index => 1, avail_values => [ 0, 1 ] },
            { name => 'sqlite_see_if_its_a_number', default_index => 1, avail_values => [ 0, 1 ] },
        ];
    }

See the db-browser option DB Options.

    choose_arguments()  =>  option "DB Options"  =>  $connect_parameter->{chosen_arg}

available_databases

Arguments

A reference to a hash. If available_databases uses the get_db_handle method, the hash reference can be passed to get_db_handle as the second argument. See "get_db_handle" for more info about the passed hash reference.

return

If the object attribute add_metadata is true, available_databases returns the "user-databases" as an array-reference and the "system-databases" (if any) as an array-reference.

If add_metadata is not true, available_databases returns only the "user-databases" as an array-reference.

get_db_handle

Arguments

The database name and a reference to a hash of hashes.

The hash of hashes provides the settings gathered from the option Database settings.

    $connect_parameter = {
        use_env_var => {
            env_var => true or false,
            env_var => true or false,
            ...
        },
        chosen_arg => {
            attribute => chosen value,
            attribute => chosen value,
            ...
        },
        required => {
            name => true or false,
            name => true or false,
            ...
        },
        read_arg => {
            name => user input,
            name => user input,
            ...
        },
        keep_secret = {
            name => true or false,
            name => true or false,
            ...
        },
        dir_sqlite => [     # array reference with directories where to search for SQLite databases
            /path/dir,
            ...
        ]
    };

For example for the plugin mysql the hash of hashes held by $connect_parameter could look like this:

    $connect_parameter = {
        use_env_var => {
            DBI_HOST => 1,
            DBI_USER => 0,
            DBI_PASS => 0,
        },
        read_arg => {
            host => undef,
            pass => undef,
            user => 'db_user_name',
            port => undef
        },
        chosen_arg => {
            mysql_enable_utf8 => 1
        },
        required => {
            port => 0,
            user => 1,
            pass => 1,
            host => 1
        },
        keep_secret => {
            port => 0,
            host => 0,
            pass => 1,
            user => 0
        },
    };
return

Database handle.

get_schema_names

Arguments

The database handle and the database name.

return

If add_metadata is true, get_schema_names returns the "user-schemas" as an array-reference and the "system-schemas" (if any) as an array-reference.

If add_metadata is not true, get_schema_names returns only the "user-schemas" as an array-reference.

get_table_names

Arguments

The database handle and the schema name.

return

If add_metadata is true, get_table_names returns the "user-tables" as an array-reference and the "system-tables" (if any) as an array-reference.

If add_metadata is not true, get_table_names returns only the "user-tables" as an array-reference.

primary_key_auto

Arguments

none

return

The primary-key-autoincrement statement.

Example for the database driver Pg:

    sub primary_key_auto {
        return "SERIAL PRIMARY KEY";
    }

column_names_and_types

Arguments

Database handle, database name, schema name, available tables as an array reference.

return

Two hash references - one for the column names and one for the column types:

    $col_names = {
        table_1 => [ column_1_name, column_2_name, ... ],
        table_2 => [ column_1_name, column_2_name, ... ],
        ...
    }

    $col_types = {
        table_1 => [ column_1_type, column_2_type, ... ],
        table_2 => [ column_1_type, column_2_type, ... ],
        ...
    }

primary_and_foreign_keys

The method primary_and_foreign_keys is optional.

Arguments

Database handle, database name, schema name, available tables as an array reference.

return

Two hash references - one for the primary keys and one for the foreign keys:

    $primary_keys = {
        table_1 => [ 'primary_key_col_1' [ , ... ] ],
        table_2 => [ 'primary_key_col_1' [ , ... ] ],
        ...
    };

    $foreign_keys = {
        table_1 => {
            fk_name_1 => {
                foreign_key_col   => [ 'foreign_key_col_1' [ , ... ] ],
                reference_table   => 'Reference_table',
                reference_key_col => [ 'reference_key_col_1' [ , ... ] ],
            fk_name_2 => {
                ...
            }
        table_2 => {
            ...
        }
    };

sql_regexp

Arguments

Column name, $do_not_match_regexp (true/false), $case_sensitive (true/false).

Use the placeholder instead of the string which should match or not match the regexp.

return

The sql regexp substatement.

Example form the plugin App::DBBrowser::DB::mysql:

    sub sql_regexp {
        my ( $self, $col, $do_not_match_regexp, $case_sensitive ) = @_;
        if ( $do_not_match_regexp ) {
            return ' '. $col . ' NOT REGEXP ?'        if ! $case_sensitive;
            return ' '. $col . ' NOT REGEXP BINARY ?' if   $case_sensitive;
        }
        else {
            return ' '. $col . ' REGEXP ?'            if ! $case_sensitive;
            return ' '. $col . ' REGEXP BINARY ?'     if   $case_sensitive;
        }
    }

concatenate

Arguments

A reference to an array of strings.

return

The sql substatement which concatenates the passed strings.

Example form the plugin App::DBBrowser::DB::Pg:

    sub concatenate {
        my ( $self, $arg ) = @_;
        return join( ' || ', @$arg );
    }

epoch_to_datetime

Arguments

The column name and the interval.

The interval is 1 (seconds), 1000 (milliseconds) or 1000000 (microseconds).

return

The sql epoch to datetime substatement.

Example form the plugin App::DBBrowser::DB::mysql:

    sub epoch_to_datetime {
        my ( $self, $col, $interval ) = @_;
        return "FROM_UNIXTIME($col/$interval,'%Y-%m-%d %H:%i:%s')";
    }

epoch_to_date

Arguments

The column name and the interval.

The interval is 1 (seconds), 1000 (milliseconds) or 1000000 (microseconds).

return

The sql epoch to date substatement.

Example form the plugin App::DBBrowser::DB::mysql:

    sub epoch_to_date {
        my ( $self, $col, $interval ) = @_;
        return "FROM_UNIXTIME($col/$interval,'%Y-%m-%d')";
    }

truncate

Arguments

The column name and the precision (int).

return

The sql truncate substatement.

Example form the plugin App::DBBrowser::DB::mysql:

    sub truncate {
        my ( $self, $col, $precision ) = @_;
        return "TRUNCATE($col,$precision)";
    }

bit_length

Arguments

The column name.

return

The sql bit length substatement.

Example form the plugin App::DBBrowser::DB::Pg:

The sql bit length substatement.

    sub bit_length {
        my ( $self, $col ) = @_;
        return "BIT_LENGTH($col)";
    }

char_length

Arguments

The column name.

return

The sql char length substatement.

Example form the plugin App::DBBrowser::DB::Pg:

    sub char_length {
        my ( $self, $col ) = @_;
        return "CHAR_LENGTH($col)";
    }

CREDITS

Thanks to the Perl-Community.de and the people form stackoverflow for the help.

AUTHOR

Matthäus Kiem <cuer2s@gmail.com>

LICENSE AND COPYRIGHT

Copyright 2012-2017 Matthäus Kiem.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl 5.10.0. For details, see the full text of the licenses in the file LICENSE.