// Copyright (c) 2005-2009 Nokia Corporation and/or its subsidiary(-ies).

// All rights reserved.

// This component and the accompanying materials are made available

// under the terms of the License "Symbian Foundation License v1.0" to Symbian Foundation members and "Symbian Foundation End User License Agreement v1.0" to non-members

// which accompanies this distribution, and is available

// at the URL "http://www.symbianfoundation.org/legal/licencesv10.html".

//

// Initial Contributors:

// Nokia Corporation - initial contribution.

//

// Contributors:

//

// Description:

// SQL Client side API header

// 

//

/**

 @file

 @publishedAll

 @released

*/

#ifndef __SQLDB_H__

#define __SQLDB_H__

#ifndef __S32STD_H__

#include <s32std.h>	//RReadStream, RWriteStream

#endif

//Forward declarations

class CSqlSecurityPolicy;

class RSqlDatabase;

class CSqlDatabaseImpl;

class RSqlStatement;

class CSqlStatementImpl;

class RSqlColumnReadStream;

class RSqlParamWriteStream;

class TSqlScalarFullSelectQuery;

/**

A container for the security policies for a shared SQL database.

The container can contain:

- security policies that apply to the database.

- security policies that apply to individual database objects, i.e. database tables.

For the database, you use RSqlSecurityPolicy::SetDbPolicy() to apply a separate

security policy to:

- the database schema.

- read activity on the database.

- write activity on the database.

For database tables, you use RSqlSecurityPolicy::SetPolicy() to apply a separate

security policy to:

- write activity on each named database table.

- read activity on each named database table.

A client uses a RSqlSecurityPolicy object to create a secure database. It does this by:

- creating a RSqlSecurityPolicy object.

- setting all the appropriate security policies into it.

- passing the object as an argument to RSqlDatabase::Create().

- closing the RSqlSecurityPolicy object on return from RSqlDatabase::Create().

Once a secure shared database has been created with specific security policies,

these policies are made persistent and cannot be changed during the life of

that database.

Security policies are encapsulated by TSecurityPolicy objects.

The general usage pattern is to create the security policies container object

(RSqlSecurityPolicy) using a default security policy (TSecurityPolicy), and then

to assign more specific 'overriding' security policies.

The following code fragment shows how you do this:

@code

TSecurityPolicy defaultPolicy;

RSqlSecurityPolicy securityPolicy;

RSqlDatabase database;

TInt err;

// Create security policies container object using a default security policy.

securityPolicy.Create(defaultPolicy); 

// Set up policy to apply to database schema

// and assign it

TSecurityPolicy schemaPolicy;

...

err = securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy);

// Set up policy to apply to write activity on the database

// and assign it

TSecurityPolicy writePolicy;

...

err = securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy);

// Set up policy to apply to write activity to the database table named "Table1"

// and assign it

TSecurityPolicy tablePolicy1;

...

err = securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, _L("Table1"), RSqlSecurityPolicy::EWritePolicy, tablePolicy1);

// Set up policy to apply to read activity to the database table named "Table2"

TSecurityPolicy tablePolicy2;

err = securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, _L("Table2"), RSqlSecurityPolicy::EReadPolicy, tablePolicy2);

// Create the database, passing the security policies

err = database.Create(KDatabaseName, securityPolicy);

// We can close the RSqlSecurityPolicy object.

securityPolicy.Close();

@endcode

Note that in this example code fragment, the client has not assigned specific

overriding policies for all possible cases; for example, no overriding policy

has been assigned to control read activity on the database, read activity

on "Table1", nor write activity on "Table2".

For these cases, the default security policy will apply.

A client can also retrieve a database's security policies by calling

RSqlDatabase::GetSecurityPolicy(); this returns a RSqlSecurityPolicy object

containing the security policies. Note that it is the client's responsibility

to close the RSqlSecurityPolicy object when the client no longer needs it. The

following code fragment suggests how you might do this:

@code

RSqlDatabase database;

RSqlSecurityPolicy securityPolicy;

// Retrieve the security policies; on return from the call to 

// GetSecurityPolicy(), the RSqlSecurityPolicy object passed 

// to this function will contain the security policies.

database.GetSecurityPolicy(securityPolicy);

...

// This is the security policy that applies to database schema

TSecurityPolicy schemaPolicy = securityPolicy.DbPolicy(RSqlSecurityPolicy::ESchemaPolicy);

...

// This is the security policy that applies to write activity to the database

// table named "Table1".

TSecurityPolicy writePolicy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, _L("Table1"), RSqlSecurityPolicy::EWritePolicy);

...

// Close the RSqlSecurityPolicy object when no longer needed.

securityPolicy.Close();

@endcode

Note that in the cases where an 'overriding' security policy was not originally assigned,

then the security policy returned will simply be the default security policy.

@see TSecurityPolicy

@see RSqlDatabase

@see RSqlSecurityPolicy::SetDbPolicy()

@see RSqlSecurityPolicy::SetPolicy()

@publishedAll

@released

*/

class RSqlSecurityPolicy

	{

	friend class RSqlDatabase;

public:

	/**

	Defines a set of values that represents the database security policy types.

	Each database security policy type refers to a set of capabilities encapsulated in 

	a TSecurityPolicy object. The TSecurityPolicy object defines what capabilities the calling

	application must have in order to perform partiqular database operation.

	@see TSecurityPolicy

	*/

	enum TPolicyType 

		{

		/**

		Schema database security policy. An application with schema database security policy can 

		modify the database schema, write to database, read from database.

		*/

		ESchemaPolicy, 

		/**

		Read database security policy. An application with read database security policy can 

		read from database.

		*/

		EReadPolicy, 

		/**

		Write database security policy. An application with write database security policy can 

		write to database.

		*/

		EWritePolicy

		};

	/**

	Not currently supported.

	Defines a set of values that represents the database objects which can be protected by 

	database security policy types.

	*/

	enum TObjectType 

		{

		ETable

		};

	IMPORT_C RSqlSecurityPolicy();

	IMPORT_C TInt Create(const TSecurityPolicy& aDefaultPolicy);

	IMPORT_C void CreateL(const TSecurityPolicy& aDefaultPolicy);

	IMPORT_C void Close();

	IMPORT_C TInt SetDbPolicy(TPolicyType aPolicyType, const TSecurityPolicy& aPolicy);

	IMPORT_C TInt SetPolicy(TObjectType aObjectType, const TDesC& aObjectName, TPolicyType aPolicyType, const TSecurityPolicy& aPolicy);

	IMPORT_C TSecurityPolicy DefaultPolicy() const;

	IMPORT_C TSecurityPolicy DbPolicy(TPolicyType aPolicyType) const;

	IMPORT_C TSecurityPolicy Policy(TObjectType aObjectType, const TDesC& aObjectName, TPolicyType aPolicyType) const;

	IMPORT_C void ExternalizeL(RWriteStream& aStream) const;

	IMPORT_C void InternalizeL(RReadStream& aStream);

private:	

	void Set(CSqlSecurityPolicy& aImpl);

	CSqlSecurityPolicy& Impl() const;

private:

	CSqlSecurityPolicy* iImpl;

	};

/**

A handle to a SQL database.

A RSqlDatabase object is, in effect, a handle to the SQL database. A client can:

- create a SQL database by calling RSqlDatabase::Create().

- open an existing SQL database by calling RSqlDatabase::Open().

- close a SQL database by calling RSqlDatabase::Close().

- copy a SQL database by calling RSqlDatabase::Copy().

- delete a SQL database by calling RSqlDatabase::Delete().

- attach a SQL database to current database connection by calling RSqlDatabase::Attach().

- detach a SQL database from current database connection by calling RSqlDatabase::Detach().

A client can create either a non-secure database or a secure database,

depending on the variant of RSqlDatabase::Create() that is used.

- a non-secure database is created if the RSqlDatabase::Create(const TDesC&) variant is used.

- a secure database is created if the RSqlDatabase::Create(const TDesC&, const RSqlSecurityPolicy&)

variant is used. In this case, a container containing a collection of security

policies needs to be set up first and passed to this Create() function.

See references to RSqlSecurityPolicy for more information on security policies.

A client can also specify how it wants a transaction to interact with

other transactions that may be running concurrently. The various ways in which

transactions can interact (i.e. how one transaction can affect another) are

referred to as "transaction isolation levels", and are defined by the values

of the TIsolationLevel enum. A client specifies this by calling RSqlDatabase::SetIsolationLevel().

Each of the various flavours of Open and Create allows the optional provision of a

configuration string. It is acceptable for this string to be missing.

In the case where the string is missing, the config in the SqlServer.sql file

will be used. If that does not exist then the MMH macro definitions will be used.

The config string is in the format PARAM=VALUE; PARAM=VALUE;...

Allowed parameters are:

	cache_size=nnnn

	page_size=nnnn

	encoding=UTF8|UTF16

Badly formed config strings are reported as KErrArgument

The string may not exceed 255 characters.

Please note that a database can only be accessed within the thread where it has been created. It is then not possible

to create a database from thread1 and access it from thread2.

A client calls RSqlDatabase::Exec() to execute SQL statements.

@see RSqlDatabase::Create()

@see RSqlDatabase::Open()

@see RSqlDatabase::Close()

@see RSqlDatabase::Copy()

@see RSqlDatabase::Delete()

@see RSqlDatabase::Attach()

@see RSqlDatabase::Detach()

@see RSqlDatabase::SetIsolationLevel()

@see RSqlDatabase::Exec()

@see TIsolationLevel

@see RSqlSecurityPolicy

@publishedAll

@released

*/

class RSqlDatabase

	{

	friend class RSqlStatement;

	friend class TSqlScalarFullSelectQuery;

public:

	/**

	Defines a set of values that represents the transaction isolation level.

	A transaction isolation level defines the way in which a transaction

	interacts with other transactions that may be in progress concurrently.

	A client sets the transaction isolation level by calling SetIsolationLevel()

	@see RSqlDatabase::SetIsolationLevel()

	*/

	enum TIsolationLevel 

		{

		/**

		A transaction can read uncommitted data, i.e. data that is being changed

		by another transaction, which is still in progress.

		This means that

		- a 'database read' transaction will not block 'database write' transactions

		being performed by different database connections on the same shared database.

		- a 'database read' transaction will not be blocked by 'database write'

		transactions performed by the same database connection.

		- concurrent 'database write' transactions are prevented.

		This transaction isolation level can be set at any time during

		the lifetime of the database.

		@see TIsolationLevel

		@see RSqlDatabase::SetIsolationLevel()

		*/

		EReadUncommitted, 

		/**

		Not currently supported.

		A transaction cannot read uncommitted data. "Dirty reads" are prevented.

		"Dirty read" is a data inconsistency type which can be described with the following example:

		- Transaction A updates TableA.Column1 value from 1 to 2;

		- Transaction B reads TableA.Column1 value;

		- Transaction A rolls back and restores the original value of TableA.Column1 (1);

		- Transaction B ends showing that TableA.Column1 value is 2, even though, logically and transactionally, 

		  this data never really even existed in the database because Transaction A never committed that change 

		  to the database;

		@see TIsolationLevel

		@see RSqlDatabase::SetIsolationLevel()

		*/

		EReadCommitted, 

		/**

		Not currently supported.

		A transaction cannot change data that is being read by a different transaction. 

		"Dirty reads" and "non-repeatable reads" are prevented.

		"Non-repeatable reads" is a data inconsistency type which can be described with the following example:

		- Transaction A reads TableA.Column1 value which is 1;

		- Transaction B updates TableA.Column1 value from 1 to 2;

		- Transaction B commits the chages;

		- Transaction A reads TableA.Column1 value again. Transaction A has inconsistent data because TableA.Column1 

		  value now is 2 instead of 1, all within the scope of the same Transaction A;

		@see TIsolationLevel

		@see RSqlDatabase::SetIsolationLevel()

		*/

		ERepeatableRead, 

		/**

		Any number of 'database read' transactions can be performed concurrently

		by different database connections on the same shared database.

		Only one 'database write' transaction can be performed at any one time. If a

		'database write' transaction is in progress, then any attempt to start

		another 'database read' or 'database write' transaction will be blocked

		until the first 'database write' transaction has completed.

		This is the default isolation level, if no isolation level is

		explicitly set.

		"Dirty reads", "non-repeatable" reads and "phantom reads" are prevented.

		"Phantom reads" is a data inconsistency type which can be described with the following example:

		- Transaction A reads all rows that have Column1 = 1;

		- Transaction B inserts a new row which has Column1 = 1;

		- Transaction B commits;

		- Transaction A updates all rows that have Column1 = 1. This will also update the row that 

		  Transaction B inserted, because Transaction A must read the data again in order to update it.

		- Transaction A commits;

		@see TIsolationLevel

		@see RSqlDatabase::SetIsolationLevel()

		*/

		ESerializable

		};

	IMPORT_C RSqlDatabase();

	IMPORT_C TInt Create(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

	IMPORT_C TInt Create(const TDesC& aDbFileName,

			const RSqlSecurityPolicy& aSecurityPolicy, const TDesC8* aConfig=NULL);

	IMPORT_C TInt Open(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

	IMPORT_C void CreateL(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

	IMPORT_C void CreateL(const TDesC& aDbFileName,

			const RSqlSecurityPolicy& aSecurityPolicy, const TDesC8* aConfig=NULL);

	IMPORT_C void OpenL(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

	IMPORT_C void Close();

	IMPORT_C TInt Attach(const TDesC& aDbFileName, const TDesC& aDbName);

	IMPORT_C TInt Detach(const TDesC& aDbName);

	IMPORT_C static TInt Copy(const TDesC& aSrcDbFileName, const TDesC& aDestDbFileName);

	IMPORT_C static TInt Delete(const TDesC& aDbFileName);

	IMPORT_C TInt GetSecurityPolicy(RSqlSecurityPolicy& aSecurityPolicy) const;

	IMPORT_C void GetSecurityPolicyL(RSqlSecurityPolicy& aSecurityPolicy) const;

	IMPORT_C TInt SetIsolationLevel(TIsolationLevel aIsolationLevel);

	IMPORT_C TInt Exec(const TDesC& aSqlStmt);

	IMPORT_C TInt Exec(const TDesC8& aSqlStmt);

	IMPORT_C void Exec(const TDesC& aSqlStmt, TRequestStatus& aStatus);

	IMPORT_C void Exec(const TDesC8& aSqlStmt, TRequestStatus& aStatus);

	IMPORT_C TPtrC LastErrorMessage() const;

	IMPORT_C TBool InTransaction() const;

	IMPORT_C TInt Size() const;

	IMPORT_C TInt ReserveDriveSpace(TInt aSize);

	IMPORT_C void FreeReservedSpace();

	IMPORT_C TInt GetReserveAccess();

	IMPORT_C void ReleaseReserveAccess();

private:

	CSqlDatabaseImpl& Impl() const;

private:

	CSqlDatabaseImpl* iImpl;

	};

/**

TSqlScalarFullSelectQuery interface is used for executing SELECT sql queries, which 

return a single row consisting of a single column value.

Examples.

CASE 1 - retrieving records count of a table:

@code

RSqlDatabase db;

//initialize db object....

.......

TSqlScalarFullSelectQuery fullSelectQuery(db);

TInt recCnt = fullSelectQuery.SelectIntL(_L("SELECT COUNT(*) FROM PersonTbl"));

@endcode

CASE 2 - retrieving specific column value using a condition in the SELECT statement:

@code

RSqlDatabase db;

//initialize db object....

.......

TSqlScalarFullSelectQuery fullSelectQuery(db);

TInt personId = fullSelectQuery.SelectIntL(_L("SELECT ID FROM PersonTbl WHERE Name = 'John'"));

@endcode

CASE 3 - retrieving a text column value, the receiving buffer is not big enough:

@code

RSqlDatabase db;

//initialize db object....

.......

TSqlScalarFullSelectQuery fullSelectQuery(db);

HBufC* buf = HBufC::NewLC(20);

TPtr name = buf->Des();

TInt rc = fullSelectQuery.SelectTextL(_L("SELECT Name FROM PersonTbl WHERE Id = 1"), name);

TEST(rc >= 0); //the function may return only non-negative values

if(rc > 0)

	{

	buf = buf->ReAllocL(rc);

	CleanupStack::Pop();	

	CleanupStack::PushL(buf);

	name.Set(buf->Des());

	rc = fullSelectQuery.SelectTextL(_L("SELECT Name FROM PersonTbl WHERE Id = 1"), name);

	TEST(rc == 0);

	}

CleanupStack::PopAndDestroy();//buf

@endcode

@see RSqlDatabase

@publishedAll

@released

*/

class TSqlScalarFullSelectQuery

	{

public:

	IMPORT_C TSqlScalarFullSelectQuery();

	IMPORT_C TSqlScalarFullSelectQuery(RSqlDatabase& aDatabase);

	IMPORT_C void SetDatabase(RSqlDatabase& aDatabase);

	IMPORT_C TInt SelectIntL(const TDesC& aSqlStmt);

	IMPORT_C TInt64 SelectInt64L(const TDesC& aSqlStmt);

	IMPORT_C TReal SelectRealL(const TDesC& aSqlStmt);

	IMPORT_C TInt SelectTextL(const TDesC& aSqlStmt, TDes& aDest);

	IMPORT_C TInt SelectBinaryL(const TDesC& aSqlStmt, TDes8& aDest);

	IMPORT_C TInt SelectIntL(const TDesC8& aSqlStmt);

	IMPORT_C TInt64 SelectInt64L(const TDesC8& aSqlStmt);

	IMPORT_C TReal SelectRealL(const TDesC8& aSqlStmt);

	IMPORT_C TInt SelectTextL(const TDesC8& aSqlStmt, TDes& aDest);