Using SQLite with Chrome 119: Difference between revisions

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


There are a couple of workarounds. In the short run, you can register your site for an [[Origin Trialhttps://googlechrome.github.io/OriginTrials/developer-guide.html|Origin Trial]], which restore the functionality until May 28, 2024 when Chrome 124 is scheduled to appear. Directions how to do this follow.
Here is how to workaround this:


For a permanent solution, you will need to include the SQLite code as a library in your project, since it will no longer be built into the browser. This solution is referred to as SQLite WASM: we will be providing instructions how to use it in the near future.
=== Workaround 1. Use SQLite3 WASM to save to localStorage ===


'''How to Register Your App to Continue to Use SQLite'''
This requires AppStudio 9.0.4 or later.


1. Go to this page: [[Register to WebSQL Trialhttps://developer.chrome.com/origintrials/#/register_trial/|Register to WebSQL Trial]]
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.


2. Fill in the URL for your site in the Web Origin. For example. https://www.nsbasic.com
AppStudio makes this easy. To enable it, select SQLite (WASM) in the Libraries section of your Project Properties:


3. Check the 4 square disclosure boxes.
[[File:Libraries-SQLite WASM.png|none|500px]]


4. You may also need to Sign In into Google.
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.
<pre>
DB = SqlOpenDatabase("localStorage")
</pre>


5. You will then see a screen like this:
The samples SQLSample1, SQLSample2 and SQLSample3 have been updated with to use SQLite WASM.
[[File:WebSQL Registration.png|thumb]]


The data saved in [https://developer.mozilla.org/en-US/docs/Web/API/Window/localStorage localStorage] is persistent.


==== New Features ====


1. <code>DB.clearStorage()</code> wipes out the database you created in localStorage.


2. <code>DB.storageSize()</code> reports how localStorage you are using.


6. Copy the Token from that screen.
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>
  DB.clearStorage()
  DB.exec("VACUUM INTO 'file:local?vfs=kvvfs'")
</pre>


7. In AppStudio Project Properties, go to ExtraHeaders and add this line:
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:
<pre>
<pre>
<meta http-equiv="origin-trial" content="Avihurku4HHj...">
// 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)
</pre>
</pre>
8. Your app should now work properly.
 
==== Restrictions ====
 
# The limit on localStorage is 10 megs (currently; on Chrome).
# You can only have one persistent database, in localStorage.
# Other databases will be cleared when the page is reloaded.
# 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.
# You will have to export your old database and import it to the new one yourself. There is no way to do this automatically.
# The library adds about 1.4 megs to your app.
# [[SqlImport|SQLImport]] and SQLExport are not supported yet.
 
This workaround continues to evolve and is subject to change.
 
=== Workaround 2. (under construction) ===

Revision as of 14:43, 28 April 2024

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, check out this post).

Here is how to workaround this:

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

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)

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. You will have to export your old database and import it to the new one yourself. There is no way to do this automatically.
  6. The library adds about 1.4 megs to your app.
  7. SQLImport and SQLExport are not supported yet.

This workaround continues to evolve and is subject to change.

Workaround 2. (under construction)

Retrieved from "https://wiki.appstudio.dev/index.php?title=Using_SQLite_with_Chrome_119&oldid=11521"