/*
 * ============================================================================
 *  Name     : BookstoreDb from DBEngine.cpp
 *  Part of  : BookstoreDb
 *  Created  : 8.12.2003 by Forum Nokia
 *  Version  : 1.0
 *  Copyright: Nokia Corporation
 * ============================================================================
 */

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

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

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

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

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

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

// ---------------------------------------------------------------------------
// CBookstoreDb::OpenDbL()
//
// Open existing bookstore database for exclusive access.
// ---------------------------------------------------------------------------
TInt CBookstoreDb::OpenDbL(const TFileName& aExistingBookstoreFile)
    {
    Close();

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

    iFileStore = CPermanentFileStore::OpenL(iFsSession,
        aExistingBookstoreFile, EFileRead|EFileWrite);
    iFileStore->SetTypeL(iFileStore->Layout());       // Set file store type
    iBookstoreDb.OpenL(iFileStore,iFileStore->Root());
    iOpen = ETrue;
    return KErrNone;
    }

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

    // Create empty database file.
    iFileStore = CPermanentFileStore::ReplaceL(iFsSession,
        aNewBookstoreFile, EFileRead|EFileWrite);
    iFileStore->SetTypeL(iFileStore->Layout());       // Set file store type
    TStreamId id = iBookstoreDb.CreateL(iFileStore);  // Create stream object
    iFileStore->SetRootL(id);            // Keep database id as root of store
    iFileStore->CommitL();               // Complete creation by commiting

    // Create bookstore tables and indexes
    CreateBooksTableL();
    CreateBooksIndexL();
    iOpen = ETrue;
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::RemoveDbL()
//
// First remove the Books table. Then remove the database file.
// ---------------------------------------------------------------------------
TInt CBookstoreDb::RemoveDbL(const TFileName& aExistingBookstoreFile)
    {
    Close();

    if(!BaflUtils::FileExists(iFsSession, aExistingBookstoreFile))
        {
            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.

    OpenDbL(aExistingBookstoreFile);
    DropBooksTableL();
    Close();

    iFsSession.Delete(aExistingBookstoreFile);
    return KErrNone;
    }

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

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

// ---------------------------------------------------------------------------
// CBookstoreDb::CreateBooksTableL()
//
// Creates Books table. Leaves, if the table cannot be created.
// ---------------------------------------------------------------------------
void CBookstoreDb::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(iBookstoreDb.CreateTable(KBooksTable,
        *bookColSet));
    CleanupStack::PopAndDestroy(bookColSet);
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::CreateBooksIndexL()
//
// Creates an index for Books table. Leaves, if the index cannot be created.
// ---------------------------------------------------------------------------
void CBookstoreDb::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(iBookstoreDb.CreateIndex(
        KBooksIndexName, KBooksTable, *index));
    CleanupStack::PopAndDestroy(index);
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::DropBooksTableL()
//
// Drop the Books table incrementally. Uses RDbIncremental and DDL statement.
// ---------------------------------------------------------------------------
void CBookstoreDb::DropBooksTableL()
    {
    _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(iBookstoreDb, sqlStr, incStep);
    while (incStep>0 && incStat==KErrNone)
        {
        incStat = incOp.Next(incStep); // Do the work
        }
    incOp.Close();
    }

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

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

    // Sql: SELECT Author, Title, Description FROM Books 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(" ORDER BY "));
    sqlStr.Append(KBooksTitleCol);
    sqlStr.Append(_L(", "));
    sqlStr.Append(KBooksAuthorCol);

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

    view.InsertL();  // Insert a row. Column order matches sql select statement

    view.SetColL(1, aAuthor);
    view.SetColL(2, aTitle);
    RDbColWriteStream writeStream;  // Use stream to insert the description
    writeStream.OpenLC(view, 3);
    writeStream.WriteL(aDescription);
    CleanupStack::Pop();
    writeStream.Close();

    view.PutL();     // Complete insertion

    view.Close();
    return KErrNone;
    }

// ---------------------------------------------------------------------------
// CBookstoreDb::AddBookWithCppApiL()
//
// Add a book to database using RDbTable API
// ---------------------------------------------------------------------------
TInt CBookstoreDb::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(iBookstoreDb, KBooksTable, table.EUpdatable);
    User::LeaveIfError(err);

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

    table.Reset();
    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
    RDbColWriteStream writeStream;
    writeStream.OpenLC(table, booksColSet->ColNo(KBooksDescriptionCol));
    writeStream.WriteL(aDescription);
    writeStream.Close();
    CleanupStack::Pop();

    CleanupStack::PopAndDestroy(booksColSet);