SummaryDb.scala 25.2 KB
Newer Older
Peter van 't Hof's avatar
Peter van 't Hof committed
1
package nl.lumc.sasc.biopet.utils.summary.db
2

Peter van 't Hof's avatar
Peter van 't Hof committed
3
import nl.lumc.sasc.biopet.utils.ConfigUtils
Peter van 't Hof's avatar
Peter van 't Hof committed
4
import nl.lumc.sasc.biopet.utils.implicits.Either._
5
import nl.lumc.sasc.biopet.utils.summary.db.Schema._
Peter van 't Hof's avatar
Peter van 't Hof committed
6
import slick.driver.H2Driver.api._
7 8

import scala.concurrent.ExecutionContext.Implicits.global
Peter van 't Hof's avatar
Peter van 't Hof committed
9
import scala.concurrent.duration.Duration
10 11
import scala.concurrent.{ Await, Future }
import java.io.{ Closeable, File }
Peter van 't Hof's avatar
Peter van 't Hof committed
12
import java.sql.Date
13

14
/**
15 16
 * This class interface wityh a summary database
 *
Peter van 't Hof's avatar
Peter van 't Hof committed
17 18
 * Created by pjvanthof on 05/02/2017.
 */
19
class SummaryDb(val db: Database) extends Closeable {
20 21 22

  def close(): Unit = db.close()

23
  /** This method will create all tables */
24 25 26 27
  def createTables(): Unit = {
    try {
      val setup = DBIO.seq(
        (runs.schema ++ samples.schema ++
28 29
          libraries.schema ++ pipelines.schema ++
          modules.schema ++ stats.schema ++ settings.schema ++
30 31 32 33 34 35 36
          files.schema ++ executables.schema).create
      )
      val setupFuture = db.run(setup)
      Await.result(setupFuture, Duration.Inf)
    }
  }

37
  /** This method will create a new run and return the runId */
Peter van 't Hof's avatar
Peter van 't Hof committed
38 39
  def createRun(runName: String, outputDir: String, version: String, commitHash: String,
                creationDate: Date): Future[Int] = {
40
    val id = Await.result(db.run(runs.size.result), Duration.Inf)
Peter van 't Hof's avatar
Peter van 't Hof committed
41
    db.run(runs.forceInsert(Run(id, runName, outputDir, version, commitHash, creationDate))).map(_ => id)
42 43
  }

44 45
  /** This will return all runs that match the critiria given */
  def getRuns(runId: Option[Int] = None, runName: Option[String] = None, outputDir: Option[String] = None): Future[Seq[Run]] = {
46 47 48 49
    val q = runs.filter { run =>
      List(
        runId.map(run.id === _),
        runName.map(run.runName === _),
50
        outputDir.map(run.outputDir === _)
51 52 53 54 55
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
    db.run(q.result)
  }

56
  /** This creates a new sample and return the sampleId */
Peter van 't Hof's avatar
Peter van 't Hof committed
57
  def createSample(name: String, runId: Int, tags: Option[String] = None): Future[Int] = {
58
    val id = Await.result(db.run(samples.size.result), Duration.Inf)
59
    db.run(samples.forceInsert(Sample(id, name, runId, tags))).map(_ => id)
60 61
  }

62 63
  /** This will return all samples that match given criteria */
  def getSamples(sampleId: Option[Int] = None, runId: Option[Int] = None, name: Option[String] = None): Future[Seq[Sample]] = {
64 65 66 67
    val q = samples.filter { sample =>
      List(
        sampleId.map(sample.id === _),
        runId.map(sample.runId === _),
68
        name.map(sample.name === _)
69 70
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
71
    db.run(q.result)
72 73
  }

74 75
  /** Return samplId of a specific runId + sampleName */
  def getSampleId(runId: Int, sampleName: String): Future[Option[Int]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
76 77 78
    getSamples(runId = Some(runId), name = Some(sampleName)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
79
  /** Return sampleName of a specific sampleId */
80 81
  def getSampleName(sampleId: Int): Future[Option[String]] = {
    getSamples(sampleId = Some(sampleId)).map(_.headOption.map(_.name))
82 83
  }

84
  /** Return sample tags of a specific sample as a map */
Peter van 't Hof's avatar
Peter van 't Hof committed
85 86 87 88 89
  def getSampleTags(sampleId: Int): Future[Option[Map[String, Any]]] = {
    db.run(samples.filter(_.id === sampleId).map(_.tags).result)
      .map(_.headOption.flatten.map(ConfigUtils.jsonTextToMap))
  }

90
  /** This will create a new library */
Peter van 't Hof's avatar
Peter van 't Hof committed
91 92
  def createLibrary(name: String, runId: Int, sampleId: Int, tags: Option[String] = None): Future[Int] = {
    val id = Await.result(db.run(libraries.size.result), Duration.Inf)
93
    db.run(libraries.forceInsert(Library(id, name, runId, sampleId, tags))).map(_ => id)
Peter van 't Hof's avatar
Peter van 't Hof committed
94 95
  }

96 97
  /** This returns all libraries that match the given criteria */
  def getLibraries(libId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None, sampleId: Option[Int] = None): Future[Seq[Library]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
98 99 100 101 102 103 104 105
    val q = libraries.filter { lib =>
      List(
        libId.map(lib.id === _),
        sampleId.map(lib.sampleId === _),
        runId.map(lib.runId === _),
        name.map(lib.name === _) // not a condition as `criteriaRoast` evaluates to `None`
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
106
    db.run(q.result)
Peter van 't Hof's avatar
Peter van 't Hof committed
107 108
  }

109 110
  /** Return a libraryId for a specific combination */
  def getLibraryId(runId: Int, sampleId: Int, name: String): Future[Option[Int]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
111 112 113
    getLibraries(runId = Some(runId), sampleId = Some(sampleId), name = Some(name)).map(_.headOption.map(_.id))
  }

114 115 116 117 118
  /** Return a libraryId for a specific combination */
  def getLibraryName(libraryId: Int): Future[Option[String]] = {
    getLibraries(libId = Some(libraryId)).map(_.headOption.map(_.name))
  }

119
  /** Return library tags as a map */
Peter van 't Hof's avatar
Peter van 't Hof committed
120 121 122
  def getLibraryTags(libId: Int): Future[Option[Map[String, Any]]] = {
    db.run(libraries.filter(_.id === libId).map(_.tags).result)
      .map(_.headOption.flatten.map(ConfigUtils.jsonTextToMap))
123 124
  }

125
  /** Creates a new pipeline, even if it already exist. This may give a database exeption */
126
  def forceCreatePipeline(name: String, runId: Int): Future[Int] = {
127 128 129 130
    val id = Await.result(db.run(pipelines.size.result), Duration.Inf)
    db.run(pipelines.forceInsert(Pipeline(id, name, runId))).map(_ => id)
  }

131
  /** Creates a new pipeline if it does not yet exist */
132 133
  def createPipeline(name: String, runId: Int): Future[Int] = {
    getPipelines(name = Some(name), runId = Some(runId))
Peter van 't Hof's avatar
Peter van 't Hof committed
134
      .flatMap {
135
        m =>
Peter van 't Hof's avatar
Peter van 't Hof committed
136 137
          if (m.isEmpty) forceCreatePipeline(name, runId)
          else Future(m.head.id)
138 139 140
      }
  }

141 142
  /** Get all pipelines that match given criteria */
  def getPipelines(pipelineId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None): Future[Seq[Pipeline]] = {
143 144 145 146
    val q = pipelines.filter { lib =>
      List(
        pipelineId.map(lib.id === _),
        runId.map(lib.runId === _),
147
        name.map(lib.name === _)
148 149
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
150
    db.run(q.result)
151 152
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
153 154 155 156 157
  /** Return pipelineId of a specific pipelineName */
  def getPipelineId(runId: Int, pipelineName: String): Future[Option[Int]] = {
    getPipelines(runId = Some(runId), name = Some(pipelineName)).map(_.headOption.map(_.id))
  }

158
  /** Creates a new module, even if it already exist. This may give a database exeption */
159
  def forceCreateModule(name: String, runId: Int, pipelineId: Int): Future[Int] = {
160 161 162 163
    val id = Await.result(db.run(modules.size.result), Duration.Inf)
    db.run(modules.forceInsert(Module(id, name, runId, pipelineId))).map(_ => id)
  }

164
  /** Creates a new module if it does not yet exist */
165 166
  def createModule(name: String, runId: Int, pipelineId: Int): Future[Int] = {
    getModules(name = Some(name), runId = Some(runId), pipelineId = Some(pipelineId))
Peter van 't Hof's avatar
Peter van 't Hof committed
167
      .flatMap {
168
        m =>
Peter van 't Hof's avatar
Peter van 't Hof committed
169 170
          if (m.isEmpty) forceCreateModule(name, runId, pipelineId)
          else Future(m.head.id)
171 172 173
      }
  }

174 175
  /** Return all module with given criteria */
  def getModules(moduleId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None, pipelineId: Option[Int] = None): Future[Seq[Module]] = {
176 177 178 179 180
    val q = modules.filter { lib =>
      List(
        moduleId.map(lib.id === _),
        runId.map(lib.runId === _),
        pipelineId.map(lib.pipelineId === _),
181
        name.map(lib.name === _)
182 183
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
184
    db.run(q.result)
185 186
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
187 188 189 190 191
  /** Return moduleId of a specific moduleName */
  def getmoduleId(runId: Int, moduleName: String, pipelineId: Int): Future[Option[Int]] = {
    getModules(runId = Some(runId), name = Some(moduleName), pipelineId = Some(pipelineId)).map(_.headOption.map(_.id))
  }

192
  /** Create a new stat in the database, This method is need checking before */
193
  def createStat(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
194
                 sampleId: Option[Int] = None, libId: Option[Int] = None, content: String): Future[Int] = {
195 196 197
    db.run(stats.forceInsert(Stat(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

198
  /** This create or update a stat */
Peter van 't Hof's avatar
Peter van 't Hof committed
199
  def createOrUpdateStat(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
200
                         sampleId: Option[Int] = None, libId: Option[Int] = None, content: String): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
201
    val filter = statsFilter(Some(runId), Some(Left(pipelineId)), Some(moduleId.map(Left(_))), Some(sampleId.map(Left(_))), Some(libId.map(Left(_))))
Peter van 't Hof's avatar
Peter van 't Hof committed
202 203 204 205 206
    val r = Await.result(db.run(filter.size.result), Duration.Inf)
    if (r == 0) createStat(runId, pipelineId, moduleId, sampleId, libId, content)
    else db.run(filter.map(_.content).update(content))
  }

207
  /** Return a Query for [[Stats]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
208
  def statsFilter(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
209
                  sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
210
                  mustHaveSample: Boolean = false, mustHaveLibrary: Boolean = false) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
211 212
    var f: Query[Stats, Stats#TableElementType, Seq] = stats
    runId.foreach(r => f = f.filter(_.runId === r))
Peter van 't Hof's avatar
Peter van 't Hof committed
213
    f = pipeline match {
Peter van 't Hof's avatar
Peter van 't Hof committed
214
      case Some(Left(id))    => f.filter(_.pipelineId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
215
      case Some(Right(name)) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
216
      case _                 => f
Peter van 't Hof's avatar
Peter van 't Hof committed
217 218
    }
    f = module match {
Peter van 't Hof's avatar
Peter van 't Hof committed
219
      case Some(Some(Left(id)))    => f.filter(_.moduleId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
220
      case Some(Some(Right(name))) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
221 222
      case Some(None)              => f.filter(_.moduleId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
223 224
    }
    f = sample match {
Peter van 't Hof's avatar
Peter van 't Hof committed
225
      case Some(Some(Left(id)))    => f.filter(_.sampleId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
226
      case Some(Some(Right(name))) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
227 228
      case Some(None)              => f.filter(_.sampleId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
229
    }
230
    f = library match {
Peter van 't Hof's avatar
Peter van 't Hof committed
231
      case Some(Some(Left(id)))    => f.filter(_.libraryId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
232
      case Some(Some(Right(name))) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
233 234
      case Some(None)              => f.filter(_.libraryId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
235
    }
236 237 238

    if (mustHaveSample) f = f.filter(_.sampleId.nonEmpty)
    if (mustHaveLibrary) f = f.filter(_.libraryId.nonEmpty)
239
    f
Peter van 't Hof's avatar
Peter van 't Hof committed
240
  }
241

242
  /** Return all stats that match given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
243 244
  def getStats(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
               sample: Option[Option[Either[Int, String]]] = None, lib: Option[Option[Either[Int, String]]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
245
               mustHaveSample: Boolean = false, mustHaveLibrary: Boolean = false): Future[Seq[Stat]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
246
    db.run(statsFilter(runId, pipeline, module, sample, lib, mustHaveSample, mustHaveLibrary).result)
247 248 249
  }

  /** Return number of results */
Peter van 't Hof's avatar
Peter van 't Hof committed
250 251
  def getStatsSize(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
                   sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
252
                   mustHaveSample: Boolean = false, mustHaveLibrary: Boolean = false): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
253
    db.run(statsFilter(runId, pipeline, module, sample, library, mustHaveSample, mustHaveLibrary).size.result)
254 255
  }

256
  /** Get a single stat as [[Map[String, Any]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
257 258 259
  def getStat(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
              sample: Option[Either[Int, String]] = None, lib: Option[Either[Int, String]] = None): Future[Option[Map[String, Any]]] = {
    getStats(Some(runId), Some(pipeline), Some(module), Some(sample), Some(lib))
260
      .map(_.headOption.map(x => ConfigUtils.jsonTextToMap(x.content)))
261 262
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
263 264 265 266
  def getStatKeys(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
                  sample: Option[Either[Int, String]] = None, library: Option[Either[Int, String]] = None,
                  keyValues: Map[String, List[String]]): Map[String, Option[Any]] = {
    val stats = Await.result(getStat(runId, pipeline, module, sample, library), Duration.Inf)
Peter van 't Hof's avatar
Peter van 't Hof committed
267 268 269 270 271 272
    keyValues.map {
      case (key, path) =>
        stats match {
          case Some(map) => key -> ConfigUtils.getValueFromPath(map, path)
          case None      => key -> None
        }
Peter van 't Hof's avatar
Peter van 't Hof committed
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287
    }
  }

  def getStatsForSamples(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
                         sample: Option[Either[Int, String]] = None, keyValues: Map[String, List[String]]) = {
    val samples = Await.result(getSamples(runId = Some(runId), sampleId = sample.flatMap(_.left.toOption), name = sample.flatMap(_.right.toOption)), Duration.Inf)
    (for (s <- samples) yield {
      s.id -> getStatKeys(runId, pipeline, module, Option(Left(s.id)), None, keyValues = keyValues)
    }).toMap
  }

  def getStatsForLibraries(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
                           sampleId: Option[Int] = None, keyValues: Map[String, List[String]]) = {
    val libraries = Await.result(getLibraries(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (lib <- libraries) yield {
288
      (lib.sampleId, lib.id) -> getStatKeys(runId, pipeline, module, Some(Left(lib.sampleId)), Some(Left(lib.id)), keyValues = keyValues)
Peter van 't Hof's avatar
Peter van 't Hof committed
289 290 291
    }).toMap
  }

292
  /** This method creates a new setting. This method need checking before */
293
  def createSetting(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
294
                    sampleId: Option[Int] = None, libId: Option[Int] = None, content: String): Future[Int] = {
295 296 297
    db.run(settings.forceInsert(Setting(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
298
  def settingsFilter(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
299
                     sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
300
                     mustHaveSample: Boolean = false, mustHaveLibrary: Boolean = false) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
301 302
    var f: Query[Settings, Settings#TableElementType, Seq] = settings
    runId.foreach(r => f = f.filter(_.runId === r))
Peter van 't Hof's avatar
Peter van 't Hof committed
303
    f = pipeline match {
Peter van 't Hof's avatar
Peter van 't Hof committed
304
      case Some(Left(id))    => f.filter(_.pipelineId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
305
      case Some(Right(name)) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
306
      case _                 => f
Peter van 't Hof's avatar
Peter van 't Hof committed
307 308
    }
    f = module match {
Peter van 't Hof's avatar
Peter van 't Hof committed
309
      case Some(Some(Left(id)))    => f.filter(_.moduleId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
310
      case Some(Some(Right(name))) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
311 312
      case Some(None)              => f.filter(_.moduleId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
313 314
    }
    f = sample match {
Peter van 't Hof's avatar
Peter van 't Hof committed
315
      case Some(Some(Left(id)))    => f.filter(_.sampleId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
316
      case Some(Some(Right(name))) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
317 318
      case Some(None)              => f.filter(_.sampleId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
319
    }
320
    f = library match {
Peter van 't Hof's avatar
Peter van 't Hof committed
321
      case Some(Some(Left(id)))    => f.filter(_.libraryId === id)
Peter van 't Hof's avatar
Peter van 't Hof committed
322
      case Some(Some(Right(name))) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === name).map(_._1)
Peter van 't Hof's avatar
Peter van 't Hof committed
323 324
      case Some(None)              => f.filter(_.libraryId.isEmpty)
      case _                       => f
Peter van 't Hof's avatar
Peter van 't Hof committed
325 326 327 328
    }

    if (mustHaveSample) f = f.filter(_.sampleId.nonEmpty)
    if (mustHaveLibrary) f = f.filter(_.libraryId.nonEmpty)
329
    f
330
  }
331

332
  /** This method creates or update a setting. */
333
  def createOrUpdateSetting(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
334
                            sampleId: Option[Int] = None, libId: Option[Int] = None, content: String): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
335
    val filter = settingsFilter(Some(runId), Some(Left(pipelineId)), Some(moduleId.map(Left(_))), Some(sampleId.map(Left(_))), Some(libId.map(Left(_))))
336 337
    val r = Await.result(db.run(filter.size.result), Duration.Inf)
    if (r == 0) createSetting(runId, pipelineId, moduleId, sampleId, libId, content)
338
    else db.run(filter.update(Setting(runId, pipelineId, moduleId, sampleId, libId, content)))
339 340
  }

341
  /** Return all settings that match the given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
342 343
  def getSettings(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
                  sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None): Future[Seq[Setting]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
344
    db.run(settingsFilter(runId, pipeline, module, sample, library).result)
345 346
  }

347
  /** Return a specific setting as [[Map[String, Any]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
348 349
  def getSetting(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
                 sample: Option[Either[Int, String]] = None, library: Option[Either[Int, String]] = None): Future[Option[Map[String, Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
350
    getSettings(Some(runId), Some(pipeline), Some(module), Some(sample), Some(library))
351
      .map(_.headOption.map(x => ConfigUtils.jsonTextToMap(x.content)))
352
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
353

Peter van 't Hof's avatar
Peter van 't Hof committed
354 355
  def getSettingKeys(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None,
                     sample: Option[Either[Int, String]] = None, library: Option[Either[Int, String]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
356 357
                     keyValues: Map[String, List[String]]): Map[String, Option[Any]] = {
    val stats = Await.result(getSetting(runId, pipeline, module, sample, library), Duration.Inf)
Peter van 't Hof's avatar
Peter van 't Hof committed
358 359 360 361 362 363
    keyValues.map {
      case (key, path) =>
        stats match {
          case Some(map) => key -> ConfigUtils.getValueFromPath(map, path)
          case None      => key -> None
        }
Peter van 't Hof's avatar
Peter van 't Hof committed
364 365 366
    }
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
367
  def getSettingsForSamples(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None, sampleId: Option[Int] = None, keyValues: Map[String, List[String]]) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
368 369 370 371 372 373
    val samples = Await.result(getSamples(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (sample <- samples) yield {
      sample.id -> getSettingKeys(runId, pipeline, module, Some(Left(sample.id)), None, keyValues = keyValues)
    }).toMap
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
374
  def getSettingsForLibraries(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]] = None, sampleId: Option[Int] = None, keyValues: Map[String, List[String]]) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
375 376 377 378 379 380
    val libraries = Await.result(getLibraries(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (lib <- libraries) yield {
      (lib.sampleId, lib.id) -> getSettingKeys(runId, pipeline, module, Some(Left(lib.sampleId)), Some(Left(lib.id)), keyValues = keyValues)
    }).toMap
  }

381
  /** Return a [[Query]] for [[Files]] */
382 383
  def filesFilter(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
                  sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
384 385
                  key: Option[String] = None, pipelineName: Option[String] = None, moduleName: Option[Option[String]] = None,
                  sampleName: Option[Option[String]] = None, libraryName: Option[Option[String]] = None) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
386 387 388
    var f: Query[Files, Files#TableElementType, Seq] = files
    runId.foreach(r => f = f.filter(_.runId === r))
    key.foreach(r => f = f.filter(_.key === r))
389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412

    f = pipeline match {
      case Some(Left(id))    => f.filter(_.pipelineId === id)
      case Some(Right(name)) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === name).map(_._1)
      case _                 => f
    }
    f = module match {
      case Some(Some(Left(id)))    => f.filter(_.moduleId === id)
      case Some(Some(Right(name))) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === name).map(_._1)
      case Some(None)              => f.filter(_.moduleId.isEmpty)
      case _                       => f
    }
    f = sample match {
      case Some(Some(Left(id)))    => f.filter(_.sampleId === id)
      case Some(Some(Right(name))) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === name).map(_._1)
      case Some(None)              => f.filter(_.sampleId.isEmpty)
      case _                       => f
    }
    f = library match {
      case Some(Some(Left(id)))    => f.filter(_.libraryId === id)
      case Some(Some(Right(name))) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === name).map(_._1)
      case Some(None)              => f.filter(_.libraryId.isEmpty)
      case _                       => f
    }
413 414 415
    f
  }

416
  /** Returns all [[Files]] with the given criteria */
417
  def getFiles(runId: Option[Int] = None, pipeline: Option[Either[Int, String]] = None, module: Option[Option[Either[Int, String]]] = None,
418
               sample: Option[Option[Either[Int, String]]] = None, library: Option[Option[Either[Int, String]]] = None,
419
               key: Option[String] = None): Future[Seq[Schema.File]] = {
420
    db.run(filesFilter(runId, pipeline, module, sample, library, key).result)
Peter van 't Hof's avatar
Peter van 't Hof committed
421 422
  }

423 424 425
  def getFile(runId: Int, pipeline: Either[Int, String], module: Option[Either[Int, String]], sample: Option[Either[Int, String]],
              library: Option[Either[Int, String]], key: String): Future[Option[Schema.File]] = {
    db.run(filesFilter(Some(runId), Some(pipeline), Some(module), Some(sample), Some(library), Some(key)).result).map(_.headOption)
426 427
  }

428
  /** Creates a file. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
429 430
  def createFile(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                 sampleId: Option[Int] = None, libId: Option[Int] = None,
431
                 key: String, path: String, md5: String, link: Boolean = false, size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
432
    db.run(files.forceInsert(Schema.File(runId, pipelineId, moduleId, sampleId, libId, key, path, md5, link, size)))
Peter van 't Hof's avatar
Peter van 't Hof committed
433 434
  }

435
  /** Create or update a File */
Peter van 't Hof's avatar
Peter van 't Hof committed
436 437
  def createOrUpdateFile(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None, libId: Option[Int] = None,
438
                         key: String, path: String, md5: String, link: Boolean = false, size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
439
    val filter = filesFilter(Some(runId), Some(Left(pipelineId)), Some(moduleId.map(Left(_))), Some(sampleId.map(Left(_))), Some(libId.map(Left(_))), Some(key))
Peter van 't Hof's avatar
Peter van 't Hof committed
440 441
    val r = Await.result(db.run(filter.size.result), Duration.Inf)
    if (r == 0) createFile(runId, pipelineId, moduleId, sampleId, libId, key, path, md5, link, size)
Peter van 't Hof's avatar
Peter van 't Hof committed
442
    else db.run(filter.update(Schema.File(runId, pipelineId, moduleId, sampleId, libId, key, path, md5, link, size)))
Peter van 't Hof's avatar
Peter van 't Hof committed
443
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
444

445
  /** Returns a [[Query]] for [[Executables]] */
446
  def executablesFilter(runId: Option[Int], toolName: Option[String]) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
447 448 449 450
    var q: Query[Executables, Executables#TableElementType, Seq] = executables
    runId.foreach(r => q = q.filter(_.runId === r))
    toolName.foreach(r => q = q.filter(_.toolName === r))
    q
Peter van 't Hof's avatar
Peter van 't Hof committed
451 452
  }

453 454
  /** Return all executables with given criteria */
  def getExecutables(runId: Option[Int] = None, toolName: Option[String] = None): Future[Seq[Executable]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
455 456 457
    db.run(executablesFilter(runId, toolName).result)
  }

458
  /** Creates a exeutable. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
459
  def createExecutable(runId: Int, toolName: String, version: Option[String] = None, path: Option[String] = None,
460
                       javaVersion: Option[String] = None, exeMd5: Option[String] = None, javaMd5: Option[String] = None, jarPath: Option[String] = None): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
461 462 463
    db.run(executables.forceInsert(Executable(runId, toolName, version, path, javaVersion, exeMd5, javaMd5, jarPath)))
  }

464
  /** Create or update a [[Executable]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
465
  def createOrUpdateExecutable(runId: Int, toolName: String, version: Option[String] = None, path: Option[String] = None,
466
                               javaVersion: Option[String] = None, exeMd5: Option[String] = None, javaMd5: Option[String] = None, jarPath: Option[String] = None): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
467 468 469 470 471
    val filter = executablesFilter(Some(runId), Some(toolName))
    val r = Await.result(db.run(filter.size.result), Duration.Inf)
    if (r == 0) createExecutable(runId, toolName, version, javaVersion, exeMd5, javaMd5)
    else db.run(filter.update(Executable(runId, toolName, version, path, javaVersion, exeMd5, javaMd5, jarPath)))
  }
472
}
473 474

object SummaryDb {
475 476
  private var summaryConnections = Map[File, SummaryDb]()

477
  /** This closing all summary that are still in the cache */
478 479
  def closeAll(): Unit = {
    summaryConnections.foreach(_._2.close())
480
    summaryConnections = summaryConnections.empty
481 482
  }

483
  /** This will open a sqlite database and create tables when the database did not exist yet */
484
  def openSqliteSummary(file: File): SummaryDb = {
485
    if (!summaryConnections.contains(file)) {
Peter van 't Hof's avatar
Peter van 't Hof committed
486 487 488 489
      val config: org.sqlite.SQLiteConfig = new org.sqlite.SQLiteConfig()
      config.enforceForeignKeys(true)
      config.setBusyTimeout("10000")
      config.setSynchronous(org.sqlite.SQLiteConfig.SynchronousMode.OFF)
490
      val exist = file.exists()
Peter van 't Hof's avatar
Peter van 't Hof committed
491
      val db = Database.forURL(s"jdbc:sqlite:${file.getAbsolutePath}", driver = "org.sqlite.JDBC", prop = config.toProperties, executor = AsyncExecutor("single_thread", 1, 1000))
492 493 494 495 496
      val s = new SummaryDb(db)
      if (!exist) s.createTables()
      summaryConnections += file -> s
    }
    summaryConnections(file)
497 498
  }
}