Persistent client side database

/* 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()