dbengine.cpp

Symbian s60 3RD 数据库开发实例

    table.PutL();    // Complete changes (the insertion)
    table.Close();

    return KErrNone;
    }

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

    RDbTable table;
    TInt err = table.Open(iBookstoreDb, 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;
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::GetABookFastL()
//
// Get a book using index. Format of the result is:
//      <Author>|<Title>|<Description>
// ---------------------------------------------------------------------------
TInt CBookstoreDb::GetABookFastL(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.
    User::LeaveIfError(
        rowset.Open(iBookstoreDb, KBooksTable, rowset.EReadOnly));
    User::LeaveIfError(
        rowset.SetIndex(KBooksIndexName));

    // Query colum numbers for author, title, and description
    CDbColSet* colSet = rowset.ColSetL();
    CleanupStack::PushL(colSet);
    TInt authorColumnNo = colSet->ColNo(KBooksAuthorCol);
    TInt titleColumnNo = colSet->ColNo(KBooksTitleCol);
    TInt descrColumnNo = colSet->ColNo(KBooksDescriptionCol);
    CleanupStack::PopAndDestroy(colSet);

    // Search the index for aTitle
    if( rowset.SeekL(seekKey) )
        {
        rowset.GetL();

        RDbColReadStream readStream;     // A stream object for long columns
        readStream.OpenLC(rowset,descrColumnNo);
        readStream.ReadL(description, rowset.ColLength(descrColumnNo));
        readStream.Close();
        CleanupStack::Pop(); //readStream

        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;
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::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* CBookstoreDb::GetBooksByKeyL(const TDesC& aColumnName,
    const TDesC& aSearchString)
    {

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

    // Sql: SELECT Author, Title, Description FROM Books
    //      WHERE "aColumnName LIKE aSearchString"
    //      ORDER BY Title, Author
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(_L("SELECT "));
    sqlStr.Append(KBooksAuthorCol);
    sqlStr.Append(_L(", "));
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(_L(", "));
    sqlStr.Append(KBooksDescriptionCol);
    sqlStr.Append(_L(" FROM "));
    sqlStr.Append(KBooksTable);
    sqlStr.Append(_L(" WHERE "));
    sqlStr.Append(aColumnName);
    sqlStr.Append(_L(" LIKE '"));
    sqlStr.Append(aSearchString);
    sqlStr.Append(_L("' ORDER BY "));
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(_L(", "));
    sqlStr.Append(KBooksAuthorCol);

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

    // Create a view on the database
    RDbView view;
    User::LeaveIfError(
        view.Prepare(iBookstoreDb, 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;

    }

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

    // Sql: DELETE FROM Books WHERE Title LIKE 'aTitle'
    TBuf<KCustomSqlMaxLength> sqlStr;
    sqlStr.Append(_L("DELETE FROM "));
    sqlStr.Append(KBooksTable);
    sqlStr.Append(_L(" WHERE "));
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(_L(" LIKE '"));
    sqlStr.Append(aTitle);
    sqlStr.Append(_L("'"));

    // 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(iBookstoreDb, 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
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::RemoveAllBooksL()
//
// 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 CBookstoreDb::RemoveAllBooksL(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(iBookstoreDb, 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
    }


// ---------------------------------------------------------------------------
// CBookstoreDb::UpdateBookTitle()
//
// Update book title using SQL UPDATE.
// ---------------------------------------------------------------------------
//
TInt CBookstoreDb::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 iBookstoreDb.Execute(sqlStr);
    }


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

    // Open the Books table.
    User::LeaveIfError(
        booksTable.Open(iBookstoreDb, KBooksTable, booksTable.EReadOnly));
    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).