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/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 +++++ 10 files changed, 2289 insertions(+) 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 (limited to 'tests/unit/SQLMap/maps/mssql') 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 -- cgit v1.2.3