V8 Coverage Report
Files covered Lines
. / test/prepare.test.js
94.85 %
406 / 428
    1.      1var sqlite3 = require('..');
    2.      1var assert = require('assert');
    3.      1
    4.      1describe('prepare', function() {
    5.      1    describe('invalid SQL', function() {
    6.      1        var db;
    7.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
    8.      1
    9.      1        var stmt;
   10.      1        it('should fail preparing a statement with invalid SQL', function(done) {
   11.      1            stmt = db.prepare('CRATE TALE foo text bar)', function(err, statement) {
   12.      1                if (err && err.errno == sqlite3.ERROR &&
   13.      1                    err.message === 'SQLITE_ERROR: near "CRATE": syntax error') {
   14.      1                    done();
   15.      0                }
   16.      0                else throw err;
   17.      1            });
   18.      1        });
   19.      1
   20.      1        after(function(done) { db.close(done); });
   21.      1    });
   22.      1
   23.      1    describe('simple prepared statement', function() {
   24.      1        var db;
   25.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
   26.      1
   27.      1        it('should prepare, run and finalize the statement', function(done) {
   28.      1            db.prepare("CREATE TABLE foo (text bar)")
   29.      1                .run()
   30.      1                .finalize(done);
   31.      1        });
   32.      1
   33.      1        after(function(done) { db.close(done); });
   34.      1    });
   35.      1
   36.      1    describe('inserting and retrieving rows', function() {
   37.      1        var db;
   38.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
   39.      1
   40.      1        var inserted = 0;
   41.      1        var retrieved = 0;
   42.      1
   43.      1        // We insert and retrieve that many rows.
   44.      1        var count = 1000;
   45.      1
   46.      1        it('should create the table', function(done) {
   47.      1            db.prepare("CREATE TABLE foo (txt text, num int, flt float, blb blob)").run().finalize(done);
   48.      1        });
   49.      1
   50.      1        it('should insert ' + count + ' rows', function(done) {
   51.   1000            for (var i = 0; i < count; i++) {
   52.   1000                db.prepare("INSERT INTO foo VALUES(?, ?, ?, ?)").run(
   53.   1000                    'String ' + i,
   54.   1000                    i,
   55.   1000                    i * Math.PI,
   56.   1000                    // null (SQLite sets this implicitly)
   57.   1000                    function(err) {
   58.      0                        if (err) throw err;
   59.   1000                        inserted++;
   60.   1000                    }
   61.   1000                ).finalize(function(err) {
   62.      0                    if (err) throw err;
   63.   1000                    if (inserted == count) done();
   64.   1000                });
   65.   1000            }
   66.      1        });
   67.      1
   68.      1        it('should prepare a statement and run it ' + (count + 5) + ' times', function(done) {
   69.      1            var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo ORDER BY num", function(err) {
   70.      0                if (err) throw err;
   71.      1                assert.equal(stmt.sql, 'SELECT txt, num, flt, blb FROM foo ORDER BY num');
   72.      1            });
   73.      1
   74.   1005            for (var i = 0; i < count + 5; i++) (function(i) {
   75.   1005                stmt.get(function(err, row) {
   76.      0                    if (err) throw err;
   77.   1005
   78.   1005                    if (retrieved >= 1000) {
   79.   1005                        assert.equal(row, undefined);
   80.   1005                    } else {
   81.   1005                        assert.equal(row.txt, 'String ' + i);
   82.   1005                        assert.equal(row.num, i);
   83.   1005                        assert.equal(row.flt, i * Math.PI);
   84.   1005                        assert.equal(row.blb, null);
   85.   1005                    }
   86.   1005
   87.   1005                    retrieved++;
   88.   1005                });
   89.   1005            })(i);
   90.      1
   91.      1            stmt.finalize(done);
   92.      1        });
   93.      1
   94.      1        it('should have retrieved ' + (count + 5) + ' rows', function() {
   95.      1            assert.equal(count + 5, retrieved, "Didn't retrieve all rows");
   96.      1        });
   97.      1
   98.      1
   99.      1        after(function(done) { db.close(done); });
  100.      1    });
  101.      1
  102.      1    describe('inserting with accidental undefined', function() {
  103.      1        var db;
  104.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
  105.      1
  106.      1        var inserted = 0;
  107.      1        var retrieved = 0;
  108.      1
  109.      1        it('should create the table', function(done) {
  110.      1            db.prepare("CREATE TABLE foo (num int)").run().finalize(done);
  111.      1        });
  112.      1
  113.      1        it('should insert two rows', function(done) {
  114.      1            db.prepare('INSERT INTO foo VALUES(4)').run(function(err) {
  115.      0                if (err) throw err;
  116.      1                inserted++;
  117.      1            }).run(undefined, function (err) {
  118.      1                // The second time we pass undefined as a parameter. This is
  119.      1                // a mistake, but it should either throw an error or be ignored,
  120.      1                // not silently fail to run the statement.
  121.      0                if (err) throw err;
  122.      1                inserted++;
  123.      1            }).finalize(function(err) {
  124.      0                if (err) throw err;
  125.      1                if (inserted == 2) done();
  126.      1            });
  127.      1        });
  128.      1
  129.      1        it('should retrieve the data', function(done) {
  130.      1            var stmt = db.prepare("SELECT num FROM foo", function(err) {
  131.      0                if (err) throw err;
  132.      1            });
  133.      1
  134.      2            for (var i = 0; i < 2; i++) (function(i) {
  135.      2                stmt.get(function(err, row) {
  136.      0                    if (err) throw err;
  137.      2                    assert(row);
  138.      2                    assert.equal(row.num, 4);
  139.      2                    retrieved++;
  140.      2                });
  141.      2            })(i);
  142.      1
  143.      1            stmt.finalize(done);
  144.      1        });
  145.      1
  146.      1        it('should have retrieved two rows', function() {
  147.      1            assert.equal(2, retrieved, "Didn't retrieve all rows");
  148.      1        });
  149.      1
  150.      1        after(function(done) { db.close(done); });
  151.      1    });
  152.      1
  153.      1    describe('retrieving reset() function', function() {
  154.      1        var db;
  155.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  156.      1
  157.      1        var retrieved = 0;
  158.      1
  159.      1        it('should retrieve the same row over and over again', function(done) {
  160.      1            var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo ORDER BY num");
  161.     10            for (var i = 0; i < 10; i++) {
  162.     10                stmt.reset();
  163.     10                stmt.get(function(err, row) {
  164.      0                    if (err) throw err;
  165.     10                    assert.equal(row.txt, 'String 0');
  166.     10                    assert.equal(row.num, 0);
  167.     10                    assert.equal(row.flt, 0.0);
  168.     10                    assert.equal(row.blb, null);
  169.     10                    retrieved++;
  170.     10                });
  171.     10            }
  172.      1            stmt.finalize(done);
  173.      1        });
  174.      1
  175.      1        it('should have retrieved 10 rows', function() {
  176.      1            assert.equal(10, retrieved, "Didn't retrieve all rows");
  177.      1        });
  178.      1
  179.      1        after(function(done) { db.close(done); });
  180.      1    });
  181.      1
  182.      1    describe('multiple get() parameter binding', function() {
  183.      1        var db;
  184.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  185.      1
  186.      1        var retrieved = 0;
  187.      1
  188.      1        it('should retrieve particular rows', function(done) {
  189.      1            var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num = ?");
  190.      1
  191.     10            for (var i = 0; i < 10; i++) (function(i) {
  192.     10                stmt.get(i * 10 + 1, function(err, row) {
  193.      0                    if (err) throw err;
  194.     10                    var val = i * 10 + 1;
  195.     10                    assert.equal(row.txt, 'String ' + val);
  196.     10                    assert.equal(row.num, val);
  197.     10                    assert.equal(row.flt, val * Math.PI);
  198.     10                    assert.equal(row.blb, null);
  199.     10                    retrieved++;
  200.     10                });
  201.     10            })(i);
  202.      1
  203.      1            stmt.finalize(done);
  204.      1        });
  205.      1
  206.      1        it('should have retrieved 10 rows', function() {
  207.      1            assert.equal(10, retrieved, "Didn't retrieve all rows");
  208.      1        });
  209.      1
  210.      1        after(function(done) { db.close(done); });
  211.      1    });
  212.      1
  213.      1    describe('prepare() parameter binding', function() {
  214.      1        var db;
  215.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  216.      1
  217.      1        var retrieved = 0;
  218.      1
  219.      1        it('should retrieve particular rows', function(done) {
  220.      1            db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num = ? AND txt = ?", 10, 'String 10')
  221.      1                .get(function(err, row) {
  222.      0                    if (err) throw err;
  223.      1                    assert.equal(row.txt, 'String 10');
  224.      1                    assert.equal(row.num, 10);
  225.      1                    assert.equal(row.flt, 10 * Math.PI);
  226.      1                    assert.equal(row.blb, null);
  227.      1                    retrieved++;
  228.      1                })
  229.      1                .finalize(done);
  230.      1        });
  231.      1
  232.      1        it('should have retrieved 1 row', function() {
  233.      1            assert.equal(1, retrieved, "Didn't retrieve all rows");
  234.      1        });
  235.      1
  236.      1        after(function(done) { db.close(done); });
  237.      1    });
  238.      1
  239.      1    describe('all()', function() {
  240.      1        var db;
  241.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  242.      1
  243.      1        var retrieved = 0;
  244.      1        var count = 1000;
  245.      1
  246.      1        it('should retrieve particular rows', function(done) {
  247.      1            db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num < ? ORDER BY num", count)
  248.      1                .all(function(err, rows) {
  249.      0                    if (err) throw err;
  250.   1000                    for (var i = 0; i < rows.length; i++) {
  251.   1000                        assert.equal(rows[i].txt, 'String ' + i);
  252.   1000                        assert.equal(rows[i].num, i);
  253.   1000                        assert.equal(rows[i].flt, i * Math.PI);
  254.   1000                        assert.equal(rows[i].blb, null);
  255.   1000                        retrieved++;
  256.   1000                    }
  257.      1                })
  258.      1                .finalize(done);
  259.      1        });
  260.      1
  261.      1        it('should have retrieved all rows', function() {
  262.      1            assert.equal(count, retrieved, "Didn't retrieve all rows");
  263.      1        });
  264.      1
  265.      1        after(function(done) { db.close(done); });
  266.      1    });
  267.      1
  268.      1    describe('all()', function() {
  269.      1        var db;
  270.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  271.      1
  272.      1        it('should retrieve particular rows', function(done) {
  273.      1           db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num > 5000")
  274.      1                .all(function(err, rows) {
  275.      0                    if (err) throw err;
  276.      1                    assert.ok(rows.length === 0);
  277.      1                })
  278.      1                .finalize(done);
  279.      1        });
  280.      1
  281.      1        after(function(done) { db.close(done); });
  282.      1    });
  283.      1
  284.      1    describe('high concurrency', function() {
  285.      1        var db;
  286.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
  287.      1
  288.   1140        function randomString() {
  289.   1140            var str = '';
  290. 166185            for (var i = Math.random() * 300; i > 0; i--) {
  291. 166185                str += String.fromCharCode(Math.floor(Math.random() * 256));
  292. 166185            }
  293.   1140            return str;
  294.   1140        }
  295.      1
  296.      1        // Generate random data.
  297.      1        var data = [];
  298.      1        var length = Math.floor(Math.random() * 1000) + 200;
  299.   1140        for (var i = 0; i < length; i++) {
  300.   1140            data.push([ randomString(), i, i * Math.random(), null ]);
  301.   1140        }
  302.      1
  303.      1        var inserted = 0;
  304.      1        var retrieved = 0;
  305.      1
  306.      1        it('should create the table', function(done) {
  307.      1            db.prepare("CREATE TABLE foo (txt text, num int, flt float, blb blob)").run().finalize(done);
  308.      1        });
  309.      1
  310.      1        it('should insert all values', function(done) {
  311.   1140            for (var i = 0; i < data.length; i++) {
  312.   1140                var stmt = db.prepare("INSERT INTO foo VALUES(?, ?, ?, ?)");
  313.   1140                stmt.run(data[i][0], data[i][1], data[i][2], data[i][3], function(err) {
  314.      0                    if (err) throw err;
  315.   1140                    inserted++;
  316.   1140                }).finalize(function(err) {
  317.      0                    if (err) throw err;
  318.   1140                    if (inserted == data.length) done();
  319.   1140                });
  320.   1140            }
  321.      1        });
  322.      1
  323.      1        it('should retrieve all values', function(done) {
  324.      1            db.prepare("SELECT txt, num, flt, blb FROM foo")
  325.      1                .all(function(err, rows) {
  326.      0                    if (err) throw err;
  327.      1
  328.   1140                    for (var i = 0; i < rows.length; i++) {
  329.   1140                        assert.ok(data[rows[i].num] !== true);
  330.   1140
  331.   1140                        assert.equal(rows[i].txt, data[rows[i].num][0]);
  332.   1140                        assert.equal(rows[i].num, data[rows[i].num][1]);
  333.   1140                        assert.equal(rows[i].flt, data[rows[i].num][2]);
  334.   1140                        assert.equal(rows[i].blb, data[rows[i].num][3]);
  335.   1140
  336.   1140                        // Mark the data row as already retrieved.
  337.   1140                        data[rows[i].num] = true;
  338.   1140                        retrieved++;
  339.   1140
  340.   1140                    }
  341.      1
  342.      1                    assert.equal(retrieved, data.length);
  343.      1                    assert.equal(retrieved, inserted);
  344.      1                })
  345.      1                .finalize(done);
  346.      1        });
  347.      1
  348.      1        after(function(done) { db.close(done); });
  349.      1    });
  350.      1
  351.      1
  352.      1    describe('test Database#get()', function() {
  353.      1        var db;
  354.      1        before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
  355.      1
  356.      1        var retrieved = 0;
  357.      1
  358.      1        it('should get a row', function(done) {
  359.      1            db.get("SELECT txt, num, flt, blb FROM foo WHERE num = ? AND txt = ?", 10, 'String 10', function(err, row) {
  360.      0                if (err) throw err;
  361.      1                assert.equal(row.txt, 'String 10');
  362.      1                assert.equal(row.num, 10);
  363.      1                assert.equal(row.flt, 10 * Math.PI);
  364.      1                assert.equal(row.blb, null);
  365.      1                retrieved++;
  366.      1                done();
  367.      1            });
  368.      1        });
  369.      1
  370.      1        it('should have retrieved all rows', function() {
  371.      1            assert.equal(1, retrieved, "Didn't retrieve all rows");
  372.      1        });
  373.      1
  374.      1        after(function(done) { db.close(done); });
  375.      1    });
  376.      1
  377.      1    describe('Database#run() and Database#all()', function() {
  378.      1        var db;
  379.      1        before(function(done) { db = new sqlite3.Database(':memory:', done); });
  380.      1
  381.      1        var inserted = 0;
  382.      1        var retrieved = 0;
  383.      1
  384.      1        // We insert and retrieve that many rows.
  385.      1        var count = 1000;
  386.      1
  387.      1        it('should create the table', function(done) {
  388.      1            db.run("CREATE TABLE foo (txt text, num int, flt float, blb blob)", done);
  389.      1        });
  390.      1
  391.      1        it('should insert ' + count + ' rows', function(done) {
  392.   1000            for (var i = 0; i < count; i++) {
  393.   1000                db.run("INSERT INTO foo VALUES(?, ?, ?, ?)",
  394.   1000                    'String ' + i,
  395.   1000                    i,
  396.   1000                    i * Math.PI,
  397.   1000                    // null (SQLite sets this implicitly)
  398.   1000                    function(err) {
  399.      0                        if (err) throw err;
  400.   1000                        inserted++;
  401.   1000                        if (inserted == count) done();
  402.   1000                    }
  403.   1000                );
  404.   1000            }
  405.      1        });
  406.      1
  407.      1        it('should retrieve all rows', function(done) {
  408.      1            db.all("SELECT txt, num, flt, blb FROM foo ORDER BY num", function(err, rows) {
  409.      0                if (err) throw err;
  410.   1000                for (var i = 0; i < rows.length; i++) {
  411.   1000                    assert.equal(rows[i].txt, 'String ' + i);
  412.   1000                    assert.equal(rows[i].num, i);
  413.   1000                    assert.equal(rows[i].flt, i * Math.PI);
  414.   1000                    assert.equal(rows[i].blb, null);
  415.   1000                    retrieved++;
  416.   1000                }
  417.      1
  418.      1                assert.equal(retrieved, count);
  419.      1                assert.equal(retrieved, inserted);
  420.      1
  421.      1                done();
  422.      1            });
  423.      1        });
  424.      1
  425.      1        after(function(done) { db.close(done); });
  426.      1    });
  427.      1});
  428.      1