NetSDS::DBI::Table(3pm) User Contributed Perl Documentation NetSDS::DBI::Table(3pm)
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
perl v5.10.1 2010-04-28 NetSDS::DBI::Table(3pm)