SQLKing

SQLKing is an Android SQLite ORM powered by an annotation preprocessor, tables are defined by @Table annotations and CRUD classes expose an expressive api for executing SQLite queries. ####Gradle dependencies#### NOTE: See https://bitbucket.org/hvisser/android-apt if you are not familiar with using annotation preprocessors on Android.

dependencies {
apt 'com.memtrip.sqlking:preprocessor:1.1.5'
compile 'com.memtrip.sqlking:client:1.1.5'
}

####Define your models### SQL tables are defined by POJOs that are annotated with @Table. Table columns are annotated with @Column and must have matching getter / setter methods i.e; private String name; must be accompanied by both a String getName() and a setName(String newVal) method.

@TablepublicclassUser {
@ColumnprivateStringusername;
@Columnprivatelongtimestamp;
@ColumnprivatebooleanisRegistered;
@Columnprivatebyte[] profilePicture;
publicStringgetUsername() {
returnusername;
}
publicvoidsetUsername(StringnewVal) {
username = newVal;
}
publiclonggetTimestamp() {
returntimestamp;
}
publicvoidsetTimestamp(longnewVal) {
timestamp = newVal;
}
publicbooleangetIsRegistered() {
returnisRegistered;
}
publicvoidsetIsRegistered(booleannewVal) {
isRegistered = newVal;
}
publicbyte[] getProfilePicture() {
returnprofilePicture;
}
publicvoidsetProfilePicture(byte[] newVal) {
profilePicture = newVal;
}
}

###Q### The Q class is generated by the annotation preprocessor, it contains a DefaultResolver() method which is required by SQLInit to create the database. Q also contains a series of static variables that can be used to reference @Table columns. As a good practise these variables should be used whenever you reference a table column.

// an example of columns that are auto generated within a Q classpublicstaticfinalStringUSERNAME = "username";
publicstaticfinalStringTIMESTAMP = "timestamp";
publicstaticfinalStringIS_REGISTERED = "isRegistered";
publicstaticfinalStringPROFILE_PICTURE = "profilePicture";
// the columns can be accessed directly from the Q class, e.g;StringusernameColumnFromUserTable = Q.User.USERNAME;

####Initialise the database#### SQLKing will create a database based on the POJOs that are annotated with @Table, when these POJOs are changed or new POJOs are added, the version number argument must be incremented. The SQLProvider instance that is returned from SQLInit must be kept throughout the lifecycle of your application, it is required by the execute() and rx() methods. We recommend you attach inject it as a dependency or attach it to your Application context. NOTE: Incrementing the version number will drop and recreate the database.

publicvoidsetUp() {
SQLProviderprovider = SQLInit.createDatabase(
"SQLKing",
1,
newQ.DefaultResolver(),
getContext(),
User.class,
Post.class
);
}

####Querying the database#### The Insert, Select, Update, Delete and Count classes are used to query database tables, they use a getBuilder() method to add clause and operation arguments. The Builder finishes by using either the execute() method or the rx() method.

The rx() method returns an RxJava Observable.

Select.getBuilder()
.rx(User.class, sqlProvider)
.subscribeOn(Schedulers.io())
.observeOn(AndroidSchedulers.mainThread())
.subscribe(newAction1<User[]>() {
@Overridepublicvoidcall(User[] users) {
// do something with the users
}
});

The execute() method returns results directly. NOTE: execute() will block the ui thread, we recommend you use RxJava.

Useruser = newUser();
user.setUsername("12345678");
user.setIsRegistered(true);
user.setTimestamp(System.currentTimeMillis());
// INSERT INTO User (username, isRegistered, timestamp) VALUES ('12345678',true,632348968244);Insert.getBuilder().values(users).execute(User.class, sqlProvider);

// SELECT * FROM User;User[] users = Select.getBuilder().execute(User.class, sqlProvider);

ContentValuescontentValues = newContentValues();
contentValues.put(Q.User.IS_REGISTERED, true);
contentValues.put(Q.User.TIMESTAMP, System.currentTimeMillis());
// UPDATE User SET isRegistered = 'true', timestamp = '123456789'introwsUpdated = Update.getBuilder()
.values(contentValues)
.execute(User.class, getSQLProvider());

// DELETE FROM User;introwsDeleted = Delete.getBuilder().execute(User.class, sqlProvider);

// SELECT Count(*) FROM User;intcount = Count.getBuilder().execute(User.class, sqlProvider);

####Clauses#### The Where, And, In, and Or classes are used to build up the query. Where is powered by the Expression enum:

publicenumExp {
EQUAL_TO ("="),
MORE_THAN (">"),
MORE_THAN_OR_EQUAL_TO (">="),
LESS_THAN ("<"),
LESS_THAN_OR_EQUAL_TO ("<="),
LIKE ("LIKE");
}

The following illustrate how to build more complex queries:

// SELECT * FROM User WHERE isRegistered = 'true';User[] users = Select.getBuilder()
.where(newWhere(Q.User.IS_REGISTERED, Where.Exp.EQUAL_TO, true))
.execute(User.class, sqlProvider);

// SELECT * FROM User WHERE username LIKE 'jo%'User[] users = Select.getBuilder()
.where(newWhere(Q.User.USERNAME, Where.Exp.LIKE, "jo%"))
.execute(User.class, sqlProvider);

// SELECT * FROM User WHERE username IN ("sam","josh");User[] users = Select.getBuilder()
.where(newIn(Q.User.USERNAME, "sam", "josh"))
.execute(User.class, sqlProvider);

// SELECT * FROM User WHERE ((username = "sam" OR username = "angie") AND (timestamp >= 1234567890));User[] users = Select.getBuilder()
.where(newAnd(
newOr(
newWhere(Q.User.USERNAME, Where.Exp.EQUAL_TO, "sam"),
newWhere(Q.User.USERNAME, Where.Exp.EQUAL_TO, "angie")
),
newAnd(
newWhere(Q.User.TIMESTAMP, Where.Exp.MORE_THAN_OR_EQUAL_TO, 1234567890)
)))
.execute(User.class, sqlProvider);

####Keywords#### The OrderBy and Limit classes are used to manipulate the results of the Select class

// SELECT * FROM user ORDER BY username DESCUser[] users = Select.getBuilder()
.orderBy(Q.User.USERNAME, OrderBy.Order.DESC)
.execute(User.class, sqlProvider);

// SELECT * FROM user ORDER BY username DESC LIMIT 2,4User[] users = Select.getBuilder()
.limit(2,4)
.orderBy(Q.User.USERNAME, OrderBy.Order.DESC)
.execute(User.class, sqlProvider);

####Joins#### Joins can be performed using the InnerJoin, LeftOutJoin, CrossInnerJoin, NaturalInnerJoin, NaturalLeftOuterJoin classes. The target table for the join must be defined as an @Column, the object will be populated with any join results.

@TablepublicclassComment {
@Column(index = true) intid;
@ColumnintuserId;
@ColumnUseruser; // The target table for a potential joinpublicintgetId() {
returnid;
}
publicvoidsetId(intid) {
this.id = id;
}
publicintgetUserId() {
returnuserId;
}
publicvoidsetUserId(intuserId) {
this.userId = userId;
}
publicUsergetUser() {
returnuser;
}
publicvoidsetUser(Useruser) {
this.user = user;
}
}
@TablepublicclassUser {
@Column(index = true) intid;
publicintgetId() {
returnid;
}
publicvoidsetId(intid) {
this.id = id;
}
}
Comment[] comments = Select.getBuilder()
.join(innerJoin(User.class, on("Comment.userId","User.id")))
.execute(Comment.class, App.getInstance().getSQLProvider());
Useruser = comments[0].getUser(); // The nested User object is populated by the join

####Primary Key#### An auto incrementing primary key can be defined using:

@TablepublicclassData {
@Column(primary_key = true, auto_increment = true) intid;
publicintgetId() {
returnid;
}
publicvoidsetId(intid) {
this.id = id;
}
}

####Tests#### The tests/java/com/memtrip/sqlking package contains a full set of unit and integration tests. The tests can be used as a good reference on how to structure queries.

####TODO####

• Validate that object relationships defined by @Column are annotated with @Table
• Validate that auto_increment columns must be int or long
• @Table annotation should support foreign_key functionality
• @NotNull annotation and handle this validation in the software layer
• Composite Foreign Key Constraints