[BioSQL-l] Re: Postgres version support

[Jason E. Stewart]

"Chris Mungall" <cjm@fruitfly.org> writes:

> personally I'm in favour of keeping the core schema in SQL (without
> all the horrible mysqlisms), but we could easily convert to an XML
> representation. In what sense would XML-schema definition allow for
> a more powerful description language?
> 
> I'm interested in the schema definition schema though - I'm rewriting the
> SQL DDL parser along with Ken Clark @ cshl and would like to use this as
> another format

Hey Chris,

A couple of thoughts in no particular order:

- parsing XML is a lot easier than SQL (to me anyway)
- The XML representation can be truly DB independent
- It enables other useful features - the XML representation can be
  converted by a simple XSLT transformation into a set of browsable
  HTML files, it can be used to create a set of HTML forms and CGI
  scripts that enable users to annotate their data, and it can be used
  to auto-generate a low-level Perl object API for the DB

However, the major reason was because Genex auto-creates it's object
API from the DB schema, I discoverd I needed extra information about
foreign key relationships that wasn't in the DDL SQL. I needed
cardinality information: one-to-one, many-to-one, and
many-to-many. That way, when I create a foreign-key accessor method in
the API, I know how many objects I'm getting back from the DB, a
scalar or an array ref. 

Also, it is important for the INSERT code - since the API code is
auto-generated it has to be generic. If a foreign key accessor is a
many-to-one relationship I need first myself (so I set a primary key)
and then insert the list of objects into the other table (so they can
reference my primary key). If it's one-to-one, I first insert the
other object (so it gets a primary key) and then myself (so I can
reference the primary key).

Cheers,
jas.
--
 "Naturally the common people don't want war: Neither in Russia, nor
 in England, nor for that matter in Germany. That is understood. But,
 after all, it is the leaders of the country who determine the policy
 and it is always a simple matter to drag the people along, whether it
 is a democracy, or a fascist dictatorship, or a parliament, or a
 communist dictatorship. Voice or no voice, the people can always be
 brought to the bidding of the leaders.  That is easy. All you have to
 do is tell them they are being attacked, and denounce the peacemakers
 for lack of patriotism and exposing the country to danger. It works
 the same in any country." - Hermann Goering