Re: SQL->XML mapping

--- Alan Knowles <alan@akbkhome.com> wrote: > Ouch, thats the best example I've seen of why committees should not get > involved in technology.. - that is a truly awfully written document.. :) Indeed it is awfully written. The reason why it has so many little specs is beacuase SQL is quite permissive w/ the naming of identifiers, e.g. "This is a column name: yeah, it is..." is a valid column name, and then you have the unnamed columnes (e.g. "select 2+2, firstname from users"), etc. A more reasonable (tho' a bit outdated) document is the one from Sybase: http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/xmlb/@Generic__BookTextView/4521;pt=4521;uf=0#X (namespace is old, and other minor things) > I've been chatting to Lukas on this, in context of reading about propel, > and needing to improve the store in DataObjects. > > DB_Schema - the famous solution :) > > I need to have another look at the code. - but in principle my view was > the class would be a data store which could load/save schemas in > multiple formats.. > eg. > DataObjects (and formbuilder) needs / may need: > = At class build time. > - get types, names etc. from database (not XML!) > - overlay user defined information (eg. links/descriptions/validation > rules/text entry field types/sizes) > * in ini files/ or xml.../ or whatever... > > =At run time > - get field types (alot) > - get autoincrement/primary keys (alot) > - get links/join info (less often) > - get other info (even less often) > > Looking through those requirements, DB_Schema could store it's data > similar to apache/db (eg. propel), and allow > loading from > a) a apache/db xml schema > b) database quering > c) ini files? *That* would be very nice indeed > And therefore write to > a) a apache/db xml schema > b) rich data xml (eg. data not related to name/type) > c) ini files > d) serialized arrays (eg. for speed) > e) classes (eg. DataObjects generator...) > > I guess the iso standard could be fitted in there somewhere.. - if > anyone could actually understand it :) I have some simple Java code on that one, can come up w/ the PHP version so you can then use it (cannot promise to maintain it due to time limitations). It does the SQL identifiers/values -> XML identifere/values mapping and the converse. I have not tackled the generation of XML Schemas from SQL Schemas. One thing that would make either (XML docs from resultsets, and XML Schemas from SQL Schemas) easier would be to have the Database absetraction layers (or related packages/classes) provide some meta-info (table, db, column type, etc.), and have a set of predefined SQL types that can be easily mapped to XML types, sort of what the JDBC ResultSetMetaData class provides in Java. > I've no idea how the original API looked, but this is what I was > thinking about. > > $x = new DB_Schema > // load merge .. file, type, options > $x->load('mysql:://.......','sql'); > $x->merge('somefile.xml','apacheDbXml'); > > $x->merge('somefile.ini','DataObject',array('format'=>'ini')); > > // to string - driver / options > $serial = $x->toString('DataObject',array('data' > =>'types','format'=>'serialize')); > $ini = $x->toString('DataObject',array('data' > =>'links','format'=>'ini')); > $ini = $x->toString('ApacheDbXml'); > > // update database to match schema.. > $x = new DB_Schema > $x->load('mysql:://.......','sql'); > $y = new DB_Schema > $y->load('somefile.xml','apacheDbXml'); > > $x->toString('sql',array('data'=>'diff','from'=>$x)); > .. or > > $x->toString('sqlxml',array('data'=>'diff','from'=>$x)); > (which would probably be the internal way to handle this..) > > > Regards > Alan > > > > > > Jesus M. Castagnetto wrote: > > >I've been working on a set of Java classes (for use at work) for mapping SQL > >identifiers and values from a database query resultset to XML according to > the > >current ISO/ANSI draft (1). > > > >Is there something similar that is planned for database abstraction layers > in > >PEAR? The spec gives the standards on how to the mappings for resultsets, as > >well as how to map a SQL schema into an XML schema. Not sure if the > >metainformation needed for the latter is supported in all db backends > >currently. > > > >(1) Document part of WG3 of the "Data Management and Interchange", see: > > > >http://www.iso.ch/iso/en/stdsdevelopment/tc/tclist/TechnicalCommitteeDetailPage.TechnicalCommitteeDetail?COMMID=160 > > > >and > > > >http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/e783d79e26bff9f388256b9f0061b6f5/b7ab89cadb9be5f288256d95005fcc47?OpenDocument > > > >(Yes, I know the URLs are very long, but so is the document: 293 pages) > > > > > >===== > >-- > >Jesus M. Castagnetto (jcastagnetto@yahoo.com) > >Research: http://metallo.scripps.edu/ > >Personal: http://www.castagnetto.org/ > >PEAR stuff: http://pear.php.net/user/jmcastagnetto > > > >__________________________________ > >Do you Yahoo!? > >Find out what made the Top Yahoo! Searches of 2003 > >http://search.yahoo.com/top2003 > > > > > > > > ===== -- Jesus M. Castagnetto (jcastagnetto@yahoo.com) Research: http://metallo.scripps.edu/ Personal: http://www.castagnetto.org/ PEAR stuff: http://pear.php.net/user/jmcastagnetto __________________________________ Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003 http://search.yahoo.com/top2003