db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Migrating from Postgresql to Apache Derby
Date Thu, 06 Mar 2008 16:35:50 GMT
Forums @ Existanze wrote:
> Hello all,
> As the subject reads, my development team and I are trying to migrate 
> to apache derby, because
> we will start deploying our application with the database embedded, 
> instead of having the hassle
> of installing a database on each client. Following are some of my 
> question regarding this migration,
> that I haven't been able to find an answer to. I hope I don't tire you 
> with this long email.
> After reading the documentation and viewing various examples I have 
> been able to successfully
> migrate my "schema" to derby, I have been able to recreate all of our 
> tables on a derby database.
> Because of previous (now very apparent) incorrect decisions in relying 
> on some Postgresql specific
> functions, I have found myself at a dead end in completing this 
> migration process.
> In order to make up for some of the functionality I want to create 
> several functions, stored procedures and triggers that will do the job.
> Forgive me if I compare postgres and derby too much, but that is the 
> way that I know how to explain it.
> When I created a "BEFORE INSERT" trigger in postgres I was able to 
> modify any of the fields of the entry that was about to be added. In 
> derby I haven't found any way to do this.
> Using the following example:
> CREATE  TRIGGER set_proper_id
>    new_person.person_code = --some generated code
> I am under the assumption that by referencing the new entry I can 
> modified it before the constraint checks,
> at least that is what is said in the reference. Am I missing something 
> here? Is what I am trying to do just not possible?
> I understood most of the example which used AFTER UPDATE or AFTER 
> INSERT, but in those situations my constraints come into play on the 
> person table before anything gets inserted and everything breaks.
> Is the trigger function supposed to return a value? In postgresql it 
> was clearly defined that we should return the entry that was going to 
> be inserted. It was very convenient that I didn't have to create the 
> whole record just to change a single field and return the modified 
> entry,  I hope that is not the case here because the person table has 
> too many fields.
> When I create a table, is it possible to specify the default value of 
> a column to be the result of a user_function?
> For example
>    test_id integer not null generated always as identity(start with 1, 
> increment by 1) primary key,
>    test_entry_code VARCHAR(10) NOT NULL DEFAULT custom_function()
> )
Hi Fotis,

Today in Derby, the DEFAULT clause can only specify a constant value. 
This is described in the Derby Reference Guide 
(http://db.apache.org/derby/docs/10.3/ref/) in a section titled 

What you want to do could be accomplished with a SQL feature called 
Generated Columns, briefly described by DERBY-481. I am hoping this 
feature will get some attention soon--but, of course, that does not help 
you today.

Here's a workaround which other people have found useful:

1) Call custom_function() in your application code everywhere that you 
insert or update the affected column.

2) Add a CHECK constraint to the column to sanity-check that you haven't 
missed any places in your code where the column is touched. Like so:

   test_id integer not null generated always as identity(start with 1, 
increment by 1) primary key,
   test_entry_code VARCHAR(10) NOT NULL CHECK ( test_entry_code = 
custom_function() )

Hope this helps,
> Well, that is it for now, I hope someone answers or at least sheds 
> some light
> Best Regards,
> Fotis

View raw message