In-depth schema details in ActiveRecord

[Gavin Kistner]

A few days ago I posted a question on how to use ActiveRecord to
determine if the DB schema allows null values in a column or not. I got
no response, and I think that's because the feature doesn't exist.

That made me wonder "why oh why doesn't this ridiculously useful
feature exist?!" Oh, the things you could do with scaffolding if you
could determine not just what type of value a column is, but if it's
required. If the AR classes had rich methods that described the full
schema they reflected, joins to other classes per column, and so on.
Think of scaffolding that did client- and server-side validation of
required fields...that used forign-key references to create drop-downs
for associated tables. Oh, the automated beauty that could be realized!

Doing more digging, I assume that the reason these sorts of
deep-schema-inspection details don't exist in AR is that (from what I
can tell) not all DBs support this level of inspection in the
interfaces. MySQL, notably.

I'm a rails noob, just starting out. Perhaps I'm wrong.

If I'm correct...this seems a shame. Lowering the functionality to the
lowest common denominator. You can write a SQL select statement in
PostgreSQL to find out if a column may be null or not. I'm pretty sure
the same is true of MSSQL2k as well. Must the Good Guys be hamstrung
just because the weak happen to be so popular?

If these features have been omitted due to lack of inspection support
by some rdbms, what would people think of coming up with a set of
full-schema inspection methods, which would return nil if the db
adaptor didn't/couldn't support those methods? Would anyone be willing
to lend a hand to such a project?

 

[Austin Ziegler]

Doing more digging, I assume that the reason these sorts of
deep-schema-inspection details don't exist in AR is that (from what I
can tell) not all DBs support this level of inspection in the
interfaces. MySQL, notably.

I'm a rails noob, just starting out. Perhaps I'm wrong.

I doubt you're wrong, as DHH has stated several times in the past that
the canonical database for Rails (and thus AR) is MySQL.

That's right. The entire semantics of AR are based on what MySQL does.

Never mind that MySQL is perhaps the crappiest SQL database that
exists that doesn't even fully conform to the ANSI SQL92 specification
and uses platform specific behaviour to determine whether tables
should be case-sensitive or not (when the ANSI SQL92 specification
clearly states that they should not, unless enclosed in double
quotes).

I don't know if Og does this better or not.

-austin

 

[B. K. Oxley (binkley)]

Never mind that MySQL is perhaps the crappiest SQL database that

I sense a feeling of hostility.

exists that doesn't even fully conform to the ANSI SQL92 specification
and uses platform specific behaviour to determine whether tables
should be case-sensitive or not (when the ANSI SQL92 specification
clearly states that they should not, unless enclosed in double
quotes).

Where might I find a good comparison of the level of support of AR for
each of the various supported databases?

I have a particular interest in sqlite(2/3) for unit testing and have
been toying (literally, toy code) with sqlite3 for exploring Rails, but
do not want to box myself into only knowing the ins/outs of just one
backend database.


Cheers,
--binkley

 

[Austin Ziegler]

I sense a feeling of hostility.

Hostility toward MySQL? Absolutely. It's an SQL-like database that
barely implements anything remotely close to a proper database. (You
want something that is ACID from the get go? You *don't* want your
table and column names to be case sensitive by default? You must be
an anti-open source advocate.)

Hostility toward AR? Not at all. Disappointment that such an
otherwise useful framework uses such crap as MySQL as its driving
principles.

Where might I find a good comparison of the level of support of AR
for each of the various supported databases?

I don't know; I haven't done anything with AR in a while, since I
found that it uses a broken model and have no current need for a
database (or even a pseudo-database like MySQL) in any of the
applications I'm developing at the moment.

I suspect that the level of support for anything outside of MySQL is
iffy, at best.

-austin

 

[Jason Foreman]

This is a nice idea, something I think would be fun to have. The
usefulness beyond simple rapid demo and prototyping is debatable, but
if nothing else it could still be useful in those aspects. The root
of the problem as you pointed out is being able to handle multiple
platforms.

I'm also new to Ruby and RubyOnRails. Coming from Java, we have the
benefit of the JDBC standard that most db drivers followed reasonably
well. The metadata provided by the driver could give me tons of
useful info. Does such a construct exist in Ruby (DBI?) that could
be leveraged in something like AR? I notice that AR has various
adapters for different databases, this might provide some insight.
I'll try to dig into all this over the weekend.

This would be an interesting project to work on, and if you get
anything going keep me posted!

Jason

 

[Kirk Haines]

I'm also new to Ruby and RubyOnRails. Coming from Java, we have the
benefit of the JDBC standard that most db drivers followed reasonably
well. The metadata provided by the driver could give me tons of
useful info. Does such a construct exist in Ruby (DBI?) that could
be leveraged in something like AR? I notice that AR has various
adapters for different databases, this might provide some insight.
I'll try to dig into all this over the weekend.

Ruby does have a DBI implementation. The ORM I primarily use and develop on
(Kansas) uses it because it was a very simple way to gain usability with a
variety of database backends.

The biggest drawback to using DBI, and the reason, I assume, why neither AR
nor Og do, is performance. Purpose built adapters to connect an ORM right
with the low level driver for a given database are going to perform better
(faster, less RAM usage) than going through DBI.

In practice, the DBI tax has not been a problem for me, but one of my goals
is to make a set of purpose build adapters available for Kansas so that one
may use them instead of DBI for supported databases (which will probably
initially be MySQL, PostreSQL, and SQLite2&3) because I can forsee it
potentially being a problem in the future.


Kirk Haines

 

[Jim Cain]

That made me wonder "why oh why doesn't this ridiculously useful
feature exist?!" Oh, the things you could do with scaffolding if you
could determine not just what type of value a column is, but if it's
required. If the AR classes had rich methods that described the full
schema they reflected, joins to other classes per column, and so on.
Think of scaffolding that did client- and server-side validation of
required fields...that used forign-key references to create drop-downs
for associated tables. Oh, the automated beauty that could be realized!

I've recently been investigating using Rails for a current project of
mine. The backend is Oracle9i (hopefully soon to be 10g), and with
Oracle you can discover all you could ever want to know about the data
model using its data dictionary.

Adding standard methods to AR's database adapter model to support such
things shouldn't be too difficult. For example, to populate drop-downs
for foreign keys, it could look for an appropriate method in the
adapter. If the database supports discovering fkeys, then the method
exists; if not, it doesn't, or returns nil or something appropriate.

Another thing that should change is sequence generation. Just because
MySQL has a datatype that supports this directly in a table doesn't
mean all databases do. There should be a way to use a table-specific
function to generate a unique ID, such as sequences in Oracle.

If there is real value in these changes, I wouldn't mind contributing
some of them myself.

As an aside, I'm excited to have a project that I might be able to use
Ruby for, and it gives me an opportunity to update my Oracle driver
(Ruby9i) for the first time in over a year. I just bought the pickaxe
book (2nd ed.) in anticipation of this.

 

[Jeremy Kemper]

Adding standard methods to AR's database adapter model to support such
things shouldn't be too difficult. For example, to populate drop-downs
for foreign keys, it could look for an appropriate method in the
adapter. If the database supports discovering fkeys, then the method
exists; if not, it doesn't, or returns nil or something appropriate.

Indeed. Discovering metadata is not a big deal, but with many databases
to support it's (unfortunately) prohibitive to develop and test. Active
Record could really use a smoke-test box running every supported db.

Regarding the preceding thread's speculation whether MySQL can tell you
whether a column is nullable: try 'desc tablename' in your client.

Another thing that should change is sequence generation. Just because
MySQL has a datatype that supports this directly in a table doesn't
mean all databases do. There should be a way to use a table-specific
function to generate a unique ID, such as sequences in Oracle.

PostgreSQL does it by defaulting the primary key to nextval(sequence).
Many can (and do, in Active Record adapters) emulate this behavior, but
there's no sense cramming it down their throats.

To make things easy on the developer *and* the db, we could provide an
id generation strategy by passing a block to the primary key declaration:

# By default, foo_id set to a serial int by the db adapter.
class Foo < ActiveRecord::Base
# foo_id set to UUID.new
primary_key { UUID.new }

# foo_pk set to UUID.new
primary_key('foo_pk') { UUID.new }

# FOOID set to nextval("schema.some_global_seq")
primary_key('FOOID') { 'nextval("schema.some_global_seq")' }
end

If there is real value in these changes, I wouldn't mind contributing
some of them myself.

There certainly is! Thanks for digging in. Further discussion is
probably better-suited for the Rails list; I've cross-posted.

jeremy

 

[Phrogz]

Regarding the preceding thread's speculation whether MySQL can tell you
whether a column is nullable: try 'desc tablename' in your client.

Is this something that:
a) You can discover using a SQL query?
(I was told on on the #sql channel on IRC)

b) May be exposed by the engine to the MySQL adaptor?


Although I personally prefer PostgreSQL, it's not my intent to impugn
MySQL. I was simply told that MySQL probably wouldn't be able to
provide this schema information.

 

[Jeremy Kemper]

Is this something that:
a) You can discover using a SQL query?
(I was told on on the #sql channel on IRC)

Yes; it's shorthand for "show columns from tablename"

b) May be exposed by the engine to the MySQL adaptor?

Active Record's MySQL adapter uses this query.

jeremy

 

[Douglas Livingstone]

Is this something that:
a) You can discover using a SQL query?
(I was told on on the #sql channel on IRC)

Yes, using the SQL above: 'desc tablename'. It returns a result with
the following fields: Field, Type, Null, Key, Default, Extra. If the
Null filed is YES, then the field is "nullable".

Try it in MySQL.

Douglas

 

[Douglas Livingstone]

Try it in MySQL.

Make that = phpMyAdmin

 

[Saynatkari]

Yes, using the SQL above: 'desc tablename'. It returns a result with
the following fields: Field, Type, Null, Key, Default, Extra. If the
Null filed is YES, then the field is "nullable".

The only DESC in SQL goes after an ORDER BY.

Try it in MySQL.

Douglas

E

 

[George Moschovitis]

I don't know if Og does this better or not.

Og does this better because you define the not-null constrain with Ruby
code. Don't forget that Og maps ruby code to the sql schema and not
vice versa.
Btw, even more advanced scaffolding is coming to the next versions of
Nitro.

-g.

 

[Jeremy Kemper]

The only DESC in SQL goes after an ORDER BY.

Clever.

 

[graham.r.jenkins]

Cannot this sort of scaffolding be driven off the domain model
relationships? It shouldn't need to involve database metadata.

On a related note, I tried a script to scaffold a set of ActiveRecord
model objects complete with relationships based on foreign and primary
key constraints in Oracle. It worked reasonably well, but determining
appropriate cascade behaviour was unreliable. Enabling cascades in
Oracle and in ActiveRecord felt like a bad idea.

PS There is a patch kicking around to support per-table sequences. I
expect this will get merged into the OCI adapter sometime.