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 Fri, 07 Mar 2008 14:08:14 GMT
Forums @ Existanze wrote:
> 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?
Hi Fotis,

I am not sure I understand what you are trying to do with the trigger. 
Please bear with me because I may be garbling your question. It sounds 
as though you may be trying to use triggers to do what a generated 
column would do for you. That is, you want to use a trigger to fill a 
column with the result of an expression computed out of other columns in 
the row. I don't see  how you can do this with an INSERT trigger. As 
you've discovered, you can do this with an AFTER trigger.

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


Mime
View raw message