When writing SQL for Oracle, for example, I often make use of the more
powerful functions like DECODE(), TO_CHAR(), SIGN(), etc. These functions
could be quite difficult to make portable to databases that don't support
user-defined database functions. Especially if these functions are used
within the WHERE, ORDER BY, or HAVING clauses-- not just the SELECT-clause.
For example, would the following SQL statement be translate-able to any
database? It creates a result set with 4 columns: team_nbr, team_name, Gp,
and LongFmtTime.
select t.team_nbr
,t.team_name
,sum(decode(sign(t.team_nbr-g.home_team)
/* HOME */
,0,decode(sign(g.home_score-g.away_score)
, NULL,0 /* HOME > AWAY W */
,1
)
/* AWAY */
,decode(sign(g.away_score-g.home_score)
, NULL,0 /* AWAY > HOME */
,1
)
)
) Gp
,to_char(g.game_datetime,'Month Day, YYYY HH:MI.ss') as LongFmtTime
from team t
,game g
where t.season_nbr = ?
and (t.team_nbr=g.home_team or t.team_nbr=g.away_team)
group by team_nbr,team_name
I think pretending to support conversion of SQL to "any" database isn't
feasible. One of the main reasons for selecting one database over another
are features such those listed above. You want to control the data layer to
take advantage of the underlying database's strengths while avoiding the
weaknesses.
--Steve
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 25, 2002 6:08 PM
To: [EMAIL PROTECTED]
Subject: [PROPOSAL] crossdb
crossdb Project
Current site: www.crossdb.com
Version 0.1 beta
(0) rationale
Different databases have different SQL syntax, features, etc. This creates
a problem when trying to a write a program because you will have
to change your code to work with different databases. Or if you are using
an O/R tool that can use multiple databases such as Torque, you are
still faced with having to rebuild all your classes and scripts to work with
the new database and are stuck with the limitations of the O/R model.
So what is needed is a powerful, efficient way to create SQL statements in
database independent manner and that can support complex queries.
(1) scope
crossdb is a Java API for writing object oriented SQL statements that are
database independent. This means that you create SQL statement objects that
will run on any database without having to change your code when moving to a
different database.
crossdb is an abstraction of SQL at a low level supporting simple and
complex SQL queries. In fact, any query you could write in SQL, you could,
in theory, write using crossdb in a database independent manner.
crossdb does not cover things like making connections to the database,
pooling, or anything like that. It is simply to make SQL statements.
crossdb is easy to use, even more so than learning SQL syntax.
(2) crossdb and Jakarta
crossdb is a natural fit with Jakarta and it's mission, because almost every
server side application requires database connectivity and now applications
can easily be database independent.
crossdb would offer any Jakarta subprojects that require database
connectivity, true database independence. They would not require rebuilding
and thus make these subprojects more along the lines of "write once, run
anywhere".
(3) current state
crossdb is ready to use with it's current functionality and is being used in
some production environments. How many people are using it in the public is
unknown, but it is being used.
(3.1) background
crossdb started open source, is not orphaned, and has no reliance on
salaried developers. crossdb loves the Apache Software Foundation and
especially Jakarta.
(3.2) todo's
crossdb could benefit immediately by other developers in the community
working on the following:
- crossdb specification (ie: the interfaces, functionality required, etc)
- other database implementations
- currently have implementations for
- mysql
- Oracle
- MS SQL Server
- Interbase (probably Firebird too)
(4) resources required
(4.1) mailing lists
crossdb-user
crossdb-dev
(4.2) CVS repositories
jakarta-crossdb
(5) current developers
Travis Reeder
Gunter Sammet
Kevin (unknown)
Any feedback would be greatly appreciated.
Travis Reeder
Chief Software Architect
www.ThinkVirtual.com
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
