Re: multimaster

On 6/3/07, Ian Harding <harding(dot)ian(at)gmail(dot)com> wrote:
> Rails propaganda explicitly proposes not "repeating yourself" and
[...]
> The creator of RoR explicitly discourages use of RI, rules, triggers,
> etc in the database as unnecessary. His disciples take this as
> gospel.

The creator of Rails is not the only developer working on Rails. There
are Rails developers who disagree with him on these issues, and "his
disciples" does not equate "the community". On the other hand, I admit
that this mindset has a negative impact on the development of Rails as
a whole.

I consider myself a moderate pragmatist, and I think both sides are
slightly wrong; the database side not pragmatic enough, and the Rails
side pragmatic at the cost of moving too much database logic into the
app.

For example, part of the point of having validations declared on the
model is so that you can raise user-friendly errors (and pipe them
through gettext for localization) such as "Your password must be at
least 4 characters long and contain only letters and digits".
Databases don't support this, and so applications end up having to
duplicate data-validation logic in order to find out what kind of user
input is invalid.

There might be hoops you could jump through to reduce the duplication.
You could, perhaps, introspect the schema and see that the password
column has a "check" constraint with a certain expression (eg.,
"password ~ '^\w+$'). On insertion failure, you use the expression
string to generate a select -- eg., "select password ~ '^\w+$' from
(select 'foobar'::text as password) as t" -- on every column you have
to see what failed. I don't think PostgreSQL had full support for ANSI
SQLSTATE column context information yet, but even if it had, I think
you would not get information about *all* failing columns, only the
first one (anyone know?), so you would still needt run the values
through the database with a select.

At this point you don't have an error message, but you could store
these in a separate table (table_name, column_name, message) or
perhaps (table_name, constraint_name, message) and look them up on
failure.

Another option might be to use triggers that call "raise" -- which may
be acceptable for "check" constraints, but breaks the idiom for the
others type of constraints; at best you will be repeating yourself.
Another idea: I believe SQLSTATE (as implemented by PostgreSQL)
currently lacks context information about which columns failed a
constraint, but you had this, you could correlate

None of this is terribly idiomatic, and involves a bunch of glue
between application and database which needs to incorporated into a
database layer.

This may be a case for letting constraints have an optional
description; this way the schema would also be self-documenting, eg.:

create table foo (
id serial,
name text check (name ~ '^(\w|\s)+$') or raise error 'Name must
contain letters, digits and spaces only'
);

This still isn't enough for the app side -- if multiple columns fail
to validate, the app needs to know about them all, not just the first
one:

# create table persons (name text check (name != '') not null unique,
age int check (age > 0));
# insert into persons (name, age) values ('', 0);
ERROR: new row for relation "persons" violates check constraint
"test_name_check1"

> I have used rails for a couple projects and I repeated myself
> religiously in database constraints. This revealed a couple
> disturbing properties of rails that made me not want to use it,
> notably, assuming an empty input box equates to an empty string vice
> null for text data types, even if the table is defined as accepting
> nulls.

An empty string is not null! Null means the value is missing, which is
clearly not the case here. I would say Rails is exactly in the right
here. When an HTML form is posted, empty input boxes are declared as
empty strings, which what the user entered. The problem is not with
Rails/ActiveRecord but with your form handling. If you want empty
boxes to become null, add some client-side JavaScript logic that sets
the "disabled" attribute on empty input elements before form is
submitted; this will prevent the client from sending the value.

Alexander.