+
+
+
+
+
+
\ No newline at end of file
diff --git a/demos/sqlmap/protected/controls/TopicList.php b/demos/sqlmap/protected/controls/TopicList.php
new file mode 100644
index 00000000..ce827cc0
--- /dev/null
+++ b/demos/sqlmap/protected/controls/TopicList.php
@@ -0,0 +1,8 @@
+
\ No newline at end of file
diff --git a/demos/sqlmap/protected/controls/TopicList.tpl b/demos/sqlmap/protected/controls/TopicList.tpl
new file mode 100644
index 00000000..6c7a3e9e
--- /dev/null
+++ b/demos/sqlmap/protected/controls/TopicList.tpl
@@ -0,0 +1,66 @@
+
\ No newline at end of file
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.
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
+
+
Have complete control over your database implementation.
+
Do not have a Database Administrator or SQL guru on the team.
+
Need to model the problem domain outside the database as an object graph.
+
+Likewise, the best time to use a Data Mapper, like SQLMap, is when:
+
+
You do not have complete control over the database implementation, or want to
+continue to access a legacy database as it is being refactored.
+
You have database administrators or SQL gurus on the team.
+
The database is being used to model the problem domain, and the application's
+primary role is to help the client use the database model.
+
+
+
+
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:
+
+
build a TSqlMapper instance from a configuration file or cache
+
execute an update query (including insert and delete)
+
execute a select query for a single object
+
execute a select query for a list of objects
+
+
+
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.
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:
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 @@
+
+
+
+ $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.
+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.
+
+
+
+
+
Attribute
+
Description
+
+
+
resource
+
Specify 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.
+
+
+
+
+
Attribute
+
Description
+
+
+
resource
+
Specify the properties file to be loaded from the directory relative
+ to the current file.
+ Example: resource="properties.config"
+
+
+
+
key
+
Defines 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:
+
+
+
+
Attribute
+
Description
+
+
+
type
+
Refers to the name of the type to handle.
+ Example: type="date"
+
+
+
+
dbType
+
Indicates 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.
+
+
+
+
+
Attribute
+
Description
+
+
+
class
+
The 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.
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Attribute
+
Description
+
+
+
connectionString
+
Data Source Name (DSN) connection string.
+ Example: connectionString="mysql://root:pwd@localhost/mydb"
+
+
+
+
driver
+
Database driver name (mysql, sqlite, etc.).
+ Example: driver="mysql"
+
+
+
+
host
+
DB host name/IP (and port number) in the format host[:port].
+ Example: host="localhost"
+
+
+
+
username
+
Database connection username.
+
+
+
password
+
Database connection password.
+
+
+
database
+
Database 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.
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.
+
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.
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.
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.
+
+
string
+
float or double
+
integer or int
+
bool or boolean
+
+
+
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.
+
+
Setup the distribution
+
Add XML documents
+
+
+
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.
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.
+
+
\ 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:
+
+
Dynamic SQL
+
Distributed Transactions
+
+
+
+
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.
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>.
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.
+
+
+
+
Value
Description
+
+
Input
+
input-only
+
+
+
Output
+
output-only
+
+
+
InputOutput
+
bidirectional
+
+
+
+
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 :
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.
+
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.
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.)
+
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>.
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.
+
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.
+
+
+
+
Attribute
Description
Required
+
+
table
+
specifies the table name to use in the SQL statement
+
yes
+
+
+
by
+
specifies the columns to use in a WHERE clause
+
no
+
+
+
+
\ 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.
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:
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 @@
+
+
+
\ 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.
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.
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?
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.
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.
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.
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.
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