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 Fri, 07 Mar 2008 15:44:22 GMT

Hello again,

I am going to top post on this one.

To be totally honest the  trigger question came about for two reasons. 
The first is that we used a single sequence to
provide primary integers to three different tables in postgresql.

As far as I know this is not possible in derby. So I though of creating 
a before insert trigger which will modify the value of
the entry column  before being inserted. The value could be generated a 
function which just increments a number on some
table each time, like a pseudo sequence. This trigger is then added all 
three tables.


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


The second reason is that we have a specific way of creating a "code" 
which gets attached to each person entry in the
table. For example, the code can be , the COUNT(*) + the month+ the 
year+ the COUNT in month, which will give a 10
digit number we use. In postgres we have a before insert trigger that 
creates this number, sets the value of the column
and the row gets inserted.

I am currently looking at hsqldb to see if I can overcome these 
postgresql only obstacles.


Thank you very much though, for all your help

Fotis

Rick Hillegas wrote:
> 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