Author: luca
Date: Mon Dec 17 21:22:54 2012
New Revision: 1423152
URL: http://svn.apache.org/viewvc?rev=1423152&view=rev
Log:
Allowing ordering of products from DataSourceCatalog in the case "product_id"
is of type string (OODT-544). See review board at:
https://reviews.apache.org/r/8648/.
Modified:
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/DataSourceCatalog.java
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/LenientDataSourceCatalog.java
oodt/trunk/filemgr/src/main/resources/cas-filemgr-core-schema-oracle.sql
oodt/trunk/filemgr/src/main/resources/cas-filemgr-schema-mysql.sql
Modified:
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/DataSourceCatalog.java
URL:
http://svn.apache.org/viewvc/oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/DataSourceCatalog.java?rev=1423152&r1=1423151&r2=1423152&view=diff
==============================================================================
---
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/DataSourceCatalog.java
(original)
+++
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/DataSourceCatalog.java
Mon Dec 17 21:22:54 2012
@@ -1996,15 +1996,31 @@ public class DataSourceCatalog implement
ResultSet.CONCUR_READ_ONLY);
String getProductSql = null;
- if (query.getCriteria().size() == 0) {
- getProductSql = "SELECT DISTINCT product_id FROM " +
type.getName() + "_metadata";
- }else if (query.getCriteria().size() == 1) {
+
+ if (!productIdString) {
+
+ if (query.getCriteria().size() == 0) {
+ getProductSql = "SELECT DISTINCT product_id FROM " +
type.getName() + "_metadata";
+ }else if (query.getCriteria().size() == 1) {
+ getProductSql =
this.getSqlQuery(query.getCriteria().get(0), type);
+ }else {
+ getProductSql = this.getSqlQuery(new
BooleanQueryCriteria(query.getCriteria(), BooleanQueryCriteria.AND), type);
+ }
+ getProductSql += " ORDER BY product_id DESC ";
+
+ } else {
+
+ if (query.getCriteria().size() == 0) {
+ getProductSql = "SELECT DISTINCT products.product_id FROM
products, " + type.getName() + "_metadata"
+ + " WHERE
products.product_id="+type.getName() + "_metadata.product_id";
+ } else if (query.getCriteria().size() == 1) {
getProductSql = this.getSqlQuery(query.getCriteria().get(0),
type);
- }else {
+ } else {
getProductSql = this.getSqlQuery(new
BooleanQueryCriteria(query.getCriteria(), BooleanQueryCriteria.AND), type);
+ }
+ getProductSql += " ORDER BY products.product_datetime DESC ";
}
- getProductSql += " ORDER BY product_id DESC ";
-
+
LOG.log(Level.FINE, "catalog query: executing: " + getProductSql);
rs = statement.executeQuery(getProductSql);
@@ -2100,8 +2116,14 @@ public class DataSourceCatalog implement
String sqlQuery = null;
if (queryCriteria instanceof BooleanQueryCriteria) {
BooleanQueryCriteria bqc = (BooleanQueryCriteria) queryCriteria;
- if (bqc.getOperator() == BooleanQueryCriteria.NOT) {
- sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName()
+ "_metadata WHERE product_id NOT IN (" +
this.getSqlQuery(bqc.getTerms().get(0), type) + ")";
+ if (bqc.getOperator() == BooleanQueryCriteria.NOT) {
+ if (!this.productIdString) {
+ sqlQuery = "SELECT DISTINCT product_id FROM " +
type.getName() + "_metadata WHERE product_id NOT IN (" +
this.getSqlQuery(bqc.getTerms().get(0), type) + ")";
+ } else {
+ sqlQuery = "SELECT DISTINCT products.product_id
FROM products," + type.getName() + "_metadata"
+ + " WHERE
products.product_id="+type.getName() + "_metadata.product_id"
+ + " AND
products.product_id NOT IN (" + this.getSqlQuery(bqc.getTerms().get(0), type) +
")";
+ }
}else {
sqlQuery = "(" + this.getSqlQuery(bqc.getTerms().get(0), type);
String op = bqc.getOperator() == BooleanQueryCriteria.AND ?
"INTERSECT" : "UNION";
@@ -2113,7 +2135,13 @@ public class DataSourceCatalog implement
String elementIdStr =
this.validationLayer.getElementByName(queryCriteria.getElementName()).getElementId();
if (fieldIdStringFlag)
elementIdStr = "'" + elementIdStr + "'";
- sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName() +
"_metadata WHERE element_id = " + elementIdStr + " AND ";
+ if (!this.productIdString) {
+ sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName()
+ "_metadata WHERE element_id = " + elementIdStr + " AND ";
+ } else {
+ sqlQuery = "SELECT DISTINCT products.product_id FROM products,"
+ type.getName() + "_metadata"
+ + " WHERE products.product_id="+type.getName() +
"_metadata.product_id"
+ + " AND element_id = " + elementIdStr + "
AND ";
+ }
if (queryCriteria instanceof TermQueryCriteria) {
sqlQuery += "metadata_value = '" + ((TermQueryCriteria)
queryCriteria).getValue() + "'";
} else if (queryCriteria instanceof RangeQueryCriteria) {
Modified:
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/LenientDataSourceCatalog.java
URL:
http://svn.apache.org/viewvc/oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/LenientDataSourceCatalog.java?rev=1423152&r1=1423151&r2=1423152&view=diff
==============================================================================
---
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/LenientDataSourceCatalog.java
(original)
+++
oodt/trunk/filemgr/src/main/java/org/apache/oodt/cas/filemgr/catalog/LenientDataSourceCatalog.java
Mon Dec 17 21:22:54 2012
@@ -753,8 +753,14 @@ public class LenientDataSourceCatalog ex
if (queryCriteria instanceof BooleanQueryCriteria) {
BooleanQueryCriteria bqc = (BooleanQueryCriteria) queryCriteria;
if (bqc.getOperator() == BooleanQueryCriteria.NOT) {
- sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName() +
"_metadata WHERE product_id NOT IN (" + this.getSqlQuery(bqc.getTerms().get(0),
type) + ")";
- }else {
+ if (!this.productIdString) {
+ sqlQuery = "SELECT DISTINCT product_id FROM " +
type.getName() + "_metadata WHERE product_id NOT IN (" +
this.getSqlQuery(bqc.getTerms().get(0), type) + ")";
+ } else {
+ sqlQuery = "SELECT DISTINCT products.product_id FROM
products," + type.getName() + "_metadata"
+ + " WHERE
products.product_id="+type.getName() + "_metadata.product_id"
+ + " AND products.product_id NOT IN
(" + this.getSqlQuery(bqc.getTerms().get(0), type) + ")";
+ }
+ } else {
sqlQuery = "(" + this.getSqlQuery(bqc.getTerms().get(0), type);
String op = bqc.getOperator() == BooleanQueryCriteria.AND ?
"INTERSECT" : "UNION";
for (int i = 1; i < bqc.getTerms().size(); i++)
@@ -769,7 +775,13 @@ public class LenientDataSourceCatalog ex
if (fieldIdStringFlag)
elementIdStr = "'" + elementIdStr + "'";
- sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName() +
"_metadata WHERE element_id = " + elementIdStr + " AND ";
+ if (!this.productIdString) {
+ sqlQuery = "SELECT DISTINCT product_id FROM " + type.getName()
+ "_metadata WHERE element_id = " + elementIdStr + " AND ";
+ } else {
+ sqlQuery = "SELECT DISTINCT products.product_id FROM products,"
+ type.getName() + "_metadata"
+ + " WHERE products.product_id="+type.getName() +
"_metadata.product_id"
+ + " AND element_id = " + elementIdStr + "
AND ";
+ }
if (queryCriteria instanceof TermQueryCriteria) {
sqlQuery += "metadata_value = '" + ((TermQueryCriteria)
queryCriteria).getValue() + "'";
} else if (queryCriteria instanceof RangeQueryCriteria) {
Modified:
oodt/trunk/filemgr/src/main/resources/cas-filemgr-core-schema-oracle.sql
URL:
http://svn.apache.org/viewvc/oodt/trunk/filemgr/src/main/resources/cas-filemgr-core-schema-oracle.sql?rev=1423152&r1=1423151&r2=1423152&view=diff
==============================================================================
--- oodt/trunk/filemgr/src/main/resources/cas-filemgr-core-schema-oracle.sql
(original)
+++ oodt/trunk/filemgr/src/main/resources/cas-filemgr-core-schema-oracle.sql
Mon Dec 17 21:22:54 2012
@@ -26,6 +26,17 @@ CREATE TABLE PRODUCTS (
PRIMARY KEY (PRODUCT_ID)
);
+-- Example PRODUCTS table using PRODUCT_ID of type string
+-- CREATE TABLE PRODUCTS (
+-- PRODUCT_ID VARCHAR(100) NOT NULL ,
+-- PRODUCT_STRUCTURE VARCHAR(20) DEFAULT ''NOT NULL,
+-- PRODUCT_TYPE_ID number(11) DEFAULT '0'NOT NULL,
+-- PRODUCT_NAME VARCHAR(255) DEFAULT ''NOT NULL,
+-- PRODUCT_TRANSFER_STATUS VARCHAR(255) DEFAULT 'TRANSFERING' NOT NULL,
+-- PRODUCT_DATETIME DATE NOT NULL,
+-- PRIMARY KEY (PRODUCT_ID)
+--);
+
CREATE INDEX PRODUCTS_idx ON PRODUCTS(product_id);
EXIT;
Modified: oodt/trunk/filemgr/src/main/resources/cas-filemgr-schema-mysql.sql
URL:
http://svn.apache.org/viewvc/oodt/trunk/filemgr/src/main/resources/cas-filemgr-schema-mysql.sql?rev=1423152&r1=1423151&r2=1423152&view=diff
==============================================================================
--- oodt/trunk/filemgr/src/main/resources/cas-filemgr-schema-mysql.sql
(original)
+++ oodt/trunk/filemgr/src/main/resources/cas-filemgr-schema-mysql.sql Mon Dec
17 21:22:54 2012
@@ -68,3 +68,17 @@ CREATE TABLE `products` (
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+# Example of 'products' table using a 'product_id' column of type string
+# Host: spawn
+# Database: test_cas
+# Table: 'products'
+#
+#CREATE TABLE `products` (
+# `product_id` varchar(100) NOT NULL,
+# `product_structure` varchar(20) NOT NULL default '',
+# `product_type_id` int(11) NOT NULL default '0',
+# `product_name` varchar(255) NOT NULL default '',
+# `product_transfer_status` varchar(255) NOT NULL default 'TRANSFERING',
+# `product_datetime` datetime NOT NULL,
+# PRIMARY KEY (`product_id`)
+#) ENGINE=MyISAM DEFAULT CHARSET=latin1;
\ No newline at end of file
