Name

QBit::Application::Model::DB - base class for DB.

Description

Base class for working with databases.

GitHub

https://github.com/QBitFramework/QBit-Application-Model-DB

Install

• cpanm QBit::Application::Model::DB
• apt-get install libqbit-application-model-db-perl (http://perlhub.ru/)

Debug

$QBit::Application::Model::DB::DEBUG = TRUE;

Abstract methods

• query

• filter

• _get_table_object

• _create_sql_db

• _connect

• _is_connection_error

Package methods

meta

Arguments:

• %meta - meta information about database

Example:

package Test::DB;

use qbit;

use base qw(QBit::Application::Model::DB);

my $meta = {
    tables => {
        users => {
            fields => [
                {name => 'id',        type => 'INT',      unsigned => 1, not_null => 1, autoincrement => 1,},
                {name => 'create_dt', type => 'DATETIME', not_null => 1,},
                {name => 'login',     type => 'VARCHAR',  length => 255, not_null => 1,},
            ],
            primary_key => [qw(id)],
            indexes     => [{fields => [qw(login)], unique => 1},],
        },

        fio => {
            fields => [
                {name => 'user_id'},
                {name => 'name',    type => 'VARCHAR', length => 255,},
                {name => 'midname', type => 'VARCHAR', length => 255,},
                {name => 'surname', type => 'VARCHAR', length => 255,},
            ],
            foreign_keys => [[[qw(user_id)] => 'users' => [qw(id)]]]
        },
    },
};

__PACKAGE__->meta($meta);

in Appplication.pm

use Test::DB accessor => 'db';

get_all_meta

Arguments:

• $package - package object or name (optional)

Return values:

• $meta - meta information about database

Example:

my $meta = $app->db->get_all_meta('Test::DB');

init

No arguments.

Method called from "new" before return object.

quote

Arguments:

• $name - string

Return values:

• $quoted_name - quoted string

Example:

my $quoted_name = $app->db->quote('users'); # 'users'

quote_identifier

Arguments:

• $name - string

Return values:

• $quoted_name - quoted string

Example:

my $quoted_name = $app->db->quote_identifier('users'); # "users"

begin

No arguments.

start a new transaction or create new savepoint

Example:

$app->db->begin();

commit

No arguments.

commits the current transaction or release savepoint

Example:

$app->db->commit();

rollback

No arguments.

rolls back the current transaction or savepoint

Example:

$app->db->rollback();

transaction

Arguments:

• $sub - reference to sub

Example:

$app->db->transaction(sub {
    # work with db
    ...
});

create_sql

Arguments:

• @tables - table names (optional)

Return values:

• $sql - sql

Example:

my $sql = $app->db->create_sql(qw(users));

init_db

Arguments:

• @tables - table names (optional)

Example:

$app->db->init_db(qw(users));

finish

No arguments.

Check that transaction closed

Example:

$app->db->finish();

Internal packages

• QBit::Application::Model::DB::Class - base class for DB modules;

• QBit::Application::Model::DB::Field - base class for DB fields;

• QBit::Application::Model::DB::Filter - base class for DB filters;

• QBit::Application::Model::DB::Query - base class for DB queries;

• QBit::Application::Model::DB::Table - base class for DB tables;

• QBit::Application::Model::DB::VirtualTable - base class for DB virtual tables;

Name

QBit::Application::Model::DB::Class

Description

Base class for DB modules.

RO accessors

• db

Package methods

init

quote

quote_identifier

filter

For more information see code and test.

Name

QBit::Application::Model::DB::Field

Description

Base class for DB fields.

RO accessors

• name

• type

• table

Package methods

init

init_check

Abstract methods

• create_sql

For more information see code and test.

Name

QBit::Application::Model::DB::Filter

Description

Base class for DB filters.

Package methods

new

and

or

and_not

or_not

expression

For more information see code and test.

Name

QBit::Application::Model::DB::Query

Description

Base class for DB queries.

Abstract methods

• _found_rows

Package methods

init

No arguments.

Method called from "new" before return object.

select

Arguments:

• %opts - options with keys
  • table - object
  • fields (optional, default: all fields)
  • filter (optional)

Return values:

• $query - object

Example:

my $query = $app->db->query->select(
    table  => $app->db->users,
    fields => [qw(id login)],
    filter => {id => 3},
);

join

Arguments:

• %opts - options with keys
  • table - object
  • alias (optional)
  • fields (optional, default: all fields)
  • filter (optional)
  • join_type (optional, default: 'INNER JOIN')
  • join_on (optional, default: use foreign keys)

Return values:

• $query - object

Example:

 my $join_query = $query->join(
     table     => $app->db->fio,
     fields    => [qw(name surname)],
     filter    => ['name' => 'LIKE' => \'Max'],
     join_type => 'INNER JOIN',
     join_on   => ['user_id' => '=' => {'id' => $app->db->users}],
 );

left_join

join_type => 'LEFT JOIN'

right_join

join_type => 'RIGHT JOIN'

group_by

Arguments:

• @fields

Return values:

• $query - object

Example:

 my $group_query = $query->group_by(qw(name surname));

order_by

Arguments:

• @fields - fields or reference to array

Return values:

• $query - object

Example:

 my $order_query = $query->order_by('id', ['login', 1]);

limit

Arguments:

• @limit

Return values:

• $query - object

Example:

 my $limit_query = $query->limit(100, 200);

distinct

No arguments.

Return values:

• $query - object

Example:

 my $distinct_query = $query->distinct();

union

Arguments:

• $query - object
• %opts - options with keys
  • all - boolean (optional, default: FALSE)

Return values:

• $query - object

Example:

 my $union_query = $query->union(
     $app->db->query->select(
         table => $app->db->people,
         fields => [qw(id login name surname)]
     ),
     all => FALSE,
 );

union_all

all => TRUE

calc_rows

Arguments:

• $flag - boolean

Return values:

• $query - object

Example:

 my $calc_rows_query = $query->calc_rows(TRUE);

all_langs

Arguments:

• $flag - boolean

Return values:

• $query - object

Example:

 my $all_langs_query = $query->all_langs(TRUE);

for_update

No arguments.

Return values:

• $query - object

Example:

 my $for_update_query = $query->for_update();

filter

get_sql_with_data

Arguments:

• %opts - options with keys
  • offset - number (optional, default: 0)

Return values:

• $sql - string

Example:

 my $sql = $query->get_sql_with_data();

get_all

No arguments.

Return values:

• $data - reference to array

Example:

my $data = $query->get_all();

found_rows

No arguments.

Return values:

• $bool

Example:

 my $bool = $query->found_rows();

For more information see code and test.

Name

QBit::Application::Model::DB::Table

Description

Base class for DB tables.

RO accessors

• name

• inherits

• primary_key

• indexes

• foreign_keys

Abstract methods

• create_sql
• add_multi
• add
• edit
• delete
• _get_field_object
• _convert_fk_auto_type

Package methods

init

No arguments.

Method called from "new" before return object.

fields

No arguments.

Return values:

• $fields - reference to array of objects (QBit::Application::Model::DB::Field)

Example:

 my $fields = $app->db->users->fields();

fields

No arguments.

Return values:

• @field_names

Example:

 my @field_names = $app->db->users->field_names();

get_all

Arguments:

• %opts - options with keys
  • fields
  • filter
  • group_by
  • order_by
  • limit
  • distinct
  • for_update
  • all_langs

For more information see QBit::Application::Model::DB::Query::get_all

Return values:

• $data - reference to array

Example:

my $data = $app->db->users->get_all(
    fields => [qw(id login)],
    filter => {id => 3},
);

get

Arguments:

• $id - scalar or hash
• %opts - options with keys
  • fields
  • for_update
  • all_langs

For more information see QBit::Application::Model::DB::Query::get_all

Return values:

• $data - reference to hash

Example:

my $data = $app->db->users->get(3, fields => [qw(id login)],);

truncate

No arguments.

Truncate table.

Example:

 $app->db->users->truncate();

default_fields

You can redefine this method in your Model.

default_primary_key

You can redefine this method in your Model.

default_indexes

You can redefine this method in your Model.

default_foreign_keys

You can redefine this method in your Model.

have_fields

Arguments:

• $fields - reference to array

Return values:

• $bool

Example:

my $bool = $app->db->users->have_fields([qw(id login)]);

For more information see code and test.

Name

QBit::Application::Model::DB::VirtualTable

Description

Base class for DB virtual tables.

RO accessors

• query

• name

Package methods

init

fields

get_sql_with_data

For more information see code and test.