From f6a5e7589396854e10e023c25237b47e512ff047 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Apr 2006 11:23:56 +0000 Subject: Adding SQLMap unit tests. Allow sqlmap to use Prado's caching module to cache records. --- tests/unit/SQLMap/BaseTest.php | 260 +++++ tests/unit/SQLMap/CacheTest.php | 164 +++ tests/unit/SQLMap/ConfigureTest.php | 20 + tests/unit/SQLMap/ConnectionTest.php | 26 + tests/unit/SQLMap/DelegateTest.php | 64 ++ tests/unit/SQLMap/GroupByTest.php | 43 + tests/unit/SQLMap/InheritanceTest.php | 146 +++ tests/unit/SQLMap/ParameterMapTest.php | 249 +++++ tests/unit/SQLMap/PropertyAccessTest.php | 78 ++ tests/unit/SQLMap/ResultClassTest.php | 282 +++++ tests/unit/SQLMap/ResultMapTest.php | 280 +++++ tests/unit/SQLMap/SelectKeyTest.php | 120 +++ tests/unit/SQLMap/SqlMapCacheTest.php | 77 ++ tests/unit/SQLMap/StatementTest.php | 1134 ++++++++++++++++++++ tests/unit/SQLMap/TAdodbConnectionTestCase.php | 63 ++ tests/unit/SQLMap/common.php | 152 +++ tests/unit/SQLMap/domain/A.php | 27 + tests/unit/SQLMap/domain/Account.php | 36 + tests/unit/SQLMap/domain/AccountBis.php | 13 + tests/unit/SQLMap/domain/AccountCollection.php | 17 + tests/unit/SQLMap/domain/B.php | 23 + tests/unit/SQLMap/domain/Book.php | 11 + tests/unit/SQLMap/domain/C.php | 15 + tests/unit/SQLMap/domain/Category.php | 19 + tests/unit/SQLMap/domain/Complex.php | 11 + tests/unit/SQLMap/domain/D.php | 16 + tests/unit/SQLMap/domain/Document.php | 16 + tests/unit/SQLMap/domain/DocumentCollection.php | 8 + tests/unit/SQLMap/domain/E.php | 16 + tests/unit/SQLMap/domain/F.php | 16 + tests/unit/SQLMap/domain/LineItem.php | 32 + tests/unit/SQLMap/domain/LineItemCollection.php | 8 + tests/unit/SQLMap/domain/Newspaper.php | 12 + tests/unit/SQLMap/domain/Order.php | 71 ++ tests/unit/SQLMap/domain/Other.php | 23 + tests/unit/SQLMap/domain/Sample.php | 55 + tests/unit/SQLMap/domain/Search.php | 23 + tests/unit/SQLMap/domain/User.php | 27 + tests/unit/SQLMap/maps/MySql/Account.xml | 624 +++++++++++ tests/unit/SQLMap/maps/MySql/Category.xml | 162 +++ tests/unit/SQLMap/maps/MySql/Complex.xml | 23 + tests/unit/SQLMap/maps/MySql/Document.xml | 53 + tests/unit/SQLMap/maps/MySql/DynamicAccount.xml | 448 ++++++++ tests/unit/SQLMap/maps/MySql/Enumeration.xml | 59 + tests/unit/SQLMap/maps/MySql/LineItem.xml | 188 ++++ tests/unit/SQLMap/maps/MySql/Order.xml | 468 ++++++++ tests/unit/SQLMap/maps/MySql/Other.xml | 170 +++ tests/unit/SQLMap/maps/MySql/ResultClass.xml | 130 +++ tests/unit/SQLMap/maps/mssql/Account.xml | 606 +++++++++++ tests/unit/SQLMap/maps/mssql/Category.xml | 171 +++ tests/unit/SQLMap/maps/mssql/Complex.xml | 21 + tests/unit/SQLMap/maps/mssql/Document.xml | 53 + tests/unit/SQLMap/maps/mssql/DynamicAccount.xml | 438 ++++++++ tests/unit/SQLMap/maps/mssql/Enumeration.xml | 47 + tests/unit/SQLMap/maps/mssql/LineItem.xml | 182 ++++ tests/unit/SQLMap/maps/mssql/Order.xml | 475 ++++++++ tests/unit/SQLMap/maps/mssql/Other.xml | 171 +++ tests/unit/SQLMap/maps/mssql/ResultClass.xml | 125 +++ tests/unit/SQLMap/maps/sqlite/Account.xml | 641 +++++++++++ tests/unit/SQLMap/maps/sqlite/Category.xml | 162 +++ tests/unit/SQLMap/maps/sqlite/Complex.xml | 23 + tests/unit/SQLMap/maps/sqlite/Document.xml | 53 + tests/unit/SQLMap/maps/sqlite/DynamicAccount.xml | 448 ++++++++ tests/unit/SQLMap/maps/sqlite/Enumeration.xml | 59 + tests/unit/SQLMap/maps/sqlite/LineItem.xml | 188 ++++ tests/unit/SQLMap/maps/sqlite/Order.xml | 507 +++++++++ tests/unit/SQLMap/maps/sqlite/Other.xml | 170 +++ tests/unit/SQLMap/maps/sqlite/ResultClass.xml | 130 +++ tests/unit/SQLMap/mssql.xml | 33 + tests/unit/SQLMap/mysql.xml | 33 + tests/unit/SQLMap/properties.config | 8 + tests/unit/SQLMap/resources/data.db | Bin 0 -> 5120 bytes tests/unit/SQLMap/resources/person.xml | 26 + tests/unit/SQLMap/resources/sqlmap.xml | 12 + tests/unit/SQLMap/resources/test.db | Bin 0 -> 5120 bytes tests/unit/SQLMap/resources/tests.db | 0 tests/unit/SQLMap/scripts/mssql/DBCreation.sql | 89 ++ tests/unit/SQLMap/scripts/mssql/DataBase.sql | 179 +++ .../SQLMap/scripts/mssql/README-embed-param.txt | 8 + tests/unit/SQLMap/scripts/mssql/account-init.sql | 47 + .../SQLMap/scripts/mssql/account-procedure.sql | 12 + tests/unit/SQLMap/scripts/mssql/category-init.sql | 17 + .../SQLMap/scripts/mssql/category-procedure.sql | 10 + tests/unit/SQLMap/scripts/mssql/documents-init.sql | 34 + .../scripts/mssql/embed-param-setup-init.sql | 94 ++ .../SQLMap/scripts/mssql/embed-param-test-init.sql | 32 + .../unit/SQLMap/scripts/mssql/enumeration-init.sql | 30 + tests/unit/SQLMap/scripts/mssql/line-item-init.sql | 53 + .../SQLMap/scripts/mssql/more-account-records.sql | 11 + tests/unit/SQLMap/scripts/mssql/order-init.sql | 54 + tests/unit/SQLMap/scripts/mssql/other-init.sql | 145 +++ .../unit/SQLMap/scripts/mssql/ps_SelectAccount.sql | 10 + tests/unit/SQLMap/scripts/mssql/swap-procedure.sql | 34 + tests/unit/SQLMap/scripts/mssql/user-init.sql | 17 + tests/unit/SQLMap/scripts/mysql/DataBase.sql | 214 ++++ tests/unit/SQLMap/scripts/mysql/account-init.sql | 20 + .../SQLMap/scripts/mysql/account-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/category-init.sql | 12 + .../SQLMap/scripts/mysql/category-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/documents-init.sql | 20 + .../unit/SQLMap/scripts/mysql/enumeration-init.sql | 18 + tests/unit/SQLMap/scripts/mysql/line-item-init.sql | 37 + .../SQLMap/scripts/mysql/more-account-records.sql | 7 + tests/unit/SQLMap/scripts/mysql/order-init.sql | 30 + tests/unit/SQLMap/scripts/mysql/other-init.sql | 91 ++ tests/unit/SQLMap/scripts/mysql/swap-procedure.sql | 2 + tests/unit/SQLMap/scripts/mysql/user-init.sql | 14 + tests/unit/SQLMap/scripts/sqlite/database.sql | 242 +++++ tests/unit/SQLMap/sqlite.xml | 32 + tests/unit/SQLMap/sqlite/backup.db | Bin 0 -> 32768 bytes tests/unit/SQLMap/sqlite/tests.db | Bin 0 -> 32768 bytes 111 files changed, 12379 insertions(+) create mode 100644 tests/unit/SQLMap/BaseTest.php create mode 100644 tests/unit/SQLMap/CacheTest.php create mode 100644 tests/unit/SQLMap/ConfigureTest.php create mode 100644 tests/unit/SQLMap/ConnectionTest.php create mode 100644 tests/unit/SQLMap/DelegateTest.php create mode 100644 tests/unit/SQLMap/GroupByTest.php create mode 100644 tests/unit/SQLMap/InheritanceTest.php create mode 100644 tests/unit/SQLMap/ParameterMapTest.php create mode 100644 tests/unit/SQLMap/PropertyAccessTest.php create mode 100644 tests/unit/SQLMap/ResultClassTest.php create mode 100644 tests/unit/SQLMap/ResultMapTest.php create mode 100644 tests/unit/SQLMap/SelectKeyTest.php create mode 100644 tests/unit/SQLMap/SqlMapCacheTest.php create mode 100644 tests/unit/SQLMap/StatementTest.php create mode 100644 tests/unit/SQLMap/TAdodbConnectionTestCase.php create mode 100644 tests/unit/SQLMap/common.php create mode 100644 tests/unit/SQLMap/domain/A.php create mode 100644 tests/unit/SQLMap/domain/Account.php create mode 100644 tests/unit/SQLMap/domain/AccountBis.php create mode 100644 tests/unit/SQLMap/domain/AccountCollection.php create mode 100644 tests/unit/SQLMap/domain/B.php create mode 100644 tests/unit/SQLMap/domain/Book.php create mode 100644 tests/unit/SQLMap/domain/C.php create mode 100644 tests/unit/SQLMap/domain/Category.php create mode 100644 tests/unit/SQLMap/domain/Complex.php create mode 100644 tests/unit/SQLMap/domain/D.php create mode 100644 tests/unit/SQLMap/domain/Document.php create mode 100644 tests/unit/SQLMap/domain/DocumentCollection.php create mode 100644 tests/unit/SQLMap/domain/E.php create mode 100644 tests/unit/SQLMap/domain/F.php create mode 100644 tests/unit/SQLMap/domain/LineItem.php create mode 100644 tests/unit/SQLMap/domain/LineItemCollection.php create mode 100644 tests/unit/SQLMap/domain/Newspaper.php create mode 100644 tests/unit/SQLMap/domain/Order.php create mode 100644 tests/unit/SQLMap/domain/Other.php create mode 100644 tests/unit/SQLMap/domain/Sample.php create mode 100644 tests/unit/SQLMap/domain/Search.php create mode 100644 tests/unit/SQLMap/domain/User.php create mode 100644 tests/unit/SQLMap/maps/MySql/Account.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Category.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Complex.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Document.xml create mode 100644 tests/unit/SQLMap/maps/MySql/DynamicAccount.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Enumeration.xml create mode 100644 tests/unit/SQLMap/maps/MySql/LineItem.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Order.xml create mode 100644 tests/unit/SQLMap/maps/MySql/Other.xml create mode 100644 tests/unit/SQLMap/maps/MySql/ResultClass.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Account.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Category.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Complex.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Document.xml create mode 100644 tests/unit/SQLMap/maps/mssql/DynamicAccount.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Enumeration.xml create mode 100644 tests/unit/SQLMap/maps/mssql/LineItem.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Order.xml create mode 100644 tests/unit/SQLMap/maps/mssql/Other.xml create mode 100644 tests/unit/SQLMap/maps/mssql/ResultClass.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Account.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Category.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Complex.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Document.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/DynamicAccount.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Enumeration.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/LineItem.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Order.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/Other.xml create mode 100644 tests/unit/SQLMap/maps/sqlite/ResultClass.xml create mode 100644 tests/unit/SQLMap/mssql.xml create mode 100644 tests/unit/SQLMap/mysql.xml create mode 100644 tests/unit/SQLMap/properties.config create mode 100644 tests/unit/SQLMap/resources/data.db create mode 100644 tests/unit/SQLMap/resources/person.xml create mode 100644 tests/unit/SQLMap/resources/sqlmap.xml create mode 100644 tests/unit/SQLMap/resources/test.db create mode 100644 tests/unit/SQLMap/resources/tests.db create mode 100644 tests/unit/SQLMap/scripts/mssql/DBCreation.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/DataBase.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/README-embed-param.txt create mode 100644 tests/unit/SQLMap/scripts/mssql/account-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/account-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/category-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/category-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/documents-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/enumeration-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/line-item-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/more-account-records.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/order-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/other-init.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/swap-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mssql/user-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/DataBase.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/account-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/account-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/category-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/category-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/documents-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/enumeration-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/line-item-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/more-account-records.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/order-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/other-init.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/swap-procedure.sql create mode 100644 tests/unit/SQLMap/scripts/mysql/user-init.sql create mode 100644 tests/unit/SQLMap/scripts/sqlite/database.sql create mode 100644 tests/unit/SQLMap/sqlite.xml create mode 100644 tests/unit/SQLMap/sqlite/backup.db create mode 100644 tests/unit/SQLMap/sqlite/tests.db (limited to 'tests/unit') diff --git a/tests/unit/SQLMap/BaseTest.php b/tests/unit/SQLMap/BaseTest.php new file mode 100644 index 00000000..ebff9c6d --- /dev/null +++ b/tests/unit/SQLMap/BaseTest.php @@ -0,0 +1,260 @@ +assertTrue(true); + } + + public function testCase2() + { + $this->assertTrue(true); + } + + public function __construct() + { + parent::__construct(); + $this->config = BaseTestConfig::createConfigInstance(); + $this->ScriptDirectory = $this->config->getScriptDir(); + } + + public function hasSupportFor($feature) + { + return $this->config->hasFeature($feature); + } + + public function __destruct() + { + if(!is_null($this->mapper)) + $this->mapper->cacheConfiguration(); + } + + function getConnection() + { + if(is_null($this->connection)) + $this->connection = new TAdodbConnection($this->config->getConnectionString()); + $this->connection->open(); + return $this->connection; + } + + protected static $ScriptDirectory; + + /** + * Initialize an sqlMap + */ + protected function initSqlMap() + { + $filename = $this->config->getSqlMapConfigFile(); + $this->mapper = new TSQLMapClient; + $this->sqlmap = $this->mapper->configure($filename,true); + $this->sqlmap->getTypeHandlerFactory()->register('date', new TDateTimeHandler); + $this->sqlmap->getDataProvider()->setConnectionString($this->config->getConnectionString()); + } + + /** + * Run a sql batch for the datasource. + */ + protected function initScript($script) + { + $runner = $this->config->getScriptRunner(); + $runner->runScript($this->getConnection(), $this->ScriptDirectory.$script); + } + + /** + * Create a new account with id = 6 + */ + protected function NewAccount6() + { + $account = new Account(); + $account->setID(6); + $account->setFirstName('Calamity'); + $account->setLastName('Jane'); + $account->setEmailAddress('no_email@provided.com'); + return $account; + } + + /** + * Verify that the input account is equal to the account(id=1). + */ + protected function assertAccount1(Account $account) + { + $this->assertEquals($account->getID(), 1); + $this->assertEquals($account->getFirstName(), 'Joe'); + $this->assertEquals($account->getEmailAddress(), 'Joe.Dalton@somewhere.com'); + } + + /** + * Verify that the input account is equal to the account(id=6). + */ + protected function assertAccount6(Account $account) + { + $this->assertEquals($account->getID(), 6); + $this->assertEquals($account->getFirstName(), 'Calamity'); + $this->assertEquals($account->getLastName(), 'Jane'); + $this->assertNull($account->getEmailAddress()); + } + + /** + * Verify that the input order is equal to the order(id=1). + */ + protected function assertOrder1(Order $order) + { + $date = @mktime(8,15,0,2,15,2003); + + $this->assertEquals((int)$order->getID(), 1); + if($order->getDate() instanceof TDateTime) + $this->assertEquals($order->getDate()->getTimestamp(), $date); + else + $this->fail(); + $this->assertEquals($order->getCardType(), 'VISA'); + $this->assertEquals($order->getCardNumber(), '999999999999'); + $this->assertEquals($order->getCardExpiry(), '05/03'); + $this->assertEquals($order->getStreet(), '11 This Street'); + $this->assertEquals($order->getProvince(), 'BC'); + $this->assertEquals($order->getPostalCode(), 'C4B 4F4'); + } + + function assertAccount1AsHashArray($account) + { + $this->assertEquals(1, (int)$account["Id"]); + $this->assertEquals("Joe", $account["FirstName"]); + $this->assertEquals("Dalton", $account["LastName"]); + $this->assertEquals("Joe.Dalton@somewhere.com", $account["EmailAddress"]); + } + + function AssertOrder1AsHashArray($order) + { + $date = @mktime(8,15,0,2,15,2003); + + $this->assertEquals(1, $order["Id"]); + if($order['Date'] instanceof TDateTime) + $this->assertEquals($date, $order["Date"]->getTimestamp()); + else + $this->fail(); + $this->assertEquals("VISA", $order["CardType"]); + $this->assertEquals("999999999999", $order["CardNumber"]); + $this->assertEquals("05/03", $order["CardExpiry"]); + $this->assertEquals("11 This Street", $order["Street"]); + $this->assertEquals("Victoria", $order["City"]); + $this->assertEquals("BC", $order["Province"]); + $this->assertEquals("C4B 4F4", $order["PostalCode"]); + } + +} + +class HundredsBool implements ITypeHandlerCallback +{ + public function getResult($string) + { + $value = intval($string); + if($value == 100) + return true; + if($value == 200) + return false; + //throw new Exception('unexpected value '.$value); + } + + public function getParameter($parameter) + { + if($parameter) + return 100; + else + return 200; + } + + public function createNewInstance() + { + throw new TDataMapperException('can not create'); + } +} + +class OuiNonBool implements ITypeHandlerCallback +{ + const YES = "Oui"; + const NO = "Non"; + + public function getResult($string) + { + if($string === self::YES) + return true; + if($string === self::NO) + return false; + //throw new Exception('unexpected value '.$string); + } + + public function getParameter($parameter) + { + if($parameter) + return self::YES; + else + return self::NO; + } + + public function createNewInstance() + { + throw new TDataMapperException('can not create'); + } +} + +class TDateTimeHandler implements ITypeHandlerCallback +{ + public function getResult($string) + { + $time = new TDateTime($string); + return $time; + } + + public function getParameter($parameter) + { + if($parameter instanceof TDateTime) + return $parameter->getTimestamp(); + else + return $parameter; + } + + public function createNewInstance() + { + return new TDateTime; + } +} + +class TDateTime +{ + private $_datetime; + + public function __construct($datetime=null) + { + if(!is_null($datetime)) + $this->setDatetime($datetime); + } + + public function getTimestamp() + { + return strtotime($this->getDatetime()); + } + + public function getDateTime() + { + return $this->_datetime; + } + + public function setDateTime($value) + { + $this->_datetime = $value; + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/CacheTest.php b/tests/unit/SQLMap/CacheTest.php new file mode 100644 index 00000000..11c250a8 --- /dev/null +++ b/tests/unit/SQLMap/CacheTest.php @@ -0,0 +1,164 @@ +initSqlMap(); + + //force autoload + new Account; + } + + function resetDatabase() + { + $this->initScript('account-init.sql'); + } + + /** + * Test for JIRA 29 + */ + function testJIRA28() + { + $account = $this->sqlmap->queryForObject("GetNoAccountWithCache",-99); + $this->assertNull($account); + } + + /** + * Test Cache query + */ + function testQueryWithCache() + { + $this->resetDatabase(); + + $list1 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $list2 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 === $list2); + + $account = $list1[1]; + $account->setEmailAddress("somebody@cache.com"); + + //this will cause the cache to flush + $this->sqlmap->update("UpdateAccountViaInlineParameters", $account); + + $list3 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 !== $list3); + + $this->resetDatabase(); + } + + + /** + * Test flush Cache + */ + function testFlushDataCache() + { + $list1 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + $list2 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 === $list2); + $this->sqlmap->flushCaches(); + + $list3 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 !== $list3); + } + + /** + * + */ + function testFlushDataCacheOnExecute() + { + $list1 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $list2 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 === $list2); + $this->sqlmap->update("UpdateAccountViaInlineParameters", $list1[0]); + + $list3 = $this->sqlmap->queryForList("GetCachedAccountsViaResultMap"); + + $this->assertTrue($list1 !== $list3); + } + + /** + */ + protected function getCacheModel() + { + $cache = new TSqlMapCacheModel(); + $cache->setFlushInterval(5*60); + $cache->setImplementation('LRU'); + $cache->initialize($this->sqlmap); + return $cache; + } + + /** + * Test CacheHit + */ + function testCacheHit() + { + $cache = $this->getCacheModel(); + $key = new TSqlMapCacheKey('testkey'); + $cache->set($key, 'a'); + + $returnedObject = $cache->get($key); + + $this->assertEquals('a', $returnedObject); + + $this->assertEquals(1, $cache->getHitRatio()); + } + + + + /** + * Test CacheMiss + */ + function testCacheMiss() + { + $cache = $this->getCacheModel(); + $key = new TSqlMapCacheKey('testKey'); + $value = 'testValue'; + $cache->set($key, $value); + + $wrongKey = new TSqlMapCacheKey('wrongKey'); + + $returnedObject = $cache->get($wrongKey); + $this->assertNotEquals($value, $returnedObject); + $this->assertNull($returnedObject) ; + $this->assertEquals(0, $cache->getHitRatio()); + } + + /** + * Test CacheHitMiss + */ + function testCacheHitMiss() + { + $cache = $this->getCacheModel(); + $key = new TSqlMapCacheKey('testKey'); + + $value = "testValue"; + $cache->set($key, $value); + + $returnedObject = $cache->get($key); + $this->assertEquals($value, $returnedObject); + + $wrongKey = new TSqlMapCacheKey('wrongKey'); + + $returnedObject = $cache->get($wrongKey); + $this->assertNotEquals($value, $returnedObject); + $this->assertNull($returnedObject) ; + $this->assertEquals(0.5, $cache->getHitRatio()); + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/ConfigureTest.php b/tests/unit/SQLMap/ConfigureTest.php new file mode 100644 index 00000000..ef2e5e31 --- /dev/null +++ b/tests/unit/SQLMap/ConfigureTest.php @@ -0,0 +1,20 @@ +configure($filename); + $this->assertNotNull($sqlmap); + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/ConnectionTest.php b/tests/unit/SQLMap/ConnectionTest.php new file mode 100644 index 00000000..138505fd --- /dev/null +++ b/tests/unit/SQLMap/ConnectionTest.php @@ -0,0 +1,26 @@ +initSqlmap(); + } + + function testOpenConnection() + { + $conn = $this->sqlmap->openConnection(); + $this->assertFalse($conn->getIsClosed()); + $this->sqlmap->closeConnection(); + $this->assertTrue($conn->getIsClosed()); + $this->sqlmap->openConnection(); + $this->assertFalse($conn->getIsClosed()); + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/DelegateTest.php b/tests/unit/SQLMap/DelegateTest.php new file mode 100644 index 00000000..e4c4d812 --- /dev/null +++ b/tests/unit/SQLMap/DelegateTest.php @@ -0,0 +1,64 @@ +initSqlMap(); + } + + function testListDelegate() + { + $list = $this->sqlmap->queryWithRowDelegate( + "GetAllAccountsViaResultMap", array($this, 'listHandler')); + + $this->assertEquals(5, count($list)); + $this->assertAccount1($list[0]); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + $this->assertEquals(3, $list[2]->getID()); + $this->assertEquals(4, $list[3]->getID()); + $this->assertEquals(5, $list[4]->getID()); + } + + /** + * Test ExecuteQueryForMap : Hashtable. + */ + function testExecuteQueryForMap() + { + $map = $this->sqlmap->QueryForMapWithRowDelegate( + "GetAllAccountsViaResultClass", array($this, 'mapHandler'), null, "FirstName"); + + $this->assertEquals(5, count($map)); + $this->assertAccount1($map["Joe"]); + + $this->assertEquals(1, $map["Joe"]->getID()); + $this->assertEquals(2, $map["Averel"]->getID()); + $this->assertEquals(3, $map["William"]->getID()); + $this->assertEquals(4, $map["Jack"]->getID()); + $this->assertEquals(5, $map["Gilles"]->getID()); + } + + public function listHandler($sender, $param) + { + $list = &$param->getList(); + $list[] = $param->result; + $this->assertTrue($param->result instanceof Account); + } + + public function mapHandler($sender, $param) + { + $map = &$param->getMap(); + $map[$param->getKey()] = $param->getValue(); + $this->assertTrue($param->getValue() instanceof Account); + } +} + + + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/GroupByTest.php b/tests/unit/SQLMap/GroupByTest.php new file mode 100644 index 00000000..a03a7305 --- /dev/null +++ b/tests/unit/SQLMap/GroupByTest.php @@ -0,0 +1,43 @@ +_orders = $orders; + } + + public function getOrders() + { + return $this->_orders; + } +} + + +/** + * @package System.DataAccess.SQLMap + */ +class GroupByTest extends BaseTest +{ + function __construct() + { + parent::__construct(); + $this->initSqlMap(); + } + + function testAccountWithOrders() + { + $this->initScript('account-init.sql'); + $accounts = $this->sqlmap->queryForList("getAccountWithOrders"); + $this->assertEquals(5, count($accounts)); + foreach($accounts as $account) + $this->assertEquals(2, count($account->getOrders())); + } + +/**/ +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/InheritanceTest.php b/tests/unit/SQLMap/InheritanceTest.php new file mode 100644 index 00000000..24e9f987 --- /dev/null +++ b/tests/unit/SQLMap/InheritanceTest.php @@ -0,0 +1,146 @@ +initSqlMap(); + $this->initScript('documents-init.sql'); + } + + /// Test All document with no formula + function testGetAllDocument() + { + $list = $this->sqlmap->queryForList("GetAllDocument"); + + $this->assertEquals(6, count($list)); + $book = $list[0]; + $this->assertBook($book, 1, "The World of Null-A", 55); + + $book = $list[1]; + $this->assertBook($book, 3, "Lord of the Rings", 3587); + + $document = $list[2]; + $this->assertDocument($document, 5, "Le Monde"); + + $document = $list[3]; + $this->assertDocument($document, 6, "Foundation"); + + $news = $list[4]; + $this->assertNewspaper($news, 2, "Le Progres de Lyon", "Lyon"); + + $document = $list[5]; + $this->assertDocument($document, 4, "Le Canard enchaine"); + } + + /// Test All document in a typed collection + function testGetTypedCollection() + { + $list = $this->sqlmap->queryForList("GetTypedCollection"); + + $this->assertEquals(6, $list->getCount()); + + $book = $list[0]; + $this->assertBook($book, 1, "The World of Null-A", 55); + + $book = $list[1]; + $this->assertBook($book, 3, "Lord of the Rings", 3587); + + $document = $list[2]; + $this->assertDocument($document, 5, "Le Monde"); + + $document = $list[3]; + $this->assertDocument($document, 6, "Foundation"); + + $news = $list[4]; + $this->assertNewspaper($news, 2, "Le Progres de Lyon", "Lyon"); + + $document = $list[5]; + $this->assertDocument($document, 4, "Le Canard enchaine"); + } + + /// Test All document with Custom Type Handler + function testGetAllDocumentWithCustomTypeHandler() + { + + //register the custom inheritance type handler + $this->sqlmap->getTypeHandlerFactory()->register( + 'CustomInheritance', new CustomInheritance); + + $list = $this->sqlmap->queryForList("GetAllDocumentWithCustomTypeHandler"); + + $this->assertEquals(6, count($list)); + $book = $list[0]; + $this->assertBook($book, 1, "The World of Null-A", 55); + + $book = $list[1]; + $this->assertBook($book, 3, "Lord of the Rings", 3587); + + $news = $list[2]; + $this->assertNewspaper($news, 5, "Le Monde", "Paris"); + + $book = $list[3]; + $this->assertBook($book, 6, "Foundation", 557); + + $news = $list[4]; + $this->assertNewspaper($news, 2, "Le Progres de Lyon", "Lyon"); + + $news = $list[5]; + $this->assertNewspaper($news, 4, "Le Canard enchaine", "Paris"); + } + + function AssertDocument(Document $document, $id, $title) + { + $this->assertEquals($id, $document->getID()); + $this->assertEquals($title, $document->getTitle()); + } + + function AssertBook(Book $book, $id, $title, $pageNumber) + { + $this->assertEquals($id, $book->getId()); + $this->assertEquals($title, $book->getTitle()); + $this->assertEquals($pageNumber, (int)$book->getPageNumber()); + } + + function AssertNewspaper(Newspaper $news, $id, $title, $city) + { + $this->assertEquals($id, $news->getId()); + $this->assertEquals($title, $news->getTitle()); + $this->assertEquals($city, $news->getCity()); + } +} + + +class CustomInheritance implements ITypeHandlerCallback +{ + public function getResult($type) + { + switch ($type) + { + case 'Monograph': case 'Book': + return 'Book'; + case 'Tabloid': case 'Broadsheet': case 'Newspaper': + return 'Newspaper'; + default: + return 'Document'; + } + } + + public function getParameter($parameter) + { + throw new TDataMapperException('not implemented'); + } + + public function createNewInstance() + { + throw new TDataMapperException('can not create'); + } +} +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/ParameterMapTest.php b/tests/unit/SQLMap/ParameterMapTest.php new file mode 100644 index 00000000..5b3c3006 --- /dev/null +++ b/tests/unit/SQLMap/ParameterMapTest.php @@ -0,0 +1,249 @@ +initSqlMap(); + } + + function setup() + { + $this->initScript('account-init.sql'); +// $this->initScript('account-procedure.sql'); + $this->initScript('order-init.sql'); +// $this->initScript('line-item-init.sql'); + $this->initScript('category-init.sql'); + } + + /// Test null replacement in ParameterMap property + function testNullValueReplacement() + { + $account = $this->newAccount6(); + + $this->sqlmap->insert("InsertAccountViaParameterMap", $account); + $account = $this->sqlmap->queryForObject("GetAccountNullableEmail", 6); + + $this->assertNull($account->getEmailAddress(), 'no_email@provided.com'); + + $this->assertAccount6($account); + } + + /// Test Test Null Value Replacement Inline + function testNullValueReplacementInline() + { + $account = $this->newAccount6(); + + $this->sqlmap->insert("InsertAccountViaInlineParameters", $account); + $account = $this->sqlmap->queryForObject("GetAccountNullableEmail", 6); + $this->assertNull($account->getEmailAddress()); + + $this->assertAccount6($account); + } + + /// Test Test Null Value Replacement Inline + function testSpecifiedType() + { + $account = $this->newAccount6(); + $account->setEmailAddress(null); + $this->sqlmap->insert("InsertAccountNullableEmail", $account); + $account = $this->sqlmap->queryForObject("GetAccountNullableEmail", 6); + $this->assertAccount6($account); + } + + + /// Test Test Null Value Replacement Inline + function testUnknownParameterClass() + { + $account = $this->newAccount6(); + $account->setEmailAddress(null); + $this->sqlmap->insert("InsertAccountUknownParameterClass", $account); + $account = $this->sqlmap->queryForObject("GetAccountNullableEmail", 6); + $this->assertAccount6($account); + } + + + /// Test null replacement in ParameterMap property + /// for System.DateTime.MinValue + function testNullValueReplacementForDateTimeMinValue() + { + $account = $this->newAccount6(); + $this->sqlmap->insert("InsertAccountViaParameterMap", $account); + $order = new Order(); + $order->setId(99); + $order->setCardExpiry("09/11"); + $order->setAccount($account); + $order->setCardNumber("154564656"); + $order->setCardType("Visa"); + $order->setCity("Lyon"); + $order->setDate(null); + $order->setPostalCode("69004"); + $order->setProvince("Rhone"); + $order->setStreet("rue Durand"); + + $this->sqlmap->insert("InsertOrderViaParameterMap", $order); + + $orderTest = $this->sqlmap->queryForObject("GetOrderLiteByColumnName", 99); + + $this->assertEquals($order->getCity(), $orderTest->getCity()); + } + + /// Test null replacement in ParameterMap/Hahstable property + /// for System.DateTime.MinValue + function testNullValueReplacementForDateTimeWithHashtable() + { + $account = $this->newAccount6(); + + $this->sqlmap->insert("InsertAccountViaParameterMap", $account); + + $order = new Order(); + $order->setId(99); + $order->setCardExpiry("09/11"); + $order->setAccount($account); + $order->setCardNumber("154564656"); + $order->setCardType("Visa"); + $order->setCity("Lyon"); + $order->setDate('0001-01-01 00:00:00'); //<-- null replacement + $order->setPostalCode("69004"); + $order->setProvince("Rhone"); + $order->setStreet("rue Durand"); + + $this->sqlmap->insert("InsertOrderViaParameterMap", $order); + + $orderTest = $this->sqlmap->queryForObject("GetOrderByHashTable", 99); + + $this->assertEquals($orderTest["Date"], '0001-01-01 00:00:00'); + } + + /// Test null replacement in ParameterMap property + /// for Guid + function testNullValueReplacementForGuidValue() + { + if($this->hasSupportFor('last_insert_id')) + { + $category = new Category(); + $category->setName("Totoasdasd"); + $category->setGuidString('00000000-0000-0000-0000-000000000000'); + + $key = $this->sqlmap->insert("InsertCategoryNull", $category); + + $categoryRead = $this->sqlmap->queryForObject("GetCategory", $key); + + $this->assertEquals($category->getName(), $categoryRead->getName()); + $this->assertEquals('', $categoryRead->getGuidString()); + } + } + + + +/// Test complex mapping Via hasTable + /// + /// + /// map.Add("Item", Item); + /// map.Add("Order", Order); + /// + /// + /// ... #Item.prop1#...#Order.prop2# + /// + /// + /// + function testComplexMappingViaHasTable() + { + $a = new Account(); + $a->setFirstName("Joe"); + + $param["Account"] = $a; + + $o = new Order(); + $o->setCity("Dalton"); + $param["Order"] = $o; + + $accountTest = $this->sqlmap->queryForObject("GetAccountComplexMapping", $param); + + $this->assertAccount1($accountTest); + } + +/* + /// Test ByteArrayTypeHandler via Picture Property + function testByteArrayTypeHandler() + { + $account = $this->newAccount6(); + + $this->sqlmap->insert("InsertAccountViaParameterMap", $account); + + $order = new Order(); + $order->setId(99); + $order->setCardExpiry("09/11"); + $order->setAccount($account); + $order->setCardNumber("154564656"); + $order->setCardType("Visa"); + $order->setCity("Lyon"); + $order->setDate(0); + $order->setPostalCode("69004"); + $order->setProvince("Rhone"); + $order->setStreet("rue Durand"); + + $this->sqlmap->insert("InsertOrderViaParameterMap", $order); + + $item = new LineItem(); + $item->setId(99); + $item->setCode("test"); + $item->setPrice(-99.99); + $item->setQuantity(99); + $item->setOrder($order); + $item->setPicture(null); + + // Check insert + $this->sqlmap->insert("InsertLineItemWithPicture", $item); + + // select + $item = null; + + $param["LineItem_ID"] = 99; + $param["Order_ID"] = 99; + + $item = $this->sqlmap->queryForObject("GetSpecificLineItemWithPicture", $param); + + $this->assertNotNull($item->getId()); +// $this->assertNotNull($item->getPicture()); +// $this->assertEquals( GetSize(item.Picture), this.GetSize( this.GetPicture() )); + } +*/ + + /// Test extend parameter map capacity + /// (Support Requests 1043181) + function testInsertOrderViaExtendParameterMap() + { + $this->sqlmap->getTypeHandlerFactory()->register('HundredsBool', new HundredsBool()); + + $account = $this->newAccount6(); + $this->sqlmap->insert("InsertAccountViaParameterMap", $account); + + $order = new Order(); + $order->setId(99); + $order->setCardExpiry("09/11"); + $order->setAccount($account); + $order->setCardNumber("154564656"); + $order->setCardType("Visa"); + $order->setCity("Lyon"); + $order->setDate(null); //<-- null replacement + $order->setPostalCode("69004"); + $order->setProvince("Rhone"); + $order->setStreet("rue Durand"); + + $this->sqlmap->insert("InsertOrderViaExtendParameterMap", $order); + + $orderTest = $this->sqlmap->queryForObject("GetOrderLiteByColumnName", 99); + + $this->assertEquals($order->getCity(), $orderTest->getCity()); + } +/**/ +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/PropertyAccessTest.php b/tests/unit/SQLMap/PropertyAccessTest.php new file mode 100644 index 00000000..96b942dc --- /dev/null +++ b/tests/unit/SQLMap/PropertyAccessTest.php @@ -0,0 +1,78 @@ +Id = 10; + $account->FirstName = "Luky"; + $account->LastName = "Luke"; + $account->EmailAddress = "luly.luke@somewhere.com"; + + $two = new AccountBis(); + $two->Id = 12; + $two->FirstName = "Mini Me!"; + $account->More = $two; + + $account6 = $this->NewAccount6(); + $two->More = $account6; + + $this->assertEquals(10, TPropertyAccess::get($account, 'Id')); + $this->assertEquals(12, TPropertyAccess::get($account, 'More.Id')); + $this->assertEquals(6, TPropertyAccess::get($account, 'More.More.Id')); + } + + function testSetPublicProperty() + { + $account = new AccountBis(); + + $account->Id = 10; + $account->FirstName = "Luky"; + $account->LastName = "Luke"; + $account->EmailAddress = "luly.luke@somewhere.com"; + + $two = new AccountBis(); + $two->Id = 12; + $two->FirstName = "Mini Me!"; + TPropertyAccess::set($account, 'More', $two); + + $account6 = $this->NewAccount6(); + TPropertyAccess::set($account, 'More.More', $account6); + + TPropertyAccess::set($account, 'More.More.EmailAddress', 'hahaha'); + + $this->assertEquals(10, TPropertyAccess::get($account, 'Id')); + $this->assertEquals(12, TPropertyAccess::get($account, 'More.Id')); + $this->assertEquals(6, TPropertyAccess::get($account, 'More.More.Id')); + + $this->assertEquals('hahaha', + TPropertyAccess::get($account, 'More.More.EmailAddress')); + } + + function testArrayAccessProperty() + { + $account = new AccountBis(); + $things['more'] = 1; + $things['accounts'] = $this->NewAccount6(); + $account->More = $things; + + $this->assertEquals(6, TPropertyAccess::get($account, 'More.accounts.ID')); + + TPropertyAccess::set($account, 'More.accounts.EmailAddress', 'adssd'); + $this->assertEquals('adssd', TPropertyAccess::get($account, 'More.accounts.EmailAddress')); + + $this->assertEquals(1, TPropertyAccess::get($things, 'more')); + } + +} + + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/ResultClassTest.php b/tests/unit/SQLMap/ResultClassTest.php new file mode 100644 index 00000000..93f93069 --- /dev/null +++ b/tests/unit/SQLMap/ResultClassTest.php @@ -0,0 +1,282 @@ +initSqlMap(); + } + + /** + * Test a boolean resultClass + */ + function testBoolean() + { + $bit = $this->sqlmap->queryForObject("GetBoolean", 1); + + $this->assertEquals(true, $bit); + } + + /** + * Test a boolean implicit resultClass + */ + function testBooleanWithoutResultClass() + { + $bit = (boolean)$this->sqlmap->queryForObject("GetBooleanWithoutResultClass", 1); + + $this->assertEquals(true, $bit); + } + + /** + * Test a byte resultClass + */ + function testByte() + { + $letter = $this->sqlmap->queryForObject("GetByte", 1); + + $this->assertEquals(155, (int)$letter); + } + + /** + * Test a byte implicit resultClass + */ + function testByteWithoutResultClass() + { + $letter = $this->sqlmap->queryForObject("GetByteWithoutResultClass", 1); + + $this->assertEquals(155, (int)$letter); + } + + /** + * Test a char resultClass + */ + function testChar() + { + $letter = $this->sqlmap->queryForObject("GetChar", 1); + + $this->assertEquals('a', trim($letter)); + } + + /** + * Test a char implicit resultClass + */ + function testCharWithoutResultClass() + { + $letter = $this->sqlmap->queryForObject("GetCharWithoutResultClass", 1); + + $this->assertEquals('a', trim($letter)); + } + + /** + * Test a DateTime resultClass + */ + function testDateTime() + { + $orderDate = $this->sqlmap->queryForObject("GetDate", 1); + + $date = @mktime(8, 15, 00, 2, 15, 2003); + + $this->assertEquals($date, $orderDate->getTimeStamp()); + } + + /** + * Test a DateTime implicit resultClass + */ + function testDateTimeWithoutResultClass() + { + $date = $this->sqlmap->queryForObject("GetDateWithoutResultClass", 1); + $orderDate = new TDateTime; + $orderDate->setDateTime($date); + $date = @mktime(8, 15, 00, 2, 15, 2003); + + $this->assertEquals($date, $orderDate->getTimeStamp()); + } + + /** + * Test a decimal resultClass + */ + function testDecimal() + { + $price = $this->sqlmap->queryForObject("GetDecimal", 1); + + $this->assertEquals(1.56, $price); + } + + /** + * Test a decimal implicit resultClass + */ + function testDecimalWithoutResultClass() + { + $price = $this->sqlmap->queryForObject("GetDecimalWithoutResultClass", 1); + + $this->assertEquals(1.56, (float)$price); + } + + /** + * Test a double resultClass + */ + function testDouble() + { + $price = $this->sqlmap->queryForObject("GetDouble", 1); + + $this->assertEquals(99.5, $price); + } + + /** + * Test a double implicit resultClass + */ + + function testDoubleWithoutResultClass() + { + $price = $this->sqlmap->queryForObject("GetDoubleWithoutResultClass", 1); + + $this->assertEquals(99.5, (float)$price); + } + + /** + * IBATISNET-25 Error applying ResultMap when using 'Guid' in resultClass + */ +/* function testGuid() + { + Guid newGuid = new Guid("CD5ABF17-4BBC-4C86-92F1-257735414CF4"); + + Guid guid = (Guid) $this->sqlmap->queryForObject("GetGuid", 1); + + $this->assertEquals(newGuid, guid); + } +*/ + + /** + * Test a Guid implicit resultClass + */ +/* function testGuidWithoutResultClass() + { + Guid newGuid = new Guid("CD5ABF17-4BBC-4C86-92F1-257735414CF4"); + + string guidString = Convert.ToString($this->sqlmap->queryForObject("GetGuidWithoutResultClass", 1)); + + Guid guid = new Guid(guidString); + + $this->assertEquals(newGuid, guid); + } +*/ + /** + * Test a int16 resultClass (integer in PHP) + */ + + function testInt16() + { + $integer = $this->sqlmap->queryForObject("GetInt16", 1); + + $this->assertEquals(32111, $integer); + } + + /** + * Test a int16 implicit resultClass (integer in PHP) + */ + + function testInt16WithoutResultClass() + { + $integer = $this->sqlmap->queryForObject("GetInt16WithoutResultClass", 1); + + $this->assertEquals(32111, (int)$integer); + } + + /** + * Test a int 32 resultClass (integer in PHP) + */ + + + function testInt32() + { + $integer = $this->sqlmap->queryForObject("GetInt32", 1); + + $this->assertEquals(999999, $integer); + } + + /** + * Test a int 32 implicit resultClass (integer in PHP) + */ + + + function testInt32WithoutResultClass() + { + $integer = $this->sqlmap->queryForObject("GetInt32WithoutResultClass", 1); + + $this->assertEquals(999999, (int)$integer); + } + + /** + * Test a int64 resultClass (float in PHP) + */ + + function testInt64() + { + $bigInt = $this->sqlmap->queryForObject("GetInt64", 1); + + $this->assertEquals(9223372036854775800, $bigInt); + } + + /** + * Test a int64 implicit resultClass (float in PHP) + */ + + function testInt64WithoutResultClass() + { + $bigInt = $this->sqlmap->queryForObject("GetInt64WithoutResultClass", 1); + + $this->assertEquals(9223372036854775800, (double)$bigInt); + } + + /** + * Test a single/float resultClass + */ + + function testSingle() + { + $price = (float)$this->sqlmap->queryForObject("GetSingle", 1); + + $this->assertEquals(92233.5, $price); + } + + /** + * Test a single/float implicit resultClass + */ + + function testSingleWithoutResultClass() + { + $price = $this->sqlmap->queryForObject("GetSingleWithoutResultClass", 1); + + $this->assertEquals(92233.5, (float)$price); + } + + /** + * Test a string resultClass + */ + + function testString() + { + $cardType = $this->sqlmap->queryForObject("GetString", 1); + + $this->assertEquals("VISA", $cardType); + } + + /** + * Test a string implicit resultClass + */ + + function testStringWithoutResultClass() + { + $cardType = $this->sqlmap->queryForObject("GetStringWithoutResultClass", 1); + + $this->assertEquals("VISA", $cardType); + } +/**/ +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/ResultMapTest.php b/tests/unit/SQLMap/ResultMapTest.php new file mode 100644 index 00000000..4909c37f --- /dev/null +++ b/tests/unit/SQLMap/ResultMapTest.php @@ -0,0 +1,280 @@ +initSqlMap(); + new Order; + new LineItemCollection; + new Account; + } + + function resetDatabase() + { + $this->initScript('account-init.sql'); + $this->initScript('order-init.sql'); + $this->initScript('line-item-init.sql'); +// $this->initScript('enumeration-init.sql'); + } + + function testColumnsByName() + { + $order = $this->sqlmap->QueryForObject('GetOrderLiteByColumnName', 1); + $this->assertOrder1($order); + } + + function testColumnsByIndex() + { + $order = $this->sqlmap->QueryForObject("GetOrderLiteByColumnIndex", 1); + $this->assertOrder1($order); + } + + + function testExtendedResultMap() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItemsNoLazyLoad", 1); + $this->assertOrder1($order); + $this->assertTrue($order->getLineItemsList() instanceof TList); + $this->assertEquals(2, $order->getLineItemsList()->getCount()); + } + + function testLazyLoad() + { + $order = $this->sqlmap->QueryForObject("GetOrderWithLineItems", 1); + $this->assertOrder1($order); + $this->assertNotNull($order->getLineItemsList()); + $this->assertFalse($order->getLineItemsList() instanceof TList); + $this->assertEquals(2, $order->getLineItemsList()->getCount()); + + // After a call to a method from a proxy object, + // the proxy object is replaced by the real object. + $this->assertTrue($order->getLineItemsList() instanceof TList); + $this->assertEquals(2, $order->getLineItemsList()->getCount()); + } + + function testLazyLoadWithOpenConnection() + { + $this->sqlmap->openConnection(); + + $this->testLazyLoad(); + $this->sqlmap->closeConnection(); + } + + function testLazyWithTypedCollectionMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItemCollection", 1); + $this->assertOrder1($order); + $this->assertNotNull($order->getLineItems()); + $this->assertFalse($order->getLineItemsList() instanceof LineItemCollection); + + $this->assertEquals(2, $order->getLineItems()->getCount()); + + // After a call to a method from a proxy object, + // the proxy object is replaced by the real object. + $this->assertTrue($order->getLineItems() instanceof LineItemCollection); + foreach($order->getLineItems() as $item) + { + $this->assertNotNull($item); + $this->assertTrue($item instanceof LineItem); + } + } + + function testNullValueReplacementOnString() + { + $account = $this->sqlmap->queryForObject("GetAccountViaColumnName", 5); + $this->assertEquals("no_email@provided.com", $account->getEmailAddress()); + } + + function testTypeSpecified() + { + $order = $this->sqlmap->queryForObject("GetOrderWithTypes", 1); + $this->assertOrder1($order); + } + + + function testComplexObjectMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderWithAccount", 1); + $this->assertOrder1($order); + $this->assertAccount1($order->getAccount()); + } + + function testCollectionMappingAndExtends() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItemsCollection", 1); + $this->assertOrder1($order); + + // Check strongly typed collection + $this->assertNotNull($order->getLineItems()); + $this->assertEquals(2, $order->getLineItems()->getCount()); + } + + function testListMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItems", 1); + $this->assertOrder1($order); + + // Check TList collection + $this->assertNotNull($order->getLineItemsList()); + $this->assertEquals(2, $order->getLineItemsList()->getCount()); + } + + function testArrayMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItemArray", 1); + $this->assertOrder1($order); + $this->assertNotNull($order->getLineItemsArray()); + $this->assertTrue(is_array($order->getLineItemsArray())); + $this->assertEquals(2, count($order->getLineItemsArray())); + } + + function testTypedCollectionMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderWithLineItemCollectionNoLazy", 1); + $this->assertOrder1($order); + $this->assertNotNull($order->getLineItems()); + $this->assertTrue($order->getLineItems() instanceof LineItemCollection); + $this->assertEquals(2, $order->getLineItems()->getCount()); + foreach($order->getLineItems() as $item) + { + $this->assertNotNull($item); + $this->assertTrue($item instanceof LineItem); + } + } + + function testHashArrayMapping() + { + $order = $this->sqlmap->queryForObject("GetOrderAsHastable", 1); + $this->assertOrder1AsHashArray($order); + } + + function testNestedObjects() + { + $order = $this->sqlmap->queryForObject("GetOrderJoinedFavourite", 1); + + $this->assertOrder1($order); + $this->assertNotNull($order->getFavouriteLineItem()); + $this->assertEquals(2, (int)$order->getFavouriteLineItem()->getID()); + $this->assertEquals("ESM-23", $order->getFavouriteLineItem()->getCode()); + + } + function testNestedObjects2() + { + $order = $this->sqlmap->queryForObject("GetOrderJoinedFavourite2", 1); + $this->assertOrder1($order); + + $this->assertNotNull($order->getFavouriteLineItem()); + $this->assertEquals(2, (int)$order->getFavouriteLineItem()->getID()); + $this->assertEquals("ESM-23", $order->getFavouriteLineItem()->getCode()); + } + + function testImplicitResultMaps() + { + $order = $this->sqlmap->queryForObject("GetOrderJoinedFavourite3", 1); + + // *** force date to timestamp since data type can't be + // *** explicity known without mapping + $order->setDate(new TDateTime($order->getDate())); + + $this->assertOrder1($order); + + $this->assertNotNull($order->getFavouriteLineItem()); + $this->assertEquals(2, $order->getFavouriteLineItem()->getID()); + $this->assertEquals("ESM-23", $order->getFavouriteLineItem()->getCode()); + } + + function testCompositeKeyMapping() + { + $this->resetDatabase(); + + $order1 = $this->sqlmap->queryForObject("GetOrderWithFavouriteLineItem", 1); + $order2 = $this->sqlmap->queryForObject("GetOrderWithFavouriteLineItem", 2); + + $this->assertNotNull($order1); + $this->assertNotNull($order1->getFavouriteLineItem()); + $this->assertEquals(2, $order1->getFavouriteLineItem()->getID()); + + $this->assertNotNull($order2); + $this->assertNotNull($order2->getFavouriteLineItem()); + $this->assertEquals(1, $order2->getFavouriteLineItem()->getID()); + } + + function testSimpleTypeMapping() + { + $this->resetDatabase(); + + $list = $this->sqlmap->QueryForList("GetAllCreditCardNumbersFromOrders", null); + + $this->assertEquals(5, count($list)); + $this->assertEquals("555555555555", $list[0]); + } + + + function testDecimalTypeMapping() + { + $this->resetDatabase(); + + $param["LineItem_ID"] = 1; + $param["Order_ID"] = 10; + $price = $this->sqlmap->queryForObject("GetLineItemPrice", $param); + $this->assertEquals(gettype($price), 'double'); + $this->assertEquals(45.43, $price); + } + +//todo +/* + function testNullValueReplacementOnEnum() + { + $enum['Id'] = 99; + $enum['Day'] = 'Days.Thu'; + $enum['Color'] = 'Colors.Blue'; + $enum['Month'] = 'Months.All'; + + $this->sqlmap->insert("InsertEnumViaParameterMap", $enum); + + $enumClass = $this->sqlmap->queryForObject("GetEnumerationNullValue", 99); + + $this->assertEquals($enumClass['Day'], 'Days.Thu'); + $this->asserEquals($enumClass['Color'], 'Colors.Blue'); + $this->assertEquals($enumClass['Month'], 'Months.All'); + } + + + function testByteArrayMapping() + { + } + + function testNullValueReplacementOnDecimal() + { + } + + function testNullValueReplacementOnDateTime() + { + } +*/ + +//future work + +/* + //requires dynamic SQL + function testDynamiqueCompositeKeyMapping() + { + $order1 = $this->sqlmap->queryForObject("GetOrderWithDynFavouriteLineItem", 1); + + $this->assertNotNull($order1); + $this->assertNotNull($order1->getFavouriteLineItem()); + var_dump($order1); + $this->assertEquals(2, $order1->getFavouriteLineItem()->getID()); + } +*/ + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/SelectKeyTest.php b/tests/unit/SQLMap/SelectKeyTest.php new file mode 100644 index 00000000..6ed48165 --- /dev/null +++ b/tests/unit/SQLMap/SelectKeyTest.php @@ -0,0 +1,120 @@ +initSqlMap(); + + //force autoload + new Account; + new Order; + new LineItem; + new LineItemCollection; + new A; new B; new C; new D; new E; new F; + } + + /** + * Test Insert with post GeneratedKey + */ + function testInsertPostKey() + { + $this->initScript('line-item-init.sql'); + + $item = new LineItem(); + + $item->setId(10); + $item->setCode("blah"); + $item->setOrder(new Order()); + $item->getOrder()->setId(9); + $item->setPrice(44.00); + $item->setQuantity(1); + + $key = $this->sqlmap->Insert("InsertLineItemPostKey", $item); + + $this->assertEquals(99, $key); + $this->assertEquals(99, $item->getId()); + + $param["Order_ID"] = 9; + $param["LineItem_ID"] =10; + $testItem = $this->sqlmap->QueryForObject("GetSpecificLineItem", $param); + + $this->assertNotNull($testItem); + $this->assertEquals(10, $testItem->getId()); + + $this->initScript('line-item-init.sql'); + } + + /** + * Test Insert pre GeneratedKey + */ + function testInsertPreKey() + { + $this->initScript('line-item-init.sql'); + + $item = new LineItem(); + + $item->setId(10); + $item->setCode("blah"); + $item->setOrder(new Order()); + $item->getOrder()->setId(9); + $item->setPrice(44.00); + $item->setQuantity(1); + + $key = $this->sqlmap->Insert("InsertLineItemPreKey", $item); + + $this->assertEquals(99, $key); + $this->assertEquals(99, $item->getId()); + + $param["Order_ID"] = 9; + $param["LineItem_ID"] = 99; + + $testItem = $this->sqlmap->QueryForObject("GetSpecificLineItem", $param); + + $this->assertNotNull($testItem); + $this->assertEquals(99, $testItem->getId()); + + $this->initScript('line-item-init.sql'); + } + + /** + * Test Test Insert No Key + */ + function testInsertNoKey() + { + $this->initScript('line-item-init.sql'); + + $item = new LineItem(); + + $item->setId(100); + $item->setCode("blah"); + $item->setOrder(new Order()); + $item->getOrder()->setId(9); + $item->setPrice(44.00); + $item->setQuantity(1); + + + $key = $this->sqlmap->Insert("InsertLineItemNoKey", $item); + + $this->assertNull($key); + $this->assertEquals(100, $item->getId()); + + $param["Order_ID"] = 9; + $param["LineItem_ID"] = 100; + + $testItem = $this->sqlmap->QueryForObject("GetSpecificLineItem", $param); + + $this->assertNotNull($testItem); + $this->assertEquals(100, $testItem->getId()); + + $this->initScript('line-item-init.sql'); + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/SqlMapCacheTest.php b/tests/unit/SQLMap/SqlMapCacheTest.php new file mode 100644 index 00000000..a2593e1a --- /dev/null +++ b/tests/unit/SQLMap/SqlMapCacheTest.php @@ -0,0 +1,77 @@ +set($key1, $object1); + $fifo->set($key2, $object2); + + $this->assertTrue($object1 === $fifo->get($key1)); + $this->assertTrue($object2 === $fifo->get($key2)); + + //object 1 should be removed + $fifo->set($key3, $object3); + + $this->assertNull($fifo->get($key1)); + $this->assertTrue($object2 === $fifo->get($key2)); + $this->assertTrue($object3 === $fifo->get($key3)); + + //object 2 should be removed + $fifo->set($key1, $object1); + + $this->assertNull($fifo->get($key2)); + $this->assertTrue($object3 === $fifo->get($key3)); + $this->assertTrue($object1 === $fifo->get($key1)); + } + + function testLruCache() + { + $lru = new TSqlMapLruCache(2); + + $object1 = new TSqlMapper; + $object2 = new TComponent; + $object3 = new TMapper; + + $key1 = 'key1'; + $key2 = 'key2'; + $key3 = 'key3'; + + $lru->set($key1, $object1); + $lru->set($key2, $object2); + + $this->assertTrue($object2 === $lru->get($key2)); + $this->assertTrue($object1 === $lru->get($key1)); + + //object 2 should be removed, i.e. least recently used + $lru->set($key3, $object3); + + $this->assertNull($lru->get($key2)); + $this->assertTrue($object1 === $lru->get($key1)); + $this->assertTrue($object3 === $lru->get($key3)); + + //object 1 will be removed + $lru->set($key2, $object2); + + $this->assertNull($lru->get($key1)); + $this->assertTrue($object2 === $lru->get($key2)); + $this->assertTrue($object3 === $lru->get($key3)); + } +} + + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/StatementTest.php b/tests/unit/SQLMap/StatementTest.php new file mode 100644 index 00000000..b014dea4 --- /dev/null +++ b/tests/unit/SQLMap/StatementTest.php @@ -0,0 +1,1134 @@ +initSqlMap(); + + //force autoload + new Account; + new Order; + new LineItem; + new LineItemCollection; + new A; new B; new C; new D; new E; new F; + } + + public function setup() + { + + } + + function resetDatabase() + { + $this->initScript('account-init.sql'); + $this->initScript('order-init.sql'); + $this->initScript('line-item-init.sql'); +// $this->initScript('enumeration-init.sql'); + $this->initScript('other-init.sql'); + } + + + #region Object Query tests + + /** + * Test Open connection with a connection string + */ + function testOpenConnection() + { + $this->sqlmap->OpenConnection($this->sqlmap->getDataProvider()->getConnectionString()); + $account= $this->sqlmap->QueryForObject("SelectWithProperty"); + $this->sqlmap->CloseConnection(); + $this->assertAccount1($account); + } + + /** + * Test use a statement with property subtitution + * (JIRA 22) + */ + function testSelectWithProperty() + { + $account= $this->sqlmap->QueryForObject("SelectWithProperty"); + $this->assertAccount1($account); + } + + /** + * Test ExecuteQueryForObject Via ColumnName + */ + function testExecuteQueryForObjectViaColumnName() + { + $account= $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + $this->assertAccount1($account); + } + + /** + * Test ExecuteQueryForObject Via ColumnIndex + */ + function testExecuteQueryForObjectViaColumnIndex() + { + $account= $this->sqlmap->QueryForObject("GetAccountViaColumnIndex", 1); + $this->assertAccount1($account); + } + + /** + * Test ExecuteQueryForObject Via ResultClass + */ + function testExecuteQueryForObjectViaResultClass() + { + $account= $this->sqlmap->QueryForObject("GetAccountViaResultClass", 1); + $this->assertAccount1($account); + } + + /** + * Test ExecuteQueryForObject With simple ResultClass : string + */ + function testExecuteQueryForObjectWithSimpleResultClass() + { + $email = $this->sqlmap->QueryForObject("GetEmailAddressViaResultClass", 1); + $this->assertEquals("Joe.Dalton@somewhere.com", $email); + } + + /** + * Test ExecuteQueryForObject With simple ResultMap : string + */ + function testExecuteQueryForObjectWithSimpleResultMap() + { + $email = $this->sqlmap->QueryForObject("GetEmailAddressViaResultMap", 1); + $this->assertEquals("Joe.Dalton@somewhere.com", $email); + } + + /** + * Test Primitive ReturnValue : TDateTime + */ + function testPrimitiveReturnValue() + { + $CardExpiry = $this->sqlmap->QueryForObject("GetOrderCardExpiryViaResultClass", 1); + $date = @mktime(8, 15, 00, 2, 15, 2003); + $this->assertEquals($date, $CardExpiry->getTimeStamp()); + } + + /** + * Test ExecuteQueryForObject with result object : Account + */ + function testExecuteQueryForObjectWithResultObject() + { + $account= new Account(); + $testAccount = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1, $account); + $this->assertAccount1($account); + $this->assertTrue($account == $testAccount); + } + + /** + * Test ExecuteQueryForObject as array + */ + function testExecuteQueryForObjectAsHashArray() + { + $account = $this->sqlmap->QueryForObject("GetAccountAsHashtable", 1); + $this->assertAccount1AsHashArray($account); + } + + /** + * Test ExecuteQueryForObject as Hashtable ResultClass + */ + function testExecuteQueryForObjectAsHashtableResultClass() + { + $account = $this->sqlmap->QueryForObject("GetAccountAsHashtableResultClass", 1); + $this->assertAccount1AsHashArray($account); + } + + /** + * Test ExecuteQueryForObject via Hashtable + */ + function testExecuteQueryForObjectViaHashtable() + { + $param["LineItem_ID"] = 2; + $param["Order_ID"] = 9; + + $testItem = $this->sqlmap->QueryForObject("GetSpecificLineItem", $param); + + $this->assertNotNull($testItem); + $this->assertEquals("TSM-12", $testItem->getCode()); + } + /**/ + + //TODO: Test Query Dynamic Sql Element + function testQueryDynamicSqlElement() + { + //$list = $this->sqlmap->QueryForList("GetDynamicOrderedEmailAddressesViaResultMap", "Account_ID"); + + //$this->assertEquals("Joe.Dalton@somewhere.com", $list[0]); + + //list = $this->sqlmap->QueryForList("GetDynamicOrderedEmailAddressesViaResultMap", "Account_FirstName"); + + //$this->assertEquals("Averel.Dalton@somewhere.com", $list[0]); + + } + + // TODO: Test Execute QueryForList With ResultMap With Dynamic Element + function testExecuteQueryForListWithResultMapWithDynamicElement() + { + //$list = $this->sqlmap->QueryForList("GetAllAccountsViaResultMapWithDynamicElement", "LIKE"); + + //$this->assertAccount1$list[0]); + //$this->assertEquals(3, $list->getCount()); + //$this->assertEquals(1, $list[0]->getID()); + //$this->assertEquals(2, $list[1]->getID()); + //$this->assertEquals(4, $list[2]->getID()); + + //list = $this->sqlmap->QueryForList("GetAllAccountsViaResultMapWithDynamicElement", "="); + + //$this->assertEquals(0, $list->getCount()); + } + + + + /** + * Test Get Account Via Inline Parameters + */ + function testExecuteQueryForObjectViaInlineParameters() + { + $account= new Account(); + $account->setID(1); + + $testAccount = $this->sqlmap->QueryForObject("GetAccountViaInlineParameters", $account); + + $this->assertAccount1($testAccount); + } + /**/ + + // TODO: Test ExecuteQuery For Object With Enum property + + function testExecuteQueryForObjectWithEnum() + { + //$enumClass = $this->sqlmap->QueryForObject("GetEnumeration", 1); + + //$this->assertEquals(enumClass.Day, Days.Sat); + //$this->assertEquals(enumClass.Color, Colors.Red); + //$this->assertEquals(enumClass.Month, Months.August); + + //enumClass = $this->sqlmap->QueryForObject("GetEnumeration", 3) as Enumeration; + + //$this->assertEquals(enumClass.Day, Days.Mon); + //$this->assertEquals(enumClass.Color, Colors.Blue); + //$this->assertEquals(enumClass.Month, Months.September);*/ + } + + #endregion + + #region List Query tests + + /** + * Test QueryForList with Hashtable ResultMap + */ + function testQueryForListWithHashtableResultMap() + { + $this->initScript('account-init.sql'); + $list = $this->sqlmap->QueryForList("GetAllAccountsAsHashMapViaResultMap"); + + $this->assertAccount1AsHashArray($list[0]); + $this->assertEquals(5, count($list)); + + $this->assertEquals(1, (int)$list[0]["Id"]); + $this->assertEquals(2, (int)$list[1]["Id"]); + $this->assertEquals(3, (int)$list[2]["Id"]); + $this->assertEquals(4, (int)$list[3]["Id"]); + $this->assertEquals(5, (int)$list[4]["Id"]); + } + + /** + * Test QueryForList with Hashtable ResultClass + */ + function testQueryForListWithHashtableResultClass() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsAsHashtableViaResultClass"); + + $this->assertAccount1AsHashArray($list[0]); + $this->assertEquals(5, count($list)); + + $this->assertEquals(1, (int)$list[0]["Id"]); + $this->assertEquals(2, (int)$list[1]["Id"]); + $this->assertEquals(3, (int)$list[2]["Id"]); + $this->assertEquals(4, (int)$list[3]["Id"]); + $this->assertEquals(5, (int)$list[4]["Id"]); + } + + /** + * Test QueryForList with IList ResultClass + */ + function testQueryForListWithIListResultClass() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsAsArrayListViaResultClass"); + + $listAccount = $list[0]; + + $this->assertEquals(1,(int)$listAccount[0]); + $this->assertEquals("Joe",$listAccount[1]); + $this->assertEquals("Dalton",$listAccount[2]); + $this->assertEquals("Joe.Dalton@somewhere.com",$listAccount[3]); + + $this->assertEquals(5, count($list)); + + $listAccount = $list[0]; + $this->assertEquals(1, (int)$listAccount[0]); + $listAccount = $list[1]; + $this->assertEquals(2, (int)$listAccount[0]); + $listAccount = $list[2]; + $this->assertEquals(3, (int)$listAccount[0]); + $listAccount = $list[3]; + $this->assertEquals(4, (int)$listAccount[0]); + $listAccount = $list[4]; + $this->assertEquals(5, (int)$listAccount[0]); + } + + /** + * Test QueryForList With ResultMap, result collection as ArrayList + */ + function testQueryForListWithResultMap() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsViaResultMap"); + + $this->assertAccount1($list[0]); + $this->assertEquals(5, count($list)); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + $this->assertEquals(3, $list[2]->getID()); + $this->assertEquals(4, $list[3]->getID()); + $this->assertEquals(5, $list[4]->getID()); + } + + /** + * Test ExecuteQueryForPaginatedList + */ + function testExecuteQueryForPaginatedList() + { + // Get List of all 5 + $list = $this->sqlmap->QueryForPagedList("GetAllAccountsViaResultMap", null, 2); + + // Test initial state (page 0) + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertAccount1($list[0]); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + + // Test illegal previous page (no effect, state should be same) + $list->PreviousPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertAccount1($list[0]); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + + // Test next (page 1) + $list->NextPage(); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(3, $list[0]->getID()); + $this->assertEquals(4, $list[1]->getID()); + + // Test next (page 2 -last) + $list->NextPage(); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + $this->assertEquals(5, $list[0]->getID()); + + // Test previous (page 1) + $list->PreviousPage(); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(3, $list[0]->getID()); + $this->assertEquals(4, $list[1]->getID()); + + // Test previous (page 0 -first) + $list->PreviousPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertAccount1($list[0]); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + + // Test goto (page 0) + $list->GotoPage(0); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + + // Test goto (page 1) + $list->GotoPage(1); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertTrue($list->getIsNextPageAvailable()); + $this->assertEquals(2, $list->getCount()); + $this->assertEquals(3, $list[0]->getID()); + $this->assertEquals(4, $list[1]->getID()); + + // Test goto (page 2) + $list->GotoPage(2); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + $this->assertEquals(5, $list[0]->getID()); + + // Test illegal goto (page 0) + $list->GotoPage(3); + $this->assertTrue($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(0, $list->getCount()); + + $list = $this->sqlmap->QueryForPagedList("GetNoAccountsViaResultMap", null, 2); + + // Test empty list + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(0, $list->getCount()); + + // Test next + $list->NextPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(0, $list->getCount()); + + // Test previous + $list->PreviousPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(0, $list->getCount()); + + // Test previous + $list->GotoPage(0); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(0, $list->getCount()); + $list = $this->sqlmap->QueryForPagedList("GetFewAccountsViaResultMap", null, 2); + + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + + // Test next + $list->NextPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + // Test previous + $list->PreviousPage(); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + + // Test previous + $list->GotoPage(0); + $this->assertFalse($list->getIsPreviousPageAvailable()); + $this->assertFalse($list->getIsNextPageAvailable()); + $this->assertEquals(1, $list->getCount()); + + + $list = $this->sqlmap->QueryForPagedList("GetAllAccountsViaResultMap", null, 5); + + $this->assertEquals(5, $list->getCount()); + + $list->NextPage(); + $this->assertEquals(5, $list->getCount()); + + $b = $list->getIsPreviousPageAvailable(); + $list->PreviousPage(); + $this->assertEquals(5, $list->getCount()); + } + + /** + * Test QueryForList with ResultObject : + * AccountCollection strongly typed collection + */ + function testQueryForListWithResultObject() + { + $accounts = new AccountCollection(); + + $this->sqlmap->QueryForList("GetAllAccountsViaResultMap", null, $accounts); + $this->assertAccount1($accounts[0]); + $this->assertEquals(5, $accounts->getCount()); + $this->assertEquals(1, $accounts[0]->getID()); + $this->assertEquals(2, $accounts[1]->getID()); + $this->assertEquals(3, $accounts[2]->getID()); + $this->assertEquals(4, $accounts[3]->getID()); + $this->assertEquals(5, $accounts[4]->GetId()); + } + + /** + * Test QueryForList with ListClass : LineItemCollection + */ + function testQueryForListWithListClass() + { + $linesItem = $this->sqlmap->QueryForList("GetLineItemsForOrderWithListClass", 10); + + $this->assertNotNull($linesItem); + $this->assertEquals(2, $linesItem->getCount()); + $this->assertEquals("ESM-34", $linesItem[0]->getCode()); + $this->assertEquals("QSM-98", $linesItem[1]->getCode()); + } + + /** + * Test QueryForList with no result. + */ + function testQueryForListWithNoResult() + { + $list = $this->sqlmap->QueryForList("GetNoAccountsViaResultMap"); + + $this->assertEquals(0, count($list)); + } + + /** + * Test QueryForList with ResultClass : Account. + */ + function testQueryForListResultClass() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsViaResultClass"); + + $this->assertAccount1($list[0]); + $this->assertEquals(5, count($list)); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + $this->assertEquals(3, $list[2]->getID()); + $this->assertEquals(4, $list[3]->getID()); + $this->assertEquals(5, $list[4]->getID()); + } + + /** + * Test QueryForList with simple resultClass : string + */ + function testQueryForListWithSimpleResultClass() + { + $list = $this->sqlmap->QueryForList("GetAllEmailAddressesViaResultClass"); + + $this->assertEquals("Joe.Dalton@somewhere.com", $list[0]); + $this->assertEquals("Averel.Dalton@somewhere.com", $list[1]); + $this->assertEquals('', $list[2]); + $this->assertEquals("Jack.Dalton@somewhere.com", $list[3]); + $this->assertEquals('', $list[4]); + } + + /** + * Test QueryForList with simple ResultMap : string + */ + function testQueryForListWithSimpleResultMap() + { + $list = $this->sqlmap->QueryForList("GetAllEmailAddressesViaResultMap"); + + $this->assertEquals("Joe.Dalton@somewhere.com", $list[0]); + $this->assertEquals("Averel.Dalton@somewhere.com", $list[1]); + $this->assertEquals('', $list[2]); + $this->assertEquals("Jack.Dalton@somewhere.com", $list[3]); + $this->assertEquals('', $list[4]); + } + + /** + * Test QueryForListWithSkipAndMax + */ + function testQueryForListWithSkipAndMax() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsViaResultMap", null, null, 2, 2); + + $this->assertEquals(2, count($list)); + $this->assertEquals(3, $list[0]->getID()); + $this->assertEquals(4, $list[1]->getID()); + } + + + /** + * Test row delegate + */ + function testQueryWithRowDelegate() + { + //$handler = new SqlMapper.RowDelegate(this.RowHandler); + + //$list = $this->sqlmap->QueryWithRowDelegate("GetAllAccountsViaResultMap", null, handler); + + //$this->assertEquals(5, _index); + //$this->assertEquals(5, $list->getCount()); + //$this->assertAccount1$list[0]); + //$this->assertEquals(1, $list[0]->getID()); + //$this->assertEquals(2, $list[1]->getID()); + //$this->assertEquals(3, $list[2]->getID()); + //$this->assertEquals(4, $list[3]->getID()); + //$this->assertEquals(5, $list[4]->getID()); + } + + #endregion + + #region Map Tests + + /** + * Test ExecuteQueryForMap : Hashtable. + */ + function testExecuteQueryForMap() + { + $map = $this->sqlmap->QueryForMap("GetAllAccountsViaResultClass", null, "FirstName"); + + $this->assertEquals(5, count($map)); + $this->assertAccount1($map["Joe"]); + + $this->assertEquals(1, $map["Joe"]->getID()); + $this->assertEquals(2, $map["Averel"]->getID()); + $this->assertEquals(3, $map["William"]->getID()); + $this->assertEquals(4, $map["Jack"]->getID()); + $this->assertEquals(5, $map["Gilles"]->getID()); + } + + /** + * Test ExecuteQueryForMap : Hashtable. + * + * If the keyProperty is an integer, you must acces the map + * by map[integer] and not by map["integer"] + */ + function testExecuteQueryForMap2() + { + $map = $this->sqlmap->QueryForMap("GetAllOrderWithLineItems", null, "PostalCode"); + + $this->assertEquals(11, count($map)); + $order = $map["T4H 9G4"]; + + $this->assertEquals(2, $order->getLineItemsList()->getCount()); + } + + /** + * Test ExecuteQueryForMap with value property : + * "FirstName" as key, "EmailAddress" as value + */ + function testExecuteQueryForMapWithValueProperty() + { + $map = $this->sqlmap->QueryForMap("GetAllAccountsViaResultClass", null, + "FirstName", "EmailAddress"); + + $this->assertEquals(5, count($map)); + + $this->assertEquals("Joe.Dalton@somewhere.com", $map["Joe"]); + $this->assertEquals("Averel.Dalton@somewhere.com", $map["Averel"]); + $this->assertNull($map["William"]); + $this->assertEquals("Jack.Dalton@somewhere.com", $map["Jack"]); + $this->assertNull($map["Gilles"]); + } + + /** + * Test ExecuteQueryForWithJoined + */ + function testExecuteQueryForWithJoined() + { + $order = $this->sqlmap->QueryForObject("GetOrderJoinWithAccount",10); + + $this->assertNotNull($order->getAccount()); + + $order = $this->sqlmap->QueryForObject("GetOrderJoinWithAccount",11); + + $this->assertNull($order->getAccount()); + } + + /** + * Test ExecuteQueryFor With Complex Joined + * + * A->B->C + * ->E + * ->F + */ + function testExecuteQueryForWithComplexJoined() + { + $a = $this->sqlmap->QueryForObject("SelectComplexJoined",null); + $this->assertNotNull($a); + $this->assertNotNull($a->getB()); + $this->assertNotNull($a->getB()->getC()); + $this->assertNull($a->getB()->getD()); + $this->assertNotNull($a->getE()); + $this->assertNull($a->getF()); + } + #endregion + + #region Extends statement + + /** + * Test base Extends statement + */ + function testExtendsGetAllAccounts() + { + $list = $this->sqlmap->QueryForList("GetAllAccounts"); + + $this->assertAccount1($list[0]); + $this->assertEquals(5, count($list)); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + $this->assertEquals(3, $list[2]->getID()); + $this->assertEquals(4, $list[3]->getID()); + $this->assertEquals(5, $list[4]->getID()); + } + + /** + * Test Extends statement GetAllAccountsOrderByName extends GetAllAccounts + */ + function testExtendsGetAllAccountsOrderByName() + { + $list = $this->sqlmap->QueryForList("GetAllAccountsOrderByName"); + + $this->assertAccount1($list[3]); + $this->assertEquals(5, count($list)); + + $this->assertEquals(2, $list[0]->getID()); + $this->assertEquals(5, $list[1]->getID()); + $this->assertEquals(4, $list[2]->getID()); + $this->assertEquals(1, $list[3]->getID()); + $this->assertEquals(3, $list[4]->getID()); + } + + /** + * Test Extends statement GetOneAccount extends GetAllAccounts + */ + function testExtendsGetOneAccount() + { + $account= $this->sqlmap->QueryForObject("GetOneAccount", 1); + $this->assertAccount1($account); + } + + /** + * Test Extends statement GetSomeAccount extends GetAllAccounts + */ + function testExtendsGetSomeAccount() + { + $param["lowID"] = 2; + $param["hightID"] = 4; + + $list = $this->sqlmap->QueryForList("GetSomeAccount", $param); + + $this->assertEquals(3, count($list)); + + $this->assertEquals(2, $list[0]->getID()); + $this->assertEquals(3, $list[1]->getID()); + $this->assertEquals(4, $list[2]->getID()); + } + + #endregion + + #region Update tests + + + /** + * Test Insert account via public fields + */ + function testInsertAccountViaPublicFields() + { + $this->initScript('account-init.sql'); + + $account = new AccountBis(); + + $account->Id = 10; + $account->FirstName = "Luky"; + $account->LastName = "Luke"; + $account->EmailAddress = "luly.luke@somewhere.com"; + + $this->sqlmap->Insert("InsertAccountViaPublicFields", $account); + + $testAccount = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 10); + + $this->assertNotNull($testAccount); + + $this->assertEquals(10, $testAccount->getID()); + + $this->initScript('account-init.sql'); + } + + /** + * + */ + function testInsertOrderViaProperties() + { + $this->initScript('account-init.sql'); + $this->initScript('order-init.sql'); + $account= $this->NewAccount6(); + + $this->sqlmap->Insert("InsertAccountViaParameterMap", $account); + + $order = new Order(); + $order->setId(99); + $order->setCardExpiry("09/11"); + $order->setAccount($account); + $order->setCardNumber("154564656"); + $order->setCardType("Visa"); + $order->setCity("Lyon"); + $order->setDate('2005-05-20'); + $order->setPostalCode("69004"); + $order->setProvince("Rhone"); + $order->setStreet("rue Durand"); + + $this->sqlmap->Insert("InsertOrderViaPublicFields", $order); + + $this->initScript('account-init.sql'); + $this->initScript('order-init.sql'); + } + + + /** + * Test Insert account via inline parameters + */ + function testInsertAccountViaInlineParameters() + { + $this->initScript('account-init.sql'); + $account= new Account(); + + $account->setId(10); + $account->setFirstName("Luky"); + $account->setLastName("Luke"); + $account->setEmailAddress("luly.luke@somewhere.com"); + + $this->sqlmap->Insert("InsertAccountViaInlineParameters", $account); + + $testAccount = $this->sqlmap->QueryForObject("GetAccountViaColumnIndex", 10); + + $this->assertNotNull($testAccount); + $this->assertEquals(10, $testAccount->getId()); + $this->initScript('account-init.sql'); + } + + /** + * Test Insert account via parameterMap + */ + function testInsertAccountViaParameterMap() + { + $this->initScript('account-init.sql'); + $account= $this->NewAccount6(); + $this->sqlmap->Insert("InsertAccountViaParameterMap", $account); + + $account = $this->sqlmap->QueryForObject("GetAccountNullableEmail", 6); + $this->AssertAccount6($account); + + $this->initScript('account-init.sql'); + } + + /** + * Test Update via parameterMap + */ + function testUpdateViaParameterMap() + { + $this->initScript('account-init.sql'); + $account= $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $account->setEmailAddress("new@somewhere.com"); + $this->sqlmap->Update("UpdateAccountViaParameterMap", $account); + + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $this->assertEquals("new@somewhere.com", $account->getEmailAddress()); + $this->initScript('account-init.sql'); + } + + /** + * Test Update via parameterMap V2 + */ + function testUpdateViaParameterMap2() + { + $this->initScript('account-init.sql'); + $account= $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $account->setEmailAddress("new@somewhere.com"); + $this->sqlmap->Update("UpdateAccountViaParameterMap2", $account); + + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $this->assertEquals("new@somewhere.com", $account->getEmailAddress()); + $this->initScript('account-init.sql'); + } + + /** + * Test Update with inline parameters + */ + function testUpdateWithInlineParameters() + { + $this->initScript('account-init.sql'); + $account= $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $account->setEmailAddress("new@somewhere.com"); + $this->sqlmap->Update("UpdateAccountViaInlineParameters", $account); + + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 1); + + $this->assertEquals("new@somewhere.com", $account->getEmailAddress()); + $this->initScript('account-init.sql'); + } + + /** + * Test Execute Update With Parameter Class + */ + function testExecuteUpdateWithParameterClass() + { + $this->initScript('account-init.sql'); + $account= $this->NewAccount6(); + + $this->sqlmap->Insert("InsertAccountViaParameterMap", $account); + + $noRowsDeleted = $this->sqlmap->Update("DeleteAccount", null); + + $this->sqlmap->Update("DeleteAccount", $account); + + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 6); + + $this->assertNull($account); + $this->assertEquals(0, $noRowsDeleted); + $this->initScript('account-init.sql'); + } + + /** + * Test Execute Delete + */ + function testExecuteDelete() + { + $this->initScript('account-init.sql'); + $account= $this->NewAccount6(); + + $this->sqlmap->Insert("InsertAccountViaParameterMap", $account); + + $account = null; + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 6); + + $this->assertTrue($account->getId() == 6); + + $rowNumber = $this->sqlmap->Delete("DeleteAccount", $account); + $this->assertTrue($rowNumber == 1); + + $account = $this->sqlmap->QueryForObject("GetAccountViaColumnName", 6); + + $this->assertNull($account); + $this->initScript('account-init.sql'); + } + + /** + * Test Execute Delete + */ + function testDeleteWithComments() + { + $this->initScript('line-item-init.sql'); + $rowNumber = $this->sqlmap->Delete("DeleteWithComments"); + + $this->assertEquals($rowNumber, 2); + $this->initScript('line-item-init.sql'); + } + + + + #endregion + + #region Row delegate + + private $_index = 0; + + function RowHandler($sender, $paramterObject, $list) + { + //_index++; + //$this->assertEquals(_index, (($account) obj).Id); + //$list->Add(obj); + } + + #endregion + + #region JIRA Tests + + /** + * Test JIRA 30 (repeating property) + */ + function testJIRA30() + { + $account= new Account(); + $account->setId(1); + $account->setFirstName("Joe"); + $account->setLastName("Dalton"); + $account->setEmailAddress("Joe.Dalton@somewhere.com"); + + $result = $this->sqlmap->QueryForObject("GetAccountWithRepeatingProperty", $account); + + $this->assertAccount1($result); + } + + /** + * Test Bit column + */ + function testJIRA42() + { + $other = new Other(); + + $other->setInt(100); + $other->setBool(true); + $other->setLong(789456321); + + $this->sqlmap->Insert("InsertBool", $other); + } + + /** + * Test for access a result map in a different namespace + */ + function testJIRA45() + { + $account= $this->sqlmap->QueryForObject("GetAccountJIRA45", 1); + $this->assertAccount1($account); + } + + /** + * Test : Whitespace is not maintained properly when CDATA tags are used + */ + function testJIRA110() + { + $account= $this->sqlmap->QueryForObject("Get1Account"); + $this->assertAccount1($account); + } + + /** + * Test : Whitespace is not maintained properly when CDATA tags are used + */ + function testJIRA110Bis() + { + $list = $this->sqlmap->QueryForList("GetAccounts"); + + $this->assertAccount1($list[0]); + $this->assertEquals(5, count($list)); + } + + /** + * Test for cache stats only being calculated on CachingStatments + */ + function testJIRA113() + { + // $this->sqlmap->FlushCaches(); + + // taken from TestFlushDataCache() + // first query is not cached, second query is: 50% cache hit + /*$list = $this->sqlmap->QueryForList("GetCachedAccountsViaResultMap"); + $firstId = HashCodeProvider.GetIdentityHashCode(list); + list = $this->sqlmap->QueryForList("GetCachedAccountsViaResultMap"); + int secondId = HashCodeProvider.GetIdentityHashCode(list); + $this->assertEquals(firstId, secondId); + + string cacheStats = $this->sqlmap->GetDataCacheStats(); + + $this->assertNotNull(cacheStats);*/ + } + + #endregion + + #region CustomTypeHandler tests + + /** + * Test CustomTypeHandler + */ + function testExecuteQueryWithCustomTypeHandler() + { + $this->sqlmap->getTypeHandlerFactory()->register('HundredsBool', new HundredsBool()); + $this->sqlmap->getTypeHandlerFactory()->register('OuiNonBool', new OuiNonBool()); + + $list = $this->sqlmap->QueryForList("GetAllAccountsViaCustomTypeHandler"); + + $this->assertAccount1($list[0]); + $this->assertEquals(5, count($list)); + $this->assertEquals(1, $list[0]->getID()); + $this->assertEquals(2, $list[1]->getID()); + $this->assertEquals(3, $list[2]->getID()); + $this->assertEquals(4, $list[3]->getID()); + $this->assertEquals(5, $list[4]->getID()); + + $this->assertFalse($list[0]->getCartOptions()); + $this->assertFalse($list[1]->getCartOptions()); + $this->assertTrue($list[2]->getCartOptions()); + $this->assertTrue($list[3]->getCartOptions()); + $this->assertTrue($list[4]->getCartOptions()); + + $this->assertTrue($list[0]->getBannerOptions()); + $this->assertTrue($list[1]->getBannerOptions()); + $this->assertFalse($list[2]->getBannerOptions()); + $this->assertFalse($list[3]->getBannerOptions()); + $this->assertTrue($list[4]->getBannerOptions()); + } + + /** + * Test CustomTypeHandler Oui/Non + */ + function testCustomTypeHandler() + { + $this->initScript('other-init.sql'); + $this->initScript('account-init.sql'); + + $this->sqlmap->getTypeHandlerFactory()->register('OuiNonBool', new OuiNonBool()); + + $other = new Other(); + $other->setInt(99); + $other->setLong(1966); + $other->setBool(true); + $other->setBool2(false); + $this->sqlmap->Insert("InsertCustomTypeHandler", $other); + + $anOther = $this->sqlmap->QueryForObject("SelectByInt", 99); + $this->assertNotNull( $anOther ); + $this->assertEquals(99, (int)$anOther->getInt()); + $this->assertEquals(1966, (int)$anOther->getLong()); + $this->assertEquals(true, (boolean)$anOther->getBool()); + $this->assertEquals(false, (boolean)$anOther->getBool2()); + + } + + /** + * Test CustomTypeHandler Oui/Non + */ + function testInsertInlineCustomTypeHandlerV1() + { + $this->initScript('other-init.sql'); + $this->initScript('account-init.sql'); + + $other = new Other(); + $other->setInt(99); + $other->setLong(1966); + $other->setBool(true); + $other->setBool2(false); + + $this->sqlmap->Insert("InsertInlineCustomTypeHandlerV1", $other); + + $anOther = $this->sqlmap->QueryForObject("SelectByIntV1", 99); + + $this->assertNotNull( $anOther ); + $this->assertEquals(99, (int)$anOther->getInt()); + $this->assertEquals(1966, (int)$anOther->getLong()); + $this->assertEquals(true, (boolean)$anOther->getBool()); + $this->assertEquals(false, (boolean)$anOther->getBool2()); + + } + + /** + * Test CustomTypeHandler Oui/Non + */ + function testInsertInlineCustomTypeHandlerV2() + { + $this->initScript('other-init.sql'); + $this->initScript('account-init.sql'); + + $other = new Other(); + $other->setInt(99); + $other->setLong(1966); + $other->setBool(true); + $other->setBool2(false); + + $this->sqlmap->Insert("InsertInlineCustomTypeHandlerV2", $other); + + $anOther = $this->sqlmap->QueryForObject("SelectByInt", 99); + + $this->assertNotNull( $anOther ); + $this->assertEquals(99, (int)$anOther->getInt()); + $this->assertEquals(1966, (int)$anOther->getLong()); + $this->assertEquals(true, (boolean)$anOther->getBool()); + $this->assertEquals(false, (boolean)$anOther->getBool2()); + } + #endregion + /**/ +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/TAdodbConnectionTestCase.php b/tests/unit/SQLMap/TAdodbConnectionTestCase.php new file mode 100644 index 00000000..df912c1b --- /dev/null +++ b/tests/unit/SQLMap/TAdodbConnectionTestCase.php @@ -0,0 +1,63 @@ +db_file = dirname(__FILE__).'/resources/test.db'; + copy($file,$this->db_file); + $provider = new TAdodbProvider(); + $provider->importAdodbLibrary(); + } + + function getDsn() + { + return 'sqlite://'.urlencode(realpath($this->db_file)); + } + + function testProviderCreation() + { + $provider = new TAdodbProvider(); + $connection = $provider->getConnection(); + $this->assertTrue($connection instanceof TAdodbConnection); + try + { + $connection->open(); + $this->fail(); + } + catch (TDbConnectionException $e) + { + $this->pass(); + } + } + + + + function testAdodbSqliteConnection() + { + $connection = new TAdodbConnection($this->getDsn()); + $this->assertTrue($connection->open()); + + $statement = "insert into person(per_id, per_first_name, + per_last_name, per_birth_date, per_weight_kg, per_height_m) + values(?, ?, ?, ?, ?, ?)"; + $sql = $connection->prepare($statement); + $connection->execute($sql, + array(2,'mini','me','2000-01-01', 50.5, 145.5)); + + $statement = "select * from person"; + $results = $connection->execute($statement); + $this->assertEquals($results->RecordCount(), 2); + + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/common.php b/tests/unit/SQLMap/common.php new file mode 100644 index 00000000..97f83267 --- /dev/null +++ b/tests/unit/SQLMap/common.php @@ -0,0 +1,152 @@ + 0) + $connection->execute($line); + } + } +} + +class CopyFileScriptRunner +{ + protected $baseFile; + protected $targetFile; + + public function __construct($base, $target) + { + $this->baseFile = $base; + $this->targetFile = $target; + } + + function runScript($connection, $script) + { + copy($this->baseFile, $this->targetFile); + } +} + +class SQLiteBaseTestConfig extends BaseTestConfig +{ + protected $baseFile; + protected $targetFile; + + public function __construct() + { + $this->_sqlmap = SQLMAP_TESTS.'/sqlite.xml'; + $this->targetFile = realpath(SQLMAP_TESTS.'/sqlite/tests.db'); + $this->baseFile = realpath(SQLMAP_TESTS.'/sqlite/backup.db'); + $file = urlencode($this->targetFile); + $this->_connectionString = "sqlite://{$file}/"; + $this->_scriptDir = SQLMAP_TESTS.'/scripts/sqlite/'; + } + + public function getScriptRunner() + { + 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->_scriptDir = SQLMAP_TESTS.'/scripts/mysql/'; + $this->_features = array('insert_id'); + } +} + +class MSSQLBaseTestConfig extends BaseTestConfig +{ + public function __construct() + { + $this->_sqlmap = SQLMAP_TESTS.'/mssql.xml'; + $this->_connectionString = 'odbc_mssql://sqlmap_tests'; + $this->_scriptDir = SQLMAP_TESTS.'/scripts/mssql/'; + $this->_features = array('insert_id'); + } +} + +class BaseTestConfig +{ + protected $_scriptDir; + protected $_connectionString; + protected $_sqlmap; + protected $_features = array(); + + public function getScriptDir() { return $this->_scriptDir; } + public function getConnectionString() { return $this->_connectionString; } + public function getSqlMapConfigFile(){ return $this->_sqlmap; } + + public function hasFeature($feature) + { + return in_array($feature, $this->_features); + } + + public function getScriptRunner() + { + return new DefaultScriptRunner(); + } + + + public static function createConfigInstance() + { + //change this to connection to a different database + + //return new MySQLBaseTestConfig(); + + return new SQLiteBaseTestConfig(); + + //return new MSSQLBaseTestConfig(); + } + +} + + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/A.php b/tests/unit/SQLMap/domain/A.php new file mode 100644 index 00000000..6830ea4d --- /dev/null +++ b/tests/unit/SQLMap/domain/A.php @@ -0,0 +1,27 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } + + public function getB(){ return $this->_B; } + public function setB($value){ $this->_B = $value; } + + public function getE(){ return $this->_E; } + public function setE($value){ $this->_E = $value; } + + public function getF(){ return $this->_F; } + public function setF($value){ $this->_F = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Account.php b/tests/unit/SQLMap/domain/Account.php new file mode 100644 index 00000000..f01726ba --- /dev/null +++ b/tests/unit/SQLMap/domain/Account.php @@ -0,0 +1,36 @@ +_ID; } + public function setID($value){ $this->_ID = intval($value); } + + public function getFirstName(){ return $this->_FirstName; } + public function setFirstName($value){ $this->_FirstName = $value; } + + public function getLastName(){ return $this->_LastName; } + public function setLastName($value){ $this->_LastName = $value; } + + public function getEmailAddress(){ return $this->_EmailAddress; } + public function setEmailAddress($value){ $this->_EmailAddress = $value; } + + public function getIDS(){ return $this->_IDS; } + public function setIDS($value){ $this->_IDS = $value; } + + public function getBannerOptions(){ return $this->_BannerOptions; } + public function setBannerOptions($value){ $this->_BannerOptions = $value; } + + public function getCartOptions(){ return $this->_CartOptions; } + public function setCartOptions($value){ $this->_CartOptions = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/AccountBis.php b/tests/unit/SQLMap/domain/AccountBis.php new file mode 100644 index 00000000..8c96d8e4 --- /dev/null +++ b/tests/unit/SQLMap/domain/AccountBis.php @@ -0,0 +1,13 @@ + \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/AccountCollection.php b/tests/unit/SQLMap/domain/AccountCollection.php new file mode 100644 index 00000000..9fc8edb7 --- /dev/null +++ b/tests/unit/SQLMap/domain/AccountCollection.php @@ -0,0 +1,17 @@ +add($account); + } + + public function copyTo(TList $array) + { + $array->copyFrom($this); + } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/B.php b/tests/unit/SQLMap/domain/B.php new file mode 100644 index 00000000..a05e21d4 --- /dev/null +++ b/tests/unit/SQLMap/domain/B.php @@ -0,0 +1,23 @@ +_C; } + public function setC($value){ $this->_C = $value; } + + public function getD(){ return $this->_D; } + public function setD($value){ $this->_D = $value; } + + public function getID(){ return $this->_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Book.php b/tests/unit/SQLMap/domain/Book.php new file mode 100644 index 00000000..cc12c30e --- /dev/null +++ b/tests/unit/SQLMap/domain/Book.php @@ -0,0 +1,11 @@ +_PageNumber; } + public function setPageNumber($value){ $this->_PageNumber = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/C.php b/tests/unit/SQLMap/domain/C.php new file mode 100644 index 00000000..09fb456a --- /dev/null +++ b/tests/unit/SQLMap/domain/C.php @@ -0,0 +1,15 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Category.php b/tests/unit/SQLMap/domain/Category.php new file mode 100644 index 00000000..83fecc7e --- /dev/null +++ b/tests/unit/SQLMap/domain/Category.php @@ -0,0 +1,19 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getName(){ return $this->_Name; } + public function setName($value){ $this->_Name = $value; } + + public function getGuidString(){ return $this->_Guid; } + public function setGuidString($value){ $this->_Guid = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Complex.php b/tests/unit/SQLMap/domain/Complex.php new file mode 100644 index 00000000..92b500f4 --- /dev/null +++ b/tests/unit/SQLMap/domain/Complex.php @@ -0,0 +1,11 @@ +_map; } + public function setMap(TMap $map){ $this->_map = $map; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/D.php b/tests/unit/SQLMap/domain/D.php new file mode 100644 index 00000000..5d1baabd --- /dev/null +++ b/tests/unit/SQLMap/domain/D.php @@ -0,0 +1,16 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Document.php b/tests/unit/SQLMap/domain/Document.php new file mode 100644 index 00000000..63bcfd33 --- /dev/null +++ b/tests/unit/SQLMap/domain/Document.php @@ -0,0 +1,16 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getTitle(){ return $this->_Title; } + public function setTitle($value){ $this->_Title = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/DocumentCollection.php b/tests/unit/SQLMap/domain/DocumentCollection.php new file mode 100644 index 00000000..c15b6f7d --- /dev/null +++ b/tests/unit/SQLMap/domain/DocumentCollection.php @@ -0,0 +1,8 @@ + \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/E.php b/tests/unit/SQLMap/domain/E.php new file mode 100644 index 00000000..2c80bb46 --- /dev/null +++ b/tests/unit/SQLMap/domain/E.php @@ -0,0 +1,16 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/F.php b/tests/unit/SQLMap/domain/F.php new file mode 100644 index 00000000..b1090cc6 --- /dev/null +++ b/tests/unit/SQLMap/domain/F.php @@ -0,0 +1,16 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getLibelle(){ return $this->_Libelle; } + public function setLibelle($value){ $this->_Libelle = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/LineItem.php b/tests/unit/SQLMap/domain/LineItem.php new file mode 100644 index 00000000..d851da6c --- /dev/null +++ b/tests/unit/SQLMap/domain/LineItem.php @@ -0,0 +1,32 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getOrder(){ return $this->_Order; } + public function setOrder($value){ $this->_Order = $value; } + + public function getCode(){ return $this->_Code; } + public function setCode($value){ $this->_Code = $value; } + + public function getQuantity(){ return $this->_Quantity; } + public function setQuantity($value){ $this->_Quantity = $value; } + + public function getPrice(){ return $this->_Price; } + public function setPrice($value){ $this->_Price = $value; } + + public function getPictureData(){ return $this->_PictureData; } + public function setPictureData($value){ $this->_PictureData = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/LineItemCollection.php b/tests/unit/SQLMap/domain/LineItemCollection.php new file mode 100644 index 00000000..f177487c --- /dev/null +++ b/tests/unit/SQLMap/domain/LineItemCollection.php @@ -0,0 +1,8 @@ + \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Newspaper.php b/tests/unit/SQLMap/domain/Newspaper.php new file mode 100644 index 00000000..4eb56aa2 --- /dev/null +++ b/tests/unit/SQLMap/domain/Newspaper.php @@ -0,0 +1,12 @@ +_City; } + public function setCity($value){ $this->_City = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Order.php b/tests/unit/SQLMap/domain/Order.php new file mode 100644 index 00000000..65d18b13 --- /dev/null +++ b/tests/unit/SQLMap/domain/Order.php @@ -0,0 +1,71 @@ +_LineItemsList = new TList; + $this->_LineItems = new TList; + $this->_FavouriteLineItem = new LineItem; + } + + public function getID(){ return $this->_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getAccount(){ return $this->_Account; } + public function setAccount($value){ $this->_Account = $value; } + + public function getDate(){ return $this->_Date; } + public function setDate($value){ $this->_Date = $value; } + + public function getCardType(){ return $this->_CardType; } + public function setCardType($value){ $this->_CardType = $value; } + + public function getCardExpiry(){ return $this->_CardExpiry; } + public function setCardExpiry($value){ $this->_CardExpiry = $value; } + + public function getCardNumber(){ return $this->_CardNumber; } + public function setCardNumber($value){ $this->_CardNumber = $value; } + + public function getStreet(){ return $this->_Street; } + public function setStreet($value){ $this->_Street = $value; } + + public function getCity(){ return $this->_City; } + public function setCity($value){ $this->_City = $value; } + + public function getProvince(){ return $this->_Province; } + public function setProvince($value){ $this->_Province = $value; } + + public function getPostalCode(){ return $this->_PostalCode; } + public function setPostalCode($value){ $this->_PostalCode = $value; } + + public function getLineItemsList(){ return $this->_LineItemsList; } + public function setLineItemsList($value){ $this->_LineItemsList = $value; } + + public function getLineItems(){ return $this->_LineItems; } + public function setLineItems($value){ $this->_LineItems = $value; } + + public function getLineItemsArray(){ return $this->_LineItemsArray; } + public function setLineItemsArray($value){ $this->_LineItemsArray = $value; } + + public function getFavouriteLineItem(){ return $this->_FavouriteLineItem; } + public function setFavouriteLineItem($value){ $this->_FavouriteLineItem = $value; } + +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Other.php b/tests/unit/SQLMap/domain/Other.php new file mode 100644 index 00000000..32d85e90 --- /dev/null +++ b/tests/unit/SQLMap/domain/Other.php @@ -0,0 +1,23 @@ +_Bool2; } + public function setBool2($value){ $this->_Bool2 = $value; } + + public function getBool(){ return $this->_Bool; } + public function setBool($value){ $this->_Bool = $value; } + + public function getInt(){ return $this->_Int; } + public function setInt($value){ $this->_Int = $value; } + + public function getLong(){ return $this->_Long; } + public function setLong($value){ $this->_Long = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Sample.php b/tests/unit/SQLMap/domain/Sample.php new file mode 100644 index 00000000..c9a34601 --- /dev/null +++ b/tests/unit/SQLMap/domain/Sample.php @@ -0,0 +1,55 @@ +_FirstID; } + public function setFirstID($value){ $this->_FirstID = $value; } + + public function getSecondID(){ return $this->_SecondID; } + public function setSecondID($value){ $this->_SecondID = $value; } + + public function getThirdID(){ return $this->_ThirdID; } + public function setThirdID($value){ $this->_ThirdID = $value; } + + public function getFourthID(){ return $this->_FourthID; } + public function setFourthID($value){ $this->_FourthID = $value; } + + public function getFifthID(){ return $this->_FifthID; } + public function setFifthID($value){ $this->_FifthID = $value; } + + public function getSequenceID(){ return $this->_SequenceID; } + public function setSequenceID($value){ $this->_SequenceID = $value; } + + public function getDistributedID(){ return $this->_DistributedID; } + public function setDistributedID($value){ $this->_DistributedID = $value; } + + public function getSampleChar(){ return $this->_SampleChar; } + public function setSampleChar($value){ $this->_SampleChar = $value; } + + public function getSampleDecimal(){ return $this->_SampleDecimal; } + public function setSampleDecimal($value){ $this->_SampleDecimal = $value; } + + public function getSampleMoney(){ return $this->_SampleMoney; } + public function setSampleMoney($value){ $this->_SampleMoney = $value; } + + public function getSampleDate(){ return $this->_SampleDate; } + public function setSampleDate($value){ $this->_SampleDate = $value; } + + public function getSequenceDate(){ return $this->_SequenceDate; } + public function setSequenceDate($value){ $this->_SequenceDate = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/Search.php b/tests/unit/SQLMap/domain/Search.php new file mode 100644 index 00000000..a09ca6e6 --- /dev/null +++ b/tests/unit/SQLMap/domain/Search.php @@ -0,0 +1,23 @@ +_NumberSearch; } + public function setNumberSearch($value){ $this->_NumberSearch = $value; } + + public function getStartDate(){ return $this->_StartDate; } + public function setStartDate($value){ $this->_StartDate = $value; } + + public function getOperande(){ return $this->_Operande; } + public function setOperande($value){ $this->_Operande = $value; } + + public function getStartDateAnd(){ return $this->_StartDateAnd; } + public function setStartDateAnd($value){ $this->_StartDateAnd = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/domain/User.php b/tests/unit/SQLMap/domain/User.php new file mode 100644 index 00000000..0c3a547f --- /dev/null +++ b/tests/unit/SQLMap/domain/User.php @@ -0,0 +1,27 @@ +_ID; } + public function setID($value){ $this->_ID = $value; } + + public function getUserName(){ return $this->_UserName; } + public function setUserName($value){ $this->_UserName = $value; } + + public function getPassword(){ return $this->_Password; } + public function setPassword($value){ $this->_Password = $value; } + + public function getEmailAddress(){ return $this->_EmailAddress; } + public function setEmailAddress($value){ $this->_EmailAddress = $value; } + + public function getLastLogon(){ return $this->_LastLogon; } + public function setLastLogon($value){ $this->_LastLogon = $value; } +} + +?> \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/Account.xml b/tests/unit/SQLMap/maps/MySql/Account.xml new file mode 100644 index 00000000..146e0e90 --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Account.xml @@ -0,0 +1,624 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) + values + (?, ?, ?, ?, ?, ?) + + + + update Accounts set + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_Id = ? + + + + update Accounts set + Account_Id = ?, + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_Id = ? + + + + delete from Accounts + where + Account_Id = #Id# + + + + + + + + + + + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress# + ) + + + + + + update Accounts set + Account_FirstName = #FirstName#, + Account_LastName = #LastName#, + Account_Email = #EmailAddress, dbType=VarChar, nullValue=no_email@provided.com# + where + Account_Id = #Id# + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar, nullValue=no_email@provided.com# + ) + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + delete from Accounts + where Account_Id = #Id# + and Account_Id = #Id# + + + + + + + + + + + + + + + + + + + + + + + + + + SELECT * + FROM + Accounts + + + + + INSERT INTO Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + VALUES(#Id#, #FirstName#, #LastName# + + + #EmailAddress# + + + null + + + ) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + ps_InsertAccount + + + + ps_swap_email_address + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/Category.xml b/tests/unit/SQLMap/maps/MySql/Category.xml new file mode 100644 index 00000000..ec81449b --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Category.xml @@ -0,0 +1,162 @@ + + + + + + + + + + + + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #GuidString:Varchar#); + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #GuidString:Varchar#) + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + + + + update Categories set + Category_Name =?, + Category_Guid = ? + where + Category_Id = ? + + + + ps_InsertCategorie + + + + + select LAST_INSERT_ID() as value + + + + + + + + + + + + + + + + + + select + Category_ID as Id, + Category_Name as Name, + Category_Guid as Guid + from Categories + + + Category_Guid=#GuidString:Varchar# + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/MySql/Complex.xml b/tests/unit/SQLMap/maps/MySql/Complex.xml new file mode 100644 index 00000000..f6da811d --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Complex.xml @@ -0,0 +1,23 @@ + + + + + + + select Account_ID from Accounts where Account_ID = #obj.Map.Id# + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + (#obj.Map.acct.Id#, #obj.Map.acct.FirstName#, #obj.Map.acct.LastName#, #obj.Map.acct.EmailAddress:Varchar:no_email@provided.com# + ) + + + + + + diff --git a/tests/unit/SQLMap/maps/MySql/Document.xml b/tests/unit/SQLMap/maps/MySql/Document.xml new file mode 100644 index 00000000..40608c97 --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Document.xml @@ -0,0 +1,53 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + * + from Documents + order by Document_Type, Document_Id + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/DynamicAccount.xml b/tests/unit/SQLMap/maps/MySql/DynamicAccount.xml new file mode 100644 index 00000000..ff89720b --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/DynamicAccount.xml @@ -0,0 +1,448 @@ + + + + + + + + + + + + + + + + SELECT + Account_ID as Id, + + + Account_FirstName as FirstName, + + + Account_LastName as LastName, + + + + Account_Email as EmailAddress + FROM + Accounts + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = 'Joe' + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + + + $statement$ + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #Ids[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + and Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + Account_ID in + + #Ids[]# + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + Account_Email = 'clinton.begin@ibatis.com' + + + Account_Email = #EmailAddress# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #[]# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #Id# + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + (Account_FirstName = #FirstName# + + Account_LastName = #LastName# + + ) + + + Account_Email like #EmailAddress# + + + Account_ID = #Id# + + + order by Account_LastName + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + ((Account_ID $Operande$ #NumberSearch#) or + (Account_ID $Operande$ #NumberSearch#)) + + + = #StartDate# ]]> + + + = #StartDate# ]]> + + + + order by Account_LastName + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/Enumeration.xml b/tests/unit/SQLMap/maps/MySql/Enumeration.xml new file mode 100644 index 00000000..b321259e --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Enumeration.xml @@ -0,0 +1,59 @@ + + + + + + + + + + + + + + + + + + + + + + + + insert into Enumerations + (Enum_ID, Enum_Day, Enum_Color, Enum_Month) + values + (?, ?, ?, ?) + + + + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/MySql/LineItem.xml b/tests/unit/SQLMap/maps/MySql/LineItem.xml new file mode 100644 index 00000000..c9778d9d --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/LineItem.xml @@ -0,0 +1,188 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + LineItem_Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + order by LineItem_Code + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price, + LineItem_Picture as PictureData + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + + + select + LineItem_ID, + LineItem_Code, + LineItem_Quantity, + LineItem_Price + from LineItems + where LineItem_ID = #value# + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price, LineItem_Picture) + values + (?, ?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + + + + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + + + + + delete from LineItems where Order_ID = 10; + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/Order.xml b/tests/unit/SQLMap/maps/MySql/Order.xml new file mode 100644 index 00000000..2e4eb616 --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Order.xml @@ -0,0 +1,468 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select * from Orders where Order_Id = #value# + + + + select Order_Date from Orders where Order_Id = #value# + + + + select + Order_Id, + Order_Date, + Order_CardExpiry, + Order_CardType, + Order_CardNumber, + Order_Street, + Order_City, + Order_Province, + Order_PostalCode + from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders + + + + select + Order_Date as 'datetime' + from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select * from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select + Orders.Order_Id as Id, + Order_Date as Date, + Order_CardExpiry as CardExpiry, + Order_CardType as CardType, + Order_CardNumber as CardNumber, + Order_Street as Street, + Order_City as City, + Order_Province as Province, + Order_PostalCode as PostalCode, + LineItem_ID as "FavouriteLineItem.Id", + LineItem_Code as "FavouriteLineItem.Code", + LineItem_Quantity as "FavouriteLineItem.Quantity", + LineItem_Price as "FavouriteLineItem.Price" + from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select distinct Order_CardNumber from Orders + order by Order_CardNumber + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (#Id#, #Account.Id#, #Date#, #CardExpiry#, #CardType#, #CardNumber#, #Street#, #City#, #Province#, #PostalCode#) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/MySql/Other.xml b/tests/unit/SQLMap/maps/MySql/Other.xml new file mode 100644 index 00000000..ca9b7a33 --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/Other.xml @@ -0,0 +1,170 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + + + Other_Int = #year# + + + + Other_Long = #areaid# + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Bit = #Bool# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, 'Yes') + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( ?, ?, ?, ?) + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,type=bool,dbType=Varchar#) + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,typeHandler=OuiNonBool#) + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/MySql/ResultClass.xml b/tests/unit/SQLMap/maps/MySql/ResultClass.xml new file mode 100644 index 00000000..3d44ec98 --- /dev/null +++ b/tests/unit/SQLMap/maps/MySql/ResultClass.xml @@ -0,0 +1,130 @@ + + + + + + select 1 from Orders where Order_ID = #dummy# + + + + + + select 155 from Orders where Order_ID = #value# + + + + + + + select cast('a' as char) from Orders where Order_ID = #value# + + + + + + select '2003-02-15 8:15:00' as datetime from Orders where Order_ID = #value# + + + + + + select 1.56 from Orders where Order_ID = #value# + + + + + + select 99.5 from Orders where Order_ID= #value# + + + + + + + select cast('CD5ABF17-4BBC-4C86-92F1-257735414CF4' as binary) from Orders where Order_ID = #value# + + + + + + select 32111 from Orders where Order_ID = #value# + + + + + + select 999999 from Orders where Order_ID = #value# + + + + + + select 9223372036854775800 from Orders where Order_ID = #value# + + + + + + select 92233.5 from Orders where Order_ID = #value# + + + + + + select 'VISA' + from Orders where Order_ID = #value# + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/Account.xml b/tests/unit/SQLMap/maps/mssql/Account.xml new file mode 100644 index 00000000..2e8dc1ce --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Account.xml @@ -0,0 +1,606 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) + values + (?, ?, ?, ?, ?, ?) + + + + update Accounts set + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_ID = ? + + + + update Accounts set + Account_ID = ?, + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_ID = ? + + + + delete from Accounts + where + Account_ID = #Id# + + + + + + + + + + + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress# + ) + + + + + update Accounts set + Account_FirstName = #FirstName#, + Account_LastName = #LastName#, + Account_Email = #EmailAddress, nullValue=no_email@provided.com# + where + Account_ID = #Id# + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress, nullValue=no_email@provided.com# + ) + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + delete from Accounts + where Account_ID = #Id, type=integer# + and Account_ID = #Id, type=integer# + + + + + + + + + + + + + + + + + + + + + + + + + + + + Accounts + + + + + INSERT INTO Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + VALUES(#Id#, #FirstName#, #LastName# + + + #EmailAddress# + + + null + + + ) + + + + + ps_InsertAccount + + + + ps_swap_email_address + + + + ps_SelectAccount + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/Category.xml b/tests/unit/SQLMap/maps/mssql/Category.xml new file mode 100644 index 00000000..b0e004a5 --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Category.xml @@ -0,0 +1,171 @@ + + + + + + + + + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #Guid:UniqueIdentifier#); + select SCOPE_IDENTITY() as value + + + + + insert into Categories + (Category_Name, Category_Guid) + values + ('toto', #value:UniqueIdentifier#); + select SCOPE_IDENTITY() as value + + + + + insert into Categories + (Category_Name, Category_Guid) + values + ('toto', #value#); + select SCOPE_IDENTITY() as value + + + + + ${selectKey} + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #Guid:UniqueIdentifier#) + + + + + ${selectKey} + + insert into Categories + (Category_Name, Category_Guid) + values + (${MyCategoryName}, #Guid:UniqueIdentifier#) + + + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + select SCOPE_IDENTITY() as value + + + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + select SCOPE_IDENTITY() as value + + + + update Categories set + Category_Name =?, + Category_Guid = ? + where + Category_Id = ? + + + + ps_InsertCategorie + + + + + select @@IDENTITY as value + + + + + + + + + + + + + + + + + + select + Category_ID as Id, + Category_Name as Name, + Category_Guid as Guid + from Categories + + + Category_Guid=#Guid:UniqueIdentifier# + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/mssql/Complex.xml b/tests/unit/SQLMap/maps/mssql/Complex.xml new file mode 100644 index 00000000..5a1bbf9c --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Complex.xml @@ -0,0 +1,21 @@ + + + + + + select Account_ID from Accounts where Account_ID = #obj.Map.Id# + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + (#obj.Map.acct.Id#, #obj.Map.acct.FirstName#, #obj.Map.acct.LastName#, #obj.Map.acct.EmailAddress:VarChar:no_email@provided.com# + ) + + + + + diff --git a/tests/unit/SQLMap/maps/mssql/Document.xml b/tests/unit/SQLMap/maps/mssql/Document.xml new file mode 100644 index 00000000..5c9b6b14 --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Document.xml @@ -0,0 +1,53 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/DynamicAccount.xml b/tests/unit/SQLMap/maps/mssql/DynamicAccount.xml new file mode 100644 index 00000000..355ef4f8 --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/DynamicAccount.xml @@ -0,0 +1,438 @@ + + + + + + + + + SELECT + Account_ID as Id, + + + Account_FirstName as FirstName, + + + Account_LastName as LastName, + + + + Account_Email as EmailAddress + FROM + Accounts + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = 'Joe' + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + + + $statement$ + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #Ids[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + and Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + Account_ID in + + #Ids[]# + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + Account_Email = 'clinton.begin@ibatis.com' + + + Account_Email = #EmailAddress# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #[]# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #Id# + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + (Account_FirstName = #FirstName# + + Account_LastName = #LastName# + + ) + + + Account_Email like #EmailAddress# + + + Account_ID = #Id# + + + order by Account_LastName + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + ((Account_ID $Operande$ #NumberSearch#) or + (Account_ID $Operande$ #NumberSearch#)) + + + = #StartDate# ]]> + + + = #StartDate# ]]> + + + + order by Account_LastName + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/Enumeration.xml b/tests/unit/SQLMap/maps/mssql/Enumeration.xml new file mode 100644 index 00000000..938a4266 --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Enumeration.xml @@ -0,0 +1,47 @@ + + + + + + + + + + + + + + + + insert into Enumerations + (Enum_ID, Enum_Day, Enum_Color, Enum_Month) + values + (?, ?, ?, ?) + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/mssql/LineItem.xml b/tests/unit/SQLMap/maps/mssql/LineItem.xml new file mode 100644 index 00000000..ced09b52 --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/LineItem.xml @@ -0,0 +1,182 @@ + + + + + + + + + + + + + + + + + + + + + + + + select + LineItem_Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + order by LineItem_Code + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price, + LineItem_Picture as PictureData + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + + + select + LineItem_ID, + LineItem_Code, + LineItem_Quantity, + LineItem_Price + from LineItems + where LineItem_ID = #value# + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price, LineItem_Picture) + values + (?, ?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, dbType=Decimal#) + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + + + + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, dbType=Decimal#) + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, dbType=Decimal#) + + + + + + delete from LineItems where Order_ID = 10 + + delete from LineItems where Order_ID = 9 + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/Order.xml b/tests/unit/SQLMap/maps/mssql/Order.xml new file mode 100644 index 00000000..14c9586a --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Order.xml @@ -0,0 +1,475 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select Order_Date from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select + Order_ID, + Order_Date, + Order_CardExpiry, + Order_CardType, + Order_CardNumber, + Order_Street, + Order_City, + Order_Province, + Order_PostalCode + from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders + + + + select + Order_Date as datetime + from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders, LineItems + where Orders.Order_ID = LineItems.Order_ID + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_ID = #value# + + + + select * from Orders, LineItems + where Orders.Order_ID = LineItems.Order_ID + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_ID = #value# + + + + select + Orders.Order_ID as Id, + Order_Date as Date, + Order_CardExpiry as CardExpiry, + Order_CardType as CardType, + Order_CardNumber as CardNumber, + Order_Street as Street, + Order_City as City, + Order_Province as Province, + Order_PostalCode as PostalCode, + LineItem_ID as "FavouriteLineItem.Id", + LineItem_Code as "FavouriteLineItem.Code", + LineItem_Quantity as "FavouriteLineItem.Quantity", + LineItem_Price as "FavouriteLineItem.Price" + from Orders, LineItems + where Orders.Order_ID = LineItems.Order_ID + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select * from Orders where Order_ID = #value# + + + + select distinct Order_CardNumber from Orders + order by Order_CardNumber + + + + insert into Orders + (Order_ID, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_ID, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_ID, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (#Id#, #Account.Id#, #Date#, #CardExpiry#, #CardType#, #CardNumber#, #Street#, #City#, #Province#, #PostalCode#) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select * from Orders where Order_ID = #value# + + + + + + + + select * from Orders where Order_ID = #value# + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/mssql/Other.xml b/tests/unit/SQLMap/maps/mssql/Other.xml new file mode 100644 index 00000000..fa250a5a --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/Other.xml @@ -0,0 +1,171 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + + + Other_Int = #year# + + + + Other_Long = #areaid# + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Bit = #Bool# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, 'Yes') + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( ?, ?, ?, ?) + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,type=bool,dbType=Varchar#) + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,typeHandler=OuiNonBool#) + + + + + + + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/mssql/ResultClass.xml b/tests/unit/SQLMap/maps/mssql/ResultClass.xml new file mode 100644 index 00000000..f6ccfbce --- /dev/null +++ b/tests/unit/SQLMap/maps/mssql/ResultClass.xml @@ -0,0 +1,125 @@ + + + + + + select cast(1 as bit) from Orders where Order_ID = #dummy# + + + + + + select cast(155 as tinyint) from Orders where Order_ID = #value# + + + + + + select cast('a' as char) from Orders where Order_ID = #value# + + + + + + + select cast('2003-02-15 8:15:00' as datetime) as datetime from Orders where Order_ID = #value# + + + + + + select cast(1.56 as decimal(9,2)) from Orders where Order_ID = #value# + + + + + + select cast(99.5 as float) from Orders where Order_ID= #value# + + + + + + select cast('CD5ABF17-4BBC-4C86-92F1-257735414CF4' as UniqueIdentifier) from Orders where Order_ID = #value# + + + + + + select cast(32111 as SmallInt) from Orders where Order_ID = #value# + + + + + + select cast(999999 as int) from Orders where Order_ID = #value# + + + + + + select cast(9223372036854775800 as bigint) from Orders where Order_ID = #value# + + + + + + select cast(92233.5 as real) from Orders where Order_ID = #value# + + + + + + select 'VISA' + from Orders where Order_ID = #value# + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/Account.xml b/tests/unit/SQLMap/maps/sqlite/Account.xml new file mode 100644 index 00000000..032febbc --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Account.xml @@ -0,0 +1,641 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) + values + (?, ?, ?, ?, ?, ?) + + + + update Accounts set + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_Id = ? + + + + update Accounts set + Account_Id = ?, + Account_FirstName = ?, + Account_LastName = ?, + Account_Email = ? + where + Account_Id = ? + + + + delete from Accounts + where + Account_Id = #Id# + + + + + + + + + + + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress# + ) + + + + + + update Accounts set + Account_FirstName = #FirstName#, + Account_LastName = #LastName#, + Account_Email = #EmailAddress, dbType=VarChar, nullValue=no_email@provided.com# + where + Account_Id = #Id# + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + (#Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar, nullValue=no_email@provided.com# + ) + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + insert into Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + values + ( #Id#, #FirstName#, #LastName#, #EmailAddress, dbType=VarChar# ) + + + + delete from Accounts + where Account_Id = #Id# + and Account_Id = #Id# + + + + + + + + + + + + + + + + + + + + + + + + + + SELECT * + FROM + Accounts + + + + + INSERT INTO Accounts + (Account_Id, Account_FirstName, Account_LastName, Account_Email) + VALUES(#Id#, #FirstName#, #LastName# + + + #EmailAddress# + + + null + + + ) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + ps_InsertAccount + + + + ps_swap_email_address + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/Category.xml b/tests/unit/SQLMap/maps/sqlite/Category.xml new file mode 100644 index 00000000..ec81449b --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Category.xml @@ -0,0 +1,162 @@ + + + + + + + + + + + + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #GuidString:Varchar#); + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (#Name#, #GuidString:Varchar#) + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + + + + + + select LAST_INSERT_ID() as value + + insert into Categories + (Category_Name, Category_Guid) + values + (?,?); + + + + update Categories set + Category_Name =?, + Category_Guid = ? + where + Category_Id = ? + + + + ps_InsertCategorie + + + + + select LAST_INSERT_ID() as value + + + + + + + + + + + + + + + + + + select + Category_ID as Id, + Category_Name as Name, + Category_Guid as Guid + from Categories + + + Category_Guid=#GuidString:Varchar# + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/sqlite/Complex.xml b/tests/unit/SQLMap/maps/sqlite/Complex.xml new file mode 100644 index 00000000..f6da811d --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Complex.xml @@ -0,0 +1,23 @@ + + + + + + + select Account_ID from Accounts where Account_ID = #obj.Map.Id# + + + + insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email) + values + (#obj.Map.acct.Id#, #obj.Map.acct.FirstName#, #obj.Map.acct.LastName#, #obj.Map.acct.EmailAddress:Varchar:no_email@provided.com# + ) + + + + + + diff --git a/tests/unit/SQLMap/maps/sqlite/Document.xml b/tests/unit/SQLMap/maps/sqlite/Document.xml new file mode 100644 index 00000000..8796865c --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Document.xml @@ -0,0 +1,53 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + * + from Documents + order by Document_Type, Document_Id + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/DynamicAccount.xml b/tests/unit/SQLMap/maps/sqlite/DynamicAccount.xml new file mode 100644 index 00000000..ff89720b --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/DynamicAccount.xml @@ -0,0 +1,448 @@ + + + + + + + + + + + + + + + + SELECT + Account_ID as Id, + + + Account_FirstName as FirstName, + + + Account_LastName as LastName, + + + + Account_Email as EmailAddress + FROM + Accounts + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = 'Joe' + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_FirstName = #value# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + where Account_ID = 1 + + + + + + + $statement$ + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #Ids[]# + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + WHERE Account_ID IN + + #[]# + + and Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + Account_ID in + + #Ids[]# + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + Account_Email = 'clinton.begin@ibatis.com' + + + Account_Email = #EmailAddress# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + Account_ID IN + + #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #[]# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #[]# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + + Account_ID = #Id# + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + Account_ID = #Id# + + + + + Account_FirstName = #FirstName# + + + Account_LastName = #LastName# + + + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + (Account_FirstName = #FirstName# + + Account_LastName = #LastName# + + ) + + + Account_Email like #EmailAddress# + + + Account_ID = #Id# + + + order by Account_LastName + + + + select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress + from Accounts + + + ((Account_ID $Operande$ #NumberSearch#) or + (Account_ID $Operande$ #NumberSearch#)) + + + = #StartDate# ]]> + + + = #StartDate# ]]> + + + + order by Account_LastName + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/Enumeration.xml b/tests/unit/SQLMap/maps/sqlite/Enumeration.xml new file mode 100644 index 00000000..b321259e --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Enumeration.xml @@ -0,0 +1,59 @@ + + + + + + + + + + + + + + + + + + + + + + + + insert into Enumerations + (Enum_ID, Enum_Day, Enum_Color, Enum_Month) + values + (?, ?, ?, ?) + + + + + + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/sqlite/LineItem.xml b/tests/unit/SQLMap/maps/sqlite/LineItem.xml new file mode 100644 index 00000000..c9778d9d --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/LineItem.xml @@ -0,0 +1,188 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + LineItem_Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems where Order_ID = #value# + order by LineItem_Code + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + select + LineItem_ID as Id, + LineItem_Code as Code, + LineItem_Quantity as Quantity, + LineItem_Price as Price, + LineItem_Picture as PictureData + from LineItems + where Order_ID = #Order_ID# + and LineItem_ID = #LineItem_ID# + + + + + + select + LineItem_ID, + LineItem_Code, + LineItem_Quantity, + LineItem_Price + from LineItems + where LineItem_ID = #value# + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price, LineItem_Picture) + values + (?, ?, ?, ?, ?, ?); + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + + + + + select 99 from LineItems where LineItem_ID = 1 and Order_ID=1 + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + + + insert into LineItems + (LineItem_ID, Order_ID, LineItem_Code, LineItem_Quantity, LineItem_Price) + values + (#Id#, #Order.Id#, #Code#, #Quantity#, #Price, type=float#) + + + + + + delete from LineItems where Order_ID = 10; + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/Order.xml b/tests/unit/SQLMap/maps/sqlite/Order.xml new file mode 100644 index 00000000..38009b1a --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Order.xml @@ -0,0 +1,507 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select * from Orders where Order_Id = #value# + + + + select Order_Date from Orders where Order_Id = #value# + + + + select + Order_Id, + Order_Date, + Order_CardExpiry, + Order_CardType, + Order_CardNumber, + Order_Street, + Order_City, + Order_Province, + Order_PostalCode + from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders + + + + select + Order_Date as 'datetime' + from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select + Orders.Order_Id as Order_Id, + Orders.Account_Id as Account_Id, + Orders.Order_Date as Order_Date, + Orders.Order_CardType as Order_CardType, + Orders.Order_CardNumber as Order_CardNumber, + Orders.Order_CardExpiry as Order_CardExpiry, + Orders.Order_Street as Order_Street, + Orders.Order_City as Order_City, + Orders.Order_Province as Order_Province, + Orders.Order_PostalCode as Order_PostalCode, + Orders.Order_FavouriteLineItem as Order_FavouriteLineItem, + LineItems.LineItem_Id as LineItem_Id, + LineItems.Order_Id as Order_Id, + LineItems.LineItem_Code as LineItem_Code, + LineItems.LineItem_Quantity as LineItem_Quantity, + LineItems.LineItem_Price as LineItem_Price, + LineItems.LineItem_Picture as LineItem_Picture + + from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select + + Orders.Order_Id as Order_Id, + Orders.Account_Id as Account_Id, + Orders.Order_Date as Order_Date, + Orders.Order_CardType as Order_CardType, + Orders.Order_CardNumber as Order_CardNumber, + Orders.Order_CardExpiry as Order_CardExpiry, + Orders.Order_Street as Order_Street, + Orders.Order_City as Order_City, + Orders.Order_Province as Order_Province, + Orders.Order_PostalCode as Order_PostalCode, + Orders.Order_FavouriteLineItem as Order_FavouriteLineItem, + LineItems.LineItem_Id as LineItem_Id, + LineItems.Order_Id as Order_Id, + LineItems.LineItem_Code as LineItem_Code, + LineItems.LineItem_Quantity as LineItem_Quantity, + LineItems.LineItem_Price as LineItem_Price, + LineItems.LineItem_Picture as LineItem_Picture + + from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select + Orders.Order_Id as Id, + Order_Date as Date, + Order_CardExpiry as CardExpiry, + Order_CardType as CardType, + Order_CardNumber as CardNumber, + Order_Street as Street, + Order_City as City, + Order_Province as Province, + Order_PostalCode as PostalCode, + LineItem_ID as "FavouriteLineItem.Id", + LineItem_Code as "FavouriteLineItem.Code", + LineItem_Quantity as "FavouriteLineItem.Quantity", + LineItem_Price as "FavouriteLineItem.Price" + from Orders, LineItems + where Orders.Order_Id = LineItems.Order_Id + and Order_FavouriteLineItem = LineItems.LineItem_ID + and Orders.Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select * from Orders where Order_Id = #value# + + + + select distinct Order_CardNumber from Orders + order by Order_CardNumber + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + + + + insert into Orders + (Order_Id, Account_ID, Order_Date, Order_CardExpiry, Order_CardType, + Order_CardNumber, Order_Street, Order_City, Order_Province, Order_PostalCode ) + values + (#Id#, #Account.Id#, #Date#, #CardExpiry#, #CardType#, #CardNumber#, #Street#, #City#, #Province#, #PostalCode#) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/maps/sqlite/Other.xml b/tests/unit/SQLMap/maps/sqlite/Other.xml new file mode 100644 index 00000000..ca9b7a33 --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/Other.xml @@ -0,0 +1,170 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + + + Other_Int = #year# + + + + Other_Long = #areaid# + + + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Bit = #Bool# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, 'Yes') + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( ?, ?, ?, ?) + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + select + Other_Int, + Other_Long, + Other_Bit, + Other_String + from Others + Where Other_Int = #value# + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,type=bool,dbType=Varchar#) + + + + Insert into Others + ( Other_Int, Other_Long, Other_Bit, Other_String ) + values + ( #Int#, #Long#, #Bool#, #Bool2,typeHandler=OuiNonBool#) + + + + + + + + + diff --git a/tests/unit/SQLMap/maps/sqlite/ResultClass.xml b/tests/unit/SQLMap/maps/sqlite/ResultClass.xml new file mode 100644 index 00000000..37985ba7 --- /dev/null +++ b/tests/unit/SQLMap/maps/sqlite/ResultClass.xml @@ -0,0 +1,130 @@ + + + + + + select 1 from Orders where Order_ID = #dummy# + + + + + + select 155 from Orders where Order_ID = #value# + + + + + + + select 'a' from Orders where Order_ID = #value# + + + + + + select '2003-02-15 8:15:00' as datetime from Orders where Order_ID = #value# + + + + + + select 1.56 from Orders where Order_ID = #value# + + + + + + select 99.5 from Orders where Order_ID= #value# + + + + + + + select cast('CD5ABF17-4BBC-4C86-92F1-257735414CF4' as binary) from Orders where Order_ID = #value# + + + + + + select 32111 from Orders where Order_ID = #value# + + + + + + select 999999 from Orders where Order_ID = #value# + + + + + + select 9223372036854775800 from Orders where Order_ID = #value# + + + + + + select 92233.5 from Orders where Order_ID = #value# + + + + + + select 'VISA' + from Orders where Order_ID = #value# + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/mssql.xml b/tests/unit/SQLMap/mssql.xml new file mode 100644 index 00000000..eae030b0 --- /dev/null +++ b/tests/unit/SQLMap/mssql.xml @@ -0,0 +1,33 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/mysql.xml b/tests/unit/SQLMap/mysql.xml new file mode 100644 index 00000000..d6e58b2c --- /dev/null +++ b/tests/unit/SQLMap/mysql.xml @@ -0,0 +1,33 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/properties.config b/tests/unit/SQLMap/properties.config new file mode 100644 index 00000000..2c46e756 --- /dev/null +++ b/tests/unit/SQLMap/properties.config @@ -0,0 +1,8 @@ + + + + + + + + diff --git a/tests/unit/SQLMap/resources/data.db b/tests/unit/SQLMap/resources/data.db new file mode 100644 index 00000000..b8c158cc Binary files /dev/null and b/tests/unit/SQLMap/resources/data.db differ diff --git a/tests/unit/SQLMap/resources/person.xml b/tests/unit/SQLMap/resources/person.xml new file mode 100644 index 00000000..f5db33f9 --- /dev/null +++ b/tests/unit/SQLMap/resources/person.xml @@ -0,0 +1,26 @@ + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/resources/sqlmap.xml b/tests/unit/SQLMap/resources/sqlmap.xml new file mode 100644 index 00000000..b15e9862 --- /dev/null +++ b/tests/unit/SQLMap/resources/sqlmap.xml @@ -0,0 +1,12 @@ + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/resources/test.db b/tests/unit/SQLMap/resources/test.db new file mode 100644 index 00000000..f939682e Binary files /dev/null and b/tests/unit/SQLMap/resources/test.db differ diff --git a/tests/unit/SQLMap/resources/tests.db b/tests/unit/SQLMap/resources/tests.db new file mode 100644 index 00000000..e69de29b diff --git a/tests/unit/SQLMap/scripts/mssql/DBCreation.sql b/tests/unit/SQLMap/scripts/mssql/DBCreation.sql new file mode 100644 index 00000000..c5ed9517 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/DBCreation.sql @@ -0,0 +1,89 @@ +-- MSQL DATABASE + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IBatisNet') + DROP DATABASE [IBatisNet] +GO + +CREATE DATABASE [IBatisNet] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'IBatisNet', N'autoclose', N'true' +GO + +exec sp_dboption N'IBatisNet', N'bulkcopy', N'false' +GO + +exec sp_dboption N'IBatisNet', N'trunc. log', N'true' +GO + +exec sp_dboption N'IBatisNet', N'torn page detection', N'true' +GO + +exec sp_dboption N'IBatisNet', N'read only', N'false' +GO + +exec sp_dboption N'IBatisNet', N'dbo use', N'false' +GO + +exec sp_dboption N'IBatisNet', N'single', N'false' +GO + +exec sp_dboption N'IBatisNet', N'autoshrink', N'true' +GO + +exec sp_dboption N'IBatisNet', N'ANSI null default', N'false' +GO + +exec sp_dboption N'IBatisNet', N'recursive triggers', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'IBatisNet', N'concat null yields null', N'false' +GO + +exec sp_dboption N'IBatisNet', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'IBatisNet', N'default to local cursor', N'false' +GO + +exec sp_dboption N'IBatisNet', N'quoted identifier', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'IBatisNet', N'auto create statistics', N'true' +GO + +exec sp_dboption N'IBatisNet', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'IBatisNet', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') + exec sp_droplogin N'IBatisNet' +GO + +use [IBatisNet] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'IBatisNet', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'IBatisNet', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'IBatisNet' and uid < 16382) + EXEC sp_grantdbaccess N'IBatisNet', N'IBatisNet' +GO + +exec sp_addrolemember N'db_owner', N'IBatisNet' +GO \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/DataBase.sql b/tests/unit/SQLMap/scripts/mssql/DataBase.sql new file mode 100644 index 00000000..733e2f99 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/DataBase.sql @@ -0,0 +1,179 @@ +-- MSQL DATABASE 'IBatisNet' + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IBatisNet') + DROP DATABASE [IBatisNet] +GO + +CREATE DATABASE [IBatisNet] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'IBatisNet', N'autoclose', N'true' +GO + +exec sp_dboption N'IBatisNet', N'bulkcopy', N'false' +GO + +exec sp_dboption N'IBatisNet', N'trunc. log', N'true' +GO + +exec sp_dboption N'IBatisNet', N'torn page detection', N'true' +GO + +exec sp_dboption N'IBatisNet', N'read only', N'false' +GO + +exec sp_dboption N'IBatisNet', N'dbo use', N'false' +GO + +exec sp_dboption N'IBatisNet', N'single', N'false' +GO + +exec sp_dboption N'IBatisNet', N'autoshrink', N'true' +GO + +exec sp_dboption N'IBatisNet', N'ANSI null default', N'false' +GO + +exec sp_dboption N'IBatisNet', N'recursive triggers', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'IBatisNet', N'concat null yields null', N'false' +GO + +exec sp_dboption N'IBatisNet', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'IBatisNet', N'default to local cursor', N'false' +GO + +exec sp_dboption N'IBatisNet', N'quoted identifier', N'false' +GO + +exec sp_dboption N'IBatisNet', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'IBatisNet', N'auto create statistics', N'true' +GO + +exec sp_dboption N'IBatisNet', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'IBatisNet', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') + exec sp_droplogin N'IBatisNet' +GO + +use [IBatisNet] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'IBatisNet') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'IBatisNet', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'IBatisNet', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'IBatisNet' and uid < 16382) + EXEC sp_grantdbaccess N'IBatisNet', N'IBatisNet' +GO + +exec sp_addrolemember N'db_owner', N'IBatisNet' +GO + +-- MSQL DATABASE 'NHibernate' + +IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NHibernate') + DROP DATABASE [NHibernate] +GO + +CREATE DATABASE [NHibernate] + COLLATE Latin1_General_CI_AS +GO + +exec sp_dboption N'NHibernate', N'autoclose', N'true' +GO + +exec sp_dboption N'NHibernate', N'bulkcopy', N'false' +GO + +exec sp_dboption N'NHibernate', N'trunc. log', N'true' +GO + +exec sp_dboption N'NHibernate', N'torn page detection', N'true' +GO + +exec sp_dboption N'NHibernate', N'read only', N'false' +GO + +exec sp_dboption N'NHibernate', N'dbo use', N'false' +GO + +exec sp_dboption N'NHibernate', N'single', N'false' +GO + +exec sp_dboption N'NHibernate', N'autoshrink', N'true' +GO + +exec sp_dboption N'NHibernate', N'ANSI null default', N'false' +GO + +exec sp_dboption N'NHibernate', N'recursive triggers', N'false' +GO + +exec sp_dboption N'NHibernate', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'NHibernate', N'concat null yields null', N'false' +GO + +exec sp_dboption N'NHibernate', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'NHibernate', N'default to local cursor', N'false' +GO + +exec sp_dboption N'NHibernate', N'quoted identifier', N'false' +GO + +exec sp_dboption N'NHibernate', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'NHibernate', N'auto create statistics', N'true' +GO + +exec sp_dboption N'NHibernate', N'auto update statistics', N'true' +GO + +if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) ) + exec sp_dboption N'NHibernate', N'db chaining', N'false' +GO + +if exists (select * from master.dbo.syslogins where loginname = N'NHibernate') + exec sp_droplogin N'NHibernate' +GO + +use [NHibernate] +GO + +if not exists (select * from master.dbo.syslogins where loginname = N'NHibernate') +BEGIN + declare @logindb nvarchar(132), @loginpass nvarchar(132), @loginlang nvarchar(132) + select @logindb = N'NHibernate', @loginpass=N'test', @loginlang = N'us_english' + exec sp_addlogin N'NHibernate', @loginpass, @logindb, @loginlang +END +GO + +if not exists (select * from dbo.sysusers where name = N'NHibernate' and uid < 16382) + EXEC sp_grantdbaccess N'NHibernate', N'NHibernate' +GO + +exec sp_addrolemember N'db_owner', N'NHibernate' +GO \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt b/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt new file mode 100644 index 00000000..355ebf42 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/README-embed-param.txt @@ -0,0 +1,8 @@ +Technique for creating large sample test data from: + +http://www.sql-server-performance.com/jc_large_data_operations.asp + +Make sure you have enough space and have either enough processing power or +enough patience to run the Embed Parameters in Statement tests. + +Run embed-parameters-setup-init.sql prior to running tests. diff --git a/tests/unit/SQLMap/scripts/mssql/account-init.sql b/tests/unit/SQLMap/scripts/mssql/account-init.sql new file mode 100644 index 00000000..8334798b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/account-init.sql @@ -0,0 +1,47 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Accounts]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Accounts + + drop table [dbo].[Accounts] +END + +CREATE TABLE [dbo].[Accounts] ( + [Account_ID] [int] NOT NULL , + [Account_FirstName] [varchar] (32) NOT NULL , + [Account_LastName] [varchar] (32) NOT NULL , + [Account_Email] [varchar] (128) NULL, + [Account_Banner_Option] [varchar] (255), + [Account_Cart_Option] [int] +) ON [PRIMARY] + +ALTER TABLE [dbo].[Accounts] WITH NOCHECK ADD + CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED + ( + [Account_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Accounts] VALUES(1,'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(2,'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(3,'William', 'Dalton', null, 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(4,'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(5,'Gilles', 'Bayon', null, 'Oui', 100); + +-- Store procedure + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_InsertAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_InsertAccount] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_SelectAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_SelectAccount] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_swap_email_address]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_swap_email_address] + + diff --git a/tests/unit/SQLMap/scripts/mssql/account-procedure.sql b/tests/unit/SQLMap/scripts/mssql/account-procedure.sql new file mode 100644 index 00000000..a18e1522 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/account-procedure.sql @@ -0,0 +1,12 @@ +CREATE PROCEDURE dbo.[ps_InsertAccount] +@Account_ID [int], +@Account_FirstName [nvarchar] (40), +@Account_LastName [varchar] (32), +@Account_Email [varchar] (128), +@Account_Banner_Option [varchar] (255), +@Account_Cart_Option [int] +AS +insert into Accounts + (Account_ID, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) +values + (@Account_ID, @Account_FirstName, @Account_LastName, @Account_Email, @Account_Banner_Option, @Account_Cart_Option) diff --git a/tests/unit/SQLMap/scripts/mssql/category-init.sql b/tests/unit/SQLMap/scripts/mssql/category-init.sql new file mode 100644 index 00000000..c0d20603 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/category-init.sql @@ -0,0 +1,17 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[Categories] + +CREATE TABLE [dbo].[Categories] ( + [Category_Id] [int] IDENTITY (1, 1) NOT NULL , + [Category_Name] [varchar] (32) NULL, + [Category_Guid] [uniqueidentifier] NULL +) ON [PRIMARY] + +-- Store procedure + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ps_InsertCategorie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) +drop procedure [dbo].[ps_InsertCategorie] diff --git a/tests/unit/SQLMap/scripts/mssql/category-procedure.sql b/tests/unit/SQLMap/scripts/mssql/category-procedure.sql new file mode 100644 index 00000000..45fcda2c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/category-procedure.sql @@ -0,0 +1,10 @@ +CREATE PROCEDURE dbo.[ps_InsertCategorie] +@Category_Id [int] output, +@Category_Name [varchar] (32), +@Category_Guid [uniqueidentifier] +AS +insert into Categories + (Category_Name, Category_Guid ) +values + (@Category_Name, @Category_Guid) +SELECT @Category_Id = SCOPE_IDENTITY() \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/documents-init.sql b/tests/unit/SQLMap/scripts/mssql/documents-init.sql new file mode 100644 index 00000000..686a80ad --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/documents-init.sql @@ -0,0 +1,34 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Documents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_LineItems_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[LineItems] DROP CONSTRAINT FK_LineItems_Orders + + drop table [dbo].[Documents] +END + +CREATE TABLE [dbo].[Documents] ( + [Document_ID] [int] NOT NULL , + [Document_Title] [varchar] (32) NULL , + [Document_Type] [varchar] (32) NULL , + [Document_PageNumber] [int] NULL , + [Document_City] [varchar] (32) NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Documents] WITH NOCHECK ADD + CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED + ( + [Document_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Documents] VALUES (1, 'The World of Null-A', 'Book', 55, null); +INSERT INTO [dbo].[Documents] VALUES (2, 'Le Progres de Lyon', 'Newspaper', null , 'Lyon'); +INSERT INTO [dbo].[Documents] VALUES (3, 'Lord of the Rings', 'Book', 3587, null); +INSERT INTO [dbo].[Documents] VALUES (4, 'Le Canard enchaine', 'Tabloid', null , 'Paris'); +INSERT INTO [dbo].[Documents] VALUES (5, 'Le Monde', 'Broadsheet', null , 'Paris'); +INSERT INTO [dbo].[Documents] VALUES (6, 'Foundation', 'Monograph', 557, null); diff --git a/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql b/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql new file mode 100644 index 00000000..de2c3f7b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/embed-param-setup-init.sql @@ -0,0 +1,94 @@ +-- Technique for creating large sample test data from +-- http://www.sql-server-performance.com/jc_large_data_operations.asp + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecords]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecords] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecordsTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecordsTest] + + + +-- Create Data Storage Table +CREATE TABLE [dbo].[ManyRecords] ( + [Many_FirstID] [int] NOT NULL, + [Many_SecondID] [int] NOT NULL, + [Many_ThirdID] [int] NOT NULL, + [Many_FourthID] [int] NOT NULL, + [Many_FifthID] [int] NOT NULL, + [Many_SequenceID] [int] NOT NULL, + [Many_DistributedID] [int] NOT NULL, + [Many_SampleCharValue] [char] (10) NOT NULL, + [Many_SampleDecimal] [decimal] (9,4) NOT NULL, + [Many_SampleMoney] [money] NOT NULL, + [Many_SampleDate] [datetime] NOT NULL, + [Many_SequenceDate] [datetime] NOT NULL ) +ON [PRIMARY] + + + +-- Create Sample Data of 1 million records (increase if needed) +BEGIN TRANSACTION + DECLARE @intIndex int, @rowCount int, @seqCount int, @distValue int + SELECT @intIndex = 1, @rowCount = 1000000, @seqCount = 10000 + SELECT @distValue = @rowCount/10000 + + WHILE @intIndex <= @rowCount + BEGIN + INSERT INTO [dbo].[ManyRecords] ( + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] ) + VALUES ( + @intIndex, -- First + @intIndex/2, -- Second + @intIndex/4, -- Third + @intIndex/10, -- Fourth + @intIndex/20, -- Fifth + (@intIndex-1)/@seqCount + 1, -- Sequential value + (@intIndex-1)%(@distValue) + 1, -- Distributed value + CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CONVERT(char(6),CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()), -- Char Value + 10000*rand(), -- Decimal value + 10000*rand(), -- Money value + DATEADD(hour,100000*rand(),'1990-01-01'), -- Date value + DATEADD(hour,@intIndex/5,'1990-01-01') ) -- Sequential date value + + SET @intIndex = @intIndex + 1 + END +COMMIT TRANSACTION + + + +-- Create Test table using storage table sample data +SELECT + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] +INTO [dbo].[ManyRecordsTest] +FROM [dbo].[ManyRecords] + + + +-- Create Test table indexes +CREATE INDEX [IDX_ManyRecordsTest_Seq] ON [dbo].[ManyRecordsTest] ([Many_SequenceID]) WITH SORT_IN_TEMPDB +CREATE INDEX [IDX_ManyRecordsTest_Dist] ON [dbo].[ManyRecordsTest] ([Many_DistributedID]) WITH SORT_IN_TEMPDB \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql b/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql new file mode 100644 index 00000000..17affd8e --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/embed-param-test-init.sql @@ -0,0 +1,32 @@ +-- Technique for creating large sample test data from +-- http://www.sql-server-performance.com/jc_large_data_operations.asp + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ManyRecordsTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[ManyRecordsTest] + + + +-- Create Test table using storage table sample data +SELECT + [Many_FirstID], + [Many_SecondID], + [Many_ThirdID], + [Many_FourthID], + [Many_FifthID], + [Many_SequenceID], + [Many_DistributedID], + [Many_SampleCharValue], + [Many_SampleDecimal], + [Many_SampleMoney], + [Many_SampleDate], + [Many_SequenceDate] +INTO [dbo].[ManyRecordsTest] +FROM [dbo].[ManyRecords] + + + +-- Create Test table indexes +CREATE INDEX [IDX_ManyRecordsTest_Seq] ON [dbo].[ManyRecordsTest] ([Many_SequenceID]) WITH SORT_IN_TEMPDB +CREATE INDEX [IDX_ManyRecordsTest_Dist] ON [dbo].[ManyRecordsTest] ([Many_DistributedID]) WITH SORT_IN_TEMPDB \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql b/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql new file mode 100644 index 00000000..f5ed9af1 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/enumeration-init.sql @@ -0,0 +1,30 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Enumerations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Enumerations] +END + +CREATE TABLE [dbo].[Enumerations] ( + [Enum_ID] [int] NOT NULL , + [Enum_Day] [int] NOT NULL , + [Enum_Color] [int] NOT NULL, + [Enum_Month] [int] NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Enumerations] WITH NOCHECK ADD + CONSTRAINT [PK_Enum] PRIMARY KEY CLUSTERED + ( + [Enum_ID] + ) ON [PRIMARY] + +-- Creating Test Data + +INSERT INTO [dbo].[Enumerations] VALUES(1, 1, 1, 128); +INSERT INTO [dbo].[Enumerations] VALUES(2, 2, 2, 2048); +INSERT INTO [dbo].[Enumerations] VALUES(3, 3, 4, 256); +INSERT INTO [dbo].[Enumerations] VALUES(4, 4, 8, null); + + diff --git a/tests/unit/SQLMap/scripts/mssql/line-item-init.sql b/tests/unit/SQLMap/scripts/mssql/line-item-init.sql new file mode 100644 index 00000000..c823667b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/line-item-init.sql @@ -0,0 +1,53 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LineItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[LineItems] + +CREATE TABLE [dbo].[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) NULL, + [LineItem_Picture] [image] null +) ON [PRIMARY] + +ALTER TABLE [dbo].[LineItems] WITH NOCHECK ADD + CONSTRAINT [PK_LinesItem] PRIMARY KEY CLUSTERED + ( + [LineItem_ID], + [Order_ID] + ) ON [PRIMARY] + +ALTER TABLE [dbo].[LineItems] ADD + CONSTRAINT [FK_LineItems_Orders] FOREIGN KEY + ( + [Order_ID] + ) REFERENCES [dbo].[Orders] ( + [Order_ID] + ) +-- Creating Test Data + +INSERT INTO [dbo].[LineItems] VALUES (1, 10, 'ESM-34', 1, 45.43, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 10, 'QSM-98', 8, 8.40, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 9, 'DSM-78', 2, 45.40, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 9, 'TSM-12', 2, 32.12, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 8, 'DSM-16', 4, 41.30, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 8, 'GSM-65', 1, 2.20, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 7, 'WSM-27', 7, 52.10, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 7, 'ESM-23', 2, 123.34, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 6, 'QSM-39', 9, 12.12, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 6, 'ASM-45', 6, 78.77, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 5, 'ESM-48', 3, 43.87, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 5, 'WSM-98', 7, 5.40, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 4, 'RSM-57', 2, 78.90, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 4, 'XSM-78', 9, 2.34, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 3, 'DSM-59', 3, 5.70, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 3, 'DSM-53', 3, 98.78, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 2, 'DSM-37', 4, 7.80, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 2, 'FSM-12', 2, 55.78, null); +INSERT INTO [dbo].[LineItems] VALUES (1, 1, 'ESM-48', 8, 87.60, null); +INSERT INTO [dbo].[LineItems] VALUES (2, 1, 'ESM-23', 1, 55.40, null); + diff --git a/tests/unit/SQLMap/scripts/mssql/more-account-records.sql b/tests/unit/SQLMap/scripts/mssql/more-account-records.sql new file mode 100644 index 00000000..d4b2199f --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/more-account-records.sql @@ -0,0 +1,11 @@ + + + +-- Creating Test Data + +INSERT INTO [dbo].[Accounts] VALUES(6,'Jane', 'Calamity', 'Jane.Calamity@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(7,'Lucky', 'Luke', 'Lucky.Luke@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(8,'Ming', 'Li Foo', null, 'Non', 100); +INSERT INTO [dbo].[Accounts] VALUES(9,'O''Hara', 'Steve', 'Jack.OHara@somewhere.com', 'Oui', 200); +INSERT INTO [dbo].[Accounts] VALUES(10,'Robert', 'O''Timmins', null, 'Non', 100); + diff --git a/tests/unit/SQLMap/scripts/mssql/order-init.sql b/tests/unit/SQLMap/scripts/mssql/order-init.sql new file mode 100644 index 00000000..b81b16ad --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/order-init.sql @@ -0,0 +1,54 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_LineItems_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) + ALTER TABLE [dbo].[LineItems] DROP CONSTRAINT FK_LineItems_Orders + + drop table [dbo].[Orders] +END + +CREATE TABLE [dbo].[Orders] ( + [Order_ID] [int] NOT NULL , + [Account_ID] [int] NULL , + [Order_Date] [datetime] NULL , + [Order_CardType] [varchar] (32) NULL , + [Order_CardNumber] [varchar] (32) NULL , + [Order_CardExpiry] [varchar] (32) NULL , + [Order_Street] [varchar] (32) NULL , + [Order_City] [varchar] (32) NULL , + [Order_Province] [varchar] (32) NULL , + [Order_PostalCode] [varchar] (32) NULL , + [Order_FavouriteLineItem] [int] NULL +) ON [PRIMARY] + +ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD + CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED + ( + [Order_ID] + ) ON [PRIMARY] + + +ALTER TABLE [dbo].[Orders] ADD + CONSTRAINT [FK_Orders_Accounts] FOREIGN KEY + ( + [Account_ID] + ) REFERENCES [dbo].[Accounts] ( + [Account_ID] + ) +-- Creating Test Data -- 2003-02-15 8:15:00/ 2003-02-15 8:15:00 + +INSERT INTO [dbo].[Orders] VALUES (1, 1, '2003-02-15 8:15:00', 'VISA', '999999999999', '05/03', '11 This Street', 'Victoria', 'BC', 'C4B 4F4',2); +INSERT INTO [dbo].[Orders] VALUES (2, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '06/03', '222 That Street', 'Edmonton', 'AB', 'X4K 5Y4',1); +INSERT INTO [dbo].[Orders] VALUES (3, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4',2); +INSERT INTO [dbo].[Orders] VALUES (4, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4',1); +INSERT INTO [dbo].[Orders] VALUES (5, 5, '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4',2); +INSERT INTO [dbo].[Orders] VALUES (6, 5, '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4',1); +INSERT INTO [dbo].[Orders] VALUES (7, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4',2); +INSERT INTO [dbo].[Orders] VALUES (8, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4',1); +INSERT INTO [dbo].[Orders] VALUES (9, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4',2); +INSERT INTO [dbo].[Orders] VALUES (10, 1, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4',1); +INSERT INTO [dbo].[Orders] VALUES (11, null, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY',1); + diff --git a/tests/unit/SQLMap/scripts/mssql/other-init.sql b/tests/unit/SQLMap/scripts/mssql/other-init.sql new file mode 100644 index 00000000..93ad5201 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/other-init.sql @@ -0,0 +1,145 @@ +-- Creating Table + +use [IBatisNet] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Others]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Others] +END + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[A] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[B] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[C]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[C] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[D]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[D] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[E]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[E] +END +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[F] +END + + +CREATE TABLE [dbo].[Others] ( + [Other_Int] [int] NULL , + [Other_Long] [BigInt] NULL, + [Other_Bit] [Bit] NOT NULL DEFAULT (0), + [Other_String] [varchar] (32) NOT NULL +) ON [PRIMARY] + +CREATE TABLE [dbo].[F] ( + [ID] [varchar] (50) NOT NULL , + [F_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_F] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[E] ( + [ID] [varchar] (50) NOT NULL , + [E_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_E] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[D] ( + [ID] [varchar] (50) NOT NULL , + [D_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_D] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + +CREATE TABLE [dbo].[C] ( + [ID] [varchar] (50) NOT NULL , + [C_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE TABLE [dbo].[B] ( + [ID] [varchar] (50) NOT NULL , + [C_ID] [varchar] (50) NULL , + [D_ID] [varchar] (50) NULL , + [B_Libelle] [varchar] (50) NULL , + CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED + ( + [ID] + ) ON [PRIMARY] , + CONSTRAINT [FK_B_C] FOREIGN KEY + ( + [C_ID] + ) REFERENCES [C] ( + [ID] + ), + CONSTRAINT [FK_B_D] FOREIGN KEY + ( + [D_ID] + ) REFERENCES [D] ( + [ID] + ) +) ON [PRIMARY] + + +CREATE TABLE [dbo].[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 + CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED + ( + [Id] + ) ON [PRIMARY] , + CONSTRAINT [FK_A_B] FOREIGN KEY + ( + [B_ID] + ) REFERENCES [B] ( + [ID] + ), + CONSTRAINT [FK_A_E] FOREIGN KEY + ( + [E_ID] + ) REFERENCES [E] ( + [ID] + ), + CONSTRAINT [FK_A_F] FOREIGN KEY + ( + [F_ID] + ) REFERENCES [F] ( + [ID] + ) +) ON [PRIMARY] + + +-- Creating Test Data + +INSERT INTO [dbo].[Others] VALUES(1, 8888888, 0, 'Oui'); +INSERT INTO [dbo].[Others] VALUES(2, 9999999999, 1, 'Non'); + +INSERT INTO [dbo].[F] VALUES('f', 'fff'); +INSERT INTO [dbo].[E] VALUES('e', 'eee'); +INSERT INTO [dbo].[D] VALUES('d', 'ddd'); +INSERT INTO [dbo].[C] VALUES('c', 'ccc'); +INSERT INTO [dbo].[B] VALUES('b', 'c', null, 'bbb'); +INSERT INTO [dbo].[A] VALUES('a', 'b', 'e', null, 'aaa'); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql b/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql new file mode 100644 index 00000000..668913ce --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/ps_SelectAccount.sql @@ -0,0 +1,10 @@ +CREATE PROCEDURE dbo.[ps_SelectAccount] +@Account_ID [int] +AS +select + Account_ID as Id, + Account_FirstName as FirstName, + Account_LastName as LastName, + Account_Email as EmailAddress +from Accounts +where Account_ID = @Account_ID \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql b/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql new file mode 100644 index 00000000..203ab60d --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/swap-procedure.sql @@ -0,0 +1,34 @@ +CREATE PROCEDURE dbo.[ps_swap_email_address] +@First_Email [nvarchar] (64) output, +@Second_Email [nvarchar] (64) output +AS + +Declare @ID1 int +Declare @ID2 int + +Declare @Email1 [nvarchar] (64) +Declare @Email2 [nvarchar] (64) + + SELECT @ID1 = Account_ID, @Email1 = Account_Email + from Accounts + where Account_Email = @First_Email + + SELECT @ID2 = Account_ID, @Email2 = Account_Email + from Accounts + where Account_Email = @Second_Email + + UPDATE Accounts + set Account_Email = @Email2 + where Account_ID = @ID1 + + UPDATE Accounts + set Account_Email = @Email1 + where Account_ID = @ID2 + + SELECT @First_Email = Account_Email + from Accounts + where Account_ID = @ID1 + + SELECT @Second_Email = Account_Email + from Accounts + where Account_ID = @ID2 diff --git a/tests/unit/SQLMap/scripts/mssql/user-init.sql b/tests/unit/SQLMap/scripts/mssql/user-init.sql new file mode 100644 index 00000000..02268f3c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mssql/user-init.sql @@ -0,0 +1,17 @@ +-- Creating Table + +use [NHibernate] + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +BEGIN + drop table [dbo].[Users] +END + +CREATE TABLE [dbo].[Users] ( + LogonID nvarchar(20) NOT NULL default '0', + Name nvarchar(40) default NULL, + Password nvarchar(20) default NULL, + EmailAddress nvarchar(40) default NULL, + LastLogon datetime default NULL, + PRIMARY KEY (LogonID) +) diff --git a/tests/unit/SQLMap/scripts/mysql/DataBase.sql b/tests/unit/SQLMap/scripts/mysql/DataBase.sql new file mode 100644 index 00000000..b6542cdb --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/DataBase.sql @@ -0,0 +1,214 @@ +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; diff --git a/tests/unit/SQLMap/scripts/mysql/account-init.sql b/tests/unit/SQLMap/scripts/mysql/account-init.sql new file mode 100644 index 00000000..51b315f4 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/account-init.sql @@ -0,0 +1,20 @@ +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; + +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); +INSERT INTO Accounts VALUES(3,'William', 'Dalton', null, 'Non', 100); +INSERT INTO Accounts VALUES(4,'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100); +INSERT INTO Accounts VALUES(5,'Gilles', 'Bayon', null, 'Oui', 100); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/account-procedure.sql b/tests/unit/SQLMap/scripts/mysql/account-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/account-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/category-init.sql b/tests/unit/SQLMap/scripts/mysql/category-init.sql new file mode 100644 index 00000000..2f50ff24 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/category-init.sql @@ -0,0 +1,12 @@ + +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; diff --git a/tests/unit/SQLMap/scripts/mysql/category-procedure.sql b/tests/unit/SQLMap/scripts/mysql/category-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/category-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/documents-init.sql b/tests/unit/SQLMap/scripts/mysql/documents-init.sql new file mode 100644 index 00000000..c254ae4d --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/documents-init.sql @@ -0,0 +1,20 @@ +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; + +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'); +INSERT INTO Documents VALUES (3, 'Lord of the Rings', 'Book', 3587, null); +INSERT INTO Documents VALUES (4, 'Le Canard enchaine', 'Tabloid', null , 'Paris'); +INSERT INTO Documents VALUES (5, 'Le Monde', 'Broadsheet', null , 'Paris'); +INSERT INTO Documents VALUES (6, 'Foundation', 'Monograph', 557, null); diff --git a/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql b/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql new file mode 100644 index 00000000..a194b636 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/enumeration-init.sql @@ -0,0 +1,18 @@ + +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; + +INSERT INTO Enumerations VALUES(1, 1, 1, 128); +INSERT INTO Enumerations VALUES(2, 2, 2, 2048); +INSERT INTO Enumerations VALUES(3, 3, 4, 256); +INSERT INTO Enumerations VALUES(4, 4, 8, null); diff --git a/tests/unit/SQLMap/scripts/mysql/line-item-init.sql b/tests/unit/SQLMap/scripts/mysql/line-item-init.sql new file mode 100644 index 00000000..cb800835 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/line-item-init.sql @@ -0,0 +1,37 @@ + +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; + +INSERT INTO LineItems VALUES (1, 10, 'ESM-34', 1, 45.43, null); +INSERT INTO LineItems VALUES (2, 10, 'QSM-98', 8, 8.40, null); +INSERT INTO LineItems VALUES (1, 9, 'DSM-78', 2, 45.40, null); +INSERT INTO LineItems VALUES (2, 9, 'TSM-12', 2, 32.12, null); +INSERT INTO LineItems VALUES (1, 8, 'DSM-16', 4, 41.30, null); +INSERT INTO LineItems VALUES (2, 8, 'GSM-65', 1, 2.20, null); +INSERT INTO LineItems VALUES (1, 7, 'WSM-27', 7, 52.10, null); +INSERT INTO LineItems VALUES (2, 7, 'ESM-23', 2, 123.34, null); +INSERT INTO LineItems VALUES (1, 6, 'QSM-39', 9, 12.12, null); +INSERT INTO LineItems VALUES (2, 6, 'ASM-45', 6, 78.77, null); +INSERT INTO LineItems VALUES (1, 5, 'ESM-48', 3, 43.87, null); +INSERT INTO LineItems VALUES (2, 5, 'WSM-98', 7, 5.40, null); +INSERT INTO LineItems VALUES (1, 4, 'RSM-57', 2, 78.90, null); +INSERT INTO LineItems VALUES (2, 4, 'XSM-78', 9, 2.34, null); +INSERT INTO LineItems VALUES (1, 3, 'DSM-59', 3, 5.70, null); +INSERT INTO LineItems VALUES (2, 3, 'DSM-53', 3, 98.78, null); +INSERT INTO LineItems VALUES (1, 2, 'DSM-37', 4, 7.80, null); +INSERT INTO LineItems VALUES (2, 2, 'FSM-12', 2, 55.78, null); +INSERT INTO LineItems VALUES (1, 1, 'ESM-48', 8, 87.60, null); +INSERT INTO LineItems VALUES (2, 1, 'ESM-23', 1, 55.40, null); + diff --git a/tests/unit/SQLMap/scripts/mysql/more-account-records.sql b/tests/unit/SQLMap/scripts/mysql/more-account-records.sql new file mode 100644 index 00000000..e9fd4ac2 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/more-account-records.sql @@ -0,0 +1,7 @@ + +INSERT INTO Accounts VALUES(6,'Jane', 'Calamity', 'Jane.Calamity@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(7,'Lucky', 'Luke', 'Lucky.Luke@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(8,'Ming', 'Li Foo', null, 'Non', 100); +INSERT INTO Accounts VALUES(9,'O''Hara', 'Steve', 'Jack.OHara@somewhere.com', 'Oui', 200); +INSERT INTO Accounts VALUES(10,'Robert', 'O''Timmins', null, 'Non', 100); + diff --git a/tests/unit/SQLMap/scripts/mysql/order-init.sql b/tests/unit/SQLMap/scripts/mysql/order-init.sql new file mode 100644 index 00000000..e83a4be3 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/order-init.sql @@ -0,0 +1,30 @@ +drop table if exists 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); +INSERT INTO Orders VALUES (3, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4',2); +INSERT INTO Orders VALUES (4, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4',1); +INSERT INTO Orders VALUES (5, 5, '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4',2); +INSERT INTO Orders VALUES (6, 5, '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4',1); +INSERT INTO Orders VALUES (7, 4, '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4',2); +INSERT INTO Orders VALUES (8, 3, '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4',1); +INSERT INTO Orders VALUES (9, 2, '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4',2); +INSERT INTO Orders VALUES (10, 1, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4',1); +INSERT INTO Orders VALUES (11, null, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY',1); + diff --git a/tests/unit/SQLMap/scripts/mysql/other-init.sql b/tests/unit/SQLMap/scripts/mysql/other-init.sql new file mode 100644 index 00000000..0281527c --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/other-init.sql @@ -0,0 +1,91 @@ + +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; + +INSERT INTO Others VALUES(1, 8888888, 0, 'Oui'); +INSERT INTO Others VALUES(2, 9999999999, 1, 'Non'); + +INSERT INTO F VALUES('f', 'fff'); +INSERT INTO E VALUES('e', 'eee'); +INSERT INTO D VALUES('d', 'ddd'); +INSERT INTO C VALUES('c', 'ccc'); +INSERT INTO B VALUES('b', 'c', null, 'bbb'); +INSERT INTO A VALUES('a', 'b', 'e', null, 'aaa'); \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql b/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql new file mode 100644 index 00000000..03b65b13 --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/swap-procedure.sql @@ -0,0 +1,2 @@ + +use IBatisNet; \ No newline at end of file diff --git a/tests/unit/SQLMap/scripts/mysql/user-init.sql b/tests/unit/SQLMap/scripts/mysql/user-init.sql new file mode 100644 index 00000000..c124fc2b --- /dev/null +++ b/tests/unit/SQLMap/scripts/mysql/user-init.sql @@ -0,0 +1,14 @@ + +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; diff --git a/tests/unit/SQLMap/scripts/sqlite/database.sql b/tests/unit/SQLMap/scripts/sqlite/database.sql new file mode 100644 index 00000000..477b4b16 --- /dev/null +++ b/tests/unit/SQLMap/scripts/sqlite/database.sql @@ -0,0 +1,242 @@ +# +# : A +# +DROP TABLE A; + +CREATE TABLE A +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + B_ID VARCHAR(50), + E_ID VARCHAR(50), + F_ID VARCHAR(50), + A_Libelle VARCHAR(50) +); + +INSERT INTO A VALUES ('a', 'b', 'e', NULL, 'aaa'); + + +# +# : Accounts +# +DROP TABLE Accounts; +CREATE TABLE Accounts +( + Account_Id INTEGER NOT NULL PRIMARY KEY, + 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 +); + +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'); +INSERT INTO Accounts VALUES ('3', 'William', 'Dalton', NULL, 'Non', '100'); +INSERT INTO Accounts VALUES ('4', 'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', '100'); +INSERT INTO Accounts VALUES ('5', 'Gilles', 'Bayon', NULL, 'Oui', '100'); + + +# +# : B +# +DROP TABLE B; +CREATE TABLE B +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + C_ID VARCHAR(50), + D_ID VARCHAR(50), + B_Libelle VARCHAR(50) +); + +INSERT INTO B VALUES ('b', 'c', NULL, 'bbb'); + + +# +# : C +# +DROP TABLE C; +CREATE TABLE C +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + C_Libelle VARCHAR(50) +); + +INSERT INTO C VALUES ('c', 'ccc'); + + +# +# : Categories +# +DROP TABLE Categories; +create table Categories +( + Category_Id INTEGER NOT NULL PRIMARY KEY, + Category_Name varchar(32), + Category_Guid varchar(36) +); + + +# +# : D +# +DROP TABLE D; +CREATE TABLE D +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + D_Libelle VARCHAR(50) +); + +INSERT INTO D VALUES ('d', 'ddd'); + + +# +# : Documents +# +DROP TABLE Documents; +CREATE TABLE Documents +( + Document_Id INT NOT NULL PRIMARY KEY, + Document_Title VARCHAR(32), + Document_Type VARCHAR(32), + Document_PageNumber INT, + Document_City VARCHAR(32) +); + +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'); +INSERT INTO Documents VALUES ('3', 'Lord of the Rings', 'Book', '3587', NULL); +INSERT INTO Documents VALUES ('4', 'Le Canard enchaine', 'Tabloid', NULL, 'Paris'); +INSERT INTO Documents VALUES ('5', 'Le Monde', 'Broadsheet', NULL, 'Paris'); +INSERT INTO Documents VALUES ('6', 'Foundation', 'Monograph', '557', NULL); + + +# +# : E +# +DROP TABLE E; +CREATE TABLE E +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + E_Libelle VARCHAR(50) +); + + +INSERT INTO E VALUES ('e', 'eee'); + + +# +# : Enumerations +# +DROP TABLE Enumerations; +create table Enumerations +( + Enum_Id int not null, + Enum_Day int not null, + Enum_Color int not null, + Enum_Month int +); + + +INSERT INTO Enumerations VALUES ('1', '1', '1', '128'); +INSERT INTO Enumerations VALUES ('2', '2', '2', '2048'); +INSERT INTO Enumerations VALUES ('3', '3', '4', '256'); +INSERT INTO Enumerations VALUES ('4', '4', '8', NULL); + + +# +# : F +# +DROP TABLE F; +CREATE TABLE F +( + ID VARCHAR(50) NOT NULL PRIMARY KEY, + F_Libelle VARCHAR(50) +); + +INSERT INTO F VALUES ('f', 'fff'); + + +# +# : LineItems +# +DROP TABLE LineItems; +CREATE TABLE LineItems +( + LineItem_Id INTEGER 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 +); + + +INSERT INTO LineItems VALUES ('1', '10', 'ESM-34', '1', '45.43', NULL); +INSERT INTO LineItems VALUES ('2', '10', 'QSM-98', '8', '8.40', NULL); +INSERT INTO LineItems VALUES ('1', '9', 'DSM-78', '2', '45.40', NULL); +INSERT INTO LineItems VALUES ('2', '9', 'TSM-12', '2', '32.12', NULL); +INSERT INTO LineItems VALUES ('1', '8', 'DSM-16', '4', '41.30', NULL); +INSERT INTO LineItems VALUES ('2', '8', 'GSM-65', '1', '2.20', NULL); +INSERT INTO LineItems VALUES ('1', '7', 'WSM-27', '7', '52.10', NULL); +INSERT INTO LineItems VALUES ('2', '7', 'ESM-23', '2', '123.34', NULL); +INSERT INTO LineItems VALUES ('1', '6', 'QSM-39', '9', '12.12', NULL); +INSERT INTO LineItems VALUES ('2', '6', 'ASM-45', '6', '78.77', NULL); +INSERT INTO LineItems VALUES ('1', '5', 'ESM-48', '3', '43.87', NULL); +INSERT INTO LineItems VALUES ('2', '5', 'WSM-98', '7', '5.40', NULL); +INSERT INTO LineItems VALUES ('1', '4', 'RSM-57', '2', '78.90', NULL); +INSERT INTO LineItems VALUES ('2', '4', 'XSM-78', '9', '2.34', NULL); +INSERT INTO LineItems VALUES ('1', '3', 'DSM-59', '3', '5.70', NULL); +INSERT INTO LineItems VALUES ('2', '3', 'DSM-53', '3', '98.78', NULL); +INSERT INTO LineItems VALUES ('1', '2', 'DSM-37', '4', '7.80', NULL); +INSERT INTO LineItems VALUES ('2', '2', 'FSM-12', '2', '55.78', NULL); +INSERT INTO LineItems VALUES ('1', '1', 'ESM-48', '8', '87.60', NULL); +INSERT INTO LineItems VALUES ('2', '1', 'ESM-23', '1', '55.40', NULL); + + +# +# : Orders +# +DROP TABLE Orders; +CREATE TABLE Orders +( + Order_Id INTEGER NOT NULL PRIMARY KEY, + Account_Id INT, + 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 +); + +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'); +INSERT INTO Orders VALUES ('3', '3', '2003-02-15 8:15:00', 'AMEX', '777777777777', '07/03', '333 Other Street', 'Regina', 'SK', 'Z4U 6Y4', '2'); +INSERT INTO Orders VALUES ('4', '2', '2003-02-15 8:15:00', 'MC', '666666666666', '08/03', '444 His Street', 'Toronto', 'ON', 'K4U 3S4', '1'); +INSERT INTO Orders VALUES ('5', '5', '2003-02-15 8:15:00', 'VISA', '555555555555', '09/03', '555 Her Street', 'Calgary', 'AB', 'J4J 7S4', '2'); +INSERT INTO Orders VALUES ('6', '5', '2003-02-15 8:15:00', 'VISA', '999999999999', '10/03', '6 Their Street', 'Victoria', 'BC', 'T4H 9G4', '1'); +INSERT INTO Orders VALUES ('7', '4', '2003-02-15 8:15:00', 'MC', '888888888888', '11/03', '77 Lucky Street', 'Edmonton', 'AB', 'R4A 0Z4', '2'); +INSERT INTO Orders VALUES ('8', '3', '2003-02-15 8:15:00', 'AMEX', '777777777777', '12/03', '888 Our Street', 'Regina', 'SK', 'S4S 7G4', '1'); +INSERT INTO Orders VALUES ('9', '2', '2003-02-15 8:15:00', 'MC', '666666666666', '01/04', '999 Your Street', 'Toronto', 'ON', 'G4D 9F4', '2'); +INSERT INTO Orders VALUES ('10', '1', '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', '99 Some Street', 'Calgary', 'AB', 'W4G 7A4', '1'); +INSERT INTO Orders VALUES ('11', NULL, '2003-02-15 8:15:00', 'VISA', '555555555555', '02/04', 'Null order', 'Calgary', 'ZZ', 'XXX YYY', '1'); + + +# +# : Others +# +DROP TABLE Others; +create table Others +( + Other_Int int, + Other_Long bigint, + Other_Bit bit not null default 0, + Other_String varchar(32) not null +); + +INSERT INTO Others VALUES ('1', '8888888', '0', 'Oui'); +INSERT INTO Others VALUES ('2', '9999999999', '1', 'Non'); + diff --git a/tests/unit/SQLMap/sqlite.xml b/tests/unit/SQLMap/sqlite.xml new file mode 100644 index 00000000..62ab2aa3 --- /dev/null +++ b/tests/unit/SQLMap/sqlite.xml @@ -0,0 +1,32 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/tests/unit/SQLMap/sqlite/backup.db b/tests/unit/SQLMap/sqlite/backup.db new file mode 100644 index 00000000..fa66b2cc Binary files /dev/null and b/tests/unit/SQLMap/sqlite/backup.db differ diff --git a/tests/unit/SQLMap/sqlite/tests.db b/tests/unit/SQLMap/sqlite/tests.db new file mode 100644 index 00000000..380ef8fa Binary files /dev/null and b/tests/unit/SQLMap/sqlite/tests.db differ -- cgit v1.2.3