SQLite

1. SQLite Using SQLite Supported by BlackBerry OS 5.0

2. Overview • SQLite Library • SQLite Database • Database Security Options • A Statement Life Cycle • Transactions • Remove Databases • Vacuum Command • Best Parctice

3. SQLite Library • It is a relational database library. • Has a small footprint. Hence, it is good for small devices. • BlackBerry devices that run BlackBerry Device Software version 5.0 or later supports SQLite library. • SQLite library has been introduced in BlackBerry Java Development Environment Version 5 and later.

4. SQLite Library (Cont.) • The package that provides classes and interfaces to work with SQLite is “net.rim.device.api.database”. • DB locations can be : • SD Card: /SDCard/ • Device memory: /store/home/user • System memory: /system/ • Default location: /SDCard/databases/<application_name>

5. SQLite Database • SQLite database is stored in a single file on a SD Card. • The default location for a SQLite database is /SDCard/databases/<application_name> • You can store the database file in the device memory by specifying the file system path • It can be encrypted or plain text.

6. How to Create a Plain Database • Import net.rim.device.api.database that include classes to work with SQLite. • Create a URI representing the database file • dbURI = URI.create(“file:///SDCard/Databases/myApplication/”+ “MyDatabase.db”); • Invoke the Create() method of the DatabaseFactory class to create a new database • Database d = DatabaseFactory.create(dbURI);

7. How to Create a Plain Database • -or – • Invoke openorCreate() to create a new database or open an existing one. • Database db = DatabaseFactory.openOrCreate(uri); • You can also create an encrypted database by specifying security options in Create() or openOrCreate() method.

8. How to Create a Plain Database (Cont.) • DatabaseFactory: • Creates new or open existing instances of a device database • Databases can be created temporary (in-memory) or persistent depending on the URI path • URI path can be one of the DB locations presented in the previous slides. • Example: create a database in device memory: • URI path: /store/home/user • Create a database in system memory: • URI path: /system/

9. Example- Open or Create a Database public void createPlainDatabaseFile() throws Exception { // The database is created in SD card String dbLocation = "/SDCard/databases/myApplication/"; //if you want to create the database in flash memory use the following URI // dbLocation = "/store/home/user/"; //Create a URI path file for the database URI uri = URI.create(dbLocation + “myDB.db”); // Invoke the create() or openOrCreate() method Database db = DatabaseFactory.openOrCreate(uri, new DatabaseSecurityOptions(false)) }

10. Encrypted Databases • A database can be encrypted by specifying the security options in Creat() or openOrCreate() method: • DatabaseFactory.create(URI fileURI, DatabaseSecurityOptions securityOptions); • DatabaseFactory.openOrCreate(URI fileURI, DatabaseSecurityOptions securityOptions);

11. Encrypted Databases (Cont.) • When a database is encrypted, it is linked to the device in which it is created. So, it does not support portability between databases. • In order to transfer the encrypted database to the other device, you have to decrypt it first. • An encrypted database should be closed as soon as possible, otherwise, it is susceptible to “cold boot attack”.

12. DatabaseSecurity Options • DatabaseSecurityOptions defines the security options for a database. • There are three choices available: • Not encrypted, accessible from any application • Encrypted, accessible from any application • Encrypted and protected, accessible only from applications that are signed with code signed key

13. DatabaseSecurity Options (Cont.) • If two applications are signed with the same key, then both applications have access to the database. • Methods: • Public DatabaseSecurityOptions(boolean encrypted) • If encrypted is true, it means that the database is encrypted and not portable • Public DatabaseSecurityOptions(CodeSigningKey key) • public DatabaseSecurityOptions(int signerId)

14. Example- Encrypted Database • It opens or creates an encrypted database file. It is not portable between devices. public void createEncryptedDatabaseFile() throws Exception { // The database is created in SD card String dbLocation = "/SDCard/databases/myApplication/"; //Create a URI path file for the database URI uri = URI.create(dbLocation + “myDB.db”); //Specify security options to encrypt the database DatabaseSecurityOptions dso = new DatabaseSecurityOptions(true); // Invoke the create() or openOrCreate() method Database db = DatabaseFactory.openOrCreate(uri, dso); }

15. Database Class • Database class allows to create, delete and execute SQL statements • In order to create a SQL statement, invoke createStatement() method of Database class. • Statement createStatement(String sqlStatement) • Creates a Statement in this database. • The argument can contain multiple SQL Statements delimited by ‘;’

16. A statement lifecycle • It represents a SQL statement. • The lifecycle of a statement is: • Create a statement • Prepare statement • Bind • Query Execution or • Update Execution

17. Example- Create a Table Public void createTable() throws Exception{ String dbLocation = "/SDCard/databases/myApplication/"; //Create a URI path file for the database URI uri = URI.create(dbLocation + “myDB.db”); Database db = DatabaseFactory.open(uri); //Create a statement Statement st = db.createStatement( “CREATE TABLE ‘Student’ ( ” + “ ‘Name’ TEXT,” + “ ‘ID’ INTEGER )”); st.prepare(); st.execute(); }

18. Bind() Method • If the SQL statement has parameters, you have to invoke bind() method to bind the parameter to a value. • Statement.bind() has two arguments: the first argument is the parameter number and the second parameter is the value to bound to it. • The occurance of ? in a statement means parameters are numbered sequentially.

19. Bind() Method (Cont.) • The occurance ?NN in a SQL statement means that each parameter is numbered as integer NN. • Example: Statement s = “SELECT * FROM Employee WHERE salary < ?2 AND salary > ?10”; Means that: bind(2, 80000); // means salary <80000 bind(10, 50000); // means salary >50000

20. Example-Bind() method Public void insertDataIntoTable() throws Exception{ String dbLocation = "/SDCard/databases/myApplication/"; URI uri = URI.create(dbLocation + “myDB.db”); Database db = DatabaseFactory.open(uri); Statement st = db.createStatement( “INSERT INTO Employee VALUES(?, ?, ?)”); st.prepare(); st.bind(1, employeeID); st.bind(2, name); st.bind(3, salary); st.execute(); }

21. Query Execution • Query execution is done using getCursor() method. • getCursor() executes only SELECT queries and returns cursor with selected rows . • The returned cursor is positioned before the first row. Hence, to get the current row, invoke Cursor.first() or Cursor.next() method. • The method’s signature: Cursor getCursor()

22. Cursor Class • Cursor provides read-only access to results returned by getCursor() method • It is an iterator. It provides one-way forward-only navigation method. • Methods: • getRow(): returns current row with values • next(): move the cursor to the next row • prev(): move cursor to the previous row • isEmpty(): determines whether cursor has rows • close(): closes cursor object

23. Example- Cursor Class Public void selectDataTable() throws Exception{ URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”); Database db = DatabaseFactory.open(uri); Statement st = db.createStatement( “SELECT * FROM Employee”); st.prepare(); Cursor cursor = st.getCursor(); while(cursor.next()) { row = cursor.getRow(); id = row.getInteger(0); name = row.getString(1); salary = tow.getInteger(2); } st.close(); cursor.close(); }

24. Update Execution • To update execution, invoke execute() method • Statement.execute() executes an update statement. It does not return a result set. • If the query statement is NOT SELECT type, invoke this method. You can use for any other SQL statement like INSERT, DELETE, CREATE, UPDATE and so on.

25. Example – Execute() method Public void deleteTable() throws Exception{ String dbLocation = "/SDCard/databases/myApplication/"; URI uri = URI.create(dbLocation + “myDB.db”); Database db = DatabaseFactory.open(uri); Statement st = db.createStatement( “DELETE Employee”); st.prepare(); st.execute(); }

26. Reset() method • Statement. reset() method resets the statement to its state after prepare() method. • It also clears all bindings. • After invoking reset() method, execute() must be called to het a new cursor. • So, if several SQL statements are run, invoke reset() method after each execute() mthod to clear bindings and clear the statement state.

27. Example- reset() Method Public void insertDataTable() throws Exception{ URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”); Database db = DatabaseFactory.open(uri); Statement st = db.createStatement(“INSERT INTO Employee VALUES(?, ?)”); st.prepare(); while(count <10) { st.bind(1,count); st.bind(2, employee[count]); st.execute(); st.reset(); count++; } st.close(); }

28. Transactions • In order to execute multiple statements in one transaction, two methods should be called: • beginTransaction(): • Starts a new transaction. So, all of the following statement executions can be committed or rolled back. • commitTransaction() • Commits current transaction starting with the beginTransaction(). Before committing the transaction, all the cursors started within this transaction should be closed otherwise, the transaction is failed. • Nested transactions are not supported.

29. Example- Transaction public void transactionStatement() { URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”); Database db = DatabaseFactory.open(uri); db.beginTransaction(); Statement st = db.createStatement(“INSERT INTO Employee VALUES(1, ‘Bob’)”); st.prepare(); st.execute(); st.reset(); Statement st = db.createStatement(“INSERT INTO Employee VALUES(2, ‘John’)”); st.prepare(); st.execute(); db.commitTransaction(); }

30. Delete a Database • Invoke DatabaseFactory.delete() to remove an exisiting database from the device. • The method’s signature: public static void delete( URI fileURI) • Example: URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”); //Remove the database from the device DatabaseFactory.delete(uri);

31. Vacuum Command • Database is stored as a file. Some operations such as dropping a table, inserting or deleting data, cause the file to be fragmented. • Vacuum command is used to defragment and reduce the size of the database file. • To defragment the file, Vacuum command copies all pieces of the file into the memory and creates a new database file.

32. Vacuum Command (Cont.) • Because Vacuum copies all information related to the database into the memory, it needs enough memory. • If enough memory is not available, the Vacuum command is failed.

33. Best Practices • In order to have a better performance when developing a database application, consider the following: • Use local variables • Use primitive types instead of classes • Use static variables instead of Strings • Use efficient algorithms • Write efficient loops • Avoid Java.util.Enumeration

34. Best Practices (Cont.) • Make classes final • Using final keyword means that it would never be extended. • Use int instead of long • long is a 64-bit integer • Avoid unnecessary field initialization • Explicitly initialize local variables • Use temporary tables as much as possible • It takes less recourses.

35. Best Practices (Cont.) • Store data as little as possible • The search is faster. • Avoid subqueries • Because the result is stored in a temporary file • Use static inner class • Reduce the number of references

36. References • BlackBerry Java Application SQLite Version. 5.0 (Development Guide) Available online at http://www.blackberry.com/developers