examples/ForumNokia/DBMS/src/DBMSEngine.cpp

/*
 * Copyright (c) 2008-2010 Nokia Corporation and/or its subsidiary(-ies). All rights reserved.
 *    
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *    
 *  * Redistributions of source code must retain the above copyright notice, this
 *    list of conditions and the following disclaimer.
 *  * Redistributions in binary form must reproduce the above copyright notice,
 *    this list of conditions and the following disclaimer in the documentation
 *    and/or other materials provided with the distribution.
 *  * Neither the name of Nokia Corporation nor the names of its contributors
 *    may be used to endorse or promote products derived from this software
 *    without specific prior written permission.
 *    
 *    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 *    AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 *    IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 *    DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
 *    FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 *    DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 *    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 *    CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 *    OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 *    OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *    
 *    Description:  
 */

#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::AddBookWithCppApiL()
//
// Add a book to database using RDbTable API
// ---------------------------------------------------------------------------
TInt CBookDb::AddBookWithCppApiL(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);
        );

        if(error!=KErrNone)
    {
        return error;
    }   
        writeStream.Close();
        
    TRAP(err, table.PutL());    // Complete changes (the insertion)
        if(err!=KErrNone)
    {
        return err;
    }   
    
    CleanupStack::PopAndDestroy(booksColSet);
    table.Close();
        
    return KErrNone;

    }

// ---------------------------------------------------------------------------
// CBookDb::GetAllBooksL()
//
// Get array of all books in database. Format of each array item is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::GetAllBooksL()
    {
    TPtrC author, title;
    TBuf<KDescriptionMaxLength> description;
    TBuf<KBookItemMaxLength> rowText;

    RDbTable table;
    TInt err = table.Open(iBookDb, KBooksTable, table.EReadOnly);
    User::LeaveIfError(err);

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    table.Reset();
    CDbColSet* colSet = table.ColSetL();
    CleanupStack::PushL(colSet);

    for (table.FirstL(); table.AtRow(); table.NextL())
        {
        description.Zero();
        rowText.Zero();

        table.GetL();

        author.Set(table.ColDes(colSet->ColNo(KBooksAuthorCol)));
        title.Set(table.ColDes(colSet->ColNo(KBooksTitleCol)));

        TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
        RDbColReadStream readStream;       // A stream object for long columns
        readStream.OpenLC(table,descrColNo);
        readStream.ReadL(description, table.ColLength(descrColNo));
        readStream.Close();
        CleanupStack::Pop(); //readStream

        rowText.Append(author);
        rowText.Append(KSeparator);
        rowText.Append(title);
        rowText.Append(KSeparator);
        rowText.Append(description);

        resultArray->AppendL(rowText); // Copy rowText to resultArray
        }
    CleanupStack::PopAndDestroy(colSet);
    CleanupStack::Pop(resultArray);
    table.Close();

    return resultArray;
    }

// ---------------------------------------------------------------------------
// CBookDb::GetABookFast()
//
// Get a book using index. Format of the result is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
TInt CBookDb::GetABookFast(const TDesC& aTitle, TDes& aResult)
    {
    TInt err = KErrNone;
    TBuf<KDescriptionMaxLength> description; // Only 128 first characters read
    RDbTable rowset;

    TDbSeekKey seekKey(aTitle); // Initialize one-column seek key

    // Open view to "Books" table. Use index to browse the table.
    err = rowset.Open(iBookDb, KBooksTable, rowset.EReadOnly);
    if(err!=KErrNone)
    {
        return err;
    }
    err = rowset.SetIndex(KBooksIndexName);
    if(err!=KErrNone)
    {
        return err;
    }
    // Query colum numbers for author, title, and description
    CDbColSet* colSet=NULL;
    TRAP(err, colSet = rowset.ColSetL());
    if(err!=KErrNone)
    {
        return err;
    }

    TInt authorColumnNo = colSet->ColNo(KBooksAuthorCol);
    TInt titleColumnNo = colSet->ColNo(KBooksTitleCol);
    TInt descrColumnNo = colSet->ColNo(KBooksDescriptionCol);

    // Search the index for aTitle
    TBool isTitle = false;
    TRAP(err, isTitle = rowset.SeekL(seekKey));
    if(isTitle)
        {
        RDbColReadStream readStream;     // A stream object for long columns
        TRAPD(error, 
                rowset.GetL();
                readStream.OpenL(rowset,descrColumnNo);
                readStream.ReadL(description, rowset.ColLength(descrColumnNo));
                );
        if(error!=KErrNone)
        {
                return error;
        }

        readStream.Close();

        aResult.Zero();
        aResult.Append(rowset.ColDes(authorColumnNo));
        aResult.Append(KSeparator);
        aResult.Append(rowset.ColDes(titleColumnNo));
        aResult.Append(KSeparator);
        aResult.Append(description);

        err = KErrNone;
        }
    else
        {
        err = KErrNotFound;
        }

    rowset.Close();
    return err;
    }

// ---------------------------------------------------------------------------
// CBookDb::GetBooksByKeyL()
//
// Get array of books from database according to column name and a search
// pattern. Format of each array item is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::GetBooksByKeyL(const TDesC& aColumnName,
    const TDesC& aSearchString)
    {

    TPtrC author, title;
    TBuf<KDescriptionMaxLength> description;
    TBuf<KBookItemMaxLength> rowText;

        _LIT(KSelect, "SELECT ");
        _LIT(KFrom, " FROM ");
        _LIT(KWhere, " WHERE ");
        _LIT(KLike, " LIKE '");
        _LIT(KOrderBy, "' ORDER BY ");
        _LIT(KDot, ", ");

    // Sql: SELECT Author, Title, Description FROM Books
    //      WHERE "aColumnName LIKE aSearchString"
    //      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(KWhere);
    sqlStr.Append(aColumnName);
    sqlStr.Append(KLike);
    sqlStr.Append(aSearchString);
    sqlStr.Append(KOrderBy);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KDot);
    sqlStr.Append(KBooksAuthorCol);

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    // Create a view on the database
    RDbView view;
    User::LeaveIfError(
        view.Prepare(iBookDb, TDbQuery(sqlStr), view.EReadOnly));
    User::LeaveIfError(view.EvaluateAll());

    CDbColSet* colSet = view.ColSetL();
    CleanupStack::PushL(colSet);

    // Append each result row to array
    for (view.FirstL(); view.AtRow(); view.NextL())
        {

        description.Zero();
        rowText.Zero();

        view.GetL();

        author.Set(view.ColDes(colSet->ColNo(KBooksAuthorCol)));
        title.Set(view.ColDes(colSet->ColNo(KBooksTitleCol)));

        TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
        RDbColReadStream readStream;       // A stream object for long columns
        readStream.OpenLC(view, descrColNo);
        readStream.ReadL(description, view.ColLength(descrColNo));
        readStream.Close();
        CleanupStack::Pop(); //readStream

        rowText.Append(author);
        rowText.Append(KSeparator);
        rowText.Append(title);
        rowText.Append(KSeparator);
        rowText.Append(description);

        resultArray->AppendL(rowText);
        }
    CleanupStack::PopAndDestroy(colSet);
    view.Close();
    CleanupStack::Pop(resultArray);

    return resultArray;

    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveBooks()
//
// Delete a book using title pattern and RDbUpdate (DML)
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveBooks(const TDesC& aTitle, TInt& aResultCount)
    {
    RDbUpdate updOp;

        _LIT(KDeleteFrom, "DELETE FROM ");
        _LIT(KWhere, " WHERE ");
        _LIT(KLike, " LIKE '");
        _LIT(KDot, "'");
        
    // Sql: DELETE FROM Books WHERE Title LIKE 'aTitle'
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KDeleteFrom);
    sqlStr.Append(KBooksTable);
    sqlStr.Append(KWhere);
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(KLike);
    sqlStr.Append(aTitle);
    sqlStr.Append(KDot);

    // Initialize execution and perform the first step.
    // Note: Execute() returns 0 (=KErrNone), but it does not affect database
    //       until Next() is called.
    TInt incStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
    incStat = updOp.Next(); // This will leave, if Execute() failed.

    while( incStat == 1 ) // Just in case, if the operation has more steps
        {
        incStat = updOp.Next();
        }
    aResultCount = updOp.RowCount();
    updOp.Close();
    return incStat; // KErrNone or system wide error code
    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveAllBooks()
//
// Delete books using asynchronous API. (RDbUpdate and DML)
// This implementation is still synchronous, because it uses
// User::WaitForRequest. Normally asynchronous functionality should be hidden
// into active object and client callback interfaces.
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveAllBooks(TInt& aResultCount)
    {
    _LIT(KDeleteFrom, "DELETE FROM ");

    // Sql: DELETE FROM Books
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KDeleteFrom);
    sqlStr.Append(KBooksTable);

    RDbUpdate updOp;
    TRequestStatus incStat(1);
    TInt updStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
    while (updStat==KErrNone && incStat ==1)
        {
        updOp.Next(incStat);           // Start async operation. It returns
                                       // immediately.
        User::WaitForRequest(incStat); // For simplicity wait completion here.
        }

    aResultCount = updOp.RowCount();
    updOp.Close();

    if(updStat!=KErrNone)
        return updStat;       // System wide error code
    else
        return incStat.Int(); // KErrNone or system wide error code
    }


// ---------------------------------------------------------------------------
// CBookDb::UpdateBookTitle()
//
// Update book title using SQL UPDATE.
// ---------------------------------------------------------------------------
//
TInt CBookDb::UpdateBookTitle(const TDesC& aOldTitleKey,
    const TDesC& aNewTitle)
    {
    _LIT(KSQLUpdateStart, "UPDATE Books SET Title = '");
    _LIT(KSQLUpdateMiddle, "' WHERE Title = '");
    _LIT(KSQLUpdateEnd, "'");

    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(KSQLUpdateStart);
    sqlStr.Append(aNewTitle);
    sqlStr.Append(KSQLUpdateMiddle);
    sqlStr.Append(aOldTitleKey);
    sqlStr.Append(KSQLUpdateEnd);

    return iBookDb.Execute(sqlStr);
    }


// ---------------------------------------------------------------------------
// CBookDb::ColumnNamesAndSizesL()
//
// Get array of column names and sizes of the Books table.
// ---------------------------------------------------------------------------
CDesCArrayFlat* CBookDb::ColumnNamesAndSizesL()
    {
    RDbTable booksTable;
    TBuf<32> columnNameAndSize;
    _LIT(KDelimiter, ": ");
    _LIT(KNoSize,"No size");

    // Open the Books table.
    TInt err = booksTable.Open(iBookDb, KBooksTable, booksTable.EReadOnly);
    User::LeaveIfError(err);
    
    CleanupClosePushL(booksTable);  // Remember to pop and close

    CDesCArrayFlat* resultArray =
        new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
    CleanupStack::PushL(resultArray);

    // Iterate through the colums of Books table. Extract the column name and
    // column size (size only for text columns).´
    // Note: Description column is long text. Database limits its size
    //       only by hardware. If size is queried, it is -1
    CDbColSet* colSet = booksTable.ColSetL();
    CleanupStack::PushL(colSet);
    TDbColSetIter colIter(*colSet);
    while(colIter)
        {
        columnNameAndSize.Zero();
        columnNameAndSize.Append(colIter->iName);
        columnNameAndSize.Append(KDelimiter);
        if(colIter->iType == EDbColText)
            columnNameAndSize.AppendNum(colIter->iMaxLength);
        else
            columnNameAndSize.Append(KNoSize);
        resultArray->AppendL(columnNameAndSize);
        colIter++;
        }
    CleanupStack::PopAndDestroy(colSet);
    CleanupStack::Pop(resultArray);

    // Pop the booksTable from cleanup stack and close it.
    CleanupStack::PopAndDestroy();

    return resultArray;
    }

// ---------------------------------------------------------------------------
// CBookDb::HasDateColumnL()
//
// Tests wheter the Books table has date column
// ---------------------------------------------------------------------------
TInt CBookDb::HasDateColumn(TBool& aReturnValue)
    {
    RDbTable booksTable;
    aReturnValue = EFalse;

    // Open the Books table.
    TInt err = booksTable.Open(iBookDb, KBooksTable, booksTable.EReadOnly);
    if(err!=KErrNone)
    {
        return err;
    }

    // Iterate through the colums of Books table. Check whether there is
    // a 'PublishDate' column
    CDbColSet* colSet=NULL;
    TRAP(err, colSet = booksTable.ColSetL());
    if(err!=KErrNone)
    {
        return err;
    }

    TDbColSetIter colIter(*colSet);
    while(colIter)
        {
        if( (colIter->iName).Compare(KBooksDateCol) == 0) // 0 = equal
            {
            aReturnValue = ETrue;
            break;
            }
        colIter++;
        }

    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookDb::AddDateColumn()
//
// Adds date column to Books table (DDL).
// ---------------------------------------------------------------------------
TInt CBookDb::AddDateColumn()
    {
    _LIT(KSqlAddDate, "ALTER TABLE Books ADD PublishDate DATE");
    return iBookDb.Execute(KSqlAddDate);
    }

// ---------------------------------------------------------------------------
// CBookDb::RemoveDateColumn()
//
// Removes date column from Books table (DDL).
// ---------------------------------------------------------------------------
TInt CBookDb::RemoveDateColumn()
    {
    _LIT(KSqlRemoveDate, "ALTER TABLE Books DROP PublishDate");
    return iBookDb.Execute(KSqlRemoveDate);
    }

---