Re: MySQL vs XML
That's an excellent paper. However, David (the OP) is not actually in control, nor is he designing his ontology He is attempting to build a persistence/retrieval system for the taxonomy (ontology) that the scientific community has already created to categorize life on our planet (Kingdom, Phylum, Class, Order, etc.). As I understand it, he is starting with the Animal kingdom. What I think will apply VERY WELL to his design process from that paper is the excellent introduction to some of the core concepts of object oriented (OO) development: the class, the instance, properties, and roles. I recommend it as a read that may help bridge the gap between real-world understanding and object conceptualization and data organization. I don't recall if the paper mentioned another OO concept, the method, but methods are merely actions a class can take within the framework of an application. In a descriptive data model, methods do not play a major role in describing the class (probably why it wasn't listed as part of an ontology). For example a document object may have a print method, something the object actually does within the bounds of the application, but the action itself provides little or no descriptive value about the document object itself. It usually trivial to take a well formed Object Model (such as you would derive by developing an ontology) and convert it to a relational database. Sure there are exceptions but the parallels between the two are generally very straight-forward. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dr kamadjeu raoul [EMAIL PROTECTED] wrote on 08/02/2005 10:27:21 PM: May be you should consider building an ontology with your data base. This links will provides ideas to explore this avenue: http://www.ksl.stanford.edu/people/dlm/papers/ontology-tutorial-noy- mcguinness-abstract.html Raoul David Blomstrom [EMAIL PROTECTED] wrote: I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: MySQL vs XML
Wow, this is turning into quite a research project. Thanks for the tip about ontologies; it doesn't make much sense to me yet, but I'll take a closer look at the article. In the meantime, I'm thinking of using a content management system called Plone. Unfortunately, I've so far been unable to install it, apparently because my computer has some kind of memory problem. When I get everything sorted out, I'll have to learn about recursive arrays, Plone, Zope, Python, XML and ontologies. Whew!!! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs XML
May be you should consider building an ontology with your data base. This links will provides ideas to explore this avenue: http://www.ksl.stanford.edu/people/dlm/papers/ontology-tutorial-noy-mcguinness-abstract.html Raoul David Blomstrom [EMAIL PROTECTED] wrote: I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: MySQL vs XML
Thanks for all the tips. That makes it much clearer. I think I'll stick with PHP and MySQL and gradually introduce a little XML if it fits in. I just downloaded a content management system called Plone, which is supposed to be a good choice for hierarchical databases. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs XML
Hi , First of all it should be clear that XML is only a well organised representation of data a mere text file. It is not a software . U will have to append entries all by urself to the file, and marking up suitable tags ( say the attribute of ur relational table) . Data will have to be extracted via suitable scripts ( viz php, python, perl etc) Database facilitates inthe way that u can access info easily with out much hues and cries . U can always create a XML representation with the data stored in ur database with proper scripts thus making ur task simpler. So it all depends on the kind of application u want. - Parag Agrawal B. Tech IIIT On 7/30/05, David Blomstrom [EMAIL PROTECTED] wrote: I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- When the going gets tough only the tough gets going -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs XML
MySQL is a relational database. XML is a text file. the biggest difference is that MySQL will let you organize, sort, match/link (joins), and otherwise manipulate the data you have. XML is just text with tags in a heirarchy; anything other than reading it in a text editor will take programming on your part. IMHO, XML is extremely overrated. it has its uses of course, but it is waay overused these days because of the buzz. David Blomstrom wrote: I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL to XML
Mathias Thanks for your help, I really appreciated it. And I was just wondering if MySQL has another statment (besides show create table) that only displays the foreign key, but I see that only with the show create table MyTable could get this. Greetings From: [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: RE: MYSQL to XML Date: Mon, 25 Apr 2005 17:41:47 +0200 MIME-Version: 1.0 X-Originating-IP: 213.41.126.253 Received: from lists.mysql.com ([213.136.52.31]) by mc5-f35.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 25 Apr 2005 08:44:24 -0700 Received: (qmail 21182 invoked by uid 109); 25 Apr 2005 15:41:56 - Received: (qmail 21160 invoked from network); 25 Apr 2005 15:41:55 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jHafVH/hFUFqKtbfGVCQG07u9r/IzplGqc= Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/183082 Delivered-To: mailing list mysql@lists.mysql.com References: [EMAIL PROTECTED] User-Agent: Internet Messaging Program (IMP) 3.2.5 X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Apr 2005 15:44:24.0943 (UTC) FILETIME=[A82A1FF0:01C549AD] 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 - [EMAIL PROTECTED]: 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 [EMAIL PROTECTED] To: mysql@lists.mysql.com CC: mysql@lists.mysql.com 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 - Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY= Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/183030 Delivered-To: mailing list mysql@lists.mysql.com 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: [EMAIL PROTECTED] 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:\Mysqlmysql -u mathias world -X -e desc country ?xml version=1.0? resultset statement=desc country row FieldCode/Field Typechar(3)/Type Null/Null KeyPRI/Key Default/Default Extra/Extra /row row FieldName/Field Typechar(52)/Type Null/Null Key/Key Default/Default Extra/Extra /row row FieldContinent/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:\Mysqlperl describe.pl ?xml version=1.0? DBI driver=DBI:mysql:database=world;host=localhost RESULTSET statement=describe country ROW FieldCode/Field Typechar(3)/Type Null/Null KeyPRI/Key Default/Default Extra/Extra /ROW ROW FieldName/Field Typechar(52
RE: MYSQL to XML
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 [EMAIL PROTECTED]
To: mysql@lists.mysql.com
CC: mysql@lists.mysql.com
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 -
Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: mysql.mysql.com
Precedence: bulk
List-Help: mailto:[EMAIL PROTECTED]
List-Unsubscribe:
mailto:[EMAIL PROTECTED]
List-Post: mailto:mysql@lists.mysql.com
List-Archive: http://lists.mysql.com/mysql/183030
Delivered-To: mailing list mysql@lists.mysql.com
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: [EMAIL PROTECTED]
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:\Mysqlmysql -u mathias world -X -e desc country
?xml version=1.0?
resultset statement=desc country
row
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/row
row
FieldName/Field
Typechar(52)/Type
Null/Null
Key/Key
Default/Default
Extra/Extra
/row
row
FieldContinent/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:\Mysqlperl describe.pl
?xml version=1.0?
DBI driver=DBI:mysql:database=world;host=localhost
RESULTSET statement=describe country
ROW
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/ROW
ROW
FieldName/Field
Typechar(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:\Mysqlperl 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
RE: MYSQL to XML
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 - [EMAIL PROTECTED]:
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 [EMAIL PROTECTED]
To: mysql@lists.mysql.com
CC: mysql@lists.mysql.com
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 -
Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: mysql.mysql.com
Precedence: bulk
List-Help: mailto:[EMAIL PROTECTED]
List-Unsubscribe:
mailto:[EMAIL PROTECTED]
List-Post: mailto:mysql@lists.mysql.com
List-Archive: http://lists.mysql.com/mysql/183030
Delivered-To: mailing list mysql@lists.mysql.com
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: [EMAIL PROTECTED]
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:\Mysqlmysql -u mathias world -X -e desc country
?xml version=1.0?
resultset statement=desc country
row
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/row
row
FieldName/Field
Typechar(52)/Type
Null/Null
Key/Key
Default/Default
Extra/Extra
/row
row
FieldContinent/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:\Mysqlperl describe.pl
?xml version=1.0?
DBI driver=DBI:mysql:database=world;host=localhost
RESULTSET statement=describe country
ROW
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/ROW
ROW
FieldName/Field
Typechar(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:\Mysqlperl desc.pl country
table name
RE: MYSQL to XML
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:\Mysqlmysql -u mathias world -X -e desc country
?xml version=1.0?
resultset statement=desc country
row
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/row
row
FieldName/Field
Typechar(52)/Type
Null/Null
Key/Key
Default/Default
Extra/Extra
/row
row
FieldContinent/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:\Mysqlperl describe.pl
?xml version=1.0?
DBI driver=DBI:mysql:database=world;host=localhost
RESULTSET statement=describe country
ROW
FieldCode/Field
Typechar(3)/Type
Null/Null
KeyPRI/Key
Default/Default
Extra/Extra
/ROW
ROW
FieldName/Field
Typechar(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:\Mysqlperl 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
youcan
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:
Re: mysql and xml
On Tue, Jul 30, 2002 at 10:34:36AM +0200, Inbal Ovadia wrote: Hi All Is MySQL works with XML? Yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 62 days, processed 1,313,326,706 queries (242/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
I know Oracle has been modified to use XML files as the database, making queries directly to a flat file No, thats not how it works. They format the *output* with XML making it easier for an application that can parse XML to import the data. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL and XML?
What are the advantages of flat files? I am having no speed issues at all... in fact Im quite happy to stay with MySQL. But my boss is loaded with ideas you see, so I have to be able to justify why mysql is better than flat files. Thanks for the response, feel free to kick the dead horse one more time :) Bryan -Original Message- From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 21, 2001 4:43 PM To: Bryan Coon; [EMAIL PROTECTED] Subject: Re: MySQL and XML? Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
The "dead horse" I'm referring to is XML, it's come up quite a few times :) The major advantage to flat files is that you don't have the MySQL overhead. However, you have to remember a few things. You will have to implement a key structure similiar to MySQL's to get the performance your getting now. This isn't something that's just quick and easy. The need of a key comes in when you have large text files. Opening and scanning through the entire contents of a 5 meg text file can take some time, you need to have a smaller key file that can let you point to a specific offset in the file using a search criteria so that you can open the file to that point. The development time it would take to implement this is rather large. Another key advantage to flat files is the 100% freedom in how you use them. The drawback to this advantage is that nearly anything you want to do with the flat files has to be 100% developed by the developer. And it's no simple task. Hope some of this information can help you out. I'm sure there are other advantages/disadvantages to using flat files that others will be able to point out. Those are just the two biggies that were on the top of my head. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: "'Eric Fitzgerald'" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 4:48 PM Subject: RE: MySQL and XML? What are the advantages of flat files? I am having no speed issues at all... in fact Im quite happy to stay with MySQL. But my boss is loaded with ideas you see, so I have to be able to justify why mysql is better than flat files. Thanks for the response, feel free to kick the dead horse one more time :) Bryan -Original Message- From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 21, 2001 4:43 PM To: Bryan Coon; [EMAIL PROTECTED] Subject: Re: MySQL and XML? Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
Bryan, You've given no indication of what "this structure" is - the fact that your data is rarely read or updated will have little bearing on whether an RDBMS is suitable for your needs. It would be a very rare case, however, where a simple flat text file gave you quicker access to a search of a set of data than would a well-indexed (or even non-indexed!) database table - *especially* a text file which needs to be expensively parsed before any searching can be effective (ie an XML file). Shall I have another rant about this year's buzzwords ? Don't assume that a word you hear being thrown around will be the solution to every single one of your IT problems. XML really is most useful where two parties need a common format for the exchange of data - there are very few situations where general storage of data in an XML format would be advantageous over other 'traditional' methods. regards, P On Wed, 21 Mar 2001, Bryan Coon wrote: Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
MySQL has no support for XML and I hope it doesn't, ever. There was a big discussion about this last month. You can read up about it in the archives found at the mysql site. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 6:23 Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
You need to read the mailing list archives, This has been discussed to death. Please try to follow the directions outlined when posting. See the 'http://lists.mysql.com/ (the list archive)' ? Bryan Coon wrote: Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and XML?
I'm sure he means for you to go back to the archives and beat the horse on your own. flat files are only useful for 1-on-1 relationships. A simple example. You have a student record and each student takes several subjects in which you keep results and other info for. You can do a flat file for it, but geez, it's very inefficient, wastes a lot of space and prone to errors. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: "'Eric Fitzgerald'" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 8:48 Subject: RE: MySQL and XML? What are the advantages of flat files? I am having no speed issues at all... in fact Im quite happy to stay with MySQL. But my boss is loaded with ideas you see, so I have to be able to justify why mysql is better than flat files. Thanks for the response, feel free to kick the dead horse one more time :) Bryan -Original Message- From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 21, 2001 4:43 PM To: Bryan Coon; [EMAIL PROTECTED] Subject: Re: MySQL and XML? Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
