\documentclass[DIV=12,%
BCOR=0mm,%
headinclude=false,%
footinclude=false,open=any,%
fontsize=10pt,%
oneside,%
paper=210mm:11in]%
{scrbook}
\usepackage[noautomatic]{imakeidx}
\usepackage{microtype}
\usepackage{graphicx}
\usepackage{alltt}
\usepackage{verbatim}
\usepackage[shortlabels]{enumitem}
\usepackage{tabularx}
\usepackage[normalem]{ulem}
\def\hsout{\bgroup \ULdepth=-.55ex \ULset}
% https://tex.stackexchange.com/questions/22410/strikethrough-in-section-title
% Unclear if \protect \hsout is needed. Doesn't looks so
\DeclareRobustCommand{\sout}[1]{\texorpdfstring{\hsout{#1}}{#1}}
\usepackage{wrapfig}
% avoid breakage on multiple
and avoid the next [] to be eaten
\newcommand*{\forcelinebreak}{\strut\\*{}}
\newcommand*{\hairline}{%
\bigskip%
\noindent \hrulefill%
\bigskip%
}
% reverse indentation for biblio and play
\newenvironment*{amusebiblio}{
\leftskip=\parindent
\parindent=-\parindent
\smallskip
\indent
}{\smallskip}
\newenvironment*{amuseplay}{
\leftskip=\parindent
\parindent=-\parindent
\smallskip
\indent
}{\smallskip}
\newcommand*{\Slash}{\slash\hspace{0pt}}
% http://tex.stackexchange.com/questions/3033/forcing-linebreaks-in-url
\PassOptionsToPackage{hyphens}{url}\usepackage[hyperfootnotes=false,hidelinks,breaklinks=true]{hyperref}
\usepackage{bookmark}
\usepackage[english,shorthands=off]{babel}
\babelfont{rm}[Path=/home/wiki/.fonts/cm-unicode/,%
BoldFont=cmunbx.otf,%
BoldItalicFont=cmunbi.otf,%
ItalicFont=cmunti.otf]{cmunrm.otf}
\babelfont{tt}[Scale=MatchLowercase,%
Path=/home/wiki/.fonts/cm-unicode/,%
BoldFont=cmuntb.otf,%
BoldItalicFont=cmuntx.otf,%
ItalicFont=cmunit.otf]{cmuntt.otf}
\babelfont{sf}[Scale=MatchLowercase,%
Path=/home/wiki/.fonts/cm-unicode/,%
BoldFont=cmunsx.otf,%
BoldItalicFont=cmunso.otf,%
ItalicFont=cmunsi.otf]{cmunss.otf}
\renewcommand*{\partpagestyle}{empty}
% global style
\pagestyle{plain}
\usepackage{indentfirst}
% remove the numbering
\setcounter{secnumdepth}{-2}
% remove labels from the captions
\renewcommand*{\captionformat}{}
\renewcommand*{\figureformat}{}
\renewcommand*{\tableformat}{}
\KOMAoption{captions}{belowfigure,nooneline}
\addtokomafont{caption}{\centering}
\deffootnote[3em]{0em}{4em}{\textsuperscript{\thefootnotemark}~}
\addtokomafont{disposition}{\rmfamily}
\addtokomafont{descriptionlabel}{\rmfamily}
\frenchspacing
% avoid vertical glue
\raggedbottom
% this will generate overfull boxes, so we need to set a tolerance
% \pretolerance=1000
% pretolerance is what is accepted for a paragraph without
% hyphenation, so it makes sense to be strict here and let the user
% accept tweak the tolerance instead.
\tolerance=200
% Additional tolerance for bad paragraphs only
\setlength{\emergencystretch}{30pt}
% (try to) forbid widows/orphans
\clubpenalty=10000
\widowpenalty=10000
% given that we said footinclude=false, this should be safe
\setlength{\footskip}{2\baselineskip}
\setlength{\parindent}{15pt}
\title{A train ride through DBIx::Class and its ecosystem}
\date{}
\author{Stefan Hornburg (Racke)}
\subtitle{}
% https://groups.google.com/d/topic/comp.text.tex/6fYmcVMbSbQ/discussion
\hypersetup{%
pdfencoding=auto,
pdftitle={A train ride through DBIx::Class and its ecosystem},%
pdfauthor={Stefan Hornburg (Racke)},%
pdfsubject={},%
pdfkeywords={DBIx::Class; Deployment Handler; dbicdump}%
}
\begin{document}
\begin{titlepage}
\strut\vskip 2em
\begin{center}
{\usekomafont{title}{\huge A train ride through DBIx::Class and its ecosystem\par}}%
\vskip 1em
\vskip 2em
{\usekomafont{author}{Stefan Hornburg (Racke)\par}}%
\vskip 1.5em
\vfill
\strut\par
\end{center}
\end{titlepage}
\cleardoublepage
\tableofcontents
% start a new right-handed page
\cleardoublepage
\chapter{Searching}
The search parameters are in the first parameter of the \emph{search} method.
\section{NOT NULL}
\begin{alltt}
\$rs->search(\{
vendor => \{ '!=', undef \}
\});
\end{alltt}
Or with helpers:
\begin{alltt}
\$rs->not\_null('vendor');
\end{alltt}
\section{Distinct}
If you want to know whether the value for a column is \emph{distinct} and retrieve the value at the \emph{same time}, look at the following example:
\begin{alltt}
my \$distinct\_status = \$rs->search(\{\}, \{
columns => [ qw/status/ ],
group\_by => [ qw/status/ ],
\});
if ( \$distinct\_status->count == 1 ) \{
my \$ol\_status = \$distinct\_status->first->status;
\}
\end{alltt}
\section{Comparing columns}
We want to find all records where the columns
\texttt{username} and \texttt{replaced\_by} contain the same
value.
Here is the corresponding SQL query:
\begin{alltt}
select * from userdb where username = replaced\_by;
\end{alltt}
We can't use a bareword on the right hand side
in our search parameters, so we have to resort
to use the \texttt{-ident} operator:
\begin{alltt}
my \$user\_rs = \$schema->resultset('User');
my \$circular = \$user\_rs->search(\{
username => \{ -ident => 'replaced\_by' \},
\});
\end{alltt}
\chapter{Sorting}
\begin{alltt}
my \$sorted\_rs = \$schema->resultset('Company')->search(
\{\},
\{ order\_by => \{ -asc => 'name' \} \},
);
\end{alltt}
\chapter{Creating schema from existing database}
You can use \texttt{dbicdump} to create a "boilerplate" schema from an
existing database.
Install first \href{https://metacpan.org/pod/DBIx::Class::Schema::Loader}{DBIx::Class::Schema::Loader}, e.g. with \texttt{cpanm}:
\begin{alltt}
cpanm DBIx::Class::Schema::Loader
\end{alltt}
Now you run \texttt{dbicdump}:
\begin{alltt}
dbicdump -o dump\_directory=/home/dance/TravelDance/lib \textbackslash{}
TravelDance::Schema \textbackslash{}
dbi:Pg:dbname=perldance
\end{alltt}
Add database credentials (username and password):
\begin{alltt}
dbicdump -o dump\_directory=/home/dance/TravelDance/lib \textbackslash{}
TravelDance::Schema \textbackslash{}
dbi:Pg:dbname=sympa sympa "nevairbe"
\end{alltt}
You can also include useful components:
\begin{alltt}
dbicdump -o dump\_directory=/home/dance/TravelDance/lib
-o components='["InflateColumn::DateTime"]'
TravelDance::Schema
dbi:Pg:dbname=perldance
\end{alltt}
\section{Remove comments generated by DBIx::Class::Schema::Loader.}
\begin{alltt}
perl -i -ne 'print if \$\_ !\textasciitilde{} /(Created by DBIx::)\textbar{}(DO NOT MODIFY)\textbar{}(You can replace)/;' `find lib -name '*.pm'`
\end{alltt}
\chapter{Debugging}
\begin{alltt}
export DBIC\_TRACE=1
\end{alltt}
\chapter{Result and ResultSet classes}
\section{Set-based DBIx::Class}
Excellent article from Arthur Axel "fREW" Schmidt explaining the features of the
ResultSet class:
\href{http://www.perladvent.org/2012/2012-12-21.html}{\texttt{http://www.perladvent.org/2012/2012-12-21.html}}
\section{Reaching out to another ResultSet class}
If you want to reach out from a method in a result or resultset class to another
resultset class, you can do the following:
\begin{alltt}
my \$rset\_message = \$self->result\_source->schema->resultset("Message");
\end{alltt}
\section{Result classes}
\subsection{Updating result objects}
\hyperdef{amuse}{discardchanges}{}%
\label{textamuse:discardchanges}%
Sometimes you want to update your result object from the database record, e.g. after
creating a database record to get the values of columns with are automatically updated such as timestamps:
\begin{alltt}
\$user->create(\{username => 'nevairbe@linuxia.de'\});
\$user->discard\_changes;
print \$user->last\_modified, "\textbackslash{}n";
\end{alltt}
\section{Flatten a object}
This turns the object into a hash:
\begin{alltt}
my \%user\_hash = \$user->get\_inflated\_columns;
\end{alltt}
\section{Predefined searches}
Predefined searches encapsulate business logic inside the schema. If you use
them in the consumers of the schema, you can change the business logic without
changing the code of the consumes.
Example:
\begin{alltt}
sub not\_amazon \{
my (\$self) = @\_;
my \$user\_rs = \$self->search(\{
username => \{ -not\_like => '\%@marketplace.amazon\%' \},
\});
return \$user\_rs;
\}
\end{alltt}
\section{Relationships}
\subsection{CASCADE actions}
\section{Indices}
Add indices to your result classes like that:
\begin{alltt}
sub sqlt\_deploy\_hook \{
my (\$self, \$sqlt\_table) = @\_;
\$sqlt\_table->add\_index(name => 'idx\_product\_canonical\_sku',
fields => ['canonical\_sku']);
\}
\end{alltt}
\begin{alltt}
sub sqlt\_deploy\_hook \{
my (\$self, \$sqlt\_table) = @\_;
\$sqlt\_table->add\_index(name => 'idx\_product\_canonical\_sku',
fields => ['canonical\_sku']
options => \{ where => q\{canonical\_sku <> ''\},
);
\}
\end{alltt}
\chapter{Components}
\hyperdef{amuse}{components}{}%
\label{textamuse:components}%
DBIx::Class uses \href{https://metacpan.org/pod/Class::C3::Componentised}{Class::C3::Componentised}
to allow mix-ins (components) to be added to the
various classes that together comprise the schema definition.
There are three different types of components:
\begin{itemize}
\item\relax
\emph{Schema} class components
\item\relax
\emph{Result} class components
\item\relax
\emph{ResultSet} class components
\end{itemize}
Many components exist for all of these three kinds.
\section{Schema Components}
You can add components like helpers into your main schema class with the method \emph{load\_components}:
\begin{alltt}
package Interchange6::Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema::Config';
\_\_PACKAGE\_\_->load\_components(
'Helper::Schema::DateTime',
'Helper::Schema::QuoteNames'
);
\end{alltt}
\section{Using Result Class Components}
In a single result class:
\begin{alltt}
package TravelDance::Schema::Result::User;
use TravelDance::Schema::Candy -components => [qw(
InflateColumn::DateTime
PassphraseColumn
TimeStamp
)];
\end{alltt}
In order to add a result class component to \emph{all} result classes you
can add these to the list
in \texttt{TravelDance::Schema::Candy}'s \texttt{parse\_arguments}.
\section{ResultSet Components}
In order to be able to add a component to all ResultSet classes in the schema \emph{Interchange6::Schema} we need to do:
\begin{enumerate}[1.]
\item\relax
create parent class \emph{Interchange6::Schema::ResultSet}
\item\relax
set as default resultset class in \emph{Interchange6::Schema}
\item\relax
use for custom resultset classes \emph{Interchange6::Schema::ResultSet::User}
\end{enumerate}
\subsection{Create parent class}
\begin{alltt}
package Interchange6::Schema::ResultSet;
use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
\_\_PACKAGE\_\_->load\_components(
'Helper::ResultSet::CorrelateRelationship',
'Helper::ResultSet::Me',
'Helper::ResultSet::Random',
'Helper::ResultSet::SetOperations',
'Helper::ResultSet::Shortcut'
);
\end{alltt}
\subsection{Set as default ResultSet class}
\begin{alltt}
package Interchange6::Schema;
\_\_PACKAGE\_\_->load\_namespaces(
default\_resultset\_class => 'ResultSet'
);
\end{alltt}
\chapter{Exceptions}
Throw an exception in your result class:
\begin{alltt}
unless ( \$self->valid\_to > \$self->valid\_from ) \{
\$schema->throw\_exception("valid\_to is not later than valid\_from");
\}
\end{alltt}
\chapter{Helpers}
\begin{figure}[htbp!]
\centering
\includegraphics[keepaspectratio=true,height=0.85\textheight,width=\textwidth]{s-h-stefan-hornburg-racke-a-train-ride-through-dbi-1.png}
\caption[]{\noindent Arthur Axel "fREW" Schmidt}
\end{figure}
\chapter{Adding accessors to the schema}
\hyperdef{amuse}{addschemaaccessors}{}%
\label{textamuse:addschemaaccessors}%
Vital information utilized in the schema and its methods isn't always available in the database, especially if you use it within web applications.
For example, we need to know which user is currently logged in to determine
\begin{itemize}
\item\relax
product prices
\item\relax
whether to restrict searches
\end{itemize}
So we add accessors to the \texttt{Schema.pm} module:
\begin{alltt}
\_\_PACKAGE\_\_->mk\_group\_ro\_accessors(
inherited => (
[ 'current\_user' => '\_ic6\_current\_user' ],
)
);
\_\_PACKAGE\_\_->mk\_group\_wo\_accessors(
inherited => (
[ 'set\_current\_user' => '\_ic6\_current\_user' ],
)
);
\end{alltt}
In our web application, we are using the accessor in the following cases:
\begin{itemize}
\item\relax
start of processing a HTTP request
\item\relax
user logs in
\item\relax
user logs out
\end{itemize}
\chapter{Deployment Handler}
In order to start with deployment handler, add a version number to your main
schema module:
\begin{alltt}
package TravelDance::Schema;
=head1 NAME
TravelDance::Schema - Database schema class loader for TravelDance
=cut
use warnings;
use strict;
use base 'DBIx::Class::Schema';
use TravelDance::Schema::Populate::CountryLocale;
our \$VERSION = 1;
\end{alltt}
To avoid unnecessary headaches, please use consecutive natural numbers.
\section{Scripts}
A number of useful scripts:
\begin{description}
\item[{dh-prepare-version-storage}]
prepares table for DH
\item[{dh-install-version-storage}]
install and populates table for DH
\item[{dh-prepare-upgrade}]
prepares upgrade
\item[{dh-upgrade}]
performs upgrade
\end{description}
\section{Adding relationships}
Please make sure that the definition of the foreign key matches the primary key in the other table in terms of data type, length and default value.
\begin{alltt}
DBI Exception: DBD::mysql::db do failed: Can't create table 'linuxia.\#sql-6f14\_e'
(errno: 150) [for Statement "ALTER TABLE orderline CHANGE COLUMN username username
varchar(255) NOT NULL, ADD INDEX orderline\_idx\_username (username), ADD CONSTRAINT
orderline\_fk\_username FOREIGN KEY (username) REFERENCES userdb (username) ON DELETE SET NULL"]
\end{alltt}
An other reason for such an error are rows that violate the foreign key, e.g. a value for \emph{username} in \emph{orderline}, which doesn't appear in \emph{orderline}.
% begin final page
\clearpage
% new page for the colophon
\thispagestyle{empty}
\begin{center}
Linuxia Wiki
\strut
\end{center}
\strut
\vfill
\begin{center}
Stefan Hornburg (Racke)
A train ride through DBIx::Class and its ecosystem
\bigskip
\bigskip
\textbf{wiki.linuxia.de}
\end{center}
% end final page with colophon
% end closing pages
\end{document}
% No format ID passed.