From 4b78404c20490a615459267426ce9e6737bf4485 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Jul 2006 09:20:45 +0000 Subject: Moving files. --- demos/sqlmap-docs/protected/pages/Home.page | 1 - .../protected/pages/Manual/BigPicture.page | 151 ------------- .../protected/pages/Manual/BuildingTSqlMapper.page | 73 ------ .../protected/pages/Manual/CacheModels.page | 113 ---------- .../protected/pages/Manual/CodingExamples.page | 55 ----- .../protected/pages/Manual/ComplexProperties.page | 247 -------------------- .../protected/pages/Manual/CompositeKeys.page | 53 ----- .../pages/Manual/ConfigurationElements.page | 249 --------------------- .../protected/pages/Manual/Configuring.page | 47 ---- .../protected/pages/Manual/CustomTypeHandlers.page | 114 ---------- .../protected/pages/Manual/DataMapperAPI.page | 162 -------------- .../protected/pages/Manual/DynamicSQL.page | 5 - .../protected/pages/Manual/ImplicitResultMaps.page | 96 -------- .../protected/pages/Manual/InheritanceMapping.page | 150 ------------- .../pages/Manual/InlineParameterMaps.page | 102 --------- .../protected/pages/Manual/Installing.page | 51 ----- .../protected/pages/Manual/MappedStatements.page | 173 -------------- .../protected/pages/Manual/Overview.page | 48 ---- .../protected/pages/Manual/ParameterMap.page | 184 --------------- .../pages/Manual/ResultMapAttributes.page | 132 ----------- .../protected/pages/Manual/ResultMaps.page | 67 ------ .../pages/Manual/StatementElementAttributes.page | 216 ------------------ .../sqlmap-docs/protected/pages/Manual/TheSQL.page | 157 ------------- .../pages/Manual/WorkingWithDataMaps.page | 129 ----------- .../sqlmap-docs/protected/pages/Manual/diagram.png | Bin 30320 -> 0 bytes .../protected/pages/Tutorial/TestAgain.page | 214 ------------------ .../protected/pages/Tutorial/TestFirst.page | 238 -------------------- .../protected/pages/Tutorial/TestSecond.page | 116 ---------- .../protected/pages/Tutorial/example1.png | Bin 236887 -> 0 bytes .../sqlmap-docs/protected/pages/Tutorial/grid1.png | Bin 275250 -> 0 bytes .../sqlmap-docs/protected/pages/Tutorial/grid2.png | Bin 218210 -> 0 bytes demos/sqlmap-docs/protected/pages/config.xml | 8 - 32 files changed, 3351 deletions(-) delete mode 100644 demos/sqlmap-docs/protected/pages/Home.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/BigPicture.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/BuildingTSqlMapper.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/CacheModels.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/CodingExamples.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/CompositeKeys.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ConfigurationElements.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/Configuring.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/CustomTypeHandlers.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/DataMapperAPI.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/DynamicSQL.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ImplicitResultMaps.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/InheritanceMapping.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/InlineParameterMaps.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/Installing.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/MappedStatements.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/Overview.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ParameterMap.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ResultMapAttributes.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ResultMaps.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/StatementElementAttributes.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/TheSQL.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/WorkingWithDataMaps.page delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/diagram.png delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/TestAgain.page delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/TestFirst.page delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/TestSecond.page delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/example1.png delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/grid1.png delete mode 100644 demos/sqlmap-docs/protected/pages/Tutorial/grid2.png delete mode 100644 demos/sqlmap-docs/protected/pages/config.xml (limited to 'demos/sqlmap-docs/protected/pages') diff --git a/demos/sqlmap-docs/protected/pages/Home.page b/demos/sqlmap-docs/protected/pages/Home.page deleted file mode 100644 index ff226d4e..00000000 --- a/demos/sqlmap-docs/protected/pages/Home.page +++ /dev/null @@ -1 +0,0 @@ -
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.
- -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.
- -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"/> - - -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.
- -If your database is generating the primary keys, the generated key can be -returned from the same method call, like this:
- -The following example shows an XML descriptor for "InsertLineItem".
-
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:
-
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.
- -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 -
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!
- - -The SQLMap DataMapper API provides four core functions:
-The API also provides support for retrieving paginated lists and managing -transactions.
- -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.
- -To obtain the TSqlMapper instance, first configure the mapper once.
-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.
- -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.
- -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:
- -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.
- -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.
- -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.
- -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.
- -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.
- -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. "===").
-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.):
- -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. -
- -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.
- -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. "===").
-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:
- -Optionally you can just specify the column names as long as they're in the -same order as the parameters. For example:
-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.
-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. -
- -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:
- -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 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 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 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. | -
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. - -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.
- -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. - -
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.
-
-
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.
- -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.
- -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.
- -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
- -The TSqlMapper instance acts as a facade to provide access the rest of -the DataMapper framework. The DataMapper API methods are shown below.
- -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.
- -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. -
- -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.
- -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.
- -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.
- -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.
- -Using transactions example.
-Dynamic SQL is not implemented yet.
- -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.
- -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.
- -Of course, these techniques will not work if you need to specify a column -type, a null value, or any other property attributes.
- -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.
- -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.
- -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.
- -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.
- -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.
- -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.
- -The following example shows how dbTypes can be declared inline.
- -The next example shows how dbTypes and null value replacements can also -be declared inline.
- -A more complete example.
- -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.
- -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.
-You can also pass in a array as a parameter object. This would usually be a an
-associative array. The following example shows a
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.
- - -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.
-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
- -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:
- -As expected, the SqlMap.config file should be (by default) placed where the DataMapper -can find them at runtime.
- - -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.
- -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.
- -The <statement> element is a general "catch all" element that can be -used for any type of SQL statement. Generally it is a good idea to use one of -the more specific statement-type elements. The more specific elements provided -better error-checking and even more functionality. (For example, the insert -statement can return a database-generated key.) The following table -summarizes the statement-type elements and their supported attributes and -features.
- - -Statement Element | -Attribute | -Child Elements | -Methods | -
---|---|---|---|
<statement> | -
- id - parameterClass - resultClass - listClass - parameterMap - resultMap - cacheModel - |
- None | -
- Insert - Update - Delete - All query methods - |
-
<insert> | -
- id - parameterClass - parameterMap - |
-
- <selectKey> - <generate> - |
-
- Insert - Update - Delete - |
-
<update> | -
- id - parameterClass - parameterMap - extends - |
- <generate> | -
- Insert - Update - Delete - |
-
<delete> | -
- id - parameterClass - parameterMap - extends - |
- <generate> | -
- Insert - Update - Delete - |
-
<select> | -
- id - parameterClass - resultClass - listClass - parameterMap - resultMap - cacheModel - extends - |
- <generate> | -- All query methods - | -
<procedure> | -
- id - parameterClass - resultClass - listClass - parameterMap - resultMap - cacheModel - |
- None | -
- Insert - Update - Delete - All query methods - |
-
TODO
- --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: -
-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.
- -Add Forum and Trac.
- -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.
- -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>.
- -The <parameterMap> element -accepts two attributes: id (required) and extends (optional).
- -The required id attribute provides a -unique identifier for the <parameterMap> within this Data Map.
- -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.
- -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.
- -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.)
- -The direction attribute may be used to indicate a stored procedure -parameter's direction.
- - -Value | Description |
---|---|
Input | -input-only | -
Output | -output-only | -
InputOutput | -bidirectional | -
The column attribute is used to define to the name of a parameter used by -a stored procedure.
- -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.
- -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.
- -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.
- -The size attribute sets the maximum size of the data within the column.
- -The precision attribute is used to set the maximum number of digits used -to represent the property value.
- -The scale attribute sets the number of decimal places used to resolve the -property value.
- -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.
- -The <resultMap> element accepts three attributes: id (required), -class (optional), and extends (optional).
- -The required id attribute provides a unique identifier for the -<resultMap> within this Data Map.
- -The optional class attribute specifies an object class to use with this -<resultMap>. The full classname must be specified. Any class can be used.
- -The optional extends attribute allows the result map to inherit all of -the properties of the "parent" resultMap that it extends.
- -The <resultMap> element holds one or more <result> child elements -that map SQL result sets to object properties.
- -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.
- -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.
- -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.
- -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.
- -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.
- -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. -
- -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.
- -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.
- -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. -
- -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.
- -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.
- -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.
- --The required id attribute provides a name for this statement, which must -be unique within this <SqlMap>.
- -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>.
-
-
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.
- -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.
- -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.
- -See Result Maps for futher details.
- -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.
- -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.
- -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.
- -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.
- -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
- -When writing Sql, you often encounter duplicate fragments of SQL. SQLMap -offers a simple yet powerful attribute to reuse them.
- -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.
- - -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.
-
-
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.
- -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.
- -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.
- -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 | -
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.
- -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:
- -No fuss, no muss.
- --Say we have a mapped statement element that looks like this:
-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:
-
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.
- -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.
- -Well, waddya know, if run our tests now, we are favored with a green bar!. It -all works!
- -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:
-
-
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" /> - - -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:
- -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.
- -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
- -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.
- -If we put this all together into a solution, we can "green bar" our test. At -this point you should have the following files.
-Run the tests by pointing your browser URL to the "run_tests.php" server -file.
- - class="figure" /> - - -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.
- -To setup Prado, we need to create the follow files and directory structure -under our example/WebView directory.
-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" /> - - -