SQL Schema generation for views using Codegen

[yadavan88]

Hi, I am not sure if this is a bug, or I am missing out on some configuration. I am using the profile.DDL for generating scripts for my database schema from the slick schema.

lazy val tables: profile.DDL = Array(employee.schema, department.schema)
val scripts = tables.flatMap(_.schema.createStatements.toList)

I have some views also in my database. So when I use the code generator, it will also add the view to the same tables array. When I generate the scripts, it is generating the views also as tables (create table instead of create view).

Do I need to do something to generate the view scripts separately ?

[cvogt]

Slick doesn't have a feature for generating DDL for views. Sounds like a nice contribution if you want to give it a shot, but also seems like it would require some effort and commitment to take it all the way. Otherwise I'd recommend to write the DDL for view by hand. A small feature you could add to the code generator that might help you might be simply making the model aware what is a view and then not generating any DDL at all.

[yadavan88]

Thank You @cvogt . I will try if I can contribute something.

[drdozer]

This bit me. I ended up having to clone the data in my materialized views into tables :(

[evbo]

Dirt simple workaround (create your own code generator runner that includes materialized views):

import slick.codegen.SourceCodeGenerator
import slick.jdbc.JdbcProfile
import slick.jdbc.meta.MTable
import slick.model.Model

import scala.concurrent.duration.Duration
import scala.concurrent.{Await, ExecutionContext}

object IncludeMaterializedViewsGenerator extends App {

  val profile = args(0)
  val jdbcDriver = args(1)
  val url = args(2)
  val outputDir = args(3)
  val pkg = args(4)
  val user = args(5)
  val password = args(6)
  val ignoreInvalidDefaults = args(7).toBoolean
  val codeGeneratorClass = args(8)

  val profileInstance: JdbcProfile = Class.forName(profile + "$").getField("MODULE$").get(null).asInstanceOf[JdbcProfile]
  val dbFactory = profileInstance.api.Database
  val db = dbFactory.forURL(url, driver = jdbcDriver, user = user, password = password, keepAliveConnection = true)

  val tables = Some(MTable.getTables(None, None, None, Some(Seq("TABLE", "MATERIALIZED VIEW"))))

  try {
    val m = Await.result(db.run(profileInstance.createModel(tables, ignoreInvalidDefaults)(ExecutionContext.global).withPinnedSession), Duration.Inf)
    val sourceGeneratorClass = Class.forName(codeGeneratorClass).asInstanceOf[Class[_ <: SourceCodeGenerator]]
    val generatorInstance = sourceGeneratorClass.getConstructor(classOf[Model]).newInstance(m)
    generatorInstance.writeToFile(profile,outputDir, pkg)
  } finally db.close

}