From 3d3f8d3832921f99daf8ce1953304763c2e76c62 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Apr 2006 06:22:09 +0000 Subject: Importing SQLMap + sample + docs. --- docs/sqlmap/latex/ch1.tex | 43 ++ docs/sqlmap/latex/ch2.tex | 151 +++++++ docs/sqlmap/latex/ch3.tex | 714 +++++++++++++++++++++++++++++ docs/sqlmap/latex/ch4.tex | 306 +++++++++++++ docs/sqlmap/latex/ch5.tex | 941 +++++++++++++++++++++++++++++++++++++++ docs/sqlmap/latex/ch6.tex | 119 +++++ docs/sqlmap/latex/ch7.tex | 3 + docs/sqlmap/latex/ch8.tex | 413 +++++++++++++++++ docs/sqlmap/latex/ch9.tex | 302 +++++++++++++ docs/sqlmap/latex/diagram.pdf | Bin 0 -> 521561 bytes docs/sqlmap/latex/example1.png | Bin 0 -> 236887 bytes docs/sqlmap/latex/grid1.png | Bin 0 -> 275250 bytes docs/sqlmap/latex/grid2.png | Bin 0 -> 218210 bytes docs/sqlmap/latex/sqlmap.tex | 143 ++++++ docs/sqlmap/latex/sqlmap_tut.tex | 96 ++++ docs/sqlmap/latex/tut1.tex | 257 +++++++++++ docs/sqlmap/latex/tut2.tex | 128 ++++++ docs/sqlmap/latex/tut3.tex | 227 ++++++++++ 18 files changed, 3843 insertions(+) create mode 100644 docs/sqlmap/latex/ch1.tex create mode 100644 docs/sqlmap/latex/ch2.tex create mode 100644 docs/sqlmap/latex/ch3.tex create mode 100644 docs/sqlmap/latex/ch4.tex create mode 100644 docs/sqlmap/latex/ch5.tex create mode 100644 docs/sqlmap/latex/ch6.tex create mode 100644 docs/sqlmap/latex/ch7.tex create mode 100644 docs/sqlmap/latex/ch8.tex create mode 100644 docs/sqlmap/latex/ch9.tex create mode 100644 docs/sqlmap/latex/diagram.pdf create mode 100644 docs/sqlmap/latex/example1.png create mode 100644 docs/sqlmap/latex/grid1.png create mode 100644 docs/sqlmap/latex/grid2.png create mode 100644 docs/sqlmap/latex/sqlmap.tex create mode 100644 docs/sqlmap/latex/sqlmap_tut.tex create mode 100644 docs/sqlmap/latex/tut1.tex create mode 100644 docs/sqlmap/latex/tut2.tex create mode 100644 docs/sqlmap/latex/tut3.tex (limited to 'docs/sqlmap/latex') diff --git a/docs/sqlmap/latex/ch1.tex b/docs/sqlmap/latex/ch1.tex new file mode 100644 index 00000000..405256ef --- /dev/null +++ b/docs/sqlmap/latex/ch1.tex @@ -0,0 +1,43 @@ +\chapter{Introduction} + +\section{Overview} + +The SQLMap DataMapper framework makes it easier to use a database with a PHP +application. SQLMap DataMapper couples objects with stored procedures or SQL +statements using a XML descriptor. Simplicity is the biggest advantage of the +SQLMap DataMapper over object relational mapping tools. To use SQLMap +DataMapper you rely on your own objects, XML, and SQL. There is little to +learn that you don't already know. With SQLMap DataMapper you have the full +power of both SQL and stored procedures at your fingertips. + +The SQLMap for PHP is based on iBATIS.NET - DataMapper Application Framework +from http://ibatis.apache.org/.The PHP version support most of the features +found in iBATIS.NET exception the following: + +\begin{itemize} + \item Dynamic SQL. + \item Distributed Transactions. +\end{itemize} + +\section{What's covered here} + +This Guide covers the PHP implementations of SQLMap DataMapper. The Java and +.NET implementation offers the same services with some changes in the API. + +Since SQLMap relies on an XML descriptor to create the mappings, much of the +material applies to both implementations. + +For installation instructions, see the section called the SQLMap PHP Developer +Guide. + +A Tutorial is also available. We recommend reviewing the Tutorial for your +platform before reading this Guide. + +\section{Support} + +Add Forum and Trac. + +\section{Disclaimer} +SQLMap MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS +DOCUMENT. The names of actual companies and products mentioned herein may be +the trademarks of their respective owners. diff --git a/docs/sqlmap/latex/ch2.tex b/docs/sqlmap/latex/ch2.tex new file mode 100644 index 00000000..da135e9c --- /dev/null +++ b/docs/sqlmap/latex/ch2.tex @@ -0,0 +1,151 @@ +\chapter{The Big Picture} +\section{Introduction} +SQLMap is a simple but complete framework that makes it easy for you to map +your objects to your SQL statements or stored procedures. The goal of the +SQLMap framework is to obtain 80\% of data access functionality using only +20\% of the code. + +\section{What does it do?} +Developers often create maps between objects within an application. One +definition of a Mapper is an ``object that sets up communication between two +independent objects.'' A Data Mapper is a ``layer of mappers that moves data +between objects and a database while keeping them independent of each other +and the mapper itself.'' [Patterns of Enterprise Architecture, ISBN +0-321-12742-0]. + +You provide the database and the objects; SQLMap provides the mapping layer +that goes between the two. + +\section{How does it work?} + +Your programming platform already provides a capable library for accessing +databases, whether through SQL statements or stored procedures. But developers +find several things are still hard to do well when using ``stock'' PHP +function including: + +Separating SQL code from programming code Passing input parameters to the +library classes and extracting the output Separating data access classes from +business logic classes Caching often-used data until it changes Managing +transactions and many more -- by using XML documents to create a mapping +between a plain-old object and a SQL statement or a stored procedure. The +``plain-old object'' can be any PHP object. + +\begin{mybox}{Tip:} +The object does not need to be part of a special object hierarchy or implement +a special interface. (Which is why we call them ``plain-old'' objects.) +Whatever you are already using should work just fine. +\end{mybox} + +\begin{figure}[!h] + \centering + \includegraphics[width=0.65\textwidth]{diagram} + \caption{SQLMap DataMapper work flow} + \label{fig:diagram} +\end{figure} + +Here's a high level description of the work flow diagrammed by +Figure~\ref{fig:diagram}: Provide a parameter, either as an object or a +primitive type. The parameter can be used to set runtime values in your SQL +statement or stored procedure. If a runtime value is not needed, the parameter +can be omitted. + +Execute the mapping by passing the parameter and the name you gave the +statement or procedure in your XML descriptor. This step is where the magic +happens. The framework will prepare the SQL statement or stored procedure, set +any runtime values using your parameter, execute the procedure or statement, +and return the result. + +In the case of an update, the number of rows affected is returned. In the case +of a query, a single object, or a collection of objects is returned. Like the +parameter, the result object, or collection of objects, can be a plain-old +object or a primitive type. + +So, what does all this look like in your source code? Here's how you might +code the insert of a ``lineItem'' object into your database. + +\begin{verbatim} +TMapper::instance()->insert("InsertLineItem", $lineItem); +\end{verbatim} + +If your database is generating the primary keys, the generated key can be +returned from the same method call, like this: + +\begin{verbatim} +$myKey = TMapper::instance()->insert("InsertLineItem", $lineItem); +\end{verbatim} + +Example~\ref{example:2.1} shows an XML descriptor for ``InsertLineItem''. +\begin{example}\label{example:2.1} +The ``InsertLineItem'' descriptor +\begin{verbatim} + + INSERT INTO [LinesItem] + (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice) + VALUES + (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#) + + select @@IDENTITY as value + + +\end{verbatim} +\end{example} + +The \tt{} stanza returns an auto-generated key from a SQL Server +database (for example). If you need to select multiple rows, SQLMap can return +a list of objects, each mapped to a row in the result set: +\begin{verbatim} +$productList = Mapper::instance()->queryForList("selectProduct",$categoryKey); +\end{verbatim} +Or just one, if that's all you need: +\begin{verbatim} +$product = Mapper::instance()->queryForObject("selectProduct",$categoryKey); +\end{verbatim} + +Of course, there's more, but this is SQLMap from 10,000 meters. (For a longer, +gentler introduction, see the Tutorial.) Section~\ref{section:3} describes the +Data Map definition files -- where the statement for ``InsertLineItem'' would +be defined. The Developers Guide for PHP (Section~\ref{section:4}) describes +the "bootstrap" configuration file that exposes SQLMap to your application. + +\section{Is SQLMap the best choice for my project?} +SQLMap is a Data Mapping tool. Its role is to map the columns of a database +query (including a stored procedure) to the properties of an object. If your +application is based on business objects (including array or lists of +objects), then SQLMap can be a good choice. SQLMap is an even better choice +when your application is layered, so that that the business layer is distinct +from the user-interface layer. + +Under these circumstances, another good choice would be an Object/Relational +Mapping tool (OR/M tool), like [...]. Other products in this category are +[...] and [...] . An OR/M tool generates all or most of the SQL for you, +either beforehand or at runtime. These products are called OR/M tools because +they try to map an object graph to a relational schema. + +SQLMap is not an OR/M tool. SQLMap helps you map objects to stored procedures +or SQL statements. The underlying schema is irrelevant. An OR/M tool is great +if you can map your objects to tables. But they are not so great if your +objects are stored as a relational view rather than as a table. If you can +write a statement or procedure that exposes the columns for your object, +regardless of how they are stored, SQLMap can do the rest. + +So, how do you decide whether to OR/M or to DataMap? As always, the best +advice is to implement a representative part of your project using either +approach, and then decide. But, in general, OR/M is a good thing when you +\begin{itemize} + \item Have complete control over your database implementation. + \item Do not have a Database Administrator or SQL guru on the team. + \item Need to model the problem domain outside the database as an object graph. +\end{itemize} +Likewise, the best time to use a Data Mapper, like SQLMap, is when: +\begin{itemize} + \item You do not have complete control over the database implementation, or want to +continue to access a legacy database as it is being refactored. + \item You have database administrators or SQL gurus on the team. + \item The database is being used to model the problem domain, and the application's +primary role is to help the client use the database model. +\end{itemize} + +In the end, you have to decide what's best for your project. If a OR/M tool +works better for you, that's great! If your next project has different needs, +then we hope you give SQLMap another look. If SQLMap works for you now: +Excellent! diff --git a/docs/sqlmap/latex/ch3.tex b/docs/sqlmap/latex/ch3.tex new file mode 100644 index 00000000..79b2c42a --- /dev/null +++ b/docs/sqlmap/latex/ch3.tex @@ -0,0 +1,714 @@ +\chapter{Working with Data Maps}\label{section:3} +\section{Introduction} + +If you want to know how to configure and install SQLMap, see the Developer +Guide section~\ref{section:4} . But if you want to know how SQLMap really +works, continue from here. + +The Data Map definition file is where the interesting stuff happens. Here, you +define how your application interacts with your database. As mentioned, the +Data Map definition is an XML descriptor file. By using a service routine +provided by SQLMap, the XML descriptors are rendered into a client object (or +Mapper). To access your Data Maps, your application calls the client object +and passes in the name of the statement you need. + +The real work of using SQLMap is not so much in the application code, but in +the XML descriptors that SQLMap renders. Instead of monkeying with application +source code, you monkey with XML descriptors instead. The benefit is that the +XML descriptors are much better suited to the task of mapping your object +properties to database entities. At least, that's our own experience with our +own applications. Of course, your mileage may vary. + +\section{What's in a Data Map definition file, anyway?} + +If you read the Tutorial, you've already seen some simple Data Map examples, +like the one shown in Example~\ref{example:2.1}. + +\begin{example}\label{example:3.1} + A simple Data Map (PHP) + \begin{verbatim} + + + + INSERT INTO [LinesItem] + (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice) + VALUES + (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#) + + + \end{verbatim} +\end{example} +This map takes some properties from a \tt{LineItem} instance and merges the +values into the SQL statement. The value-add is that our SQL in separated from +our program code, and we can pass our \tt{LineItem} instance directly to a +library method: +\begin{verbatim} +TMapper::instance()->insert("InsertLineItem",$lineItem); +\end{verbatim} +No fuss, no muss. Likewise, see Example\ref{example:3.2} for a simple select +statement. + +\begin{mybox}{Info:} +\textbf{A Quick Glance at Inline Parameters} + +Say we have a mapped statement element that looks like this: +\begin{verbatim} + + insert into Products (Product_Id, Product_Description) + values (#Id#, #Description#); + +\end{verbatim} +The inline parameters here are \tt{\#Id\#} and \tt{\#Description\#}. Let's +also say that we have an object with the properties \tt{Id} and +\tt{Description}. If we set the object properties to $5$ and ``dog'', +respectively, and passed the object to the mapped statement, we'd end up with +a runtime query that looked like this: +\begin{verbatim} +insert into Products (Product_Id, Product_Description) values (5, 'dog'); +\end{verbatim} +For more about inline parameters, see Chapter~\ref{section:3.4}. +\end{mybox} + +But, what if you wanted some ice cream with that pie? And maybe a cherry on +top? What if we wanted to cache the result of the select? Or, what if we +didn't want to use SQL aliasing or named parameters. (Say, because we were +using pre-existing SQL that we didn't want to touch.) +Example~\ref{example:3.2} shows a Data Map that specifies a cache, and uses a +\tt{} and a \tt{} to keep our SQL pristine. + +\begin{example}\label{example:3.2} +A Data Map definition file with some bells and whistles +\begin{verbatim} + + + + + + + + + + + + + + + + + + + + +\end{verbatim} +\end{example} +In Example~\ref{example:3.2}, \tt{} maps the SQL ``?'' to the +product \tt{Id} property. The \tt{} maps the columns to our object +properties. The \tt{} keeps the result of the last one thousand of +these queries in active memory for up to 24 hours. + +Example~\ref{example:3.2} is longer and more complex than +Example~\ref{example:3.1}, but considering what you get in return, it seems +like a fair trade. (A bargain even.) + +Many agile developers would start with something like +Example~\ref{example:3.1} and add features like caching later. If you changed +the Data Map from Example~\ref{example:3.1} to Example~\ref{example:3.2}, you +would not have to touch your application source code at all. You can start +simple and add complexity only when it is needed. + +A single Data Map definition file can contain as many Cache Models, Type +Aliases, Result Maps, Parameter Maps, and Mapped Statements (including stored +procedures), as you like. Everything is loaded into the same configuration, so +you can define elements in one Data Map and then use them in another. Use +discretion and organize the statements and maps appropriately for your +application by finding some logical way to group them. + +\section{Mapped Statements} +Mapped Statements can hold any SQL statement and can use Parameter Maps and +Result Maps for input and output. (A stored procedure is a specialized form of +a statement. See section~\ref{section:3.3.1} and \ref{section:3.3.2} for more +information.) + +If the case is simple, the Mapped Statement can reference the parameter and +result classes directly. Mapped Statements support caching through reference +to a Cache Model element. The following example shows the syntax for a +statement element. + +\begin{example}\label{example:3.3} +Statement element syntax +\begin{verbatim} + + + select * from Products where Product_Id = [?|#propertyName#] + order by [$simpleDynamic$] + + +\end{verbatim} +\end{example} +In Example~\ref{example:3.3}, the [bracketed] parts are optional, and some +options are mutually exclusive. It is perfectly legal to have a Mapped +Statement as simple as shown by Example~\ref{example:3.4}. + +\begin{example}\label{example:3.4} +A simplistic Mapped Statement +\begin{verbatim} + + insert into Products (Product_Id, Product_Description) values (1, "Shih Tzu") + +\end{verbatim} +\end{example} + +Example~\ref{example:3.4} is obviously unlikely, unless you are running a +test. But it does shows that you can use SQLMap to execute arbitrary SQL +statements. More likely, you will use the object mapping features with +Parameter Maps (Chapter~\ref{section:3.4}) and Result Maps +(Chapter~\ref{section:3.5}) since that's where the magic happens. + +\subsection{Statement Types}\label{section:3.3.1} +The \tt{} element is a general ``catch all'' element that can be +used for any type of SQL statement. Generally it is a good idea to use one of +the more specific statement-type elements. The more specific elements provided +better error-checking and even more functionality. (For example, the insert +statement can return a database-generated key.) Table~\ref{table:3.1} +summarizes the statement-type elements and their supported attributes and +features. The various attributes used by statement-type elements are covered +in Section~\ref{section:3.3.4}. +\begin{table}[!hpt] +\caption{The six statement-type elements } \label{table:3.1} + \centering +\begin{tabular}{|l|l|l|l|} + \hline + \textbf{Statement Element} & + \textbf{Attribute} & + \textbf{Child Elements} & + \textbf{Methods} \\ + \hline + % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ... + \tt{} & + \begin{minipage}{0.17\textwidth} + \vspace{3mm} + id \\ + parameterClass \\ + resultClass \\ + listClass \\ + parameterMap \\ + resultMap\\ + cacheModel + \vspace{3mm} + \end{minipage} + & + \begin{minipage}{0.22\textwidth} + None + \end{minipage} + & + \begin{minipage}{0.2\textwidth} + Insert \\ Update \\ Delete \\ All query methods + \end{minipage} + \\ + \hline + \tt{} & + \begin{minipage}{0.17\textwidth} + \vspace{3mm} + id \\ + parameterClass \\ + parameterMap + \vspace{3mm} + \end{minipage} + & + \begin{minipage}{0.22\textwidth} + \tt{}\\ + \tt{} + \end{minipage} + & + \begin{minipage}{0.2\textwidth} + Insert \\ Update \\ Delete + \end{minipage} +\\ +\hline + \tt{} & + \begin{minipage}{0.17\textwidth} + \vspace{3mm} + id \\ + parameterClass \\ + parameterMap \\ + extends + \vspace{3mm} + \end{minipage} + & + \begin{minipage}{0.22\textwidth} + \tt{} + \end{minipage} + & + \begin{minipage}{0.2\textwidth} + Insert \\ Update \\ Delete + \end{minipage} + \\ + \hline + \tt{} & + \begin{minipage}{0.17\textwidth} + \vspace{3mm} + id \\ + parameterClass \\ + parameterMap \\ + extends + \vspace{3mm} + \end{minipage} + & + \begin{minipage}{0.22\textwidth} + \tt{} + \end{minipage} + & + \begin{minipage}{0.2\textwidth} + Insert \\ Update \\ Delete + \end{minipage} + \\ + \hline + \tt{ + + + + + + + + select @@IDENTITY as value + + + +\end{verbatim} +\end{example} +The tag generates ANSI SQL, which should work with any compliant database. +Special types, such as blobs, are not supported, and vendor-specific types are +also not supported. But, the generate tag does keep the simple things simple. + +\begin{mybox}{Note:} +The SQL is generated when the DataMapper instance is built and can be cached +afterward, so there is no performance impact at execution time. +\end{mybox} + +The generate tag supports two attributes : +\begin{table}[!htb]\centering\label{table:3.2} +\caption{\tt{} attributes} +\begin{tabular}{|l|l|l|} + \hline + % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ... + \textbf{Attribute} & \textbf{Description} & \textbf{Required} \\ + \hline + table & specifies the table name to use in the SQL statement. & yes \\ + \hline + by & specifies the columns to use in a WHERE clause & no \\ + \hline +\end{tabular} +\end{table} + +\section{Statement-type Element Attributes}\label{section:3.3.4} +The six statement-type elements take various attributes. See +Section~\ref{section:3.3.1} for a table itemizing which attributes each +element-type accepts. The individual attributes are described in the sections +that follow. + +\subsection{\tt{id} attribute} +The required \tt{id} attribute provides a name for this statement, which must +be unique within this \tt{}. + +\subsection{\tt{parameterMap} attribute} +A Parameter Map defines an ordered list of values that match up with the ``?'' +placeholders of a standard, parameterized query statement. +Example~\ref{example:3.9} shows a \tt{} and a corresponding +\tt{}. + +\begin{example}\label{example:3.9} +A \tt{parameterMap} and corresponding statement +\begin{verbatim} + + + + + + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?); + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.9}, the Parameter Map describes two parameters that +will match, in order, two placeholders in the SQL statement. The first ``?'' +is replaced by the value of the \tt{id} property. The second is replaced with +the \tt{description} property. + +SQLMap also supports named, inline parameters, which most developers seem to +prefer. However, Parameter Maps are useful when the SQL must be kept in a +standard form or when extra information needs to be provided. For more about +Parameter Maps see Chapter~\ref{section:3.4}. + +\subsection{\tt{parameterClass} attribute } +If a \tt{parameterMap} attribute is not specified, you may specify a +\tt{parameterClass} instead and use inline parameters (see +Section~\ref{section:3.4.3}). The value of the \tt{parameterClass} attribute +can be any existing PHP class name. Example~\ref{example:3.10} shows a +statement using a PHP class named \tt{Product} in \tt{parameterClass} +attribute. + +\begin{example}\label{example:3.10} +Specify the \tt{parameterClass} with a PHP class name. +\begin{verbatim} + + insert into PRODUCT values (#id#, #description#, #price#) + +\end{verbatim} +\end{example} + +\subsection{\tt{resultMap} attribute} +A Result Map lets you control how data is extracted from the result of a +query, and how the columns are mapped to object properties. +Example~\ref{example:3.11} shows a \tt{} element and a +corresponding \tt{} element. +\begin{example}\label{example:3.11} +A \tt{} and corresponding \tt{} +\begin{verbatim} + + + + + + + select * from PRODUCT + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.11}, the result of the SQL query will be mapped to +an instance of the \tt{Product} class using the ``select-product-result'' +\tt{}. The \tt{} says to populate the \tt{id} property +from the \tt{PRD\_ID} column, and to populate the \tt{description} property +from the \tt{PRD\_DESCRIPTION} column. + +\begin{mybox}{Tip:} +In Example~\ref{example:3.11}, note that using `` select * '' is supported. If +you want all the columns, you don't need to map them all individually. (Though +many developers consider it a good practice to always specify the columns +expected.) +\end{mybox} + +For more about Result Maps, see Chapter~\ref{section:3.5}. + +\subsection{\tt{resultClass} attribute} +If a \tt{resultMap} is not specified, you may specify a \tt{resultClass} +instead. The value of the \tt{resultClass} attribute can be the name of a PHP +class or primitives like \tt{integer}, \tt{string}, or \tt{array}. The class +specified will be automatically mapped to the columns in the result, based on +the result metadata. The following example shows a \tt{} element +with a \tt{resultClass} attribute. + +\begin{example}\label{example:3.12} +A \tt{} element with \tt{resultClass} attribute +\begin{verbatim} + + SELECT + PER_ID as Id, + PER_FIRST_NAME as FirstName, + PER_LAST_NAME as LastName, + PER_BIRTH_DATE as BirthDate, + PER_WEIGHT_KG as WeightInKilograms, + PER_HEIGHT_M as HeightInMeters + FROM PERSON + WHERE PER_ID = #value# + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.12}, the \tt{Person} class has properties including: +\tt{Id}, \tt{FirstName}, \tt{LastName}, \tt{BirthDate}, +\tt{WeightInKilograms}, and \tt{HeightInMeters}. Each of these corresponds +with the column aliases described by the SQL select statement using the ``as'' +keyword ¨Ca standard SQL feature. When executed, a \tt{Person} object is +instantiated and populated by matching the object property names to the column +names from the query. + +Using SQL aliases to map columns to properties saves defining a +\tt{} element, but there are limitations. There is no way to +specify the types of the output columns (if needed), there is no way to +automatically load related data such as complex properties.You can overcome +these limitations with an explicit Result Map (Chapter~\ref{section:3.5}). + +\subsection{\tt{listClass} attribute} +In addition to providing the ability to return an \tt{TList} of objects, the +DataMapper supports the use of custom collection: a class that implements +\tt{ArrayAccess}. The following is an example of a TList (it implements +ArrayAccess) class that can be used with the DataMapper. + +\begin{example}\label{example:3.13} +An \tt{ArrayAccess} implementation, by extending \tt{TList}. +\begin{verbatim} +class AccountCollection extends TList +{ + public function addRange($accounts) + { + foreach($accounts as $account) + $this->add($account); + } + + public function copyTo(TList $array) + { + $array->copyFrom($this); + } +} +\end{verbatim} +\end{example} +An \tt{ArrayAccess} class can be specified for a select statement through the +\tt{listClass} attribute. The value of the \tt{listClass} attribute is the +full name of a PHP class that implements \tt{ArrayAccess}. The statement +should also indicate the \tt{resultClass} so that the DataMapper knows how to +handle the type of objects in the collection. The \tt{resultClass} specified +will be automatically mapped to the columns in the result, based on the result +metadata. The following example shows a \tt{} element with a +\tt{listClass} attribute. + +\begin{example}\label{example:3.14} +A \tt{} element with \tt{listClass} attribute +\begin{verbatim} + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + order by Account_LastName, Account_FirstName + +\end{verbatim} +\end{example} + +\subsection{\tt{cacheModel} attribute} +If you want to cache the result of a query, you can specify a Cache Model as +part of the \tt{} element. Example~\ref{example:3.15} shows a +\tt{} element and a corresponding \tt{}. + +\begin{example}\label{example:3.15} +A \tt{} element with its corresponding \tt{} +\begin{verbatim} + + + + + + + + + + select * from PRODUCT where PRD_CAT_ID = #value# + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.15}, a cache is defined for products that uses a +Least Recently Used [LRU] type and flushes every 24 hours or whenever +associated update statements are executed. For more about Cache Models, see +Section~\ref{section:3.8}. + +\subsection{\tt{extends} attribute} +When writing Sql, you often encounter duplicate fragments of SQL. SQLMap +offers a simple yet powerful attribute to reuse them. + +\begin{verbatim} + + + +\end{verbatim} diff --git a/docs/sqlmap/latex/ch4.tex b/docs/sqlmap/latex/ch4.tex new file mode 100644 index 00000000..a4cbc937 --- /dev/null +++ b/docs/sqlmap/latex/ch4.tex @@ -0,0 +1,306 @@ +\chapter{Parameter Maps and Inline Parameters}\label{section:3.4} +Most SQL statements are useful because we can pass them values at runtime. +Someone wants a database record with the ID 42, and we need to merge that ID +number into a select statement. A list of one or more parameters are passed at +runtime, and each placeholder is replaced in turn. This is simple but labor +intensive, since developers spend a lot of time counting symbols to make sure +everything is in sync. + +\begin{mybox}{Note:} +Preceding sections briefly touched on inline parameters, which automatically +map properties to named parameters. Many iBATIS developers prefer this +approach. But others prefer to stick to the standard, anonymous approach to +SQL parameters by using parameter maps. Sometimes people need to retain the +purity of the SQL statements; other times they need the detailed specification +offered by parameter maps due to database or provider-specific information +that needs to be used. +\end{mybox} + +\section{Parameter Map} +A Parameter Map defines an ordered list of values that match up with the +placeholders of a parameterized query statement. While the attributes +specified by the map still need to be in the correct order, each parameter is +named. You can populate the underlying class in any order, and the Parameter +Map ensures each value is passed in the correct order. + +Parameter Maps can be provided as an external element and \emph{inline}. +Example~\ref{example:3.16} shows an external Parameter Map. + +\begin{example}\label{example:3.16} +An external Parameter Map +\begin{verbatim} + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.16}, the parts in [brackets] are optional. The +\tt{parameterMap} element only requires the id attribute. +Example~\ref{example:3.17} shows a typical \tt{}. + +\begin{example}\label{example:3.17} +A typical \tt{} element +\begin{verbatim} + + + + + + + insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?); + +\end{verbatim} +\end{example} + +\begin{mybox}{Note:} +Parameter Map names are always local to the Data Map definition file where +they are defined. You can refer to a Parameter Map in another Data Map +definition file by prefixing the \tt{id} of the Parameter Map with the +namespace of the Data Map (set in the \tt{} root element). If the +Parameter Map in Example~\ref{example:3.17} were in a Data Map named +``Product'', it could be referenced from another file using +``Product.insert-product-param''. +\end{mybox} + +\subsection{\tt{} attributes} The \tt{} element +accepts two attributes: \tt{id} (required) and \tt{extends} (optional). + +\subsubsection{\tt{id} attribute} The required \tt{id} attribute provides a +unique identifier for the \tt{} within this Data Map. + +\subsubsection{\tt{extends} attribute} +The optional \tt{extends} attribute can be set to the name of another +\tt{parameterMap} upon which to base this \tt{parameterMap}. All properties of +the super \tt{parameterMap} will be included as part of this +\tt{parameterMap}, and values from the super \tt{parameterMap} are set before +any values specified by this \tt{parameterMap}. The effect is similar to +extending a class. + +\section{\tt{} Elements} +The \tt{} element holds one or more parameter child elements +that map object properties to placeholders in a SQL statement. The sections +that follow describe each of the attributes. + +\subsection{\tt{property} attribute} +The \tt{property} attribute of \tt{} is the name of a property of +the parameter object. It may also be the name of an entry in an array. The +name can be used more than once depending on the number of times it is needed +in the statement. (In an update, you might set a column that is also part of +the where clause.) + +\subsection{\tt{direction} attribute} +The \tt{direction} attribute may be used to indicate a stored procedure +parameter's direction. + +\subsection{\tt{column} attribute} +The \tt{column} attribute is used to define to the name of a parameter used by +a stored procedure. + +\begin{table}[!h]\centering\label{table:3.3} +\caption{Parameter \tt{direction} attribute values } +\begin{tabular}{|l|l|} + \hline + % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ... + \textbf{Value} & \textbf{Description}\\ + \hline + Input & input-only \\ + \hline + Output & output-only \\ + \hline + InputOutput & bidirectional \\ + \hline +\end{tabular} +\end{table} + +\subsection{\tt{dbType} attribute} +The \tt{dbType} attribute is used to explicitly specify the database column +type of the parameter to be set by this property. This attribute is normally +only required if the column is nullable. Although, another reason to use the +\tt{dbType} attribute is to explicitly specify date types. Most SQL databases +have more than one \tt{datetime} type. Usually, a database has at least three +different types (DATE, DATETIME, TIMESTAMP). In order for the value to map +correctly, you might need to specify the column's \tt{dbType}. + +\begin{mybox}{Note:} +Most providers only need the \tt{dbType} specified for nullable columns. In +this case, you only need to specify the type for the columns that are +nullable. +\end{mybox} + +\subsection{\tt{type} attribute} +The \tt{type} attribute is used to specify the type of the parameter's +property. This attribute is useful when passing \tt{InputOutput} and +\tt{Output} parameters into stored procedures. The framework uses the +specified type to properly handle and set the parameter object's properties +with the procedure's output values after execution. + +%If the attribute type is not set and the framework cannot otherwise determine +%the type, the type is assumed to be an StdObject. Section~\ref{section:6} +%details the types and available aliases that have pre-built support in the +%framework. + +\subsection{\tt{nullValue} attribute}\label{section:nullValueParameter} +The \tt{nullValue} attribute can be set to any valid value (based on property +type). The \tt{nullValue} attribute is used to specify an outgoing null value +replacement. What this means is that when the value is detected in the object +property, a NULL will be written to the database (the opposite behavior of an +inbound null value replacement). This allows you to use a magic null number in +your application for types that do not support null values (such as int, +double, float). When these types of properties contain a matching null value +(for example, say, $-9999$), a NULL will be written to the database instead of +the value. + + +\begin{mybox}{Tip:} +For round-trip transparency of null values, you must also specify database +columns null value replacements in your Result Map (see +Chapter~\ref{section:3.5}). +\end{mybox} + + +\subsection{\tt{size} attribute} +The \tt{size} attribute sets the maximum size of the data within the column. + +\subsection{\tt{precision} attribute} +The \tt{precision} attribute is used to set the maximum number of digits used +to represent the property value. + +\subsection{\tt{scale} attribute} +The \tt{scale} attribute sets the number of decimal places used to resolve the +property value. + +\subsection{\tt{typeHandler} attribute} +The \tt{typeHandler} attribute allows the use of a Custom Type Handler (see +the Custom Type Handler section). This allows you to extend the DataMapper's +capabilities in handling types that are specific to your database provider, +are not handled by your database provider, or just happen to be a part of your +application design. You can create custom type handlers to deal with storing +and retrieving booleans from your database for example. + +\section{Inline Parameter Maps}\label{section:3.4.3} +If you prefer to use inline parameters instead of parameter maps, you can add +extra type information inline too. The inline parameter map syntax lets you +embed the property name, the property type, the column type, and a null value +replacement into a parametrized SQL statement. The next four examples shows +statements written with inline parameters. + +\begin{example}\label{example:3.18} + A \tt{} using inline parameters +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id#, #description#) + +\end{verbatim} +\end{example} + +The following example shows how \tt{dbTypes} can be declared inline. + +\begin{example}\label{example:3.19} + A \tt{} using an inline parameter map with a type +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int#, #description, dbType=VarChar#) + +\end{verbatim} +\end{example} + +The next example shows how \tt{dbTypes} and null value replacements can also +be declared inline. + +\begin{example}\label{example:3.20} +A \tt{} using an inline parameter map with a null value replacement +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int, nullValue=-999999#, #description, dbType=VarChar#) + +\end{verbatim} +\end{example} + +\begin{example}\label{example:3.21} +A more complete example. +\begin{verbatim} + + update Accounts set + Account_FirstName = #FirstName#, + Account_LastName = #LastName#, + Account_Email = #EmailAddress,type=string,dbType=Varchar,nullValue=no_email@provided.com# + where + Account_ID = #Id# + +\end{verbatim} +\end{example} + +\begin{mybox}{Note:} +Inline parameter maps are handy for small jobs, but when there are a lot of +type descriptors and null value replacements in a complex statement, an +industrial-strength, external \tt{parameterMap} can be easier. +\end{mybox} + +\section{Standard Type Parameters} +In practice, you will find that many statements take a single parameter, often +an \tt{integer} or a \tt{string}. Rather than wrap a single value in another +object, you can use the standard library object (string, integer, et cetera) +as the parameter directly. Example~\ref{example:3.22} shows a statement using +a standard type parameter. + +\begin{example}\label{example:3.22} +A \tt{} using standard type parameters +\begin{verbatim} + + select * from PRODUCT where PRD_ID = #value# + +\end{verbatim} +\end{example} + +Assuming \tt{PRD\_ID} is a numeric type, when a call is made to this Mapped +Statement, a standard integer can be passed in. The \tt{\#value\#} parameter +will be replaced with the value of the integer. The name \tt{value} is simply +a placeholder, you can use another moniker of your choice. Result Maps support +primitive types as results as well. + +For your convenience, the following PHP primitive types are supported. +\begin{itemize} + \item \tt{string} + \item \tt{float} or \tt{double} + \item \tt{integer} or \tt{int} + \item \tt{bool} or \tt{boolean} +\end{itemize} + +\section{Array Type Parameters} +You can also pass in a array as a parameter object. This would usually be a an +associative array. Example~\ref{example:3.23} shows a \tt{} using +an array for a \tt{parameterClass}. + + +\begin{example}\label{example:3.23} +A \tt{} using an array for a \tt{parameterClass} +\begin{verbatim} + + select * from PRODUCT + where PRD_CAT_ID = #catId# + and PRD_CODE = #code# + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.23}, notice that the SQL in this Mapped Statement +looks like any other. There is no difference in how the inline parameters are +used. If an associative array is passed, it must contain keys named \tt{catId} +and \tt{code}. The values referenced by those keys must be of the appropriate +type for the column, just as they would be if passed from a properties object. diff --git a/docs/sqlmap/latex/ch5.tex b/docs/sqlmap/latex/ch5.tex new file mode 100644 index 00000000..076f6f5d --- /dev/null +++ b/docs/sqlmap/latex/ch5.tex @@ -0,0 +1,941 @@ +\chapter{Result Maps}\label{section:3.5} +Chapter~\ref{section:3.4} describes Parameter Maps and Inline parameters, +which map object properties to parameters in a database query. Result Maps +finish the job by mapping the result of a database query (a set of columns) to +object properties. Next to Mapped Statements, the Result Map is probably one +of the most commonly used and most important features to understand. + +A Result Map lets you control how data is extracted from the result of a +query, and how the columns are mapped to object properties. A Result Map can +describe the column type, a null value replacement, and complex property +mappings including Collections. Example~\ref{example:3.24} shows the structure +of a \tt{} element. + +\begin{example}\label{example:3.24} +The structure of a \tt{} element. +\begin{verbatim} + + + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.24}, the [brackets] indicate optional attributes. +The \tt{id} attribute is required and provides a name for the statement to +reference. The \tt{class} attribute is also required, and specifies the full +name of a PHP class. This is the class that will be instantiated and populated +based on the result mappings it contains. + +The \tt{resultMap} can contain any number of property mappings that map object +properties to the columns of a result element. The property mappings are +applied, and the columns are read, in the order that they are defined. +Maintaining the element order ensures consistent results between different +drivers and providers. + +\begin{mybox}{Note:} +As with parameter classes, the result class must be a PHP class object or +array instance. +\end{mybox} + +\section{Extending \tt{resultMaps}} +The optional \tt{extends} attribute can be set to the name of another +\tt{resultMap} upon which to base this \tt{resultMap}. All properties of the +``super'' \tt{resultMap} will be included as part of this \tt{resultMap}, and +values from the ``super'' \tt{resultMap} are set before any values specified +by this \tt{resultMap}. The effect is similar to extending a class. + +\begin{mybox}{Tip:} +The ``super'' \tt{resultMap} must be defined in the file before the extending +\tt{resultMap}. The classes for the super and sub \tt{resultMaps} need not be +the same, and do not need to be related in any way. +\end{mybox} + +\section{\tt{} attributes} +The \tt{} element accepts three attributes: \tt{id} (required), +\tt{class} (optional), and \tt{extends} (optional). + +\subsection{\tt{id} attribute} +The required \tt{id} attribute provides a unique identifier for the +\tt{} within this Data Map. + +\subsection{\tt{class} attribute} +The optional \tt{class} attribute specifies an object class to use with this +\tt{}. The full classname must be specified. Any class can be used. + +\begin{mybox}{Note:} +As with parameter classes, the result class must be a PHP class object or +array instance. +\end{mybox} + +\subsection{\tt{extends} attribute} +The optional \tt{extends} attribute allows the result map to inherit all of +the properties of the ``super'' \tt{resultMap} that it extends. + +\section{\tt{} Elements} + +The \tt{} element holds one or more \tt{} child elements +that map SQL result sets to object properties. + +\subsection{\tt{property} attribute} +The \tt{property} attribute is the name of a property of the result object +that will be returned by the Mapped Statement. The name can be used more than +once depending on the number of times it is needed to populate the results. + +\subsection{\tt{column} attribute} +The \tt{column} attribute value is the name of the column in the result set +from which the value will be used to populate the property. + +\subsection{\tt{columnIndex} attribute} +The \tt{columnIndex} attribute value is the index of the column in the +ResultSet from which the value will be used to populate the object property. +This is not likely needed in 99\% of applications and sacrifices +maintainability and readability for speed. Some providers may not realize any +performance benefit, while others will speed up dramatically. + +\subsection{\tt{dbType} attribute} +The \tt{dbType} attribute is used to explicitly specify the database column +type of the ResultSet column that will be used to populate the object +property. Although Result Maps do not have the same difficulties with null +values, specifying the type can be useful for certain mapping types such as +Date properties. Because an application language has one Date value type and +SQL databases may have many (usually at least 3), specifying the date may +become necessary in some cases to ensure that dates (or other types) are set +correctly. Similarly, String types may be populated by a \tt{VarChar}, +\tt{Char} or \tt{CLOB}, so specifying the type might be needed in those cases +too. + +\subsection{\tt{type} attribute} +The \tt{type} attribute is used to explicitly specify the property type of the +parameter to be set. If the attribute \tt{type} is not set and the framework +cannot otherwise determine the type, the type is assumed to be \tt{StdObject}. + +\subsection{\tt{resultMapping} attribute} +The \tt{resultMapping} attribute can be set to the name of another +\tt{resultMap} used to fill the property. If the \tt{resultMap} is in an other +mapping file, you must specified the fully qualified name as : + +\begin{verbatim} +resultMapping="[namespace.sqlMap.]resultMappingId" + +resultMapping="Newspaper" + +resultMapping="LineItem.LineItem" +\end{verbatim} + +\subsection{\tt{nullValue} attribute} +The \tt{nullValue} attribute can be set to any valid value (based on property +type). The \tt{nullValue} attribute is used to specify an outgoing null value +replacement. What this means is that when the value is detected in the object +property, a NULL will be written to the database (the opposite behavior of an +inbound null value replacement). This allows you to use a ``magic'' null +number in your application for types that do not support null values (such as +int, double, float). When these types of properties contain a matching null +value (say, $-9999$), a NULL will be written to the database instead of the +value. + +If your database has a NULLABLE column, but you want your application to +represent NULL with a constant value, you can specify it in the Result Map as +shown in Example~\ref{example:3.25}. + +\begin{example}\label{example:3.25} +Specifying a \tt{nullvalue} attribute in a Result Map +\begin{verbatim} + + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.25}, if PRD\_SUB\_CODE is read as NULL, then the +\tt{subCode} property will be set to the value of $-9999$. This allows you to +use a primitive type to represent a NULLABLE column in the database. Remember +that if you want this to work for queries as well as updates/inserts, you must +also specify the \tt{nullValue} in the Parameter Map (see, +Section~\ref{section:nullValueParameter}). + +\subsection{\tt{select} attribute} +The \tt{select} attribute is used to describe a relationship between objects +and to automatically load complex (i.e. user defined) property types. The +value of the statement property must be the name of another mapped statement. +The value of the database column (the column attribute) that is defined in the +same property element as this statement attribute will be passed to the +related mapped statement as the parameter. More information about supported +primitive types and complex property mappings/relationships is discussed later +in this document. The \tt{lazyLoad} attribute can be specified with the +\tt{select}. + +\subsection{\tt{lazyLoad} attribute} +Use the \tt{lazyLoad} attribute with the \tt{select} attribute to indicate +whether or not the select statement's results should be lazy loaded. This can +provide a performance boost by delaying the loading of the select statement's +results until they are needed/accessed. + +\subsection{\tt{typeHandler} attribute} +The \tt{typeHandler} attribute allows the use of a Custom Type Handler (see +the Custom Type Handler in the following section). This allows you to extend +the DataMapper's capabilities in handling types that are specific to your +database provider, are not handled by your database provider, or just happen +to be a part of your application design. You can create custom type handlers +to deal with storing and retrieving booleans from your database for example. + +\section{Custom Type Handlers} +A custom type handler allows you to extend the DataMapper's capabilities in +handling types that are specific to your database provider, not handled by +your database provider, or just happen to be part of your application design. +The SQLMap for PHP DataMapper provides an interface, +\tt{ITypeHandlerCallback}, for you to use in implementing your custom type +handler. + +\begin{example}\label{example:3.26} +\tt{ITypeHandlerCallback} interface +\begin{verbatim} +interface ITypeHandlerCallback +{ + public function getParameter($object); + + public function getResult($string); + + public function createNewInstance(); +} +\end{verbatim} +\end{example} + +The \tt{getParameter} method allows you to process a \tt{} +parameter's value before it is added as an parameter. This enables you to do +any necessary type conversion and clean-up before the DataMapper gets to work. + +The \tt{getResult} method allows you to process a database result value right +after it has been retrieved by the DataMapper and before it is used in your +\tt{resultClass}, \tt{resultMap}, or \tt{listClass}. + +The \tt{createNewInstance} method allows the DataMapper to create new instance +of a particular type handled by this callback. + +One scenario where custom type handlers are useful are the when you want to +use date time values in the database. First, consider a very basic TDateTime +class. + +\begin{verbatim} +class TDateTime +{ + private $_datetime; + + public function __construct($datetime=null) + { + if(!is_null($datetime)) + $this->setDatetime($datetime); + } + + public function getTimestamp() + { + return strtotime($this->getDatetime()); + } + + public function getDateTime() + { + return $this->_datetime; + } + + public function setDateTime($value) + { + $this->_datetime = $value; + } +} +\end{verbatim} + +We can use a custom type handler to intercept result and parameter mapping +that uses the say ``data'' as one of its property type. The handler can be +written as follows. + +\begin{example}\label{example:3.27} +A \tt{TDateTime} Type Handler +\begin{verbatim} +class TDateTimeHandler implements ITypeHandlerCallback +{ + public function getResult($string) + { + return new TDateTime($string); + } + + public function getParameter($parameter) + { + if($parameter instanceof TDateTime) + return $parameter->getTimestamp(); + else + return $parameter; + } + + public function createNewInstance() + { + return new TDateTime; + } +} +\end{verbatim} +\end{example} + +With our custom type handler we can use the handler in our SqlMaps. To do +that, we specify it as a basic \tt{} for all \tt{date} types +mapped in our SqlMap files + +\begin{example}\label{example:3.29} +\tt{} in SqlMap.config +\begin{verbatim} +[Our SqlMap.config] + + + + + + +[One of our SqlMap.xml files] + + + + + + + +\end{verbatim} +\end{example} + +%3.5.5. Inheritance Mapping The iBATIS DataMapper supports the implementation +%of object-oriented inheritance (subclassing) in your object model. There are +%several developer options for mapping entity classes and subclasses to +%database results: +% +%resultMap for each class resultMap with submaps for a class hierarchy +%resultMap with extended resultMaps for each subclass You can use the most +%efficient mapping strategies from a SQL and query performance perspective when +%using the inheritance mappings of the DataMapper. To implement an inheritance +%mapping, the resultMap must define one or more columns in your query's +%resultset that will serve to identify which resultMap should be used to map +%each result record to a specific subclass. In many cases, you will use one +%column value for the DataMapper to use in identifying the proper resultMap and +%subclass. This column is known as a discriminator. +% +%For example, we have a table defined in a database that contains Document +%records. There are five table columns used to store Document IDs, Titles, +%Types, PageNumbers, and Cities. Perhaps this table belongs to a legacy +%database, and we need to create an application using this table with a domain +%model that defines a class hierarchy of different types of Documents. Or +%perhaps we are creating a new application and database and just want to +%persist the data found in a set of related classes into one table. In either +%case, the DataMapper's inheritance mapping feature can help. +% +%\begin{verbatim} +%// Database table Document +%CREATE TABLE [Documents] ( +% [Document_ID] [int] NOT NULL , +% [Document_Title] [varchar] (32) NULL , +% [Document_Type] [varchar] (32) NULL , +% [Document_PageNumber] [int] NULL , +% [Document_City] [varchar] (32) NULL +%) +%\end{verbatim} +% +%To illustrate this, let's take a look at a few example classes shown below +%that have a relationship through inheritance and whose properties can be +%persisted into our Documents table. First, we have a base Document class that +%has Id and Title properties. Next, we have a Book class that inherits from +%Document and contains an additional property called PageNumber. Last, we have +%a Newspaper class that also inherits from Document and contains a City +%property. +% +%Example 3.30. Documents, Books, and Newspapers! +% +%\begin{verbatim} +%// C# class +%public class Document { +% private int _id = -1; +% private string _title = string.Empty; +% +% public int Id +% { +% get { return _id; } +% set { _id = value; } +% } +% +% public string Title +% { +% get { return _title; } +% set { _title = value; } +% } +%} +% +%public class Book : Document { +% private int _pageNumber = -1; +% +% public int PageNumber +% { +% get { return _pageNumber; } +% set { _pageNumber = value; } +% } +%} +% +%public class Newspaper : Document { +% private string _city = string.Empty; +% +% public string City +% { +% get { return _city; } +% set { _city = value; } +% } +%} +%\end{verbatim} +% +%Now that we have our classes and database table, we can start working on our +%mappings. We can create one +% select +% Document_Id, Document_Title, Document_Type, +% Document_PageNumber, Document_City +% from Documents +% order by Document_Type, Document_Id +% +% +% +% +% +% +% +% +% +% +% +% +% +% +% +% +% +%\end{verbatim} +% +%The DataMapper compares the data found in the discriminator column to the +%different values using the column value's string equivalence. Based +%on this string value, iBATIS DataMapper will use the resultMap named "Book" or +%"Newspaper" as defined in the elements or it will use the "super" +%resultMap "Document" if neither of the submap values satisfy the comparison. +%With these resultMaps, we can implement an object-oriented inheritance mapping +%to our database table. +% +%If you want to use custom logic, you can use the typeHandler attribute of the +% element to specify a custom type handler for the discriminator +%column. +% +%Example 3.31. Complex disciminator usage with Custom Type Handler +% +%\begin{verbatim} +% +% +% +% +% +% +% +% +% +% +% +% +% +%\end{verbatim} +% +%The value of the typeHandler attribute specifies which of our classes +%implements the ITypeHandlerCallback interface. This interface furnishes a +%GetResult method for coding custom logic to read the column result value and +%return a value for the DataMapper to use in its comparison to the resultMap's +%defined values. +% +%Example 3.32. Example ITypeHandlerCallback interface implementation +% +%\begin{verbatim} +%public class CustomInheritance : ITypeHandlerCallback { +% #region ITypeHandlerCallback members +% +% public object ValueOf(string nullValue) +% { +% throw new NotImplementedException(); +% } +% +% public object GetResult(IResultGetter getter) +% { +% string type = getter.Value.ToString(); +% +% if (type=="Monograph" || type=="Book") +% { +% return "Book"; +% } +% else if (type=="Tabloid" || type=="Broadsheet" || type=="Newspaper") +% { +% return "Newspaper"; +% } +% else +% { +% return "Document"; +% } +% +% } +% +% public void SetParameter(IParameterSetter setter, object parameter) +% { +% throw new NotImplementedException(); +% } +% #endregion +%} +%\end{verbatim} + +\section{Implicit Result Maps} +If the columns returned by a SQL statement match the result object, you may +not need an explicit Result Map. If you have control over the relational +schema, you might be able to name the columns so they also work as property +names. In Example~\ref{example:3.33}, the column names and property names +already match, so a result map is not needed. + +\begin{example}\label{example:3.33} +A Mapped Statement that doesn't need a Result Map +\begin{verbatim} + + select + id, + description + from PRODUCT + where id = #value# + +\end{verbatim} +\end{example} + +Another way to skip a result map is to use column aliasing to make the column +names match the properties names, as shown in Example~\ref{example:3.34}. + +\begin{example}\label{example:3.34} +A Mapped Statement using column aliasing instead of a Result Map +\begin{verbatim} + + select + PRD_ID as id, + PRD_DESCRIPTION as description + from PRODUCT + where PRD_ID = #value# + +\end{verbatim} +\end{example} + +Of course, these techniques will not work if you need to specify a column +type, a null value, or any other property attributes. + +\section{Primitive Results (i.e. String, Integer, Boolean)} +Many times, we don't need to return an object with multiple properties. We +just need a string, integer, boolean, and so forth. If you don't need to +populate an object, SQLMap can return one of the primitive types instead. If +you just need the value, you can use a primitive type as a result class, as +shown in Example~\ref{example:3.35}. + +\begin{example}\label{example:3.35} +Selecting a primitive type +\begin{verbatim} + +\end{verbatim} +\end{example} + +\begin{example}\label{example:3.36} +Loading a simple list of product descriptions +\begin{verbatim} + + + +\end{verbatim} +\end{example} + +\section{Maps with ResultMaps} +Instead of a rich object, sometimes all you might need is a simple key/value +list of the data, where each property is an entry on the list. If so, Result +Maps can populate an array instance as easily as property objects. The syntax +for using an array is identical to the rich object syntax. As shown in Example +~\ref{example:3.37}, only the result object changes. + +\begin{example}\label{example:3.37} +Result Maps can use arrays. +\begin{verbatim} + + + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.37}, an array instance would be created for each row +in the result set and populated with the Product data. The property name +attributes, like \tt{id}, \tt{code}, and so forth, would be the key of the +entry, and the value of the mapped columns would be the value of the entry. + +As shown in Example~\ref{example:3.38}, you can also use an implicit Result +Map with an array type. + +\begin{example}\label{example:3.38} +Implicit Result Maps can use arrays too. +\begin{verbatim} + + select * from PRODUCT + +\end{verbatim} +\end{example} + +What set of entries is returned by Example~\ref{example:3.38} depends on what +columns are in the result set. If the set of column changes (because columns +are added or removed), the new set of entries would automatically be returned. + +\begin{mybox}{Note:} +Certain providers may return column names in upper case or lower case format. +When accessing values with such a provider, you will have to pass the key name +in the expected case. +\end{mybox} + +\section{Complex Properties} +In a relational database, one table will often refer to another. Likewise, +some of your business objects may include another object or list of objects. +Types that nest other types are called ``complex types''. You may not want a +statement to return a simple type, but a fully-formed complex type. + +In the database, a related column is usually represented via a 1:1 +relationship, or a 1:M relationship where the class that holds the complex +property is from the ``many side'' of the relationship and the property itself +is from the ``one side'' of the relationship. The column returned from the +database will not be the property we want; it is a key to be used in another +query. + +From the framework's perspective, the problem is not so much loading a complex +type, but loading each ``complex property''. To solve this problem, you can +specify in the Result Map a statement to run to load a given property. In +Example~\ref{example:3.39}, the ``category'' property of the +``select-product-result'' element is a complex property. + +\begin{example}\label{example:3.39} +A Result Map with a Complex Property +\begin{verbatim} + + + + + + + + + + + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.39}, the framework will use the ``selectCategory'' +statement to populate the ``category'' property. The value of each category is +passed to the ``selectCategory'' statement, and the object returned is set to +the category property. When the process completes, each Product instance will +have the the appropriate category object instance set. + +\section{Avoiding N+1 Selects (1:1)} +A problem with Example~\ref{example:3.39} may be that whenever you load a +Product, two statements execute: one for the Product and one for the Category. +For a single Product, this issue may seem trivial. But if you load 10 +products, then 11 statements execute. For 100 Products, instead of one +statement product statement executing, a total of 101 statements execute. The +number of statements executing for Example~\ref{example:3.40} will always be +N+1: 100+1=101. + +\begin{example}\label{example:3.40} +N+1 Selects (1:1) +\begin{verbatim} + + + + + + + + + + + + + + + + + +\end{verbatim} +\end{example} + +One way to mitigate the problem is to cache the ``selectCategory'' statement . +We might have a hundred products, but there might only be five categories. +Instead of running a SQL query or stored procedure, the framework will return +the category object from it cache. A 101 statements would still run, but they +would not be hitting the database. (See Chapter~\ref{section:3.8} for more +about caches.) + +Another solution is to use a standard SQL join to return the columns you need +from the another table. A join can bring all the columns we need over from the +database in a single query. When you have a nested object, you can reference +nested properties using a dotted notation, like ``category.description''. + +Example~\ref{example:3.41} solves the same problem as +Example~\ref{example:3.40}, but uses a join instead of nested properties. + +\begin{example}\label{example:3.41} +Resolving complex properties with a join +\begin{verbatim} + + + + + + + + select * + from PRODUCT, CATEGORY + where PRD_CAT_ID=CAT_ID + and PRD_ID = #value# + +\end{verbatim} +\end{example} + +\begin{mybox}{Lazy Loading vs. Joins (1:1):} +It's important to note that using a join is not always better. If you are in a +situation where it is rare to access the related object (e.g. the category +property of the Product class) then it might actually be faster to avoid the +join and the unnecessary loading of all category properties. This is +especially true for database designs that involve outer joins or nullable +and/or non-indexed columns. In these situations it might be better to use the +sub-select solution with lazy loading enabled. The general rule of thumb is: +use the join if you're more likely going to access the associated properties +than not. Otherwise, only use it if lazy loading is not an option. +\\ +\\ +If you're having trouble deciding which way to go, don't worry. No matter +which way you go, you can always change it without impacting your application +source code. Example~\ref{example:3.40} and \ref{example:3.41} result in +exactly the same object graph and are loaded using the exact same method call +from the application. The only consideration is that if you were to enable +caching, then the using the separate select (not the join) solution could +result in a cached instance being returned. But more often than not, that +won't cause a problem (your application shouldn't be dependent on instance +level equality i.e. ``===''). +\end{mybox} + +\section{Complex Collection Properties} +It is also possible to load properties that represent lists of complex +objects. In the database the data would be represented by a M:M relationship, +or a 1:M relationship where the class containing the list is on the ``one +side'' of the relationship and the objects in the list are on the ``many +side''. To load a \tt{TList} of objects, there is no change to the statement +(see example above). The only difference required to cause the SQLMap +DataMapper framework to load the property as a \tt{TList} is that the property +on the business object must be of type \tt{TList}. For example, if a Category +has a \tt{TList} of Product instances, the mapping would look like this +(assuming Category has a property called "ProductList" of \tt{TList}.): + +\begin{example}\label{example:3.42} +Mapping that creates a list of complex objects +\begin{verbatim} + + + + + + + + + + + + + + + + + + select * from CATEGORY where CAT_ID = #value# + + + + select * from PRODUCT where PRD_CAT_ID = #value# + + +\end{verbatim} +\end{example} + +\section{Avoiding N+1 Select Lists (1:M and M:N)} +This is similar to the 1:1 situation above, but is of even greater concern due +to the potentially large amount of data involved. The problem with the +solution above is that whenever you load a Category, two SQL statements are +actually being run (one for the Category and one for the list of associated +Products). This problem seems trivial when loading a single Category, but if +you were to run a query that loaded ten (10) Categories, a separate query +would be run for each Category to load its associated list of Products. This +results in eleven (11) queries total: one for the list of Categories and one +for each Category returned to load each related list of Products (N+1 or in +this case 10+1=11). To make this situation worse, we're dealing with +potentially large lists of data. + +\begin{example}\label{example:3.43} +N+1 Select Lists (1:M and M:N) +\begin{verbatim} + + + + + + + + + + + + + + + + + + + select * from CATEGORY where CAT_ID = #value# + + + + + select * from PRODUCT where PRD_CAT_ID = #value# + + +\end{verbatim} +\end{example} + +\subsection{1:N \& M:N Solution?} +Currently the feature that resolves this issue not implemented, but the +development discussions are active, and we expect it to be included in a +future release. + +\begin{mybox}{Lazy Loading vs. Joins (1:M and M:N):} +As with the 1:1 situation described previously, it's important to note that +using a join is not always better. This is even more true for collection +properties than it was for individual value properties due to the greater +amount of data. If you are in a situation where it is rare to access the +related object (e.g. the ProductList property of the Category class) then it +might actually be faster to avoid the join and the unnecessary loading of the +list of products. This is especially true for database designs that involve +outer joins or nullable and/or non-indexed columns. In these situations it +might be better to use the sub-select solution with the lazy loading. The +general rule of thumb is: use the join if you're more likely going to access +the associated properties than not. Otherwise, only use it if lazy loading is +not an option. +\\ +\\ +As mentioned earlier, if you're having trouble deciding which way to go, don't +worry. No matter which way you go, you can always change it without impacting +your PHP code. The two examples above would result in exactly the same object +graph and are loaded using the exact same method call. The only consideration +is that if you were to enable caching, then the using the separate select (not +the join) solution could result in a cached instance being returned. But more +often than not, that won't cause a problem (your application should not be +dependent on instance level equality i.e. ``===''). +\end{mybox} + +\section{Composite Keys or Multiple Complex Parameters Properties} +You might have noticed that in the above examples there is only a single key +being used as specified in the \tt{resultMap} by the \tt{column} attribute. +This would suggest that only a single column can be associated to a related +mapped statement. However, there is an alternate syntax that allows multiple +columns to be passed to the related mapped statement. This comes in handy for +situations where a composite key relationship exists, or even if you simply +want to use a parameter of some name other than \tt{\#value\#}. The alternate +syntax for the column attribute is simply \{param1=column1, param2=column2, +$\cdots$, paramN=columnN\}. Consider the example below where the PAYMENT table +is keyed by both Customer ID and Order ID: + +\begin{example}\label{example:3.44} +Mapping a composite key +\begin{verbatim} + + + + + ... + + + + + + + + select * from PAYMENT + where PAY_ORD_ID = #itemId# + and PAY_CST_ID = #custId# + + +\end{verbatim} +\end{example} + +Optionally you can just specify the column names as long as they're in the +same order as the parameters. For example: +\begin{verbatim} +{ORD_ID, ORD_CST_ID} +\end{verbatim} + +\begin{mybox}{Important!} +Currently the SQLMap DataMapper framework does not automatically resolve +circular relationships. Be aware of this when implementing parent/child +relationships (trees). An easy work around is to simply define a second result +map for one of the cases that does not load the parent object (or vice versa), +or use a join as described in the ``N+1 avoidance'' solutions. +\end{mybox} + +\begin{mybox}{Note:} +Result Map names are always local to the Data Map definition file that they +are defined in. You can refer to a Result Map in another Data Map definition +file by prefixing the name of the Result Map with the namespace of the SqlMap +set in the \tt{} root element. +\end{mybox} diff --git a/docs/sqlmap/latex/ch6.tex b/docs/sqlmap/latex/ch6.tex new file mode 100644 index 00000000..09a2be6f --- /dev/null +++ b/docs/sqlmap/latex/ch6.tex @@ -0,0 +1,119 @@ +\chapter{Cache Models}\label{section:3.8} +Some values in a database are know to change slower than others. To improve +performance, many developers like to cache often-used data to avoid making +unnecessary trips back to the database. SQLMap provides its own caching +system, that you configure through a \tt{} element. + +The results from a query Mapped Statement can be cached simply by specifying +the \tt{cacheModel} parameter in the statement tag (seen above). A cache model +is a configured cache that is defined within your DataMapper configuration +file. Cache models are configured using the \tt{cacheModel} element as +follows: + +\begin{example}\label{example:3.45} +Configuring a cache using the Cache Model element +\begin{verbatim} + + + + + + + +\end{verbatim} +\end{example} + +The cache model above will create an instance of a cache named +``product-cache'' that uses a Least Recently Used (LRU) implementation. The +value of the \tt{type} attribute is either a class name, or an alias for one +of the included implementations (see below). Based on the flush elements +specified within the cache model, this cache will be flushed every 24 hours. +There can be only one flush interval element and it can be set using hours, +minutes, seconds or milliseconds. In addition the cache will be flushed +whenever the \tt{insertProduct}, \tt{updateProduct}, or \tt{deleteProduct} +mapped statements are executed. There can be any number of ``flush on +execute'' elements specified for a cache. Some cache implementations may need +additional properties, such as the ``cache-size'' property demonstrated above. +In the case of the LRU cache, the size determines the number of entries to +store in the cache. Once a cache model is configured, you can specify the +cache model to be used by a mapped statement, for example: + +\begin{example}\label{example:3.46} +Specifying a Cache Model from a Mapped Statement +\begin{verbatim} + + select * from PRODUCT where PRD_CAT_ID = #value# + +\end{verbatim} +\end{example} + +\section{Cache Implementation} +The cache model uses a pluggable framework for supporting different types of +caches. The choice of cache is specified in the ``implementation'' attribute +of the \tt{cacheModel} element as discussed above. The class name specified +must be an implementation of the \tt{ISqlMapCache} interface, or one of the +two aliases discussed below. Further configuration parameters can be passed to +the implementation via the property elements contained within the body of the +\tt{cacheModel}. Currently there are 2 implementations included with the PHP +distribution. + +\subsection{Least Recently Used [LRU] Cache} The LRU cache implementation uses +an Least Recently Used algorithm to determines how objects are automatically +removed from the cache. When the cache becomes over full, the object that was +accessed least recently will be removed from the cache. This way, if there is +a particular object that is often referred to, it will stay in the cache with +the least chance of being removed. The LRU cache makes a good choice for +applications that have patterns of usage where certain objects may be popular +to one or more users over a longer period of time (e.g. navigating back and +forth between paginated lists, popular search keys etc.). + +The LRU implementation is configured as follows: +\begin{example}\label{example:3.48} +Configuring a LRU type cache +\begin{verbatim} + + + + + + + +\end{verbatim} +\end{example} + +Only a single property is recognized by the LRU cache implementation. This +property, named \tt{CacheSize} must be set to an integer value representing +the maximum number of objects to hold in the cache at once. An important thing +to remember here is that an object can be anything from a single string +instance to an array of object. So take care not to store too much in your +cache and risk running out of memory and disk space. + +\subsection{FIFO Cache} +The FIFO cache implementation uses an First In First Out algorithm to +determines how objects are automatically removed from the cache. When the +cache becomes over full, the oldest object will be removed from the cache. The +FIFO cache is good for usage patterns where a particular query will be +referenced a few times in quick succession, but then possibly not for some +time later. + +The FIFO implementation is configured as follows: + +\begin{example}\label{example:3.49} +Configuring a FIFO type cache +\begin{verbatim} + + + + + + + +\end{verbatim} +\end{example} + +Only a single property is recognized by the FIFO cache implementation. This +property, named \tt{CacheSize} must be set to an integer value representing +the maximum number of objects to hold in the cache at once. An important thing +to remember here is that an object can be anything from a single String +instance to an array of object. So take care not to store too much in your +cache and risk running out of memory and disk space. diff --git a/docs/sqlmap/latex/ch7.tex b/docs/sqlmap/latex/ch7.tex new file mode 100644 index 00000000..37a6a0f4 --- /dev/null +++ b/docs/sqlmap/latex/ch7.tex @@ -0,0 +1,3 @@ +\chapter{Dynamic SQL} + +Not supported in this release. diff --git a/docs/sqlmap/latex/ch8.tex b/docs/sqlmap/latex/ch8.tex new file mode 100644 index 00000000..7d7e3576 --- /dev/null +++ b/docs/sqlmap/latex/ch8.tex @@ -0,0 +1,413 @@ +\chapter{Installation and Setup}\label{section:4.3} +\section{Introduction} +This section explains how to install, configure, and use the SQLMap DataMapper +with your PHP application. + +\section{Installing the DataMapper for PHP} +There are two steps to using SQLMap DataMapper with your application for the +first time. +\begin{itemize} + \item Setup the distribution + \item Add XML documents +\end{itemize} + +\subsection{Setup the Distribution} + +The official site for SQLMap DataMapper for PHP is http://... . The DataMapper +is availabe as a source distribution in the form of a ZIP archive. To download +the distributions, follow the link to the Downloads area on the web site, and +select the the source distribution for the SQLMap PHP DataMapper release. You +can extract the distribution using a utility like WinZip or the extractor +built into newer versions of Windows. + +Under the distribution's source folder are eight folders that make up the +SQLMap PHP DataMapper distribution, as shown in Table 4.1. + +\begin{table}[!hpt] +\caption{Folders found in the SQLMap PHP DataMapper source distribution} +\label{table:4.1} + \centering +\begin{tabular}{|l|l|} +\hline + \textbf{Folder name} & \textbf{Description} \\ + \hline +\end{tabular} +\end{table} + +\subsection{Add XML file items} +After unpacking the source distribution, you will need to add two types of XML +files to your Web application, or library project (and Test project if you +have one). These files are: + +\begin{description} + \item[SqlMap.xml] -- + A Data Map file that contains your SQL queries. Your project will contain one + or more of these files with names such as Account.xml or Product.xml. + + \item[SqlMap.config] -- + The DataMapper configuration file that is used to specify the locations of your + SqlMap.xml files. It is also used to define other DataMapper + configuration options such as caching. You will need to include one SqlMap.config + file for each data source that your project has. +\end{description} + +As expected, the \tt{SqlMap.config} file must be placed where the DataMapper +can find them at runtime. + +\section{Configuring the DataMapper for PHP} +The SQLMap PHP DataMapper is configured using a central XML descriptor file, +usually named \tt{SqlMap.config}, which provides the details for your data +source, data maps, and other features like caching, and transactions. At +runtime, your application code will call a class method provided by the SQLMap +library to read and parse your \tt{SqlMap.config} file. After parsing the +configuration file, a DataMapper client will be returned by SQLMap for your +application to use. + +\subsection{DataMapper clients} +Currently, the SQLMap PHP DataMapper framework revolves around the +\tt{TSqlMapper} class, which acts as a facade to the DataMapper framework API. +You can create a DataMapper client by instantiating an object of the +\tt{TSqlMapper} class. An instance of the \tt{TSqlMapper} class (your +DataMapper client) is created by reading a single configuration file. Each +configuration file can specify one database or data source. You can of couse +use multiple DataMapper clients in your application. Just create another +configuration file and pass the name of that file when the DataMapper client +is created. The configuration files might use a different account with the +same database, or reference different databases on different servers. You can +read from one client and write to another, if that's what you need to do. See +Section~\ref{section:4.4.1} for more details on building a \tt{TSqlMapper} +instance, but first, let's take a look at the DataMapper configuration file. + +\section{DataMapper Configuration File (SqlMap.config)} +A sample configuration file for a PHP web application is shown in +Example~\ref{example:4.1}. Not all configuration elements are required. The +following sections describe the elements of this \tt{SqlMap.config} file in +more detail. + +\begin{example}\label{example:4.1} +Sample SqlMap.Config for a PHP Web Application. +\begin{verbatim} + + + + + + + + + + + + + + + +\end{verbatim} +\end{example} + +\section{DataMapper Configuration Elements} +Sometimes the values we use in an XML configuration file occur in more than +one element. Often, there are values that change when we move the application +from one server to another. To help you manage configuration values, you can +specify a standard properties file (with name=value entries) as part of a +DataMapper configuration. Each named value in the properties file becomes a +shell variable that can be used in the DataMapper configuration file and your +Data Map definition files (see Chapter~\ref{section:3}). + +\subsection{\tt{} attributes} +The \tt{} element can accept one \tt{resource} attribute to +specify the location of the properties file. + +\begin{table}[!hpt] +\caption{Attributes of the \tt{} element} \label{table:4.3} +\centering +\begin{tabular}{|l|l|} + \hline + \textbf{Attribute} & \textbf{Description} \\ + \hline + \tt{resource} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Specify the properties file to be loaded from the directory relative to + the current file. + \vspace{-3mm}\begin{verbatim} + resource="properties.config" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +\end{tabular} +\end{table} + +For example, if the ``properties.config'' file contains + +\begin{verbatim} + + + + +\end{verbatim} + +then all elements in the DataMapper configuration can use the variable +\tt{\${username}} to insert the value ``albert''. For example: + +\begin{verbatim} +} element and attributes} +You can also specify more than one properties file or add property keys and +values directly into your \tt{SqlMap.config} file by using \tt{} +elements. For example: + +\begin{verbatim} + + + + + +\end{verbatim} + +\begin{table}[!hpt] +\caption{Attributes of the \tt{} element} \label{table:4.3} +\centering +\begin{tabular}{|l|l|} + \hline + \textbf{Attribute} & \textbf{Description} \\ + \hline + \tt{resource} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Specify the properties file to be loaded from the directory relative to + the current file. + \vspace{-3mm}\begin{verbatim} + resource="properties.config" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{key} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Defines a property key (variable) name + \vspace{-3mm}\begin{verbatim} + key="username" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{value} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Defines a value that will be used by the DataMapper in place of the + the specified property key/variable + \vspace{-3mm}\begin{verbatim} + value="mydbuser" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +\end{tabular} +\end{table} + +\subsection{The \tt{} Element} +The \tt{} element allows for the configuration and use of a +Custom Type Handler (see the Custom Type Handler section). This extends the +DataMapper's capabilities in handling types that are specific to your database +provider, are not handled by your database provider, or just happen to be a +part of your application design. + +\begin{verbatim} + +\end{verbatim} + +The \tt{} element has three attributes: +\begin{table}[!hpt] +\caption{Attributes of the \tt{} element} \label{table:4.5} +\centering +\begin{tabular}{|l|l|} + \hline + \textbf{Attribute} & \textbf{Description} \\ + \hline +% + \tt{type} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Refers to the name of the type to handle + \vspace{-3mm}\begin{verbatim} + type="date" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{dbType} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Indicates the provider dbType to handle + \vspace{-3mm}\begin{verbatim} + dbType="Varchar2" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{callback} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + The custom type handler class name + \vspace{-3mm}\begin{verbatim} + callback="TDateTimeHandler" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +\end{tabular} +\end{table} + + +\subsection{The \tt{} element and attribute} +The \tt{} element encloses a \tt{} that configure the +database system for use by the framework. +\begin{table}[!hpt] +\caption{Attributes of the \tt{} element} \label{table:4.3} +\centering +\begin{tabular}{|l|l|} + \hline + \textbf{Attribute} & \textbf{Description} \\ + \hline + \tt{class} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + The database provider class that extends + \tt{TDatabaseProvider}. + \vspace{-3mm}\begin{verbatim} + class="TAdodbProvider" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline + \end{tabular} +\end{table} + +\subsection{The \tt{} element and attributes} +The \tt{} element specifies the connection string. +Example~\ref{example:4.2} shows sample element MySql. + +\begin{example}\label{example:4.2} +Sample \tt{} and \tt{} elements. +\begin{verbatim} + + + + + + + +\end{verbatim} +\end{example} + +\begin{table}[!hpt] +\caption{Attributes of the \tt{} element} \label{table:4.4} +\centering +\begin{tabular}{|l|l|} + \hline + \textbf{Attribute} & \textbf{Description} \\ + \hline +% + \tt{connectionString} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Data Source Name (DSN) connection string. + \vspace{-3mm}\begin{verbatim} + connectionString="mysql://root:pwd@localhost/mydb" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{driver} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Database driver name (mysql, sqlite, etc.) + \vspace{-3mm}\begin{verbatim} + driver="mysql" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{host} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + DB host name/IP (and port number) in the format \tt{host[:port]}. + \vspace{-3mm}\begin{verbatim} + connectionString="mysql://root:pwd@localhost/mydb" + \end{verbatim}\vspace{-5mm} + \end{minipage} + \\ + \hline +% + \tt{username} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Database connection username.\vspace{2mm} + \end{minipage} + \\ + \hline +% + \tt{password} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Database connection password.\vspace{2mm} + \end{minipage} + \\ + \hline +% + \tt{database} & + \begin{minipage}{0.7\textwidth}\vspace{2mm} + Database name to use in the connection.\vspace{2mm} + \end{minipage} + \\ + \hline + \end{tabular} +\end{table} + +\begin{mybox}{Tip:} + Use Data Source Name (DSN) connection string or specify the + necessary individual connection parameters. +\end{mybox} + +\subsection{The \tt{} Element} +On a daily basis, most of your work will be with the Data Maps, which are +covered by Chapter~\ref{section:3}. The Data Maps define the actual SQL +statements or stored procedures used by your application. The parameter and +result objects are also defined as part of the Data Map. As your application +grows, you may have several varieties of Data Map. To help you keep your Data +Maps organized, you can create any number of Data Map definition files and +incorporate them by reference in the DataMapper configuration. All of the +definition files used by a DataMapper instance must be listed in the +configuration file. + +Example~\ref{example:4.3} shows \tt{} elements for loading a set of +Data Map definitions. For more about Data Map definition files, see +Chapter~\ref{section:3}. + +\begin{example}\label{example:4.3} +Specifying \tt{sqlMap} locations +\begin{verbatim} + + + + + + + + + +\end{verbatim} +\end{example} + +\begin{mybox}{Tip:} +Since the application root directory location differs by project type +(Windows, Web, or library), it is best to use a properties variable to +indicate the relative path when using the \tt{} \tt{resource} +attribute. Having a variable defined in a properties file makes it easy to +change the path to all your Data Mapper configuration resources in one +location (note the \tt{\$\{projectdir\}} and \tt{\$\{root\}} variables in the +example above). +\end{mybox} diff --git a/docs/sqlmap/latex/ch9.tex b/docs/sqlmap/latex/ch9.tex new file mode 100644 index 00000000..b1ebb522 --- /dev/null +++ b/docs/sqlmap/latex/ch9.tex @@ -0,0 +1,302 @@ +\chapter{Using SQLMap PHP DataMapper} +The SQLMap DataMapper API provides four core functions: + +\begin{itemize} + \item build a \tt{TSqlMapper} instance from a configuration file or cache + \item execute an update query (including insert and delete). + \item execute a select query for a single object + \item execute a select query for a list of objects +\end{itemize} + +The API also provides support for retrieving paginated lists and managing +transactions. + +\section{Building a \tt{TSqlMapper} instance} +An XML document is a wonderful tool for describing a database configuration +(Chapter~\ref{section:4.3}) or defining a set of data mappings +(Chapter~\ref{section:3}), but you can't execute XML. In order to use the +SQLMap configuration and definitions in your PHP application, you need a class +you can call. + +The framework provides service methods that you can call which read the +configuration file (and any of its definition files) and builds a +\tt{TSqlMapper} object. The \tt{TSqlMapper} object provides access to the rest +of the framework. Example~\ref{example:9.4} shows a singleton Mapper that is +similar to the one bundled with the framework. + +\begin{example}\label{example:9.4} +A Mapper singleton you can call from your own applications +\begin{verbatim} +require_once('/path/to/SQLMap/TSqlMapper.php'); + +class TMapper +{ + private static $_mapper; + + public static function configure($configFile) + { + if(is_null(self::$_mapper)) + { + $builder = new TDomSqlMapBuilder(); + self::$_mapper = $builder->configure($configFile); + } + return self::$_mapper; + } + + public static function instance() + { + return self::$_mapper; + } +} +\end{verbatim} +\end{example} + +To obtain the \tt{TSqlMapper} instance, first configure the mapper once, +\begin{verbatim} +TMapper::configure('path/to/sqlmap.config'); +\end{verbatim} +The \tt{TDomSqlMapBuilder} object will go throught the the \tt{sqlmap.config} +file and build a \tt{TSqlMapper} instance. To use \tt{TSqlMapper} in your +application, specify one of the \tt{TSqlMapper} methods (see Section ???). +Here's an example: +\begin{verbatim} +$list = TMapper::instance()->queryForList("PermitNoForYearList", $values); +\end{verbatim} + +\subsection{Multiple Databases} +If you need access to more than one database from the same application, create +a DataMapper configuration file for that database and another Mapper class to +go with it. + +\subsection{\tt{TDomSqlMapBuilder} Configuration Options} +If you find that you already have loaded your DataMapper configuration +information as a \tt{SimpleXMLElement} instance within your application, the +\tt{TDomSqlMapBuilder} provides \tt{Configure} overloads for those types as +well. + +\section{Exploring the SQLMap PHP DataMapper API through the \tt{TSqlMapper}} +The \tt{TSqlMapper} instance acts as a facade to provide access the rest of +the DataMapper framework. The DataMapper API methods are shown in Example +4.11. + +\begin{example} +The SQLMap DataMapper API for PHP. +\begin{verbatim} + /* Query API */ + public function queryForObject($statementName, $parameter=null, $result=null); + public function queryForList($statementName, $parameter=null, $result=null, + $skip=-1, $max=-1); + public function queryForPagedList($statementName, $parameter=null, $pageSize=10); + public function queryForMap($statementName, $parameter=null, + $keyProperty=null, $valueProperty=null); + + public function insert($statementName, $parameter=null) + public function update($statementName, $parameter=null) + public function delete($statementName, $parameter=null) + + /* Connection API */ + public function openConnection() + public function closeConnection() + + /* Transaction API */ + public function beginTransaction() + public function commitTransaction() + public function rollBackTransaction() +\end{verbatim} +\end{example} + +Note that each of the API methods accept the name of the Mapped Statement as +the first parameter. The \tt{statementName} parameter corresponds to the +\tt{id} of the Mapped Statement in the Data Map definition (see +Section~\ref{section:3.3}). In each case, a \tt{parameterObject} also may be +passed. The following sections describe how the API methods work. + +\subsection{Insert, Update, Delete} +\begin{verbatim} + public function insert($statementName, $parameter=null) + public function update($statementName, $parameter=null) + public function delete($statementName, $parameter=null) +\end{verbatim} + +If a Mapped Statement uses one of the \tt{}, \tt{}, or +\tt{} statement-types, then it should use the corresponding API +method. The \tt{} element supports a nested \tt{} element +for generating primary keys (see Section~\ref{section:3.3.3}). If the +\tt{} stanza is used, then \tt{insert} returns the generated key; +otherwise a null object is returned. Both the \tt{update} and \tt{delete} +methods return the number of rows affected by the statement. + +\subsection{QueryForObject} +\begin{verbatim} +public function queryForObject($statementName, $parameter=null, $result=null); +\end{verbatim} + +If a Mapped Statement is expected to select a single row, then call it using +\tt{queryForObject}. Since the Mapped Statement definition specifies the +result class expected, the framework can both create and populate the result +class for you. Alternatively, if you need to manage the result object +yourself, say because it is being populated by more than one statement, you +can use the alternate form and pass your \tt{\$resultObject} as the third +parameter. + +\subsection{QueryForList} + +\begin{verbatim} +public function queryForList($statementName, $parameter=null, $result=null, + $skip=-1, $max=-1); +\end{verbatim} +If a Mapped Statement is expected to select multiple rows, then call it using +\tt{queryForList}. Each entry in the list will be an result object populated +from the corresponding row of the query result. If you need to manage the +\tt{\$resultObject} yourself, then it can be passed as the third parameter. If +you need to obtain a partial result, the fourth parameter \tt{\$skip} and +fifth parameter \tt{\$max} allow you to skip a number of records (the starting +point) and the maximum number to return. + + +\subsection{QueryForPagedList} +\begin{verbatim} + public function queryForPagedList($statementName, $parameter=null, $pageSize=10); +\end{verbatim} +We live in an age of information overflow. A database query often returns more +hits than users want to see at once, and our requirements may say that we need +to offer a long list of results a ``page'' at a time. If the query returns +1000 hits, we might need to present the hits to the user in sets of fifty, and +let them move back and forth between the sets. Since this is such a common +requirement, the framework provides a convenience method. + +The \tt{TSqlMapPagedList} interface includes methods for navigating through +pages (\tt{nextPage()}, \tt{previousPage()}, \tt{gotoPage(\$pageIndex)}) and +also checking the status of the page (\tt{getIsFirstPage()}, +\tt{getIsMiddlePage()}, \tt{getIsLastPage()}, \tt{getIsNextPageAvailable()}, +\tt{getIsPreviousPageAvailable()}, \tt{getCurrentPageIndex()}, +\tt{getPageSize()}). The total number of records available is not accessible +from the \tt{TSqlMapPagedList} interface, unless a virtual count is defined +using \tt{setVirtualCount(\$value)}, this should be easily accomplished by +simply executing a second statement that counts the expected results. + +\begin{mybox}{Tip:} +The \tt{queryForPagedList} method is convenient, but note that a larger set +(up to 3 times the page size) will first be returned by the database provider +and the smaller set extracted by the framework. The higher the page size, the +larger set that will be returned and thrown away. For very large sets, you may +want to use a stored procedure or your own query that uses \tt{\$skip} and +\tt{\$max} as parameters in \tt{queryForList}. +\end{mybox} + +\subsection{QueryForMap} +\begin{verbatim} +public function queryForMap($statementName, $parameter=null, + $keyProperty=null, $valueProperty=null); +\end{verbatim} +The \tt{queryForList} methods return the result objects within a \tt{TList} or +array instance. Alternatively, the \tt{queryForMap} returns a TMap or +associative array instance. The value of each entry is one of the result +objects. The key to each entry is indicated by the \tt{\$keyProperty} +parameter. This is the name of the one of the properties of the result object, +the value of which is used as the key for each entry. For example, If you +needed a set of \tt{Employee} objects, you might want them returned as a +\tt{TMap} keyed by each object's \tt{EmployeeNumber} property. + +If you don't need the entire result object in your result, you can add the +\tt{\$valueProperty} parameter to indicate which result object property should +be the value of an entry. For example, you might just want the +\tt{EmployeeName} keyed by \tt{EmployeeNumber}. + +\subsection{Transaction} +The DataMapper API includes methods to demarcate transactional boundaries. A +transaction can be started, committed and/or rolled back. You can call the +transaction methods from the \tt{TSqlMapper} instance. + +\begin{verbatim} +// Begin a transactional session using Adodb transaction API +public function beginTransaction() + +// Commit a transaction, uses Adodb transaction API +public function commitTransaction() + +// RollBack a transaction, uses Adodb transaction API +public void RollBackTransaction() +\end{verbatim} + +\begin{example}\label{example:9.15} +Using transactions +\begin{verbatim} +try +{ + $sqlMap->beginTransaction(); + $item = $sqlMap->queryForObject("getItem", $itemId); + $item->setDescription($newDescription); + $sqlMap->update("updateItem", $item); + $sqlMap->commitTransaction(); +} +catch +{ + $sqlMap->rollBackTransaction(); +} +\end{verbatim} +\end{example} + +\section{Coding Examples} +\begin{example}\label{example:10.1} +Executing Update (insert, update, delete) +\begin{verbatim} +$product = new Product(); +$product->setId(1); +$product->setDescription('Shih Tzui'); + +$key = $sqlMap->insert('insertProduct', $product); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.2} +Executing Query for Object (select) +\begin{verbatim} +$key = 1; +$product = $sqlMap->queryForObject ('getProduct', $key); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.3} +Executing Query for Object (select) With Preallocated Result Object +\begin{verbatim} +$customer = new Customer(); + +$sqlMap->beginTransaction(); + +$sqlMap->queryForObject('getCust', $parameter, $customer); +$sqlMap->queryForObject('getAddr', $parameter, $customer); +$sqlMap->commitTransaction(); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.4} +Executing Query for List (select) +\begin{verbatim} +$list = $sqlMap->queryForList ('getProductList'); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.4} +Executing Query for List (select) With Result Boundaries +\begin{verbatim} +$list = $sqlMap->queryForList ('getProductList', $key, null, 0, 40); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.5} +Executing Query for Paginated List (select) +\begin{verbatim} +$list = $sqlMap->queryForPagedList ('getProductList', null, 10); +$list->nextPage(); +$list->previousPage(); +\end{verbatim} +\end{example} + +\begin{example}\label{example:10.6} +Executing Query for Map +\begin{verbatim} + $map = $sqlMap->QueryForMap('getProductList', null, 'productCode'); + $product = $map['EST-93']; +\end{verbatim} +\end{example} diff --git a/docs/sqlmap/latex/diagram.pdf b/docs/sqlmap/latex/diagram.pdf new file mode 100644 index 00000000..5e64e388 Binary files /dev/null and b/docs/sqlmap/latex/diagram.pdf differ diff --git a/docs/sqlmap/latex/example1.png b/docs/sqlmap/latex/example1.png new file mode 100644 index 00000000..b5241de6 Binary files /dev/null and b/docs/sqlmap/latex/example1.png differ diff --git a/docs/sqlmap/latex/grid1.png b/docs/sqlmap/latex/grid1.png new file mode 100644 index 00000000..845b9581 Binary files /dev/null and b/docs/sqlmap/latex/grid1.png differ diff --git a/docs/sqlmap/latex/grid2.png b/docs/sqlmap/latex/grid2.png new file mode 100644 index 00000000..dcafc33d Binary files /dev/null and b/docs/sqlmap/latex/grid2.png differ diff --git a/docs/sqlmap/latex/sqlmap.tex b/docs/sqlmap/latex/sqlmap.tex new file mode 100644 index 00000000..57faaab3 --- /dev/null +++ b/docs/sqlmap/latex/sqlmap.tex @@ -0,0 +1,143 @@ +\documentclass{book} +%\usepackage{graphicx} + +\usepackage[pdftex]{hyperref} +\usepackage[pdftex]{graphicx} + +\usepackage{amsmath} +\usepackage{amsfonts} +\usepackage{amssymb} + +\usepackage{fancyhdr} + +\newtheorem{example}{Example}[section] + +\renewcommand{\tt}[1]{\texttt{#1}} + +%---------- fonts Type 1 ----------------- +\usepackage{times} +\usepackage[T1]{fontenc} +\usepackage{textcomp} + +%------------------------Page set-up----------------------------------------- + +\renewcommand{\baselinestretch}{1.25} +\setlength{\hoffset}{-1in} +\setlength{\oddsidemargin}{3.5cm} +\setlength{\evensidemargin}{3.5cm} +\setlength{\topmargin}{0cm} +\setlength{\footskip}{2cm} +\setlength{\headheight}{14pt} +\setlength{\marginparwidth}{0cm} +\setlength{\marginparsep}{0cm} +\setlength{\marginparpush}{0cm} +\setlength{\textwidth}{15cm} +\setlength{\parindent}{0cm} +\setlength{\parskip}{0.75\baselineskip} + +%------------------------------------------------------------------------------ + +\newsavebox{\fmboxb} +\newenvironment{mybox}[1] + {\vspace{-2mm}\begin{center}\begin{lrbox}{\fmboxb}\hspace{2mm} + \begin{minipage}{0.85\textwidth} \vspace{2mm}\small \textbf{#1}} + { \vspace{2mm} \end{minipage} + \hspace{2mm}\end{lrbox}\fbox{\usebox{\fmboxb}}\end{center}} + + +%---- change link style ---- +\hypersetup{colorlinks, linkcolor=blue, pdfstartview={FitH}} + + +% Pages and Fancyheadings stuff +%----------------------------------------------------------------------- +\cfoot{\thepage} +\fancyhead[LE,RO]{} +\fancyhead[LO]{\nouppercase{\scshape\rightmark}} +\fancyhead[RE]{\nouppercase{\scshape\leftmark}} +%----------------------------------------------------------------------- + +%----------------- TITLE -------------- + +\title{\Huge \bfseries SQLMap PHP DataMapper Application Framework -- +v1.0 + \thanks{Copyright 2006. All Rights Reserved.} +} +\author{Wei Zhuo} +\date{\today} + + +%-------------- BEGIN DOCUMENT ------------------ + + +\begin{document} + +\maketitle + +\pagestyle{plain} +\addcontentsline{toc}{chapter}{Contents} +\pagenumbering{roman} +\tableofcontents + +\chapter*{Legal Notice} +\addcontentsline{toc}{chapter}{Legal Notice} + +Copies of this document may be made for your own use and for distribution to +others, provided that you do not charge any fee for such copies and further +provided that each copy contains this Copyright Notice, whether distributed in +print or electronically. + +This document is largely based on the iBATIS.NET -- DataMapper Application +Framework Developer Guide. + +\chapter*{License} +\addcontentsline{toc}{chapter}{License} +SQLMap for PHP is free software released under the terms of the following BSD license.\\ +Copyright 2004-2006, PradoSoft (http://www.pradosoft.com)\\ +All rights reserved. + +Redistribution and use in source and binary forms, with or without +modification, are permitted provided that the following conditions are met: +\begin{enumerate} + \item Redistributions of source code must retain the above copyright notice, +this list of conditions and the following disclaimer. + + \item Redistributions in binary form must reproduce the above copyright notice, +this list of conditions and the following disclaimer in the documentation +and/or other materials provided with the distribution. + +\item Neither the name of the developer nor the names of its contributors may +be used to endorse or promote products derived from this software without +specific prior written permission. +\end{enumerate} + +\begin{verbatim} +THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" +AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE +IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE +ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE +FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL +DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR +SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) +HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT +LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT +OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +\end{verbatim} + + +\newpage + +\pagestyle{fancyplain} +\pagenumbering{arabic} + +\include{ch1} +\include{ch2} +\include{ch3} +\include{ch4} +\include{ch5} +\include{ch6} +\include{ch7} +\include{ch8} +\include{ch9} + +\end{document} diff --git a/docs/sqlmap/latex/sqlmap_tut.tex b/docs/sqlmap/latex/sqlmap_tut.tex new file mode 100644 index 00000000..728fc91f --- /dev/null +++ b/docs/sqlmap/latex/sqlmap_tut.tex @@ -0,0 +1,96 @@ +\documentclass{article} +%\usepackage{graphicx} + +\usepackage[pdftex]{hyperref} +\usepackage[pdftex]{graphicx} + +\usepackage{amsmath} +\usepackage{amsfonts} +\usepackage{amssymb} + + +\newtheorem{example}{Example}[section] + +\renewcommand{\tt}[1]{\texttt{#1}} + +%---------- fonts Type 1 ----------------- +\usepackage{times} +\usepackage[T1]{fontenc} +\usepackage{textcomp} + +%------------------------Page set-up----------------------------------------- + +\renewcommand{\baselinestretch}{1.25} +\setlength{\hoffset}{-1in} +\setlength{\oddsidemargin}{3.5cm} +\setlength{\evensidemargin}{3.5cm} +\setlength{\topmargin}{0cm} +\setlength{\footskip}{2cm} +\setlength{\headheight}{14pt} +\setlength{\marginparwidth}{0cm} +\setlength{\marginparsep}{0cm} +\setlength{\marginparpush}{0cm} +\setlength{\textwidth}{15cm} +\setlength{\parindent}{0cm} +\setlength{\parskip}{0.75\baselineskip} + +\hypersetup{colorlinks, linkcolor=blue, pdfstartview={FitH}} + +%------------------------------------------------------------------------------ + +\newsavebox{\fmboxb} +\newenvironment{mybox}[1] + {\vspace{-2mm}\begin{center}\begin{lrbox}{\fmboxb}\hspace{2mm} + \begin{minipage}{0.85\textwidth} \vspace{2mm}\small \textbf{#1}} + { \vspace{2mm} \end{minipage} + \hspace{2mm}\end{lrbox}\fbox{\usebox{\fmboxb}}\end{center}} + +%----------------- TITLE -------------- + +\title{\vspace{-2.5cm} \bfseries SQLMap PHP DataMapper Tutorial + \thanks{Copyright 2006. All Rights Reserved.} +} +\author{Wei Zhuo} +\date{\today} + + +%-------------- BEGIN DOCUMENT ------------------ + + +\begin{document} + +\maketitle + +This tutorial takes an ``over-the-shoulder'' Cookbook approach. We'll define a +simple data access problem and use SQLMap to solve it for us. + + +\section*{Legal Notice} + +Copies of this document may be made for your own use and for distribution to +others, provided that you do not charge any fee for such copies and further +provided that each copy contains this Copyright Notice, whether distributed in +print or electronically. + +This document is largely based on the iBATIS.NET -- DataMapper Application +Tutorial. + +\subsection*{License Information} +SQLMap for PHP is free software released under the terms of the following BSD +license. Copyright 2004-2006, PradoSoft (http://www.pradosoft.com) All rights +reserved. + +\subsection*{Disclaimer} +SQLMap PHP DataMapper MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE +INFORMATION IN THIS DOCUMENT. The names of actual companies and products +mentioned herein may be the trademarks of their respective owners. + +\subsection*{Remark} +Original writing by Clinton Begin, Ted Husted and Gilles Bayon. + +\include{tut1} +\include{tut2} +\include{tut3} + + +\end{document} diff --git a/docs/sqlmap/latex/tut1.tex b/docs/sqlmap/latex/tut1.tex new file mode 100644 index 00000000..8f7a4a26 --- /dev/null +++ b/docs/sqlmap/latex/tut1.tex @@ -0,0 +1,257 @@ +\section{Test First!} + +Let's say that our most important client has a database and one of the tables +in the database is a list of people. Our client tells us: + +``We would like to use a web application to display the people in this table +and to add, edit, and delete individual records.'' + +Not a complicated story, but it will cover the CRUD most developers want to +learn first. :) Let's start with the people table that the client mentioned. +Since we're keeping it simple, we'll say it's a table in an Access database. +The table definition is shown as Example~\ref{example:1}. + +\begin{example}\label{example:1} +The Person Table +\begin{verbatim} +Name Type Size +PER_ID Long Integer 4 +PER_FIRST_NAME Text 40 +PER_LAST_NAME Text 40 +PER_BIRTH_DATE Date/Time 8 +PER_WEIGHT_KG Double 8 +PER_HEIGHT_M Double 8 +\end{verbatim} +\end{example} + +\begin{mybox}{Tip:} + This example is bundled with a SQLite database file ``Data/test.db'' + that contains the \tt{Person} table and some data, ready to use. +\end{mybox} + +The first thing our story says is that client would like to display a list of +people. Example~\ref{example:2} shows our test for that. + +\begin{example}\label{example:2} +Tests/PersonTest.php +\begin{verbatim} +queryForList("SelectAll"); + + //test it + $this->assertNotNull($people, "Person list is not returned"); + $this->assertTrue($people->getCount() > 0, "Person list is empty"); + $person = $people[0]; + $this->assertNotNull($person, "Person not returned"); + } +} +?> +\end{verbatim} +\end{example} + +Well, Example 2 sure looks easy enough! We ask a method to ``select all'', and +it returns a list of person objects. But, what code do we need to write to +pass this test? + +\begin{mybox}{Note:} + Save the PersonTest.php into a \tt{Tests} directory. The unit tests are + written for the SimpleTest framework (http://simpletest.sf.net). +\end{mybox} + +Now, to setup the testing framework, suppose you have the \tt{SimpleTest} +framework installed. Then we need to create an entry file to run the tests. +See the \tt{SimpleTest} documentation for further details on setting up tests. + +\begin{example}\label{example:2a} +Unit test entry file, \tt{run\_tests.php}. +\begin{verbatim} +addTestFile('Tests/PersonTest.php'); $test->run(new HtmlReporter()); +?> +\end{verbatim} +\end{example} +To run the tests, point your browser to the ``run\_test.php'' script file +served from your web server. + +Let's see. The test uses a list of person objects. We could start with a blank +object, just to satisfy the test, and add the display properties later. But +let's be naughty and skip a step. Our fully-formed person object is shown in +Example~\ref{example:3}. + +\begin{example}\label{example:3} +Models/Person.php +\begin{verbatim} +_birthDate; + } + + public function setBirthDate($value) + { + $this->_birthDate = $value; + } +} +?> +\end{verbatim} +\end{example} + +OK, that was fun! The \tt{\$this->assertXXX} methods are built into +\tt{UnitTestCase} class. So to run Example~\ref{example:2}, we just need the +\tt{TMapper} object and \tt{queryForList} method. Wonderfully, the SQLMap +DataMapper framework has a \tt{TMapper}class built into it that will work just +fine for for us to use in this tutorial, so we don't need to write that +either. + +When the \tt{TMapper->instance()} method is called, an instance of the SQLMap +\tt{TSqlMapper} class is returned that has various methods available such as +\tt{queryForList}. In this example, the SQLMap \tt{TSqlMapper->queryForList()} +method executes our SQL statement (or stored procedure) and returns the result +as a list. Each row in the result becomes an entry in the list. Along with +\tt{queryForList()}, there are also \tt{delete()}, \tt{insert()}, +\tt{queryForObject()}, \tt{queryForPagedList()} and a few other methods in the +SQLMap API. (See Chapter 9 in the SQLMap DataMapper Developer Guide for +details.) + +Looking at Example~\ref{example:2}, we see that the \tt{queryForList()} method +takes the name of the statement we want to run. OK. Easy enough. But where +does SQLMap get the ``SelectAll'' statement? Some systems try to generate SQL +statements for you, but SQLMap specializes in data mapping, not code +generation. It's our job (or the job of our database administrator) to craft +the SQL or provide a stored procedure. We then describe the statement in an +XML element, like the one shown in Example~\ref{example:4}. + +\begin{example}\label{example:4} +We use XML elements to map a database statement to an application object. +\begin{verbatim} + + + + +\end{verbatim} +\end{example} + +The SQLMap mapping documents can hold several sets of related elements, like +those shown in Example~\ref{example:4}. We can also have as many mapping +documents as we need to help organize our code. Additionally, having multiple +mapping documents is handy when several developers are working on the project +at once. + +So, the framework gets the SQL code for the query from the mapping, and plugs +it into a prepared statement. But, how does SQLMap know where to find the +table's datasource? + +Surprise! More XML! You can define a configuration file for each datasource +your application uses. Example~\ref{example:5} shows a configuration file for +our SQLite database. + +\begin{example}\label{example:5} +sqlmap.config - a configuration file for our SQLite database +\begin{verbatim} + + + + + + + + + +\end{verbatim} +\end{example} + +The \tt{} specifies the database provider class, in this case +\tt{TAdodbProvider} using the Adodb library. The \tt{} tag +specifies the database connection details. In this case, for an SQLite +database, we just need the driver name, and the host that points to the actual +SQLite database file. + +The last part of the configuration file ("sqlMaps") is where we list our +mapping documents, like the one shown back in Example~\ref{example:4}. We can +list as many documents as we need here, and they will all be read when the +configuration is parsed. + +OK, so how does the configuration get parsed? + +Look back at Example~\ref{example:2}. The heart of the code is the call to the +``\tt{TMapper}'' object (under the remark "try it"). The \tt{TMapper} object +is a singleton that handles the instantiation and configuration of an SQLMap +\tt{TSqlMapper} object, which provides a facade to the SQLMap DataMapper +framework API. + +The first time that the \tt{TMapper} is called, it reads in the +\tt{sqlmap.config} file and associated mapping documents to create an instance +of the \tt{TSqlMapper} class. On subsequent calls, it reuses the +\tt{TSqlMapper} object so that the configuration is re-read only when files +change. + +The framework comes bundled with a default \tt{TMapper} class for you to use +immediately to get access to the SQLMap SqlMapper object. If you want to use a +different name other than \tt{sqlmap.config} at the default location for the +configuration file, or need to use more than one database and have one +SqlMapper per database, you can also write your own class to mimic the role of +the Mapper class view by copying and modifying the standard version. + +\begin{mybox}{Tip:} + You can also call \tt{TMapper::configure('/path/to/your/sqlmap.config')} + to configure the \tt{TMapper} for a specific configuration file. +\end{mybox} + +If we put this all together into a solution, we can ``green bar'' our test. At +this point you should have the following files. +\begin{verbatim} +Data/person.xml % Mapping file. +Data/test.db % SQLite database file. + +Models/Person.php % Person class file. + +Tests/PersonTest.php % Unit test case for Person mapping. + +run_tests.php % Unit test entry point. +sqlmap.config % SQLMap configuration file. +\end{verbatim} + +Run the tests by pointing your browser URL to the ``run\_tests.php'' server +file. + +\begin{figure}[!h] + \centering + \includegraphics[width=0.7\textwidth]{example1} + \caption{Green Bar!} + \label{fig:diagram} +\end{figure} diff --git a/docs/sqlmap/latex/tut2.tex b/docs/sqlmap/latex/tut2.tex new file mode 100644 index 00000000..b5389138 --- /dev/null +++ b/docs/sqlmap/latex/tut2.tex @@ -0,0 +1,128 @@ +\section{Playtest second!} +Now that we have a passing test, we want to display some results as web pages. +The following examples utilize the Prado framework to display and manipulate +the database through SQLMap. Since SQLMap framework and Prado framework solve +different problems, they are both fairly independent, they can be used +together or separately. + +\subsection{SQLMap and Prado} +To setup Prado, we need to create the follow files and directory structure +under our \tt{example/WebView} directory. +\begin{verbatim} +assets/ % application public assets + +protected/pages/Home.page % default page +protected/pages/Home.php % default page class +protected/runtime/ % run time data + +protected/application.xml % application configuration + +index.php % application entry point +\end{verbatim} + +The \tt{application.xml} and \tt{assets} directory are not necessary but we +will make use of them later. The \tt{application.xml} is used to define some +directory aliases and override the data source definitions in the +\tt{sqlmap.config}. This is because SQLite database files are defined +relatively, otherwise we don't need to override the data source definitions. +The example \tt{application.xml} is show in Example~\ref{example:2.0}. + +\begin{example}\label{example:2.0} +Prado application.xml, defines path aliases and override SQLite database +location. +\begin{verbatim} + + + + + + + + + + + + + + + +\end{verbatim} +\end{example} + +The entry point to a Prado application in this example is \tt{index.php}. +Example~\ref{example:2.1} shows the basic \tt{index.php} content. +\begin{example}\label{example:2.1} +Prado application entry point, \tt{index.php}. +\begin{verbatim} +run(); +?> +\end{verbatim} +\end{example} + +Now we are ready to setup a page to display our list of people. +Example~\ref{example:7} shows the Prado code for our display page. The key +piece is the TDataGrid. + +\begin{example}\label{example:7} +Prado page for our Person list, \tt{Home.page}. +\begin{verbatim} + + + + Person + + + +

Person List

+ + + + + + +\end{verbatim} +\end{example} + +Of course, we still need to populate that TDataGrid. Example~\ref{example:8} +shows the PHP code for \tt{Home.php}. The operative method is \tt{loadData()}. +The rest is supporting code. + +\begin{example}\label{example:8} +\tt{Home.php} class for our Person list page +\begin{verbatim} +Application->getModule('SQLMap')->getClient(); + $this->personList->DataSource = $sqlmap->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } +} +?> +\end{verbatim} +\end{example} + +If we run this now, we'll get a list like the one shown in +Figure~\ref{figure:2}. +\begin{figure}[!h] + \centering + \includegraphics[width=0.75\textwidth]{grid1} + \caption{A quick-and-dirty Person List} + \label{figure:2} +\end{figure} diff --git a/docs/sqlmap/latex/tut3.tex b/docs/sqlmap/latex/tut3.tex new file mode 100644 index 00000000..4e16ab28 --- /dev/null +++ b/docs/sqlmap/latex/tut3.tex @@ -0,0 +1,227 @@ +\section{Test, test, again ...} +Of course, tweaking the Person List display is not going to be the end of it. +Clients always want more, and now ours wants to edit, add, or delete records. +Let's write some tests for these new tasks, as shown in +Example~\ref{example:9}. + +\begin{example}\label{example:9} +New stories, new tests +\begin{verbatim} + function testPersonUpdate() + { + $expect = "wei"; + $edited = "Nah"; + + //get it; + $person = TMapper::instance()->queryForObject("Select", 1); + + //test it + $this->assertNotNull($person); + $this->assertEqual($expect, $person->FirstName); + + //change it + $person->FirstName = $edited; + TMapper::instance()->update("Update", $person); + + //get it again + $person = TMapper::instance()->queryForObject("Select", 1); + + //test it + $this->assertEqual($edited, $person->FirstName); + + //change it back + $person->FirstName = $expect; + TMapper::instance()->update("Update", $person); + } + + function testPersonDelete() + { + //insert it + $person = new Person; + $person->ID = -1; + TMapper::instance()->insert("Insert", $person); + + //delte it + $count = TMapper::instance()->delete("Delete", -1); + $this->assertEqual(1, $count); + } +\end{verbatim} +\end{example} + +Not the best tests ever written, but for now, they will do :) + +To make the new tests work, we'll need some new mapping statements. +Example~\ref{example:10} shows the complete mapper document that we've called +\tt{personHelper.xml}. + +\begin{example} +The new and improved mapper document +\begin{verbatim} + + + + + + + insert into PERSON + (PER_ID, PER_FIRST_NAME, PER_LAST_NAME, + PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M) + values + (#ID#, #FirstName#, #LastName#, + #BirthDate#, #WeightInKilograms#, #HeightInMeters#) + + + + update PERSON set + PER_FIRST_NAME = #FirstName#, + PER_LAST_NAME = #LastName#, + PER_BIRTH_DATE = #BirthDate#, + PER_WEIGHT_KG = #WeightInKilograms#, + PER_HEIGHT_M = #HeightInMeters# + where PER_ID = #ID# + + + + delete from PERSON + where PER_ID = #value# + + +\end{verbatim} +\end{example} +Well, waddya know, if run our tests now, we are favored with a green bar!. It +all works! + +\begin{mybox}{Note:} +Though, of course, things usually do not work perfectly the first time! We +have to fix this and that, and try, try, again. But SimpleTest makes trying +again quick and easy. You can changes to the XML mapping documents and rerun +the tests! No muss, no fuss. +\end{mybox} + +Turning back to our Prado page, we can revamp the TDataGrid to allow in-place +editing and deleting. To add records, we provide a button after the grid that +inserts a blank person for client to edit. The page code is shown as +Example~\ref{example:11}. +\begin{example}\label{example:11} +Prado page code for our enhanced TDataGrid +\begin{verbatim} + + + + + + + + + +\end{verbatim} +\end{example} + +Example~\ref{example:12} shows the corresponding methods from page PHP class. + +\begin{example}\label{example:12} +The page class code for our enhanced TDataGrid +\begin{verbatim} + private function sqlmap() + { + return $this->Application->getModule('SQLMap')->getClient(); + } + + private function loadData() + { + $this->personList->DataSource = + $this->sqlmap()->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } + + protected function editPerson($sender,$param) + { + $this->personList->EditItemIndex=$param->Item->ItemIndex; + $this->loadData(); + } + + protected function deletePerson($sender, $param) + { + $id = $this->getKey($sender, $param); + $this->sqlmap()->update("Delete", $id); + $this->loadData(); + } + + protected function updatePerson($sender, $param) + { + $person = new Person(); + $person->FirstName = $this->getText($param, 0); + $person->LastName = $this->getText($param, 1); + $person->HeightInMeters = $this->getText($param, 2); + $person->WeightInKilograms = $this->getText($param, 3); + $person->ID = $this->getKey($sender, $param); + $this->sqlmap()->update("Update", $person); + $this->refreshList($sender, $param); + } + + protected function addNewPerson($sender, $param) + { + $person = new Person; + $person->FirstName = "-- New Person --"; + $this->sqlmap()->insert("Insert", $person); + $this->loadData();; + } + + protected function refreshList($sender, $param) + { + $this->personList->EditItemIndex=-1; + $this->loadData(); + } + + private function getText($param, $index) + { + $item = $param->Item; + return $item->Cells[$index]->Controls[0]->Text; + } + + private function getKey($sender, $param) + { + return $sender->DataKeys[$param->Item->DataSourceIndex]; + } +\end{verbatim} +\end{example} + +OK, we are CRUD complete! There's more we could do here. In particular, we +should add validation methods to prevent client from entering alphabetic +characters where only numbers can live. But, that's a different Prado +tutorial, and this is an SQLMap DataMapper tutorial. + +\begin{figure}[!h] + \centering + \includegraphics[width=0.75\textwidth]{grid2} + \caption{Person List CRUD} + \label{figure:2} +\end{figure} -- cgit v1.2.3