Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.
At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.
Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.
PostgreSQL is definitely better. I heard that the new version of MySQL
will have some of the new features you ask, but its still in beta stages.
Theres also an opensource DB called Firebird, which is based on source
code released of Interbase 6.x I think (might be saying nonsense here tough).
–lf
On Tue, Apr 20, 2004 at 11:04:10PM +0900, Ruby Tuesdays wrote:
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.
RubyForge runs about on PostgreSQL and I’ve been really happy with it so
far. RubyForge is not a busy database - only about a query per second
or so, only about 400K records - but it’s doing what we need it to do.
Yours,
Tom
On Tue, 2004-04-20 at 10:04, Ruby Tuesdays wrote:
Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.
It’s fab. The main/only reason to use MySQL is everyone and
their dog has support for it in their php webmail/whatever.
–
For your penance, say five Hail Marys and one loud BLAH!
Rasputin :: Jack of All Trades - Master of Nuns
Ruby Tuesdays wrote:
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.
Using postgres and satisfied (I couldn’t work without triggers).
I heard that with tables with millions of entries, postgres could become rather slow.
I used postgres both with the ruby-dbi and native interfaces without problems.
I program the functions used by the triggers in plpgsql (there’s a project to let you program the postgres functions in ruby, but it was unstable last time I looked at it).
Firebird (which is indeed coming from Interbase) is also a possibility, but I have no experience with it.
Raph
I love PostgreSQL as well, and my main gripe with it is speed. I often
run into situations where classes correspond nicely to tables (like a
User class to a users table), so I’m tempted to write code which would
automatically query the database for the neccessary information in its
initialize. However, this gets quite slow because it becomes hard to
combine simple queries into complex ones (which execute much much
faster). I have made optimizations around this, but they’re not as nice
to use as just pure data encapsulation.
This is in no way meant to discourage you from using PostgreSQL, merely
inform you on its biggest limitation I see. It has too many other
merits to be given up (triggers, decent transaction support, so on).
As a friend has told me again and again, always choose data integrity
over speed.
Lo-lee-ta: the tip of the tongue taking a trip of three
steps down the palate to tap, at three, on the teeth.
Lo. Lee. Ta. GUO Shu-yu shu@rufuran.org
Ruby Tuesdays wrote:
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any other choice beside the
two? Thanks.
Postgresql is VERY robust and VERY rich with features.
You’ll love it. I know I do. 
Triggers are implemented. As are serial (autonumber), PK, FK, and all other
types of constraints you can conjure up.
A very strong language for scripting stored procedures is implemented too.
(named: plpgsql)
exporting, backing up, everything is there.
Redhat even developed a very nice GUI for Postgresql 7.3, which they call
RHDB (RedHatDataBase) which is actually Postgresql 7.3 (i didn’t find any
differences)
It is called Database Administrator for Postgres Redhat edition3.
I am still running their alpha, but it works like a charm, so why update?
I hope I sound enthousiastic enough, because I really like postgresql.
Regards,
Erwin Moller
“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@ID-205437.news.uni-berlin.de…
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.
You do not need stored procedures or database triggers to write successful
applications. I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible to
keep track of what was being fired where.
Another reason I prefer to put all my business logic into PHP code instead
of triggers is that PHP code is a lot easier to debug. Have you come across
an interactive debugger for database procedures and triggers?
–
Tony Marston
Is PostgreSQL any better/worse? Or is that any other choice beside the
two?
Thanks.
You might also look at what was the SAP database that MySQL took over
and renamed MaxDB. Look at http://www.mysql.com/products/maxdb/
It has:
Views
Server-side cursors
Stored procedures and triggers
Auto fallover to standby server
and others
I haven’t used but it is worth checking out.
Rick
Ruby Tuesdays wrote:
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.
–
Have you noticed that a “slight tax increase” costs us at least two
hundred dollars a year, but a “substantial tax cut” saves us maybe
thirty cents?
Hi,
The latest version of Firebird (1.5) has been
rewritten in C++ and has been very much improved in
the process. It’s a very lightweight but powerful
(R)DBMS. I advise anyone interested to take a look at
their sites:
The only “problem” is to use ODBC to access it.
Cheers,
Joao
— Luis Felipe Strano Moraes ra016681@ic.unicamp.br
wrote:
PostgreSQL is definitely better. I heard that the
new version of MySQL
will have some of the new features you ask, but its
still in beta stages.
Theres also an opensource DB called Firebird, which
is based on source
code released of Interbase 6.x I think (might be
saying nonsense here tough).–lf
On Tue, Apr 20, 2004 at 11:04:10PM +0900, Ruby > Tuesdays wrote:
Perhaps you database guru able to suggest what
would be a good choice for
opensource database platform to use to develop
projects.At the moment the project is small/medium, but it
will grow in size both
data, users, and number of transactions. I’m using
MySQL for right now but
it lack of trigger, stored procedures, etc … it
sometimes slows the
project.Is PostgreSQL any better/worse? Or is that any
other choice beside the two?
Thanks.
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25¢
http://photos.yahoo.com/ph/print_splash
If one needs the speed, it’s a faster database than PostgreSQL, and the
feature gap continues to narrow between it and PostgreSQL. If you don’t
need the speed, though, there is a lot of love out in netland for
PostgreSQL.
Kirk Haines
On Wed, 21 Apr 2004, Dick Davies wrote:
Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.It’s fab. The main/only reason to use MySQL is everyone and
their dog has support for it in their php webmail/whatever.
I’m not convinced by the speed argument- I haven’t found anything quicker
for the data I typically store. In other words: postgresql has always been
so fast that I haven’t needed to care. Having seen postgres outperform
sybase by a factor of 10 when building objects from stored data, it’s
clear that a lot depends on the resources you dedicate to the database
rather than the database software itself (in ideal conditions, I’m sure
the race would be much closer).
As well as the rich data types and operations supported in postgresql, the
big win is PL/ruby which means you can write triggers in ruby. It’s great
for embedding those tricky bits of business logic and data manipulation in
the database itself.
Cheers,
Mike
On Wed, 21 Apr 2004, Shu-yu Guo wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1I love PostgreSQL as well, and my main gripe with it is speed. I often
run into situations where classes correspond nicely to tables (like a
User class to a users table), so I’m tempted to write code which would
automatically query the database for the neccessary information in its
initialize. However, this gets quite slow because it becomes hard to
combine simple queries into complex ones (which execute much much
faster). I have made optimizations around this, but they’re not as nice
to use as just pure data encapsulation.This is in no way meant to discourage you from using PostgreSQL, merely
inform you on its biggest limitation I see. It has too many other
merits to be given up (triggers, decent transaction support, so on).As a friend has told me again and again, always choose data integrity
over speed.
–
Mike Z zofa@wyer.org - it’s an apathy thing
There are a couple problems here, and a few solutions to them, as
well. I’ll shamelessly plug Criteria here. Make sure you use the SVN
version:
http://svn.mephle.org/svn/mephle/criteria/
That will help you combine your queries; I do this regularly:
class Customer
# This is the table, you probably want to use a DBITable though.
IDX = SQLTable.new(“my_table”, …)
CUSTOMER_NAME = (IDX.name == IDX.x0?) # .x0? is a placeholder
CUSTOMER_BEFORE = (IDX.date < IDX.x0?)
# :
# : more queries
# :
end
Later on we can combine them:
query = CUSTOMER_NAME[“Bob”] & CUSTOMER_BEFORE[1975]
You get the idea. However this is only one of your problems; the
other is the fact it’s very slow to do a query everytime you want an
attribute, either getting or setting. For this I would recommend
“caching” things; make an attr_sql_accessor or the like which (perhaps
using Criteria… I do this too) makes note of when the value changes,
but only updates the database on a flush, and/or tie in transactions
(especially nice with PgSQL’s MVCC). This should keep you from having
3 lines like this:
customer.name = “Larry”
customer.date = 1998
customer.address = “…”
…generate 3 UPDATE statements, but only one instead. The only thing
you need to watch out for is making sure someone doesn’t update the
database behind you; for this I’m not sure what you can do. (Maybe
some magic with stored ruby procedures and triggers… dunno.)
On Wed, 21 Apr 2004 06:38:06 +0900 Shu-yu Guo shu@rufuran.org wrote:
I love PostgreSQL as well, and my main gripe with it is speed. I
often run into situations where classes correspond nicely to tables
(like a User class to a users table), so I’m tempted to write code
which would automatically query the database for the neccessary
information in its initialize. However, this gets quite slow because
it becomes hard to combine simple queries into complex ones (which
execute much much faster). I have made optimizations around this,
but they’re not as nice to use as just pure data encapsulation.
–
Ryan Pavlik rpav@mephle.com
“Feh. At least we can agree that if anything goes wrong,
it was due to your acute negativism” - 8BT
Tony Marston wrote:
“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@ID-205437.news.uni-berlin.de…Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now
but it lack of trigger, stored procedures, etc … it sometimes slows the
project.You do not need stored procedures or database triggers to write successful
applications. I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible
to keep track of what was being fired where.Another reason I prefer to put all my business logic into PHP code instead
of triggers is that PHP code is a lot easier to debug. Have you come
across an interactive debugger for database procedures and triggers?
Very good advise, Tony.
I second that opinion 100%.
Been there too. 
Stick to constrainst like foreign keys (which do fire a trigger I think when
the relevant colums are updated/inserted) and avoid writing your own.
And stick to constraints like CHECK to make sure the data inserted makes
sense to you instead of triggers.
Stored procedures do often shorten the developmenttime, but are hard to
debug.
It is often easy to do ‘some quick and dirty’ solution using stored
procedures, but when something changes in the application (which happens
all the time) you have to debug those SP’s too.
Regards,
Erwin Moller
“Tony Marston” tony@NOSPAM.demon.co.uk writes:
You do not need stored procedures or database triggers to write successful
applications. I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible to
keep track of what was being fired where.
Right! Gratuitous use of triggers, SPs and the like results in
indirection that can make a system impossible to understand. Use with
care.
But is this a vote in favor of a less advanced system that prevents
the temtation? I don’t think so.
YMMV
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
May be we can get away with the strored procedures. Why would one do
manipulation of the data inside a database engine, right? The function of
database is to store and retrieve information, that’s it. Beyond that you
really stretch it and this where scripting language such as Ruby comes in.
But for trigger, to write a commercial grade transaction processing
application, you definitely need trigger. Trigger will aid
programmers/designer to ensure certain consistency checking happen
before/after certain opearation. How would you do that outside the database
engine withouth kludging it?
Just my 2cents back from my college days.
For simple and less-dynamic-update application, you might not need tri
“Tony Marston” tony@NOSPAM.demon.co.uk wrote in message
news:c65jm0$nl$1$8302bc10@news.demon.co.uk…
“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@ID-205437.news.uni-berlin.de…Perhaps you database guru able to suggest what would be a good choice
for
opensource database platform to use to develop projects.At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now
but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.You do not need stored procedures or database triggers to write successful
applications. I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible
to
keep track of what was being fired where.Another reason I prefer to put all my business logic into PHP code instead
of triggers is that PHP code is a lot easier to debug. Have you come
across
an interactive debugger for database procedures and triggers?
–
Tony MarstonIs PostgreSQL any better/worse? Or is that any other choice beside the
two?
Thanks.
tony@NOSPAM.demon.co.uk says…
“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@ID-205437.news.uni-berlin.de…Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.
Sorry, can’t confidently recommend. Am spoilt, have Oracle.
At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I’m using MySQL for right now but
it lack of trigger, stored procedures, etc … it sometimes slows the
project.You do not need stored procedures or database triggers to write successful
applications.
But they can be very beneficial.
I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible to
keep track of what was being fired where.
Bad design/code is bad design/code no matter at what “layer” it is
written. Doesn’t mean you should throw the baby out with the bath
water.
Another reason I prefer to put all my business logic into PHP code instead
of triggers is that PHP code is a lot easier to debug.
I use stored procedures and triggers for things like serialized
transactions, audit logs and complex calculations. In general (in Oracle
at least) nearly everything that can be done inside the “database
level” is more efficient, sometimes massively so, than the same thing
written at the PHP “application level”.
I’ve fought similar anti-database-level-coding arguments against the use
of database referential integrity constraints.
Have you come across
an interactive debugger for database procedures and triggers?
For Oracle many exist, if you have the necessary $$$.
Geoff M
Tony Marston wrote:
You do not need stored procedures or database triggers to write successful
applications. I once had to maintain a system that was built around
procedures and triggers, and it was a nightmare. The problem was that one
trigger/procedure updated several tables, which fired more triggers which
contained more updates which fired more triggers … It was impossible to
keep track of what was being fired where.
Design of complex systems is, necessarily, complex. One approach is to
consolidate all of the logic at one layer – but that can result in
significant performance differences for an app that uses stored
procedures / triggers / functions to avoid communications overhead of
the client-server interactions and to take advantage of the built-in
optimizations the SQL engine can use for stored procedures / functions.
Another reason I prefer to put all my business logic into PHP code instead
of triggers is that PHP code is a lot easier to debug. Have you come across
an interactive debugger for database procedures and triggers?
Not quite on topic, because it’s not an open source database, but DB2
does includes an interactive debugger for stored procedures in the DB2
Development Center
(http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0007399.htm)
In some cases I would argue that issuing a couple of CALL and SELECT
statements is a lot easier than trying to figure out whether you’ve
introduced a problem in your PHP code or in your SQL statements within
the PHP code.
Dan
There are a couple problems here, and a few solutions to them, as
well. I’ll shamelessly plug Criteria here. Make sure you use the SVN
version:
quite nice 
but is sqlite post-setup.rb and the test scripts
uptodate?
it produce errors.
i’ve change to try
$SQLITE = SQLite::Database.new(dbfile, 0)
but then it’s a bit to heavy for me
-bash-2.05b$ ruby post-setup.rb
Skipping DBI tests
Loaded suite post-setup
Started
…F
Finished in 0.132559 seconds.
create' ./t/sqlite.rb:10:in test_sqlite’assert_nothing_raised' ./t/sqlite.rb:14:in test_sqlite’16 tests, 65 assertions, 1 failures, 0 errors
maybe it helps.
regards
Karl-Heinz
There are a couple problems here, and a few solutions to them, as
well. I’ll shamelessly plug Criteria here. Make sure you use the SVN
version:http://svn.mephle.org/svn/mephle/criteria/
That will help you combine your queries; I do this regularly:
class Customer
# This is the table, you probably want to use a DBITable though.
IDX = SQLTable.new(“my_table”, …)CUSTOMER_NAME = (IDX.name == IDX.x0?) # .x0? is a placeholder CUSTOMER_BEFORE = (IDX.date < IDX.x0?) # : # : more queries # :end
Later on we can combine them:
query = CUSTOMER_NAME[“Bob”] & CUSTOMER_BEFORE[1975]
Criteria is pretty slick. As an exercise in comparison, here’s how I’d do
it using Kansas:
ksdbh.table(‘Customer’,‘customer’) # args are classname, tablename
Then to do the query:
customers = ksdbh.select(:Customer) do |customer|
(customer.name == ‘Bob’) && (customer.date < 1975)
end
You get the idea. However this is only one of your problems; the
other is the fact it’s very slow to do a query everytime you want an
attribute, either getting or setting. For this I would recommend
“caching” things; make an attr_sql_accessor or the like which (perhaps
using Criteria… I do this too) makes note of when the value changes,
but only updates the database on a flush, and/or tie in transactions
(especially nice with PgSQL’s MVCC). This should keep you from having
3 lines like this:customer.name = “Larry”
customer.date = 1998
customer.address = “…”…generate 3 UPDATE statements, but only one instead. The only thing
you need to watch out for is making sure someone doesn’t update the
database behind you; for this I’m not sure what you can do. (Maybe
some magic with stored ruby procedures and triggers… dunno.)
This is trickier. The direction that I am taking with Kansas is to have a
level of transaction support.
ksdbh.transaction do
customer.name = ‘Larry’
customer.date = 1998
customer.address = ‘123 Trelona Circle’
ksdbh.commit
end
At the moment this is only partialy implemented, but what should be
happening is that Kansas takes care of initiating a transaction at the
database level to keep things from changing underneath us at that level.
It applies the attribute changes to the object, but wait until the commit
to roll them up and apply them all at once down at the database level
before issuing an echoing commit() on the database. For DBI backends that
don’t support transactions, one will still have the transactional support,
including the ability to rollback changes to the objects, at the Ruby
level, and one would still have the rolling up of multiple updates into a
single update.
This doesn’t address the problems of the database data changing underneath
the object and the object knowing nothing of it. The only alternatives
that I see are doing something with triggers or making each object so that
it can refresh itself to make sure it is in sync with the db.
customer.refresh
or something like that. And if there is a high likelyhood of the db
changing under and object, it might be worth the performance cost to have
something like:
customer.autorefresh
which would tell the object to check itself against the db before any
attribute is accessed. That could be a huge performance hit, though, and
could itself introduce problems. Tricky.
Kirk Haines
On Wed, 21 Apr 2004, Ryan Pavlik wrote: