From Forum Nokia Wiki

ID	CS000925	Creation date	May 2, 2008
Platform	S60 3rd Edition, FP2	Tested on devices	Nokia 6220 Classic
Category	Symbian C++	Subcategory	Files/Data

Keywords (APIs, classes, methods, functions): RSqlDatabase, TSecurityPolicy, RSqlSecurityPolicy,RSqlDatabase::Create(), RSqlDatabase::Exec(), RSqlDatabase::Close(), RSqlSecurityPolicy::Create(), RSqlDatabase::Copy(),RSqlDatabase::Delete(), RSqlSecurityPolicy::SetDbPolicy(), RSqlSecurityPolicy::SetPolicy()

Overview

This code snippet shows how the SQL API's RSqlDatabase handle class allows the user to interact with the SQL server and create either a non-secure or secure database. Non-secure databases are databases that can be accessed and updated by any program. Secure databases have a defined static policy and can be accessed only by authorized clients with specific capabilities. To create a secure database, the UID of the application that creates the database and security policy are needed.

Another possibility to create a new database is to make a copy of an existing database with the method RSqlDatabase::Copy(). Databases can be deleted using the method RSqlDatabase::Delete().

This snippet can be self-signed.

MMP file

The following libraries are required:

LIBRARY  euser.lib
LIBRARY  sqldb.lib

The following capabilities are needed to test the secure database in this example:

CAPABILITY ReadUserData
CAPABILITY WriteUserData

Source file

#include <e32base.h>
#include <SqlDb.h>
 
void CreateDatabasesL()
  {
//== Create non-secure database ==
  RSqlDatabase database;
 
  _LIT(KNonSecureDbName, "\\nonsecure.db");
  TInt error = database.Create(KNonSecureDbName);
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
 
    //Create MOVIES table
    _LIT(KSqlCreateTableMovies, "CREATE TABLE MOVIES(ID INTEGER, TITLE TEXT, 
YEAR SMALLINT, DURATION SMALLINT, COLOR BOOLEAN)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMovies));
 
    _LIT(KSqlCreateTableMoviesIndex, "CREATE UNIQUE INDEX IDX ON MOVIES (ID)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMoviesIndex));
 
    //Insert data into MOVIES table
    _LIT(KSqlInsertIntoTableMovies, "INSERT INTO MOVIES(ID, TITLE, YEAR, 
DURATION, COLOR) VALUES(1, 'numberOne', 1999, 120, 'true')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableMovies));
 
    CleanupStack::PopAndDestroy(); //database
    }
  else
    {
    //creating database failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
//== Create secure database ==
 
  TSecurityPolicy defaultPolicy;
  RSqlSecurityPolicy securityPolicy;
 
  // Create security policies container object using a default security policy.
  securityPolicy.Create(defaultPolicy);
  CleanupClosePushL(securityPolicy);
 
  // Set up policy to apply to database schema and assign it
  TSecurityPolicy schemaPolicy(ECapabilityReadUserData, ECapabilityWriteUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy,
 schemaPolicy));
 
  // Set up policy to apply to write activity on the database and assign it
  TSecurityPolicy readPolicy(ECapabilityReadUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
 
  // Set up policy to apply to write activity to the database table named
  // "MOVIES" and assign it
  TSecurityPolicy tablePolicy(ECapabilityWriteUserData);
 
  _LIT(KMoviesTableName, "MOVIES");
  User::LeaveIfError(securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KMoviesTableName,
      RSqlSecurityPolicy::EWritePolicy, tablePolicy));
 
  // Create the database, passing the security policies
  _LIT(KSecureDbName, "[E80000AF]secure.db");  //Note: [UID3]
  error = database.Create(KSecureDbName, securityPolicy);
 
  //close RSqlSecurityPolicy object.
  CleanupStack::PopAndDestroy(); //securityPolicy
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
    //Create BOOKS table
    _LIT(KSqlCreateTableBooks, "CREATE TABLE BOOKS(ID INTEGER NOT NULL, 
TITLE TEXT, AUTHOR TEXT)");
    User::LeaveIfError(database.Exec(KSqlCreateTableBooks));
 
    //insert data
    _LIT(KSqlInsertIntoTableBooks, "INSERT INTO BOOKS(ID, TITLE, AUTHOR) 
VALUES(1, 'numberOne', 'author1')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableBooks));
 
    CleanupStack::PopAndDestroy(1); //database
    }
  else
    {
    //database creation failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
  }

Postconditions

Two databases, nonsecure.db and secure.db, have been created and the example rows are inserted into the databases.

nonsecure.db after creation:

TABLE:MOVIES
ID|TITLE    |YEAR|DURATION|COLOR
1 |numberOne|1999|120     |true

[UID3]secure.db after creation:

TABLE:BOOKS
ID|TITLE    |AUTHOR
1 |numberOne|author1

See also

• CS000926 - Using SQL API for attaching and detaching databases
• CS000927 - Using SQL API with SQL statements which do not return data
• CS000928 - Using SQL API with SQL statements which return data
• CS000929 - Using SQL API with scalar queries
• CS000930 - Using SQL API with data streams