Using CSV files in your application
Summary
This is the second blog about the SQL Beta feature in AppStudio 2.0. If you haven't seen the first blog, I recommend you start there.
In this blog, we will discuss SQLite In-Memory databases and CSV files and how this may benefit your application.
1. SQLite In-Memory Database
The ":memory:" name is used to specify the in-memory database. This is useful for applications that would like to start with a blank SQLite database every time the application is run and do not need the data to persist after the application has completed. This is useful for providing model content to populate controls.
import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0
App {
id: app
width: 640 * AppFramework.displayScaleFactor
height: 480 * AppFramework.displayScaleFactor
ComboBox {
id: countryComboBox
readonly property var _model: model
textRole: "country"
}
SqlDatabase {
id: db
databaseName: ":memory:"
}
Component.onCompleted: {
db.open();
db.query("CREATE TABLE world_cities (name TEXT,subcountry TEXT, country TEXT)");
db.query("INSERT INTO world_cities VALUES ('Melbourne', 'Victoria', 'Australia')");
db.query("INSERT INTO world_cities VALUES ('Redlands', 'California', 'United States')");
countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
}
}
2. Bundling CSV files in your app
If you have data in a CSV file, we can create a linked table in SQLite. As a linked table only the metadata, i.e. filename to the CSV file is stored in SQLite, the content will continue to reside only in the CSV file. The CSV file can be either a physical file or an embedded resource. The syntax to linking tables is as follows:
CREATE VIRTUAL TABLE tableName USING CSV ( 'filePath' )The advantage of using technique is that you will have access to your CSV data via the SQLite query engine.
When we're developing the application we will be using:
CREATE VIRTUAL TABLE world_cities USING CSV ( 'C:/Users/Stephen/ArcGIS/AppStudio/Apps/Sample/data/world-cities.csv' )When we've finalized the application and want to build it using Local Make or Cloud Make, we want to change it to:
CREATE VIRTUAL TABLE world_cities USING CSV ( ':/qml/data/world-cities.csv' )To achieve both we will use the FileFolder object to help.
SqlDatabase {
id: db
databaseName: ":memory:"
}
FileFolder {
id: dataFolder
url: "data"
}
Component.onCompleted: {
var csvFilePath = dataFolder.filePath("world-cities.csv");
db.open();
db.query("CREATE VIRTUAL TABLE world_cities USING CSV ( '" + csvFilePath + "' )");
countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
}3. ComboBox with Huge Data
By leveraging from the SQLite query engine we can build quite compelling user interfaces quickly and simply. In the following example, instead of asking the user to select from a list of 20,000 cities, we can filter the list with a WHERE clause on country and subcountry.
cityComboBox.model = db.queryModel(
"SELECT name "
+ "FROM world_cities "
+ "WHERE country = :country "
+ "AND subcountry = :subcountry "
+ "ORDER BY name",
{
country: countryComboBox.currentText,
subcountry: subcountryComboBox.currentText
}
);If you are experiencing a lag whenever the queries are run, then, at this point, you will need to consider using SQLite indexes. However, indexes cannot be applied to the linked table. We must make a copy of the table and then we can put indexes on the copy.
Component.onCompleted: {
var csvFilePath = dataFolder.filePath("world-cities.csv");
db.open();
db.query("CREATE VIRTUAL TABLE world_cities_csv USING CSV ('" + csvFilePath + "')");
db.query("CREATE TABLE world_cities AS SELECT * FROM world_cities_csv");
db.query("CREATE INDEX IX_world_cities_001 ON world_cities (country, subcountry, name)");
countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
}4. Known Issues
4.1. Garbage Collection on Models
The Javascript garbage collector is prematurely destroying models. For example, the pattern for assigning a query model to a QML component is as follows:
countryComboBox.model = db.queryModel(QML components, currently, do not inform the Javascript garbage collector that it has a reference to the model. The Javascript garbage collector *CAN* destroy the model whenever it feels like. This can lead to instability with the QML component. i.e. suddenly the values in the ComboBox may disappear (i.e. be replaced with blanks instead of values) or interacting with the ComboBox may crash the application.
A workaround is to define a Javascript property that keeps the model alive. This is the purpose of the readonly _model property in the following code snippet:
ComboBox {
id: countryComboBox
readonly property var _model: model
textRole: "country"
}We've informed Qt company of this issue.
4.2 Quoted values
If you have quoted values in your CSV file, the values aren't unquoted when you access them. The workaround is you have to add additional logic to identify and remove the extraneous quotes.
This issue is being addressed for the next release of AppStudio
4.3 Trailing spaces aren't being trimmed
If you have trailing spaces in your CSV, e.g. "country, subcountry" instead of "country,subcountry" then this will cause a problem. For the time being, you will have to trim the trailing spaces from your CSV file.
This issue is being addressed for the next release of AppStudio
5. Select City sample
The "Select City from CSV" app is available for you to try which demonstrates all of the above points. You can find "Select City from CSV" app in AppStudio.
- Launch AppStudio
- Select New App
- Click Search Icon
- Type: Select City from CSV
The "Select City from CSV" source code is also available at arcgis-appstudio-samples GitHub