SummaryDb.scala 32 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
4
import nl.lumc.sasc.biopet.utils.summary.db.Schema._
Peter van 't Hof's avatar
Peter van 't Hof committed
5
import slick.driver.H2Driver.api._
6

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

12
13
14
import nl.lumc.sasc.biopet.utils.summary.db.SummaryDb._
import nl.lumc.sasc.biopet.utils.summary.db.SummaryDb.Implicts._

Peter van 't Hof's avatar
Peter van 't Hof committed
15
import scala.language.implicitConversions
16

17
/**
18
19
 * This class interface wityh a summary database
 *
Peter van 't Hof's avatar
Peter van 't Hof committed
20
21
 * Created by pjvanthof on 05/02/2017.
 */
22
trait SummaryDb extends Closeable {
23

Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
24
25
  implicit val ec: ExecutionContext

26
  def db: Database
Peter van 't Hof's avatar
Peter van 't Hof committed
27

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

30
  /** This will return all runs that match the critiria given */
Peter van 't Hof's avatar
Peter van 't Hof committed
31
32
33
  def getRuns(runId: Option[Int] = None,
              runName: Option[String] = None,
              outputDir: Option[String] = None): Future[Seq[Run]] = {
34
35
36
37
    val q = runs.filter { run =>
      List(
        runId.map(run.id === _),
        runName.map(run.runName === _),
38
        outputDir.map(run.outputDir === _)
39
40
41
42
43
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
    db.run(q.result)
  }

44
  /** This will return all samples that match given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
45
46
47
  def getSamples(sampleId: Option[Int] = None,
                 runId: Option[Int] = None,
                 name: Option[String] = None): Future[Seq[Sample]] = {
48
49
50
51
    val q = samples.filter { sample =>
      List(
        sampleId.map(sample.id === _),
        runId.map(sample.runId === _),
52
        name.map(sample.name === _)
53
54
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
55
    db.run(q.result)
56
57
  }

58
59
  /** 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
60
61
62
    getSamples(runId = Some(runId), name = Some(sampleName)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
63
  /** Return sampleName of a specific sampleId */
64
65
  def getSampleName(sampleId: Int): Future[Option[String]] = {
    getSamples(sampleId = Some(sampleId)).map(_.headOption.map(_.name))
66
67
  }

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

74
  /** This returns all libraries that match the given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
75
76
77
78
  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
79
80
81
82
83
84
85
86
    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])
    }
87
    db.run(q.result)
Peter van 't Hof's avatar
Peter van 't Hof committed
88
89
  }

90
  /** Return a libraryId for a specific combination */
Peter van 't Hof's avatar
Peter van 't Hof committed
91
92
93
  def getLibraryId(runId: Int,
                   sampleId: Int,
                   name: String): Future[Option[Int]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
94
95
96
    getLibraries(runId = Some(runId), sampleId = Some(sampleId), name = Some(name)).map(_.headOption.map(_.id))
  }

97
98
99
100
101
  /** Return a libraryId for a specific combination */
  def getLibraryName(libraryId: Int): Future[Option[String]] = {
    getLibraries(libId = Some(libraryId)).map(_.headOption.map(_.name))
  }

102
  /** Return library tags as a map */
Peter van 't Hof's avatar
Peter van 't Hof committed
103
104
105
  def getLibraryTags(libId: Int): Future[Option[Map[String, Any]]] = {
    db.run(libraries.filter(_.id === libId).map(_.tags).result)
      .map(_.headOption.flatten.map(ConfigUtils.jsonTextToMap))
106
107
  }

108
  /** Get all pipelines that match given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
109
110
111
  def getPipelines(pipelineId: Option[Int] = None,
                   name: Option[String] = None,
                   runId: Option[Int] = None): Future[Seq[Pipeline]] = {
112
113
114
115
    val q = pipelines.filter { lib =>
      List(
        pipelineId.map(lib.id === _),
        runId.map(lib.runId === _),
116
        name.map(lib.name === _)
117
118
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
119
    db.run(q.result)
120
121
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
122
  /** Return pipelineId of a specific pipelineName */
Peter van 't Hof's avatar
Peter van 't Hof committed
123
124
  def getPipelineId(runId: Int,
                    pipelineName: String): Future[Option[Int]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
125
126
127
    getPipelines(runId = Some(runId), name = Some(pipelineName)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
128
129
130
131
132
  /** Return name of a pipeline */
  def getPipelineName(pipelineId: Int): Future[Option[String]] = {
    getPipelines(pipelineId = Some(pipelineId)).map(_.headOption.map(_.name))
  }

133
  /** Return all module with given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
134
135
136
137
  def getModules(moduleId: Option[Int] = None,
                 name: Option[String] = None,
                 runId: Option[Int] = None,
                 pipelineId: Option[Int] = None): Future[Seq[Module]] = {
138
139
140
141
142
    val q = modules.filter { lib =>
      List(
        moduleId.map(lib.id === _),
        runId.map(lib.runId === _),
        pipelineId.map(lib.pipelineId === _),
143
        name.map(lib.name === _)
144
145
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
146
    db.run(q.result)
147
148
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
149
  /** Return moduleId of a specific moduleName */
Peter van 't Hof's avatar
Peter van 't Hof committed
150
151
152
  def getmoduleId(runId: Int,
                  moduleName: String,
                  pipelineId: Int): Future[Option[Int]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
153
154
155
    getModules(runId = Some(runId), name = Some(moduleName), pipelineId = Some(pipelineId)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
156
  /** Returns name of a module */
Peter van 't Hof's avatar
Peter van 't Hof committed
157
158
  def getModuleName(pipelineId: Int,
                    moduleId: Int): Future[Option[String]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
159
160
161
    getModules(pipelineId = Some(pipelineId), moduleId = Some(moduleId)).map(_.headOption.map(_.name))
  }

162
  /** Return a Query for [[Stats]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
163
164
165
166
167
168
169
  def statsFilter(runId: Option[Int] = None,
                  pipeline: Option[PipelineQuery] = None,
                  module: Option[ModuleQuery] = None,
                  sample: Option[SampleQuery] = None,
                  library: Option[LibraryQuery] = None,
                  mustHaveSample: Boolean = false,
                  mustHaveLibrary: Boolean = false): slick.driver.H2Driver.api.Query[Stats, Stat, Seq] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
170
171
    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
172
    f = pipeline match {
Peter van 't Hof's avatar
Peter van 't Hof committed
173
174
175
      case Some(p: PipelineId)   => f.filter(_.pipelineId === p.id)
      case Some(p: PipelineName) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === p.name).map(_._1)
      case _                     => f
Peter van 't Hof's avatar
Peter van 't Hof committed
176
177
    }
    f = module match {
Peter van 't Hof's avatar
Peter van 't Hof committed
178
179
180
181
      case Some(m: ModuleId)   => f.filter(_.moduleId === m.id)
      case Some(m: ModuleName) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === m.name).map(_._1)
      case Some(NoModule)      => f.filter(_.moduleId.isEmpty)
      case _                   => f
Peter van 't Hof's avatar
Peter van 't Hof committed
182
183
    }
    f = sample match {
Peter van 't Hof's avatar
Peter van 't Hof committed
184
185
186
187
      case Some(s: SampleId)   => f.filter(_.sampleId === s.id)
      case Some(s: SampleName) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === s.name).map(_._1)
      case Some(NoSample)      => f.filter(_.sampleId.isEmpty)
      case _                   => f
Peter van 't Hof's avatar
Peter van 't Hof committed
188
    }
189
    f = library match {
Peter van 't Hof's avatar
Peter van 't Hof committed
190
191
192
193
      case Some(l: LibraryId)   => f.filter(_.libraryId === l.id)
      case Some(l: LibraryName) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === l.name).map(_._1)
      case Some(NoLibrary)      => f.filter(_.libraryId.isEmpty)
      case _                    => f
Peter van 't Hof's avatar
Peter van 't Hof committed
194
    }
195
196
197

    if (mustHaveSample) f = f.filter(_.sampleId.nonEmpty)
    if (mustHaveLibrary) f = f.filter(_.libraryId.nonEmpty)
198
    f
Peter van 't Hof's avatar
Peter van 't Hof committed
199
  }
200

201
  /** Return all stats that match given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
202
203
204
205
206
207
208
  def getStats(runId: Option[Int] = None,
               pipeline: Option[PipelineQuery] = None,
               module: Option[ModuleQuery] = None,
               sample: Option[SampleQuery] = None,
               library: Option[LibraryQuery] = None,
               mustHaveSample: Boolean = false,
               mustHaveLibrary: Boolean = false): Future[Seq[Stat]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
209
    db.run(statsFilter(runId, pipeline, module, sample, library, mustHaveSample, mustHaveLibrary).result)
210
211
212
  }

  /** Return number of results */
Peter van 't Hof's avatar
Peter van 't Hof committed
213
214
215
216
217
218
219
  def getStatsSize(runId: Option[Int] = None,
                   pipeline: Option[PipelineQuery] = None,
                   module: Option[ModuleQuery] = None,
                   sample: Option[SampleQuery] = None,
                   library: Option[LibraryQuery] = None,
                   mustHaveSample: Boolean = false,
                   mustHaveLibrary: Boolean = false): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
220
    db.run(statsFilter(runId, pipeline, module, sample, library, mustHaveSample, mustHaveLibrary).size.result)
221
222
  }

223
  /** Get a single stat as [[Map[String, Any]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
224
225
226
227
228
  def getStat(runId: Int,
              pipeline: PipelineQuery,
              module: ModuleQuery = NoModule,
              sample: SampleQuery = NoSample,
              library: LibraryQuery = NoLibrary): Future[Option[Map[String, Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
229
    getStats(Some(runId), pipeline, module, sample, library)
230
      .map(_.headOption.map(x => ConfigUtils.jsonTextToMap(x.content)))
231
232
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
233
234
235
236
237
  def getStatKeys(runId: Int,
                  pipeline: PipelineQuery,
                  module: ModuleQuery = NoModule,
                  sample: SampleQuery = NoSample,
                  library: LibraryQuery = NoLibrary,
Peter van 't Hof's avatar
Peter van 't Hof committed
238
239
                  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
240
241
242
243
244
245
    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
246
247
248
    }
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
249
250
251
252
253
  def getStatsForSamples(runId: Int,
                         pipeline: PipelineQuery,
                         module: ModuleQuery = NoModule,
                         sample: Option[SampleQuery] = None,
                         keyValues: Map[String, List[String]]): Map[Int, Map[String, Option[Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
254
    val samples = Await.result(getSamples(runId = Some(runId), sampleId = sample.collect { case s: SampleId => s.id }, name = sample.collect { case s: SampleName => s.name }), Duration.Inf)
Peter van 't Hof's avatar
Peter van 't Hof committed
255
    (for (s <- samples) yield {
Peter van 't Hof's avatar
Peter van 't Hof committed
256
      s.id -> getStatKeys(runId, pipeline, module, SampleId(s.id), NoLibrary, keyValues = keyValues)
Peter van 't Hof's avatar
Peter van 't Hof committed
257
258
259
    }).toMap
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
260
261
262
263
264
  def getStatsForLibraries(runId: Int,
                           pipeline: PipelineQuery,
                           module: ModuleQuery = NoModule,
                           sampleId: Option[Int] = None,
                           keyValues: Map[String, List[String]]): Map[(Int, Int), Map[String, Option[Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
265
266
    val libraries = Await.result(getLibraries(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (lib <- libraries) yield {
Peter van 't Hof's avatar
Peter van 't Hof committed
267
      (lib.sampleId, lib.id) -> getStatKeys(runId, pipeline, module, SampleId(lib.sampleId), LibraryId(lib.id), keyValues = keyValues)
Peter van 't Hof's avatar
Peter van 't Hof committed
268
269
270
    }).toMap
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
271
272
273
274
275
276
277
  def settingsFilter(runId: Option[Int] = None,
                     pipeline: Option[PipelineQuery] = None,
                     module: Option[ModuleQuery] = None,
                     sample: Option[SampleQuery] = None,
                     library: Option[LibraryQuery] = None,
                     mustHaveSample: Boolean = false,
                     mustHaveLibrary: Boolean = false): slick.driver.H2Driver.api.Query[Settings, Setting, Seq] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
278
279
    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
280
    f = pipeline match {
Peter van 't Hof's avatar
Peter van 't Hof committed
281
282
283
      case Some(p: PipelineId)   => f.filter(_.pipelineId === p.id)
      case Some(p: PipelineName) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === p.name).map(_._1)
      case _                     => f
Peter van 't Hof's avatar
Peter van 't Hof committed
284
285
    }
    f = module match {
Peter van 't Hof's avatar
Peter van 't Hof committed
286
287
288
289
      case Some(m: ModuleId)   => f.filter(_.moduleId === m.id)
      case Some(m: ModuleName) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === m.name).map(_._1)
      case Some(NoModule)      => f.filter(_.moduleId.isEmpty)
      case _                   => f
Peter van 't Hof's avatar
Peter van 't Hof committed
290
291
    }
    f = sample match {
Peter van 't Hof's avatar
Peter van 't Hof committed
292
293
294
295
      case Some(s: SampleId)   => f.filter(_.sampleId === s.id)
      case Some(s: SampleName) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === s.name).map(_._1)
      case Some(NoSample)      => f.filter(_.sampleId.isEmpty)
      case _                   => f
Peter van 't Hof's avatar
Peter van 't Hof committed
296
    }
297
    f = library match {
Peter van 't Hof's avatar
Peter van 't Hof committed
298
299
300
301
      case Some(l: LibraryId)   => f.filter(_.libraryId === l.id)
      case Some(l: LibraryName) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === l.name).map(_._1)
      case Some(NoLibrary)      => f.filter(_.libraryId.isEmpty)
      case _                    => f
Peter van 't Hof's avatar
Peter van 't Hof committed
302
303
304
305
    }

    if (mustHaveSample) f = f.filter(_.sampleId.nonEmpty)
    if (mustHaveLibrary) f = f.filter(_.libraryId.nonEmpty)
306
    f
307
  }
308

309
  /** Return all settings that match the given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
310
311
312
313
314
  def getSettings(runId: Option[Int] = None,
                  pipeline: Option[PipelineQuery] = None,
                  module: Option[ModuleQuery] = None,
                  sample: Option[SampleQuery] = None,
                  library: Option[LibraryQuery] = None): Future[Seq[Setting]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
315
    db.run(settingsFilter(runId, pipeline, module, sample, library).result)
316
317
  }

318
  /** Return a specific setting as [[Map[String, Any]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
319
320
321
322
323
  def getSetting(runId: Int,
                 pipeline: PipelineQuery,
                 module: ModuleQuery = NoModule,
                 sample: SampleQuery = NoSample,
                 library: LibraryQuery = NoLibrary): Future[Option[Map[String, Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
324
    getSettings(Some(runId), Some(pipeline), module, sample, library)
325
      .map(_.headOption.map(x => ConfigUtils.jsonTextToMap(x.content)))
326
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
327

Peter van 't Hof's avatar
Peter van 't Hof committed
328
329
330
331
332
  def getSettingKeys(runId: Int,
                     pipeline: PipelineQuery,
                     module: ModuleQuery = NoModule,
                     sample: SampleQuery = NoSample,
                     library: LibraryQuery = NoLibrary,
Peter van 't Hof's avatar
Peter van 't Hof committed
333
334
                     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
335
336
337
338
339
340
    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
341
342
343
    }
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
344
345
346
347
348
  def getSettingsForSamples(runId: Int,
                            pipeline: PipelineQuery,
                            module: ModuleQuery = NoModule,
                            sampleId: Option[Int] = None,
                            keyValues: Map[String, List[String]]): Map[Int, Map[String, Option[Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
349
350
    val samples = Await.result(getSamples(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (sample <- samples) yield {
Peter van 't Hof's avatar
Peter van 't Hof committed
351
      sample.id -> getSettingKeys(runId, pipeline, module, SampleId(sample.id), NoLibrary, keyValues = keyValues)
Peter van 't Hof's avatar
Peter van 't Hof committed
352
353
354
    }).toMap
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
355
356
357
358
359
  def getSettingsForLibraries(runId: Int,
                              pipeline: PipelineQuery,
                              module: ModuleQuery = NoModule,
                              sampleId: Option[Int] = None,
                              keyValues: Map[String, List[String]]): Map[(Int, Int), Map[String, Option[Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
360
361
    val libraries = Await.result(getLibraries(runId = Some(runId), sampleId = sampleId), Duration.Inf)
    (for (lib <- libraries) yield {
Peter van 't Hof's avatar
Peter van 't Hof committed
362
      (lib.sampleId, lib.id) -> getSettingKeys(runId, pipeline, module, SampleId(lib.sampleId), LibraryId(lib.id), keyValues = keyValues)
Peter van 't Hof's avatar
Peter van 't Hof committed
363
364
365
    }).toMap
  }

366
  /** Return a [[Query]] for [[Files]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
367
368
369
370
371
372
373
374
375
376
  def filesFilter(runId: Option[Int] = None,
                  pipeline: Option[PipelineQuery] = None,
                  module: Option[ModuleQuery] = None,
                  sample: Option[SampleQuery] = None,
                  library: Option[LibraryQuery] = None,
                  key: Option[String] = None,
                  pipelineName: Option[String] = None,
                  moduleName: Option[Option[String]] = None,
                  sampleName: Option[Option[String]] = None,
                  libraryName: Option[Option[String]] = None): slick.driver.H2Driver.api.Query[Files, Files#TableElementType, Seq] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
377
378
379
    var f: Query[Files, Files#TableElementType, Seq] = files
    runId.foreach(r => f = f.filter(_.runId === r))
    key.foreach(r => f = f.filter(_.key === r))
380
381

    f = pipeline match {
Peter van 't Hof's avatar
Peter van 't Hof committed
382
383
384
      case Some(p: PipelineId)   => f.filter(_.pipelineId === p.id)
      case Some(p: PipelineName) => f.join(pipelines).on(_.pipelineId === _.id).filter(_._2.name === p.name).map(_._1)
      case _                     => f
385
386
    }
    f = module match {
Peter van 't Hof's avatar
Peter van 't Hof committed
387
388
389
390
      case Some(m: ModuleId)   => f.filter(_.moduleId === m.id)
      case Some(m: ModuleName) => f.join(modules).on(_.moduleId === _.id).filter(_._2.name === m.name).map(_._1)
      case Some(NoModule)      => f.filter(_.moduleId.isEmpty)
      case _                   => f
391
392
    }
    f = sample match {
Peter van 't Hof's avatar
Peter van 't Hof committed
393
394
395
396
      case Some(s: SampleId)   => f.filter(_.sampleId === s.id)
      case Some(s: SampleName) => f.join(samples).on(_.sampleId === _.id).filter(_._2.name === s.name).map(_._1)
      case Some(NoSample)      => f.filter(_.sampleId.isEmpty)
      case _                   => f
397
398
    }
    f = library match {
Peter van 't Hof's avatar
Peter van 't Hof committed
399
400
401
402
      case Some(l: LibraryId)   => f.filter(_.libraryId === l.id)
      case Some(l: LibraryName) => f.join(libraries).on(_.libraryId === _.id).filter(_._2.name === l.name).map(_._1)
      case Some(NoLibrary)      => f.filter(_.libraryId.isEmpty)
      case _                    => f
403
    }
404
405
406
    f
  }

407
  /** Returns all [[Files]] with the given criteria */
Peter van 't Hof's avatar
Peter van 't Hof committed
408
409
  def getFiles(runId: Option[Int] = None, pipeline: Option[PipelineQuery] = None, module: Option[ModuleQuery] = None,
               sample: Option[SampleQuery] = None, library: Option[LibraryQuery] = None,
410
               key: Option[String] = None): Future[Seq[Schema.File]] = {
411
    db.run(filesFilter(runId, pipeline, module, sample, library, key).result)
Peter van 't Hof's avatar
Peter van 't Hof committed
412
413
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
414
415
  def getFile(runId: Int, pipeline: PipelineQuery, module: ModuleQuery = NoModule, sample: SampleQuery = NoSample,
              library: LibraryQuery = NoLibrary, key: String): Future[Option[Schema.File]] = {
416
    db.run(filesFilter(Some(runId), Some(pipeline), Some(module), Some(sample), Some(library), Some(key)).result).map(_.headOption)
417
418
  }

419
  /** Returns a [[Query]] for [[Executables]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
420
421
  def executablesFilter(runId: Option[Int],
                        toolName: Option[String]): slick.driver.H2Driver.api.Query[Executables, Executable, Seq] = {
422
423
424
425
426
427
428
429
430
431
432
433
434
    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
  }

  /** Return all executables with given criteria */
  def getExecutables(runId: Option[Int] = None, toolName: Option[String] = None): Future[Seq[Executable]] = {
    db.run(executablesFilter(runId, toolName).result)
  }

}

Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
435
class SummaryDbReadOnly(val db: Database)(implicit val ec: ExecutionContext) extends SummaryDb
436

Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
437
class SummaryDbWrite(val db: Database)(implicit val ec: ExecutionContext) extends SummaryDb {
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
  /** This method will create all tables */
  def createTables(): Unit = {
    try {
      val setup = DBIO.seq(
        (runs.schema ++ samples.schema ++
          libraries.schema ++ pipelines.schema ++
          modules.schema ++ stats.schema ++ settings.schema ++
          files.schema ++ executables.schema).create
      )
      val setupFuture = db.run(setup)
      Await.result(setupFuture, Duration.Inf)
    }
  }

  /** This method will create a new run and return the runId */
  def createRun(runName: String, outputDir: String, version: String, commitHash: String,
                creationDate: Date): Future[Int] = {
    val id = Await.result(db.run(runs.size.result), Duration.Inf)
    db.run(runs.forceInsert(Run(id, runName, outputDir, version, commitHash, creationDate))).map(_ => id)
  }

  /** This creates a new sample and return the sampleId */
  def createSample(name: String, runId: Int, tags: Option[String] = None): Future[Int] = {
    val id = Await.result(db.run(samples.size.result), Duration.Inf)
    db.run(samples.forceInsert(Sample(id, name, runId, tags))).map(_ => id)
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
465
466
467
468
  def createOrUpdateSample(name: String,
                           runId: Int,
                           tags: Option[String] = None): Future[Int] = {
    getSampleId(runId, name).flatMap {
469
470
471
472
      case Some(id: Int) =>
        db.run(samples.filter(_.name === name).filter(_.id === id).map(_.tags).update(tags))
          .map(_ => id)
      case _ => createSample(name, runId, tags)
Peter van 't Hof's avatar
Peter van 't Hof committed
473
    }
474
475
476
  }

  /** This will create a new library */
Peter van 't Hof's avatar
Peter van 't Hof committed
477
478
479
480
  def createLibrary(name: String,
                    runId: Int,
                    sampleId: Int,
                    tags: Option[String] = None): Future[Int] = {
481
482
483
484
    val id = Await.result(db.run(libraries.size.result), Duration.Inf)
    db.run(libraries.forceInsert(Library(id, name, runId, sampleId, tags))).map(_ => id)
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
485
486
487
488
489
  def createOrUpdateLibrary(name: String,
                            runId: Int,
                            sampleId: Int,
                            tags: Option[String] = None): Future[Int] = {
    getLibraryId(runId, sampleId, name).flatMap {
490
491
492
493
      case Some(id: Int) =>
        db.run(libraries.filter(_.name === name).filter(_.id === id).filter(_.sampleId === sampleId).map(_.tags).update(tags))
          .map(_ => id)
      case _ => createLibrary(name, runId, sampleId, tags)
Peter van 't Hof's avatar
Peter van 't Hof committed
494
    }
495
496
497
  }

  /** Creates a new pipeline, even if it already exist. This may give a database exeption */
Peter van 't Hof's avatar
Peter van 't Hof committed
498
499
  def forceCreatePipeline(name: String,
                          runId: Int): Future[Int] = {
500
501
502
503
504
    val id = Await.result(db.run(pipelines.size.result), Duration.Inf)
    db.run(pipelines.forceInsert(Pipeline(id, name, runId))).map(_ => id)
  }

  /** Creates a new pipeline if it does not yet exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
505
506
  def createPipeline(name: String,
                     runId: Int): Future[Int] = {
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
    getPipelines(name = Some(name), runId = Some(runId))
      .flatMap {
        m =>
          if (m.isEmpty) forceCreatePipeline(name, runId)
          else Future(m.head.id)
      }
  }

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

  /** Creates a new module if it does not yet exist */
  def createModule(name: String, runId: Int, pipelineId: Int): Future[Int] = {
    getModules(name = Some(name), runId = Some(runId), pipelineId = Some(pipelineId))
      .flatMap {
        m =>
          if (m.isEmpty) forceCreateModule(name, runId, pipelineId)
          else Future(m.head.id)
      }
  }

  /** Create a new stat in the database, This method is need checking before */
Peter van 't Hof's avatar
Peter van 't Hof committed
532
533
534
535
536
537
  def createStat(runId: Int,
                 pipelineId: Int,
                 moduleId: Option[Int] = None,
                 sampleId: Option[Int] = None,
                 libId: Option[Int] = None,
                 content: String): Future[Int] = {
538
539
540
541
    db.run(stats.forceInsert(Stat(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

  /** This create or update a stat */
Peter van 't Hof's avatar
Peter van 't Hof committed
542
543
544
545
546
547
548
549
  def createOrUpdateStat(runId: Int,
                         pipelineId: Int,
                         moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None,
                         libId: Option[Int] = None,
                         content: String): Future[Int] = {
    val filter = statsFilter(Some(runId), pipelineId, Some(moduleId.map(ModuleId).getOrElse(NoModule)),
      Some(sampleId.map(SampleId).getOrElse(NoSample)), Some(libId.map(LibraryId).getOrElse(NoLibrary)))
550
551
552
553
554
555
    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))
  }

  /** This method creates a new setting. This method need checking before */
Peter van 't Hof's avatar
Peter van 't Hof committed
556
557
558
559
560
561
  def createSetting(runId: Int,
                    pipelineId: Int,
                    moduleId: Option[Int] = None,
                    sampleId: Option[Int] = None,
                    libId: Option[Int] = None,
                    content: String): Future[Int] = {
562
563
564
565
    db.run(settings.forceInsert(Setting(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

  /** This method creates or update a setting. */
Peter van 't Hof's avatar
Peter van 't Hof committed
566
567
568
569
570
571
  def createOrUpdateSetting(runId: Int,
                            pipelineId: Int,
                            moduleId: Option[Int] = None,
                            sampleId: Option[Int] = None,
                            libId: Option[Int] = None,
                            content: String): Future[Int] = {
572
573
574
575
576
577
    val filter = settingsFilter(Some(runId), PipelineId(pipelineId), moduleId.map(ModuleId), sampleId.map(SampleId), libId.map(LibraryId))
    val r = Await.result(db.run(filter.size.result), Duration.Inf)
    if (r == 0) createSetting(runId, pipelineId, moduleId, sampleId, libId, content)
    else db.run(filter.update(Setting(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

578
  /** Creates a file. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
579
580
581
582
583
584
585
586
587
588
  def createFile(runId: Int,
                 pipelineId: Int,
                 moduleId: Option[Int] = None,
                 sampleId: Option[Int] = None,
                 libId: Option[Int] = None,
                 key: String,
                 path: String,
                 md5: String,
                 link: Boolean = false,
                 size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
589
    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
590
591
  }

592
  /** Create or update a File */
Peter van 't Hof's avatar
Peter van 't Hof committed
593
594
595
596
597
598
599
600
601
602
  def createOrUpdateFile(runId: Int,
                         pipelineId: Int,
                         moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None,
                         libId: Option[Int] = None,
                         key: String,
                         path: String,
                         md5: String,
                         link: Boolean = false,
                         size: Long): Future[Int] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
603
    val filter = filesFilter(Some(runId), PipelineId(pipelineId), moduleId.map(ModuleId), sampleId.map(SampleId), libId.map(LibraryId), Some(key))
Peter van 't Hof's avatar
Peter van 't Hof committed
604
605
    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
606
    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
607
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
608

609
  /** Creates a exeutable. This method will raise expection if it already exist */
Peter van 't Hof's avatar
Peter van 't Hof committed
610
611
612
613
  def createExecutable(runId: Int,
                       toolName: String,
                       version: Option[String] = None,
                       path: Option[String] = None,
614
                       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
615
616
617
    db.run(executables.forceInsert(Executable(runId, toolName, version, path, javaVersion, exeMd5, javaMd5, jarPath)))
  }

618
  /** Create or update a [[Executable]] */
Peter van 't Hof's avatar
Peter van 't Hof committed
619
620
621
622
623
624
625
626
  def createOrUpdateExecutable(runId: Int,
                               toolName: String,
                               version: Option[String] = None,
                               path: Option[String] = None,
                               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
627
628
629
630
631
    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)))
  }
632

633
}
634
635

object SummaryDb {
636

Peter van 't Hof's avatar
Peter van 't Hof committed
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
  trait PipelineQuery
  case class PipelineId(id: Int) extends PipelineQuery
  case class PipelineName(name: String) extends PipelineQuery

  trait SampleQuery
  case object NoSample extends SampleQuery
  case class SampleId(id: Int) extends SampleQuery
  case class SampleName(name: String) extends SampleQuery

  trait LibraryQuery
  case object NoLibrary extends LibraryQuery
  case class LibraryId(id: Int) extends LibraryQuery
  case class LibraryName(name: String) extends LibraryQuery

  trait ModuleQuery
  case object NoModule extends ModuleQuery
  case class ModuleId(id: Int) extends ModuleQuery
  case class ModuleName(name: String) extends ModuleQuery

656
  object Implicts {
Peter van 't Hof's avatar
Peter van 't Hof committed
657
658

    implicit def intToPipelineQuery(x: Int): PipelineQuery = PipelineId(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
659
    implicit def stringToPipelineQuery(x: String): PipelineQuery = PipelineName(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
660
    implicit def intToOptionPipelineQuery(x: Int): Option[PipelineQuery] = Some(PipelineId(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
661
    implicit def stringToOptionPipelineQuery(x: String): Option[PipelineQuery] = Some(PipelineName(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
662
663
664
    implicit def sampleQueryToOptionPipelineQuery(x: PipelineQuery): Option[PipelineQuery] = Some(x)

    implicit def intToModuleQuery(x: Int): ModuleQuery = ModuleId(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
665
    implicit def stringToModuleQuery(x: String): ModuleQuery = ModuleName(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
666
667
668
669
670
    implicit def intToOptionModuleQuery(x: Int): Option[ModuleQuery] = Some(ModuleId(x))
    implicit def intToOptionModuleQuery(x: String): Option[ModuleQuery] = Some(ModuleName(x))
    implicit def moduleQueryToOptionModuleQuery(x: ModuleQuery): Option[ModuleQuery] = Some(x)

    implicit def intToSampleQuery(x: Int): SampleQuery = SampleId(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
671
    implicit def stringToSampleQuery(x: String): SampleQuery = SampleName(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
672
    implicit def intToOptionSampleQuery(x: Int): Option[SampleQuery] = Some(SampleId(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
673
    implicit def stringToOptionSampleQuery(x: String): Option[SampleQuery] = Some(SampleName(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
674
675
676
    implicit def sampleQueryToOptionSampleQuery(x: SampleQuery): Option[SampleQuery] = Some(x)

    implicit def intToLibraryQuery(x: Int): LibraryQuery = LibraryId(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
677
    implicit def stringToLibraryQuery(x: String): LibraryQuery = LibraryName(x)
Peter van 't Hof's avatar
Peter van 't Hof committed
678
    implicit def intToOptionLibraryQuery(x: Int): Option[LibraryQuery] = Some(LibraryId(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
679
    implicit def stringToOptionLibraryQuery(x: String): Option[LibraryQuery] = Some(LibraryName(x))
Peter van 't Hof's avatar
Peter van 't Hof committed
680
    implicit def libraryQueryToOptionLibraryQuery(x: LibraryQuery): Option[LibraryQuery] = Some(x)
681
682
  }

683
  private var summaryConnections = Map[File, SummaryDbWrite]()
684

685
  /** This closing all summary that are still in the cache */
686
687
  def closeAll(): Unit = {
    summaryConnections.foreach(_._2.close())
688
    summaryConnections = summaryConnections.empty
689
690
  }

691
  /** This will open a sqlite database and create tables when the database did not exist yet */
Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
692
  def openSqliteSummary(file: File)(implicit ec: ExecutionContext): SummaryDbWrite = {
693
    if (!summaryConnections.contains(file)) {
Peter van 't Hof's avatar
Peter van 't Hof committed
694
695
696
      val config: org.sqlite.SQLiteConfig = new org.sqlite.SQLiteConfig()
      config.enforceForeignKeys(true)
      config.setBusyTimeout("10000")
697
      config.setSynchronous(org.sqlite.SQLiteConfig.SynchronousMode.FULL)
698
      val exist = file.exists()
Peter van 't Hof's avatar
Peter van 't Hof committed
699
      val db = Database.forURL(s"jdbc:sqlite:${file.getAbsolutePath}", driver = "org.sqlite.JDBC", prop = config.toProperties, executor = AsyncExecutor("single_thread", 1, 1000))
700
      val s = new SummaryDbWrite(db)
701
702
703
704
      if (!exist) s.createTables()
      summaryConnections += file -> s
    }
    summaryConnections(file)
705
  }
706

Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
707
  def openReadOnlySqliteSummary(file: File)(implicit ec: ExecutionContext): SummaryDbReadOnly = {
708
709
710
711
712
713
    require(file.exists(), s"File does not exist: $file")
    val config: org.sqlite.SQLiteConfig = new org.sqlite.SQLiteConfig()
    config.enforceForeignKeys(true)
    config.setBusyTimeout("10000")
    config.setSynchronous(org.sqlite.SQLiteConfig.SynchronousMode.FULL)
    config.setReadOnly(true)
Peter van 't Hof's avatar
Peter van 't Hof committed
714
715

    val asyncExecutor = new AsyncExecutor {
Peter van 't Hof's avatar
WIP    
Peter van 't Hof committed
716
      override def executionContext: ExecutionContext = ec
Peter van 't Hof's avatar
Peter van 't Hof committed
717
718
719
720
      override def close(): Unit = {}
    }

    val db = Database.forURL(s"jdbc:sqlite:${file.getAbsolutePath}", driver = "org.sqlite.JDBC", prop = config.toProperties, executor = asyncExecutor)
721
722
    new SummaryDbReadOnly(db)
  }
723
}