Rails - Sep 2005 - AWD with Rails : comments and questions on ActiveRecord

I''ve started playing with Ruby on Rails and find it exciting, it is a
really cool woodoo :)

But while reading "Agile Web Development on Rails" I found some
unclear and
probably not too correct statements in ActiveRecord chapters 14, 15.
Some are probably book errors and others apparently came from authors''
MySQL practice. (my DBMS of choice is PostgreSQL)

So notes follow (I refer to P1.0 PDF book version). If anyone has 
explanation or advice, share your knowledge :)

1. BASICS
>[p.190]
>Active Record closely follows the standard ORM model: tables map to
>classes, rows to objects, and columns to object attributes.
>
I used several ORM libraries for Java and ActiveRecord is far more simpler
to use. However, it''s more convenient for me to consider ActiveRecord
as
very handy Relation-to-Object mapping.

2. PRIMARY KEYS AND IDS

I would debate that autoincremented id fields are the best choice for 
primary key.
They aren''t the best. They are just useful. Developing SQL schema for
tables
I follow
simple rule - unique constraint for every table are indeed necessary, 
surrogate
keys are only practically useful.

And one more question is - why there is no support for multicolumn PK ?
(Though that is common issue with all ORM libraries :)

3. UPDATES
>[p.211 Updating Existing Rows]
>If this object had previously been read from the database, this save will
>update the existing row; otherwise, the save will insert a new row.
>If an existing row is updated, Active Record will use its primary key 
column
>to match it with the in-memory object. The attributes contained in
>the Active Record object dete mine the columns that will be updated - a
>column will be updated in the database even if its value has not changed.
>
>[p.212]
>The update() method takes an id parameter and a set of attributes. It 
fetches
>the corresponding row, updates the given attributes, saves the result back
>to the database, and returns the model object.
>...
>You can pass update() an array of ids and an array of attribute value
>hashes, and it will update all the corresponding rows in the database,
>returning an array of model objects.
>
What''s the reason for that? Yes, the underlying code in
ActiveRecord::Base
is straitforward:

obj = Model.find(id)
obj.attributes = attributes
obj.save
# this leads to
connection.execute(
"UPDATE #{self.class.table_name} " +
"SET #{quoted_comma_pair_list(connection, attributes_with_quotes(false))}
"
+
"WHERE #{self.class.primary_key} = #{quote(id)}" )
But why not UPDATE via SQL first and find then?



4. FOREIGN KEYS AND ASSOCIATIONS
>[p.217]
>It''s worth noting that it isn''t the foreign key
constraints that set up the
>relationships. These are just hints to the database that it should check
>that the values in the columns reference known keys in the target tables.
>The DBMS is free to ignore these constraints (and some versions of MySQL
>do). The intertable relationships are set up simply because the developer
>chooses to populate the columns product_id and order_id with key values
>from the products and orders table.
>
(IMHO)
It looks like some(all) DBMS vendors are ignorant of relational theory.
A RDBMS not *should* but *ALWAYS HAS TO* check foreign keys.
These aren''t "just hints" but strong requirements.
If it doesn''t, it''s not reliable, it couldn''t be
called Relational DB
Management System.
(i''m a bit infected with dbdebunk.com <http://dbdebunk.com> :)
>[p.217, p.218]
>create table line_items (
> id int not null auto_increment,
> product_id int not null,
> order_id int not null,
> quantity int not null default 0,
> unit_price float(10,2) not null,
> constraint fk_items_product foreign key (product_id) references 
products(id),
> constraint fk_items_order foreign key (order_id) references orders(id),
> primary key (id)
>);
>
>....
>
>Looking at this DDL, we can see why it''s hard for Active Record to
divine
>the relationships between tables automatically. The orders and products
>foreign key references in the line_items table look identical. However, the
>product_id column is used to associate a line item with exactly one 
product.
>The order_id column is used to associate multiple line items with a single
>order. The line item is part of the order but references the product.
>
First, I''d say that table line_items is missing an unique constraint
on pair (order_id, product_id).

Consider another view:
LineItem refers to one Order, and also refers to one Product.
Order is referenced from many LineItems and Product is referenced
from many LineItems.

It could be useful to have constructs like
LineItem ref_to_one :product
Order ref_from_many :line_items
User ref_from_one :user_profile

Yes, belongs_to, has_one and has_many are more semantic and convenient, but
forementioned ref_* constructs can be reverse engineered from database 
schema.
4.1 HAS-ONE
>[p.220,p.221]
>If there is already an existing child object when you assign a new object
>to a has_one association, that existing object will be updated to remove
>its foreign key association with the parent row (the foreign key will be
>set to zero).
>
Why set to zero? Why not to -1 or 123456789?
That''s probably a book error, as code shows:

@target[@association_class_primary_key_name] = nil

4.2 HAS-MANY
>[p.226]
>has_one and has_many both support the :dependent option. This tells Rails
>to destroy dependent rows in the child table when you destroy a row in the
>parent table. This works by traversing the child table, calling destroy( ) 
on
>each row with a foreign key referencing the row being deleted in the parent
>table.
>
Actually, this is a duty of RDBMS - FOREIGN KEY ON DELETE CASCADE.
That has to be handled by DBMS, not application.
>[p.228]
>orders.delete(order1, ...)
> Deletes one or more order objects from the list of orders associated
> with this customer. This does not delete the order objects from the
> database--it simply sets their customer_id foreign keys to null, breaking
> their association.
>
>orders.clear
> Disassociates all orders from this customer. Like delete( ), this breaks
> the association but deletes the orders from the database only if they
> were marked as :dependent.
>
Actually, in class HasManyAssociation I see method clear() that simply sets 
foreign
key to NULL in association table. No check of :dependent ! A bug?

But delete() method checks it and calls destroy() for each object if 
:dependent is
set and sets NULL''s otherwise.

Book error or design issue/bug ? I think both :-)

A note on :dependent flag. A reverse engineering of db schema can show if
references are dependent - not null constraints and ON DELETE CASCADE are
defined for foreign key.

4.3. MANY-TO-MANY
>[p.230]
>Within the database, many-to-many associations are implemented using
>an intermediate join table. This contains foreign key pairs linking the
>two target tables.
>
What about join tables having three or more foreign keys?
Would you advise to create a surrogate id key for such tables and add a 
model? :-)

5. Counters
>[p.236]
>In the belongs_to declaration in the child model
>you can ask Active Record to maintain a count of the number of associated
>children in the parent table rows. This count will be automatically
>maintained--if you add a child row, the count in the parent row will be
>incremented, and if you delete a child row, it will be decremented.
>
No thanks, for such rare cases I''d better use only triggers.
That''s much
more reliable.

6. TRANSACTIONS

My usage pattern is "transaction per action". i.e. in action
dispatcher:

connection.setAutoCommit(false);
try {
[dispatch to action handling]
}catch( Exception e){
[exception handling]
}finally{
connection.commit();
}

How can I achieve that using Rails?

7. VALIDATIONS

My notes:
a) validation rules *MUST BE* part of database schema constraints, 
well-designed
schema needs only DBMS to handle data integrity, without any outer
"helper"
app.

b) validation code in model classes is indeed just for convenient 
user-friendly
error reporting, also rejecting invalid rows before sending request to DB.

c) if validation rules are part of db schema, they can be reverse engineered
(probably not all, but simple almost certainly) and injected into model,
thus reducing validation code in model classes.


I think about creating more "intellectual" model generator (probably
only
for PostgreSQL), that would extract constraints and foreign key information
from schema. DRY principle rocks!

P.S. Sorry for my non-native English and long posting. :)


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

I too have come up against the point you raised with :dependent - it
is slighty strange behavior from the perspective of someone who likes
to put as much constraint in the DB as possible. Also, an extended AR
that introspected some of PostgreSQL''s information on constraints
would be very beneficial, I agree.

A well thought out post!

Cheers,
Dan