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

     2 // All rights reserved.

     3 // This component and the accompanying materials are made available

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

     5 // which accompanies this distribution, and is available

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

     7 //

     8 // Initial Contributors:

     9 // Nokia Corporation - initial contribution.

    10 //

    11 // Contributors:

    12 //

    13 // Description:

    14 //

    15 

    16 /**

    17  @file

    18  @internalComponent

    19  @released

    20 */

    21 

    22 #include <e32base.h>

    23 #include <e32cons.h>

    24 #include "cntsqlprovider.h"

    25 

    26 /**

    27 Constant descriptor representing 'update' sql key word. Used to build sql statements.

    28 

    29 @internalComponent

    30 @released

    31 */	

    32 _LIT(KSqlUpdate, "UPDATE");

    33 

    34 

    35 /**

    36 Constant descriptor representing 'delete' sql key word. Used to build sql statements.

    37 

    38 @internalComponent

    39 @released

    40 */	

    41 _LIT(KSqlDelete, "DELETE");

    42 

    43 

    44 /**

    45 Constant descriptor representing 'insert' sql key word. Used to build sql statements.

    46 

    47 @internalComponent

    48 @released

    49 */	

    50 _LIT(KSqlInsert, "INSERT");

    51 

    52 

    53 /**

    54 Constant descriptor representing 'select' sql key word. Used to build sql statements.

    55 

    56 @internalComponent

    57 @released

    58 */	

    59 _LIT(KSqlSelect, "SELECT");

    60 

    61 

    62 /**

    63 Constant descriptor used to build sql statements.

    64 

    65 @internalComponent

    66 @released

    67 */	

    68 _LIT(KSqlWhere, "WHERE");

    69 

    70 

    71 /**

    72 Constant descriptor used to build sql statements.

    73 

    74 @internalComponent

    75 @released

    76 */	

    77 _LIT(KSqlSet, "SET");

    78 

    79 

    80 /**

    81 Constant descriptor used to build sql statements.

    82 

    83 @internalComponent

    84 @released

    85 */	

    86 _LIT(KSqlInto, "INTO");

    87 

    88 

    89 /**

    90 Constant descriptor used to build sql statements.

    91 

    92 @internalComponent

    93 @released

    94 */	

    95 _LIT(KSqlValues, "VALUES");

    96 

    97 

    98 /**

    99 Constant descriptor used to build sql statements.

   100 

   101 @internalComponent

   102 @released

   103 */	

   104 _LIT(KSqlFrom, "FROM");

   105 

   106 

   107 /**

   108 Constant descriptor used to build sql statements.

   109 

   110 @internalComponent

   111 @released

   112 */	

   113 _LIT(KSpace, " ");

   114 

   115 

   116 /**

   117 Constant descriptor used to build sql statements.

   118 

   119 @internalComponent

   120 @released

   121 */	

   122 _LIT(KEqual, " = ");

   123 

   124 

   125 /**

   126 Constant descriptor used to build sql statements.

   127 

   128 @internalComponent

   129 @released

   130 */	

   131 _LIT(KSemicolon, ";");

   132 

   133 

   134 /**

   135 Constant descriptor used to build sql statements.

   136 

   137 @internalComponent

   138 @released

   139 */	

   140 _LIT(KComma, ",");

   141 

   142 

   143 /**

   144 Constant descriptor used to build sql statements.

   145 

   146 @internalComponent

   147 @released

   148 */	

   149 _LIT(KOpenBracket, "(");

   150 

   151 

   152 /**

   153 Constant descriptor used to build sql statements.

   154 

   155 @internalComponent

   156 @released

   157 */	

   158 _LIT(KCloseBracket, ")");

   159 

   160 /**

   161 TStatement type constructor,

   162 

   163 @param aSqlStatement Specifies the sql statement type: select, insert, delete, update.

   164 @param aTableName 	 Table against which sql statement will be built

   165 

   166 */

   167 TCntSqlStatementType::TCntSqlStatementType(TCntSqlStatement aSqlStatement, const TDesC& aTableName)

   168 	:

   169 	iSqlStatement(aSqlStatement), 

   170 	iTableName(aTableName)

   171 	{

   172 	}

   173 

   174 /**

   175 TSqlStatement setter. Set the TSqlStatement held by this class,

   176 

   177 @param aSqlStatement Specifies the sql statement type: select, insert, delete, update.

   178 

   179 */	

   180 void TCntSqlStatementType::SetSqlStatement(TCntSqlStatement aSqlStatement)

   181 	{

   182 	iSqlStatement = aSqlStatement;	

   183 	}

   184 	

   185 /**

   186 TSqlStatement getter. Returns the sql statement type held by this class,

   187 

   188 @return 	TSqlStatement type held by this class

   189 

   190 */

   191 TCntSqlStatement TCntSqlStatementType::SqlStatement() const

   192 	{

   193 	return iSqlStatement;	

   194 	}

   195 	

   196 /**

   197 Table name getter. Returns the table's name held by this class,

   198 

   199 @return 	TDesC containing the table's name held by this class

   200 

   201 */

   202 const TDesC& TCntSqlStatementType::TableName() const

   203 	{

   204 	return iTableName;	

   205 	}

   206 	

   207 /**

   208 Factory method used to construct and retrieve CCntSqlStatement based objects. 

   209 CCntSqlStatement objects are used to build and provide sql strings (sql statements) to be 

   210 used in RSqlStatement.

   211 

   212 @param aStatementType TStatementType containing thwe sql statement type requested and the

   213 					  table name against which sql query will be run.

   214 

   215 @return Pointer  to concrete CCntSqlStatement implementation used to retrieve sql strings.

   216 

   217 */		

   218 CCntSqlStatement* TSqlProvider::GetSqlStatementL(TCntSqlStatementType aStatementType)

   219 	{

   220 	CCntSqlStatement* cntSqlStatement = NULL;

   221 	

   222 	switch(aStatementType.SqlStatement())

   223 		{

   224 		case EInsert:

   225 			cntSqlStatement = CCntSqlInsert::NewL( aStatementType.TableName() );

   226 			break;

   227 		case EUpdate:

   228 			cntSqlStatement = CCntSqlUpdate::NewL( aStatementType.TableName() );

   229 			break;		

   230 		case EDelete:

   231 			cntSqlStatement = CCntSqlDelete::NewL( aStatementType.TableName() );

   232 			break;

   233 		case ESelect:

   234 			cntSqlStatement = CCntSqlSelect::NewL( aStatementType.TableName() );

   235 		}

   236 	

   237 	return(cntSqlStatement);

   238 	}

   239 

   240 /**

   241 SQL parameter setter. Set value for a specified sql parameter,

   242 

   243 @param aParam SQL parameter name

   244 @param aValue SQL parameter value. 

   245 

   246 @leave KErrNoMemory, an out of memory occured

   247 

   248 @panic USER 0 panic; in debug mode if number of specified parameters is not equal with 

   249 		number of parameter values

   250 		

   251 */	

   252 void CCntSqlStatement::SetParamL(const TDesC& aParam, const TDesC& aValue)

   253 	{

   254 	ASSERT( iParams->Count() == iValues->Count() ); // in debug mode check 

   255 													// if number of provided params is equal 

   256 													// with number of provided values

   257 

   258 	ASSERT( iProcessed == EFalse ); // in debug mode check if the statement wasn't processed yet

   259 	

   260 	iParams->AppendL(aParam);

   261 	iValues->AppendL(aValue);

   262 	}

   263 	

   264 /**

   265 Utility method used to retrieve the position of a given parameter.

   266 The array of parameters is searched sequentially because we have to provide

   267 the index of the parameter as it was inserted (so binary search is not usefull here)

   268 Limit the usage of this method since it can be time consuming.

   269 

   270 @param  aParam parameter name for which index has to be retrieved

   271 @return index of the parameter if this is found in the parameter array (eg. previous set)

   272 		or KErrNotFound if the parameter was not found

   273 */

   274 TInt CCntSqlStatement::ParameterIndex(const TDesC& aParam) const

   275 	{

   276 	ASSERT( iParams->Count() == iValues->Count() ); // in debug mode check 

   277 													// if number of provided params is equal 

   278 													// with number of provided values

   279 	for(TInt i = 0; i < iParams->Count(); ++i)

   280 		{

   281 		if(aParam.Compare((*iParams)[i]) == 0)

   282 			{

   283 			return i;				

   284 			}

   285 		}

   286 		

   287 	return KErrNotFound;

   288 	}

   289 

   290 /**		

   291 SQL condition setter, Set condition for a sql statement (to be used in where clause)

   292 

   293 @param aCondition condition value.

   294 

   295 @leave KErrNoMemory, an out of memory occured

   296 

   297 */	

   298 void CCntSqlStatement::SetConditionL(const TDesC& aCondition)

   299 	{

   300 	ClearCondition();

   301 		

   302 	iCondition = aCondition.AllocL();	

   303 	iProcessed = EFalse;

   304 	delete iSqlString;

   305 	iSqlString = NULL;

   306 	}

   307 

   308 /**		

   309 Clearing SQL condition from a sql statement 

   310 */	

   311 void CCntSqlStatement::ClearCondition()

   312 	{

   313 	if(iCondition)

   314 		{

   315 		delete iCondition;	

   316 		iCondition = NULL;

   317     	iProcessed = EFalse;

   318     	delete iSqlString;

   319     	iSqlString = NULL;

   320 		} 

   321 	}

   322 

   323 	

   324 /**

   325 Remove all sql parameters prviously set and their values. Also reset previously set 

   326 sql condition.

   327 

   328 */	

   329 void CCntSqlStatement::Reset()

   330 	{

   331 	if(iCondition)

   332 		{

   333 		delete iCondition;	

   334 		iCondition = NULL;

   335 		}

   336 		

   337 	delete iSqlString;

   338 	iSqlString = NULL;

   339 		

   340 	iParams->Reset();

   341 	iValues->Reset();	

   342 	iProcessed = EFalse;

   343 	}

   344 

   345 /**

   346 Second phase constructor for CCntSqlStatement based classes

   347 

   348 @param aTableName table name against which sql statement will be run

   349 

   350 @leave KErrNoMemory, an out of memory occured

   351 

   352 */		

   353 void CCntSqlStatement::ConstructL(const TDesC& aTableName)

   354 	{

   355 	const TInt KGranularity = 5;

   356 	

   357 	iTableName = aTableName.AllocL();	

   358 	iParams = new (ELeave) CDesCArrayFlat(KGranularity);

   359 	iValues = new (ELeave) CDesCArrayFlat(KGranularity);	

   360 	

   361 	// Lazy initialisation for iSqlString; iSqlString is initialised in SqlStringL() method.

   362 	// This is done because in SqlStringL() method we know what size buffer must have

   363 	

   364 	iProcessed = EFalse;

   365 	}

   366 			

   367 /**

   368 CCntSqlStatement destructor.

   369 

   370 */	

   371 CCntSqlStatement::~CCntSqlStatement()

   372 	{

   373 	delete iTableName;	

   374 	delete iCondition;	

   375 	delete iSqlString;	

   376 	

   377 	if (iParams)

   378 		{

   379 		iParams->Reset();	

   380 		delete iParams;	

   381 		}

   382 	

   383 	if (iValues)

   384 		{

   385 		iValues->Reset();

   386 		delete iValues;		

   387 		}

   388 	}

   389 	

   390 /**

   391 Creates a concrete CCntSqlUpdate object (used to retrieve sql update statements)

   392 

   393 @param aTableName table name against which update statement will be run

   394 

   395 @return concrete CCntSqlUpdate object

   396 

   397 @leave KErrNoMemory, an out of memory occured

   398 

   399 */		

   400 CCntSqlUpdate* CCntSqlUpdate::NewL(const TDesC& aTableName)

   401 	{

   402 	CCntSqlUpdate* self = new (ELeave) CCntSqlUpdate();

   403 	CleanupStack::PushL(self );

   404 	self->ConstructL(aTableName);

   405 	CleanupStack::Pop(self);

   406 	return self;

   407 	}

   408 	

   409 /**

   410 Return a string representing an update sql statement

   411 

   412 @leave KErrArgument if the no parameter was provided

   413 */		

   414 TDesC& CCntSqlUpdate::SqlStringL()

   415 	{

   416 	if(iProcessed)

   417 		{

   418 		return *iSqlString;	

   419 		}

   420 		

   421 	ASSERT( iParams->Count() == iValues->Count() ); // in debug mode check 

   422 													// if number of provided params is equal 

   423 													// with number of provided values

   424 	

   425 	if(iParams->Count() == 0)

   426 		{

   427 		User::Leave( KErrArgument );	

   428 		}

   429 	

   430 	// First calculate the buffer size

   431 	TInt bufferSize = 0;

   432 	bufferSize = KSqlUpdate().Size() + 

   433 				iTableName->Size() + 

   434 				KSpace().Size() + 

   435 				KSqlSet().Size() + 

   436 				KSpace().Size();

   437 	

   438 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   439 		{

   440 		bufferSize += (*iParams)[loop].Size() + 

   441 					KEqual().Size() + 

   442 					(*iValues)[loop].Size();

   443 		if(loop < iParams->Count() -1)

   444 			{

   445 			bufferSize += KComma().Size();		

   446 			}

   447 		}

   448 	if(iCondition)

   449 		{

   450 		bufferSize += KSpace().Size() + 

   451 					KSqlWhere().Size() + 

   452 					KSpace().Size() + iCondition->Size();

   453 		}

   454 	bufferSize += KSemicolon().Size();

   455 	

   456 	// Allocate buffer

   457 	iSqlString = HBufC::NewL( bufferSize );		

   458 	

   459 	TPtr dataPtr = iSqlString->Des();

   460 	

   461 	dataPtr.Append(KSqlUpdate);

   462 	dataPtr.Append(KSpace);

   463 	dataPtr.Append(*iTableName);

   464 	dataPtr.Append(KSpace);

   465 	dataPtr.Append(KSqlSet);

   466 	dataPtr.Append(KSpace);

   467 	

   468 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   469 		{

   470 		dataPtr.Append((*iParams)[loop]);

   471 		dataPtr.Append(KEqual);

   472 		dataPtr.Append((*iValues)[loop]);

   473 		if(loop < iParams->Count() -1)

   474 			{

   475 			dataPtr.Append(KComma);		

   476 			}

   477 		}

   478 	

   479 	if(iCondition)

   480 		{

   481 		dataPtr.Append(KSpace);

   482 		dataPtr.Append(KSqlWhere);

   483 		dataPtr.Append(KSpace);	

   484 		dataPtr.Append(*iCondition);

   485 		}

   486 	dataPtr.Append(KSemicolon);	

   487 

   488 	iProcessed = ETrue;

   489 	return *iSqlString;	

   490 	}	

   491 

   492 /**

   493 Creates a concrete CCntSqlDelete object (used to retrieve sql delete statements)

   494 

   495 @param aTableName table name against which delete statement will be run

   496 

   497 @return concrete CCntSqlDelete object

   498 

   499 @leave KErrNoMemory, an out of memory occured

   500 

   501 */		

   502 CCntSqlDelete* CCntSqlDelete::NewL(const TDesC& aTableName)

   503 	{

   504 	CCntSqlDelete* self = new (ELeave) CCntSqlDelete();

   505 	CleanupStack::PushL(self);

   506 	self->ConstructL(aTableName);

   507 	CleanupStack::Pop(self);

   508 	return self;	

   509 	}

   510 	

   511 /**

   512 Return a string representing a delete sql statement

   513 

   514 @leave	KErrArgument if condition is not set

   515 */			

   516 TDesC& CCntSqlDelete::SqlStringL()

   517 	{

   518 	if(iProcessed) 

   519 		{

   520 		return *iSqlString;	

   521 		}

   522 

   523 	if(!iCondition)

   524 		{

   525 		User::Leave(KErrArgument);		

   526 		}	

   527 	

   528 	// First calculate the buffer size

   529 	TInt bufferSize = 0;

   530 	bufferSize = KSqlDelete().Size() + 

   531 				KSpace().Size() + 

   532 				KSqlFrom().Size() +

   533 				KSpace().Size() +

   534 				iTableName->Size() +

   535 				KSpace().Size() + 

   536 				KSqlWhere().Size() +

   537 				KSpace().Size() + 

   538 				iCondition->Size() + 

   539 				KSemicolon().Size();

   540 	

   541 	// Allocate buffer

   542 	iSqlString = HBufC::NewL( bufferSize );

   543 	

   544 	TPtr dataPtr = iSqlString->Des();

   545 	

   546 	dataPtr.Append(KSqlDelete);

   547 	dataPtr.Append(KSpace);

   548 	dataPtr.Append(KSqlFrom);

   549 	dataPtr.Append(KSpace);

   550 	dataPtr.Append(*iTableName);

   551 	

   552 	dataPtr.Append(KSpace);

   553 	dataPtr.Append(KSqlWhere);

   554 	dataPtr.Append(KSpace);	

   555 	dataPtr.Append(*iCondition);

   556 	dataPtr.Append(KSemicolon);

   557 	

   558 	iProcessed = ETrue;

   559 	return *iSqlString;	

   560 	}

   561 

   562 /**

   563 Creates a concrete CCntSqlInsert object (used to retrieve sql insert statements)

   564 

   565 @param aTableName table name against which insert statement will be run

   566 

   567 @return concrete CCntSqlInsert object

   568 

   569 @leave KErrNoMemory, an out of memory occured

   570 

   571 */			

   572 CCntSqlInsert* CCntSqlInsert::NewL(const TDesC& aTableName)

   573 	{

   574 	CCntSqlInsert* self = new (ELeave) CCntSqlInsert();

   575 	CleanupStack::PushL(self);

   576 	self->ConstructL(aTableName);

   577 	CleanupStack::Pop(self);

   578 	return self;	

   579 	}

   580 	

   581 /**

   582 Return a string representing an insert sql statement

   583 

   584 @leave 	KErrArgument if the no parameter was provided

   585 */			

   586 TDesC& CCntSqlInsert::SqlStringL()

   587 	{

   588 	if(iProcessed)

   589 		{

   590 		return *iSqlString;	

   591 		}

   592 

   593 	ASSERT( iParams->Count() == iValues->Count() ); // in debug mode check 

   594 													// if number of provided params is equal 

   595 													// with number of provided values

   596 	

   597 	if(iParams->Count() == 0)

   598 		{

   599 		User::Leave( KErrArgument );	

   600 		}		

   601 	

   602 	// First calculate the buffer size

   603 	TInt bufferSize = 0;

   604 	bufferSize = KSqlInsert().Size() + 

   605 				KSpace().Size() +

   606 				KSqlInto().Size() +

   607 				KSpace().Size() + 

   608 				iTableName->Size() + 

   609 				KSpace().Size() +		

   610 				KOpenBracket().Size();

   611 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   612 		{

   613 		bufferSize += (*iParams)[loop].Size() + (*iValues)[loop].Size();

   614 		if(loop < iParams->Count() - 1) 

   615 			{

   616 			bufferSize += KComma().Size() + KComma().Size();		

   617 			}

   618 		}

   619 	bufferSize += KCloseBracket().Size() + 

   620 				KSpace().Size() + 

   621 				KSqlValues().Size() +

   622 				KSpace().Size() + 	

   623 				KCloseBracket().Size() +

   624 				KSemicolon().Size();

   625 	

   626 	// Allocate buffer

   627 	iSqlString = HBufC::NewL( bufferSize );

   628 	

   629 	TPtr dataPtr = iSqlString->Des();

   630 	

   631 	dataPtr.Append(KSqlInsert);

   632 	dataPtr.Append(KSpace);

   633 	dataPtr.Append(KSqlInto);

   634 	dataPtr.Append(KSpace);

   635 	dataPtr.Append(*iTableName);

   636 	dataPtr.Append(KSpace);

   637 			

   638 	dataPtr.Append(KOpenBracket);

   639 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   640 		{

   641 		dataPtr.Append((*iParams)[loop]);

   642 		if(loop < iParams->Count() - 1) 

   643 			{

   644 			dataPtr.Append(KComma);		

   645 			}

   646 		}

   647 		

   648 	dataPtr.Append(KCloseBracket);

   649 	

   650 	dataPtr.Append(KSpace);

   651 	dataPtr.Append(KSqlValues);

   652 	dataPtr.Append(KSpace);

   653 	

   654 	dataPtr.Append(KOpenBracket);

   655 	for(TInt loop = 0; loop < iValues->Count(); ++loop) 

   656 		{

   657 		dataPtr.Append((*iValues)[loop]);

   658 		if(loop < iValues->Count() - 1)

   659 			{

   660 			dataPtr.Append(KComma);		

   661 			}

   662 		}

   663 	

   664 	dataPtr.Append(KCloseBracket);

   665 	dataPtr.Append(KSemicolon);

   666 	

   667 	iProcessed = ETrue;

   668 	

   669 	return *iSqlString;	

   670 	}

   671 	

   672 /**

   673 Creates a concrete CCntSqlSelect object (used to retrieve sql select statements)

   674 

   675 @param aTableName table name against which select statement will be run

   676 

   677 @return concrete CCntSqlSelect object

   678 

   679 @leave KErrNoMemory, an out of memory occured

   680 

   681 */			

   682 CCntSqlSelect* CCntSqlSelect::NewL(const TDesC& aTableName)

   683 	{

   684 	CCntSqlSelect* self = new (ELeave) CCntSqlSelect();

   685 	CleanupStack::PushL(self);

   686 	self->ConstructL(aTableName);

   687 	CleanupStack::Pop(self);

   688 	return self;			

   689 	}

   690 	

   691 /**

   692 Return a string representing a select sql statement

   693 When constructing simple select statement (against one single table) table name 

   694 has to be provided.

   695 For making joint selects a list of tables will be provided: table1, table2 as

   696 table name.

   697 

   698 @leave	KErrArgument if there is no argument provided

   699 */			

   700 TDesC& CCntSqlSelect::SqlStringL()

   701 	{

   702 	if(iProcessed) 

   703 		{

   704 		return *iSqlString;	

   705 		}

   706 	

   707 	if(iParams->Count() == 0)

   708 		{

   709 		User::Leave(KErrArgument);	

   710 		}

   711 	

   712 	// First calculate the buffer size

   713 	TInt bufferSize = 0;

   714 	bufferSize = KSqlSelect().Size() +

   715 				KSpace().Size();

   716 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   717 		{

   718 		bufferSize += (*iParams)[loop].Size();

   719 		if(loop < iParams->Count() - 1)

   720 			{

   721 			bufferSize += KComma().Size();		

   722 			}

   723 		}	

   724 	bufferSize += KSpace().Size() + 

   725 				KSqlFrom().Size() +

   726 				KSpace().Size() +

   727 				iTableName->Size();

   728 	if(iCondition)

   729 		{

   730 		bufferSize += KSpace().Size() + 

   731 					KSqlWhere().Size() +

   732 					KSpace().Size() + 

   733 					iCondition->Size();

   734 		}

   735 	bufferSize += KSemicolon().Size();

   736 	

   737 	// Allocate buffer

   738 	iSqlString = HBufC::NewL( bufferSize );

   739 	TPtr dataPtr = iSqlString->Des();

   740 	

   741 	dataPtr.Append(KSqlSelect);

   742 	dataPtr.Append(KSpace);

   743 

   744 	for(TInt loop = 0; loop < iParams->Count(); ++loop) 

   745 		{

   746 		dataPtr.Append((*iParams)[loop]);

   747 		if(loop < iParams->Count() - 1)

   748 			{

   749 			dataPtr.Append(KComma);		

   750 			}

   751 		}	

   752 	

   753 	dataPtr.Append(KSpace);

   754 	

   755 	dataPtr.Append(KSqlFrom);

   756 	dataPtr.Append(KSpace);

   757 	dataPtr.Append(*iTableName);

   758 

   759 	if(iCondition)

   760 		{

   761 		dataPtr.Append(KSpace);

   762 		dataPtr.Append(KSqlWhere);

   763 		dataPtr.Append(KSpace);	

   764 		dataPtr.Append(*iCondition);

   765 		}

   766 	dataPtr.Append(KSemicolon);	

   767 			

   768 	iProcessed = ETrue;

   769 	

   770 	return *iSqlString;	

   771 	}