In-depth schema details in ActiveRecord
Gavin Kistner
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
--
Austin Ziegler * halos...@gmail.com
* Alternate: aus...@halostatue.ca
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
<bin...@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.
Kujawa, Greg
much more than basic select statements being thrown against a database.
There are more capable alternatives (such as PostgreSQL) IMHO that are
proven. I know this list might be a bit dated but check out some of the
MySQL "gotchas" at http://sql-info.de/mysql/gotchas.html. Some of these are
staggering. Especially the ones that involve invalid/out-of-bounds data
being inserted into the database...
Alan Garrison
>Agreed. I have used MySQL in the past for quick setups that required not
>much more than basic select statements being thrown against a database.
>There are more capable alternatives (such as PostgreSQL) IMHO that are
>proven. I know this list might be a bit dated but check out some of the
>MySQL "gotchas" at http://sql-info.de/mysql/gotchas.html. Some of these are
>staggering. Especially the ones that involve invalid/out-of-bounds data
>being inserted into the database...
>
>
I've heard the "gotchas" list is a bit out of date, but I agree with
Austin in that developing from a MySQL (instead of ANSI SQL) base is
unfortunate. I'm very much a PostgreSQL devote and avoid MySQL like the
plague, and if a given software package is focused on working with MySQL
I generally avoid it.
That, and PostgreSQL just rocks anyways. :)
--
Alan Garrison
Cronosys, LLC <http://www.cronosys.com>
Phone: 216-221-4600 ext 308
Jason Foreman
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
> 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.
Berger, Daniel
> From: Jim Cain [mailto:jec...@gmail.com]
> Sent: Thursday, April 07, 2005 10:31 AM
> To: ruby-talk ML
> Subject: Re: In-depth schema details in ActiveRecord
> 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.
Yay!
Dan
Eric Hodel
> Kujawa, Greg wrote:
>
>> I know this list might be a bit dated but check out some of the
>> MySQL "gotchas" at http://sql-info.de/mysql/gotchas.html.
>
> I've heard the "gotchas" list is a bit out of date.
The list mentions which gotchas are present in which versions up to 4.1
and the maintainer started testing 5.0.3 beta on March 30.
--
Eric Hodel - drb...@segment7.net - http://segment7.net
FEC2 57F1 D465 EB15 5D6E 7C11 332A 551C 796C 9F04
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
Alan Garrison
> On 07 Apr 2005, at 08:12, Alan Garrison wrote:
>
>> Kujawa, Greg wrote:
>>
>>> I know this list might be a bit dated but check out some of the
>>> MySQL "gotchas" at http://sql-info.de/mysql/gotchas.html.
>>
>>
>> I've heard the "gotchas" list is a bit out of date.
>
>
> The list mentions which gotchas are present in which versions up to
> 4.1 and the maintainer started testing 5.0.3 beta on March 30.
>
Ah, and there's also a PostgreSQL gotchas list, which is a bit smaller
than MySQL's. Heh.
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
> 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
> 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
Douglas Livingstone
> Try it in MySQL.
Make that = phpMyAdmin
Saynatkari
The only DESC in SQL goes after an ORDER BY.
> Try it in MySQL.
>
> Douglas
E
George Moschovitis
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
>> 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.
Clever.
graham.r...@gmail.com
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.