Entry
Database: SQL: Generate: Table:Key:Foreign:Create: How generate table with foreign key using XML: 2?
Mar 20th, 2005 09:47
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: 2?
---
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) -->
<!-- -->
<TABLES>
<!-- -->
<TABLE>
<!-- -->
<TABLENAME>
Test2
</TABLENAME>
<!-- -->
<COLUMNPRIMARYKEY>
<!-- -->
<COLUMNPRIMARYKEYNAME>
columnTestPrimaryKey2I
</COLUMNPRIMARYKEYNAME>
<!-- -->
<COLUMNPRIMARYKEYTYPE>
INT AUTO_INCREMENT NOT NULL PRIMARY KEY
</COLUMNPRIMARYKEYTYPE>
<!-- -->
</COLUMNPRIMARYKEY>
<!-- -->
<COLUMN>
<!-- -->
<COLUMNNAME>
columnTest2S
</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>
<!-- -->
<TABLE>
<!-- -->
<TABLENAME>
Test3
</TABLENAME>
<!-- -->
<COLUMNPRIMARYKEY>
<!-- -->
<COLUMNPRIMARYKEYNAME>
columnTestPrimaryKey3I
</COLUMNPRIMARYKEYNAME>
<!-- -->
<COLUMNPRIMARYKEYTYPE>
INT AUTO_INCREMENT NOT NULL PRIMARY KEY
</COLUMNPRIMARYKEYTYPE>
<!-- -->
</COLUMNPRIMARYKEY>
<!-- -->
<COLUMN>
<!-- -->
<COLUMNNAME>
columnTest3S
</COLUMNNAME>
<!-- -->
<COLUMNTYPE>
VARCHAR( 50 )
</COLUMNTYPE>
<!-- -->
</COLUMN>
<!-- -->
<COLUMNFOREIGNKEY>
<!-- -->
<COLUMNFOREIGNKEYNAME>
columnTestForeignKeyI31
</COLUMNFOREIGNKEYNAME>
<!-- -->
<COLUMNFOREIGNKEYREFERENCETABLE>
tableTest1S
</COLUMNFOREIGNKEYREFERENCETABLE>
<!-- -->
<COLUMNFOREIGNKEYREFERENCECOLUMN>
columnTestPrimaryKey1
</COLUMNFOREIGNKEYREFERENCECOLUMN>
<!-- -->
</COLUMNFOREIGNKEY>
<!-- -->
<COLUMNINDEX>
<!-- -->
<COLUMNINDEXNAME>
columnTestIndex
</COLUMNINDEXNAME>
<!-- -->
<COLUMNINDEXREFERENCECOLUMN>
columnTestForeignKeyI31
</COLUMNINDEXREFERENCECOLUMN>
<!-- -->
</COLUMNINDEX>
<!-- -->
</TABLE>
<!-- -->
</TABLES>
<!-- -->
--- cut here: end ----------------------------------------------------
2. -Create a DTD file describing the structure
--- cut here: begin --------------------------------------------------
<!-- -->
<!ELEMENT TABLES (TABLE+)>
<!-- -->
<!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 COLUMNTYPE (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNPRIMARYKEYNAME (#PCDATA)>
<!-- -->
<!ELEMENT COLUMNPRIMARYKEYTYPE (#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="TABLES">
<HTML>
<HEAD>
<TITLE>
My table
</TITLE>
</HEAD>
<BODY>
<H3> My table </H3>
<BR/>
<BR/>
<HR/>
<xsl:apply-templates/>
<BR/>
<BR/>
<HR/>
</BODY>
</HTML>
</xsl:template>
<!-- -->
<xsl:template match="TABLE">
<BR/>
<xsl:apply-templates/>
<BR/>
)
<BR/>
TYPE=INNODB
<BR/>
;
</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="COLUMNFOREIGNKEYNAME"/>
INT,
<BR/>
FOREIGN KEY (
<xsl:value-of select="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="COLUMNINDEXREFERENCECOLUMN"/>
)
</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 --------------------------------------------------
--------------------------------------------------------------------
CREATE TABLE
Test2
(
columnTestPrimaryKey2I INT AUTO_INCREMENT NOT NULL PRIMARY KEY
columnTest2S VARCHAR( 50 ),
columnTestForeignKeyI21 INT,
FOREIGN KEY ( columnTestForeignKeyI21 ) REFERENCES tableTest1S (
columnTestPrimaryKeyI1 ),
INDEX columnTestIndex ( columnTestForeignKeyI21 )
)
TYPE=INNODB
;
CREATE TABLE
Test3
(
columnTestPrimaryKey3I INT AUTO_INCREMENT NOT NULL PRIMARY KEY
columnTest3S VARCHAR( 50 ),
columnTestForeignKeyI31 INT,
FOREIGN KEY ( columnTestForeignKeyI31 ) REFERENCES tableTest1S (
columnTestPrimaryKey1 ),
INDEX columnTestIndex ( columnTestForeignKeyI31 )
)
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
----------------------------------------------------------------------
