Support for SQLite: Difference between revisions

From NSB App Studio
Jump to navigation Jump to search
No edit summary
 
(26 intermediate revisions by the same user not shown)
Line 1: Line 1:
Starting with Chrome 119, Google has deprecated SQlite, also referred to as WebSQL. They have been warning of this for a while. (If you're interested in why they are doing this, [https://groups.google.com/a/chromium.org/g/blink-dev/c/fWYb6evVA-w/m/pziWcvboAgAJ?pli=1 check out this post]).
Google has deprecated SQlite, also referred to as WebSQL. They have been warning of this for a while. (If you're interested in why they did this, [https://groups.google.com/a/chromium.org/g/blink-dev/c/fWYb6evVA-w/m/pziWcvboAgAJ?pli=1 check out this post]).


There are several workarounds:
Here is how to workaround:


===== Workaround 1. Set a flag in Chrome =====
=== Workaround 1. Use SQLite3 WASM to save to localStorage ===


# Open chrome://flags/ in Chrome
This requires AppStudio 9.0.4 or later.
# Search for web-sql-access
# Enable it
# relaunch browser


This is probably the quickest solution for most users. It will probably only work until Chrome 124 is released - by that time, you will need a more permanent solution in place.
The solution is to include the SQLite3 libraries with your project. A [https://sqlite.org/wasm/doc/trunk/index.md library] has been developed which provides the SQLite3 functionality for JavaScript.


===== Workaround 2. Register your site for an Origin Trial =====
AppStudio makes this easy. To enable it, select SQLite (WASM) in the Libraries section of your Project Properties:


You can register your site for an [https://googlechrome.github.io/OriginTrials/developer-guide.html Origin Trial], which restores the functionality until May 28, 2024 when Chrome 124 is scheduled to appear. The advantage of this solution is that you only have to do this once: your users do not have to make any changes at their end.
[[File:Libraries-SQLite WASM.png|none|500px]]


# Go to this page: [https://developer.chrome.com/origintrials/#/register_trial/ Register to WebSQL Trial]
If you're using AppStudio's [[Sql]] and [[SqlOpenDatabase]], only your SqlOpenDatabase statement will need to be changed: your database name should be localStorage to save your data between runs. It is based on [https://sqlite.org/wasm/doc/trunk/persistence.md#kvvfs kvvfs]. [[SqlImport]] and [[sqlExport]] are not supported yet.
# Fill in the URL for your site in the Web Origin. For example: https://www.nsbasic.com
# Check the 4 square disclosure boxes.
# You may also need to Sign In into Google.
# You will then see a screen like this:
[[File:WebSQL Registration.png|none|500px]]
# Copy the '''Token''' from that screen.
# In AppStudio Project Properties, go to '''ExtraHeaders''' and add this line:
<pre>
<pre>
<meta http-equiv="origin-trial" content="Avihurku4HHj...">
DB = SqlOpenDatabase("localStorage")
</pre>
</pre>
# Your app should work properly now.


===== Workaround 3. SQLite3 WASM (AppStudio 9) =====
The samples SQLSample1, SQLSample2 and SQLSample3 have been updated with to use SQLite WASM.


A permanent solution is to include the SQLite3 library with your project. A [https://sqlite.org/wasm/doc/trunk/index.md library] has been developed which provides the SQLite3 functionality for JavaScript.
The data saved in [https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage localStorage] is persistent.


AppStudio makes this easy. To enable it, select SQLite WASM in the Libraries section of your Project Properties:
==== New Features and Notes ====


[[File:Libraries-SQLite WASM.png|none|500px]]
1. <code>DB.clearStorage()</code> wipes out the database you created in localStorage.
 
2. <code>DB.storageSize()</code> reports how localStorage you are using.


If you're using AppStudio's [[Sql]] and [[SqlOpenDatabase]], only your SqlOpenDatabase statement will need to be changed: your database name should be localStorage or sessionStorage. It is based on [https://sqlite.org/wasm/doc/trunk/persistence.md#kvvfs kvvfs]. [[SqlImport]] and [[sqlExport]] are not supported yet.
3. Compressing localStorage - The database in localStorage is updated whenever you update the database. However, garbage is not collected. You can clean it up (and save some space) by doing the following:
<pre>
<pre>
DB = SqlOpenDatabase("localStorage")
  DB.clearStorage()
DB = SqlOpenDatabase("sessionStorage")
  DB.exec("VACUUM INTO 'file:local?vfs=kvvfs'")
</pre>
</pre>


The data saved in [https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage localStorage] is persistent. Data saved in [https://developer.mozilla.org/en-US/docs/Web/API/Window/sessionStorage sessionStorage] is cleared when the page session ends.
4. The [[Sql]] function is now synchronous - meaning it executes completely before the next statement is executed. In most cases, this will make no difference to your code. We did find an error in one of our samples:
 
The [[Sql]] function is now synchronous - meaning it executes completely before the next statement is executed. In most cases, this will make no difference to your code. We did find an error in one of our samples:
<pre>
<pre>
// No longer works as expected
// No longer works as expected
   sqlList[0]=["SELECT orderno, customer FROM tabOrders;",showOrderSuccess, sqlErr]
   sqlList[0]=["SELECT orderno, customer FROM tabOrders;"]
   Sql(DB,sqlList)
   Sql(DB,sqlList)
   sqlList = []
   sqlList = []
Line 58: Line 47:
</pre>
</pre>


There are restrictions to keep in mind:
5. Double quotes are not allowed in SQL statements. For example:
<pre>
...AND customer_type = "T"...
</pre>
should be
<pre>
...AND customer_type = 'T'...
</pre>
 
6. You'll see some new error messages in the console. These can be ignored:
[[File:Screenshot 2024-05-22 at 9.25.55 AM.png|SQLite WASM Console messages]]
 
==== Restrictions ====
 
# The limit on localStorage is 10 megs (currently; on Chrome).
# The limit on localStorage is 10 megs (currently; on Chrome).
# You can only have one database. (2 if you can use sessionStorage - but it's not persistent)
# You can only have one persistent database, in localStorage.
# You cannot use SQLite WASM and native WebSQL at the same time.
# Other databases will be cleared when the page is reloaded.
# You will have to export your old database and import it to the new one yourself. There is no way to do this automatically.
# You cannot use SQLite WASM and Cordova plugins at the same time: Use SQLite WASM for desktop apps and a Cordova plugin for mobile apps.
# The library adds about 1.4 megs to your app.
# The library adds about 1.4 megs to your app.
# You can test using '''Start in Desktop Browser''', but not '''Deploy to Local Folder'''.
This workaround continues to evolve and is subject to change.
=== Workaround 2. OPFS ===
It's possible to use OPFS to save the database to an actual file, which removes the restrictions on database size and number.
If you're using OPFS, there are a couple of utilities which allow you to examine the database. [https://chromewebstore.google.com/detail/opfs-explorer/acndjpgkpaclldomagafnognkcgjignd?pli=1 OPFS Explorer] is a browser extension which can be used to save your database to your computer's file system as an SQLite database. [https://sqlitebrowser.org/ DB Browser for SQlite] is a standalone app which lets you create, search, and edit SQLite database files.

Latest revision as of 12:28, 9 August 2024

Google has deprecated SQlite, also referred to as WebSQL. They have been warning of this for a while. (If you're interested in why they did this, check out this post).

Here is how to workaround:

Workaround 1. Use SQLite3 WASM to save to localStorage

This requires AppStudio 9.0.4 or later.

The solution is to include the SQLite3 libraries with your project. A library has been developed which provides the SQLite3 functionality for JavaScript.

AppStudio makes this easy. To enable it, select SQLite (WASM) in the Libraries section of your Project Properties:

If you're using AppStudio's Sql and SqlOpenDatabase, only your SqlOpenDatabase statement will need to be changed: your database name should be localStorage to save your data between runs. It is based on kvvfs. SqlImport and sqlExport are not supported yet.

DB = SqlOpenDatabase("localStorage")

The samples SQLSample1, SQLSample2 and SQLSample3 have been updated with to use SQLite WASM.

The data saved in localStorage is persistent.

New Features and Notes

1. DB.clearStorage() wipes out the database you created in localStorage.

2. DB.storageSize() reports how localStorage you are using.

3. Compressing localStorage - The database in localStorage is updated whenever you update the database. However, garbage is not collected. You can clean it up (and save some space) by doing the following:

  DB.clearStorage()
  DB.exec("VACUUM INTO 'file:local?vfs=kvvfs'")

4. The Sql function is now synchronous - meaning it executes completely before the next statement is executed. In most cases, this will make no difference to your code. We did find an error in one of our samples:

// No longer works as expected
  sqlList[0]=["SELECT orderno, customer FROM tabOrders;"]
  Sql(DB,sqlList)
  sqlList = []

// Proper way to do this
  sqlList = []
  sqlList[0]=["SELECT orderno, customer FROM tabOrders;",showOrderSuccess, sqlErr]
  Sql(DB,sqlList)

5. Double quotes are not allowed in SQL statements. For example:

...AND customer_type = "T"...

should be

...AND customer_type = 'T'...

6. You'll see some new error messages in the console. These can be ignored: SQLite WASM Console messages

Restrictions

  1. The limit on localStorage is 10 megs (currently; on Chrome).
  2. You can only have one persistent database, in localStorage.
  3. Other databases will be cleared when the page is reloaded.
  4. You cannot use SQLite WASM and Cordova plugins at the same time: Use SQLite WASM for desktop apps and a Cordova plugin for mobile apps.
  5. The library adds about 1.4 megs to your app.
  6. You can test using Start in Desktop Browser, but not Deploy to Local Folder.


This workaround continues to evolve and is subject to change.

Workaround 2. OPFS

It's possible to use OPFS to save the database to an actual file, which removes the restrictions on database size and number.

If you're using OPFS, there are a couple of utilities which allow you to examine the database. OPFS Explorer is a browser extension which can be used to save your database to your computer's file system as an SQLite database. DB Browser for SQlite is a standalone app which lets you create, search, and edit SQLite database files.