examples/SFExamples/Files/StoreDatabase/src/StoreDatabaseAppUi.cpp

/*
 * Copyright (c) 2002-2011 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:  
 */  

// INCLUDES
#include <f32file.h>
#include <s32file.h>
#include <d32dbms.h>
#include <bautils.h>
#include <eikmenub.h>

#include <StoreDatabase.rsg>

#include "StoreDatabaseAppUi.h"
#include "StoreDatabaseMainView.h"
#include "employee.h"
#include "StoreDatabase.hrh"

// CONSTANTS
_LIT(KFileName,      "c:\\data\\employee.db");

_LIT(KEmployeesTable, "EmployeesTable");
_LIT(KEmployeesIndex, "EmployeesIndex");

_LIT(KIdentifier,    "Identifier");
_LIT(KName,          "Name");
_LIT(KPhoneNumber,   "PhoneNumber");

const TInt KIdentifierIdx  = 1;
const TInt KNameIdx        = 2;
const TInt KPhoneNumberIdx = 3;

const TInt KMaxBuffer      = 100;

_LIT(KName1,        "John Doe");
_LIT(KPhoneNumber1, "+1-222-333-4444");

_LIT(KName2,        "Jane Roe");
_LIT(KPhoneNumber2, "+1-234-567-8900");

const TChar KEndOfLine = '\f';


// MEMBER FUNCTIONS

// --------------------------------------------------------------------------
// Constructor
// --------------------------------------------------------------------------
void CStoreDatabaseAppUi::ConstructL()
        {


        BaseConstructL(EAknEnableSkin);
        
        iMainView = CStoreDatabaseMainView::NewL(ClientRect());
        iStatus = EStatusUninitialized;
        BaflUtils::EnsurePathExistsL(iCoeEnv->FsSession(), KFileName);
        }
        
// --------------------------------------------------------------------------
// Destructor
// --------------------------------------------------------------------------
CStoreDatabaseAppUi::~CStoreDatabaseAppUi()
    {
    if (iMainView)
        {
        delete iMainView;
        iMainView = NULL;
        }
    
    iDatabase.Close();
    delete iFileStore;
    iArray.ResetAndDestroy();
    }

// --------------------------------------------------------------------------
// Handles user command.
// --------------------------------------------------------------------------
void CStoreDatabaseAppUi::HandleCommandL(TInt aCommand)
        {
        switch ( aCommand )
                {
                // For S60, we need to handle this event, which is normally
                // an event from the right soft key.
                case EAknSoftkeyExit:
                case EEikCmdExit:
                        {
                        Exit();
                        break;
                        }
                
                case EStoreDatabaseCreateDb:
                        {
                        DoCreateDbL();                  
                        break;
                        }
                        
                case EStoreDatabaseAddDataToDb:
                        {
                        DoAddDataToDbL();       
                        break;
                        }
                        
                case EStoreDatabaseReadDataFromDb:
                        {
                        DoReadDataFromDbL();
                        break;
                        }
                        
                case EStoreDatabaseDeleteDataFromDb:
                        {
                        // Delete all data from the database.
                        DoDeleteDataFromDbL();
                        break;
                        }
                
                default:
                        // Do nothing
                        break;
                }
        }



// --------------------------------------------------------------------------
// Handles screen resolution/size changes.
// --------------------------------------------------------------------------
void CStoreDatabaseAppUi::HandleResourceChangeL(TInt aType)
        {
        CAknAppUi::HandleResourceChangeL(aType);
        iMainView->SetRect(ClientRect());
        }

void CStoreDatabaseAppUi::DoCreateDbL()
        {
        CreateDatabaseL(iCoeEnv->FsSession(), KFileName);
        CreateTableL(iDatabase);
        CreateIndexL(iDatabase);
        iStatus = EStatusCreated;
        
        // Display message on the main view.
        HBufC* message = iCoeEnv->AllocReadResourceLC(
                        R_STOREDATABASE_DBCREATED);
        iMainView->SetTextL(*message);
        CleanupStack::PopAndDestroy(message);
        }

void CStoreDatabaseAppUi::DoAddDataToDbL()
        {
        // Display a warning when the database has not been created.
        if (EStatusUninitialized == iStatus)
                {
                iEikonEnv->InfoWinL(R_STOREDATABASE_CAPTION, 
                                R_STOREDATABASE_UNINITIALIZED);
                return;
                }
        
        // If the data has been added, then just return.
        if (EStatusAdded == iStatus)
                {
                return;
                }
        
        // Create a new instance of CEmployee.
        CEmployee* employee = new (ELeave) CEmployee();
        CleanupStack::PushL(employee);
        
        // Add the first employee.
        employee->SetName(KName1);
        employee->SetPhoneNumber(KPhoneNumber1);
        AddEmployeeToDatabaseUsingSqlL(iDatabase, *employee);
        
        //
        // Alternatively, we can add using this statement.
        // The statement below uses RDbView to add to the database,
        // instead of using SQL statement.
        //
        // -> AddEmployeeToDatabaseL(iDatabase, *employee);
        
        // Add the second employee.
        employee->SetName(KName2);
        employee->SetPhoneNumber(KPhoneNumber2);
        AddEmployeeToDatabaseUsingSqlL(iDatabase, *employee);
        
        CleanupStack::PopAndDestroy(employee);
        iStatus = EStatusAdded;
        
        // Display message on the main view.
        HBufC* message = iCoeEnv->AllocReadResourceLC(
                        R_STOREDATABASE_DBADDED);
        iMainView->SetTextL(*message);
        CleanupStack::PopAndDestroy(message);
        }

void CStoreDatabaseAppUi::DoReadDataFromDbL()
        {
        // Display a warning if the database has not been created yet.
        if (iStatus == EStatusUninitialized)
                {
                iEikonEnv->InfoWinL(R_STOREDATABASE_CAPTION,
                                R_STOREDATABASE_UNINITIALIZED);
                return;
                }
        
        // Reset the array and read all employees from the database.
        iArray.ResetAndDestroy();
        ReadEmployeesFromDatabaseL(iDatabase);

        // Display all the employees on the main view.
        RBuf buffer;
        buffer.CreateL(KMaxBuffer * iArray.Count());
        CleanupClosePushL(buffer);
        for (TInt i = 0; i < iArray.Count(); i++)
                {
                const CEmployee* employee = iArray[i];
                buffer.AppendNum(employee->Identifier());
                buffer.Append(KEndOfLine);
                buffer.Append(employee->Name());
                buffer.Append(KEndOfLine);
                buffer.Append(employee->PhoneNumber());
                buffer.Append(KEndOfLine);
                buffer.Append(KEndOfLine);
                }
        iMainView->SetTextL(buffer);
        
        CleanupStack::PopAndDestroy(&buffer);
        }

void CStoreDatabaseAppUi::DoDeleteDataFromDbL()
        {
        // Display a warning if the database has not been created yet.
        if (iStatus == EStatusUninitialized)
                {
                iEikonEnv->InfoWinL(R_STOREDATABASE_CAPTION,
                                R_STOREDATABASE_UNINITIALIZED);
                return;
                }
        
        //
        // Delete all employees from the database.
        //
        DeleteEmployeeFromDatabaseL(iDatabase, KName1);
        DeleteEmployeeFromDatabaseL(iDatabase, KName2);
        iStatus = EStatusCreated;
                
        // Display message on the main view.
        HBufC* message = iCoeEnv->AllocReadResourceLC(
                        R_STOREDATABASE_DBDELETED);
        iMainView->SetTextL(*message);
        CleanupStack::PopAndDestroy(message);
        }

void CStoreDatabaseAppUi::CreateDatabaseL(RFs& aFs, const TDesC& aFileName)
        {
        // Create a permanent file store for the database.
        delete iFileStore;
        iFileStore = 0;
        iFileStore = CPermanentFileStore::ReplaceL(
                        aFs, aFileName, EFileRead | EFileWrite);
        iFileStore->SetTypeL(iFileStore->Layout());
        
        // Create a database in the store.
        iDatabase.Close();
        TStreamId id = iDatabase.CreateL(iFileStore);
        
        // Set the root for this store and then commit the change.
        iFileStore->SetRootL(id);
        iFileStore->CommitL();
        }

void CStoreDatabaseAppUi::CreateTableL(RDbDatabase& aDatabase)
        {
        CDbColSet* columns = CDbColSet::NewLC();
        
        // The first column is an identifier, which is auto-increment.
        TDbCol identifier(KIdentifier, EDbColUint32);
        identifier.iAttributes = TDbCol::EAutoIncrement;
        columns->AddL(identifier);
        
        // The second column is name.
        TDbCol name(KName, EDbColText, KMaxEmployeeName);
        name.iAttributes = TDbCol::ENotNull;
        columns->AddL(name);
        
        // The third column is phone number.
        columns->AddL(TDbCol(KPhoneNumber, EDbColText, KMaxEmployeeNumber));

        // Create a new table based on the columns definition above.
        User::LeaveIfError(aDatabase.CreateTable(KEmployeesTable, *columns));
        
        CleanupStack::PopAndDestroy(columns);
        }

void CStoreDatabaseAppUi::CreateIndexL(RDbDatabase& aDatabase)
        {
        // Define a key that uses the name and identifier.
        CDbKey* key = CDbKey::NewLC();
        key->AddL(TDbKeyCol(KName));
        key->AddL(TDbKeyCol(KIdentifier));
        
        // Create an index on the database.
        User::LeaveIfError(aDatabase.CreateIndex(KEmployeesIndex, KEmployeesTable, *key));
        
        CleanupStack::PopAndDestroy(key);
        }

void CStoreDatabaseAppUi::AddEmployeeToDatabaseL(RDbDatabase& aDatabase,
                const CEmployee& aEmployee)
        {
        // The SQL statement to select all employees.
        _LIT(KSqlStatement, "SELECT * FROM %S");
        TBuf<KMaxBuffer> sqlStatement;
        sqlStatement.AppendFormat(KSqlStatement, &KEmployeesTable);
        
        // Create a view based on the SQL statement above.
        RDbView view;
        User::LeaveIfError(view.Prepare(
                        aDatabase, TDbQuery(sqlStatement, EDbCompareFolded)));
        CleanupClosePushL(view);
        User::LeaveIfError(view.EvaluateAll());
        
        // Insert a new row.
        view.InsertL();
        view.SetColL(KNameIdx, aEmployee.Name());
        view.SetColL(KPhoneNumberIdx, aEmployee.PhoneNumber());
        view.PutL();
        
        // Free all the resources.
        CleanupStack::PopAndDestroy(&view);
        }

void CStoreDatabaseAppUi::AddEmployeeToDatabaseUsingSqlL(RDbDatabase& aDatabase,
                const CEmployee& aEmployee)
        {
        // The SQL statement to insert an employee.
        _LIT(KSqlStatement, "INSERT INTO %S (%S, %S) VALUES ('%S', '%S')");
        
        TBuf<KMaxBuffer> sqlStatement;
        TPtrC name = aEmployee.Name();
        TPtrC phoneNumber = aEmployee.PhoneNumber();
        sqlStatement.AppendFormat(KSqlStatement, &KEmployeesTable,
                        &KName, &KPhoneNumber, &name, &phoneNumber);
        
        // Execute the SQL statement.
        User::LeaveIfError(aDatabase.Execute(sqlStatement, EDbCompareFolded));
        }

void CStoreDatabaseAppUi::ReadEmployeesFromDatabaseL(RDbDatabase& aDatabase)
        {
        // Prepare and evaluate SQL statement using RDbView.
        _LIT(KSqlStatement, "SELECT * FROM %S ORDER BY %S");
        TBuf<KMaxBuffer> sqlStatement;
        sqlStatement.AppendFormat(KSqlStatement, &KEmployeesTable,
                        &KName);
        
        RDbView view;
        User::LeaveIfError(view.Prepare(
                        aDatabase, TDbQuery(sqlStatement, EDbCompareFolded)));
        CleanupClosePushL(view);
        User::LeaveIfError(view.EvaluateAll());
        
        // Iterate to RDbView from the first row to the last one.
        view.FirstL();
        while (view.AtRow())
                {
                view.GetL();
                TUint32 identifier = view.ColUint32(KIdentifierIdx);
                TPtrC name = view.ColDes(KNameIdx);
                TPtrC phoneNumber = view.ColDes(KPhoneNumberIdx);
                view.NextL();

                // Do something with the data here.
                // In this example, we add the data to the array.
                CEmployee* employee = new (ELeave) CEmployee();
                employee->SetIdentifier(identifier);
                employee->SetName(name);
                employee->SetPhoneNumber(phoneNumber);
                iArray.Append(employee);
                }
                
        CleanupStack::PopAndDestroy(&view);
        }

void CStoreDatabaseAppUi::DeleteEmployeeFromDatabaseL(RDbDatabase& aDatabase,
                const TDesC& aName)
        {
        // Prepare and evaluate SQL statement using RDbView.
        _LIT(KSqlStatement, "DELETE FROM %S WHERE %S='%S'");
        
        TBuf<KMaxBuffer> sqlStatement;
        sqlStatement.AppendFormat(KSqlStatement, &KEmployeesTable,
                        &KName, &aName);
        
        // Execute the SQL statement.
        User::LeaveIfError(aDatabase.Execute(sqlStatement, EDbCompareFolded));
        }

// End of File

---