Browser SQLite Stencil Component
npm install jeep-sqlitejeep-sqliteis a Stencil component to create SQLite database and query it in the browser. The entire database is stored in an IndexedDB store named jeepSQLiteStore in a table databases. Multiple databases can be stored on this table.
jeep-sqlite is based on sql.jsfor SQLite queries and localforagefor database storage in IndexedDB.
This component might be used in PWA applications. It will also be used in the web implementation of the @capacitor-community-sqlite.
This is the reason for having similar API than the @capacitor-community-sqlite. Look at the @capacitor-community-sqlitedocumentation and CHANGELOG.md
It will be used at that stage to test the integration with the @capacitor-community-sqlite but can also be used in development of Stencil or Ionic/Angular applications.
Integration in other frameworks (Vue, React, Ionic/Vue, Ionic/React,SolidJS) are alos available.
Stencil is also great for building entire apps. For that, use the stencil-app-starter instead.
!!!!!!!!!!!!!!!!!!!!!!!!!!
When you get this message
```
I am getting the following error in console in ionic Application.
Error: Uncaught (in promise): TypeError: x is not a function
TypeError: x is not a function
at x (jeep-sqlite.entry.js:2648:80)
at f.onRuntimeInitialized (jeep-sqlite.entry.js:2555:318)
...
means that you have to copy the sql-wasm.wasm from node_modules/sql.js/dist/sql-wasm.wasm to your application assets directory
!!!!!!!!!!!!!!!!!!!!!!!!!!
- Release 2.5.6 ->>
Step back to sql.js@1.8.0 as sql.js@1.9.0 give an Error: out of memory see issue #33.
- Release 2.5.0 ->>
- add methods to manage the transaction process flow :
beginTransaction, commitTransaction, rollbackTransaction,
isActiveTransaction see index_transaction.html
- upgrade to @stencil/core@4.0.5
- Release 2.3.8 ->>
- add support for RETURNING in sqlite statement
- Release 2.3.2 ->>
- add property pickText to customize the pick button text.saveText
- add property to customize the save button text.buttonOptions
- add property to customise the button style.jeepSqliteSaveDatabaseToDisk
- add - From Local Disk to Store - to Usage chapter.
- Release 2.3.1 ->>
- add event listener.
- Release 2.3.0 ->>
Use of the File System Access API through the Browser-FS-Access module.getFromLocalDiskToStore
- add : read a database from your local disk and save it to the IndexedDB jeepSqliteStore store.saveToLocalDisk
- add : save a database to your local disk will allows developers to view the database in separate DB tools like DB Browser for Sqlite.jeepSqlitePickDatabaseEnded
- add event listener.index_getFromLocalDiskToStore.html
- add to demonstrate the use of the two new methods.
- Release 2.1.0, 2.2.0->> DEPRECATED
- Release 1.6.6 ->>
fix WAL mode for concurrency access to databases. WAL2 is not supported
- Release 1.6.4 ->>
add jeepSqliteHTTPRequestEnded event listener
- Release 1.6.3 ->>
add getFromHTTPRequest to get database or zip containing multiple database files from a remote server.
- Release 1.6.2 ->>
add database read-only mode
- Release 1.6.0 ->>
Update sql.js@1.8.0
- Release 1.5.8 ->>
The API method addUpgradeStatement has been modified to define the new structure of the database as a list of incremental upgrades. Every upgrade is executed over the previous version.
see https://github.com/capacitor-community/sqlite/blob/master/docs/UpgradeDatabaseVersion.md
- Release 1.5.7 ->>
The path for the sql-wasm.wasm file which is by default /assets can now be specified by adding the property wasmPath to jeep-sqlite
- default
``
- given the wasm file path
``
- Release 1.5.0 ->>
The main change is related to the delete table's rows when a synchronization table exists as well as a last_mofidied table's column, allowing for database synchronization of the local database with a remote server database.
- All existing triggers to YOUR_TABLE_NAME_trigger_last_modified must be modified as follows
``
CREATE TRIGGER YOUR_TABLE_NAME_trigger_last_modified
AFTER UPDATE ON YOUR_TABLE_NAME
FOR EACH ROW WHEN NEW.last_modified < OLD.last_modified
BEGIN
UPDATE YOUR_TABLE_NAME SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
END;
sql_deleted
- an new column must be added to each of your tables as`
`
sql_deleted BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0, 1))
DELETE FROM ...
This column will be autommatically set to 1 when you will use a sql statement in the execute, run or executeSet methods.
- In the JSON object that you provide to importFromJson, all the deleted rows in your remote server database's tables must have the sql_deleted column set to 1. This will indicate to the import process to physically delete the corresponding rows in your local database. All the others rows must have the sql_deleted column set to 0.
- In the JSON object outputs by the exportToJson, all the deleted rows in your local database have got the sql_deleted column set to 1 to help in your synchronization management process with the remote server database. A system last_exported_date is automatically saved in the synchronization table at the start of the export process flow.
- On successfull completion of your synchronization management process with the remote server database, you must
- Set a new synchronization date (as (new Date()).toISOString()) with the setSyncDate method.deleteExportedRows
- Execute the method which physically deletes all table's rows having 1 as value for the sql_deleted column prior to the last_exported_date in your local database.
An example of using this new feature is given in the index_delete.html file. It has been used to test the validity of the implementation.
- Put a script tag similar to this
`html`
in the head of your index.html
- Then you can use the element anywhere in your template, JSX, html etc
- Put a script tag similar to this
in the head of your index.html
- Then you can use the element anywhere in your template, JSX, html etc$3
- Run npm install jeep-sqlite --save
- Add an import to the npm packages import jeep-sqlite;
- Then you can use the element anywhere in your template, JSX, html etcSupported methods
| Name | Web |
| :-------------------------- | :------ |
| echo | ✅ |
| createConnection | ✅ |
| closeConnection | ✅ |
| isConnection | ✅ |
| open (non-encrypted DB) | ✅ |
| close | ✅ |
| execute | ✅ |
| executeSet | ✅ |
| run | ✅ |
| query | ✅ |
| deleteDatabase | ✅ |
| isDBExists | ✅ |
| isDBOpen | ✅ |
| isStoreOpen | ✅ |
| isTableExists | ✅ |
| getVersion | ✅ |
| createSyncTable | ✅ |
| getSyncDate | ✅ |
| setSyncDate | ✅ |
| isJsonValid | ✅ |
| importFromJson | ✅ |
| exportToJson | ✅ |
| deleteExportedRows | ✅ | NEW in 1.5.0
| copyFromAssets | ✅ |
| addUpgradeStatement | ✅ |
| isDatabase | ✅ |
| getDatabaseList | ✅ |
| getTableList | ✅ |
| checkConnectionsConsistency | ✅ |
| saveToStore | ✅ |
| getFromHTTPRequest | ✅ | New in 1.6.3
| getFromLocalDiskToStore | ✅ | New in 2.3.0
| saveToLocalDisk | ✅ | New in 2.3.0
The database is saved when you run the methods
closeor closeConnection, in the Browser Storage IndexedDB as a localforage store under the jeepSqliteStore name and databasestable name.The
copyFromAssets required to have a databases.jsonfile having the name of the databases in the assets/databases The
databases.json file is for example
`json
{
"databaseList" : [
"dbForCopy.db",
"myDBSQLite.db"
]
}
`
if dbForCopy.db and myDBSQLite.db are databases located in the assets/databases folder.Supported Events
| Name | Web |
| :--------------------------- | :------ |
| jeepSqliteImportProgress | ✅ |
| jeepSqliteExportProgress | ✅ |
| jeepSqliteHTTPRequestEnded | ✅ |
| jeepSqlitePickDatabaseEnded | ✅ |
| jeepSqliteSaveDatabaseToDisk | ✅ |
Usage
`html
Stencil Component Starter
`$3
A database can be read from the local disk by using the
File System Access API`, save to the store, open through a standard connection.Standard CRUD operations can then being performed, the modifications are save to the store for persistent storage during the session on user's request or at the connection close.
Before closing the connection, the database might also be saved back to the local disk for secure persistent storage (backup) and/or database visibility through the use of SQLite third party tools like DB Browser for SQLite.