ruby ORM

[Ammar Ali]

I needed to process some data for automated test scripts this morning. I
switched on my rigorously locked down corporate laptop which has
absolutely no chance of getting Ruby on it. So I try VBA and
blow-me-down they've locked out ADOX. No chance of getting that sorted
this side of Xmas so I was forced to turn to good old Access. An hour or
two after that everything was sorted and I didn't even need to write a
line of code - not even edit raw SQL.

Cast off that ORM straightjacket and explore the bright lights of the
database world. You'll have some fun!

Similar situation here. An app I'm working on needed a small fix and
uses mysql. Getting that installed turned into a bigger task that I
had hoped or cared to install on my personal machine. Thankfully I was
using a decent ORM, and just switched the adapter name from mysql to
sqlite and I was up and running in no time.

There's some green grass on both sides.

Regards,
Ammar

 

[Robert Klemme]

Essentially, the data that you store in a database, any database, has
to be massaged such that you can store it in the first place. AKA
normalization.

You would apply the very same reasoning when creating classes for a
particular problem domain so normalization isn't really that esoteric
for someone doing OO development.

No, it doesn't. SQL's syntax is unlike any programming language I
know. Pure SQL contains no looping constructs, for example. It can
only retrieve and store data.

Since the advent of Analytical SQL you can do amazing things in a single
query.

In how far is SQL's representation of data "more sophisticated"? In
the end, the rows returned by an SQL query are nothing but an Array
(maybe a Hash with the SQL query as a key in Ruby parlance).

I don't think the *representation* is so much more sophisticated - if at
all. It's the *storage* of and *access* to the data which is extremely
sophisticated in modern RDBMS. IMHO the two main features of RBDMS are
efficient management of large volumes of data and maintaining integrity
(transaction management).

Since Triggers operate on the SQL statements, which the programming
language<-> SQL DB interface *has* to use (no matter if it is shelled
out to the command line, uses ORM, or JDBC), you don't lose the any
capabilities your SQL DBMS offers you, since those operate on the SQL
statements (they have to, since you don't have to interact with a DB
with its vendor-supplied driver).

However, the abstraction offered by, say, ORM, allows the programmer
not to have to *care* about the underlying database, so he doesn't
have to care about the SQL used. Which, considering the ugliness of
SQL, is no small blessing.

This is the standard argument for ORM and it is true in many situations.
However, there are some grains of salt to be taken with this:
unfortunately the exact fact that you do not have to care about the DB
underneath can lead to serious issues because it lowers the awareness
level for database peculiarities - and I am not only talking about
differences in SQL dialects. The more important things that someone
creating an application which uses a RDBMS should be aware of are
concurrency models and performance. If you create an application which
is exposed to the internet (or any other potentially "unlimited" number
of users) you need to take these aspects into consideration otherwise
you'll learn too late that the design you chose does not work. :-}

Just one example: often ORM are ideal to retrieve one or few objects,
manipulate them in memory and store them later. Even a seemingly simple
task like "up salary of everyone in marketing by 5%" can be tedious with
ORM. Typically "UPDATE EMP SET sal = sal * 1.10 WHERE dept =
'marketing'" is far more efficient than an ORM solution - unless the ORM
supports mass data updates via special mechanisms.

Kind regards

robert

 

[Justin Baker]

[Note: parts of this message were removed to make it a legal post.]

What is the point of arguing over ORM vs SQL? They both have their own high
and low points.

SQL is a language missing many components many are used to, and the ways of
performing operations can be difficult. This makes it less accessible to new
programmers, and the younger programmers.

ORM's provide an easier to use interface to interact with databases,
therefore make it simpler for someone to get started with databases from
particular languages. A downfall is speed, which can always be improved
upon. SQL's syntax will likely never be changed.

Point in case: It all matters WHO wants to program and their specific needs.

 

[Phillip Gawlowski]

You would apply the very same reasoning when creating classes for a
particular problem domain so normalization isn't really that esoteric for
someone doing OO development.

Absolutely, and I never said otherwise. No matter what tool one uses,
you don't get around the fact that you have to think about what you
are dealing with, how to deal with it, and which tool to use.

Since the advent of Analytical SQL you can do amazing things in a single
query.

I'm also aware of the trigonometric and geospatial functions modern
databases provide. Doesn't mean that SQL gets prettier, or any more
useful (the issue actually begets the question of separation of
concerns, which is a whole other kettle of fish).

I don't think the *representation* is so much more sophisticated - if at
all. =A0It's the *storage* of and *access* to the data which is extremely
sophisticated in modern RDBMS. =A0IMHO the two main features of RBDMS are
efficient management of large volumes of data and maintaining integrity
(transaction management).

Absolutely. Which is why I happily use an ACID compliant database any
day (unless a simple text file is easier to deal with, but that's only
true in few circumstances, like log files, config files, and such).

This is the standard argument for ORM and it is true in many situations.
=A0However, there are some grains of salt to be taken with this: unfortun= ately
the exact fact that you do not have to care about the DB underneath can l= ead
to serious issues because it lowers the awareness level for database
peculiarities - and I am not only talking about differences in SQL dialec= ts.
=A0The more important things that someone creating an application which u= ses a
RDBMS should be aware of are concurrency models and performance. =A0If yo= u
create an application which is exposed to the internet (or any other
potentially "unlimited" number of users) you need to take these aspects i= nto
consideration otherwise you'll learn too late that the design you chose d= oes
not work. :-}

This is such a generalized statement, it is true in pretty much all
circumstances: You *have* to be aware of the limitations your chosen
toolset has. Using an ORM library doesn't excuse you from thinking
about the database (it "merely" makes working with the DB easier), nor
does the lack of an ORM excuse you from thinking about how to map the
data to the program model.

There are no silver bullets, and every abstraction is a trade off. We
use Ruby, for example, because we prefer an expressive syntax and
dynamic features about the line noise of C despite C's better
performance in some circumstances.

Just one example: often ORM are ideal to retrieve one or few objects,
manipulate them in memory and store them later. =A0Even a seemingly simpl= e
task like "up salary of everyone in marketing by 5%" can be tedious with
ORM. =A0Typically "UPDATE EMP SET sal =3D sal * 1.10 WHERE dept =3D 'mark= eting'"
is far more efficient than an ORM solution - unless the ORM supports mass
data updates via special mechanisms.

Obviously. But for mass updates, I rather grab a script and fire that
off against the DB itself, than use a database abstraction layer of
any kind. It's simply faster. Similarly for moving data into a
database. Using an ORM layer and such slows things down too much for
little to no benefit (I'd hate to move any non-trivial dataset via
ActiveRecord and its migration semantics, for example).

TL;DR: No choice of tools is superior in all circumstances. Never was,
never will be. But if you decide to agitate against a specific set of
tools because of what amounts to religious fundamentalism and with
righteous preaching, I'm happy to apply a 4x2 clue any day.

--=20
Phillip Gawlowski

Though the folk I have met,
(Ah, how soon!) they forget
When I've moved on to some other place,
There may be one or two,
When I've played and passed through,
Who'll remember my song or my face.

 

[Skye Shaw!@#$]

Databases are supposed to be designed with external schemas ie you never
program to a base table...ever! You program to views

What happens when I want to make an update

 

[Sam Duncan]

Sam I'm sure you're a decent person but it's actually the opposite

Too kind! From a pragmatic perspective an ORM is certainly not the
opposite of providing flexibility. Horses for courses. There are a lot
more ways to store data than SQL databases ;]

Sam

 

[Skye Shaw!@#$]

As a rule, I think the Sequel library may be unfairly overlooked in the
'good ways to deal with databases category'.

Sequel is very good. Its dataset based and therefore quite flexible.
ORM is optional and a wide array of plugins are available.

On the other hand it's can get complicated to configure, though this
is more a result of its ability to meet a user's complicated data
requirements. Setting up basic ORM functionality is seamless.


ActiveRecord is great, but the "convention over configuration" aspect
might be surprising for newcomers, particularly if they're not
creating their DB from scratch or if they're trying to map exotic
relationships and multi column primary keys (not sure if/how version 3
changes this).


-Skye

 

[Petite Abeille]

=20
What happens when I want to make an update

If one choose to, one could always perform DML on a view courtesy of =
'instead of' triggers or such.

Alternatively, one could look at the db as a, hmm, black box. The db =
only exposes itself as a set of RPC calls (aka store procedures or =
such). Clients only interact with the db through these. No free form =
queries or anything. As always, YMMV.=20

 

[Mike Stephens]

Petite Abeille wrote in post #961384:

Alternatively, one could look at the db as a, hmm, black box. The db
only exposes itself as a set of RPC calls (aka store procedures or
such). Clients only interact with the db through these. No free form
queries or anything. As always, YMMV.

I suspect this is what most programmers do as most databases seem to be
very poor at views, and particularly at updatable views. (Jet curiously
is really rather smashing at this).

This all comes from the Three Schema Architecture, which proposes you
have a logical model (the base tables) which represents the objects in
your domain, an internal model which is how your data is physically
stored and an external model which is in a format relevant and
convenient for your application. When the relational model was defined
(by ANSC),no internal schema structure was covered (almost always we use
the relations of the logical model more-or-less). An external schema was
however defined. The expectation was you would write your programs to
views.

 

[Robert Klemme]

Absolutely, and I never said otherwise. No matter what tool one uses,
you don't get around the fact that you have to think about what you
are dealing with, how to deal with it, and which tool to use.

I completely agree, but unfortunately reality seems to be that for
many developers ORM "hides" the database and makes it disappear from
their radar. So I think it's important to remember people from time
to time that there is a whole world behind their ORM which is
worthwhile to know (and it's so interesting, too!).

There are no silver bullets, and every abstraction is a trade off. We
use Ruby, for example, because we prefer an expressive syntax and
dynamic features about the line noise of C despite C's better
performance in some circumstances.

I see, we are in complete agreement.

TL;DR: No choice of tools is superior in all circumstances. Never was,
never will be. But if you decide to agitate against a specific set of
tools because of what amounts to religious fundamentalism and with
righteous preaching, I'm happy to apply a 4x2 clue any day.

How true. Unfortunately there are still a lot of people around that
believe they need an ideology of some kind. When that collides with
reality unfortunately it's not given that reality wins - and if it
doesn't this usually leads to a lot of suffering. But I am
digressing...

Kind regards

robert

 

[Mike Stephens]

Skye Shaw!@#$ wrote in post #961372:

What happens when I want to make an update

Did a bit of research last night. These databases have some ability to
update views:

Oracle
DB2
Jet
IDMS

I'm sure there are more.

People on this channel may be unaware of what databases can do. I
believe for a long time MySQL did not have views at all. Or subqueries
for that matter. Mickey Mouse web applications might be fine with a
handful of tables, but in corporate contexts it is normal to see 100 or
even a 1000 tables in a database. Managing all this via ORM approaches
might be more difficult than at first appears. It's the difference
between driving your car to work and running an airline.

 

[Phillip Gawlowski]

Skye Shaw!@#$ wrote in post #961372:

People on this channel may be unaware of what databases can do. I
believe for a long time MySQL did not have views at all. Or subqueries
for that matter. Mickey Mouse web applications might be fine with a
handful of tables, but in corporate contexts it is normal to see 100 or
even a 1000 tables in a database. Managing all this via ORM approaches
might be more difficult than at first appears. It's the difference
between driving your car to work and running an airline.

It's actually rather easy to run an airline (as easy as any transport
corporation). It's difficult to get the infrastructure going, however.

So, pray tell, in how far is it easy to deal with 100, or even 1000
different tables per database[0] (BTW, when you say "database" do you
mean the engine, or actual database entities. IOW: 1000 tables per
schema, or 1000 tables in total) in any sensible, or at least easy-ish
way? Instead of bashing ORM, offer suggestions, for once.

[0]Not forgetting that the only people who have access to all tables
in any given database are only a select few, simply for privacy and
security reasons.

--
Phillip Gawlowski

Though the folk I have met,
(Ah, how soon!) they forget
When I've moved on to some other place,
There may be one or two,
When I've played and passed through,
Who'll remember my song or my face.

 

[Robert Klemme]

I completely agree, but unfortunately reality seems to be that for
many developers ORM "hides" the database and makes it disappear from
their radar. =A0So I think it's important to remember people from time
to time that there is a whole world behind their ORM which is
worthwhile to know (and it's so interesting, too!).

Just so nobody copies my bad practice: that should have read "So I
think it's important to *remind* people from time...".

Cheers

robert


--=20
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

 

[Mike Stephens]

Phillip Gawlowski wrote in post #962160:

(BTW, when you say "database" do you
mean the engine, or actual database entities. IOW: 1000 tables per
schema, or 1000 tables in total)

I'm thinking of eg a large general insurance application. There would be
hundreds of other databases across the organisation but I recall 800
Oracle tables in the main app.

the only people who have access to all tables
in any given database are only a select few, simply for privacy and
security reasons.

You refer to people who have access to all tables as though there are
only two types of users - 800 tablers or 20 tablers. In fact there will
be all sorts of different profiles.

You seem very evangelical when it comes to ORMs but I'm sure you're
aware the impedance mismatch is a well known issue. I didn't invent it.

 

[Phillip Gawlowski]

I'm thinking of eg a large general insurance application. There would be
hundreds of other databases across the organisation but I recall 800
Oracle tables in the main app.

So, how do you access them, then? ORM is not an option, so how do you
et the data into a report, a quoting app, or shove it over to accounts
payable?

You refer to people who have access to all tables as though there are
only two types of users - 800 tablers or 20 tablers. In fact there will
be all sorts of different profiles.

I never said that, and I never implied that. But an insurance agent
will *not* have access to the financial records of the corporation,
nor to detailed payment behavior of clients, nor will HR have access
to client information, nor will billing have access to client profiles
or financial records. The only people with access to all the data in
all the tables with be a handful of DBAs. Don't put words in my worth.

You seem very evangelical when it comes to ORMs

Not at all. I'm merely trying to get answer out of you as what
alternatives *you see* to ORM. So far, you haven't offered one,
despite claiming that ORM is a sub-par technology when it comes to
accessing databases. Burden of proof is yours, so provide some proof.

but I'm sure you're
aware the impedance mismatch is a well known issue. I didn't invent it.

I'm also aware that databases aren't the best place to put program
logic (stored procedures are pushing it already, but enforcing
constraints, normalization, and access *is* a key feature of database
engines).

So, let me reiterate: What alternatives do you offer to accessing a
database from within programs? You've not yet answered this question.

--
Phillip Gawlowski

Though the folk I have met,
(Ah, how soon!) they forget
When I've moved on to some other place,
There may be one or two,
When I've played and passed through,
Who'll remember my song or my face.

 

[Arturo Garcia]

I think you need ORM but in that order

Object --> Relational --> Mapping
(Datamapper could be your friend)

The other way won't do.

Otherwise, stored procedures.

I think your DBAs will favour the second one, and I would suggest splitting
your models in two:

Business Logic
Data Access

You will probably find that SELECTs will be free of charge, but DELETEs and
UPDATES wont. My feeling is your application is more of a transactional one,
so the models might model the transactions themselves, rather than the
entities.

Good luck!

 

[Mike Stephens]

Phillip Gawlowski wrote in post #962325:

et the data into a report, a quoting app, or shove it over to accounts
payable?

I'm not sure what you're getting at. The normal ways of dealing with
databases are to call an SQL interface or a stored procedure. This is
relatively painless if you accept SQL is one of the principal tools in
the developer's toolbag. You may get a relation returned (which is easy
to map to a hash or whatever) or a cursor. You have to write a small
method to make the call and transfer the data but in return you have the
flexibility to adjust the SQL to use its terrific power. Ruby might be
fun but so is SQL.

For a long time after relational databases came in you would see
developers writing code that just used SQL to write and read records.
Nothing had changed since the days of ISAM, IMS, IDMS etc etc. Nowadays
you see database calls that cover two pages of SQL. People have realised
they can solve an awful lot before they get the data back into their
progams. I just worry ORMs tempt new people down the old path.

 

[Phillip Gawlowski]

I'm not sure what you're getting at. The normal ways of dealing with
databases are to call an SQL interface or a stored procedure. This is
relatively painless if you accept SQL is one of the principal tools in
the developer's toolbag. You may get a relation returned (which is easy
to map to a hash or whatever) or a cursor. You have to write a small
method to make the call and transfer the data but in return you have the
flexibility to adjust the SQL to use its terrific power. Ruby might be
fun but so is SQL.

Sorry, but the DBAs can't allow change the stored procedures without
CTO approval.

Seriously, though: All you are ending up with is writing an ORM
*yourself*, *specific* to one single database (you have to map the
result set and the stored procedure to program code in *some* way, and
since pretty much all languages are OO these days, you create an
object). The only difference is that you create SQL by hand (or,
worse, somebody else has to write the SQL for you, if you can't access
the DB).

Given that CPU time (And thus code generation) is cheaper than
developer time, and a developer's time is better spend on solving
business problems, in how far is your approach sustainable beyond very
simple relationships of data?

For a long time after relational databases came in you would see
developers writing code that just used SQL to write and read records.
Nothing had changed since the days of ISAM, IMS, IDMS etc etc. Nowadays
you see database calls that cover two pages of SQL. People have realised
they can solve an awful lot before they get the data back into their
progams. I just worry ORMs tempt new people down the old path.

Instead of writing code that only works for one vendor's DB engine,
people write SQL statements that are, theoretically, portable to any
other SQL engine. In howfar has *anything* changed since the days of
dBase IV, aside from getting a standardized query language?

Do you really advocate writing a two page long SQL statement is vastly
preferable to automatically generated code that can deal with pretty
much any datastorage system you throw at it without having to change
semantics at every instance?

--
Phillip Gawlowski

Though the folk I have met,
(Ah, how soon!) they forget
When I've moved on to some other place,
There may be one or two,
When I've played and passed through,
Who'll remember my song or my face.

 

[Mike Stephens]

Robby on Rails (Robby Russell) was briefly a fan of the reverse side of
the coin - sticking half your app inside the database with PL/Ruby:

http://robbyonrails.com/articles/2005/8

He's now gone all quiet on that front so I guess he's swallowed the ORM
shilling.

 

[Kevin]

Robby on Rails (Robby Russell) was briefly a fan of the reverse side of
the coin - sticking half your app inside the database with PL/Ruby:

http://robbyonrails.com/articles/2005/8

He's now gone all quiet on that front so I guess he's swallowed the ORM
shilling.

There is nothing wrong with using an ORM. The problem is that some
people have the insane idea that the application layer should be
responsible for maintaining referential integrity. Thee only other
thing that bothers me about ORMs in Ruby is that they don't have very
good support for stored procedures. They all seem to expect you to use
dynamic SQL for everything, which would be fine if I did not already
have stored procedures that did everything in my database.

Writing your own pseudo-ORM is really not the best use of your time even
if it results in cleaner code in your application as opposed to manually
setting everything up. I wrote a bunch of classes to map my database's
stored procedures in VB.net (This was for a .NET 2.0 application so no
Linq or Entity Framework.) while interesting because it provided me an
opprotunity to learn more about reflection in the language I am most
comfortable in, I recognize that using an ORM like Entity Framework
would have been more appropriate. Especially since Entity Framework
supports stored procedures at least if you are using MS SQL Server.

I also don't see the big benefit of using instead of triggers to update
views that would otherwise not be updatable over an ORM.