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)