Christian,
What do you think about a command line argument for generate-mapping that
enables guessing of primary keys? --guess-pk or something like that? It would
be useful not only for Access users. Primary keys are missing surprisingly
often. Your code is a bit of a hack, but hey if it works ... ;-)
Best,
Richard
On 12 Jan 2011, at 16:52, Christian Brenninkmeijer wrote:
> 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
------------------------------------------------------------------------------
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
