Re: Database for gnome

[Peter Norton <spacey inch com>]

[This is a long rant. Skip if you don't like databases]

 On Fri, Feb 06, 1998 at 01:45:36PM -0500, J. Patrick Narkinsky wrote:
> On Fri, 6 Feb 1998, Miguel de Icaza wrote:
> 
> > Isn't sql standard?  
> > 
> > If it is not, we can just come up with a backend thing.  
> > 
> 
> Well, yes and no.

More no then less.  There are some fundamental problems with SQL as a
standard:
1) The ansi standard SQl is stuck 1 revision behind.  SQL 2 is outdated,
but SQL 3 apparently has enough political pet projects in it that all of
the major RDBMS vendors have vowed that that they're not going to bother
changing their systems.  So while you have a stndard, you will have many
implementations that use different parts of that standard.

2) The interesting technology that PostgreSQL builds on, object-relational
databases (not Object Oriented Databases, mind you - they're very
different beasts) allows for addition to and overloading of the base
types. You see, ansi sql only accounts for a *very* limited set of
operations on a *very* limited number of basic types - character,
varchars, integers, floats, dates, object id's.  Object Relationsal
Databases allow for adding datatypes within the database - reducing
copying of data, and no longer forcing developers to re-invent the wheel
each time they want to, i.e., put a gif and its mime-type into the
database (each database treats Binary Large OBjects, or BLOBs slightly
differently as well). However this ability isn't a part of ANSI sql. 

3) there are vague areas of SQL syntax that each vendor implements
differently.  For example, certian types of joins require a different
syntax between Oracle, Sybase, Microsoft SQL, and others. Some RDBMS's
reqire their own cruft for non-obvious reasons, but in common cases.  I.e.
different rules on what types and in what circumstances single and double
quotes are needed.

> While there is a standard for SQL (ANSI), there are a lot of variations in
> the way various vendors implement.  Even worse, there is no standard for
> communication between a client and a database (i.e. no real standard
> network protocol or anything). This is typically done (on UNIX) using a
> library which has a set of calls to send queries and retrieve the
> responses.  

Right, it's called the CLI - the Call Level Interface.  And each one is
completely different.  Blech.
 
> There is no real standard for these library calls.
> 
> ODBC is basically a standardized API for applications wanting to
> communicate with a database (and a rationalized version of SQL called
> "ODBCSQL"). While there are ODBC drivers for UNIX, near as I can gather
> they aren't really standardized on UNIX.  (In windoze, support is provided
> by Microsoft.)

However, ODBC doesn't *AT ALL* define a network or RPC interface.  It only
defines a uniform API that can be used to access any database's underlying
CLI.  This means that if you run M$'s SQL server w/ it's ODBC driver, and
try to use a driver build w/ another ODBC library, they won't be able to
talk.  Either you have to put M$'s ODBC driver on your system,a nd re-link
your program w/ it so it can talk to the server, or you have to install
the 3rd party ODBC driver on your database server and configure it to talk
to the database server.  It's an UGLY mess. 

I was toying w/ the idea of writing a transparent network proxy for the
FreeODBC project to allow me to work on an Oracle server from linux
(currently impossible w/o at least a $2k initial outlay to purchase
server, client, and development options from a commercial ODBC driver
company. The best part about ODBC is that more and more RDBMS' are using
ODBC as the native CLI, so it's gaining support.  For instance check out
http://www.kubl.com. 

The bright side to this is that there is a FreeODBC driver out there - it
works w/ mSQL, Oracle, and Solid (I think).  The only reason more RDBMS'
aren't supported is because more ports haven't been done.  If we can
extend and CORBA-ize it FreeODBC and add a couple of other RDMBS', that
should obviate my sockets-based proxy idea, and make ODBC standard on the
linux desktop. (Oh, the reason I never got anywhere with this was that I
couldn't get mSQL 2 to work on my glibc linux system - I'll have to try
again).
 
OBTW, perl has DBD::ODBC as well :)  

FreeODBC:
http://users.ids.net/~bjepson/freeODBC/

-Peter