// sqlsrv.cpp

// 

// Copyright (c) 2010 Accenture. All rights reserved.

// This component and the accompanying materials are made available

// under the terms of the "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:

// Accenture - Initial contribution

//

#include "sqlsrv.h"

#include "sqlcmd_open.h"

_LIT(KNewLine, "\r\n");

_LIT(KPrompt, "ok\r\n");

_LIT(KHistoryFile, "c:\\system\\console\\fshell_sqlsrv\\history");

const TInt KFileBufferSize = 32768;

CCmdSqlSrv*	CCmdSqlSrv::iOnlyInstance = NULL;

CCommandBase* CCmdSqlSrv::NewLC()

	{

	if (CCmdSqlSrv::iOnlyInstance)

		{

		User::LeaveIfError(KErrAlreadyExists);

		}

	CCmdSqlSrv* self = new (ELeave)CCmdSqlSrv();

	CCmdSqlSrv::iOnlyInstance = self;

	CleanupStack::PushL(self);

	self->BaseConstructL();

	return self;

	}

//static:

//Check if a string is wrapped by double quote, and removed them

//

void CCmdSqlSrv::StripWrapDoubleQuote(HBufC& aText)

	{

	RBuf text;

	text.Create(aText);

	text.Trim();

	if (text.Left(1)==_L("\"") && text.Right(1)== _L("\""))

		{

		//delete right side quote

		text.Delete(text.Length()-1, 1);

		//delete left side quote

		text.Delete(0,1);

		aText = text;

		}

	text.Close();

	}

//static 

CCmdSqlSrv* CCmdSqlSrv::GetServer()

	{

	return CCmdSqlSrv::iOnlyInstance;	

	}

CCmdSqlSrv::CCmdSqlSrv()

	: CServerBase(0, KPrompt, KHistoryFile), iNextCommandId(1)

	{

	iTmpFileNo = 1;

	iDatabaseOpened = EFalse;

	}

void CCmdSqlSrv::ConstructL()

	{

	BaseConstructL();

	}

CCmdSqlSrv::~CCmdSqlSrv()

	{

	iSqlDb.Close();	

	CCmdSqlSrv::iOnlyInstance = NULL;

	}

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

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

const TDesC& CCmdSqlSrv::Name() const

	{

	_LIT(KName, "sqlsrv");

	return KName;

	}

const TDesC& CCmdSqlSrv::Description() const

	{

	_LIT(KDescription, "a command to interact with Symbian SQLite server");

	return KDescription;

	}

void CCmdSqlSrv::ArgumentsL(RCommandArgumentList& /*aArguments*/)

	{

	}

void CCmdSqlSrv::OptionsL(RCommandOptionList& /*aOptions*/)

	{

	}

TInt CCmdSqlSrv::NextCommandId()

	{

	return iNextCommandId++;

	}

//when retrieving SQL records, if there is any binary columns and user has specified

//a Temp file template, then we will generate a temp file and dump the binary into such file.

void CCmdSqlSrv::MakeTempFilename(TDes& aTmpFile, TDesC& aTemplate)

	{

	TFileName2 tmpTemplate(aTemplate);

	TPtrC drvPath = tmpTemplate.DriveAndPath();

	TPtrC name = tmpTemplate.Name();

	TPtrC ext = tmpTemplate.Ext();

	aTmpFile.Format(_L("%S%S.%02d%S"), &drvPath, &name, iTmpFileNo, &ext);

	iTmpFileNo++;

	}

void CCmdSqlSrv::ReportResult(const TServerCommandId& aId, const TDesC& aName, TRefByValue<const TDesC> aFmt, ...)

	{

	CheckNewConsoleLine();

	DoPrintf(_L("result:%u:%S:"), aId.Value(), &aName);

	VA_LIST list;

	VA_START(list, aFmt);

	DoPrintList(aFmt, list, ENewLine);

	VA_END(list);

	}

void CCmdSqlSrv::InitializeL()

	{

	CServerCommandFactory& factory = Factory();

	factory.AddLeafCommandL<CSqlCmdExit>();

	factory.AddLeafCommandL<CSqlCmdOpen>();

	factory.AddLeafCommandL<CSqlCmdCreate>();

	factory.AddLeafCommandL<CSqlCmdExec>();

	factory.AddLeafCommandL<CSqlCmdClose>();	

	factory.AddLeafCommandL<CSqlCmdState>();

	factory.AddLeafCommandL<CSqlCmdAttach>();

	factory.AddLeafCommandL<CSqlCmdDetach>();

#ifdef SQL_COMPACT	

	factory.AddLeafCommandL<CSqlCmdCompact>();

#endif

	}

void CCmdSqlSrv::DoPrintf(TRefByValue<const TDesC> aFmt, ...)

	{

	VA_LIST list;

	VA_START(list, aFmt);

	DoPrintList(aFmt, list);

	VA_END(list);

	}

void CCmdSqlSrv::DoPrintf(TRefByValue<const TDesC8> aFmt, ...)

	{

	VA_LIST list;

	VA_START(list, aFmt);

	DoPrintList(aFmt, list);

	VA_END(list);

	}

void CCmdSqlSrv::DoPrintList(TRefByValue<const TDesC> aFmt, VA_LIST& aList, TPrintPostfix aPostfix)

	{

	TOverflowTruncate overflow;

	TBuf<0x100> buf;

	buf.AppendFormatList(aFmt, aList, &overflow);

	DoPrint(buf, aPostfix);

	}

void CCmdSqlSrv::DoPrintList(TRefByValue<const TDesC8> aFmt, VA_LIST& aList, TPrintPostfix aPostfix)

	{

	TOverflowTruncate8 overflow;

	TBuf8<0x200> buf;

	buf.AppendFormatList(aFmt, aList, &overflow);

	if (buf.Length() > 0x100) buf.SetLength(0x100); // Truncate to half the buffer size so that the call to Expand doesn't panic

	TPtrC wideBuf = buf.Expand();

	DoPrint(wideBuf, aPostfix);

	}

void CCmdSqlSrv::DoPrint(const TDesC& aDes, TPrintPostfix aPostfix)

	{

	Stdout().Write(aDes);

	if ((aPostfix == ENewLine) && ((aDes.Length() < KNewLine().Length()) || (aDes.Right(KNewLine().Length()) != KNewLine)))

		{

		Stdout().Write(KNewLine);

		}

	}

void CCmdSqlSrv::Report(const TServerCommandId& /*aId*/, const TDesC& /*aDes*/)

	{

	//TODO?

//	Log(aId, aDes);

	}

void CCmdSqlSrv::Report(const TServerCommandId& /*aId*/, const TDesC8& /*aDes*/)

	{

	//TODO?

//	Log(aId, aDes);

	}

void CCmdSqlSrv::ReportWarning(const TServerCommandId& aId, const TDesC& aDes)

	{

	CheckNewConsoleLine();

	DoPrintf(_L("warning:%u:%S\r\n"), aId.Value(), &aDes);

	}

void CCmdSqlSrv::ReportError(const TServerCommandId& aId, TInt aError, const TDesC& aDes)

	{

	CheckNewConsoleLine();

	DoPrintf(_L("error:%d:%u:%S\r\n"), aError, aId.Value(), &aDes);

	}

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

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

//aCommand: the command which is invoking this function, SHOULD NOT BE NULL

//

void CCmdSqlSrv::SqlCreateL(TDesC& aFilename, CServerCommandBase* aCommand)

	{

	if (iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("A database is already opened"));

	iSqlDb.CreateL(aFilename);

	//Print size information if no error occurrs

	TInt size = iSqlDb.Size();

	ReportResult(aCommand->Id(), aCommand->Name(), _L("Database created, size: %d bytes.\r\n"), size);

	iDatabaseOpened = ETrue;

	}

void CCmdSqlSrv::SqlOpenL(TDesC& aFilename, CServerCommandBase* aCommand)

	{

	if (iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("A database is already opened"));

	iSqlDb.OpenL(aFilename);

	//Print size information if no error occurrs

	TInt size = iSqlDb.Size();

	ReportResult(aCommand->Id(), aCommand->Name(), _L("Database opened, size: %d bytes.\r\n"), size);

	iDatabaseOpened = ETrue;

	//more detailed size information seems only available from Symbian OS 9.5 or later.

/*	

	RSqlDatabase::TSize size;

	iSqlDb.Size(size);

	ReportResult(aCommand->Id(), aCommand->Name(), _L("Database opened, size: %Ld bytes. free:%Ld bytes\r\n"), 

			size.iSize, size.iFree);

*/			

	}

//execute a SQL statement without parameter/response

void CCmdSqlSrv::SqlExecL(TDesC& aStatement, CServerCommandBase* aCommand)

	{

	TInt err;

	if (!iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("No database opened"));

	err = iSqlDb.Exec(aStatement);

	if (err < 0)

		{

		TPtrC errMsg = iSqlDb.LastErrorMessage();

		ReportResult(aCommand->Id(), aCommand->Name(), _L("Exec return code:%d message:%S\r\n"),

				err, &errMsg);				

		}

	User::LeaveIfError(err);

	//print the return code if it's not error

	ReportResult(aCommand->Id(), aCommand->Name(), _L("Exec return code:%d\r\n"),err);		

	}

//execute a SQL statement with parameter/response

//aStatement: SQL statement

//aOptParamFile: contains multiple string (HBufC*):should be parameters for that statement

//aTempFileTemplate: template to generate temp file, used to dump binary contents

void CCmdSqlSrv::SqlStateL(TDesC& aStatement, RPointerArray<HBufC> &aOptParamFile, CServerCommandBase* aCommand, TDesC* aTempFileTemplate)

	{

	TInt err;

	if (!iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("No database opened"));

	RSqlStatement sqlState;

	CleanupClosePushL(sqlState);

	err = sqlState.Prepare(iSqlDb, aStatement);

	if (err < 0)

		{

		TPtrC errMsg = iSqlDb.LastErrorMessage();

		ReportResult(aCommand->Id(), aCommand->Name(), _L("RSqlStatement::Prepare() return code:%d message:%S\r\n"),

				err, &errMsg);				

		}

	User::LeaveIfError(err);

	//depend on the statement type, for something like SELECT, which is expected to have returned results,

	//we should call Next(),

	//otherwise we should call Exec();

	//Panic will occur if calling wrong function

	//so check if this statement is SELECT

	TBool bIsSelect;

	TBuf<64> strBegin;

	strBegin.Copy(aStatement.Left(64));

	strBegin.TrimLeft();

	strBegin.UpperCase();

	if (strBegin.Left(6)==_L("SELECT"))

		bIsSelect = ETrue;

	else

		bIsSelect = EFalse;

	if (bIsSelect) 

		{

		//once successfully execute the statement, check if there is any response.

		//only quit the loop when there is no more results found

		TInt totalRecordsReturned = 0;	

		while(ETrue)

			{	

			err =  sqlState.Next();

			if (err==KSqlAtRow)	//ready to retrieve a result (usual response for SELECT command)

				{

				totalRecordsReturned++;

				ReportResult(aCommand->Id(), aCommand->Name(), _L("Record #%d =========================\r\n"), totalRecordsReturned);

				ParseResultL(sqlState, aCommand, aTempFileTemplate);

				}

			else if (err==KSqlAtEnd) //no records found

				{

				ReportResult(aCommand->Id(), aCommand->Name(), _L("===================================\r\n"));

				ReportResult(aCommand->Id(), aCommand->Name(), _L("%d record(s) returned\r\n"), totalRecordsReturned);

				break;

				}

			else 

				{

				User::LeaveIfError(err);			

				}

			}

		}

	else

		{		

		RSqlParamWriteStream strm;	//only will have effect when parameters are used

	    CleanupClosePushL(strm);

		//check if there is any parameters that need to be bound with the statement

	    TInt paramCnt = aOptParamFile.Count();

		if (paramCnt)

			{

			for (TInt paramId = 0; paramId<paramCnt; paramId++)

				{

				HBufC* paramFile = aOptParamFile[paramId];

				RFile file;

				CleanupClosePushL(file);

				err = file.Open(FsL(), *paramFile, EFileRead);

				LeaveIfErr(err, _L("Error openning file %S"), paramFile);

				err = strm.BindBinary(sqlState, paramId);

				User::LeaveIfError(err);

				//dump the file content the the parameter

				//file could be very large, so it is wise to separate it into several times. each time 32K or so.

				{

				TInt fileSize; 

				file.Size(fileSize);

				RBuf8 buf;

				buf.CreateL(KFileBufferSize);	//each time 32KB

				for (TInt curPos = 0; curPos<fileSize ;)

					{

					TInt remaingBytes = (fileSize-curPos);

					TInt bytesToProc = (remaingBytes>KFileBufferSize)? KFileBufferSize : remaingBytes;

					err = file.Read(curPos, buf, bytesToProc);

					User::LeaveIfError(err);

					strm.WriteL(buf);

					curPos += bytesToProc;

					}

				buf.Close();

				strm.CommitL();

				strm.Close();

				}

				CleanupStack::PopAndDestroy();	//file

				}

			}				

		err = sqlState.Exec();

		ReportResult(aCommand->Id(), aCommand->Name(), _L("Exec return code:%d\r\n"),err);

		CleanupStack::PopAndDestroy();	//strm

		}

	CleanupStack::PopAndDestroy();

	}

//close SQL database file

void CCmdSqlSrv::SqlClose(CServerCommandBase* aCommand)

	{

	if (!iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("No database opened"));

	iSqlDb.Close();

	//print the return code if it's not error

	ReportResult(aCommand->Id(), aCommand->Name(), _L("SQL database file closed\r\n"));

	iDatabaseOpened = EFalse;

	}

//attach additional database onto main database

void CCmdSqlSrv::SqlAttachL(TDesC& aFilename, TDesC& aDateBaseName, CServerCommandBase* aCommand)

	{

	TInt err;

	if (!iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("No database opened"));

	err = iSqlDb.Attach(aFilename, aDateBaseName);

	ReportResult(aCommand->Id(), aCommand->Name(), _L("RSqlDatabase::Attach return code:%d\r\n"), err);		

	User::LeaveIfError(err);	

	}

//

void CCmdSqlSrv::SqlDetachL(TDesC& aDateBaseName, CServerCommandBase* aCommand)

	{

	TInt err;

	if (!iDatabaseOpened)

		LeaveIfErr(KErrGeneral, _L("No database opened"));

	err = iSqlDb.Detach(aDateBaseName);

	ReportResult(aCommand->Id(), aCommand->Name(), _L("RSqlDatabase::Detach return code:%d\r\n"), err);		

	User::LeaveIfError(err);	

	}

#ifdef SQL_COMPACT

//RSqlDatabase::Compact() is documented but not implemented, so Do not use for now

//aDateBaseName: can be NULL (compacting main database)

void CCmdSqlSrv::SqlCompactL(TDesC* aDateBaseName, CServerCommandBase* aCommand)

	{

	TInt err;

	err = iSqlDb.Compact(0, aDateBaseName);

	ReportResult(aCommand->Id(), aCommand->Name(), _L("RSqlDatabase::Compact return code:%d\r\n"), err);		

	User::LeaveIfError(err);	

	}

#endif

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

//once we got a result from a SELECT command, then we go through the returned result

//only call this function after calling Next().

//we only analyse ONE returned record in this function

void CCmdSqlSrv::ParseResultL(RSqlStatement& aState, CServerCommandBase* aCommand, TDesC* aTempFileTemplate)

	{

	TInt err;

	TInt colCnt = aState.ColumnCount();

	TBuf<64> colHdr;	//column information fixed header

	for (TInt i=0; i<colCnt; i++)

		{

		TPtrC columnName;

		err = aState.ColumnName(i, columnName);

		if (err != KErrNone)

			{

			_LIT(KNotAvailable, "N/A");

			columnName.Set(KNotAvailable);

			}

		TSqlColumnType colType = aState.ColumnType(i);

		const TDesC* pTypeStr = ColumnTypeToString(colType);

		TInt colSize = aState.ColumnSize(i);

		colHdr.Format(_L("Column %d \"%S\"(%S)"), i, &columnName, pTypeStr);

		switch(colType)

			{

			case ESqlNull:

				{

				ReportResult(aCommand->Id(), aCommand->Name(), _L("%S \r\n"), &colHdr);	

				break;

				}

			case ESqlInt:

				{

				TInt colValue = aState.ColumnInt(i);

				ReportResult(aCommand->Id(), aCommand->Name(), _L("%S %d\r\n"), &colHdr, colValue);	

				break;

				}

			case ESqlInt64:

				{

				TInt64 colValue = aState.ColumnInt64(i);

				ReportResult(aCommand->Id(), aCommand->Name(),_L("%S %Ld\r\n"), &colHdr , colValue);

				break;

				}			

			case ESqlReal:

				{

				TInt64 colValue = aState.ColumnReal(i);

				ReportResult(aCommand->Id(), aCommand->Name(),_L("%S %f\r\n"), &colHdr, colValue);				

				break;

				}						

			case ESqlText:

				{

				TPtrC  colValue = aState.ColumnTextL(i);

				ReportResult(aCommand->Id(), aCommand->Name(),_L("%S %S\r\n"), &colHdr, &colValue);				

				break;

				}						

			case ESqlBinary:

				{

				TBool dumpToFile = EFalse;

				TBuf<128> printableHex;

				TFileName tmpFileName;

				RSqlColumnReadStream strm;

				CleanupClosePushL(strm);

				err = strm.ColumnBinary(aState, i);

				//generate a temp file name for dumping binary content

				RFile tmpFile;

				CleanupClosePushL(tmpFile);