Persistent client side database

From Trephine

Jump to: navigation, search

Trephine demo illustrating how to use HSQLDB to create a client side RDBMS.

You must have JavaScript enabled to run this demo.

/* Status and element functions. */
var e = function(tag,index){ return demotab.getElementsByTagName(tag)[index||0]; };
var status = function(msg) {
  if (!msg) return e('p').style.display = 'none';
  e('p').style.display = '';
  e('p').innerHTML = msg;
};
 
/* Define clientdb singleton */
var clientdb = window.clientdb = {
 
  // Setup the demo
  init: function() {
 
    // Prepare java-side clientdb object to use as a driver
    var result = trephine.js( function(){
 
      // Get handle on demo directory or create it if necessary
      var homedir = new java.io.File( java.lang.System.getProperty('user.home') );
      var demodir = new java.io.File( homedir, ['.trephine', applet.documentBase.host, 'client-side-db-demo'].join('/') );
      if (!demodir.exists()) {
        if (!demodir.mkdirs()) throw "Error: Could not create " + demodir.toString();
      }
 
      // Initialize and register the HSQL JDBC driver
      java.sql.DriverManager.registerDriver( new org.hsqldb.jdbcDriver() );
 
      // Create connection to file-backed HSQLDB instance
      try {
        var conn = java.sql.DriverManager.getConnection( "jdbc:hsqldb:" + demodir.toString() + "/demo-db", "sa", "" );
      } catch (err) {
        var ds = new org.hsqldb.jdbc.jdbcDataSource();
        ds.database = "jdbc:hsqldb:" + demodir.toString() + "/demo-db";
        var conn = ds.getConnection( "sa", "" );
      }
 
      // Expose SQL interface through clientdb object
      return global.clientdb = {
 
        // Execute an arbitrary SQL expression
        execute: function(sql) {
 
          var result = null;
          var statement = conn.createStatement(); // java.sql.Statement
          var hasresult = statement.execute(sql);
 
          if (hasresult) { // Must have been a SELECT - collect results to return
 
            var rs = statement.resultSet; // java.sql.ResultSet
            var meta = rs.metaData; // java.sql.ResultSetMetaData
            var colmax = meta.columnCount, data = [];
            var headings = [];
            for (var i=0; i<colmax; i++) headings[headings.length] = meta.getColumnLabel( i + 1 );
            while( rs.next() ) {
              var row = [];
              for (var i=0; i<colmax; i++) row[row.length] = rs.getObject( i + 1 );
              data[data.length] = row.join('</td><td>');
            }
            result = [
              '<table class="wikitable"><thead><tr><th>',
              headings.join('</th><th>'),
              '</th></tr></thead><tbody><tr><td>',
              data.join('</td></tr><tr><td>'),
              '</td></tr></tbody></table>'
            ].join('');
 
          } else { // Must have been an UPDATE/INSERT/DELETE - create message to return
 
            var count = statement.updateCount;
            if (count==-1) throw "SQL Error: Update executed, but invalid update count returned.";
            result = '<p class="updatesuccess">Execution successful, ' + count + (count==1?' row':' rows') + ' affected.</p>';
 
          }
 
          statement.close();
          return result;
        },
 
        // Shuts down the database and closes the connection thereto (cleanup)
        close: function() {
          var statement = conn.createStatement();
          statement.execute( "SHUTDOWN" );
          statement.close();
          conn.close();
        }
 
      }; // End clientdb definition
 
    } ); // End trephine.js() call
 
    if (result.error) throw "Initialization error: " + result.error.toString();
 
    // Setup form controls
    e('form').onsubmit = function(event) {
      try {
        if (!trephine || !trephine.loaded) throw "Error: trephine not loaded.";
        if (!trephine.hasPermission()) throw "Error: elevated permissions have not been granted.";
        var results = clientdb.execute( e('textarea').value + '' ), output = [];
        for (var i=0; i<results.length; i++) {
          var result = results[i];
          output[output.length] = '<p class="sqlecho">SQL: <tt>' + result.sql + '</tt></p>';
          if (result.success) output[output.length] = result.result.toString() + '';
          else output[output.length] = '<p class="errorbox" style="float:none">' + result.error.toString() + '</p>';
        };
        e('div').innerHTML = output.join("\n");
      } catch(err) {
        e('div').innerHTML = '<p class="errorbox" style="float:none">' + err + '</p>';
      }
      return false;
    };
 
  },
 
  // Sequentially execute any SQL 
  execute: function(blob) {
    var expressions = this.extract(blob), results = [];
    for (var i=0; i<expressions.length; i++) {
      var result = trephine.js( function(sql) { return global.clientdb.execute(sql); }, expressions[i] );
      result.sql = expressions[i];
      results[results.length] = result;
      if (result.error) break;
    }
    return results;
  },
 
  // Extracts a list of actionable expressions from a blob of SQL
  extract: function(blob) {
    var exps = [], buf = [], state = 'scan', quote, ignore = new RegExp('\\w;');
    var machine = {
      'scan': function(ch) { // Looking for an expression or comment
        if (ch.match(ignore)) return;
        if (ch=='-') return state = 'leadingdash';
        if (ch=='/') return state = 'leadingslash';
        buf[buf.length] = ch;
        if (ch=='"' || ch=="'") {
          quote = ch;
          return state = 'quotedvalue';
        }
        return state = 'expression';
      },
      'expression': function(ch) { // In an SQL expression
        if (ch=='-') return state = 'leadingdash';
        if (ch=='/') return state = 'leadingslash';
        if (ch==';') {
          if (buf.length) exps[exps.length] = buf.join('');
          buf = [];
          return state = 'scan';
        }
        buf[buf.length] = ch;
        if (ch=='"' || ch=="'") {
          quote = ch;
          return state = 'quotedvalue';
        }
      },
      'quotedvalue': function(ch) { // Inside a quoted value
        buf[buf.length] = ch;
        if (ch==quote) return state = 'innerquote';
      },
      'innerquote': function(ch) { // Found a quote inside a quoted value
        buf[buf.length] = ch;
        if (ch==quote) return state = 'quotedvalue';
        state = 'expression';
      },
      'leadingdash': function(ch) { // Found a dash character - may start a line comment
        if (ch=='-') return state = 'linecomment';
        buf[buf.length] = '-' + ch;
        state = 'expression';
      },
      'leadingslash': function(ch) { // Found a slash character - may start a multi-line comment
        if (ch=='*') return state = 'multilinecomment';
        buf[buf.length] = '/' + ch;
        state = 'expression';
      },
      'linecomment': function(ch) { // Inside a single-line comment
        if (ch=="\n") return state = buf.length ? 'expression' : 'scan';
      },
      'multilinecomment': function(ch) { // Inside a multi-line comment
        if (ch=='*') return state = 'innerasterisk';
      },
      'innerasterisk': function(ch) { // Found an asterisk inside a multi-line comment
        if (ch=='/') return state = buf.length ? 'expression' : 'scan';
        state = 'multilinecomment';
      }
    };
    for (var i=0; i<blob.length; i++) machine[state](blob.charAt(i));
    if (buf.length) {
      var sql = buf.join('');
      if (sql.match(/\S/)) exps[exps.length] = sql;
    }
    return exps;
  }
 
};
 
/* Load trephine */
status('<span class="spinner"></span> Loading trephine...');
trephine.load({
  debug: true, // Enables debug logging to the Java Console
  jars: ['hsqldb.jar'], // Include HSQLDB
  engines: ['js'], // Initialize the JavaScript engine as soon as possible
  onload: function() { // Immediately prompt for permissions
    status('<span class="spinner"></span> Asking for elevated permissions...');
    trephine.askPermission( function(response) {
      if (!response) { // Short-circuit if permission request was denied
        return demotab.innerHTML = '<div class="error">Sorry, you must grant trephine privileges to enjoy this demo.</div>';
      }
      clientdb.init();
      status('Permission request granted.');
      setTimeout( function(){ status(); }, 2000 );
    } );
  }
});
trephine.js.toJSON = JSON.encode; // Using MooTools JSON encoder for arguments to trephine.js()
<p class="notice"></p>
<form class="clientdb">
  <input class="button" type="submit" value="execute"/>
  <h4>SQL:</h4>
  <textarea>
-- Drop the table if it already exists
DROP TABLE IF EXISTS sample_table;
 
-- Create table
CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER );
 
-- Insert a bunch of data
INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100);
INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200);
INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300);
INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400);
 
-- Perform a simple query
SELECT * FROM sample_table WHERE num_col < 250;
    </textarea>
  <h4>Results:</h4>
  <div></div>
</form>
.clientdb input {
  float: right;
}
.clientdb textarea {
  width: 890px;
  height: 100px;
  background: #fffff0;
  margin-bottom: 18px;
}
.clientdb h4 {
  font-weight: bold;
}
.clientdb tt {
  background: #f0f0f0;
  padding: 0 0.5em;
}
.clientdb td {
  border-top: 1px solid #ccc;
}
.clientdb table {
  border: 1px solid #ccc;
}
.demotab .spinner {
  float: left;
  height: 20px;
  width: 25px;
  background: url("skins/common/images/spinner.gif");
  background-repeat: no-repeat;
}

Contents

Activation

  1. Activate the demo by clicking the specified link.
  2. You may see a Java Security dialog, if so, click "Run" or "Trust" to indicate your acceptance
  3. In the "Trephine permission request" dialog, answer the math challenge and click "OK" to grant scripts on this page elevated privileges.

Usage

  1. Enter desired SQL into the textarea provided.
  2. Click the execute button in the upper-right corner to trigger SQL evaluation.
  3. Repeat as many times as you like.

How it works

This is a fairly sophisticated demo, despite the simplicity of the UI. The following is a breakdown of what goes on behind the scenes. Note that you are encouraged to read through the demo source code as well.

Loading the HSQLDB jar file

The command to load the trephine applet is towards the end of the demo code, but is worth mentioning right away. The trephine.load() method accepts an options hash as a parameter. By specifying the 'jars' field, the caller can provide a list of additional Jars to make available to the applet.

In the case of this demo, the 'jars' field is provided with a value of ['hsqldb.jar'], signifying that the HSQLDB jar should be loaded from the same location as trephine.jar (from trephine.s3.amazonaws.com that is).

Creating the database

When you click to activate this demo, it will do a number of tasks, starting with loading trephine and prompting for privileged access.

Once trephine is up and running, the demo will:

  1. Look for a directory called .trephine/trephine.org/client-side-db-demo under the current user's home directory.
  2. Create the aforementioned directory if it doesn't exist already
  3. Initialize an HSQLDB database called 'demo-db' if one isn't already in place and create a connection thereto.
  4. Define the execute() method and supporting functions for evaluating SQL expressions later.
  5. Prepares the 'execute' button to accept click events

Executing SQL

When the 'execute' button is clicked:

  1. The demo code parses the SQL textarea, stripping out single and multi-line comments and extracting a list of individual expressions.
    Note: This parsing is fairly involved and more than what most applications will require.
  2. The extracted SQL expressions are evaluated sequentially by sending them through trephine.js() - if any fail, no more are attempted
  3. The results of the expressions are formatted for display in the Results area.

Example SQL

The following commands are adapted from HSQLDB's First JDBC Client Example. You are encoraged to review HSQLDB's SQL Syntax for more information on creating valid SQL expressions.

Create a table
CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER );
Insert data
INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100);
INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200);
INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300);
INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400);
Select from table
SELECT * FROM sample_table WHERE num_col < 250;
Drop table
DROP TABLE IF EXISTS sample_table;
Personal tools