From 4b78404c20490a615459267426ce9e6737bf4485 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Jul 2006 09:20:45 +0000 Subject: Moving files. --- demos/sqlmap/protected/pages/Home.page | 1 + .../sqlmap/protected/pages/Manual/BigPicture.page | 151 +++++++++++++ .../protected/pages/Manual/BuildingTSqlMapper.page | 73 ++++++ .../sqlmap/protected/pages/Manual/CacheModels.page | 113 ++++++++++ .../protected/pages/Manual/CodingExamples.page | 55 +++++ .../protected/pages/Manual/ComplexProperties.page | 247 ++++++++++++++++++++ .../protected/pages/Manual/CompositeKeys.page | 53 +++++ .../pages/Manual/ConfigurationElements.page | 249 +++++++++++++++++++++ .../sqlmap/protected/pages/Manual/Configuring.page | 47 ++++ .../protected/pages/Manual/CustomTypeHandlers.page | 114 ++++++++++ .../protected/pages/Manual/DataMapperAPI.page | 162 ++++++++++++++ .../sqlmap/protected/pages/Manual/DynamicSQL.page | 5 + .../protected/pages/Manual/ImplicitResultMaps.page | 96 ++++++++ .../protected/pages/Manual/InheritanceMapping.page | 150 +++++++++++++ .../pages/Manual/InlineParameterMaps.page | 102 +++++++++ .../sqlmap/protected/pages/Manual/Installing.page | 51 +++++ .../protected/pages/Manual/MappedStatements.page | 173 ++++++++++++++ demos/sqlmap/protected/pages/Manual/Overview.page | 48 ++++ .../protected/pages/Manual/ParameterMap.page | 184 +++++++++++++++ .../pages/Manual/ResultMapAttributes.page | 132 +++++++++++ .../sqlmap/protected/pages/Manual/ResultMaps.page | 67 ++++++ .../pages/Manual/StatementElementAttributes.page | 216 ++++++++++++++++++ demos/sqlmap/protected/pages/Manual/TheSQL.page | 157 +++++++++++++ .../pages/Manual/WorkingWithDataMaps.page | 129 +++++++++++ demos/sqlmap/protected/pages/Manual/diagram.png | Bin 0 -> 30320 bytes demos/sqlmap/protected/pages/Sample/Home.page | 5 + .../sqlmap/protected/pages/Sample/ProductList.page | 9 + .../sqlmap/protected/pages/Sample/ProductList.php | 18 ++ demos/sqlmap/protected/pages/Sample/crud1.page | 19 ++ demos/sqlmap/protected/pages/Sample/crud1.php | 21 ++ demos/sqlmap/protected/pages/Sample/crud2.page | 36 +++ demos/sqlmap/protected/pages/Sample/crud2.php | 79 +++++++ .../sqlmap/protected/pages/Tutorial/TestAgain.page | 214 ++++++++++++++++++ .../sqlmap/protected/pages/Tutorial/TestFirst.page | 238 ++++++++++++++++++++ .../protected/pages/Tutorial/TestSecond.page | 116 ++++++++++ demos/sqlmap/protected/pages/Tutorial/example1.png | Bin 0 -> 236887 bytes demos/sqlmap/protected/pages/Tutorial/grid1.png | Bin 0 -> 275250 bytes demos/sqlmap/protected/pages/Tutorial/grid2.png | Bin 0 -> 218210 bytes demos/sqlmap/protected/pages/config.xml | 8 + 39 files changed, 3538 insertions(+) create mode 100644 demos/sqlmap/protected/pages/Home.page create mode 100644 demos/sqlmap/protected/pages/Manual/BigPicture.page create mode 100644 demos/sqlmap/protected/pages/Manual/BuildingTSqlMapper.page create mode 100644 demos/sqlmap/protected/pages/Manual/CacheModels.page create mode 100644 demos/sqlmap/protected/pages/Manual/CodingExamples.page create mode 100644 demos/sqlmap/protected/pages/Manual/ComplexProperties.page create mode 100644 demos/sqlmap/protected/pages/Manual/CompositeKeys.page create mode 100644 demos/sqlmap/protected/pages/Manual/ConfigurationElements.page create mode 100644 demos/sqlmap/protected/pages/Manual/Configuring.page create mode 100644 demos/sqlmap/protected/pages/Manual/CustomTypeHandlers.page create mode 100644 demos/sqlmap/protected/pages/Manual/DataMapperAPI.page create mode 100644 demos/sqlmap/protected/pages/Manual/DynamicSQL.page create mode 100644 demos/sqlmap/protected/pages/Manual/ImplicitResultMaps.page create mode 100644 demos/sqlmap/protected/pages/Manual/InheritanceMapping.page create mode 100644 demos/sqlmap/protected/pages/Manual/InlineParameterMaps.page create mode 100644 demos/sqlmap/protected/pages/Manual/Installing.page create mode 100644 demos/sqlmap/protected/pages/Manual/MappedStatements.page create mode 100644 demos/sqlmap/protected/pages/Manual/Overview.page create mode 100644 demos/sqlmap/protected/pages/Manual/ParameterMap.page create mode 100644 demos/sqlmap/protected/pages/Manual/ResultMapAttributes.page create mode 100644 demos/sqlmap/protected/pages/Manual/ResultMaps.page create mode 100644 demos/sqlmap/protected/pages/Manual/StatementElementAttributes.page create mode 100644 demos/sqlmap/protected/pages/Manual/TheSQL.page create mode 100644 demos/sqlmap/protected/pages/Manual/WorkingWithDataMaps.page create mode 100644 demos/sqlmap/protected/pages/Manual/diagram.png create mode 100644 demos/sqlmap/protected/pages/Sample/Home.page create mode 100644 demos/sqlmap/protected/pages/Sample/ProductList.page create mode 100644 demos/sqlmap/protected/pages/Sample/ProductList.php create mode 100644 demos/sqlmap/protected/pages/Sample/crud1.page create mode 100644 demos/sqlmap/protected/pages/Sample/crud1.php create mode 100644 demos/sqlmap/protected/pages/Sample/crud2.page create mode 100644 demos/sqlmap/protected/pages/Sample/crud2.php create mode 100644 demos/sqlmap/protected/pages/Tutorial/TestAgain.page create mode 100644 demos/sqlmap/protected/pages/Tutorial/TestFirst.page create mode 100644 demos/sqlmap/protected/pages/Tutorial/TestSecond.page create mode 100644 demos/sqlmap/protected/pages/Tutorial/example1.png create mode 100644 demos/sqlmap/protected/pages/Tutorial/grid1.png create mode 100644 demos/sqlmap/protected/pages/Tutorial/grid2.png create mode 100644 demos/sqlmap/protected/pages/config.xml (limited to 'demos/sqlmap/protected/pages') diff --git a/demos/sqlmap/protected/pages/Home.page b/demos/sqlmap/protected/pages/Home.page new file mode 100644 index 00000000..ff226d4e --- /dev/null +++ b/demos/sqlmap/protected/pages/Home.page @@ -0,0 +1 @@ +

Welcome to Prado!

\ No newline at end of file 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 @@ + +

The Big Picture

+

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.

+ +

What does it do?

+

Developers often create maps between objects within an application. One +definition of a Mapper is an "object that sets up communication between two +independent objects." A Data Mapper is a "layer of mappers that moves data +between objects and a database while keeping them independent of each other +and the mapper itself." [Patterns of Enterprise Architecture, ISBN +0-321-12742-0].

+ +

You provide the database and the objects; SQLMap provides the mapping layer +that goes between the two.

+ +

How does it work?

+ +

Your programming platform already provides a capable library for accessing +databases, whether through SQL statements or stored procedures. But developers +find several things are still hard to do well when using "stock" PHP +function including:

+ +

Separating SQL code from programming code Passing input parameters to the +library classes and extracting the output Separating data access classes from +business logic classes Caching often-used data until it changes Managing +transactions and many more -- by using XML documents to create a mapping +between a plain-old object and a SQL statement or a stored procedure. The +"plain-old object" can be any PHP object.

+ +

Tip: +The object does not need to be part of a special object hierarchy or implement +a special interface. (Which is why we call them "plain-old" objects.) +Whatever you are already using should work just fine. +

+ + alt="SQLMap DataMapper work flow" id="fig:diagram.png" class="figure"/> +
Figure 1: SQLMap DataMapper work flow
+ +

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.

+ +

Execute the mapping by passing the parameter and the name you gave the +statement or procedure in your XML descriptor. This step is where the magic +happens. The framework will prepare the SQL statement or stored procedure, set +any runtime values using your parameter, execute the procedure or statement, +and return the result.

+ +

In the case of an update, the number of rows affected is returned. In the case +of a query, a single object, or a collection of objects is returned. Like the +parameter, the result object, or collection of objects, can be a plain-old +object or a primitive type.

+ +

So, what does all this look like in your source code? Here's how you might +code the insert of a "lineItem" object into your database.

+ + +TMapper::instance()->insert("InsertLineItem", $lineItem); + + +

If your database is generating the primary keys, the generated key can be +returned from the same method call, like this:

+ + +$myKey = TMapper::instance()->insert("InsertLineItem", $lineItem); + + +

The following example shows an XML descriptor for "InsertLineItem". + + + INSERT INTO [LinesItem] + (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice) + VALUES + (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#) + + select @@IDENTITY as value + + + +

+ +

The <selectKey> 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: + +$productList = Mapper::instance()->queryForList("selectProduct",$categoryKey); + +Or just one, if that's all you need: + +$product = Mapper::instance()->queryForObject("selectProduct",$categoryKey); + +

+ +

Of course, there's more, but this is SQLMap from 10,000 meters. (For a longer, +gentler introduction, see the Tutorial.) +The Data Map definition files describes +where the statement for "InsertLineItem" would +be defined. The Installation and Setup section describes +the "bootstrap" configuration file that exposes SQLMap to your application.

+ +

Is SQLMap the best choice for my project?

+

SQLMap is a Data Mapping tool. Its role is to map the columns of a database +query (including a stored procedure) to the properties of an object. If your +application is based on business objects (including array or lists of +objects), then SQLMap can be a good choice. SQLMap is an even better choice +when your application is layered, so that that the business layer is distinct +from the user-interface layer.

+ +

Under these circumstances, another good choice would be an Object/Relational +Mapping tool (OR/M tool), like [...]. Other products in this category are +[...] and [...] . An OR/M tool generates all or most of the SQL for you, +either beforehand or at runtime. These products are called OR/M tools because +they try to map an object graph to a relational schema.

+ +

SQLMap is not an OR/M tool. SQLMap helps you map objects to stored procedures +or SQL statements. The underlying schema is irrelevant. An OR/M tool is great +if you can map your objects to tables. But they are not so great if your +objects are stored as a relational view rather than as a table. If you can +write a statement or procedure that exposes the columns for your object, +regardless of how they are stored, SQLMap can do the rest.

+ +

So, how do you decide whether to OR/M or to DataMap? As always, the best +advice is to implement a representative part of your project using either +approach, and then decide. But, in general, OR/M is a good thing when you +

+Likewise, the best time to use a Data Mapper, like SQLMap, is when: + +

+ +

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!

+ + +
\ 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 @@ + +

Using SQLMap PHP DataMapper

+

The SQLMap DataMapper API provides four core functions:

+
    +
  1. build a TSqlMapper instance from a configuration file or cache
  2. +
  3. execute an update query (including insert and delete)
  4. +
  5. execute a select query for a single object
  6. +
  7. execute a select query for a list of objects
  8. +
+ +

The API also provides support for retrieving paginated lists and managing +transactions.

+ +

Building a TSqlMapper instance

+

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.

+ +

The framework provides service methods that you can call which read the +configuration file (and any of its definition files) and builds a +TSqlMapper object. The TSqlMapper object provides access to the rest +of the framework. The following example shows a singleton TMapper that is +similar to the one bundled with the framework.

+ + +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; + } +} + + +

To obtain the TSqlMapper instance, first configure the mapper once.

+ +TMapper::configure('path/to/sqlmap.config'); + + +

The TDomSqlMapBuilder object will go throught the the sqlmap.config +file and build a TSqlMapper instance. To use TSqlMapper in your +application, specify one of the TSqlMapper methods. Here's an example:

+ + +$list = TMapper::instance()->queryForList("PermitNoForYearList", $values); + + +

Multiple Databases

+

If you need access to more than one database from the same application, create +a DataMapper configuration file for that database and another Mapper class to +go with it.

+ +

TDomSqlMapBuilder Configuration Options

+

If you find that you already have loaded your DataMapper configuration +information as a SimpleXMLElement instance within your application, the +TDomSqlMapBuilder provides Configure overloads for those types as +well.

+ +
\ 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 @@ + + +

Cache Models

+

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 <cacheModel> element. +

+ +

The results from a query Mapped Statement can be cached simply by specifying +the cacheModel parameter in the statement tag (seen above). A cache model +is a configured cache that is defined within your DataMapper configuration +file. Cache models are configured using the cacheModel element as +follows:

+ + + + + + + + + + +

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 type attribute is either a class name, or an alias for one +of the included implementations (see below). The cache will be flushed +whenever the insertProduct, updateProduct, or deleteProduct +mapped statements are executed. There can be any number of "flush on +execute" elements specified for a cache. Some cache implementations may need +additional properties, such as the "cache-size" property demonstrated above. +In the case of the LRU cache, the size determines the number of entries to +store in the cache. Once a cache model is configured, you can specify the +cache model to be used by a mapped statement, for example:

+ + + + select * from PRODUCT where PRD_CAT_ID = #value# + + + +

Cache Implementation

+

The cache model uses a pluggable framework for supporting different types of +caches. The choice of cache is specified in the "implementation" attribute +of the cacheModel element as discussed above. The class name specified +must be an implementation of the ISqlMapCache interface, or one of the +two aliases discussed below. Further configuration parameters can be passed to +the implementation via the property elements contained within the body of the +cacheModel. Currently there are 2 implementations included with the SQLMap PHP DataMapper.

+ +
Info: +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. +
+ +

Least Recently Used [LRU] Cache

+

The LRU cache implementation uses +an Least Recently Used algorithm to determines how objects are automatically +removed from the cache. When the cache becomes over full, the object that was +accessed least recently will be removed from the cache. This way, if there is +a particular object that is often referred to, it will stay in the cache with +the least chance of being removed. The LRU cache makes a good choice for +applications that have patterns of usage where certain objects may be popular +to one or more users over a longer period of time (e.g. navigating back and +forth between paginated lists, popular search keys etc.).

+ +

The LRU implementation is configured as follows:

+ + + + + + + + + +

Only a single property is recognized by the LRU cache implementation. This +property, named CacheSize must be set to an integer value representing +the maximum number of objects to hold in the cache at once. An important thing +to remember here is that an object can be anything from a single string +instance to an array of object. So take care not to store too much in your +cache and risk running out of memory.

+ +

FIFO Cache

+

The FIFO cache implementation uses an First In First Out algorithm to +determines how objects are automatically removed from the cache. When the +cache becomes over full, the oldest object will be removed from the cache. The +FIFO cache is good for usage patterns where a particular query will be +referenced a few times in quick succession, but then possibly not for some +time later.

+ +

The FIFO implementation is configured as follows:

+ + + + + + + + + + +

Only a single property is recognized by the FIFO cache implementation. This +property, named CacheSize must be set to an integer value representing +the maximum number of objects to hold in the cache at once. An important thing +to remember here is that an object can be anything from a single String +instance to an array of object. So take care not to store too much in your +cache and risk running out of memory.

+ +
\ 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 @@ + +

Cookbook sample

+ +

Executing Update (insert, update, delete)

+ +$product = new Product(); +$product->setId(1); +$product->setDescription('Shih Tzui'); + +$key = $sqlMap->insert('insertProduct', $product); + + +

Executing Query for Object (select)

+ +$key = 1; +$product = $sqlMap->queryForObject ('getProduct', $key); + + + +

Executing Query for Object (select) With Preallocated Result Object

+ +$customer = new Customer(); + +$sqlMap->beginTransaction(); + +$sqlMap->queryForObject('getCust', $parameter, $customer); +$sqlMap->queryForObject('getAddr', $parameter, $customer); +$sqlMap->commitTransaction(); + + +

Executing Query for List (select)

+ +$list = $sqlMap->queryForList ('getProductList'); + + +

Executing Query for List (select) With Result Boundaries

+ +$list = $sqlMap->queryForList ('getProductList', $key, null, 0, 40); + + +

Executing Query for Paginated List (select)

+ +$list = $sqlMap->queryForPagedList ('getProductList', null, 10); +$list->nextPage(); +$list->previousPage(); + + +

Executing Query for Map

+ + $map = $sqlMap->QueryForMap('getProductList', null, 'productCode'); + $product = $map['EST-93']; + + + +
\ 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 @@ + +

Complex Properties

+

In a relational database, one table will often refer to another. Likewise, +some of your business objects may include another object or list of objects. +Types that nest other types are called "complex types". You may not want a +statement to return a simple type, but a fully-formed complex type.

+ +

In the database, a related column is usually represented via a 1:1 +relationship, or a 1:M relationship where the class that holds the complex +property is from the "many side" of the relationship and the property itself +is from the "one side" of the relationship. The column returned from the +database will not be the property we want; it is a key to be used in another +query.

+ +

From the framework's perspective, the problem is not so much loading a complex +type, but loading each "complex property". To solve this problem, you can +specify in the Result Map a statement to run to load a given property. In +the following example, the "category" property of the +"select-product-result" element is a complex property.

+ + + + + + + + + + + + + + + + + + +

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.

+ +

Avoiding N+1 Selects (1:1)

+

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.

+ +

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 Cache Models +more details about caches.

+ +

Another solution is to use a standard SQL join to return the columns you need +from the another table. A join can bring all the columns we need over from the +database in a single query. When you have a nested object, you can reference +nested properties using a dotted notation, like "category.description".

+ +

The following example solves the same problem as the previous +example, but uses a join instead of nested properties.

+ + + + + + + + + + select * + from PRODUCT, CATEGORY + where PRD_CAT_ID=CAT_ID + and PRD_ID = #value# + + + +
Lazy Loading vs. Joins (1:1): +

It's important to note that using a join is not always better. If you are in a +situation where it is rare to access the related object (e.g. the category +property of the Product class) then it might actually be faster to avoid the +join and the unnecessary loading of all category properties. This is +especially true for database designs that involve outer joins or nullable +and/or non-indexed columns. In these situations it might be better to use the +sub-select solution with lazy loading enabled. The general rule of thumb is: +use the join if you're more likely going to access the associated properties +than not. Otherwise, only use it if lazy loading is not an option.

+ +

If you're having trouble deciding which way to go, don't worry. No matter +which way you go, you can always change it without impacting your application +source code. 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. "===").

+
+ +

Complex Collection Properties

+

It is also possible to load properties that represent lists of complex +objects. In the database the data would be represented by a M:M relationship, +or a 1:M relationship where the class containing the list is on the "one +side" of the relationship and the objects in the list are on the "many +side"". To load a TList of objects, there is no change to the statement +(see example above). The only difference required to cause the SQLMap +DataMapper framework to load the property as a TList is that the property +on the business object must be of type TList. For example, if a Category +has a TList of Product instances, the mapping would look like this +(assuming Category has a property called "ProductList" of TList.):

+ + + + + + + + + + + + + + + select * from CATEGORY where CAT_ID = #value# + + + + select * from PRODUCT where PRD_CAT_ID = #value# + + + +

Avoiding N+1 Select Lists (1:M and M:N)

+

This is similar to the 1:1 situation above, but is of even greater concern due +to the potentially large amount of data involved. The problem with the +solution above is that whenever you load a Category, two SQL statements are +actually being run (one for the Category and one for the list of associated +Products). This problem seems trivial when loading a single Category, but if +you were to run a query that loaded ten (10) Categories, a separate query +would be run for each Category to load its associated list of Products. This +results in eleven (11) queries total: one for the list of Categories and one +for each Category returned to load each related list of Products (N+1 or in +this case 10+1=11). To make this situation worse, we're dealing with +potentially large lists of data.

+ + + + + + + + + + + + + + + + select * from CATEGORY where CAT_ID = #value# + + + + + select * from PRODUCT where PRD_CAT_ID = #value# + + + +

1:N and M:N Solution?

+

+One way to avoid multiple SQL queries is to use Joins in the query +together with the groupBy attribute in <resultMap> and +the resultMapping attribute of <result>. +The following example selects all the categories and the corresponding +products using 1 query. +

+ + + + + + + + + + + + + + + + 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 + + + +

In the above example, the groupBy attribute is set +to the column of that specifies the Category ID. All the rows +with the same CAT_ID will be considered as a collection +for the ProductList property.

+ +
Lazy Loading vs. Joins (1:M and M:N): +

As with the 1:1 situation described previously, it's important to note that +using a join is not always better. This is even more true for collection +properties than it was for individual value properties due to the greater +amount of data. If you are in a situation where it is rare to access the +related object (e.g. the ProductList property of the Category class) then it +might actually be faster to avoid the join and the unnecessary loading of the +list of products. This is especially true for database designs that involve +outer joins or nullable and/or non-indexed columns. In these situations it +might be better to use the sub-select solution with the lazy loading. The +general rule of thumb is: use the join if you're more likely going to access +the associated properties than not. Otherwise, only use it if lazy loading is +not an option.

+ +

As mentioned earlier, if you're having trouble deciding which way to go, don't +worry. No matter which way you go, you can always change it without impacting +your PHP code. The two examples above would result in exactly the same object +graph and are loaded using the exact same method call. The only consideration +is that if you were to enable caching, then the using the separate select (not +the join) solution could result in a cached instance being returned. But more +often than not, that won't cause a problem (your application should not be +dependent on instance level equality i.e. "===").

+
+ +
\ 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 @@ + + +

Composite Keys or Multiple Complex Parameters Properties

+

You might have noticed that in the above examples there is only a single key +being used as specified in the resultMap by the column attribute. +This would suggest that only a single column can be associated to a related +mapped statement. However, there is an alternate syntax that allows multiple +columns to be passed to the related mapped statement. This comes in handy for +situations where a composite key relationship exists, or even if you simply +want to use a parameter of some name other than #value#. The alternate +syntax for the column attribute is simply param1=column1, param2=column2, ... , +paramN=columnN. Consider the example below where the PAYMENT table +is keyed by both Customer ID and Order ID:

+ + + + + + ... + + + + + select * from PAYMENT + where PAY_ORD_ID = #itemId# + and PAY_CST_ID = #custId# + + + +

Optionally you can just specify the column names as long as they're in the +same order as the parameters. For example:

+ +{ORD_ID, ORD_CST_ID} + + +
Important! +

Currently the SQLMap DataMapper framework does not automatically resolve +circular relationships. Be aware of this when implementing parent/child +relationships (trees). An easy work around is to simply define a second result +map for one of the cases that does not load the parent object (or vice versa), +or use a join as described in the "N+1 avoidance" solutions.

+
+ +
Info: +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 <sqlMap> root element. +
+ + +
\ 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 @@ + + +

DataMapper Configuration Elements

+ +

Sometimes the values we use in an XML configuration file occur in more than +one element. Often, there are values that change when we move the application +from one server to another. To help you manage configuration values, you can +specify a standard properties file (with name=value entries) as part of a +DataMapper configuration. Each named value in the properties file becomes a +shell variable that can be used in the DataMapper configuration file and your +Data Map definition files. +

+ +

<properties> attributes

+

The <properties> element can accept one resource attribute to +specify the location of the properties file.

+ + + + + + + + + + + +
AttributeDescription
resourceSpecify the properties file to be loaded from the directory relative + to the current file. + Example: resource="properties.config" +
+

+For example, if the "properties.config" file contains

+ + + + + + + +

then all elements in the DataMapper configuration can use the variable +${username} to insert the value "albert". For example:

+ + + + + +
Tip: +Properties are handy during building, testing, and deployment by making it +easy to reconfigure your application for multiple environments. +
+ +

<property> element and attributes

+

You can also specify more than one properties file or add property keys and +values directly into your SqlMap.config file by using <property> +elements.

+ + + + + + + + + + + + + + + + + + + +
AttributeDescription
resourceSpecify the properties file to be loaded from the directory relative + to the current file. + Example: resource="properties.config" +
keyDefines a property key (variable) name. + Example: key="username" +
value Defines a value that will be used by the DataMapper in place of the + the specified property key/variable. + Example: value="mydbuser" +
+

For example:

+ + + + + + + + + +

The <typeHandler> Element

+

The <typeHandler> 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.

+ + + + + +

The <typeHandler> element has three attributes:

+ + + + + + + + + + + + + + + + + + +
AttributeDescription
typeRefers to the name of the type to handle. + Example: type="date" +
dbTypeIndicates the provider dbType to handle. + Example: dbType="Varchar2" +
callback The custom type handler class name. + Example: callback="TDateTimeHandler" +
+ +

The <provider> element and attribute

+ +

The <provider> element encloses a <datasource> that configure the +database system for use by the framework.

+ + + + + + + + + + + +
AttributeDescription
classThe database provider class that extends + TDatabaseProvider. + Example: class="TAdodbProvider" +
+ +

The <datasource> element and attributes

+

The <datasource> element specifies the connection string. +The following example shows a <datasource> element for a MySql connection.

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
AttributeDescription
connectionStringData Source Name (DSN) connection string. + Example: connectionString="mysql://root:pwd@localhost/mydb" +
driver Database driver name (mysql, sqlite, etc.). + Example: driver="mysql" +
hostDB host name/IP (and port number) in the format host[:port]. + Example: host="localhost" +
usernameDatabase connection username.
passwordDatabase connection password.
databaseDatabase name to use in the connection.
+ +
Tip: + Use Data Source Name (DSN) connection string or specify the + necessary individual connection parameters. +
+ +

The <sqlMap> Element

+

On a daily basis, most of your work will be with the Data Maps, which are +covered in Working with Data Maps. +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.

+ +The following example shows <sqlMap> elements for loading a set of +Data Map definitions. + + + + + + + + + + + + + +
Tip: +Since the application root directory location differs by project type +(Windows, Web, or library), it is best to use a properties variable to +indicate the relative path when using the <sqlMap> resource +attribute. Having a variable defined in a properties file makes it easy to +change the path to all your Data Mapper configuration resources in one +location (note the ${projectdir} and ${root} variables in the +example above). +
+ +
\ 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 @@ + + +

Configuring the DataMapper for PHP

+

The SQLMap PHP DataMapper is configured using a central XML descriptor file, +usually named SqlMap.config, which provides the details for your data +source, data maps, and other features like caching, and transactions. At +runtime, your application code will call a class method provided by the SQLMap +library to read and parse your SqlMap.config file. After parsing the +configuration file, a DataMapper client will be returned by SQLMap for your +application to use.

+ +

DataMapper clients

+

Currently, the SQLMap PHP DataMapper framework revolves around the +TSqlMapper class, which acts as a facade to the DataMapper framework API. +You can create a DataMapper client by instantiating an object of the +TSqlMapper class. An instance of the TSqlMapper class (your +DataMapper client) is created by reading a single configuration file. Each +configuration file can specify one database or data source. You can of couse +use multiple DataMapper clients in your application. Just create another +configuration file and pass the name of that file when the DataMapper client +is created. The configuration files might use a different account with the +same database, or reference different databases on different servers. You can +read from one client and write to another, if that's what you need to do. +First, let's take a look at the DataMapper configuration file. + +

DataMapper Configuration File (SqlMap.config)

+

A sample configuration file for a PHP web application is shown below. +Not all configuration elements are required. See +DataMapper Configuration Elements +for details of each configuration elements in a SqlMap.config file. + + +<?xml version="1.0" encoding="utf-8"?> + + + + + + + + + + + + + + \ 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 @@ + + +

Custom Type Handlers

+

A custom type handler allows you to extend the DataMapper's capabilities in +handling types that are specific to your database provider, not handled by +your database provider, or just happen to be part of your application design. +The SQLMap for PHP DataMapper provides an interface, +ITypeHandlerCallback, for you to use in implementing your custom type +handler.

+ + +interface ITypeHandlerCallback +{ + public function getParameter($object); + + public function getResult($string); + + public function createNewInstance(); +} + + +

The getParameter method allows you to process a <statement> +parameter's value before it is added as an parameter. This enables you to do +any necessary type conversion and clean-up before the DataMapper gets to work.

+ +

The getResult method allows you to process a database result value right +after it has been retrieved by the DataMapper and before it is used in your +resultClass, resultMap, or listClass.

+ +

The createNewInstance method allows the DataMapper to create new instance +of a particular type handled by this callback.

+ +

One scenario where custom type handlers are useful are the when you want to +use date time values in the database. First, consider a very basic TDateTime +class.

+ + +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; + } +} + + +

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.

+ + +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; + } +} + + +

With our custom type handler we can use the handler in our SqlMaps. To do +that, we specify it as a basic <typeHandler> for all date types +mapped in our SqlMap files

+ + +[Our SqlMap.config] + + + + + + +[One of our SqlMap.xml files] + + + + + + + + + +
\ 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 @@ + + +

Exploring the SQLMap PHP DataMapper API through the TSqlMapper

+

The TSqlMapper instance acts as a facade to provide access the rest of +the DataMapper framework. The DataMapper API methods are shown below.

+ + +/* 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() + + +

Note that each of the API methods accept the name of the Mapped Statement as +the first parameter. The statementName parameter corresponds to the +id of the Mapped Statement in the Data Map definition. +In each case, a parameterObject also may be +passed. The following sections describe how the API methods work.

+ +

Insert, Update, Delete

+ +public function insert($statementName, $parameter=null) +public function update($statementName, $parameter=null) +public function delete($statementName, $parameter=null) + + +

If a Mapped Statement uses one of the <insert>, <update>, or +<delete> statement-types, then it should use the corresponding API +method. The <insert> element supports a nested <selectKey> element +for generating primary keys. If the +<selectKey> stanza is used, then insert returns the generated key; +otherwise a null object is returned. Both the update and delete +methods return the number of rows affected by the statement. +

+ +

QueryForObject

+ +public function queryForObject($statementName, $parameter=null, $result=null); + + +

If a Mapped Statement is expected to select a single row, then call it using +queryForObject. Since the Mapped Statement definition specifies the +result class expected, the framework can both create and populate the result +class for you. Alternatively, if you need to manage the result object +yourself, say because it is being populated by more than one statement, you +can use the alternate form and pass your $resultObject as the third +parameter.

+ +

QueryForList

+ + +public function queryForList($statementName, $parameter=null, $result=null, + $skip=-1, $max=-1); + + +

If a Mapped Statement is expected to select multiple rows, then call it using +queryForList. Each entry in the list will be an result object populated +from the corresponding row of the query result. If you need to manage the +$resultObject yourself, then it can be passed as the third parameter. If +you need to obtain a partial result, the fourth parameter $skip and +fifth parameter $max allow you to skip a number of records (the starting +point) and the maximum number to return.

+ +

QueryForPagedList

+ + public function queryForPagedList($statementName, $parameter=null, $pageSize=10); + + +

We live in an age of information overflow. A database query often returns more +hits than users want to see at once, and our requirements may say that we need +to offer a long list of results a "page" at a time. If the query returns +1000 hits, we might need to present the hits to the user in sets of fifty, and +let them move back and forth between the sets. Since this is such a common +requirement, the framework provides a convenience method.

+ +

The TSqlMapPagedList interface includes methods for navigating through +pages (nextPage(), previousPage(), gotoPage($pageIndex)) and +also checking the status of the page (getIsFirstPage(), +getIsMiddlePage(), getIsLastPage(), getIsNextPageAvailable(), +getIsPreviousPageAvailable(), getCurrentPageIndex(), +getPageSize()). The total number of records available is not accessible +from the TSqlMapPagedList interface, unless a virtual count is defined +using setVirtualCount($value), this should be easily accomplished by +simply executing a second statement that counts the expected results.

+ +
Tip: +The queryForPagedList method is convenient, but note that a larger set +(up to 3 times the page size) will first be returned by the database provider +and the smaller set extracted by the framework. The higher the page size, the +larger set that will be returned and thrown away. For very large sets, you may +want to use a stored procedure or your own query that uses $skip and +$max as parameters in queryForList. +
+ +

QueryForMap

+ +public function queryForMap($statementName, $parameter=null, + $keyProperty=null, $valueProperty=null); + + +

The queryForList methods return the result objects within a TList or +array instance. Alternatively, the queryForMap returns a TMap or +associative array instance. The value of each entry is one of the result +objects. The key to each entry is indicated by the $keyProperty +parameter. This is the name of the one of the properties of the result object, +the value of which is used as the key for each entry. For example, If you +needed a set of Employee objects, you might want them returned as a +TMap keyed by each object's EmployeeNumber property.

+ +

If you don't need the entire result object in your result, you can add the +$valueProperty parameter to indicate which result object property should +be the value of an entry. For example, you might just want the +EmployeeName keyed by EmployeeNumber.

+ +

Transaction

+

The DataMapper API includes methods to demarcate transactional boundaries. A +transaction can be started, committed and/or rolled back. You can call the +transaction methods from the TSqlMapper instance.

+ + +// 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() + + +

Using transactions example.

+ +try +{ + $sqlMap->beginTransaction(); + $item = $sqlMap->queryForObject("getItem", $itemId); + $item->setDescription($newDescription); + $sqlMap->update("updateItem", $item); + $sqlMap->commitTransaction(); +} +catch +{ + $sqlMap->rollBackTransaction(); +} + + +
\ 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 @@ + +

Dynamic SQL

+

Dynamic SQL is not implemented yet.

+ +
\ 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 @@ + + +

Implicit Result Maps

+

If the columns returned by a SQL statement match the result object, you may +not need an explicit Result Map. If you have control over the relational +schema, you might be able to name the columns so they also work as property +names. In the following example, the column names and property names +already match, so a result map is not needed.

+ + + + select + id, + description + from PRODUCT + where id = #value# + + + +

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.

+ + + + select + PRD_ID as id, + PRD_DESCRIPTION as description + from PRODUCT + where PRD_ID = #value# + + + +

Of course, these techniques will not work if you need to specify a column +type, a null value, or any other property attributes.

+ +

Primitive Results (i.e. String, Integer, Boolean)

+

Many times, we don't need to return an object with multiple properties. We +just need a string, integer, boolean, and so forth. If you don't need to +populate an object, SQLMap can return one of the primitive types instead. If +you just need the value, you can use a primitive type as a result class, as +shown in following example.

+ + + + + + + + + + + +

Maps with ResultMaps

+

Instead of a rich object, sometimes all you might need is a simple key/value +list of the data, where each property is an entry on the list. If so, Result +Maps can populate an array instance as easily as property objects. The syntax +for using an array is identical to the rich object syntax. As shown in following example, +only the result object changes.

+ + + + + + + + + + +

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 id, code, and so forth, would be the key of the +entry, and the value of the mapped columns would be the value of the entry.

+ +

As shown in the following example, you can also use an implicit Result +Map with an array type.

+ + + + select * from PRODUCT + + + +

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.

+ +
Note: +Certain providers may return column names in upper case or lower case format. +When accessing values with such a provider, you will have to pass the key name +in the expected case. +
+ +
\ 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 @@ + + +

Inheritance Mapping

+

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:

+ +

You can use the most +efficient mapping strategies from a SQL and query performance perspective when +using the inheritance mappings of the DataMapper. To implement an inheritance +mapping, the resultMap must define one or more columns in your query's +resultset that will serve to identify which resultMap should be used to map +each result record to a specific subclass. In many cases, you will use one +column value for the DataMapper to use in identifying the proper resultMap and +subclass. This column is known as a discriminator.

+ +

For example, we have a table defined in a database that contains Document +records. There are five table columns used to store Document IDs, Titles, +Types, PageNumbers, and Cities. Perhaps this table belongs to a legacy +database, and we need to create an application using this table with a domain +model that defines a class hierarchy of different types of Documents. Or +perhaps we are creating a new application and database and just want to +persist the data found in a set of related classes into one table. In either +case, the DataMapper's inheritance mapping feature can help.

+ + +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 +) + + +

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.

+ + +class Document +{ + public $ID = -1; + public $Title = ''; +} + +class Book extends Document +{ + public $PageNumber = -1; +} + +class Newspaper extends Document +{ + public $City = ''; +} + + +

Now that we have our classes and database table, we can start working on our +mappings. We can create one <select> 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 Document_Type column holds values +that will distinguish one record from another for mapping the results into our +class hierarchy.

+ + + + + + + + + + + + + + + + + + + + + +

The DataMapper compares the data found in the discriminator column to the +different <submap> values using the column value's string equivalence. Based +on this string value, SQLMap DataMapper will use the resultMap named "Book" or +"Newspaper" as defined in the <submap> elements or it will use the +"parent" resultMap "Document" if neither of the submap values satisfy the comparison. +With these resultMaps, we can implement an object-oriented inheritance mapping +to our database table.

+ +

If you want to use custom logic, you can use the typeHandler attribute of the +<discriminator> element to specify a custom type handler for the discriminator +column.

+ + + + + + + + + + + + +

The value of the typeHandler attribute specifies which of our classes +implements the ITypeHandlerCallback interface. This interface furnishes a +getResult method for coding custom logic to read the column result value and +return a value for the DataMapper to use in its comparison to the resultMap's +defined <submap> values.

+ + +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'); + } +} + + +
\ 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 @@ + +

Inline Parameter Maps

+ +

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.

+ + + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id#, #description#) + + + +

The following example shows how dbTypes can be declared inline.

+ + + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int#, #description, dbType=VarChar#) + + + +

The next example shows how dbTypes and null value replacements can also +be declared inline.

+ + + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int, nullValue=-999999#, #description, dbType=VarChar#) + + + +

A more complete example.

+ + + + 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# + + + +
Note: +Inline parameter maps are handy for small jobs, but when there are a lot of +type descriptors and null value replacements in a complex statement, an +industrial-strength, external parameterMap can be easier. +
+ +

Standard Type Parameters

+

In practice, you will find that many statements take a single parameter, often +an integer or a string. Rather than wrap a single value in another +object, you can use the standard library object (string, integer, et cetera) +as the parameter directly. The following example shows a statement using +a standard type parameter.

+ + + + select * from PRODUCT where PRD_ID = #value# + + + +

Assuming PRD_ID is a numeric type, when a call is made to this Mapped +Statement, a standard integer can be passed in. The #value# parameter +will be replaced with the value of the integer. The name value is simply +a placeholder, you can use another moniker of your choice. Result Maps support +primitive types as results as well.

+ +

For your convenience, the following PHP primitive types are supported.

+ + +

Array Type Parameters

+

You can also pass in a array as a parameter object. This would usually be a an +associative array. The following example shows a using +an array for a parameterClass.

+ + + + select * from PRODUCT + where PRD_CAT_ID = #catId# + and PRD_CODE = #code# + + + +

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 catId +and code. The values referenced by those keys must be of the appropriate +type for the column, just as they would be if passed from a properties object.

+ + +
\ 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 @@ + + +

Installing the DataMapper for PHP

+ +

This section explains how to install, configure, and use the SQLMap DataMapper +with your PHP application.

+ +

There are two steps to using SQLMap DataMapper with your application for the +first time.

+
    +
  1. Setup the distribution
  2. +
  3. Add XML documents
  4. +
+ +

Setup the Distribution

+ +

The official site for SQLMap PHP DataMapper is http://www.pradosoft.com. +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.

+ +

TODO: Add distribution contents

+ +

Add XML file items

+

After unpacking the source distribution, you will need to add two types of XML +files to your Web application, or library project (and Test project if you +have one). These files are:

+ +
+
SqlMap.xml
+
-- A Data Map file that contains your SQL queries. Your project will contain one + or more of these files with names such as Account.xml or Product.xml. +
+ +
SqlMap.config
+
-- + The DataMapper configuration file that is used to specify the locations of your + SqlMap.xml files. It is also used to define other DataMapper + configuration options such as caching. You will need to include one SqlMap.config + file for each data source that your project has. +
+
+ +

As expected, the SqlMap.config file should be (by default) placed where the DataMapper +can find them at runtime.

+ + +
\ 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 @@ + + +

Mapped Statements

+

Mapped Statements can hold any SQL statement and can use Parameter Maps and +Result Maps for input and output. (A stored procedure is a specialized form of +a statement.

+ +

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.

+ + + + select * from Products where Product_Id = [?|#propertyName#] + order by [$simpleDynamic$] + + + +

The [bracketed] parts are optional, and some +options are mutually exclusive. It is perfectly legal to have a Mapped +Statement as simple as shown by the following example.

+ + + + insert into Products (Product_Id, Product_Description) values (1, "Shih Tzu") + + + +

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 +Parameter Maps and +Result Maps since that's where the magic happens.

+ +

Statement Types

+

The <statement> 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.

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Statement ElementAttributeChild ElementsMethods
<statement> + id
+ parameterClass
+ resultClass
+ listClass
+ parameterMap
+ resultMap
+ cacheModel +
None + Insert
+ Update
+ Delete
+ All query methods +
<insert> + id
+ parameterClass
+ parameterMap +
+ <selectKey>
+ <generate> +
+ Insert
+ Update
+ Delete +
<update> + id
+ parameterClass
+ parameterMap
+ extends +
<generate> + Insert
+ Update
+ Delete +
<delete> + id
+ parameterClass
+ parameterMap
+ extends +
<generate> + Insert
+ Update
+ Delete +
<select> + id
+ parameterClass
+ resultClass
+ listClass
+ parameterMap
+ resultMap
+ cacheModel
+ extends +
<generate> + All query methods +
<procedure> + id
+ parameterClass
+ resultClass
+ listClass
+ parameterMap
+ resultMap
+ cacheModel +
None + Insert
+ Update
+ Delete
+ All query methods +
+ +

Stored Procedures

+ +

TODO

+ +
\ 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 @@ + +

Overview

+

+The SQLMap DataMapper framework makes it easier to use a database with a PHP +application. SQLMap DataMapper couples objects with stored procedures or SQL +statements using a XML descriptor. Simplicity is the biggest advantage of the +SQLMap DataMapper over object relational mapping tools. To use SQLMap +DataMapper you rely on your own objects, XML, and SQL. There is little to +learn that you don't already know. With SQLMap DataMapper you have the full +power of both SQL and stored procedures at your fingertips. +

+ +

The SQLMap for PHP is based on iBATIS.NET - DataMapper Application Framework +from http://ibatis.apache.org. +The PHP version support most of the features found in +iBATIS.NET exception the following: +

    +
  1. Dynamic SQL
  2. +
  3. Distributed Transactions
  4. +
+

+ +

What's covered here

+

+This Guide covers the PHP implementations of SQLMap DataMapper. The Java and +.NET implementation offers the same services with some changes in the API. +

+ +

Since SQLMap relies on an XML descriptor to create the mappings, much of the +material applies to both implementations. +

+ +

For installation instructions, see the section called the +SQLMap PHP Developer Guide.

+ +

A Tutorial is also available. We recommend reviewing the Tutorial for your +platform before reading this Guide.

+ +

Support

+ +

Add Forum and Trac.

+ +

Disclaimer

+

SQLMap MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS +DOCUMENT. The names of actual companies and products mentioned herein may be +the trademarks of their respective owners.

+ +
diff --git a/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 @@ + +

Parameter Maps and Inline Parameters

+ +

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.

+ +
Note: +Preceding sections briefly touched on inline parameters, which automatically +map properties to named parameters. Many iBATIS developers prefer this +approach. But others prefer to stick to the standard, anonymous approach to +SQL parameters by using parameter maps. Sometimes people need to retain the +purity of the SQL statements; other times they need the detailed specification +offered by parameter maps due to database or provider-specific information +that needs to be used. +
+ +

Parameter Map

+

A Parameter Map defines an ordered list of values that match up with the +placeholders of a parameterized query statement. While the attributes +specified by the map still need to be in the correct order, each parameter is +named. You can populate the underlying class in any order, and the Parameter +Map ensures each value is passed in the correct order.

+ +

Parameter Maps can be provided as an external element and \emph{inline}. +The following example shows an external Parameter Map.

+ + + + + + + + +

In the above example, the parts in [brackets] are optional. The +parameterMap element only requires the id attribute. +The following example shows a typical <parameterMap>.

+ + + + + + + + + insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?); + + + +
Note: +Parameter Map names are always local to the Data Map definition file where +they are defined. You can refer to a Parameter Map in another Data Map +definition file by prefixing the id of the Parameter Map with the +namespace of the Data Map (set in the <sqlMap> root element). +
+ +

<parameterMap> attributes

+ +

The <parameterMap> element +accepts two attributes: id (required) and extends (optional).

+ +

id attribute

+ +

The required id attribute provides a +unique identifier for the <parameterMap> within this Data Map.

+ +

extends attribute

+

The optional extends attribute can be set to the name of another +parameterMap upon which to base this parameterMap. All properties of +the super parameterMap will be included as part of this +parameterMap, and values from the super parameterMap are set before +any values specified by this parameterMap. The effect is similar to +extending a class.

+ +

<parameter> Elements

+

The <parameterMap> 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.

+ +

property attribute

+

The property attribute of <parameter> 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.)

+ +

direction attribute

+

The direction attribute may be used to indicate a stored procedure +parameter's direction.

+ + + + + + + + + + + + + + + + +
ValueDescription
Inputinput-only
Outputoutput-only
InputOutputbidirectional
+ +

column attribute

+

The column attribute is used to define to the name of a parameter used by +a stored procedure.

+ +

dbType attribute

+

The dbType attribute is used to explicitly specify the database column +type of the parameter to be set by this property. This attribute is normally +only required if the column is nullable. Although, another reason to use the +dbType attribute is to explicitly specify date types. Most SQL databases +have more than one datetime type. Usually, a database has at least three +different types (DATE, DATETIME, TIMESTAMP). In order for the value to map +correctly, you might need to specify the column's dbType.

+ +
Note: +Most providers only need the dbType specified for nullable columns. In +this case, you only need to specify the type for the columns that are +nullable. +
+ +

type attribute

+

The type attribute is used to specify the type of the parameter's +property. This attribute is useful when passing InputOutput and +Output parameters into stored procedures. The framework uses the +specified type to properly handle and set the parameter object's properties +with the procedure's output values after execution.

+ +

nullValue attribute

+ +

The nullValue attribute can be set to any valid value (based on property +type). The nullValue attribute is used to specify an outgoing null value +replacement. What this means is that when the value is detected in the object +property, a NULL will be written to the database (the opposite behavior of an +inbound null value replacement). This allows you to use a magic null number in +your application for types that do not support null values (such as int, +double, float). When these types of properties contain a matching null value +(for example, say, -9999), a NULL will be written to the database instead of +the value.

+ +
Tip: +For round-trip transparency of null values, you must also specify database +columns null value replacements in your Result Map. +
+ +

size attribute

+

The size attribute sets the maximum size of the data within the column.

+ +

precision attribute

+

The precision attribute is used to set the maximum number of digits used +to represent the property value.

+ +

scale attribute

+

The scale attribute sets the number of decimal places used to resolve the +property value.

+ +

typeHandler attribute

+

The typeHandler attribute allows the use of a +Custom Type Handler. 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.

+ +
\ 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 @@ + + +

Result Map Attributes

+

The <resultMap> element accepts three attributes: id (required), +class (optional), and extends (optional).

+ +

id attribute

+

The required id attribute provides a unique identifier for the +<resultMap> within this Data Map.

+ +

class attribute

+

The optional class attribute specifies an object class to use with this +<resultMap>. The full classname must be specified. Any class can be used.

+ +
Note: +As with parameter classes, the result class must be a PHP class object or +array instance. +
+ +

extends attribute

+

The optional extends attribute allows the result map to inherit all of +the properties of the "parent" resultMap that it extends.

+ +

<result> Element attributes

+ +

The <resultMap> element holds one or more <result> child elements +that map SQL result sets to object properties.

+ +

property attribute

+

The property attribute is the name of a property of the result object +that will be returned by the Mapped Statement. The name can be used more than +once depending on the number of times it is needed to populate the results.

+ +

column attribute

+

The column attribute value is the name of the column in the result set +from which the value will be used to populate the property.

+ +

columnIndex attribute

+

The columnIndex attribute value is the index of the column in the +ResultSet from which the value will be used to populate the object property. +This is not likely needed in 99\% of applications and sacrifices +maintainability and readability for speed. Some providers may not realize any +performance benefit, while others will speed up dramatically.

+ +

dbType attribute

+

The dbType attribute is used to explicitly specify the database column +type of the ResultSet column that will be used to populate the object +property. Although Result Maps do not have the same difficulties with null +values, specifying the type can be useful for certain mapping types such as +Date properties. Because an application language has one Date value type and +SQL databases may have many (usually at least 3), specifying the date may +become necessary in some cases to ensure that dates (or other types) are set +correctly. Similarly, String types may be populated by a VarChar, +Char or CLOB, so specifying the type might be needed in those cases +too.

+ +

type attribute

+

The type attribute is used to explicitly specify the property type of the +parameter to be set. If the attribute type is not set and the framework +cannot otherwise determine the type, the type is assumed to be StdObject.

+ +

resultMapping attribute

+

The resultMapping attribute can be set to the name of another +resultMap used to fill the property. If the resultMap is in an other +mapping file, you must specified the fully qualified name as :

+ + +resultMapping="[namespace.sqlMap.]resultMappingId" + +resultMapping="Newspaper" + +resultMapping="LineItem.LineItem" + + +

nullValue attribute

+

The nullValue attribute can be set to any valid value (based on property +type). The nullValue attribute is used to specify an outgoing null value +replacement. What this means is that when the value is detected in the object +property, a NULL will be written to the database (the opposite behavior of an +inbound null value replacement). This allows you to use a "magic" null +number in your application for types that do not support null values (such as +int, double, float). When these types of properties contain a matching null +value (say, -9999), a NULL will be written to the database instead of the +value.

+ +

If your database has a NULLABLE column, but you want your application to +represent NULL with a constant value, you can specify it in the Result Map as +shown in the following example.

+ + + + + + + + + +

In the above example, PRD_SUB_CODE is read as NULL, then the +subCode property will be set to the value of -9999. This allows you to +use a primitive type to represent a NULLABLE column in the database. Remember +that if you want this to work for queries as well as updates/inserts, you must +also specify the nullValue in the Parameter Map. +

+ +

select attribute

+

The select attribute is used to describe a relationship between objects +and to automatically load complex (i.e. user defined) property types. The +value of the statement property must be the name of another mapped statement. +The value of the database column (the column attribute) that is defined in the +same property element as this statement attribute will be passed to the +related mapped statement as the parameter. More information about supported +primitive types and complex property mappings/relationships is discussed later +in this document. The lazyLoad attribute can be specified with the +select.

+ +

lazyLoad attribute

+

Use the lazyLoad attribute with the select attribute to indicate +whether or not the select statement's results should be lazy loaded. This can +provide a performance boost by delaying the loading of the select statement's +results until they are needed/accessed.

+ +

typeHandler attribute

+

The typeHandler attribute allows the use of a +Custom Type Handler. +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. +

+ +
\ 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 @@ + +

Result Maps

+Parameter Maps and +Inline parameters + map object properties to parameters in a database query. Result Maps +finish the job by mapping the result of a database query (a set of columns) to +object properties. Next to Mapped Statements, the Result Map is probably one +of the most commonly used and most important features to understand.

+ +

A Result Map lets you control how data is extracted from the result of a +query, and how the columns are mapped to object properties. A Result Map can +describe the column type, a null value replacement, and complex property +mappings including Collections. The following example shows the structure +of a <resultMap> element.

+ + + + + + + + + + +

In the above example, the [brackets] indicate optional attributes. +The id attribute is required and provides a name for the statement to +reference. The class attribute is also required, and specifies the full +name of a PHP class. This is the class that will be instantiated and populated +based on the result mappings it contains.

+ +

The resultMap can contain any number of property mappings that map object +properties to the columns of a result element. The property mappings are +applied, and the columns are read, in the order that they are defined. +Maintaining the element order ensures consistent results between different +drivers and providers.

+ +
Note: +As with parameter classes, the result class must be a PHP class object or +array instance. +
+ +

Extending resultMaps

+

The optional extends attribute can be set to the name of another +resultMap upon which to base this resultMap. All properties of the +"parent" resultMap will be included as part of this resultMap, and +values from the "parent" resultMap are set before any values specified +by this resultMap. The effect is similar to extending a class.

+ +
Tip: +The "parent" resultMap must be defined in the file before the extending +resultMap. The classes for the parent and child resultMaps need not be +the same, and do not need to be related in any way. +
+ +
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 @@ + + +

Statement-type Element Attributes

+

The six statement-type elements take various attributes. See +Mapped Statements for a +table itemizing which attributes each +element-type accepts. The individual attributes are described in the sections +that follow.

+ +

id attribute

+

+The required id attribute provides a name for this statement, which must +be unique within this <SqlMap>.

+ +

parameterMap attribute

+

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 <parameterMap> and a corresponding +<statement>. + + + + + + + + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?); + + + +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 id property. The second is replaced with +the description property.

+ +

SQLMap also supports named, inline parameters, which most developers seem to +prefer. However, Parameter Maps are useful when the SQL must be kept in a +standard form or when extra information needs to be provided. See +Parameter Maps for futher details.

+ +

parameterClass attribute

+

If a parameterMap attribute is not specified, you may specify a +parameterClass instead and use inline parameters. +The value of the parameterClass attribute +can be any existing PHP class name. The following example shows a +statement using a PHP class named Product in parameterClass +attribute.

+ + + + insert into PRODUCT values (#id#, #description#, #price#) + + + +

resultMap attribute

+

A Result Map lets you control how data is extracted from the result of a +query, and how the columns are mapped to object properties. +The following example shows a <resultMap> element and a +corresponding <statement> element.

+ + + + + + + + + select * from PRODUCT + + + +

In the above example, the result of the SQL query will be mapped to +an instance of the Product class using the "select-product-result" +<resultMap>. The <resultMap> says to +populate the id property +from the PRD_ID column, and to populate the description property +from the PRD_DESCRIPTION column.

+ +
Tip: +In the above example, note that using " select * " is supported. If +you want all the columns, you don't need to map them all individually. (Though +many developers consider it a good practice to always specify the columns +expected.) +
+ +

See Result Maps for futher details.

+ +

resultClass attribute

+

If a resultMap is not specified, you may specify a resultClass +instead. The value of the resultClass attribute can be the name of a PHP +class or primitives like integer, string, or array. The class +specified will be automatically mapped to the columns in the result, based on +the result metadata. The following example shows a <statement> element +with a resultClass attribute.

+ + + + 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# + + + +

In the above example, the Person class has properties including: +Id, FirstName, LastName, BirthDate, +WeightInKilograms, and HeightInMeters. 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 Person object is +instantiated and populated by matching the object property names to the column +names from the query.

+ +

Using SQL aliases to map columns to properties saves defining a +<resultMap> element, but there are limitations. There is no way to +specify the types of the output columns (if needed), there is no way to +automatically load related data such as complex properties.You can overcome +these limitations with an explicit Result Map.

+ +

listClass attribute

+

In addition to providing the ability to return an TList of objects, the +DataMapper supports the use of custom collection: a class that implements +ArrayAccess. The following is an example of a TList (it implements +ArrayAccess) class that can be used with the DataMapper.

+ + +class AccountCollection extends TList +{ + public function addRange($accounts) + { + foreach($accounts as $account) + $this->add($account); + } + + public function copyTo(TList $array) + { + $array->copyFrom($this); + } +} + + +

An ArrayAccess class can be specified for a select statement through the +listClass attribute. The value of the listClass attribute is the +full name of a PHP class that implements ArrayAccess. The statement +should also indicate the resultClass so that the DataMapper knows how to +handle the type of objects in the collection. The resultClass specified +will be automatically mapped to the columns in the result, based on the result +metadata. The following example shows a <statement> element with a +listClass attribute.

+ + + + 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 + + + +

cacheModel attribute

+

If you want to cache the result of a query, you can specify a Cache Model as +part of the <statement> element. The following example shows a +<cacheModel> element and a corresponding <statement>.

+ + + + + + + + + + + + select * from PRODUCT where PRD_CAT_ID = #value# + + + +

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 +Cache Models for futher details

+ +

extends attribute

+

When writing Sql, you often encounter duplicate fragments of SQL. SQLMap +offers a simple yet powerful attribute to reuse them.

+ + + + + + + +
\ 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 @@ + + +

The SQL

+

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.

+ + +

Escaping XML symbols

+

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. + + + + #value# + ]]> + + + +

Auto-Generated Keys

+

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 <selectKey> stanza within an +<insert> element. The following example shows an <insert> +statement for either approach.

+ + + + + + SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL + + insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#) + + + + + insert into PRODUCT (PRD_DESCRIPTION) + values (#description#) + + select @@IDENTITY as value + + + + + + insert into PRODUCT (PRD_DESCRIPTION) + values (#description#) + + select LAST_INSERT_ID() as value + + + + +

<generate> tag

+

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 <generate> tag can be used to +create simple SQL statements automatically, based on a <parameterMap> +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.

+ +
Important: +The intended use of the <generate> 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 <generate> +tag is not a replacement for any of those. When the <generate> tag does +not suit your needs, use a conventional statement instead. +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select @@IDENTITY as value + + + + + +

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.

+ +
Note: +The SQL is generated when the DataMapper instance is built and can be cached +afterward, so there is no performance impact at execution time. +
+ +

The generate tag supports two attributes.

+ + + + + + + + + + + + + + +
AttributeDescriptionRequired
tablespecifies the table name to use in the SQL statementyes
byspecifies the columns to use in a WHERE clauseno
+ +
\ 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 @@ + + +

Working With Data Maps

+ +

If you want to know how to configure and install SQLMap, +see the Installation and +Configuration. +But if you want to know how SQLMap really +works, continue from here.

+ +

The Data Map definition file is where the interesting stuff happens. Here, you +define how your application interacts with your database. As mentioned, the +Data Map definition is an XML descriptor file. By using a service routine +provided by SQLMap, the XML descriptors are rendered into a client object (or +Mapper). To access your Data Maps, your application calls the client object +and passes in the name of the statement you need.

+ +

The real work of using SQLMap is not so much in the application code, but in +the XML descriptors that SQLMap renders. Instead of monkeying with application +source code, you monkey with XML descriptors instead. The benefit is that the +XML descriptors are much better suited to the task of mapping your object +properties to database entities. At least, that's our own experience with our +own applications. Of course, your mileage may vary.

+ +

What's in a Data Map definition file, anyway?

+ +

If you read the Tutorial, you've already +seen some simple Data Map examples like the one below.

+ + + + + + INSERT INTO [LinesItem] + (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice) + VALUES + (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#) + + + + +

This map takes some properties from a LineItem instance and merges the +values into the SQL statement. The value-add is that our SQL in separated from +our program code, and we can pass our LineItem instance directly to a +library method:

+ + +TMapper::instance()->insert("InsertLineItem",$lineItem); + +

No fuss, no muss.

+ +
Info: +A Quick Glance at Inline Parameters +

+Say we have a mapped statement element that looks like this:

+ + + insert into Products (Product_Id, Product_Description) + values (#Id#, #Description#); + + + +

The inline parameters here are #Id# and #Description#. Let's +also say that we have an object with the properties Id and +Description. If we set the object properties to 5 and "dog", +respectively, and passed the object to the mapped statement, we'd end up with +a runtime query that looked like this: + +insert into Products (Product_Id, Product_Description) values (5, 'dog'); + +See inline parameters for further details. +

+ +

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 +<parameterMap> and a <resultMap> to keep our SQL pristine. +

+ + + + + + + + + + + + + + + + + + + + + + + + +

In the above example, <parameterMap> maps the SQL "?" to the +product Id property. The <resultMap> maps the columns to our object +properties. The <cacheModel> keeps the result of the last one thousand of +these queries in active memory for up to 24 hours.

+ +

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.)

+ +

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.

+ +

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.

+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Manual/diagram.png b/demos/sqlmap/protected/pages/Manual/diagram.png new file mode 100644 index 00000000..0a0ca73d Binary files /dev/null and b/demos/sqlmap/protected/pages/Manual/diagram.png differ diff --git a/demos/sqlmap/protected/pages/Sample/Home.page b/demos/sqlmap/protected/pages/Sample/Home.page new file mode 100644 index 00000000..6d059c7e --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/Home.page @@ -0,0 +1,5 @@ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/ProductList.page b/demos/sqlmap/protected/pages/Sample/ProductList.page new file mode 100644 index 00000000..904d411d --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/ProductList.page @@ -0,0 +1,9 @@ +

Database Examples

+ + +
+ Code: <%# $this->DataItem['productid'] %> + Category: <%# $this->DataItem['category'] %> +
+
+
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/ProductList.php b/demos/sqlmap/protected/pages/Sample/ProductList.php new file mode 100644 index 00000000..a35c40ea --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/ProductList.php @@ -0,0 +1,18 @@ +IsPostBack) + { + $sqlmap = $this->Application->Modules['petshop-sqlmap']; + $products = $sqlmap->queryForList('SelectAllProducts'); + $this->productList->setDataSource($products); + $this->productList->dataBind(); + } + } +} + +?> \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/crud1.page b/demos/sqlmap/protected/pages/Sample/crud1.page new file mode 100644 index 00000000..ce467ffa --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/crud1.page @@ -0,0 +1,19 @@ + + + + + Person + + + + + +

Person List

+ + + +
+ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/crud1.php b/demos/sqlmap/protected/pages/Sample/crud1.php new file mode 100644 index 00000000..40733dc9 --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/crud1.php @@ -0,0 +1,21 @@ +Application->Modules['person-sample']; + $this->personList->DataSource = $sqlmap->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } +} + +?> \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/crud2.page b/demos/sqlmap/protected/pages/Sample/crud2.page new file mode 100644 index 00000000..4d34e873 --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/crud2.page @@ -0,0 +1,36 @@ + + + + + Person + + + + + +

Person List

+ + + + + + + + + + +
+ + \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Sample/crud2.php b/demos/sqlmap/protected/pages/Sample/crud2.php new file mode 100644 index 00000000..46fe3893 --- /dev/null +++ b/demos/sqlmap/protected/pages/Sample/crud2.php @@ -0,0 +1,79 @@ +Application->Modules['person-sample']; + } + + private function loadData() + { + $this->personList->DataSource = + $this->sqlmap()->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } + + protected function editPerson($sender,$param) + { + $this->personList->EditItemIndex=$param->Item->ItemIndex; + $this->loadData(); + } + + protected function deletePerson($sender, $param) + { + $id = $this->getKey($sender, $param); + + $this->sqlmap()->update("Delete", $id); + $this->loadData(); + } + + protected function updatePerson($sender, $param) + { + $person = new Person(); + $person->FirstName = $this->getText($param, 0); + $person->LastName = $this->getText($param, 1); + $person->HeightInMeters = $this->getText($param, 2); + $person->WeightInKilograms = $this->getText($param, 3); + $person->ID = $this->getKey($sender, $param); + + $this->sqlmap()->update("Update", $person); + $this->refreshList($sender, $param); + } + + protected function addNewPerson($sender, $param) + { + $person = new Person; + $person->FirstName = "-- New Person --"; + $this->sqlmap()->insert("Insert", $person); + + $this->loadData();; + } + + protected function refreshList($sender, $param) + { + $this->personList->EditItemIndex=-1; + $this->loadData(); + } + + private function getText($param, $index) + { + $item = $param->Item; + return $item->Cells[$index]->Controls[0]->Text; + } + + private function getKey($sender, $param) + { + return $sender->DataKeys[$param->Item->DataSourceIndex]; + } +} + +?> \ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/TestAgain.page b/demos/sqlmap/protected/pages/Tutorial/TestAgain.page new file mode 100644 index 00000000..4adac73b --- /dev/null +++ b/demos/sqlmap/protected/pages/Tutorial/TestAgain.page @@ -0,0 +1,214 @@ + +

Test, test, again ...

+

Of course, tweaking the Person List display is not going to be the end of it. +Clients always want more, and now ours wants to edit, add, or delete records. +Let's write some tests for these new tasks, as shown in the following.

+ + +function testPersonUpdate() +{ + $expect = "wei"; + $edited = "Nah"; + + //get it; + $person = TMapper::instance()->queryForObject("Select", 1); + + //test it + $this->assertNotNull($person); + $this->assertEqual($expect, $person->FirstName); + + //change it + $person->FirstName = $edited; + TMapper::instance()->update("Update", $person); + + //get it again + $person = TMapper::instance()->queryForObject("Select", 1); + + //test it + $this->assertEqual($edited, $person->FirstName); + + //change it back + $person->FirstName = $expect; + TMapper::instance()->update("Update", $person); +} + +function testPersonDelete() +{ + //insert it + $person = new Person; + $person->ID = -1; + TMapper::instance()->insert("Insert", $person); + + //delte it + $count = TMapper::instance()->delete("Delete", -1); + $this->assertEqual(1, $count); +} + + +

Not the best tests ever written, but for now, they will do :)

+ +

To make the new tests work, we'll need some new mapping statements. +The following sample shows the complete mapper document that we've called +personHelper.xml.

+ + + + + + + + + insert into PERSON + (PER_ID, PER_FIRST_NAME, PER_LAST_NAME, + PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M) + values + (#ID#, #FirstName#, #LastName#, + #BirthDate#, #WeightInKilograms#, #HeightInMeters#) + + + + update PERSON set + PER_FIRST_NAME = #FirstName#, + PER_LAST_NAME = #LastName#, + PER_BIRTH_DATE = #BirthDate#, + PER_WEIGHT_KG = #WeightInKilograms#, + PER_HEIGHT_M = #HeightInMeters# + where PER_ID = #ID# + + + + delete from PERSON + where PER_ID = #value# + + + + +

Well, waddya know, if run our tests now, we are favored with a green bar!. It +all works!

+ +
Note: +Though, of course, things usually do not work perfectly the first time! We +have to fix this and that, and try, try, again. But SimpleTest makes trying +again quick and easy. You can changes to the XML mapping documents and rerun +the tests! No muss, no fuss. +
+ +

Turning back to our Prado page, we can revamp the TDataGrid to allow in-place +editing and deleting. To add records, we provide a button after the grid that +inserts a blank person for client to edit. The page code is shown as: + + + <com:TDataGrid id="personList" + DataKeyField="ID" + AutoGenerateColumns="False" + OnEditCommand="editPerson" + OnUpdateCommand="updatePerson" + OnCancelCommand="refreshList" + OnDeleteCommand="deletePerson"> + <com:TBoundColumn DataField="FirstName" HeaderText="First Name" /> + <com:TBoundColumn DataField="LastName" HeaderText="Last Name" /> + <com:TBoundColumn DataField="HeightInMeters" HeaderText="Height" /> + <com:TBoundColumn DataField="WeightInKilograms" HeaderText="Weight" /> + <com:TEditCommandColumn + HeaderText="Edit" + UpdateText="Save" /> + <com:TButtonColumn + HeaderText="Delete" + Text="Delete" + CommandName="delete"/> + </com:TDataGrid> + <com:TButton Text="Add" OnClick="addNewPerson" /> + + +

The following sample shows the corresponding methods from page PHP class.

+ + + private function sqlmap() + { + return $this->Application->getModule('SQLMap')->getClient(); + } + + private function loadData() + { + $this->personList->DataSource = + $this->sqlmap()->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } + + protected function editPerson($sender,$param) + { + $this->personList->EditItemIndex=$param->Item->ItemIndex; + $this->loadData(); + } + + protected function deletePerson($sender, $param) + { + $id = $this->getKey($sender, $param); + $this->sqlmap()->update("Delete", $id); + $this->loadData(); + } + + protected function updatePerson($sender, $param) + { + $person = new Person(); + $person->FirstName = $this->getText($param, 0); + $person->LastName = $this->getText($param, 1); + $person->HeightInMeters = $this->getText($param, 2); + $person->WeightInKilograms = $this->getText($param, 3); + $person->ID = $this->getKey($sender, $param); + $this->sqlmap()->update("Update", $person); + $this->refreshList($sender, $param); + } + + protected function addNewPerson($sender, $param) + { + $person = new Person; + $person->FirstName = "-- New Person --"; + $this->sqlmap()->insert("Insert", $person); + $this->loadData();; + } + + protected function refreshList($sender, $param) + { + $this->personList->EditItemIndex=-1; + $this->loadData(); + } + + private function getText($param, $index) + { + $item = $param->Item; + return $item->Cells[$index]->Controls[0]->Text; + } + + private function getKey($sender, $param) + { + return $sender->DataKeys[$param->Item->DataSourceIndex]; + } + + +

OK, we are CRUD complete! There's more we could do here. In particular, we +should add validation methods to prevent client from entering alphabetic +characters where only numbers can live. But, that's a different Prado +tutorial, and this is an SQLMap DataMapper tutorial.

+ + class="figure" /> +
Figure 4: Person List CRUD
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/TestFirst.page b/demos/sqlmap/protected/pages/Tutorial/TestFirst.page new file mode 100644 index 00000000..80a155cb --- /dev/null +++ b/demos/sqlmap/protected/pages/Tutorial/TestFirst.page @@ -0,0 +1,238 @@ + +

Test First!

+ +

Let's say that our most important client has a database and one of the tables +in the database is a list of people. Our client tells us:

+ +

"We would like to use a web application to display the people in this table +and to add, edit, and delete individual records."

+ +

Not a complicated story, but it will cover the CRUD most developers want to +learn first. :) Let's start with the people table that the client mentioned. +Since we're keeping it simple, we'll say it's a table in an Access database. +The table definition is shown as:

+ + +Name Type Size +PER_ID Long Integer 4 +PER_FIRST_NAME Text 40 +PER_LAST_NAME Text 40 +PER_BIRTH_DATE Date/Time 8 +PER_WEIGHT_KG Double 8 +PER_HEIGHT_M Double 8 + + +
Tip: + This example is bundled with a SQLite database file "Data/test.db" + that contains the Person table and some data, ready to use. +
+ +

The first thing our story says is that client would like to display a list of +people. The following example shows our test for that.

+ + +<?php +class PersonTest extends UnitTestCase +{ + function testPersonList() + { + //try it + $people = TMapper::instance()->queryForList("SelectAll"); + + //test it + $this->assertNotNull($people, "Person list is not returned"); + $this->assertTrue($people->getCount() > 0, "Person list is empty"); + $person = $people[0]; + $this->assertNotNull($person, "Person not returned"); + } +} +?> + + +

Well, the example sure looks easy enough! We ask a method to "select all", and +it returns a list of person objects. But, what code do we need to write to +pass this test?

+ +
Note: + Save the PersonTest.php into a tests directory. + The unit tests are written for the SimpleTest Unit Testing framework. +
+ +

Now, to setup the testing framework, suppose you have the SimpleTest +framework installed. Then we need to create an entry file to run the tests. +See the SimpleTest documentation for further details on setting up tests.

+ + +<?php +require_once('../tests/simpletest/unit_tester.php'); +require_once('../tests/simpletest/reporter.php'); +require_once('../SQLMap/TMapper.php'); +require_once('Models/Person.php'); + +//supress strict warnings from Adodb. +error_reporting(E_ALL); + +$test = new GroupTest('All tests'); +$test->addTestFile('Tests/PersonTest.php'); $test->run(new HtmlReporter()); +?> + + +

To run the tests, point your browser to the "run_test.php" script file +served from your web server.

+ +

Let's see. The test uses a list of person objects. We could start with a blank +object, just to satisfy the test, and add the display properties later. But +let's be naughty and skip a step. Our fully-formed person object is shown in +the following example

+ + +<?php +class Person +{ + public $ID = -1; + public $FirstName; + public $LastName; + public $WeightInKilograms = 0.0; + public $HeightInMeters = 0.0; + + private $_birthDate; + + //setters and getter for BirthDate + public function getBirthDate() + { + return $this->_birthDate; + } + + public function setBirthDate($value) + { + $this->_birthDate = $value; + } +} +?> + + +

OK, that was fun! The $this->assertXXX(...) methods are built into +UnitTestCase class. So to run the unit test example, we just need the +TMapper object and queryForList method. Wonderfully, the SQLMap +DataMapper framework has a TMapperclass built into it that will work just +fine for for us to use in this tutorial, so we don't need to write that +either.

+ +

When the TMapper->instance() method is called, an instance of the SQLMap +TSqlMapper class is returned that has various methods available such as +queryForList. In this example, the SQLMap TSqlMapper->queryForList() +method executes our SQL statement (or stored procedure) and returns the result +as a list. Each row in the result becomes an entry in the list. Along with +queryForList(), there are also delete(), insert(), +queryForObject(), queryForPagedList() and a few other methods in the +SQLMap API. + +

Looking at unit test example, we see that the queryForList() method +takes the name of the statement we want to run. OK. Easy enough. But where +does SQLMap get the "SelectAll" statement? Some systems try to generate SQL +statements for you, but SQLMap specializes in data mapping, not code +generation. It's our job (or the job of our database administrator) to craft +the SQL or provide a stored procedure. We then describe the statement in an +XML element, like the one shown the following where +we use XML elements to map a database statement to an application object. + + + + + + + + +

The SQLMap mapping documents can hold several sets of related elements, like +those shown in the unit test case example. We can also have as many mapping +documents as we need to help organize our code. Additionally, having multiple +mapping documents is handy when several developers are working on the project +at once.

+ +

So, the framework gets the SQL code for the query from the mapping, and plugs +it into a prepared statement. But, how does SQLMap know where to find the +table's datasource?

+ +

Surprise! More XML! You can define a configuration file for each datasource +your application uses. The following code shows a configuration file named "sqlmap.config" for +our SQLite database.

+ + + + + + + + + + + + + +

The <provider> specifies the database provider class, in this case +TAdodbProvider using the Adodb library. The <datasource> tag +specifies the database connection details. In this case, for an SQLite +database, we just need the driver name, and the host that points to the actual +SQLite database file.

+ +

The last part of the configuration file ("sqlMaps") is where we list our +mapping documents, like the one shown back in the previous code sample. We can +list as many documents as we need here, and they will all be read when the +configuration is parsed.

+ +

OK, so how does the configuration get parsed?

+ +

Look back at the unit test case example. The heart of the code is the call to the +"TMapper" object (under the remark "try it"). The TMapper object +is a singleton that handles the instantiation and configuration of an SQLMap +TSqlMapper object, which provides a facade to the SQLMap DataMapper +framework API.

+ +

The first time that the TMapper is called, it reads in the +sqlmap.config file and associated mapping documents to create an instance +of the TSqlMapper class. On subsequent calls, it reuses the +TSqlMapper object so that the configuration is not re-read.

+ +

The framework comes bundled with a default TMapper class for you to use +immediately to get access to the SQLMap client TSqlMapper object. If you want to use a +different name other than sqlmap.config at the default location for the +configuration file, or need to use more than one database and have one +TSqlMapper per database, you can also write your own class to mimic the role of +the Mapper class view by copying and modifying the standard version.

+ +
Tip: + You can also call TMapper::configure('/path/to/your/sqlmap.config') + to configure the TMapper for a specific configuration file. +
+ +

If we put this all together into a solution, we can "green bar" our test. At +this point you should have the following files.

+ +Data/person.xml % Mapping file. +Data/test.db % SQLite database file. + +Models/Person.php % Person class file. + +Tests/PersonTest.php % Unit test case for Person mapping. + +run_tests.php % Unit test entry point. +sqlmap.config % SQLMap configuration file. + + +

Run the tests by pointing your browser URL to the "run_tests.php" server +file.

+ + class="figure" /> +
Figure 2: Green Bar!
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/TestSecond.page b/demos/sqlmap/protected/pages/Tutorial/TestSecond.page new file mode 100644 index 00000000..706b5220 --- /dev/null +++ b/demos/sqlmap/protected/pages/Tutorial/TestSecond.page @@ -0,0 +1,116 @@ + + +

Playtest second!

+

Now that we have a passing test, we want to display some results as web pages. +The following examples utilize the Prado framework to display and manipulate +the database through SQLMap. Since SQLMap framework and Prado framework solve +different problems, they are both fairly independent, they can be used +together or separately.

+ +

SQLMap and Prado

+

To setup Prado, we need to create the follow files and directory structure +under our example/WebView directory.

+ +assets/ % application public assets + +protected/pages/Home.page % default page +protected/pages/Home.php % default page class +protected/runtime/ % run time data + +protected/application.xml % application configuration + +index.php % application entry point + + +

The application.xml and assets directory are not necessary but we +will make use of them later. The application.xml is used to define some +directory aliases and override the data source definitions in the +sqlmap.config. This is because SQLite database files are defined +relatively, otherwise we don't need to override the data source definitions. +The example application.xml is shown below, defining path aliases and override SQLite database +location.

+ + + + + + + + + + + + + + + + + + + +

The entry point to a Prado application in this example is index.php +and generally contains the following code.

+ + +<?php +error_reporting(E_ALL); +require_once('/path/to/prado/framework/prado.php'); +$application=new TApplication; +$application->run(); +?> + + +

Now we are ready to setup a page to display our list of people. +The following sample shows the Prado code for our display page. The key +piece is the TDataGrid. We save the file as Home.page.

+ + + + + + Person + + +<com:TForm> +

Person List

+ <com:TDataGrid id="personList"> + <com:TBoundColumn DataField="BirthDate" + HeaderText="Birth Date"/> + </com:TDataGrid> +</com:TForm> + + +
+ +

Of course, we still need to populate that TDataGrid. The following code +shows the PHP for Home.php. The operative method is loadData(). +The rest is supporting code.

+ + +<?php +Prado::using('Example.Models.Person'); +class Home extends TPage +{ + private function loadData() + { + $sqlmap = $this->Application->getModule('SQLMap')->getClient(); + $this->personList->DataSource = $sqlmap->queryForList('SelectAll'); + $this->personList->dataBind(); + } + + public function onLoad($param) + { + if(!$this->IsPostBack) + $this->loadData(); + } +} +?> + + +

If we run this now, we'll get a list like the one shown the figure below.

+ class="figure" /> +
Figure 3: A quick-and-dirty Person List
+ +
\ No newline at end of file diff --git a/demos/sqlmap/protected/pages/Tutorial/example1.png b/demos/sqlmap/protected/pages/Tutorial/example1.png new file mode 100644 index 00000000..b5241de6 Binary files /dev/null and b/demos/sqlmap/protected/pages/Tutorial/example1.png differ diff --git a/demos/sqlmap/protected/pages/Tutorial/grid1.png b/demos/sqlmap/protected/pages/Tutorial/grid1.png new file mode 100644 index 00000000..845b9581 Binary files /dev/null and b/demos/sqlmap/protected/pages/Tutorial/grid1.png differ diff --git a/demos/sqlmap/protected/pages/Tutorial/grid2.png b/demos/sqlmap/protected/pages/Tutorial/grid2.png new file mode 100644 index 00000000..dcafc33d Binary files /dev/null and b/demos/sqlmap/protected/pages/Tutorial/grid2.png differ diff --git a/demos/sqlmap/protected/pages/config.xml b/demos/sqlmap/protected/pages/config.xml new file mode 100644 index 00000000..e0850c2c --- /dev/null +++ b/demos/sqlmap/protected/pages/config.xml @@ -0,0 +1,8 @@ + + + + + + + + \ No newline at end of file -- cgit v1.2.3