Tag Archives: WebSQL

Ember Pouch: PouchDB/CouchDB adapter for Ember Data


via nolanlawson/ember-pouch · GitHub.

Ember Pouch is a PouchDB/CouchDB adapter for Ember Data.

With Ember Pouch, all of your app’s data is automatically saved on the client-side using IndexedDB or WebSQL, and you just keep using the regular Ember Data store API. This data may be automatically synced to a remote CouchDB (or compatible servers) using PouchDB replication.

What’s the point?

  1. You don’t need to write any server-side logic. Just use CouchDB.
  2. Data syncs automatically.
  3. Your app works offline, and requests are super fast, because they don’t need the network.

For more on PouchDB, check out pouchdb.com.

Local Storage with WebSQL in JavaScript


via Local Storage con WebSql en JavaScript | Tuto-Do.

Welcome to this introduction information storage by WebSQL (Sqlite) which is used in the creation of mobile applications based on HTML5.

They explain basic concepts with which the reader will have the ability to create an application offilne if they prefer, with data storage locally. At the end of this course you can download the source code, and if you prefer, watch video everything done.

Then we begin making the first application. This will have all the basics, and learn as we develop.

Taming the asynchronous beast with ES7


via Taming the asynchronous beast with ES7.

One of the trickiest aspects of PouchDB is that its API is asynchronous. I see no shortage of confused questions on Stack Overflow, Github, and IRC, and most often they stem from a misunderstanding of callbacks and promises.

We can’t really help it. PouchDB is an abstraction over IndexedDB, WebSQL, LevelDB (in Node), and CouchDB (via Ajax). All of those APIs are asynchronous; hence PouchDB must be asynchronous.

HTML5 Offline Mobile App Using Ionic and PouchDB


via HTML5 Offline Mobile App Using Ionic and PouchDB – WebSpeaks.

In this article I will demonstrate how to create an HTML5 mobile app using Ionic Framework which will be available even in offline mode. This app will show the RSS feeds from webspeaks.in and store them on your mobile device for offline access. So you will be able to view the feeds even when your internet connection is down.

I have chosen Ionic Framework for app development as I feel this is one of the most powerful and developer friendly framework with great command line support. For storing data we have a number of options available in terms of local storage, IndexedDB, WebSQL etc. But cross browser and cross platform implementation of these standards is a real pain. So for storage purposes I have usedPouchDB database. PouchDB is a NoSQL database and is a JavaScript implementation of Apache CouchDB. Great thing about PouchDb is that it abstracts the underlying database implementation from us. It means that it can automatically chose which database engine to use behind the scenes. The greatest thing about PouchDB is that it has real time syncing capabilities i.e it can sync the local database on client with the remote database on server. However in this application we will not be using the syncing feature.
Download Script   Live Demo

Alasql.js – pure JavaScript fast client-side SQL database


README.md

Alasql.js – pure JavaScript client-side in-memory fast SQL-database

Version: 0.0.15 Date: November 10, 2014 Changelog Release Plan

Alasql – ‘à la SQL‘ – is a lightweight client-side in-memory SQL database designed to work in browser and Node.js.

  • Alasql was written with pure JavaScript and does not use browser WebSQL database.
  • Alasql is fully functional compact sql server with JOINs, GROUPs, UNIONs, ANY, ALL, IN, subqueries and very limited transactions support.
  • Alasql supports ROLLUP(), CUBE() and GROUPING SETS() functions
  • Alasql works with all modern versions of browsers (Chrome, Firefox, IE, Safari), Node.js, and mobile iOS and Android.
  • Alasql is fast, because it uses some optimization methods.

Check Alasql vs other JavaScript databases:

Warning: Alasql project is very young and still in active development phase, therefore it may has some bugs. Please, wait a little bit before start to use it in production. I am going to add more tests and clean the code before relaese more stable version 0.1.0 in the mid of November.

Examples

Try Alasql in Fiddle: sample 1, sample 2, sample 3

Other examples:

Installation

In browser

Include file: alasql.js to the page.

  <script src="alasql.js"></script>  
  <script>
    alasql("CREATE TABLE test (language INT, hello STRING)");
    alasql("INSERT INTO test VALUES (1,'Hello!')");
    alasql("INSERT INTO test VALUES (2,'Aloha!')");
    alasql("INSERT INTO test VALUES (3,'Bonjour!')");
    console.table(alasql("SELECT * FROM test WHERE language > 1"));
  </script>

You can use alasql.js with define()/require() functions in browser as well, because it supports AMD and UMD:

    require(['../../alasql.js'], function(alasql) {
        alasql('CREATE TABLE test1 (a int, b int, c int)');
        alasql('INSERT INTO test1 VALUES (1,10,1)');
        console.log(alasql('SELECT * FROM test1'));
    });

In Node.js

Use the following command for installation:

    npm install alasql

Then require alasql.js file:

    var alasql = require('alasql');

    var db = new alasql.Database();

    db.exec("CREATE TABLE test (one INT, two INT)");
    db.tables.test.data = [   // You can mix SQL and JavaScript
        {one:3,two:4},
        {one:5,two:6},
    ];
    var res = db.exec("SELECT * FROM test ORDER BY two DESC");
    console.log(res[0].one);

Supported SQL statements

  • SELECT fields FROM tableid1 JOIN tableid2 ON oncond WHERE cond GROUP BY v1,v2 HAVING cond ORDER BY a,b, LIMIT number OFFSET number
  • INSERT INTO table [ (field1, field2) ] VALUES (value1, value2), (value3, value4), …
  • INSERT INTO table SELECT subquery
  • UPDATE table SET field = value1, field = value2 WHERE condition
  • DELETE FROM table WHERE condition
  • CREATE TABLE [IF NOT EXISTS] table
  • DROP TABLE [IF EXISTS] table

SELECT statement

Now Alasql.js supports following subset of SELECT syntax:

  • SELECT column1, column2 AS alias3, FUNCTION(field4+field5) AS alias6, SUM(expression7) AS alias8, , table2.
  • FROM table1, table2, (SELECT * FROM table3) alias
  • LEFT / RIGHT / INNER / OUTER / ANTI / SEMI / CROSS JOIN table2 ON condition / USING columns
  • WHERE condition
  • GROUP BY column1, column2, ROLLUP(a,b), CUBE(c,d,e), GROUPING SETS(g,h)
  • HAVING condition
  • ORDER BY column1, column2 DESC,
  • LIMIT number [OFFSET number]
  • UNION / UNION ALL select / INTERSECT / EXCEPT

Operators

  • +, -, *, /, %, AND, OR, NOT, BETWEEN, NOT BETWEEN, EXISTS (Subquery), > ALL (subquery/array), > ANY/SOME (subquery / array), [NOT] IN (subquery / array), LIKE

Aggregators

  • SUM()
  • COUNT()
  • MIN()
  • MAX()
  • FIRST()
  • LAST()
  • Sorry, AVG still does not work

GROUP BY Grouping functions

  • ROLLUP()
  • CUBE()
  • GROUPING SETS()

    Functions

  • ABS(), IIF(), IFNULL(), INSTR(), LOWER(), UPPER(), LCASE(), UCASE(), LEN(), LENGTH()

User-defined JavaScript functions

You can use all benefits of SQL and JavaScript togeather by defining user functions. Just add new functions to alasql.fn object:

        alasql.fn.DOUBLE = function(x){return x*2};        
        alasql.fn.SUM10 = function(x,y) { return x+y*10; }
        db.exec('SELECT a, DOUBLE(a) AS b, SUM10(a,b) FROM test1');

alasql

alasql is a main variable of module. You can use it immediatly as default database

In browser:

    <script src="alasql.js"></script>
    <script>
        alasql('CREATE TABLE one (two INT)');
    </script>

or in Node.js:

    var alasql = require('alasql');
    alasql('CREATE TABLE one (two INT)');

Another approach is to create new database:

    var mybase = new alasql Database();
    mybase.exec('CREATE TABLE one (two INT)');

You can give a name to database and then access it from alasql:

    var mybase = new alasql Database('mybase');
    console.log(alasql.databases.mybase);

Each database can be used with the following methods:

  • vat db = new alasql.Database() – create new alasql-database
  • var res = db.exec(“sql-statement”) – executes SELECT query and returns array of objects

Usually, alasql.js works synchronously, but you can use callback.

    db.exec('SELECT * FROM test', function(res){
        console.log(res);
    });

or you can use aexec() – promised version of exec (in this case you need to install es6-promise module for Node.js) (this feature is experimental and may be removed in a future to reduce dependices):

    db.aexec('SELECT * FROM test').then(function(res){
        console.log(res);
    });

You can use compile statements:

    var insert = db.compile('INSERT INTO one (1,2)');
    insert();

You can use parameters in compiled statements:

    var insert1 = db.compile('INSERT INTO one (?,?)');
    var insert2 = db.compile('INSERT INTO one ($a,$b)');
    var insert3 = db.compile('INSERT INTO one (:a,:b)');

    insert1([1,2]);
    insert2({a:1,b:2});
    insert3({a:3,b:4});

    db.exec('INSERT INTO one (?,?)',[5,6]);

You even can use param in FROM clause:

        var years = [
            {yearid: 2012},
            {yearid: 2013},
            {yearid: 2014},
            {yearid: 2015},
            {yearid: 2016},
        ];

        var res = alasql.queryArray('SELECT * FROM ? AS years ' +
            'WHERE yearid > ?', [years,2014]);

        // res == [2015,2016]

Transactions

There is a limited support of transactions (with tx.rollback() and tx.commit() functions):

    db = new alasql.Database("mydb");
    db.transaction(function(tx) {
        tx.exec('SELECT COUNT(*) FROM students');
        tx.rollback();
    });     

SQL to JavaScript parser and compiler

You can use Alasql to parse to AST and compile SQL statements:

    // Parse to AST
    var ast = alasql.parse("SELECT * FROM one");
    console.log(ast.toString());

    // Compile to JavaScript function with or without parameters
    var statement = alasql.compile("SELECT * FROM one WHERE a > ? AND b < ?");
    statement([1,5]);

Alasql uses wonderful Jison parser to produce AST-tree.

Performance

According the preliminary performance tests alasql.js is faster than sql.js in 5 to 10 times on more than 1000 records tables, and 2 to 3 times to WebSQL on different queries.

Alasql has four different optimization algorithm:

  • Caching of compiled queries
  • Joins: Preindexation of joined table
  • Joins: Prefiltering of WHERE expressions

Now optimization algorithm has some limitations and therefore “table1 JOIN table2 USING column1, column2” is faster than “table1 JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2 “, but with one column it is ok.

Limitations

It is Ok with select for 1000000 records or 2 join two tables by 10000 records in each.

Tests

I use mocha for tests. Run mocha from command line:

    mocha

or run test/main.html in browser.

Known Bugs

  1. Dates sorting and comparing
  2. Semicolon with multiple statements
  3. ORDER BY clause on three or more UNIONS
  4. AVG() does not work
  5. There are many of others. Please, give me a chance to fix them. Thank you!

Future Plans

Read my to do page

Similar projects (SQL database, MDX/OLAP on JavaScript)

Alasql in blogs and press

Credits

Many thanks to Andrew Kent for his SQL Parser and other people for useful tools, which made our work much easier.

License

(c) 2014, Andrey Gershun (agershun@gmail.com), MIT licence information