aboutsummaryrefslogtreecommitdiffstats
path: root/infrastructure/framework-src/modules/sqlbase
diff options
context:
space:
mode:
Diffstat (limited to 'infrastructure/framework-src/modules/sqlbase')
-rw-r--r--infrastructure/framework-src/modules/sqlbase/persistent_vars.js57
-rw-r--r--infrastructure/framework-src/modules/sqlbase/sqlbase.js205
-rw-r--r--infrastructure/framework-src/modules/sqlbase/sqlcommon.js99
-rw-r--r--infrastructure/framework-src/modules/sqlbase/sqlobj.js551
4 files changed, 912 insertions, 0 deletions
diff --git a/infrastructure/framework-src/modules/sqlbase/persistent_vars.js b/infrastructure/framework-src/modules/sqlbase/persistent_vars.js
new file mode 100644
index 0000000..1c4cc95
--- /dev/null
+++ b/infrastructure/framework-src/modules/sqlbase/persistent_vars.js
@@ -0,0 +1,57 @@
+/**
+ * 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.
+ */
+
+
+import("sqlbase.sqlobj");
+import("sqlbase.sqlcommon");
+
+jimport("java.lang.System.out.println");
+
+// TODO: add caching?
+
+// Curently supports:
+// Strings
+
+function get(name) {
+ if (!sqlcommon.doesTableExist('persistent_vars')) {
+ return undefined;
+ }
+ var r = sqlobj.selectSingle('persistent_vars', {name: name});
+ if (!r) {
+ return undefined;
+ }
+ return r.stringVal;
+}
+
+function put(name, val) {
+ if (typeof(val) != 'string') {
+ throw Error("unsupported type for persistent_vars: "+typeof(val));
+ }
+
+ var r = sqlobj.selectSingle('persistent_vars', {name: name});
+ if (r) {
+ sqlobj.updateSingle('persistent_vars', {id: r.id}, {stringVal: val});
+ } else {
+ sqlobj.insert('persistent_vars', {name: name, stringVal: val});
+ }
+}
+
+function remove(name) {
+ var r = sqlobj.selectSingle('persistent_vars', {name: name});
+ if (r) {
+ sqlobj.deleteRows('persistent_vars', {id: r.id});
+ }
+}
diff --git a/infrastructure/framework-src/modules/sqlbase/sqlbase.js b/infrastructure/framework-src/modules/sqlbase/sqlbase.js
new file mode 100644
index 0000000..3df1a0f
--- /dev/null
+++ b/infrastructure/framework-src/modules/sqlbase/sqlbase.js
@@ -0,0 +1,205 @@
+/**
+ * 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.
+ */
+
+import("jsutils.*");
+import("sqlbase.sqlcommon");
+import("fastJSON");
+import("timer");
+
+jimport("java.lang.System.out.println");
+
+function _sqlbase() {
+ return sqlcommon.getSqlBase();
+}
+
+/**
+ * Creates a SQL table suitable for storing a mapping from String to JSON value.
+ * Maximum key length is 128 characters. Has no effect if the table already exists.
+ */
+function createJSONTable(tableName) {
+ _sqlbase().createJSONTable(String(tableName));
+}
+
+/**
+ * Retrieves a JavaScript object or value from a table. Returns undefined
+ * if there is no mapping for the given string key. Requires that the table
+ * exist.
+ */
+function getJSON(tableName, stringKey) {
+ var result = _sqlbase().getJSON(String(tableName), String(stringKey));
+ if (result) {
+
+ return fastJSON.parse(String(result))['x'];
+
+ /* performance-testing JSON
+ var obj1 = timer.time("JSON.parse (json2)", function() {
+ return JSON.parse(String(result))['x'];
+ });
+ var obj2 = timer.time("JSON.parse (fastJSON)", function() {
+ return fastJSON.parse(String(result))['x'];
+ });
+ return obj2;
+ */
+ }
+ return undefined;
+}
+
+function getAllJSON(tableName, start, count) {
+ var result = _sqlbase().getAllJSON(String(tableName), Number(start), Number(count));
+ return Array.prototype.map.call(result, function(x) {
+ return {id: x.id(), value: fastJSON.parse(String(x.value()))['x']};
+ })
+}
+
+function getAllJSONKeys(tableName) {
+ var result = _sqlbase().getAllJSONKeys(String(tableName));
+ return Array.prototype.map.call(result, function(x) { return String(x); });
+}
+
+/**
+ * Assigns a JavaScript object or primitive value to a string key in a table.
+ * Maximum key length is 128 characters. Requires that the table exist.
+ */
+function putJSON(tableName, stringKey, objectOrValue) {
+ var obj = ({x:objectOrValue});
+
+ var json = fastJSON.stringify(obj);
+
+ /* performance-testing JSON
+
+ var json1 = timer.time("JSON.stringify (json2)", function() {
+ return JSON.stringify(obj);
+ });
+ var json2 = timer.time("JSON.stringify (fastJSON)", function() {
+ return fastJSON.stringify(obj);
+ });
+
+ if (json1 != json2) {
+ println("json strings do not match!");
+ println("\n\n");
+ println(json1);
+ println("\n");
+ println(json2);
+ println("\n\n");
+ }*/
+
+ _sqlbase().putJSON(String(tableName), String(stringKey), json);
+}
+
+/**
+ * Removes the mapping for a string key from a table. Requires that the table
+ * exist.
+ */
+function deleteJSON(tableName, stringKey) {
+ _sqlbase().deleteJSON(String(tableName), String(stringKey));
+}
+
+/**
+ * Creates a SQL table suitable for storing a mapping from (key,n) to string.
+ * The mapping may be sparse, but storage is most efficient when n are consecutive.
+ * The "length" of the array is not stored and must be externally maintained.
+ * Maximum key length is 128 characters. This call has no effect if the table
+ * already exists.
+ */
+function createStringArrayTable(tableName) {
+ _sqlbase().createStringArrayTable(String(tableName));
+}
+
+/**
+ * Assigns a string value to a (key,n) pair in a StringArray table. Maximum key length
+ * is 128 characters. Requires that the table exist.
+ */
+function putStringArrayElement(tableName, stringKey, n, value) {
+ _sqlbase().putStringArrayElement(String(tableName), String(stringKey),
+ Number(n), String(value));
+}
+
+/**
+ * Equivalent to a series of consecutive puts of the elements of valueArray, with the first
+ * one going to n=startN, the second to n=startN+1, and so on, but much more efficient.
+ */
+function putConsecutiveStringArrayElements(tableName, stringKey, startN, valueArray) {
+ var putter = _sqlbase().putMultipleStringArrayElements(String(tableName), String(stringKey));
+ for(var i=0;i<valueArray.length;i++) {
+ putter.put(Number(startN)+i, String(valueArray[i]));
+ }
+ putter.finish();
+}
+
+/**
+ * Equivalent to a series of puts of the (key,value) entries of the JavaScript object
+ * nToValue, using as few database operations as possible.
+ */
+function putDictStringArrayElements(tableName, stringKey, nToValue) {
+ var nArray = [];
+ for(var n in nToValue) {
+ nArray.push(n);
+ }
+ nArray.sort(function(a,b) { return Number(a) - Number(b); });
+
+ var putter = _sqlbase().putMultipleStringArrayElements(String(tableName), String(stringKey));
+ nArray.forEach(function(n) {
+ putter.put(Number(n), String(nToValue[n]));
+ });
+ putter.finish();
+}
+
+/**
+ * Retrieves a string value from a StringArray table. Returns undefined
+ * if there is no mapping for the given (key,n) pair. Requires that the table
+ * exist.
+ */
+function getStringArrayElement(tableName, stringKey, n) {
+ var result = _sqlbase().getStringArrayElement(String(tableName),
+ String(stringKey), Number(n));
+ if (result) {
+ return String(result);
+ }
+ return undefined;
+}
+
+/**
+ * Retrieves all values from the database page that contains the mapping for n.
+ * Properties are added to destMap for n, if present in the database, and any other
+ * numeric entries in the same page. No return value.
+ */
+function getPageStringArrayElements(tableName, stringKey, n, destMap) {
+ var array = _sqlbase().getPageStringArrayElements(String(tableName), String(stringKey), n);
+ for(var i=0;i<array.length;i++) {
+ var entry = array[i];
+ destMap[entry.index()] = String(entry.value());
+ }
+}
+
+/**
+ * Removes the mapping for a (key,n) pair from a StringArray table. Requires that the table
+ * exist.
+ */
+function deleteStringArrayElement(tableName, stringKey, n) {
+ _sqlbase().putStringArrayElement(String(tableName), String(stringKey), Number(n), null);
+}
+
+/**
+ * Removes all mappings and metadata associated with a given key in a table.
+ */
+function clearStringArray(tableName, stringKey) {
+ _sqlbase().clearStringArray(String(tableName), stringKey);
+}
+
+function getStringArrayAllKeys(tableName) {
+ var result = _sqlbase().getStringArrayAllKeys(String(tableName));
+ return Array.prototype.map.call(result, function(x) { return String(x); });
+}
diff --git a/infrastructure/framework-src/modules/sqlbase/sqlcommon.js b/infrastructure/framework-src/modules/sqlbase/sqlcommon.js
new file mode 100644
index 0000000..360f5e2
--- /dev/null
+++ b/infrastructure/framework-src/modules/sqlbase/sqlcommon.js
@@ -0,0 +1,99 @@
+/**
+ * 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.
+ */
+
+import("jsutils.scalaF1")
+import("stringutils.startsWith");
+
+jimport("net.appjet.ajstdlib.SQLBase");
+jimport("java.lang.System.out.println");
+
+function _sqlbase() { return appjet.cache.sqlbase };
+
+function init(driver, url, username, password) {
+ var dbName = url.split(":")[1];
+ println("Using "+dbName+" database type.");
+
+ appjet.cache.sqlbase = new SQLBase(driver, url, username, password);
+
+ // Test the connection
+ println("Establishing "+dbName+" connection (this may take a minute)...");
+ try {
+ withConnection(function() {
+ return;
+ });
+ } catch (ex) {
+ println("Error establishing "+dbName+" connection:");
+ println(ex.toString().split('\n')[0]);
+ if (_sqlbase().isMysql()) {
+ println("Perhaps mysql server is not running, or you did not specify "+
+ "proper database credentials with --etherpad.SQL_PASSWORD "+
+ "and --etherpad.SQL_USERNAME?");
+ }
+ if (_sqlbase().isDerby()) {
+ println("Perhaps database directory "+appjet.config.derbyHome+
+ " is not writable?");
+ }
+ println("Exiting...");
+ Packages.java.lang.System.exit(1);
+ }
+ println(dbName+" connection established.");
+}
+
+function onShutdown() {
+ _sqlbase().close();
+}
+
+function withConnection(f) {
+ return _sqlbase().withConnection(scalaF1(f));
+}
+
+function inTransaction(f) {
+ return _sqlbase().inTransaction(scalaF1(f));
+}
+
+function closing(s, f) {
+ if (s instanceof java.sql.Connection) {
+ throw new java.lang.IllegalArgumentException("Don't want to use 'closing()' on a sql connection!");
+ }
+ try {
+ return f();
+ }
+ finally {
+ s.close();
+ }
+}
+
+function doesTableExist(table) {
+ return withConnection(function(conn) {
+ return _sqlbase().doesTableExist(conn, table);
+ });
+}
+
+function autoIncrementClause() {
+ return _sqlbase().autoIncrementClause();
+}
+
+function createTableOptions() {
+ return _sqlbase().createTableOptions();
+}
+
+function btquote(x) { return _sqlbase().quoteIdentifier(x); }
+
+function getSqlBase() { return _sqlbase(); }
+
+function isMysql() { return _sqlbase().isMysql(); }
+function isDerby() { return _sqlbase().isDerby(); }
+
diff --git a/infrastructure/framework-src/modules/sqlbase/sqlobj.js b/infrastructure/framework-src/modules/sqlbase/sqlobj.js
new file mode 100644
index 0000000..e599c92
--- /dev/null
+++ b/infrastructure/framework-src/modules/sqlbase/sqlobj.js
@@ -0,0 +1,551 @@
+/**
+ * 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.
+ */
+
+import("cache_utils.syncedWithCache");
+import("sqlbase.sqlcommon.*");
+import("jsutils.*");
+import("etherpad.log");
+
+jimport("java.lang.System.out.println");
+jimport("java.sql.Statement");
+
+function _withCache(name, fn) {
+ return syncedWithCache('sqlobj.'+name, fn);
+}
+
+function getIdColspec() {
+ return ('INT NOT NULL '+autoIncrementClause()+' PRIMARY KEY');
+}
+
+function getLongtextColspec(extra) {
+ var spec = getSqlBase().longTextType();
+ if (extra) {
+ spec = (spec + " " + extra);
+ }
+ return spec;
+}
+
+function getBoolColspec(extra) {
+ var spec;
+ if (isMysql()) {
+ spec = 'TINYINT(1)';
+ } else {
+ spec = 'SMALLINT';
+ }
+ if (extra) {
+ spec = (spec + " " + extra);
+ }
+ return spec;
+}
+
+function getDateColspec(extra) {
+ var spec;
+ if (isMysql()) {
+ spec = 'DATETIME';
+ } else {
+ spec = 'TIMESTAMP';
+ }
+ if (extra) {
+ spec = (spec + " " + extra);
+ }
+ return spec;
+}
+
+function _bq(x) { return btquote(x); }
+
+/*
+ * for debugging queries
+ */
+function _qdebug(q) {
+ if (appjet.config.debugSQL) {
+ println(q);
+ }
+}
+
+/** executeFn is either "execute" or "executeUpdate" "executeQuery" */
+function _execute(stmnt, executeFn) {
+ if (!executeFn) {
+ executeFn = 'execute';
+ }
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ return closing(pstmnt, function() {
+ _qdebug(stmnt);
+ return pstmnt[executeFn]();
+ });
+ });
+}
+
+function _executeUpdate(stmnt) {
+ return _execute(stmnt, 'executeUpdate');
+}
+
+function _executeQuery(stmnt) {
+ return _execute(stmnt, 'executeQuery');
+}
+
+/*
+ * Not all SQL/JS types supported.
+ */
+function _getJsValFromResultSet(rs, type, colName) {
+ var r;
+ if (type == java.sql.Types.VARCHAR ||
+ type == java.sql.Types.LONGVARCHAR ||
+ type == java.sql.Types.CHAR) {
+ r = String(rs.getString(colName));
+ } else if (type == java.sql.Types.TIMESTAMP) {
+ var t = rs.getTimestamp(colName);
+ if (t) {
+ r = new Date(t.getTime());
+ } else {
+ r = null;
+ }
+ } else if (type == java.sql.Types.BIGINT ||
+ type == java.sql.Types.INTEGER ||
+ type == java.sql.Types.SMALLINT ||
+ type == java.sql.Types.TINYINT) {
+ r = rs.getInt(colName);
+ } else if (type == java.sql.Types.DECIMAL) {
+ r = rs.getFloat(colName);
+ } else if (type == java.sql.Types.BIT) {
+ r = rs.getBoolean(colName);
+ } else {
+ throw Error("Cannot fetch sql type ID "+type+" (columnName = "+colName+")");
+ }
+
+ if (rs.wasNull()) {
+ r = null;
+ }
+ return r;
+}
+
+function _lookupColumnType(tableName, columnName) {
+ return withConnection(function(conn) {
+ var metadata = conn.getMetaData();
+ var rs = metadata.getColumns(null, null, tableName, columnName);
+ if (!rs) {
+ throw Error("Table '"+tableName+"' does not appear to have colum '"+columnName+"'.");
+ }
+ var rsmd = rs.getMetaData();
+ var colCount = rsmd.getColumnCount();
+// rs.first();
+ rs.next();
+ var type = rs.getInt("DATA_TYPE");
+ return type;
+ });
+}
+
+/* cached, on misses calls _lookuParameterType */
+function _getParameterType(tableName, columnName) {
+ var key = [tableName, columnName].join(".");
+ return _withCache('column-types', function(cache) {
+ if (!cache[key]) {
+ cache[key] = _lookupColumnType(tableName, columnName);
+ }
+ return cache[key];
+ });
+}
+
+/*
+ * Not all SQL/JS types supported.
+ */
+function _setPreparedValues(tableName, pstmnt, keyList, obj, indexOffset) {
+ if (!indexOffset) { indexOffset = 0; }
+
+ for (var i = 1; i <= keyList.length; i++) {
+ var k = keyList[i-1];
+ var v = obj[k];
+ var j = i + indexOffset;
+
+ if (v === undefined) {
+ throw Error("value is undefined for key "+k);
+ }
+
+ if (v === null) {
+ var type = _getParameterType(tableName, k);
+ pstmnt.setNull(j, type);
+ } else if (typeof(v) == 'string') {
+ pstmnt.setString(j, v);
+ } else if (typeof(v) == 'number') {
+ pstmnt.setInt(j, v);
+ } else if (typeof(v) == 'boolean') {
+ pstmnt.setBoolean(j, v);
+ } else if (v.valueOf && v.getDate && v.getHours) {
+ pstmnt.setTimestamp(j, new java.sql.Timestamp(+v));
+ } else {
+ throw Error("Cannot insert this type of javascript object: "+typeof(v)+" (key="+k+", value = "+v+")");
+ }
+ }
+}
+
+function _resultRowToJsObj(resultSet) {
+ var resultObj = {};
+
+ var metaData = resultSet.getMetaData();
+ var colCount = metaData.getColumnCount();
+
+ for (var i = 1; i <= colCount; i++) {
+ var colName = metaData.getColumnLabel(i);
+ var type = metaData.getColumnType(i);
+ resultObj[colName] = _getJsValFromResultSet(resultSet, type, colName);
+ }
+
+ return resultObj;
+}
+
+/*
+ * Inserts the object into the given table, and returns auto-incremented ID if any.
+ */
+function insert(tableName, obj) {
+ var keyList = keys(obj);
+
+ var stmnt = "INSERT INTO "+_bq(tableName)+" (";
+ stmnt += keyList.map(function(k) { return _bq(k); }).join(', ');
+ stmnt += ") VALUES (";
+ stmnt += keyList.map(function(k) { return '?'; }).join(', ');
+ stmnt += ")";
+
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt, Statement.RETURN_GENERATED_KEYS);
+ return closing(pstmnt, function() {
+ _setPreparedValues(tableName, pstmnt, keyList, obj, 0);
+ _qdebug(stmnt);
+ pstmnt.executeUpdate();
+ var rs = pstmnt.getGeneratedKeys();
+ if (rs != null) {
+ return closing(rs, function() {
+ if (rs.next()) {
+ return rs.getInt(1);
+ }
+ });
+ }
+ });
+ });
+};
+
+/*
+ * Selects a single object given the constraintMap. If there are more
+ * than 1 objects that match, it will return a single one of them
+ * (unspecified which one). If no objects match, returns null.
+ *
+ * constraints is a javascript object of column names to values.
+ * Currently only supports string equality of constraints.
+ */
+function selectSingle(tableName, constraints) {
+ var keyList = keys(constraints);
+
+ var stmnt = "SELECT * FROM "+_bq(tableName)+" WHERE (";
+ stmnt += keyList.map(function(k) { return '('+_bq(k)+' = '+'?)'; }).join(' AND ');
+ stmnt += ')';
+ if (isMysql()) {
+ stmnt += ' LIMIT 1';
+ }
+
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ return closing(pstmnt, function() {
+ _setPreparedValues(tableName, pstmnt, keyList, constraints, 0);
+ _qdebug(stmnt);
+ var resultSet = pstmnt.executeQuery();
+ return closing(resultSet, function() {
+ if (!resultSet.next()) {
+ return null;
+ }
+ return _resultRowToJsObj(resultSet);
+ });
+ });
+ });
+}
+
+function _makeConstraintString(key, value) {
+ if (typeof(value) != 'object' || ! (value instanceof Array)) {
+ return '('+_bq(key)+' = ?)';
+ } else {
+ var comparator = value[0];
+ return '('+_bq(key)+' '+comparator+' ?)';
+ }
+}
+
+function _preparedValuesConstraints(constraints) {
+ var c = {};
+ eachProperty(constraints, function(k, v) {
+ c[k] = (typeof(v) != 'object' || ! (v instanceof Array) ? v : v[1]);
+ });
+ return c;
+}
+
+function selectMulti(tableName, constraints, options) {
+ if (!options) {
+ options = {};
+ }
+
+ var constraintKeys = keys(constraints);
+
+ var stmnt = "SELECT * FROM "+_bq(tableName)+" ";
+
+ if (constraintKeys.length > 0) {
+ stmnt += "WHERE (";
+ stmnt += constraintKeys.map(function(key) {
+ return _makeConstraintString(key, constraints[key]);
+ }).join(' AND ');
+ stmnt += ')';
+ }
+
+ if (options.orderBy) {
+ var orderEntries = [];
+ options.orderBy.split(",").forEach(function(orderBy) {
+ var asc = "ASC";
+ if (orderBy.charAt(0) == '-') {
+ orderBy = orderBy.substr(1);
+ asc = "DESC";
+ }
+ orderEntries.push(_bq(orderBy)+" "+asc);
+ });
+ stmnt += " ORDER BY "+orderEntries.join(", ");
+ }
+
+ if (options.limit) {
+ stmnt += " LIMIT "+options.limit;
+ }
+
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ return closing(pstmnt, function() {
+ _setPreparedValues(
+ tableName, pstmnt, constraintKeys,
+ _preparedValuesConstraints(constraints), 0);
+
+ _qdebug(stmnt);
+ var resultSet = pstmnt.executeQuery();
+ var resultArray = [];
+
+ return closing(resultSet, function() {
+ while (resultSet.next()) {
+ resultArray.push(_resultRowToJsObj(resultSet));
+ }
+
+ return resultArray;
+ });
+ });
+ });
+}
+
+function executeRaw(stmnt, params) {
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ return closing(pstmnt, function() {
+ for (var i = 0; i < params.length; i++) {
+ var v = params[i];
+
+ if (v === undefined) {
+ throw Error("value is undefined for key "+i);
+ }
+
+ if (typeof(v) == 'object' && v.isnull) {
+ pstmnt.setNull(i+1, v.type);
+ } else if (typeof(v) == 'string') {
+ pstmnt.setString(i+1, v);
+ } else if (typeof(v) == 'number') {
+ pstmnt.setInt(i+1, v);
+ } else if (typeof(v) == 'boolean') {
+ pstmnt.setBoolean(i+1, v);
+ } else if (v.valueOf && v.getDate && v.getHours) {
+ pstmnt.setTimestamp(i+1, new java.sql.Timestamp(+v));
+ } else {
+ throw Error("Cannot insert this type of javascript object: "+typeof(v)+" (key="+i+", value = "+v+")");
+ }
+ }
+
+ _qdebug(stmnt);
+ var resultSet = pstmnt.executeQuery();
+ var resultArray = [];
+
+ return closing(resultSet, function() {
+ while (resultSet.next()) {
+ resultArray.push(_resultRowToJsObj(resultSet));
+ }
+
+ return resultArray;
+ });
+ });
+ });
+}
+
+/* returns number of rows updated */
+function update(tableName, constraints, obj) {
+ var objKeys = keys(obj);
+ var constraintKeys = keys(constraints);
+
+ var stmnt = "UPDATE "+_bq(tableName)+" SET ";
+ stmnt += objKeys.map(function(k) { return ''+_bq(k)+' = ?'; }).join(', ');
+ stmnt += " WHERE (";
+ stmnt += constraintKeys.map(function(k) { return '('+_bq(k)+' = ?)'; }).join(' AND ');
+ stmnt += ')';
+
+ return withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ return closing(pstmnt, function() {
+ _setPreparedValues(tableName, pstmnt, objKeys, obj, 0);
+ _setPreparedValues(tableName, pstmnt, constraintKeys, constraints, objKeys.length);
+ _qdebug(stmnt);
+ return pstmnt.executeUpdate();
+ });
+ });
+}
+
+function updateSingle(tableName, constraints, obj) {
+ var count = update(tableName, constraints, obj);
+ if (count != 1) {
+ throw Error("save count != 1. instead, count = "+count);
+ }
+}
+
+function deleteRows(tableName, constraints) {
+ var constraintKeys = keys(constraints);
+ var stmnt = "DELETE FROM "+_bq(tableName)+" WHERE (";
+ stmnt += constraintKeys.map(function(k) { return '('+_bq(k)+' = ?)'; }).join(' AND ');
+ stmnt += ')';
+ withConnection(function(conn) {
+ var pstmnt = conn.prepareStatement(stmnt);
+ closing(pstmnt, function() {
+ _setPreparedValues(tableName, pstmnt, constraintKeys, constraints);
+ _qdebug(stmnt);
+ pstmnt.executeUpdate();
+ });
+ })
+}
+
+//----------------------------------------------------------------
+// table management
+//----------------------------------------------------------------
+
+/*
+ * Create a SQL table, specifying column names and types with a
+ * javascript object.
+ */
+function createTable(tableName, colspec, indices) {
+ if (doesTableExist(tableName)) {
+ return;
+ }
+
+ var stmnt = "CREATE TABLE "+_bq(tableName)+ " (";
+ stmnt += keys(colspec).map(function(k) { return (_bq(k) + ' ' + colspec[k]); }).join(', ');
+ if (indices) {
+ stmnt += ', ' + keys(indices).map(function(k) { return 'INDEX (' + _bq(k) + ')'; }).join(', ');
+ }
+ stmnt += ')'+createTableOptions();
+ _execute(stmnt);
+}
+
+function dropTable(tableName) {
+ _execute("DROP TABLE "+_bq(tableName));
+}
+
+function dropAndCreateTable(tableName, colspec, indices) {
+ if (doesTableExist(tableName)) {
+ dropTable(tableName);
+ }
+
+ return createTable(tableName, colspec, indices);
+}
+
+function renameTable(oldName, newName) {
+ _executeUpdate("RENAME TABLE "+_bq(oldName)+" TO "+_bq(newName));
+}
+
+function modifyColumn(tableName, columnName, newSpec) {
+ _executeUpdate("ALTER TABLE "+_bq(tableName)+" MODIFY "+_bq(columnName)+" "+newSpec);
+}
+
+function alterColumn(tableName, columnName, alteration) {
+ var q = "ALTER TABLE "+_bq(tableName)+" ALTER COLUMN "+_bq(columnName)+" "+alteration;
+ _executeUpdate(q);
+}
+
+function changeColumn(tableName, columnName, newSpec) {
+ var q = ("ALTER TABLE "+_bq(tableName)+" CHANGE COLUMN "+_bq(columnName)
+ +" "+newSpec);
+ _executeUpdate(q);
+}
+
+function addColumns(tableName, colspec) {
+ inTransaction(function(conn) {
+ eachProperty(colspec, function(name, definition) {
+ var stmnt = "ALTER TABLE "+_bq(tableName)+" ADD COLUMN "+_bq(name)+" "+definition;
+ _executeUpdate(stmnt);
+ });
+ });
+}
+
+function dropColumn(tableName, columnName) {
+ var stmnt = "ALTER TABLE "+_bq(tableName)+" DROP COLUMN "+_bq(columnName);
+ _executeUpdate(stmnt);
+}
+
+function listTables() {
+ return withConnection(function(conn) {
+ var metadata = conn.getMetaData();
+ var resultSet = metadata.getTables(null, null, null, null);
+ var resultArray = [];
+
+ return closing(resultSet, function() {
+ while (resultSet.next()) {
+ resultArray.push(resultSet.getString("TABLE_NAME"));
+ }
+ return resultArray;
+ });
+ });
+}
+
+function setTableEngine(tableName, engineName) {
+ var stmnt = "ALTER TABLE "+_bq(tableName)+" ENGINE="+_bq(engineName);
+ _executeUpdate(stmnt);
+}
+
+function getTableEngine(tableName) {
+ if (!isMysql()) {
+ throw Error("getTableEngine() only supported by MySQL database type.");
+ }
+
+ var tableEngines = {};
+
+ withConnection(function(conn) {
+ var stmnt = "show table status";
+ var pstmnt = conn.prepareStatement(stmnt);
+ closing(pstmnt, function() {
+ _qdebug(stmnt);
+ var resultSet = pstmnt.executeQuery();
+ closing(resultSet, function() {
+ while (resultSet.next()) {
+ var n = resultSet.getString("Name");
+ var eng = resultSet.getString("Engine");
+ tableEngines[n] = eng;
+ }
+ });
+ });
+ });
+
+ return tableEngines[tableName];
+}
+
+function createIndex(tableName, columns) {
+ var indexName = "idx_"+(columns.join("_"));
+ var stmnt = "CREATE INDEX "+_bq(indexName)+" on "+_bq(tableName)+" (";
+ stmnt += columns.map(_bq).join(", ");
+ stmnt += ")";
+ _executeUpdate(stmnt);
+}
+