Hi,
I’m new to RoR and exploring its use with an existing application. One
thing I can’t seem to find much detail on is using database triggers.
All RoR examples and tutorials I’ve seen are very basic saving data back
to a single table. My use case includes a somewhat denormalized DB that
uses triggers to store data in multiple tables on occassion. I
currently use IBM DB2 9.
Can anyone explain how to use DB triggers in a complimentary fashion
with RoR? Or, I’m assuming that because I could not find examples of how
to work with triggers, the more elegant RoR model-based approach on how
to go about this?
As an example. I have a table called “new_books”, where a user can
enter a new book through a form. This new book must be validated by an
admin before it can be moved to the (validated) “book” table. Normally,
I would use a trigger.
Another example would be collecting data from a web from where the
majority is stored in its appropriate DB table but a few items should
also be stored in one or more of my denormalized tables as needed for
performance reasons. Again, normally I would use a trigger to
accomplish this.
How would one go about achieving these tasks with RoR without triggers?
Thanks in advance for any advice.
-Shim
Brad,
I’m going through the same types of questions : however my background is
DB2-centric.
I’ve got a couple of comments to make that hopefully are helpful.
Your first issue, with books, would probably be helped by a change to
your
table design. “New Books” and “Validated Books” aren’t two separate
entities (objects) but different states of the one object. You should
probably have a column for “book status” (set initially to “Needs
Validation”). When the admin validates the book you’d then have a very
simple validated() method to update this status column. You can create
separate methods in your controller (or model ?) to retrieve only
validated
books or books awaiting validation for display. In the RoR way, you’d
just
add the condition to the find method. An alternative would be to
create
views over the table which simulate the tables you originally had.
As far as DB2 is concerned, it is fairly uncommon now to need to
denormalize
for performance reasons. Much better to tune the database a little bit
by
adding appropriate indexes. Look at Design Advisor if you need help
with
this (or give me a call ).
To comment on the trigger question, it is still my opinion that you
should
continue to use triggers for certain things : especially where these
actions
are mandatory across all uses of the table. Validation and maintaining
audit trails are two very good examples. But coming from a database
background I probably am biased. If these things are not coded at the
database level then someone accessing the database outside your
application
(e.g. from a command line) wouldn’t invoke them.
I believe there needs to be enhancements to RoR / ActiveRecord to allow
it to
“play nice” with database encoded rules. It looks like “Dr Nic’s Magic
Models” is going some way at least towards this, but I think there is
more to
do. The database adaptors should be able to be used to feed back a
lot of
information into the RoR models which would eliminate a lot of
duplication.
For example using your foreign keys, check constraints etc to drive
validation.
I think there also needs to be more effort to support legacy database
designs
and designs which use natural keys. At the moment I’m working on a
system
which uses the standard ISO country definitions. It is totally
pointless to
generate an integer ID as well as the two character country code. But
you
have to jump through too many hoops to make the two character code your
primary key. Even worse if you want to use a composite natural key :
although again there is a gem that is supposed to help with this (I’ve
still
to look at that).
HTH
Phil Nelson
[email protected]
Unfortunately, my scenario is a bit more complicated. The “New Books”
table has multiple columns that are used in the validation stages that
aren’t in the “Books” table…No point in diving deeply into the DDL
but a single bit column doesn’t make sense in this scenario…
Same thing goes for the other scenario. My application has some
significant OLAP needs that absolutely require some
denormalization…otherwise, it would require far too many table joins
and horsepower. Indexes aren’t the answer. It is significantly more
efficient to denormalize some data into a single table.
Let me simplify my question. Let’s say I have a form with values X,Y,Z
that are saved into table A. I also need value X and Y in table B. How
does one accomplish this in RoR without using triggers?
Thanks
Perfect.
Thanks for the feedback Dave. That’s excactly what I needed to know.
On 19/08/06, Brad R. [email protected] wrote:
…
Let me simplify my question. Let’s say I have a form with values X,Y,Z
that are saved into table A. I also need value X and Y in table B. How
does one accomplish this in RoR without using triggers?
In your controller, once the user has entered data into your form:
def save_form_data
a = A.create(:field1 => X, :field2 => Y, :field3 => Z).save
b = B.create(:field4 => X, :field5 => Y).save
end
where A is a model referencing your ‘table A’, and B is a model
referencing your ‘table B’.
Regards
Dave M.