This chapter includes the following sections:
SQLite is a relational database management system (RDBMS) specifically designed for embedded applications.
SQLite has the following characteristics:
It is ACID-compliant: like other traditional database systems, it has the properties of atomicity, consistency, isolation, and durability.
It is lightweight: the entire database consists of a small C library designed to be embedded directly within an application.
It is portable: the database is self-contained in a single file that is binary-compatible across a diverse range of computer architectures and operating systems
For more information, see the SQLite website at http://www.sqlite.org.
For sample usage of the local SQLite database, see the MAF sample application called CRUDDemo located in the PublicSamples.zip file within the jdev_install/jdeveloper/jdev/extensions/oracle.maf/Samples directory on your development computer. The CRUDDemo sample application uses a custom SQLite database file that is packaged within this application. The database file contains a table with records which include information on employees. When the application is activated, it reads data from the table and displays a list of employees. The information about the employees can be subject to CRUD operations: employees can be created, reordered, updated, and deleted through the user interface. All the CRUD operations are updated in the SQLite database.
If you plan to use the SQLite database to provide offline access and synchronization with a REST data service in your MAF application, we recommend that you use the MAF client data model. It provides wizards to facilitate the retrieval of data from REST services, to select what data to persist in the SQLite database when your MAF application is offline, and enables support for offline transaction plus synchronization when the MAF application returns online. It also provides an API (DBPersistenceManager) which exposes a variety of methods to interact with the SQLite database. See Creating the Client Data Model in a MAF Application and Accessing the SQLite Database Using the MAF Client Data Model DBPersistenceManager.
SQLite is designed for use as an embedded database system, one that is typically used by a single user, and often linked directly into the application. Enterprise databases, on the other hand, are designed for high concurrency in a distributed client-server environment.
Because of these differences, there are a number of limitations compared to Oracle databases. Some of the most important differences are:
For more information, see the following:
Documentation section of the SQLite website at http://www.sqlite.org/docs.html
"Limits In SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/limits.html
At any given time, a single instance of the SQLite database may have either a single read-write connection or multiple read-only connections. Its coarse-grained locking mechanism does not allow SQLite to support multiple read-write connections to the same database instance.
Due to its coarse-grained locking mechanism, SQLite does not support multiple read-write connections to the same database instance. See File Locking And Concurrency In SQLite Version 3 available from the Documentation section of the SQLite website at http://www.sqlite.org/lockingv3.html.
Despite SQLite complying with the SQL92 standard, it has a few unsupported constructs.
Although SQLite complies with the SQL92 standard, there are a few unsupported constructs, including the following:
RIGHT OUTER JOIN
FULL OUTER JOIN
GRANT
REVOKE
See "SQL Features That SQLite Does Not Implement" available from the Documentation section of the SQLite website at http://www.sqlite.org/omitted.html.
For information on how SQLite interprets SQL, see "SQL As Understood by SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/lang_createtable.html.
SQLite is dynamically typed and any value can be stored in any column, regardless of its declared type.
While most database systems are strongly typed, SQLite is dynamically typed and therefore any value can be stored in any column, regardless of its declared type. SQLite does not return an error if, for instance, a string value is mistakenly stored in a numeric column. See "Datatypes In SQLite Version 3" available from the Documentation section of the SQLite website at http://www.sqlite.org/datatype3.html.
SQLite supports foreign keys. It parses and enforces foreign key constraints. See the SQLite Foreign Key Support available from the Documentation section of the SQLite site at http://www.sqlite.org/foreignkeys.html.
SQLite is ACID-compliant and hence supports transactions but it neither supports nested transactions, nor does it allow a transaction to be rolled back until all open ResultSets have been closed. SQLite either permits multiple read-only connections or a single read-write connection to any given database.
Although SQLite is ACID-compliant and hence supports transactions, there are some fundamental differences between its transaction support and that of Oracle:
Nested transactions: SQLite does not support nested transactions. Only a single transaction may be active at any given time.
Commit: SQLite permits either multiple read-only connections or a single read-write connection to any given database. Therefore, if you have multiple connections to the same database, only the first connection that attempts to modify the database can succeed.
Rollback: SQLite does not permit a transaction to be rolled back until all open ResultSets have been closed first.
See "Distinctive Features of SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/different.html.
SQLite neither supports role-based nor user-based authentication. It allows users access to all the data in a file, and you can use MAF encryption to secure the data.
SQLite does not support any form of role-based or user-based authentication. By default, anyone can access all the data in the file. However, MAF provides encryption routines that you can use to secure the data, and prevent access by users without a valid set of credentials. See How to Encrypt and Decrypt the Database.
MAF contains an encrypted SQLite 3.8.5 database.
A typical SQLite usage requires you to know the following:
You can connect to the SQLite database using the java.sql.Connection object associated with the application. The SQLite JDBC URL must begin with the text jdbc:sqlite.
Connecting to the SQLite database differs from opening a connection to an Oracle database. Once you have acquired the initial connection, you can use most of the same JDBC APIs and SQL syntax to query and modify the database.
You use the java.sql.Connection object associated with your application to connect to the SQLite database. When creating the connection, ensure that every SQLite JDBC URL begins with the text jdbc:sqlite:.
The following example shows how to open a connection to an unencrypted database. Before obtaining the connection, load the JDBC driver.
public static Connection getConnection() throws Exception {
if (conn == null) {
try {
// create a database connection
String Dir = AdfmfJavaUtilities.getDirectoryPathRoot(
AdfmfJavaUtilities.ApplicationDirectory);
String connStr = "jdbc:sqlite:" + Dir + "/portfolio.db";
// Load the driver
Class.forName("SQLite.JDBCDriver");
conn = DriverManager.getConnection(connStr);
}
catch (SQLException e) {
// If the error message is "out of memory", it probably
// means that no database file is found
System.err.println(e.getClass().getName() + ": " + e.getMessage() );
e.printStackTrace();
}
}
return conn;
}
The following example shows how to open a connection to an encrypted database.
java.sql.Connection connection = new SQLite.JDBCDataSource(
"jdbc:sqlite:/path/to/database").getConnection(null,"password");
In the preceding example, the first parameter of the getConnection method is the user name, but since SQLite does not support user-based security, this value is ignored.
Note:
SQLite does not display any error messages if you open an encrypted database with an incorrect password. Likewise, you are not alerted if you mistakenly open an unencrypted database with a password. Instead, when you attempt to read or modify the data, an SQLException is thrown with the message "Error: file is encrypted or is not a database".
When the application starts, an SQL script initializes the database. The script must be added as a resource to the ApplicationController project of the MAF application.
An SQL script is used to initialize the database when the application starts. The following example shows the SQL initialization script that demonstrates some of the supported SQL syntax (described in SQL Support and Interpretation) through its use of the DROP TABLE, CREATE TABLE, and INSERT commands and the NUMBER and VARCHAR2 data types.
DROP TABLE IF EXISTS PERSONS; CREATE TABLE PERSONS ( PERSON_ID NUMBER(15) NOT NULL, FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), EMAIL VARCHAR2(25) NOT NULL ); INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 100, 'David', 'King', 'steven@king.net'); INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 101, 'Neena', 'Kochhar', 'neena@kochhar.net'); INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 102, 'Lex', 'De Haan', 'lex@dehaan.net'); INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 103, 'Alexander', 'Hunold', 'alexander@hunold.net'); INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 104, 'Bruce', 'Ernst', 'bruce@ernst.net');
To use the SQL script, add the script as a resource to the ApplicationController project of your MAF application. Assume that a sample script has been saved as initialize.sql in the META-INF directory. The following example shows the code that you must add to parse the SQL script and execute the statements.
private static void initializeDatabaseFromScript() throws Exception {
InputStream scriptStream = null;
Connection conn = null;
try {
// ApplicationDirectory returns the private read-write sandbox area
// of the mobile device's file system that this application can access.
// This is where the database is created
String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot
(AdfmfJavaUtilities.ApplicationDirectory);
String dbName = docRoot + "/sample.db";
// Verify whether or not the database exists.
// If it does, then it has already been initialized
// and no furher actions are required
File dbFile = new File(dbName);
if (dbFile.exists())
return;
// If the database does not exist, a new database is automatically
// created when the SQLite JDBC connection is created
conn = new SQLite.JDBCDataSource("jdbc:sqlite:" + docRoot +
"/sample.db").getConnection();
// To improve performance, the statements are executed
// one at a time in the context of a single transaction
conn.setAutoCommit(false);
// Since the SQL script has been packaged as a resource within
// the application, the getResourceAsStream method is used
scriptStream = Thread.currentThread().getContextClassLoader().
getResourceAsStream("META-INF/initialize.sql");
BufferedReader scriptReader = new BufferedReader
(new InputStreamReader(scriptStream));
String nextLine;
StringBuffer nextStatement = new StringBuffer();
// The while loop iterates over all the lines in the SQL script,
// assembling them into valid SQL statements and executing them as
// a terminating semicolon is encountered
Statement stmt = conn.createStatement();
while ((nextLine = scriptReader.readLine()) != null) {
// Skipping blank lines, comments, and COMMIT statements
if (nextLine.startsWith("REM") ||
nextLine.startsWith("COMMIT") ||
nextLine.length() < 1)
continue;
nextStatement.append(nextLine);
if (nextLine.endsWith(";")) {
stmt.execute(nextStatement.toString());
nextStatement = new StringBuffer();
}
}
conn.commit();
}
finally {
if (conn != null)
conn.close();
}
}
Note:
To keep the example simple, the preceding example omits error handling.
Invoke the database initialization code (see the preceding example) from the start method of the LifeCycleListenerImpl, as the following example shows.
public void start() {
try {
initializeDatabaseFromScript();
}
catch (Exception e) {
Trace.log(Utility.FrameworkLogger,
Level.SEVERE,
LifeCycleListenerImpl.class,
"start",
e);
}
}
A database can be initialized on the iOS, Android, Windows, Linux, and Mac platforms using the same database file. In case of complexities, initialize the database on a desktop using third-party tools, and package the response file as a resource in the application.
To use the database, add the database as a resource to the ApplicationController project of your MAF application. Assume that a database has been saved as sample.db in the META-INF directory. The following example shows the code that you must add to copy the database from your application to the mobile device's file system to enable access to the database.
private static void initializeDatabase() throws Exception {
InputStream sourceStream = null;
FileOutputStream destinationStream = null;
try {
// ApplicationDirectory returns the private read-write sandbox area
// of the mobile device's file system that this application can access.
// This is where the database is created
String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot
(AdfmfJavaUtilities.ApplicationDirectory);
String dbName = docRoot + "/sample.db";
// Verify whether or not the database exists.
// If it does, then it has already been initialized
// and no furher actions are required
File dbFile = new File(dbName);
if (dbFile.exists())
return;
// Since the database has been packaged as a resource within
// the application, the getResourceAsStream method is used
sourceStream = Thread.currentThread().getContextClassLoader().
getResourceAsStream("META-INF/sample.db");
destinationStream = new FileOutputStream(dbName);
byte[] buffer = new byte[1000];
int bytesRead;
while ((bytesRead = sourceStream.read(buffer)) != -1) {
destinationStream.write(buffer, 0, bytesRead);
}
}
finally {
if (sourceStream != null)
sourceStream.close();
if (destinationStream != null)
destinationStream.close();
}
}
Note:
To keep the example simple, the preceding example omits error handling.
Invoke the database initialization code (see the preceding example) from the start method of the LifeCycleListenerImpl, as the following example shows.
public void start() {
try {
initializeDatabase();
}
catch (Exception e) {
Trace.log(Utility.FrameworkLogger,
Level.SEVERE,
LifeCycleListenerImpl.class,
"start",
e);
}
}
The SQLite database provides an auto-commit functionality that commits statements as they are read from the SQL script.
Commit statements are ignored when encountered. Each statement is committed as it is read from the SQL script. This auto-commit functionality is provided by the SQLite database by default. To improve your application's performance, you can disable the auto-commit to allow a regular execution of commit statements by using the Connection's setAutoCommit(false) method.
The getByte method of the ResultSet and the execute method of Statement from the java.sql package have limited or no support in MAF.
The following methods from the java.sql package have limited or no support in MAF:
The getByte method of the ResultSet is not supported. If used, this method throws an SQLException when executed.
The execute method of the Statement always returns true (as opposed to returning true only for statements that return a ResultSet).
The size of the SQLite database remains constant even after records deletion leading to fragmentation and degraded performance. Use the VACUUM command to prevent performance degradation.
When records are deleted from an SQLite database, its size remains unchanged. This constant size leads to fragmentation and, ultimately, results in degraded performance. To prevent performance degradation, run the VACUUM command, periodically.
Note:
The VACUUM command uses long periods of time when run on large databases (approximately 0.5 seconds per megabyte on the Linux computer on which SQLite is developed). It can also use up to twice as much temporary disk space as the original file while it is running.
The VACUUM command must be run from a properly registered LifeCycleListener implementation (see Using Lifecycle Listeners in MAF Applications.).
You not only can encrypt the SQLite database using APIs, but can also specify a password for the encryption. Use the procedures to encrypt the database with your own password, decrypt the database encrypted with your own password, encrypt the database using the MAF-generated password, and to decrypt the database and delete the MAF-generated password
MAF allows you to provide the SQLite database with an initial or subsequent encryption through the use of various APIs. Some of these APIs enable you to specify your own password for encrypting the database. Others are used when you prefer MAF to generate and, optionally, manage the password.
Use the procedure to encrypt the database with your own password.
To encrypt the database with your own password:
Establish the database connection (see How to Connect to the Database).
Use the following utility method to encrypt the database with a new key:
AdfmfJavaUtilities.encryptDatabase(connection, "newPassword");
Use the procedure to decrypt the database encrypted with your own password.
To permanently decrypt the database encrypted with your own password:
Open the encrypted database with the correct password.
Use the following utility method:
AdfmfJavaUtilities.decryptDatabase(connection);
Caution:
If you open a database incorrectly (for example, use an invalid password to open an encrypted database), and then encrypt it again, neither the old correct password, the invalid password, nor the new password can unlock the database resulting in the irretrievable loss of data.
Use the procedure to encrypt the database using the MAF-generated password.
To encrypt the database using the MAF-generated password:
Generate a password using the following method:
GeneratedPassword.setPassword("databasePasswordID", "initialSeedValue");
This method requires both a unique identifier and an initial seed value to aid the cryptographic functions in generating a strong password.
Retrieve the created password using the previously-specified ID as follows:
char[] password = GeneratedPassword.getPassword("databasePasswordID");
Establish the database connection (see How to Connect to the Database).
Encrypt the database as follows:
AdfmfJavaUtilities.encryptDatabase(connection, new String(password));
Use the procedure to decrypt the database and delete the MAF-generated password.
To decrypt the database and delete the MAF-generated password:
Obtain the correct password as follows:
char[] password = GeneratedPassword.getPassword("databasePasswordID");
Establish the database connection and decrypt the database as follows:
java.sql.Connection connection =
SQLite.JDBCDataSource("jdbc:sqlite:/path/to/database").
getConnection(null, new String(password));
Optionally, delete the generated password using the following method:
GeneratedPassword.clearPassword("databasePasswordID");