Skip to content
This repository has been archived by the owner on Feb 23, 2018. It is now read-only.

metalnx specific queries only operate vs. postgres #15

Open
michael-conway opened this issue Oct 26, 2017 · 3 comments
Open

metalnx specific queries only operate vs. postgres #15

michael-conway opened this issue Oct 26, 2017 · 3 comments
Assignees
@michael-conway
Labels
enhancement

Comments

@michael-conway
Copy link

example versus mariadb...

continuation is zero, no need to close...silently ignored
2017-10-26 16:29:19 INFO  SpecificQueryAOImpl:196 - query definition:SpecificQuerySQLDetails:
   alias:metalnxListOfDataObjectsThatMatchSearchTextWithOrdering
    sql: select     data_name,    coll_name,    data_id,    data_size,    data_path,    data_owner_name,    data_owner_zone,    create_ts,    modify_ts  from (   select distinct on (d.data_name)     d.* ,     c.coll_name   from     R_DATA_MAIN d     left join     R_COLL_MAIN c   on (c.coll_id = d.coll_id)   where     c.coll_name = ?     AND     d.data_name ILIKE ?  ) searchDataObjsByMetadata  order by data_name asc  offset ?  limit ? 
    argumentCount:4
    columnNames:[data_name, coll_name, data_id, data_size, data_path, data_owner_name, data_owner_zone, create_ts, modify_ts]
2017-10-26 16:29:19 INFO  SpecificQueryAOImpl:505 - found specific query definition by alias
2017-10-26 16:29:19 ERROR CollectionServiceImpl:1011 - Could not execute specific query for listing data objects that match a search text
org.irods.jargon.core.exception.CatalogSQLException: Catalog SQL error
	at org.irods.jargon.core.connection.IRODSErrorScanner.checkSpecificCodesAndThro
@michael-conway michael-conway self-assigned this Oct 30, 2017
@michael-conway
Copy link
Author

add mysql compliant queries at:

https://github.com/DICE-UNC/metalnx-web/blob/master/src/emc-metalnx-services/src/main/java/com/emc/metalnx/services/irods/SpecQueryServiceImpl.java#L140-L226

michael-conway added a commit that referenced this issue Nov 1, 2017
@michael-conway
#15 built factory, tests, and interface for specific query provider..…
f27cc59 
…next implement for postgres
michael-conway added a commit that referenced this issue Nov 2, 2017
@michael-conway
#15 breaks build beware - wip on spec query
4c85d7a
michael-conway added a commit that referenced this issue Nov 2, 2017
@michael-conway
#15 builds...needs testing
b08149c
michael-conway added a commit that referenced this issue Nov 2, 2017
@michael-conway
#15 unit tests of specific query
a588f72
michael-conway added a commit that referenced this issue Nov 3, 2017
@michael-conway
#15 tests now passing on postgres
9072be1
@michael-conway
Copy link
Author

NB look at offset here..that's wrong! On a text search...

sql:SELECT * 	FROM ( 	SELECT  	r_data_main.data_name AS name,  	r_data_main.data_repl_num AS repl_num,  	r_data_main.data_owner_name AS owner_name,  	r_data_main.data_owner_zone AS owner_zone,  	r_data_main.data_size AS size,  	r_data_main.resc_name,  	CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END AS path,  	r_data_main.data_checksum AS checksum,  	CAST(r_data_main.create_ts AS BIGINT),   	CAST(r_data_main.modify_ts AS BIGINT) 	FROM  	r_data_main    INNER JOIN    	r_coll_main    ON    	r_data_main.coll_id = r_coll_main.coll_id   ) AS fileProperties WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' ) OFFSET 23187 LIMIT 10
    argumentCount:0
    columnNames:[*]

@michael-conway
Copy link
Author

logs with queries...



2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:295 - creating specific query: SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209313
    sql:SELECT COUNT(*) FROM	( SELECT replace(r_coll_main.coll_name, r_coll_main.parent_coll_name || '/', '') AS name, 0 AS repl_num,	r_coll_main.coll_owner_name AS owner_name, r_coll_main.coll_owner_zone AS owner_zone, 0 AS size, 	'' AS resc_name, r_coll_main.coll_name AS path, '' AS checksum, 	CAST(r_coll_main.create_ts AS BIGINT), CAST(r_coll_main.modify_ts AS BIGINT) FROM r_coll_main   ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
    argumentCount:0
    columnNames:[]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:300 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:313 - added specific query
2017-11-03 16:06:49 INFO  SpecQueryServiceImpl:248 - Specific query: SELECT COUNT(*) FROM	( SELECT replace(r_coll_main.coll_name, r_coll_main.parent_coll_name || '/', '') AS name, 0 AS repl_num,	r_coll_main.coll_owner_name AS owner_name, r_coll_main.coll_owner_zone AS owner_zone, 0 AS size, 	'' AS resc_name, r_coll_main.coll_name AS path, '' AS checksum, 	CAST(r_coll_main.create_ts AS BIGINT), CAST(r_coll_main.modify_ts AS BIGINT) FROM r_coll_main   ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:490 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:151 - findSpecificQueryByAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:163 - alias:metalnxUserQuery_1509725209313
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:164 - zoneHint:
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:425 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  QueryResultProcessingUtils:60 - rows returned from iRODS query: 1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:578 - doing a close for this page...
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:676 - closeResultSet()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:682 - continuation is zero, no need to close...silently ignored
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:196 - query definition:SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209313
    sql:SELECT COUNT(*) FROM	( SELECT replace(r_coll_main.coll_name, r_coll_main.parent_coll_name || '/', '') AS name, 0 AS repl_num,	r_coll_main.coll_owner_name AS owner_name, r_coll_main.coll_owner_zone AS owner_zone, 0 AS size, 	'' AS resc_name, r_coll_main.coll_name AS path, '' AS checksum, 	CAST(r_coll_main.create_ts AS BIGINT), CAST(r_coll_main.modify_ts AS BIGINT) FROM r_coll_main   ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
    argumentCount:0
    columnNames:[*]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:505 - found specific query definition by alias
2017-11-03 16:06:49 INFO  QueryResultProcessingUtils:60 - rows returned from iRODS query: 1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:578 - doing a close for this page...
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:676 - closeResultSet()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:682 - continuation is zero, no need to close...silently ignored
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:365 - removing specific query by alias: metalnxUserQuery_1509725209313
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:368 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:370 - removed specific query
2017-11-03 16:06:49 INFO  SpecQueryServiceImpl:221 - countItemsMatchingFileProperties()
2017-11-03 16:06:49 INFO  EnvironmentalInfoAOImpl:107 - retrieveClientHints()
2017-11-03 16:06:49 INFO  EnvironmentalInfoAOImpl:110 - first look in cache if not a refresh
2017-11-03 16:06:49 INFO  DiscoveredServerPropertiesCache:112 - now retriving clientHints from cache with zone:zone1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:295 - creating specific query: SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209670
    sql:SELECT COUNT(*) FROM	( SELECT r_data_main.data_name as name, r_data_main.data_repl_num as repl_num,	r_data_main.data_owner_name as owner_name, r_data_main.data_owner_zone as owner_zone, 	r_data_main.data_size as size, r_data_main.resc_name,  CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END as path, 	r_data_main.data_checksum as checksum, CAST(r_data_main.create_ts AS BIGINT),  	CAST(r_data_main.modify_ts AS BIGINT) FROM r_data_main INNER JOIN r_coll_main ON 	r_data_main.coll_id = r_coll_main.coll_id ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
    argumentCount:0
    columnNames:[]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:300 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:313 - added specific query
2017-11-03 16:06:49 INFO  SpecQueryServiceImpl:248 - Specific query: SELECT COUNT(*) FROM	( SELECT r_data_main.data_name as name, r_data_main.data_repl_num as repl_num,	r_data_main.data_owner_name as owner_name, r_data_main.data_owner_zone as owner_zone, 	r_data_main.data_size as size, r_data_main.resc_name,  CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END as path, 	r_data_main.data_checksum as checksum, CAST(r_data_main.create_ts AS BIGINT),  	CAST(r_data_main.modify_ts AS BIGINT) FROM r_data_main INNER JOIN r_coll_main ON 	r_data_main.coll_id = r_coll_main.coll_id ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:490 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:151 - findSpecificQueryByAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:163 - alias:metalnxUserQuery_1509725209670
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:164 - zoneHint:
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:425 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  QueryResultProcessingUtils:60 - rows returned from iRODS query: 1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:578 - doing a close for this page...
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:676 - closeResultSet()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:682 - continuation is zero, no need to close...silently ignored
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:196 - query definition:SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209670
    sql:SELECT COUNT(*) FROM	( SELECT r_data_main.data_name as name, r_data_main.data_repl_num as repl_num,	r_data_main.data_owner_name as owner_name, r_data_main.data_owner_zone as owner_zone, 	r_data_main.data_size as size, r_data_main.resc_name,  CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END as path, 	r_data_main.data_checksum as checksum, CAST(r_data_main.create_ts AS BIGINT),  	CAST(r_data_main.modify_ts AS BIGINT) FROM r_data_main INNER JOIN r_coll_main ON 	r_data_main.coll_id = r_coll_main.coll_id ) AS fileProperties  WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' )
    argumentCount:0
    columnNames:[*]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:505 - found specific query definition by alias
2017-11-03 16:06:49 INFO  QueryResultProcessingUtils:60 - rows returned from iRODS query: 1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:578 - doing a close for this page...
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:676 - closeResultSet()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:682 - continuation is zero, no need to close...silently ignored
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:365 - removing specific query by alias: metalnxUserQuery_1509725209670
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:368 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:370 - removed specific query
2017-11-03 16:06:49 INFO  EnvironmentalInfoAOImpl:107 - retrieveClientHints()
2017-11-03 16:06:49 INFO  EnvironmentalInfoAOImpl:110 - first look in cache if not a refresh
2017-11-03 16:06:49 INFO  DiscoveredServerPropertiesCache:112 - now retriving clientHints from cache with zone:zone1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:295 - creating specific query: SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209844
    sql:SELECT * 	FROM ( 	SELECT  	r_data_main.data_name AS name,  	r_data_main.data_repl_num AS repl_num,  	r_data_main.data_owner_name AS owner_name,  	r_data_main.data_owner_zone AS owner_zone,  	r_data_main.data_size AS size,  	r_data_main.resc_name,  	CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END AS path,  	r_data_main.data_checksum AS checksum,  	CAST(r_data_main.create_ts AS BIGINT),   	CAST(r_data_main.modify_ts AS BIGINT) 	FROM  	r_data_main    INNER JOIN    	r_coll_main    ON    	r_data_main.coll_id = r_coll_main.coll_id   ) AS fileProperties WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' ) OFFSET 23187 LIMIT 10
    argumentCount:0
    columnNames:[]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:300 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:313 - added specific query
2017-11-03 16:06:49 INFO  SpecQueryServiceImpl:296 - Specific query: SELECT * 	FROM ( 	SELECT  	r_data_main.data_name AS name,  	r_data_main.data_repl_num AS repl_num,  	r_data_main.data_owner_name AS owner_name,  	r_data_main.data_owner_zone AS owner_zone,  	r_data_main.data_size AS size,  	r_data_main.resc_name,  	CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END AS path,  	r_data_main.data_checksum AS checksum,  	CAST(r_data_main.create_ts AS BIGINT),   	CAST(r_data_main.modify_ts AS BIGINT) 	FROM  	r_data_main    INNER JOIN    	r_coll_main    ON    	r_data_main.coll_id = r_coll_main.coll_id   ) AS fileProperties WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' ) OFFSET 23187 LIMIT 10
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:490 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:151 - findSpecificQueryByAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:163 - alias:metalnxUserQuery_1509725209844
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:164 - zoneHint:
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:425 - executeSpecificQueryUsingAlias()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  QueryResultProcessingUtils:60 - rows returned from iRODS query: 1
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:578 - doing a close for this page...
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:676 - closeResultSet()
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:682 - continuation is zero, no need to close...silently ignored
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:196 - query definition:SpecificQuerySQLDetails:
   alias:metalnxUserQuery_1509725209844
    sql:SELECT * 	FROM ( 	SELECT  	r_data_main.data_name AS name,  	r_data_main.data_repl_num AS repl_num,  	r_data_main.data_owner_name AS owner_name,  	r_data_main.data_owner_zone AS owner_zone,  	r_data_main.data_size AS size,  	r_data_main.resc_name,  	CASE WHEN r_coll_main.parent_coll_name = '/' THEN '/' || r_data_main.data_name ELSE r_coll_main.coll_name || '/' || r_data_main.data_name END AS path,  	r_data_main.data_checksum AS checksum,  	CAST(r_data_main.create_ts AS BIGINT),   	CAST(r_data_main.modify_ts AS BIGINT) 	FROM  	r_data_main    INNER JOIN    	r_coll_main    ON    	r_data_main.coll_id = r_coll_main.coll_id   ) AS fileProperties WHERE  LOWER( fileProperties.name ) ILIKE LOWER( '%textSearch%' ) OFFSET 23187 LIMIT 10
    argumentCount:0
    columnNames:[*]
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:505 - found specific query definition by alias
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:552 - no reults from iRODS, return as an empty result set
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:725 - by version number I know I support specific query
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:365 - removing specific query by alias: metalnxUserQuery_1509725209844
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:368 - executing specific query PI
2017-11-03 16:06:49 INFO  SpecificQueryAOImpl:370 - removed specific query
2017-11-03 16:06:49 INFO  IRODSMidLevelProtocol:338 - preDisconnectAction()

michael-conway added a commit that referenced this issue Nov 3, 2017
@michael-conway
#15 postgres testing complete
a4ef678
michael-conway added a commit that referenced this issue Nov 7, 2017
@michael-conway
#15 mysql testing..pause for now
fd95ac7
@michael-conway michael-conway mentioned this issue Dec 12, 2017
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@michael-conway michael-conway

Labels
enhancement
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@michael-conway