dbmsengine.cpp

symbian基本的数据库编程,数据库的常用操作.

/*
 * ============================================================================
 *  Name     : BookDb from DBMSEngine.cpp
 *  Part of  : DBMS
 *  Created  : 04.11.2006 by Forum Nokia
 *  Version  : 2.0
 *  Copyright: Nokia Corporation
 * ============================================================================
 */

#include <badesca.h>    // CDesCArrayFlat
#include <s32file.h>    // CFileStore & CPermanentFileStore
#include <bautils.h>    // file helpers
#include <eikenv.h>
#include "DBMSEngine.h"

// Implementation specific constants
const int KCustomSqlMaxLength = 256;
const int KArrayGranularity = 5;     // for CDesCArrayFlat

// ---------------------------------------------------------------------------
// CBookDb::NewL()
//
// Create instance of the Book database engine.
// ---------------------------------------------------------------------------
CBookDb* CBookDb::NewL() 
    {
    CBookDb* tmp = new (ELeave)CBookDb();
    CleanupStack::PushL(tmp);
    tmp->ConstructL();
    CleanupStack::Pop();
    return tmp;
    }

// ---------------------------------------------------------------------------
// CBookDb::~CBookDb()
//
// Destructor of the Book database engine. Release resources.
// ---------------------------------------------------------------------------
CBookDb::~CBookDb()
    {
    Close();  // Just in case, if the user does not close this explicitely
    iFsSession.Close();
    }

// ---------------------------------------------------------------------------
// CBookDb::ConstructL()
//
// Second phase construction. Leaves, if RFs session cannot be created.
// ---------------------------------------------------------------------------
void CBookDb::ConstructL()
    {
    TInt err = iFsSession.Connect();
    if(err)
        User::Leave(err);
    }

// ---------------------------------------------------------------------------
// CBookDb::CBookDb()
//
// Constructor
// ---------------------------------------------------------------------------
CBookDb::CBookDb()
    {
    iOpen = EFalse;
    }

// ---------------------------------------------------------------------------
// CBookDb::OpenDbL()
//
// Open existing Book database for exclusive access.
// ---------------------------------------------------------------------------
TInt CBookDb::OpenDb(const TFileName& aExistingBookFile)
    {
    Close();

    if(!BaflUtils::FileExists(iFsSession, aExistingBookFile))
        {
            return KErrNotFound;
        }

	TRAPD(error, 
		iFileStore = CPermanentFileStore::OpenL(iFsSession, aExistingBookFile, 
			EFileRead|EFileWrite);
		iFileStore->SetTypeL(iFileStore->Layout());/* Set file store type*/
		iBookDb.OpenL(iFileStore,iFileStore->Root())
		);
    if(error!=KErrNone)
    {
    	return error;
    }
    
    iOpen = ETrue;
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::CreateDbL()
//
// Create a new database. The database will be in exclusive access mode.
// ---------------------------------------------------------------------------
TInt CBookDb::CreateDb(const TFileName& aNewBookFile)
    {
    Close();

    // Create empty database file.
    TRAPD(error,
    	iFileStore = CPermanentFileStore::ReplaceL(iFsSession, aNewBookFile, 
    		EFileRead|EFileWrite);
    	iFileStore->SetTypeL(iFileStore->Layout());// Set file store type
    	TStreamId id = iBookDb.CreateL(iFileStore);// Create stream object
    	iFileStore->SetRootL(id);// Keep database id as root of store
    	iFileStore->CommitL();// Complete creation by commiting
    	// Create Book tables and indexes
    	CreateBooksTableL();
    	CreateBooksIndexL();
    	);
    
    if(error!=KErrNone)
    {
    	return error;
    }
    iOpen = ETrue;
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveDb()
//
// First remove the Books table. Then remove the database file.
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveDb(const TFileName& aExistingBookFile)
    {
    Close();

    if(!BaflUtils::FileExists(iFsSession, aExistingBookFile))
        {
            return KErrNotFound;
        }

    // It is enough to delete the database file directly. Because this example
    // demonstrates DDL statements, it first opens and drops the Books table.

	TInt error = OpenDb(aExistingBookFile);
	if(error!=KErrNone)
    {
    	return error;
    }
    
    DropBooksTable();
    Close();

    iFsSession.Delete(aExistingBookFile);
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::Close()
//
// Close the database.
// ---------------------------------------------------------------------------
TInt CBookDb::Close()
    {
    iBookDb.Close();
    if(iFileStore)
        {
        delete iFileStore;
        iFileStore = NULL;
        }
    iOpen = EFalse;
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::IsOpen()
//
// Return open status of the database.
// ---------------------------------------------------------------------------
TBool CBookDb::IsOpen() const
    {
    return iOpen;
    }

// ---------------------------------------------------------------------------
// CBookDb::CreateBooksTableL()
//
// Creates Books table. Leaves, if the table cannot be created.
// ---------------------------------------------------------------------------
void CBookDb::CreateBooksTableL()
    {

    // Specify columns for Books table
    TDbCol authorCol(KBooksAuthorCol, EDbColText);   // Using default length
    TDbCol titleCol(KBooksTitleCol, EDbColText, KTitleMaxLength);
    titleCol.iAttributes = TDbCol::ENotNull;
    TDbCol descriptionCol(KBooksDescriptionCol, EDbColLongText); // Stream Data

    // Add the columns to column set
    CDbColSet* bookColSet = CDbColSet::NewLC();
    bookColSet->AddL(authorCol);
    bookColSet->AddL(titleCol);
    bookColSet->AddL(descriptionCol);

    // Create the Books table
    User::LeaveIfError(iBookDb.CreateTable(KBooksTable,
        *bookColSet));
    CleanupStack::PopAndDestroy(bookColSet);
    }

// ---------------------------------------------------------------------------
// CBookDb::CreateBooksIndexL()
//
// Creates an index for Books table. Leaves, if the index cannot be created.
// ---------------------------------------------------------------------------
void CBookDb::CreateBooksIndexL()
    {
    // Create index consisting of two columns
    TDbKeyCol authorCol(KBooksAuthorCol);
    TDbKeyCol titleCol(KBooksTitleCol);

    CDbKey* index = CDbKey::NewLC();   // create index key set
    index->AddL(titleCol);
    index->AddL(authorCol);
    User::LeaveIfError(iBookDb.CreateIndex(
        KBooksIndexName, KBooksTable, *index));
    CleanupStack::PopAndDestroy(index);
    }

// ---------------------------------------------------------------------------
// CBookDb::DropBooksTable()
//
// Drop the Books table incrementally. Uses RDbIncremental and DDL statement.
// ---------------------------------------------------------------------------
void CBookDb::DropBooksTable()
    {
    
    _LIT(KDropTable, "DROP TABLE ");

    // Sql: DROP TABLE Books
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KDropTable);
    sqlStr.Append(KBooksTable);

    RDbIncremental incOp;
    TInt incStep = 0xFFFF;
    // Initialise Execution
    TInt incStat = incOp.Execute(iBookDb, sqlStr, incStep);
    while (incStep>0 && incStat==KErrNone)
        {
        incStat = incOp.Next(incStep); // Do the work
        }
    incOp.Close();
    }

// ---------------------------------------------------------------------------
// CBookDb::AddBookWithSqlL()
//
// Add a book to database using RDbView and SQL
// ---------------------------------------------------------------------------
TInt CBookDb::AddBookWithSql(const TDesC& aAuthor,
                         const TDesC& aTitle,
                         const TDesC& aDescription)
    {

    if(aAuthor.Length()==0 || aTitle.Length()==0 || aDescription.Length()==0)
        {
        return KErrGeneral;
        }

	_LIT(KSelect, "SELECT ");
	_LIT(KFrom, " FROM ");
	_LIT(KOrderBy, " ORDER BY ");
	_LIT(KDot, ", ");

    // Sql: SELECT Author, Title, Description FROM Books ORDER BY Title, Author
    TBuf<KCustomSqlMaxLength> sqlStr;
    
    sqlStr.Append(KSelect);
    sqlStr.Append(KBooksAuthorCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksDescriptionCol);
    sqlStr.Append(KFrom);
    sqlStr.Append(KBooksTable);
    sqlStr.Append(KOrderBy);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksAuthorCol);

    RDbView view;    // Create a view on the database
    TInt error;
    error = view.Prepare(iBookDb, TDbQuery(sqlStr, EDbCompareFolded));
    if(error!=KErrNone)
    {
    	return error;
    }
    error = view.EvaluateAll();
    if(error!=KErrNone)
    {
    	return error;
    }
    RDbColWriteStream writeStream;  // Use stream to insert the description
    
    TRAP(error,
    	view.InsertL();  // Insert a row. Column order matches sql select statement
    	view.SetColL(1, aAuthor);
   	 	view.SetColL(2, aTitle);
    	writeStream.OpenL(view, 3);
    	writeStream.WriteL(aDescription);
    	
    	);
    if(error!=KErrNone)
    {
    	return error;
    }
    writeStream.Close();
    TRAP(error, view.PutL()); // Complete insertion
	if(error!=KErrNone)
    {
    	return error;
    } 
    view.Close();
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::AddBookWithCppApi()
//
// Add a book to database using RDbTable API
// ---------------------------------------------------------------------------
TInt CBookDb::AddBookWithCppApi(const TDesC& aAuthor,
                            const TDesC& aTitle,
                            const TDesC& aDescription)
    {

    if(aAuthor.Length()==0 || aTitle.Length()==0 || aDescription.Length()==0)
        {
        return KErrGeneral;
        }

    // Create an updateable database table object
    RDbTable table;
    TInt err = table.Open(iBookDb, KBooksTable, table.EUpdatable);
    
    if(err!=KErrNone)
    {
    	return err;
    }
    
	CDbColSet* booksColSet = table.ColSetL();
    CleanupStack::PushL(booksColSet);
    
    table.Reset();
    RDbColWriteStream writeStream;
    
    TRAPD(error,
    	table.InsertL();
       	table.SetColL(booksColSet->ColNo(KBooksAuthorCol), aAuthor); // col = 1
    	table.SetColL(booksColSet->ColNo(KBooksTitleCol), aTitle);   // col = 2
    	// Use a stream for the long text column
    		writeStream.OpenL(table, booksColSet->ColNo(KBooksDescriptionCol));
    	writeStream.WriteL(aDescription);
    	);