diff options
Diffstat (limited to 'infrastructure/net.appjet.ajstdlib/sqlbase.scala')
| -rw-r--r-- | infrastructure/net.appjet.ajstdlib/sqlbase.scala | 563 |
1 files changed, 563 insertions, 0 deletions
diff --git a/infrastructure/net.appjet.ajstdlib/sqlbase.scala b/infrastructure/net.appjet.ajstdlib/sqlbase.scala new file mode 100644 index 0000000..047c086 --- /dev/null +++ b/infrastructure/net.appjet.ajstdlib/sqlbase.scala @@ -0,0 +1,563 @@ +/** + * Copyright 2009 Google Inc. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS-IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package net.appjet.ajstdlib; + +import scala.collection.mutable.ArrayBuffer; + +import java.sql.{DriverManager, SQLException, Statement}; +import net.appjet.oui.{profiler, config, NoninheritedDynamicVariable}; +import com.mchange.v2.c3p0._; + +class SQLBase(driverClass: String, url: String, userName: String, password: String) { + + def isMysql:Boolean = (url.startsWith("jdbc:mysql:")); + def isDerby:Boolean = (url.startsWith("jdbc:derby:")); + + if (isDerby) { + System.setProperty("derby.system.home", config.derbyHome); + val f = new java.io.File(config.derbyHome); + if (! f.exists) { + if (! f.mkdirs()) + throw new RuntimeException("Couldn't create internal database storage directory: "+config.derbyHome); + } + if (! f.isDirectory) + throw new RuntimeException("Internal database storage directory is not a directory: "+config.derbyHome); + if (! f.canWrite) + throw new RuntimeException("Can't write to internal database storage directory: "+config.derbyHome); + } + + val cpds = new ComboPooledDataSource(); + cpds.setDriverClass(driverClass); + cpds.setJdbcUrl(url+(if (isMysql) "?useUnicode=true&characterEncoding=utf8" else "")); + + // derby does not require a password + if (!isDerby) { + cpds.setUser(userName); + cpds.setPassword(password); + } + + cpds.setMaxPoolSize(config.jdbcPoolSize); + cpds.setMaxConnectionAge(6*60*60); // 6 hours + if (config.devMode) { + cpds.setAutomaticTestTable("cpds_testtable"); + cpds.setTestConnectionOnCheckout(true); + } + +// { +// // register db driver +// try { +// new JDCConnectionDriver(driverClass, url+"?useUnicode=true&characterEncoding=utf8", userName, password); +// } catch { +// case e => { +// e.printStackTrace(); +// Runtime.getRuntime.halt(1); +// } +// } +// } + + private def getConnectionFromPool = { + val c = cpds.getConnection(); + c.setAutoCommit(true); + c; + } + + // Creates a dynamic variable whose .value depends on the innermost + // .withValue(){} on the call-stack. + private val currentConnection = new NoninheritedDynamicVariable[Option[java.sql.Connection]](None); + + def withConnection[A](block: java.sql.Connection=>A): A = { + currentConnection.value match { + case Some(c) => { + block(c); + } + case None => { + val t1 = profiler.time; + val c = getConnectionFromPool; + profiler.recordCumulative("getConnection", profiler.time-t1); + try { + currentConnection.withValue(Some(c)) { + block(c); + } + } finally { + c.close; + } + } + } + } + + private val currentlyInTransaction = new NoninheritedDynamicVariable(false); + + def inTransaction[A](block: java.sql.Connection=>A): A = { + withConnection(c => { + if (currentlyInTransaction.value) { + return block(c); + } else { + currentlyInTransaction.withValue(true) { + c.setAutoCommit(false); + c.setTransactionIsolation(java.sql.Connection.TRANSACTION_REPEATABLE_READ); + + try { + val result = block(c); + c.commit(); + c.setAutoCommit(true); + result; + } catch { + case e@net.appjet.oui.AppGeneratedStopException => { + c.commit(); + c.setAutoCommit(true); + throw e; + } + case (e:org.mozilla.javascript.WrappedException) if (e.getWrappedException == + net.appjet.oui.AppGeneratedStopException) => { + c.commit(); + c.setAutoCommit(true); + throw e; + } + case e => { + //println("inTransaction() caught error:"); + //e.printStackTrace(); + try { + c.rollback(); + c.setAutoCommit(true); + } catch { + case ex => { + println("Could not rollback transaction because: "+ex.toString()); + } + } + throw e; + } + } + } + } + }); + } + + def closing[A](closable: java.sql.Statement)(block: =>A): A = { + try { block } finally { closable.close(); } + } + + def closing[A](closable: java.sql.ResultSet)(block: =>A): A = { + try { block } finally { closable.close(); } + } + + def tableName(t: String) = id(t); + + val identifierQuoteString = withConnection(_.getMetaData.getIdentifierQuoteString); + def quoteIdentifier(s: String) = identifierQuoteString+s+identifierQuoteString; + private def id(s: String) = quoteIdentifier(s); + + def longTextType = if (isDerby) "CLOB" else "MEDIUMTEXT"; + + // derby seems to do things intelligently w.r.t. case-sensitivity and unicode support. + def createTableOptions = if (isMysql) " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin" else ""; + + // creates table if it doesn't exist already + def createJSONTable(table: String) { + withConnection { c=> + val s = c.createStatement; + if (! doesTableExist(c, table)) { + closing(s) { + s.execute("CREATE TABLE "+tableName(table)+" ("+ + id("ID")+" VARCHAR(128) PRIMARY KEY NOT NULL, "+ + id("JSON")+" "+longTextType+" NOT NULL"+ + ")"+createTableOptions); + } + } + } + } + + // requires: table exists + // returns null if key doesn't exist + def getJSON(table: String, key: String): String = { + withConnection { c=> + val s = c.prepareStatement("SELECT "+id("JSON")+" FROM "+tableName(table)+" WHERE "+id("ID")+" = ?"); + closing(s) { + s.setString(1, key); + var resultSet = s.executeQuery(); + closing(resultSet) { + if (! resultSet.next()) { + null; + } + else { + resultSet.getString(1); + } + } + } + } + } + + def getAllJSON(table: String, start: Int, count: Int): Array[Object] = { + withConnection { c => + val s = c.prepareStatement("SELECT "+id("ID")+","+id("JSON")+" FROM "+tableName(table)+ + " ORDER BY "+id("ID")+" DESC"+ + " LIMIT ? OFFSET ?"); + closing(s) { + s.setInt(2, start); + s.setInt(1, count); + var resultSet = s.executeQuery(); + var output = new ArrayBuffer[Object]; + closing(resultSet) { + while (resultSet.next()) { + output += new { val id = resultSet.getString(1); val value = resultSet.getString(2) }; + } + output.toArray; + } + } + } + } + + def getAllJSONKeys(table: String): Array[String] = { + withConnection { c => + val s = c.prepareStatement("SELECT "+id("ID")+" FROM "+tableName(table)); + closing(s) { + var resultSet = s.executeQuery(); + var output = new ArrayBuffer[String]; + closing(resultSet) { + while (resultSet.next()) { + output += resultSet.getString(1); + } + output.toArray; + } + } + } + } + + // requires: table exists + // inserts key if it doesn't exist + def putJSON(table: String, key: String, json: String) { + withConnection { c=> + val update = c.prepareStatement("UPDATE "+tableName(table)+" SET "+id("JSON")+"=? WHERE "+id("ID")+"=?"); + closing(update) { + update.setString(1, json); + update.setString(2, key); + update.executeUpdate(); + if (update.getUpdateCount == 0) { + val insert = c.prepareStatement( + "INSERT INTO "+tableName(table)+" ("+id("ID")+", "+id("JSON")+") values (?,?)"); + closing(insert) { + insert.setString(1, key); + insert.setString(2, json); + insert.executeUpdate(); + } + } + } + } + } + + def deleteJSON(table: String, key: String) { + // requires: table exists + withConnection { c=> + val update = c.prepareStatement("DELETE FROM "+tableName(table)+" WHERE "+id("ID")+"=?"); + closing(update) { + update.setString(1, key); + update.executeUpdate(); + } + } + } + + private def metaName(table: String) = table+"_META"; + private def metaTableName(table: String) = tableName(metaName(table)); + private def textTableName(table: String) = tableName(table+"_TEXT"); + private def escapeSearchString(dbm: java.sql.DatabaseMetaData, s: String): String = { + val e = dbm.getSearchStringEscape(); + s.replace("_", e+"_").replace("%", e+"%"); + } + + private final val PAGE_SIZE = 20; + + def doesTableExist(connection: java.sql.Connection, table: String): Boolean = { + val databaseMetadata = connection.getMetaData; + val tables = databaseMetadata.getTables(null, null, + escapeSearchString(databaseMetadata, table), null); + closing(tables) { + tables.next(); + } + } + + def autoIncrementClause = if (isDerby) "GENERATED BY DEFAULT AS IDENTITY" else "AUTO_INCREMENT"; + + // creates table if it doesn't exist already + def createStringArrayTable(table: String) { + withConnection { c=> + if (! doesTableExist(c, metaName(table))) { // check to see if the *_META table exists + // create tables and indices + val s = c.createStatement; + closing(s) { + s.execute("CREATE TABLE "+metaTableName(table)+" ("+ + id("ID")+" VARCHAR(128) PRIMARY KEY NOT NULL, "+ + id("NUMID")+" INT UNIQUE "+autoIncrementClause+" "+ + ")"+createTableOptions); + val defaultOffsets = (1 to PAGE_SIZE).map(x=>"").mkString(","); + s.execute("CREATE TABLE "+textTableName(table)+" ("+ + ""+id("NUMID")+" INT, "+id("PAGESTART")+" INT, "+id("OFFSETS")+" VARCHAR(256) NOT NULL DEFAULT '"+defaultOffsets+ + "', "+id("DATA")+" "+longTextType+" NOT NULL"+ + ")"+createTableOptions); + s.execute("CREATE INDEX "+id(table+"-NUMID-PAGESTART")+" ON "+textTableName(table)+"("+id("NUMID")+", "+id("PAGESTART")+")"); + } + } + } + } + + // requires: table exists + // returns: null if key or (key,index) doesn't exist, else the value + def getStringArrayElement(table: String, key: String, index: Int): String = { + val (pageStart, offset) = getPageStartAndOffset(index); + val page = new StringArrayPage(table, key, pageStart, true); + page.data(offset); + } + + // requires: table exists + // returns: an array of the mappings present in the page that should hold the + // particular (key,index) mapping. the array may be empty or otherwise not + // contain the given (key,index). + def getPageStringArrayElements(table: String, key: String, index: Int): Array[IndexValueMapping] = { + val (pageStart, offset) = getPageStartAndOffset(index); + val page = new StringArrayPage(table, key, pageStart, true); + val buf = new scala.collection.mutable.ListBuffer[IndexValueMapping]; + + for(i <- 0 until page.data.length) { + val s = page.data(i); + if (s ne null) { + val n = pageStart + i; + buf += IndexValueMapping(n, s); + } + } + + buf.toArray; + } + + // requires: table exists + // creates key if doesn't exist + // value may be null + def putStringArrayElement(table: String, key: String, index: Int, value: String) { + val (pageStart, offset) = getPageStartAndOffset(index); + val page = new StringArrayPage(table, key, pageStart, false); + page.data(offset) = value; + page.updateDB(); + } + + def putMultipleStringArrayElements(table: String, key: String): Multiputter = new Multiputter { + var currentPage = None:Option[StringArrayPage]; + def flushPage() { + if (currentPage.isDefined) { + val page = currentPage.get; + page.updateDB(); + currentPage = None; + } + } + def finish() { + flushPage(); + } + def put(index: Int, value: String) { + try { + val (pageStart, offset) = getPageStartAndOffset(index); + if (currentPage.isEmpty || currentPage.get.pageStart != pageStart) { + flushPage(); + currentPage = Some(new StringArrayPage(table, key, pageStart, false)); + } + currentPage.get.data(offset) = value; + } + catch { + case e => { e.printStackTrace; throw e } + } + } + } + + trait Multiputter { + def put(index: Int, value: String); + def finish(); + } + + case class IndexValueMapping(index: Int, value: String); + + def clearStringArray(table: String, key: String) { + withConnection { c=> + val numid = getStringArrayNumId(c, table, key, false); + if (numid >= 0) { + { + val s = c.prepareStatement("DELETE FROM "+textTableName(table)+" WHERE "+id("NUMID")+"=?"); + closing(s) { + s.setInt(1, numid); + s.executeUpdate(); + } + } + { + val s = c.prepareStatement("DELETE FROM "+metaTableName(table)+" WHERE "+id("NUMID")+"=?"); + closing(s) { + s.setInt(1, numid); + s.executeUpdate(); + } + } + } + } + } + + private def getPageStartAndOffset(index: Int): (Int,Int) = { + val pageStart = (index / PAGE_SIZE) * PAGE_SIZE; + (pageStart, index - pageStart); + } + + // requires: table exists + // returns: numid of new string array + private def newStringArray(c: java.sql.Connection, table: String, key: String): Int = { + val s = c.prepareStatement("INSERT INTO "+metaTableName(table)+" ("+id("ID")+") VALUES (?)", + Statement.RETURN_GENERATED_KEYS); + closing(s) { + s.setString(1, key); + s.executeUpdate(); + val resultSet = s.getGeneratedKeys; + if (resultSet == null) + error("No generated numid for insert"); + closing(resultSet) { + if (! resultSet.next()) error("No generated numid for insert"); + resultSet.getInt(1); + } + } + } + + def getStringArrayNumId(c: java.sql.Connection, table: String, key: String, creating: Boolean): Int = { + val s = c.prepareStatement("SELECT "+id("NUMID")+" FROM "+metaTableName(table)+" WHERE "+id("ID")+"=?"); + closing(s) { + s.setString(1, key); + val resultSet = s.executeQuery(); + closing(resultSet) { + if (! resultSet.next()) { + if (creating) { + newStringArray(c, table, key); + } + else { + -1 + } + } + else { + resultSet.getInt(1); + } + } + } + } + + def getStringArrayAllKeys(table: String): Array[String] = { + withConnection { c=> + val s = c.prepareStatement("SELECT "+id("ID")+" FROM "+metaTableName(table)); + closing(s) { + val resultSet = s.executeQuery(); + closing(resultSet) { + val buf = new ArrayBuffer[String]; + while (resultSet.next()) { + buf += resultSet.getString(1); + } + buf.toArray; + } + } + } + } + + private class StringArrayPage(table: String, key: String, val pageStart: Int, readonly: Boolean) { + + val data = new Array[String](PAGE_SIZE); + + private val numid = withConnection { c=> + val nid = getStringArrayNumId(c, table, key, ! readonly); + + if (nid >= 0) { + val s = c.prepareStatement( + "SELECT "+id("OFFSETS")+","+id("DATA")+" FROM "+textTableName(table)+" WHERE "+id("NUMID")+"=? AND "+id("PAGESTART")+"=?"); + closing(s) { + s.setInt(1, nid); + s.setInt(2, pageStart); + val resultSet = s.executeQuery(); + closing(resultSet) { + if (! resultSet.next()) { + if (! readonly) { + val insert = c.prepareStatement("INSERT INTO "+textTableName(table)+ + " ("+id("NUMID")+", "+id("PAGESTART")+", "+id("DATA")+") VALUES (?,?,'')"); + closing(insert) { + insert.setInt(1, nid); + insert.setInt(2, pageStart); + insert.executeUpdate(); + } + } + } + else { + val offsetsField = resultSet.getString(1); + val dataField = resultSet.getString(2); + val offsetStrings = offsetsField.split(",", -1); + var i = 0; + var idx = 0; + while (i < PAGE_SIZE) { + val nstr = offsetStrings(i); + if (nstr != "") { + val n = nstr.toInt; + data(i) = dataField.substring(idx, idx+n); + idx += n; + } + i += 1; + } + } + } + } + } + nid; + } + + def updateDB() { + if (readonly) { + error("this is a readonly StringArrayPage"); + } + // assert: the relevant row of the TEXT table exists + if (data.find(_ ne null).isEmpty) { + withConnection { c=> + val update = c.prepareStatement("DELETE FROM "+textTableName(table)+ + " WHERE "+id("NUMID")+"=? AND "+id("PAGESTART")+"=?"); + closing(update) { + update.setInt(1, numid); + update.setInt(2, pageStart); + update.executeUpdate(); + } + } + } + else { + val offsetsStr = data.map(s => if (s eq null) "" else s.length.toString).mkString(","); + val dataStr = data.map(s => if (s eq null) "" else s).mkString(""); + withConnection { c=> + val s = c.prepareStatement("UPDATE "+textTableName(table)+ + " SET "+id("OFFSETS")+"=?, "+id("DATA")+"=? WHERE "+id("NUMID")+"=? AND "+id("PAGESTART")+"=?"); + closing(s) { + s.setString(1, offsetsStr); + s.setString(2, dataStr); + s.setInt(3, numid); + s.setInt(4, pageStart); + s.executeUpdate(); + } + } + } + } + } + + def close { + if (isDerby) { + cpds.close(); + try { + DriverManager.getConnection("jdbc:derby:;shutdown=true"); + } catch { + case e: SQLException => if (e.getErrorCode() != 50000) throw e + } + } + } +} + + |