Easy(3pm)

✨ Easy(3pm) ✨

okay bestie, sit DOWN, because this is the DBIx::Easy page — yes, from the User Contributed Perl Documentation crew — and it's literally the glow-up your database code has been waiting for 💅

---

🌸 NAME 🌸

DBIx::Easy — an Easy to Use DBI interface 🫶

---

🎀 SYNOPSIS 🎀

  use DBIx::Easy;
  my $dbi_interface = new DBIx::Easy qw(Pg template1);

  $dbi_interface -> insert ('transaction',
                   id => serial ('transaction', 'transactionid'),
                   time => \$dbi_interface -> now);

  $dbi_interface -> update ('components', "table='ram'", price => 100);
  $rows_deleted = $dbi_interface -> delete ('components', 'stock = 0');
  $dbi_interface -> makemap ('components', 'id', 'price', 'price > 10');
  $components = $dbi_interface -> rows ('components');
  $components_needed = $dbi_interface -> rows ('components', 'stock = 0');

---

🦋 DESCRIPTION 🦋

DBIx::Easy is, no surprise, an easy to use DBI interface. Right now the Pg, mSQL, mysql, Sybase, ODBC and XBase drivers are supported. it's giving range, it's giving versatility ✨

---

👑 CREATING A NEW DBI INTERFACE OBJECT 👑

  $dbi_interface = new DBIx::Easy qw(Pg template1);
  $dbi_interface = new DBIx::Easy qw(Pg template1 racke);
  $dbi_interface = new DBIx::Easy qw(Pg template1 racke aF3xD4_i);
  $dbi_interface = new DBIx::Easy qw(Pg template1 racke@linuxia.de aF3xD4_i);
  $dbi_interface = new DBIx::Easy qw(Pg template1 racke@linuxia.de:3306 aF3xD4_i);

So the required parameters are the database driver and the database name. That's the non-negotiable part, bestie. Then the extra parameters are the database user and the password to access the database. If you wanna name the database host, use the USER@HOST notation for the user parameter. And if you're feeling spicy and want to specify the port to connect to, it's USER@HOST:PORT. slay 🌈

---

💖 DESTROYING A DBI INTERFACE OBJECT 💖

Okay this one matters, listen up: it is important that you commit all changes at the end of the interaction with the DBMS. You've got two ways to do it.

You can either explicitly commit:

  $dbi_interface -> commit ();

or do it implicitly:

  undef $dbi_interface;

either way, don't just ghost your DBMS, that's rude 😭

---

🙈 ERROR HANDLING 🙈

  sub fatal {
    my ($statement, $err, $msg) = @_;
    die ("$0: Statement \"$statement\" failed (ERRNO: $err, ERRMSG: $msg)\n");
  }
  $dbi_interface -> install_handler (\&fatal);

If any of the DBI methods fails, either die will be invoked or an error handler installed with install_handler will be called. So like, you will know. no silent failing here, periodt 💅

---

🧚 CACHING ISSUES 🧚

By default, this module caches table structures. You can turn that off by setting $DBIx::Easy::cache_structs to 0. your call, bestie 🫶

---

💀 XBASE DRIVER 💀

The DBIx::Easy method rows fails to work with the DBD::XBase driver.

yeah. it just does. not our fault, just don't be surprised 😭

---

⭐ METHODS ⭐

🌈 DATABASE ACCESS 🌈

process statement

  $sth = $dbi_interface -> process ("SELECT * FROM foo");
  print "Table foo contains ", $sth -> rows, " rows.\n";

Processes statement by just combining the prepare and execute steps of the DBI. Returns the statement handle in case of success. two steps, one method, efficiency icon ✨

insert table column value [column value] ...

  $sth = $dbi_interface -> insert ('bar', drink => 'Caipirinha');

Inserts the given column/value pairs into table. It figures out from the SQL data type which values have to be quoted. And if you wanna protect values with SQL functions from getting quoted, just pass a reference to the value. iconic behaviour honestly 🍓

update table conditions column value [column value] ...

  $dbi_interface -> update ('components', "table='ram'", price => 100);
  $dbi_interface -> update ('components', "table='ram'", price => \"price + 20");

Updates any row of table which fulfills the conditions by inserting the given column/value pairs. You can use scalar references to embed strings into the resulting SQL statement without further quoting. Returns the number of rows modified. it tells you what it did, love that for you 👑

put table conditions column value [column value] ...

delete table conditions

  $dbi_interface -> delete ('components', "stock=0");

Deletes any row of table which fulfills the conditions. Without conditions, all rows are deleted — yes, ALL of them, so be careful bestie 😭. Returns the number of rows deleted.

do_without_transaction statement

  $sth = $dbi_interface -> do_without_transaction ("CREATE DATABASE foo");

Issues a DBI do statement while forcing autocommit. This is used for statements that can't be run in transaction mode (like CREATE DATABASE in PostgreSQL). special-case statements gotta be handled special 💅

rows table [conditions]

  $components = $dbi_interface -> rows ('components');
  $components_needed = $dbi_interface -> rows ('components', 'stock = 0');

Returns the number of rows within table satisfying conditions, if any were given. counting era 🔢

makemap table keycol valcol [condition]

  $dbi_interface -> makemap ('components', 'idf', 'price');
  $dbi_interface -> makemap ('components', 'idf', 'price', 'price > 10');
  $dbi_interface -> makemap ('components', 'idf', '*');
  $dbi_interface -> makemap ('components', 'idf', '*', 'price > 10');

Produces a mapping between the values within column keycol and column valcol from table. If a condition is given, only rows matching this condition are used for the mapping.

And ooh — if you want the hash reference to the whole record as the value of the mapping, just use the asterisk as the valcol parameter. main character move ⭐

random_row table conditions [map]

Returns a random row of the specified table. If map is set, the result is a hash reference of the selected row, otherwise an array reference. If the table doesn't contain rows, undefined is returned. chaos but make it structured 🎲

serial table sequence

Returns a serial number for table by querying the next value from sequence. Depending on the DBMS one of the parameters is ignored. This is sequence for mSQL resp. table for PostgreSQL. mysql doesn't support sequences, but the AUTO_INCREMENT keyword for fields. In this case this method returns 0 and mysql generates a serial number for this field. everyone does it differently, we don't judge 💜

fill sth hashref [flag column ...]

Fetches the next table row from the result stored into sth and records the value of each field in hashref. If flag is set, only the fields specified by the column arguments are considered, otherwise the fields specified by the column arguments are omitted. inclusion/exclusion, we contain multitudes ✨

view table [name value ...]

  foreach my $table (sort $dbi_interface -> tables)
    {
    print $cgi -> h2 ('Contents of ', $cgi -> code ($table));
    print $dbi_interface -> view ($table);
    }

Produces a plain text representation of the database table table. This method accepts the following options as name/value pairs:

• columns — which columns to display.
• order — which column to sort the row after.
• limit — maximum number of rows to display.
• separator — separator inserted between the columns.
• where — display only rows matching this condition.

  print $dbi_interface -> view ($table,
                                order => $cgi -> param ('order') || '',
                                where => "price > 0");

🌟 DATABASE INFORMATION 🌟

is_table NAME

Returns a truth value if there exists a table NAME in this database. yes or no, no essay needed 💅

tables

Returns a list of all tables in this database.

sequences

Returns a list of all sequences in this database (Postgres only).

columns TABLE

Returns a list of the column names of TABLE.

types TABLE

Returns a list of the column types of TABLE.

sizes TABLE

Returns a list of the column sizes of TABLE.

typemap TABLE

Returns a mapping between column names and column types for table TABLE.

sizemap TABLE

Returns a mapping between column names and column sizes for table TABLE.

⏰ TIME VALUES ⏰

now

  $dbi_interface -> insert ('transaction',
                   id => serial ('transaction', 'transactionid'),
                   time => \$dbi_interface -> now);

Returns a representation for the current time. It uses special values of the DBMS if possible. timezone-literate king 👑

💰 MONETARY VALUES 💰

money2num money

Converts the monetary value money to a numeric one. math but make it cute 🍓

🎀 MISCELLANEOUS 🎀

is_auth_error msg

This method decides if the error message msg is caused by an authentification error or not. detective work, honestly 🔍

---

🫶 AUTHORS 🫶

Stefan Hornburg (Racke), racke@linuxia.de — Dennis Sch[:o]n, ds@1d10t.de

Support for Sybase and ODBC provided by David B. Bitton <david@codenoevil.com>. legends, all of them 💖

---

💫 VERSION 💫

0.20

---

🌈 SEE ALSO 🌈

perl(1), DBI(3), DBD::Pg(3), DBD::mysql(3), DBD::msql(3), DBD::Sybase(3), DBD::ODBC(3).

---

and that's the whole page, bestie — brought to you by perl v5.32.0, dated 2021-01-06, filed under Easy(3pm). go forth and query responsibly 💅✨🦋