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