---
name: "NetSDS::DBI::Table"
section: "3pm"
platform: "linux"
package: "libnetsds-perl"
version: "1.301-4"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/NetSDS::DBI::Table"
---

# 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