From: Date: April 25 2005 5:41pm
Subject: RE: MYSQL to XML
List-Archive: http://lists.mysql.com/mysql/183082
Message-Id: <1114443707.426d0fbba0978@imp1-q.free.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi Mikel,
Show create table shows a line CONSTRAINT ... FORIEGN KEY ...

you can add a grep on this line. But this will be difficult. You can construct
another desc2xml using just "show create table" to have it easier.

Mathias

Selon Mikel - <ironmitss@stripped>:

> Thanx Mathias for your quick and effective response, I see that your program
> almost display the format that I need, the thing is that I need the foreign
> key information too, Does MySQL have a statement besides "show create table"
> to display this information?....Thanks again for your suggestions and help
>
> Greetings
>
> >From: "mathias fatene" <mfatene@stripped>
> >To: <mysql@stripped>
> >CC: <mysql@stripped>
> >Subject: RE: MYSQL to XML
> >Date: Sun, 24 Apr 2005 21:39:14 +0200
> >MIME-Version: 1.0
> >Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com
> >with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700
> >Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 -0000
> >Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -0000
> >Received: pass (lists.mysql.com: local policy)
> >X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
> >Mailing-List: contact mysql-help@stripped; run by ezmlm
> >List-ID: <mysql.mysql.com>
> >Precedence: bulk
> >List-Help: <mailto:mysql-help@stripped>
> >List-Unsubscribe:
> ><mailto:mysql-unsubscribe-ironmitss=hotmail.com@stripped>
> >List-Post: <mailto:mysql@stripped>
> >List-Archive: http://lists.mysql.com/mysql/183030
> >Delivered-To: mailing list mysql@stripped
> >X-MSMail-Priority: Normal
> >X-Mailer: Microsoft Outlook, Build 10.0.2616
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> >X-Virus-Checked: Checked
> >Return-Path: mysql-return-183030-ironmitss=hotmail.com@stripped
> >X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC)
> >FILETIME=[BE1839D0:01C54905]
> >
> >Hi Mikel,
> >There are a lot of possibilities including commercial (:o)) products.
> >I suggest you those solutions. The output should be reparsed for your
> >needs :
> >1. the -X on client :
> >     C:\Mysql>mysql -u mathias world -X -e "desc country"
> >     <?xml version="1.0"?>
> >
> >     <resultset statement="desc country">
> >       <row>
> >         <Field>Code</Field>
> >         <Type>char(3)</Type>
> >         <Null></Null>
> >         <Key>PRI</Key>
> >         <Default></Default>
> >         <Extra></Extra>
> >       </row>
> >       <row>
> >             <Field>Name</Field>
> >             <Type>char(52)</Type>
> >             <Null></Null>
> >             <Key></Key>
> >             <Default></Default>
> >             <Extra></Extra>
> >       </row>
> >
> >       <row>
> >             <Field>Continent</Field>
> >...
> >...
> >
> >2. install perl DBI and DBIx-XML_RDB modules :
> >#!perl -w
> ># ---------------------------------------------------------------
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date   : 24 april 2005
> ># ---------------------------------------------------------------
> >use DBIx::XML_RDB;
> >
> >  my $userid='root';
> >  my $password='**************';
> >  my $dbname='world';
> >  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> >  my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
> >"Failed to make new xmlout";
> >
> >   $xmlout->DoSql("describe country");
> >   print $xmlout->GetData;
> >
> >C:\Mysql>perl describe.pl
> ><?xml version="1.0"?>
> ><DBI driver="DBI:mysql:database=world;host=localhost">
> >         <RESULTSET statement="describe country">
> >                 <ROW>
> >                         <Field>Code</Field>
> >                         <Type>char(3)</Type>
> >                         <Null></Null>
> >                         <Key>PRI</Key>
> >                         <Default></Default>
> >                         <Extra></Extra>
> >                 </ROW>
> >                 <ROW>
> >                         <Field>Name</Field>
> >                         <Type>char(52)</Type>
> >                         <Null></Null>
> >                         <Key></Key>
> >                         <Default></Default>
> >                         <Extra></Extra>
> >                 </ROW>
> >                 <ROW>
> >...
> >...
> >3. install Perl DBI and DBD-Mysql and use my program (formatted for your
> >needs) :
> >#!perl -w
> ># ---------------------------------------------------------------
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date   : April, 24 2005
> ># ---------------------------------------------------------------
> >use DBI;
> >
> >  my $userid='root';
> >  my $password='************';
> >  my $dbname='world';
> >  my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> >  my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
> >   # ---------------------------------------------------------------
> >   # describe country table and print it in XML format
> >   # ---------------------------------------------------------------
> >   my $table="country";
> >   $sth = $dbh->prepare("describe $table");
> >   $sth->execute();
> >
> >   print "\<table name=\"$table\"\>\n";
> >   while (my @ref = $sth->fetchrow_array()) {
> >        print "\<column name=\"$ref[0]\" required=\"true\"
> >type=\"$ref[1]\"";
> >        print " primaryKey=\"true\"" if ($ref[3] eq "PRI") ;
> >        print "/\>\n";
> >   }
> >   $sth->finish();
> >   print "\</table\>\n";
> >
> >   # Disconnect from the database.
> >   $dbh->disconnect();
> >
> >C:\Mysql>perl desc.pl country
> ><table name="country">
> ><column name="Code" required="true" type="char(3)" primaryKey="true"/>
> ><column name="Name" required="true" type="char(52)"/>
> ><column name="Continent" required="true"
> >type="enum('Asia','Europe','North
> >America','Africa','Oceania','Antarctica','South America')"/>
> ><column name="Region" required="true" type="char(26)"/>
> ><column name="SurfaceArea" required="true" type="float(10,2)"/>
> ><column name="IndepYear" required="true" type="smallint(6)"/>
> ><column name="Population" required="true" type="int(11)"/>
> ><column name="LifeExpectancy" required="true" type="float(3,1)"/>
> ><column name="GNP" required="true" type="float(10,2)"/>
> ><column name="GNPOld" required="true" type="float(10,2)"/>
> ><column name="LocalName" required="true" type="char(45)"/>
> ><column name="GovernmentForm" required="true" type="char(45)"/>
> ><column name="HeadOfState" required="true" type="char(60)"/>
> ><column name="Capital" required="true" type="int(11)"/>
> ><column name="Code2" required="true" type="char(2)"/>
> ></table>
> >
> >is this beautifull ?
> >
> >I will modify Describe2xml.pl to be more parametrized (user, db, pass,
> >FK, ...) as soon as possible.
> >
> >Mathias
> >
> >
> >
> > >>  Hi list, does it possible for MySQL to generate XML in the followin
> >format:
> > >>
> > >>  <table name="ServiceType">
> > >>      <column name="idTipoServicio" primaryKey="true"
> > >>              required="true" type="VARCHAR" size="10"/>
> > >>      <column name="nombre" required="true" type="VARCHAR"
> >size="255"/>
> > >>      <column name="costo" required="true" type="FLOAT" size="9"/>
> > >>      <column name="idGrupo" required="true" type="INTEGER"/>
> > >>      <column name="activa" required="true" type="BOOLEANINT"/>
> > >>
> > >>      <foreign-key foreignTable="Grupo" onUpdate="none"
> >onDelete="none">
> > >>          <reference foreign="idGrupo" local="idGrupo"/>
> > >>      </foreign-key>
> > >>    </table>
> > >>
> > >>  This XML is the structure of the ServiceType table,  I'll hope that
> >you >>   can
> > >>  help me
> > >>
> > >>  Thnx in advanced
> > >>
> > >>  Greetings
> > >>
> > >>  P.S. Any suggestions (tools) will be appreciated
> > >>
> > >>
> > >>
> > >>  Thread
> > >>
> > >>      * MySQL to XML - Mikel -, April 23 2005 1:07am
> > >>
> >
> >
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:    http://lists.mysql.com/mysql?unsub=ironmitss@stripped
> >
>
>
>