NetSDS::DBI::Table (3pm)

        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();

\*(C`NetSDS::DBI::Table\*(C' module provides commonly used \s-1CRUD\s0 functionality for data stored in single database.

Main idea was that we can agree about some limitations:

* every such table contains \*(C`id\*(C' field that is primary key

* we use PostgreSQL \s-1DBMS\s0 with all it's features

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 \s-1SQL\s0 expressions like \*(C`status = 'active'\*(C' for \*(C`WHERE\*(C' clause * order - arrayref of \s-1SQL\s0 expressions like \*(C`id desc\*(C' for \*(C`ORDER BY\*(C' clause * limit - max number of records to fetch (\s-1LIMIT\s0 N) * offset - records to skip from beginning (\s-1OFFSET\s0 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 \*(L"id\*(R" 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 \s-1INSERT\s0 queries and in fact is only wrapper for multiple \*(C`insert_row()\*(C' calls. So it's not so fast as one insert but allows to use different key-value pairs for different records. 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 \s-1SQL\s0 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); } \s-1NOTE\s0: this method works only for restricted tables that use \*(C`fields\*(C' parameter at construction time.

See \*(C`samples/test_db_table.pl\*(C' script

Bad documentation

NetSDS::DBI

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

None

Michael Bochkaryov <[email protected]>

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 \s-1GNU\s0 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 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1WARRANTY\s0; without even the implied warranty of \s-1MERCHANTABILITY\s0 or \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0. See the \s-1GNU\s0 General Public License for more details.

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