官网
https://sql.js.org/#/
写入
let request = window.indexedDB.open("test1",1);
let idb;
request.onerror = (event)=>console.log(event)
request.onsuccess = function (event) {
idb = request.result;
console.log('数据库打开成功');
};
request.onupgradeneeded = function (event) {
idb = event.target.result;
var objectStore;
if (!idb.objectStoreNames.contains('sqlite')) {
objectStore = idb.createObjectStore('sqlite', { keyPath: 'id' });
}
console.log("onupgradeneeded 数据库更新")
}
wasmSQL = document.createElement("script")
wasmSQL.src = "https://sql.js.org/dist/sql-wasm.js"
document.body.appendChild(wasmSQL)
await setTimeout(()=>{
let config = {
locateFile: () => "https://sql.js.org/dist/sql-wasm.wasm",
};
initSqlJs(config).then((SQL) => {
let db = new SQL.Database();
db.run("CREATE TABLE test (col1, col2);");
db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);
const stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
stmt.getAsObject({$start:1, $end:1});
stmt.bind({$start:1, $end:2});
while(stmt.step()) { //
const row = stmt.getAsObject();
console.log('Here is a row: ' + JSON.stringify(row));
}
console.log(db.export());
console.log("写入数据库")
request = idb.transaction(["sqlite"],"readwrite").objectStore("sqlite").put({id:"testDb",data:db.export()})
request.onsuccess = ()=>console.log("写入成功")
request.onerror = (event)=>console.log(event, "写入失败")
idb.close()
});
},1000)
读取
worker = new Worker(location.origin + "/ncda/sql/worker.sql-wasm.js")
request = window.indexedDB.open("test", 2);
var idb;
request.onerror = (event)=>console.log(event)
request.onsuccess = function (event) {
idb = request.result;
console.log('数据库打开成功');
};
request.onupgradeneeded = function (event) {
idb = event.target.result;
var objectStore;
if (!db.objectStoreNames.contains('sqlite')) {
objectStore = db.createObjectStore('sqlite', { keyPath: 'id' });
}
}
os = idb.transaction(["sqlite"],"readonly").objectStore("sqlite")
dbRequest = os.get("testDb")
var dbFile
dbRequest.onsuccess = (event)=>{
console.log("获取成功");
if(event.target.result){
dbFile = event.target.result.data
}
worker.onmessage = (event)=>{console.log(event, "加载数据库")}
worker.postMessage({
action: "open",
buffer: dbFile
})
worker.onmessage = (event)=>console.log("执行结果", event.data)
worker.postMessage({
action: "exec",
sql: "SELECT * FROM test"
})
}
保存
worker.onmessage = function (event) {
var arraybuff = event.data.buffer;
var blob = new Blob([arraybuff]);
var a = document.createElement("a");
document.body.appendChild(a);
a.href = window.URL.createObjectURL(blob);
a.download = "sql.db";
a.onclick = function () {
setTimeout(function () {
window.URL.revokeObjectURL(a.href);
}, 1500);
};
a.click();
};