I think it's better to pick one database and try to design and code
to its strengths and weaknesses.
I've worked on several projects in which the overall architectural
plan involved writing a database abstraction layer to keep options
open as to the specific relational db - MySQL, Oracle, MS SQL, etc.
None of these projects has gone particularly well. I've worked on
other projects where the focus was on making things work well with
whatever database was chosen and not worrying so much about keeping
the options open, and these projects have gone much better. And in
neither case has the project actually ever switched databases, so the
effort spent in trying to preserve compatibility was wasted.
If you stick to the lowest common denominator in your SQL you won't
be taking advantage of any of the unique benefits which the different
servers bring. MySQL is my favorite database. For web work it has
several significant advantages over other databases - the LIMIT
clause alone is unique to MySQL and it's worth using MySQL just for
that. For example, if you use MS SQL and .Net, the entire results of
queries are sent from the DB server to the web server and pagination
is done on the web server. This moves a lot more data over network
connections, ties up more memory in the db and web server, and slows
things down. With the LIMIT clause, only the rows to be displayed on
the current page are sent over the wire. If you try to use only the
ANSI standard features of MySQL you will miss out on this advantage.
Here is an interesting article about Google's switch from MySQL to
Oracle for AdWords: http://xooglers.blogspot.com/2005/12/lets-get-
real-database.html I believe (can anyone from the big G confirm or
correct?) that AdWords has been moved back to MySQL.
Oracle has some neat features for handling trees and hierarchical
data (CONNECT BY etc) which won't work in any other version of SQL.
If you're going to use Oracle and you have data which is best
represented in trees, it would be a mistake to not use Oracle's built-
in tree features. Oracle is very nice, but it doesn't give you any
speed over MySQL and you can put a man on the moon for less money.
Microsoft SQL Server is very nice if you like that sort of thing and
Sybase is pretty much the same thing only the port numbers have been
changed to protect the innocent. I'm sure DB2 and Postgres and
Informix and all the others are very nice too.
Stored procedure syntax is significantly different among the major
dbs, so if you're going to limit yourself to standard sql there's no
point in using stored procedures. Which isn't really a bad thing on
the whole.
Good luck!
On Mar 22, 2006, at 11:30 PM, David Griffiths wrote:
That's a pretty difficult request:
1) What versions? MySQL 4.0 has no stored procedures, functions,
views, subselects. 4.1 added subselects. 5.0 added the rest. Oracle
8i doesn't support ANSI-style outer joins (they use a proprietary
format).
2) Different engines; MySQL supports federated, archive, MyISAM,
InnoDB in 5.0 (there are probably a few others). Each engine has
different DML (Data Manipulation Language - select, insert, update,
delete) and DDL options (ALTER TABLE, CREATE TABLE, etc).
3) Built-in funtions vary widely (though there are some common
ones, the format and structure can differ).
That's just touching the surface (I have 5 minutes while a database
machine reboots, so I thought I'd post a reply).
I am not sure what you are after, but you might want to consider an
existing ORM (Object-Relational) tool that does the SQL for you.
Hibernate for Java is amazing, and NHibernate is now out for .NET
(not sure if it's alpha, beta or production).
If you are coding to experiment, I'd suggest you limit yourself to
a few (MySQL-InnoDB is very popular, and Postgres). Both free, with
lots of good online-documentation available.
Check out this article:
http://www.devx.com/dbzone/Article/20743
David
ChadDavis wrote:
Does anyone know of a resource ( on the web perhaps ) that
discusses the
core differences between the different database's sql. I'm trying to
write
code that produces the correct sql for a variety of databases. Such
things
as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest.
Maybe
I'm asking too much to find a summary of such differences. But
I'm only
interested in using mainstream sql functinality, nothing complicated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Douglas Sims
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
