| /******************************************************************************* |
| * Copyright (c) 2004, 2007 Boeing. |
| * All rights reserved. This program and the accompanying materials |
| * are made available under the terms of the Eclipse Public License v1.0 |
| * which accompanies this distribution, and is available at |
| * http://www.eclipse.org/legal/epl-v10.html |
| * |
| * Contributors: |
| * Boeing - initial API and implementation |
| *******************************************************************************/ |
| package org.eclipse.osee.orcs.db.internal.search.engines; |
| |
| import static java.util.Arrays.asList; |
| import static org.eclipse.osee.orcs.db.internal.search.handlers.SqlHandlerFactoryUtil.createArtifactSqlHandlerFactory; |
| import static org.junit.Assert.assertEquals; |
| import static org.mockito.Matchers.any; |
| import static org.mockito.Matchers.eq; |
| import static org.mockito.Mockito.doAnswer; |
| import static org.mockito.Mockito.when; |
| import java.util.Collections; |
| import java.util.Iterator; |
| import java.util.List; |
| import org.eclipse.osee.executor.admin.ExecutorAdmin; |
| import org.eclipse.osee.framework.core.enums.CoreArtifactTypes; |
| import org.eclipse.osee.framework.core.enums.CoreAttributeTypes; |
| import org.eclipse.osee.framework.core.enums.CoreBranches; |
| import org.eclipse.osee.framework.core.enums.CoreRelationTypes; |
| import org.eclipse.osee.framework.core.enums.QueryOption; |
| import org.eclipse.osee.framework.core.sql.OseeSql; |
| import org.eclipse.osee.framework.jdk.core.type.OseeCoreException; |
| import org.eclipse.osee.framework.jdk.core.util.GUID; |
| import org.eclipse.osee.logger.Log; |
| import org.eclipse.osee.orcs.OrcsSession; |
| import org.eclipse.osee.orcs.core.ds.Criteria; |
| import org.eclipse.osee.orcs.core.ds.CriteriaSet; |
| import org.eclipse.osee.orcs.core.ds.Options; |
| import org.eclipse.osee.orcs.core.ds.OptionsUtil; |
| import org.eclipse.osee.orcs.core.ds.QueryData; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaAllArtifacts; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaArtifactGuids; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaArtifactIds; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaArtifactType; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaAttributeKeywords; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaAttributeOther; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaAttributeTypeExists; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaBranch; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelatedTo; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelationTypeExists; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelationTypeFollow; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelationTypeNotExists; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelationTypeSideExists; |
| import org.eclipse.osee.orcs.core.ds.criteria.CriteriaRelationTypeSideNotExists; |
| import org.eclipse.osee.orcs.db.internal.IdentityLocator; |
| import org.eclipse.osee.orcs.db.internal.SqlProvider; |
| import org.eclipse.osee.orcs.db.internal.search.QuerySqlContext; |
| import org.eclipse.osee.orcs.db.internal.search.QuerySqlContextFactory; |
| import org.eclipse.osee.orcs.db.internal.search.tagger.TagCollector; |
| import org.eclipse.osee.orcs.db.internal.search.tagger.TagProcessor; |
| import org.eclipse.osee.orcs.db.internal.sql.SqlHandlerFactory; |
| import org.eclipse.osee.orcs.db.internal.sql.join.AbstractJoinQuery; |
| import org.eclipse.osee.orcs.db.internal.sql.join.CharJoinQuery; |
| import org.eclipse.osee.orcs.db.internal.sql.join.IdJoinQuery; |
| import org.eclipse.osee.orcs.db.internal.sql.join.SqlJoinFactory; |
| import org.junit.Assert; |
| import org.junit.Before; |
| import org.junit.Rule; |
| import org.junit.Test; |
| import org.junit.rules.ExpectedException; |
| import org.mockito.Mock; |
| import org.mockito.MockitoAnnotations; |
| import org.mockito.invocation.InvocationOnMock; |
| import org.mockito.stubbing.Answer; |
| |
| /** |
| * Test Case for {@link ArtifactQuerySqlContextFactoryImpl} |
| * |
| * @author Roberto E. Escobar |
| */ |
| public class ArtifactQuerySqlContextFactoryImplTest { |
| |
| private static final String QUICK_SEARCH_VALUE = "hello1_two_three"; |
| private static final String WORD_1 = "hello"; |
| private static final String WORD_2 = "two"; |
| private static final String WORD_3 = "three"; |
| private static final long CODED_WORD_1 = 1520625L; |
| private static final long CODED_WORD_2 = 6106L; |
| private static final long CODED_WORD_3 = 981274L; |
| |
| private static final Criteria GUIDS = new CriteriaArtifactGuids(asList(GUID.create(), GUID.create())); |
| private static final Criteria IDS = new CriteriaArtifactIds(asList(1L, 2L, 3L, 4L, 5L)); |
| private static final Criteria TYPES = new CriteriaArtifactType(null, asList(CoreArtifactTypes.CodeUnit), false); |
| private static final Criteria ATTRIBUTE = new CriteriaAttributeOther(Collections.singleton(CoreAttributeTypes.Name), |
| asList("Hello")); |
| private static final Criteria ATTR_TYPE_EXITS = new CriteriaAttributeTypeExists(asList(CoreAttributeTypes.Name)); |
| private static final Criteria REL_TYPE_EXISTS = new CriteriaRelationTypeExists( |
| CoreRelationTypes.Default_Hierarchical__Child); |
| |
| private static final Criteria ATTRIBUTE_KEYWORD = new CriteriaAttributeKeywords(false, asList( |
| CoreAttributeTypes.Name, CoreAttributeTypes.WordTemplateContent), null, QUICK_SEARCH_VALUE, |
| QueryOption.TOKEN_DELIMITER__ANY, QueryOption.TOKEN_MATCH_ORDER__MATCH, QueryOption.TOKEN_COUNT__IGNORE, |
| QueryOption.CASE__MATCH); |
| |
| private static final Criteria RELATED_TO = new CriteriaRelatedTo(CoreRelationTypes.Default_Hierarchical__Child, |
| asList(45, 61)); |
| |
| private static final Criteria ALL_ARTIFACTS = new CriteriaAllArtifacts(); |
| |
| private static final Criteria FOLLOW_RELATION_TYPE = new CriteriaRelationTypeFollow( |
| CoreRelationTypes.Default_Hierarchical__Child); |
| |
| @Rule |
| public ExpectedException thrown = ExpectedException.none(); |
| |
| // @formatter:off |
| @Mock private Log logger; |
| @Mock private SqlProvider sqlProvider; |
| @Mock private IdentityLocator identityService; |
| @Mock private TagProcessor tagProcessor; |
| |
| @Mock private ExecutorAdmin executorAdmin; |
| @Mock private OrcsSession session; |
| @Mock private SqlJoinFactory joinFactory; |
| // @formatter:on |
| |
| private final static long EXPECTED_BRANCH_ID = 570; |
| private final static int EXPECTED_TX_ID = 45678; |
| |
| private QuerySqlContextFactory queryEngine; |
| private QueryData queryData; |
| |
| @Before |
| public void setUp() throws OseeCoreException { |
| MockitoAnnotations.initMocks(this); |
| |
| String sessionId = GUID.create(); |
| when(session.getGuid()).thenReturn(sessionId); |
| |
| SqlHandlerFactory handlerFactory = createArtifactSqlHandlerFactory(logger, identityService, tagProcessor); |
| queryEngine = new ArtifactQuerySqlContextFactoryImpl(logger, joinFactory, sqlProvider, handlerFactory); |
| |
| CriteriaSet criteriaSet = new CriteriaSet(); |
| Options options = OptionsUtil.createOptions(); |
| criteriaSet.add(new CriteriaBranch(CoreBranches.COMMON.getUuid())); |
| queryData = new QueryData(criteriaSet, options); |
| |
| when(sqlProvider.getSql(OseeSql.QUERY_BUILDER)).thenReturn("/*+ ordered */"); |
| |
| doAnswer(new Answer<Void>() { |
| |
| @Override |
| public Void answer(InvocationOnMock invocation) throws Throwable { |
| Object[] args = invocation.getArguments(); |
| String value = (String) args[0]; |
| assertEquals(QUICK_SEARCH_VALUE, value); |
| |
| TagCollector collector = (TagCollector) args[1]; |
| collector.addTag(WORD_1, CODED_WORD_1); |
| collector.addTag(WORD_2, CODED_WORD_2); |
| collector.addTag(WORD_3, CODED_WORD_3); |
| return null; |
| } |
| }).when(tagProcessor).collectFromString(eq(QUICK_SEARCH_VALUE), any(TagCollector.class)); |
| |
| when(joinFactory.createIdJoinQuery()).thenAnswer(new Answer<IdJoinQuery>() { |
| |
| @Override |
| public IdJoinQuery answer(InvocationOnMock invocation) throws Throwable { |
| return new IdJoinQuery(null, -1L, 23); |
| } |
| |
| }); |
| when(joinFactory.createCharJoinQuery()).thenAnswer(new Answer<CharJoinQuery>() { |
| |
| @Override |
| public CharJoinQuery answer(InvocationOnMock invocation) throws Throwable { |
| return new CharJoinQuery(null, -1L, 23); |
| } |
| |
| }); |
| } |
| |
| @Test |
| public void testCount() throws Exception { |
| String expected = |
| "SELECT/*+ ordered */ count(art2.art_id)\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?"; |
| |
| queryData.addCriteria(GUIDS, IDS, TYPES); |
| |
| QuerySqlContext context = queryEngine.createCountContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(6, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| } |
| |
| @Test |
| public void testCountHistorical() throws Exception { |
| String expected = "SELECT count(xTable.art_id) FROM (\n" + // |
| " SELECT/*+ ordered */ max(txs1.transaction_id) as transaction_id, art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.art_type_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " GROUP BY art1.art_id, txs1.branch_id\n" + // |
| ") xTable"; |
| |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| queryData.addCriteria(TYPES); |
| |
| QuerySqlContext context = queryEngine.createCountContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(3, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQuery() throws Exception { |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, IDS, TYPES); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(6, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| } |
| |
| @Test |
| public void testQuerySqlIncludeDeleted() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current IN (1, 2, 3) AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current IN (1, 2, 3) AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, IDS, TYPES); |
| OptionsUtil.setIncludeDeletedArtifacts(queryData.getOptions(), true); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(6, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| } |
| |
| @Test |
| public void testQueryHistorical() throws OseeCoreException { |
| String expected = |
| "WITH artUuid1 AS ( \n" + // |
| "SELECT max(txs.transaction_id) as transaction_id, art.art_id as art_id\n" + // |
| " FROM osee_txs txs, osee_artifact art, osee_join_char_id id\n" + // |
| " WHERE txs.gamma_id = art.gamma_id\n" + // |
| " AND art.guid = id.id AND id.query_id = ? AND txs.transaction_id <= ? AND txs.branch_id = ?\n" + // |
| " GROUP BY art.art_id\n" + // |
| " )\n" + // |
| "SELECT max(txs2.transaction_id) as transaction_id, art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2, artUuid1\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ?\n" + // |
| " AND \n" + // |
| "artUuid1.transaction_id = txs2.transaction_id AND artUuid1.art_id = art2.art_id AND art2.gamma_id = txs2.gamma_id AND txs2.transaction_id <= ?\n" + // |
| " AND \n" + //" |
| "txs2.mod_type <> 3 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " GROUP BY art2.art_id, txs2.branch_id\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, IDS, TYPES); |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(11, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(3, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(joins.get(2).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| } |
| |
| @Test |
| public void testQueryHistoricalMultipleItems() throws OseeCoreException { |
| String expected = |
| "WITH artUuid1 AS ( \n" + // |
| "SELECT max(txs.transaction_id) as transaction_id, art.art_id as art_id\n" + // |
| " FROM osee_txs txs, osee_artifact art, osee_join_char_id id\n" + // |
| " WHERE txs.gamma_id = art.gamma_id\n" + // |
| " AND art.guid = id.id AND id.query_id = ? AND txs.transaction_id <= ? AND txs.branch_id = ?\n" + // |
| " GROUP BY art.art_id\n" + // |
| " ), \n" + // |
| " attrExt1 AS ( \n" + // |
| "SELECT max(txs.transaction_id) as transaction_id, attr.art_id as art_id\n" + // |
| " FROM osee_txs txs, osee_attribute attr\n" + // |
| " WHERE txs.gamma_id = attr.gamma_id\n" + // |
| " AND att.attr_type_id = ? AND txs.transaction_id <= ? AND txs.branch_id = ?\n" + // |
| " GROUP BY attr.art_id\n" + // |
| " )\n" + // |
| "SELECT max(txs2.transaction_id) as transaction_id, art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2, osee_attribute att1, osee_txs txs3, osee_relation_link rel1, osee_txs txs4, artUuid1, attrExt1\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ?\n" + // |
| " AND \n" + // |
| "artUuid1.transaction_id = txs2.transaction_id AND artUuid1.art_id = art2.art_id AND art2.gamma_id = txs2.gamma_id AND txs2.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs2.mod_type <> 3 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.attr_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.art_id = art1.art_id AND att1.art_id = art2.art_id\n" + // |
| " AND \n" + // |
| "attrExt1.transaction_id = txs3.transaction_id AND attrExt1.art_id = att1.art_id\n" + // |
| " AND \n" + // |
| "att1.gamma_id = txs3.gamma_id AND txs3.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs3.mod_type <> 3 AND txs3.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art1.art_id OR rel1.b_art_id = art1.art_id)\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art2.art_id OR rel1.b_art_id = art2.art_id)\n" + // |
| " AND \n" + // |
| "rel1.gamma_id = txs4.gamma_id\n" + // |
| " AND \n" + // |
| "txs4.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs4.mod_type <> 3 AND txs4.branch_id = ?\n" + // |
| " GROUP BY art2.art_id, txs2.branch_id\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, IDS, TYPES, ATTR_TYPE_EXITS, REL_TYPE_EXISTS); |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(20, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(3, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(joins.get(2).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), iterator.next()); |
| |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), iterator.next()); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryGuids() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| queryData.addCriteria(IDS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(2, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(1, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryArtifactTypes() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.art_type_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| queryData.addCriteria(TYPES); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(2, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryAttribute() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_attribute att1, osee_txs txs1, osee_artifact art1, osee_txs txs2\n" + // |
| " WHERE \n" + // |
| "att1.attr_type_id = ? AND att1.value = ?\n" + // |
| " AND \n" + // |
| "art1.art_id = att1.art_id AND att1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(ATTRIBUTE); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(4, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), iterator.next()); |
| Assert.assertEquals("Hello", iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryAtttributeKeyword() throws OseeCoreException { |
| String expected = "WITH gamma1 AS ( \n" + // |
| " ( \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " ) \n" + // |
| " ), \n" + // |
| " att1 AS ( \n" + // |
| " SELECT art_id FROM osee_attribute att, osee_txs txs, osee_join_id jid1, gamma1\n" + // |
| " WHERE \n" + // |
| " att.gamma_id = gamma1.gamma_id AND att.attr_type_id = jid1.id AND jid1.query_id = ?\n" + // |
| " AND \n" + // |
| " att.gamma_id = txs.gamma_id AND txs.tx_current = 1 AND txs.branch_id = ?\n" + // |
| " )\n" + // |
| "SELECT art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1, att1\n" + // |
| " WHERE \n" + // |
| "art1.art_id = att1.art_id\n" + // |
| " AND \n" + // |
| "txs1.gamma_id = art1.gamma_id\n" + // |
| " AND \n" + // |
| "txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| queryData.addCriteria(ATTRIBUTE_KEYWORD); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(6, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(1, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(CODED_WORD_1, iterator.next()); // Coded Hello |
| Assert.assertEquals(CODED_WORD_2, iterator.next()); // Coded two |
| Assert.assertEquals(CODED_WORD_3, iterator.next()); // Coded three |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryAtttributeCombined() throws OseeCoreException { |
| String expected = "WITH gamma1 AS ( \n" + // |
| " ( \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " ) \n" + // |
| " ), \n" + // |
| " att2 AS ( \n" + // |
| " SELECT art_id FROM osee_attribute att, osee_txs txs, osee_join_id jid1, gamma1\n" + // |
| " WHERE \n" + // |
| " att.gamma_id = gamma1.gamma_id AND att.attr_type_id = jid1.id AND jid1.query_id = ?\n" + // |
| " AND \n" + // |
| " att.gamma_id = txs.gamma_id AND txs.tx_current = 1 AND txs.branch_id = ?\n" + // |
| " )\n" + // |
| "SELECT art1.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_attribute att1, osee_txs txs1, osee_artifact art1, osee_txs txs2, att2\n" + // |
| " WHERE \n" + // |
| "att1.attr_type_id = ? AND att1.value = ?\n" + // |
| " AND \n" + // |
| "art1.art_id = att1.art_id AND att1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_id = att2.art_id\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ?\n" + // |
| " ORDER BY art1.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(ATTRIBUTE, ATTRIBUTE_KEYWORD, TYPES); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(10, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(1, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(CODED_WORD_1, iterator.next()); // Coded Hello |
| Assert.assertEquals(CODED_WORD_2, iterator.next()); // Coded two |
| Assert.assertEquals(CODED_WORD_3, iterator.next()); // Coded three |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), iterator.next()); |
| Assert.assertEquals("Hello", iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), iterator.next()); |
| } |
| |
| @Test |
| public void testQueryExistsNoBranch() throws OseeCoreException { |
| |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, " + // |
| "osee_join_char_id jch1, osee_artifact art2, osee_txs txs2, " + // |
| "osee_attribute att1, osee_txs txs3, " + // |
| "osee_relation_link rel1, osee_txs txs4\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.attr_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.art_id = art1.art_id AND att1.art_id = art2.art_id\n" + // |
| " AND \n" + // |
| "att1.gamma_id = txs3.gamma_id AND txs3.tx_current = 1 AND txs3.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art1.art_id OR rel1.b_art_id = art1.art_id)\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art2.art_id OR rel1.b_art_id = art2.art_id)\n" + // |
| " AND \n" + // |
| "rel1.gamma_id = txs4.gamma_id\n" + // |
| " AND \n" + // |
| "txs4.tx_current = 1 AND txs4.branch_id = ?\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, TYPES, REL_TYPE_EXISTS, IDS, ATTR_TYPE_EXITS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(10, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Assert.assertEquals(joins.get(0).getQueryId(), parameters.get(0)); |
| Assert.assertEquals(joins.get(1).getQueryId(), parameters.get(2)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(3)); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(4)); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(5)); |
| |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), parameters.get(6)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(7)); |
| Assert.assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), parameters.get(8)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(9)); |
| } |
| |
| @Test |
| public void testQueryExistsWithBranch() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, " + // |
| "osee_join_char_id jch1, osee_artifact art2, osee_txs txs2, " + // |
| "osee_attribute att1, osee_txs txs3, " + // |
| "osee_relation_link rel1, osee_txs txs4\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.attr_type_id = ?\n" + // |
| " AND \n" + // |
| "att1.art_id = art1.art_id AND att1.art_id = art2.art_id\n" + // |
| " AND \n" + // |
| "att1.gamma_id = txs3.gamma_id AND txs3.tx_current = 1 AND txs3.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art1.art_id OR rel1.b_art_id = art1.art_id)\n" + // |
| " AND \n" + // |
| "(rel1.a_art_id = art2.art_id OR rel1.b_art_id = art2.art_id)\n" + // |
| " AND \n" + // |
| "rel1.gamma_id = txs4.gamma_id\n" + // |
| " AND \n" + // |
| "txs4.tx_current = 1 AND txs4.branch_id = ?\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, TYPES, REL_TYPE_EXISTS, IDS, ATTR_TYPE_EXITS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(10, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Assert.assertEquals(joins.get(0).getQueryId(), parameters.get(0)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(1)); |
| |
| Assert.assertEquals(joins.get(1).getQueryId(), parameters.get(2)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(3)); |
| |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(4)); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(5)); |
| |
| Assert.assertEquals(CoreAttributeTypes.Name.getGuid(), parameters.get(6)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(7)); |
| |
| Assert.assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), parameters.get(8)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(9)); |
| } |
| |
| @Test |
| public void testRelatedTo() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs2.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1, osee_join_char_id jch1, osee_artifact art2, osee_txs txs2, osee_join_id jid2, osee_relation_link rel1, osee_txs txs3\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art2.guid = jch1.id AND jch1.query_id = ? AND art2.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_type_id = ? AND art2.art_type_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ? AND rel1.b_art_id = jid2.id AND jid2.query_id = ?\n" + // |
| " AND \n" + // |
| "rel1.a_art_id = art1.art_id\n" + // |
| " AND \n" + // |
| "rel1.a_art_id = art2.art_id\n" + // |
| " AND \n" + // |
| "rel1.gamma_id = txs3.gamma_id\n" + // |
| " AND \n" + // |
| "txs3.tx_current = 1 AND txs3.branch_id = ?\n" + // |
| " ORDER BY art2.art_id, txs2.branch_id"; |
| |
| queryData.addCriteria(GUIDS, TYPES, IDS, RELATED_TO); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(9, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(3, joins.size()); |
| |
| Assert.assertEquals(joins.get(0).getQueryId(), parameters.get(0)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(1)); |
| |
| Assert.assertEquals(joins.get(1).getQueryId(), parameters.get(2)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(3)); |
| |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(4)); |
| Assert.assertEquals(CoreArtifactTypes.CodeUnit.getGuid(), parameters.get(5)); |
| |
| Assert.assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), parameters.get(6)); |
| Assert.assertEquals(joins.get(2).getQueryId(), parameters.get(7)); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, parameters.get(8)); |
| } |
| |
| @Test |
| public void testCountAllArtifactsFromBranch() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ count(art1.art_id)\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?"; |
| |
| queryData.addCriteria(ALL_ARTIFACTS); |
| |
| QuerySqlContext context = queryEngine.createCountContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(1, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testCountAllArtifactsFromBranchHistorical() throws OseeCoreException { |
| String expected = "SELECT count(xTable.art_id) FROM (\n" + // |
| " SELECT/*+ ordered */ max(txs1.transaction_id) as transaction_id, art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " GROUP BY art1.art_id, txs1.branch_id\n" + // |
| ") xTable"; |
| |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| queryData.addCriteria(ALL_ARTIFACTS); |
| |
| QuerySqlContext context = queryEngine.createCountContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(2, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryAllArtifactsFromBranch() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| queryData.addCriteria(ALL_ARTIFACTS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(1, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testQueryAllArtifactsFromBranchHistorical() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ max(txs1.transaction_id) as transaction_id, art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " GROUP BY art1.art_id, txs1.branch_id\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| queryData.addCriteria(ALL_ARTIFACTS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(2, parameters.size()); |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(EXPECTED_TX_ID, iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testAllArtifactWithOtherCriteria() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_id jid1, osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.art_id = jid1.id AND jid1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| queryData.addCriteria(ALL_ARTIFACTS, IDS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(2, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(1, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| Assert.assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| Assert.assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testRelationTypeNotExists() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "NOT EXISTS (SELECT 1 FROM osee_relation_link rel, osee_txs txs WHERE rel.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "(rel.a_art_id = art1.art_id OR rel.b_art_id = art1.art_id)\n" + // |
| " AND \n" + // |
| "rel.gamma_id = txs.gamma_id\n" + // |
| " AND \n" + // |
| "txs.tx_current = 1 AND txs.branch_id = ?)\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| Criteria relTypeNotExists = new CriteriaRelationTypeNotExists(CoreRelationTypes.Default_Hierarchical__Child); |
| queryData.addCriteria(relTypeNotExists); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(3, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| } |
| |
| @Test |
| public void testRelationTypeSideNotExists() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "NOT EXISTS (SELECT 1 FROM osee_relation_link rel, osee_txs txs WHERE rel.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "rel.b_art_id = art1.art_id\n" + // |
| " AND \n" + // |
| "rel.gamma_id = txs.gamma_id\n" + // |
| " AND \n" + // |
| "txs.tx_current = 1 AND txs.branch_id = ?)\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| Criteria relTypeSideNotExists = |
| new CriteriaRelationTypeSideNotExists(CoreRelationTypes.Default_Hierarchical__Child); |
| queryData.addCriteria(relTypeSideNotExists); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(3, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| } |
| |
| @Test |
| public void testRelationTypeSideExists() throws OseeCoreException { |
| String expected = "SELECT/*+ ordered */ art1.art_id, txs1.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1, osee_relation_link rel1, osee_txs txs2\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ?\n" + // |
| " AND \n" + // |
| "rel1.b_art_id = art1.art_id\n" + // |
| " AND \n" + // |
| "rel1.gamma_id = txs2.gamma_id\n" + // |
| " AND \n" + // |
| "txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " ORDER BY art1.art_id, txs1.branch_id"; |
| |
| Criteria relTypeSideNotExists = new CriteriaRelationTypeSideExists(CoreRelationTypes.Default_Hierarchical__Child); |
| queryData.addCriteria(relTypeSideNotExists); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(3, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| } |
| |
| @Test |
| public void testRelationTypeFollow() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ art2.art_id, txs3.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1, osee_relation_link rel1, osee_txs txs2, osee_artifact art2, osee_txs txs3\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ? AND rel1.a_art_id = art1.art_id AND rel1.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.b_art_id = art2.art_id AND art2.gamma_id = txs3.gamma_id AND txs3.tx_current = 1 AND txs3.branch_id = ?\n" + // |
| " ORDER BY art2.art_id, txs3.branch_id"; |
| |
| queryData.addCriteria(FOLLOW_RELATION_TYPE); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(4, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testRelationTypeFollowCombined() throws OseeCoreException { |
| String expected = |
| "WITH gamma1 AS ( \n" + // |
| " ( \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " INTERSECT \n" + // |
| " SELECT gamma_id FROM osee_search_tags WHERE coded_tag_id = ?\n" + // |
| " ) \n" + // |
| " ), \n" + // |
| " att1 AS ( \n" + // |
| " SELECT art_id FROM osee_attribute att, osee_txs txs, osee_join_id jid1, gamma1\n" + // |
| " WHERE \n" + // |
| " att.gamma_id = gamma1.gamma_id AND att.attr_type_id = jid1.id AND jid1.query_id = ?\n" + // |
| " AND \n" + // |
| " att.gamma_id = txs.gamma_id AND txs.tx_current = 1 AND txs.branch_id = ?\n" + // |
| " )\n" + // |
| "SELECT art2.art_id, txs3.branch_id\n" + // |
| " FROM \n" + // |
| "osee_join_char_id jch1, osee_artifact art1, osee_txs txs1, osee_relation_link rel1, osee_txs txs2, osee_artifact art2, osee_txs txs3, att1\n" + // |
| " WHERE \n" + // |
| "art1.guid = jch1.id AND jch1.query_id = ? AND art1.gamma_id = txs1.gamma_id AND txs1.tx_current = 1 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "art1.art_id = att1.art_id\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ? AND rel1.a_art_id = art1.art_id AND rel1.gamma_id = txs2.gamma_id AND txs2.tx_current = 1 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.b_art_id = art2.art_id AND art2.gamma_id = txs3.gamma_id AND txs3.tx_current = 1 AND txs3.branch_id = ?\n" + // |
| " ORDER BY art2.art_id, txs3.branch_id"; |
| |
| queryData.addCriteria(ATTRIBUTE_KEYWORD, FOLLOW_RELATION_TYPE, GUIDS); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(10, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(2, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| |
| assertEquals(CODED_WORD_1, iterator.next()); // Coded Hello |
| assertEquals(CODED_WORD_2, iterator.next()); // Coded two |
| assertEquals(CODED_WORD_3, iterator.next()); // Coded three |
| assertEquals(joins.get(0).getQueryId(), iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(joins.get(1).getQueryId(), iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| |
| @Test |
| public void testRelationTypeFollowHistorical() throws OseeCoreException { |
| String expected = |
| "SELECT/*+ ordered */ max(txs3.transaction_id) as transaction_id, art2.art_id, txs3.branch_id\n" + // |
| " FROM \n" + // |
| "osee_artifact art1, osee_txs txs1, osee_relation_link rel1, osee_txs txs2, osee_artifact art2, osee_txs txs3\n" + // |
| " WHERE \n" + // |
| "art1.gamma_id = txs1.gamma_id AND txs1.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs1.mod_type <> 3 AND txs1.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.rel_link_type_id = ? AND rel1.a_art_id = art1.art_id AND rel1.gamma_id = txs2.gamma_id AND txs2.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs2.mod_type <> 3 AND txs2.branch_id = ?\n" + // |
| " AND \n" + // |
| "rel1.b_art_id = art2.art_id AND art2.gamma_id = txs3.gamma_id AND txs3.transaction_id <= ?\n" + // |
| " AND \n" + // |
| "txs3.mod_type <> 3 AND txs3.branch_id = ?\n" + // |
| " GROUP BY art2.art_id, txs3.branch_id\n" + // |
| " ORDER BY art2.art_id, txs3.branch_id"; |
| |
| OptionsUtil.setFromTransaction(queryData.getOptions(), EXPECTED_TX_ID); |
| queryData.addCriteria(FOLLOW_RELATION_TYPE); |
| |
| QuerySqlContext context = queryEngine.createQueryContext(session, queryData); |
| Assert.assertEquals(expected, context.getSql()); |
| |
| List<Object> parameters = context.getParameters(); |
| Assert.assertEquals(7, parameters.size()); |
| |
| List<AbstractJoinQuery> joins = context.getJoins(); |
| Assert.assertEquals(0, joins.size()); |
| |
| Iterator<Object> iterator = parameters.iterator(); |
| assertEquals(EXPECTED_TX_ID, iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(CoreRelationTypes.Default_Hierarchical__Child.getGuid(), iterator.next()); |
| assertEquals(EXPECTED_TX_ID, iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| assertEquals(EXPECTED_TX_ID, iterator.next()); |
| assertEquals(EXPECTED_BRANCH_ID, iterator.next()); |
| } |
| } |
