com.orientechnologies.orient.core.sql.query.OSQLSynchQuery Java Examples

private void fixComponentBatch(final ODatabaseDocumentTx db, final OIdentifiable bucket) {
  log.debug("Processing batch of {} yum component records...", BATCH_SIZE);

  OSQLSynchQuery<Object> query = new OSQLSynchQuery<>(SELECT_COMPONENT_BATCH_SQL);

  List<ODocument> components = db.query(query, bucket, new ORecordId());

  while (!components.isEmpty()) {
    ORID last = components.get(components.size() - 1).getIdentity();

    for (ODocument component : components) {
      fixComponent(db, component, bucket);
    }

    components = db.query(query, bucket, last);
  }
}
 

protected long getTotal(Map<String, Object> data, Map<String, Object> criteria) {
    long total = 0;
    StringBuilder sb = new StringBuilder("SELECT COUNT(*) as count FROM (TRAVERSE out_HasComment FROM ").append(data.get("@rid")).append(") ");
    String whereClause = DbService.getWhereClause(criteria);
    if(whereClause != null && whereClause.length() > 0) {
        sb.append(whereClause);
    }
    //System.out.println("sql=" + sb);
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        total = ((ODocument)graph.getRawGraph().query(new OSQLSynchQuery<ODocument>(sb.toString())).get(0)).field("count");
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return total;
}
 

/**
 * Find all GAVs that qualify for deletion.
 */
@VisibleForTesting
Set<GAV> findSnapshotCandidates(final StorageTx tx, final Repository repository)
{
  log.info(PROGRESS, "Searching for GAVS with snapshots that qualify for deletion on repository '{}'",
      repository.getName());

  final Bucket bucket = tx.findBucket(repository);
  final OResultSet<ODocument> result = tx.getDb().command(new OSQLSynchQuery<>(GAVS_WITH_SNAPSHOTS))
      .execute(AttachedEntityHelper.id(bucket));
  return result.stream().map((doc) -> {
    String group = doc.field(P_GROUP, String.class);
    String name = doc.field(P_NAME, String.class);
    String baseVersion = doc.field("baseVersion", String.class);
    Integer count = doc.field("cnt", Integer.class);
    return new GAV(group, name, baseVersion, count);
  }).collect(Collectors.toSet());
}
 

protected String getRules(String host) {
    String sql = "SELECT FROM Rule";
    if(host != null) {
        sql = sql + " WHERE host = '" + host;
    }
    String json = null;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
        List<ODocument> rules = graph.getRawGraph().command(query).execute();
        json = OJSONWriter.listToJSON(rules, null);
    } catch (Exception e) {
        logger.error("Exception:", e);
        throw e;
    } finally {
        graph.shutdown();
    }
    return json;
}
 

protected String getBranchDropdownDb(String branchType, String host) {
    String json = null;
    String sql = "SELECT FROM " + branchType + " WHERE host = ? ORDER BY categoryId";
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> docs = graph.getRawGraph().command(query).execute(host);
        if(docs.size() > 0) {
            List<Map<String, String>> list = new ArrayList<Map<String, String>>();
            for(ODocument doc: docs) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("label", (String)doc.field("categoryId"));
                map.put("value", doc.field("@rid").toString());
                list.add(map);
            }
            json = mapper.writeValueAsString(list);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return json;
}
 

protected String getCommentTreeDb(String rid, String sortedBy, String sortDir) {
    String sql = "select from Comment where in_HasComment[0] = " + rid + " ORDER BY " + sortedBy + " " + sortDir;
    String json = null;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> list = graph.getRawGraph().command(query).execute();
        if(list.size() > 0) {
            json = OJSONWriter.listToJSON(list, "rid,fetchPlan:[*]in_HasComment:-2 [*]out_ReportSpam:-2 [*]out_UpVote:-2 [*]out_DownVote:-2 in_Create[]:0 [*]out_Create:-2 [*]out_Update:-2 [*]out_HasComment:-1");
            // need to fixed the in_Create within the json using json path.
            DocumentContext dc = JsonPath.parse(json);
            MapFunction propsFunction = new StripPropsMapFunction();
            dc.map("$..in_UpVote[*]", propsFunction);
            dc.map("$..in_DownVote[*]", propsFunction);
            dc.map("$..in_ReportSpam[*]", propsFunction);

            MapFunction createFunction = new StripInCreateMapFunction();
            json = dc.map("$..in_Create[0]", createFunction).jsonString();
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return json;
}
 

protected String getAllConfig() {
    String sql = "SELECT FROM Config";
    String json = null;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
        List<ODocument> configs = graph.getRawGraph().command(query).execute();
        if(configs != null && configs.size() > 0) {
            json = OJSONWriter.listToJSON(configs, null);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
        throw e;
    } finally {
        graph.shutdown();
    }
    return json;
}
 

private Stream<ODocument> findAssets(
    final ODatabaseDocumentTx db,
    final List<String> repositoryNames,
    final String SQL)
{
  return repositoryNames
      .stream()
      .flatMap(repositoryName -> {
        OIndex<?> bucketIdx = db.getMetadata().getIndexManager().getIndex(new OIndexNameBuilder()
            .type("bucket")
            .property(P_REPOSITORY_NAME)
            .build());
        OIdentifiable bucket = (OIdentifiable) bucketIdx.get(repositoryName);
        if (bucket == null) {
          log.debug("Unable to find bucket for {}", repositoryName);
          return Stream.empty();
        }
        List<ODocument> assets =
            db.query(new OSQLSynchQuery<ODocument>(SQL), bucket.getIdentity());
        return assets.stream();
      });
}
 

protected String getTagDropdownDb(String host) {
    String json = null;
    String sql = "SELECT FROM Tag WHERE host = ? ORDER BY tagId";
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> docs = graph.getRawGraph().command(query).execute(host);
        if (docs.size() > 0) {
            List<Map<String, String>> list = new ArrayList<Map<String, String>>();
            for (ODocument doc : docs) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("label", (String) doc.field("tagId"));
                map.put("value", (String) doc.field("tagId"));
                list.add(map);
            }
            json = mapper.writeValueAsString(list);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return json;
}
 

@Override
public void apply() throws Exception {
  List<String> repositoryNames;

  try (ODatabaseDocumentTx configDb = configDatabaseInstance.get().connect()) {
    repositoryNames = configDb.<List<ODocument>>query(
        new OSQLSynchQuery<ODocument>(SELECT_DOCKER_REPOSITORIES)).stream()
        .map(d -> (String) d.field(P_REPOSITORY_NAME))
        .collect(toList());
  }

  if (repositoryNames != null && !repositoryNames.isEmpty()) {
    log.info("Deleting browse_node data from docker repositories to be rebuilt ({}).", repositoryNames);

    try (ODatabaseDocumentTx componentDb = componentDatabaseInstance.get().connect()) {
      OSchemaProxy schema = componentDb.getMetadata().getSchema();
      if (schema.existsClass(C_BROWSE_NODE)) {
        componentDb.command(new OCommandSQL(DELETE_BROWSE_NODE_FROM_REPOSITORIES)).execute(repositoryNames);
      }
    }
  }
}
 

protected List<String> getRecentEntityListDb(String host, String categoryType, String sortedBy, String sortDir) {
    List<String> entityList = null;
    String sql = "select @rid from Post where host = ? and in_HasPost[0].@class = ? order by " + sortedBy + " " + sortDir;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> entities = graph.getRawGraph().command(query).execute(host, categoryType);
        if(entities.size() > 0) {
            entityList = new ArrayList<String>();
            for(ODocument entity: entities) {
                entityList.add(((ODocument)entity.field("rid")).field("@rid").toString());
            }
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return entityList;
}
 

@VisibleForTesting
void updateAssetBlobStoreRefs(final Map<String, String> renamedBlobStores) {
  try (ODatabaseDocumentTx db = componentDatabaseInstance.get().connect()) {
    renamedBlobStores.forEach((originalName, newName) -> {
      log.debug("Searching for BlobStoreRefs, original name: {}, new name: {} ", originalName, newName);

      OSQLSynchQuery query = new OSQLSynchQuery<>("select from asset where blob_ref like ? and @rid > ? limit 100");
      String nameTestValue = originalName + "@%";

      List<ODocument> results = db.query(query, nameTestValue, new ORecordId());

      while (!results.isEmpty()) {
        log.debug("Updating set of {} Refs", results.size());
        ORID last = results.get(results.size() - 1).getIdentity();

        results.forEach(doc -> updateDocWithNewName(originalName, newName, doc));

        results = db.query(query, nameTestValue, last);
      }
    });
  }
}
 

protected List<String> getCategoryEntityListDb(String categoryRid, String sortedBy, String sortDir) {
    List<String> entityList = null;
    String sql = "select @rid from (traverse out_Own, out_HasPost from ?) where @class = 'Post' order by " + sortedBy + " " + sortDir;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> entities = graph.getRawGraph().command(query).execute(categoryRid);
        if(entities.size() > 0) {
            entityList = new ArrayList<String>();
            for(ODocument entity: entities) {
                entityList.add(((ODocument)entity.field("rid")).field("@rid").toString());
            }
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return entityList;
}
 

protected String getProductDb() {
    String json = null;
    String sql = "select from product";
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> products = graph.getRawGraph().command(query).execute();
        if(products.size() > 0) {
            json = OJSONWriter.listToJSON(products, null);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return json;
}
 

protected String getProductDb(String host) {
    String json = null;
    String sql = "select from product where host = ?";
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> products = graph.getRawGraph().command(query).execute(host);
        if(products.size() > 0) {
            json = OJSONWriter.listToJSON(products, null);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return json;
}
 

@Override
protected List<String> getCategoryEntityListDb(String categoryRid, String sortedBy, String sortDir) {
    List<String> entityList = null;
    String sql = "select @rid from (traverse out_Own, out_HasProduct from ?) where @class = 'Product' order by " + sortedBy + " " + sortDir;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql);
        List<ODocument> entities = graph.getRawGraph().command(query).execute(categoryRid);
        if(entities.size() > 0) {
            entityList = new ArrayList<String>();
            for(ODocument entity: entities) {
                entityList.add(((ODocument)entity.field("rid")).field("@rid").toString());
            }
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
    } finally {
        graph.shutdown();
    }
    return entityList;
}
 

protected String getRoles(OrientGraph graph, String host) {
    String sql = "SELECT FROM Role";
    if(host != null) {
        sql = sql + " WHERE host = '" + host + "' OR host IS NULL";
    }
    String json = null;
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
        List<ODocument> roles = graph.getRawGraph().command(query).execute();
        json = OJSONWriter.listToJSON(roles, null);
    } catch (Exception e) {
        logger.error("Exception:", e);
        throw e;
    } finally {
        graph.shutdown();
    }
    return json;
}
 

protected String getAllForm(String host) {
    String sql = "SELECT FROM Form";
    if(host != null) {
        sql = sql + " WHERE host = '" + host + "' OR host IS NULL";
    }
    String json = null;
    OrientGraph graph = ServiceLocator.getInstance().getGraph();
    try {
        OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
        List<ODocument> forms = graph.getRawGraph().command(query).execute();
        if(forms != null && forms.size() > 0) {
            json = OJSONWriter.listToJSON(forms, null);
        }
    } catch (Exception e) {
        logger.error("Exception:", e);
        throw e;
    } finally {
        graph.shutdown();
    }
    return json;
}
 

public void insertPerson() {

    for (int i = 0; i < 1000; i++) {
      ODocument doc = new ODocument("Person");
      doc.field("isDeleted", i % 2 == 0);
      databaseDocumentTx.save(doc);
    }

    List<ODocument> docs = databaseDocumentTx
        .query(new OSQLSynchQuery<ODocument>("select from Person where isDeleted lucene false"));

    Assert.assertEquals(500, docs.size());
    Assert.assertEquals(false, docs.get(0).field("isDeleted"));
    docs = databaseDocumentTx.query(new OSQLSynchQuery<ODocument>("select from Person where isDeleted lucene true"));

    Assert.assertEquals(500, docs.size());
    Assert.assertEquals(true, docs.get(0).field("isDeleted"));
  }
 

/**
 * Get the size of the data
 * @return results size
 */
public long size()
{
	if(size==null)
	{
 	ODatabaseDocument db = OrientDbWebSession.get().getDatabase();
 	OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(queryManager.getCountSql());
 	List<ODocument> ret = db.query(enhanceContextByVariables(query), prepareParams());
 	if(ret!=null && ret.size()>0)
 	{
 		Number sizeNumber = ret.get(0).field("count");
 		size = sizeNumber!=null?sizeNumber.longValue():0;
 	}
 	else
 	{
 		size = 0L;
 	}
	}
	return size;
}
 

protected long getTotalNumberUserFromDb(OrientGraph graph, Map<String, Object> criteria) throws Exception {
    StringBuilder sql = new StringBuilder("SELECT COUNT(*) as count FROM User");
    String whereClause = DbService.getWhereClause(criteria);
    if(whereClause != null && whereClause.length() > 0) {
        sql.append(whereClause);
    }
    logger.debug("sql=" + sql);
    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<ODocument>(sql.toString());
    List<ODocument> list = graph.getRawGraph().command(query).execute();
    return list.get(0).field("count");
}
 

private ODocument createModuleDocument(ODatabaseDocument db) {
    List<ODocument> docs = db.query(new OSQLSynchQuery<>("select from " + ModuleModel.CLASS_NAME, 1));

    if (docs != null && !docs.isEmpty()) {
        return null;
    }
    return new ODocument(ModuleModel.CLASS_NAME);
}
 

public void testManualIndex() {

    databaseDocumentTx.command(new OCommandSQL("insert into index:manual (key,rid) values(['Enrico','Rome'],#5:0) ")).execute();
    databaseDocumentTx.command(new OCommandSQL("insert into index:manual (key,rid) values(['Luca','Rome'],#5:0) ")).execute();
    databaseDocumentTx.command(new OCommandSQL("insert into index:manual (key,rid) values(['Luigi','Rome'],#5:0) ")).execute();

    OIndex<?> manual = databaseDocumentTx.getMetadata().getIndexManager().getIndex("manual");

    Assert.assertEquals(manual.getSize(), 3);

    List<ODocument> docs = databaseDocumentTx.command(new OSQLSynchQuery("select from index:manual where key LUCENE 'Enrico'"))
        .execute();
    Assert.assertEquals(docs.size(), 1);
  }
 

public static OLoggerModule.Module getModule(ODatabaseDocument db) {
    String sql = String.format("select from %s where %s = ?", OLoggerModule.Module.CLASS_NAME, OLoggerModule.OMODULE_NAME);
    List<OIdentifiable> identifiables = db.query(new OSQLSynchQuery<>(sql, 1), OLoggerModule.NAME);
    return CommonUtils.getDocument(identifiables)
            .map(OLoggerModule.Module::new)
            .orElseThrow(() -> new IllegalStateException("There is no module with name: " + OLoggerModule.NAME + " in database"));
}
 

@Test
public void testLuceneVsLucene() throws IOException, ParseException {
  InputStream stream = ClassLoader.getSystemResourceAsStream("testLuceneIndex.sql");

  databaseDocumentTx.command(new OCommandScript("sql", getScriptFromStream(stream))).execute();

  for (ODocument oDocument : databaseDocumentTx.browseClass("Song")) {

    String title = oDocument.field("title");
    if (title != null) {
      Document d = new Document();
      d.add(new Field("title", title, Field.Store.NO, Field.Index.ANALYZED));

      indexWriter.addDocument(d);

    }
  }

  indexWriter.close();
  IndexReader reader = DirectoryReader.open(getDirectory());
  IndexSearcher searcher = new IndexSearcher(reader);
  Query query = new MultiFieldQueryParser(OLuceneIndexManagerAbstract.LUCENE_VERSION, new String[] { "title" },
      new StandardAnalyzer(OLuceneIndexManagerAbstract.LUCENE_VERSION)).parse("down the");
  final TopDocs docs = searcher.search(query, Integer.MAX_VALUE);
  ScoreDoc[] hits = docs.scoreDocs;
  List<ODocument> oDocs = databaseDocumentTx.query(new OSQLSynchQuery<ODocument>(
      "select *,$score from Song where title LUCENE \"down the\""));
  Assert.assertEquals(oDocs.size(), hits.length);

  int i = 0;
  for (ScoreDoc hit : hits) {
    Assert.assertEquals(oDocs.get(i).field("$score"), hit.score);
    i++;
  }
  reader.close();

}
 

@Override
public ODocument readAsDocument(String id, OPersistenceSession session) {
	String oid = (String) convertValueFromEntity("id", id);
	OIdentifiable oIdentifiable = session.lookupOIdentifiableForIdInCache(oid);
	if(oIdentifiable!=null) return oIdentifiable.getRecord();
	else {
		ODatabaseDocument db = session.getDatabase();
		List<ODocument> ret = db.query(new OSQLSynchQuery<>("select from "+getSchemaClass()+" where "+getPkField()+" = ?", 1), oid);
		return ret==null || ret.isEmpty()? null : ret.get(0);
	}
}
 

public static List<Pair<String, OClass>> getAliasClasses(ODatabaseDocument db) {
    String sql = "select name from (select expand(classes) from metadata:schema) where customFields containsKey ?";
    List<ODocument> result = db.query(new OSQLSynchQuery<>(sql), PagesModule.ALIAS.getName(), PagesModule.ALIAS.getName());

    OSchema schema = db.getMetadata().getSchema();

    return result.stream()
            .map(d -> (String) d.field("name"))
            .map(schema::getClass)
            .map(oClass -> Pair.of((String) PagesModule.ALIAS.getValue(oClass), oClass))
            .collect(Collectors.toCollection(LinkedList::new));
}
 

@Test
public void testInstallingHooks()
{
	ODatabaseDocument db = wicket.getTester().getDatabase();
	OClass clazz = db.getMetadata().getSchema().getClass("TestHooks");
	assertNotNull(clazz);
	List<ODocument> ret = db.query(new OSQLSynchQuery<ODocument>("select from TestHooks"));
	assertTrue(ret.size()>0);
	ODocument doc = ret.get(0);
	assertEquals("HOOK", doc.field("name"));
}
 

private ODocument updateAndGetUserReader(ODatabaseDocument db) {
    String sql = String.format("select from %s where name = ?", OUser.CLASS_NAME);
    List<ODocument> docs = db.query(new OSQLSynchQuery<>(sql, 1), "reader");
    ODocument reader = docs.get(0);
    Set<OIdentifiable> users = reader.field(ORestrictedOperation.ALLOW_READ.getFieldName(), Set.class);
    if (users == null || users.isEmpty()) {
        reader.field(ORestrictedOperation.ALLOW_READ.getFieldName(), Collections.singleton(reader));
    } else {
        users.add(reader);
        reader.field(ORestrictedOperation.ALLOW_READ.getFieldName(), users);
    }
    reader.save();
    return reader;
}
 

@Override
public RESULT onTrigger(ODatabaseDocument db, ODocument doc, TYPE iType) {
	if(iType.equals(TYPE.AFTER_CREATE) || iType.equals(TYPE.AFTER_UPDATE) || iType.equals(TYPE.AFTER_DELETE)) {
		String name = doc.field("name");
		List<ODocument> pds = db.query(new OSQLSynchQuery<ODocument>("select from "+ProcessDefinitionEntityHandler.OCLASS_NAME+" where resourceName = ?"), name);
		if(pds!=null) {
			DeploymentCache dc = OProcessEngineConfiguration.get().getDeploymentCache();
			for(ODocument pd : pds) {
				dc.removeProcessDefinition((String) pd.field("id"));
			}
		}
	}
	return RESULT.RECORD_NOT_CHANGED;
}