// Copyright (c) 2006-2010 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 <e32test.h>

#include <f32file.h>

#include <sqldb.h>

#include <f32file.h>

///////////////////////////////////////////////////////////////////////////////////////

static RFs TheFs;

static RTest TheTest(_L("t_sqldefect2 test"));

static RSqlDatabase TheDb1;

static RSqlDatabase TheDb2;

_LIT(KTestDir, "c:\\test\\");

_LIT(KTestDatabase1, "c:\\test\\t_sqldefect2.db");

_LIT(KTestDatabaseJournal1, "c:\\test\\t_sqldefect2.db-journal");

_LIT(KServerTempDir, "c:\\private\\10281e17\\temp\\");

///////////////////////////////////////////////////////////////////////////////////////

//Deletes all created test files.

void DestroyTestEnv()

	{

    TheDb2.Close();

    TheDb1.Close();

	(void)RSqlDatabase::Delete(KTestDatabase1);

	TheFs.Close();

	}

///////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////

//Test macros and functions

void Check1(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		DestroyTestEnv();

		RDebug::Print(_L("*** Line %d\r\n"), aLine);

		TheTest(EFalse, aLine);

		}

	}

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

	{

	if(aValue != aExpected)

		{

		DestroyTestEnv();

		RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);

		TheTest(EFalse, aLine);

		}

	}

#define TEST(arg) ::Check1((arg), __LINE__)

#define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)

///////////////////////////////////////////////////////////////////////////////////////

//Creates file session instance and the test directory

void CreateTestEnv()

    {

	TInt err = TheFs.Connect();

	TEST2(err, KErrNone);

	err = TheFs.MkDir(KTestDir);

	TEST(err == KErrNone || err == KErrAlreadyExists);

	}

/**

@SYMTestCaseID          PDS-SQL-CT-4154

@SYMTestCaseDesc        Test for DEF143062: SQL, "CREATE INDEX" sql crashes SQL server.

                        The test creates a database with one empty table and establishes two connections

                        to that database. Then, while the first connection is at the middle of a read

                        transaction, the second connection attempts to create an index.

                        If the defect is not fixed, the SQL server will crash.

@SYMTestPriority        High

@SYMTestActions         DEF143062: SQL, "CREATE INDEX" sql crashes SQL server.

@SYMTestExpectedResults Test must not fail

@SYMDEF                 DEF143062

*/

void DEF143062()

    {

    (void)RSqlDatabase::Delete(KTestDatabase1);

    TInt err = TheDb1.Create(KTestDatabase1);

    TEST2(err, KErrNone);

    err = TheDb1.Exec(_L("CREATE TABLE T0(Thread INTEGER, LocalIndex INTEGER, Inserts INTEGER, Updates INTEGER, IndexMod8 INTEGER)"));

    TEST(err >= 0);

    err = TheDb2.Open(KTestDatabase1);

    TEST2(err, KErrNone);

    RSqlStatement stmt;

    err = stmt.Prepare(TheDb1, _L8("SELECT COUNT(Thread) FROM T0 WHERE Thread = 0"));

    TEST2(err, KErrNone);

    err = stmt.Next();

    TEST2(err, KSqlAtRow);

    err = TheDb2.Exec(_L8("CREATE INDEX T0INDEX ON T0(Thread,IndexMod8)"));//crashes the SQL server if the defect is not fixed 

    TEST2(err, KSqlErrLocked);

    stmt.Close();

    TheDb2.Close();

    TheDb1.Close();

    err = RSqlDatabase::Delete(KTestDatabase1);

    TEST2(err, KErrNone);

    }

/**

@SYMTestCaseID          PDS-SQL-CT-4155

@SYMTestCaseDesc        Test for DEF143061: SQL, SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT value is too big.

                                         The test verifies that after comitting a big transaction, the journal file size is made equal the 

                                          max journal file size limit of 64Kb.

@SYMTestPriority        High

@SYMTestActions         DEF143061: SQL, SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT value is too big..

@SYMTestExpectedResults Test must not fail

@SYMDEF                 DEF143061

*/

void DEF143061()

    {

    (void)RSqlDatabase::Delete(KTestDatabase1);

    //"Auto" compaction is used in order to see how the journal file is immediatelly used.

    _LIT8(KConfig, "compaction=auto");

    TInt err = TheDb1.Create(KTestDatabase1, &KConfig);

    TEST2(err, KErrNone);

    err = TheDb1.Exec(_L("CREATE TABLE A(I INTEGER, B BLOB)"));

    TEST(err >= 0);

    const TInt KBlobSize = 100000;//bigger than the journal size limit

    HBufC8* buf = HBufC8::New(KBlobSize);

    TEST(buf != NULL);

    TPtr8 ptr = buf->Des();

    ptr.SetLength(KBlobSize);

    RSqlStatement stmt;

    err = stmt.Prepare(TheDb1, _L("INSERT INTO A VALUES(1, :Prm)"));

    TEST2(err, KErrNone);

    ptr.Fill(TChar('N'));

    err = stmt.BindBinary(0, ptr);

    TEST2(err, KErrNone);

    err = stmt.Exec();

    TEST2(err, 1);

    stmt.Close();

    //Try to update the BLOB in the record that was just inserted. This operation should create a big journal file.

    err = stmt.Prepare(TheDb1, _L("UPDATE A SET B=:Prm WHERE I=1"));

    TEST2(err, KErrNone);

    ptr.Fill(TChar('Y'));

    err = stmt.BindBinary(0, ptr);

    TEST2(err, KErrNone);

    err = stmt.Exec();

    TEST2(err, 1);

    stmt.Close();

    //Check the journal file size. It should be less than the 64Kb limit defined in sqlite_macro.mmh file.  

    TEntry entry;

    err = TheFs.Entry(KTestDatabaseJournal1, entry);

    TEST2(err, KErrNone);

    TInt64 fsize = entry.FileSize();

    TEST(fsize <= SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT);

    delete buf;

    TheDb1.Close();

    err = RSqlDatabase::Delete(KTestDatabase1);

    TEST2(err, KErrNone);

    }

/**

@SYMTestCaseID          PDS-SQL-CT-4156

@SYMTestCaseDesc        Test for DEF143150: SQL, strftime() returns incorrect result.

                        The test takes the current universal time (using TTime) 

                        and the current time retrieved from the SQL  server.

                        The test compares the times and expects the difference to be no more than

                        1 second. 

@SYMTestPriority        High

@SYMTestActions         DEF143150: SQL, strftime() returns incorrect result

@SYMTestExpectedResults Test must not fail

@SYMDEF                 DEF143150

*/

void DEF143150()

    {

    (void)RSqlDatabase::Delete(KTestDatabase1);

    TInt err = TheDb1.Create(KTestDatabase1);

    TEST2(err, KErrNone);

    //Home date & time

    TBuf<50> dtstr1;

    TTime time;

    time.UniversalTime();

    TDateTime dt = time.DateTime();

    RSqlStatement stmt;

    err = stmt.Prepare(TheDb1, _L("SELECT strftime('%Y-%m-%d,%H:%M:%S','now')"));

    TEST2(err, KErrNone);

    err = stmt.Next();

    TEST2(err, KSqlAtRow);

    //SQLite date & time

    TBuf<50> dtstr2;

    err = stmt.ColumnText(0, dtstr2);

    TEST2(err, KErrNone);

    stmt.Close();

    TheDb1.Close();

    err = RSqlDatabase::Delete(KTestDatabase1);

    TEST2(err, KErrNone);

    dtstr1.Format(_L("%04d-%02d-%02d,%02d:%02d:%02d"), dt.Year(), dt.Month() + 1, dt.Day() + 1, dt.Hour(), dt.Minute(), dt.Second());

    TheTest.Printf(_L("Universal date&time=\"%S\"\n"), &dtstr1);

    TheTest.Printf(_L("SQLite    date&time=\"%S\"\n"), &dtstr2);

    //Comapare and fail if dates are not equal (+- 1 second)

    TLex lex;

    lex = dtstr2.Mid(0, 4);

    TInt sqlyear;

    err = lex.Val(sqlyear);

    TEST2(err, KErrNone);

    lex = dtstr2.Mid(5, 2);

    TInt sqlmonth;

    err = lex.Val(sqlmonth);

    TEST2(err, KErrNone);

    lex = dtstr2.Mid(8, 2);

    TInt sqlday;

    err = lex.Val(sqlday);

    TEST2(err, KErrNone);

    lex = dtstr2.Mid(11, 2);

    TInt sqlhour;

    err = lex.Val(sqlhour);

    TEST2(err, KErrNone);

    lex = dtstr2.Mid(14, 2);

    TInt sqlminute;

    err = lex.Val(sqlminute);

    TEST2(err, KErrNone);

    lex = dtstr2.Mid(17, 2);

    TInt sqlsecond;

    err = lex.Val(sqlsecond);

    TEST2(err, KErrNone);

    TDateTime sqldt(sqlyear, (TMonth)(sqlmonth - 1), sqlday - 1, sqlhour, sqlminute, sqlsecond, 0);

    TTime sqltime(sqldt);

    TTimeIntervalSeconds diff;

    err = sqltime.SecondsFrom(time, diff);

    TEST2(err, KErrNone);

    TEST(diff.Int() <= 1);

    }

static TInt KillProcess(const TDesC& aProcessName)

    {

    TFullName name;

    TBuf<64> pattern(aProcessName);

    TInt length = pattern.Length();

    pattern += _L("*");

    TFindProcess procFinder(pattern);

    while (procFinder.Next(name) == KErrNone)

        {

        if (name.Length() > length)

            {//If found name is a string containing aProcessName string.

            TChar c(name[length]);

            if (c.IsAlphaDigit() ||

                c == TChar('_') ||

                c == TChar('-'))

                {

                // If the found name is other valid application name

                // starting with aProcessName string.

                continue;

                }

            }

        RProcess proc;

        if (proc.Open(name) == KErrNone)

            {

            proc.Kill(0);

            }

        proc.Close();

        }

    return KErrNone;

    }

/**

@SYMTestCaseID          PDS-SQL-CT-4210

@SYMTestCaseDesc        Test for the change "Temp files created during sql operations are not deleted after rebooting the phone" 

@SYMTestPriority        High

@SYMTestActions         Kill the sql server

                        Create two temp files in sql server's private directory

                        Start the sql server

                        Test that the temp files do not exist.

@SYMTestExpectedResults Test must not fail

*/

void DeleteTempFile()

    {   

    _LIT(KSqlSrvName, "sqlsrv.exe");

    _LIT(KTempFile1, "TMP00052.$$$");

    _LIT(KTempFile2, "TMP00044.$$$");

    KillProcess(KSqlSrvName);

    //Create two temp file in c:\\private\\10281e17\\temp\\ folder

    TInt err = TheFs.MkDir(KServerTempDir);

    TEST(err == KErrNone || err == KErrAlreadyExists);

    RFile file;

    TFileName filename1(KServerTempDir);

    TFileName filename2(KServerTempDir);

    filename1.Append(KTempFile1);

    filename2.Append(KTempFile2);

    err = file.Replace(TheFs, filename1, 0);

    file.Close();

    TEST2(err, KErrNone);

    err = file.Replace(TheFs, filename2, 0);

    file.Close();

    TEST2(err, KErrNone);

    //Create a database that should start sql server

    err = TheDb1.Create(KTestDatabase1);

    TEST(err == KErrNone || err == KErrAlreadyExists);

    //Test that the temp files have been deleted during server's start-up

    TUint dummy;

    err = TheFs.Att(filename1, dummy);

    TEST2(err, KErrNotFound);

    err = TheFs.Att(filename2, dummy);

    TEST2(err, KErrNotFound);

    TheDb1.Close();

    err = RSqlDatabase::Delete(KTestDatabase1);

    TEST2(err, KErrNone);

    }

TInt TempFilesCount()

	{

    _LIT(KServerTempDirMask, "c:\\private\\10281e17\\temp\\*.*");

	CDir* dir = NULL;

	TInt err = TheFs.GetDir(KServerTempDirMask, KEntryAttNormal, ESortNone, dir);

	TEST2(err, KErrNone);

	TInt tmpFileCount = dir->Count();

	delete dir;

	return tmpFileCount;

	}

/**

@SYMTestCaseID          PDS-SQL-CT-4211

@SYMTestCaseDesc        Test for the change "Temp files created during sql operations are not deleted after rebooting the phone" 

@SYMTestPriority        High

@SYMTestActions         The test creates a database and runs a set of statements that

						will lead to a delayed creation of a temp file.

						At the end the test checks that the temp file was created.

@SYMTestExpectedResults Test must not fail

*/

void TempFileTest()

	{

    (void)RSqlDatabase::Delete(KTestDatabase1);

    TInt err = TheDb1.Create(KTestDatabase1);

    TEST2(err, KErrNone);

    //Get the number of the files in the SQL temp directory 

	TInt tmpFileCount = TempFilesCount();

    //    

    err = TheDb1.Exec(_L("CREATE TABLE t1(x UNIQUE); INSERT INTO t1 VALUES(1)"));

    TEST(err >= 0);

    err = TheDb1.Exec(_L("BEGIN; UPDATE t1 SET x = 2; UPDATE t1 SET x = 3; COMMIT"));

    TEST(err >= 0);

    //Check that a temp file really was created

	TInt tmpFileCount2 = TempFilesCount();

	TEST(tmpFileCount2 > tmpFileCount);

    //

    TheDb1.Close();

    err = RSqlDatabase::Delete(KTestDatabase1);

    TEST2(err, KErrNone);

/**

@SYMTestCaseID          PDS-SQL-CT-4213

@SYMTestCaseDesc        Tests the ability of the SQL server to store empty strings and retrieve them as 

						text column values, not NULLs.

						Change: ou1cimx1#504388. 

@SYMTestPriority        High

@SYMTestActions         The test creates a database and a table and stores there empty strings.

						Then the test retrieves the stored column values and verifies that the column type is

						"text", not "null".

@SYMTestExpectedResults Test must not fail

*/

void EmptyTextColumnTest()

	{

	_LIT8(KEncUtf16, "encoding=\"UTF-16\"");

	_LIT8(KEncUtf8, "encoding=\"UTF-8\"");

	TPtrC8 enc[] = {KEncUtf16(), KEncUtf8()};

	for(TInt i=0;i<(sizeof(enc)/sizeof(enc[0]));++i)

		{

		(void)RSqlDatabase::Delete(KTestDatabase1);

		TInt err = TheDb1.Create(KTestDatabase1, &enc[i]);

		TEST2(err, KErrNone);

		//Insert records with empty text column values using RSqlDatabase::Exec()

	    err = TheDb1.Exec(_L("CREATE TABLE A(ID INTEGER, T TEXT)"));

	    TEST(err >= 0);

	    err = TheDb1.Exec(_L("INSERT INTO A VALUES(1, '')"));

	    TEST2(err, 1);

	    err = TheDb1.Exec(_L8("INSERT INTO A VALUES(2, '')"));

	    TEST2(err, 1);

		//Insert a record with empty text column value using RSqlParamWriteStream

	    RSqlStatement stmt;

	    err = stmt.Prepare(TheDb1, _L("INSERT INTO A(ID, T) VALUES(:P1, :P2)"));

	    TEST2(err, KErrNone);

		err = stmt.BindInt(0, 3);

	    TEST2(err, KErrNone);

	    RSqlParamWriteStream strm;

	    err = strm.BindText(stmt, 1);

	    TEST2(err, KErrNone);

	    TRAP(err, strm.WriteL(KNullDesC));

	    TEST2(err, KErrNone);

	    strm.Close();

	    err = stmt.Exec();

	    TEST2(err, 1);

	    stmt.Close();

		//Insert records with empty text column values using RSqlStatement::Bind()

	    err = stmt.Prepare(TheDb1, _L("INSERT INTO A(ID, T) VALUES(:P1, :P2)"));

	    TEST2(err, KErrNone);

		err = stmt.BindInt(0, 4);

	    TEST2(err, KErrNone);

		err = stmt.BindText(1, KNullDesC);

	    TEST2(err, KErrNone);

	    err = stmt.Exec();

	    TEST2(err, 1);

	    //

	    err = stmt.Reset();

	    TEST2(err, KErrNone);

		err = stmt.BindInt(0, 5);

	    TEST2(err, KErrNone);

	    _LIT(KEmptyStr, "");

		err = stmt.BindText(1, KEmptyStr);

	    TEST2(err, KErrNone);

	    err = stmt.Exec();

	    TEST2(err, 1);

	    stmt.Close();

	    //Read the empty text column values

	    err = stmt.Prepare(TheDb1, _L("SELECT T FROM A"));

	    TEST2(err, KErrNone);

	    TInt cnt = 0;

	    while((err = stmt.Next()) == KSqlAtRow)

	    	{

			++cnt;

			TPtrC val;

			err = stmt.ColumnText(0, val);

			TEST2(err, KErrNone);

			TEST2(val.Length(), 0);

			TSqlColumnType type = stmt.ColumnType(0);

			TEST2(type, ESqlText);

	    	}

	    stmt.Close();

	    TEST2(err, KSqlAtEnd);

	    TEST2(cnt, 5);

	    //

	    TheDb1.Close();

	    err = RSqlDatabase::Delete(KTestDatabase1);

	    TEST2(err, KErrNone);

		}

	}

void DoTestsL()

	{

	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-4154 DEF143062: SQL, \"CREATE INDEX\" sql crashes SQL server"));

	DEF143062();

    TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-4155 DEF143061: SQL, SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT value is too big"));