kc-websql

This Polymer element makes using WebSQL easy. From table migration to sql statements like SELECT, INSERT, UPDATE and DELETE. You can use Polymers data-binding for all of these!

Description

WebSQL is a client-side feature most modern browsers support. See caniuse.com for more info on browser support.

Sadly, the specification has been marked depricated by the W3C because most browser simply use SQLite to implement the feature. Its successor is IndexedDB mostly pushed by Mozilla. But, this is a NoSQL database and if you want to store structured data on the client-side using SQL you'll need WebSQL.

The kc-websql element family is a group of Polymer elements used to simplify the use of WebSQL.

They'll create a WebSQL database and the table structure as you designed it with just a few Polymer elements.

You can then create instances of kc-websql-select, kc-websql-insert, kc-websql-update or kc-websql-delete to manipulate the data. All of these elements have attributes, which represent a part of the SQL statement. You can therefore use data-binding to supply the necessary parameters to your statements. The elements also help defining parametrized statements. This way you don't have to rewrite the whole statements, but call them with different parameters.

As some of the attributes take an array as input, I recommend to use the Array-Composer element we developed.

Dependencies

As you may gues, the WebSQL feature is required for this to work.

But also required is the Promise API. More info on browser support here on caniuse.com.

You may use polyfills for both of these features.

Source

The kc-websql project is hosted on GitHub.

Install package

You can install the package from Bower.io and add this element to your project:

$ bower install kc-websql

To use the bower command, you'll need some dev tools first:

Git
Node.js

Install both and then run this command to install bower to the global node cache:

$ npm install -g bower

See the offical bower help for more details.

Demo

Find the complete demo source here: Demo on GitHub

These examples assume, that there is a element <template is="dom-bind" id="dom-bind"> surrounding all of this, to enable data-binding and event handling in Polymer.

kc-websql-unsupported

Let's start this with the worst case: WebSQL or Primisses aren't supported by the client browser.

The kc-websql-unsupported element let's you define custom HTML, which is loaded and displayed only if the dependencies of the kc-websql elements are not met.

This is the only element you don't have to place inside a kc-websql element. You may place this anywhere you like.

<kc-websql-unsupported on-show="showDialog">
  <paper-dialog modal id="unsupported-dialog">
    <h2> Ooops... </h2>
    <div>
      WebSQL is not supported by this browser.
      Try your luck with another one.
      For example the latest version of Google Chrome or Safari.
    </div>
  </paper-dialog>
</kc-websql-unsupported> 
<script>
(function() {
  var domBind = document.getElementById('dom-bind');
  // web sql not supported event handler
  domBind.showDialog = function(s) {
    Polymer.dom(document).querySelector("#unsupported-dialog").open();
  };
})();
</script>

If the feature is not supported, this example will use a paper-dialog element to show a modal message dialog. To open the dialog, the open() method has to be called, so the on-show event is used to call this method.

kc-websql

To create and maintain a database you need the main kc-websql element.

<kc-websql database="demo-db" version="1.0">

You can have more than one database. All kc-websql elements will use the first database defined by default. You need to set the database attribute at the elements to use another database.

kc-websql-table

To design table structures you need to place kc-websql-table elements inside the kc-websql element.

<kc-websql-table name="texts" version="1.0" primary-key="nr ASC">
  <!-- SQLite will make `nr` an alias for `rowid` because it is an INTEGER and PRIMARY KEY -->
  <kc-websql-fields>nr INTEGER, text TEXT, ts INTEGER</kc-websql-fields>
  <kc-websql-index name="texts_ts">ts DESC</kc-websql-index>
</kc-websql-table>

This will add a table called texts with its inital table version being 1.0. See the next element kc-websql-migration for more on table versions.

The table fields are specified with a kc-websql-fields element. Have a look at the SQLite datatypes for more on all possible types of data.

The table also defines a custom primary-key attribute and an additional index with the kc-websql-index element. Index names have to be unique within the whole database!

kc-websql-migration

Each table is created like the one above. Once created the kc-websql element will remember the inital version number of the table. If the version number of the table definition changes, kc-websql-migration elements need to be added, too. These elements contain sql statements to upgrade the old table structure to the new model. There are sql migration tools helping you to create these statements.

<kc-websql-migration from="1.0" to="2.0">
  ALTER TABLE tbl ADD COLUMN newcol TEXT;
</kc-websql-migration>

This will migrate a version 1.0 table to version 2.0 model.

Remember to add a migration for each version change. The kc-websql element will execute all migrations needed to upgrade the tables, even if the database is more than one migration behind.

kc-websql-select

Technically you don't have to place these elements within the kc-websql element, but it will hide them from the local DOM.

<kc-websql-select fields="text, ts, nr" from="texts" order="ts DESC" result="{{data}}" auto /> 

<template is="dom-repeat" items="{{data}}">
  <div kc-texts-nr="{{item.nr}}">
    <span>
      <paper-icon-button icon="delete" kcnr="{{item.nr}}" onclick="deleteText(this.kcnr)" />
    </span>
    <span>
      <paper-icon-button icon="arrow-drop-up" kcnr="{{item.nr}}" onclick="moveUp(this.kcnr)" />
    </span>
    <span>{{formatTimestamp(item.ts)}}</span>: <span>{{item.text}}</span>
  </div>
</template>
<div class="baseline">
  <span>{{data.length}}</span> - Records
</div>

The frist line defines the kc-websql-select element, which fills an array with the resultset. The template below the select is automatically populated with this resultset through data-bindings.

If you would want to call the select again, use this snipped: Polymer.dom(document).querySelector('kc-websql-select').execute();

kc-websql-insert

You can easily insert data with the kc-websql-insert element.

<paper-input-container>
  <label>Your text</label>
  <input is="iron-input" id="text-input" bind-value="{{inputText}}" value="{{value::input}}">
</paper-input-container>
<paper-button onclick="save()" class="colorful">Save</paper-button>

<!-- kc-timer: used to get timestamp -->
<kc-timer timestamp="{{ts}}" output="ts" active fire-on-activate />
<!-- array-composer: used to merge fields to array -->
<array-composer length="2" array="{{insertArgs}}" f0="[[inputText]]" f1="[[ts]]" update-on="f0" />
<kc-websql-insert fields="text,ts" into="texts" params="[[insertArgs]]" on-finished="sqlFinished" />

<script>
  function save() {
    // Insert new text, parameters are set via data-bindings
    Polymer.dom(document).querySelector('kc-websql-insert').execute();
  }
  (function() {
    var domBind = document.getElementById('dom-bind');
    // sqlFinished event handler
    domBind.sqlFinished = function () {
      // refresh view by execut select
      Polymer.dom(document).querySelector('kc-websql-select').execute();
    };
  })();
</script>

This sample shows the use of two additional Polymer elements available here: kc-timer and Array-Composer.

The insert statement is build within the kc-websql-insert element. It is parametrized so you can supply it with an array containing the values to insert.

The statement is executed via script when the save button is clicked.

kc-websql-update

Updates are almost equal to inserts. I skip the overhead here:

<kc-websql-update table="texts" fields="ts=?" where="nr=?" params="[[updateArgs]]" />

You can use the questionmark operator for parameters within your statements.

kc-websql-delete

The same is true for delete statements.

<kc-websql-delete from="texts" where="nr=?" />
<script>
  function deleteText(nr) {
    // delete text by number index
    Polymer.dom(document).querySelector('kc-websql-delete').execute(nr);
  }
</script>

Here the parameters are not data-bound, but given to the execute() method, which is the alternative to the params attribute used before.