SummaryDb.scala 15 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 java.io.{ Closeable, File }
4

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

import scala.concurrent.ExecutionContext.Implicits.global
Peter van 't Hof's avatar
Peter van 't Hof committed
10
11
import scala.concurrent.duration.Duration
import scala.concurrent.{Await, Future}
12
13

/**
Peter van 't Hof's avatar
Peter van 't Hof committed
14
15
 * Created by pjvanthof on 05/02/2017.
 */
16
17
18
19
class SummaryDb(db: Database) extends Closeable {

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

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

  def createRun(runName: String, outputDir: String): Future[Int] = {
    val id = Await.result(db.run(runs.size.result), Duration.Inf)
36
    db.run(runs.forceInsert(Run(id, runName, outputDir))).map(_ => id)
37
38
39
40
41
42
43
  }

  def getRuns(runId: Option[Int] = None, runName: Option[String] = None, outputDir: Option[String] = None) = {
    val q = runs.filter { run =>
      List(
        runId.map(run.id === _),
        runName.map(run.runName === _),
44
        outputDir.map(run.outputDir === _)
45
46
47
48
49
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
    db.run(q.result)
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
50
  def createSample(name: String, runId: Int, tags: Option[String] = None): Future[Int] = {
51
    val id = Await.result(db.run(samples.size.result), Duration.Inf)
52
    db.run(samples.forceInsert(Sample(id, name, runId, tags))).map(_ => id)
53
54
55
56
57
58
59
  }

  def getSamples(sampleId: Option[Int] = None, runId: Option[Int] = None, name: Option[String] = None) = {
    val q = samples.filter { sample =>
      List(
        sampleId.map(sample.id === _),
        runId.map(sample.runId === _),
60
        name.map(sample.name === _)
61
62
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
63
    db.run(q.result)
64
65
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
66
67
68
69
  def getSampleId(runId: Int, sampleName: String) = {
    getSamples(runId = Some(runId), name = Some(sampleName)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
70
71
72
73
74
75
76
  def getSampleTags(sampleId: Int): Future[Option[Map[String, Any]]] = {
    db.run(samples.filter(_.id === sampleId).map(_.tags).result)
      .map(_.headOption.flatten.map(ConfigUtils.jsonTextToMap))
  }

  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)
77
    db.run(libraries.forceInsert(Library(id, name, runId, sampleId, tags))).map(_ => id)
Peter van 't Hof's avatar
Peter van 't Hof committed
78
79
80
81
82
83
84
85
86
87
88
  }

  def getLibraries(libId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None, sampleId: Option[Int] = None) = {
    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])
    }
89
    db.run(q.result)
Peter van 't Hof's avatar
Peter van 't Hof committed
90
91
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
92
93
94
95
  def getLibraryId(runId: Int, sampleId: Int, name: String) = {
    getLibraries(runId = Some(runId), sampleId = Some(sampleId), name = Some(name)).map(_.headOption.map(_.id))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
96
97
98
  def getLibraryTags(libId: Int): Future[Option[Map[String, Any]]] = {
    db.run(libraries.filter(_.id === libId).map(_.tags).result)
      .map(_.headOption.flatten.map(ConfigUtils.jsonTextToMap))
99
100
  }

101
  def forceCreatePipeline(name: String, runId: Int): Future[Int] = {
102
103
104
105
    val id = Await.result(db.run(pipelines.size.result), Duration.Inf)
    db.run(pipelines.forceInsert(Pipeline(id, name, runId))).map(_ => id)
  }

106
107
  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
108
109
110
111
      .flatMap {
        case m =>
          if (m.isEmpty) forceCreatePipeline(name, runId)
          else Future(m.head.id)
112
113
114
      }
  }

115
116
117
118
119
  def getPipelines(pipelineId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None) = {
    val q = pipelines.filter { lib =>
      List(
        pipelineId.map(lib.id === _),
        runId.map(lib.runId === _),
120
        name.map(lib.name === _)
121
122
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
123
    db.run(q.result)
124
125
  }

126
  def forceCreateModule(name: String, runId: Int, pipelineId: Int): Future[Int] = {
127
128
129
130
    val id = Await.result(db.run(modules.size.result), Duration.Inf)
    db.run(modules.forceInsert(Module(id, name, runId, pipelineId))).map(_ => id)
  }

131
132
  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
133
134
135
136
      .flatMap {
        case m =>
          if (m.isEmpty) forceCreateModule(name, runId, pipelineId)
          else Future(m.head.id)
137
138
139
      }
  }

140
141
142
143
144
145
  def getModules(moduleId: Option[Int] = None, name: Option[String] = None, runId: Option[Int] = None, pipelineId: Option[Int] = None) = {
    val q = modules.filter { lib =>
      List(
        moduleId.map(lib.id === _),
        runId.map(lib.runId === _),
        pipelineId.map(lib.pipelineId === _),
146
        name.map(lib.name === _)
147
148
      ).collect({ case Some(criteria) => criteria }).reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
    }
149
    db.run(q.result)
150
151
  }

152
153
154
155
156
  def createStat(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                 sampleId: Option[Int] = None, libId: Option[Int] = None, content: String) = {
    db.run(stats.forceInsert(Stat(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

Peter van 't Hof's avatar
Peter van 't Hof committed
157
158
159
160
161
162
163
164
165
  def createOrUpdateStat(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None, libId: Option[Int] = None, content: String) = {
    val filter = statsFilter(Some(runId), Some(pipelineId), Some(moduleId), Some(sampleId), Some(libId))
    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))
  }

  private def statsFilter(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
166
                          sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
167
168
169
170
171
172
173
    val l: List[Option[Query[Stats, Stats#TableElementType, Seq] => Query[Stats, Stats#TableElementType, Seq]]] = List(
      runId.map(x => y => y.filter(_.runId === x)),
      pipelineId.map(x => y => y.filter(_.pipelineId === x)),
      moduleId.map(x => y => (if (x.isDefined) y.filter(_.moduleId === x) else y.filter(_.moduleId.isEmpty))),
      sampleId.map(x => y => (if (x.isDefined) y.filter(_.sampleId === x) else y.filter(_.sampleId.isEmpty))),
      libId.map(x => y => (if (x.isDefined) y.filter(_.libraryId === x) else y.filter(_.libraryId.isEmpty)))
    )
Peter van 't Hof's avatar
Peter van 't Hof committed
174
    l.flatten.foldLeft(stats.subquery)((a, b) => b(a))
Peter van 't Hof's avatar
Peter van 't Hof committed
175
  }
176

Peter van 't Hof's avatar
Peter van 't Hof committed
177
178
179
  def getStats(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]] = None,
               sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None) = {
    db.run(statsFilter(runId, pipelineId, moduleId, sampleId, libId).result)
180
181
182
183
  }

  def getStat(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
              sampleId: Option[Int] = None, libId: Option[Int] = None): Future[Option[Map[String, Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
184
185
186
187
188
189
190
    val l: List[Query[Stats, Stats#TableElementType, Seq] => Query[Stats, Stats#TableElementType, Seq]] = List(
      y => y.filter(_.runId === runId),
      y => y.filter(_.pipelineId === pipelineId),
      y => (if (moduleId.isDefined) y.filter(_.moduleId === moduleId) else y.filter(_.moduleId.isEmpty)),
      y => (if (sampleId.isDefined) y.filter(_.sampleId === sampleId) else y.filter(_.sampleId.isEmpty)),
      y => (if (libId.isDefined) y.filter(_.libraryId === libId) else y.filter(_.libraryId.isEmpty))
    )
Peter van 't Hof's avatar
Peter van 't Hof committed
191
    val q = l.foldLeft(stats.subquery)((a, b) => b(a))
192
193
194
195
196

    db.run(q.map(_.content).result).map(_.headOption.map(ConfigUtils.jsonTextToMap))
  }

  def createSetting(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
197
                    sampleId: Option[Int] = None, libId: Option[Int] = None, content: String) = {
198
199
200
    db.run(settings.forceInsert(Setting(runId, pipelineId, moduleId, sampleId, libId, content)))
  }

201
202
  def settingsFilter(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]] = None,
                     sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
203
204
205
206
207
208
209
    val l: List[Option[Query[Settings, Settings#TableElementType, Seq] => Query[Settings, Settings#TableElementType, Seq]]] = List(
      runId.map(x => y => y.filter(_.runId === x)),
      pipelineId.map(x => y => y.filter(_.pipelineId === x)),
      moduleId.map(x => y => (if (x.isDefined) y.filter(_.moduleId === x) else y.filter(_.moduleId.isEmpty))),
      sampleId.map(x => y => (if (x.isDefined) y.filter(_.sampleId === x) else y.filter(_.sampleId.isEmpty))),
      libId.map(x => y => (if (x.isDefined) y.filter(_.libraryId === x) else y.filter(_.libraryId.isEmpty)))
    )
210
211
    l.flatten.foldLeft(settings.subquery)((a, b) => b(a))
  }
212

213
214
215
216
217
218
219
220
221
222
223
  def createOrUpdateSetting(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
                         sampleId: Option[Int] = None, libId: Option[Int] = None, content: String) = {
    val filter = settingsFilter(Some(runId), Some(pipelineId), Some(moduleId), Some(sampleId), Some(libId))
    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.map(_.content).update(content))
  }

  def getSettings(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]] = None,
                  sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None) = {
    db.run(settingsFilter(runId, pipelineId, moduleId, sampleId, libId).result)
224
225
226
  }

  def getSetting(runId: Int, pipelineId: Int, moduleId: Option[Int] = None,
Peter van 't Hof's avatar
Peter van 't Hof committed
227
                 sampleId: Option[Int] = None, libId: Option[Int] = None): Future[Option[Map[String, Any]]] = {
Peter van 't Hof's avatar
Peter van 't Hof committed
228
229
230
231
232
233
234
235
    val l: List[Query[Settings, Settings#TableElementType, Seq] => Query[Settings, Settings#TableElementType, Seq]] = List(
      _.filter(_.runId === runId),
      _.filter(_.pipelineId === pipelineId),
      y => (if (moduleId.isDefined) y.filter(_.moduleId === moduleId) else y.filter(_.moduleId.isEmpty)),
      y => (if (sampleId.isDefined) y.filter(_.sampleId === sampleId) else y.filter(_.sampleId.isEmpty)),
      y => (if (libId.isDefined) y.filter(_.libraryId === libId) else y.filter(_.libraryId.isEmpty))
    )

Peter van 't Hof's avatar
Peter van 't Hof committed
236
    val q = l.foldLeft(settings.subquery)((a, b) => b(a))
237
238
    db.run(q.map(_.content).result).map(_.headOption.map(ConfigUtils.jsonTextToMap))
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262

  def filesFilter(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]],
                  sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None,
                  key: Option[String] = None) = {
    val l: List[Option[Query[Files, Files#TableElementType, Seq] => Query[Files, Files#TableElementType, Seq]]] = List(
      runId.map(x => y => y.filter(_.runId === x)),
      pipelineId.map(x => y => y.filter(_.pipelineId === x)),
      moduleId.map(x => y => (if (x.isDefined) y.filter(_.moduleId === x) else y.filter(_.moduleId.isEmpty))),
      sampleId.map(x => y => (if (x.isDefined) y.filter(_.sampleId === x) else y.filter(_.sampleId.isEmpty))),
      libId.map(x => y => (if (x.isDefined) y.filter(_.libraryId === x) else y.filter(_.libraryId.isEmpty))),
      key.map(x => y => y.filter(_.key === x))
    )
    l.flatten.foldLeft(files.subquery)((a, b) => b(a))
  }

  def getFiles(runId: Option[Int] = None, pipelineId: Option[Int] = None, moduleId: Option[Option[Int]],
               sampleId: Option[Option[Int]] = None, libId: Option[Option[Int]] = None,
               key: Option[String] = None) = {
    db.run(filesFilter(runId, pipelineId, moduleId, sampleId, libId, key).result)
  }

  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) = {
Peter van 't Hof's avatar
Peter van 't Hof committed
263
    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
264
265
266
267
268
269
270
271
  }

  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) = {
    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
272
    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
273
  }
Peter van 't Hof's avatar
Peter van 't Hof committed
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298

  def executablesFilter(runId: Option[Int], toolName: Option[String]) = {
    val l: List[Option[Query[Executables, Executables#TableElementType, Seq] => Query[Executables, Executables#TableElementType, Seq]]] = List(
      runId.map(x => y => y.filter(_.runId === x)),
      toolName.map(x => y => y.filter(_.toolName === x))
    )
    l.flatten.foldLeft(executables.subquery)((a, b) => b(a))
  }

  def getFiles(runId: Option[Int], toolName: Option[String]) = {
    db.run(executablesFilter(runId, toolName).result)
  }

  def createExecutable(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) = {
    db.run(executables.forceInsert(Executable(runId, toolName, version, path, javaVersion, exeMd5, javaMd5, jarPath)))
  }

  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) = {
    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)))
  }
299
}
300
301
302
303
304
305
306
307
308
309

object SummaryDb {
  def openSqliteSummary(file: File): SummaryDb = {
    val exist = file.exists()
    val db = Database.forURL(s"jdbc:sqlite:${file.getAbsolutePath}", driver = "org.sqlite.JDBC")
    val s = new SummaryDb(db)
    if (!exist) s.createTables()
    s
  }
}