[metacat-cvs] metacat/src/edu/ucsb/nceas/dbadapter AbstractDatabase.java OracleAdapter.java PostgresqlAdapter.java SqlserverAdapter.java
Saurabh Garg
sgarg at ecoinformatics.org
Thu Nov 10 13:22:58 PST 2005
sgarg 05/11/10 13:22:57
Modified: src/edu/ucsb/nceas/dbadapter AbstractDatabase.java
OracleAdapter.java PostgresqlAdapter.java
SqlserverAdapter.java
Log:
New function which returns the formatted left join query. This is required as Oracle and postgres have different left join syntax
Revision Changes Path
1.8 +17 -2 metacat/src/edu/ucsb/nceas/dbadapter/AbstractDatabase.java
Index: AbstractDatabase.java
===================================================================
RCS file: /cvs/metacat/src/edu/ucsb/nceas/dbadapter/AbstractDatabase.java,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -r1.7 -r1.8
--- AbstractDatabase.java 8 Nov 2005 22:01:59 -0000 1.7
+++ AbstractDatabase.java 10 Nov 2005 21:22:57 -0000 1.8
@@ -8,9 +8,9 @@
* Authors: Jivka Bojilova
* Release: @release@
*
- * '$Author: tao $'
- * '$Date: 2005/11/08 22:01:59 $'
- * '$Revision: 1.7 $'
+ * '$Author: sgarg $'
+ * '$Date: 2005/11/10 21:22:57 $'
+ * '$Revision: 1.8 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -92,6 +92,21 @@
return "to_date(" + "'"+ dateString + "', '" + format + "')";
}
+
+ /**
+ * Syntax for doing a left join
+ * Add 'a.' in front of the fields for first table and
+ * 'b.' in front of the fields for the second table
+ *
+ * @param selectFields fields that you want to be selected
+ * @param tableA first table in the join
+ * @param tableB second table in the join
+ * @param joinCriteria the criteria based on which the join will be made
+ * @param nonJoinCriteria all other criterias
+ * @return return the string for teh select query
+ */
+ public abstract String getLeftJoinQuery(String selectFields, String tableA,
+ String tableB, String joinCriteria, String nonJoinCriteria);
/**
* Instantiate a class using the name of the class at runtime
1.7 +22 -2 metacat/src/edu/ucsb/nceas/dbadapter/OracleAdapter.java
Index: OracleAdapter.java
===================================================================
RCS file: /cvs/metacat/src/edu/ucsb/nceas/dbadapter/OracleAdapter.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- OracleAdapter.java 8 Nov 2005 22:02:24 -0000 1.6
+++ OracleAdapter.java 10 Nov 2005 21:22:57 -0000 1.7
@@ -6,9 +6,9 @@
* Authors: Jivka Bojilova
* Release: @release@
*
- * '$Author: tao $'
- * '$Date: 2005/11/08 22:02:24 $'
- * '$Revision: 1.6 $'
+ * '$Author: sgarg $'
+ * '$Date: 2005/11/10 21:22:57 $'
+ * '$Revision: 1.7 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -97,6 +97,26 @@
public String getStringDelimiter() {
return "'";
+ }
+
+ /**
+ * The Oracles's syntax for doing a left join
+ * Add 'a.' in front of the fields for first table and
+ * 'b.' in front of the fields for the second table
+ *
+ * @param selectFields fields that you want to be selected
+ * @param tableA first table in the join
+ * @param tableB second table in the join
+ * @param joinCriteria the criteria based on which the join will be made
+ * @param nonJoinCriteria all other criterias
+ * @return return the string for teh select query
+ */
+ public String getLeftJoinQuery(String selectFields, String tableA,
+ String tableB, String joinCriteria, String nonJoinCriteria){
+
+ return "SELECT " + selectFields + " FROM " + tableA + " a, "
+ + tableB + " b WHERE " + joinCriteria + "(+) " + " AND ("
+ + nonJoinCriteria +")";
}
1.8 +22 -2 metacat/src/edu/ucsb/nceas/dbadapter/PostgresqlAdapter.java
Index: PostgresqlAdapter.java
===================================================================
RCS file: /cvs/metacat/src/edu/ucsb/nceas/dbadapter/PostgresqlAdapter.java,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -r1.7 -r1.8
--- PostgresqlAdapter.java 8 Nov 2005 22:02:47 -0000 1.7
+++ PostgresqlAdapter.java 10 Nov 2005 21:22:57 -0000 1.8
@@ -6,9 +6,9 @@
* Authors: Jivka Bojilova
* Release: @release@
*
- * '$Author: tao $'
- * '$Date: 2005/11/08 22:02:47 $'
- * '$Revision: 1.7 $'
+ * '$Author: sgarg $'
+ * '$Date: 2005/11/10 21:22:57 $'
+ * '$Revision: 1.8 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -100,6 +100,26 @@
return "\"";
}
+ /**
+ * PostgreSQL's syntax for doing a left join
+ * Add 'a.' in front of the fields for first table and
+ * 'b.' in front of the fields for the second table
+ *
+ * @param selectFields fields that you want to be selected
+ * @param tableA first table in the join
+ * @param tableB second table in the join
+ * @param joinCriteria the criteria based on which the join will be made
+ * @param nonJoinCriteria all other criterias
+ * @return return the string for teh select query
+ */
+ public String getLeftJoinQuery(String selectFields, String tableA,
+ String tableB, String joinCriteria, String nonJoinCriteria){
+
+ return "SELECT " + selectFields + " FROM " + tableA + " a LEFT JOIN "
+ + tableB + " b ON " + joinCriteria + " WHERE ("
+ + nonJoinCriteria +")";
+ }
+
/**
* Return a hard code string to get xml_document list in timed replcation
*/
1.7 +22 -3 metacat/src/edu/ucsb/nceas/dbadapter/SqlserverAdapter.java
Index: SqlserverAdapter.java
===================================================================
RCS file: /cvs/metacat/src/edu/ucsb/nceas/dbadapter/SqlserverAdapter.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- SqlserverAdapter.java 8 Nov 2005 22:03:14 -0000 1.6
+++ SqlserverAdapter.java 10 Nov 2005 21:22:57 -0000 1.7
@@ -6,9 +6,9 @@
* Authors: Jivka Bojilova
* Release: @release@
*
- * '$Author: tao $'
- * '$Date: 2005/11/08 22:03:14 $'
- * '$Revision: 1.6 $'
+ * '$Author: sgarg $'
+ * '$Date: 2005/11/10 21:22:57 $'
+ * '$Revision: 1.7 $'
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
@@ -107,6 +107,26 @@
}
/**
+ * MSSQL's syntax for doing a left join
+ * Add 'a.' in front of the fields for first table and
+ * 'b.' in front of the fields for the second table
+ *
+ * @param selectFields fields that you want to be selected
+ * @param tableA first table in the join
+ * @param tableB second table in the join
+ * @param joinCriteria the criteria based on which the join will be made
+ * @param nonJoinCriteria all other criterias
+ * @return return the string for teh select query
+ */
+ public String getLeftJoinQuery(String selectFields, String tableA,
+ String tableB, String joinCriteria, String nonJoinCriteria){
+
+ return "SELECT " + selectFields + " FROM " + tableA + " a LEFT JOIN "
+ + tableB + " b ON " + joinCriteria + " WHERE ("
+ + nonJoinCriteria +")";
+ }
+
+/**
* Return a hard code string to get xml_document list in timed replcation
*/
public String getReplicationDocumentListSQL()
@@ -114,6 +134,5 @@
String sql ="select a.docid, a.rev, a.doctype from ( xml_documents a left outer join xml_revisions b on (a.docid=b.docid and a.rev<=b.rev)) where b.docid is null ";
return sql;
}
-
}
More information about the Metacat-cvs
mailing list