JSON support in SquiDB

SquiDB 3.0 introduces the squidb-json addon, a set of modules that facilitate using SquiDB with JSON objects and the SQLite json1 extension. It has two core features:

• Facilitate serializing and deserializing arbitrary objects to String columns as JSON
• Provide support for using the json1 extension SQL functions in SquiDB queries

Serializing objects

To use this feature, all three modules of squidb-json are required. This includes squidb-json-annotations, squidb-json-compiler (which controls code generation), and squidb-json-plugin, which provides runtime support for the generated code. You can add these three modules as a dependency to your project like so:

// in build.gradle
dependencies {
    // ... other squidb dependencies appear here

    compile 'com.yahoo.squidb:squidb-json-annotations:3.0.0'
    compile 'com.yahoo.squidb:squidb-json-plugin:3.0.0'
    annotationProcessor 'com.yahoo.squidb:squidb-json-compiler:3.0.0'
}

android {
    ...
    defaultConfig {
        javaCompileOptions {
            annotationProcessorOptions {
                arguments = [
                        squidbPlugins : 'com.yahoo.squidb.json.JSONPlugin',
                ]
            }
        }
    }
}

With these modules added as a dependency, you can annotate fields in your table model spec classes with @JSONColumn. JSON columns are not limited to the primitive types; arbitrary objects are supported. For example:

@TableModelSpec(className = "JSONExample", tableName = "json_example")
class JSONExampleSpec {

    // This column stores a MySpecialPojo as a serialized JSON string
    @JSONColumn
    MySpecialPojo jsonPojo;

    // This column stores a List<String> as a serialized JSON string
    @JSONColumn
    List<String> someList;

    // This column stores a Map<String, String> as a serialized JSON string
    @JSONColumn
    Map<String, String> someMap;
}

The getters and setters generated for these columns will use their declared object types, rather than a string. Under the hood, these methods will take care of serializing and deserializing these objects to JSON strings and storing them as strings in the database. For example:

JSONExample example = new JSONExample();
List<String> sampleList = Arrays.asList("A", "B", "C", "D", "E");

example.setSomeList(exampleList);
db.persist(example);

JSONExample fetchedFromDatabase = db.fetch(JSONExample.class, example.getId());
List<String> fetchedList = fetchedFromDatabase.getSomeList();

Your only other responsibility is to initialize the squidb-json runtime to make sure it knows how to perform this serialization. The runtime is serialization-library-agnostic, so you can implement serialization using your favorite JSON library (e.g. GSON or Jackson). All you have to do is implement the JSONMapper interface and initialize JSONPropertySupport with an instance of that interface. For example, using GSON:

public class GsonMapper implements JSONMapper {
    // Configure Gson however you want here
    private static final Gson GSON = new GsonBuilder().serializeNulls().create();

    @Override
    public String toJSON(Object toSerialize, Type javaType) throws Exception {
        return GSON.toJson(toSerialize, javaType);
    }

    @Override
    public <T> T fromJSON(String jsonString, Type javaType) throws Exception {
        return GSON.fromJson(jsonString, javaType);
    }
}

// Somewhere at runtime:
JSONPropertySupport.setJSONMapper(new GsonMapper());

That's it! You're totally free to implement JSONMapper however you want, using whatever library you want.

json1 function support

To use this feature, only the squidb-json-plugin module is required as a dependency. The JSONFunctions class in this module contains declarations for the SQL functions found in the json1 extension, in the same way that the Function class in SquiDB core contains declarations for core SQLite functions. These JSON functions can be used to construct queries using SquiDB's query object. For example:

JSONExample example = new JSONExample();
List<String> sampleList = Arrays.asList("A", "B", "C", "D", "E");

example.setSomeList(exampleList);
db.persist(example);

Query jsonArrayLength = Query.select(JSONFunctions.jsonArrayLength(JSONExample.SOME_LIST))
    .from(JSONExample.TABLE).where(JSONExample.ID.eq(example.getId()));

long jsonArrayLengthResult = db.simpleQueryForLong(jsonArrayLength); // returns 5

Note that using these functions requires connecting to a SQLite build that has the json1 extension enabled. It's not enabled on stock Android SQLite, but it is enabled by default in the squidb-sqlite-bindings module. See this wiki page for instructions on how to connect to a custom SQLite build. The json1 extension is well documented, so we won't rehash all the functions here.

---

See also:

• Using a custom SQLite build
• Adding plugins to code generation