Entry
Database: SQL: Generate: Table:Key:Foreign:Create: How generate table with foreign key using XML: 1?
Mar 20th, 2005 08:28
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 19 March 2005 - 11:17 pm ----------------------
Database: SQL: Generate: Table:Key:Foreign:Create: How generate table
with foreign key using XML: 1?
---
Steps: Overview:
1. -Create an XML file with some data
--- cut here: begin --------------------------------------------------
<!-- -->
<?xml version="1.0" encoding="UTF-8"?>
<!-- -->
<!DOCTYPE MYBOOKS SYSTEM "test.dtd">
<!-- -->
<?xml-stylesheet type="text/xsl" href="test.xsl"?>
<!-- -->
<!-- library: (filenamemacro=test.xml) -->
<!-- -->
<TABLE>
<!-- -->
<TABLENAME>
tableTest2S
</TABLENAME>
<!-- -->
<COLUMNPRIMARYKEY>
<!-- -->
<COLUMNPRIMARYKEYNAME>
columnTestPrimaryKeyI
</COLUMNPRIMARYKEYNAME>
<!-- -->
<COLUMNPRIMARYKEYTYPE>
INT AUTO_INCREMENT NOT NULL PRIMARY KEY
</COLUMNPRIMARYKEYTYPE>
<!-- -->
</COLUMNPRIMARYKEY>
<!-- -->
<COLUMN>
<!-- -->
<COLUMNNAME>
columnTestS
</COLUMNNAME>
<!-- -->
<COLUMNTYPE>
VARCHAR( 50 )
</COLUMNTYPE>
<!-- -->
</COLUMN>
<!-- -->
<COLUMNFOREIGNKEY>
<!-- -->
<COLUMNFOREIGNKEYNAME>
columnTestForeignKeyI21
</COLUMNFOREIGNKEYNAME>
<!-- -->
<COLUMNFOREIGNKEYREFERENCETABLE>
tableTest1S
</COLUMNFOREIGNKEYREFERENCETABLE>
<!-- -->
<COLUMNFOREIGNKEYREFERENCECOLUMN>
columnTestPrimaryKeyI1
</COLUMNFOREIGNKEYREFERENCECOLUMN>
<!-- -->
</COLUMNFOREIGNKEY>
<!-- -->
<COLUMNINDEX>
<!-- -->
<COLUMNINDEXNAME>
columnTestIndex
</COLUMNINDEXNAME>
<!-- -->
<COLUMNINDEXREFERENCECOLUMN>
columnTestForeignKeyI21
</COLUMNINDEXREFERENCECOLUMN>
<!-- -->
</COLUMNINDEX>
<!-- -->
</TABLE>
<!-- -->
--- cut here: end ----------------------------------------------------
2. -Create a DTD file describing the structure
--- cut here: begin --------------------------------------------------
<!-- -->
<!ELEMENT TABLE (TABLENAME, COLUMNPRIMARYKEY, COLUMN+,
COLUMNFOREIGNKEY*, COLUMNINDEX*)>
<!-- -->
<!ELEMENT TABLENAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNPRIMARYKEY (COLUMNPRIMARYKEYNAME,
COLUMNPRIMARYKEYTYPE+)>
<!-- -->
<!ELEMENT COLUMN (COLUMNNAME, COLUMNTYPE+)>
<!-- -->
<!ELEMENT COLUMNINDEX (COLUMNINDEXNAME, COLUMNINDEXREFERENCECOLUMN)>
<!-- -->
<!ELEMENT COLUMNFOREIGNKEY (COLUMNFOREIGNKEYNAME,
COLUMNFOREIGNKEYREFERENCETABLE, COLUMNFOREIGNKEYREFERENCECOLUMN)>
<!-- -->
<!ELEMENT COLUMNNAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMTYPE (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNPRIMARYKEYNAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMPRIMARYKEYTYPE (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNINDEXNAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNINDEXREFERENCECOLUMN (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNFOREIGNKEYNAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNFOREIGNKEYREFERENCETABLE (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNFOREIGNKEYREFERENCECOLUMN (#PCDATA)>
<!-- -->
--- cut here: end ----------------------------------------------------
3. -Create an XSL file describing how it should look like
--- cut here: begin --------------------------------------------------
<!-- -->
<?xml version="1.0"?>
<!-- -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<!-- -->
<xsl:template match="/">
<xsl:apply-templates/>
</xsl:template>
<!-- -->
<xsl:template match="TABLE">
<HTML>
<HEAD>
<TITLE>
My table
</TITLE>
</HEAD>
<BODY>
<H3> My table </H3>
<BR/>
<BR/>
<HR/>
<xsl:apply-templates/>
<BR/>
)
<BR/>
TYPE=INNODB
<BR/>
;
<BR/>
<BR/>
<HR/>
</BODY>
</HTML>
</xsl:template>
<!-- -->
<xsl:template match="TABLENAME">
<FONT SIZE="3" COLOR="blue">
<BR/> CREATE TABLE
<BR/>
<xsl:value-of select="."/>
<xsl:apply-templates/>
</FONT>
<BR/>
(
</xsl:template>
<!-- -->
<xsl:template match="COLUMNPRIMARYKEY">
<BR/>
<xsl:value-of select="."/>
,
</xsl:template>
<!-- -->
<xsl:template match="COLUMNPRIMARYKEYNAME">
<BR/>
<xsl:value-of select="."/>
</xsl:template>
<!-- -->
<xsl:template match="COLUMNPRIMARYKEYTYPE">
<BR/>
<xsl:value-of select="."/>
</xsl:template>
<!-- -->
<xsl:template match="COLUMN">
<BR/>
<xsl:value-of select="."/>
,
</xsl:template>
<!-- -->
<xsl:template match="COLUMNNAME">
<BR/>
<xsl:value-of select="."/>
</xsl:template>
<!-- -->
<xsl:template match="COLUMNTYPE">
<BR/>
<xsl:value-of select="."/>
</xsl:template>
<!-- -->
<xsl:template match="COLUMNFOREIGNKEY">
<BR/>
<xsl:value-of
select="/TABLE/COLUMNFOREIGNKEY/COLUMNFOREIGNKEYNAME"/>
INT
,
<BR/>
FOREIGN KEY (
<xsl:value-of
select="/TABLE/COLUMNFOREIGNKEY/COLUMNFOREIGNKEYNAME"/>
) REFERENCES
<xsl:value-of select="COLUMNFOREIGNKEYREFERENCETABLE"/>
(
<xsl:value-of select="COLUMNFOREIGNKEYREFERENCECOLUMN"/>
)
,
</xsl:template>
<!-- -->
<xsl:template match="COLUMNINDEX">
<BR/>
INDEX
<xsl:value-of select="COLUMNINDEXNAME"/>
(
<xsl:value-of
select="/TABLE/COLUMNFOREIGNKEY/COLUMNFOREIGNKEYNAME"/>
)
</xsl:template>
<!-- -->
</xsl:stylesheet>
<!-- -->
--- cut here: end ----------------------------------------------------
4. -Save this XML, DTD and XSL file in the same directory
5. -If you then load the XML file in your browser,
you should see an output similar to the following:
--- cut here: begin --------------------------------------------------
My table
--------------------------------------------------------------------
CREATE TABLE
tableTest2S
(
columnTestPrimaryKeyI INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
columnTestS VARCHAR( 50 ) ,
columnTestForeignKeyI21 INT ,
FOREIGN KEY ( columnTestForeignKeyI21 ) REFERENCES tableTest1S (
columnTestPrimaryKeyI1 ) ,
INDEX columnTestIndex ( columnTestForeignKeyI21 )
)
TYPE=INNODB
;
--------------------------------------------------------------------
--- cut here: end ----------------------------------------------------
---
---
This is a script where you can test this generated SQL source with:
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
databaseMyTest;
-----------------------------------------------
USE
databaseMyTest;
-----------------------------------------------
DROP TABLE
tableTest2S;
-----------------------------------------------
DROP TABLE
tableTest1S;
-----------------------------------------------
CREATE TABLE
tableTest1S
(
columnTestPrimaryKeyI1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
columnTestS VARCHAR( 50 )
)
TYPE=INNODB
;
-----------------------------------------------
CREATE TABLE
tableTest2S
(
columnTestPrimaryKeyI2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
columnTestS VARCHAR( 50 ) ,
columnTestForeignKeyI21 INT,
INDEX columnTestIndex ( columnTestForeignKeyI21 ),
FOREIGN KEY ( columnTestForeignKeyI21 ) REFERENCES tableTest1S (
columnTestPrimaryKeyI1 )
)
TYPE=INNODB
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
---
---
Internet: see also:
---
Database: SQL: Generate: Structure: Link: Can you give overview of
links about generating SQL? [XML]
http://www.faqts.com/knowledge_base/view.phtml/aid/34527/fid/54
----------------------------------------------------------------------
