examples/ForumNokia/DBMS/src/DBMSEngine.cpp

00001 /*
00002  * Copyright (c) 2008-2010 Nokia Corporation and/or its subsidiary(-ies). All rights reserved.
00003  *    
00004  * Redistribution and use in source and binary forms, with or without
00005  * modification, are permitted provided that the following conditions are met:
00006  *    
00007  *  * Redistributions of source code must retain the above copyright notice, this
00008  *    list of conditions and the following disclaimer.
00009  *  * Redistributions in binary form must reproduce the above copyright notice,
00010  *    this list of conditions and the following disclaimer in the documentation
00011  *    and/or other materials provided with the distribution.
00012  *  * Neither the name of Nokia Corporation nor the names of its contributors
00013  *    may be used to endorse or promote products derived from this software
00014  *    without specific prior written permission.
00015  *    
00016  *    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
00017  *    AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
00018  *    IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
00019  *    DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
00020  *    FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
00021  *    DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
00022  *    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
00023  *    CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
00024  *    OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
00025  *    OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
00026  *    
00027  *    Description:  
00028  */
00029 
00030 #include <badesca.h>    // CDesCArrayFlat
00031 #include <s32file.h>    // CFileStore & CPermanentFileStore
00032 #include <bautils.h>    // file helpers
00033 #include <eikenv.h>
00034 #include "DBMSEngine.h"
00035 
00036 // Implementation specific constants
00037 const int KCustomSqlMaxLength = 256;
00038 const int KArrayGranularity = 5;     // for CDesCArrayFlat
00039 
00040 // ---------------------------------------------------------------------------
00041 // CBookDb::NewL()
00042 //
00043 // Create instance of the Book database engine.
00044 // ---------------------------------------------------------------------------
00045 CBookDb* CBookDb::NewL() 
00046     {
00047     CBookDb* tmp = new (ELeave)CBookDb();
00048     CleanupStack::PushL(tmp);
00049     tmp->ConstructL();
00050     CleanupStack::Pop();
00051     return tmp;
00052     }
00053 
00054 // ---------------------------------------------------------------------------
00055 // CBookDb::~CBookDb()
00056 //
00057 // Destructor of the Book database engine. Release resources.
00058 // ---------------------------------------------------------------------------
00059 CBookDb::~CBookDb()
00060     {
00061     Close();  // Just in case, if the user does not close this explicitely
00062     iFsSession.Close();
00063     }
00064 
00065 // ---------------------------------------------------------------------------
00066 // CBookDb::ConstructL()
00067 //
00068 // Second phase construction. Leaves, if RFs session cannot be created.
00069 // ---------------------------------------------------------------------------
00070 void CBookDb::ConstructL()
00071     {
00072     TInt err = iFsSession.Connect();
00073     if(err)
00074         User::Leave(err);
00075     }
00076 
00077 // ---------------------------------------------------------------------------
00078 // CBookDb::CBookDb()
00079 //
00080 // Constructor
00081 // ---------------------------------------------------------------------------
00082 CBookDb::CBookDb()
00083     {
00084     iOpen = EFalse;
00085     }
00086 
00087 // ---------------------------------------------------------------------------
00088 // CBookDb::OpenDbL()
00089 //
00090 // Open existing Book database for exclusive access.
00091 // ---------------------------------------------------------------------------
00092 TInt CBookDb::OpenDb(const TFileName& aExistingBookFile)
00093     {
00094     Close();
00095 
00096     if(!BaflUtils::FileExists(iFsSession, aExistingBookFile))
00097         {
00098             return KErrNotFound;
00099         }
00100 
00101         TRAPD(error, 
00102                 iFileStore = CPermanentFileStore::OpenL(iFsSession, aExistingBookFile, 
00103                         EFileRead|EFileWrite);
00104                 iFileStore->SetTypeL(iFileStore->Layout());/* Set file store type*/
00105                 iBookDb.OpenL(iFileStore,iFileStore->Root())
00106                 );
00107     if(error!=KErrNone)
00108     {
00109         return error;
00110     }
00111     
00112     iOpen = ETrue;
00113     return KErrNone;
00114     }
00115 
00116 // ---------------------------------------------------------------------------
00117 // CBookDb::CreateDbL()
00118 //
00119 // Create a new database. The database will be in exclusive access mode.
00120 // ---------------------------------------------------------------------------
00121 TInt CBookDb::CreateDb(const TFileName& aNewBookFile)
00122     {
00123     Close();
00124 
00125     // Create empty database file.
00126     TRAPD(error,
00127         iFileStore = CPermanentFileStore::ReplaceL(iFsSession, aNewBookFile, 
00128                 EFileRead|EFileWrite);
00129         iFileStore->SetTypeL(iFileStore->Layout());// Set file store type
00130         TStreamId id = iBookDb.CreateL(iFileStore);// Create stream object
00131         iFileStore->SetRootL(id);// Keep database id as root of store
00132         iFileStore->CommitL();// Complete creation by commiting
00133         // Create Book tables and indexes
00134         CreateBooksTableL();
00135         CreateBooksIndexL();
00136         );
00137     
00138     if(error!=KErrNone)
00139     {
00140         return error;
00141     }
00142     iOpen = ETrue;
00143     return KErrNone;
00144     }
00145 
00146 // ---------------------------------------------------------------------------
00147 // CBookDb::RemoveDb()
00148 //
00149 // First remove the Books table. Then remove the database file.
00150 // ---------------------------------------------------------------------------
00151 TInt CBookDb::RemoveDb(const TFileName& aExistingBookFile)
00152     {
00153     Close();
00154 
00155     if(!BaflUtils::FileExists(iFsSession, aExistingBookFile))
00156         {
00157             return KErrNotFound;
00158         }
00159 
00160     // It is enough to delete the database file directly. Because this example
00161     // demonstrates DDL statements, it first opens and drops the Books table.
00162 
00163         TInt error = OpenDb(aExistingBookFile);
00164         if(error!=KErrNone)
00165     {
00166         return error;
00167     }
00168     
00169     DropBooksTable();
00170     Close();
00171 
00172     iFsSession.Delete(aExistingBookFile);
00173     return KErrNone;
00174     }
00175 
00176 // ---------------------------------------------------------------------------
00177 // CBookDb::Close()
00178 //
00179 // Close the database.
00180 // ---------------------------------------------------------------------------
00181 TInt CBookDb::Close()
00182     {
00183     iBookDb.Close();
00184     if(iFileStore)
00185         {
00186         delete iFileStore;
00187         iFileStore = NULL;
00188         }
00189     iOpen = EFalse;
00190     return KErrNone;
00191     }
00192 
00193 // ---------------------------------------------------------------------------
00194 // CBookDb::IsOpen()
00195 //
00196 // Return open status of the database.
00197 // ---------------------------------------------------------------------------
00198 TBool CBookDb::IsOpen() const
00199     {
00200     return iOpen;
00201     }
00202 
00203 // ---------------------------------------------------------------------------
00204 // CBookDb::CreateBooksTableL()
00205 //
00206 // Creates Books table. Leaves, if the table cannot be created.
00207 // ---------------------------------------------------------------------------
00208 void CBookDb::CreateBooksTableL()
00209     {
00210 
00211     // Specify columns for Books table
00212     TDbCol authorCol(KBooksAuthorCol, EDbColText);   // Using default length
00213     TDbCol titleCol(KBooksTitleCol, EDbColText, KTitleMaxLength);
00214     titleCol.iAttributes = TDbCol::ENotNull;
00215     TDbCol descriptionCol(KBooksDescriptionCol, EDbColLongText); // Stream Data
00216 
00217     // Add the columns to column set
00218     CDbColSet* bookColSet = CDbColSet::NewLC();
00219     bookColSet->AddL(authorCol);
00220     bookColSet->AddL(titleCol);
00221     bookColSet->AddL(descriptionCol);
00222 
00223     // Create the Books table
00224     User::LeaveIfError(iBookDb.CreateTable(KBooksTable,
00225         *bookColSet));
00226     CleanupStack::PopAndDestroy(bookColSet);
00227     }
00228 
00229 // ---------------------------------------------------------------------------
00230 // CBookDb::CreateBooksIndexL()
00231 //
00232 // Creates an index for Books table. Leaves, if the index cannot be created.
00233 // ---------------------------------------------------------------------------
00234 void CBookDb::CreateBooksIndexL()
00235     {
00236     // Create index consisting of two columns
00237     TDbKeyCol authorCol(KBooksAuthorCol);
00238     TDbKeyCol titleCol(KBooksTitleCol);
00239 
00240     CDbKey* index = CDbKey::NewLC();   // create index key set
00241     index->AddL(titleCol);
00242     index->AddL(authorCol);
00243     User::LeaveIfError(iBookDb.CreateIndex(
00244         KBooksIndexName, KBooksTable, *index));
00245     CleanupStack::PopAndDestroy(index);
00246     }
00247 
00248 // ---------------------------------------------------------------------------
00249 // CBookDb::DropBooksTable()
00250 //
00251 // Drop the Books table incrementally. Uses RDbIncremental and DDL statement.
00252 // ---------------------------------------------------------------------------
00253 void CBookDb::DropBooksTable()
00254     {
00255     
00256     _LIT(KDropTable, "DROP TABLE ");
00257 
00258     // Sql: DROP TABLE Books
00259     TBuf<KCustomSqlMaxLength> sqlStr;
00260     sqlStr.Append(KDropTable);
00261     sqlStr.Append(KBooksTable);
00262 
00263     RDbIncremental incOp;
00264     TInt incStep = 0xFFFF;
00265     // Initialise Execution
00266     TInt incStat = incOp.Execute(iBookDb, sqlStr, incStep);
00267     while (incStep>0 && incStat==KErrNone)
00268         {
00269         incStat = incOp.Next(incStep); // Do the work
00270         }
00271     incOp.Close();
00272     }
00273 
00274 // ---------------------------------------------------------------------------
00275 // CBookDb::AddBookWithSqlL()
00276 //
00277 // Add a book to database using RDbView and SQL
00278 // ---------------------------------------------------------------------------
00279 TInt CBookDb::AddBookWithSql(const TDesC& aAuthor,
00280                          const TDesC& aTitle,
00281                          const TDesC& aDescription)
00282     {
00283 
00284     if(aAuthor.Length()==0 || aTitle.Length()==0 || aDescription.Length()==0)
00285         {
00286         return KErrGeneral;
00287         }
00288 
00289         _LIT(KSelect, "SELECT ");
00290         _LIT(KFrom, " FROM ");
00291         _LIT(KOrderBy, " ORDER BY ");
00292         _LIT(KDot, ", ");
00293 
00294     // Sql: SELECT Author, Title, Description FROM Books ORDER BY Title, Author
00295     TBuf<KCustomSqlMaxLength> sqlStr;
00296     
00297     sqlStr.Append(KSelect);
00298     sqlStr.Append(KBooksAuthorCol);
00299     sqlStr.Append(KDot);
00300     sqlStr.Append(KBooksTitleCol);
00301     sqlStr.Append(KDot);
00302     sqlStr.Append(KBooksDescriptionCol);
00303     sqlStr.Append(KFrom);
00304     sqlStr.Append(KBooksTable);
00305     sqlStr.Append(KOrderBy);
00306     sqlStr.Append(KBooksTitleCol);
00307     sqlStr.Append(KDot);
00308     sqlStr.Append(KBooksAuthorCol);
00309 
00310     RDbView view;    // Create a view on the database
00311     TInt error;
00312     error = view.Prepare(iBookDb, TDbQuery(sqlStr, EDbCompareFolded));
00313     if(error!=KErrNone)
00314     {
00315         return error;
00316     }
00317     error = view.EvaluateAll();
00318     if(error!=KErrNone)
00319     {
00320         return error;
00321     }
00322     RDbColWriteStream writeStream;  // Use stream to insert the description
00323     
00324     TRAP(error,
00325         view.InsertL();  // Insert a row. Column order matches sql select statement
00326         view.SetColL(1, aAuthor);
00327                 view.SetColL(2, aTitle);
00328         writeStream.OpenL(view, 3);
00329         writeStream.WriteL(aDescription);
00330         
00331         );
00332     if(error!=KErrNone)
00333     {
00334         return error;
00335     }
00336     writeStream.Close();
00337     TRAP(error, view.PutL()); // Complete insertion
00338         if(error!=KErrNone)
00339     {
00340         return error;
00341     } 
00342     view.Close();
00343     return KErrNone;
00344     }
00345 
00346 // ---------------------------------------------------------------------------
00347 // CBookDb::AddBookWithCppApiL()
00348 //
00349 // Add a book to database using RDbTable API
00350 // ---------------------------------------------------------------------------
00351 TInt CBookDb::AddBookWithCppApiL(const TDesC& aAuthor,
00352                             const TDesC& aTitle,
00353                             const TDesC& aDescription)
00354     {
00355 
00356     if(aAuthor.Length()==0 || aTitle.Length()==0 || aDescription.Length()==0)
00357         {
00358         return KErrGeneral;
00359         }
00360 
00361     // Create an updateable database table object
00362     RDbTable table;
00363     TInt err = table.Open(iBookDb, KBooksTable, table.EUpdatable);
00364     
00365     if(err!=KErrNone)
00366     {
00367         return err;
00368     }
00369     
00370         CDbColSet* booksColSet = table.ColSetL();
00371     CleanupStack::PushL(booksColSet);
00372     
00373     table.Reset();
00374     RDbColWriteStream writeStream;
00375     
00376     TRAPD(error,
00377         table.InsertL();
00378         table.SetColL(booksColSet->ColNo(KBooksAuthorCol), aAuthor); // col = 1
00379         table.SetColL(booksColSet->ColNo(KBooksTitleCol), aTitle);   // col = 2
00380         // Use a stream for the long text column
00381                 writeStream.OpenL(table, booksColSet->ColNo(KBooksDescriptionCol));
00382         writeStream.WriteL(aDescription);
00383         );
00384 
00385         if(error!=KErrNone)
00386     {
00387         return error;
00388     }   
00389         writeStream.Close();
00390         
00391     TRAP(err, table.PutL());    // Complete changes (the insertion)
00392         if(err!=KErrNone)
00393     {
00394         return err;
00395     }   
00396     
00397     CleanupStack::PopAndDestroy(booksColSet);
00398     table.Close();
00399         
00400     return KErrNone;
00401 
00402     }
00403 
00404 // ---------------------------------------------------------------------------
00405 // CBookDb::GetAllBooksL()
00406 //
00407 // Get array of all books in database. Format of each array item is:
00408 //      <Author>|<Title>|<Description>
00409 // ---------------------------------------------------------------------------
00410 CDesCArrayFlat* CBookDb::GetAllBooksL()
00411     {
00412     TPtrC author, title;
00413     TBuf<KDescriptionMaxLength> description;
00414     TBuf<KBookItemMaxLength> rowText;
00415 
00416     RDbTable table;
00417     TInt err = table.Open(iBookDb, KBooksTable, table.EReadOnly);
00418     User::LeaveIfError(err);
00419 
00420     CDesCArrayFlat* resultArray =
00421         new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
00422     CleanupStack::PushL(resultArray);
00423 
00424     table.Reset();
00425     CDbColSet* colSet = table.ColSetL();
00426     CleanupStack::PushL(colSet);
00427 
00428     for (table.FirstL(); table.AtRow(); table.NextL())
00429         {
00430         description.Zero();
00431         rowText.Zero();
00432 
00433         table.GetL();
00434 
00435         author.Set(table.ColDes(colSet->ColNo(KBooksAuthorCol)));
00436         title.Set(table.ColDes(colSet->ColNo(KBooksTitleCol)));
00437 
00438         TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
00439         RDbColReadStream readStream;       // A stream object for long columns
00440         readStream.OpenLC(table,descrColNo);
00441         readStream.ReadL(description, table.ColLength(descrColNo));
00442         readStream.Close();
00443         CleanupStack::Pop(); //readStream
00444 
00445         rowText.Append(author);
00446         rowText.Append(KSeparator);
00447         rowText.Append(title);
00448         rowText.Append(KSeparator);
00449         rowText.Append(description);
00450 
00451         resultArray->AppendL(rowText); // Copy rowText to resultArray
00452         }
00453     CleanupStack::PopAndDestroy(colSet);
00454     CleanupStack::Pop(resultArray);
00455     table.Close();
00456 
00457     return resultArray;
00458     }
00459 
00460 // ---------------------------------------------------------------------------
00461 // CBookDb::GetABookFast()
00462 //
00463 // Get a book using index. Format of the result is:
00464 //      <Author>|<Title>|<Description>
00465 // ---------------------------------------------------------------------------
00466 TInt CBookDb::GetABookFast(const TDesC& aTitle, TDes& aResult)
00467     {
00468     TInt err = KErrNone;
00469     TBuf<KDescriptionMaxLength> description; // Only 128 first characters read
00470     RDbTable rowset;
00471 
00472     TDbSeekKey seekKey(aTitle); // Initialize one-column seek key
00473 
00474     // Open view to "Books" table. Use index to browse the table.
00475     err = rowset.Open(iBookDb, KBooksTable, rowset.EReadOnly);
00476     if(err!=KErrNone)
00477     {
00478         return err;
00479     }
00480     err = rowset.SetIndex(KBooksIndexName);
00481     if(err!=KErrNone)
00482     {
00483         return err;
00484     }
00485     // Query colum numbers for author, title, and description
00486     CDbColSet* colSet=NULL;
00487     TRAP(err, colSet = rowset.ColSetL());
00488     if(err!=KErrNone)
00489     {
00490         return err;
00491     }
00492 
00493     TInt authorColumnNo = colSet->ColNo(KBooksAuthorCol);
00494     TInt titleColumnNo = colSet->ColNo(KBooksTitleCol);
00495     TInt descrColumnNo = colSet->ColNo(KBooksDescriptionCol);
00496 
00497     // Search the index for aTitle
00498     TBool isTitle = false;
00499     TRAP(err, isTitle = rowset.SeekL(seekKey));
00500     if(isTitle)
00501         {
00502         RDbColReadStream readStream;     // A stream object for long columns
00503         TRAPD(error, 
00504                 rowset.GetL();
00505                 readStream.OpenL(rowset,descrColumnNo);
00506                 readStream.ReadL(description, rowset.ColLength(descrColumnNo));
00507                 );
00508         if(error!=KErrNone)
00509         {
00510                 return error;
00511         }
00512 
00513         readStream.Close();
00514 
00515         aResult.Zero();
00516         aResult.Append(rowset.ColDes(authorColumnNo));
00517         aResult.Append(KSeparator);
00518         aResult.Append(rowset.ColDes(titleColumnNo));
00519         aResult.Append(KSeparator);
00520         aResult.Append(description);
00521 
00522         err = KErrNone;
00523         }
00524     else
00525         {
00526         err = KErrNotFound;
00527         }
00528 
00529     rowset.Close();
00530     return err;
00531     }
00532 
00533 // ---------------------------------------------------------------------------
00534 // CBookDb::GetBooksByKeyL()
00535 //
00536 // Get array of books from database according to column name and a search
00537 // pattern. Format of each array item is:
00538 //      <Author>|<Title>|<Description>
00539 // ---------------------------------------------------------------------------
00540 CDesCArrayFlat* CBookDb::GetBooksByKeyL(const TDesC& aColumnName,
00541     const TDesC& aSearchString)
00542     {
00543 
00544     TPtrC author, title;
00545     TBuf<KDescriptionMaxLength> description;
00546     TBuf<KBookItemMaxLength> rowText;
00547 
00548         _LIT(KSelect, "SELECT ");
00549         _LIT(KFrom, " FROM ");
00550         _LIT(KWhere, " WHERE ");
00551         _LIT(KLike, " LIKE '");
00552         _LIT(KOrderBy, "' ORDER BY ");
00553         _LIT(KDot, ", ");
00554 
00555     // Sql: SELECT Author, Title, Description FROM Books
00556     //      WHERE "aColumnName LIKE aSearchString"
00557     //      ORDER BY Title, Author
00558     TBuf<KCustomSqlMaxLength> sqlStr;
00559     sqlStr.Append(KSelect);
00560     sqlStr.Append(KBooksAuthorCol);
00561     sqlStr.Append(KDot);
00562     sqlStr.Append(KBooksTitleCol);
00563     sqlStr.Append(KDot);
00564     sqlStr.Append(KBooksDescriptionCol);
00565     sqlStr.Append(KFrom);
00566     sqlStr.Append(KBooksTable);
00567     sqlStr.Append(KWhere);
00568     sqlStr.Append(aColumnName);
00569     sqlStr.Append(KLike);
00570     sqlStr.Append(aSearchString);
00571     sqlStr.Append(KOrderBy);
00572     sqlStr.Append(KBooksTitleCol);
00573     sqlStr.Append(KDot);
00574     sqlStr.Append(KBooksAuthorCol);
00575 
00576     CDesCArrayFlat* resultArray =
00577         new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
00578     CleanupStack::PushL(resultArray);
00579 
00580     // Create a view on the database
00581     RDbView view;
00582     User::LeaveIfError(
00583         view.Prepare(iBookDb, TDbQuery(sqlStr), view.EReadOnly));
00584     User::LeaveIfError(view.EvaluateAll());
00585 
00586     CDbColSet* colSet = view.ColSetL();
00587     CleanupStack::PushL(colSet);
00588 
00589     // Append each result row to array
00590     for (view.FirstL(); view.AtRow(); view.NextL())
00591         {
00592 
00593         description.Zero();
00594         rowText.Zero();
00595 
00596         view.GetL();
00597 
00598         author.Set(view.ColDes(colSet->ColNo(KBooksAuthorCol)));
00599         title.Set(view.ColDes(colSet->ColNo(KBooksTitleCol)));
00600 
00601         TDbColNo descrColNo = colSet->ColNo(KBooksDescriptionCol);
00602         RDbColReadStream readStream;       // A stream object for long columns
00603         readStream.OpenLC(view, descrColNo);
00604         readStream.ReadL(description, view.ColLength(descrColNo));
00605         readStream.Close();
00606         CleanupStack::Pop(); //readStream
00607 
00608         rowText.Append(author);
00609         rowText.Append(KSeparator);
00610         rowText.Append(title);
00611         rowText.Append(KSeparator);
00612         rowText.Append(description);
00613 
00614         resultArray->AppendL(rowText);
00615         }
00616     CleanupStack::PopAndDestroy(colSet);
00617     view.Close();
00618     CleanupStack::Pop(resultArray);
00619 
00620     return resultArray;
00621 
00622     }
00623 
00624 // ---------------------------------------------------------------------------
00625 // CBookDb::RemoveBooks()
00626 //
00627 // Delete a book using title pattern and RDbUpdate (DML)
00628 // ---------------------------------------------------------------------------
00629 TInt CBookDb::RemoveBooks(const TDesC& aTitle, TInt& aResultCount)
00630     {
00631     RDbUpdate updOp;
00632 
00633         _LIT(KDeleteFrom, "DELETE FROM ");
00634         _LIT(KWhere, " WHERE ");
00635         _LIT(KLike, " LIKE '");
00636         _LIT(KDot, "'");
00637         
00638     // Sql: DELETE FROM Books WHERE Title LIKE 'aTitle'
00639     TBuf<KCustomSqlMaxLength> sqlStr;
00640     sqlStr.Append(KDeleteFrom);
00641     sqlStr.Append(KBooksTable);
00642     sqlStr.Append(KWhere);
00643     sqlStr.Append(KBooksTitleCol);
00644     sqlStr.Append(KLike);
00645     sqlStr.Append(aTitle);
00646     sqlStr.Append(KDot);
00647 
00648     // Initialize execution and perform the first step.
00649     // Note: Execute() returns 0 (=KErrNone), but it does not affect database
00650     //       until Next() is called.
00651     TInt incStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
00652     incStat = updOp.Next(); // This will leave, if Execute() failed.
00653 
00654     while( incStat == 1 ) // Just in case, if the operation has more steps
00655         {
00656         incStat = updOp.Next();
00657         }
00658     aResultCount = updOp.RowCount();
00659     updOp.Close();
00660     return incStat; // KErrNone or system wide error code
00661     }
00662 
00663 // ---------------------------------------------------------------------------
00664 // CBookDb::RemoveAllBooks()
00665 //
00666 // Delete books using asynchronous API. (RDbUpdate and DML)
00667 // This implementation is still synchronous, because it uses
00668 // User::WaitForRequest. Normally asynchronous functionality should be hidden
00669 // into active object and client callback interfaces.
00670 // ---------------------------------------------------------------------------
00671 TInt CBookDb::RemoveAllBooks(TInt& aResultCount)
00672     {
00673     _LIT(KDeleteFrom, "DELETE FROM ");
00674 
00675     // Sql: DELETE FROM Books
00676     TBuf<KCustomSqlMaxLength> sqlStr;
00677     sqlStr.Append(KDeleteFrom);
00678     sqlStr.Append(KBooksTable);
00679 
00680     RDbUpdate updOp;
00681     TRequestStatus incStat(1);
00682     TInt updStat = updOp.Execute(iBookDb, sqlStr, EDbCompareFolded);
00683     while (updStat==KErrNone && incStat ==1)
00684         {
00685         updOp.Next(incStat);           // Start async operation. It returns
00686                                        // immediately.
00687         User::WaitForRequest(incStat); // For simplicity wait completion here.
00688         }
00689 
00690     aResultCount = updOp.RowCount();
00691     updOp.Close();
00692 
00693     if(updStat!=KErrNone)
00694         return updStat;       // System wide error code
00695     else
00696         return incStat.Int(); // KErrNone or system wide error code
00697     }
00698 
00699 
00700 // ---------------------------------------------------------------------------
00701 // CBookDb::UpdateBookTitle()
00702 //
00703 // Update book title using SQL UPDATE.
00704 // ---------------------------------------------------------------------------
00705 //
00706 TInt CBookDb::UpdateBookTitle(const TDesC& aOldTitleKey,
00707     const TDesC& aNewTitle)
00708     {
00709     _LIT(KSQLUpdateStart, "UPDATE Books SET Title = '");
00710     _LIT(KSQLUpdateMiddle, "' WHERE Title = '");
00711     _LIT(KSQLUpdateEnd, "'");
00712 
00713     TBuf<KCustomSqlMaxLength> sqlStr;
00714     sqlStr.Append(KSQLUpdateStart);
00715     sqlStr.Append(aNewTitle);
00716     sqlStr.Append(KSQLUpdateMiddle);
00717     sqlStr.Append(aOldTitleKey);
00718     sqlStr.Append(KSQLUpdateEnd);
00719 
00720     return iBookDb.Execute(sqlStr);
00721     }
00722 
00723 
00724 // ---------------------------------------------------------------------------
00725 // CBookDb::ColumnNamesAndSizesL()
00726 //
00727 // Get array of column names and sizes of the Books table.
00728 // ---------------------------------------------------------------------------
00729 CDesCArrayFlat* CBookDb::ColumnNamesAndSizesL()
00730     {
00731     RDbTable booksTable;
00732     TBuf<32> columnNameAndSize;
00733     _LIT(KDelimiter, ": ");
00734     _LIT(KNoSize,"No size");
00735 
00736     // Open the Books table.
00737     TInt err = booksTable.Open(iBookDb, KBooksTable, booksTable.EReadOnly);
00738     User::LeaveIfError(err);
00739     
00740     CleanupClosePushL(booksTable);  // Remember to pop and close
00741 
00742     CDesCArrayFlat* resultArray =
00743         new (ELeave)CDesC16ArrayFlat(KArrayGranularity);
00744     CleanupStack::PushL(resultArray);
00745 
00746     // Iterate through the colums of Books table. Extract the column name and
00747     // column size (size only for text columns).
00748     // Note: Description column is long text. Database limits its size
00749     //       only by hardware. If size is queried, it is -1
00750     CDbColSet* colSet = booksTable.ColSetL();
00751     CleanupStack::PushL(colSet);
00752     TDbColSetIter colIter(*colSet);
00753     while(colIter)
00754         {
00755         columnNameAndSize.Zero();
00756         columnNameAndSize.Append(colIter->iName);
00757         columnNameAndSize.Append(KDelimiter);
00758         if(colIter->iType == EDbColText)
00759             columnNameAndSize.AppendNum(colIter->iMaxLength);
00760         else
00761             columnNameAndSize.Append(KNoSize);
00762         resultArray->AppendL(columnNameAndSize);
00763         colIter++;
00764         }
00765     CleanupStack::PopAndDestroy(colSet);
00766     CleanupStack::Pop(resultArray);
00767 
00768     // Pop the booksTable from cleanup stack and close it.
00769     CleanupStack::PopAndDestroy();
00770 
00771     return resultArray;
00772     }
00773 
00774 // ---------------------------------------------------------------------------
00775 // CBookDb::HasDateColumnL()
00776 //
00777 // Tests wheter the Books table has date column
00778 // ---------------------------------------------------------------------------
00779 TInt CBookDb::HasDateColumn(TBool& aReturnValue)
00780     {
00781     RDbTable booksTable;
00782     aReturnValue = EFalse;
00783 
00784     // Open the Books table.
00785     TInt err = booksTable.Open(iBookDb, KBooksTable, booksTable.EReadOnly);
00786     if(err!=KErrNone)
00787     {
00788         return err;
00789     }
00790 
00791     // Iterate through the colums of Books table. Check whether there is
00792     // a 'PublishDate' column
00793     CDbColSet* colSet=NULL;
00794     TRAP(err, colSet = booksTable.ColSetL());
00795     if(err!=KErrNone)
00796     {
00797         return err;
00798     }
00799 
00800     TDbColSetIter colIter(*colSet);
00801     while(colIter)
00802         {
00803         if( (colIter->iName).Compare(KBooksDateCol) == 0) // 0 = equal
00804             {
00805             aReturnValue = ETrue;
00806             break;
00807             }
00808         colIter++;
00809         }
00810 
00811     return KErrNone;
00812     }
00813 
00814 // ---------------------------------------------------------------------------
00815 // CBookDb::AddDateColumn()
00816 //
00817 // Adds date column to Books table (DDL).
00818 // ---------------------------------------------------------------------------
00819 TInt CBookDb::AddDateColumn()
00820     {
00821     _LIT(KSqlAddDate, "ALTER TABLE Books ADD PublishDate DATE");
00822     return iBookDb.Execute(KSqlAddDate);
00823     }
00824 
00825 // ---------------------------------------------------------------------------
00826 // CBookDb::RemoveDateColumn()
00827 //
00828 // Removes date column from Books table (DDL).
00829 // ---------------------------------------------------------------------------
00830 TInt CBookDb::RemoveDateColumn()
00831     {
00832     _LIT(KSqlRemoveDate, "ALTER TABLE Books DROP PublishDate");
00833     return iBookDb.Execute(KSqlRemoveDate);
00834     }
00835 

Generated by  doxygen 1.6.2