Menu
▾
▴
[CIShell-SVN] SF.net SVN: cishell:[1020] trunk/core/org.cishell.utilities
|
From: <fu...@us...> - 2010-01-21 20:31:35
|
Revision: 1020
http://cishell.svn.sourceforge.net/cishell/?rev=1020&view=rev
Author: fugu13
Date: 2010-01-21 20:31:28 +0000 (Thu, 21 Jan 2010)
Log Message:
-----------
Add various database utilities.
Modified Paths:
--------------
trunk/core/org.cishell.utilities/META-INF/MANIFEST.MF
trunk/core/org.cishell.utilities/src/org/cishell/utilities/DatabaseUtilities.java
Added Paths:
-----------
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/Column.java
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ColumnPair.java
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/DatabaseTable.java
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKey.java
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKeyNameWithTable.java
trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/InvalidRepresentationException.java
Modified: trunk/core/org.cishell.utilities/META-INF/MANIFEST.MF
===================================================================
--- trunk/core/org.cishell.utilities/META-INF/MANIFEST.MF 2010-01-14 21:02:06 UTC (rev 1019)
+++ trunk/core/org.cishell.utilities/META-INF/MANIFEST.MF 2010-01-21 20:31:28 UTC (rev 1020)
@@ -20,6 +20,7 @@
prefuse.util,
prefuse.util.collections
Export-Package: org.cishell.utilities,
+ org.cishell.utilities.database,
org.cishell.utilities.mutateParameter,
org.cishell.utilities.mutateParameter.defaultvalue,
org.cishell.utilities.mutateParameter.dropdown,
Modified: trunk/core/org.cishell.utilities/src/org/cishell/utilities/DatabaseUtilities.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/DatabaseUtilities.java 2010-01-14 21:02:06 UTC (rev 1019)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/DatabaseUtilities.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -2,12 +2,18 @@
import java.sql.Connection;
import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
import org.cishell.framework.algorithm.AlgorithmExecutionException;
import org.cishell.service.database.Database;
public class DatabaseUtilities {
public static void closeConnectionQuietly(Connection connection) {
+ if(connection == null) {
+ return;
+ }
try {
connection.close();
} catch (SQLException e) {
@@ -25,4 +31,38 @@
}
return connection;
}
+
+ public static String getInExpression(List<String> columns, List<Map<String, Object>> valueMaps) {
+ String columnNames = implodeAndWrap(columns);
+
+ List<String> values = new ArrayList<String>();
+ for(Map<String, Object> oldValues : valueMaps) {
+ List<String> rowValues = new ArrayList<String>();
+ for(String column : columns) {
+ rowValues.add(formatValue(oldValues.get(column)));
+ }
+ values.add(implodeAndWrap(rowValues));
+ }
+ String columnValues = implodeAndWrap(values);
+
+
+ //first make part with column names
+ //then make part with groups of column values
+ return columnNames + " IN " + columnValues;
+ }
+
+ //TODO: expand this to other sorts of SQL datatypes (per their Prefuse table equivalents)
+ public static String formatValue(Object value) {
+ if(value == null) {
+ return "NULL";
+ } else if(value instanceof Number) {
+ return value.toString();
+ } else {
+ return "'" + value.toString() + "'";
+ }
+ }
+
+ public static String implodeAndWrap(List<String> values) {
+ return "(" + StringUtilities.implodeList(values, ", ") + ")";
+ }
}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/Column.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/Column.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/Column.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,55 @@
+package org.cishell.utilities.database;
+
+import java.sql.Types;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Map;
+import java.util.Set;
+
+public class Column {
+
+ public final String name;
+ public final int type;
+ public final int size;
+
+ public static final Map<Integer, String> TYPE_MAP = constructTypeMap();
+ public static final Set<Integer> SIZED_TYPES = constructSizedTypes();
+
+ private static Map<Integer, String> constructTypeMap() { //if this ever gets derby specific, it shouldn't go here
+ Map<Integer, String> typeMap = new HashMap<Integer, String>() {{
+ put(Types.CHAR, "char");
+ put(Types.DATE, "date");
+ put(Types.DOUBLE, "double");
+ put(Types.FLOAT, "float");
+ put(Types.INTEGER, "integer");
+ put(Types.SMALLINT, "smallint");
+ put(Types.TIME, "time");
+ put(Types.VARCHAR, "varchar");
+ }};
+ return Collections.unmodifiableMap(typeMap);
+ }
+
+ private static Set<Integer> constructSizedTypes() {
+ Set<Integer> sizedTypes = new HashSet<Integer>() {{
+ add(Types.CHAR);
+ add(Types.VARCHAR);
+
+ }};
+ return null;
+ }
+
+ public Column(String name, int type, int size) {
+ this.name = name;
+ this.type = type;
+ this.size = size;
+ }
+
+
+
+ public String getDefinition() {
+ // TODO Auto-generated method stub
+ return null;
+ }
+
+}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ColumnPair.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ColumnPair.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ColumnPair.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,23 @@
+package org.cishell.utilities.database;
+
+public final class ColumnPair {
+ public final String local;
+ public final String foreign;
+
+ public ColumnPair(String local, String foreign) {
+ this.local = local;
+ this.foreign = foreign;
+ }
+
+ public boolean equals(Object other) {
+ if(!(other instanceof ColumnPair)) {
+ return false;
+ }
+ ColumnPair o = (ColumnPair) other;
+ return o.local.equals(this.local) && o.foreign.equals(this.foreign);
+ }
+
+ public int hashCode() {
+ return local.hashCode() * 31 + foreign.hashCode();
+ }
+}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/DatabaseTable.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/DatabaseTable.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/DatabaseTable.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,193 @@
+package org.cishell.utilities.database;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import org.cishell.utilities.DatabaseUtilities;
+
+public final class DatabaseTable {
+ public final String catalog;
+ public final String schema;
+ public final String name;
+
+ public DatabaseTable(String catalog, String schema, String name) {
+ this.catalog = catalog == null ? "" : catalog.intern();
+ this.schema = schema == null ? "" : schema.intern();
+ this.name = name == null ? "" : name.intern();
+ }
+
+ public static DatabaseTable fromRepresentation(String representation) throws InvalidRepresentationException {
+ String[] parts = representation.split("\\.");
+ switch(parts.length) {
+ case 1:
+ return new DatabaseTable(null, null, parts[0]);
+ case 2:
+ return new DatabaseTable(null, parts[0], parts[1]);
+ case 3:
+ return new DatabaseTable(parts[0], parts[1], parts[2]);
+ default:
+ throw new InvalidRepresentationException("The representation '" + representation + "' has the wrong number of parts!");
+ }
+ }
+
+ public static DatabaseTable[] availableTables(Connection connection) throws SQLException {
+ ResultSet results = connection.getMetaData().getTables(null, null, null, new String[]{"TABLE"});
+
+ List<DatabaseTable> tables = new ArrayList<DatabaseTable>();
+ while(results.next()) {
+ tables.add(new DatabaseTable(results.getString(1), results.getString(2), results.getString(3)));
+ }
+
+ results.close();
+
+ return tables.toArray(new DatabaseTable[]{});
+ }
+
+ public String toString() {
+ StringBuilder output = new StringBuilder();
+ if(catalog != null && catalog != "") {
+ output.append(catalog);
+ output.append('.');
+ }
+ if(schema != null && schema != "") {
+ output.append(schema);
+ output.append('.');
+ }
+
+ output.append(name);
+
+ return output.toString();
+ }
+
+ public boolean equals(Object other) {
+ if(!(other instanceof DatabaseTable)) {
+ return false;
+ }
+ DatabaseTable o = (DatabaseTable) other;
+ return o.catalog == this.catalog && o.schema == this.schema && o.name == this.name;
+ }
+
+
+ public int hashCode() {
+ int hash = catalog.hashCode() * 7;
+ hash += schema.hashCode() * 5;
+ hash += name.hashCode() * 3;
+ return hash;
+
+ }
+
+ public boolean presentInDatabase(Connection connection) throws SQLException {
+ ResultSet results = connection.getMetaData().getTables(catalog, schema, name, new String[]{"TABLE"});
+ boolean foundOne = results.next();
+ results.close();
+ return foundOne;
+ }
+
+ public boolean hasPrimaryKey(Connection connection) throws SQLException {
+ return getPrimaryKeyColumns(connection).length != 0;
+ }
+
+ public ForeignKey[] getRelations(Connection connection) throws SQLException {
+ ResultSet related = connection.getMetaData().getExportedKeys(catalog, schema, name);
+ Map<ForeignKeyNameWithTable, Set<ColumnPair>> correspondences = new HashMap<ForeignKeyNameWithTable, Set<ColumnPair>>();
+ while(related.next()) {
+ String foreignKeyName = related.getString(12);
+ DatabaseTable relatedTable = new DatabaseTable(related.getString(5), related.getString(6), related.getString(7));
+ ForeignKeyNameWithTable key = new ForeignKeyNameWithTable(foreignKeyName, relatedTable);
+ ColumnPair pair = new ColumnPair(related.getString(4), related.getString(8));
+ if(!correspondences.containsKey(key)) {
+ correspondences.put(key, new HashSet<ColumnPair>());
+ }
+ correspondences.get(key).add(pair);
+ }
+
+ related.close();
+
+ return makeForeignKeys(correspondences);
+ }
+
+ private ForeignKey[] makeForeignKeys(
+ Map<ForeignKeyNameWithTable, Set<ColumnPair>> correspondences) {
+ ForeignKey[] foreignKeys = new ForeignKey[correspondences.size()];
+ int index = 0;
+ for(Map.Entry<ForeignKeyNameWithTable, Set<ColumnPair>> entry : correspondences.entrySet()) {
+ foreignKeys[index] = new ForeignKey(this, entry.getKey().table, entry.getValue());
+ index++;
+ }
+ return foreignKeys;
+ }
+
+ public String[] getPrimaryKeyColumns(Connection connection) throws SQLException {
+ ResultSet columns = connection.getMetaData().getPrimaryKeys(catalog, schema, name);
+ List<String> columnNames = new ArrayList<String>();
+ while(columns.next()) {
+ columnNames.add(columns.getString(4));
+ }
+ return columnNames.toArray(new String[]{});
+ }
+
+ public void deleteRowsByColumns(List<Map<String, Object>> otherEntities, Connection connection) throws SQLException {
+ if(otherEntities.size() == 0) {
+ return;
+ }
+ List<String> columns = new ArrayList<String>(otherEntities.get(0).keySet());
+ String deleteStatement = constructDeleteStatement(columns, otherEntities);
+ connection.createStatement().executeUpdate(deleteStatement);
+ }
+
+ private String constructDeleteStatement(List<String> columns,
+ List<Map<String, Object>> otherEntities) {
+ return "DELETE FROM " + this.toString() + " WHERE " + DatabaseUtilities.getInExpression(columns, otherEntities);
+ }
+
+ public void duplicateTable(Connection originalConnection,
+ Connection newConnection) throws SQLException {
+ this.duplicateTableStructure(originalConnection, newConnection);
+ //TODO: finish
+
+ }
+
+ public void duplicateTableStructure(Connection originalConnection,
+ Connection newConnection) throws SQLException {
+ Column[] columns = getColumns(originalConnection);
+ String createStatement = createCreateStatement(columns);
+ newConnection.createStatement().executeUpdate(createStatement);
+ }
+
+ private String createCreateStatement(Column[] columns) {
+ List<String> definitions = new ArrayList<String>();
+ for(int ii = 0; ii < columns.length; ii++) {
+ definitions.add(columns[ii].getDefinition());
+ }
+ return "CREATE TABLE " + this.toString() + DatabaseUtilities.implodeAndWrap(definitions);
+ }
+
+ private Column[] getColumns(Connection connection) throws SQLException {
+ ResultSet results = connection.getMetaData().getColumns(this.catalog, this.schema, this.name, null);
+ List<Column> columns = new ArrayList<Column>();
+ while(results.next()) {
+ columns.add(new Column(results.getString(4), results.getInt(5), results.getInt(7)));
+ }
+ return columns.toArray(new Column[]{});
+ }
+
+ public void transferPrimaryKey(Connection originalConnection,
+ Connection newConnection) {
+ // TODO Auto-generated method stub
+
+ }
+
+ public void pointForeignKeys(Connection originalConnection,
+ Connection newConnection) {
+ // TODO Auto-generated method stub
+
+ }
+}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKey.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKey.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKey.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,82 @@
+package org.cishell.utilities.database;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import org.cishell.utilities.DatabaseUtilities;
+import org.cishell.utilities.StringUtilities;
+
+public final class ForeignKey {
+
+ final public DatabaseTable localTable;
+ final public DatabaseTable otherTable;
+ final public Set<ColumnPair> pairs;
+
+ public ForeignKey(DatabaseTable localTable, DatabaseTable otherTable, Set<ColumnPair> pairs) {
+ this.localTable = localTable;
+ this.otherTable = otherTable;
+ this.pairs = Collections.unmodifiableSet(pairs);
+ }
+
+ public void repoint(List<Map<String, Object>> from,
+ Map<String, Object> to, Connection connection) throws SQLException {
+
+ String updateQuery = constructUpdateQuery(from, to);
+ //TODO: remove
+ System.err.println("Issuing update: " + updateQuery);
+ connection.createStatement().executeUpdate(updateQuery);
+
+ }
+
+ private String constructUpdateQuery(List<Map<String, Object>> from,
+ Map<String, Object> to) {
+ return "UPDATE " + otherTable.toString() + " SET "+ formatUpdates(to) + " WHERE "
+ + DatabaseUtilities.getInExpression(getForeignColumnNames(), translateToForeignNames(from));
+ }
+
+
+
+ private List<Map<String, Object>> translateToForeignNames(
+ List<Map<String, Object>> from) {
+ List<Map<String, Object>> output = new ArrayList<Map<String, Object>>();
+ for(Map<String, Object> fromValues : from) {
+ Map<String, Object> toValues = new HashMap<String, Object>();
+ for(ColumnPair pair : pairs) {
+ toValues.put(pair.foreign, fromValues.get(pair.local));
+ }
+ output.add(toValues);
+ }
+
+
+ return output;
+ }
+
+ private List<String> getForeignColumnNames() {
+ List<String> foreignColumns = new ArrayList<String>();
+ for(ColumnPair pair : pairs) {
+ foreignColumns.add(pair.foreign);
+ }
+ return foreignColumns;
+ }
+
+
+
+ private String formatUpdates(Map<String, Object> to) {
+ List<String> updateStatements = new ArrayList<String>();
+ for(ColumnPair pair : pairs) {
+ String foreignColumn = pair.foreign;
+ Object newValue = to.get(pair.local);
+ updateStatements.add(foreignColumn + " = " + DatabaseUtilities.formatValue(newValue));
+ }
+ return StringUtilities.implodeList(updateStatements, ", ");
+ }
+
+
+
+}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKeyNameWithTable.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKeyNameWithTable.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/ForeignKeyNameWithTable.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,23 @@
+package org.cishell.utilities.database;
+
+public final class ForeignKeyNameWithTable {
+ public final String name;
+ public final DatabaseTable table;
+
+ public ForeignKeyNameWithTable(String name, DatabaseTable table) {
+ this.name = name;
+ this.table = table;
+ }
+
+ public boolean equals(Object other) {
+ if(!(other instanceof ForeignKeyNameWithTable)) {
+ return false;
+ }
+ ForeignKeyNameWithTable o = (ForeignKeyNameWithTable) other;
+ return o.name.equals(this.name) && o.table.equals(this.table);
+ }
+
+ public int hashCode() {
+ return name.hashCode() * 31 + table.hashCode();
+ }
+}
Added: trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/InvalidRepresentationException.java
===================================================================
--- trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/InvalidRepresentationException.java (rev 0)
+++ trunk/core/org.cishell.utilities/src/org/cishell/utilities/database/InvalidRepresentationException.java 2010-01-21 20:31:28 UTC (rev 1020)
@@ -0,0 +1,14 @@
+package org.cishell.utilities.database;
+
+public class InvalidRepresentationException extends Exception {
+
+ /**
+ *
+ */
+ private static final long serialVersionUID = 1L;
+
+ public InvalidRepresentationException(String message) {
+ super(message);
+ }
+
+}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
