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

NAME

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

VERSION

Version 2.013

DESCRIPTION

This version introduces backwards incompatible changes.

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 options menu (db-browser -h) by selecting DB and then DB Plugins.

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

METHODS

Required methods

new( \%info )

The constructor method.

When db-browser calls the plugin constructor it passes a reference to a hash ($info):

    sub new {
        my ( $class, $info ) = @_;
        my $self = {
            app_dir       => $info->{app_dir},       # path to the application directoriy
            add_metadata  => $info->{add_metadata},  # true or false

            # for SQLite databases:
            reset_search_cache => $info->{reset_search_cache}, # true ore false
        };
        return bless $self, $class;
    }

reset_search_cache is true if db-browser is called with the argument -s|--search - see "SYNOPSIS" in db-browser.

Returns the created object.

get_db_driver()

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

get_databases( \%connect_parameters );

If get_databases uses the method get_db_handle, \%connect_parameters can be passed to get_db_handle as the second argument. See "get_db_handle" for more info about the passed hash reference.

Returns two array references: the first reference refers to the array of user-databases the second refers to the array of system-databases. The second array reference is optional.

If the option add_metadata is true, user-databases and system-databases are used else only the user-databases are used.

get_db_handle( $database_name, \%connect_parameters )

The data in \%connect_parameters represents the settings from the option Database settings. Which Database settings are available depends on the methods read_arguments, env_variables and set_attributes.

For example the hash of hashes for a mysql plugin could look like this:

    $connect_parameters = {
        use_env_var => {
            DBI_HOST => 1,
            DBI_USER => 0,
            DBI_PASS => 0,
        },
        arguments => {
            host => undef,
            pass => undef,
            user => 'db_user_name',
            port => undef
        },
        attributes => {
            mysql_enable_utf8 => 1
        },
        required => {
            port => 0,
            user => 1,
            pass => 1,
            host => 1
        },
        secret => {
            port => 0,
            host => 0,
            pass => 1,
            user => 0
        },
    };

db-browser expects a database handle with the attribute RaiseError enabled.

Returns the database handle.

Optional methods

DB configuration methods

If the following three methods are available, the db-brower user can configure the different database settings in the options menu. These configurations are then available in the get_db_handle argument $connect_parameter.

If the database driver is SQLite, only set_attributes is used.

read_arguments()

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

    { name => 'string', prompt => 'string', 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 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 secret is true.

An example read_arguments method:

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

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

env_variables()

Returns a reference to an array of environment variables.

An example env_variables method:

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

See the db-browser -h option ENV Variables.

set_attributes()

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

    { name => 'string', prompt => 'string', default => index, 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.

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

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

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

    sub set_attributes {
        my ( $self ) = @_;
        return [
            { name => 'sqlite_unicode',             default => 1, values => [ 0, 1 ] },
            { name => 'sqlite_see_if_its_a_number', default => 1, values => [ 0, 1 ] },
        ];
    }

set_attributes determines the database handle attributes offered in the db-browser option DB Options.

The following methods are already built in. These methods provided by the plugin overwrite the built in methods.

Whether passed column names are quoted or not depends on how db-browser was configured.

get_schemas( $dbh, $database_name )

$dbh is the database handle returned by the method db_hanlde.

Returns the user-schemas as an array-reference and the system-schemas as an array-reference (if any).

If the option add_metadata is true, user-schemas and system-schemas are used else only the user-schemas are used.

regexp( $column_name, $do_not_match, $case_sensitive )

$do_not_match and $case_sensitive are true or false.

Returns the SQL regexp substatement.

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

Example (mysql):

    sub regexp {
        my ( $self, $col, $do_not_match, $case_sensitive ) = @_;
        if ( $do_not_match ) {
            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( \@strings )

Returns the SQL substatement which concatenates the passed strings.

Example (Pg):

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

epoch_to_datetime( $column_name, $interval )

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

Returns the SQL "epoch to datetime" substatement.

Example (mysql):

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

epoch_to_date( $column_name, $interval )

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

Returns the SQL "epoch to date" substatement.

Example (mysql):

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

truncate( $column_name, $precision )

$precision is an integer value.

The SQL truncate substatement.

Example (mysql):

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

bit_length( $column_name )

Returns the SQL bit length substatement.

Example (Pg):

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

char_length( $column_name )

Returns the SQL char length substatement.

Example (Pg):

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

EXAMPLE

A simple plugin which provides only the required methods:

    package App::DBBrowser::DB::MyPlugin;
    use strict;
    use DBI;

    sub new {
        my ( $class, $info ) = @_;  # no use for $info in this plugin
        my $self = {};
        return bless $self, $class;
    }

    sub get_db_driver {
        my ( $self ) = @_;
        return 'mysql';
    }

    sub get_db_handle {
        my ( $self, $db, $connect_parameter ) = @_;
        # "$connect_parameter" contains data if the plugin provides the methods "env_variables",
        # "read_arguments" and "set_attributes"
        my $dbh = DBI->connect( "DBI:mysql:dbname=$db", 'user', 'password', {
            RaiseError => 1,
            PrintError => 0,
        }) or die $DBI::errstr;
        return $dbh;
    }

    sub get_databases {
        my ( $self, $connect_parameter ) = @_;
        return [ 'My_DB_1', 'My_DB_2' ];
    }

    1;

AUTHOR

Matthäus Kiem <cuer2s@gmail.com>

LICENSE AND COPYRIGHT

Copyright 2012-2018 Matthäus Kiem.

THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.

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.