diff options
| author | wei <> | 2006-07-14 09:20:45 +0000 |
|---|---|---|
| committer | wei <> | 2006-07-14 09:20:45 +0000 |
| commit | 4b78404c20490a615459267426ce9e6737bf4485 (patch) | |
| tree | be68ab7a2155980b05e5ab9f454e991e93007563 /demos/sqlmap/protected/pages/Manual | |
| parent | 143980b6dab8ad87c44518e5b7befb614fb83b85 (diff) | |
Moving files.
Diffstat (limited to 'demos/sqlmap/protected/pages/Manual')
24 files changed, 2774 insertions, 0 deletions
diff --git a/demos/sqlmap/protected/pages/Manual/BigPicture.page b/demos/sqlmap/protected/pages/Manual/BigPicture.page new file mode 100644 index 00000000..7857d6ec --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/BigPicture.page @@ -0,0 +1,151 @@ +<com:TContent ID="body">
+<h1>The Big Picture</h1>
+<p>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.</p>
+
+<h1>What does it do?</h1>
+<p>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].</p>
+
+<p>You provide the database and the objects; SQLMap provides the mapping layer
+that goes between the two.</p>
+
+<h1>How does it work?</h1>
+
+<p>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:</p>
+
+<p>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.</p>
+
+<p class="tip"><b class="tip">Tip:</b>
+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.
+</p>
+
+<img src=<%~ diagram.png %> alt="SQLMap DataMapper work flow" id="fig:diagram.png" class="figure"/>
+<div class="caption"><b>Figure 1:</b> SQLMap DataMapper work flow</div>
+
+<p>Here's a high level description of the work flow shown in the figure above:
+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.</p>
+
+<p>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.</p>
+
+<p>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.</p>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+TMapper::instance()->insert("InsertLineItem", $lineItem);
+</com:TTextHighlighter>
+
+<p>If your database is generating the primary keys, the generated key can be
+returned from the same method call, like this:</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+$myKey = TMapper::instance()->insert("InsertLineItem", $lineItem);
+</com:TTextHighlighter>
+
+<p>The following example shows an XML descriptor for "InsertLineItem".
+<com:TTextHighlighter Language="xml" CssClass="source">
+<insert id="InsertLineItem" parameterClass="LineItem">
+ INSERT INTO [LinesItem]
+ (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice)
+ VALUES
+ (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#)
+ <selectKey type="post" resultClass="int" property="Id" >
+ select @@IDENTITY as value
+ </selectKey>
+</insert>
+</com:TTextHighlighter>
+</p>
+
+<p>The <tt><selectKey></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:
+<com:TTextHighlighter Language="php" CssClass="source">
+$productList = Mapper::instance()->queryForList("selectProduct",$categoryKey);
+</com:TTextHighlighter>
+Or just one, if that's all you need:
+<com:TTextHighlighter Language="php" CssClass="source">
+$product = Mapper::instance()->queryForObject("selectProduct",$categoryKey);
+</com:TTextHighlighter>
+</p>
+
+<p>Of course, there's more, but this is SQLMap from 10,000 meters. (For a longer,
+gentler introduction, see the <a href="?page=Tutorial.TestFirst">Tutorial</a>.)
+The <a href=" ?page=Manual.DataMapperConfiguration">Data Map definition</a> files describes
+where the statement for "InsertLineItem" would
+be defined. The <a href="?page=Manual.Installing">Installation and Setup</a> section describes
+the "bootstrap" configuration file that exposes SQLMap to your application.</p>
+
+<h1>Is SQLMap the best choice for my project?</h1>
+<p>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.</p>
+
+<p>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.</p>
+
+<p>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.</p>
+
+<p>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
+<ul>
+ <li>Have complete control over your database implementation.</li>
+ <li>Do not have a Database Administrator or SQL guru on the team.</li>
+ <li>Need to model the problem domain outside the database as an object graph.</li>
+</ul>
+Likewise, the best time to use a Data Mapper, like SQLMap, is when:
+<ul>
+ <li>You do not have complete control over the database implementation, or want to
+continue to access a legacy database as it is being refactored.</li>
+ <li>You have database administrators or SQL gurus on the team.</li>
+ <li>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.</li>
+</ul>
+</p>
+
+<p>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!</p>
+
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/BuildingTSqlMapper.page b/demos/sqlmap/protected/pages/Manual/BuildingTSqlMapper.page new file mode 100644 index 00000000..87165da2 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/BuildingTSqlMapper.page @@ -0,0 +1,73 @@ +<com:TContent ID="body">
+<h1>Using SQLMap PHP DataMapper</h1>
+<p>The SQLMap DataMapper API provides four core functions:</p>
+<ol>
+ <li>build a <tt>TSqlMapper</tt> instance from a configuration file or cache</li>
+ <li>execute an update query (including insert and delete)</li>
+ <li>execute a select query for a single object</li>
+ <li>execute a select query for a list of objects</li>
+</ol>
+
+<p>The API also provides support for retrieving paginated lists and managing
+transactions.</p>
+
+<h1>Building a <tt>TSqlMapper</tt> instance</h1>
+<p>An XML document is a wonderful tool for describing a database configuration
+, 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.</p>
+
+<p>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</tt> object. The <tt>TSqlMapper</tt> object provides access to the rest
+of the framework. The following example shows a singleton <tt>TMapper</tt> that is
+similar to the one bundled with the framework.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+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;
+ }
+}
+</com:TTextHighlighter>
+
+<p>To obtain the <tt>TSqlMapper</tt> instance, first configure the mapper once.</p>
+<com:TTextHighlighter Language="php" CssClass="source">
+TMapper::configure('path/to/sqlmap.config');
+</com:TTextHighlighter>
+
+<p>The <tt>TDomSqlMapBuilder</tt> object will go throught the the <tt>sqlmap.config</tt>
+file and build a <tt>TSqlMapper</tt> instance. To use <tt>TSqlMapper</tt> in your
+application, specify one of the <tt>TSqlMapper</tt> methods. Here's an example:</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+$list = TMapper::instance()->queryForList("PermitNoForYearList", $values);
+</com:TTextHighlighter>
+
+<h2>Multiple Databases</h2>
+<p>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.</p>
+
+<h2><tt>TDomSqlMapBuilder</tt> Configuration Options</h2>
+<p>If you find that you already have loaded your DataMapper configuration
+information as a <tt>SimpleXMLElement</tt> instance within your application, the
+<tt>TDomSqlMapBuilder</tt> provides <tt>Configure</tt> overloads for those types as
+well.</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/CacheModels.page b/demos/sqlmap/protected/pages/Manual/CacheModels.page new file mode 100644 index 00000000..94f20f3d --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/CacheModels.page @@ -0,0 +1,113 @@ +<com:TContent ID="body">
+
+<h1>Cache Models</h1>
+<p>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><cacheModel></tt> element.
+</p>
+
+<p>The results from a query Mapped Statement can be cached simply by specifying
+the <tt>cacheModel</tt> 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</tt> element as
+follows:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<cacheModel id="product-cache" implementation="LRU" >
+ <flushOnExecute statement="insertProduct"/>
+ <flushOnExecute statement="updateProduct"/>
+ <flushOnExecute statement="deleteProduct"/>
+ <property name="CacheSize" value="100"/>
+</cacheModel>
+</com:TTextHighlighter>
+
+<p>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</tt> attribute is either a class name, or an alias for one
+of the included implementations (see below). The cache will be flushed
+whenever the <tt>insertProduct</tt>, <tt>updateProduct</tt>, or <tt>deleteProduct</tt>
+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:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="getProductList" cacheModel="product-cache">
+ select * from PRODUCT where PRD_CAT_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<h1>Cache Implementation</h1>
+<p>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</tt> element as discussed above. The class name specified
+must be an implementation of the <tt>ISqlMapCache</tt> 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</tt>. Currently there are 2 implementations included with the SQLMap PHP DataMapper.</p>
+
+<div class="info"><b class="tip">Info:</b>
+The cache implementations, LRU and FIFO cache below do not persist across
+requests. That is, once the request is complete, all cache data is lost.
+These caches are useful queries that results in the same repeated data during
+the current request.
+</div>
+
+<h2>Least Recently Used [LRU] Cache</h2>
+<p>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.).</p>
+
+<p>The LRU implementation is configured as follows:</p>
+<com:TTextHighlighter Language="xml" CssClass="source">
+<cacheModel id="product-cache" implementation="LRU" >
+ <flushOnExecute statement="insertProduct"/>
+ <flushOnExecute statement="updateProduct"/>
+ <flushOnExecute statement="deleteProduct"/>
+ <property name="CacheSize" value="100"/>
+</cacheModel>
+</com:TTextHighlighter>
+
+<p>Only a single property is recognized by the LRU cache implementation. This
+property, named <tt>CacheSize</tt> 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.</p>
+
+<h2>FIFO Cache</h2>
+<p>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.</p>
+
+<p>The FIFO implementation is configured as follows:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<cacheModel id="product-cache" implementation="FIFO" >
+ <flushOnExecute statement="insertProduct"/>
+ <flushOnExecute statement="updateProduct"/>
+ <flushOnExecute statement="deleteProduct"/>
+ <property name="CacheSize" value="100"/>
+</cacheModel>
+</com:TTextHighlighter>
+
+<p>Only a single property is recognized by the FIFO cache implementation. This
+property, named <tt>CacheSize</tt> 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.</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/CodingExamples.page b/demos/sqlmap/protected/pages/Manual/CodingExamples.page new file mode 100644 index 00000000..bbd1488e --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/CodingExamples.page @@ -0,0 +1,55 @@ +<com:TContent ID="body">
+<h1>Cookbook sample</h1>
+
+<h2>Executing Update (insert, update, delete)</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$product = new Product();
+$product->setId(1);
+$product->setDescription('Shih Tzui');
+
+$key = $sqlMap->insert('insertProduct', $product);
+</com:TTextHighlighter>
+
+<h2>Executing Query for Object (select)</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$key = 1;
+$product = $sqlMap->queryForObject ('getProduct', $key);
+</com:TTextHighlighter>
+
+
+<h2>Executing Query for Object (select) With Preallocated Result Object</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$customer = new Customer();
+
+$sqlMap->beginTransaction();
+
+$sqlMap->queryForObject('getCust', $parameter, $customer);
+$sqlMap->queryForObject('getAddr', $parameter, $customer);
+$sqlMap->commitTransaction();
+</com:TTextHighlighter>
+
+<h2>Executing Query for List (select)</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$list = $sqlMap->queryForList ('getProductList');
+</com:TTextHighlighter>
+
+<h2>Executing Query for List (select) With Result Boundaries</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$list = $sqlMap->queryForList ('getProductList', $key, null, 0, 40);
+</com:TTextHighlighter>
+
+<h2>Executing Query for Paginated List (select)</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+$list = $sqlMap->queryForPagedList ('getProductList', null, 10);
+$list->nextPage();
+$list->previousPage();
+</com:TTextHighlighter>
+
+<h2>Executing Query for Map</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+ $map = $sqlMap->QueryForMap('getProductList', null, 'productCode');
+ $product = $map['EST-93'];
+</com:TTextHighlighter>
+
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/ComplexProperties.page b/demos/sqlmap/protected/pages/Manual/ComplexProperties.page new file mode 100644 index 00000000..f62795f8 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ComplexProperties.page @@ -0,0 +1,247 @@ +<com:TContent ID="body">
+<h1>Complex Properties</h1>
+<p>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.</p>
+
+<p>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.</p>
+
+<p>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
+the following example, the "category" property of the
+"select-product-result" element is a complex property.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-product-result" class="product">
+ <result property="id" column="PRD_ID"/>
+ <result property="description" column="PRD_DESCRIPTION"/>
+ <result property="category" column="PRD_CAT_ID" select="selectCategory"/>
+</resultMap>
+
+<resultMap id="select-category-result" class="category">
+ <result property="id" column="CAT_ID"/>
+ <result property="description" column="CAT_DESCRIPTION"/>
+</resultMap>
+
+<select id="selectProduct" parameterClass="int" resultMap="select-product-result">
+ select * from PRODUCT where PRD_ID = #value#
+</select>
+
+<select id="selectCategory" parameterClass="int" resultMap="select-category-result">
+ select * from CATEGORY where CAT_ID = #value#
+</select>
+</com:TTextHighlighter>
+
+<p>In the above example, 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.</p>
+
+<h1>Avoiding N+1 Selects (1:1)</h1>
+<p>A problem with the above example 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 the above example will always be
+N+1: 100+1=101.</p>
+
+<p>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 <a href="?page=Manual.CacheModels">Cache Models</a>
+more details about caches.</p>
+
+<p>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".</p>
+
+<p>The following example solves the same problem as the previous
+example, but uses a join instead of nested properties.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-product-result" class="product">
+ <result property="id" column="PRD_ID"/>
+ <result property="description" column="PRD_DESCRIPTION"/>
+ <result property="category" resultMapping="Category.CategoryResult" />
+</resultMap>
+
+<statement id="selectProduct" parameterClass="int" resultMap="select-product-result">
+ select *
+ from PRODUCT, CATEGORY
+ where PRD_CAT_ID=CAT_ID
+ and PRD_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<div class="info"><b class="tip">Lazy Loading vs. Joins (1:1):</b>
+<p>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.</p>
+
+<p>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. The two examples above 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. "<tt>===</tt>").</p>
+</div>
+
+<h1>Complex Collection Properties</h1>
+<p>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</tt> 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</tt> is that the property
+on the business object must be of type <tt>TList</tt>. For example, if a Category
+has a <tt>TList</tt> of Product instances, the mapping would look like this
+(assuming Category has a property called "ProductList" of <tt>TList</tt>.):</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-category-result" class="Category">
+ <result property="Id" column="CAT_ID"/>
+ <result property="Description" column="CAT_DESCRIPTION"/>
+ <result property="ProductList" column="CAT_ID" select="selectProductsByCatId"/>
+</resultMap>
+
+<resultMap id="select-product-result" class="Product">
+ <result property="Id" column="PRD_ID"/>
+ <result property="Description" column="PRD_DESCRIPTION"/>
+</resultMap>
+
+<statement id="selectCategory" parameterClass="int"
+ resultMap="select-category-result">
+ select * from CATEGORY where CAT_ID = #value#
+</statement>
+
+<statement id="selectProductsByCatId" parameterClass="int"
+ resultMap="select-product-result">
+ select * from PRODUCT where PRD_CAT_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<h1>Avoiding N+1 Select Lists (1:M and M:N)</h1>
+<p>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.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-category-result" class="Category">
+ <result property="Id" column="CAT_ID"/>
+ <result property="Description" column="CAT_DESCRIPTION"/>
+ <result property="ProductList" column="CAT_ID" select="selectProductsByCatId"/>
+</resultMap>
+
+<resultMap id="select-product-result" class="Product">
+ <result property="Id" column="PRD_ID"/>
+ <result property="Description" column="PRD_DESCRIPTION"/>
+</resultMap>
+
+<!-- This statement executes 1 time -->
+<statement id="selectCategory" parameterClass="int"
+ resultMap="select-category-result">
+ select * from CATEGORY where CAT_ID = #value#
+</statement>
+
+<!-- This statement executes N times (once for each category returned above)
+ and returns a list of Products (1:M) -->
+<statement id="selectProductsByCatId" parameterClass="int"
+ resultMap="select-product-result">
+ select * from PRODUCT where PRD_CAT_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<h2>1:N and M:N Solution?</h2>
+<p>
+One way to avoid multiple SQL queries is to use Joins in the query
+together with the <tt>groupBy</tt> attribute in <tt><resultMap></tt> and
+the <tt>resultMapping</tt> attribute of <tt><result></tt>.
+The following example selects all the categories and the corresponding
+products using 1 query.
+</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-category-result" class="Category" groupBy="CAT_ID">
+ <result property="Id" column="CAT_ID"/>
+ <result property="Description" column="CAT_DESCRIPTION"/>
+ <result property="ProductList" resultMapping="select-product-result"/>
+</resultMap>
+
+<resultMap id="select-product-result" class="Product">
+ <result property="Id" column="PRD_ID"/>
+ <result property="Description" column="PRD_DESCRIPTION"/>
+</resultMap>
+
+<!-- This statement executes 1 time -->
+<statement id="selectCategory" parameterClass="int"
+ resultMap="select-category-result">
+ select
+ CATEGORY.CAT_ID as CAT_ID,
+ CATEGORY.CAT_DESCRIPTION as CAT_DESCRIPTION,
+ PRODUCT.PRD_ID as PRD_ID,
+ PRODUCT.PRD_DESCRIPTION as PRD_DESCRIPTION
+ from CATEGORY
+ left join PRODUCT on
+ PRODUCT.PRD_CAT_ID = CATEGORY.CAT_ID
+</statement>
+</com:TTextHighlighter>
+
+<p>In the above example, the <tt>groupBy</tt> attribute is set
+to the column of that specifies the Category ID. All the rows
+with the same <tt>CAT_ID</tt> will be considered as a collection
+for the <tt>ProductList</tt> property.</p>
+
+<div class="info"><b>Lazy Loading vs. Joins (1:M and M:N):</b>
+<p>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.</p>
+
+<p>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. "<tt>===</tt>").</p>
+</div>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/CompositeKeys.page b/demos/sqlmap/protected/pages/Manual/CompositeKeys.page new file mode 100644 index 00000000..6e33c6cd --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/CompositeKeys.page @@ -0,0 +1,53 @@ +<com:TContent ID="body">
+
+<h1>Composite Keys or Multiple Complex Parameters Properties</h1>
+<p>You might have noticed that in the above examples there is only a single key
+being used as specified in the <tt>resultMap</tt> by the <tt>column</tt> 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#</tt>. The alternate
+syntax for the column attribute is simply <tt>param1=column1, param2=column2, ... ,
+paramN=columnN</tt>. Consider the example below where the PAYMENT table
+is keyed by both Customer ID and Order ID:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-order-result" class="order">
+ <result property="id" column="ORD_ID"/>
+ <result property="customerId" column="ORD_CST_ID"/>
+ ...
+ <result property="payments" column="{itemId=ORD_ID, custId=ORD_CST_ID}"
+ select="selectOrderPayments"/>
+</resultMap>
+
+<statement id="selectOrderPayments" resultMap="select-payment-result">
+ select * from PAYMENT
+ where PAY_ORD_ID = #itemId#
+ and PAY_CST_ID = #custId#
+</statement>
+</com:TTextHighlighter>
+
+<p>Optionally you can just specify the column names as long as they're in the
+same order as the parameters. For example:</p>
+<com:TTextHighlighter Language="xml" CssClass="source">
+{ORD_ID, ORD_CST_ID}
+</com:TTextHighlighter>
+
+<div class="note"><b>Important!</b>
+<p>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.</p>
+</div>
+
+<div class="info"><b class="tip">Info:</b>
+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><sqlMap></tt> root element.
+</div>
+
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/ConfigurationElements.page b/demos/sqlmap/protected/pages/Manual/ConfigurationElements.page new file mode 100644 index 00000000..589a6dde --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ConfigurationElements.page @@ -0,0 +1,249 @@ +<com:TContent ID="body">
+
+<h1>DataMapper Configuration Elements</h1>
+
+<p>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.
+</p>
+
+<h2><tt><properties></tt> attributes</h2>
+<p>The <tt><properties></tt> element can accept one <tt>resource</tt> attribute to
+specify the location of the properties file.</p>
+
+<!-- tabular: align=|l|l|, width=(0.2 0.7) -->
+<table class="tabular">
+ <tr>
+ <th>Attribute</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td><tt>resource</tt></td>
+ <td>Specify the properties file to be loaded from the directory relative
+ to the current file.
+ Example: <tt>resource="properties.config"</tt>
+ </td>
+ </tr>
+</table>
+<p>
+For example, if the "<tt>properties.config</tt>" file contains</p>
+<com:TTextHighlighter Language="xml" CssClass="source">
+<?xml version="1.0" encoding="utf-8" ?>
+<settings>
+ <add key="username" value="albert" />
+</settings>
+</com:TTextHighlighter>
+
+<p>then all elements in the DataMapper configuration can use the variable
+<tt>${username}</tt> to insert the value "albert". For example:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<provider ConnectionString="mysql://${username}:..." ... />
+</com:TTextHighlighter>
+
+<div class="tip"><b class="tip">Tip:</b>
+Properties are handy during building, testing, and deployment by making it
+easy to reconfigure your application for multiple environments.
+</div>
+
+<h2><tt><property></tt> element and attributes</h2>
+<p>You can also specify more than one properties file or add property keys and
+values directly into your <tt>SqlMap.config</tt> file by using <tt><property></tt>
+elements.</p>
+
+<!-- tabular: align=|l|l|, width=(0.2 0.7) -->
+<table class="tabular">
+ <tr>
+ <th>Attribute</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td><tt>resource</tt></td>
+ <td>Specify the properties file to be loaded from the directory relative
+ to the current file.
+ Example: <tt>resource="properties.config"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>key</tt></td>
+ <td>Defines a property key (variable) name.
+ Example: <tt>key="username"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>value</tt></td>
+ <td> Defines a value that will be used by the DataMapper in place of the
+ the specified property key/variable.
+ Example: <tt>value="mydbuser"</tt>
+ </td>
+ </tr>
+</table>
+<p>For example:</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<properties>
+ <property resource="myProperties.config"/>
+ <property resource="anotherProperties.config"/>
+ <property key="host" value="ibatis.com" />
+</properties>
+</com:TTextHighlighter>
+
+<h2>The <tt><typeHandler></tt> Element</h2>
+<p>The <tt><typeHandler></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.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<typeHandler type="date" callback="TDateTimeHandler"/>
+</com:TTextHighlighter>
+
+<p>The <tt><typeHandler></tt> element has three attributes:</p>
+<!-- tabular: align=|l|l|, width=(0.2 0.7) -->
+<table class="tabular">
+ <tr>
+ <th>Attribute</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td><tt>type</tt></td>
+ <td>Refers to the name of the type to handle.
+ Example: <tt>type="date"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>dbType</tt></td>
+ <td>Indicates the provider dbType to handle.
+ Example: <tt>dbType="Varchar2"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>callback</tt></td>
+ <td> The custom type handler class name.
+ Example: <tt>callback="TDateTimeHandler"</tt>
+ </td>
+ </tr>
+</table>
+
+<h2>The <tt><provider></tt> element and attribute</h2>
+
+<p>The <tt><provider></tt> element encloses a <tt><datasource></tt> that configure the
+database system for use by the framework.</p>
+
+<!-- tabular: align=|l|l|, width=(0.2 0.7) -->
+<table class="tabular">
+ <tr>
+ <th>Attribute</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td><tt>class</tt></td>
+ <td>The database provider class that extends
+ <tt>TDatabaseProvider</tt>.
+ Example: <tt>class="TAdodbProvider"</tt>
+ </td>
+ </tr>
+</table>
+
+<h2>The <tt><datasource></tt> element and attributes</h2>
+<p>The <tt><datasource></tt> element specifies the connection string.
+The following example shows a <tt><datasource></tt> element for a MySql connection.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<!-- The ${properties} are defined in an external file, -->
+<!-- but the values could also be coded inline. -->
+
+<!-- Connecting to a MySQL database -->
+<provider class="TAdodbProvider" >
+ <datasource
+ ConnectionString="mysql://${username}:${password}@${host}/${database}" />
+</provider>
+</com:TTextHighlighter>
+
+<!-- tabular: align=|l|l|, width=(0.2 0.7) -->
+<table class="tabular">
+ <tr>
+ <th>Attribute</th>
+ <th>Description</th>
+ </tr>
+ <tr>
+ <td><tt>connectionString</tt></td>
+ <td>Data Source Name (DSN) connection string.
+ Example: <tt>connectionString="mysql://root:pwd@localhost/mydb"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>driver</tt></td>
+ <td> Database driver name (mysql, sqlite, etc.).
+ Example: <tt>driver="mysql"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>host</tt></td>
+ <td>DB host name/IP (and port number) in the format <tt>host[:port]</tt>.
+ Example: <tt>host="localhost"</tt>
+ </td>
+ </tr>
+ <tr>
+ <td><tt>username</tt></td>
+ <td>Database connection username.</td>
+ </tr>
+ <tr>
+ <td><tt>password</tt></td>
+ <td>Database connection password.</td>
+ </tr>
+ <tr>
+ <td><tt>database</tt></td>
+ <td>Database name to use in the connection.</td>
+ </tr>
+</table>
+
+<div class="tip"><b class="tip">Tip:</b>
+ Use Data Source Name (DSN) connection string or specify the
+ necessary individual connection parameters.
+</div>
+
+<h2>The <tt><sqlMap></tt> Element</h2>
+<p>On a daily basis, most of your work will be with the Data Maps, which are
+covered in <a href="?page=Manual.WorkingWithDataMaps">Working with Data Maps</a>.
+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.</p>
+
+The following example shows <tt><sqlMap></tt> elements for loading a set of
+<a href="?page=Manual.WorkingWithDataMaps">Data Map definitions</a>.
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<!-- Relative path from the directory of the
+ current file using a property variable -->
+<sqlMap resource="${root}/Maps/Account.xml"/>
+<sqlMap resource="${root}/Maps/Category.xml"/>
+<sqlMap resource="${root}/Maps/Product.xml"/>
+
+<!-- Full file path with a property variable -->
+<sqlMap resource="/${projectdir}/MyApp/Maps/Account.xml"/>
+<sqlMap resource="/${projectdir}/MyApp/Maps/Category.xml"/>
+<sqlMap resource="/${projectdir}/MyApp/Maps/Product.xml"/>
+</com:TTextHighlighter>
+
+<div class="tip"><b class="tip">Tip:</b>
+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><sqlMap></tt> <tt>resource</tt>
+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}</tt> and <tt>${root}</tt> variables in the
+example above).
+</div>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Configuring.page b/demos/sqlmap/protected/pages/Manual/Configuring.page new file mode 100644 index 00000000..75dade31 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Configuring.page @@ -0,0 +1,47 @@ +<com:TContent ID="body">
+
+<h1>Configuring the DataMapper for PHP</h1>
+<p>The SQLMap PHP DataMapper is configured using a central XML descriptor file,
+usually named <tt>SqlMap.config</tt>, 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</tt> file. After parsing the
+configuration file, a DataMapper client will be returned by SQLMap for your
+application to use.</p>
+
+<h2>DataMapper clients</h2>
+<p>Currently, the SQLMap PHP DataMapper framework revolves around the
+<tt>TSqlMapper</tt> 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</tt> class. An instance of the <tt>TSqlMapper</tt> 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.
+First, let's take a look at the DataMapper configuration file.
+
+<h1>DataMapper Configuration File (SqlMap.config)</h1>
+<p>A sample configuration file for a PHP web application is shown below.
+Not all configuration elements are required. See
+<a href="?page=Manual.ConfigurationElements">DataMapper Configuration Elements</a>
+for details of each configuration elements in a <tt>SqlMap.config</tt> file.
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<?xml version="1.0" encoding="utf-8"?>
+<sqlMapConfig>
+ <provider class="TAdodbProvider" >
+ <datasource ConnectionString="mysql://user:pass@localhost/test1" />
+ </provider>
+ <sqlMaps>
+ <sqlMap name="Account" resource="maps/Account.xml"/>
+ <sqlMap name="Order" resource="maps/Order.xml"/>
+ <sqlMap name="Category" resource="maps/Category.xml"/>
+ <sqlMap name="LineItem" resource="maps/LineItem.xml"/>
+ </sqlMaps>
+</sqlMapConfig>
+</com:TTextHighlighter>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/CustomTypeHandlers.page b/demos/sqlmap/protected/pages/Manual/CustomTypeHandlers.page new file mode 100644 index 00000000..c66820cf --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/CustomTypeHandlers.page @@ -0,0 +1,114 @@ +<com:TContent ID="body">
+
+<h1>Custom Type Handlers</h1>
+<p>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</tt>, for you to use in implementing your custom type
+handler.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+interface ITypeHandlerCallback
+{
+ public function getParameter($object);
+
+ public function getResult($string);
+
+ public function createNewInstance();
+}
+</com:TTextHighlighter>
+
+<p>The <tt>getParameter</tt> method allows you to process a <tt><statement></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.</p>
+
+<p>The <tt>getResult</tt> 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>, <tt>resultMap</tt>, or <tt>listClass</tt>.</p>
+
+<p>The <tt>createNewInstance</tt> method allows the DataMapper to create new instance
+of a particular type handled by this callback.</p>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+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;
+ }
+}
+</com:TTextHighlighter>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+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;
+ }
+}
+</com:TTextHighlighter>
+
+<p>With our custom type handler we can use the handler in our SqlMaps. To do
+that, we specify it as a basic <tt><typeHandler></tt> for all <tt>date</tt> types
+mapped in our SqlMap files</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+[Our SqlMap.config]
+
+<typeHandlers>
+ <typeHandler type="date" callback="TDateTimeHandler"/>
+</typeHandlers>
+
+
+[One of our SqlMap.xml files]
+ <parameterMap id="boc-params">
+ <parameter property="releasedDate" type="date"/>
+ </parameterMap>
+
+ <resultMap id="boc-result" class="BudgetObjectCode">
+ <result property="releasedDate" column="BOC_DATE" type="date"/>
+ </resultMap>
+</com:TTextHighlighter>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/DataMapperAPI.page b/demos/sqlmap/protected/pages/Manual/DataMapperAPI.page new file mode 100644 index 00000000..8f0b66a4 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/DataMapperAPI.page @@ -0,0 +1,162 @@ +<com:TContent ID="body">
+
+<h1>Exploring the SQLMap PHP DataMapper API through the <tt>TSqlMapper</tt></h1>
+<p>The <tt>TSqlMapper</tt> instance acts as a facade to provide access the rest of
+the DataMapper framework. The DataMapper API methods are shown below.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+/* 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()
+</com:TTextHighlighter>
+
+<p>Note that each of the API methods accept the name of the Mapped Statement as
+the first parameter. The <tt>statementName</tt> parameter corresponds to the
+<tt>id</tt> of the Mapped Statement in the Data Map definition.
+In each case, a <tt>parameterObject</tt> also may be
+passed. The following sections describe how the API methods work.</p>
+
+<h2>Insert, Update, Delete</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+public function insert($statementName, $parameter=null)
+public function update($statementName, $parameter=null)
+public function delete($statementName, $parameter=null)
+</com:TTextHighlighter>
+
+<p>If a Mapped Statement uses one of the <tt><insert></tt>, <tt><update></tt>, or
+<tt><delete></tt> statement-types, then it should use the corresponding API
+method. The <tt><insert></tt> element supports a nested <tt><selectKey></tt> element
+for generating primary keys. If the
+<tt><selectKey></tt> stanza is used, then <tt>insert</tt> returns the generated key;
+otherwise a null object is returned. Both the <tt>update</tt> and <tt>delete</tt>
+methods return the number of rows affected by the statement.
+</p>
+
+<h2>QueryForObject</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+public function queryForObject($statementName, $parameter=null, $result=null);
+</com:TTextHighlighter>
+
+<p>If a Mapped Statement is expected to select a single row, then call it using
+<tt>queryForObject</tt>. 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</tt> as the third
+parameter.</p>
+
+<h2>QueryForList</h2>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+public function queryForList($statementName, $parameter=null, $result=null,
+ $skip=-1, $max=-1);
+</com:TTextHighlighter>
+
+<p>If a Mapped Statement is expected to select multiple rows, then call it using
+<tt>queryForList</tt>. 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</tt> yourself, then it can be passed as the third parameter. If
+you need to obtain a partial result, the fourth parameter <tt>$skip</tt> and
+fifth parameter <tt>$max</tt> allow you to skip a number of records (the starting
+point) and the maximum number to return.</p>
+
+<h2>QueryForPagedList</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+ public function queryForPagedList($statementName, $parameter=null, $pageSize=10);
+</com:TTextHighlighter>
+
+<p>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.</p>
+
+<p>The <tt>TSqlMapPagedList</tt> interface includes methods for navigating through
+pages (<tt>nextPage()</tt>, <tt>previousPage()</tt>, <tt>gotoPage($pageIndex)</tt>) and
+also checking the status of the page (<tt>getIsFirstPage()</tt>,
+<tt>getIsMiddlePage()</tt>, <tt>getIsLastPage()</tt>, <tt>getIsNextPageAvailable()</tt>,
+<tt>getIsPreviousPageAvailable()</tt>, <tt>getCurrentPageIndex()</tt>,
+<tt>getPageSize()</tt>). The total number of records available is not accessible
+from the <tt>TSqlMapPagedList</tt> interface, unless a virtual count is defined
+using <tt>setVirtualCount($value)</tt>, this should be easily accomplished by
+simply executing a second statement that counts the expected results.</p>
+
+<div class="tip"><b class="tip">Tip:</b>
+The <tt>queryForPagedList</tt> 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</tt> and
+<tt>$max</tt> as parameters in <tt>queryForList</tt>.
+</div>
+
+<h2>QueryForMap</h2>
+<com:TTextHighlighter Language="php" CssClass="source">
+public function queryForMap($statementName, $parameter=null,
+ $keyProperty=null, $valueProperty=null);
+</com:TTextHighlighter>
+
+<p>The <tt>queryForList</tt> methods return the result objects within a <tt>TList</tt> or
+array instance. Alternatively, the <tt>queryForMap</tt> 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</tt>
+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</tt> objects, you might want them returned as a
+<tt>TMap</tt> keyed by each object's <tt>EmployeeNumber</tt> property.</p>
+
+<p>If you don't need the entire result object in your result, you can add the
+<tt>$valueProperty</tt> parameter to indicate which result object property should
+be the value of an entry. For example, you might just want the
+<tt>EmployeeName</tt> keyed by <tt>EmployeeNumber</tt>.</p>
+
+<h2>Transaction</h2>
+<p>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</tt> instance.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+// 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()
+</com:TTextHighlighter>
+
+<p>Using transactions example.</p>
+<com:TTextHighlighter Language="php" CssClass="source">
+try
+{
+ $sqlMap->beginTransaction();
+ $item = $sqlMap->queryForObject("getItem", $itemId);
+ $item->setDescription($newDescription);
+ $sqlMap->update("updateItem", $item);
+ $sqlMap->commitTransaction();
+}
+catch
+{
+ $sqlMap->rollBackTransaction();
+}
+</com:TTextHighlighter>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/DynamicSQL.page b/demos/sqlmap/protected/pages/Manual/DynamicSQL.page new file mode 100644 index 00000000..2ed8c6ec --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/DynamicSQL.page @@ -0,0 +1,5 @@ +<com:TContent ID="body">
+<h1>Dynamic SQL</h1>
+<p>Dynamic SQL is not implemented yet.</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/ImplicitResultMaps.page b/demos/sqlmap/protected/pages/Manual/ImplicitResultMaps.page new file mode 100644 index 00000000..07dc61e0 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ImplicitResultMaps.page @@ -0,0 +1,96 @@ +<com:TContent ID="body">
+
+<h1>Implicit Result Maps</h1>
+<p>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 the following example, the column names and property names
+already match, so a result map is not needed.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="selectProduct" resultClass="Product">
+ select
+ id,
+ description
+ from PRODUCT
+ where id = #value#
+</statement>
+</com:TTextHighlighter>
+
+<p>Another way to skip a result map is to use column aliasing to make the column
+names match the properties names, as shown in the following example.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="selectProduct" resultClass="Product">
+ select
+ PRD_ID as id,
+ PRD_DESCRIPTION as description
+ from PRODUCT
+ where PRD_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<p>Of course, these techniques will not work if you need to specify a column
+type, a null value, or any other property attributes.</p>
+
+<h1>Primitive Results (i.e. String, Integer, Boolean)</h1>
+<p>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 following example.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<select id="selectProductCount" resultClass="integer">
+ select count(1)
+ from PRODUCT
+</select>
+</com:TTextHighlighter>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-product-result" resultClass="string">
+ <result property="value" column="PRD_DESCRIPTION"/>
+</resultMap>
+</com:TTextHighlighter>
+
+<h1>Maps with ResultMaps</h1>
+<p>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 following example,
+only the result object changes.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-product-result" class="array">
+ <result property="id" column="PRD_ID"/>
+ <result property="code" column="PRD_CODE"/>
+ <result property="description" column="PRD_DESCRIPTION"/>
+ <result property="suggestedPrice" column="PRD_SUGGESTED_PRICE"/>
+</resultMap>
+</com:TTextHighlighter>
+
+<p>In the above example, 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>, <tt>code</tt>, and so forth, would be the key of the
+entry, and the value of the mapped columns would be the value of the entry.</p>
+
+<p>As shown in the following example, you can also use an implicit Result
+Map with an array type.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="selectProductCount" resultClass="array">
+ select * from PRODUCT
+</statement>
+</com:TTextHighlighter>
+
+<p>What set of entries is returned by the above example 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.</p>
+
+<div class="note"><b class="tip">Note:</b>
+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.
+</div>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/InheritanceMapping.page b/demos/sqlmap/protected/pages/Manual/InheritanceMapping.page new file mode 100644 index 00000000..0458a291 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/InheritanceMapping.page @@ -0,0 +1,150 @@ +<com:TContent ID="body">
+
+<h1>Inheritance Mapping</h1>
+<p>The SQLMap PHP 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:</p>
+
+<p>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 <tt>resultMap</tt> must define one or more columns in your query's
+resultset that will serve to identify which <tt>resultMap</tt> 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.</p>
+
+<p>For example, we have a table defined in a database that contains <tt>Document</tt>
+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.</p>
+
+<com:TTextHighlighter Language="sql" CssClass="source">
+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
+)
+</com:TTextHighlighter>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+class Document
+{
+ public $ID = -1;
+ public $Title = '';
+}
+
+class Book extends Document
+{
+ public $PageNumber = -1;
+}
+
+class Newspaper extends Document
+{
+ public $City = '';
+}
+</com:TTextHighlighter>
+
+<p>Now that we have our classes and database table, we can start working on our
+mappings. We can create one <tt><select></tt> statement that returns all columns in the
+table. To help the DataMapper discriminate between the different Document
+records, we're going to indicate that the <tt>Document_Type</tt> column holds values
+that will distinguish one record from another for mapping the results into our
+class hierarchy.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<select id="GetAllDocument" resultMap="document">
+ select
+ Document_Id, Document_Title, Document_Type,
+ Document_PageNumber, Document_City
+ from Documents
+ order by Document_Type, Document_Id
+</select>
+
+<resultMap id="document" class="Document">
+ <result property="Id" column="Document_ID"/>
+ <result property="Title" column="Document_Title"/>
+ <discriminator column="Document_Type" type="string"/>
+ <subMap value="Book" resultMapping="book"/>
+ <subMap value="Newspaper" resultMapping="newspaper"/>
+</resultMap>
+
+<resultMap id="book" class="Book" extends="document">
+ <property="PageNumber" column="Document_PageNumber"/>
+</resultMap>
+
+<resultMap id="newspaper" class="Newspaper" extends="document">
+ <property="City" column="Document_City"/>
+</resultMap>
+</com:TTextHighlighter>
+
+<p>The DataMapper compares the data found in the discriminator column to the
+different <tt><submap></tt> values using the column value's string equivalence. Based
+on this string value, SQLMap DataMapper will use the resultMap named "<tt>Book</tt>" or
+"<tt>Newspaper</tt>" as defined in the <tt><submap></tt> elements or it will use the
+"parent" resultMap "<tt>Document</tt>" if neither of the submap values satisfy the comparison.
+With these resultMaps, we can implement an object-oriented inheritance mapping
+to our database table.</p>
+
+<p>If you want to use custom logic, you can use the typeHandler attribute of the
+<tt><discriminator></tt> element to specify a custom type handler for the discriminator
+column.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+ <resultMap id="document-custom-formula" class="Document">
+ <result property="Id" column="Document_ID"/>
+ <result property="Title" column="Document_Title"/>
+ <discriminator column="Document_Type" typeHandler="CustomInheritance"/>
+ <subMap value="Book" resultMapping="book"/>
+ <subMap value="Newspaper" resultMapping="newspaper"/>
+ </resultMap>
+</resultMaps>
+</com:TTextHighlighter>
+
+<p>The value of the <tt>typeHandler</tt> attribute specifies which of our classes
+implements the <tt>ITypeHandlerCallback</tt> interface. This interface furnishes a
+<tt>getResult</tt> 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 <tt><submap></tt> values.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+class CustomInheritance implements ITypeHandlerCallback
+{
+ public function getResult($type)
+ {
+ if ($type=="Monograph" || $type=="Book")
+ return "Book";
+ else if ($type=="Tabloid" || $type=="Broadsheet" || $type=="Newspaper")
+ return "Newspaper";
+ else
+ return "Document";
+ }
+
+ public function getParameter($object)
+ {
+ throw new Exception('unimplemented');
+ }
+
+ public function createNewInstance()
+ {
+ throw new Exception('unimplemented');
+ }
+}
+</com:TTextHighlighter>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/InlineParameterMaps.page b/demos/sqlmap/protected/pages/Manual/InlineParameterMaps.page new file mode 100644 index 00000000..3bfa4ceb --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/InlineParameterMaps.page @@ -0,0 +1,102 @@ +<com:TContent ID="body">
+<h1>Inline Parameter Maps</h1>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="insertProduct" parameterClass="Product">
+ insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
+ values (#id#, #description#)
+</statement>
+</com:TTextHighlighter>
+
+<p>The following example shows how <tt>dbTypes</tt> can be declared inline.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="insertProduct" parameterClass="Product">
+ insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
+ values (#id, dbType=int#, #description, dbType=VarChar#)
+</statement>
+</com:TTextHighlighter>
+
+<p>The next example shows how <tt>dbTypes</tt> and null value replacements can also
+be declared inline.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="insertProduct" parameterClass="Product">
+ insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
+ values (#id, dbType=int, nullValue=-999999#, #description, dbType=VarChar#)
+</statement>
+</com:TTextHighlighter>
+
+<p>A more complete example.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<update id="UpdateAccountViaInlineParameters" parameterClass="Account">
+ 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#
+</update>
+</com:TTextHighlighter>
+
+<div class="note"><b class="tip">Note:</b>
+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</tt> can be easier.
+</div>
+
+<h1>Standard Type Parameters</h1>
+<p>In practice, you will find that many statements take a single parameter, often
+an <tt>integer</tt> or a <tt>string</tt>. Rather than wrap a single value in another
+object, you can use the standard library object (string, integer, et cetera)
+as the parameter directly. The following example shows a statement using
+a standard type parameter.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="getProduct" parameterClass="System.Int32">
+ select * from PRODUCT where PRD_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<p>Assuming <tt>PRD_ID</tt> is a numeric type, when a call is made to this Mapped
+Statement, a standard integer can be passed in. The <tt>#value#</tt> parameter
+will be replaced with the value of the integer. The name <tt>value</tt> is simply
+a placeholder, you can use another moniker of your choice. Result Maps support
+primitive types as results as well.</p>
+
+<p>For your convenience, the following PHP primitive types are supported.</p>
+<ul>
+ <li><tt>string</tt></li>
+ <li><tt>float</tt> or <tt>double</tt></li>
+ <li><tt>integer</tt> or <tt>int</tt></li>
+ <li><tt>bool</tt> or <tt>boolean</tt></li>
+</ul>
+
+<h1>Array Type Parameters</h1>
+<p>You can also pass in a array as a parameter object. This would usually be a an
+associative array. The following example shows a <tt><statement></tt> using
+an array for a <tt>parameterClass</tt>.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="getProduct" parameterClass="array">
+ select * from PRODUCT
+ where PRD_CAT_ID = #catId#
+ and PRD_CODE = #code#
+</statement>
+</com:TTextHighlighter>
+
+<p>In the above example, 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</tt>
+and <tt>code</tt>. 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.</p>
+
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Installing.page b/demos/sqlmap/protected/pages/Manual/Installing.page new file mode 100644 index 00000000..bb083996 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Installing.page @@ -0,0 +1,51 @@ +<com:TContent ID="body">
+
+<h1>Installing the DataMapper for PHP</h1>
+
+<p>This section explains how to install, configure, and use the SQLMap DataMapper
+with your PHP application.</p>
+
+<p>There are two steps to using SQLMap DataMapper with your application for the
+first time.</p>
+<ol>
+ <li>Setup the distribution</li>
+ <li>Add XML documents</li>
+</ol>
+
+<h2>Setup the Distribution</h2>
+
+<p>The official site for SQLMap PHP DataMapper is <a href="http://www.pradosoft.com">http://www.pradosoft.com</a>.
+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.</p>
+
+<p><b>TODO: Add distribution contents</b></p>
+
+<h1>Add XML file items</h1>
+<p>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:</p>
+
+<dl> + <dt>SqlMap.xml</dt>
+ <dd>-- 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.
+ </dd> +
+ <dt>SqlMap.config</dt>
+ <dd>--
+ 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. + </dd> + </dl> +
+<p>As expected, the <tt>SqlMap.config</tt> file should be (by default) placed where the DataMapper
+can find them at runtime.</p>
+
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/MappedStatements.page b/demos/sqlmap/protected/pages/Manual/MappedStatements.page new file mode 100644 index 00000000..c4dfa3d9 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/MappedStatements.page @@ -0,0 +1,173 @@ +<com:TContent ID="body">
+
+<h1>Mapped Statements</h1>
+<p>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. </p>
+
+<p>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.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="statement.name"
+ [parameterMap="parameterMap.name"]
+ [parameterClass="class.name"]
+ [resultMap="resultMap.name"]
+ [resultClass="class.name"]
+ [listClass="class.name"]
+ [cacheModel="cache.name"]
+>
+ select * from Products where Product_Id = [?|#propertyName#]
+ order by [$simpleDynamic$]
+</statement>
+</com:TTextHighlighter>
+
+<p>The <tt>[bracketed]</tt> parts are optional, and some
+options are mutually exclusive. It is perfectly legal to have a Mapped
+Statement as simple as shown by the following example.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="InsertTestProduct" >
+ insert into Products (Product_Id, Product_Description) values (1, "Shih Tzu")
+</statement>
+</com:TTextHighlighter>
+
+<p>The above example 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
+<a href="?page=Manual.ParameterMap">Parameter Maps</a> and
+<a href="?page=Manual.ResultMaps">Result Maps</a> since that's where the magic happens.</p>
+
+<h2>Statement Types</h2>
+<p>The <tt><statement></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.) The following table
+summarizes the statement-type elements and their supported attributes and
+features. </p>
+
+<!-- tabular: align=|l|l|l|l|, width=(0.2 0.2 0.2 0.2) -->
+<table class="tabular">
+ <tr>
+ <th>Statement Element</th>
+ <th>Attribute</th>
+ <th>Child Elements</th>
+ <th>Methods</th>
+ </tr>
+ <tr>
+ <td><tt><statement></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>resultClass</tt><br />
+ <tt>listClass</tt><br />
+ <tt>parameterMap</tt><br />
+ <tt>resultMap</tt><br />
+ <tt>cacheModel</tt>
+ </td>
+ <td>None</td>
+ <td>
+ <tt>Insert</tt><br />
+ <tt>Update</tt><br />
+ <tt>Delete</tt><br />
+ All query methods
+ </td>
+ </tr>
+
+ <tr>
+ <td><tt><insert></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>parameterMap</tt>
+ </td>
+ <td>
+ <tt><selectKey></tt><br />
+ <tt><generate></tt>
+ </td>
+ <td>
+ <tt>Insert</tt><br />
+ <tt>Update</tt><br />
+ <tt>Delete</tt>
+ </td>
+ </tr>
+
+ <tr>
+ <td><tt><update></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>parameterMap</tt><br />
+ <tt>extends</tt>
+ </td>
+ <td><tt><generate></tt></td>
+ <td>
+ <tt>Insert</tt><br />
+ <tt>Update</tt><br />
+ <tt>Delete</tt>
+ </td>
+ </tr>
+
+ <tr>
+ <td><tt><delete></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>parameterMap</tt><br />
+ <tt>extends</tt>
+ </td>
+ <td><tt><generate></tt></td>
+ <td>
+ <tt>Insert</tt><br />
+ <tt>Update</tt><br />
+ <tt>Delete</tt>
+ </td>
+ </tr>
+
+ <tr>
+ <td><tt><select></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>resultClass</tt><br />
+ <tt>listClass</tt><br />
+ <tt>parameterMap</tt><br />
+ <tt>resultMap</tt><br />
+ <tt>cacheModel</tt><br />
+ <tt>extends</tt>
+ </td>
+ <td><tt><generate></tt></td>
+ <td>
+ All query methods
+ </td>
+ </tr>
+
+ <tr>
+ <td><tt><procedure></tt></td>
+ <td>
+ <tt>id</tt><br />
+ <tt>parameterClass</tt><br />
+ <tt>resultClass</tt><br />
+ <tt>listClass</tt><br />
+ <tt>parameterMap</tt><br />
+ <tt>resultMap</tt><br />
+ <tt>cacheModel</tt>
+ </td>
+ <td>None</td>
+ <td>
+ <tt>Insert</tt><br />
+ <tt>Update</tt><br />
+ <tt>Delete</tt><br />
+ All query methods
+ </td>
+ </tr>
+</table>
+
+<h2>Stored Procedures</h2>
+
+<p>TODO</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/Overview.page b/demos/sqlmap/protected/pages/Manual/Overview.page new file mode 100644 index 00000000..694daf68 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/Overview.page @@ -0,0 +1,48 @@ +<com:TContent ID="body">
+<h1>Overview</h1>
+<p>
+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.
+</p>
+
+<p>The SQLMap for PHP is based on iBATIS.NET - DataMapper Application Framework
+from <a href="http://ibatis.apache.org/">http://ibatis.apache.org</a>.
+The PHP version support most of the features found in
+iBATIS.NET exception the following:
+<ol>
+ <li>Dynamic SQL</li>
+ <li>Distributed Transactions</li>
+</ol>
+</p>
+
+<h1>What's covered here</h1>
+<p>
+This Guide covers the PHP implementations of SQLMap DataMapper. The Java and
+.NET implementation offers the same services with some changes in the API.
+</p>
+
+<p>Since SQLMap relies on an XML descriptor to create the mappings, much of the
+material applies to both implementations.
+</p>
+
+<p>For installation instructions, see the section called the
+SQLMap PHP Developer Guide.</p>
+
+<p>A Tutorial is also available. We recommend reviewing the Tutorial for your
+platform before reading this Guide.</p>
+
+<h1>Support</h1>
+
+<p>Add Forum and Trac.</p>
+
+<h1>Disclaimer</h1>
+<p>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.</p>
+
+</com:TContent>
diff --git a/demos/sqlmap/protected/pages/Manual/ParameterMap.page b/demos/sqlmap/protected/pages/Manual/ParameterMap.page new file mode 100644 index 00000000..de574139 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ParameterMap.page @@ -0,0 +1,184 @@ +<com:TContent ID="body">
+<h1>Parameter Maps and Inline Parameters</h1>
+
+<p>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.</p>
+
+<div class="note"><b class="tip">Note:</b>
+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.
+</div>
+
+<h1>Parameter Map</h1>
+<p>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.</p>
+
+<p>Parameter Maps can be provided as an external element and \emph{inline}.
+The following example shows an external Parameter Map.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<parameterMap id="parameterMapIdentifier"
+ [extends="[sqlMapNamespace.]parameterMapId"]>
+ <parameter
+ property ="propertyName"
+ [column="columnName"]
+ [dbType="databaseType"]
+ [type="propertyCLRType"]
+ [nullValue="nullValueReplacement"]
+ [size="columnSize"]
+ [precision="columnPrecision"]
+ [scale="columnScale"]
+ [typeHandler="class.name"]
+ <parameter ... ... />
+ <parameter ... ... />
+</parameterMap>
+</com:TTextHighlighter>
+
+<p>In the above example, the parts in <tt>[brackets]</tt> are optional. The
+<tt>parameterMap</tt> element only requires the id attribute.
+The following example shows a typical <tt><parameterMap></tt>.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<parameterMap id="insert-product-param" class="Product">
+ <parameter property="description" />
+ <parameter property="id"/>
+</parameterMap>
+
+<statement id="insertProduct" parameterMap="insert-product-param">
+ insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?);
+</statement>
+</com:TTextHighlighter>
+
+<div class="note"><b class="tip">Note:</b>
+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</tt> of the Parameter Map with the
+namespace of the Data Map (set in the <tt><sqlMap></tt> root element).
+</div>
+
+<h2><tt><parameterMap></tt> attributes</h2>
+
+<p>The <tt><parameterMap></tt> element
+accepts two attributes: <tt>id</tt> (required) and <tt>extends</tt> (optional).</p>
+
+<h3><tt>id</tt> attribute</h3>
+
+<p>The required <tt>id</tt> attribute provides a
+unique identifier for the <tt><parameterMap></tt> within this Data Map.</p>
+
+<h3><tt>extends</tt> attribute</h3>
+<p>The optional <tt>extends</tt> attribute can be set to the name of another
+<tt>parameterMap</tt> upon which to base this <tt>parameterMap</tt>. All properties of
+the super <tt>parameterMap</tt> will be included as part of this
+<tt>parameterMap</tt>, and values from the super <tt>parameterMap</tt> are set before
+any values specified by this <tt>parameterMap</tt>. The effect is similar to
+extending a class.</p>
+
+<h1><tt><parameter></tt> Elements</h1>
+<p>The <tt><parameterMap></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.</p>
+
+<h2><tt>property</tt> attribute</h2>
+<p>The <tt>property</tt> attribute of <tt><parameter></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.)</p>
+
+<h2><tt>direction</tt> attribute</h2>
+<p>The <tt>direction</tt> attribute may be used to indicate a stored procedure
+parameter's direction.</p>
+
+<!-- tabular: align=|l|l|, width=(0.2 0.4) -->
+<table class="tabular">
+ <tr><th>Value</th><th>Description</th></tr>
+ <tr>
+ <td><tt>Input</tt></td>
+ <td>input-only</td>
+ </tr>
+ <tr>
+ <td><tt>Output</tt></td>
+ <td>output-only</td>
+ </tr>
+ <tr>
+ <td><tt>InputOutput</tt></td>
+ <td>bidirectional</td>
+ </tr>
+</table>
+
+<h2><tt>column</tt> attribute</h2>
+<p>The <tt>column</tt> attribute is used to define to the name of a parameter used by
+a stored procedure.</p>
+
+<h2><tt>dbType</tt> attribute</h2>
+<p>The <tt>dbType</tt> 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</tt> attribute is to explicitly specify date types. Most SQL databases
+have more than one <tt>datetime</tt> 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</tt>.</p>
+
+<div class="note"><b class="tip">Note:</b>
+Most providers only need the <tt>dbType</tt> specified for nullable columns. In
+this case, you only need to specify the type for the columns that are
+nullable.
+</div>
+
+<h2><tt>type</tt> attribute</h2>
+<p>The <tt>type</tt> attribute is used to specify the type of the parameter's
+property. This attribute is useful when passing <tt>InputOutput</tt> and
+<tt>Output</tt> 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.</p>
+
+<h2><tt>nullValue</tt> attribute</h2>
+
+<p>The <tt>nullValue</tt> attribute can be set to any valid value (based on property
+type). The <tt>nullValue</tt> 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.</p>
+
+<div class="tip"><b class="tip">Tip:</b>
+For round-trip transparency of null values, you must also specify database
+columns null value replacements in your <a href="?page=Manual.ResultMaps">Result Map</a>.
+</div>
+
+<h2><tt>size</tt> attribute</h2>
+<p>The <tt>size</tt> attribute sets the maximum size of the data within the column.</p>
+
+<h2><tt>precision</tt> attribute</h2>
+<p>The <tt>precision</tt> attribute is used to set the maximum number of digits used
+to represent the property value.</p>
+
+<h2><tt>scale</tt> attribute</h2>
+<p>The <tt>scale</tt> attribute sets the number of decimal places used to resolve the
+property value.</p>
+
+<h2><tt>typeHandler</tt> attribute</h2>
+<p>The <tt>typeHandler</tt> attribute allows the use of a
+<a href="?page=Manual.CustomTypeHandlers">Custom Type Handler</a>. 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.</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/ResultMapAttributes.page b/demos/sqlmap/protected/pages/Manual/ResultMapAttributes.page new file mode 100644 index 00000000..dd754197 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ResultMapAttributes.page @@ -0,0 +1,132 @@ +<com:TContent ID="body">
+
+<h1>Result Map Attributes</h1>
+<p>The <tt><resultMap></tt> element accepts three attributes: <tt>id</tt> (required),
+<tt>class</tt> (optional), and <tt>extends</tt> (optional).</p>
+
+<h2><tt>id</tt> attribute</h2>
+<p>The required <tt>id</tt> attribute provides a unique identifier for the
+<tt><resultMap></tt> within this Data Map.</p>
+
+<h2><tt>class</tt> attribute</h2>
+<p>The optional <tt>class</tt> attribute specifies an object class to use with this
+<tt><resultMap></tt>. The full classname must be specified. Any class can be used.</p>
+
+<div class="note"><b class="tip">Note:</b>
+As with parameter classes, the result class must be a PHP class object or
+array instance.
+</div>
+
+<h2><tt>extends</tt> attribute</h2>
+<p>The optional <tt>extends</tt> attribute allows the result map to inherit all of
+the properties of the "parent" <tt>resultMap</tt> that it extends.</p>
+
+<h1><tt><result></tt> Element attributes</h1>
+
+<p>The <tt><resultMap></tt> element holds one or more <tt><result></tt> child elements
+that map SQL result sets to object properties.</p>
+
+<h2><tt>property</tt> attribute</h2>
+<p>The <tt>property</tt> 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.</p>
+
+<h2><tt>column</tt> attribute</h2>
+<p>The <tt>column</tt> attribute value is the name of the column in the result set
+from which the value will be used to populate the property.</p>
+
+<h2><tt>columnIndex</tt> attribute</h2>
+<p>The <tt>columnIndex</tt> 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.</p>
+
+<h2><tt>dbType</tt> attribute</h2>
+<p>The <tt>dbType</tt> 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>,
+<tt>Char</tt> or <tt>CLOB</tt>, so specifying the type might be needed in those cases
+too.</p>
+
+<h2><tt>type</tt> attribute</h2>
+<p>The <tt>type</tt> attribute is used to explicitly specify the property type of the
+parameter to be set. If the attribute <tt>type</tt> is not set and the framework
+cannot otherwise determine the type, the type is assumed to be <tt>StdObject</tt>.</p>
+
+<h2><tt>resultMapping</tt> attribute</h2>
+<p>The <tt>resultMapping</tt> attribute can be set to the name of another
+<tt>resultMap</tt> used to fill the property. If the <tt>resultMap</tt> is in an other
+mapping file, you must specified the fully qualified name as :</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+resultMapping="[namespace.sqlMap.]resultMappingId"
+
+resultMapping="Newspaper"
+<!--resultMapping with a fully qualified name.-->
+resultMapping="LineItem.LineItem"
+</com:TTextHighlighter>
+
+<h2><tt>nullValue</tt> attribute</h2>
+<p>The <tt>nullValue</tt> attribute can be set to any valid value (based on property
+type). The <tt>nullValue</tt> 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.</p>
+
+<p>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 the following example.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="get-product-result" class="product">
+ <result property="id" column="PRD_ID"/>
+ <result property="description" column="PRD_DESCRIPTION"/>
+ <result property="subCode" column="PRD_SUB_CODE" nullValue="-9999"/>
+</resultMap>
+</com:TTextHighlighter>
+
+<p>In the above example, <tt>PRD_SUB_CODE</tt> is read as <tt>NULL</tt>, then the
+<tt>subCode</tt> property will be set to the value of -9999. This allows you to
+use a primitive type to represent a <tt>NULLABLE</tt> 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</tt> in the <a href="?page=ParameterMaps">Parameter Map</a>.
+</p>
+
+<h2><tt>select</tt> attribute</h2>
+<p>The <tt>select</tt> 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</tt> attribute can be specified with the
+<tt>select</tt>.</p>
+
+<h2><tt>lazyLoad</tt> attribute</h2>
+<p>Use the <tt>lazyLoad</tt> attribute with the <tt>select</tt> 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.</p>
+
+<h2><tt>typeHandler</tt> attribute</h2>
+<p>The <tt>typeHandler</tt> attribute allows the use of a
+<a href="?page=Manual.CustomTypeHandlers">Custom Type Handler</a>.
+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.
+</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/ResultMaps.page b/demos/sqlmap/protected/pages/Manual/ResultMaps.page new file mode 100644 index 00000000..97165ffb --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/ResultMaps.page @@ -0,0 +1,67 @@ +<com:TContent ID="body">
+<h1>Result Maps</h1>
+<a href="?page=Manual.ParameterMaps">Parameter Maps</a> and
+<a href="?page=Manual.InlineParameterMaps">Inline parameters</a>
+ 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.</p>
+
+<p>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. The following example shows the structure
+of a <tt><resultMap></tt> element.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="resultMapIdentifier"
+ [class="class.name"]
+ [extends="[sqlMapNamespace.]resultMapId"]>
+
+ <result property="propertyName"
+ column="columnName"
+ [columnIndex="columnIndex"]
+ [dbType="databaseType"]
+ [type="propertyCLRType"]
+ [resultMapping="resultMapName"]
+ [nullValue="nullValueReplacement"]
+ [select="someOtherStatementName"]
+ [lazyLoad="true|false"]
+ [typeHandler="class.name"]
+ />
+ <result ... .../>
+ <result ... .../>
+</resultMap>
+</com:TTextHighlighter>
+
+<p>In the above example, the <tt>[brackets]</tt> indicate optional attributes.
+The <tt>id</tt> attribute is required and provides a name for the statement to
+reference. The <tt>class</tt> 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.</p>
+
+<p>The <tt>resultMap</tt> 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.</p>
+
+<div class="note"><b class="tip">Note:</b>
+As with parameter classes, the result class must be a PHP class object or
+array instance.
+</div>
+
+<h1>Extending <tt>resultMaps</tt></h1>
+<p>The optional <tt>extends</tt> attribute can be set to the name of another
+<tt>resultMap</tt> upon which to base this <tt>resultMap</tt>. All properties of the
+"parent" <tt>resultMap</tt> will be included as part of this <tt>resultMap</tt>, and
+values from the "parent" <tt>resultMap</tt> are set before any values specified
+by this <tt>resultMap</tt>. The effect is similar to extending a class.</p>
+
+<div class="tip"><b class="tip">Tip:</b>
+The "parent" <tt>resultMap</tt> must be defined in the file before the extending
+<tt>resultMap</tt>. The classes for the parent and child <tt>resultMaps</tt> need not be
+the same, and do not need to be related in any way.
+</div>
+
+</com:TContent>
diff --git a/demos/sqlmap/protected/pages/Manual/StatementElementAttributes.page b/demos/sqlmap/protected/pages/Manual/StatementElementAttributes.page new file mode 100644 index 00000000..e5cef09d --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/StatementElementAttributes.page @@ -0,0 +1,216 @@ +<com:TContent ID="body">
+
+<h1>Statement-type Element Attributes</h1>
+<p>The six statement-type elements take various attributes. See
+<a href="?page=Manual.MappedStatements">Mapped Statements</a> for a
+table itemizing which attributes each
+element-type accepts. The individual attributes are described in the sections
+that follow.</p>
+
+<h2><tt>id</tt> attribute</h2>
+<p>
+The required <tt>id</tt> attribute provides a name for this statement, which must
+be unique within this <tt><SqlMap></tt>.</p>
+
+<h2><tt>parameterMap</tt> attribute</h2>
+<p>A Parameter Map defines an ordered list of values that match up with the "?"
+placeholders of a standard, parameterized query statement.
+The following example shows a <tt><parameterMap></tt> and a corresponding
+<tt><statement></tt>.
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<parameterMap id="insert-product-param" class="Product">
+ <parameter property="id"/>
+ <parameter property="description"/>
+</parameterMap>
+
+<statement id="insertProduct" parameterMap="insert-product-param">
+ insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?);
+</statement>
+</com:TTextHighlighter>
+
+In the above example, 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</tt> property. The second is replaced with
+the <tt>description</tt> property.</p>
+
+<p>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. See
+<a href="?page=Manual.ParameterMap">Parameter Maps</a> for futher details.</p>
+
+<h2><tt>parameterClass</tt> attribute</h2>
+<p>If a <tt>parameterMap</tt> attribute is not specified, you may specify a
+<tt>parameterClass</tt> instead and use <a href="?page=Manual.InlineParameterMaps">inline parameters</a>.
+The value of the <tt>parameterClass</tt> attribute
+can be any existing PHP class name. The following example shows a
+statement using a PHP class named <tt>Product</tt> in <tt>parameterClass</tt>
+attribute.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="statementName" parameterClass="Product">
+ insert into PRODUCT values (#id#, #description#, #price#)
+</statement>
+</com:TTextHighlighter>
+
+<h2><tt>resultMap</tt> attribute</h2>
+<p>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.
+The following example shows a <tt><resultMap></tt> element and a
+corresponding <tt><statement></tt> element.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<resultMap id="select-product-result" class="product">
+ <result property="id" column="PRD_ID"/>
+ <result property="description" column="PRD_DESCRIPTION"/>
+</resultMap>
+
+<statement id="selectProduct" resultMap="select-product-result">
+ select * from PRODUCT
+</statement>
+</com:TTextHighlighter>
+
+<p>In the above example, the result of the SQL query will be mapped to
+an instance of the <tt>Product</tt> class using the "select-product-result"
+<tt><resultMap></tt>. The <tt><resultMap></tt> says to
+populate the <tt>id</tt> property
+from the <tt>PRD_ID</tt> column, and to populate the <tt>description</tt> property
+from the <tt>PRD_DESCRIPTION</tt> column.</p>
+
+<div class="tip"><b class="tip">Tip:</b>
+In the above example, note that using "<tt> select * </tt>" 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.)
+</div>
+
+<p>See <a href="?page=Manual.ResultMaps">Result Maps</a> for futher details.</p>
+
+<h2><tt>resultClass</tt> attribute</h2>
+<p>If a <tt>resultMap</tt> is not specified, you may specify a <tt>resultClass</tt>
+instead. The value of the <tt>resultClass</tt> attribute can be the name of a PHP
+class or primitives like <tt>integer</tt>, <tt>string</tt>, or <tt>array</tt>. The class
+specified will be automatically mapped to the columns in the result, based on
+the result metadata. The following example shows a <tt><statement></tt> element
+with a <tt>resultClass</tt> attribute.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="SelectPerson" parameterClass="int" resultClass="Person">
+ 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#
+</statement>
+</com:TTextHighlighter>
+
+<p>In the above example, the <tt>Person</tt> class has properties including:
+<tt>Id</tt>, <tt>FirstName</tt>, <tt>LastName</tt>, <tt>BirthDate</tt>,
+<tt>WeightInKilograms</tt>, and <tt>HeightInMeters</tt>. Each of these corresponds
+with the column aliases described by the SQL select statement using the "as"
+keyword, a standard SQL feature. When executed, a <tt>Person</tt> object is
+instantiated and populated by matching the object property names to the column
+names from the query.</p>
+
+<p>Using SQL aliases to map columns to properties saves defining a
+<tt><resultMap></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 <a href="?page=Manual.ResultMaps">Result Map</a>.</p>
+
+<h2><tt>listClass</tt> attribute</h2>
+<p>In addition to providing the ability to return an <tt>TList</tt> of objects, the
+DataMapper supports the use of custom collection: a class that implements
+<tt>ArrayAccess</tt>. The following is an example of a TList (it implements
+ArrayAccess) class that can be used with the DataMapper.</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+class AccountCollection extends TList
+{
+ public function addRange($accounts)
+ {
+ foreach($accounts as $account)
+ $this->add($account);
+ }
+
+ public function copyTo(TList $array)
+ {
+ $array->copyFrom($this);
+ }
+}
+</com:TTextHighlighter>
+
+<p>An <tt>ArrayAccess</tt> class can be specified for a select statement through the
+<tt>listClass</tt> attribute. The value of the <tt>listClass</tt> attribute is the
+full name of a PHP class that implements <tt>ArrayAccess</tt>. The statement
+should also indicate the <tt>resultClass</tt> so that the DataMapper knows how to
+handle the type of objects in the collection. The <tt>resultClass</tt> specified
+will be automatically mapped to the columns in the result, based on the result
+metadata. The following example shows a <tt><statement></tt> element with a
+<tt>listClass</tt> attribute.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="GetAllAccounts"
+ listClass="AccountCollection"
+ resultClass="Account">
+ 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
+</statement>
+</com:TTextHighlighter>
+
+<h2><tt>cacheModel</tt> attribute</h2>
+<p>If you want to cache the result of a query, you can specify a Cache Model as
+part of the <tt><statement></tt> element. The following example shows a
+<tt><cacheModel></tt> element and a corresponding <tt><statement></tt>.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<cacheModel id="product-cache" implementation="LRU">
+ <flushInterval hours="24"/>
+ <flushOnExecute statement="insertProduct"/>
+ <flushOnExecute statement="updateProduct"/>
+ <flushOnExecute statement="deleteProduct"/>
+ <property name="size" value="1000" />
+</cacheModel>
+
+<statement id="selectProductList" parameterClass="int" cacheModel="product-cache">
+ select * from PRODUCT where PRD_CAT_ID = #value#
+</statement>
+</com:TTextHighlighter>
+
+<p>In the above example, 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. See
+<a href="?page=Manual.CacheModels">Cache Models</a> for futher details</p>
+
+<h2><tt>extends</tt> attribute</h2>
+<p>When writing Sql, you often encounter duplicate fragments of SQL. SQLMap
+offers a simple yet powerful attribute to reuse them.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<select id="GetAllAccounts"
+ resultMap="indexed-account-result">
+select
+ Account_ID,
+ Account_FirstName,
+ Account_LastName,
+ Account_Email
+from Accounts
+</select>
+
+<select id="GetAllAccountsOrderByName"
+ extends="GetAllAccounts"
+ resultMap="indexed-account-result">
+ order by Account_FirstName
+</select>
+</com:TTextHighlighter>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/TheSQL.page b/demos/sqlmap/protected/pages/Manual/TheSQL.page new file mode 100644 index 00000000..0817ec26 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/TheSQL.page @@ -0,0 +1,157 @@ +<com:TContent ID="body">
+
+<h1>The SQL</h1>
+<p>If you are not using stored procedures, the most important part of a
+statement-type element is the SQL. You can use any SQL statement that is valid
+for your database system. Since SQLMap passes the SQL through to a standard
+libraries (Adodb for PHP), you can use any statement with SQLMap that you
+could use without SQLMap. You can use whatever functions your database system
+supports, and even send multiple statements, so long as your driver or
+provider supports them.</p>
+
+
+<h2>Escaping XML symbols</h2>
+<p>Because you are combining SQL and XML in a
+single document, conflicts can occur. The most common conflict is the
+greater-than and less-than symbols (><). SQL statements use these symbols as
+operators, but they are reserved symbols in XML. A simple solution is to
+escape the SQL statements that uses XML reserved symbols within a CDATA
+element. The following example demonstrates this.
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="SelectPersonsByAge" parameterClass="int" resultClass="Person">
+ <![CDATA[
+ SELECT * FROM PERSON WHERE AGE > #value#
+ ]]>
+</statement>
+</com:TTextHighlighter>
+
+<h2>Auto-Generated Keys</h2>
+<p>Many database systems support auto-generation of primary key fields, as a
+vendor extension. Some vendors pre-generate keys (e.g. Oracle), some vendors
+post-generate keys (e.g. MS-SQL Server and MySQL). In either case, you can
+obtain a pre-generated key using a <tt><selectKey></tt> stanza within an
+<tt><insert></tt> element. The following example shows an <tt><insert></tt>
+statement for either approach.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<!-- Oracle SEQUENCE Example using .NET 1.1 System.Data.OracleClient -->
+<insert id="insertProduct-ORACLE" parameterClass="product">
+ <selectKey resultClass="int" type="pre" property="Id" >
+ SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL
+ </selectKey>
+ insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#)
+</insert>
+
+<!-- Microsoft SQL Server IDENTITY Column Example -->
+<insert id="insertProduct-MS-SQL" parameterClass="product">
+ insert into PRODUCT (PRD_DESCRIPTION)
+ values (#description#)
+ <selectKey resultClass="int" type="post" property="id" >
+ select @@IDENTITY as value
+ </selectKey>
+</insert>
+
+<!-- MySQL Example -->
+<insert id="insertProduct-MYSQL" parameterClass="product">
+ insert into PRODUCT (PRD_DESCRIPTION)
+ values (#description#)
+ <selectKey resultClass="int" type="post" property="id" >
+ select LAST_INSERT_ID() as value
+ </selectKey>
+</insert>
+</com:TTextHighlighter>
+
+<h2><tt><generate></tt> tag</h2>
+<p>You can use SQLMap to execute any SQL statement your application requires.
+When the requirements for a statement are simple and obvious, you may not even
+need to write a SQL statement at all. The <tt><generate></tt> tag can be used to
+create simple SQL statements automatically, based on a <tt><parameterMap></tt>
+element. The four CRUD statement types (insert, select, update, and delete)
+are supported. For a select, you can select all or select by a key (or keys).
+The following example shows an example of generating the usual array of
+CRUD statements.</p>
+
+<div class="note"><b class="tip">Important:</b>
+The intended use of the <tt><generate></tt> tag is to save developers the trouble
+of coding mundane SQL statements (and only mundane statements). It is not
+meant as a object-to-relational mapping tool. There are many frameworks that
+provide extensive object-to-relational mapping features. The <tt><generate></tt>
+tag is not a replacement for any of those. When the <tt><generate></tt> tag does
+not suit your needs, use a conventional statement instead.
+</div>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+ <parameterMap id="insert-generate-params">
+ <parameter property="Name" column="Category_Name"/>
+ <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>
+ </parameterMap>
+
+ <parameterMap id="update-generate-params" extends="insert-generate-params">
+ <parameter property="Id" column="Category_Id" />
+ </parameterMap>
+
+ <parameterMap id="delete-generate-params">
+ <parameter property="Id" column="Category_Id" />
+ <parameter property="Name" column="Category_Name"/>
+ </parameterMap>
+
+ <parameterMap id="select-generate-params">
+ <parameter property="Id" column="Category_Id" />
+ <parameter property="Name" column="Category_Name"/>
+ <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>
+ </parameterMap>
+
+ <update id="UpdateCategoryGenerate" parameterMap="update-generate-params">
+ <generate table="Categories" by="Category_Id"/>
+ </update>
+
+ <delete id="DeleteCategoryGenerate" parameterMap="delete-generate-params">
+ <generate table="Categories" by="Category_Id, Category_Name"/>
+ </delete>
+
+ <select id="SelectByPKCategoryGenerate" resultClass="Category"
+ parameterClass="Category" parameterMap="select-generate-params">
+ <generate table="Categories" by="Category_Id"/>
+ </select>
+
+ <select id="SelectAllCategoryGenerate" resultClass="Category"
+ parameterMap="select-generate-params">
+ <generate table="Categories" />
+ </select>
+
+ <insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
+ <selectKey property="Id" type="post" resultClass="int">
+ select @@IDENTITY as value
+ </selectKey>
+ <generate table="Categories" />
+ </insert>
+</com:TTextHighlighter>
+
+<p>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.</p>
+
+<div class="note"><b class="tip">Note:</b>
+The SQL is generated when the DataMapper instance is built and can be cached
+afterward, so there is no performance impact at execution time.
+</div>
+
+<p>The generate tag supports two attributes.</p>
+
+<!-- tabular: align=|l|l|l|, width=(0.2 0.4 0.2) -->
+<table class="tabular">
+ <tr><th>Attribute</th><th>Description</th><th>Required</th></tr>
+ <tr>
+ <td><tt>table</tt></td>
+ <td>specifies the table name to use in the SQL statement</td>
+ <td>yes</td>
+ </tr>
+ <tr>
+ <td><tt>by</tt></td>
+ <td>specifies the columns to use in a WHERE clause</td>
+ <td>no</td>
+ </tr>
+</table>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/WorkingWithDataMaps.page b/demos/sqlmap/protected/pages/Manual/WorkingWithDataMaps.page new file mode 100644 index 00000000..321b2993 --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/WorkingWithDataMaps.page @@ -0,0 +1,129 @@ +<com:TContent ID="body">
+
+<h1>Working With Data Maps</h1>
+
+<p>If you want to know how to configure and install SQLMap,
+see the <a href="?page=Manual.Installing">Installation</a> and
+<a href="?page=Manual.Configuring">Configuration</a>.
+But if you want to know how SQLMap really
+works, continue from here.</p>
+
+<p>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.</p>
+
+<p>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.</p>
+
+<h1>What's in a Data Map definition file, anyway?</h1>
+
+<p>If you read the <a href="?page=Tutorial.TestFirst">Tutorial</a>, you've already
+seen some simple Data Map examples like the one below.</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<?xml version="1.0" encoding="UTF-8" ?>
+ <sqlMap namespace="LineItem">
+ <insert id="InsertLineItem" parameterClass="LineItem">
+ INSERT INTO [LinesItem]
+ (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice)
+ VALUES
+ (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#)
+ </insert>
+</sqlMap>
+</com:TTextHighlighter>
+
+<p>This map takes some properties from a <tt>LineItem</tt> 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</tt> instance directly to a
+library method:</p>
+
+<com:TTextHighlighter Language="php" CssClass="source">
+TMapper::instance()->insert("InsertLineItem",$lineItem);
+</com:TTextHighlighter>
+<p>No fuss, no muss.</p>
+
+<div class="info"><b class="tip">Info:</b>
+<b>A Quick Glance at Inline Parameters</b>
+<p>
+Say we have a mapped statement element that looks like this:</p>
+<com:TTextHighlighter Language="xml" CssClass="source">
+<statement id="InsertProduct">
+ insert into Products (Product_Id, Product_Description)
+ values (#Id#, #Description#);
+</statement>
+</com:TTextHighlighter>
+
+<p>The inline parameters here are <tt>#Id#</tt> and <tt>#Description#</tt>. Let's
+also say that we have an object with the properties <tt>Id</tt> and
+<tt>Description</tt>. 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:
+<com:TTextHighlighter Language="sql" CssClass="source">
+insert into Products (Product_Id, Product_Description) values (5, 'dog');
+</com:TTextHighlighter>
+See <a href="?page=Manual.InlineParameterMaps">inline parameters</a> for further details.
+</div>
+
+<p>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.)
+The following example shows a Data Map that specifies a cache, and uses a
+<tt><parameterMap></tt> and a <tt><resultMap></tt> to keep our SQL pristine.
+</p>
+
+<com:TTextHighlighter Language="xml" CssClass="source">
+<?xml version="1.0" encoding="UTF-8" ?>
+ <sqlMap namespace="Product">
+
+ <cacheModel id="productCache" type="LRU">
+ <flushInterval hours="24"/>
+ <property name="CacheSize" value="1000" />
+ </cacheModel>
+
+ <resultMap id="productResult" class="Product">
+ <result property="Id" column="Product_Id"/>
+ <result property="Description" column="Product_Description"/>
+ </resultMap>
+
+ <select id="GetProduct" parameterMap="productParam" cacheModel="productCache">
+ select * from Products where Product_Id = ?
+ </select>
+
+ <parameterMap id="productParam" class="Product">
+ <parameter property="Id"/>
+ </parameterMap>
+
+</sqlMap>
+</com:TTextHighlighter>
+
+<p>In the above example, <tt><parameterMap></tt> maps the SQL "?" to the
+product <tt>Id</tt> property. The <tt><resultMap></tt> maps the columns to our object
+properties. The <tt><cacheModel></tt> keeps the result of the last one thousand of
+these queries in active memory for up to 24 hours.</p>
+
+<p>The above example is longer and more complex than
+the previous example, but considering what you get in return, it seems
+like a fair trade. (A bargain even.)</p>
+
+<p>Many agile developers would start with something like
+the first example and add features like caching later. If you changed
+the Data Map from the first example to the second example, you
+would not have to touch your application source code at all. You can start
+simple and add complexity only when it is needed.</p>
+
+<p>A single Data Map definition file can contain as many Cache Models, 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.</p>
+
+</com:TContent>
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/diagram.png b/demos/sqlmap/protected/pages/Manual/diagram.png Binary files differnew file mode 100644 index 00000000..0a0ca73d --- /dev/null +++ b/demos/sqlmap/protected/pages/Manual/diagram.png |
