Lift Mapper support for Oracle Sequences

[AnthonyW]

For Oracle database tables, one typically creates a Sequence to
provide primary key values.  For example:

create table WIDGET (
    id Number(8,0) not null,
    name varchar2(50) not null,
    ...
    constraint WIDGET_PK primary key (id),
    ...
)
create sequence WIDGET_ID_SEQ

When creating records using Mapper, is there a convenient way to get
Mapper to automatically leverage the corresponding sequence to
populate the id column?

(Why Oracle doesn't do this automatically in 2009 like SQL Server,
Sybase, and other database systems, is beyond me...)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to liftweb@...
To unsubscribe from this group, send email to liftweb+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en
-~----------~----~----~----~------~----~------~--~---

[Derek Chen-Becker-4]

The code in 1.1-SNAPSHOT already uses sequences for Oracle. Here's the relevant code:

  override def primaryKeySetup(tableName : String, columnName : String) : List[String] = {
    /*
     * This trigger and sequence setup is taken from http://www.databaseanswers.org/sql_scripts/ora_sequence.htm
     */
    super.primaryKeySetup(tableName, columnName) :::
    List("CREATE SEQUENCE " + tableName + "_sequence START WITH 1 INCREMENT BY 1",
         "CREATE OR REPLACE TRIGGER " + tableName + "_trigger BEFORE INSERT ON " + tableName + " " +
         "FOR EACH ROW " +
         "WHEN (new." + columnName + " is null) " +
         "BEGIN " +
         "SELECT " + tableName + "_sequence.nextval INTO :new." + columnName + " FROM DUAL; " +
         "END;")
  }

Are you running into an issue with it?

Derek

On Tue, Nov 10, 2009 at 1:46 AM, aw <anthony@...> wrote:

For Oracle database tables, one typically creates a Sequence to
provide primary key values.  For example:

create table WIDGET (
   id Number(8,0) not null,
   name varchar2(50) not null,
   ...
   constraint WIDGET_PK primary key (id),
   ...
)
create sequence WIDGET_ID_SEQ

When creating records using Mapper, is there a convenient way to get
Mapper to automatically leverage the corresponding sequence to
populate the id column?

(Why Oracle doesn't do this automatically in 2009 like SQL Server,
Sybase, and other database systems, is beyond me...)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to liftweb@...
To unsubscribe from this group, send email to liftweb+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en
-~----------~----~----~----~------~----~------~--~---

[AnthonyW]

Thank you Derek...  Clever trick!

I'm not using 1.1-SNAPSHOT -- I am using 1.1-M7...  But that shouldn't
matter to me since I am not using Schemifier to create and manage the
database...  (I am using LiquiBase.)

I had imagined an alternative strategy whereby I would provide a
sequence name to the id object and then it would simply create
statements like:

  insert into Table (id, colA, colB, colC) values
(Table_Sequence.NextVal, colA, colB, colC)

but the more I think about it...  I'm thinking your strategy insulates
my code from declaring anything Oracle specific in my mapper
objects...

I will give this a go.  Thanks!

--

You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to liftweb@....
To unsubscribe from this group, send email to liftweb+unsubscribe@....
For more options, visit this group at http://groups.google.com/group/liftweb?hl=.

[Derek Chen-Becker-4]

I think that the change was committed pre-M7, so you should have it.

On Thu, Nov 19, 2009 at 12:12 AM, aw <anthony@...> wrote:

Thank you Derek...  Clever trick!

I'm not using 1.1-SNAPSHOT -- I am using 1.1-M7...  But that shouldn't
matter to me since I am not using Schemifier to create and manage the
database...  (I am using LiquiBase.)

I had imagined an alternative strategy whereby I would provide a
sequence name to the id object and then it would simply create
statements like:

 insert into Table (id, colA, colB, colC) values
(Table_Sequence.NextVal, colA, colB, colC)

but the more I think about it...  I'm thinking your strategy insulates
my code from declaring anything Oracle specific in my mapper
objects...

I will give this a go.  Thanks!

--

You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to liftweb@....
To unsubscribe from this group, send email to liftweb%2Bunsubscribe@....

For more options, visit this group at http://groups.google.com/group/liftweb?hl=.

--

You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to liftweb@....
To unsubscribe from this group, send email to liftweb+unsubscribe@....
For more options, visit this group at http://groups.google.com/group/liftweb?hl=.