SQLite的一个JavaScript移植：sql.js
jopen
9年前
sql.js是SQLite的一个JavaScript移植，用Emscripten编译SQLite C 代码,这里没有C绑定或node-gyp编译。
SQLite is public domain, sql.js is MIT licensed.
用法
var sql = require('sql.js'); // or sql = window.SQL if you are in a browser // Create a database var db = new sql.Database(); // NOTE: You can also use new sql.Database(data) where // data is an Uint8Array representing an SQLite database file // Execute some sql sqlstr = "CREATE TABLE hello (a int, b char);"; sqlstr += "INSERT INTO hello VALUES (0, 'hello');" sqlstr += "INSERT INTO hello VALUES (1, 'world');" db.run(sqlstr); // Run the query without returning anything var res = db.exec("SELECT * FROM hello"); /* [ {columns:['a','b'], values:[[0,'hello'],[1,'world']]} ] */ // Prepare an sql statement var stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval"); // Bind values to the parameters and fetch the results of the query var result = stmt.getAsObject({':aval' : 1, ':bval' : 'world'}); console.log(result); // Will print {a:1, b:'world'} // Bind other values stmt.bind([0, 'hello']); while (stmt.step()) console.log(stmt.get()); // Will print [0, 'hello'] // free the memory used by the statement stmt.free(); // You can not use your statement anymore once it has been freed. // But not freeing your statements causes memory leaks. You don't want that. // Export the database to an Uint8Array containing the SQLite database file var binaryArray = db.export();示例
There is an online demo available here : http://kripken.github.io/sql.js/GUI
Examples
The test files provide up to date example of the use of the api.
Inside the browser
Example HTML file:
<script src='js/sql.js'></script> <script> //Create the database var db = new SQL.Database(); // Run a query without reading the results db.run("CREATE TABLE test (col1, col2);"); // Insert two rows: (1,111) and (2,222) db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]); // Prepare a statement var stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end"); stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111} // Bind new values stmt.bind({$start:1, $end:2}); while(stmt.step()) { // var row = stmt.getAsObject(); // [...] do something with the row of result } </script>Creating a database from a file choosen by the user
SQL.Databaseconstructor takes an array of integer representing a database file as an optional parameter. The following code uses an HTML input as the source for loading a database:
dbFileElm.onchange = function() { var f = dbFileElm.files[0]; var r = new FileReader(); r.onload = function() { var Uints = new Uint8Array(r.result); db = new SQL.Database(Uints); } r.readAsArrayBuffer(f); }See : http://kripken.github.io/sql.js/GUI/gui.js
Loading a database from a server
var xhr = new XMLHttpRequest(); xhr.open('GET', '/path/to/database.sqlite', true); xhr.responseType = 'arraybuffer'; xhr.onload = function(e) { var uInt8Array = new Uint8Array(this.response); var db = new SQL.Database(uInt8Array); var contents = db.exec("SELECT * FROM my_table"); // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}] }; xhr.send();