Skip to content

Commit 55b6177

Browse files
lovasoalovasoa
committed
Merge pull request sql-js#45 from lovasoa/master
* Update SQLite to 3.8.5 * Change the API * Add support for prepared statements * Add support for BLOBs * Compile to asm.js * Many other things, see the commits
2 parents 2bd993e + 7356d05 commit 55b6177

38 files changed

+232472
-20013
lines changed

‎.travis.yml

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
language: node_js
2+
node_js:
3+
- "0.10"

‎AUTHORS

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
Ophir LOJKINE <pere.jobs@gmail.com> (https://github.com/lovasoa)
2+
@kripken
3+
@hankinsoft
4+

‎Makefile

Lines changed: 37 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,46 @@
11
# Need $(EMSCRIPTEN), for example run with emmake make
22

3-
EMCC=$(EMSCRIPTEN)/emcc -s RESERVED_FUNCTION_POINTERS=2 -O2 --closure 1 -s ASM_JS=0
4-
# -s INLINING_LIMIT=0
5-
CFLAGS=-DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0
3+
EMSCRIPTEN?=/usr/bin
64

7-
all: js/sql.js test/benchmark.js test/benchmark
5+
EMCC=$(EMSCRIPTEN)/emcc
86

9-
js/sql.js: c/sqlite3.c
10-
$(EMCC) $(CFLAGS) c/sqlite3.c c/main.c --pre-js js/pre.js --post-js js/post.js -o js/sql.js -s EXPORTED_FUNCTIONS="['_sqlite3_open', '_sqlite3_close', '_sqlite3_exec']"
7+
CFLAGS=-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0
118

12-
test/benchmark.js: c/sqlite3.c c/benchmark.c
13-
$(EMCC) $(CFLAGS) c/sqlite3.c c/benchmark.c -o test/benchmark.js
9+
all: js/sql.js
1410

15-
test/benchmark: c/benchmark.c
16-
gcc $(CFLAGS) -pthread -O2 c/sqlite3.c c/benchmark.c -o test/benchmark -ldl
11+
debug: EMFLAGS= -O1 -g -s INLINING_LIMIT=10
12+
debug: js/sql-debug.js
13+
14+
optimized: EMFLAGS= --closure 1 -O3 -s INLINING_LIMIT=50
15+
optimized: js/sql-optimized.js
16+
17+
js/sql.js: optimized
18+
cp js/sql-optimized.js js/sql.js
19+
20+
js/sql%.js: js/shell-pre.js js/sql%-raw.js js/shell-post.js
21+
cat $^ > $@
22+
23+
js/sql%-raw.js: c/sqlite3.bc js/api.js exported_functions
24+
$(EMCC) $(EMFLAGS) -s EXPORTED_FUNCTIONS=@exported_functions c/sqlite3.bc --post-js js/api.js -o $@
25+
26+
js/api.js: coffee/api.coffee coffee/exports.coffee coffee/api-data.coffee
27+
coffee --bare --compile --join $@ --compile $^
28+
29+
# Web worker API
30+
worker: js/worker.sql.js
31+
js/worker.js: coffee/worker.coffee
32+
coffee --bare --compile --join $@ --compile $^
33+
34+
js/worker.sql.js: js/sql.js js/worker.js
35+
cat $^ > $@
36+
37+
c/sqlite3.bc: c/sqlite3.c
38+
# Generate llvm bitcode
39+
$(EMCC) $(CFLAGS) c/sqlite3.c -o c/sqlite3.bc
40+
41+
module.tar.gz: test package.json AUTHORS README.md js/sql.js
42+
tar --create --gzip $^ > $@
1743

1844
clean:
19-
rm js/sql.js test/benchmark.js test/benchmark
45+
rm -rf js/sql*.js js/api.js js/sql*-raw.js c/sqlite3.bc
2046

‎README.md

Lines changed: 169 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
# sql.js
1+
# SQLite compiled to javascript
2+
[![Build Status](https://travis-ci.org/lovasoa/sql.js.svg?branch=master)](http://travis-ci.org/lovasoa/sql.js)
3+
4+
This is my fork of sql.js, by kripken. Try it online here: http://lovasoa.github.io/sql.js/GUI/
25

36
sql.js is a port of SQLite to JavaScript, by compiling the SQLite C code with Emscripten.
47
no C bindings or node-gyp compilation here.
@@ -7,40 +10,174 @@ SQLite is public domain, sql.js is MIT licensed.
710

811
## Usage
912

10-
```coffeescript
11-
Sql = require 'node-sqlite-purejs'
12-
Sql.open 'db/development.sqlite', {}, (err, db) ->
13-
throw err if err
14-
db.exec '''
15-
/* Demo DB */
16-
CREATE TABLE employees( id integer, name text,
17-
designation text, manager integer,
18-
hired_on date, salary integer,
19-
commission float, dept integer);
20-
21-
INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);
22-
INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3);
23-
INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3);
24-
INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3);
25-
INSERT INTO employees VALUES (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4);
26-
INSERT INTO employees VALUES (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4);
27-
INSERT INTO employees VALUES (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4);
28-
INSERT INTO employees VALUES (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2);
29-
INSERT INTO employees VALUES (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4);
30-
INSERT INTO employees VALUES (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2);
31-
INSERT INTO employees VALUES (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2);
32-
INSERT INTO employees VALUES (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4);
33-
INSERT INTO employees VALUES (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2);
34-
INSERT INTO employees VALUES (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1);
35-
'''
36-
37-
db.exec "SELECT * FROM employees WHERE designation = 'CEO';", (err, results) ->
38-
assert.deepEqual [{"id":"9","name":"JACKSON","designation":"CEO","manager":"(null)","hired_on":"01-01-1990","salary":"75000","commission":"(null)","dept":"4"}], results
13+
```javascript
14+
var sql = require('./js/sql-api.js');
15+
// or sql = window.SQL if you are in a browser
16+
17+
// Create a database
18+
var db = new sql.Database();
19+
// NOTE: You can also use new sql.Database(data) where
20+
// data is an Uint8Array representing an SQLite database file
21+
22+
// Execute some sql
23+
sqlstr = "CREATE TABLE hello (a int, b char);";
24+
sqlstr += "INSERT INTO hello VALUES (0, 'hello');"
25+
sqlstr += "INSERT INTO hello VALUES (1, 'world');"
26+
db.run(sqlstr); // Run the query without returning anything
27+
28+
var res = db.exec("SELECT * FROM hello");
29+
/*
30+
[
31+
{columns:['a','b'], values:[[0,'hello'],[1,'world']]}
32+
]
33+
*/
34+
35+
// Prepare an sql statement
36+
var stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");
37+
38+
// Bind values to the parameters and fetch the results of the query
39+
var result = stmt.getAsObject({':aval' : 1, ':bval' : 'world'});
40+
console.log(result); // Will print {a:1, b:'world'}
41+
42+
// Bind other values
43+
stmt.bind([0, 'hello']);
44+
while (stmt.step()) console.log(stmt.get()); // Will print [0, 'hello']
45+
46+
// free the memory used by the statement
47+
stmt.free();
48+
// You can not use your statement anymore once it has been freed.
49+
// But not freeing your statements causes memory leaks. You don't want that.
50+
51+
// Export the database to an Uint8Array containing the SQLite database file
52+
var binaryArray = db.export();
53+
```
54+
55+
## Demo
56+
There is an online demo available here : http://lovasoa.github.io/sql.js/GUI
57+
58+
## Exemples
59+
The test files provide up to date example of the use of the api.
60+
### Inside the browser
61+
#### Example **HTML** file:
62+
```html
63+
<script src='js/sql.js'></script>
64+
<script>
65+
//Create the database
66+
var db = new SQL.Database();
67+
// Run a query without reading the results
68+
db.run("CREATE TABLE test (col1, col2);");
69+
// Insert two rows: (1,111) and (2,222)
70+
db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);
71+
72+
// Prepare a statement
73+
var stmt = db.prepare("SELECT * FROM test WHERE a BETWEEN $start AND $end");
74+
stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}
75+
76+
// Bind new values
77+
stmt.bind({$start:1, $end:2});
78+
while(stmt.step()) { //
79+
var row = stmt.getAsObject();
80+
// [...] do something with the row of result
81+
}
82+
</script>
83+
```
84+
85+
#### Creating a database from a file choosen by the user
86+
`SQL.Database` constructor takes an array of integer representing a database file as an optional parameter.
87+
The following code uses an HTML input as the source for loading a database:
88+
```javascript
89+
dbFileElm.onchange = function() {
90+
var f = dbFileElm.files[0];
91+
var r = new FileReader();
92+
r.onload = function() {
93+
var Uints = new Uint8Array(r.result);
94+
db = new SQL.Database(Uints);
95+
}
96+
r.readAsArrayBuffer(f);
97+
}
3998
```
99+
See : http://lovasoa.github.io/sql.js/GUI/gui.js
100+
101+
### Use from node.js
102+
103+
`sql.js` is [hosted on npm](https://www.npmjs.org/package/sql.js). To install it, you can simply run `npm install sql.js`.
104+
Alternatively, you can simply download the file `sql.js`, from the download link below.
105+
106+
#### read a database from the disk:
107+
```javascript
108+
var fs = require('fs');
109+
var SQL = require('sql.js');
110+
var filebuffer = fs.readFileSync('test.sqlite');
111+
112+
// Load the db
113+
var db = new SQL.Database(filebuffer);
114+
```
115+
116+
#### write a database to the disk
117+
You need to convert the result of `db.export` to a buffer
118+
```javascript
119+
var fs = require("fs");
120+
// [...] (create the database)
121+
var data = db.export();
122+
var buffer = new Buffer(data);
123+
fs.writeFileSync("filename.sqlite", buffer);
124+
```
125+
126+
See : https://github.com/lovasoa/sql.js/blob/master/test/test_node_file.js
127+
128+
### Use as web worker
129+
If you don't want to run CPU-intensive SQL queries in your main application thread,
130+
you can use the *more limited* WebWorker API.
131+
132+
You will need to download `worker.sql.js`
133+
134+
Example:
135+
```html
136+
<script>
137+
var worker = new Worker("js/worker.sql.js"); // You can find worker.sql.js in this repo
138+
worker.onmessage = function() {
139+
console.log("Database opened");
140+
worker.onmessage = function(event){
141+
console.log(event.data); // The result of the query
142+
};
143+
worker.postMessage({
144+
id: 2,
145+
action: 'exec',
146+
sql: 'SELECT * FROM test'
147+
});
148+
};
149+
150+
worker.onerror = function(e) {console.log("Worker error: ", e)};
151+
worker.postMessage({
152+
id:1,
153+
action:'open',
154+
buffer:buf, /*Optional. An ArrayBuffer representing an SQLite Database file*/
155+
});
156+
</script>
157+
```
158+
159+
See : https://github.com/lovasoa/sql.js/blob/master/test/test_worker.js
160+
161+
## Documentation
162+
The API is fully documented here : http://lovasoa.github.io/sql.js/documentation/
163+
164+
## Downloads
165+
- You can download `sql.js` here : http://lovasoa.github.io/sql.js/js/sql.js
166+
- And the Web Worker version: http://lovasoa.github.io/sql.js/js/worker.sql.js
40167

41-
see [test/test.coffee](https://github.com/mikesmullin/node-sqlite-purejs/blob/stable/test/test.coffee) for more examples.
168+
## Differences from the original sql.js
169+
* Support for BLOBs
170+
* Support for prepared statements
171+
* Cleaner API
172+
* More recent version of SQLite (3.8.4)
173+
* Compiled to asm.js (should be faster, at least on firefox)
174+
* Changed API. Results now have the form <code>[{'columns':[], values:[]}]</code>
175+
* Improved GUI of the demo. It now has :
176+
* syntax highlighting
177+
* nice HTML tables to display results
178+
* ability to load and save sqlite database files
42179

43180
## Related
44181

45-
* [In-Browser/Client-Side Demo](http://kripken.github.io/sql.js/test/demo.html)
182+
* [In-Browser/Client-Side Demo](http://lovasoa.github.io/sql.js/GUI/)
46183

‎c/benchmark.c

Lines changed: 0 additions & 87 deletions
This file was deleted.

‎c/main.c

Lines changed: 0 additions & 3 deletions
This file was deleted.

0 commit comments

Comments
 (0)