sl@0: // Copyright (c) 1998-2009 Nokia Corporation and/or its subsidiary(-ies).
sl@0: // All rights reserved.
sl@0: // This component and the accompanying materials are made available
sl@0: // under the terms of "Eclipse Public License v1.0"
sl@0: // which accompanies this distribution, and is available
sl@0: // at the URL "http://www.eclipse.org/legal/epl-v10.html".
sl@0: //
sl@0: // Initial Contributors:
sl@0: // Nokia Corporation - initial contribution.
sl@0: //
sl@0: // Contributors:
sl@0: //
sl@0: // Description:
sl@0: //
sl@0: 
sl@0: // MSVC++ up to 5.0 has problems with expanding inline functions
sl@0: // This disables the mad warnings for the whole project
sl@0: #if defined(NDEBUG) && defined(__VC32__) && _MSC_VER<=1100
sl@0: #pragma warning(disable : 4710)			// function not expanded. MSVC 5.0 is stupid
sl@0: #endif
sl@0: 
sl@0: #include <d32dbms.h>
sl@0: #include <f32file.h>
sl@0: #include <e32test.h>
sl@0: #include <e32math.h>
sl@0: 
sl@0: // constructing literal TInt64 from compiler 64 bit integer rep
sl@0: 
sl@0: #ifndef I64LIT
sl@0: #if defined __GCC32__ || defined __EABI__
sl@0: #define _LINT64(val) MAKE_TINT64(TUint(val##LL>>32),TUint(val##LL&0xffffffffu))
sl@0: #else
sl@0: #define _LINT64(val) MAKE_TINT64(TUint(__int64(val)>>32),TUint(__int64(val)&0xffffffffu))
sl@0: #endif
sl@0: #else // I64LIT
sl@0: #define _LINT64 I64LIT
sl@0: #endif // I64LIT
sl@0: 
sl@0: LOCAL_D RTest TheTest(_L("t_dbsql: DBMS SQL parsing and execution tests"));
sl@0: 
sl@0: LOCAL_D RFs TheFs;
sl@0: LOCAL_D CTrapCleanup* TheTrapCleanup;
sl@0: LOCAL_D RDbs TheDbs;
sl@0: LOCAL_D RDbNamedDatabase TheDatabase;
sl@0: LOCAL_D RDbTable TheTable;
sl@0: LOCAL_D RDbView TheView;
sl@0: LOCAL_D TBuf<1024> TheSql;
sl@0: 
sl@0: const TInt KTestCleanupStack=0x20;
sl@0: const TPtrC KTestDatabase=_L("c:\\dbms-tst\\t_sql.db");
sl@0: 
sl@0: #define elementsof(array) (sizeof(array)/sizeof(array[0]))
sl@0: 
sl@0: LOCAL_C void TestCleanup()
sl@0: 	{
sl@0: 	TheTable.Close();
sl@0: 	TheView.Close();
sl@0: 	TheDatabase.Close();
sl@0: 	TheDbs.Close();
sl@0: 	TheFs.Close();
sl@0: 	/////////////
sl@0: 	RFs fsSession;
sl@0: 	TInt err = fsSession.Connect();
sl@0: 	if(err == KErrNone)
sl@0: 		{
sl@0: 		TEntry entry;
sl@0: 		if(fsSession.Entry(KTestDatabase, entry) == KErrNone)
sl@0: 			{
sl@0: 			RDebug::Print(_L("Deleting \"%S\" file.\n"), &KTestDatabase);
sl@0: 			err = fsSession.SetAtt(KTestDatabase, 0, KEntryAttReadOnly);
sl@0: 			if(err != KErrNone)
sl@0: 				{
sl@0: 				RDebug::Print(_L("Error %d changing \"%S\" file attributes.\n"), err, &KTestDatabase);
sl@0: 				}
sl@0: 			err = fsSession.Delete(KTestDatabase);
sl@0: 			if(err != KErrNone)
sl@0: 				{
sl@0: 				RDebug::Print(_L("Error %d deleting \"%S\" file.\n"), err, &KTestDatabase);
sl@0: 				}
sl@0: 			}
sl@0: 		fsSession.Close();
sl@0: 		}
sl@0: 	else
sl@0: 		{
sl@0: 		RDebug::Print(_L("Error %d connecting file session. File: %S.\n"), err, &KTestDatabase);
sl@0: 		}
sl@0: 	}
sl@0: 
sl@0: LOCAL_C void CloseDatabase()
sl@0: 	{
sl@0: 	TheDatabase.Close();
sl@0: 	}
sl@0: 
sl@0: LOCAL_C void Disconnect()
sl@0: 	{
sl@0: 	TheDbs.ResourceCheck();
sl@0: 	TheDbs.Close();
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: //Tests macros and functions.
sl@0: //If (!aValue) then the test will be panicked, the test data files will be deleted.
sl@0: static void Check(TInt aValue, TInt aLine)
sl@0: 	{
sl@0: 	if(!aValue)
sl@0: 		{
sl@0:         RDebug::Print(_L("*** Expression evaluated to false\r\n"));
sl@0: 		::TestCleanup();
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: //If (aValue != aExpected) then the test will be panicked, the test data files will be deleted.
sl@0: static void Check(TInt aValue, TInt aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
sl@0: 		::TestCleanup();
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: //Use these to test conditions.
sl@0: #define TEST(arg) ::Check((arg), __LINE__)
sl@0: #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: // Create the database
sl@0: LOCAL_C void CreateDatabase()
sl@0: 	{
sl@0: 	TInt r=TheDatabase.Replace(TheFs,KTestDatabase);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: LOCAL_C void Connect()
sl@0: 	{
sl@0: 	TInt r=TheDbs.Connect();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TheDbs.ResourceMark();
sl@0: 	}
sl@0: 
sl@0: // Open the database through the server
sl@0: LOCAL_C void ShareDatabase()
sl@0: 	{
sl@0: 	TInt r=TheDatabase.Open(TheDbs,KTestDatabase);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: struct TSelectTest
sl@0: 	{
sl@0: 	const TText* iSearchCondition;
sl@0: 	TUint iResultSet;
sl@0: 	};
sl@0: #define ROWS(n,m) (((~0u)<<(n))&((2u<<(m))-1))
sl@0: #define ROW(n) ROWS(n,n)
sl@0: #define NO_ROWS 0
sl@0: #define ALL_ROWS (~1u)		// all rows which are not NULL
sl@0: 
sl@0: class TestPredicateBase
sl@0: 	{
sl@0: protected:
sl@0: 	static void Create(const TText* aType);
sl@0: 	static void Test(const TSelectTest* aTest,TInt aCount,TInt aRows, TBool aLog);
sl@0: 	static void TestViewL(const TSelectTest* aTest,TInt aCount,TInt aRows, TBool aLog=EFalse);
sl@0: 	};
sl@0: 
sl@0: // Create the table for the predicate tests
sl@0: void TestPredicateBase::Create(const TText* aType)
sl@0: 	{
sl@0: 	TheTest.Next(TPtrC(aType));
sl@0: 	TheDatabase.Begin();
sl@0: 	TheSql.Format(_L("CREATE TABLE Compare (Id COUNTER,Test %s)"),aType);
sl@0: 	TInt r=TheDatabase.Execute(TheSql);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	r=TheTable.Open(TheDatabase,_L("Compare"),TheTable.EInsertOnly);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: // Test the predicate on the table, then on the indexed table
sl@0: void TestPredicateBase::Test(const TSelectTest* aTest,TInt aCount,TInt aRows, TBool aLog)
sl@0: 	{
sl@0:     if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("TestPredicateBase::Test\r\n"));
sl@0:         }
sl@0: 	TheTable.Close();
sl@0: 	TInt r=TheDatabase.Commit();
sl@0: 	if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("Commit %d\r\n"), r);
sl@0:         }
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TRAPD(errCode, TestViewL(aTest,aCount,aRows, aLog));
sl@0: 	if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("TestViewL %d"), errCode);
sl@0:         }
sl@0: 
sl@0: 	TEST2(errCode, KErrNone);
sl@0: 	r=TheDatabase.Execute(_L("CREATE INDEX Key ON Compare (Test)"));
sl@0:    if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("Execute %d"), r);
sl@0:         }
sl@0: 
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TRAP(errCode,TestViewL(aTest,aCount,aRows, aLog));
sl@0: 	if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("TestViewL %d"), errCode);
sl@0: 	    }
sl@0: 
sl@0: 	TEST2(errCode, KErrNone);
sl@0: 	r=TheDatabase.Execute(_L("DROP TABLE Compare"));
sl@0:     if(aLog)
sl@0:         {
sl@0:         TheTest.Printf(_L("Execute %d"), r);
sl@0:         }
sl@0: 
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: // Test the predicate on the table
sl@0: void TestPredicateBase::TestViewL(const TSelectTest* aTest,TInt aCount,TInt aRows, TBool /*aLog*/)
sl@0: 	{
sl@0: 	TUint rowMask=(2u<<aRows)-1;
sl@0: 	for (;--aCount>=0;++aTest)
sl@0: 		{
sl@0: 		TheSql.Format(_L("SELECT Id FROM Compare WHERE Test %s"),aTest->iSearchCondition);
sl@0: 		TInt r=TheView.Prepare(TheDatabase,TheSql,TheView.EReadOnly);
sl@0: 		if(r!=KErrNone)
sl@0: 		    {
sl@0:             TheTest.Printf(_L("Prepare r= %d aCount= %d  statement %S\r\n"), r, aCount, &TheSql);
sl@0: 		    }
sl@0: 		TEST2(r, KErrNone);
sl@0: 		TBool ignoreRow0=TheView.Unevaluated();
sl@0: 		r=TheView.EvaluateAll();
sl@0:  		TEST2(r, KErrNone);
sl@0: 		TUint rows=0;
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0:  			TheView.GetL();
sl@0: 			rows|=1u<<TheView.ColUint(1);
sl@0: 			}
sl@0: 		if (ignoreRow0)
sl@0: 		    {
sl@0:   			TEST((rows&~ROW(0))==(aTest->iResultSet&rowMask&~ROW(0)));
sl@0: 		    }
sl@0: 		else
sl@0: 		    {
sl@0:  			TEST(rows==(aTest->iResultSet&rowMask));
sl@0: 		    }
sl@0: 		TheView.Close();
sl@0: 		}
sl@0: 	}
sl@0: 
sl@0: typedef void (*FSetColL)(TDbColNo aCol,const TAny* aVal);
sl@0: 
sl@0: void WriteRowsL(const TAny* aValues,TInt aRows,TInt aSize,FSetColL aSetColL, TBool aLog)
sl@0: 	{
sl@0: 	for (TInt row=0;row<=aRows;++row)
sl@0: 		{
sl@0:         if(aLog)
sl@0:             {
sl@0:             TheTest.Printf(_L("row = %d"), row);
sl@0:             }
sl@0: 		TheTable.InsertL();
sl@0: 		TEST(TheTable.ColUint(1)==TUint(row));
sl@0: 		if (row>0)
sl@0: 			{	// first row is always null
sl@0: 			aSetColL(2,aValues);
sl@0: 			aValues=PtrAdd(aValues,aSize);
sl@0: 			}
sl@0: 		TheTable.PutL();
sl@0: 		}
sl@0: 	}
sl@0: 
sl@0: template <class T>
sl@0: struct SetCol
sl@0: 	{
sl@0: 	static void SetColL(TDbColNo aCol,const TAny* aVal)
sl@0: 		{
sl@0: 		TheTable.SetColL(aCol,*(const T*)aVal);
sl@0: 		}
sl@0: 	};
sl@0: 
sl@0: template <class T>
sl@0: inline void WriteRowsL(const T* aValues,TUint aRows, TBool aLog)
sl@0: 	{
sl@0: 	WriteRowsL(aValues,aRows,sizeof(T),&SetCol<T>::SetColL, aLog);
sl@0: 	}
sl@0: 
sl@0: template <class T>
sl@0: class TestPredicate : public TestPredicateBase
sl@0: 	{
sl@0: public:
sl@0: 	static void RunL();
sl@0: 	};
sl@0: 
sl@0: // Test the Predicate operators for all types
sl@0: template <class T>
sl@0: void TestPredicate<T>::RunL()
sl@0: 	{
sl@0: 	Create(T::KType);
sl@0: 	TBool log = EFalse;
sl@0: 	if((TPtrC(T::KType)).CompareF(_L("TIME"))==0)
sl@0: 	    {
sl@0:         log = ETrue;
sl@0: 	    }
sl@0: 	WriteRowsL(T::KValues,elementsof(T::KValues), log);
sl@0: 	Test(T::KTests,elementsof(T::KTests),elementsof(T::KValues),log);
sl@0: 	}
sl@0: 
sl@0: struct TypeBit
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TUint KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeBit::KType=_S("BIT");
sl@0: const TUint TypeBit::KValues[]={0,1};
sl@0: const TSelectTest TypeBit::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(1)},
sl@0: 	{_S("<>0"),ROW(2)},
sl@0: 	{_S(">0"),ROW(2)},
sl@0: 	{_S("<0"),NO_ROWS},
sl@0: 	{_S("<=0"),ROW(1)},
sl@0: 	{_S(">=0"),ALL_ROWS},
sl@0: 	{_S("=1"),ROW(2)},
sl@0: 	{_S("<>1"),ROW(1)},
sl@0: 	{_S(">1"),NO_ROWS},
sl@0: 	{_S("<1"),ROW(1)},
sl@0: 	{_S("<=1"),ALL_ROWS},
sl@0: 	{_S(">=1"),ROW(2)}
sl@0: 	};
sl@0: 
sl@0: struct TypeUint8
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TUint KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeUint8::KType=_S("UNSIGNED TINYINT");
sl@0: const TUint TypeUint8::KValues[]={0,1,127,128,254,255};
sl@0: const TSelectTest TypeUint8::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(1)},
sl@0: 	{_S("=3"),NO_ROWS},
sl@0: 	{_S("<2"),ROWS(1,2)},
sl@0: 	{_S(">=0"),ALL_ROWS},
sl@0: 	{_S("<128"),ROWS(1,3)},
sl@0: 	{_S("<>1"),ALL_ROWS-ROW(2)},
sl@0: 	{_S(">255"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeUint16
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TUint KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeUint16::KType=_S("UNSIGNED SMALLINT");
sl@0: const TUint TypeUint16::KValues[]={0,1,5000,32767,32768,65534,65535};
sl@0: const TSelectTest TypeUint16::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(1)},
sl@0: 	{_S("=3"),NO_ROWS},
sl@0: 	{_S("<2"),ROWS(1,2)},
sl@0: 	{_S(">=32768"),ROWS(5,7)},
sl@0: 	{_S("<32767"),ROWS(1,3)},
sl@0: 	{_S("<>1"),ALL_ROWS-ROW(2)},
sl@0: 	{_S(">65535"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeUint32
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TUint KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeUint32::KType=_S("UNSIGNED INTEGER");
sl@0: const TUint TypeUint32::KValues[]={0,1,2147483647u,2147483648u,3000000000u,4294967294u,4294967295u};
sl@0: const TSelectTest TypeUint32::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(1)},
sl@0: 	{_S("=3"),NO_ROWS},
sl@0: 	{_S("<2"),ROWS(1,2)},
sl@0: 	{_S(">=2147483648"),ROWS(4,7)},
sl@0: 	{_S("<2147483647"),ROWS(1,2)},
sl@0: 	{_S("<>3000000000"),ALL_ROWS-ROW(5)},
sl@0: 	{_S(">4294967295"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeInt8
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TInt KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeInt8::KType=_S("TINYINT");
sl@0: const TInt TypeInt8::KValues[]={-128,-1,0,1,127};
sl@0: const TSelectTest TypeInt8::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(3)},
sl@0: 	{_S("=-1"),ROW(2)},
sl@0: 	{_S("<2"),ROWS(1,4)},
sl@0: 	{_S(">=-128"),ALL_ROWS},
sl@0: 	{_S("<128"),ALL_ROWS},
sl@0: 	{_S("<>1"),ALL_ROWS-ROW(4)}
sl@0: 	};
sl@0: 
sl@0: struct TypeInt16
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TInt KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeInt16::KType=_S("SMALLINT");
sl@0: const TInt TypeInt16::KValues[]={-32768,-32767,-1,0,1,32766,32767};
sl@0: const TSelectTest TypeInt16::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(4)},
sl@0: 	{_S("<>0"),ALL_ROWS-ROW(4)},
sl@0: 	{_S("=-1"),ROW(3)},
sl@0: 	{_S("<2"),ROWS(1,5)},
sl@0: 	{_S(">=-40000"),ALL_ROWS},
sl@0: 	{_S("<32766"),ROWS(1,5)},
sl@0: 	{_S("=40"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeInt32
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TInt KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeInt32::KType=_S("INTEGER");
sl@0: const TInt TypeInt32::KValues[]={0x80000000/*-2147483648*/,-2147483647,-1,0,1,2147483646,2147483647};
sl@0: const TSelectTest TypeInt32::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(4)},
sl@0: 	{_S("<>0"),ALL_ROWS-ROW(4)},
sl@0: 	{_S("=-1"),ROW(3)},
sl@0: 	{_S("<2"),ROWS(1,5)},
sl@0: 	{_S(">=-2147483648"),ALL_ROWS},
sl@0: 	{_S("<2147483646"),ROWS(1,5)},
sl@0: 	{_S(">2147483647"),NO_ROWS},
sl@0: 	{_S("=40"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeInt64
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TInt64 KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeInt64::KType=_S("BIGINT");
sl@0: const TInt64 TypeInt64::KValues[]=
sl@0: 	{
sl@0: 	MAKE_TINT64(0x80000000, 0x00000000), // min int64
sl@0: 	_LINT64(-4294967296),
sl@0: 	TInt(0x80000000),			// -2147483648!
sl@0: 	-1,
sl@0: 	0u,
sl@0: 	1u,
sl@0: 	2147483647u,
sl@0: 	_LINT64(2147483648),
sl@0: 	_LINT64(4294967295),
sl@0: 	_LINT64(4294967296),
sl@0: 	_LINT64(9223372036854775807)		// max int64
sl@0: 	};
sl@0: const TSelectTest TypeInt64::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(5)},
sl@0: 	{_S("<>0"),ALL_ROWS-ROW(5)},
sl@0: 	{_S("=-1"),ROW(4)},
sl@0: 	{_S("<2"),ROWS(1,6)},
sl@0: 	{_S(">=-9223372036854775808"),ALL_ROWS},
sl@0: 	{_S("<4294967296"),ROWS(1,9)},
sl@0: 	{_S(">9223372036854775806"),ROW(11)},
sl@0: 	{_S("=40"),NO_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeReal32
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TReal32 KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeReal32::KType=_S("REAL");
sl@0: const TReal32 TypeReal32::KValues[]=
sl@0: 	{
sl@0: 	-KMaxTReal32,
sl@0: 	-1.0f,
sl@0: 	-KMinTReal32,
sl@0: 	0.0f,
sl@0: 	KMinTReal32,
sl@0: 	1.0f,
sl@0: 	KMaxTReal32
sl@0: 	};
sl@0: const TSelectTest TypeReal32::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(4)},
sl@0: 	{_S("<>0.0"),ALL_ROWS-ROW(4)},
sl@0: 	{_S("=-1"),ROW(2)},
sl@0: 	{_S("<2e1"),ROWS(1,6)},
sl@0: 	{_S(">=-100000000000000"),ROWS(2,7)},
sl@0: 	{_S("<1e-36"),ROWS(1,5)},
sl@0: 	{_S(">1e15"),ROW(7)},
sl@0: 	{_S("=.5"),NO_ROWS},
sl@0: 	{_S("<=-.0"),ROWS(1,4)},
sl@0: 	{_S("<1e40"),ALL_ROWS}
sl@0: 	};
sl@0: 
sl@0: struct TypeReal64
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TReal64 KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeReal64::KType=_S("DOUBLE");
sl@0: const TReal64 TypeReal64::KValues[]=
sl@0: 	{
sl@0: 	-KMaxTReal64,
sl@0: 	-1.0f,
sl@0: 	-KMinTReal64,
sl@0: 	0.0f,
sl@0: 	KMinTReal64,
sl@0: 	1.0f,
sl@0: 	KMaxTReal64
sl@0: 	};
sl@0: const TSelectTest TypeReal64::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=0"),ROW(4)},
sl@0: 	{_S("<>0"),ALL_ROWS-ROW(4)},
sl@0: 	{_S("=-1"),ROW(2)},
sl@0: 	{_S("<2"),ROWS(1,6)},
sl@0: 	{_S(">=-100000000000000"),ROWS(2,7)},
sl@0: 	{_S("<1e-300"),ROWS(1,5)},
sl@0: 	{_S(">1e15"),ROW(7)},
sl@0: 	{_S("=.5"),NO_ROWS},
sl@0: 	{_S("<=0"),ROWS(1,4)},
sl@0: 	{_S("<1e40"),ROWS(1,6)}
sl@0: 	};
sl@0: 
sl@0: struct TypeTime
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TTime KValues[];
sl@0: 	static const TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeTime::KType=_S("TIME");
sl@0: const TTime TypeTime::KValues[]=
sl@0: 	{
sl@0: 	TInt64(0u),				// zero date/time
sl@0: 	_L(":085815"),			// 8:58:15am
sl@0: 	_L("19181010:110000"),	// 11am, 11 Nov 1918
sl@0: 	_L("19750226:"),		// midnight, 27 Mar 1975
sl@0: 	_L("19961130:235959"),	// 11:59:59pm, 31 Dec 1996
sl@0: 	_L("19970000:"),		// midnight, 1 Jan 1997
sl@0: 	_L("19970611:210000"),	// 9pm, 12 July 1997
sl@0: 	_L("19980309:214500"),	// 9:45pm, 10 April 1998
sl@0: 	_L("20700608:")			// midnight, 9 July 2070
sl@0: 	};
sl@0: const TSelectTest TypeTime::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROW(0)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS},
sl@0: 	{_S("=#12am#"),ROW(1)},
sl@0: 	{_S("<#Jan 1 2100#"),ALL_ROWS},
sl@0: 	{_S("<>#31/12/1996 23:59:59#"),ALL_ROWS-ROW(5)},
sl@0: 	{_S("<#9a#"),ROWS(1,2)},
sl@0: 	{_S(">=#11:59:59pm, 31 Dec 1996#"),ROWS(5,9)},
sl@0: 	{_S("=#9:45pm 10 April, 1998#"),ROW(8)},
sl@0: 	{_S("=#8:58:15#"),ROW(2)}
sl@0: 	};
sl@0: 
sl@0: struct TypeText
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	static const TText* const KValues[];
sl@0: 	static TSelectTest KTests[];
sl@0: 	};
sl@0: const TText* const TypeText::KType=_S("VARCHAR(100)");
sl@0: const TText* const TypeText::KValues[]=
sl@0: 	{
sl@0: 	_S(""),					// this should be equivalent to NULL
sl@0: 	_S("a"),
sl@0: 	_S("aa"),
sl@0: 	_S("aba"),
sl@0: 	_S("like"),
sl@0: 	_S("abcdefghijklmnopqrstuvwxyzlike"),
sl@0: 	_S("likeabcdefghijklmnopqrstuvwxyz"),
sl@0: 	_S("abcdefghijklmnopqrstuvwxyzlikeabcdefghijklmnopqrstuvwxyz"),
sl@0: 	_S("abcdefghijklmnopqrstuvwxyzliveabcdefghijklmnopqrstuvwxyz"),
sl@0: 	_S("l'ke"),
sl@0: 	_S("'Tis")
sl@0: 	};
sl@0: TSelectTest TypeText::KTests[]=
sl@0: 	{
sl@0: 	{_S("IS NULL"),ROWS(0,1)},
sl@0: 	{_S("IS NOT NULL"),ALL_ROWS-ROW(1)},
sl@0: //
sl@0: 	{_S("=''"),ROWS(0,1)},					// equivalent to IS NULL
sl@0: 	{_S("<>''"),ALL_ROWS-ROW(1)},			// equivalent to IS NOT NULL
sl@0: 	{_S(">''"),ALL_ROWS-ROW(1)},			// equivalent to IS NOT NULL
sl@0: 	{_S("<''"),NO_ROWS},					// expression is trivially false
sl@0: 	{_S("<=''"),ROWS(0,1)},
sl@0: 	{_S(">=''"),ALL_ROWS+ROW(0)},			// expression is trivially true
sl@0: //
sl@0: 	{_S("LIKE ''"),ROWS(0,1)},			// synonomous with IS NULL
sl@0: 	{_S("NOT LIKE ''"),ALL_ROWS-ROW(1)},
sl@0: 	{_S("LIKE '?*'"),ALL_ROWS-ROW(1)},	// synonomous with IS NOT NULL
sl@0: 	{_S("NOT LIKE '?*'"),ROWS(0,1)},
sl@0: 	{_S("LIKE '*'"),ALL_ROWS+ROW(0)},	// trivially true
sl@0: 	{_S("NOT LIKE '*'"),NO_ROWS},
sl@0: //
sl@0: 	{_S("='a'"),ROW(2)},
sl@0: 	{_S("<'ab'"),ROWS(0,3)+ROW(11)},
sl@0: 	{_S("<'abc'"),ROWS(0,4)+ROW(11)},
sl@0: 	{_S("<'b'"),ROWS(0,4)+ROW(6)+ROWS(8,9)+ROW(11)},
sl@0: 	{_S(">'abc'"),ROWS(5,10)},
sl@0: 	{_S("='like'"),ROW(5)},
sl@0: 	{_S("='l''ke'"),ROW(10)},
sl@0: 	{_S("='''Tis'"),ROW(11)},
sl@0: //
sl@0: 	{_S("LIKE 'a'"),ROW(2)},
sl@0: 	{_S("LIKE 'a*'"),ROWS(2,4)+ROW(6)+ROWS(8,9)},
sl@0: 	{_S("LIKE '*a'"),ROWS(2,4)},
sl@0: 	{_S("LIKE 'a*a'"),ROWS(3,4)},
sl@0: 	{_S("LIKE '*a*'"),ROWS(2,4)+ROWS(6,9)},
sl@0: //
sl@0: 	{_S("LIKE 'like'"),ROW(5)},
sl@0: 	{_S("LIKE 'l?ke'"),ROW(5)+ROW(10)},
sl@0: 	{_S("LIKE 'like*'"),ROW(5)+ROW(7)},
sl@0: 	{_S("LIKE '*like'"),ROWS(5,6)},
sl@0: 	{_S("LIKE '*like*'"),ROWS(5,8)},
sl@0: 	{_S("LIKE '*likeit*'"),NO_ROWS},
sl@0: 	{_S("LIKE '*li?e*'"),ROWS(5,9)},
sl@0: 	{_S("LIKE '?*li?e*'"),ROW(6)+ROWS(8,9)},
sl@0: 	{_S("LIKE '*li?e*?'"),ROWS(7,9)},
sl@0: 	{_S("LIKE '?*li?e*?'"),ROWS(8,9)},
sl@0: 	{_S("LIKE '*?k?*'"),ROWS(5,10)},
sl@0: 	{_S("LIKE '*?i?e*'"),ROWS(5,9)},
sl@0: //
sl@0: 	{_S("LIKE '*e*'"),ROWS(5,10)},
sl@0: 	{_S("LIKE '*z*k*e*'"),ROW(6)+ROW(8)},
sl@0: 	{_S("LIKE '\?\?k?'"),ROW(5)+ROW(10)},
sl@0: 	{_S("LIKE '\?\?k*'"),ROW(5)+ROW(7)+ROW(10)},
sl@0: 	{_S("LIKE '*''*'"),ROWS(10,11)},
sl@0: 	{_S("LIKE '?''\?\?'"),ROW(10)},
sl@0: 	{_S("LIKE '?'"),ROW(2)},
sl@0: 	{_S("LIKE '\?\?\?\?'"),ROW(5)+ROWS(10,11)},
sl@0: 	{_S("LIKE '\?\?*\?\?'"),ROWS(5,11)},
sl@0: 	{_S("LIKE '''*'"),ROW(11)}
sl@0: 	};
sl@0: 
sl@0: TEMPLATE_SPECIALIZATION struct SetCol<const TText* const>
sl@0: 	{
sl@0: 	static void SetColL(TDbColNo aCol,const TAny* aVal)
sl@0: 		{
sl@0: 		TheTable.SetColL(aCol,TPtrC(*(const TText* const*)aVal));
sl@0: 		}
sl@0: 	};
sl@0: TEMPLATE_SPECIALIZATION struct SetCol<const TText*>
sl@0: 	{
sl@0: 	static void SetColL(TDbColNo aCol,const TAny* aVal)
sl@0: 		{
sl@0: 		TheTable.SetColL(aCol,TPtrC(*(const TText* const*)aVal));
sl@0: 		}
sl@0: 	};
sl@0: 
sl@0: struct TypeLongText
sl@0: 	{
sl@0: public:
sl@0: 	static const TText* const KType;
sl@0: 	};
sl@0: const TText* const TypeLongText::KType=_S("LONG VARCHAR");
sl@0: 
sl@0: class TestPredicate2 : public TestPredicateBase
sl@0: 	{
sl@0: public:
sl@0: 	static void RunL();
sl@0: 	};
sl@0: 
sl@0: // write rows equivalent to TypeText and use its tests
sl@0: void TestPredicate2::RunL()
sl@0: 	{
sl@0: 	Create(TypeLongText::KType);
sl@0: 	TBuf<1022> fill=_S("abcdefghijklmnopqrstuvqxyz");
sl@0: 	fill.AppendFill('.',fill.MaxLength()-fill.Length());
sl@0: 
sl@0: 	for (TInt row=0;row<=TInt(elementsof(TypeText::KValues));++row)
sl@0: 		{
sl@0: 		TheTable.InsertL();
sl@0: 		TheTable.SetColL(1,row);
sl@0: 		if (row>0)
sl@0: 			{
sl@0: 			RDbColWriteStream blob;
sl@0: 			blob.OpenLC(TheTable,2);
sl@0: 			switch (row)
sl@0: 				{
sl@0: 			case 0:
sl@0: 				break;
sl@0: 			case 1: case 2: case 3: case 4: case 5: case 10: case 11:
sl@0: 				blob.WriteL(TPtrC(TypeText::KValues[row-1]));
sl@0: 				break;
sl@0: 			case 6:
sl@0: 				blob.WriteL(fill);
sl@0: 				blob.WriteL(_L("like"));
sl@0: 				break;
sl@0: 			case 7:
sl@0: 				blob.WriteL(_L("like"));
sl@0: 				blob.WriteL(fill);
sl@0: 				break;
sl@0: 			case 8:
sl@0: 				blob.WriteL(fill);
sl@0: 				blob.WriteL(_L("like"));
sl@0: 				blob.WriteL(fill);
sl@0: 				break;
sl@0: 			case 9:
sl@0: 				blob.WriteL(fill);
sl@0: 				blob.WriteL(_L("live"));
sl@0: 				blob.WriteL(fill);
sl@0: 				break;
sl@0: 				}
sl@0: 			blob.CommitL();
sl@0: 			CleanupStack::PopAndDestroy();
sl@0: 			}
sl@0: 		TheTable.PutL();
sl@0: 		}
sl@0: 	TheTable.Close();
sl@0: 	TInt r=TheDatabase.Commit();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TestViewL(TypeText::KTests,elementsof(TypeText::KTests),elementsof(TypeText::KValues));
sl@0: 	CDbKey& key=*CDbKey::NewLC();
sl@0: 	key.AddL(TDbKeyCol(_L("Test"),120));
sl@0: 	r=TheDatabase.CreateIndex(_L("Key"),_L("Compare"),key);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	CleanupStack::PopAndDestroy();
sl@0: 	TestViewL(TypeText::KTests,elementsof(TypeText::KTests),elementsof(TypeText::KValues));
sl@0: 	r=TheDatabase.Execute(_L("DROP TABLE Compare"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: * Utility for DEF063276 fix.
sl@0: */
sl@0: 
sl@0: _LIT(KTypeTextKTests44, "Z:\\test\\TypeTextKTests44.dat");
sl@0: _LIT(KTypeTextKTests46, "Z:\\test\\TypeTextKTests46.dat");
sl@0: _LIT(KTypeTextKTests47, "Z:\\test\\TypeTextKTests47.dat");
sl@0: 
sl@0: static void ReadDesc(TDes& aDes, const TDesC& aFilename, RFs& aFs)
sl@0: 	{
sl@0:     TheTest.Printf(_L("---ReadDesc(), aFilename=%S\r\n"), &aFilename);
sl@0: 	RFile file;
sl@0: 	TInt err = file.Open(aFs, aFilename, EFileRead);
sl@0: 	TheTest.Printf(_L("Open file aFilename=%S err = %d\r\n"), &aFilename, err);
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	TPtr8 ptr(reinterpret_cast<TUint8*>(const_cast<TUint16*>(aDes.Ptr())), aDes.MaxSize());
sl@0: 	err = file.Read(ptr);
sl@0: 	TheTest.Printf(_L("Read file aFilename=%S err = %d\r\n"), &aFilename, err);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	aDes.SetLength(ptr.Length() / sizeof(TText));
sl@0: 	file.Close();
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID          SYSLIB-DBMS-CT-0633
sl@0: @SYMTestCaseDesc        Tests the Predicate operators for all types
sl@0: @SYMTestPriority        Medium
sl@0: @SYMTestActions         Attempt to check with different types
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ                 REQ0000
sl@0: */
sl@0: LOCAL_C void TestPredicatesL()
sl@0: 	{
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0633 init "));
sl@0: 	CreateDatabase();
sl@0: 	TestPredicate<TypeBit>::RunL();
sl@0: 	TestPredicate<TypeUint8>::RunL();
sl@0: 	TestPredicate<TypeUint16>::RunL();
sl@0: 	TestPredicate<TypeUint32>::RunL();
sl@0: 	TestPredicate<TypeInt8>::RunL();
sl@0: 	TestPredicate<TypeInt16>::RunL();
sl@0: 	TestPredicate<TypeInt32>::RunL();
sl@0: 	TestPredicate<TypeInt64>::RunL();
sl@0: 	TestPredicate<TypeReal32>::RunL();
sl@0: 	TestPredicate<TypeReal64>::RunL();
sl@0: 	TestPredicate<TypeTime>::RunL();
sl@0: 
sl@0: 	/**
sl@0: 	* Work around for DEF063276.
sl@0: 	* These literals are now loaded from Z:\test\TypeTextKTests44.dat,
sl@0: 	* Z:\test\data\TypeTextKTests44.dat and Z:\test\TypeTextKTests44.dat respectively.
sl@0: 	* Bullseye Coverage corrupts these literals to avoid this they are stored in files as to not be touched by Bullseye Coverage.
sl@0: 	*/
sl@0: 
sl@0: 	TBuf<16> buf44;
sl@0: 	ReadDesc(buf44, KTypeTextKTests44, TheFs);
sl@0: 	TypeText::KTests[44].iSearchCondition = const_cast<TText*>(buf44.PtrZ());
sl@0: 
sl@0: 	TBuf<32> buf46(TypeText::KTests[46].iSearchCondition);
sl@0: 	ReadDesc(buf46, KTypeTextKTests46, TheFs);
sl@0: 	TypeText::KTests[46].iSearchCondition = const_cast<TText*>(buf46.PtrZ());
sl@0: 
sl@0: 	TBuf<32> buf47(TypeText::KTests[47].iSearchCondition);
sl@0: 	ReadDesc(buf47, KTypeTextKTests47, TheFs);
sl@0: 	TypeText::KTests[47].iSearchCondition = const_cast<TText*>(buf47.PtrZ());
sl@0: 
sl@0: 	// End fix.
sl@0: 
sl@0: 	TestPredicate<TypeText>::RunL();
sl@0: 	TestPredicate2::RunL();
sl@0: 	CloseDatabase();
sl@0: 	TheTest.End();
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID          SYSLIB-DBMS-CT-0634
sl@0: @SYMTestCaseDesc        DML Query test
sl@0:                         Test for RDbNamedDatabase::Execute() function
sl@0: @SYMTestPriority        Medium
sl@0: @SYMTestActions         Tests for CREATE TABLE,CREATE UNIQUE INDEX,INSET INTO,UPDATE,DELETE,DROP queries
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ                 REQ0000
sl@0: */
sl@0: LOCAL_C void TestDataModificationlanguage()
sl@0: 	{
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0634 init "));
sl@0: 	Connect();
sl@0: 	ShareDatabase();
sl@0: 	TInt r=TheDatabase.Execute(_L("CREATE TABLE test (ID INTEGER NOT NULL,SALARY DOUBLE)"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	r=TheDatabase.Execute(_L("CREATE UNIQUE INDEX key ON test (ID)"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 
sl@0: 	TheTest.Next(_L("insert-statements"));
sl@0: 	r=TheDatabase.Execute(_L("INSERT INTO test VALUES (0,0)"));
sl@0: 	TEST(r==1);
sl@0: 	r=TheDatabase.Execute(_L("INSERT INTO test (ID) VALUES (1)"));
sl@0: 	TEST(r==1);
sl@0: 	r=TheDatabase.Execute(_L("INSERT INTO test (SALARY,ID) VALUES (20.4,2)"));
sl@0: 	TEST(r==1);
sl@0: 
sl@0: 	TheTest.Next(_L("update-statements"));
sl@0: 	r=TheDatabase.Execute(_L("UPDATE test SET SALARY=30000 WHERE ID=1"));
sl@0: 	TEST(r==1);
sl@0: 
sl@0: 	TheTest.Next(_L("delete-statements"));
sl@0: 	r=TheDatabase.Execute(_L("DELETE FROM test WHERE SALARY<40"));
sl@0: 	TEST(r==2);
sl@0: 	r=TheDatabase.Execute(_L("DELETE FROM test"));
sl@0: 	TEST(r==1);
sl@0: 	r=TheDatabase.Execute(_L("DROP TABLE test"));
sl@0: 	TEST2(r, KErrNone);
sl@0: //
sl@0: 	TheTest.Next(_L("larger table"));
sl@0: 	r=TheDatabase.Execute(_L("CREATE TABLE test (ID COUNTER,DATA INTEGER)"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 
sl@0: 	TheTest.Next(_L("insert"));
sl@0: 	r=TheDatabase.Begin();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TBuf<256> sql;
sl@0: 	for (TInt ii=0;ii<100;++ii)
sl@0: 		{
sl@0: 		sql.Format(_L("INSERT INTO test (DATA) VALUES (%D)"),100-ii);
sl@0: 		r=TheDatabase.Execute(sql);
sl@0: 		TEST(r==1);
sl@0: 		}
sl@0: 	r=TheDatabase.Commit();
sl@0: 	TEST2(r, KErrNone);
sl@0: 
sl@0: 	TheTest.Next(_L("update"));
sl@0: 	r=TheDatabase.Execute(_L("UPDATE test SET DATA=200 WHERE ID>=40 AND ID<60"));
sl@0: 	TEST(r==20);
sl@0: 
sl@0: 	TheTest.Next(_L("delete"));
sl@0: 	r=TheDatabase.Execute(_L("DELETE FROM test WHERE DATA>90"));
sl@0: 	TEST(r==30);
sl@0: 
sl@0: 	TheTest.Next(_L("update"));
sl@0: 	r=TheDatabase.Execute(_L("UPDATE test SET DATA=-1"));
sl@0: 	TEST(r==70);
sl@0: 
sl@0: 	TheTest.Next(_L("delete"));
sl@0: 	r=TheDatabase.Execute(_L("DELETE FROM test"));
sl@0: 	TEST(r==70);
sl@0: 	r=TheDatabase.Execute(_L("DROP TABLE test"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	CloseDatabase();
sl@0: 	Disconnect();
sl@0: 	TheTest.End();
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID          SYSLIB-DBMS-CT-0635
sl@0: @SYMTestCaseDesc        DBMS SQL parsing and execution tests.Tests for database index order
sl@0: 						Test for RDbNamedDatabase::Execute() function
sl@0: @SYMTestPriority        Medium
sl@0: @SYMTestActions         Tests for order by index
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ                 REQ0000
sl@0: */
sl@0: LOCAL_C void TestOrderByL()
sl@0: 	{
sl@0: 	static const TReal TestDataSalary[]={10,34000,15000,53200,17800,240000};
sl@0: 	static const TText* const TestDataNames[]={_S("gopher"),_S("james '007' bond"),_S("moneypenny"),_S("Q"),_S("james '007' bond"),_S("M")};
sl@0: //
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0635 init "));
sl@0: 	Connect();
sl@0: 	ShareDatabase();
sl@0: 	TheDatabase.Begin();
sl@0: 	TInt r=TheDatabase.Execute(_L("CREATE TABLE test (ID INTEGER NOT NULL,SALARY DOUBLE,NAME VARCHAR)"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	r=TheDatabase.Execute(_L("CREATE UNIQUE INDEX key ON test (ID)"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 
sl@0: 	TheTest.Next(_L("insert data"));
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT ID,SALARY,NAME FROM test"),TheView.EInsertOnly);
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TInt ii;
sl@0: 	for (ii=0;ii<6;++ii)
sl@0: 		{
sl@0: 		TheView.InsertL();
sl@0: 		TheView.SetColL(1,6-ii);
sl@0: 		TheView.SetColL(2,TestDataSalary[ii]);
sl@0: 		TheView.SetColL(3,TPtrC(TestDataNames[ii]));
sl@0: 		TheView.PutL();
sl@0: 		}
sl@0: 	r=TheDatabase.Commit();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <index>"));
sl@0: 	// test the index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT ID FROM test ORDER BY ID"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(!TheView.Unevaluated());
sl@0: 	TInt c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TInt last=TheView.ColInt(1);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TInt v=TheView.ColInt(1);
sl@0: 			TEST(v>last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==6);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <no-index> 1"));
sl@0: 	// test that no index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT SALARY FROM test ORDER BY SALARY"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(TheView.Unevaluated());
sl@0: 	r=TheView.EvaluateAll();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TReal last=TheView.ColReal(1);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TReal v=TheView.ColReal(1);
sl@0: 			TEST(v>=last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==6);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <no-index> 2"));
sl@0: 	// test that no index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT SALARY FROM test ORDER BY SALARY,NAME"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(TheView.Unevaluated());
sl@0: 	r=TheView.EvaluateAll();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TReal last=TheView.ColReal(1);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TReal v=TheView.ColReal(1);
sl@0: 			TEST(v>=last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==6);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <no-index> 3"));
sl@0: 	// test that no index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT NAME FROM test ORDER BY NAME"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(TheView.Unevaluated());
sl@0: 	r=TheView.EvaluateAll();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TBuf<50> last=TheView.ColDes(1);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TPtrC v=TheView.ColDes(1);
sl@0: 			TEST(v>=last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==6);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <no-index> 4"));
sl@0: 	// test that no index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT NAME FROM test ORDER BY NAME,SALARY"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(TheView.Unevaluated());
sl@0: 	r=TheView.EvaluateAll();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TBuf<50> last=TheView.ColDes(1);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TPtrC v=TheView.ColDes(1);
sl@0: 			TEST(v>=last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==6);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by + search <no-index>"));
sl@0: 	// test that no index is used here
sl@0: 	r=TheView.Prepare(TheDatabase,_L("SELECT ID,SALARY FROM test WHERE SALARY>30000 ORDER BY SALARY DESC"));
sl@0: 	TEST2(r, KErrNone);
sl@0: 	TEST(TheView.Unevaluated());
sl@0: 	r=TheView.EvaluateAll();
sl@0: 	TEST2(r, KErrNone);
sl@0: 	c=0;
sl@0: 	if (TheView.FirstL())
sl@0: 		{
sl@0: 		++c;
sl@0: 		TheView.GetL();
sl@0: 		TReal last=TheView.ColReal(2);
sl@0: 		while (TheView.NextL())
sl@0: 			{
sl@0: 			++c;
sl@0: 			TheView.GetL();
sl@0: 			TReal v=TheView.ColReal(2);
sl@0: 			TEST(v<=last);
sl@0: 			last=v;
sl@0: 			}
sl@0: 		}
sl@0: 	TEST(c==3);
sl@0: 	TEST(c==TheView.CountL());
sl@0: 	TheView.Close();
sl@0: //
sl@0: 	CloseDatabase();
sl@0: 	Disconnect();
sl@0: 
sl@0: 	TheTest.Next(_L("Order by <index> Finished"));
sl@0: 	TheTest.End();
sl@0: 	}
sl@0: 
sl@0: LOCAL_C void doMain()
sl@0: 	{
sl@0: 	TheTest.Start(_L("Predicate tests"));
sl@0: 	TRAPD(errCode, TestPredicatesL());
sl@0: 	TEST2(errCode, KErrNone);
sl@0: 
sl@0: 	TheTest.Next(_L("DML execution"));
sl@0: 	TestDataModificationlanguage();
sl@0: 
sl@0: 	TheTest.Next(_L("ORDER BY clause"));
sl@0: 	TRAP(errCode, TestOrderByL());
sl@0: 	TEST2(errCode, KErrNone);
sl@0: 	TheTest.End();
sl@0: 	}
sl@0: 
sl@0: // Prepare the test directory.
sl@0: LOCAL_C void setupTestDirectory()
sl@0:     {
sl@0: 	TInt r=TheFs.Connect();
sl@0: 	TEST2(r, KErrNone);
sl@0: //
sl@0: 	r=TheFs.MkDir(KTestDatabase);
sl@0: 	TEST(r==KErrNone || r==KErrAlreadyExists);
sl@0: 	}
sl@0: 
sl@0: // Initialise the cleanup stack.
sl@0: LOCAL_C void setupCleanup()
sl@0:     {
sl@0: 	TheTrapCleanup=CTrapCleanup::New();
sl@0: 	TEST(TheTrapCleanup!=NULL);
sl@0: 	TRAPD(r,\
sl@0: 		{\
sl@0: 		for (TInt i=KTestCleanupStack;i>0;i--)\
sl@0: 			CleanupStack::PushL((TAny*)0);\
sl@0: 		CleanupStack::Pop(KTestCleanupStack);\
sl@0: 		});
sl@0: 	TEST2(r, KErrNone);
sl@0: 	}
sl@0: 
sl@0: // Test streaming conversions.
sl@0: GLDEF_C TInt E32Main()
sl@0:     {
sl@0: 	TheTest.Title();
sl@0: 	setupTestDirectory();
sl@0: 	setupCleanup();
sl@0: 	__UHEAP_MARK;
sl@0: //
sl@0: 	TheTest.Start(_L("Standard database"));
sl@0: 	doMain();
sl@0: 
sl@0: 	// clean up data file used by this test - must be done before call to End() - DEF047652
sl@0: 	::TestCleanup();
sl@0: 	TheTest.End();
sl@0: //
sl@0: 	__UHEAP_MARKEND;
sl@0: 
sl@0: 
sl@0: 	delete TheTrapCleanup;
sl@0: 	TheTest.Close();
sl@0: 	return 0;
sl@0:     }