Is used to demonstrate a malformed database schema bug in Slick and SQLite3.

main

import java.io.File

import scala.slick.driver.SQLiteDriver.simple._
import Database.threadLocalSession

//Define database schema
case class Article(id: Long, title: String, text: String)

object Articles extends Table[Article]("ARTICLES") {
  def id = column[Long]("ID", O.PrimaryKey)
  def title = column[String]("TITLE")
  def text = column[String]("TEXT")

  def * = id ~ title ~ text <> (Article, Article.unapply _)
}

case class Category(id: Int, docID: Long, title: String)

object CategoryTitles extends Table[Category]("CATEGORY_TITLES") {
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def docID = column[Long]("DOC_ID")
  def title = column[String]("TITLE")

  def * = id ~ docID ~ title <> (Category, Category.unapply _)
  def autoInc = docID ~ title returning id

  def article = foreignKey("DOC_FK", docID, Articles)(_.id)
}

object Main {

  def main(args: Array[String]) {

    Database.forURL("jdbc:sqlite:test.db", driver = "org.sqlite.JDBC") withSession {

      val ddl = (Articles.ddl ++ CategoryTitles.ddl)
      ddl.createStatements.foreach(println)
      ddl.create

      Articles.insert(new Article(1, "Obama", "Obama is president"))
      Articles.insert(new Article(2, "Washington", "Washington is a state"))

      CategoryTitles.autoInc.insert(1, "Presidents")
      CategoryTitles.autoInc.insert(2, "States")

      val r =  for {
          a <- Articles
          c <- CategoryTitles if a.id === c.docID
      } yield (a.title, c.title)
      
      r foreach println
    }
  }
}