.\" Automatically generated by Pod::Man 4.09 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .if !\nF .nr F 0 .if \nF>0 \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} .\} .\" ======================================================================== .\" .IX Title "KiokuDB::Backend::DBI 3pm" .TH KiokuDB::Backend::DBI 3pm "2018-03-30" "perl v5.26.1" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" KiokuDB::Backend::DBI \- DBI backend for KiokuDB .SH "VERSION" .IX Header "VERSION" version 1.23 .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 7 \& my $dir = KiokuDB\->connect( \& "dbi:mysql:foo", \& user => "blah", \& password => "moo\*(Aq, \& columns => [ \& # specify extra columns for the \*(Aqentries\*(Aq table \& # in the same format you pass to DBIC\*(Aqs add_columns \& \& name => { \& data_type => "varchar", \& is_nullable => 1, # probably important \& }, \& ], \& ); \& \& $dir\->search({ name => "foo" }); # SQL::Abstract .Ve .SH "DESCRIPTION" .IX Header "DESCRIPTION" This backend for KiokuDB leverages existing \s-1DBI\s0 accessible databases. .PP The schema is based on two tables, \f(CW\*(C`entries\*(C'\fR and \f(CW\*(C`gin_index\*(C'\fR (the latter is only used if a Search::GIN extractor is specified). .PP The \f(CW\*(C`entries\*(C'\fR table has two main columns, \f(CW\*(C`id\*(C'\fR and \f(CW\*(C`data\*(C'\fR (currently in \&\s-1JSPON\s0 format, in the future the format will be pluggable), and additional user specified columns. .PP The user specified columns are extracted from inserted objects using a callback (or just copied for simple scalars), allowing \s-1SQL\s0 where clauses to be used for searching. .SH "COLUMN EXTRACTIONS" .IX Header "COLUMN EXTRACTIONS" The columns are specified using a DBIx::Class::ResultSource instance. .PP One additional column info parameter is used, \f(CW\*(C`extract\*(C'\fR, which is called as a method on the inserted object with the column name as the only argument. The return value from this callback will be used to populate the column. .PP If the column extractor is omitted then the column will contain a copy of the entry data key by the same name, if it is a plain scalar. Otherwise the column will be \f(CW\*(C`NULL\*(C'\fR. .PP These columns are only used for lookup purposes, only \f(CW\*(C`data\*(C'\fR is consulted when loading entries. .SH "DBIC INTEGRATION" .IX Header "DBIC INTEGRATION" This backend is layered on top of DBIx::Class::Storage::DBI and reused DBIx::Class::Schema for \s-1DDL.\s0 .PP Because of this objects from a DBIx::Class::Schema can refer to objects in the KiokuDB entries table, and vice versa. .PP For more details see DBIx::Class::Schema::KiokuDB. .SH "SUPPORTED DATABASES" .IX Header "SUPPORTED DATABASES" This driver has been tested with MySQL 5 (4.1 should be the minimal supported version), SQLite 3, and PostgreSQL 8.3. .PP The \s-1SQL\s0 code is reasonably portable and should work with most databases. Binary column support is required when using the Storable serializer. .SS "Transactions" .IX Subsection "Transactions" For reasons of performance and ease of use database vendors ship with read committed transaction isolation by default. .PP This means that read locks are \fBnot\fR acquired when data is fetched from the database, allowing it to be updated by another writer. If the current transaction then updates the value it will be silently overwritten. .PP \&\s-1IMHO\s0 this is a much bigger problem when the data is unstructured. This is because data is loaded and fetched in potentially smaller chunks, increasing the risk of phantom reads. .PP Unfortunately enabling truly isolated transaction semantics means that \&\f(CW\*(C`txn_commit\*(C'\fR may fail due to a lock contention, forcing you to repeat your transaction. Arguably this is more correct \*(L"read comitted\*(R", which can lead to race conditions. .PP Enabling repeatable read or serializable transaction isolation prevents transactions from interfering with eachother, by ensuring all data reads are performed with a shared lock. .PP For more information on isolation see .PP \fISQLite\fR .IX Subsection "SQLite" .PP SQLite provides serializable isolation by default. .PP .PP \fIMySQL\fR .IX Subsection "MySQL" .PP MySQL provides read committed isolation by default. .PP Serializable level isolation can be enabled by by default by changing the \&\f(CW\*(C`transaction\-isolation\*(C'\fR global variable, .PP .PP \fIPostgreSQL\fR .IX Subsection "PostgreSQL" .PP PostgreSQL provides read committed isolation by default. .PP Repeatable read or serializable isolation can be enabled by setting the default transaction isolation level, or using the \f(CW\*(C`SET TRANSACTION\*(C'\fR \s-1SQL\s0 statement. .PP , .SH "ATTRIBUTES" .IX Header "ATTRIBUTES" .IP "schema" 4 .IX Item "schema" Created automatically. .Sp This is DBIx::Class::Schema object that is used for schema deployment, connectivity and transaction handling. .IP "connect_info" 4 .IX Item "connect_info" An array reference whose contents are passed to \*(L"connect\*(R" in DBIx::Class::Schema. .Sp If omitted will be created from the attrs \f(CW\*(C`dsn\*(C'\fR, \f(CW\*(C`user\*(C'\fR, \f(CW\*(C`password\*(C'\fR and \&\f(CW\*(C`dbi_attrs\*(C'\fR. .IP "dsn" 4 .IX Item "dsn" .PD 0 .IP "user" 4 .IX Item "user" .IP "password" 4 .IX Item "password" .IP "dbi_attrs" 4 .IX Item "dbi_attrs" .PD Convenience attrs for connecting using \*(L"connect\*(R" in KiokuDB. .Sp User in \f(CW\*(C`connect_info\*(C'\fR's builder. .IP "columns" 4 .IX Item "columns" Additional columns, see \*(L"\s-1COLUMN EXTRACTIONS\*(R"\s0. .IP "serializer" 4 .IX Item "serializer" KiokuDB::Serializer. Coerces from a string, too: .Sp .Vb 1 \& KiokuDB\->connect("dbi:...", serializer => "storable"); .Ve .Sp Defaults to KiokuDB::Serializer::JSON. .IP "create" 4 .IX Item "create" If true the existence of the tables will be checked for and the \s-1DB\s0 will be deployed if not. .Sp Defaults to false. .IP "extract" 4 .IX Item "extract" An optional Search::GIN::Extract used to create the \f(CW\*(C`gin_index\*(C'\fR entries. .Sp Usually Search::GIN::Extract::Callback. .IP "schema_hook" 4 .IX Item "schema_hook" A hook that is called on the backend object as a method with the schema as the argument just before connecting. .Sp If you need to modify the schema in some way (adding indexes or constraints) this is where it should be done. .IP "for_update" 4 .IX Item "for_update" If true (the defaults), will cause all select statement to be issued with a \&\f(CW\*(C`FOR UPDATE\*(C'\fR modifier on MySQL, Postgres and Oracle. .Sp This is highly reccomended because these database provide low isolation guarantees as configured out the box, and highly interlinked graph databases are much more susceptible to corruption because of lack of transcational isolation than normalized relational databases. .IP "sqlite_sync_mode" 4 .IX Item "sqlite_sync_mode" If this attribute is set and the underlying database is SQLite, then \&\f(CW\*(C`PRAGMA syncrhonous=...\*(C'\fR will be issued with this value. .Sp Can be \f(CW\*(C`OFF\*(C'\fR, \f(CW\*(C`NORMAL\*(C'\fR or \f(CW\*(C`FULL\*(C'\fR (SQLite's default), or 0, 1, or 2. .Sp See . .IP "mysql_strict" 4 .IX Item "mysql_strict" If true (the default), sets MySQL's strict mode. .Sp This is \fB\s-1HIGHLY\s0\fR reccomended, or you may enjoy some of MySQL's more interesting features, like automatic data loss when the columns are too narrow. .Sp See and DBIx::Class::Storage::DBI::mysql for more details. .IP "on_connect_call" 4 .IX Item "on_connect_call" See DBIx::Class::Storage::DBI. .Sp This attribute is constructed based on the values of \f(CW\*(C`mysql_version\*(C'\fR and \&\f(CW\*(C`sqlite_sync_mode\*(C'\fR, but may be overridden if you need more control. .IP "dbic_attrs" 4 .IX Item "dbic_attrs" See DBIx::Class::Storage::DBI. .Sp Defaults to .Sp .Vb 1 \& { on_connect_call => $self\->on_connect_call } .Ve .IP "batch_size" 4 .IX Item "batch_size" \&\s-1SQL\s0 that deals with entries run in batches of the amount provided in \&\f(CW\*(C`batch_size\*(C'\fR. If it is not provided, the statements will run in a single batch. .Sp This solves the issue with SQLite where lists can only handle 999 elements at a time. \f(CW\*(C`batch_size\*(C'\fR will be set to 999 by default if the driver in use is SQLite. .SH "METHODS" .IX Header "METHODS" See KiokuDB::Backend and the various roles for more info. .IP "deploy" 4 .IX Item "deploy" Calls \*(L"deploy\*(R" in DBIx::Class::Schema. .Sp Deployment to MySQL requires that you specify something like: .Sp .Vb 1 \& $dir\->backend\->deploy({ producer_args => { mysql_version => 4 } }); .Ve .Sp 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. .IP "drop_tables" 4 .IX Item "drop_tables" Drops the \f(CW\*(C`entries\*(C'\fR and \f(CW\*(C`gin_index\*(C'\fR tables. .SH "TROUBLESHOOTING" .IX Header "TROUBLESHOOTING" .ie n .SS "I get ""unexpected end of string while parsing JSON string""" .el .SS "I get \f(CWunexpected end of string while parsing JSON string\fP" .IX Subsection "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 \&\f(CW\*(C`BLOB\*(C'\fR column MySQL will simply shorten it for you. .PP Why \f(CW\*(C`BLOB\*(C'\fR 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 \*(L"strict\*(R" \s-1SQL\s0 mode in the configuration. .PP To resolve the actual problem (though this obviously won't repair your lost data), alter the entries table so that the \f(CW\*(C`data\*(C'\fR column uses the nonstandard \&\f(CW\*(C`LONGBLOB\*(C'\fR datatype. .SH "VERSION CONTROL" .IX Header "VERSION CONTROL" KiokuDB-Backend-DBI is maintained using Git. Information about the repository is available on .SH "AUTHOR" .IX Header "AUTHOR" Yuval Kogman .SH "COPYRIGHT AND LICENSE" .IX Header "COPYRIGHT AND LICENSE" This software is copyright (c) 2014 by Yuval Kogman, Infinity Interactive. .PP This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.