% $Author: gpolito $
% $Date: 2011-07-09 17:14:33 -0300 (Sat, 09 Jul 2011) $
% $Revision: 1 $

%=================================================================
\ifx\wholebook\relax\else
% --------------------------------------------
% Lulu:
	\documentclass[a4paper,10pt,twoside]{book}
	\usepackage[
		papersize={6.13in,9.21in},
		hmargin={.75in,.75in},
		vmargin={.75in,1in},
		ignoreheadfoot
	]{geometry}
	\input{../common.tex}
	\setboolean{lulu}{true}
% --------------------------------------------
% A4:
%	\documentclass[a4paper,11pt,twoside]{book}
%	\input{../common.tex}
%	\usepackage{a4wide}
% --------------------------------------------
    \graphicspath{{figures/} {../figures/}}
	\begin{document}
\fi
%=================================================================
%\renewmessage{\nnbb}[2]{} % Disable editorial comments
\sloppy

%=================================================================
%\renewcommand{\nnbb}[2]{#2} % Disable editorial comments

\chapter{DBXTalk}
\chapterauthor{\authorguille{}}

DBXTalk is a database driver that allows interaction with major relational database engines such as
Oracle and MSSQL, apart from those which are open source, like PostgreSQL and MySQL.

Moreover, this driver is integrated with GLORP, enabling a complete and open-source
solution to relational database access.  To do this, DBXTalk uses a library called OpenDBX.
\section{DBXTalk Driver Architecture}
The DBXTalk Driver relies on several components in order to connect to different relational databases:
\begin{itemize}
\item The OpenDBXDriver package talks to the OpenDBX library and handles the engines differences.
\item OpenDBX is a C library which stands as an adapter between the different database engines 
and our Pharo image, and provides a common interface to interact with through FFI.
\item We will need the corresponding client database library that OpenDBX will talk to.
\end{itemize}
\includegraphics[width=\linewidth]{dbx_architecture.png}


\section{Installing DBXTalk OpenDBX Driver}

In Order to install DBXTalk library, we need to install the previously detailed components.

\subsection*{Install OpenDBX Driver}

As we already introduced, an important part of DBXTalk architecture is the OpenDBX Driver, which allows us
to communicate with different relational database engines with a common approach.  OpenDBX is an open source library,
licensed as LGPL.

It's installation instructions for different engines and operative systems can be found on \url{http://www.linuxnetworks.de/doc/index.php/OpenDBX}.

You can also participate in the Issue Tracker and mailing list of this proyect to ask questions and contribute.

Issue Tracker: \url{http://bugs.linuxnetworks.de/index.php?project=3}
Mailing List: \url{https://lists.sourceforge.net/lists/listinfo/libopendbx-devel}

\subsection*{Install Smalltalk OpenDBXDriver}

The OpenDBXDriver written in Smalltalk is also needed in order to use the DBXTalk suite.  The easiest way to download it
is using it's metacello configuration, ensuring all it's dependencies to be loaded, such as FFI.  This can be performed executing
in a workspace the following script:

\begin{code}{}
Gofer it
	squeaksource: 'DBXTalk';
	package: 'ConfigurationOfOpenDBXDriver';
	load.
	
(((Smalltalk at: #ConfigurationOfOpenDBXDriver) perform: #project) perform: #version: with: #stable) load
\end{code}

\subsection*{Ensuring everything was ok}

The OpenDBXDriver package comes along with lots of tests cases you can use to test the your installation.  But before running
 the tests, you may want to configure the database connection settings in order to match your actual configuration.
To do that, just go to the corresponsing DBX*Engine*Facility class in the OpenDBXDriverTests package, and modify the
createConnection method to suite your needs.

For example, if I want to configure the tests to run in my MySql database, I should go to DBXMySQLFacility>>createConnection, to see the following:

\begin{code}{}
createConnection
    self connectionSettings: (DBXConnectionSettings
			    host: 'localhost'
			    port: '3306'
			    database: 'sodbxtest'
			    userName: 'sodbxtest'
			    userPassword: 'sodbxtest').
    self platform: DBXMySQLBackend new.
\end{code}

There I can change the host, port, database, username and password to connecto to my own database.

\section{Getting down to business with DBXTalk OpenDBXDriver}

Now you have already installed your database driver, you are ready to use it and build applications with it!  But you need to know the basics in order to get the mayor leagues.  In the following section you will be introduced in creating connections, execute SQL statements and handle transactions.

\section{Creating a connection}

The first step in order to execute a SQL statement in a database engine is to create a connection to that database.  The DBXTalk OpenDBXDriver uses 3 objects to fulfill this objective: a connection object, which uses a platform/backend object and a connection settings object.

For example, to open a connection to a MySQL database called sodbxtest, located in localhost you can use the following code:

\begin{code}{}
settings := DBXConnectionSettings
			host: 'localhost'
			port: '3306'
			database: 'sodbxtest'
			userName: 'sodbxtest'
			userPassword: 'sodbxtest'.
platform := DBXMySQLBackend new.
connection := DBXConnection platform: platform settings: settings.

connection connect.
connection open.
\end{code}

As you can see, after creating the connection object, you have to send it first the \#connect message to let OpenDBX create all the needded internal structure.  After that, you can send it the \#open message to associate the connection to the desired database, verify the credentials and enable us to start sending querys.

The main reason to separate these two operations is to configure some extra options before the connection is open.  If you do not want to specify any of these options, you can send the \#connectAndOpen message to the connection instead:

\begin{code}{}
connection connectAndOpen.
\end{code}

\subsection{Connection special options}

As we told you in the last section, you can specify some special options to the connection before it is opened.  All of these options can be enabled through the following helper methods of the connection.  What they do is to try to enable the desired option, and return a boolean indicating if the option succeded or not.

Each option may or not succeed because it depends on your database engine support for it.

\begin{itemize}
\item \#enableCompression

It tries to enable the Compression option.

\item \#enableEncryption: aEncryptionOption

It tries to enable the Encryption option.  The possible encryption option values are:
\begin{itemize}
    \item DBXEncryptionValues never
    \item DBXEncryptionValues try
    \item DBXEncryptionValues always
\end{itemize}

\item \#enableMultipleStatements

It tries to enable Multiple Statements option.

\item \#enablePagedResults

It tries to enable paged results.

\item \#enableSpecialModes: modes 

	It tries to enable specific modes. For example MySQL special modes (\url{http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html}).

\end{itemize}

\subsection{Executing SQL statements}

The execution of a SQL statement in the database of your desire is performed sending the \#execute: message -or \#executeMultiStatement: if enabled- to the connection object.  So, once you have your connection established and open, you can try evaluating in a workspace code like:

\begin{code}{}
"we create a table to store our trading card game cards in our MySQL database"
connection execute: 'CREATE TABLE CARD(ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100))'

"we insert some cards into our database"
connection execute: 'INSERT INTO CARDS (NAME) VALUES(''Giant Growth'')'.
connection execute: 'INSERT INTO CARDS (NAME) VALUES(''Llanowar Elves'')'.

"If we did enable multistatements before opening the connection we can do some inserts at the same time"
connection executeMultiStatement: 'INSERT INTO CARDS (NAME) VALUES(''Rancor'');
                                   INSERT INTO CARDS (NAME) VALUES(''Counterspell'')'.

"we execute an invalid SQL statement to see how it raises a DBXError"
connection execute: 'some invalid sql statement'.
\end{code}

\subsection{Fetching results}
So far we have only executed statements without looking at their results.  Each statement execution has a Result, which may be one of DBXResult, DBXResultSet and DBXMultiStatementResultSetIterator.

A DBXResult is obtained when executing a SQL statement which does not have a set of rows as a result -such as a \emph{CREATE TABLE}, \emph{INSERT} or \emph{UPDATE} operation.  You can ask it for its \#rowsAffected to know how many rows were affected in the operation.

\begin{code}{}
result := connection execute: 'UPDATE CARDS SET NAME=''CounterSpell'' where NAME=''Counterspell'' '.
Transcript show: result rowsAffected.
\end{code}

A SQL statement with rows as a result -such as a \emph{SELECT} statement- brings a DBXResultSet as the result.  A DBXResultSet is a consumable set of rows.  This means that onces you've consumed all the rows in the DBXResultSet, they will not be available any more.  You can work with a DBXResultSet in several ways:

\begin{itemize}
\item Delegate row iteration to the result set:
\begin{code}{}
cardsResultSet := connection execute: 'SELECT * FROM CARDS'.
cardsResultSet rowsDo: [ :row | Transcript show: (row valueAt: 1) ]
\end{code}
\item Getting all rows from a result set:
\begin{code}{}
cardsResultSet := connection execute: 'SELECT * FROM CARDS'.
rows := cardsResultSet rows
rows do: [ :row | Transcript show: (row valueAt: 1) ]
\end{code}
\item Fetching one row at a time:
\begin{code}{}
cardsResultSet := connection execute: 'SELECT * FROM CARDS'.
[ row := cardsResultSet nextRow.
  row notNil ] 
    whileTrue: [ Transcript show: (row valueAt: 1); cr ]
\end{code}
\end{itemize}


When accessing a ResultSet rows, we get some DBXRow instances to play with.  A DBXRow is the model of a database row, and each of the values for it's columns can be accessed in two formats: the raw format, which is the string representation of the value, sent by the database, and a converted format, which is the Pharo object representation for that object.
The DBXRow objects understand some of the following messages:

\begin{itemize}
    \item \#valueAt: retrieves the converted value at the column in the given index.
    \item \#values retrieves a sequenceable collection with all the converted values of the row.
    \item \#rawValueAt: retrieves the raw value at the column in the given index.
    \item \#rawValues retrieves a sequenceable collection with all the raw values of the row.
\end{itemize}

Some examples of the behavior of a DBXRow are the following:
\begin{code}{}
myRow valueAt: 1. -> 1          "First column is INTEGER and contains a 1. It retrieves a Pharo SmallInteger"
myRow rawValueAt: 1. -> '1'     "First column is INTEGER and contains a 1. It retrieves an string"

myRow values.    -> #(1 'Llanowar Elves')   "A collection with every row value converted to Pharo objects."
myRow rawValues. -> #('1' 'Llanowar Elves') "A collection with every row raw value."
\end{code}


Finally, the other kind of result we can get from a SQL statement is a DBXMultiStatementResultSetIterator.  It stands as a DBXResultSet container, for multi-statement querys.  You can browse the results using the following convenience methods.

\begin{code}{}
myMultistatementResult allResultsDo: [ :aResult | "doSometing here with the result" ].  "internal iteration of the results."

[result := myMultistatementResult next. result notNil] 
    whileTrue: [ :aResult | "doSometing here with the result" ].  "iterating the results one by one."
\end{code}

%=========================================================
\ifx\wholebook\relax\else
   \bibliographystyle{jurabib}
   \nobibliography{scg}
   \end{document}
\fi
%=========================================================



%%% Local Variables: 
%%% coding: utf-8
%%% mode: latex
%%% TeX-master: Lint.tex
%%% TeX-PDF-mode: t
%%% End:

