CORAL Component documentation: SQLiteAccess

Zhen Xie

Princeton University

October 2005

1. Introduction

1.1. Purpose of the component

The SQLiteAccess package is a plugin library providing an implementation of the IRelatioalDomain interface for accessing a filebased SQLite database. The package is based on the sqlite3 API.

1.2. Repository of the component

2. SQLiteAccess Semantics

2.1. The logical data hiererchy

In order to connect to a SQLite database file using CORAL one should specify a connection string which typically has a format such as

In the case the contact string has the format: sqlite_file:my.db

-if SQLITE_FILE_PATH variable is set, SQLiteAccess read/write my.db in this dir, exception if the dir does not exist;

-if SQLITE_FILE_PATH variable is not set, SQLiteAccess read/write my.db in the currentdir

In the case the contact string has the absolute path format:

the SQLITE_FILE_PATH variable is not taken into consideration

There is zero configuration required to read/write data into sqlite files.

The schema name is fixed as "main".

sqlite database doesnot have its own SQL types. SQLiteAccess defines the following SQL to c++ type mapping: BOOLEAN(bool), CHAR(char), UNSIGNEDCHAR(unsigned char), SHORT(short), UNSIGNEDSHORT(unsigned short), INT(int), UNSIGNEDINT(unsigned int), LONG(long), UNSIGNEDLONG(unsigned long), ULONGLONG(unsigned long long), SLONGLONG(long long), FLOAT(float), DOUBLE(double), LONGDOUBLE(long double), TEXT(std::string), BLOB(coral::blob), DATE(coral::date), TIMESTAMP(coral::timestamp)

Note: if the SQL type in a sqlite database is not found in this list, SQLiteAccess will NOT recognise it.

3. Implementation specifics

The package is based on SQLite version 3. The implementation uses SQLite 3 C API. The format used by SQLite database files has been completely from version 2 to version 3 therefore SQLiteAccess plugin will not read a SQLite version 2 database file. The shared library is linked against library sqlite3

Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. No user name and password are required to start a user session in sqlite domain. Also the PrivilegeManager implementation is completely dummy which means GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented.

VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. Different from the oracle backend, the constraints on the columns of the source table of the view are NOT propagated to the view.

FOREIGN KEY constraints are parsed and created but are not really enforced by the backend.

The current SQLite backend implementation only allows a single active transaction. Nested transactions are not supported.

There is no tablespace management in SQLiteAccess therefore tablespace arguments are ignored.

Only one schema with fixed name "main" is handled in one SQLiteAccess session.

4. Related components

  • RelationalAccess, is the package where the CORAL abstract interfaces are defined.

  • CoralCommon, is the package where some developer-level interfaces and some common implementation classes are defined.

5. SQLiteAccess Reference

5.1. Properties of the "CORAL/RelationalPlugins/sqlite" component

There's no specific parameters to be set for the sqlite domain.