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 "dbsqldumper.h"

    23 #include "dbhtmltags.h"

    24 #include "dbsqlconstants.h"

    25 #include <e32test.h>

    26 #include <s32mem.h>

    27 

    28 _LIT(KErrorMessage, "DBSqlDumper Failed");	

    29 const TInt KMaxPathLength = 100;

    30 const TInt KBufferLength = 80;

    31 

    32 

    33 /**

    34 Class constructor

    35 */

    36 CDbStructure::CDbStructure()

    37 	{

    38 	Init();	

    39 	}

    40 	

    41 /**

    42 Class destructor

    43 */

    44 CDbStructure::~CDbStructure()	

    45 	{

    46 	iColumnsIndex.Reset();

    47 	iNumColumns.Reset();	

    48 	}

    49 

    50 /**

    51 Initialize table structure (number of tables, number of column in each table)

    52 

    53 */

    54 void CDbStructure::Init()

    55 	{	

    56 	iTablesIndex = KInitialValue;

    57 	iNumTables = KNumOfTables;

    58 	iColumnsIndex.Reset();

    59 	iNumColumns.Reset();

    60 	//columns in pref table

    61 	iColumnsIndex.Append(KInitialValue);

    62 	iNumColumns.Append(KNumColInPrefTable);

    63 	//columns in contact table

    64 	iColumnsIndex.Append(KInitialValue);

    65 	iNumColumns.Append(KNumColInContactTable);

    66 	//columns in group table

    67 	iColumnsIndex.Append(KInitialValue);

    68 	iNumColumns.Append(KNumColInGroupTable);

    69 	//columns in comm-address table

    70 	iColumnsIndex.Append(KInitialValue);

    71 	iNumColumns.Append(KNumColInCommTable);

    72 	}

    73 		

    74 /**

    75 Moves the cursor forward to the next table

    76 

    77 @return ETrue if the cursor can be moved to the next index or EFalse if the cursor

    78 		is already at the end

    79 */		

    80 TBool CDbStructure::NextTable()

    81 	{

    82 	++iTablesIndex;

    83 	return iTablesIndex < iNumTables;

    84 	}

    85 	

    86 /**

    87 Return current table name in the cursor

    88 

    89 */	

    90 const TDesC& CDbStructure::TableName()

    91 	{

    92 	return TableName(iTablesIndex);

    93 	}

    94 	

    95 /**

    96 Returns name of the table located at specified position in the table array

    97 

    98 */

    99 const TDesC& CDbStructure::TableName(TInt aTableIndex)

   100 	{

   101 	switch(aTableIndex)

   102 		{

   103 		case EContactPrefTableName:

   104 			{

   105 			return KSqlContactPrefTableName;	

   106 			}

   107 		case EContactTableName:

   108 			{

   109 			return KSqlContactTableName;

   110 			}

   111 		case EContactGroupTableName:

   112 			{

   113 			return KSqlContactGroupTableName;	

   114 			}

   115 		case EContactCommAddressTableName:

   116 			{

   117 			return KSqlContactCommAddrTableName;		

   118 			}

   119 		default:

   120 			{

   121 			return KNullText;

   122 			} 

   123 		}

   124 	}

   125 	

   126 /**

   127 Moves the cursor forward to the next column in the specified table

   128 

   129 @return ETrue if the cursor can be moved to the next index or EFalse if the cursor

   130 		is already at the end

   131 */

   132 TBool CDbStructure::NextColumn(TInt aTableIndex)

   133 	{

   134 	++iColumnsIndex[aTableIndex];

   135 	return iColumnsIndex[aTableIndex] < iNumColumns[aTableIndex];

   136 	}

   137 

   138 /**

   139 Return current column name for the specified table

   140 

   141 @param	aTableIndex table index for which column name has to be returned

   142 */	

   143 const TDesC& CDbStructure::Column(TInt aTableIndex)

   144 	{

   145 	return Column(aTableIndex, iColumnsIndex[aTableIndex]);	

   146 	}

   147 	

   148 /**	

   149 Return name of the column located at specified pozition in the specified table

   150 

   151 */

   152 const TDesC& CDbStructure::Column(TInt aTableIndex, TInt aColumnIndex)

   153 	{

   154 	switch(aTableIndex)

   155 		{

   156 		case EContactPrefTableName: //columns in preference table

   157 			switch(aColumnIndex)

   158 				{

   159 				case 0:

   160 					{

   161 					return KPrefPrefId;	

   162 					}

   163 				case 1:

   164 					{

   165 					return KPrefSchemaVersion;	

   166 					}

   167 				case 2:

   168 					{

   169 					return KPrefDatabaseUid;	

   170 					}

   171 				case 3:

   172 					{

   173 					return KPrefPrefCreationDate;	

   174 					}

   175 				case 4:

   176 					{

   177 					return KPrefMachineId;	

   178 					}

   179 				case 5:

   180 					{

   181 					return KPrefPreferredOrder;	

   182 					}

   183 				case 6:

   184 					{

   185 					return KPrefferredTemplateId;

   186 					}

   187 				}

   188 			break;	

   189 		case EContactTableName: //columns in contact table

   190 			switch(aColumnIndex)

   191 				{

   192 				case 0:

   193 					{

   194 					return KContactId;

   195 					}

   196 				case 1:

   197 					{

   198 					return KContactTemplateId;

   199 					}

   200 				case 2:

   201 					{

   202 					return KContactTypeFlags;

   203 					}

   204 				case 3:

   205 					{

   206 					return KContactAccessCount;

   207 					}

   208 				case 4:

   209 					{

   210 					return KContactLastModified;

   211 					}

   212 				case 5:

   213 					{

   214 					return KContactGuidString;

   215 					}

   216 				case 6:

   217 					{

   218 					return KContactFirstName;

   219 					}

   220 				case 7:

   221 					{

   222 					return KContactLastName;

   223 					}

   224 				case 8:

   225 					{

   226 					return KContactCompanyName;

   227 					}

   228 				case 9:

   229 					{

   230 					return KContactFirstNamePrn;

   231 					}

   232 				case 10:

   233 					{

   234 					return KContactLastNamePrn;

   235 					}

   236 				case 11:

   237 					{

   238 					return KContactCompanyNamePrn;

   239 					}

   240 				case 12:

   241 					{

   242 					return KContactTextFieldHeader;

   243 					}

   244 				case 13:

   245 					{

   246 					return KContactBinaryFieldHeader;

   247 					}

   248 				case 14:

   249 					{

   250 					return KContactTextFields;

   251 					}

   252 				case 15:

   253 					{

   254 					return KContactBinaryFields;

   255 					}

   256 				}

   257 			break;	

   258 		case EContactGroupTableName: //columns in group table

   259 			switch(aColumnIndex)

   260 				{

   261 				case 0:

   262 					{

   263 					return KGroupContactGroupId;

   264 					}

   265 				case 1:

   266 					{

   267 					return KGroupContactGroupId;

   268 					}

   269 				case 2:

   270 					{

   271 					return KGroupContactGroupMemberId;

   272 					}

   273 				}

   274 			break;				

   275 		case EContactCommAddressTableName: //columns in comm-address table

   276 			switch(aColumnIndex)

   277 				{

   278 				case 0: 

   279 					{

   280 					return KCommAddrId;

   281 					}

   282 				case 1:

   283 					{

   284 					return KCommAddrType;

   285 					}

   286 				case 2:

   287 					{

   288 					return KCommAddrValue;

   289 					}

   290 				case 3:

   291 					{

   292 					return KCommAddrExtraValue;

   293 					}

   294 				case 4:

   295 					{

   296 					return KCommAddrContactId;

   297 					}

   298 				}

   299 			break;

   300 		}

   301 		return KNullText;

   302 	}

   303 	

   304 /**

   305 Check if current column is the last column

   306 

   307 */	

   308 TBool CDbStructure::IsLastColumn(TInt aTableIndex)

   309 	{

   310 	return iColumnsIndex[aTableIndex] == (iNumColumns[aTableIndex]-1);

   311 	}

   312 	

   313 /**

   314 Return number of columns specified table has

   315 

   316 */

   317 TInt CDbStructure::ColumnNo(TInt aTableIndex)

   318 	{

   319 	return 	iNumColumns[aTableIndex];

   320 	}

   321 

   322 /**

   323 Class destructor

   324 

   325 */	

   326 CDbSqlDumper::~CDbSqlDumper()

   327 	{

   328 	delete iBuffer;

   329 	iTableNames.Reset();

   330 	iFile.Close();

   331 	iDatabase.Close();

   332 	iFsSession.Close();

   333 	delete iDbStructure;

   334 	}

   335 

   336 /**

   337 Second phase constructor for CDbSqlDumper class

   338 

   339 @param  aDbName name of the database to be dumped.

   340 @param	aOutputDir directory where html file will be generated		

   341 */	

   342 void CDbSqlDumper::ConstructL(const TDesC& aDbName, const TDesC& aOutputDir)

   343 	{

   344 	// Output File Name	

   345 	_LIT(KOutputFile,"DbDump.html");

   346 	TBuf<KMaxPathLength> path(aOutputDir);

   347 	path.Append(KOutputFile);

   348 	

   349 	User::LeaveIfError(iFsSession.Connect() );

   350 	iDatabase.OpenL(aDbName);

   351 

   352 	User::LeaveIfError(iFile.Replace(iFsSession, path, EFileWrite) );

   353 

   354 	iBuffer = HBufC8::NewL(KBufferLength);

   355 	iDbStructure = new (ELeave) CDbStructure();

   356 	while(iDbStructure->NextTable())

   357 		{

   358 		iTableNames.AppendL(&(iDbStructure->TableName()));		

   359 		}	

   360 	}

   361 

   362 /**

   363 Return a concrete CDbSqlDumper object

   364 

   365 @param  aDbName name of the database to be dumped.

   366 @param	aOutputDir directory where html file will be generated		

   367 */	

   368 CDbSqlDumper* CDbSqlDumper::NewLC(const TDesC& aDbName, const TDesC& aOutputDir)

   369 	{

   370 	CDbSqlDumper* self = new (ELeave) CDbSqlDumper();

   371 	CleanupStack::PushL(self);

   372 	self->ConstructL(aDbName, aOutputDir);

   373 	return self;

   374 	}

   375 	

   376 /**

   377 Utility method used to build sql string. The result sql string will be

   378 used to select from specified table

   379 

   380 @param	aTableIndex	table index in the table array

   381 */	

   382 void CDbSqlDumper::PrepareSQLStatementL(TInt aTableIndex)

   383 	{

   384 	//calculate the space needed to build the saq string

   385 	iDbStructure->Init();

   386 	TInt size = KSQLSelect().Size();

   387 	while(iDbStructure->NextColumn(aTableIndex))

   388 		{

   389 		size += iDbStructure->Column(aTableIndex).Size() + 1;

   390 		}

   391 	size += KFrom().Size() + iDbStructure->TableName(aTableIndex).Size();	

   392 

   393 	HBufC* sqlString = HBufC::NewLC( size );

   394 	

   395 	TPtr buffer = sqlString->Des();

   396 	

   397 	iDbStructure->Init();

   398 	buffer.Append(KSQLSelect);

   399 	while(iDbStructure->NextColumn(aTableIndex))

   400 		{

   401 		buffer.Append(iDbStructure->Column(aTableIndex));

   402 		if(!iDbStructure->IsLastColumn(aTableIndex))	

   403 			{

   404 			buffer.Append(KComma);	

   405 			}

   406 		else	

   407 			{

   408 			buffer.Append(KSpace);	

   409 			}		

   410 		}

   411 	buffer.Append(KFrom);	

   412 	buffer.Append(iDbStructure->TableName(aTableIndex) );

   413 	User::LeaveIfError(iSqlStatement.Prepare(iDatabase, buffer));

   414 	

   415 	CleanupStack::PopAndDestroy(sqlString);

   416 	}

   417 

   418 /**

   419 Outputs one table values. Fetch all selected rows and output them in the

   420 html file

   421 

   422 @param	aTableIndex	table index in the table array

   423 @leave  if there is an error at sql level, it will be forwarded

   424 */	

   425 void CDbSqlDumper::OutputTableToFileL(TInt aTableIndex) 

   426 	{

   427 	TInt err;

   428 	iFile.Write(KTable);

   429 	OutputTableNameToFile(aTableIndex);

   430 	OutputTableColHeadingsToFile(aTableIndex);

   431 	PrepareSQLStatementL(aTableIndex);

   432 	

   433 	// output the table's rows

   434 	while ((err =iSqlStatement.Next()) == KSqlAtRow )

   435 		{

   436 		iFile.Write(KRow);

   437 		OutputTableColDataToFileL(aTableIndex);

   438 		iFile.Write(KRowEnd);	

   439 		}

   440 

   441 	// check if there were any error

   442 	if(err != KSqlAtEnd) 

   443 		{

   444 		iSqlStatement.Close();

   445 		User::Leave(err);	

   446 		}

   447 	

   448 	iSqlStatement.Close();

   449 	iFile.Write(KTableEnd);

   450 	}

   451 

   452 /**

   453 Outputs the table name in the html file

   454 

   455 @param	aTableIndex	table index in the table array

   456 

   457 */	

   458 void CDbSqlDumper::OutputTableNameToFile(TInt aTableIndex)

   459 	{

   460 	iFile.Write(KBold);	

   461 	iBuffer->Des().Copy( *(iTableNames[aTableIndex]) ); // write iTableNames[aTableIndex] to the file.

   462 	iFile.Write(*iBuffer);

   463 	iFile.Write(KBoldEnd);

   464 	}

   465 

   466 /**

   467 Outputs the header of the tables created in the html file. 

   468 Header will contain column names

   469 

   470 @param	aTableIndex	table index in the table array

   471 

   472 */

   473 void CDbSqlDumper::OutputTableColHeadingsToFile(TInt aTableIndex)

   474 	{

   475 	iFile.Write(KHdRow);

   476 

   477 	iDbStructure->Init();

   478 	while(iDbStructure->NextColumn(aTableIndex))

   479 		{

   480 		iFile.Write(KCell);

   481 		iBuffer->Des().Copy(iDbStructure->Column(aTableIndex));

   482 		iFile.Write(*iBuffer);

   483 		iFile.Write(KCellEnd);

   484 		}

   485 

   486     iFile.Write(KRowEnd);

   487 	}

   488 

   489 /**

   490 Outputs one row of a tables data.

   491 

   492 @param	aTableIndex	table index in the table array

   493 

   494 */

   495 void CDbSqlDumper::OutputTableColDataToFileL(TInt aTableIndex)

   496 	{

   497 	TInt counter = 0;

   498 	const TInt KNumberOfCols( iDbStructure->ColumnNo(aTableIndex) );  

   499 

   500     // cols are from 1, to maxCols. not from 0 to KNumberOfCols-1.

   501 	for (counter = 0; counter < KNumberOfCols; ++counter)   // for each column value in this row 

   502 		{

   503 		iFile.Write(KCell);

   504 

   505 		if (iSqlStatement.IsNull(counter) )

   506 			{

   507 			iBuffer->Des().Format(KNullCol); // write out 'NULL' to the file

   508 			}

   509 		else

   510 			{

   511 			switch ( iSqlStatement.ColumnType(counter) )

   512 				{

   513 				case ESqlInt:

   514 					{

   515 					TInt32 val = iSqlStatement.ColumnInt(counter); 

   516 					iBuffer->Des().Format(KInt32String, val);

   517 					}

   518 				break;

   519 				case ESqlInt64:

   520 					{

   521 					TInt64 val = iSqlStatement.ColumnInt64(counter);

   522 					iBuffer->Des().Format(KUInt64String, I64HIGH(val), I64LOW(val) );

   523 					}

   524 				break;

   525 				case ESqlReal:

   526 					{

   527 					TReal32 val = iSqlStatement.ColumnReal(counter);

   528 					iBuffer->Des().Format(KRealString, val);

   529 					}

   530 				break;

   531 				case ESqlText:

   532 					{

   533 					iBuffer->Des().Copy(iSqlStatement.ColumnTextL(counter));

   534 					}

   535 				break;

   536 				case ESqlBinary:

   537 					if ((iDbStructure->Column(aTableIndex, counter) == KContactTextFieldHeader) ||

   538 						(iDbStructure->Column(aTableIndex, counter) == KContactBinaryFieldHeader))

   539 						{

   540 						FieldHeaderColL(counter);

   541 						}

   542 					else 

   543 						{

   544 						LongBinaryL(counter);

   545 						}

   546 				break;

   547 				default:

   548 					iBuffer->Des().Format(KUnknownMessage);

   549 				break;

   550 				} // switch

   551 			} // if

   552 

   553 		iFile.Write(*iBuffer);

   554 		iFile.Write(KCellEnd);

   555 		

   556 		} // for

   557 	}

   558 

   559 /**

   560 Outputs a binary column/value representing header fields

   561 

   562 @param	aFieldIndex	column index in the curently executed sql statement

   563 

   564 */

   565 void CDbSqlDumper::FieldHeaderColL(TInt aFieldIndex)

   566 	{

   567 	TAutoClose<RSqlColumnReadStream> headerBlob;

   568 	TAutoClose<RStoreReadStream> stream;

   569 

   570 	headerBlob.iObj.ColumnBinary(iSqlStatement, aFieldIndex);

   571 	CEmbeddedStore* headerStore = CEmbeddedStore::FromLC(headerBlob.iObj);

   572 	stream.iObj.OpenLC(*headerStore, headerStore->Root());

   573 

   574 	DumpStreamL(stream.iObj);

   575 

   576 	CleanupStack::PopAndDestroy(&stream.iObj); //OpenLC

   577 	CleanupStack::PopAndDestroy(headerStore);

   578 	}

   579 

   580 /**

   581 Outputs a binary column/value representing binaryfields

   582 

   583 @param	aFieldIndex	column index in the curently executed sql statement

   584 

   585 */

   586 void CDbSqlDumper::LongBinaryL(TInt aFieldIndex)

   587 	{

   588 	TAutoClose<RSqlColumnReadStream> rdStream;

   589 	rdStream.iObj.ColumnBinary(iSqlStatement,aFieldIndex);

   590 	

   591 	DumpStreamL(rdStream.iObj);

   592 	}

   593 

   594 /**

   595 Utility method used to read from a read stream and write to file

   596 

   597 @param	stream RReadStream from which information will be read

   598 

   599 */

   600 void CDbSqlDumper::DumpStreamL(RReadStream& stream)

   601 	{

   602 	const TInt KStreamSize = stream.Source()->SizeL();

   603 

   604 	iFile.Write(KDumpFont);

   605 

   606 	_LIT8(KSizeMessage, "size is %d <BR>");

   607 	_LIT8(KEightSpaces, "        ");

   608 	_LIT8(KByteFormat, "%02x ");

   609 	_LIT8(KFormatString, "%S&nbsp;<BR>\r\n");

   610 	

   611 	iBuffer->Des().Format(KSizeMessage, KStreamSize);

   612 	iFile.Write(*iBuffer);

   613 

   614 	TBuf8<8> str(KEightSpaces); //Reserve 8 symbols

   615 

   616 	TInt counter(0);

   617 	while (counter < KStreamSize)

   618 		{

   619 		for(TInt i=0; i < 8; ++i)

   620 			{

   621 			if (counter >= KStreamSize)

   622 				{

   623 				break;	

   624 				}

   625 			//format the bynary value to a printable char	

   626 			TInt8 byte( stream.ReadInt8L() );

   627 			iBuffer->Des().Format(KByteFormat, byte);

   628 			iFile.Write(*iBuffer);

   629 			

   630 			str[i] = (byte>32 ? byte : '.');

   631 			++counter;

   632 			}

   633 		iBuffer->Des().Format(KFormatString, &str);

   634 		iFile.Write(*iBuffer);

   635 		}

   636 

   637 	iFile.Write(KDumpFontEnd);

   638 	}

   639 

   640 /**

   641 Output database content to a file

   642 

   643 */

   644 void CDbSqlDumper::OutputDBContentsL()    

   645 	{

   646 	// Output Title

   647 	_LIT8(KHdr,"<H2>Contacts Model DB Dump:</H2>");

   648 	iFile.Write(KHdr);

   649 		

   650 	TInt counter = 0;

   651 	const TInt KNumTables( iTableNames.Count() ); 

   652 	

   653 	// for each table - output all tables 

   654 	for (counter = 0; counter < KNumTables; ++counter)

   655 		{

   656 		OutputTableToFileL(counter);

   657 		}			

   658 	}

   659 

   660 void RunDumpL()

   661 	{

   662 // Location of Output Directory. (File will be called DbDump.html). Final "\" required on path.

   663 _LIT(KOutputDir, "?:\\");

   664 TBuf<sizeof(KOutputDir)> outputDir(KOutputDir);

   665 outputDir[0] = (TUint8) RFs::GetSystemDriveChar();

   666 

   667 // Location of Contacts DB

   668 _LIT(KDBName, "?:\\private\\10003A73\\SQLite__contacts.cdb");

   669 TBuf<sizeof(KDBName)> fileDBName(KDBName);

   670 fileDBName[0] = (TUint8) RFs::GetSystemDriveChar();

   671 

   672 	// dump sql database

   673 	CDbSqlDumper* sqlDumper = CDbSqlDumper::NewLC(fileDBName, outputDir);   

   674     sqlDumper->OutputDBContentsL();   

   675 	CleanupStack::PopAndDestroy(sqlDumper);   

   676 	}

   677 

   678 GLDEF_C TInt E32Main()

   679 	{

   680 	__UHEAP_MARK;

   681 	CActiveScheduler* scheduler = new CActiveScheduler;

   682 	if (scheduler)

   683 		{

   684 		CActiveScheduler::Install(scheduler);

   685 		CTrapCleanup* cleanup = CTrapCleanup::New();

   686 		if (cleanup)

   687 			{

   688 			TRAPD(err,RunDumpL() );	

   689 			__ASSERT_ALWAYS(err == KErrNone, User::Panic(KErrorMessage,err) ); 

   690 			delete cleanup;

   691 			}

   692 		delete scheduler;

   693 		}

   694 	__UHEAP_MARKEND;

   695 	return KErrNone;

   696     }

   697