// Copyright (c) 1998-2009 Nokia Corporation and/or its subsidiary(-ies).

// All rights reserved.

// This component and the accompanying materials are made available

// under the terms of "Eclipse Public License v1.0"

// which accompanies this distribution, and is available

// at the URL "http://www.eclipse.org/legal/epl-v10.html".

//

// Initial Contributors:

// Nokia Corporation - initial contribution.

//

// Contributors:

//

// Description:

//

#include <d32dbms.h>

#include <s32file.h>

#include <e32test.h>

#include <e32math.h>

#include "D32TABLE.H"

// MSVC++ up to 5.0 has problems with expanding inline functions

// This disables the mad warnings for the whole project

#if defined(NDEBUG) && defined(__VC32__) && _MSC_VER<=1100

#pragma warning(disable : 4710)			// function not expanded. MSVC 5.0 is stupid

#endif

LOCAL_D RTest test(_L("t_dbindex - Test DBMS indexing and ordering"));

LOCAL_D CTrapCleanup* TheTrapCleanup;

LOCAL_D RDbs TheDbs;

LOCAL_D RDbNamedDatabase TheDatabase;

LOCAL_D RDbTable TheTable;

LOCAL_D RDbView TheView;

LOCAL_D RFs TheFs;

LOCAL_D TBuf<0x200> TheBuf;

const TInt KTestCleanupStack=0x20;

const TPtrC KTestDatabase=_L("C:\\DBMS-TST\\T_INDEX.DB");

const TPtrC KTableName(_S("Table"));

const TPtrC KIndexName(_S("index"));

const TPtrC KIndexTwo(_S("index_two"));

const TPtrC KColumnName(_S("column"));

const TPtrC KColumnTwo(_S("column2"));

const TPtrC KTableName1(_S("Table1"));

const TPtrC KColumnName1(_S("column1"));

const TPtrC KIndexName1(_S("index1"));

const TPtrC KSelectOrdered(_L("select column from table order by column"));

#define elementsof(array) (sizeof(array)/sizeof(array[0]))

void Check(TInt aValue,TInt aExpected,TInt aLine)

	{

	if (aValue!=aExpected)

		{

		test.Printf(_L("*** Expected %d: got %d\r\n"),aExpected,aValue);

		test.operator()(EFalse,aLine);

		}

	}

#define test2(a,b) Check(a,b,__LINE__)

//

// Open the database (shared access) (SYMBIAN_REMOVE_TRIVIAL_ENCRYPTION version)

//

LOCAL_C void OpenDatabase()

	{

	TInt r=TheDatabase.Open(TheDbs,KTestDatabase);

	test (r==KErrNone);

	}

//

// Create the database (SYMBIAN_REMOVE_TRIVIAL_ENCRYPTION version)

//

LOCAL_C void CreateDatabase()

	{

	TInt r=TheDatabase.Replace(TheFs,KTestDatabase);

	test (r==KErrNone);

	TheDatabase.Close();

	OpenDatabase();

	}

LOCAL_C void CloseDatabase()

	{

	TheDatabase.Close();

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-0618

@SYMTestCaseDesc        Tests for RDbNamedDatabase functionality

@SYMTestPriority        Medium

@SYMTestActions         Tests for RDbNamedDatabase::CreateIndex()

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

LOCAL_C void TestIndexBuildL()

	{

	test.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0618 Create table "));

	TheDatabase.Begin();

	CDbColSet *cs=CDbColSet::NewLC();

	cs->AddL(TDbCol(KColumnName,EDbColInt32));

	cs->AddL(TDbCol(KColumnTwo,EDbColInt32));

	test(TheDatabase.CreateTable(KTableName,*cs)==KErrNone);

	CleanupStack::PopAndDestroy();

	test.Next(_L("create indices"));

	CDbKey *key=CDbKey::NewLC();

	key->AddL(TDbKeyCol(KColumnName));

	key->MakeUnique();

	test(TheDatabase.CreateIndex(KIndexName,KTableName,*key)==KErrNone);

	key->Clear();

	key->AddL(TDbKeyCol(KColumnTwo));

	key->MakeUnique();

	test(TheDatabase.CreateIndex(KIndexTwo,KTableName,*key)==KErrNone);

	CleanupStack::PopAndDestroy();

	test.Next(_L("Populate table"));

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	TheTable.InsertL();

	TheTable.SetColL(1,1);

	TheTable.SetColL(2,-1);

	TheTable.PutL();

	TheTable.InsertL();

	TheTable.SetColL(1,2);

	TheTable.SetColL(2,-2);

	TheTable.PutL();

	test(TheDatabase.Commit()==KErrNone);

	test.Next(_L("Check order"));

	test(TheTable.SetNoIndex()==KErrNone);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	test(TheTable.CountL()==2);

	test(TheTable.NextL());

	TheTable.GetL();

	test(TheTable.ColInt(1)==1);

	test(TheTable.NextL());

	TheTable.GetL();

	test(TheTable.ColInt(1)==2);

	test(!TheTable.NextL());

	test(TheTable.SetIndex(KIndexTwo)==KErrNone);

	test(TheTable.CountL()==2);

	test(TheTable.NextL());

	TheTable.GetL();

	test(TheTable.ColInt(1)==2);

	test(TheTable.NextL());

	TheTable.GetL();

	test(TheTable.ColInt(1)==1);

	test(!TheTable.NextL());

 	test(TheTable.SetNoIndex()==KErrNone);

	test(TheTable.CountL()==2);

	test(TheTable.NextL());

	test(TheTable.NextL());

	test(!TheTable.NextL());

	TheTable.Close();

	test.Next(_L("Drop indices"));

	test(TheDatabase.DropIndex(KIndexTwo,KTableName)==KErrNone);

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	test(TheTable.SetIndex(KIndexTwo)!=KErrNone);

	TheTable.Close();

	test(TheDatabase.DropIndex(KIndexName,KTableName)==KErrNone);

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	test(TheTable.SetIndex(KIndexName)!=KErrNone);

	test(TheTable.SetIndex(KIndexTwo)!=KErrNone);

	TheTable.Close();

	test(TheDatabase.DropTable(KTableName)==KErrNone);

	test.End();

	}

LOCAL_C TInt CountRowsL()

	{

	TInt count=0;

	while (TheTable.NextL())

		++count;

	return count;

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-0619

@SYMTestCaseDesc        RDbNamedDatabase::Execute() function test

@SYMTestPriority        Medium

@SYMTestActions         Tests for CREATE TABLE,CREATE INDEX and CREATE UNIQUE INDEX query

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

LOCAL_C void TestPersistenceL()

	{

	test.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0619 Create table "));

	TheDatabase.Begin();

	test(TheDatabase.Execute(_L("CREATE TABLE Table (column CHAR(120) NOT NULL)"))==KErrNone);

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	TheTable.InsertL();

	TheTable.SetColL(1,_L("a"));

	TheTable.PutL();

	TheTable.InsertL();

	TheTable.SetColL(1,_L("b"));

	TheTable.PutL();

	TheTable.InsertL();

	TheTable.SetColL(1,_L("c"));

	TheTable.PutL();

	TheTable.InsertL();

	TheTable.SetColL(1,_L("d"));

	TheTable.PutL();

	TheTable.InsertL();

	TheTable.SetColL(1,_L("e"));

	TheTable.PutL();

	TheTable.Close();

	test(TheDatabase.Commit()==KErrNone);

	test.Next(_L("Build indices"));

	test (TheDatabase.Execute(_L("CREATE INDEX index ON table (column ASC)"))==KErrNone);

	test (TheDatabase.Execute(_L("CREATE UNIQUE INDEX index_two ON table (column DESC)"))==KErrNone);

	test.Next(_L("Close and re-open database"));

	CloseDatabase();

	OpenDatabase();

	test.Next(_L("Check indices"));

	CDbKey* key=TheDatabase.KeyL(KIndexName,KTableName);

	test (key->Count()==1);

	test (key->Comparison()==EDbCompareNormal);

	test (!key->IsUnique());

	test ((*key)[0].iName.CompareF(KColumnName)==0);

	test ((*key)[0].iOrder==TDbKeyCol::EAsc);

	delete key;

	key=TheDatabase.KeyL(KIndexTwo,KTableName);

	test (key->Count()==1);

	test (key->Comparison()==EDbCompareNormal);

	test (key->IsUnique());

	test ((*key)[0].iName.CompareF(KColumnName)==0);

	test ((*key)[0].iOrder==TDbKeyCol::EDesc);

	delete key;

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	test(TheTable.SetNoIndex()==KErrNone);

	test(CountRowsL()==5);

	test(TheTable.CountL()==5);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	test(TheTable.CountL()==5);

	test(CountRowsL()==5);

	test(TheTable.SetIndex(KIndexTwo)==KErrNone);

	test(TheTable.CountL()==5);

	test(CountRowsL()==5);

	TheTable.Close();

	test.Next(_L("Drop indices"));

	TheDatabase.Begin();

	test (TheDatabase.Execute(_L("DROP INDEX index_two FROM table"))==KErrNone);

	test (TheDatabase.Execute(_L("DROP INDEX index FROM table"))==KErrNone);

	test (TheDatabase.Execute(_L("DROP TABLE table"))==KErrNone);

	test(TheDatabase.Commit()==KErrNone);

	test.End();

	}

LOCAL_C void BuildTablePrologL(TDbColType aType,TInt aAttribs=0)

	{

	TheDatabase.Begin();

	CDbColSet *cs=CDbColSet::NewLC();

	TDbCol col(KColumnName,aType);

	col.iAttributes=aAttribs;

	cs->AddL(col);

	test(TheDatabase.CreateTable(KTableName,*cs)==KErrNone);

	CleanupStack::PopAndDestroy();

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TInt& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TUint& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TInt64& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TReal32& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TReal64& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TTime& aValue)

	{aSet.SetColL(col,aValue);}

inline void SetColL(RDbRowSet& aSet,TDbColNo col,const TPtrC& aValue)

	{aSet.SetColL(col,aValue);}

template <class T>

void BuildTableL(TDbColType aType,TInt aAttribs,T aValues[],TInt aCount)

	{

	BuildTablePrologL(aType,aAttribs);

	for (TInt ii=0;ii<aCount;++ii)

		{

		TheTable.InsertL();

		SetColL(TheTable,1,aValues[ii]);

		TheTable.PutL();

		}

	test(TheDatabase.Commit()==KErrNone);

	test (TheTable.CountL()==aCount);

	TheTable.Close();

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-0620

@SYMTestCaseDesc        Setting the specified index as the active index for a table test

@SYMTestPriority        Medium

@SYMTestActions         Create an index and set as active index for the table.

                        Tests for RDbNamedDatabase::CreateIndex(),RDbNamedDatabase::Commit()

						RDbTable::Open(),RDbTable::SetIndex() functions.

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

LOCAL_C void BuildIndexL(TDbTextComparison aComparison=EDbCompareNormal,TInt aLength=KDbUndefinedLength)

	{

	test.Next(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-0620 Test index order "));

	TheDatabase.Begin();

	CDbKey *key=CDbKey::NewLC();

	key->AddL(TDbKeyCol(KColumnName,aLength));

	key->MakeUnique();

	key->SetComparison(aComparison);

	test(TheDatabase.CreateIndex(KIndexName,KTableName,*key)==KErrNone);

	CleanupStack::PopAndDestroy();

	test (TheDatabase.Commit()==KErrNone);

	test(TheTable.Open(TheDatabase,KTableName)==KErrNone);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TInt& aValue)

	{aValue=aSet.ColInt(col);}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TUint& aValue)

	{aValue=aSet.ColUint(col);}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TInt64& aValue)

	{aValue=aSet.ColInt64(col);}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TReal32& aValue)

	{aValue=aSet.ColReal32(col);}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TReal64& aValue)

	{aValue=aSet.ColReal64(col);}

inline void GetCol(RDbRowSet& aSet,TDbColNo col,TTime& aValue)

	{aValue=aSet.ColTime(col);}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-0621

@SYMTestCaseDesc        RDbRowSet ordering test

@SYMTestPriority        Medium

@SYMTestActions         Reorder the row set data with RDbRowSet::GetL(),SetL() functions.

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

template <class T>

void TestOrderingL(RDbRowSet& aSet,T,TInt aCount)

	{

	test.Next( _L( " @SYMTestCaseID:SYSLIB-DBMS-CT-0621 " ) );

	test(aSet.CountL()==aCount);

	TInt count=0;

	if (aSet.FirstL())

		{

		++count;

		aSet.GetL();

		T last;

		GetCol(aSet,1,last);

		while (aSet.NextL())

			{

			++count;

			aSet.GetL();

			T current;

			GetCol(aSet,1,current);

			test(last<current);

			last=current;

			}

		}

	test(count==aCount);

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-0622

@SYMTestCaseDesc        RDbTable::SeekL() function test

@SYMTestPriority        Medium

@SYMTestActions         Tests for the retrieved column value

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

template <class T>

void TestSeekL(RDbTable& aTable,T,const T aValues[],TInt aCount)

	{

	test.Next( _L( " @SYMTestCaseID:SYSLIB-DBMS-CT-0622 Test index seeking " ) );

	for (TInt ii=0;ii<aCount;++ii)

		{

		test(aTable.SeekL(aValues[ii]));

		aTable.GetL();

		T val;

		GetCol(aTable,1,val);

		test(aValues[ii]==val);

		}

	}

/**

aVal argument is used in the test functions (TestType & TestOrdering) and has no meaning outside them.

It is used only to avoid some compiler varnings and to determine the correct template type

@SYMTestCaseID          SYSLIB-DBMS-CT-0623

@SYMTestCaseDesc        Tests for RDbTable,RDbRowSet classes

@SYMTestPriority        Medium

@SYMTestActions         Call up Test for table ordering and index seeking functions

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

template <class T>

void TestTypeL( TDbColType aType, TInt aAttribs, const T aValues[], TInt aCount )

	{

	test.Start( _L( " @SYMTestCaseID:SYSLIB-DBMS-CT-0623 Build table " ) );

	BuildTableL( aType, aAttribs, aValues, aCount );

	T t(0);

//

	test.Next( _L( "Test ORDER BY ordering" ) );

	test( TheView.Prepare( TheDatabase, KSelectOrdered ) == KErrNone );

	test( TheView.EvaluateAll() == KErrNone );

	TestOrderingL( TheView, t, aCount );

	TheView.Close();

//

	BuildIndexL();

	TestOrderingL( TheTable, t, aCount );

//

	TestSeekL( TheTable, t, aValues, aCount );

	TheTable.Close();

//

	test( TheDatabase.DropTable( KTableName ) == KErrNone );

	test.End();

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-1322

@SYMTestCaseDesc        Text ordering test

@SYMTestPriority        Medium

@SYMTestActions         Tests for RDbRowSet::Next(),RDbRowSet::GetL(),RDbRowSet::ColDes() functions

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

void TestTextOrderingL(RDbRowSet& aSet,TInt aCount,const TTextOps& aComp)

	{

	test.Next(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-1322 "));

	test(aSet.CountL()==aCount);

	TInt count=0;

	while (aSet.NextL())

		{

		aSet.GetL();

		TPtrC current=aSet.ColDes(1);

		if (count++>0)

			test(aComp.Compare(TheBuf,current)<0);

		TheBuf=current;

		}

	test(count==aCount);

	}

/**

@SYMTestCaseID          SYSLIB-DBMS-CT-1323

@SYMTestCaseDesc        Tests for RDbView,RDbTable classes

@SYMTestPriority        Medium

@SYMTestActions         Wrapper function to call up for text ordering and text indexing tests

@SYMTestExpectedResults Test must not fail

@SYMREQ                 REQ0000

*/

void TestTextL(const TPtrC aValues[],TInt aCount,TDbTextComparison aComparison,TInt aLength=KDbUndefinedLength)

	{

	const TTextOps& comp=TTextOps::Ops(aComparison);

	test.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-CT-1323 Build table "));

	BuildTableL(EDbColText,0,aValues,aCount);

//

	test.Next(_L("Test ORDER BY ordering"));

	test (TheView.Prepare(TheDatabase,TDbQuery(KSelectOrdered,aComparison))==KErrNone);

	test (TheView.EvaluateAll()==KErrNone);

	TestTextOrderingL(TheView,aCount,comp);

	TheView.Close();

//

	BuildIndexL(aComparison,aLength);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	TestTextOrderingL(TheTable,aCount,comp);

//

	test.Next(_L("Test index seeking"));

	for (TInt ii=0;ii<aCount;++ii)

		{

		test(TheTable.SeekL(aValues[ii]));

		TheTable.GetL();

		test(comp.Compare(aValues[ii],TheTable.ColDes(1))==0);

		}

//

	TheTable.Close();

	test(TheDatabase.DropTable(KTableName)==KErrNone);

	test.End();

	}

void TestLongTextOrderingL(RDbRowSet& aSet,TInt aCount,const TTextOps& aComp)

	{

	test(aSet.CountL()==aCount);

	TInt count=0;

	HBufC* prev=0;

	while (aSet.NextL())

		{

		aSet.GetL();

		TInt len=aSet.ColLength(1);

		HBufC* buf=HBufC::NewL(len);

		RDbColReadStream s;

		s.OpenLC(aSet,1);

		TPtr des(buf->Des());

		s.ReadL(des,len);

		CleanupStack::PopAndDestroy();

		if (count++)

			test (aComp.Compare(*buf,*prev)>=0);

		delete prev;

		prev=buf;

		}

	delete prev;

	test(count==aCount);

	}

LOCAL_C void OrderByLongTextL(TInt aCount,TDbTextComparison aComparison)

	{

	test (TheView.Prepare(TheDatabase,TDbQuery(KSelectOrdered,aComparison))==KErrNone);

	test (TheView.EvaluateAll()==KErrNone);

	TestLongTextOrderingL(TheView,aCount,TTextOps::Ops(aComparison));

	TheView.Close();

	}

LOCAL_C void TestLongTextL(const TPtrC aValues[],TInt aCount,TDbTextComparison aComparison,TInt aLength=KDbUndefinedLength)

	{

	const TTextOps& comp=TTextOps::Ops(aComparison);

	test.Start(_L("Build table"));

	BuildTableL(EDbColLongText,0,aValues,aCount);

//

	test.Next(_L("Test ORDER BY ordering"));

	OrderByLongTextL(aCount,aComparison);

//

	BuildIndexL(aComparison,aLength);

	test(TheTable.SetIndex(KIndexName)==KErrNone);

	TestLongTextOrderingL(TheTable,aCount,comp);

//

	test.Next(_L("Test index seeking"));

	for (TInt ii=0;ii<aCount;++ii)

		{

		test(TheTable.SeekL(aValues[ii]));

		TheTable.GetL();

		RDbColReadStream strm;

		strm.OpenLC(TheTable,1);