db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Forums @ Existanze" <for...@existanze.com>
Subject Re: Migrating from Postgresql to Apache Derby
Date Thu, 06 Mar 2008 16:40:46 GMT
Rick Hillegas wrote:
> 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
>> NO CASCADE BEFORE INSERT ON person
>> REFERENCING NEW AS new_person
>> FOR EACH ROW MODE DB2SQL
>>    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
>>
>> CREATE TABLE test(
>>    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,
>
Hello rick, thanks for taking the time to answer,
> 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 
> "column-definition".
>
> 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:
>
> CREATE TABLE test(
>   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,
> -Rick
This is way too much work, and I will probably need to find some other 
solution, but thank you for you suggestion. Any word on the trigger side?

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


Mime
View raw message