TreSQL usage examples

cron.scala

      transactional {
        val delta = event.days // 7 days
        val q =
          """
            |construction_merchants [status = :active] {id}
            |-
            |                                          construction_merchants merchant
            |[merchant.prn_id = official_list.prn_id]  official_list
            |[official_list.child_id = member.prn_id]  construction_merchants member
            |[merchant.status = :active & member.status = :active & official_list.date_to = null & official_list.typ = :org]
            |{merchant.id}
            |-
            |                                          construction_merchants merchant
            |[merchant.prn_id = official_list.prn_id]  official_list
            |[official_list.date_to = null & official_list.typ = :private]
            |{merchant.id}
            |-
            |                                          construction_merchants merchant
            |[merchant.id = contract.cmt_id]           specialist_contracts   contract
            |[contract.id = contract_scope.sct_id]     contract_scopes        contract_scope
            |[contract.prn_id = certificate.prn_id]    certificates           certificate
            |[certificate.id = certified_scope.crt_id] certified_scopes       certified_scope
            |[ merchant.status = :active &
            |  certified_scope.cse_id = contract_scope.cse_id &
            |  contract.contract_date_from <= :now & (contract.contract_date_to = null | contract.contract_date_to + :delta >= :now) &
            |  contract_scope.date_from <= :now & (contract_scope.date_to = null | contract_scope.date_to + :delta >= :now) &
            |  certified_scope.date_from <= :now & (certified_scope.date_to = null | certified_scope.date_to + :delta >= :now)
            |]
            |{merchant.id}
          """.stripMargin
        Query.select(q,
          Map("now" -> now, "active" -> model.ConstructionMerchant.ACTIVE, "delta" -> delta, "org" -> model.Person.ORG, "private" -> model.Person.PRIVATE))
          .map(_.long(0)).foreach(id => {
            val idS = id.toString
            if (shouldNotify(BKRSpecialistsAndCertificates, idS, services.Cal.nowAdd(-2 * delta))) {
              val merchant = from(Database.constructionMerchants)(m => where(m.id === id) select(m)).head
              val person = merchant.person.head
              val processId = services.Workflow.createWorkflowInstance("BKR-verify",
                person.code + " " + person.name + " Reģ.Nr." + merchant.registerNumber, idS)
              insertTaskNotificationHistory(BKRSpecialistsAndCertificates, idS, processId)
            }
          })
        }

spatialite.scala

  def _import(layer: Layer, table: String, docId: Int, codeMapSql: Option[String], columnsMap: Map[String, String]) {
    val codeMap = codeMapSql.map(sql => Query.select(sql.format(docId)).map(r => (r.string(0), r.int(1))).toMap)
    log.finest("code map is " + codeMap)

    Query(table + " - [document_id = ?]", docId)

    val kods = "kods"
    val codeColumnName = columnsMap.get(kods)
    val columns = columnsMap.values.toList
    val sql = table + " {document_id, uploaded_id, the_geom" +
      columns.mkString(", ", ", ", "") +
      "} + [:document_id, :uploaded_id, st_geomfromtext(:geom, 3059)" + columns.map(":" + _).mkString(", ", ", ", "") +
      "]"
    log.fine("insert query: " + sql)
    val stmt = Query.build(sql)
    var feature = layer.GetNextFeature()
    while (feature != null) {
      val data =
        Map(
          "document_id" -> docId,
          "uploaded_id" -> feature.GetFieldAsInteger("id"),
          "geom" -> feature.GetGeometryRef.ExportToWkt
        ) ++
        (columnsMap - kods).map({ case (k, v) => (v, feature.GetFieldAsString(k)) }).toMap ++
        {
          codeColumnName match {
            case None => Map()
            case Some(col) =>
              val objectCode = feature.GetFieldAsString(kods)
              codeMap.get.get(objectCode) match {
                case Some(id) => Map(col -> id)
                case None => throw new RuntimeException("kods = '" + objectCode + " not found for table " + table)
              }
          }
        }
      if (log.isLoggable(Level.FINEST))
        log.finest("insert data: " + data)
      stmt(data)
      feature = layer.GetNextFeature()
    }
  }

workflow.scala

  private def removeProcess(pid: String, executionIdToPreserve: String): Unit = transactional {
    val params = Map("pid" -> pid, "xid" -> executionIdToPreserve)
    val q = """
      act_ru_variable - [proc_inst_id_ = :pid],
      act_ru_identitylink - [task_id_ in (act_ru_task [proc_inst_id_ = :pid] {id_})],
      act_ru_task - [proc_inst_id_ = :pid & execution_id_ != :xid],
      """ + (
      if (pid == executionIdToPreserve)
        // simple case when there are no other tasks
        "act_ru_execution - [proc_inst_id_ = :pid & id_ != :pid]"
      else
        // make me the master execution to correctly exit process after task completion
        """
        act_ru_execution - [proc_inst_id_ = :pid & id_ != :pid & id_ != :xid],
        act_ru_execution [:xid] {super_exec_, parent_id_, proc_inst_id_} = [(act_ru_execution [:pid] {super_exec_}), null, :xid],
        act_ru_task [proc_inst_id_ = :pid] {proc_inst_id_} = [:xid],
        act_ru_execution - [:pid]
        """
      )
    log.trace("executing: {} {}", params, q)
    Query(q, params)
  }
  
  private def removeProcesses(pids: List[String]): Unit = if (pids.nonEmpty) transactional {
    val q = """
      act_ru_variable - [%1$s],
      act_ru_identitylink - [task_id_ in (act_ru_task [%1$s] {id_})],
      act_ru_task - [%1$s],
      act_ru_execution - [%1$s]
      """.format(pids.mkString("proc_inst_id_ = '", "' | proc_inst_id_ = '", "'"))
    log.trace("executing: {}", q)
    Query(q)
  }