Using SQLite

SQLite is a fast, popular and easy to use database. Its speed and easy of use make it a practical choice for use when you need an indexed file system for an app. Databases you create can be accessed, without change, on any platform SQLite supports. It is built into mobile devices that AppStudio supports.

If you just need to save some simple data, without needing an index, have a look at using localStorage.

SQLite is a zero-configuration, in-process, SQL database engine. But don't let this description scare you: SQLite is an easy way for your app to store and retrieve data.

Your app can create, update and delete SQLite databases. The databases are kept in your app's private directory. Generally, an app is not allowed to access databases belonging to other apps for security reasons. However, apps deployed from the same server can share databases. This allows you to create a family of apps which share data, so long as they all come from the same server. The security protects apps from changing or stealing information from other app's databases.

Databases cannot be directly imported or exported, once again, for security reasons. However, it is possible to include an SQLite database with your application. Add the name of the database to the manifest in Properties Window and it will be automatically converted in to a format that can be exported from the server. The app will then import the database automatically when the application starts for the first time. To do this, the SQLExport and SQLImport methods are called internally. These methods are also available for use at any time.

There are two sets of commands: the ones AppStudio uses to call SQLite and the SQLite commands themselves. Documentation on AppStudio's commands are in this Tech Note; documentation on SQLite itself is in the SQLite Reference.

Persisting Updates to the Database

If you have an already defined database that you want to include with your application, to ensure that updates to your database are persisted, one needs to ensure that the "manifest" property of the project is properly set. When the database is specified in the manifest property, its uploaded to the application when the application is first run just like the SQLImport statement.

The SQLImport function uses this statement for example, SQLImport(JSON, [DB], [callback], [overwrite]), and your "manifest" property should be in line with this for your updates to be considered when your application is run, an example would be 'mydb.db,dbObj,callBackMethod,NSB.overwriteIfVersionDifferent'.

NB: If on this property only the database name is specified that should be included with the application, any changes made during the application run, whilst they will be reflected in your application, the problem is that the next time you start the application, all your updates will not exist.

Asynchronous Results

The results of calls to the database are returned asynchronously. That means that after a statement involving the database is executed, the next statement in the program is executed - even though the database operation may not be complete. The database operation continues on its own. When it finishes, it will call a function you specify in your program, depending on the success or failure of the operation. In the meantime, you can do other processing: update controls on the screen or do calculations. You cannot do a MsgBox statement while you are waiting for an SQL call to finish - you will lose the callback.

Consider the following code:

   MsgBox "Before doing call"
   sqlList="CREATE TABLE myData('name', 'age')"
   sql(DB, [sqlList, Success_Function, Fail_Function])
   Msgbox "After doing call"

   Function Success_Function(transaction, result)
     Msgbox "Success - result received"
   End function

   Function Fail_Function(transaction, result)
     Msgbox "Fail"
   End function

The result will be 3 messages, in this order:

   "Before doing call"
   "After doing call"
   "Success - result received"

Transactions

When doing multiple operations on a database, for example, adding a number of new records, it is much faster to group them into a transaction. A transaction is a list of database operations that are performed as a set: each of the operations on the list has to successful. If any one step fails, the entire transaction can be aborted without change to the database.

A transaction is implemented as an array, with one operation in each element of the array.

Example: Add 3 new records into customerData

   sqlList = []
   sqlList[0]="INSERT INTO customerData (name,age,sales) VALUES ('Haley',16,121)"
   sqlList[1]="INSERT INTO customerData (name,age,sales) VALUES ('Alex',12,80)"
   sqlList[2]="INSERT INTO customerData (name,age,sales) VALUES ('Luke',7,65)"

Each operation in a transaction can have optional success and fail callbacks. In this case, the element in transaction array is itself an array:

[operation, success, fail] , where operation is a string. success and fail are functions in the program. You are required to have at least a success function defined in your program.

Example:

  sqlList[3]=array("Select * from customerData", Success_Function, Fail_Function)

Opening the database file using SqlOpenDatabase()

SQLite keeps the entire database in a single physical file. Use the SqlOpenDatabase method to open the file. If it does not exist, it is created:

   DB = SqlOpenDatabase("customers.db","1.0","My Customer Database")
   If DB.version<>"" Then Print "Database Opened"

The return value, DB, will be used to identify the database in subsequent calls. If the database does not exist, it is created. Here is the full syntax of SqlOpenDatabase:

res = SqlOpenDatabase(filename, version, fullname, maxsize)

where

• filename is the actual name of the file
• version is the version number (optional). Used on file creation. Set to "1.0" if no value supplied.
• fullname is a long description of the file. (optional). Used on file creation. Set to filename if no value supplied.
• maxsize is the maximum number of records (optional). Used on file creation. Set to 1,000,000 if no value supplied.

Create a table in the database

After step 1 creates a new database file, it is empty. A database is made up of tables. A table is simply a collection of records with a defined format. Let's create the table, but first, let's get rid of any existing table with the same name.

  sqlList=[]
  sqlList[0]=["DROP TABLE customerData;",,skipError]
  sqlList[1]=["CREATE TABLE customerData('name', 'age', 'sales', PRIMARY KEY('name') );"]

  Sql(DB, sqlList)

We're creating a table with three columns, indexed by the first name: each record therefore has 3 fields. The Sql statement is used to send a transaction (a list of SQL commands) to SQLite. The syntax of Sql is:

 Sql(db, sqlList)

where

• db is the reference to the database returned by SqlOpenDatabase
• sqList is an SQLite command or an array of SQLite commands.

Add records to the database and select them

  For j = 0 to databaseSize-1
    sqlList[j]="INSERT INTO customerData (name, age, sales) VALUES ( " & "'cust" & j & "', " & j & ", " & j*10 & ");"
  Next
  sqlList[databaseSize]=["SELECT * from customerData ORDER BY name;", dataHandler]

  Sql(DB, sqlList)

The SELECT command is very powerful - you can use it to collect all kinds of different sets of records. Read more about it in the [SQLite.htm SQL Documentation].

In the SELECT command, we're specifying that we want to run a special function when the SELECT completes. The function datahandler will be called. It gives the result of the SELECT:

  Function dataHandler(transaction, results)
    // Called on completion of SQL command
    DBRecords = results
    message.innerHTML = "Records created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & " milliseconds."
  End Function

In dataHandler, transaction contains information about the last transaction. Results is an array, with one row for each record of the table that is returned.

Replace a record in the table

  cmd="INSERT OR REPLACE INTO ""customerData"" VALUES (""" & tbFirstName.text & """,""" & _
    tbLastName.text &""",""" & tbAge.text & """)"
  Sql(DB, cmd)

Once again, all the quotes get resolved in the command. The actual command sent to SQLite is:

  INSERT OR REPLACE INTO "customerData" VALUES("John","Kemeny","80")

Using the last name as the key, the record is added or replaced. Once again, there are many more options on this command: we're just trying to get you started here. Once you have added the record, you'll need to do your selection again. It won't be automatically added to the existing selection.

Update a record

Let's start with the simplest case:

Function HeaderBar1_onclick(button)
  s=Array(["UPDATE Customers SET CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid])
  Sql(DB,s)
End Function

You need to extend it in two ways: update more fields, and update more rows.

To update more fields, add more to the SET clause:

s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])

To update more rows, add a new array element for each row:

s=Array(["UPDATE Customers SET CompanyName='NewCo', City='Toronto' WHERE CustomerID='ALFKI';", loadGrid])
s.push(["UPDATE Customers SET CompanyName='NewCo2', City='New York' WHERE CustomerID='ANATR';", loadGrid])

Delete a record

This is pretty easy. Do not use success or fail routine with DELETE.

  cmd="DELETE FROM customerData WHERE lastname = """ & tbLastName.Text & """"
  Sql(DB, cmd)

The command sent to SQLite resolves to:

  DELETE FROM customerData WHERE lastname = "Kemeny"

Closing the Database

When you are done using a database, you can close it.

  DB.close

Complete Sample

'This sample shows how to access SQLite from AppStudio. It can also be found in the Samples folder 2: it is SQLSample1.

Dim databaseSize  'number of records in the database
Dim DB            'the database itself
Dim startTime     'used for timer
Dim lastSQL       'the last SQL statement. Useful for debugging.
Dim total         'use to total sales from customers
Dim DBRecords     'the last set of records selected from DB

databaseSize=1000

Function btnCreate_onclick()
  total=0
  initDB
End Function

Function btnCount_onclick()
  total=0
  startTime=SysInfo(10)
  For i=0 To DBRecords.rows.length-1
    total=total +  DBRecords.rows.item(i)["sales"]
  Next
  txtMessage.value = "Total of " & DBRecords.rows.length & " recs is " & total & " in " & _
    (Sysinfo(10)-startTime) & " milliseconds."
End Function

Function btnRandom_onclick()
  Dim sqlList
  startTime=SysInfo(10)
  total=0
  sqlList = []
  For j=0 To databaseSize-1
    sqlList[j]=["SELECT * FROM customerData WHERE name='cust" & Fix(Rnd * databaseSize) &  "'",Random_Total]
  Next

  sqlList[databaseSize-1][1]=Random_Complete
  Sql(DB, sqlList)
End Function

Function Random_Total(transaction, results)
  total=total +  results.rows.item(0)["sales"]
End Function

Function Random_Complete(transaction, results)
  txtMessage.value =  "Total of " & databaseSize & " random recs is " & total & " in " & _
    (Sysinfo(10)-startTime) & " milliseconds."
End Function

Function initDB()
  Dim sqlList
  DB = SqlOpenDatabase("customerdb.db","1.0","My Customer Database")
  If DB<>0 Then
    startTime=SysInfo(10)
    nTables = -1
    sqlList=[]
    sqlList[0]=["SELECT COUNT(*) AS tabCount FROM sqlite_master WHERE type='table' AND name='customerData';",tableExistCheck,masterErr]
    Sql(DB, sqlList)
  End If
End Function

Function dataHandler(transaction, results)
  // Called On completion of Sql command
  DBRecords = results
  txtMessage.value = "Recs created: " & DBRecords.rows.length & " in " & (Sysinfo(10)-startTime) & _
    " milliseconds."
End Function

Function skipError(transaction, error)
  //Called On failure of Sql command
  MsgBox "SQL Error: " & error.message & " (" & error.code & ")"
End Function

Function btnDelete_onclick()
  Dim SQList
  SQList=[]
  'Note no success or fail callbacks!
  SQList[0]=["DELETE FROM customerData"]
  SQList[1]=["SELECT * from customerData ORDER BY name;", dataHandler]
  Sql(DB, SQList)
  txtMessage.value = "All rows deleted."
End Function

Using the Chrome Debugger to examine databases

By opening the Chrome Debugger and using the Resource Tab, you can examine the database. The following example shows a database named customers with a single record containing John Doe's name, age and sales:

Clearing a Locally-Deployed App Incorporating an SQLite Database From a PC

Clearing a remotely-deployed app's data and SQLite DB from a browser simply involves clearing the browser's cached app data or content for that app's domain via the settings panel. However, for a locally-deployed app the process is more involved because the various pieces are stored in multiple locations on the PC.

Here's a way to eliminate all traces of a locally-deployed app so that it will behave as a freshly installed app on the next deploy:

1. In Chrome Debugger's console, with the app currently deployed locally, delete all localStorage variables with the localStorage.clear() command. Use the Resources tab, Local Storage, file:// to confirm success.

2. Find the special location where Windows stores SQLite (and other) databases and delete that folder with its contents:

• Windows Vista or 7: \Users\_username_\AppData\Local\Google\Chrome\User Data\Default\databases
• Windows XP: \Documents and Settings\_username_\Local Settings\Application Data\Google\Chrome\User Data\Default\databases
• Mac OS X: ~/Library/Application Support/Google/Chrome/Default/databases
• Linux: ~/.config/google-chrome/Default/databases

3. Delete the app files themselves (html, /nsb folder, etc.) from the Windows system. The location for these is found in the URL/Address window of the Chrome browser itself. Just drill down using the file explorer and delete the temp folder being used.

Contributed by c185driver