Rails - Aug 2005 - [OT] Postgres vs Mysql

Assuming that I already know how to somewhat use and administer a
mysql database, what are the (pragmatic) reasons that would move me
over to postgres?  (assuming mysql 4.1 here)

For discussion purposes, say I''m developing a small-scale e-commerce
website.

I like sqlite, but there seem to be more GUIs available for easily
editing msyql databases.  And migrations don''t yet work with sqlite.

Thanks,
Joe

Postgres has lots of stuff MySQL still doesn''t have.
Views, triggers, sequences, stored procs, cursors...

It also provides inheritance and you can define your own data types.

The general (if disputable) summary is that Postgres is where you go  
for features, MySQL for stability.

The licensing is also different, which is important depending on what  
you''re building.



On Aug 5, 2005, at 3:14 PM, Joe Van Dyk wrote:
> Assuming that I already know how to somewhat use and administer a
> mysql database, what are the (pragmatic) reasons that would move me
> over to postgres?  (assuming mysql 4.1 here)
>
> For discussion purposes, say I''m developing a small-scale e- 
> commerce website.
>
> I like sqlite, but there seem to be more GUIs available for easily
> editing msyql databases.  And migrations don''t yet work with
sqlite.
>
> Thanks,
> Joe
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Quoting Toby Boudreaux <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>:
> The general (if disputable) summary is that Postgres is where you go
> for features, MySQL for stability.
Are you suggesting that PostgreSQL isn''t stable?

On 05/08/05, Toby Boudreaux
<rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>
wrote:
> Postgres has lots of stuff MySQL still doesn''t have.
> Views, triggers, sequences, stored procs, cursors...
Postgresql 8.0 also has updatable views, tablespaces, savepoints, HA Clustering 
(with Slony), etc. It''s the most advanced Open Source DB available,
and is slowly
catching up to Oracle.
> 
> It also provides inheritance and you can define your own data types.
> 
> The general (if disputable) summary is that Postgres is where you go
> for features, MySQL for stability.
It''s quite the opposite actually, Postgresql is rock hard stable. Most
of the new
features available with Mysql 4.1 and Mysql 5.0, were available in Postgresql a
few years ago. 
Also Mysql has a dangerous and annoying tendency to change data at will.
For example, changing NULL date fields to ''0000-00-00''.

Regards,
Sebastian A. Espindola.

> The licensing is also different, which is important depending on what
> you''re building.
>

On Fri, 2005-08-05 at 15:24 -0400, alang-+Dj+6f/KO4tWk0Htik3J/w@public.gmane.org
wrote:
> Quoting Toby Boudreaux
<rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>:
> 
> > The general (if disputable) summary is that Postgres is where you go
> > for features, MySQL for stability.
> 
> Are you suggesting that PostgreSQL isn''t stable?
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
Joe,
As friendly and helpful as the RoR community is about answering
questions, perhaps it might be best if you do a little reading:

http://www.google.com/search?q=postgresql+vs+mysql&sourceid=mozilla-search&start=0&start=0&ie=utf-8&oe=utf-8&client=firefox&rls=org.mozilla:en-US:unofficial
and decide this one for yourself, before this thread goes much further ;)

I wasn''t suggesting that Postgres wasn''t stable --
that''s what the
"if disputable" disclaimer was about. It''s a mantra
I''ve heard a lot,
but isn''t my rule, nor do I abide by it.



On Aug 5, 2005, at 3:33 PM, Lord Khaos wrote:
> On Fri, 2005-08-05 at 15:24 -0400,
alang-+Dj+6f/KO4tWk0Htik3J/w@public.gmane.org wrote:
>
>> Quoting Toby Boudreaux
<rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>:
>>
>>
>>> The general (if disputable) summary is that Postgres is where you
go
>>> for features, MySQL for stability.
>>>
>>
>> Are you suggesting that PostgreSQL isn''t stable?
>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>
> Joe,
> As friendly and helpful as the RoR community is about answering
> questions, perhaps it might be best if you do a little reading:
>
> http://www.google.com/search?q=postgresql+vs+mysql&sourceid=mozilla- 
>
search&start=0&start=0&ie=utf-8&oe=utf-8&client=firefox&rls=org.mozill
> a:en-US:unofficial
> and decide this one for yourself, before this thread goes much  
> further ;)
>
>
>
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Geeze buddy,

You have been on the list for half a year at least. You have seen all
the flame threads which followed retarded ( yes, retaded) questions
like this.

Please stop trolling. 

On 8/5/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Assuming that I already know how to somewhat use and administer a
> mysql database, what are the (pragmatic) reasons that would move me
> over to postgres?  (assuming mysql 4.1 here)
> 
> For discussion purposes, say I''m developing a small-scale
e-commerce website.
> 
> I like sqlite, but there seem to be more GUIs available for easily
> editing msyql databases.  And migrations don''t yet work with
sqlite.
> 
> Thanks,
> Joe

-- 
Tobi
http://www.snowdevil.ca - Snowboards that don''t suck
http://typo.leetsoft.com - Open source weblog engine
http://blog.leetsoft.com - Technical weblog

Toby Boudreaux wrote:
> Postgres has lots of stuff MySQL still doesn''t have.
> Views, triggers, sequences, stored procs, cursors...
 >
 > It also provides inheritance and you can define your own data types.

Agreed.  I love the phrase "PostgreSQL: All the features you need 
tomorrow, yesterday."
> 
> The general (if disputable) summary is that Postgres is where you go  
> for features, MySQL for stability.
::screeching tires::

Say WHAT? Stability is greater with MySQL?  I have never heard anyone 
claim that before.
> 
> The licensing is also different, which is important depending on what  
> you''re building.
> 
There is also an ideological difference here.  MySQL is a pseudo-open 
source project.  Really, the feature development is about making money. 
  PostgreSQL is pure open source, started the old school way: by 
academics who were actually experts in database theory.

MySQL is very easy to deploy.  It is very easy to get started using.  It 
can, under certain circumstances, outperform a Postgres database, but 
that is always changing.

Postgres has the features.  If you want to really flex the power of a 
database, it is the choice.  If you I am developing anything more than a 
trivial app, I would not consider using anything other than postgresql.

Personally, I find it a bit shocking that people can come to rails 
leaving PHP behind and not see that MySQL is inferior to Postgres in 
some of the same ways.  If you are willing to change things, then why 
not do it all right?

-Jeff

-- 
Jeff Casimir
Author, CommonText Literacy Project: http://www.commontext.com
Teacher, César Chávez PCHS: http://www.cesarchavezhs.org

I think if MySQL works for you, keep using it.

I use both, but am far from an expert in either one.  I''ve honestly
yet to notice a difference really...I don''t know enough to take
advantage of the advanced features of PG.  Never had stability issues
with either one.

Of course when I develop the killer app that 2 billion people use,
I''ll figure out which is better.  Until then, there''s no real
difference for me.

Pat



On 8/5/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Assuming that I already know how to somewhat use and administer a
> mysql database, what are the (pragmatic) reasons that would move me
> over to postgres?  (assuming mysql 4.1 here)
> 
> For discussion purposes, say I''m developing a small-scale
e-commerce website.
> 
> I like sqlite, but there seem to be more GUIs available for easily
> editing msyql databases.  And migrations don''t yet work with
sqlite.
> 
> Thanks,
> Joe
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> writes:
> Assuming that I already know how to somewhat use and administer a
> mysql database, what are the (pragmatic) reasons that would move me
> over to postgres? (assuming mysql 4.1 here)
Whoooo boy, here it comes...  =)

-- 
I tend to view "truly flexible" by another term: "Make everything
equally hard". -- DHH

No, definitely not -- just repeating a mantra I''ve heard a lot from  
people since 98 or so. I said it was disputable because... well... it  
is.


On Aug 5, 2005, at 3:24 PM, alang-+Dj+6f/KO4tWk0Htik3J/w@public.gmane.org wrote:
> Quoting Toby Boudreaux
<rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>:
>
>
>> The general (if disputable) summary is that Postgres is where you go
>> for features, MySQL for stability.
>>
>
> Are you suggesting that PostgreSQL isn''t stable?
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Please stop trolling.
POSTGRES IS TEH R0XXX0R

MYSQL IS TEH SUCK

OMFG you MORON.  MySQL is teh_r0x0r!!

Postgres is teh suck

go back to PHP, retard



On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Please stop trolling.
> 
> POSTGRES IS TEH R0XXX0R
> 
> MYSQL IS TEH SUCK
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Mysql is mom and pop

Its ok for simple queries, but as soon as you need something more industrial
strength I suggest moving to postgres

It is far superior in a lot of areas. Including transactions, scabability
and custom functions to name a few




-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Pat Maddox
Sent: August 5, 2005 1:27 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] [OT] Postgres vs Mysql

OMFG you MORON.  MySQL is teh_r0x0r!!

Postgres is teh suck

go back to PHP, retard



On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Please stop trolling.
> 
> POSTGRES IS TEH R0XXX0R
> 
> MYSQL IS TEH SUCK
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On 8/5/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Geeze buddy,
> 
> You have been on the list for half a year at least. You have seen all
> the flame threads which followed retarded ( yes, retaded) questions
> like this.
Didn''t mean to troll.  I was just wondering if there were any
real/pragmatic reasons about why I should spend some effort to look
into postgres.  I realize that postgres has additional features that
mysql doesn''t have, but didn''t know how important those
features were
in your average Rails site.
 
> On 8/5/05, Joe Van Dyk
<joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Assuming that I already know how to somewhat use and administer a
> > mysql database, what are the (pragmatic) reasons that would move me
> > over to postgres?  (assuming mysql 4.1 here)
> >
> > For discussion purposes, say I''m developing a small-scale
e-commerce website.
> >
> > I like sqlite, but there seem to be more GUIs available for easily
> > editing msyql databases.  And migrations don''t yet work with
sqlite.
> >
> > Thanks,
> > Joe
> 
> 
> --
> Tobi
> http://www.snowdevil.ca - Snowboards that don''t suck
> http://typo.leetsoft.com - Open source weblog engine
> http://blog.leetsoft.com - Technical weblog
>

On 8/5/05, Clayton Cottingham <dr.frog-fVOoFLC7IWo@public.gmane.org>
wrote:
> Mysql is mom and pop
> 
> Its ok for simple queries, but as soon as you need something more
industrial
> strength I suggest moving to postgres
> 
> It is far superior in a lot of areas. Including transactions, scabability
> and custom functions to name a few
Can''t you do transactions with Rails with mysql? 

And I''d assume that the vast majority of sites don''t have a
problem
scaling at the DB end, right?  I mean, I''d probably never be more
popular than, say, 43things, and I think they just use one DB server.

(not meaning to troll :(  )
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Pat Maddox
> Sent: August 5, 2005 1:27 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] [OT] Postgres vs Mysql
> 
> OMFG you MORON.  MySQL is teh_r0x0r!!
> 
> Postgres is teh suck
> 
> go back to PHP, retard
> 
> 
> 
> On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Please stop trolling.
> >
> > POSTGRES IS TEH R0XXX0R
> >
> > MYSQL IS TEH SUCK
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

AFAIK mysql doesn''t do transactions inside its own databases you need a
secondary storage engine InnoDB


As far as scalability, I''ve done some tests PG vs mysql 

mysql started to really bog down on databases over the 1gb range







-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
Sent: August 5, 2005 4:15 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] [OT] Postgres vs Mysql

On 8/5/05, Clayton Cottingham <dr.frog-fVOoFLC7IWo@public.gmane.org>
wrote:
> Mysql is mom and pop
> 
> Its ok for simple queries, but as soon as you need something more
industrial
> strength I suggest moving to postgres
> 
> It is far superior in a lot of areas. Including transactions, scabability
> and custom functions to name a few
Can''t you do transactions with Rails with mysql? 

And I''d assume that the vast majority of sites don''t have a
problem
scaling at the DB end, right?  I mean, I''d probably never be more
popular than, say, 43things, and I think they just use one DB server.

(not meaning to troll :(  )
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Pat Maddox
> Sent: August 5, 2005 1:27 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] [OT] Postgres vs Mysql
> 
> OMFG you MORON.  MySQL is teh_r0x0r!!
> 
> Postgres is teh suck
> 
> go back to PHP, retard
> 
> 
> 
> On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Please stop trolling.
> >
> > POSTGRES IS TEH R0XXX0R
> >
> > MYSQL IS TEH SUCK
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

I''m going to add some potentially useless data from my experience. 
This is only about PostgreSQL - I have 0 experience with MySQL.

I''ve been a long time PostgreSQL user, but I''ve rarely had
tables over
50k lines.

Once though, I tried to migrate an MS SQL Server database to
PostgreSQL.  Very quickly I discovered that "backing up" and
"restoring" (pg_dump, psql dbname < file.sql) was very, very slow
compared to the equivalent in MSSQL.

I don''t know how MySQL is for backing up and restoring the DB, but
PostgreSQL was really rotten in that one respect.

I love everything else about PostgreSQL though, particularly the LACK
of roadblocks in the SQL implementation.  If you want it (from the SQL
standard), it''s probably in there.  I also read somewhere that
PostgreSQL had the most complete SQL92 implementation of any database,
including Oracle.  But that could be heresay.

Why use MySQL:
Your application has few concurrent queries, and speed is of the 
esscense, and you have very little horsepower to do it with.

Why use PostgreSQL:
More functionality OUTSIDE of the Rails application.  Stored procedures 
(functions), views (even useful under Rails), real transactions (imagine 
an application that takes transactions all day via rails and then batch 
processes them).

Here is perhaps the coolest one, though:   One language all of the way 
through.   You can install Ruby as your stored procedure language.



As a professional developer, specializing in database applications, I 
have moved many people from MySQL to PostgreSQL for functionality 
reasons, but never the other, for any reason.   Both are good.   I have 
not had enough time looking at MySQL to really answer how well it 
handles complex queries or how much effort the maintenance takes (you 
need to at least backup and build statistics).  MySQL should be OK for 
anything that is just web entry, but when you start to worry about 
multiple people hitting things at once, getting in each other''s way, 
large data, or large offline processing, You may want to look at 
PostgreSQL.   It is not as light as MySQL on system resources at the 
small end, but it is lighter at the large end.

Conclusion:   Pick what fits the task.

On 8/5/05, Grant Johnson <grant-ousXCV6JwGd54TAoqtyWWQ@public.gmane.org>
wrote:

Thanks for the informative post!
> Why use MySQL:
> Your application has few concurrent queries, and speed is of the
> esscense, and you have very little horsepower to do it with.
> 
> Why use PostgreSQL:
> More functionality OUTSIDE of the Rails application.  Stored procedures
> (functions), views (even useful under Rails), real transactions (imagine
> an application that takes transactions all day via rails and then batch
> processes them).
When should you use database views with Rails?  

And I''ve heard that stored procs were sort of evil.  Dunno why though.

I can see how real database transactions would be useful if you had to
access the database without going through ActiveRecord, but I''d
probably want to use a webservice for external access.  If I used a
webservice that went through ActiveRecord, would I avoid the need for
real db transactions?

Thanks,
Joe

I was looking on pg''s site for the how to define ruby as language et al
Saw you mention it ...

Can you point me in the right direction?

Thanks!


-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Grant Johnson
Sent: August 5, 2005 6:49 PM
To: Michael Teter; rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] [OT] Postgres vs Mysql

Why use MySQL:
Your application has few concurrent queries, and speed is of the 
esscense, and you have very little horsepower to do it with.

Why use PostgreSQL:
More functionality OUTSIDE of the Rails application.  Stored procedures 
(functions), views (even useful under Rails), real transactions (imagine 
an application that takes transactions all day via rails and then batch 
processes them).

Here is perhaps the coolest one, though:   One language all of the way 
through.   You can install Ruby as your stored procedure language.



As a professional developer, specializing in database applications, I 
have moved many people from MySQL to PostgreSQL for functionality 
reasons, but never the other, for any reason.   Both are good.   I have 
not had enough time looking at MySQL to really answer how well it 
handles complex queries or how much effort the maintenance takes (you 
need to at least backup and build statistics).  MySQL should be OK for 
anything that is just web entry, but when you start to worry about 
multiple people hitting things at once, getting in each other''s way, 
large data, or large offline processing, You may want to look at 
PostgreSQL.   It is not as light as MySQL on system resources at the 
small end, but it is lighter at the large end.

Conclusion:   Pick what fits the task.
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

[snip]
> And I''ve heard that stored procs were sort of evil.  Dunno why
though.
[/snip]

I''ll answer this one.  

Using stored procs ties you to a specific vendor.  It also means you
have business logic in your database.  Depending on the scale of your
app, that can be too cumbersome (and it won''t be taking advantage of
your OO world).

On the plus side, sometimes it''s very convenient, and it''s
always
faster than any alternative (assuming everything everywhere is done
reasonably).

Personally I like the Rails (and generally-accepted multi-tiered)
idea, which is to make the database layer as dumb as possible, and to
keep the business logic in the app layer.  Then it''s much easier to
change database vendors, and you can leverage your object model.  The
downside is it''s much much slower than dirty low-level code (which
includes db stored procs).  Human time > CPU time, within reason :)

On Aug 6, 2005, at 12:43 AM, Michael Teter wrote:
> [snip]
>
>> And I''ve heard that stored procs were sort of evil.  Dunno why
>> though.
>>
> [/snip]
>
> I''ll answer this one.
I''ll chime in with pretty much the opposite position.

Using stored procedures allows you bind together the data being  
stored in the database with the business rules that define what  
actions on that data are allowed. Doing this allows you to implement  
the rules once and write programs in almost any language that are  
governed by a single set of consistently applied rules.

The alternative is to have to re-write your business rules as  
technology and fashion advance. The more likely outcome is that you  
don''t take advantage of advancing technology (and fashion) because  
your application is stuck on a particular stack. Or you have  
implementations of business rules in different languages and struggle  
to keep them consistent. Rails is cool in part because it finally  
becomes practical to do client-side data validation without re- 
writing your business rules in Javascript.

Database technology is relatively mature. Translating between  
PostgreSQL and Oracle stored procedures can almost been done  
mechanically. The risk of wanting to move databases but not being  
able to is very low. In the last ten years, I''ve written web  
applications against Oracle, mSQL, MySQL, and PostgreSQL. Only twice  
did the underlying database change during the lifespan of a product:  
once from mMQL to MySQL and once from MySQL to PostgreSQL.

On the other hand, I cannot count the number of times C CGIs became  
Perl CGIs became Java servlets became mod_python scripts became PHP  
sites became RoR sites. Web application technology is still immature.  
Writing your business rules in your web application is like building  
on quicksand.

That said, it sometimes makes sense to build on quicksand. Especially  
when your application is a prototype. (Rails, like other dynamically  
typed languages, is also cool because you can usually get from  
prototype to final product without starting from scratch.)

The conspiracy theorist in me believes that MySQL was the perfect  
database for Java precisely because it was so inadequate. It  
displaced database analysts, designers, and administrators with Java  
technicians.

Regards,
Ed

-- 
Transmogrify, LLC * <http://xmog.com/>

I think you''re a lot more likely to write another app, perhaps in
Ruby, perhaps in another language, that operates on existing data,
then you are to switch over to another database.  If a reasonable
portion of the business logic is offloaded to the db, then you ensure
that no future apps circumvent the business rules.  Also, any changes
to the business rules can be updated in the db and are then propogated
through any client apps you may have written, with minor changes to
the client code necessary your interfaces aren''t tight and clean.

Pat



On 8/5/05, Michael Teter
<michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> [snip]
> > And I''ve heard that stored procs were sort of evil.  Dunno
why though.
> [/snip]
> 
> I''ll answer this one.
> 
> Using stored procs ties you to a specific vendor.  It also means you
> have business logic in your database.  Depending on the scale of your
> app, that can be too cumbersome (and it won''t be taking advantage
of
> your OO world).
> 
> On the plus side, sometimes it''s very convenient, and
it''s always
> faster than any alternative (assuming everything everywhere is done
> reasonably).
> 
> Personally I like the Rails (and generally-accepted multi-tiered)
> idea, which is to make the database layer as dumb as possible, and to
> keep the business logic in the app layer.  Then it''s much easier
to
> change database vendors, and you can leverage your object model.  The
> downside is it''s much much slower than dirty low-level code (which
> includes db stored procs).  Human time > CPU time, within reason :)
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I always forget that sarcasm and irony don''t work on the internet.

:)

On 05/08/05, Pat Maddox <pergesu-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> OMFG you MORON.  MySQL is teh_r0x0r!!
> 
> Postgres is teh suck
> 
> go back to PHP, retard
> 
> 
> 
> On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Please stop trolling.
> >
> > POSTGRES IS TEH R0XXX0R
> >
> > MYSQL IS TEH SUCK
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Saturday 06 August 2005 19:03, Pat Maddox wrote:
> If a reasonable
> portion of the business logic is offloaded to the db, then you ensure
> that no future apps circumvent the business rules.  Also, any changes
> to the business rules can be updated in the db and are then
> propogated through any client apps you may have written, with minor
> changes to the client code necessary your interfaces aren''t tight
and
> clean.
Okay, let''s say you convinced me. Where do I learn how to do all this? 
I''m using PostgreSQL (and that other DBMS), last I looked, the docs 
have some reference on writing stored procedures and triggers, but I''m 
not aware of any tutorial that teaches all the important points:

- how to actually write stored procedures
- application design with SPs and triggers; what goes where (app/db)
- error handling

Obviously there are people who know how to do this, but I have the 
impression they learned their skills on commercial DBMSs and 
transferred them to PgSQL. I''ve repeatedly looked for advanced PgSQL 
books -- without success. What are recommended sources to learn about 
this stuff?

Michael

-- 
Michael Schuerig                       All good people read good books
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                Now your
conscience is clear
http://www.schuerig.de/michael/ --Tanita Tikaram, Twist In My Sobriety

Clayton Cottingham wrote:
>I was looking on pg''s site for the how to define ruby as language
et al
>Saw you mention it ...
>
>Can you point me in the right direction?
>
>Thanks!
>  
>
1)  On Debian, it is a module called postgresql-plruby.  The home page 
is http://moulon.inra.fr/ruby/plruby.html.   I have done nothing further 
than notice it was ther and say "Cool, I might want that someday!"

One thing people are forgetting is that although Rail is great, sometime 
other processing happens to the data.   Since Rails works with any 
database, the other uses of your data may dictate which is best for you.

Imagine a data warehouse, where your translation as standardization 
tables are maintained via the web.   A great use for RoR, however, 
shoving the billions of records from here to there and doing the 
standardization may be best in another tool.

On 8/6/05, Pat Maddox <pergesu-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I think you''re a lot more likely to write another app, perhaps in
> Ruby, perhaps in another language, that operates on existing data,
> then you are to switch over to another database.  If a reasonable
> portion of the business logic is offloaded to the db, then you ensure
> that no future apps circumvent the business rules.  Also, any changes
> to the business rules can be updated in the db and are then propogated
> through any client apps you may have written, with minor changes to
> the client code necessary your interfaces aren''t tight and clean.
I suppose the pragmatic thing to do would be to write the business
logic using ActiveRecord first.  Then, if it looks like you''re going
to be forced to move to some super cool new technology in the future,
then reevaluate the decision to keep the business logic in the
database or at a higher level.
> On 8/5/05, Michael Teter
<michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > [snip]
> > > And I''ve heard that stored procs were sort of evil. 
Dunno why though.
> > [/snip]
> >
> > I''ll answer this one.
> >
> > Using stored procs ties you to a specific vendor.  It also means you
> > have business logic in your database.  Depending on the scale of your
> > app, that can be too cumbersome (and it won''t be taking
advantage of
> > your OO world).
> >
> > On the plus side, sometimes it''s very convenient, and
it''s always
> > faster than any alternative (assuming everything everywhere is done
> > reasonably).
> >
> > Personally I like the Rails (and generally-accepted multi-tiered)
> > idea, which is to make the database layer as dumb as possible, and to
> > keep the business logic in the app layer.  Then it''s much
easier to
> > change database vendors, and you can leverage your object model.  The
> > downside is it''s much much slower than dirty low-level code
(which
> > includes db stored procs).  Human time > CPU time, within reason :)

On Aug 6, 2005, at 1:55 PM, Joe Van Dyk wrote:
>
> I suppose the pragmatic thing to do would be to write the business
> logic using ActiveRecord first.  Then, if it looks like you''re
going
> to be forced to move to some super cool new technology in the future,
> then reevaluate the decision to keep the business logic in the
> database or at a higher level.
If I have a problem with Rails right now, it''s that it doesn''t
seem
to want to let the database do many useful things that databases are  
very good at. For example, just say you have a resource with a tag,  
just like del.icio.us. Furthermore, let''s say you want to make a list  
of tags, the size of each being a function of the number of resources  
associated with that tag. You''d have tables that look roughly like
this:

create table resource (
     resource_id serial primary key,
     name text,
     resource_location text
);

create table tag (
     tag_id serial primary key,
     name
);

create table resource_tag (
     resource_id int references resource,
     tag_id int references tag
);

With ActiveRecord, if I want to get what I want I have to iterate  
through each tag, get the resources, and then count them. That is  
very expensive. It takes more than five seconds with a modest data  
set on my PowerBook.

Here''s the SQL that returns that I wound up executing directly:

select tag_id, name, count(*)
     from resource_tag
     natural join tag
     group by tag_id, name;

It runs in 0.15 seconds or so on my PowerBook. The solution is not  
(apropos of the Mac mini thread) to get a faster computer: The answer  
is to use the awesome power of the relational database whenever it  
makes sense.

I realize that the ActiveRecord approach gets more data than the SQL  
query, but it would be nice to not have to restrict my use of  
ActiveRecord to CRUDly stuff that isn''t that challenging to deal with  
anyway. Am I missing something about AR that lets me do what I want?

Regards,
Ed

-- 
Transmogrify, LLC * <http://xmog.com/>

On 8/6/05, Ed Watkeys <edw-tIV1OJqwIcc@public.gmane.org>
wrote:
> 
> On Aug 6, 2005, at 1:55 PM, Joe Van Dyk wrote:
> 
> >
> > I suppose the pragmatic thing to do would be to write the business
> > logic using ActiveRecord first.  Then, if it looks like
you''re going
> > to be forced to move to some super cool new technology in the future,
> > then reevaluate the decision to keep the business logic in the
> > database or at a higher level.
> 
> If I have a problem with Rails right now, it''s that it
doesn''t seem
> to want to let the database do many useful things that databases are
> very good at. For example, just say you have a resource with a tag,
> just like del.icio.us. Furthermore, let''s say you want to make a
list
> of tags, the size of each being a function of the number of resources
> associated with that tag. You''d have tables that look roughly like
this:
> 
> create table resource (
>      resource_id serial primary key,
>      name text,
>      resource_location text
> );
> 
> create table tag (
>      tag_id serial primary key,
>      name
> );
> 
> create table resource_tag (
>      resource_id int references resource,
>      tag_id int references tag
> );
> 
> With ActiveRecord, if I want to get what I want I have to iterate
> through each tag, get the resources, and then count them. That is
> very expensive. It takes more than five seconds with a modest data
> set on my PowerBook.
> 
> Here''s the SQL that returns that I wound up executing directly:
> 
> select tag_id, name, count(*)
>      from resource_tag
>      natural join tag
>      group by tag_id, name;
> 
> It runs in 0.15 seconds or so on my PowerBook. The solution is not
> (apropos of the Mac mini thread) to get a faster computer: The answer
> is to use the awesome power of the relational database whenever it
> makes sense.
> 
> I realize that the ActiveRecord approach gets more data than the SQL
> query, but it would be nice to not have to restrict my use of
> ActiveRecord to CRUDly stuff that isn''t that challenging to deal
with
> anyway. Am I missing something about AR that lets me do what I want?
You can use plain old SQL with ActiveRecord.

Look up ActiveRecord#find_by_sql

On Aug 6, 2005, at 1:39 PM, Michael Schuerig wrote:
>
> Okay, let''s say you convinced me. Where do I learn how to do all
this?
> I''m using PostgreSQL (and that other DBMS), last I looked, the
docs
> have some reference on writing stored procedures and triggers, but
I''m
> not aware of any tutorial that teaches all the important points:
>
> - how to actually write stored procedures
> - application design with SPs and triggers; what goes where (app/db)
> - error handling
>
> Obviously there are people who know how to do this, but I have the
> impression they learned their skills on commercial DBMSs and
> transferred them to PgSQL. I''ve repeatedly looked for advanced
PgSQL
> books -- without success. What are recommended sources to learn about
> this stuff?
_Practical PostgreSQL_ from O''Reilly is pretty good. The online  
documentation at postgresql.org is also not bad. PostgreSQL 8.0 made  
writing stored procedures much easier thanks to new string quoting  
syntax that makes typing four or six single quotes in a row a thing  
of the past. The book covers 7.x.

Learning this stuff isn''t a cakewalk, but neither is learning Rails  
or Ruby for that matter. There''s probably more good documentation  
along with sample code and mailing list archives out there for  
PostgreSQL than RoR.

Regards,
Ed

-- 
Transmogrify, LLC * <http://xmog.com/>

That is still a shortcoming of AR with habtm, something I''ve discussed
with a few people on IRC.  AR is so clean and intuitive in nearly
every respect, save for habtm.  It should be able to automatically do
the joins so we can continue to code the Rails way.

I''ve been thinking of trying to add this in myself, but am hoping that
someone a lot smarter than me gets to it :)



On 8/6/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 8/6/05, Ed Watkeys <edw-tIV1OJqwIcc@public.gmane.org> wrote:
> >
> > On Aug 6, 2005, at 1:55 PM, Joe Van Dyk wrote:
> >
> > >
> > > I suppose the pragmatic thing to do would be to write the
business
> > > logic using ActiveRecord first.  Then, if it looks like
you''re going
> > > to be forced to move to some super cool new technology in the
future,
> > > then reevaluate the decision to keep the business logic in the
> > > database or at a higher level.
> >
> > If I have a problem with Rails right now, it''s that it
doesn''t seem
> > to want to let the database do many useful things that databases are
> > very good at. For example, just say you have a resource with a tag,
> > just like del.icio.us. Furthermore, let''s say you want to
make a list
> > of tags, the size of each being a function of the number of resources
> > associated with that tag. You''d have tables that look roughly
like this:
> >
> > create table resource (
> >      resource_id serial primary key,
> >      name text,
> >      resource_location text
> > );
> >
> > create table tag (
> >      tag_id serial primary key,
> >      name
> > );
> >
> > create table resource_tag (
> >      resource_id int references resource,
> >      tag_id int references tag
> > );
> >
> > With ActiveRecord, if I want to get what I want I have to iterate
> > through each tag, get the resources, and then count them. That is
> > very expensive. It takes more than five seconds with a modest data
> > set on my PowerBook.
> >
> > Here''s the SQL that returns that I wound up executing
directly:
> >
> > select tag_id, name, count(*)
> >      from resource_tag
> >      natural join tag
> >      group by tag_id, name;
> >
> > It runs in 0.15 seconds or so on my PowerBook. The solution is not
> > (apropos of the Mac mini thread) to get a faster computer: The answer
> > is to use the awesome power of the relational database whenever it
> > makes sense.
> >
> > I realize that the ActiveRecord approach gets more data than the SQL
> > query, but it would be nice to not have to restrict my use of
> > ActiveRecord to CRUDly stuff that isn''t that challenging to
deal with
> > anyway. Am I missing something about AR that lets me do what I want?
> 
> You can use plain old SQL with ActiveRecord.
> 
> Look up ActiveRecord#find_by_sql
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Aug 6, 2005, at 2:50 PM, Joe Van Dyk wrote:
>
> You can use plain old SQL with ActiveRecord.
>
> Look up ActiveRecord#find_by_sql
But in this case, I only want what is essentially metadata: the count  
of resources for each tag. In this case the number of resources a tag  
has becomes an attribute of the tag. I guess I could create a view  
that would make this accessible to AR as e.g. resource_count.

Regards,
Ed

-- 
Transmogrify, LLC * <http://xmog.com/>

Alright well let me first point out that I don''t actually know this
stuff :)  What I said just comes from discussions I''ve had with other
hackers.  Lately I''ve been thinking about the merits of moving more
logic to the db, starting off with very simple business logic that
just ensures referential integrity.

So what I said isn''t a recommendation as much as it is just providing
some ideas I''ve been having, hoping that someone with more experience
can come (in)validate them and explain why.

I''ve heard that the creatively titled "PostgreSQL" by Korry
Douglas is
supposed to be very good.  The second edition was published just a few
weeks ago, I''m planning on picking it up.

Spamming the pg mailing lists with newbie questions will probably help
you out too.

Pat



On 8/6/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
wrote:
> On Saturday 06 August 2005 19:03, Pat Maddox wrote:
> > If a reasonable
> > portion of the business logic is offloaded to the db, then you ensure
> > that no future apps circumvent the business rules.  Also, any changes
> > to the business rules can be updated in the db and are then
> > propogated through any client apps you may have written, with minor
> > changes to the client code necessary your interfaces aren''t
tight and
> > clean.
> 
> Okay, let''s say you convinced me. Where do I learn how to do all
this?
> I''m using PostgreSQL (and that other DBMS), last I looked, the
docs
> have some reference on writing stored procedures and triggers, but
I''m
> not aware of any tutorial that teaches all the important points:
> 
> - how to actually write stored procedures
> - application design with SPs and triggers; what goes where (app/db)
> - error handling
> 
> Obviously there are people who know how to do this, but I have the
> impression they learned their skills on commercial DBMSs and
> transferred them to PgSQL. I''ve repeatedly looked for advanced
PgSQL
> books -- without success. What are recommended sources to learn about
> this stuff?
> 
> Michael
> 
> --
> Michael Schuerig                       All good people read good books
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                Now
your conscience is clear
> http://www.schuerig.de/michael/ --Tanita Tikaram, Twist In My Sobriety
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Aug 6, 2005, at 3:02 PM, Ed Watkeys wrote:
>
> On Aug 6, 2005, at 2:50 PM, Joe Van Dyk wrote:
>
>>
>> You can use plain old SQL with ActiveRecord.
>>
>> Look up ActiveRecord#find_by_sql
>>
>
> But in this case, I only want what is essentially metadata: the  
> count of resources for each tag. In this case the number of  
> resources a tag has becomes an attribute of the tag. I guess I  
> could create a view that would make this accessible to AR as e.g.  
> resource_count.
I don''t normally respond to myself, but I sort of solved the problem  
for now. I created a view (in PostgreSQL):

create view meta_tag as
     select
             tag_id as meta_tag_id,
             name,
             count(*) as resource_count
         from resource_tag
         natural join tag
         group by tag_id, name;

I then created a MetaTag class, which is just like the regular Tag  
class but has a resource_count attribute. This model class is read- 
only, so it''s not ideal, but it sure beats having to re-write code  
that used AR to refer to integer subscripts of an array for attribute  
access. Now in my list method in my tag controller, I just do this:

     @tags = MetaTag.find(:all) # was originally Tag.find(:all)

And then replace a_tag.resource.size() with a_tag.resource_count.  
Again, not perfect, but good enough.

Regards,
Ed


-- 
Transmogrify, LLC * <http://xmog.com/>

On Saturday 06 August 2005 20:53, Ed Watkeys wrote:
> On Aug 6, 2005, at 1:39 PM, Michael Schuerig wrote:
[Learning about using(!) PgSQL stored procs and triggers]
> _Practical PostgreSQL_ from O''Reilly is pretty good.
I''ve repeatedly looked at the online version. It''s a bit thin
on these
particular topics.
> Learning this stuff isn''t a cakewalk, but neither is learning
Rails
> or Ruby for that matter. There''s probably more good documentation
> along with sample code and mailing list archives out there for
> PostgreSQL than RoR.
I don''t expect it to be a cakewalk, but from my POV, there''s a
glaring
absence of books on this stuff. There''s an incredible number of books 
on multi-tier application architecture and design, technology-agnostic 
as well as related to specific products.

I may be mistaken, but my impression is that rise of Java, application 
servers, and other middleware, the more advanced DB backend stuff has 
been relegated to some dusty corner. With a new technology such as 
Rails, I''m perfectly willing to accept that there may be little 
documentation and hunt what is there. With something as established as 
DBMSs presumably are, I prefer to pay for a good book.

Michael

-- 
Michael Schuerig                       All good people read good books
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                Now your
conscience is clear
http://www.schuerig.de/michael/ --Tanita Tikaram, Twist In My Sobriety

On Saturday 06 August 2005 21:03, Pat Maddox wrote:
> I''ve heard that the creatively titled "PostgreSQL" by
Korry Douglas
> is supposed to be very good.  The second edition was published just a
> few weeks ago, I''m planning on picking it up.
Thanks for bringing this to my attention. I''ve just ordered it. 
Amazon.com appears to have the best price internationally.
> Spamming the pg mailing lists with newbie questions will probably
> help you out too.
As explained in a sibling message, that''s something I''d rather
not do
for an established technology. There I expect a more economical way of 
gathering information is available.

Michael

-- 
Michael Schuerig                            You can twist perceptions
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                       
Reality won''t budge
http://www.schuerig.de/michael/               --Rush, Show Don''t Tell

[How about some quoting discipline...]


On Saturday 06 August 2005 20:57, Pat Maddox wrote:
> > > Here''s the SQL that returns that I wound up executing
directly:
> > >
> > > select tag_id, name, count(*)
> > >      from resource_tag
> > >      natural join tag
> > >      group by tag_id, name;
> That is still a shortcoming of AR with habtm, something I''ve
> discussed with a few people on IRC.  AR is so clean and intuitive in
> nearly every respect, save for habtm.  It should be able to
> automatically do the joins so we can continue to code the Rails way.
>
> I''ve been thinking of trying to add this in myself, but am hoping
> that someone a lot smarter than me gets to it :)
Well, let''s start with how you would *like* to express your query in 
Rails. That is, specify the "programmer interface" you want. Maybe we 
can then find a way to implement it.

Michael

-- 
Michael Schuerig                              Life is just as deadly
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                          
As it looks
http://www.schuerig.de/michael/          --Richard Thompson, Sibella

I hope we''re not going to get into that big top-quoting debate again.

Anyway, I made a thread a while back about doing finds on habtm. 
Can''t find it because the gmane search seems to be broken.  But
basically what I wanted to do was search within a collection.

Item has_and_belongs_to_many Tags
Tag has_and_belongs_to_many Items

So now let''s say I get a particular tag and want to get all the items
that belong to that tag that have the field ''available'' set to
true.
The intuitive way to do that is
tag.items.find(:all, :conditions => "available is true")

And in fact that works if a tag has_many items...the SQL that gets
generated looks like
SELECT * FROM items WHERE tag_id=#{tag.id} AND available IS TRUE

The problem is that the habtm has a lookup table, so a item doesn''t
have a tag_id, and you get a bad statement.  It''s pretty easy to
perform the logic by using a join...but you have to create a SQL
statement manually to do it.  I think that AR should automatically do
the join when doing stuff like that in a habtm.

Pat




On 8/6/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
wrote:
> 
> [How about some quoting discipline...]
> 
> 
> On Saturday 06 August 2005 20:57, Pat Maddox wrote:
> 
> > > > Here''s the SQL that returns that I wound up
executing directly:
> > > >
> > > > select tag_id, name, count(*)
> > > >      from resource_tag
> > > >      natural join tag
> > > >      group by tag_id, name;
> 
> > That is still a shortcoming of AR with habtm, something I''ve
> > discussed with a few people on IRC.  AR is so clean and intuitive in
> > nearly every respect, save for habtm.  It should be able to
> > automatically do the joins so we can continue to code the Rails way.
> >
> > I''ve been thinking of trying to add this in myself, but am
hoping
> > that someone a lot smarter than me gets to it :)
> 
> Well, let''s start with how you would *like* to express your query
in
> Rails. That is, specify the "programmer interface" you want.
Maybe we
> can then find a way to implement it.
> 
> Michael
> 
> --
> Michael Schuerig                              Life is just as deadly
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                     
As it looks
> http://www.schuerig.de/michael/          --Richard Thompson, Sibella
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Michael,

I''m with you: nothing beats a good book. Your point from a couple of  
posts ago was spot on: A lot of people come from Oracle knowing how  
to do all of this stuff, and then basically learn the delta between  
PostgreSQL and Oracle. I definitely had a better handle on PostgreSQL  
stored procedures after I spent some quality time on an Oracle  
project that prominently featured stored procedures. One of things I  
like about PostgreSQL is that it''s much easier to move stuff back and  
forth between Oracle and it then between Oracle and MySQL.

I forgot one important thing: it''s very easy to create stored  
procedures in PostgreSQL using SQL as the procedure language. You can  
accomplish a lot without using Pg/SQL. For example:

create function
     country_shipping_method(text, text, numeric, character)
     returns setof shipping_method
     as $_$
         insert into shipping_method
             (shipping_method_name,
              shipping_method_description,
              cost)
             values ($1, $2, $3);
         insert into shipping_method_country_code_rel
             values
                
(currval(''shipping_method_shipping_method_id_seq''),
                  $4);
         select *
             from shipping_method
             where
                 shipping_method_id                    
currval(''shipping_method_shipping_method_id_seq'');
$_$ language sql;

Regards,
Ed


-- 
Transmogrify, LLC * <http://xmog.com/>

Hi,

there is one very beautiful utilization of updateable view of pgsql.
Imagine situation, when you have table with binary data which you use
rarely. In my scenario there is table with images, every item handles,
name of image, description, width, height, etc... and binary payload
of the image itself.

Following the rule "make it run, make if fast", I was wondering how
slow it was to list more images at once. After some hacking of
undocumented ActionRecord procedures I replaced default "select * from
images" with specific column select without binary data.
Effect: at least 10x faster.

I had to do more and more hacking to support all CRUD operations in
effective way, but this made my code ugly and not following
specification to support future versions of rails.

With PGSQL, there is another solution, and I think this is far more
beautiful". Use updateable view of original images table ommiting
binary data column. Create new descendant of Image (ActiveRecord), say
MetaImage, which uses updateable view and not the original table. Use
MetaImage everywhere you don''t need that binary data.

Few lines of SQL definition of updateable view made lots of ugly rails
code obsolete.
This is not possible with MySQL4, afaik.
And you don''t tie to specific DB vendor, just need that updateable
views.

On 8/6/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 8/5/05, Grant Johnson
<grant-ousXCV6JwGd54TAoqtyWWQ@public.gmane.org> wrote:
> 
> Thanks for the informative post!
> 
> > Why use MySQL:
> > Your application has few concurrent queries, and speed is of the
> > esscense, and you have very little horsepower to do it with.
> >
> > Why use PostgreSQL:
> > More functionality OUTSIDE of the Rails application.  Stored
procedures
> > (functions), views (even useful under Rails), real transactions
(imagine
> > an application that takes transactions all day via rails and then
batch
> > processes them).
> 
> When should you use database views with Rails?
> 
> And I''ve heard that stored procs were sort of evil.  Dunno why
though.
> 
> I can see how real database transactions would be useful if you had to
> access the database without going through ActiveRecord, but I''d
> probably want to use a webservice for external access.  If I used a
> webservice that went through ActiveRecord, would I avoid the need for
> real db transactions?
> 
> Thanks,
> Joe
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I wonder if your approach to modifying AR points out a possible  
shortcoming in AR that might be easily patched.
If there were a way, per model, to specify a list of columns to use  
instead of the catchall, we could set this easily per model. The  
default could still be to select everything, I believe.

To select just the id and titles, for instance, we might do something  
like:

:include_columns [:id, :title]

To select all but one (such as your example) we might do something like:

:exclude_columns[:binary_data]

This was a design problem I hit last year with a PHP5 ORM type  
library. I never wanted to pull all data for all records, but never  
quite found an elegant way of handling attempts to access data that  
had yet to be loaded. My "final" approach was to do as-needed selects
if an object hadn''t yet been populated, but this became a little  
tricky with default values in classes folks were creating.

It would be nice (if it isn''t currently possible) to be a little less  
greedy in order to optimize things... but without having to go  
straight to find_by_sql.





On Aug 6, 2005, at 5:05 PM, Rastislav Kassak wrote:
> Hi,
>
> there is one very beautiful utilization of updateable view of pgsql.
> Imagine situation, when you have table with binary data which you use
> rarely. In my scenario there is table with images, every item handles,
> name of image, description, width, height, etc... and binary payload
> of the image itself.
>
> Following the rule "make it run, make if fast", I was wondering
how
> slow it was to list more images at once. After some hacking of
> undocumented ActionRecord procedures I replaced default "select * from
> images" with specific column select without binary data.
> Effect: at least 10x faster.
>
> I had to do more and more hacking to support all CRUD operations in
> effective way, but this made my code ugly and not following
> specification to support future versions of rails.
>
> With PGSQL, there is another solution, and I think this is far more
> beautiful". Use updateable view of original images table ommiting
> binary data column. Create new descendant of Image (ActiveRecord), say
> MetaImage, which uses updateable view and not the original table. Use
> MetaImage everywhere you don''t need that binary data.
>
> Few lines of SQL definition of updateable view made lots of ugly rails
> code obsolete.
> This is not possible with MySQL4, afaik.
> And you don''t tie to specific DB vendor, just need that updateable
> views.
>
> On 8/6/05, Joe Van Dyk
<joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>> On 8/5/05, Grant Johnson
<grant-ousXCV6JwGd54TAoqtyWWQ@public.gmane.org> wrote:
>>
>> Thanks for the informative post!
>>
>>
>>> Why use MySQL:
>>> Your application has few concurrent queries, and speed is of the
>>> esscense, and you have very little horsepower to do it with.
>>>
>>> Why use PostgreSQL:
>>> More functionality OUTSIDE of the Rails application.  Stored  
>>> procedures
>>> (functions), views (even useful under Rails), real transactions  
>>> (imagine
>>> an application that takes transactions all day via rails and then  
>>> batch
>>> processes them).
>>>
>>
>> When should you use database views with Rails?
>>
>> And I''ve heard that stored procs were sort of evil.  Dunno why
>> though.
>>
>> I can see how real database transactions would be useful if you  
>> had to
>> access the database without going through ActiveRecord, but
I''d
>> probably want to use a webservice for external access.  If I used a
>> webservice that went through ActiveRecord, would I avoid the need for
>> real db transactions?
>>
>> Thanks,
>> Joe
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Your ":exclude_columns[:binary_data]" is very similar to my last
version before using view: "find :all, :without => [:binary_data]".
:))

As I was diving into rails internals solving this scenario, I found
some places where the ActionRecord could be more flexible to utilize
advances of better databases. But I postponed this area of interrest
until I could call myself rails guru.

I could be only happy if there are more people with this kind of thought.


On 8/6/05, Toby Boudreaux <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org>
wrote:
> I wonder if your approach to modifying AR points out a possible
> shortcoming in AR that might be easily patched.
> If there were a way, per model, to specify a list of columns to use
> instead of the catchall, we could set this easily per model. The
> default could still be to select everything, I believe.
> 
> To select just the id and titles, for instance, we might do something
> like:
> 
> :include_columns [:id, :title]
> 
> To select all but one (such as your example) we might do something like:
> 
> :exclude_columns[:binary_data]
> 
> This was a design problem I hit last year with a PHP5 ORM type
> library. I never wanted to pull all data for all records, but never
> quite found an elegant way of handling attempts to access data that
> had yet to be loaded. My "final" approach was to do as-needed
selects
> if an object hadn''t yet been populated, but this became a little
> tricky with default values in classes folks were creating.
> 
> It would be nice (if it isn''t currently possible) to be a little
less
> greedy in order to optimize things... but without having to go
> straight to find_by_sql.
> 
> 
> 
> 
> 
> On Aug 6, 2005, at 5:05 PM, Rastislav Kassak wrote:
> 
> > Hi,
> >
> > there is one very beautiful utilization of updateable view of pgsql.
> > Imagine situation, when you have table with binary data which you use
> > rarely. In my scenario there is table with images, every item handles,
> > name of image, description, width, height, etc... and binary payload
> > of the image itself.
> >
> > Following the rule "make it run, make if fast", I was
wondering how
> > slow it was to list more images at once. After some hacking of
> > undocumented ActionRecord procedures I replaced default "select *
from
> > images" with specific column select without binary data.
> > Effect: at least 10x faster.
> >
> > I had to do more and more hacking to support all CRUD operations in
> > effective way, but this made my code ugly and not following
> > specification to support future versions of rails.
> >
> > With PGSQL, there is another solution, and I think this is far more
> > beautiful". Use updateable view of original images table ommiting
> > binary data column. Create new descendant of Image (ActiveRecord), say
> > MetaImage, which uses updateable view and not the original table. Use
> > MetaImage everywhere you don''t need that binary data.
> >
> > Few lines of SQL definition of updateable view made lots of ugly rails
> > code obsolete.
> > This is not possible with MySQL4, afaik.
> > And you don''t tie to specific DB vendor, just need that
updateable
> > views.
> >
> > On 8/6/05, Joe Van Dyk
<joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> >> On 8/5/05, Grant Johnson
<grant-ousXCV6JwGd54TAoqtyWWQ@public.gmane.org> wrote:
> >>
> >> Thanks for the informative post!
> >>
> >>
> >>> Why use MySQL:
> >>> Your application has few concurrent queries, and speed is of
the
> >>> esscense, and you have very little horsepower to do it with.
> >>>
> >>> Why use PostgreSQL:
> >>> More functionality OUTSIDE of the Rails application.  Stored
> >>> procedures
> >>> (functions), views (even useful under Rails), real
transactions
> >>> (imagine
> >>> an application that takes transactions all day via rails and
then
> >>> batch
> >>> processes them).
> >>>
> >>
> >> When should you use database views with Rails?
> >>
> >> And I''ve heard that stored procs were sort of evil. 
Dunno why
> >> though.
> >>
> >> I can see how real database transactions would be useful if you
> >> had to
> >> access the database without going through ActiveRecord, but
I''d
> >> probably want to use a webservice for external access.  If I used
a
> >> webservice that went through ActiveRecord, would I avoid the need
for
> >> real db transactions?
> >>
> >> Thanks,
> >> Joe
> >> _______________________________________________
> >> Rails mailing list
> >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >> http://lists.rubyonrails.org/mailman/listinfo/rails
> >>
> >>
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Sebastian A. Espindola wrote:
>On 05/08/05, Toby Boudreaux
<rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> wrote:
>  
>
>>Postgres has lots of stuff MySQL still doesn''t have.
>>Views, triggers, sequences, stored procs, cursors...
>>    
>>
>
>Postgresql 8.0 also has updatable views, tablespaces, savepoints, HA
Clustering
>(with Slony), etc. It''s the most advanced Open Source DB available,
>and is slowly
>catching up to Oracle.
>
>  
>
also see recent extensions to support large data sets in data 
warehousing applications

http://pgfoundry.org/forum/forum.php?forum_id=475

and

“Greenplum, JasperSoft and Kinetic Networks Launches First Development 
Stack”

http://www.b-eye-network.com/view/1287?jsessionid=07f861b45e3745160584331d371ceb17

Published: August 5, 2005

(Article URL: http://www.b-eye-network.com/view/1287)


Nitin Borwankar




_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Michael Schuerig wrote:
>
>As explained in a sibling message, that''s something I''d
rather not do
>for an established technology. There I expect a more economical way of 
>gathering information is available.
>
>  
>
actually there is a newbie postgres list with a tagline
 "no question is considered too simple"

http://archives.postgresql.org/pgsql-novice/

Nitin Borwankar
>Michael
>
>  
>


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

>>> Postgres has lots of stuff MySQL still doesn''t have.
>>> Views, triggers, sequences, stored procs, cursors...
>>>   
>>
>>
>> Postgresql 8.0 also has updatable views, tablespaces, savepoints, HA 
>> Clustering (with Slony), etc. It''s the most advanced Open
Source DB
>> available,
>> and is slowly
>> catching up to Oracle.
>>
>>  
>>
>
Catching up, in some ways, in others, surpassing!   The PostgreSQL 
method of handling multiple version concurrency works much better than 
Oracle''s rollback segements (Snapshot Too Old anyone?) and
PostgreSQL''s
partial indexes are exceptional.  You still can ''t even invent your own
datatypes or do arrays in Oracle that I have seen.

Also, on large, unweildy, multi-way joins on really big, tables, it 
smokes Oracle (Oracle - no result in 10+hours, PostgreSQL, result in 
under 15 minutes, same data, same table structures, same indexes, fresh 
statistics on both, Oracle on Solaris on 22CPU''s with 16G+RAM and fibre
SAN, PostgreSQL on single Athlon XP2200+ with 1G RAM and SATA X2 in RAID 
mirror).   I have not had the opportunity (yet) to really test 
insert/update throughput, but from what I have seen, it keeps up.   BTW, 
on the large, unweildy queries, DB2 smokes everything I have used.

PostgreSQL is, however, powerful enough, more powerful than some, faster 
than most, and very low maintenance.  It also runs everywhere.

Pat Maddox wrote:
>I think you''re a lot more likely to write another app, perhaps in
>Ruby, perhaps in another language, that operates on existing data,
>then you are to switch over to another database.  
>
You might be right, but I doubt either is very common.  What is much 
more common is that you or someone maintaining your code will wish to 
change this existing business logic.
>If a reasonable
>portion of the business logic is offloaded to the db, then you ensure
>that no future apps circumvent the business rules.  
>
You also ensure that maintainence of the code becomes much more 
difficult, not only can they not circumvent the business rules they also 
cannot change them.  Code at the application level is much more 
maintainable then tracking down some hidden database trigger.  Whats 
worse is if the business logic is divided between the DB and 
application, then any changes must occur in multiple places in multiple 
technologies.
>Also, any changes
>to the business rules can be updated in the db and are then propogated
>through any client apps you may have written, with minor changes to
>the client code necessary your interfaces aren''t tight and clean.
>
>Pat
>  
>
What if all applications don''t wish to see the same change? 
It''s much
easier in the application layer to use 
inheritence/composition/polymorphism to determine which clients will see 
the change in business rules and which will not.

-John
>
>
>On 8/5/05, Michael Teter
<michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>  
>
>>[snip]
>>    
>>
>>>And I''ve heard that stored procs were sort of evil.  Dunno
why though.
>>>      
>>>
>>[/snip]
>>
>>I''ll answer this one.
>>
>>Using stored procs ties you to a specific vendor.  It also means you
>>have business logic in your database.  Depending on the scale of your
>>app, that can be too cumbersome (and it won''t be taking
advantage of
>>your OO world).
>>
>>On the plus side, sometimes it''s very convenient, and
it''s always
>>faster than any alternative (assuming everything everywhere is done
>>reasonably).
>>
>>Personally I like the Rails (and generally-accepted multi-tiered)
>>idea, which is to make the database layer as dumb as possible, and to
>>keep the business logic in the app layer.  Then it''s much
easier to
>>change database vendors, and you can leverage your object model.  The
>>downside is it''s much much slower than dirty low-level code
(which
>>includes db stored procs).  Human time > CPU time, within reason :)
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>    
>>
>_______________________________________________
>Rails mailing list
>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>  
>

-- 
John Towell - Agile Technology Group
(e) jtowell-zbO79nAUJZuStSZJieJLidBPR1lH4CV8@public.gmane.org
(m) 415-595-4954

I''ve been using PostgreSQL for several projects now but my latest two 
are actually using MySQL, mainly because a) MySQL is more wide-spread b) 
The frontend tools for PostgreSQL suck, stuff like copying db''s between
hosts, SSH tunneling just isn''t present yet. If there were better 
frontend tools for PostgreSQL it would clearly be my db of choice 
(anyone tried to get UTF-8 working in MySQL lately? ;))

SIMEN BREKKEN / born to synthesize.

Joe Van Dyk wrote:
> Assuming that I already know how to somewhat use and administer a
> mysql database, what are the (pragmatic) reasons that would move me
> over to postgres?  (assuming mysql 4.1 here)
> 
> For discussion purposes, say I''m developing a small-scale
e-commerce website.
> 
> I like sqlite, but there seem to be more GUIs available for easily
> editing msyql databases.  And migrations don''t yet work with
sqlite.
> 
> Thanks,
> Joe

On Fri, 2005-08-05 at 19:09 -0500, Michael Teter wrote:
> I''m going to add some potentially useless data from my experience.
> This is only about PostgreSQL - I have 0 experience with MySQL.
> 
> I''ve been a long time PostgreSQL user, but I''ve rarely
had tables over
> 50k lines.
FWIW, RubyForge runs on PostgreSQL; it has about 2.5 million records in
the database and one table has about 750K records.

Yours,

tom

I wonder how long a database dump/load takes.  In my fairly simple
case, it was pretty poor (which is in contrast to how fast the DB is
during normal CRUD operation via apps)

On 8/8/05, Tom Copeland <tom-rlPNtO1eV9h54TAoqtyWWQ@public.gmane.org>
wrote:
> On Fri, 2005-08-05 at 19:09 -0500, Michael Teter wrote:
> > I''m going to add some potentially useless data from my
experience.
> > This is only about PostgreSQL - I have 0 experience with MySQL.
> >
> > I''ve been a long time PostgreSQL user, but I''ve
rarely had tables over
> > 50k lines.
> 
> FWIW, RubyForge runs on PostgreSQL; it has about 2.5 million records in
> the database and one table has about 750K records.
> 
> Yours,
> 
> tom
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Mon, 2005-08-08 at 10:19 -0500, Michael Teter wrote:
> I wonder how long a database dump/load takes.  In my fairly simple
> case, it was pretty poor (which is in contrast to how fast the DB is
> during normal CRUD operation via apps)
Just timed one on RubyForge, it took about 43 seconds (2.5M records,
database backup file is 50MB compressed).  This nice thing is that you
don''t have to shut down the database to dump a consistent backup since
PostgreSQL has that PITR thingy.

Yours,

tom

I''m talking about more basic business rules.  How about a banking app
where a rule is that no account balance can ever be negative.  Throw
that in the DB, and then no application can violate it.  If the
business rules change, the apps automatically change along with them.

The problem with not keeping that kind of logic in the db is that a
buggy/outdated/whatever program can insert invalid data to the db, and
now every client application is corrupted.

Pat



On 8/7/05, John Towell
<jtowell-zbO79nAUJZuStSZJieJLidBPR1lH4CV8@public.gmane.org>
wrote:
> 
> Pat Maddox wrote:
> 
> >I think you''re a lot more likely to write another app, perhaps
in
> >Ruby, perhaps in another language, that operates on existing data,
> >then you are to switch over to another database.
> >
> You might be right, but I doubt either is very common.  What is much
> more common is that you or someone maintaining your code will wish to
> change this existing business logic.
> 
> >If a reasonable
> >portion of the business logic is offloaded to the db, then you ensure
> >that no future apps circumvent the business rules.
> >
> You also ensure that maintainence of the code becomes much more
> difficult, not only can they not circumvent the business rules they also
> cannot change them.  Code at the application level is much more
> maintainable then tracking down some hidden database trigger.  Whats
> worse is if the business logic is divided between the DB and
> application, then any changes must occur in multiple places in multiple
> technologies.
> 
> >Also, any changes
> >to the business rules can be updated in the db and are then propogated
> >through any client apps you may have written, with minor changes to
> >the client code necessary your interfaces aren''t tight and
clean.
> >
> >Pat
> >
> >
> What if all applications don''t wish to see the same change? 
It''s much
> easier in the application layer to use
> inheritence/composition/polymorphism to determine which clients will see
> the change in business rules and which will not.
> 
> -John
> 
> >
> >
> >On 8/5/05, Michael Teter
<michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> >
> >>[snip]
> >>
> >>
> >>>And I''ve heard that stored procs were sort of evil. 
Dunno why though.
> >>>
> >>>
> >>[/snip]
> >>
> >>I''ll answer this one.
> >>
> >>Using stored procs ties you to a specific vendor.  It also means
you
> >>have business logic in your database.  Depending on the scale of
your
> >>app, that can be too cumbersome (and it won''t be taking
advantage of
> >>your OO world).
> >>
> >>On the plus side, sometimes it''s very convenient, and
it''s always
> >>faster than any alternative (assuming everything everywhere is done
> >>reasonably).
> >>
> >>Personally I like the Rails (and generally-accepted multi-tiered)
> >>idea, which is to make the database layer as dumb as possible, and
to
> >>keep the business logic in the app layer.  Then it''s much
easier to
> >>change database vendors, and you can leverage your object model. 
The
> >>downside is it''s much much slower than dirty low-level
code (which
> >>includes db stored procs).  Human time > CPU time, within reason
:)
> >>_______________________________________________
> >>Rails mailing list
> >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >>http://lists.rubyonrails.org/mailman/listinfo/rails
> >>
> >>
> >>
> >_______________________________________________
> >Rails mailing list
> >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> 
> 
> --
> John Towell - Agile Technology Group
> (e) jtowell-zbO79nAUJZuStSZJieJLidBPR1lH4CV8@public.gmane.org
> (m) 415-595-4954
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

> > >>I''ll answer this one.
> > >>
> > >>Using stored procs ties you to a specific vendor.  It also
means you
> > >>have business logic in your database.  Depending on the scale
of your
> > >>app, that can be too cumbersome (and it won''t be
taking advantage of
> > >>your OO world).
> > >>
> > >>On the plus side, sometimes it''s very convenient, and
it''s always
> > >>faster than any alternative (assuming everything everywhere is
done
> > >>reasonably).
> > >>
> > >>Personally I like the Rails (and generally-accepted
multi-tiered)
> > >>idea, which is to make the database layer as dumb as possible,
and to
> > >>keep the business logic in the app layer.  Then it''s
much easier to
> > >>change database vendors, and you can leverage your object
model.  The
> > >>downside is it''s much much slower than dirty
low-level code (which
> > >>includes db stored procs).  Human time > CPU time, within
reason :)

This really just depends on the situation.  Where I work we handle
large volumes of financial data, and we have found it''s better to keep
a good amount of validation in the database.   We use postgresql and
all database queries are done via database functions or procedural
languages with heavy use of views, rules, and triggers.   Only a
couple of people have access to changing them, whereas we have several
dozen developers that have access to various parts of our application
codebase.  It''s also a security layer.  Applications can only access
functions, they dont'' have access to the underlying tables.   No one
layer of security is all encompassing, you have to use multiple
layers, and the database is just another layer we can take advantage
of.  We also issue client certificates t our customers, and then grab
them when a customer logs into our web interface and then use it when
connecting to postgresql which requires client certificates.

Chris

I would agree with chris fully on this.  I also work at a financial
institute where ALL database access uses stored procedures.

Personally i think validations should be placed both places.  its good
to see in your code what kind of validations are going on (and
prevents you from having to catch exceptions everywhere.)  Also in the
database to prevent anything retarded happening *just in case*.  Im
sure everyone of you have decided that fixing a tiny bug in your
database can be done by hand and not to worry about it... and im sure
one of those times you will screw up.

and ill agree again like a lot of people hate to admit,  its very
unlikely your going to change your database before writing another app
to interact with the database.  and IF just IF you do change your
database, what are the chances your going to downgrade from a db that
supports stored procs/views/functions to one that does not.

id like to see someone *upgrade* from postgresql -> mysql and have
good reasons for doing so.

the database isnt JUST to hold data inside of it.  validate ALL your
data at the lowest level possible.


On 8/9/05, snacktime <snacktime-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> > > >>I''ll answer this one.
> > > >>
> > > >>Using stored procs ties you to a specific vendor.  It
also means you
> > > >>have business logic in your database.  Depending on the
scale of your
> > > >>app, that can be too cumbersome (and it won''t be
taking advantage of
> > > >>your OO world).
> > > >>
> > > >>On the plus side, sometimes it''s very
convenient, and it''s always
> > > >>faster than any alternative (assuming everything
everywhere is done
> > > >>reasonably).
> > > >>
> > > >>Personally I like the Rails (and generally-accepted
multi-tiered)
> > > >>idea, which is to make the database layer as dumb as
possible, and to
> > > >>keep the business logic in the app layer.  Then
it''s much easier to
> > > >>change database vendors, and you can leverage your object
model.  The
> > > >>downside is it''s much much slower than dirty
low-level code (which
> > > >>includes db stored procs).  Human time > CPU time,
within reason :)
> 
> 
> This really just depends on the situation.  Where I work we handle
> large volumes of financial data, and we have found it''s better to
keep
> a good amount of validation in the database.   We use postgresql and
> all database queries are done via database functions or procedural
> languages with heavy use of views, rules, and triggers.   Only a
> couple of people have access to changing them, whereas we have several
> dozen developers that have access to various parts of our application
> codebase.  It''s also a security layer.  Applications can only
access
> functions, they dont'' have access to the underlying tables.   No
one
> layer of security is all encompassing, you have to use multiple
> layers, and the database is just another layer we can take advantage
> of.  We also issue client certificates t our customers, and then grab
> them when a customer logs into our web interface and then use it when
> connecting to postgresql which requires client certificates.
> 
> Chris
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Zachery Hostens <zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>

I agree.
This is one point where *strict* adherence to the DRY ideal should be  
considered impractical. Security and data fidelity are of supreme  
importance, and checks should be done at all practical points, as  
Zachery is stating.



On Aug 9, 2005, at 5:18 PM, Zachery Hostens wrote:
> the database isnt JUST to hold data inside of it.  validate ALL your
> data at the lowest level possible.
>


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Zachery Hostens wrote:
>I would agree with chris fully on this.  I also work at a financial
>institute where ALL database access uses stored procedures.
>
>Personally i think validations should be placed both places.  its good
>to see in your code what kind of validations are going on (and
>prevents you from having to catch exceptions everywhere.)  Also in the
>database to prevent anything retarded happening *just in case*.  Im
>sure everyone of you have decided that fixing a tiny bug in your
>database can be done by hand and not to worry about it... and im sure
>one of those times you will screw up.
>
>and ill agree again like a lot of people hate to admit,  its very
>unlikely your going to change your database before writing another app
>to interact with the database.  and IF just IF you do change your
>database, what are the chances your going to downgrade from a db that
>supports stored procs/views/functions to one that does not.
>
>id like to see someone *upgrade* from postgresql -> mysql and have
>good reasons for doing so.
>
>the database isnt JUST to hold data inside of it.  validate ALL your
>data at the lowest level possible.
>
>
>On 8/9/05, snacktime
<snacktime-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>  
>
>>>>>>I''ll answer this one.
>>>>>>
>>>>>>Using stored procs ties you to a specific vendor.  It
also means you
>>>>>>have business logic in your database.  Depending on the
scale of your
>>>>>>app, that can be too cumbersome (and it won''t
be taking advantage of
>>>>>>your OO world).
>>>>>>
>>>>>>On the plus side, sometimes it''s very
convenient, and it''s always
>>>>>>faster than any alternative (assuming everything
everywhere is done
>>>>>>reasonably).
>>>>>>
>>>>>>Personally I like the Rails (and generally-accepted
multi-tiered)
>>>>>>idea, which is to make the database layer as dumb as
possible, and to
>>>>>>keep the business logic in the app layer.  Then
it''s much easier to
>>>>>>change database vendors, and you can leverage your
object model.  The
>>>>>>downside is it''s much much slower than dirty
low-level code (which
>>>>>>includes db stored procs).  Human time > CPU time,
within reason :)
>>>>>>            
>>>>>>
>>This really just depends on the situation.  Where I work we handle
>>large volumes of financial data, and we have found it''s better
to keep
>>a good amount of validation in the database.   We use postgresql and
>>all database queries are done via database functions or procedural
>>languages with heavy use of views, rules, and triggers.   Only a
>>couple of people have access to changing them, whereas we have several
>>dozen developers that have access to various parts of our application
>>codebase.  It''s also a security layer.  Applications can only
access
>>functions, they dont'' have access to the underlying tables.  
No one
>>layer of security is all encompassing, you have to use multiple
>>layers, and the database is just another layer we can take advantage
>>of.  We also issue client certificates t our customers, and then grab
>>them when a customer logs into our web interface and then use it when
>>connecting to postgresql which requires client certificates.
>>
>>Chris
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>    
>>
>
>
>  
>
I''ve told in another thread that database is *just* to store the data, 
and all other functions should be kept in the app level. But, I partly 
agree w/ you in this case. Rules and referential integrity, at database 
level, is needed in some applications. Actually, referential integrity 
should be applied for all applications, but sometimes we forget about it 
(specially when we use mysql ;-) ).
But I still think that to keep business logic in database is evil :-) 
Note the slight difference between "validation" and "business
logic".

Regards,
Juraci Krohling Costa
http://jkcosta.info
juca at jkcosta dot info

...but it''s also good for developers who see the database as nothing
more than a fancy data store. That''s why it''s been good for
web apps.
The web interface can kind of hide some of the lacking features of
MySQL, where most of the web developers end up writing ACID code in
the application domain anyways (and would with any other database).

If you have any plans at all of having different access paths into the
database, then use a more full-blown database besides MySQL. A lot of
the code you might write for ensuring multi-table inserts, etc., in
the application can then just be done in the database once and for
all. You can even chameleonize the application - interface and data
are through views and stored procedures, and you pull a page from SAP
and do the rest of the schema in Swahili or Tagalog.

But MySQL could still be a good part of your web apps. For example, a
web app that has lots of users could benefit from simple very fast
username/password hash lookups in MySQL, with the rest of the app done
with a different DBMS.

-Corey Lawson

On 8/5/05, Clayton Cottingham <dr.frog-fVOoFLC7IWo@public.gmane.org>
wrote:
> Mysql is mom and pop
> 
> Its ok for simple queries, but as soon as you need something more
industrial
> strength I suggest moving to postgres
> 
> It is far superior in a lot of areas. Including transactions, scabability
> and custom functions to name a few
> 
> 
> 
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Pat Maddox
> Sent: August 5, 2005 1:27 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] [OT] Postgres vs Mysql
> 
> OMFG you MORON.  MySQL is teh_r0x0r!!
> 
> Postgres is teh suck
> 
> go back to PHP, retard
> 
> 
> 
> On 8/5/05, Colin Fleming
<colin.mailinglist-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 05/08/05, Tobias Luetke
<tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Please stop trolling.
> >
> > POSTGRES IS TEH R0XXX0R
> >
> > MYSQL IS TEH SUCK
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>