NAME

NetSDS::DBI::Table

SYNOPSIS

        use NetSDS::DBI::Table;

        my $q = NetSDS::DBI::Table->new(
                dsn    => 'dbi:Pg:dbname=netsdsdb;host=127.0.0.1',
                user   => 'netsds',
                passwd => 'test',
                table  => 'public.messages',
        ) or warn NetSDS::DBI::Table->errstr();

DESCRIPTION

NetSDS::DBI::Table module provides commonly used CRUD functionality for data stored in single database.

Main idea was that we can agree about some limitations:

* every such table contains id field that is primary key

* we use PostgreSQL DBMS with all it's features

CLASS API

new([...]) - class constructor

        my $tbl = NetSDS::DBI::Table->new(
                dsn => 'dbi:Pg:dbname=content',
                login => 'netsds',
                passwd => 'topsecret,
                table => 'content.meta',
        );

fetch(%params) - get records from table as array of hashrefs

Paramters (hash):

* fields - fetch fields by list

* filter - arrayref of SQL expressions like status = 'active' for WHERE clause

* order - arrayref of SQL expressions like id desc for ORDER BY clause

* limit - max number of records to fetch (LIMIT N)

* offset - records to skip from beginning (OFFSET N)

* for_update - records selected for further update within current transaction

Returns: message as array of hashrefs

Sample:

        my @messages = $q->fetch(
                fields => ['id', 'now() as time'],
                filter => ['msg_status = 5', 'date_received < now()'], # where msg_status=5 and date_received < now()
                order  => ['id desc', 'src_addr'], # order by id desc, src_addr
                limit => 3, # fetch 3 records
                offset => 5, # from 6-th record
                for_update => 1, # for update
        )

insert_row(%key_val_pairs) - insert record into table

Paramters: record fields as hash

Returns: id of inserted record

        my $user_id = $tbl->insert_row(
                'login' => 'vasya',
                'password' => $encrypted_passwd,
        );

insert(@records_list) - mass insert

Paramters: list of records (as hashrefs)

Returns: array of inserted records "id"

This method allows mass insert of records.

        my @user_ids = $tbl->insert(
                { login => 'vasya', password => $str1 },
                { login => 'masha', password => $str2 },
                { login => 'petya', password => $str3, active => 'false' },
        );

Warning! This method use separate INSERT queries and in fact is only wrapper for multiple insert_row() calls. So it's not so fast as one insert but allows to use different key-value pairs for different records.

update_row($id, %params) - update record parameters

Paramters: id, new parameters as hash

Returns: updated record as hash

Example:

        my %upd = $table->update_row($msg_id,
                status => 'failed',
                dst_addr => '380121234567',
                );

After this %upd hash will contain updated table record.

update(%params) - update records by filter

Paramters: filter, new values

        $tbl->update(
                filter => ['active = true', 'created > '2008-01-01'],
                set => {
                        info => 'Created after 2007 year',
                }
        );

get_count(%params) - retrieve number of records

Just return total number of records by calling:

        # SELECT COUNT(id) FROM schema.table
        my $count = $tbl->get_count();

        my $count_active = $tbl->get_count(filter => ['active = true']);

delete_by_id(@ids) - delete records by identifier

Paramters: list of record id

Returns: 1 if ok, undef if error

Method deletes records from SQL table by it's identifiers.

        if ($tbl->remove(5, 8 ,19)) {
                print "Records successfully removed.";
        }

delete(@filters) - delete records

Paramters: list of filters

Returns: 1 if ok, undef if error

        $tbl->delete(
                'active = false',
                'expire < now()',
        );

get_fields() - get list of fields

Example:

        my @fields = @{ $tbl->get_fields() };
        print "Table fields: " . join (', ', @fields);

has_field($field) - check if field exists

Paramters: field name

Example:

        if ($tbl->has_field('uuid')) {
                $tbl->call("delete tbldata where uuid=?", $uuid);
        }

NOTE: this method works only for restricted tables that use fields parameter at construction time.

EXAMPLES

See samples/test_db_table.pl script

BUGS

Bad documentation

SEE ALSO

NetSDS::DBI

http://en.wikipedia.org/wiki/Create,_read,_update_and_delete

TODO

None

AUTHOR

Michael Bochkaryov <misha@rattler.kiev.ua>

LICENSE

Copyright (C) 2008-2009 Net Style Ltd.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA