Examples with ReadStoredQuery ambit2.db.update.storedquery.ReadStoredQuery used on opensource projects

Example 1 with ReadStoredQuery

use of ambit2.db.update.storedquery.ReadStoredQuery in project ambit-mirror by ideaconsult.

the class MetadatasetResource method getQuery.

protected IQueryRetrieval<M> getQuery(Context context, Request request, Response response, boolean IDcanBeEmpty) throws ResourceException {
    Form form = getResourceRef(request).getQueryAsForm();
    try {
        headless = Boolean.parseBoolean(form.getFirstValue("headless"));
    } catch (Exception x) {
        headless = false;
    }
    AbstractReadDataset query = null;
    structureParam = getStructureParameter();
    StringCondition condition;
    try {
        condition = StringCondition.getInstance(form.getFirstValue(QueryResource.condition));
    } catch (Exception x) {
        condition = StringCondition.getInstance(StringCondition.C_EQ);
    }
    Property property = new Property(null);
    property.setClazz(null);
    property.setLabel(null);
    property.setReference(null);
    for (search_features sf : search_features.values()) {
        Object id = form.getFirstValue(sf.name());
        if (id != null) {
            // because we are not storing full local references!
            if (search_features.feature_hassource.equals(sf)) {
                String parent = getRequest().getRootRef().toString();
                int p = id.toString().indexOf(parent);
                if (p >= 0) {
                    // yet one more hack ... should store at least prefixes
                    id = id.toString().substring(p + parent.length()).replace("/algorithm/", "").replace("/model/", "");
                }
            }
            sf.setProperty(property, id);
            if (query == null) {
                query = new QueryDatasetByFeatures(property, condition);
                ((QueryDatasetByFeatures) query).setStructure(structureParam);
            }
        }
    }
    if (query == null) {
        query = new ReadDataset();
        query.setValue(null);
    }
    Object id = request.getAttributes().get(DatasetStructuresResource.datasetKey);
    if (id != null)
        try {
            Integer idnum = new Integer(Reference.decode(id.toString()));
            dataset = (M) new SourceDataset();
            dataset.setID(idnum);
            query.setValue(dataset);
        } catch (NumberFormatException x) {
            if (id.toString().startsWith(DatasetStructuresResource.QR_PREFIX)) {
                String key = id.toString().substring(DatasetStructuresResource.QR_PREFIX.length());
                try {
                    IQueryRetrieval<M> q = (IQueryRetrieval<M>) new ReadStoredQuery(Integer.parseInt(key.toString()));
                    return q;
                } catch (NumberFormatException xx) {
                    throw new InvalidResourceIDException(id);
                }
            } else {
                dataset = (M) new SourceDataset();
                dataset.setName(id.toString());
                query.setValue(dataset);
            }
        } catch (Exception x) {
            throw new InvalidResourceIDException(id);
        }
    if (!IDcanBeEmpty && (query.getValue() == null))
        throw new ResourceException(Status.CLIENT_ERROR_BAD_REQUEST, "Empty dataset ID!");
    return query;
}

Example 2 with ReadStoredQuery

use of ambit2.db.update.storedquery.ReadStoredQuery in project ambit-mirror by ideaconsult.

the class ProcessorCreateQuery method process.

public IStoredQuery process(IQueryObject<IStructureRecord> target) throws AmbitException {
    if (target == null)
        throw new AmbitException("Undefined query!");
    if (qexec == null) {
        qexec = new QueryExecutor();
        qexec.setCloseConnection(false);
    }
    qexec.setConnection(connection);
    try {
        if (result == null) {
            if (!copy & (target instanceof QueryStoredResults))
                return ((QueryStoredResults) target).getFieldname();
            result = new StoredQuery(-1);
            result.setName(queryName == null ? UUID.randomUUID().toString() : queryName);
        } else {
            if (target instanceof QueryStoredResults) {
                if ((((QueryStoredResults) target).getId() == result.getId()) && (result.getId() > 0))
                    // if we are copying to the same query , don't delete!
                    delete = false;
            }
        }
        result.setQuery(target);
        connection.setAutoCommit(false);
        // create entry in the query table
        if (result.getId() <= 0) {
            // read by name and folder title
            ReadStoredQuery findIfExists = new ReadStoredQuery();
            findIfExists.setValue(result);
            // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            findIfExists.setFieldname(getSession().getName());
            ResultSet found = null;
            try {
                found = qexec.process(findIfExists);
                while (found.next()) {
                    IStoredQuery q = findIfExists.getObject(found);
                    result.setID(q.getID());
                    break;
                }
            } catch (Exception x) {
                logger.warning(x.getMessage());
            } finally {
                try {
                    found.close();
                } catch (Exception x) {
                }
            }
            // if still not found, add it! TODO refactor it to use UpdateExecutor
            if (result.getId() <= 0) {
                PreparedStatement s = connection.prepareStatement(CreateStoredQuery.sql_byname, Statement.RETURN_GENERATED_KEYS);
                s.setNull(1, Types.INTEGER);
                if (result.getName().length() > 255)
                    s.setString(2, result.getName().substring(0, 255));
                else
                    s.setString(2, result.getName());
                try {
                    s.setString(3, result.getQuery().toString() == null ? "Results" : result.getQuery().toString());
                } catch (Exception x) {
                    s.setString(3, "Results");
                }
                s.setString(4, getSession().getName());
                // execute
                if (s.executeUpdate() > 0) {
                    ResultSet rss = s.getGeneratedKeys();
                    while (rss.next()) result.setId(new Integer(rss.getInt(1)));
                    rss.close();
                }
                s.close();
            }
        }
        // sometimes we want to remove the old content
        if (delete && (result.getId() > 0)) {
            PreparedStatement s = null;
            try {
                s = connection.prepareStatement("Delete from query_results where idquery=?");
                s.setInt(1, result.getId());
                s.executeUpdate();
            } catch (Exception x) {
            } finally {
                try {
                    if (s != null)
                        s.close();
                } catch (Exception x) {
                }
            }
        }
        if (result.getId() > 0) {
            int rows = 0;
            if ((result.getQuery() instanceof IQueryRetrieval) && ((IQueryRetrieval) result.getQuery()).isPrescreen()) {
                rows = insertScreenedResults(result, (IQueryRetrieval<IStructureRecord>) result.getQuery());
            } else {
                rows = insertResults(result);
                if (rows > 0)
                    connection.commit();
                else
                    connection.rollback();
            }
            insertProfile(result, profile);
        }
        return result;
    } catch (Exception x) {
        try {
            connection.rollback();
        } catch (SQLException xx) {
        }
        throw new ProcessorException(this, x);
    } finally {
        try {
            close();
        } catch (Exception e) {
        }
    }
}

Example 3 with ReadStoredQuery

use of ambit2.db.update.storedquery.ReadStoredQuery in project ambit-mirror by ideaconsult.

the class QueryCombinedTest method test.

@Test
public void test() throws Exception {
    ReadStoredQuery qs = new ReadStoredQuery();
    qs.setName("test");
    QueryCombined qc = new QueryCombinedStructure();
    qc.setId(55);
    QueryStructureByID q = new QueryStructureByID(100);
    q.setCondition(NumberCondition.getInstance("<="));
    Assert.assertNotNull(q.getParameters().get(1).getValue());
    qc.add(q);
    // between 150 and 200
    QueryStructureByID q1 = new QueryStructureByID(150, 200);
    Assert.assertNotNull(q1.getParameters().get(1).getValue());
    Assert.assertNotNull(q1.getParameters().get(3).getValue());
    qc.add(q1);
    qc.setCombine_as_and(false);
    /*
		Assert.assertEquals("select ? as idquery,idchemical,idstructure,if(type_structure='NA',0,1) as selected,? as metric,? as text from structure where idstructure <= ? order by type_structure desc\nunion\nselect ? as idquery,idchemical,idstructure,if(type_structure='NA',0,1) as selected,? as metric,? as text from structure where idstructure between  ? and ? order by type_structure desc",
				qc.getSQL());
		*/
    Assert.assertNotNull(q.getParameters().get(1).getValue());
    List<QueryParam> params = qc.getParameters();
    Assert.assertNotNull(params);
    Assert.assertEquals(9, params.size());
    Object[] values = { 55, 1, null, 100, 55, 1, null, 150, 200 };
    for (int i = 0; i < params.size(); i++) {
        if (values[i] != null) {
            Assert.assertEquals(Integer.class, params.get(i).getType());
            Assert.assertEquals(values[i], params.get(i).getValue());
        }
    }
    qc.setScope(qs);
/*
		qc.setScope(null);
		qc.setCombine_as_and(true);
		assertEquals("select Q1.idquery,s.idchemical,idstructure,Q1.selected as selected,Q1.metric as metric from structure as s\njoin\n(select ? as idquery,idchemical,idstructure,1 as selected,1 as metric from structure where idstructure <= ?)\nas Q1\nusing (idstructure)\njoin\n(select ? as idquery,idchemical,idstructure,1 as selected,1 as metric from structure where idstructure between ? and ?)\nas Q2\nusing (idstructure)",qc.getSQL());
		System.out.println(qc.getSQL());
		
		QueryStored qs = new QueryStored();
		qs.setName("test");
		
		qc.setScope(qs);
		System.out.println(qc.getSQL());
		*/
}

Example 4 with ReadStoredQuery

use of ambit2.db.update.storedquery.ReadStoredQuery in project ambit-mirror by ideaconsult.

the class QueryStoredTest method verify.

@Override
protected void verify(ReadStoredQuery query, ResultSet rs) throws Exception {
    int c = 0;
    while (rs.next()) {
        IStoredQuery q = query.getObject(rs);
        Assert.assertTrue(((q.getId() == 1) && (q.getName().equals("test query 1"))) || ((q.getId() == 2) && (q.getName().equals("test query 2"))));
        c++;
    }
    Assert.assertEquals(1, c);
}

Example 5 with ReadStoredQuery

use of ambit2.db.update.storedquery.ReadStoredQuery in project ambit-mirror by ideaconsult.

the class QueryStoredTest method createQuery.

@Override
protected ReadStoredQuery createQuery() throws Exception {
    ReadStoredQuery q = new ReadStoredQuery();
    Assert.assertEquals("select idquery,name,title,content from query join sessions using(idsessions)\n where  title = ? order by name", q.getSQL());
    StoredQuery sq = new StoredQuery(1);
    sq.setName(null);
    q.setValue(sq);
    Assert.assertEquals("select idquery,name,title,content from query join sessions using(idsessions)\n where  idquery = ? and  title = ? order by name", q.getSQL());
    return q;
}