diff options
Diffstat (limited to 'trunk/infrastructure/framework-src/modules/sqlbase')
4 files changed, 866 insertions, 0 deletions
diff --git a/trunk/infrastructure/framework-src/modules/sqlbase/persistent_vars.js b/trunk/infrastructure/framework-src/modules/sqlbase/persistent_vars.js new file mode 100644 index 0000000..1c4cc95 --- /dev/null +++ b/trunk/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/trunk/infrastructure/framework-src/modules/sqlbase/sqlbase.js b/trunk/infrastructure/framework-src/modules/sqlbase/sqlbase.js new file mode 100644 index 0000000..3df1a0f --- /dev/null +++ b/trunk/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/trunk/infrastructure/framework-src/modules/sqlbase/sqlcommon.js b/trunk/infrastructure/framework-src/modules/sqlbase/sqlcommon.js new file mode 100644 index 0000000..360f5e2 --- /dev/null +++ b/trunk/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/trunk/infrastructure/framework-src/modules/sqlbase/sqlobj.js b/trunk/infrastructure/framework-src/modules/sqlbase/sqlobj.js new file mode 100644 index 0000000..4bc1263 --- /dev/null +++ b/trunk/infrastructure/framework-src/modules/sqlbase/sqlobj.js @@ -0,0 +1,505 @@ +/** + * 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.*"); + +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.INTEGER || + type == java.sql.Types.SMALLINT || + type == java.sql.Types.TINYINT) { + r = rs.getInt(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.getColumnName(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; + }); + }); + }); +} + +/* 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); +} + |