[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

In-depth schema details in ActiveRecord

Gavin Kistner

4/7/2005 1:42:00 PM

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?
--
(-, /\ \/ / /\/
16 Answers

Austin Ziegler

4/7/2005 2:12:00 PM

0

On Apr 7, 2005 9:41 AM, Gavin Kistner <gavin@refinery.com> wrote:
> 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
--
Austin Ziegler * halostatue@gmail.com
* Alternate: austin@halostatue.ca

B. K. Oxley (binkley)

4/7/2005 2:17:00 PM

0

Austin Ziegler wrote:
> 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

4/7/2005 2:38:00 PM

0

On Apr 7, 2005 10:16 AM, B. K. Oxley (binkley)
<binkley@alumni.rice.edu> wrote:
> Austin Ziegler wrote:
>> Never mind that MySQL is perhaps the crappiest SQL database that
> 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.

>> 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 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
--
Austin Ziegler * halostatue@gmail.com
* Alternate: austin@halostatue.ca

threeve.org

4/7/2005 3:23:00 PM

0

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

khaines

4/7/2005 3:38:00 PM

0

Jason Foreman wrote:

> 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

4/7/2005 4:31:00 PM

0

On Apr 7, 2005 9:41 AM, Gavin Kistner <gavin@refinery.com> wrote:
> 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

4/7/2005 5:23:00 PM

0

Jim Cain wrote:
> 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

Gavin Kistner

4/7/2005 6:58:00 PM

0

Jeremy Kemper wrote:
> 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

4/7/2005 7:10:00 PM

0

Phrogz wrote:
> Jeremy Kemper wrote:
>>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)

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

4/7/2005 7:12:00 PM

0

On Apr 7, 2005 7:59 PM, Phrogz <gavin@refinery.com> wrote:
> Jeremy Kemper wrote:
> > 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)

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