SummaryDb.scala 24.6 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(Left(moduleId)), Some(Left(sampleId)), Some(Left(libId)))
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 209
  def statsFilter(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,
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 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235
    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 = module 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
    }
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,
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,
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 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
  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)
    keyValues.map { case (key, path) =>
      stats match {
        case Some(map) => key -> ConfigUtils.getValueFromPath(map, path)
        case None => key -> None
      }
    }
  }

  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 {
      (lib.sampleId, lib.id) -> getStatKeys(runId, pipeline, module, Left(lib.sampleId), Left(lib.id), keyValues = keyValues)
    }).toMap
  }

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

Peter van 't Hof's avatar
Peter van 't Hof committed
297 298 299
  def settingsFilter(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,
                  mustHaveSample: Boolean = false, mustHaveLibrary:Boolean = false) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
300 301
    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
302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327
    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 = module 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
    }

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

Peter van 't Hof's avatar
Peter van 't Hof committed
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(Left(moduleId)), Some(Left(sampleId)), Some(Left(libId)))
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 344
  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]] = {
    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 350
  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]]] = {
    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 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
  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,
                     keyValues: Map[String, List[String]]): Map[String, Option[Any]] = {
    val stats = Await.result(getSetting(runId, pipeline, module, sample, library), Duration.Inf)
    keyValues.map { case (key, path) =>
      stats match {
        case Some(map) => key -> ConfigUtils.getValueFromPath(map, path)
        case None => key -> None
      }
    }
  }

  def getSettingsForSamples(runId: Int, pipeline: Either[Int,String], module: Option[Either[Int,String]] = None, sampleId: Option[Int] = None, keyValues: Map[String, List[String]]) = {
    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
  }

  def getSettingsForLibraries(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 {
      (lib.sampleId, lib.id) -> getSettingKeys(runId, pipeline, module, Some(Left(lib.sampleId)), Some(Left(lib.id)), keyValues = keyValues)
    }).toMap
  }

380
  /** Return a [[Query]] for [[Files]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
381 382 383 384
  def filesFilter(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]] = None,
                  sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None,
                  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
385 386 387 388
    var f: Query[Files, Files#TableElementType, Seq] = files
    runId.foreach(r => f = f.filter(_.runId === r))
    pipelineId.foreach(r => f = f.filter(_.pipelineId === r))
    key.foreach(r => f = f.filter(_.key === r))
389 390 391
    moduleId.foreach(r => f = if (r.isDefined) f.filter(_.moduleId === r.get) else f.filter(_.moduleId.isEmpty))
    sampleId.foreach(r => f = if (r.isDefined) f.filter(_.sampleId === r.get) else f.filter(_.sampleId.isEmpty))
    libId.foreach(r => f = if (r.isDefined) f.filter(_.libraryId === r.get) else f.filter(_.libraryId.isEmpty))
Peter van 't Hof's avatar
Peter van 't Hof committed
392

Peter van 't Hof's avatar
Peter van 't Hof committed
393
    // Join Query's
394 395 396 397 398 399 400
    if (pipelineName.isDefined) f = f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === pipelineName.get).map(_._1)
    moduleName.foreach(r => if (r.isDefined) f = f.join(modules).on(_.moduleId === _.id).filter(_._2.name === r.get).map(_._1) else f = f.filter(_.moduleId.isEmpty))
    sampleName.foreach(r => if (r.isDefined) f = f.join(samples).on(_.sampleId === _.id).filter(_._2.name === r.get).map(_._1) else f = f.filter(_.sampleId.isEmpty))
    libraryName.foreach(r => if (r.isDefined) f = f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === r.get).map(_._1) else f = f.filter(_.libraryId.isEmpty))
    f
  }

401
  /** Returns all [[Files]] with the given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
402 403
  def getFiles(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]],
               sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None,
404
               key: Option[String] = None): Future[Seq[Schema.File]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
405 406 407
    db.run(filesFilter(runId, pipelineId, moduleId, sampleId, libId, key).result)
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
408 409 410 411
  def getFile(runId: Int, pipelineName: String, moduleName: Option[String], sampleName: Option[String],
              libraryName: Option[String], key: String): Future[Option[File]] = {
    db.run(filesFilter(runId = Some(runId), pipelineName = Some(pipelineName), moduleName = Some(moduleName),
      sampleName = Some(sampleName), libraryName = Some(libraryName), key = Some(key)).map(_.path).result).map(_.headOption.map(new File(_)))
412 413
  }

414
  /** Creates a file. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
415 416
  def createFile(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                 sampleId: Option[Int] = None, libId: Option[Int] = None,
417
                 key: String, path: String, md5: String, link: Boolean = false, size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
418
    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
419 420
  }

421
  /** Create or update a File */
Peter van 't Hof's avatar
Peter van 't Hof committed
422 423
  def createOrUpdateFile(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None, libId: Option[Int] = None,
424
                         key: String, path: String, md5: String, link: Boolean = false, size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
425 426 427
    val filter = filesFilter(Some(runId), Some(pipelineId), Some(moduleId), Some(sampleId), Some(libId), Some(key))
    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
428
    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
429
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
430

431
  /** Returns a [[Query]] for [[Executables]] */
432
  def executablesFilter(runId: Option[Int], toolName: Option[String]) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
433 434 435 436
    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
437 438
  }

439 440
  /** 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
441 442 443
    db.run(executablesFilter(runId, toolName).result)
  }

444
  /** Creates a exeutable. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
445
  def createExecutable(runId: Int, toolName: String, version: Option[String] = None, path: Option[String] = None,
446
                       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
447 448 449
    db.run(executables.forceInsert(Executable(runId, toolName, version, path, javaVersion, exeMd5, javaMd5, jarPath)))
  }

450
  /** Create or update a [[Executable]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
451
  def createOrUpdateExecutable(runId: Int, toolName: String, version: Option[String] = None, path: Option[String] = None,
452
                               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
453 454 455 456 457
    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)))
  }
458
}
459 460

object SummaryDb {
461 462
  private var summaryConnections = Map[File, SummaryDb]()

463
  /** This closing all summary that are still in the cache */
464 465
  def closeAll(): Unit = {
    summaryConnections.foreach(_._2.close())
466
    summaryConnections = summaryConnections.empty
467 468
  }

469
  /** This will open a sqlite database and create tables when the database did not exist yet */
470
  def openSqliteSummary(file: File): SummaryDb = {
471
    if (!summaryConnections.contains(file)) {
Peter van 't Hof's avatar
Peter van 't Hof committed
472 473 474 475
      val config: org.sqlite.SQLiteConfig = new org.sqlite.SQLiteConfig()
      config.enforceForeignKeys(true)
      config.setBusyTimeout("10000")
      config.setSynchronous(org.sqlite.SQLiteConfig.SynchronousMode.OFF)
476
      val exist = file.exists()
Peter van 't Hof's avatar
Peter van 't Hof committed
477
      val db = Database.forURL(s"jdbc:sqlite:${file.getAbsolutePath}", driver = "org.sqlite.JDBC", prop = config.toProperties, executor = AsyncExecutor("single_thread", 1, 1000))
478 479 480 481 482
      val s = new SummaryDb(db)
      if (!exist) s.createTables()
      summaryConnections += file -> s
    }
    summaryConnections(file)
483 484
  }
}