Author: cbegin Date: Tue Jan 24 14:47:31 2006 New Revision: 372039 URL: http://svn.apache.org/viewcvs?rev=372039&view=rev Log: added primary key support to database metadata
Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/ClassMapper.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Canonicalizer.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Match.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/MatchCalculator.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/NameMatcher.java Removed: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/Canonicalizer.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/Match.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/MatchCalculator.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/NameMatcher.java Modified: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/DatabaseFactory.java ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/Table.java ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/CanonicalizerTest.java ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/DatabaseMetadataTest.java ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/MatchCalculatorTest.java ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/NameMatcherTest.java Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/ClassMapper.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/ClassMapper.java?rev=372039&view=auto ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/ClassMapper.java (added) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/ClassMapper.java Tue Jan 24 14:47:31 2006 @@ -0,0 +1,7 @@ +package com.ibatis.sqlmap.engine.mapper; + +public class ClassMapper { + + + +} Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Canonicalizer.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Canonicalizer.java?rev=372039&view=auto ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Canonicalizer.java (added) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Canonicalizer.java Tue Jan 24 14:47:31 2006 @@ -0,0 +1,177 @@ +package com.ibatis.sqlmap.engine.mapper.matcher; + +import java.util.StringTokenizer; +import java.util.Map; +import java.util.HashMap; +import java.util.Iterator; + +public class Canonicalizer { + + public Map buildCanonicalMap(String[] originals) { + return buildCanonicalMap(originals, null); + } + public Map buildCanonicalMap(String[] originals, String parentName) { + Map map = new HashMap(); + for (int i=0; i < originals.length; i++) { + map.put(originals[i], originals[i]); + } + upperCase(map); + removeUnderscores(map); + removePKFK(map); + removePrefixes(map); + removeSuffixes(map); + removePluralization(map); + removeParentName(map, parentName); + return map; + } + + private void removeParentName(Map map, String parentName) { + if (parentName != null) { + parentName = parentName.toUpperCase(); + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + if (canonical.startsWith(parentName)) { + map.put(original, canonical.substring(parentName.length())); + } + if (canonical.endsWith(parentName)) { + map.put(original, canonical.substring(0, canonical.length() - parentName.length())); + } + } + } + } + + private void upperCase(Map map) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + map.put(original, canonical.toUpperCase()); + } + } + + private void removePKFK(Map map) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + if (canonical.startsWith("PK")) { + map.put(original, canonical.substring(2)); + } else if (canonical.startsWith("FK")) { + map.put(original, canonical.substring(2)); + } + if (canonical.endsWith("PK")) { + map.put(original, canonical.substring(0, canonical.length() - 2)); + } else if (canonical.endsWith("FK")) { + map.put(original, canonical.substring(0, canonical.length() - 2)); + } + } + } + + private void removePrefixes(Map map) { + int prefix = findPrefixLength(map); + + if (prefix > 1) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + map.put(original, canonical.substring(prefix)); + } + } + } + + private void removeSuffixes(Map map) { + int suffix = findSuffixLength(map); + + if (suffix > 1) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + map.put(original, canonical.substring(0, canonical.length() - suffix)); + } + } + } + + private int findPrefixLength(Map map) { + String[] originals = (String[])map.keySet().toArray(new String[map.keySet().size()]); + char[] samples = ((String) map.get(findShortestString(originals))).toCharArray(); + int prefix = 0; + for (int i=0; i < samples.length; i++) { + for (int j=0; j < originals.length; j++) { + String original = originals[j]; + String canonical = (String) map.get(original); + if (canonical.charAt(prefix) != samples[i]) { + return prefix; + } + } + prefix++; + } + if (prefix == samples.length) { + prefix = 0; + } + return prefix; + } + + private int findSuffixLength(Map map) { + String[] originals = (String[])map.keySet().toArray(new String[map.keySet().size()]); + char[] samples = ((String) map.get(findShortestString(originals))).toCharArray(); + int suffix = 0; + for (int i=0; i < samples.length; i++) { + for (int j=0; j < originals.length; j++) { + String original = originals[j]; + String canonical = (String) map.get(original); + if (canonical.charAt(canonical.length() - suffix - 1) != samples[samples.length - i - 1]) { + return suffix; + } + } + suffix++; + } + if (suffix == samples.length) { + suffix = 0; + } + return suffix; + } + + private String findShortestString(String[] originals) { + String shortest = originals[0]; + for (int i=0; i < originals.length; i++) { + if (originals[i].length() < shortest.length()) { + shortest = originals[i]; + } + } + return shortest; + } + + private void removeUnderscores (Map map) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + map.put(original, removeUnderscoresFromString(canonical)); + } + } + + private void removePluralization (Map map) { + Iterator i = map.keySet().iterator(); + while (i.hasNext()) { + String original = (String) i.next(); + String canonical = (String) map.get(original); + if (canonical.endsWith("S") || canonical.endsWith("s")) { + map.put(original, canonical.substring(0, canonical.length() - 1)); + } + } + } + + private String removeUnderscoresFromString (String original) { + StringBuffer canonical = new StringBuffer(); + StringTokenizer parser = new StringTokenizer (original, "_", false); + while(parser.hasMoreTokens()) { + canonical.append(parser.nextToken()); + } + return canonical.toString(); + } + +} Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Match.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Match.java?rev=372039&view=auto ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Match.java (added) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/Match.java Tue Jan 24 14:47:31 2006 @@ -0,0 +1,32 @@ +package com.ibatis.sqlmap.engine.mapper.matcher; + +public class Match { + + private String property; + private String field; + private double matchScore; + + public Match(String property, String field, double matchScore) { + this.property = property; + this.field = field; + this.matchScore = matchScore; + } + + public String getProperty() { + return property; + } + + public String getField() { + return field; + } + + public double getMatchScore() { + return matchScore; + } + + + public String toString() { + return property + " => " + field + " ("+matchScore+")"; + } + +} Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/MatchCalculator.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/MatchCalculator.java?rev=372039&view=auto ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/MatchCalculator.java (added) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/MatchCalculator.java Tue Jan 24 14:47:31 2006 @@ -0,0 +1,69 @@ +package com.ibatis.sqlmap.engine.mapper.matcher; + +import java.util.Set; +import java.util.HashSet; +import java.util.Iterator; + +public class MatchCalculator { + + private static final int SET_BEGIN = 2; + private static final int SET_END = 5; + private static final double MAX_VALUE = 1; + + public MatchCalculator() { + } + + public double calculateMatch (String first, String second) { + + Set firstSet = buildSets(first); + Set secondSet = buildSets(second); + + double value1 = compareSets(firstSet, secondSet); + double value2 = compareSets(secondSet, firstSet); + return (value1 + value2) / 2; + } + + private Set buildSets(String string) { + Set set = new HashSet(); + char[] chars = string.toUpperCase().toCharArray(); + for (int i = SET_BEGIN; i <= SET_END; i++) { + setsOf(i, chars, set); + } + return set; + } + + private void setsOf(int size, char[] chars, Set set) { + for (int i=0; i < chars.length - size + 1; i++) { + char[] group = new char[size]; + for (int j=0; j < group.length; j++) { + group[j] = chars[i+j]; + } + set.add(new String(group)); + } + } + + private double compareSets(Set firstSet, Set secondSet) { + double value = MAX_VALUE; + double interval = calculateInterval(firstSet, secondSet); + Iterator i = firstSet.iterator(); + while (i.hasNext()) { + String group = (String)i.next(); + if (!secondSet.contains(group)) { + value -= interval; + } + } + return value; + } + + private double calculateInterval(Set firstSet, Set secondSet) { + double interval; + if (firstSet.size() > secondSet.size()) { + interval = MAX_VALUE / firstSet.size(); + } else { + interval = MAX_VALUE / secondSet.size(); + } + return interval; + } + + +} Added: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/NameMatcher.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/NameMatcher.java?rev=372039&view=auto ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/NameMatcher.java (added) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/matcher/NameMatcher.java Tue Jan 24 14:47:31 2006 @@ -0,0 +1,94 @@ +package com.ibatis.sqlmap.engine.mapper.matcher; + +import com.ibatis.sqlmap.engine.mapper.matcher.Canonicalizer; +import com.ibatis.sqlmap.engine.mapper.matcher.Match; +import com.ibatis.sqlmap.engine.mapper.matcher.MatchCalculator; + +import java.util.*; + +public class NameMatcher { + + public Map matchNames(String[] properties, String[] fields) { + Map propertyMap = invertMap(buildCanonicalMap(properties)); + Map fieldMap = invertMap(buildCanonicalMap(fields)); + + String[] canonicalProperties = setToStringArray(propertyMap.keySet()); + String[] canonicalFields = setToStringArray(fieldMap.keySet()); + + // consider building in both directions + // consider tracking which fields have already been mapped to avoid duplicate mappings + List matchList = buildMatchList(canonicalProperties, canonicalFields); + + Map matchedNames = new HashMap(); + Iterator matches = matchList.iterator(); + while(matches.hasNext()) { + Match match = (Match)matches.next(); + String property = (String) propertyMap.get(match.getProperty()); + String field = (String) fieldMap.get(match.getField()); + matchedNames.put(property, field); + } + return matchedNames; + } + + private Map buildCanonicalMap(String[] properties) { + return new Canonicalizer().buildCanonicalMap(properties); + } + + private String[] setToStringArray(Set set) { + return (String[]) set.toArray(new String[set.size()]); + } + + private List buildMatchList(String[] canonicalProperties, String[] canonicalFields) { + List matchList = new LinkedList(); + MatchCalculator calc = new MatchCalculator(); + for (int i = 0; i < canonicalProperties.length; i++) { + for (int j = 0; j < canonicalFields.length; j++) { + String prop = canonicalProperties[i]; + String field = canonicalFields[j]; + double score = calc.calculateMatch(prop, field); + Match match = new Match(prop, field, score); + matchList.add(match); + } + } + sortMatches(matchList); + removeDuplicatesAndLowScores (matchList); + return matchList; + } + + private void removeDuplicatesAndLowScores(List matchList) { + Set usedProperties = new HashSet(); + Set usedFields = new HashSet(); + Iterator i = matchList.iterator(); + while (i.hasNext()) { + Match m = (Match)i.next(); + if (usedProperties.contains(m.getProperty()) || usedFields.contains(m.getField()) || m.getMatchScore() < 0.40) { + i.remove(); + } else { + usedProperties.add(m.getProperty()); + usedFields.add(m.getField()); + } + } + + } + + private void sortMatches(List matchList) { + Collections.sort(matchList, new Comparator () { + public int compare(Object o1, Object o2) { + Match m1 = (Match) o1; + Match m2 = (Match) o2; + return m1.getMatchScore() < m2.getMatchScore() ? 1 : m1.getMatchScore() > m2.getMatchScore() ? -1 : 0; + } + }); + } + + private Map invertMap(Map original) { + Map inverse = new HashMap(); + Iterator keys = original.keySet().iterator(); + while (keys.hasNext()) { + Object key = keys.next(); + Object value = original.get(key); + inverse.put(value, key); + } + return inverse; + } +} Modified: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/DatabaseFactory.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/DatabaseFactory.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/DatabaseFactory.java (original) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/DatabaseFactory.java Tue Jan 24 14:47:31 2006 @@ -1,40 +1,52 @@ package com.ibatis.sqlmap.engine.mapper.metadata; import javax.sql.DataSource; -import java.sql.SQLException; -import java.sql.Connection; -import java.sql.ResultSet; -import java.sql.DatabaseMetaData; +import java.sql.*; public class DatabaseFactory { - private DatabaseFactory() { } - public static Database newDatabase (DataSource dataSource, String catalog, String schema) throws SQLException { - Database database = new Database (catalog, schema); + public static Database newDatabase(DataSource dataSource, String catalog, String schema) throws SQLException { + Database database = new Database(catalog, schema); Connection conn = dataSource.getConnection(); ResultSet rs = null; try { DatabaseMetaData dbmd = conn.getMetaData(); - rs = dbmd.getColumns(catalog, schema, null, null); - while (rs.next()) { - String tableName = rs.getString ("TABLE_NAME"); - String columnName = rs.getString ("COLUMN_NAME"); - int dataType = Integer.parseInt(rs.getString ("DATA_TYPE")); - Table table = database.getTable(tableName); - if (table == null) { - table = new Table(tableName); - database.addTable(table); + + try { + rs = dbmd.getColumns(catalog, schema, null, null); + while (rs.next()) { + String tableName = rs.getString("TABLE_NAME"); + String columnName = rs.getString("COLUMN_NAME"); + int dataType = Integer.parseInt(rs.getString("DATA_TYPE")); + Table table = database.getTable(tableName); + if (table == null) { + table = new Table(tableName); + database.addTable(table); + } + table.addColumn(new Column(columnName, dataType)); } - table.addColumn(new Column(columnName, dataType)); + } finally { + if (rs != null) rs.close(); } - } finally { + try { - if (rs != null) rs.close(); + String[] tableNames = database.getTableNames(); + for (int i=0; i < tableNames.length; i++) { + Table table = database.getTable(tableNames[i]); + rs = dbmd.getPrimaryKeys(catalog, schema, table.getName()); + if (rs.next()) { + String columnName = rs.getString("COLUMN_NAME"); + table.setPrimaryKey (table.getColumn(columnName)); + } + } } finally { - conn.close(); + if (rs != null) rs.close(); } + + } finally { + conn.close(); } return database; } Modified: ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/Table.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/Table.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/Table.java (original) +++ ibatis/trunk/java/mapper/mapper2/src/com/ibatis/sqlmap/engine/mapper/metadata/Table.java Tue Jan 24 14:47:31 2006 @@ -4,10 +4,9 @@ import java.util.Map; public class Table { - private String name; - private Map columns = new HashMap(); + private Column primaryKey; public Table(String name) { this.name = name; @@ -17,26 +16,31 @@ return name; } - public void addColumn (Column col) { + public void addColumn(Column col) { columns.put(col.getName(), col); } - public Column getColumn (String name) { + public Column getColumn(String name) { return (Column) columns.get(name); } - public String[] getColumnNames () { - return (String[])columns.keySet().toArray(new String[columns.size()]); + public String[] getColumnNames() { + return (String[]) columns.keySet().toArray(new String[columns.size()]); + } + + public void setPrimaryKey(Column column) { + primaryKey = column; + } + + public Column getPrimaryKey() { + return primaryKey; } public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; - final Table table = (Table) o; - if (name != null ? !name.equals(table.name) : table.name != null) return false; - return true; } Modified: ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/CanonicalizerTest.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/CanonicalizerTest.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/CanonicalizerTest.java (original) +++ ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/CanonicalizerTest.java Tue Jan 24 14:47:31 2006 @@ -1,7 +1,7 @@ package com.ibatis.sqlmap.mapper; import junit.framework.TestCase; -import com.ibatis.sqlmap.engine.mapper.Canonicalizer; +import com.ibatis.sqlmap.engine.mapper.matcher.Canonicalizer; import java.util.Map; Modified: ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/DatabaseMetadataTest.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/DatabaseMetadataTest.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/DatabaseMetadataTest.java (original) +++ ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/DatabaseMetadataTest.java Tue Jan 24 14:47:31 2006 @@ -32,6 +32,7 @@ assertNotNull(table.getColumn("ACC_DATE_ADDED")); assertNotNull(table.getColumn("ACC_AGE")); assertNotNull(table.getColumn("ACC_CART_OPTION")); + assertEquals(table.getColumn("ACC_ID"), table.getPrimaryKey()); } } Modified: ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/MatchCalculatorTest.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/MatchCalculatorTest.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/MatchCalculatorTest.java (original) +++ ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/MatchCalculatorTest.java Tue Jan 24 14:47:31 2006 @@ -1,7 +1,7 @@ package com.ibatis.sqlmap.mapper; import junit.framework.TestCase; -import com.ibatis.sqlmap.engine.mapper.MatchCalculator; +import com.ibatis.sqlmap.engine.mapper.matcher.MatchCalculator; public class MatchCalculatorTest extends TestCase { Modified: ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/NameMatcherTest.java URL: http://svn.apache.org/viewcvs/ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/NameMatcherTest.java?rev=372039&r1=372038&r2=372039&view=diff ============================================================================== --- ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/NameMatcherTest.java (original) +++ ibatis/trunk/java/mapper/mapper2/test/com/ibatis/sqlmap/mapper/NameMatcherTest.java Tue Jan 24 14:47:31 2006 @@ -1,6 +1,6 @@ package com.ibatis.sqlmap.mapper; -import com.ibatis.sqlmap.engine.mapper.NameMatcher; +import com.ibatis.sqlmap.engine.mapper.matcher.NameMatcher; import junit.framework.TestCase; import java.util.Map;