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
  }
}