Rails - Sep 2005 - DRY for ActiveRecord w/PostgreSQL : defaults and validations

As I''ve already posted some ideas to this list
(see http://article.gmane.org/gmane.comp.lang.ruby.rails/22231),
  here''s continuation.

I think, that properly developed DB schema has to include all
(or almost all) constraints and validation rules for our particular 
domain data model.
Rails gives very handy and useful ways to support various callbacks and 
validations on data model objects, but as for me, some points and 
solutions are neat when business rules and data integrity are held 
rather in application code than in DBMS (as seen in many PHP+MySQL 
apps). Part of such issues can be more effectively handled on DBMS 
server side (I mean triggers, proper referential integrity support, 
stored procedures etc.).

In PostgreSQL practically full information about schema, intertable
relationships and constraints can be extracted and injected to Rails app.

Why to repeat yourself? If data constraints are already in schema,
why not query DB to validate data before insert or update?
Why not query DB for record defaults defined in schema?
Why not retrieve associations defined by foreign keys from schema and 
inject them?

So, here are my ideas.
---------------
1. ActiveRecord::Base.default_new - creates record, filled with 
defaults, defined in DB.
It can be done in such manner:
         rows=exec(DEFAULTS_query) # see queries below
         new_record=find_by_sql("SELECT #{rows.join('','')}
")

Benefits:
         * no need to convert db defaults to Ruby expr,
         * values even for complex defaults with functions calls can be 
easily retrieved
         * autogenerated serial ids come from db

--------------
2. Validation on INSERT OR UPDATE directly against database table 
constraints:
         a) value type and NOT NULL constraints can be checked in app 
from column definitions (and already are?)
         b) char(n)/varchar(n) size violations can be checked in app, as 
part of type check when setting attr value
         c) CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY constraints can be 
checked against "VIRTUAL" row, see below

Example:
CREATE TABLE Groups (
         id serial primary key,
	name	varchar(200) not null
);
INSERT INTO GROUPS VALUES(12);
CREATE TABLE Users (
         id serial,
         login varchar(64) not null,
         group_id integer not null,
         birth_date date,
         constraint pkey_user primary key (id),
         constraint fkey_user_group foreign key (group_id) references 
groups (id) on delete cascade,
         constraint uniq_user_login unique(login),
         constraint chek_user_age   check (birth_date + ''18 
years''::interval < now())
);

Let new record be (100, ''smith'', 12,
''1986-10-30'')

It can be checked against table constraints before really doing insert.
Step 1. Construct SQL query t represent a virtual record:
SELECT
         100::integer as id,
         ''smith''::character varying(64) as login,
         12::integer as  group_id,
         ''1986-10-30''::date as birth_date

Step 2. Construct predicate list query to check constraints:
SELECT
         (birth_date + ''18 years''::interval < now()) as
VALID_chek_user_age,
         NOT EXISTS (SELECT 1 FROM users u WHERE u.login = virt.login) 
as VALID_uniq_user_login,
         EXISTS (SELECT 1 FROM groups g WHERE g.id = virt.group_id) as 
VALID_user_group,
         NOT EXISTS (SELECT 1 FROM users u WHERE u.id = virt.id) as 
VALID_pkey_user

Step 3. Joining above two :)
SELECT
         -- CHECK constraint
         (birth_date + ''18 years''::interval < now()) as
VALID_chek_user_age,
         -- UNIQUE constraint
         NOT EXISTS (SELECT 1 FROM users u WHERE u.login = virt.login) 
as VALID_uniq_user_login,
         -- FOREIGN KEY constraint
         EXISTS (SELECT 1 FROM groups g WHERE g.id = virt.group_id) as 
VALID_user_group,
         -- PRIMARY KEY constraint
         NOT EXISTS (SELECT 1 FROM users u WHERE u.id = virt.id) as 
VALID_pkey_user
FROM
         (SELECT
                 100::integer as id,
                 ''smith''::character varying(64) as login,
                 12::integer as  group_id,
                 ''1986-10-30''::date as birth_date
         ) as VIRT;

So, we can check if record is valid and display all possible flaws that 
will lead
DB to exception when doing INSERT.

Well, for UPDATE, some changes needed:
         1. predicate for PRIMARY KEY constraint is changed to EXISTS.
         2. UNIQUE constraints will look smth like this:
         NOT EXISTS (SELECT 1 FROM users u WHERE u.login = virt.login
                 AND u.id <> virt.id)
         3. FOREIGN and CHECK w/o changes.
A possible special cases here are excluding BYTEA fields and FKEY checks 
where
fields with permitted NULL occur.

In Rails model code it could look like this:
class User < ActiveRecord::Base
         set_new_db_defaults true # see first idea on defaults

         # validate_against_db (when, constraint_list, messages)
         validate_against_db  :create, :all,
                 [:pkey_user => ''Already exists!'',
                  :uniq_user_login => ''Login is already
used!'',
                  :fkey_user_group => ''Cannot add user to
nonexistent
group'',
                  :chek_user_age   => ''Too young to die!'' ]
end

---------------------

3.REF_FROM, REF_TO associations.

Information about foreign key constraints can be used to generate 
scaffold models with ref_from, ref_to associations that can be then 
easily converted to more semantic has_one, has_many, belongs_to, 
has_and_belongs_to_many.
A more detailed study may show that it can be possible to fish them out 
directly?

---Basic Schema investigation queries:---

COLUMN DEFINITIONS:
         select  attname as name,
                 format_type(atttypid, atttypmod) as sql_type,
                 attnotnull as not_null,
                 pg_get_expr(adbin, adrelid) as default_def
         from pg_attribute left join pg_attrdef on (attrelid = adrelid 
and adnum=attnum)
         where attrelid = :TABLE_NAME ::regclass and not attisdropped 
and attnum>0;

DEFAULTS:
         select  pg_get_expr(adbin, adrelid) || ''::'' || 
format_type(atttypid, atttypmod)
                 || '' as '' || quote_ident(attname) as def_v
         from pg_attribute left  join pg_attrdef on (attrelid = adrelid 
and adnum=attnum)
         where attrelid = :TABLE_NAME ::regclass and not attisdropped 
and attnum>0 and atthasdef

TABLE CONSTRAINTS:
         select conname, contype, pg_get_constraintdef(oid) as condef
         from pg_constraint where conrelid = :TABLE_NAME ::regclass;

FOREIGN KEYS:
         select  conname, contype, pg_get_constraintdef(oid) as condef,
                 (select relname from pg_class c1 where c1.oid = 
conrelid) as ref_from_table,
                 (select relname from pg_class c2 where c2.oid = 
confrelid) as ref_to_table
         from pg_constraint where conrelid = :TABLE_NAME ::regclass
	or confrelid = :TABLE_NAME :: regclass
         and contype = ''f'';

------------------------------------

Probably, if this list isn''t the proper place to this post, suggest 
another one  :)

>
>
> In PostgreSQL practically full information about schema, intertable
> relationships and constraints can be extracted and injected to Rails app.

What about other RDBMSs that rails supports?
>
> Why to repeat yourself? If data constraints are already in schema,
> why not query DB to validate data before insert or update?
> Why not query DB for record defaults defined in schema?
> Why not retrieve associations defined by foreign keys from schema and 
> inject them?
[snip ideas]

I agree with the DRY principle, but I''m thinking that if you do a query
to check the constraints for every insert, then you''ll be bogging the 
database down with two calls for every insert instead of 1 call.  Just 
something to consider.  Although the fact that the schema does define 
the constraints should mean that the developer shouldn''t have to repeat
these in the model code.

Kev

Kev Jackson wrote:
>> In PostgreSQL practically full information about schema, intertable
>> relationships and constraints can be extracted and injected to Rails
app.
> 
> What about other RDBMSs that rails supports?
> 
Well, I don''t have much experience with other SQL DBMS, but SQL
standard
includes "information schema", so it can be possible with other DBMS 
also, if the feature is properly supported.
> 
> I agree with the DRY principle, but I''m thinking that if you do a
query
> to check the constraints for every insert, then you''ll be bogging
the
> database down with two calls for every insert instead of 1 call.  Just 
> something to consider.  Although the fact that the schema does define 
> the constraints should mean that the developer shouldn''t have to
repeat
> these in the model code.
> 
> Kev
As for me it''s better to make one more constraint check query to 
database than handling weird exception messages (or even exposing them 
to end users).
Though, if one doesn''t care neither about data integrity(primary
reason)
nor friendly UI(keep users with this software), validations aren''t 
necessary at all and can be avoided :)

WBR, Lugovoi Nikolai

On Monday 19 September 2005 11:39, Lugovoi Nikolai
wrote:
> As for me it''s better to make one more constraint check query to
> database than handling weird exception messages (or even exposing
> them to end users).
Before reinventing anything, have a look at

  http://dev.rubyonrails.org/ticket/861

Michael

-- 
Michael Schuerig               Most people would rather die than think.
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                          
In fact, they do.
http://www.schuerig.de/michael/                      --Bertrand Russell

Michael Schuerig wrote:
> On Monday 19 September 2005 11:39, Lugovoi Nikolai wrote:
> 
>>As for me it''s better to make one more constraint check query
to
>>database than handling weird exception messages (or even exposing
>>them to end users).
> 
> 
> Before reinventing anything, have a look at
> 
>   http://dev.rubyonrails.org/ticket/861
> 
> Michael
> 
Tnahks, but JS client-side validation can be bypassed, and server-side 
then is also required. And after all there are still schema constraints 
that DBMS has to check :)

I wouldn''t rely on JS client-side validation, though it can be more
user
friendly and use less traffic.

WBR, Lugovoi Nikolai

Nicholas Van Weerdenburg wrote:
> There has been a few long threads on the philosophy of dbms
> constraints versus application-logic constraints. IIRC correctly,
> application-logic validation was the philosophical direction, being
> DHH''s preference.
> 
> Part of the influence on this was, I think, MySQL''s only rather
recent
> support of constraints in the DBMS.
> 
> The other was that an OO domain model can be hard to constrain in a
> RDMS constraint model.
> 
> Nick
My view on this (for what it''s worth) is that DBMS constraints should
be
used if they''re available, but that it should be possible to put them
in
the application if not - ideally, this should be an transparent
fall-back, but I can see that breaking in lots of nasty ways.  My
reasoning is that the DBMS constraint code is much more mature (in
general), and therefore likely to be far better tested, than new
application-level code (with no disrespect intended to its authors,
naturally).  It''s also likely to be more efficient, but that''s
a
tangential issue.

Going the other way, it would be *extremely* nice if the DB wrappers
could push AR-defined constraints down to the DB level if it detected
that they were available (ALTER TABLE... et al), but signal back that
they were not if necessary, so that AR could handle it at the
application level for those running on a feature-poor backend.

Am I totally off-base here?  I can''t help but think that being able to
say :dependent in AR is far clearer than having to say ''FOREIGN
KEY...''
in SQL, in part because you''re then defining the cascade in the same
place as the relationship itself, and with a possible push-down from the
wrapper layer, you get the best of both worlds.

Thoughts?  Comments?  Rotten fruit?  Pointers for where this might be
patchable-in?

-- 
Alex

On 9/19/05, Lugovoi Nikolai
<meadow.nnick-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Michael Schuerig wrote:
> > On Monday 19 September 2005 11:39, Lugovoi Nikolai wrote:
> >
> >>As for me it''s better to make one more constraint check
query to
> >>database than handling weird exception messages (or even exposing
> >>them to end users).
> >
> >
> > Before reinventing anything, have a look at
> >
> >   http://dev.rubyonrails.org/ticket/861
> >
> > Michael
> >
> 
> Tnahks, but JS client-side validation can be bypassed, and server-side
> then is also required. And after all there are still schema constraints
> that DBMS has to check :)
> 
> I wouldn''t rely on JS client-side validation, though it can be
more user
> friendly and use less traffic.
> 
> WBR, Lugovoi Nikolai
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
There has been a few long threads on the philosophy of dbms
constraints versus application-logic constraints. IIRC correctly,
application-logic validation was the philosophical direction, being
DHH''s preference.

Part of the influence on this was, I think, MySQL''s only rather recent
support of constraints in the DBMS.

The other was that an OO domain model can be hard to constrain in a
RDMS constraint model.

Nick
-- 
Nicholas Van Weerdenburg

>
>
>
>There has been a few long threads on the philosophy of dbms
>constraints versus application-logic constraints. IIRC correctly,
>application-logic validation was the philosophical direction, being
>DHH''s preference.
>
>Part of the influence on this was, I think, MySQL''s only rather
recent
>support of constraints in the DBMS.
>
>The other was that an OO domain model can be hard to constrain in a
>RDMS constraint model.
>
>Nick
>  
>
That said, would it make sense, when generating a model, to place any 
database constraints and relationships in the model?   not 
null=validates presense of, char(2)= validates length of, foriegn 
key=belongs to, etc.

Lugovoi Nikolai wrote:
> Basically, there are two choices where to put business logic and how
> much of it will go to DB or app code (in any case, portions will be
> present in both)
But we should minimise the amount in one, and maximise it in the other...
> So, if one''s model is primarily an Object-Oriented one,
constraints are
> defined and handled by classes and methods, and if data model grounds
> mainly, say, on relational theory, constraints are defined and handled
> by RDBMS (yes, we don''t yet have one, only SQL DBMS ;-( ).
That''s what I''m saying, really.  Given that AR requires that
we put some
model definition in ruby, why not have the option to put it all there?
That way we can compensate for dodgy back-ends when we have to, without
endangering the data.
> My view of validations - keep software more fool-proof (but that''s
the
> primary goal of constraint) and more luser-friendly.
I find that the latter comes through clarity and consistency of expression.
> Clearer? Maybe, as for developer.
> More reliable? I doubt, unless such constraints are pushed from AR to
> DB. 
That''s exactly what I''m proposing, although I can now see a
slight
hitch:  What should happen when there''s a constraint already defined on
the schema, and the AR model has a conflicting definition?  My instinct
says to fail hard and as early as possible under those circumstances.
> And, as for me, it''s rather  scary to develop serious apps against
poor
> DBMS, though even SQLite can be appropriate sometimes.
I couldn''t agree more.  I''d like to be able to develop on a
rock-solid
platform, but let the user be able to run a shoddy back-end if their
circumstances dictate it, and suffer as little as possible for it.

To take a very pertinent example, I''m currently developing a PHP site
that''s going to be using MySQL 4.0.17 because the host that my client
uses refuse to upgrade, and they refuse to go with a different host.  I
can''t use InnoDB tables throughout for a number of reasons, which means
transactions and foreign key constraints can''t be reliably supported by
the DB.  Does this mean they shouldn''t have them?  Hell no!

-- 
Alex

On Monday 19 September 2005 15:38, Grant Johnson wrote:
> That said, would it make sense, when generating a model, to place any
> database constraints and relationships in the model?   not
> null=validates presense of, char(2)= validates length of, foriegn
> key=belongs to, etc.
For what it''s worth, that''s what I''m doing in
BoilerPlate[*] for
validates_presence_of and validates_length_of

Michael

[*] http://www.schuerig.de/michael/boilerplate/

-- 
Michael Schuerig                 Nothing is as brilliantly adaptive
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org       as selective
stupidity.
http://www.schuerig.de/michael/    --A.O. Rorty, The Deceptive Self

Alex Young wrote:
 > Going the other way, it would be *extremely* nice if the DB wrappers
 > could push AR-defined constraints down to the DB level if it detected
 > that they were available (ALTER TABLE... et al), but signal back that
 > they were not if necessary, so that AR could handle it at the
 > application level for those running on a feature-poor backend.
 >

Basically, there are two choices where to put business logic and how 
much of it will go to DB or app code (in any case, portions will be 
present in both), and the answer to "So, what''s right, what to
choose?"
is simple: "it depends" :)

Agile tools could provide options to inject constraints from DB schema 
to ORM-based model and vice versa, and developer can choose where to 
take the gun to shoot himself.

The rule is simple: for one application there has to be single data 
model, and therefore single place to put constraints (if your model 
isn''t constrained, you must be The God or an ignorant idiot :).

So, if one''s model is primarily an Object-Oriented one, constraints are
defined and handled by classes and methods, and if data model grounds 
mainly, say, on relational theory, constraints are defined and handled 
by RDBMS (yes, we don''t yet have one, only SQL DBMS ;-( ).

My view of validations - keep software more fool-proof (but that''s the 
primary goal of constraint) and more luser-friendly.
> Am I totally off-base here?  I can''t help but think that being
able to
> say :dependent in AR is far clearer than having to say ''FOREIGN
KEY...''
> in SQL, in part because you''re then defining the cascade in the
same
> place as the relationship itself, and with a possible push-down from the
> wrapper layer, you get the best of both worlds.
> 
Clearer? Maybe, as for developer.
More reliable? I doubt, unless such constraints are pushed from AR to 
DB. Data survives apps and often is reused through different apps, 
unless one isn''t aware.

And, as for me, it''s rather  scary to develop serious apps against poor
DBMS, though even SQLite can be appropriate sometimes.

WBR, Lugovoi Nikolai

Grant Johnson wrote:
> That said, would it make sense, when generating a model, to place any 
> database constraints and relationships in the model?   not 
> null=validates presense of, char(2)= validates length of, foriegn 
> key=belongs to, etc.
well, i think it can be easily done w/o manually written additional code 
in your model.

WBR, Lugovoi Nikolai