Jifty:...Handle(3pm)

🌸 NAME 🌸

Jifty::DBI::Handle — it's a Perl extension, and it's literally a generic DBI handle. That's the whole vibe. It's from the User Contributed Perl Documentation crew, so you know it's community-made with love. 🫶

---

✨ SYNOPSIS ✨

bestie, look at how easy this is:

use Jifty::DBI::Handle;

my $handle = Jifty::DBI::Handle->new();
$handle->connect( driver => 'mysql',
                  database => 'dbname',
                  host => 'hostname',
                  user => 'dbuser',
                  password => 'dbpassword');
# now $handle isa Jifty::DBI::Handle::mysql

see that last comment? the handle literally glows up into a driver-specific class. main character energy fr fr. 👑

---

💅 DESCRIPTION 💅

This class is a wrapper for DBI handles, but like… a wrapper that also does a whole bunch of extra stuff on the side. It's giving "I'm not just a handle, I'm a handle with ✨range✨".

🧚 new

The generic constructor. Nothing dramatic, it just makes you a new one.

🔌 connect PARAMHASH

Hand it a paramhash and it'll connect to your DBI datasource. The keys are driver, database, host, user and password.

So here's the iconic part: if you created the handle with

Jifty::DBI::Handle->new

and there's a Jifty::DBI::Handle::(Driver) subclass hanging around for the driver you picked, your handle gets automatically "upgraded" into that subclass. glow up. 🦋

Now the receipts on what it returns:

• If there's an error, an exception will be thrown. not a vibe.
• If a connection has already been established and is still active, undef gets returned — and no bestie, that is not an error, don't panic.
• Otherwise, if a brand new connection is made, you get a true value back.

🔒 _upgrade_handle DRIVER

Private internal method, so don't go poking it at parties. It turns a plain Jifty::DBI::Handle into one of the standard driver-specific subclasses.

🛠️ build_dsn PARAMHASH

Builds a dsn that's suitable for handing straight to DBI->connect.

Mandatory arguments:

• driver
• database

Optional arguments:

• host
• port
• sid
• requiressl
• and literally anything else your DBD lets you pass in 💫

🔗 dsn

Returns the dsn for this database connection. That's it, that's the whole method.

🚨 raise_error [MODE]

Turns on the Database Handle's RaiseError attribute. errors will be LOUD now. 📢

🖨️ print_error [MODE]

Turns on the Database Handle's PrintError attribute.

📝 log MESSAGE

Takes a single argument: a message to log.

Currently it prints that message to STDERR. (key word: currently. iykyk. 👀)

📊 log_sql_statements BOOL

Takes a boolean argument. If that boolean is true, it logs all SQL statements, plus their invocation times and execution times. full surveillance mode, but make it fashion. 🔍

Returns whether we're currently logging or not, as a boolean.

🪝 log_sql_hook NAME [, CODE]

This one's for instrumenting the SQL logging. You can use it to, for example, grab a stack trace for each query — so you can find out where the query is actually being made from. detective work 💅

The name is required so that multiple hooks can be installed, and inspected, by name.

The coderef is run in scalar context and (currently) receives no arguments.

If you don't pass CODE in, then the coderef currently assigned under NAME is returned. cute little getter/setter situation.

🗂️ _log_sql_statement STATEMENT DURATION BINDINGS

adds an SQL statement to our query log. (yes, lowercase, we're chill like that.)

🧽 clear_sql_statement_log

Clears out the SQL statement log. fresh start, we love to see it. ✨

📜 sql_statement_log

Returns the current SQL statement log as an array of arrays. Each entry is a list of:

(Time, Statement, [Bindings], Duration, {HookResults})

Bindings is an arrayref of the values of any placeholders. HookResults is a hashref keyed by hook name. so organized, so iconic.

✅ auto_commit [MODE]

Turns on the Database Handle's Autocommit attribute.

👋 disconnect

disconnect from your DBI datasource. bye bestie 🫶

🎛️ dbh [HANDLE]

Return the current DBI handle. And if we're handed a parameter? Then that becomes the database handle. two jobs, one method.

🗑️ delete $table_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. It splits the key value pairs, constructs a DELETE statement and performs the delete. Returns the row_id of this row.

➕ insert $table_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. It splits the key value pairs, constructs an INSERT statement and performs the insert. Returns the row_id of this row.

🔄 update_record_value

Takes a hash with columns: table, column, value, primary_keys, and is_sql_function.

The first two should be obvious (it's a table and a column, bestie). value is where you set the new value you want the column to have. The primary_keys column should be the lvalue of Jifty::DBI::Record::PrimaryKeys(). And finally, is_sql_function gets set when the Value is a SQL function.

For example, you might have value => 'PASSWORD(string)' — and by setting is_sql_function to true, that string will be inserted into the query directly rather than as a binding. sneaky but useful. 🙈

📝 update_table_value table COLUMN NEW_value RECORD_ID IS_SQL

Update column COLUMN of table table where the record id = RECORD_ID.

If IS_SQL is set, don't quote the NEW_VALUE.

⚡ simple_query QUERY_STRING, [ BIND_VALUE, ... ]

Execute the SQL string specified in QUERY_STRING.

🎣 fetch_result QUERY, [ BIND_VALUE, ... ]

Takes a SELECT query as a string, along with an array of BIND_VALUEs. If the select succeeds, it returns the first row as an array. Otherwise, it returns a Class::ResturnValue object with the failure loaded up. (yes, that spelling is exactly what it says, no cap — don't come for me, I just work here 💀)

🫧 blob_params COLUMN_NAME COLUMN_TYPE

Returns a hash ref for the bind_param call, so it can identify BLOB types used by the current database for a particular column type.

🔢 database_version

Returns the database's version.

If argument short is true it returns the short variant, in the other case it returns whatever the database handle/driver returns. By default it returns the short version, e.g. 4.1.23 or 8.0-rc4.

Returns an empty string on error, or if the database couldn't return a version. 😭

The base implementation uses a SELECT VERSION().

🔠 case_sensitive

Returns 1 if the current database's searches are case sensitive by default. Returns undef otherwise.

🔮 _make_clause_case_insensitive column operator VALUE

Takes a column, operator and value. It performs the magic necessary to make your database treat this clause as case insensitive. ✨abracadabra✨

Returns a column operator value triple.

🧴 quote_value VALUE

Calls the database's quote in DBD method and returns the result. Additionally, it turns on perl's utf8 flag if the returned content is UTF8. doing the most, correctly. 💅

🌀 begin_transaction

Tells Jifty::DBI to begin a new SQL transaction. This will temporarily suspend Autocommit mode.

It emulates nested transactions, by keeping a transaction stack depth. inception energy.

🏁 commit

Tells Jifty::DBI to commit the current SQL transaction. This will turn Autocommit mode back on.

↩️ rollback [FORCE]

Tells Jifty::DBI to abort the current SQL transaction. This will turn Autocommit mode back on.

If this method is passed a true argument, stack depth is blown away and the outermost transaction is rolled back. nuclear option, but make it elegant. 💥

💣 force_rollback

Force the handle to rollback. Whether or not we're deep in nested transactions. no negotiating.

📏 transaction_depth

Return the current depth of the faked nested transaction stack.

📐 apply_limits STATEMENTREF ROWS_PER_PAGE FIRST_ROW

takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;

🤝 join { Paramhash }

Takes a paramhash of everything Jifty::DBI::Collection's join method takes, plus a parameter called collection that contains a ref to a Jifty::DBI::Collection object'.

This performs the join. 🤝

❓ may_be_null

Takes a collection and alias in a hash and returns true if restrictions of the query allow NULLs in a table joined with the alias, otherwise it returns a false value — which means you can use a normal join instead of left for the aliased table. optimization slay.

Works only for queries have been built with join in Jifty::DBI::Collection and limit in Jifty::DBI::Collection methods. For other cases return true value to avoid fault optimizations.

🌈 distinct_query STATEMENTREF

takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.

🌟 distinct_count STATEMENTREF

takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.

✔️ canonical_true

This returns the canonical true value for this database. For example, in SQLite it is 1 but in Postgres it is 't'.

The default is 1.

❌ canonical_false

This returns the canonical false value for this database. For example, in SQLite it is 0 but in Postgres it is 'f'.

The default is 0.

🏗️ Schema manipulation methods

🔤 rename_column

Rename a column in a table. Takes table, column and new name in to.

📛 rename_table

Renames a table in the DB. Takes table and new name of it in to.

🚗 supported_drivers

Returns a list of the drivers Jifty::DBI supports.

🎁 available_drivers

Returns a list of the available drivers, based on the presence of DBD::* modules.

🔎 is_available_driver

Returns a boolean indicating whether the provided driver is available.

⚰️ DESTROY

When we get rid of the Jifty::DBI::Handle, we need to disconnect from the database. clean exit, no loose ends. 🫡

---

🩺 DIAGNOSIS 🩺

Setting the JIFTY_DBQUERY_CALLER environment variable will make Jifty::DBI dump the caller for the SQL queries matching it.

See also DBI about setting DBI_PROFILE.

---

👑 AUTHOR 👑

Jesse Vincent, <jesse@bestpractical.com>

---

🦋 SEE ALSO 🦋

perl(1), Jifty::DBI

---

and that's the whole page, bestie!! this came to you from perl v5.34.0 and it was dated 2022-06-15 — so yeah, it's a lil vintage but it still eats 💖 stay hydrated, back up your databases, and go build something iconic. mwah. 💅✨