SQLighter

Quick bite with property mapping and association fetching

/* This is Java */
AnOrm<Customer> customerOrm = incubator.make(Customer.class);
AnOrm<Appointment> apptOrm = incubator.make(Appointment.class);
	// retrieve the Customer with id == 1;
customerOrm.startSqlSelect(); 
customerOrm.addWhere("id = ?", 1); 
Customer customer = customerOrm.getSingleResult(); 

/* many-to-one and one-to-many association fetching */

	// lets retrieve customer.getAppointments() associaion
customerOrm.fetch(customer, "appointments");
	// let's retrieve appointment.getCreateUser() for each Appointment
apptOrm.fetch(customer.getAppointments(), "createUser");

/* This is Objective C */
// ...
// jsonAppointmentStringFromServer is:  {"id": "234", "name": "Appointment #234", "isProcessed": "0"}
Appointment *appointmentFromServer = [apptOrm asNativeObjectWithNSString: jsonAppointmentStringFromServer];
[apptOrm startSqlInsertWithId: appointmentFromServer];
[apptOrm apply]; // inserted into mobile device's database
...

What is it?

SQLighter is Object Oriented SQLite implementation for Android and iOS mobile platforms.

• Provides SQL capabilities and flexibilty (SQLighter) in similar to JDBC way
• ORM features such as property mapping, association fetching, auto generate tables for your objects etc. (AmfibiaN ORM sub-project within this github repository)
• Database schema versioning management
• JSON mappings
• Portability of your implementation between Android and iOS platforms by being compliant with J2ObjC code translation standards.

This repository contains two Demo projects (one for each platforma=s - Android and iOS) with demonstration and tests.

SQLighter is compatible with J2ObjC technology and provides both Android and Objective C libraries.

You should be able to code SQLite database related logics in java programming language for your Android application and translate/reuse your code for your iOS mobile application using J2ObjC tool from Google.

You can also use SQLighter independently on either of the platforms.

SQLite database is your best choice for mobile platform development since it is part of both iOS and Android SDKs.

Table of content

• [Overview] (https://github.com/vals-productions/sqlighter#overview)
• [ORM] (https://github.com/vals-productions/sqlighter/blob/master/README.md#orm)
• [Going by example] (https://github.com/vals-productions/sqlighter#going-by-example)
• [Pre requisites] (https://github.com/vals-productions/sqlighter#pre-requisites)
• [Android code] (https://github.com/vals-productions/sqlighter#android-code)
• [iOS code] (https://github.com/vals-productions/sqlighter#ios-code)
• Demo
• [J2ObjC] (https://github.com/vals-productions/sqlighter#j2objc)
• [Project configuration] (https://github.com/vals-productions/sqlighter#project-configuration)
• [Using provided libraries] (https://github.com/vals-productions/sqlighter#using-provided-libraries)
• [Using source files] (https://github.com/vals-productions/sqlighter#using-source-files)
• [Sqlighter at Android] (https://github.com/vals-productions/sqlighter#sqlighter-at-android)
• [Sqlighter at iOS] (https://github.com/vals-productions/sqlighter#sqlighter-at-ios)
• [Database file] (https://github.com/vals-productions/sqlighter#database-file)
• [SQLighterDb.deployDbOnce()] (https://github.com/vals-productions/sqlighter#sqlighterdbdeploydbonce)
• [Instantiation example] (https://github.com/vals-productions/sqlighter#instantiation-example)
• [Usage] (https://github.com/vals-productions/sqlighter#usage)
• [Exception and error handling] (https://github.com/vals-productions/sqlighter#exception-and-error-handling)

Overview

This implementation is based on standard Android's SQLite implementation that is being used on Android devices, and matching implementation on iOS device.

This library does not attempt to replicate Android's implementation completely. The goal is to provide ability to execute pretty much any SQL statements at either of the platforms with single and simple interface without dependencies on existing platform specific implementations.

Sqlighter design reminds JDBC implementation. Anyone familiar with JDBC should pick it up really easy.

Sqlighter supports all SQLite datatypes and adds its own (optional) implementation of Date type.

ORM

AmfibiaN ORM, part of this repository, is a "swiss army knife" toolkit that fully supports SQLighter.

AmfibiaN lets you transition your domain objects between their native state, JSON representation and SQL database persistent storage. This covers all your basic needs on object transformations in mobile application. AmfibiaN is J2ObjC compatible given your business objects carry reflection information through J2ObjC translation process.

AmfibiaN's AnUpgrade class provides DB schema versioning and upgrade management features.

      AmfibiaN
     
  [Native Object]
         / \
        /   \
  [JSON] -- [DB]

For more information on AmfibiaN go here

Note, that AmfibiaN might migrate in its own github repository in the future.

We will continue with SQLighter basic SQL features in this document.

Going by example

The following sample Java code results in identical output after being converted into Objective-C using J2ObjC. Therefore, you can implement your database related logics in java language and just convert/reuse it in iOS.

Note: for more up to date examples please check some demo code [Demo.java] (https://github.com/vals-productions/sqlighter/blob/master/demo/andr-demo-prj/app/src/main/java/com/prod/vals/andr_demo_prj/Demo.java) which is part of the actual demo project code for Android and iOS devices.

Pre requisites

Let's create table "user" in sqlite database.

CREATE TABLE "user" (
	`name`	TEXT,
	`email`	TEXT,
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
	`data`	BLOB,
	`height` REAL
);

Let's insert the following initial records:

INSERT INTO user(name, email, data, height) values ('user 1', 'user1@email.com', null, 1.4);
INSERT INTO user(name, email, data, height) values ('user 2', 'user2@email.com', null, null);
INSERT INTO user(name, email, data, height) values ('user 3', 'user3@email.com', null, 4.89);
INSERT INTO user(name, email, data, height) values ('user 4', null, null, null);

Android code

First let's just output what is initially in the table by executing SQL select statement with no parameters:

SQLighterDb db = Bootstrap.getInstance().getDb();
SQLighterRs rs = db.executeSelect("select id, email, name, data, height from user");
System.out.println("initial state ");
while (rs.hasNext()) {
  print(rs);
}
rs.close();

And this should result in:

initial state 
pk: 1, email: user1@email.com, name: user 1, blob data: , height: 1.4
pk: 2, email: user2@email.com, name: user 2, blob data: , height: null
pk: 3, email: user3@email.com, name: user 3, blob data: , height: 4.89
pk: 4, email: null, name: user 4, blob data: , height: null

print function looks just like this and is used in all examples:

private void print(SQLighterRs rs) {
	Long pk = rs.getLong(0);
    String e = rs.getString(1);
    String n = rs.getString(2);
    byte[] dataBytes = rs.getBlob(3);
    String dataString = null;
    if (dataBytes != null) {
    	dataString = new String(dataBytes);
    }
    Number h = rs.getDouble(4);
    System.out.println("pk: " + pk + ", email: " + e + ", name: " + n + 
    						", blob data: " + dataString + ", height: " + h );
}

Then, add another record with some blob value:

String dataStr = "This is blob string example";
byte[] data = dataStr.getBytes();
db.addParam("user name 5"); // bind too the first insert value (name)
db.addParam("qw@er.ty1"); // bind to the second one (email)
db.addParam(data); // bind to the data column
db.addParam(5.67); // bind to the height column
db.executeChange("insert into user( name, email, data, height) values (?, ?, ?, ?)");

And let's also requery what we just inserted

db.addParam("qw@er.ty1"); // bind to the where email filter condition
System.out.println("check if the record was inserted");
rs = db.executeSelect("select id, email, name, data, height from user where email = ?");
while (rs.hasNext()) {
  print(rs);
}
rs.close();

Which should result in:

check if the record was inserted
pk: 5, email: qw@er.ty1, name: user name 5, blob data: This is blob string example, height: 5.67

Then, let's do some update

db.addParam("user@email.com"); // bind to the set email = ? 
db.addParam("qw@er.ty1"); // bind to where email = ?
db.executeChange("update user set email = ? where email is null or email = ?");

... and verify the output with above select all code

after update state
pk: 1, email: user1@email.com, name: user 1, blob data: , height: 1.4
pk: 2, email: user2@email.com, name: user 2, blob data: , height: null
pk: 3, email: user3@email.com, name: user 3, blob data: , height: 4.89
pk: 4, email: user@email.com, name: user 4, blob data: , height: null
pk: 5, email: user@email.com, name: user name 5, blob data: This is blob string example, height: 5.67

Let's delete something

db.addParam("user@email.com");
db.executeChange("delete from user where email = ?");

and see what we get:

after delete state
pk: 1, email: user1@email.com, name: user 1, blob data: , height: 1.4
pk: 2, email: user2@email.com, name: user 2, blob data: , height: null
pk: 3, emai