--- a/project/build/FisProject.scala	Fri Feb 10 09:53:04 2012 +0100
+++ b/project/build/FisProject.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -30,6 +30,9 @@
   val servlet = "javax.servlet" % "servlet-api" % "2.5" % "provided"
   val jetty6 = "org.mortbay.jetty" % "jetty" % "6.1.23" % "test"
 
+  // runtime
+  val pgsql = "postgresql" % "postgresql" % "8.4-702.jdbc4"
+
   // testing
   val scalatest = "org.scalatest" % "scalatest" % "1.3" % "test"
   val h2 = "com.h2database" % "h2" % "1.2.142" % "test"

--- a/src/main/scala/fis/base/model/BaseSchema.scala	Fri Feb 10 09:53:04 2012 +0100
+++ b/src/main/scala/fis/base/model/BaseSchema.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -20,6 +20,7 @@
 
 trait BaseSchema extends Schema {
   val codeListItems = table[CodeListItem]
+  on(codeListItems) { t => declare(t.id.is(autoIncremented("entity_id_seq"))) }
 }
 
 object BaseSchema extends BaseSchema

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/scala/fis/base/model/SeqIdH2Adapter.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -0,0 +1,83 @@
+/*
+ * Copyright 2011 Tomas Zeman <tzeman@volny.cz>
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package fis.base.model
+
+import java.sql.{SQLException, ResultSet}
+import org.squeryl.{Session, Table}
+import org.squeryl.adapters.H2Adapter
+import org.squeryl.internals.{StatementWriter, FieldMetaData}
+
+/**
+ * H2 adapter which takes entity IDs from db sequences instead of
+ * autoincrements. Sequence names can be reused (multiple entities can use
+ * the same sequence as ID generator).
+ *
+ * The implementation is taken mostly from Postgres adapter which is
+ *
+ *    Copyright 2010 Maxime Lévesque
+ *
+ * and licensed under the same license (Apache 2).
+ */
+class SeqIdH2Adapter extends H2Adapter {
+  override def supportsAutoIncrementInColumnDeclaration = false
+
+  override def postCreateTable(t: Table[_],
+    printSinkWhenWriteOnlyMode: Option[String => Unit]) = {
+
+    val autoIncrementedFields = t.posoMetaData.fieldsMetaData.filter(
+      _.isAutoIncremented)
+
+    for(fmd <-autoIncrementedFields) {
+      val sw = new StatementWriter(false, this)
+      sw.write("create sequence if not exists ", quoteName(fmd.sequenceName))
+
+      if(printSinkWhenWriteOnlyMode == None) {
+        val st = Session.currentSession.connection.createStatement
+        st.execute(sw.statement)
+      }
+      else
+        printSinkWhenWriteOnlyMode.get.apply(sw.statement + ";")
+    }
+  }
+
+  override def writeInsert[T](o: T, t: Table[T], sw: StatementWriter): Unit = {
+
+    val o_ = o.asInstanceOf[AnyRef]
+
+    val autoIncPK = t.posoMetaData.fieldsMetaData.find(fmd =>
+      fmd.isAutoIncremented)
+
+    if(autoIncPK == None) {
+      super.writeInsert(o, t, sw)
+      return
+    }
+
+    val f = t.posoMetaData.fieldsMetaData.filter(fmd => fmd != autoIncPK.get)
+
+    val colNames = List(autoIncPK.get) ::: f.toList
+    val colVals = List("nextval('" + quoteName(autoIncPK.get.sequenceName)
+      + "')") ::: f.map(fmd => writeValue(o_, fmd, sw)).toList
+
+    sw.write("insert into ");
+    sw.write(quoteName(t.prefixedName));
+    sw.write(" (");
+    sw.write(colNames.map(fmd => quoteName(fmd.columnName)).mkString(", "));
+    sw.write(") values ");
+    sw.write(colVals.mkString("(",",",")"));
+  }
+}
+
+// vim: set ts=2 sw=2 et:

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/scala/fis/base/model/SeqIdPostgreSqlAdapter.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -0,0 +1,58 @@
+/*
+ * Copyright 2011 Tomas Zeman <tzeman@volny.cz>
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package fis.base.model
+
+import org.squeryl.{Session, Table}
+import org.squeryl.adapters.PostgreSqlAdapter
+import org.squeryl.internals.StatementWriter
+import scala.collection.mutable.HashSet
+
+/**
+ * Postgres adapter which enables multiple entities to share the same
+ * sequence. 
+ *
+ * The implementation is based on Postgres adapter which is
+ *
+ *    Copyright 2010 Maxime Lévesque
+ *
+ * and licensed under the same license (Apache 2).
+ */
+class SeqIdPostgreSqlAdapter extends PostgreSqlAdapter {
+  private val seqs = new HashSet[String]
+
+  override def postCreateTable(t: Table[_],
+    printSinkWhenWriteOnlyMode: Option[String => Unit]) = {
+
+    val autoIncrementedFields = t.posoMetaData.fieldsMetaData.
+      filter(_.isAutoIncremented).
+      filter(f => !seqs.contains(f.sequenceName))
+
+    for(fmd <-autoIncrementedFields) {
+      val sw = new StatementWriter(false, this)
+      sw.write("create sequence ", quoteName(fmd.sequenceName))
+      seqs += fmd.sequenceName
+
+      if(printSinkWhenWriteOnlyMode == None) {
+        val st = Session.currentSession.connection.createStatement
+        st.execute(sw.statement)
+      }
+      else
+        printSinkWhenWriteOnlyMode.get.apply(sw.statement + ";")
+    }
+  }
+}
+
+// vim: set ts=2 sw=2 et:

--- a/src/test/scala/fis/base/model/CodeListSpec.scala	Fri Feb 10 09:53:04 2012 +0100
+++ b/src/test/scala/fis/base/model/CodeListSpec.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -25,13 +25,12 @@
   import net.liftweb.mapper.{DB, DefaultConnectionIdentifier, StandardDBVendor}
   import net.liftweb.squerylrecord.RecordTypeMode._
   import net.liftweb.squerylrecord.SquerylRecord
-  import org.squeryl.adapters.H2Adapter
   import TestBaseSchema._
 
   val dbVendor = new StandardDBVendor("org.h2.Driver",
     "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", Empty, Empty)
   DB.defineConnectionManager(DefaultConnectionIdentifier, dbVendor)
-  SquerylRecord.init(() => new H2Adapter)
+  SquerylRecord.init(() => new SeqIdH2Adapter)
 
   beforeAll {
     doInDB { dropAndCreate }
@@ -41,6 +40,7 @@
     doInDB {
       val cli1 = CodeListItem.createRecord.name("cli1").codeListType(1)
       codeListItems.insert(cli1)
+      cli1.id should equal (1)
       val l1 = from(codeListItems)(cli => select(cli)).toList
       l1.size should equal (1)
       val cl = new CodeListImpl(1, { i => i } )

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/test/scala/fis/base/model/SeqIdPostgreSqlAdapterSpec.scala	Fri Feb 10 09:53:04 2012 +0100
@@ -0,0 +1,106 @@
+/*
+ * Copyright 2011 Tomas Zeman <tzeman@volny.cz>
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package fis.base.model
+
+import net.liftweb.common._
+import org.scalatest._
+import org.scalatest.matchers.ShouldMatchers
+
+class SeqIdPostgreSqlAdapterSpec extends FlatSpec with ShouldMatchers
+  with Logger with BeforeAndAfterAllFunctions {
+
+  import java.sql.Connection
+  import net.liftweb.mapper.{DB, DefaultConnectionIdentifier}
+  import net.liftweb.squerylrecord.SquerylRecord
+  import net.tz.lift.util.StandardDBVendor
+  import org.squeryl.PrimitiveTypeMode._
+  import TestSchema._
+
+  val dbVendor = new StandardDBVendor("org.postgresql.Driver",
+    "jdbc:postgresql://localhost:7902/fis_test", Empty, Empty) {
+    val testQuery = "SELECT version()"
+    override protected def testConnection(c: Connection) = {
+      c.prepareStatement(testQuery).executeQuery
+    }
+  }
+  DB.defineConnectionManager(DefaultConnectionIdentifier, dbVendor)
+  SquerylRecord.init(() => new SeqIdPostgreSqlAdapter)
+
+  beforeAll {
+    //doInDB { dropAndCreate }
+  }
+
+  afterAll {
+    dbVendor.closeAllConnections_!
+  }
+
+  "PostgreSqlAdapter" should "create schema" in {
+    doInDB { dropAndCreate }
+  }
+
+  it should "create entities" in  {
+    val a1 = Author(0, "f1", "l1", None)
+    authors.insert(a1)
+    books.insert(Book(0, "b1", a1.id, None))
+  }
+
+  it should "use global sequence" in {
+    val cnt: Long = from(authors)(a => compute(count))
+    info("Authors count: " + cnt)
+    cnt should be (1)
+    authors.lookup(1L) should be ('defined)
+    val bookCnt: Long = from(books)(b => compute(count))
+    bookCnt should be (1)
+    val b = books.lookup(2L)
+    info("Book: " + b)
+    b should be ('defined)
+  }
+
+  def doInDB(block: => Any) {
+    DB.use(DefaultConnectionIdentifier) { c =>
+      block
+      c.commit
+    }
+  }
+}
+
+import org.squeryl.{KeyedEntity, Schema}
+import org.squeryl.PrimitiveTypeMode._
+
+case class Author(val id: Long, val firstName: String, val lastName: String,
+  val email: Option[String]) extends KeyedEntity[Long] {
+  def this() = this(0,"","",Some(""))      
+}
+
+case class Book(val id: Long, var title: String, var authorId: Long,
+  var coAuthorId: Option[Long]) extends KeyedEntity[Long] {
+  def this() = this(0,"",0,Some(0L))
+}
+
+object TestSchema extends Schema {
+  val authors = table[Author]
+  val books = table[Book]
+
+  def dropAndCreate {
+    drop
+    create
+  }
+
+  on(authors) { t => declare(t.id.is(autoIncremented("entity_id_seq"))) }
+  on(books)   { t => declare(t.id.is(autoIncremented("entity_id_seq"))) }
+}
+
+// vim: set ts=2 sw=2 et: