diff options
41 files changed, 1336 insertions, 618 deletions
diff --git a/.gitattributes b/.gitattributes index d8e0942d..e9fe5dc6 100644 --- a/.gitattributes +++ b/.gitattributes @@ -1072,7 +1072,12 @@ demos/quickstart/protected/pages/Controls/TextHighlighter.page -text demos/quickstart/protected/pages/Controls/Validation.page -text demos/quickstart/protected/pages/Controls/Wizard.page -text demos/quickstart/protected/pages/Controls/wizard.gif -text +demos/quickstart/protected/pages/Database/ActiveRecord.page -text demos/quickstart/protected/pages/Database/DAO.page -text +demos/quickstart/protected/pages/Database/SqlMap.page -text +demos/quickstart/protected/pages/Database/diagram.png -text +demos/quickstart/protected/pages/Database/object_states.png -text +demos/quickstart/protected/pages/Database/sqlmap_active_record.png -text demos/quickstart/protected/pages/Fundamentals/Applications.page -text demos/quickstart/protected/pages/Fundamentals/Architecture.page -text demos/quickstart/protected/pages/Fundamentals/Components.page -text @@ -2353,6 +2358,7 @@ tests/simple_unit/SqlMap/domain/Search.php -text tests/simple_unit/SqlMap/domain/User.php -text tests/simple_unit/SqlMap/gen.php -text tests/simple_unit/SqlMap/maps/MySql/Account.xml -text +tests/simple_unit/SqlMap/maps/MySql/ActiveRecord.xml -text tests/simple_unit/SqlMap/maps/MySql/Category.xml -text tests/simple_unit/SqlMap/maps/MySql/Complex.xml -text tests/simple_unit/SqlMap/maps/MySql/Document.xml -text diff --git a/buildscripts/texbuilder/quickstart/pages.php b/buildscripts/texbuilder/quickstart/pages.php index c237a179..91dba08d 100644 --- a/buildscripts/texbuilder/quickstart/pages.php +++ b/buildscripts/texbuilder/quickstart/pages.php @@ -86,6 +86,11 @@ $pages['Active Control Overview'] = array( $pages['Write New Controls'] = array(
'Controls/NewControl.page');
+$pages['Working with Databases'] = array(
+ 'Database/DAO.page',
+ 'Database/ActiveRecord.page',
+ 'Database/SqlMap.page');
+
$pages['Advanced Topics'] = array(
'Advanced/Collections.page',
'Advanced/Auth.page',
diff --git a/buildscripts/texbuilder/quickstart/quickstart.tex b/buildscripts/texbuilder/quickstart/quickstart.tex index 264f9c85..d84383b8 100644 --- a/buildscripts/texbuilder/quickstart/quickstart.tex +++ b/buildscripts/texbuilder/quickstart/quickstart.tex @@ -40,6 +40,16 @@ \fancyhead[RE]{\nouppercase{\scshape\leftmark}}
%-----------------------------------------------------------------------
+% boxes
+
+\newsavebox{\fmboxb}
+\newenvironment{mybox}
+ {\vspace{-2mm}\begin{center}\begin{lrbox}{\fmboxb}\hspace{2mm}
+ \begin{minipage}{0.85\textwidth} \vspace{2mm}\small}
+ { \vspace{2mm} \end{minipage}
+ \hspace{2mm}\end{lrbox}\fbox{\usebox{\fmboxb}}\end{center}}
+
+
%----------------- TITLE --------------
\title{\Huge \bfseries PRADO v3.1 Quickstart Tutorial
diff --git a/demos/quickstart/protected/controls/TopicList.tpl b/demos/quickstart/protected/controls/TopicList.tpl index ddf88c2c..e2554a52 100644 --- a/demos/quickstart/protected/controls/TopicList.tpl +++ b/demos/quickstart/protected/controls/TopicList.tpl @@ -52,12 +52,14 @@ </div>
<div class="topic">
-<div>Working with Database</div>
+<div>Working with Databases</div>
<ul>
<li><a href="?page=Database.DAO">Data Access Objects</a></li>
+ <li><a href="?page=Database.ActiveRecord">Active Record</a></li>
+ <li><a href="?page=Database.SqlMap">SqlMap Data Mapper</a></li>
</ul>
</div>
-
+<!--
<div class="topic">
<div>Module Reference</div>
<ul>
@@ -69,7 +71,7 @@ <ul>
</ul>
</div>
-
+-->
<div class="topic">
<div>Advanced Topics</div>
<ul>
diff --git a/demos/quickstart/protected/pages/Database/ActiveRecord.page b/demos/quickstart/protected/pages/Database/ActiveRecord.page new file mode 100644 index 00000000..017b8d45 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/ActiveRecord.page @@ -0,0 +1,360 @@ +<com:TContent ID="body" > +<!-- $Id $ --> +<h1>Active Record</h1> +<p>Active Records are objects that wrap a row in a database table or view, + encapsulates the database access and adds domain logic on that data. + The basics of an Active Record is a business object class, e.g., a + <tt>Products</tt> class, that match very closely the record structure + of an underlying database table. Each Active Record will be responsible for + saving and loading data to and from the database. </p> +<div class="info"><b class="note">Info:</b> + The data structure of an Active Record should match exactly that of a table + in the database. + Each field in the class must correspond to one column in the table. +</div> + +<h2>When to Use It</h2> +<p>Active Record is a good choice for domain logic that isn't too complex, + such as creates, reads, updates, and deletes. Derivations and validations + based on a single record work well in this structure. Active Record has the + primary advantage of simplicity. It's easy to build + Active Records, and they are easy to understand.</p> + + <p>However, as your business logic grows in complexity, you'll soon want + to use your object's direct relationships, collections, inheritance, and so + forth. These don't map easily onto Active Record, and adding them piecemeal + gets very messy. + Another argument against Active Record is the fact that it couples the object + design to the database design. This makes it more difficult to refactor as a project goes forward.</p> + + <p>The alternative is to use a Data Mapper that separates the roles of the + business object and how these objects are stored. + Prado provides a complimentary choice between Active Record and + <a href="?page=Database.SqlMap">SqlMap Data Mapper</a>. + A SqlMap Data Mapper can be used to load Active Record objects, in turn, these + Active Record objects can be used to update the database. + The "relationship" between Active Records and <a href="?page=Database.SqlMap">SqlMap</a> is illustrated in the + following diagram. More details regarding the SqlMap Data Mapper can be found in + the <a href="http://www.pradosoft.com/demos/sqlmap/">SqlMap Manual</a>. + <img src=<%~ sqlmap_active_record.png %> alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/> + </p> + + <p> + The Active Record class has methods that do the following: + <ul> + <li>Construct an instance of the Active Record from a SQL result set row.</li> + <li>Construct a new instance for later insertion into the table.</li> + <li>Finder methods to wrap commonly used SQL queries and return Active Record objects.</li> + <li>Update existing records and insert new records into the database.</li> + </ul> + </p> +The Active Record implementation utilizes the <a href="?page=Database.DAO">Prado DAO</a> classes for data access. +The current Active Record implementation supports +<a href="http://www.mysql.com">MySQL</a>, +<a href="http://www.postgres.com">Postgres SQL</a> and +<a href="http://www.sqlite.org">SQLite</a> databases. +Support for other databases can be provided when there are sufficient demand. +<h2>Defining an Active Record</h2> +<p>Let us + consider the following "users" table that contains two columns named "username" and "email", + where "username" is also the primary key. +<com:TTextHighlighter Language="sql" CssClass="source"> +CREATE TABLE users +( + username VARCHAR( 20 ) NOT NULL , + email VARCHAR( 200 ) , + PRIMARY KEY ( username ) +); +</com:TTextHighlighter> +</p> +<p>Next we define our Active Record class that corresponds to the "users" table. +<com:TTextHighlighter Language="php" CssClass="source"> +class UserRecord extends TActiveRecord +{ + public $username; //the column named "username" in the "users" table + public $email; + + private static $_tablename='users'; //table name + + /** + * @return TActiveRecord active record finder instance + */ + public static function finder() + { + return self::getRecordFinder('UserRecord'); + } +} +</com:TTextHighlighter> +</p> +<p>Each property of the <tt>UserRecord</tt> class must correspond to a + column with the same name in the "users" table. The static class variable + <tt>$_tablename</tt> is optional when the class name is the same as + the table name in the database, otherwise <tt>$_tablename</tt> must + specify the table name that corresponds to your Active Record class. +</p> + +<div class="tip"><b class="note">Tip:</b> + Since <tt>TActiveRecord</tt> extends <tt>TComponent</tt>, setter and + getter methods can be defined to allow control over how variables + are set and returned. For example, adding a <tt>$level</tt> property to the UserRecord class: +<com:TTextHighlighter Language="php" CssClass="source"> +class UserRecord extends TActiveRecord { + ... //existing definitions as above + + private $_level; + public function setLevel($value) { + $this->_level=TPropertyValue::ensureInteger($value,0); + } + public function getLevel($value){ + return $this->_level; + } +} +</com:TTextHighlighter> +</div> + +<p> + The static method <tt>finder()</tt> returns an <tt>UserRecord</tt> instance + that can be used to load records from the database. The loading of records + using the finer methods is discuss a little later. The <tt>TActiveRecord::getRecordFinder()</tt> + static method takes the name of the current Active Record class as parameter. +</p> + +<h2>Setting up a database connection</h2> +<p> + A default database connection for Active Record can be set as follows. + See <a href="?page=Database.DAO">Establishing Database Connection</a> for + futher details regarding creation of database connection in general. +<com:TTextHighlighter Language="php" CssClass="source"> +//create a connection and give it to the ActiveRecord manager. +$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL +$conn = new TDbConnection($dsn, 'dbuser','dbpass'); +TActiveRecordManager::getInstance()->setDbConnection($conn); +</com:TTextHighlighter> +</p> + +<p> + The default database connection can also be configured using a <tt><module></tt> + tag in the <a href="?page=Configurations.AppConfig">application.xml</a> + or <a href="?page=Configurations.PageConfig">config.xml</a> as follows. +<com:TTextHighlighter Language="xml" CssClass="source"> +<modules> + <module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true"> + <database ConnectionString="pgsql:host=localhost;dbname=test" + Username="dbuser" Password="dbpass" /> + </module> +</modules> +</com:TTextHighlighter> +<div class="tip"><b class="note">Tip:</b> + The <tt>EnableCache</tt> attribute when set to "true" will cache the table + meta data, that is, the table columns names, indexes and constraints are + saved in the cache and reused. You must clear or disable the cache if you + wish to see chanages made to your table definitions. A <a href="?page=Advanced.Performance#6402">cache + module</a> must also be defined for the cache to function. +</div> +</p> + +<p>A <tt>ConnectionID</tt> property can be specified with value corresponding + to another <tt>TDataSourceConfig</tt> module configuration's ID value. This allows + the same database connection to be used in other modules such as <a href="?page=Database.SqlMap">SqlMap</a>. +<com:TTextHighlighter Language="xml" CssClass="source"> +<modules> + <module class="System.Data.TDataSourceConfig" ID="db1"> + <database ConnectionString="pgsql:host=localhost;dbname=test" + Username="dbuser" Password="dbpass" /> + </module> + + <module class="System.Data.ActiveRecord.TActiveRecordConfig" + ConnectionID="db1" EnableCache="true" /> + + <module class="System.Data.SqlMap.TSqlMapConfig" + ConnectionID="db1" ... /> +</modules> +</com:TTextHighlighter> +</p> + +<h2>Loading data from the database</h2> +<p> + The <tt>TActiveRecord</tt> class provides many convenient methods to find + records from the database. The simplest is finding records by matching primary keys. + See the <com:DocLink ClassPath="System.Data.ActiveRecord.TActiveRecord" /> for + more details. +</p> + <h3><tt>findByPk()</tt></h3> + <p>Finds one record using only the primary key or composite primary keys. +<com:TTextHighlighter Language="php" CssClass="source"> +$finder = UserRecord::finder(); +$user = $finder->findByPk($primaryKey); + +//when the table uses composite keys +$record = $finder->findByPk($key1, $key2, ...); //for composite keys +$record = $finder->findByPk(array($key1, $key2,...)); //same as above +</com:TTextHighlighter> +</p> + +<h3><tt>find()</tt></h3> +<p>Finds <b>one single record</b> that matches the criteria. The criteria + can be a partial SQL string or a <tt>TActiveRecordCriteria</tt> object. +<com:TTextHighlighter Language="php" CssClass="source"> +$finder = UserRecord::finder(); + +//:name and :pass are place holders for specific values of $name and $pass +$finder->find('username = :name AND password = :pass', + array(':name'=>$name, ':pass'=>$pass)); + +//using position place holders +$finder->find('username = ? AND password = ?', array($name, $pass)); +//same as above +$finder->find('username = ? AND password = ?', $name, $pass); + +//$criteria is of TActiveRecordCriteria +$finder->find($criteria); //the 2nd parameter for find() is ignored. +</com:TTextHighlighter> +</p> + +<p>The <tt>TActiveRecordCriteria</tt> class has the following properties: + <ul> + <li><tt>Parameters</tt> -- name value parameter pairs.</li> + <li><tt>OrderBy</tt> -- column name and ordering pairs.</li> + <li><tt>Condition</tt> -- parts of the WHERE SQL conditions.</li> + <li><tt>Limit</tt> -- maximum number of records to return.</li> + <li><tt>Offset</tt> -- record offset in the table.</li> + </ul> +</p> + +<com:TTextHighlighter Language="php" CssClass="source"> +$criteria = new TActiveRecordCriteria; +$criteria->Condition = 'username = :name AND password = :pass'; +$criteria->Parameters[':name'] = 'admin'; +$criteria->Parameters[':pass'] = 'prado'; +$criteria->OrdersBy['level'] = 'desc'; +$criteria->OrdersBy['name'] = 'asc'; +$criteria->Limit = 10; +$criteria->Offset = 20; +</com:TTextHighlighter> + +<h3><tt>findAll()</tt></h3> +<p>Same as <tt>find()</tt> but returns an array of objects.</p> + +<h3><tt>findBy*()</tt> and <tt>findAllBy*()</tt></h3> +<p>Dynamic find method using parts of method name as search criteria. +Method names starting with <tt>findBy</tt> return 1 record only. +Method names starting with <tt>findAllBy</tt> return an array of records. +The condition is taken as part of the method name after <tt>findBy</tt> or <tt>findAllBy</tt>. + +The following blocks of code are equivalent: + +<com:TTextHighlighter Language="php" CssClass="source"> +$finder->findByName($name) +$finder->find('Name = ?', $name); +</com:TTextHighlighter> + +<com:TTextHighlighter Language="php" CssClass="source"> +$finder->findByUsernameAndPassword($name,$pass); +$finder->findBy_Username_And_Password($name,$pass); +$finder->find('Username = ? AND Password = ?', $name, $pass); +</com:TTextHighlighter> + +<com:TTextHighlighter Language="php" CssClass="source"> +$finder->findAllByAge($age); +$finder->findAll('Age = ?', $age); +</com:TTextHighlighter> +</p> + +<h3><tt>findBySql()</tt></h3> +<p>Finds records using full SQL, returns corresponding array of record objects.</p> + +<h3><tt>count()</tt></h3> +<p>Find the number of matchings records.</p> + +<h2>Inserting and updating records</h2> +<p> +Add a new record using TActiveRecord is very simple, just create a new Active +Record object and call the <tt>save()</tt> method. E.g. +<com:TTextHighlighter Language="php" CssClass="source"> +$user1 = new UserRecord(); +$user1->username = "admin" +$user1->email = "admin@example.com"; +$user1->save(); //insert a new record + +$data = array('username'=>'admin', 'email'=>'admin@example.com'); +$user2 = new UserRecord($data); //create by passing some existing data +$user2->save(); //insert a new record +</com:TTextHighlighter> +<div class="tip"><b class="note">Tip:</b> +The objects are update with the primary key of those the tables that contains +definitions that automatically creates a primary key for the newly insert records. +For example, if you insert a new record into a MySQL table that has columns +defined with "autoincrement", the Active Record objects will be updated with the new +incremented values.</div> +</p> + +<p> +To update a record in the database, just change one or more properties of +the Active Record object that has been loaded from the database and then +call the <tt>save()</tt> method. + +<com:TTextHighlighter Language="php" CssClass="source"> +$user = UserRecord::finder()->findByName('admin'); +$user->email="test@example.com"; //change property +$user->save(); //update it. +</com:TTextHighlighter> +</p> + +<p> +Active Record objects have a simple life-cycle illustrated in the following diagram. +<img src=<%~ object_states.png %> alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/> +</p> + +<h2>Deleting existing records</h2> +<p> + To delete an existing record that is already loaded, just call the <tt>delete()</tt> method. + You can also delete records in the database by primary keys without + loading any records using the <tt>deleteByPk()</tt> method. + For example, to delete one or records with tables having a scalar primary key. +<com:TTextHighlighter Language="php" CssClass="source"> +$finder->deleteByPk($primaryKey); //delete 1 record +$finder->deleteByPk($key1,$key2,...); //delete multiple records +$finder->deleteByPk(array($key1,$key2,...)); //delete multiple records +</com:TTextHighlighter> +</p> + +<p> +For composite primary keys (determined automatically from the table definitions): +<com:TTextHighlighter Language="php" CssClass="source"> +$finder->deleteByPk(array($key1,$key2)); //delete 1 record + +//delete multiple records +$finder->deleteByPk(array($key1,$key2), array($key3,$key4),...); + +//delete multiple records +$finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. )); +</com:TTextHighlighter> +</p> + +<h2>Transactions</h2> +<p>All Active Record objects contains the property <tt>DbConnection</tt> + that can be used to obtain a transaction object. +<com:TTextHighlighter Language="php" CssClass="source"> +$finder = UserRecord::finder(); + +$transaction = $finder->DbConnection->beginTransaction(); +try +{ + $user = $finder->findByPk('admin'); + $user->email = 'test@example.com'; //alter the $user object + $user->save(); + $transaction->commit(); +} +catch(Exception $e) // an exception is raised if a query fails will be raised +{ + $transaction->rollBack(); +} +</com:TTextHighlighter> + +<h2>References</h2> +<ul> + <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>, + Addison Wesley, 2002.</li> +</ul> + +</com:TContent>
\ No newline at end of file diff --git a/demos/quickstart/protected/pages/Database/SqlMap.page b/demos/quickstart/protected/pages/Database/SqlMap.page new file mode 100644 index 00000000..2b70e8f5 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/SqlMap.page @@ -0,0 +1,265 @@ +<com:TContent ID="body"> +<!-- $Id $ --> + +<h1>Data Mapper</h1> +<p>Data Mappers moves data between objects and a database while keeping them + independent of each other and the mapper itself. If you started with + <a href="?page=Database.ActiveRecord">Active Records</a>, you may eventually + faced with more complex business + objects as your project progresses. When you build an object model with a + lot of business logic it's valuable to use these mechanisms to better organize + the data and the behavior that goes with it. Doing so leads to variant schemas; + that is, the object schema and the relational schema don't match up. +</p> + +<p>The Data Mapper separates the in-memory objects from the database. Its responsibility + is to transfer data between the two and also to isolate them from each other. + With Data Mapper the in-memory objects needn't know even that there's a database + present; they need no SQL interface code, and certainly no knowledge of the + database schema. (The database schema is always ignorant of the objects that use it.) +</p> + +<h2>When to Use It</h2> +<p>The primary occasion for using Data Mapper is when you want the database schema + and the object model to evolve independently. Data Mapper's primary benefit is + that when working on the business (or domain) objects you can ignore the database, both in + design and in the build and testing process. The domain objects have no idea + what the database structure is, because all the correspondence is done by the mappers. +</p> + +<p>This helps you in the code because you can understand and work with the domain objects + without having to understand how they're stored in the database. You can modify the + business models or the database without having to alter either. With complicated + mappings, particularly those involving <b>existing databases</b>, this is very valuable. +</p> + +<p>The price, of course, is the extra layer that you don't get with + <a href="?page=Database.ActiveRecord">Active Record</a>, + so the test for using these patterns is the complexity of the business logic. + If you have fairly simple business logic, an <a href="?page=Database.ActiveRecord">Active Record</a> + will probably work. + For more complicated logic a Data Mapper may be more suitable. +</p> + +<h2>SqlMap Data Mapper</h2> +<p>The SqlMap DataMapper framework makes it easier to use a database with a PHP application. + SqlMap DataMapper couples objects with stored procedures or SQL statements using + a XML descriptor. Simplicity is the biggest advantage of the SqlMap DataMapper over + object relational mapping tools. To use SqlMap DataMapper you rely on your own objects, + XML, and SQL. There is little to learn that you don't already know. + With SqlMap DataMapper you have the full power of both SQL and stored procedures at + your fingertip +</p> + +<p> + <img src=<%~ diagram.png %> alt="SqlMap Data Mapper Overview" id="fig:sqlmap.png" class="figure"/> + + Here's a high level description of the work flow illustrated in the figure abov. + Provide a parameter, either as an object or a primitive type. The parameter can be + used to set runtime values in your SQL statement or stored procedure. If a runtime value + is not needed, the parameter can be omitted. +</p> +<p>Execute the mapping by passing the parameter and the name you gave the statement or + procedure in your XML descriptor. This step is where the magic happens. The framework + will prepare the SQL statement or stored procedure, set any runtime values using your + parameter, execute the procedure or statement, and return the result. +</p> + +<p>In the case of an update, the number of rows affected is returned. In the case of a + query, a single object, or a collection of objects is returned. Like the parameter, + the result object, or collection of objects, can be a plain-old object or a primitive PHP type. +</p> + +<h2>Setting up a database connection and initializing the SqlMap</h2> +<p> + A database connection for SqlMap can be set as follows. + See <a href="?page=Database.DAO">Establishing Database Connection</a> for + futher details regarding creation of database connection in general. +<com:TTextHighlighter Language="php" CssClass="source"> +//create a connection and give it to the SqlMap manager. +$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL +$conn = new TDbConnection($dsn, 'dbuser','dbpass'); +$manager = new TSqlMapManager($conn); +$manager->configureXml('my-sqlmap.xml'); +$sqlmap = $manager->getSqlMapGateway(); +</com:TTextHighlighter> +</p> + +<p> + The <tt>TSqlMapManager</tt> is responsible for setting up the database connection + and configuring the SqlMap with given XML file(s). The <tt>configureXml()</tt> + method accepts a string that points to a SqlMap XML configuration file. Once + configured, call the <tt>getSqlMapGateway()</tt> method to obtain an instance + of the SqlMap gateway interface (use this object to insert/delete/find records). +</p> + +<p> + SqlMap database connection can also be configured using a <tt><module></tt> + tag in the <a href="?page=Configurations.AppConfig">application.xml</a> + or <a href="?page=Configurations.PageConfig">config.xml</a> as follows. +<com:TTextHighlighter Language="xml" CssClass="source"> +<modules> + <module id="my-sqlmap" class="System.Data.SqlMap.TSqlMapConfig" + EnableCache="true" ConfigFile="my-sqlmap.xml" > + <database ConnectionString="pgsql:host=localhost;dbname=test" + Username="dbuser" Password="dbpass" /> + </module> +</modules> +</com:TTextHighlighter> +</p> + +<p> + The <tt>ConfigFile</tt> attribute should point to a SqlMap configuration file + (to be detailed later) either using absolute path, relative path or the + Prado's namespace dot notation path (must omit the ".xml" extension). + + <div class="tip"><b class="note">Tip:</b> + The <tt>EnableCache</tt> attribute when set to "true" will cache the + parsed configuration. You must clear or disable the cache if you + make chanages your configuration file. + A <a href="?page=Advanced.Performance#6402">cache + module</a> must also be defined for the cache to function. + </div> +</p> + +<p>To obtain the SqlMap gateway interface from the <module> configuration, simply + do, for example, +<com:TTextHighlighter Language="php" CssClass="source"> +class MyPage extends TPage +{ + public function onLoad($param) + { + parent::onLoad($param); + $sqlmap = $this->Application->Modules['my-sqlmap']->Client; + $sqlmap->queryForObject(...); //query for some object + } +} +</com:TTextHighlighter> +</p> + +<h2>A quick example</h2> +<p>Let us + consider the following "users" table that contains two columns named "username" and "email", + where "username" is also the primary key. +<com:TTextHighlighter Language="sql" CssClass="source"> +CREATE TABLE users +( + username VARCHAR( 20 ) NOT NULL , + email VARCHAR( 200 ) , + PRIMARY KEY ( username ) +); +</com:TTextHighlighter> +</p> +<p>Next we define our plain <tt>User</tt> class as follows. Notice that + the <tt>User</tt> is very simple. +<com:TTextHighlighter Language="php" CssClass="source"> +class User +{ + public $username; + public $email; +} +</com:TTextHighlighter> +</p> +</p> + +<p>Next, we need to define a SqlMap XMl configuration file, lets name + the file as <tt>my-sqlmap.xml</tt> +<com:TTextHighlighter Language="xml" CssClass="source"> +<?xml version="1.0" encoding="utf-8" ?> +<sqlMapConfig> + <select id="SelectUsers" resultClass="User"> + SELECT username, email FROM users + </select> +</sqlMapConfig> +</com:TTextHighlighter> +</p> +<p>The <select> tag returns defines an SQL statement. The <tt>id</tt> + attribute will be used as the identifier for the query. The <tt>resultClass</tt> + attribute value is the name of the class the the objects to be returned. + We can now query the objects as follows: + +<com:TTextHighlighter Language="php" CssClass="source"> +//assume that $sqlmap is an TSqlMapGateway instance +$userList = $sqlmap->queryForList("SelectUsers"); + +//Or just one, if that's all you need: +$user = $sqlmap->queryForObject("SelectUsers"); +</com:TTextHighlighter> +</p> + +<p>The above example shows demonstrates only a fraction of the capabilities + of the SqlMap Data Mapper. Further details can be found in the + <a href="http://www.pradosoft.com/demo/sqlamp/">SqlMap Manual</a>. +</p> + +<h2>Combining SqlMap with Active Records</h2> +<p>The above example may seem trival and it also seems that there is + alot work just to retrieve some data. However, notice that the <tt>User</tt> + class is totally unware of been stored in the database, and the database is + unware of the <tt>User</tt> class. +</p> +<p> + One of advantages of SqlMap is the + ability to map complex object relationship, collections from an existing + database. On the other hand, <a href="?page=Database.ActiveRecord">Active Record</a> + provide a very simple way + to interact with the underlying database but unable to do more complicated + relationship or collections. A good compromise is to use SqlMap to retrieve + complicated relationships and collections as Active Record objects and then using + these Active Records to do the updates, inserts and deletes. +</p> +<p>Continuing with the previous example, we change the definition of the + <tt>User</tt> class to become an Active Record. +<com:TTextHighlighter Language="php" CssClass="source"> +class UserRecord extends TActiveRecord +{ + public $username; //the column named "username" in the "users" table + public $email; + + private static $_tablename='users'; //table name + + /** + * @return TActiveRecord active record finder instance + */ + public static function finder() + { + return self::getRecordFinder('UserRecord'); + } +} +</com:TTextHighlighter> +</p> + +<p>We also need to change the definition of the SqlMap XML configuration. We + just need to change the value of <tt>resultClass</tt> attribute to <tt>UserRecord</tt>. +<com:TTextHighlighter Language="xml" CssClass="source"> +<?xml version="1.0" encoding="utf-8" ?> +<sqlMapConfig> + <select id="SelectUsers" resultClass="UserRecord"> + SELECT username, email FROM users + </select> +</sqlMapConfig> +</com:TTextHighlighter> +</p> + + +<p>The PHP code for retrieving the users remains the same, but SqlMap + returns Active Records instead, and we can take advantage of the Active Record methods. + +<com:TTextHighlighter Language="php" CssClass="source"> +//assume that $sqlmap is an TSqlMapGateway instance +$user = $sqlmap->queryForObject("SelectUsers"); + +$user->email = 'test@example.com'; //change data +$user->save(); //save it using Active Record +</com:TTextHighlighter> +</p> + +<h2>References</h2> +<ul> + <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>, + Addison Wesley, 2002.</li> + <li>xxxx. <i>iBatis Data Mapper</i>, + <a href="http://www.apache.org/ibatis">http://www.apache.org/ibatis</a>.</li> +</ul> + +</com:TContent>
\ No newline at end of file diff --git a/demos/quickstart/protected/pages/Database/diagram.png b/demos/quickstart/protected/pages/Database/diagram.png Binary files differnew file mode 100644 index 00000000..0a0ca73d --- /dev/null +++ b/demos/quickstart/protected/pages/Database/diagram.png diff --git a/demos/quickstart/protected/pages/Database/object_states.png b/demos/quickstart/protected/pages/Database/object_states.png Binary files differnew file mode 100755 index 00000000..db194783 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/object_states.png diff --git a/demos/quickstart/protected/pages/Database/sqlmap_active_record.png b/demos/quickstart/protected/pages/Database/sqlmap_active_record.png Binary files differnew file mode 100755 index 00000000..6d958d33 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/sqlmap_active_record.png diff --git a/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php b/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php index 3e5b6456..15d0c2a3 100644 --- a/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php @@ -72,6 +72,7 @@ class ReportsDao extends BaseDao {
$sqlmap = $this->getSqlMap();
$ids = implode(',', array_map('intval', $projects));
+ $sqlmap->getDbConnection()->setActive(true); //db connection needs to be open for quoteString
$usernames = implode(',', array_map(array($sqlmap->getDbConnection(), 'quoteString'), $users));
$param['projects'] = $ids;
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml index 099e7fef..bcac9480 100644 --- a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml +++ b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml @@ -5,9 +5,9 @@ <result property="ID" column="ProjectID" type="integer"/>
<result property="Name" column="Name" />
<result property="Description" column="Description" />
- <result property="DateCreated" column="CreationDate" typeHandler="DateTime" />
+ <result property="DateCreated" column="CreationDate" typeHandler="DateTimeTypeHandler" />
<result property="EstimateDuration" column="EstimateDuration" type="float" />
- <result property="CompletionDate" column="CompletionDate" typeHandler="DateTime" />
+ <result property="CompletionDate" column="CompletionDate" typeHandler="DateTimeTypeHandler" />
<result property="CreatorUserName" column="CreatorID" />
<result property="ManagerUserName" column="ManagerID" />
<result property="ActualDuration" column="ActualDuration" type="float" />
@@ -22,7 +22,7 @@ (Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
VALUES
(#Name#, #Description#, NOW(), #EstimateDuration#,
- #CompletionDate, typeHandler=DateTime#,
+ #CompletionDate, typeHandler=DateTimeTypeHandler#,
#CreatorUserName#, #ManagerUserName#)
<selectKey property="ID" type="post" resultClass="int">
select LAST_INSERT_ID() as value
@@ -62,6 +62,7 @@ project.CompletionDate as CompletionDate,
project.CreatorID as CreatorID,
project.ManagerID as ManagerID,
+ project.ManagerID as ManagerUserName,
SUM(time_entry.Duration) as ActualDuration
FROM project
LEFT JOIN categories ON project.ProjectID = categories.ProjectID
@@ -144,7 +145,7 @@ <update id="UpdateProject" parameterClass="ProjectRecord">
UPDATE project
SET
- CompletionDate = #CompletionDate, typeHandler=DateTime#,
+ CompletionDate = #CompletionDate, typeHandler=DateTimeTypeHandler#,
Description = #Description#,
EstimateDuration = #EstimateDuration#,
ManagerId =#ManagerUserName#,
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql index bb120fc4..91481efe 100644 --- a/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql +++ b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql @@ -1,136 +1,227 @@ -DROP TABLE IF EXISTS user_roles;
-CREATE TABLE user_roles (
- UserID varchar(50) NOT NULL,
- RoleType varchar(50) NOT NULL,
- PRIMARY KEY (UserID,RoleType),
- KEY RoleType (RoleType)
+--
+-- Database: `time-tracker`
+--
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `categories`
+--
+
+CREATE TABLE IF NOT EXISTS `categories` (
+ `CategoryID` int(11) NOT NULL auto_increment,
+ `Name` varchar(255) NOT NULL,
+ `ProjectID` int(11) NOT NULL,
+ `ParentCategoryID` int(11) default '0',
+ `Abbreviation` varchar(255) default NULL,
+ `EstimateDuration` float(10,2) default '0.00',
+ PRIMARY KEY (`CategoryID`),
+ UNIQUE KEY `UniqueNamePerProject` (`Name`,`ProjectID`),
+ KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS role_types;
-CREATE TABLE role_types (
- RoleType varchar(50) NOT NULL,
- Description varchar(255) NOT NULL,
- PRIMARY KEY (RoleType)
+--
+-- Dumping data for table `categories`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `project`
+--
+
+CREATE TABLE IF NOT EXISTS `project` (
+ `ProjectID` int(11) NOT NULL auto_increment,
+ `Name` varchar(255) NOT NULL,
+ `Description` varchar(255) default NULL,
+ `CreationDate` datetime NOT NULL,
+ `CompletionDate` datetime NOT NULL,
+ `Disabled` tinyint(1) NOT NULL default '0',
+ `EstimateDuration` float(10,2) NOT NULL default '0.00',
+ `CreatorID` varchar(50) NOT NULL,
+ `ManagerID` varchar(50) default NULL,
+ PRIMARY KEY (`ProjectID`),
+ KEY `Name` (`Name`),
+ KEY `CreatorID` (`CreatorID`),
+ KEY `ManagerID` (`ManagerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS project_members;
-CREATE TABLE project_members (
- UserID varchar(50) NOT NULL,
- ProjectID int(11) NOT NULL,
- PRIMARY KEY (UserID,ProjectID),
- KEY ProjectID (ProjectID)
+--
+-- Dumping data for table `project`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `project_members`
+--
+
+CREATE TABLE IF NOT EXISTS `project_members` (
+ `UserID` varchar(50) NOT NULL,
+ `ProjectID` int(11) NOT NULL,
+ PRIMARY KEY (`UserID`,`ProjectID`),
+ KEY `ProjectID` (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS time_entry;
-CREATE TABLE time_entry (
- EntryID int(11) NOT NULL auto_increment,
- EntryCreated datetime NOT NULL,
- Duration float(10,2) NOT NULL default '0.00',
- Description varchar(1000) default NULL,
- CategoryID int(11) NOT NULL default '0',
- EntryDate datetime default NULL,
- CreatorID varchar(50) NOT NULL,
- UserID varchar(50) NOT NULL,
- PRIMARY KEY (EntryID),
- KEY CategoryID (CategoryID),
- KEY CreatorID (CreatorID),
- KEY UserID (UserID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
-DROP TABLE IF EXISTS signon;
-CREATE TABLE signon (
- SessionToken varchar(32) NOT NULL,
- Username varchar(50) NOT NULL,
- LastSignOnDate datetime NOT NULL,
- PRIMARY KEY (SessionToken),
- KEY Username (Username)
+--
+-- Dumping data for table `project_members`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `role_types`
+--
+
+CREATE TABLE IF NOT EXISTS `role_types` (
+ `RoleType` varchar(50) NOT NULL,
+ `Description` varchar(255) NOT NULL,
+ PRIMARY KEY (`RoleType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-DROP TABLE IF EXISTS categories;
-CREATE TABLE categories (
- CategoryID int(11) NOT NULL auto_increment,
- `Name` varchar(255) NOT NULL,
- ProjectID int(11) NOT NULL,
- ParentCategoryID int(11) default '0',
- Abbreviation varchar(255) default NULL,
- EstimateDuration float(10,2) default '0.00',
- PRIMARY KEY (CategoryID),
- UNIQUE KEY UniqueNamePerProject (`Name`,ProjectID),
- KEY ProjectID (ProjectID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
-DROP TABLE IF EXISTS project;
-CREATE TABLE project (
- ProjectID int(11) NOT NULL auto_increment,
- `Name` varchar(255) NOT NULL,
- Description varchar(255) default NULL,
- CreationDate datetime NOT NULL,
- CompletionDate datetime NOT NULL,
- Disabled tinyint(1) NOT NULL default '0',
- EstimateDuration float(10,2) NOT NULL default '0.00',
- CreatorID varchar(50) NOT NULL,
- ManagerID varchar(50) default NULL,
- PRIMARY KEY (ProjectID),
- KEY `Name` (`Name`),
- KEY CreatorID (CreatorID),
- KEY ManagerID (ManagerID)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+--
+-- Dumping data for table `role_types`
+--
+
+INSERT INTO `role_types` (`RoleType`, `Description`) VALUES ('admin', 'Project administrator may additionally view the list of all users.'),
+('consultant', 'Consultant may log time entries only.'),
+('manager', 'Project manager may additionally edit all projects and view reports.');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `signon`
+--
+
+CREATE TABLE IF NOT EXISTS `signon` (
+ `SessionToken` varchar(32) NOT NULL,
+ `Username` varchar(50) NOT NULL,
+ `LastSignOnDate` datetime NOT NULL,
+ PRIMARY KEY (`SessionToken`),
+ KEY `Username` (`Username`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `signon`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `time_entry`
+--
+
+CREATE TABLE IF NOT EXISTS `time_entry` (
+ `EntryID` int(11) NOT NULL auto_increment,
+ `EntryCreated` datetime NOT NULL,
+ `Duration` float(10,2) NOT NULL default '0.00',
+ `Description` varchar(1000) default NULL,
+ `CategoryID` int(11) NOT NULL default '0',
+ `EntryDate` datetime default NULL,
+ `CreatorID` varchar(50) NOT NULL,
+ `UserID` varchar(50) NOT NULL,
+ PRIMARY KEY (`EntryID`),
+ KEY `CategoryID` (`CategoryID`),
+ KEY `CreatorID` (`CreatorID`),
+ KEY `UserID` (`UserID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `time_entry`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `user_roles`
+--
+
+CREATE TABLE IF NOT EXISTS `user_roles` (
+ `UserID` varchar(50) NOT NULL,
+ `RoleType` varchar(50) NOT NULL,
+ PRIMARY KEY (`UserID`,`RoleType`),
+ KEY `RoleType` (`RoleType`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `user_roles`
+--
+
+INSERT INTO `user_roles` (`UserID`, `RoleType`) VALUES ('admin', 'admin'),
+('admin', 'consultant'),
+('consultant', 'consultant'),
+('manager', 'consultant'),
+('admin', 'manager'),
+('manager', 'manager');
-DROP TABLE IF EXISTS users;
-CREATE TABLE users (
- Username varchar(50) NOT NULL,
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `users`
+--
+
+CREATE TABLE IF NOT EXISTS `users` (
+ `Username` varchar(50) NOT NULL,
`Password` varchar(50) NOT NULL,
- EmailAddress varchar(100) NOT NULL,
- Disabled tinyint(1) NOT NULL default '0',
- PRIMARY KEY (Username)
+ `EmailAddress` varchar(100) NOT NULL,
+ `Disabled` tinyint(1) NOT NULL default '0',
+ PRIMARY KEY (`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Dumping data for table `users`
+--
+
+INSERT INTO `users` (`Username`, `Password`, `EmailAddress`, `Disabled`) VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@pradosoft.com', 0),
+('consultant', '7adfa4f2ba9323e6c1e024de375434b0', 'consultant@pradosoft.com', 0),
+('manager', '1d0258c2440a8d19e716292b231e3190', 'manager@pradosoft.com', 0);
+
+--
+-- Constraints for dumped tables
+--
+
+--
+-- Constraints for table `categories`
+--
ALTER TABLE `categories`
- ADD CONSTRAINT categories_ibfk_1
- FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
+ ADD CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE;
+--
+-- Constraints for table `project`
+--
ALTER TABLE `project`
- ADD CONSTRAINT project_ibfk_2
- FOREIGN KEY (ManagerID) REFERENCES users (Username),
- ADD CONSTRAINT project_ibfk_1
- FOREIGN KEY (CreatorID) REFERENCES users (Username);
+ ADD CONSTRAINT `project_ibfk_6` FOREIGN KEY (`ManagerID`) REFERENCES `users` (`Username`),
+ ADD CONSTRAINT `project_ibfk_5` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `project_members`
+--
ALTER TABLE `project_members`
- ADD CONSTRAINT project_members_ibfk_1
- FOREIGN KEY (UserID) REFERENCES users (Username),
- ADD CONSTRAINT project_members_ibfk_2
- FOREIGN KEY (ProjectID) REFERENCES project (ProjectID);
+ ADD CONSTRAINT `project_members_ibfk_6` FOREIGN KEY (`ProjectID`) REFERENCES `project` (`ProjectID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `project_members_ibfk_5` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`) ON DELETE CASCADE;
+--
+-- Constraints for table `signon`
+--
ALTER TABLE `signon`
- ADD CONSTRAINT signon_ibfk_1
- FOREIGN KEY (Username) REFERENCES users (Username);
+ ADD CONSTRAINT `signon_ibfk_1` FOREIGN KEY (`Username`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `time_entry`
+--
ALTER TABLE `time_entry`
- ADD CONSTRAINT time_entry_ibfk_2
- FOREIGN KEY (UserID) REFERENCES users (Username),
- ADD CONSTRAINT time_entry_ibfk_1
- FOREIGN KEY (CategoryID) REFERENCES categories (CategoryID);
+ ADD CONSTRAINT `time_entry_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`),
+ ADD CONSTRAINT `time_entry_ibfk_6` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `time_entry_ibfk_7` FOREIGN KEY (`CreatorID`) REFERENCES `users` (`Username`);
+--
+-- Constraints for table `user_roles`
+--
ALTER TABLE `user_roles`
- ADD CONSTRAINT user_roles_ibfk_2
- FOREIGN KEY (RoleType) REFERENCES role_types (RoleType),
- ADD CONSTRAINT user_roles_ibfk_1
- FOREIGN KEY (UserID) REFERENCES users (Username);
-
-INSERT INTO role_types (RoleType, Description) VALUES
-('admin', 'Project administrator may additionally view the list of all users.'),
-('consultant', 'Consultant may log time entries only.'),
-('manager', 'Project manager may additionally edit all projects and view reports.');
-
-INSERT INTO users (Username, Password, EmailAddress, Disabled) VALUES
-('admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@pradosoft.com', 0),
-('manager', '1d0258c2440a8d19e716292b231e3190', 'manager@pradosoft.com', 0),
-('consultant', '7adfa4f2ba9323e6c1e024de375434b0', 'consultant@pradosoft.com', 0);
-
-INSERT INTO user_roles (UserID, RoleType) VALUES
-('admin', 'admin'),
-('admin', 'manager'),
-('admin', 'consultant'),
-('manager', 'manager'),
-('manager', 'consultant'),
-('consultant', 'consultant');
\ No newline at end of file + ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`RoleType`) REFERENCES `role_types` (`RoleType`),
+ ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`Username`);
diff --git a/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml index 84468044..ff7f636e 100644 --- a/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml +++ b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml @@ -1,29 +1,17 @@ <?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
-
- <typeHandlers>
- <typeHandler type="TimeTrackerUser"
- callback="TimeTrackerUserTypeHandler"/>
- <typeHandler type="DateTime"
- callback="DateTimeTypeHandler" />
- </typeHandlers>
+
+ <typeHandler type="TimeTrackerUser" class="TimeTrackerUserTypeHandler"/>
+ <typeHandler type="DateTime" class="DateTimeTypeHandler" />
-
- <provider class="TAdodb">
- <datasource
- driver="mysql"
- username="time-tracker"
- password="prado"
- host="localhost"
- database="time-tracker" />
- </provider>
-
- <sqlMaps>
- <sqlMap resource="MySQL4/users.xml"/>
- <sqlMap resource="MySQL4/projects.xml"/>
- <sqlMap resource="MySQL4/category.xml" />
- <sqlMap resource="MySQL4/time-entry.xml" />
- <sqlMap resource="MySQL4/reports.xml" />
- </sqlMaps>
+ <connection class="TDbConnection"
+ ConnectionString="mysql:host=localhost;dbname=time-tracker"
+ Username="time-tracker" Password="prado" />
+
+ <sqlMap resource="MySQL4/users.xml"/>
+ <sqlMap resource="MySQL4/projects.xml"/>
+ <sqlMap resource="MySQL4/category.xml" />
+ <sqlMap resource="MySQL4/time-entry.xml" />
+ <sqlMap resource="MySQL4/reports.xml" />
</sqlMapConfig>
\ No newline at end of file diff --git a/demos/time-tracker/protected/application.xml b/demos/time-tracker/protected/application.xml index bae5f126..d9e4d01c 100644 --- a/demos/time-tracker/protected/application.xml +++ b/demos/time-tracker/protected/application.xml @@ -17,7 +17,7 @@ <module id="cache" class="System.Caching.TMemCache" />
-->
<module id="daos" class="DaoManager" EnableCache="true"
- configFile="Application.App_Data.sqlite-sqlmap">
+ configFile="Application.App_Data.mysql4-sqlmap">
<dao id="UserDao" class="Application.App_Code.Dao.UserDao" />
<dao id="ProjectDao" class="Application.App_Code.Dao.ProjectDao" />
<dao id="TimeEntryDao" class="Application.App_Code.Dao.TimeEntryDao" />
diff --git a/demos/time-tracker/protected/pages/TimeTracker/ProjectList.php b/demos/time-tracker/protected/pages/TimeTracker/ProjectList.php index eb92dcb7..4e3ac485 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/ProjectList.php +++ b/demos/time-tracker/protected/pages/TimeTracker/ProjectList.php @@ -9,7 +9,7 @@ class ProjectList extends TPage $this->projectList->dataBind();
}
- protected function getSortOrder($sort)
+ protected function getSortOrdering($sort)
{
$ordering = $this->getViewState('SortOrder', array());
$order = isset($ordering[$sort]) ? $ordering[$sort] : 'DESC';
@@ -21,7 +21,7 @@ class ProjectList extends TPage protected function sortProjects($sender, $param)
{
$sort = $param->SortExpression;
- $this->showProjects($sort, $this->getSortOrder($sort));
+ $this->showProjects($sort, $this->getSortOrdering($sort));
}
public function onLoad($param)
diff --git a/demos/time-tracker/tests/unit/BaseTestCase.php b/demos/time-tracker/tests/unit/BaseTestCase.php index 7e4520f8..d72cbc5c 100644 --- a/demos/time-tracker/tests/unit/BaseTestCase.php +++ b/demos/time-tracker/tests/unit/BaseTestCase.php @@ -42,7 +42,7 @@ class BaseTestCase extends UnitTestCase function flushMySQLDatabase()
{
$conn = $this->sqlmap->getDbConnection();
- $file = Prado::getPathOfNamespace('Application.App_Data.mysql-reset','.sql');
+ $file = Prado::getPathOfNamespace('Application.App_Data.MySQL4.mysql-reset','.sql');
if(is_file($file))
$this->runScript($conn, $file);
else
@@ -57,7 +57,7 @@ class BaseTestCase extends UnitTestCase {
$line = trim($line);
if(strlen($line) > 0)
- $connection->execute($line);
+ $connection->createCommand($line)->execute();
}
}
}
diff --git a/demos/time-tracker/tests/unit/ProjectDaoTestCase.php b/demos/time-tracker/tests/unit/ProjectDaoTestCase.php index 3ccb28e2..22a01fca 100644 --- a/demos/time-tracker/tests/unit/ProjectDaoTestCase.php +++ b/demos/time-tracker/tests/unit/ProjectDaoTestCase.php @@ -113,11 +113,11 @@ class ProjectDaoTestCase extends BaseTestCase try
{
$this->projectDao->addUserToProject($project->ID, 'asd');
- $this->pass();
+ $this->fail();
}
- catch(TSqlMapQueryExecutionException $e)
+ catch(TDbException $e)
{
- $this->fail();
+ $this->pass();
}
}
diff --git a/demos/time-tracker/tests/unit/UserDaoTestCase.php b/demos/time-tracker/tests/unit/UserDaoTestCase.php index 404dd9e2..d216dbe6 100644 --- a/demos/time-tracker/tests/unit/UserDaoTestCase.php +++ b/demos/time-tracker/tests/unit/UserDaoTestCase.php @@ -175,11 +175,11 @@ class UserDaoTestCase extends BaseTestCase try
{
$this->userDao->updateUserRoles($user);
- $this->pass();
+ $this->fail();
}
- catch(TDataMapperException $e)
+ catch(TDbException $e)
{
- $this->fail();
+ $this->pass();
}
$check = $this->sqlmap->queryForObject('GetUserByName', 'user1');
diff --git a/framework/Data/SqlMap/Configuration/TParameterMap.php b/framework/Data/SqlMap/Configuration/TParameterMap.php index e4d77a91..74fe8800 100644 --- a/framework/Data/SqlMap/Configuration/TParameterMap.php +++ b/framework/Data/SqlMap/Configuration/TParameterMap.php @@ -140,7 +140,7 @@ class TParameterMap extends TComponent {
$value = $this->getObjectValue($parameterValue,$property);
- if(!is_null($handler=$registry->getTypeHandler($property->getType())))
+ if(!is_null($handler=$this->createTypeHandler($property, $registry)))
$value = $handler->getParameter($value);
$value = $this->nullifyDefaultValue($property,$value);
@@ -150,6 +150,23 @@ class TParameterMap extends TComponent return $value;
}
+
+
+ /**
+ * Create type handler from {@link Type setType()} or {@link TypeHandler setTypeHandler}.
+ * @param TParameterProperty parameter property
+ * @param TSqlMapTypeHandlerRegistry type handler registry
+ * @return TSqlMapTypeHandler type handler.
+ */
+ protected function createTypeHandler($property, $registry)
+ {
+ $type=$property->getTypeHandler() ? $property->getTypeHandler() : $property->getType();
+ $handler=$registry->getTypeHandler($type);
+ if($handler===null && $property->getTypeHandler())
+ $handler = Prado::createComponent($type);
+ return $handler;
+ }
+
/**
* @param mixed object to obtain the property from.
diff --git a/framework/Data/SqlMap/TSqlMapManager.php b/framework/Data/SqlMap/TSqlMapManager.php index 37d50f5d..fa45b849 100644 --- a/framework/Data/SqlMap/TSqlMapManager.php +++ b/framework/Data/SqlMap/TSqlMapManager.php @@ -93,22 +93,6 @@ class TSqlMapManager extends TComponent }
/**
- * @param string configuration file.
- */
- public function setConfigFile($file)
- {
- $this->_configFile=$file;
- }
-
- /**
- * @return string configuration file.
- */
- public function getConfigFile()
- {
- return $this->_configFile;
- }
-
- /**
* @return TSqlMapGateway SqlMap gateway.
*/
public function getSqlmapGateway()
diff --git a/tests/simple_unit/SqlMap/ActiveRecordSqlMapTest.php b/tests/simple_unit/SqlMap/ActiveRecordSqlMapTest.php index 107985db..9dc944f5 100644 --- a/tests/simple_unit/SqlMap/ActiveRecordSqlMapTest.php +++ b/tests/simple_unit/SqlMap/ActiveRecordSqlMapTest.php @@ -1,4 +1,4 @@ -<?php +<?php
require_once(dirname(__FILE__).'/BaseCase.php');
@@ -14,7 +14,7 @@ class ActiveAccount extends TActiveRecord public $Account_Banner_Option;
public $Account_Cart_Option;
- private static $_tablename='accounts';
+ private static $_tablename='Accounts';
public static function finder()
{
@@ -81,5 +81,5 @@ class ActiveRecordSqlMapTest extends BaseCase $this->assertEqual($account1->{$prop}, $account2->{$prop});
}
}
- +
?>
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/InheritanceTest.php b/tests/simple_unit/SqlMap/InheritanceTest.php index 38542516..b0dd2dc9 100644 --- a/tests/simple_unit/SqlMap/InheritanceTest.php +++ b/tests/simple_unit/SqlMap/InheritanceTest.php @@ -20,7 +20,7 @@ class InheritanceTest extends BaseCase {
$list = $this->sqlmap->queryForList("GetAllDocument");
- $this->assertIdentical(6, count($list));
+ $this->assertEqual(6, count($list));
$book = $list[0];
$this->assertBook($book, 1, "The World of Null-A", 55);
@@ -45,7 +45,7 @@ class InheritanceTest extends BaseCase {
$list = $this->sqlmap->queryForList("GetTypedCollection");
- $this->assertIdentical(6, $list->getCount());
+ $this->assertEqual(6, $list->getCount());
$book = $list[0];
$this->assertBook($book, 1, "The World of Null-A", 55);
@@ -75,7 +75,7 @@ class InheritanceTest extends BaseCase $list = $this->sqlmap->queryForList("GetAllDocumentWithCustomTypeHandler");
- $this->assertIdentical(6, count($list));
+ $this->assertEqual(6, count($list));
$book = $list[0];
$this->assertBook($book, 1, "The World of Null-A", 55);
@@ -97,22 +97,22 @@ class InheritanceTest extends BaseCase function AssertDocument(Document $document, $id, $title)
{
- $this->assertIdentical($id, $document->getID());
- $this->assertIdentical($title, $document->getTitle());
+ $this->assertEqual($id, $document->getID());
+ $this->assertEqual($title, $document->getTitle());
}
function AssertBook(Book $book, $id, $title, $pageNumber)
{
- $this->assertIdentical($id, $book->getId());
- $this->assertIdentical($title, $book->getTitle());
- $this->assertIdentical($pageNumber, (int)$book->getPageNumber());
+ $this->assertEqual($id, $book->getId());
+ $this->assertEqual($title, $book->getTitle());
+ $this->assertEqual($pageNumber, (int)$book->getPageNumber());
}
function AssertNewspaper(Newspaper $news, $id, $title, $city)
{
- $this->assertIdentical($id, $news->getId());
- $this->assertIdentical($title, $news->getTitle());
- $this->assertIdentical($city, $news->getCity());
+ $this->assertEqual($id, $news->getId());
+ $this->assertEqual($title, $news->getTitle());
+ $this->assertEqual($city, $news->getCity());
}
}
diff --git a/tests/simple_unit/SqlMap/StatementTest.php b/tests/simple_unit/SqlMap/StatementTest.php index 671cae77..3ac0a3ab 100644 --- a/tests/simple_unit/SqlMap/StatementTest.php +++ b/tests/simple_unit/SqlMap/StatementTest.php @@ -41,8 +41,7 @@ class StatementTest extends BaseCase */
function testOpenConnection()
{
- $conn = $this->getConnection();
- $this->sqlmap->setDbConnection($conn);
+ $conn = $this->sqlmap->getDbConnection();
$conn->setActive(true);
$account= $this->sqlmap->QueryForObject("SelectWithProperty");
$conn->setActive(false);
@@ -644,7 +643,7 @@ class StatementTest extends BaseCase */
function testExecuteQueryForWithComplexJoined()
{
- $a = $this->sqlmap->QueryForObject("SelectComplexJoined",null);
+ $a = $this->sqlmap->QueryForObject("SelectComplexJoined");
$this->assertNotNull($a);
$this->assertNotNull($a->getB());
$this->assertNotNull($a->getB()->getC());
diff --git a/tests/simple_unit/SqlMap/common.php b/tests/simple_unit/SqlMap/common.php index 57685c31..aed11671 100644 --- a/tests/simple_unit/SqlMap/common.php +++ b/tests/simple_unit/SqlMap/common.php @@ -41,7 +41,7 @@ class DefaultScriptRunner {
$line = trim($line);
if(strlen($line) > 0)
- $connection->execute($line);
+ $connection->createCommand($line)->execute();
}
}
}
@@ -84,15 +84,16 @@ class SQLiteBaseTestConfig extends BaseTestConfig return new CopyFileScriptRunner($this->baseFile, $this->targetFile);
}
}
-/*
+
class MySQLBaseTestConfig extends BaseTestConfig
{
public function __construct()
{
- $this->_sqlmap = SQLMAP_TESTS.'/mysql.xml';
- $this->_connectionString = 'mysql://root:weizhuo01@localhost/IBatisNet';
+ $this->_sqlmapConfigFile = SQLMAP_TESTS.'/mysql.xml';
$this->_scriptDir = SQLMAP_TESTS.'/scripts/mysql/';
$this->_features = array('insert_id');
+ $dsn = 'mysql:host=localhost;dbname=sqlmap_test';
+ $this->_connection = new TDbConnection($dsn, 'test', 'test111');
}
}
@@ -106,7 +107,6 @@ class MSSQLBaseTestConfig extends BaseTestConfig $this->_features = array('insert_id');
}
}
-*/
class BaseTestConfig
{
@@ -143,9 +143,9 @@ class BaseTestConfig {
//change this to connection to a different database
- //return new MySQLBaseTestConfig();
+ return new MySQLBaseTestConfig();
- return new SQLiteBaseTestConfig();
+ //return new SQLiteBaseTestConfig();
//return new MSSQLBaseTestConfig();
}
diff --git a/tests/simple_unit/SqlMap/maps/MySql/Account.xml b/tests/simple_unit/SqlMap/maps/MySql/Account.xml index 146e0e90..77a1d433 100644 --- a/tests/simple_unit/SqlMap/maps/MySql/Account.xml +++ b/tests/simple_unit/SqlMap/maps/MySql/Account.xml @@ -70,7 +70,7 @@ <result property="FirstName" column="Account_FirstName"/>
<result property="LastName" column="Account_LastName"/>
<result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
- <result property="BannerOptions" column="Account_Banner_Option" dbType="Varchar" typeHandler="OuiNonBool"/>
+ <result property="BannerOptions" column="Account_Banner_Option" typeHandler="OuiNonBool"/>
<result property="CartOptions" column="Account_Cart_Option" typeHandler="HundredsBool"/>
</resultMap>
<resultMap id="indexed-account-result" class="Account">
@@ -551,9 +551,9 @@ <!-- accounts and orders -->
<select id="getAccountWithOrders" resultMap="Account-with-Orders">
- SELECT * FROM accounts
- LEFT JOIN orders ON
- accounts.account_id = orders.account_id
+ SELECT * FROM Accounts
+ LEFT JOIN Orders ON
+ Accounts.account_id = Orders.account_id
</select>
<resultMap id="Account-with-Orders" class="AccountWithOrders" groupBy="Account_Id">
diff --git a/tests/simple_unit/SqlMap/maps/MySql/ActiveRecord.xml b/tests/simple_unit/SqlMap/maps/MySql/ActiveRecord.xml new file mode 100644 index 00000000..1c48010f --- /dev/null +++ b/tests/simple_unit/SqlMap/maps/MySql/ActiveRecord.xml @@ -0,0 +1,16 @@ +<?xml version="1.0" encoding="UTF-8"?> +<sqlMap> + + <select id="GetActiveRecordAccounts" resultClass="ActiveAccount"> + select + Account_Id, + Account_FirstName, + Account_LastName, + Account_Email, + Account_Banner_Option, + Account_Cart_Option + from Accounts + order by Account_Id + </select> + +</sqlMap>
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/maps/MySql/DynamicAccount.xml b/tests/simple_unit/SqlMap/maps/MySql/DynamicAccount.xml index ff89720b..ee565158 100644 --- a/tests/simple_unit/SqlMap/maps/MySql/DynamicAccount.xml +++ b/tests/simple_unit/SqlMap/maps/MySql/DynamicAccount.xml @@ -49,7 +49,7 @@ </dynamic>
</select>
- <!-- IBATISNET-114: remapResults -->
+ <!-- IBATISNET-114: remapResults
<statement id="DynamicSqlOnColumnSelection"
parameterClass="Account"
resultClass="Account"
@@ -69,6 +69,7 @@ FROM
Accounts
</statement>
+ -->
<statement id="DynamicIsEqual"
parameterClass="string"
diff --git a/tests/simple_unit/SqlMap/maps/MySql/Order.xml b/tests/simple_unit/SqlMap/maps/MySql/Order.xml index 2e4eb616..ac3b7b84 100644 --- a/tests/simple_unit/SqlMap/maps/MySql/Order.xml +++ b/tests/simple_unit/SqlMap/maps/MySql/Order.xml @@ -76,15 +76,15 @@ </resultMap>
<resultMap id="order-with-types-result" class="Order">
- <result property="Id" column="Order_Id" dbType="Int32"/>
- <result property="Date" column="Order_Date" type="date" dbType="DateTime "/>
- <result property="CardExpiry" column="Order_CardExpiry" dbType="VarChar"/>
- <result property="CardType" column="Order_CardType" dbType="VarChar"/>
- <result property="CardNumber" column="Order_CardNumber" dbType="VarChar"/>
- <result property="Street" column="Order_Street" dbType="VarChar"/>
- <result property="City" column="Order_City" dbType="VarChar"/>
- <result property="Province" column="Order_Province" dbType="VarChar"/>
- <result property="PostalCode" column="Order_PostalCode" dbType="VarChar"/>
+ <result property="Id" column="Order_Id" />
+ <result property="Date" column="Order_Date" type="date" />
+ <result property="CardExpiry" column="Order_CardExpiry" />
+ <result property="CardType" column="Order_CardType" />
+ <result property="CardNumber" column="Order_CardNumber" />
+ <result property="Street" column="Order_Street" />
+ <result property="City" column="Order_City" />
+ <result property="Province" column="Order_Province" />
+ <result property="PostalCode" column="Order_PostalCode" />
</resultMap>
<resultMap id="lite-order-result-by-index" class="Order">
diff --git a/tests/simple_unit/SqlMap/mysql.xml b/tests/simple_unit/SqlMap/mysql.xml index d6e58b2c..11d6e4bc 100644 --- a/tests/simple_unit/SqlMap/mysql.xml +++ b/tests/simple_unit/SqlMap/mysql.xml @@ -1,21 +1,15 @@ <?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig>
- <properties resource="properties.config"/>
-
- <settings>
- <setting useStatementNamespaces="false"/>
- <setting cacheModelsEnabled="true"/>
- </settings>
-
- <!-- ==== Database configuration ========= -->
- <provider class="TAdodbProvider" >
- <!-- connection string set in common.php -->
- <datasource />
- </provider>
-
+ <properties>
+ <property name="selectKey" value="select @@IDENTITY as value" />
+ <property name="MyCategoryName" value="'Film'" />
+ <property name="accountName" value="'Joe'" />
+ </properties>
+
<typeHandlers>
- <typeHandler type="bool" dbType="Varchar" callback="OuiNonBool"/>
+ <typeHandler dbType="Varchar" class="OuiNonBool"/>
+ <typeHandler dbType="date" class="TDateTimeHandler" />
</typeHandlers>
<sqlMaps>
@@ -29,5 +23,7 @@ <sqlMap name="Other" resource="maps/MySql/Other.xml"/>
<sqlMap name="ResultClass" resource="maps/MySql/ResultClass.xml"/>
<sqlMap name="Document" resource="maps/MySql/Document.xml"/>
+ <sqlMap resource="maps/MySql/ActiveRecord.xml"/>
</sqlMaps>
+
</sqlMapConfig>
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/scripts/mysql/DataBase.sql b/tests/simple_unit/SqlMap/scripts/mysql/DataBase.sql index b6542cdb..2cf1e502 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/DataBase.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/DataBase.sql @@ -1,214 +1,356 @@ -use mysql;
-
-drop database IBatisNet;
-create database IBatisNet;
-
-drop database NHibernate;
-create database NHibernate;
-
-grant all privileges on IBatisNet.* to IBatisNet@'%' identified by 'test';
-grant all privileges on IBatisNet.* to IBatisNet@localhost identified by 'test';
-grant all privileges on IBatisNet.* to IBatisNet@localhost.localdomain identified by 'test';
-
-grant all privileges on NHibernate.* to NHibernate@'%' identified by 'test';
-grant all privileges on NHibernate.* to NHibernate@localhost identified by 'test';
-grant all privileges on NHibernate.* to NHibernate@localhost.localdomain identified by 'test';
-
-
-/*==============================================================*/
-/* Nom de la base : MYSQL */
-/* Nom de SGBD : MySQL 3.23 */
-/* Date de cr閍tion : 27/05/2004 20:51:40 */
-/*==============================================================*/
-
-use IBatisNet;
-
-drop table if exists Accounts;
-
-drop table if exists Categories;
-
-drop table if exists Enumerations;
-
-drop table if exists LineItems;
-
-drop table if exists Orders;
-
-drop table if exists Others;
-
-drop table if exists Documents;
-
-/*==============================================================*/
-/* Table : Accounts */
-/*==============================================================*/
-create table Accounts
-(
- Account_Id int not null,
- Account_FirstName varchar(32) not null,
- Account_LastName varchar(32) not null,
- Account_Email varchar(128),
- Account_Banner_Option varchar(255),
- Account_Cart_Option int,
- primary key (Account_Id)
-) TYPE=INNODB;
-
-/*==============================================================*/
-/* Table : Categories */
-/*==============================================================*/
-create table Categories
-(
- Category_Id int not null AUTO_INCREMENT,
- Category_Name varchar(32),
- Category_Guid varchar(36),
- primary key (Category_Id)
-) TYPE=INNODB;
-
-/*==============================================================*/
-/* Table : Enumerations */
-/*==============================================================*/
-create table Enumerations
-(
- Enum_Id int not null,
- Enum_Day int not null,
- Enum_Color int not null,
- Enum_Month int,
- primary key (Enum_Id)
-) TYPE=INNODB;
-
-/*==============================================================*/
-/* Table : LineItems */
-/*==============================================================*/
-create table LineItems
-(
- LineItem_Id int not null,
- Order_Id int not null,
- LineItem_Code varchar(32) not null,
- LineItem_Quantity int not null,
- LineItem_Price decimal(18,2),
- LineItem_Picture blob,
- primary key (Order_Id, LineItem_Id)
-) TYPE=INNODB;
-
-/*==============================================================*/
-/* Table : Orders */
-/*==============================================================*/
-create table Orders
-(
- Order_Id int not null,
- Account_Id int null,
- Order_Date datetime,
- Order_CardType varchar(32),
- Order_CardNumber varchar(32),
- Order_CardExpiry varchar(32),
- Order_Street varchar(32),
- Order_City varchar(32),
- Order_Province varchar(32),
- Order_PostalCode varchar(32),
- Order_FavouriteLineItem int,
- primary key (Order_Id)
-) TYPE=INNODB;
-
-/*==============================================================*/
-/* Table : Others */
-/*==============================================================*/
-create table Others
-(
- Other_Int int,
- Other_Long bigint,
- Other_Bit bit not null default 0,
- Other_String varchar(32) not null
-) TYPE=INNODB;
-
-CREATE TABLE F (
- ID varchar(50) NOT NULL ,
- F_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE E (
- ID varchar(50) NOT NULL ,
- E_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE D (
- ID varchar(50) NOT NULL ,
- D_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE C (
- ID varchar(50) NOT NULL ,
- C_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-
-CREATE TABLE B (
- ID varchar(50) NOT NULL ,
- C_ID varchar(50) NULL ,
- D_ID varchar(50) NULL ,
- B_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-ALTER TABLE B ADD CONSTRAINT FK_B_C FOREIGN KEY FK_B_C (C_ID)
- REFERENCES C (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_B_D FOREIGN KEY FK_B_D (D_ID)
- REFERENCES D (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT;
-
-CREATE TABLE A (
- ID varchar(50) NOT NULL ,
- B_ID varchar(50) NULL ,
- E_ID varchar(50) NULL ,
- F_ID varchar(50) NULL ,
- A_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-ALTER TABLE A ADD CONSTRAINT FK_A_B FOREIGN KEY FK_A_B (B_ID)
- REFERENCES B (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_A_E FOREIGN KEY FK_A_E (E_ID)
- REFERENCES E (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_A_F FOREIGN KEY FK_A_F (F_ID)
- REFERENCES F (ID)
- ON DELETE RESTRICT;
-
-/*==============================================================*/
-/* Table : Documents */
-/*==============================================================*/
-create table Documents
-(
- Document_Id int not null,
- Document_Title varchar(32),
- Document_Type varchar(32),
- Document_PageNumber int,
- Document_City varchar(32),
- primary key (DOCUMENT_ID)
-) TYPE=INNODB;
-
-
-
-use NHibernate;
-
-drop table if exists Users;
-
-/*==============================================================*/
-/* Table : Users */
-/*==============================================================*/
-create table Users
-(
- LogonId varchar(20) not null default '0',
- Name varchar(40) default null,
- Password varchar(20) default null,
- EmailAddress varchar(40) default null,
- LastLogon datetime default null,
- primary key (LogonId)
-) TYPE=INNODB;
+--
+-- Table structure for table `A`
+--
+
+DROP TABLE IF EXISTS `A`;
+CREATE TABLE `A` (
+ `ID` varchar(50) NOT NULL,
+ `B_ID` varchar(50) default NULL,
+ `E_ID` varchar(50) default NULL,
+ `F_ID` varchar(50) default NULL,
+ `A_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`),
+ KEY `FK_A_B` (`B_ID`),
+ KEY `FK_A_E` (`E_ID`),
+ KEY `FK_A_F` (`F_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `A`
+--
+
+INSERT INTO `A` (`ID`, `B_ID`, `E_ID`, `F_ID`, `A_Libelle`) VALUES ('a', 'b', 'e', NULL, 'aaa');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Accounts`
+--
+
+DROP TABLE IF EXISTS `Accounts`;
+CREATE TABLE `Accounts` (
+ `Account_Id` int(11) NOT NULL,
+ `Account_FirstName` varchar(32) NOT NULL,
+ `Account_LastName` varchar(32) NOT NULL,
+ `Account_Email` varchar(128) default NULL,
+ `Account_Banner_Option` varchar(255) default NULL,
+ `Account_Cart_Option` int(11) default NULL,
+ PRIMARY KEY (`Account_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Accounts`
+--
+
+INSERT INTO `Accounts` (`Account_Id`, `Account_FirstName`, `Account_LastName`, `Account_Email`, `Account_Banner_Option`, `Account_Cart_Option`) VALUES (1, 'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200),
+(2, 'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200),
+(3, 'William', 'Dalton', NULL, 'Non', 100),
+(4, 'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100),
+(5, 'Gilles', 'Bayon', NULL, 'Oui', 100);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `B`
+--
+
+DROP TABLE IF EXISTS `B`;
+CREATE TABLE `B` (
+ `ID` varchar(50) NOT NULL,
+ `C_ID` varchar(50) default NULL,
+ `D_ID` varchar(50) default NULL,
+ `B_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`),
+ KEY `FK_B_C` (`C_ID`),
+ KEY `FK_B_D` (`D_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `B`
+--
+
+INSERT INTO `B` (`ID`, `C_ID`, `D_ID`, `B_Libelle`) VALUES ('b', 'c', NULL, 'bbb');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `C`
+--
+
+DROP TABLE IF EXISTS `C`;
+CREATE TABLE `C` (
+ `ID` varchar(50) NOT NULL,
+ `C_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `C`
+--
+
+INSERT INTO `C` (`ID`, `C_Libelle`) VALUES ('c', 'ccc');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Categories`
+--
+
+DROP TABLE IF EXISTS `Categories`;
+CREATE TABLE `Categories` (
+ `Category_Id` int(11) NOT NULL auto_increment,
+ `Category_Name` varchar(32) default NULL,
+ `Category_Guid` varchar(36) default NULL,
+ PRIMARY KEY (`Category_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
+
+--
+-- Dumping data for table `Categories`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `D`
+--
+
+DROP TABLE IF EXISTS `D`;
+CREATE TABLE `D` (
+ `ID` varchar(50) NOT NULL,
+ `D_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `D`
+--
+
+INSERT INTO `D` (`ID`, `D_Libelle`) VALUES ('d', 'ddd');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Documents`
+--
+
+DROP TABLE IF EXISTS `Documents`;
+CREATE TABLE `Documents` (
+ `Document_Id` int(11) NOT NULL,
+ `Document_Title` varchar(32) default NULL,
+ `Document_Type` varchar(32) default NULL,
+ `Document_PageNumber` int(11) default NULL,
+ `Document_City` varchar(32) default NULL,
+ PRIMARY KEY (`Document_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Documents`
+--
+
+INSERT INTO `Documents` (`Document_Id`, `Document_Title`, `Document_Type`, `Document_PageNumber`, `Document_City`) VALUES (1, 'The World of Null-A', 'Book', 55, NULL),
+(2, 'Le Progres de Lyon', 'Newspaper', NULL, 'Lyon'),
+(3, 'Lord of the Rings', 'Book', 3587, NULL),
+(4, 'Le Canard enchaine', 'Tabloid', NULL, 'Paris'),
+(5, 'Le Monde', 'Broadsheet', NULL, 'Paris'),
+(6, 'Foundation', 'Monograph', 557, NULL);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `E`
+--
+
+DROP TABLE IF EXISTS `E`;
+CREATE TABLE `E` (
+ `ID` varchar(50) NOT NULL,
+ `E_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `E`
+--
+
+INSERT INTO `E` (`ID`, `E_Libelle`) VALUES ('e', 'eee');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Enumerations`
+--
+
+DROP TABLE IF EXISTS `Enumerations`;
+CREATE TABLE `Enumerations` (
+ `Enum_Id` int(11) NOT NULL,
+ `Enum_Day` int(11) NOT NULL,
+ `Enum_Color` int(11) NOT NULL,
+ `Enum_Month` int(11) default NULL,
+ PRIMARY KEY (`Enum_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Enumerations`
+--
+
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `F`
+--
+
+DROP TABLE IF EXISTS `F`;
+CREATE TABLE `F` (
+ `ID` varchar(50) NOT NULL,
+ `F_Libelle` varchar(50) default NULL,
+ PRIMARY KEY (`ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `F`
+--
+
+INSERT INTO `F` (`ID`, `F_Libelle`) VALUES ('f', 'fff');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `LineItems`
+--
+
+DROP TABLE IF EXISTS `LineItems`;
+CREATE TABLE `LineItems` (
+ `LineItem_Id` int(11) NOT NULL,
+ `Order_Id` int(11) NOT NULL,
+ `LineItem_Code` varchar(32) NOT NULL,
+ `LineItem_Quantity` int(11) NOT NULL,
+ `LineItem_Price` decimal(18,2) default NULL,
+ `LineItem_Picture` blob,
+ PRIMARY KEY (`Order_Id`,`LineItem_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `LineItems`
+--
+
+INSERT INTO `LineItems` (`LineItem_Id`, `Order_Id`, `LineItem_Code`, `LineItem_Quantity`, `LineItem_Price`, `LineItem_Picture`) VALUES (1, 1, 'ESM-48', 8, 87.60, NULL),
+(2, 1, 'ESM-23', 1, 55.40, NULL),
+(1, 2, 'DSM-37', 4, 7.80, NULL),
+(2, 2, 'FSM-12', 2, 55.78, NULL),
+(1, 3, 'DSM-59', 3, 5.70, NULL),
+(2, 3, 'DSM-53', 3, 98.78, NULL),
+(1, 4, 'RSM-57', 2, 78.90, NULL),
+(2, 4, 'XSM-78', 9, 2.34, NULL),
+(1, 5, 'ESM-48', 3, 43.87, NULL),
+(2, 5, 'WSM-98', 7, 5.40, NULL),
+(1, 6, 'QSM-39', 9, 12.12, NULL),
+(2, 6, 'ASM-45', 6, 78.77, NULL),
+(1, 7, 'WSM-27', 7, 52.10, NULL),
+(2, 7, 'ESM-23', 2, 123.34, NULL),
+(1, 8, 'DSM-16', 4, 41.30, NULL),
+(2, 8, 'GSM-65', 1, 2.20, NULL),
+(1, 9, 'DSM-78', 2, 45.40, NULL),
+(2, 9, 'TSM-12', 2, 32.12, NULL),
+(1, 10, 'ESM-34', 1, 45.43, NULL),
+(2, 10, 'QSM-98', 8, 8.40, NULL);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Orders`
+--
+
+DROP TABLE IF EXISTS `Orders`;
+CREATE TABLE `Orders` (
+ `Order_Id` int(11) NOT NULL,
+ `Account_Id` int(11) default NULL,
+ `Order_Date` datetime default NULL,
+ `Order_CardType` varchar(32) default NULL,
+ `Order_CardNumber` varchar(32) default NULL,
+ `Order_CardExpiry` varchar(32) default NULL,
+ `Order_Street` varchar(32) default NULL,
+ `Order_City` varchar(32) default NULL,
+ `Order_Province` varchar(32) default NULL,
+ `Order_PostalCode` varchar(32) default NULL,
+ `Order_FavouriteLineItem` int(11) default NULL,
+ PRIMARY KEY (`Order_Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Orders`
+--
+
+INSERT INTO `Orders` (`Order_Id`, `Account_Id`, `Order_Date`, `Order_CardType`, `Order_CardNumber`, `Order_CardExpiry`, `Order_Street`, `Order_City`, `Order_Province`, `Order_PostalCode`, `Order_FavouriteLineItem`) VALUES (1, 1, '2003-02-15 08:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4', 2),
+(2, 4, '2003-02-15 08:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4', 1),
+(3, 3, '2003-02-15 08:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4', 2),
+(4, 2, '2003-02-15 08:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4', 1),
+(5, 5, '2003-02-15 08:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4', 2),
+(6, 5, '2003-02-15 08:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4', 1),
+(7, 4, '2003-02-15 08:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4', 2),
+(8, 3, '2003-02-15 08:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4', 1),
+(9, 2, '2003-02-15 08:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4', 2),
+(10, 1, '2003-02-15 08:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4', 1),
+(11, NULL, '2003-02-15 08:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY', 1);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Others`
+--
+
+DROP TABLE IF EXISTS `Others`;
+CREATE TABLE `Others` (
+ `Other_Int` int(11) default NULL,
+ `Other_Long` bigint(20) default NULL,
+ `Other_Bit` bit(1) NOT NULL default '\0',
+ `Other_String` varchar(32) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Others`
+--
+
+INSERT INTO `Others` (`Other_Int`, `Other_Long`, `Other_Bit`, `Other_String`) VALUES (1, 8888888, '\0', 'Oui'),
+(2, 9999999999, '', 'Non'),
+(99, 1966, '', 'Non');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `Users`
+--
+
+DROP TABLE IF EXISTS `Users`;
+CREATE TABLE `Users` (
+ `LogonId` varchar(20) NOT NULL default '0',
+ `Name` varchar(40) default NULL,
+ `Password` varchar(20) default NULL,
+ `EmailAddress` varchar(40) default NULL,
+ `LastLogon` datetime default NULL,
+ PRIMARY KEY (`LogonId`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `Users`
+--
+
+
+--
+-- Constraints for dumped tables
+--
+
+--
+-- Constraints for table `A`
+--
+ALTER TABLE `A`
+ ADD CONSTRAINT `FK_A_B` FOREIGN KEY (`B_ID`) REFERENCES `B` (`ID`),
+ ADD CONSTRAINT `FK_A_E` FOREIGN KEY (`E_ID`) REFERENCES `E` (`ID`),
+ ADD CONSTRAINT `FK_A_F` FOREIGN KEY (`F_ID`) REFERENCES `F` (`ID`);
+
+--
+-- Constraints for table `B`
+--
+ALTER TABLE `B`
+ ADD CONSTRAINT `FK_B_C` FOREIGN KEY (`C_ID`) REFERENCES `C` (`ID`),
+ ADD CONSTRAINT `FK_B_D` FOREIGN KEY (`D_ID`) REFERENCES `D` (`ID`);
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/account-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/account-init.sql index 51b315f4..afe8dab1 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/account-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/account-init.sql @@ -1,17 +1,4 @@ -use IBatisNet;
-
-drop table if exists Accounts;
-
-create table Accounts
-(
- Account_Id int not null,
- Account_FirstName varchar(32) not null,
- Account_LastName varchar(32) not null,
- Account_Email varchar(128),
- Account_Banner_Option varchar(255),
- Account_Cart_Option int,
- primary key (Account_Id)
-) TYPE=INNODB;
+TRUNCATE `Accounts`;
INSERT INTO Accounts VALUES(1,'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200);
INSERT INTO Accounts VALUES(2,'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200);
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/account-procedure.sql b/tests/simple_unit/SqlMap/scripts/mysql/account-procedure.sql index 03b65b13..d3f5a12f 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/account-procedure.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/account-procedure.sql @@ -1,2 +1 @@ -use IBatisNet;
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/scripts/mysql/category-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/category-init.sql index 2f50ff24..2030181d 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/category-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/category-init.sql @@ -1,12 +1 @@ -
-use IBatisNet;
-
-drop table if exists Categories;
-
-create table Categories
-(
- Category_Id int not null AUTO_INCREMENT,
- Category_Name varchar(32),
- Category_Guid varchar(36),
- primary key (Category_Id)
-) TYPE=INNODB;
+TRUNCATE `Categories`;
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/category-procedure.sql b/tests/simple_unit/SqlMap/scripts/mysql/category-procedure.sql index 03b65b13..e69de29b 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/category-procedure.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/category-procedure.sql @@ -1,2 +0,0 @@ -
-use IBatisNet;
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/scripts/mysql/documents-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/documents-init.sql index c254ae4d..5a1cdb2a 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/documents-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/documents-init.sql @@ -1,16 +1,4 @@ -use IBatisNet;
-
-drop table if exists Documents;
-
-create table Documents
-(
- Document_Id int not null,
- Document_Title varchar(32),
- Document_Type varchar(32),
- Document_PageNumber int,
- Document_City varchar(32),
- primary key (DOCUMENT_ID)
-) TYPE=INNODB;
+TRUNCATE `Documents`;
INSERT INTO Documents VALUES (1, 'The World of Null-A', 'Book', 55, null);
INSERT INTO Documents VALUES (2, 'Le Progres de Lyon', 'Newspaper', null , 'Lyon');
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/enumeration-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/enumeration-init.sql index a194b636..bc296957 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/enumeration-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/enumeration-init.sql @@ -1,16 +1,4 @@ -
-use IBatisNet;
-
-drop table if exists Enumerations;
-
-create table Enumerations
-(
- Enum_Id int not null,
- Enum_Day int not null,
- Enum_Color int not null,
- Enum_Month int,
- primary key (Enum_Id)
-) TYPE=INNODB;
+TRUNCATE `Enumerations`;
INSERT INTO Enumerations VALUES(1, 1, 1, 128);
INSERT INTO Enumerations VALUES(2, 2, 2, 2048);
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/line-item-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/line-item-init.sql index cb800835..b0f7e424 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/line-item-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/line-item-init.sql @@ -1,18 +1,5 @@ -use IBatisNet;
-
-drop table if exists LineItems;
-
-create table LineItems
-(
- LineItem_Id int not null,
- Order_Id int not null,
- LineItem_Code varchar(32) not null,
- LineItem_Quantity int not null,
- LineItem_Price decimal(18,2),
- LineItem_Picture blob,
- primary key (Order_Id, LineItem_Id)
-) TYPE=INNODB;
+TRUNCATE `LineItems`;
INSERT INTO LineItems VALUES (1, 10, 'ESM-34', 1, 45.43, null);
INSERT INTO LineItems VALUES (2, 10, 'QSM-98', 8, 8.40, null);
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/order-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/order-init.sql index e83a4be3..5c23081f 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/order-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/order-init.sql @@ -1,20 +1,5 @@ -drop table if exists Orders;
+TRUNCATE `Orders`;
-create table Orders
-(
- Order_Id int not null,
- Account_Id int null,
- Order_Date datetime,
- Order_CardType varchar(32),
- Order_CardNumber varchar(32),
- Order_CardExpiry varchar(32),
- Order_Street varchar(32),
- Order_City varchar(32),
- Order_Province varchar(32),
- Order_PostalCode varchar(32),
- Order_FavouriteLineItem int,
- primary key (Order_Id)
-) TYPE=INNODB;
INSERT INTO Orders VALUES (1, 1, '2003-02-15 8:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4',2);
INSERT INTO Orders VALUES (2, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4',1);
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/other-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/other-init.sql index 0281527c..e3ed6b32 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/other-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/other-init.sql @@ -1,84 +1,11 @@ -use IBatisNet;
-
-drop table if exists Others;
-drop table if exists A;
-drop table if exists B;
-drop table if exists C;
-drop table if exists D;
-drop table if exists E;
-drop table if exists F;
-
-create table Others
-(
- Other_Int int,
- Other_Long bigint,
- Other_Bit bit not null default 0,
- Other_String varchar(32) not null
-) TYPE=INNODB;
-
-CREATE TABLE F (
- ID varchar(50) NOT NULL ,
- F_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE E (
- ID varchar(50) NOT NULL ,
- E_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE D (
- ID varchar(50) NOT NULL ,
- D_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-CREATE TABLE C (
- ID varchar(50) NOT NULL ,
- C_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-
-CREATE TABLE B (
- ID varchar(50) NOT NULL ,
- C_ID varchar(50) NULL ,
- D_ID varchar(50) NULL ,
- B_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-ALTER TABLE B ADD CONSTRAINT FK_B_C FOREIGN KEY FK_B_C (C_ID)
- REFERENCES C (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_B_D FOREIGN KEY FK_B_D (D_ID)
- REFERENCES D (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT;
-
-CREATE TABLE A (
- ID varchar(50) NOT NULL ,
- B_ID varchar(50) NULL ,
- E_ID varchar(50) NULL ,
- F_ID varchar(50) NULL ,
- A_Libelle varchar(50) NULL ,
- primary key (ID)
-) TYPE=INNODB;
-
-ALTER TABLE A ADD CONSTRAINT FK_A_B FOREIGN KEY FK_A_B (B_ID)
- REFERENCES B (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_A_E FOREIGN KEY FK_A_E (E_ID)
- REFERENCES E (ID)
- ON DELETE RESTRICT
- ON UPDATE RESTRICT,
- ADD CONSTRAINT FK_A_F FOREIGN KEY FK_A_F (F_ID)
- REFERENCES F (ID)
- ON DELETE RESTRICT;
+TRUNCATE `Others`;
+TRUNCATE `A`;
+TRUNCATE `B`;
+TRUNCATE `C`;
+TRUNCATE `D`;
+TRUNCATE `E`;
+TRUNCATE `F`;
INSERT INTO Others VALUES(1, 8888888, 0, 'Oui');
INSERT INTO Others VALUES(2, 9999999999, 1, 'Non');
diff --git a/tests/simple_unit/SqlMap/scripts/mysql/swap-procedure.sql b/tests/simple_unit/SqlMap/scripts/mysql/swap-procedure.sql index 03b65b13..e69de29b 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/swap-procedure.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/swap-procedure.sql @@ -1,2 +0,0 @@ -
-use IBatisNet;
\ No newline at end of file diff --git a/tests/simple_unit/SqlMap/scripts/mysql/user-init.sql b/tests/simple_unit/SqlMap/scripts/mysql/user-init.sql index c124fc2b..0bcd6a65 100644 --- a/tests/simple_unit/SqlMap/scripts/mysql/user-init.sql +++ b/tests/simple_unit/SqlMap/scripts/mysql/user-init.sql @@ -1,14 +1,2 @@ -use NHibernate;
-
-drop table if exists Users;
-
-create table Users
-(
- LogonId varchar(20) not null default '0',
- Name varchar(40) default null,
- Password varchar(20) default null,
- EmailAddress varchar(40) default null,
- LastLogon datetime default null,
- primary key (LogonId)
-) TYPE=INNODB;
+TRUNCATE `users`;
|
