NetSDS...:Table(3pm)

📦
version 1.301 · 2 versions
, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute

💖 NAME 💖

NetSDS::DBI::Table — the Perl module that handles all your boring database CRUD so you don't have to, bestie. It lives in the user-contributed Perl docs, section 3pm. 🎀

✨ 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 🌸

Okay so NetSDS::DBI::Table gives you the CRUD functionality you use literally all the time, for data chillin' in a single database. No cap.

The whole main character idea here is that we can all agree on a few limitations up front:

  • every table like this has an id field, and that's the primary key 🔑
  • we're using PostgreSQL, and we're using it with all of its features, periodt 🐘

👑 CLASS API 👑

🦋 new([...]) — class constructor 🦋

Builds you a fresh object. Iconic behaviour, honestly.

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 🍓

Parameters (a hash, obvi):

  • fields — fetch fields by list
  • filter — arrayref of SQL expressions like "status = 'active'" for the WHERE clause
  • order — arrayref of SQL expressions like "id desc" for the 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, bestie:

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 🧚

Parameters: 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 🌈

Parameters: list of records (as hashrefs)

Returns: array of inserted records id

This method lets you mass insert records, and it ate:

my @user_ids = $tbl->insert(
        { login => 'vasya', password => $str1 },
        { login => 'masha', password => $str2 },
        { login => 'petya', password => $str3, active => 'false' },
);
⚠️ Warning! This method uses separate INSERT queries and is literally just a wrapper around multiple insert_row() calls. So it's not as fast as one big insert — but the trade-off is it lets you use different key-value pairs for different records. Slay, but know what you're signing up for. 💀

💜 update_row($id, %params) — update record parameters 💜

Parameters: 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, the %upd hash will contain the updated table record. 🫶

🔥 update(%params) — update records by filter 🔥

Parameters: 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 returns the 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 🎀

Parameters: list of record id

Returns: 1 if ok, undef if error

This method deletes records from the SQL table by its identifiers.

if ($tbl->remove(5, 8 ,19)) {
        print "Records successfully removed.";
}

💅 delete(@filters) — delete records 💅

Parameters: 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 🦋

Parameters: 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 the fields parameter at construction time. So like, don't come for it if you didn't set that up, bestie. 🙈

🌟 EXAMPLES 🌟

Go look at the samples/test_db_table.pl script. It's right there. ✨

💀 BUGS 💀

Bad documentation. (Their words, not mine — but honestly? Respect the honesty. 😭)

🌈 SEE ALSO 🌈

🎀 TODO 🎀

None. Periodt. 💅

👑 AUTHOR 👑

Michael Bochkaryov <misha@rattler.kiev.ua> — the main character who wrote this. 🫶

💜 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

and that's the whole page, bestie 💖 this one's from perl v5.32.1, dated 2021-12-26, and it's giving "old but iconic". go build something cute with it 🧚✨

📦 where this page comes from