dbmsengine.cpp

SymbianOS的DBMS实例 详细的代码揭示SymbianOS数据库管理系统的使用 很规范

/*
* ============================================================================
*  Name     : CDBMSengine from CDBMSengine.h
*  Part of  : DBMSexample
*  Created  : 02/28/2003 by Forum Nokia
*  Implementation notes:
*
*  Version  :
*  Copyright: Nokia Corporation, 2003
* ============================================================================
*/

#include "DBMSengine.h"
#include <eikenv.h>
#include <f32file.h>
#include <aknnotewrappers.h>
#include <DBMSexample.rsg>

_LIT( KSQLQuote1, "'" );
_LIT( KSQLQuote2, "'," );
_LIT( KSQLend, ")" );
_LIT( KComma, "," );

/*
-----------------------------------------------------------------------------

    CDBMSengine::CDBMSengine( )

	Description: Constructor
	Comments:

    Return values: N/A

-----------------------------------------------------------------------------
*/    
CDBMSengine::CDBMSengine( )
{
	_LIT( Kxxx, "xxx" );
	iName = Kxxx;
	iOldName = Kxxx;
	iSex = 0;
	iBirthday = _L("19650405");
	iPersonal_id = 10000;
	iIsDatabaseOpened = EFalse;
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::~CDBMSengine()

	Description: Destructor
	Comments:

    Return values: N/A

-----------------------------------------------------------------------------
*/    
CDBMSengine::~CDBMSengine()
{
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::ConstructL()

	Description: Second-phase constructor
	Comments:

    Return values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::ConstructL()
{

}

/*
-----------------------------------------------------------------------------

    CDBMSengine::OpenL()

	Open a database

    Returns values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::OpenL()
{
    
	User::LeaveIfError( iDbSession.Connect() );
    
	if ( KErrNone != iDatabase.Open(iDbSession, KDatabaseName) )
		{
		// If the file cannot be opened, mostly, the database file is not there
		CreateL();
		Close();
		User::LeaveIfError( iDbSession.Connect() );
		// Re-open the database
		User::LeaveIfError( iDatabase.Open(iDbSession, KDatabaseName) );
		}

	iIsDatabaseOpened = ETrue;
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::Close()

	Description: Close a database
	Comments:

    Returns values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::Close()
{
	iView.Close();

    iDatabase.Close();
    iDbSession.Close();

	iIsDatabaseOpened = EFalse;
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::CreateL()

	Create a database. If a database has existed, then replace it. If not,
	create a new database.

    Returns values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::CreateL()
{

	TBool existed = IsDatabaseCreatedL( );

	RFs fsSession;
    
    RDbNamedDatabase database;
	
    User::LeaveIfError(fsSession.Connect());
	CleanupClosePushL(fsSession);
          
    CleanupClosePushL(database);

	// Check whether the database has existed or not
	if ( existed )
		{
		// Prompt to the user whether a new database needs to be created.
		CAknQueryDialog* dlg = CAknQueryDialog::NewL(CAknQueryDialog::ENoTone);

		dlg->PrepareLC( R_CONFIRMATION_QUERY_CREATE );
		if ( dlg->RunLD() )
			{
			// If the answer is yes, create a brand-new database, and all the records
			// will be lost.
			User::LeaveIfError( database.Replace(fsSession, KDatabaseName ));
			}
		else
			{
			CleanupStack::PopAndDestroy(); // pop and close database
			CleanupStack::PopAndDestroy(); // pop and close dbSession    
			return;			
			}
		}
	else
		{
		User::LeaveIfError( database.Replace(fsSession, KDatabaseName ));
		}

	// The database should have been created now. Then gives an indication!
	TBuf< 256 > des;
	CEikonEnv::Static()->ReadResource( des, R_DATABASE_CREATED );
	CAknInformationNote* informationNote = new (ELeave) CAknInformationNote; 
	informationNote->ExecuteLD( des );

	// Create a table for the database
#ifdef USE_DBMS_API
    _LIT( KTable, "employees");
    CDbColSet* columns= CDbColSet::NewLC();
	
	// Add name column
    _LIT( KName,  "name");
	TDbCol name( KName, EDbColText,40 );
    name.iAttributes = TDbCol::ENotNull;
    columns->AddL( name );

	// Add sex column
	_LIT( KSex, "sex" );
	TDbCol sex( KSex, EDbColUint32 );
    sex.iAttributes = TDbCol::ENotNull;
    columns->AddL( sex );

	// Add birthday column
	_LIT( KBirthday, "birthday" );
	TDbCol birthday( KBirthday, EDbColText,40 );
    birthday.iAttributes = TDbCol::ENotNull;
    columns->AddL( birthday );

	// Add persona_id column
	_LIT( KPersonalId, "personal_id" );
	TDbCol personal_id( KPersonalId, EDbColUint32 );
    personal_id.iAttributes = TDbCol::ENotNull;
    columns->AddL( personal_id );

    User::LeaveIfError ( database.CreateTable ( KTable, *columns ) );
    CleanupStack::PopAndDestroy(); // columns
#else
    // Construct the SQL statement
    _LIT(KSQLCreateTable, "CREATE TABLE employees (name CHAR (40) NOT NULL, sex UNSIGNED INTEGER NOT NULL, birthday CHAR (40) NOT NULL, personal_id UNSIGNED INTEGER NOT NULL)");
    User::LeaveIfError(database.Execute(KSQLCreateTable));
#endif


	// Create an index
#ifdef USE_DBMS_API
    _LIT( KName1,  "name" );
	_LIT( KPersonalId1, "personal_id" );
    _LIT( KIndex, "emp_index");
	_LIT( KTable1, "employees" );
    CDbKey* key = CDbKey::NewLC();
    TDbKeyCol name1( KName1 );
    key->AddL( name1 );
	TDbKeyCol id( KPersonalId1 );
	key->AddL( id );
    key->MakeUnique();
    User::LeaveIfError(database.CreateIndex( KIndex, KTable1,*key ) );
    CleanupStack::PopAndDestroy(); // key
#else
	// It seems this is also very important, otherwise, we cannot insert a record.
	 _LIT(KSQLTableIndex,"CREATE UNIQUE INDEX emp_index ON employees (name, personal_id)");
    User::LeaveIfError(database.Execute(KSQLTableIndex));
#endif
	
	CleanupStack::PopAndDestroy(2); // fsSession

}

/*
-----------------------------------------------------------------------------

    CDBMSengine::AddRecord()
	
	Description: Add a new record into database.
	Comments:

    Returns values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::AddRecordL()
{
	TInt ret;

	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;

	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );
	ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		// TBuf<64> des1;
		// CEikonEnv::Static()->ReadResource( des1, R_PLEASE_CREATE_DB_FIRST );

		CEikonEnv::Static()->InfoWinL( _L("Information"), _L("Please create database first!") );

		CleanupStack::PopAndDestroy(); // dbSession
		return;
		}
    CleanupClosePushL( database );

#ifdef USE_DBMS_API
    _LIT( KSQLInsert, "SELECT name, sex, birthday, personal_id FROM employees");

	RDbView view;
    view.Prepare( database, TDbQuery(KSQLInsert), RDbView::EInsertOnly );

    view.InsertL();
    view.SetColL(1, iName );
    view.SetColL(2, iSex );
    view.SetColL(3, iBirthday );
	view.SetColL(4, iPersonal_id );
    view.PutL();
	view.Close();
#else          
	// _LIT(KSQLInsert, "INSERT INTO employees (name, sex, birthday, personal_id) VALUES ('Liu XiaoGuo','male','19450405',10000)");
	// User::LeaveIfError( database.Execute( KSQLInsert ) );

	_LIT(KSQLInsert, "INSERT INTO employees (name, sex, birthday, personal_id) VALUES ('");

    TBuf<KMaxSQLLength>  SQLStatement;
    SQLStatement = KSQLInsert;
    SQLStatement.Append( iName );			// Append name
    SQLStatement.Append( KSQLQuote2 );		// Append quote2
	SQLStatement.AppendNum( iSex );			// Append sex
	SQLStatement.Append( KComma );
	SQLStatement.Append( KSQLQuote1 );
	SQLStatement.Append( iBirthday );		// Append Birthday
	SQLStatement.Append( KSQLQuote2 );
    SQLStatement.AppendNum( iPersonal_id ); // Append personal id
    SQLStatement.Append( KSQLend );			// Append ")"

	// _LIT(KSQLInsert1, "INSERT INTO employees (name, sex, birthday, personal_id) VALUES ('Liu XiaoGuo', 'male', '19450405', 10000)");
	// User::LeaveIfError( database.Execute( KSQLInsert1 ));

	// The following statement returns the number of row inserted, otherwise,
	// -11 is returned.
    ret = database.Execute( SQLStatement );
	if ( ret != 1 )
		{
		// Prompt cannot add record.
		CEikonEnv::Static()->InfoWinL( _L("Duplicated record!"), _L("Cannot add...") );
		}
#endif

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession    
}


/*
-----------------------------------------------------------------------------

    CDBMSengine::RetrieveRecordL()
	
	Description: Retrieve records from database.
	Comments:
    Returns values: if it is found, return true, if not, return false.

-----------------------------------------------------------------------------
*/
TBool CDBMSengine::RetrieveRecordL()
{
	TInt ret;
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );
	ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CEikonEnv::Static()->InfoWinL( _L("Information"), _L("Please create database first!"));
		CleanupStack::PopAndDestroy(); // dbSession
		return EFalse;
		}

    CleanupClosePushL( database );

	// Retrieve all the information in the database
	_LIT(KSQLQuery, "SELECT name, personal_id, sex, birthday FROM employees ORDER BY personal_id");

	RDbView view;
	view.Prepare( database, TDbQuery(KSQLQuery));
	view.EvaluateAll();
   
	TBufC<KDbNameLen> name;
	TUint32 personal_id;
	
	TBool b = view.FirstL();
	if ( !b )
		{
		view.Close();

		CleanupStack::PopAndDestroy(); // pop and close database
		CleanupStack::PopAndDestroy(); // pop and close dbSession
		return EFalse;
		}

	TBool found = EFalse;
    while (view.AtRow())
		{
		view.GetL();
		name = view.ColDes(1);
		// MessageBox( name );
        personal_id = view.ColUint32(2);
        view.NextL();

		found = ETrue;
        }

	view.Close();

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession

	return found;
}


/*
-----------------------------------------------------------------------------

    CDBMSengine::RetrieveRecordL()
	
	Description: Retrieve a record from database given a name for the name 
				 field in database
	Comments:

    Returns values: if it is found, return true, if not, return false.

-----------------------------------------------------------------------------
*/
TBool CDBMSengine::RetrieveRecordL( const TDes &aName)
{
	TInt ret;

	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );
	ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CEikonEnv::Static()->InfoWinL( _L("Information"), _L("Please create database first!"));
		CleanupStack::PopAndDestroy(); // dbSession
		return EFalse;
		}

    CleanupClosePushL( database );

	// Retrieve all the information in the database
	_LIT(KSQLQuery, "SELECT name, personal_id, sex, birthday FROM employees WHERE name = '");

    TBuf<KMaxSQLLength>  SQLStatement;
    SQLStatement = KSQLQuery;
    SQLStatement.Append( aName );		// Append name

	_LIT( KOrder, "ORDER BY personal_id" );
	SQLStatement.Append( KSQLQuote1 );
	SQLStatement.Append( KOrder );

	RDbView view;
	// view.Prepare( database, TDbQuery(KSQLQuery));
	view.Prepare( database, TDbQuery( SQLStatement ));
	view.EvaluateAll();
	
	TBool b = view.FirstL();
	if ( !b )
		{
		view.Close();

		// Close();
		CleanupStack::PopAndDestroy(); // pop and close database
		CleanupStack::PopAndDestroy(); // pop and close dbSession
		return EFalse;
		}

	TBool found = EFalse;
    while (view.AtRow())
		{
		view.GetL();
		iName = view.ColDes( 1 );
        iPersonal_id = view.ColUint32( 2 );
		iSex = view.ColUint32( 3 );
		iBirthday = view.ColDes( 4 );
        view.NextL();

		found = ETrue;
        }

	view.Close();

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession

	return found;
}
/*
-----------------------------------------------------------------------------

    CDBMSengine::GetRecordByIndex( TInt aIndex )
	Description: Get a record according to the "index" valued passed in	
	Comments: 
    Returns values: ETrue if the record is found
					EFalse if the record is not found
-----------------------------------------------------------------------------
*/
TBool CDBMSengine::GetRecordByIndexL( TInt aIndex )
{
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );

	TInt ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CleanupStack::PopAndDestroy(); // dbSession
		return EFalse;
		}

    CleanupClosePushL( database );

	// Retrieve all the information in the database
	_LIT(KSQLQuery, "SELECT name, personal_id, sex, birthday FROM employees ORDER BY personal_id");

	RDbView view;
	view.Prepare( database, TDbQuery(KSQLQuery));
	view.EvaluateAll();
   	
	TBool b = view.FirstL();
	if ( !b )
		{
		view.Close();

		CleanupStack::PopAndDestroy(); // pop and close database
		CleanupStack::PopAndDestroy(); // pop and close dbSession
		view.Close();
		return EFalse;
		}

	TInt index =  0;
	TBool found = EFalse;
    while ( view.AtRow() )
		{
		if ( index == aIndex )
			{
			view.GetL();
			iName = view.ColDes( 1 );

			// Save the iName for later update if possible
			iOldName = iName;

			iPersonal_id = view.ColUint32( 2 );
			iSex = view.ColUint32( 3 );
			iBirthday = view.ColDes( 4 );
			found = ETrue;
			break;
		}