CODEWITHSUNDEEP
scalah2squeryl
Pengin
Pengin

Reputation: 4772

Concatenating databases with Squeryl

I'm trying to use Squeryl to take the contents of a table from one database, and append it to the equivalent table in another database. The primary key will have to be reassigned in the process, but I'm getting the error NULL not allowed for column "SIMID". Why is this?

object Concatenator {
  def main(args: Array[String]) {
    Class.forName("org.h2.Driver");

    val seshA = Session.create(
      java.sql.DriverManager.getConnection("jdbc:h2:file:data/resultsA", "sa", "password"),
      new H2Adapter
    )

    val seshB = Session.create(
      java.sql.DriverManager.getConnection("jdbc:h2:file:data/resultsB", "sa", "password"),
      new H2Adapter
    )

    using(seshA){
      import Library._
      from(sims){s => select(s)}.foreach{item =>
        using(seshB){
          sims.insert(item);
        }
      }
    }

  }

  case class Simulation(
    @Column("SIMID")
    var id: Long, 
    val date: Date
  ) extends KeyedEntity[Long]

  object Library extends Schema {
    val sims = table[Simulation]

    on(sims)(s => declare(
      s.id is(unique, indexed, autoIncremented)
    ))
  }
}

Update: I think it might be something to do with the DBs. They were created in a Java project using JPA/EclipseLink and in additional to generating tables for my entities it also created a table called SEQUENCE, presumably for primary key generation.

I've found that I can create an brand new table in Squeryl and manually put the contents of both databases in that, thus achieving the same effect. Interestingly this new table did not have any SEQUENCE table auto generated. So I'm guessing it comes down to how JPA/EclipseLink was generating my primary keys?

Update 2: As requested, I appended trace_level_file=3 to the url and the files are here: resultsA.trace.db and resultsB.trace.db. B is the more interesting one I think. Also, I've put a simplified version of the database here which has had unnecessary tables removed (the same database is used for resultsA and resultsB).

Upvotes: 1

Views: 449

Answers (2)

Dave Whittaker
Dave Whittaker

Reputation: 3102

Just got a moment to look at this more closely. I turns out you were on the right track. While I guess that EclipseLink uses Sequences to generate the PK value, Squeryl defines the column as something like:

simid bigint not null primary key auto_increment

Without the auto_increment flag a value is never placed in the column and you end up with the constraint violation you mentioned. It sounds like you've already worked around the issue, but hopefully this will help you or someone else in the future.

Upvotes: 1

Pengin
Pengin

Reputation: 4772

Not really a solution, but my workaround is to create a new database

val seshNew = Session.create(java.sql.DriverManager.getConnection("jdbc:h2:file:data/resultsNew", "sa","password"),new H2Adapter)

and then just write all the data from the other databases into it

using(seshNew){
    sims.insert(new Simulation(0,item.date))
}

The primary keys 0 gets overwritten as appropriate.

Upvotes: 0

Related Questions