Summary
Consider a project which uses a SQLite database.
Over time requirements changes. We may be forced to change the schema of that SQLite database.
Unlike other RDBMS, SQLite doesn't allow you to add, modify or delete columns in a table after a table is created. You will be required to export the data, recreate the schema, then import the data. Such data migration exercises requires scripts to be built with attention to detail to ensure there is no data loss during data migration.
You can future proof your schema by storing data as JSON. AppStudio's SqlScalarFunction helps optimize the use of JSON.
Scenario
You've been tasked with creating a Geographic Quiz app.
Version 1 will ship with a set of countries and we want to quiz the user their knowledge of capitals.
Version 2 will ship with population and we want to quiz the user to rank countries in order.
Version 3 will ship with currency conversion quiz.
Initial App
The following is a sample app which is an initial implementation of Version 1. We will deconstruct this app and explore how it could handle the future requirements.
<SPAN class="keyword token">import</SPAN> QtQuick <SPAN class="number token">2.7</SPAN>
<SPAN class="keyword token">import</SPAN> ArcGIS<SPAN class="punctuation token">.</SPAN>AppFramework <SPAN class="number token">1.0</SPAN>
<SPAN class="keyword token">import</SPAN> ArcGIS<SPAN class="punctuation token">.</SPAN>AppFramework<SPAN class="punctuation token">.</SPAN>Sql <SPAN class="number token">1.0</SPAN>
Item <SPAN class="punctuation token">{</SPAN>
property FileFolder sqlFolder<SPAN class="punctuation token">:</SPAN> FileFolder <SPAN class="punctuation token">{</SPAN> path<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"~/ArcGIS/Data/Sql"</SPAN> <SPAN class="punctuation token">}</SPAN>
ListView <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> listView
anchors<SPAN class="punctuation token">.</SPAN>fill<SPAN class="punctuation token">:</SPAN> parent
anchors<SPAN class="punctuation token">.</SPAN>margins<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">10</SPAN>
delegate<SPAN class="punctuation token">:</SPAN> Row <SPAN class="punctuation token">{</SPAN> spacing<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">;</SPAN> Text <SPAN class="punctuation token">{</SPAN> text<SPAN class="punctuation token">:</SPAN> name <SPAN class="punctuation token">}</SPAN> Text <SPAN class="punctuation token">{</SPAN> text<SPAN class="punctuation token">:</SPAN> capital <SPAN class="punctuation token">}</SPAN> <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
SqlDatabase <SPAN class="punctuation token">{</SPAN>
id<SPAN class="punctuation token">:</SPAN> db
databaseName<SPAN class="punctuation token">:</SPAN> sqlFolder<SPAN class="punctuation token">.</SPAN><SPAN class="token function">filePath</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'countries.sqlite'</SPAN><SPAN class="punctuation token">)</SPAN>
SqlScalarFunction <SPAN class="punctuation token">{</SPAN>
name<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'json_value'</SPAN>
method<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">function</SPAN> <SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> key<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN> <SPAN class="keyword token">return</SPAN> json_text <SPAN class="operator token">?</SPAN> JSON<SPAN class="punctuation token">.</SPAN><SPAN class="token function">parse</SPAN><SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">null</SPAN><SPAN class="punctuation token">;</SPAN> <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
Component<SPAN class="punctuation token">.</SPAN>onCompleted<SPAN class="punctuation token">:</SPAN> <SPAN class="punctuation token">{</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">open</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"DROP TABLE IF EXISTS countries "</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CREATE TABLE IF NOT EXISTS countries (json_text TEXT)"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"CREATE INDEX countries_name ON countries ( json_value(json_text, 'name') COLLATE NOCASE )"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO countries VALUES (:json_text)"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">{</SPAN> json_text<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'{"name":"United States", "capital":"Washington D.C."}'</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO countries VALUES (:json_text)"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">{</SPAN> json_text<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'{"name":"Australia", "capital":"Canberra", "population": 24130000}'</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">query</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"INSERT INTO countries VALUES (:json_text)"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">{</SPAN> json_text<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'{"name":"France", "capital":"Paris"}'</SPAN><SPAN class="punctuation token">}</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>
<SPAN class="string token">"SELECT json_value(json_text, 'name') as name, "</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">" json_value(json_text, 'capital') as capital "</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"FROM countries "</SPAN><SPAN class="punctuation token">,</SPAN>
<SPAN class="string token">"WHERE name like 'United%' "</SPAN>
<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="token function">join</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
listView<SPAN class="punctuation token">.</SPAN>model <SPAN class="operator token">=</SPAN> db<SPAN class="punctuation token">.</SPAN><SPAN class="token function">queryModel</SPAN><SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>Schema
You see the schema for our app is simply:
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">TABLE</SPAN> countries
<SPAN class="punctuation token">(</SPAN>
json_text <SPAN class="keyword token">TEXT</SPAN>
<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
In Version 1, we want json_text to be a JSON string with the country's name and capital defined. To preview Version 2 one of the records already has population defined as well.
Unpacking JSON with SqlScalarFunction
We have a SqlScalarFunction defined which implements:
<SPAN class="keyword token">function</SPAN> <SPAN class="token function">json_value</SPAN><SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> key<SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">{</SPAN>
<SPAN class="keyword token">return</SPAN> json_text <SPAN class="operator token">?</SPAN> JSON<SPAN class="punctuation token">.</SPAN><SPAN class="token function">parse</SPAN><SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">null</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>So that `SQLite` can use this to unpack a JSON string and retrieve a value by its key.
Indexing and Querying
SQLite allows one to create indexes on expressions. This means we can create an index on a key extracted from the JSON string. This feature is incredible. It means we are effectively caching an extracted value so that means we can avoid repeated calculation.
<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">INDEX</SPAN> countries_name <SPAN class="keyword token">ON</SPAN> countries <SPAN class="punctuation token">(</SPAN> json_value<SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'name'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">COLLATE</SPAN> NOCASE <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">SELECT</SPAN> json_value<SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'name'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> name<SPAN class="punctuation token">,</SPAN>
json_value<SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'capital'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> capital
<SPAN class="keyword token">FROM</SPAN> countries
<SPAN class="keyword token">WHERE</SPAN> name <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'United%'</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
When you check the query with `EXPLAIN QUERY PLAN` we see that it is using the index:
<SPAN class="keyword token">EXPLAIN</SPAN> QUERY <SPAN class="keyword token">PLAN</SPAN>
<SPAN class="keyword token">SELECT</SPAN> json_value<SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'name'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> name<SPAN class="punctuation token">,</SPAN>
json_value<SPAN class="punctuation token">(</SPAN>json_text<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'capital'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">AS</SPAN> capital
<SPAN class="keyword token">FROM</SPAN> countries
<SPAN class="keyword token">WHERE</SPAN> name <SPAN class="operator token">LIKE</SPAN> <SPAN class="string token">'United%'</SPAN><SPAN class="punctuation token">;</SPAN>
SEARCH <SPAN class="keyword token">TABLE</SPAN> countries <SPAN class="keyword token">USING</SPAN> <SPAN class="keyword token">INDEX</SPAN> countries_name <SPAN class="punctuation token">(</SPAN><SPAN class="operator token"><</SPAN>expr<SPAN class="operator token">>></SPAN>? <SPAN class="operator token">AND</SPAN> <SPAN class="operator token"><</SPAN>expr<SPAN class="operator token">></SPAN><SPAN class="operator token"><</SPAN>?<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Note that a collating sequence must be specified else `LIKE` where clauses will yield a full table scan.
Planning
When planning for Versions 2 and Versions 3 of the app, you'll appreciate that we can reuse the existing SQLite database and just add or update records within. We can easily drop / create indexes to accommodate new fields and new app requirements.
