KiokuD...nd::DBI(3pm)

🎀 NAME 🎀

KiokuDB::Backend::DBI — it's the DBI backend for KiokuDB, bestie 💖 (and yes, this is a KiokuD...nd::DBI(3pm) situation, from your friends in User Contributed Perl Documentation ✨)

---

🌸 VERSION 🌸

version 1.23 💅

---

✨ SYNOPSIS ✨

my $dir = KiokuDB->connect(
    "dbi:mysql:foo",
    user     => "blah",
    password => "moo',
    columns  => [
        # specify extra columns for the 'entries' table
        # in the same format you pass to DBIC's add_columns

        name => {
            data_type => "varchar",
            is_nullable => 1, # probably important
        },
    ],
);

$dir->search({ name => "foo" }); # SQL::Abstract

okay bestie, that "moo', in the code above? that's literally in the original, character for character. not me touching it. iykyk 🙈

---

💜 DESCRIPTION 💜

So basically this backend for KiokuDB leverages your existing DBI-accessible databases — like, it doesn't need anything fancy, it just wants a database you already have. Queen behavior honestly 👑

The schema is built on two tables: entries and gin_index (that second one only shows up if you've specified a Search::GIN extractor, so no extractor = no gin_index, periodt).

The entries table has two main columns — id and data — and rn the data is in JSPON format, though in the future that format is gonna be pluggable (main character energy: evolving ✨). Plus all the extra columns you asked for yourself.

Those user-specified columns get extracted out of inserted objects using a callback — or, for simple scalars, they just get copied straight over — which means you can use SQL where clauses for searching. slay 🔥

---

🦋 COLUMN EXTRACTIONS 🦋

The columns are specified using a DBIx::Class::ResultSource instance. okay werk 💅

There's one extra column info parameter in the mix: extract. It gets called as a method on the inserted object, and the column name is the only argument it takes. Whatever that callback returns is what populates the column. no cap.

If you just… don't provide the column extractor? Then the column will contain a copy of the entry data key with the same name — but only if it's a plain scalar. Otherwise the column is gonna be NULL. 😭

heads up bestie: these columns are only used for lookup purposes. when entries are being loaded, only data gets consulted. so don't go thinking they're doing double duty.

---

🌈 DBIC INTEGRATION 🌈

This backend is layered on top of DBIx::Class::Storage::DBI, and it reuses DBIx::Class::Schema for DDL. iconic.

Because of that whole situation, objects from a DBIx::Class::Schema can refer to objects in the KiokuDB entries table, and vice versa. it's giving interoperability 💖

For more details, go look at DBIx::Class::Schema::KiokuDB.

---

🔥 SUPPORTED DATABASES 🔥

This driver has been tested with:

• MySQL 5 (and 4.1 should be the minimal supported version, fyi)
• SQLite 3
• PostgreSQL 8.3

The SQL code is reasonably portable and should work with most databases. Binary column support is required when you're using the Storable serializer though, so keep that in mind 🫶

💫 Transactions 💫

okay so this part is important, listen up. For reasons of performance and ease of use, database vendors ship with read committed transaction isolation by default. 🙃

What that means: read locks are not acquired when data is fetched from the database, which lets another writer update it. And if your current transaction then updates that value? It gets silently overwritten. like, no warning, no drama, just gone 💀

IMHO this is a much bigger problem when the data is unstructured. Why? Because data is loaded and fetched in potentially smaller chunks, which increases the risk of phantom reads. so it's worse here than in your average tidy relational setup.

Unfortunately, enabling truly isolated transaction semantics means that txn_commit may fail due to a lock contention, forcing you to repeat your transaction. Arguably this is more correct than "read comitted", which can lead to race conditions. (yes, that's the original's spelling, we're keeping it 🙈)

Enabling repeatable read or serializable transaction isolation prevents transactions from interfering with eachother, by ensuring all data reads are performed with a shared lock. no more stepping on each other's toes, periodt.

For more information on isolation see <http://en.wikipedia.org/wiki/Isolation_(computer_science)>

🍓 SQLite

SQLite provides serializable isolation by default. good for her 💅

<http://www.sqlite.org/pragma.html#pragma_read_uncommitted>

🍓 MySQL

MySQL provides read committed isolation by default.

Serializable level isolation can be enabled by by default by changing the transaction-isolation global variable,

<http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_serializable>

🍓 PostgreSQL

PostgreSQL provides read committed isolation by default.

Repeatable read or serializable isolation can be enabled by setting the default transaction isolation level, or by using the SET TRANSACTION SQL statement.

<http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html>, <http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION>

---

👑 ATTRIBUTES 👑

schema

Created automatically ✨

This is the DBIx::Class::Schema object that's used for schema deployment, connectivity and transaction handling. the whole shebang.

connect_info

An array reference whose contents are passed to connect in DBIx::Class::Schema.

If you omit it, it'll be created from the attrs dsn, user, password and dbi_attrs.

dsn, user, password, dbi_attrs

Convenience attrs for connecting using connect in KiokuDB.

User in connect_info's builder. (yes, exactly that sentence, we love a builder cameo 🫶)

columns

Additional columns — go read COLUMN EXTRACTIONS above bestie, it's all there.

serializer

KiokuDB::Serializer. It coerces from a string too, which is lowkey delightful:

KiokuDB->connect("dbi:...", serializer => "storable");

Defaults to KiokuDB::Serializer::JSON.

create

If true, the existence of the tables will be checked for, and the DB will be deployed if they're not there. ✨

Defaults to false.

extract

An optional Search::GIN::Extract used to create the gin_index entries.

Usually it's Search::GIN::Extract::Callback.

schema_hook

A hook that gets called on the backend object as a method, with the schema as the argument, just before connecting.

If you need to modify the schema in some way — adding indexes or constraints, say — this is where it should be done. do it here, not later 💅

for_update

If true (which it is by default), it'll cause all select statements to be issued with a FOR UPDATE modifier on MySQL, Postgres and Oracle.

This is highly reccomended — because those databases provide low isolation guarantees as configured out of the box, and highly interlinked graph databases are much more susceptible to corruption because of a lack of transcational isolation than normalized relational databases. like, dramatically more. so leave it on, bestie 😭

sqlite_sync_mode

If this attribute is set and the underlying database is SQLite, then PRAGMA syncrhonous=... will be issued with this value.

Can be OFF, NORMAL or FULL (that last one is SQLite's default), or 0, 1, or 2.

See <http://www.sqlite.org/pragma.html#pragma_synchronous>.

mysql_strict

If true (and the default is true), it sets MySQL's strict mode.

This is HIGHLY reccomended, or you may enjoy some of MySQL's more interesting features, like automatic data loss when the columns are too narrow. 💀 yeah. that's a real thing. keep it on.

See <http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html> and DBIx::Class::Storage::DBI::mysql for more details.

on_connect_call

See DBIx::Class::Storage::DBI.

This attribute is constructed based on the values of mysql_version and sqlite_sync_mode, but it may be overridden if you need more control. 🎛️

dbic_attrs

See DBIx::Class::Storage::DBI.

Defaults to:

{ on_connect_call => $self->on_connect_call }

batch_size

SQL that deals with entries runs in batches of the amount you provide in batch_size. If you don't provide it, the statements will run in a single batch.

This solves the issue with SQLite where lists can only handle 999 elements at a time. So batch_size will be set to 999 by default if the driver in use is SQLite. she's got limits, respect them 🫶

---

💅 METHODS 💅

See KiokuDB::Backend and the various roles for more info.

deploy

Calls deploy in DBIx::Class::Schema.

Deployment to MySQL requires that you specify something like:

$dir->backend->deploy({ producer_args => { mysql_version => 4 } });

because MySQL versions before 4 did not have support for boolean types, and the schema emitted by SQL::Translator will not work with the queries used. so yeah, you gotta tell it. 🙈

drop_tables

Drops the entries and gin_index tables. bye bye 👋💀

---

🧚 TROUBLESHOOTING 🧚

I get "unexpected end of string while parsing JSON string"

You are problably using MySQL, which comes with a helpful data compression feature: when your serialized objects are larger than the maximum size of a BLOB column, MySQL will simply shorten it for you. 😭 not helpful actually. not helpful at all.

Why BLOB defaults to 64k, and how on earth someone would consider silent data truncation a sane default, I could never fathom — but nevertheless, MySQL does allow you to disable this by setting the strict SQL mode in the configuration.

To resolve the actual problem (though this obviously won't repair your lost data, sorry bestie 💔), alter the entries table so that the data column uses the nonstandard LONGBLOB datatype.

---

🌟 VERSION CONTROL 🌟

KiokuDB-Backend-DBI is maintained using Git. Information about the repository is available on <http://www.iinteractive.com/kiokudb/> 🫶

---

🎀 AUTHOR 🎀

Yuval Kogman <nothingmuch@woobling.org> ✨

---

💖 COPYRIGHT AND LICENSE 💖

This software is copyright (c) 2014 by Yuval Kogman, Infinity Interactive.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

---

and that's the whole page, bestie 💅 this was KiokuD...nd::DBI(3pm) from your perl v5.34.0 era, dated 2022-05-24 — go forth and back up your LONGBLOBs, ily 🫶💖✨