search

DICE-UNC / jargon

Jargon core libraries
Other
28 stars 31 forks source link

gen query error with IN statement #17

Closed michael-conway closed 10 years ago

michael-conway commented 10 years ago

They have AVU of the form: [myAttributeName1, val1] or [myAttributeName1, val2] or [myAttributeName1, val3], and they all have in common an AVU of the form: [myAttributeName2, Val4]

How would you write a QueryBuilder for surch query? I am including my attempt (which is not returning what I should expect)

IRODSGenQueryBuilder queryBuilder = new IRODSGenQueryBuilder(true, true, true, null); queryBuilder.addConditionAsGenQueryField(RodsGenQueryEnum.COL_META_COLL_ATTR_NAME, QueryConditionOperators.EQUAL, myAttributeName1);

queryBuilder.addConditionAsGenQueryField(RodsGenQueryEnum.COL_META_COLL_ATTR_VALUE, QueryConditionOperators.IN, val1+","+val2+","+val3);

queryBuilder.addConditionAsGenQueryField(RodsGenQueryEnum.COL_META_COLL_ATTR_NAME, QueryConditionOperators.EQUAL, myAttributeName2); queryBuilder.addConditionAsGenQueryField(RodsGenQueryEnum.COL_META_COLL_ATTR_VALUE, QueryConditionOperators.EQUAL, val4);

//After a IRODSGenQueryExecutor object executor is defined, I run the following: IRODSQueryResultSet resultSet = exectutor.executeIRODSQueryAndCloseResult (queryBuilder.exportIRODSQueryFromBuilder(10), 0); System.out.println(resultSet.getTotalRecords());

I get 0 printed out, and I know for a fact that this is wrong because I have verified using icommands.

michael-conway commented 10 years ago

observe icommand 

<maxRows>256</maxRows>
<continueInx>0</continueInx>
<partialStartIndex>0</partialStartIndex>
<options>0</options>
<KeyValPair_PI>
<ssLen>0</ssLen>
</KeyValPair_PI>
<InxIvalPair_PI>
<iiLen>1</iiLen>
<inx>403</inx>
<ivalue>1</ivalue>
</InxIvalPair_PI>
<InxValPair_PI>
<isLen>1</isLen>
<inx>601</inx>
<svalue>IN ('testExecuteMetadataQueryWithInvalue3','testExecuteMetadataQueryWith
Invalue2')</svalue>
</InxValPair_PI>
</GenQueryInp_PI>

packed XML:
<MsgHeader_PI>
<type>RODS_API_REQ</type>
<msgLen>450</msgLen>
<errorLen>0</errorLen>
<bsLen>0</bsLen>
<intInfo>702</intInfo>
</MsgHeader_PI>

sending header: len = 141
<MsgHeader_PI>
<type>RODS_API_REQ</type>
<msgLen>450</msgLen>
<errorLen>0</errorLen>
<bsLen>0</bsLen>
<intInfo>702</intInfo>
</MsgHeader_PI>

sending msg:
<GenQueryInp_PI>
<maxRows>256</maxRows>
<continueInx>0</continueInx>
<partialStartIndex>0</partialStartIndex>
<options>0</options>
<KeyValPair_PI>
<ssLen>0</ssLen>
</KeyValPair_PI>
<InxIvalPair_PI>
<iiLen>1</iiLen>
<inx>403</inx>
<ivalue>1</ivalue>
</InxIvalPair_PI>
<InxValPair_PI>
<isLen>1</isLen>
<inx>601</inx>
<svalue>IN ('testExecuteMetadataQueryWithInvalue3','testExecuteMetadataQueryWith
Invalue2')</svalue>
</InxValPair_PI>
</GenQueryInp_PI>

received header: len = 142
<MsgHeader_PI>
<type>RODS_API_REPLY</type>
<msgLen>3842</msgLen>
<errorLen>0</errorLen>
<bsLen>0</bsLen>
<intInfo>0</intInfo>
</MsgHeader_PI>

received msg:
<GenQueryOut_PI>
<rowCnt>1</rowCnt>
<attriCnt>1</attriCnt>
<continueInx>0</continueInx>
<totalRowCount>0</totalRowCount>
<SqlResult_PI>
<attriInx>403</attriInx>
<reslen>50</reslen>
<value>testExecuteMetadataQueryWithIn.dat</value>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
<SqlResult_PI>
<attriInx>0</attriInx>
<reslen>0</reslen>
</SqlResult_PI>
</GenQueryOut_PI>

DATA_NAME = testExecuteMetadataQueryWithIn.dat
------------------------------------------------------------
packed XML:
<MsgHeader_PI>
<type>RODS_DISCONNECT</type>
<msgLen>0</msgLen>
<errorLen>0</errorLen>
<bsLen>0</bsLen>
<intInfo>0</intInfo>
</MsgHeader_PI>

sending header: len = 140
<MsgHeader_PI>
<type>RODS_DISCONNECT</type>
<msgLen>0</msgLen>
<errorLen>0</errorLen>
<bsLen>0</bsLen>
<intInfo>0</intInfo>
</MsgHeader_PI>

Mike@TUMBLINGDICE ~/Documents/workspace-release-github/jargon (master)
$ testExecuteMetadataQueryWithInvalue3
michael-conway commented 10 years ago

simple jargon example not working...

calling irods function with:<GenQueryInp_PI><maxRows>1</maxRows>
<continueInx>0</continueInx>
<partialStartIndex>0</partialStartIndex>
<options>32</options>
<KeyValPair_PI><ssLen>0</ssLen>
</KeyValPair_PI>
<InxIvalPair_PI><iiLen>1</iiLen>
<inx>403</inx>
<ivalue>1</ivalue>
</InxIvalPair_PI>
<InxValPair_PI><isLen>1</isLen>
<inx>601</inx>
<svalue> in '('testExecuteMetadataQueryWithInvalue1','testExecuteMetadataQueryWithInvalue2','testExecuteMetadataQueryWithInvalue3')' </svalue>
</InxValPair_PI>
</GenQueryInp_PI>
michael-conway commented 10 years ago

fixed by unit test


    /**
     * BUG: gen query error with IN statement #17
     * https://github.com/DICE-UNC/jargon/issues/17
     * 
     * @throws Exception
     */
    @Test
    public final void testExecuteMetadataQueryWithIn() throws Exception {
        String testFileName = "testExecuteMetadataQueryWithIn.dat";
        String absPath = scratchFileUtils
                .createAndReturnAbsoluteScratchPath(IRODS_TEST_SUBDIR_PATH);
        String localFileName = FileGenerator
                .generateFileOfFixedLengthGivenName(absPath, testFileName, 10);

        IRODSAccount irodsAccount = testingPropertiesHelper
                .buildIRODSAccountFromTestProperties(testingProperties);

        // put scratch file into irods in the right place on the first resource

        String targetIrodsCollection = testingPropertiesHelper
                .buildIRODSCollectionAbsolutePathFromTestProperties(
                        testingProperties, IRODS_TEST_SUBDIR_PATH);

        IRODSFile testFile = irodsFileSystem.getIRODSFileFactory(irodsAccount)
                .instanceIRODSFile(targetIrodsCollection);
        testFile.deleteWithForceOption();
        testFile.mkdirs();

        String dataObjectAbsPath = targetIrodsCollection + '/' + testFileName;

        DataTransferOperations dto = irodsFileSystem
                .getIRODSAccessObjectFactory().getDataTransferOperations(
                        irodsAccount);

        dto.putOperation(localFileName, targetIrodsCollection,
                irodsAccount.getDefaultStorageResource(), null, null);

        DataObjectAO dataObjectAO = irodsFileSystem
                .getIRODSAccessObjectFactory().getDataObjectAO(irodsAccount);

        // initialize the AVU data
        String expectedAttribName1 = "testExecuteMetadataQueryWithInattrib1";
        String expectedAttribValue1 = "testExecuteMetadataQueryWithInvalue1";
        String expectedAttribUnits1 = "testExecuteMetadataQueryWithInunits1";

        AvuData avuData = AvuData.instance(expectedAttribName1,
                expectedAttribValue1, expectedAttribUnits1);

        dataObjectAO.deleteAVUMetadata(dataObjectAbsPath, avuData);
        dataObjectAO.addAVUMetadata(dataObjectAbsPath, avuData);

        String expectedAttribName2 = "testExecuteMetadataQueryWithInattrib2";
        String expectedAttribValue2 = "testExecuteMetadataQueryWithInvalue2";
        String expectedAttribUnits2 = "testExecuteMetadataQueryWithInunits2";

        avuData = AvuData.instance(expectedAttribName2, expectedAttribValue2,
                expectedAttribUnits2);

        dataObjectAO.deleteAVUMetadata(dataObjectAbsPath, avuData);
        dataObjectAO.addAVUMetadata(dataObjectAbsPath, avuData);

        String expectedAttribName3 = "testExecuteMetadataQueryWithInattrib3";
        String expectedAttribValue3 = "testExecuteMetadataQueryWithInvalue3";
        String expectedAttribUnits3 = "testExecuteMetadataQueryWithInunits3";

        avuData = AvuData.instance(expectedAttribName3, expectedAttribValue3,
                expectedAttribUnits3);

        dataObjectAO.deleteAVUMetadata(dataObjectAbsPath, avuData);
        dataObjectAO.addAVUMetadata(dataObjectAbsPath, avuData);

        IRODSGenQueryExecutor irodsGenQueryExecutor = irodsFileSystem
                .getIRODSAccessObjectFactory().getIRODSGenQueryExecutor(
                        irodsAccount);

        IRODSGenQueryBuilder queryBuilder = new IRODSGenQueryBuilder(true,
                false, true, null);
        queryBuilder.addSelectAsGenQueryValue(RodsGenQueryEnum.COL_DATA_NAME);

        queryBuilder.addConditionAsGenQueryField(
                RodsGenQueryEnum.COL_META_DATA_ATTR_NAME,
                QueryConditionOperators.EQUAL, expectedAttribName1);

        List<String> values = new ArrayList<String>();
        values.add(expectedAttribValue1);
        values.add(expectedAttribValue2);
        values.add(expectedAttribValue3);

        queryBuilder.addConditionAsMultiValueCondition(
                RodsGenQueryEnum.COL_META_DATA_ATTR_VALUE,
                QueryConditionOperators.IN, values);

        queryBuilder.addConditionAsGenQueryField(
                RodsGenQueryEnum.COL_META_DATA_ATTR_NAME,
                QueryConditionOperators.EQUAL, expectedAttribName2);
        queryBuilder.addConditionAsGenQueryField(
                RodsGenQueryEnum.COL_META_DATA_ATTR_VALUE,
                QueryConditionOperators.EQUAL, expectedAttribValue2);

        IRODSGenQueryFromBuilder query = queryBuilder
                .exportIRODSQueryFromBuilder(1);

        IRODSQueryResultSetInterface resultSet = irodsGenQueryExecutor
                .executeIRODSQuery(query, 0);
        Assert.assertTrue("no result", resultSet.getResults().size() > 0);

    }
michael-conway commented 10 years ago

added between