Hi Guys,
I have been running MappingGenerator over Accessdatabase where you
can't get information about the primary keys, as well as over ones
converted tpo mysql where the information got lost.
To avoid having add them in by hand each time I wrote a hack to quess them.
Thought I might as well share it.
I am sure there are better ways of doing this but it is working for me
so far.
A better approach is to store the keys when found rather than searching
multiple times but it works.
In de.fuberlin.wiwiss.d2rq.dbschema. DatabaseSchemaInspector
public List primaryKeyColumns(RelationName tableName) {
...
} catch (SQLException ex) {
return hackedPrimaryKeyColumns(tableName);
//throw new D2RQException("Database exception", ex);
}
Then add:
private boolean candidatePrimaryKey (String tableName, int
allRowCount, String[] columns){
ResultSet rs;
Statement stmt;
try {
//Normal SQL
String query = "SELECT COUNT(DISTINCT `" + columns[0] + "`";
for (int i = 1; i< columns.length; i++) {
query = query + ", `" + columns[i] + "`";
}
query = query + ") FROM " + tableName;
stmt = db.connection().createStatement();
rs = stmt.executeQuery(query);
} catch (SQLException ex) {
//MS Access has to be different!
String query = "SELECT COUNT(*) AS colCount FROM (SELECT
DISTINCT [" + columns[0] + "]";
for (int i = 1; i< columns.length; i++) {
query = query + ", [" + columns[i] + "]";
}
query = query + " FROM [" + tableName + "])";
System.out.println(query);
try {
stmt = db.connection().createStatement();
rs = stmt.executeQuery(query);
} catch (SQLException ex1) {
throw new D2RQException("Database exception", ex1);
}
}
try {
rs.next();
int rowCount = rs.getInt(1);
rs.close();
stmt.close();
return (rowCount == allRowCount);
} catch (SQLException ex) {
throw new D2RQException("Database exception", ex);
}
}
private List hackedPrimaryKeyColumns(RelationName tableName){
Statement stmt;
String query;
ResultSet rs;
int allRowCount;
ArrayList<String> columnsList = new ArrayList<String>();
try {
stmt = db.connection().createStatement();
query = "SELECT COUNT(*) AS MYCOUNT FROM `" +
tableName.tableName() + "`";
rs = stmt.executeQuery(query);
rs.next();
allRowCount = rs.getInt(1);
rs.close();
rs = this.schema.getColumns(null, null,
tableName.tableName(), null);
while (rs.next()) {
String columnName = rs.getString("COLUMN_NAME");
columnsList.add(columnName);
}
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new D2RQException("Database exception", ex);
}
String[] columns = new String[0];
columns = columnsList.toArray(columns);
//Try Single columns first
String[] tryColumns = new String[1];
for (int i = 0; i < columns.length; i++){
tryColumns[0] = columns[i];
if (candidatePrimaryKey(tableName.tableName(), allRowCount,
tryColumns)){
List result = new ArrayList();
result.add(new Attribute(tableName, columns[i]));
return result;
}
}
//Try two columns now
tryColumns = new String[2];
for (int i = 0; i < columns.length - 1; i++){
tryColumns[0] = columns[i];
for (int j = i+1; j < columns.length; j++){
tryColumns[1] = columns[j];
if (candidatePrimaryKey(tableName.tableName(),
allRowCount, tryColumns)){
List result = new ArrayList();
for (int c = 0; c < 2; c++){
result.add(new Attribute(tableName,
tryColumns[c]));
}
return result;
}
}
}
//Try three columns now
tryColumns = new String[3];
for (int i = 0; i < columns.length - 2; i++){
tryColumns[0] = columns[i];
for (int j = i+1; j < columns.length - 1; j++){
tryColumns[1] = columns[j];
for (int k = j+1; k < columns.length; k++){
tryColumns[2] = columns[k];
if (candidatePrimaryKey(tableName.tableName(),
allRowCount, tryColumns)){
List result = new ArrayList();
for (int c = 0; c < 2; c++){
result.add(new Attribute(tableName,
tryColumns[c]));
}
return result;
}
}
}
}
//Try four columns now
tryColumns = new String[4];
for (int i = 0; i < columns.length - 3; i++){
tryColumns[0] = columns[i];
for (int j = i+1; j < columns.length - 2; j++){
tryColumns[1] = columns[j];
for (int k = j+1; k < columns.length - 1; k++){
tryColumns[2] = columns[k];
for (int l = k+1; l < columns.length; l++){
tryColumns[3] = columns[l];
if (candidatePrimaryKey(tableName.tableName(),
allRowCount, tryColumns)){
List result = new ArrayList();
for (int c = 0; c < 2; c++){
result.add(new Attribute(tableName,
tryColumns[c]));
}
return result;
}
}
}
}
}
// OK now I give up
// May use all columns but even that may not be unique
return new ArrayList();
}
--
Dr Christian Brenninkmeijer
Department of Computer Science
University of Manchester
------------------------------------------------------------------------------
Protect Your Site and Customers from Malware Attacks
Learn about various malware tactics and how to avoid them. Understand
malware threats, the impact they can have on your business, and how you
can protect your company and customers by using code signing.
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
d2rq-map-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
