XMLDB editing

Creation of XMLDB database definition file

The XMLDB database definition is stored in the file db/install.xml. You can use a built-in XMLDB editor to create or edit any install.xml files. If you're using MySQL as your default database schemes. XMLDB editor's powerful tools allowing retrofit MySQL table to XMLDB format. However, to design of the complex structure of tables by built-in editor are requires a significant amount of time. At the same time, built-in editor generates the correct contents of the install.xml file. If you create the database schema by hand, it is very difficult to trace the relationships between the tables, fields, keys, and indexes, which are set by PREVIOUS and NEXT attributes. So we would like to have a tool that make the creation of database schema easier.

Easy correction of the XMLDB file structure

During the development of the database scheme is very convenient to write XML structure in the following form:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="correct-xmldb-format.xslt"?>
<XMLDB PATH="mod/module-name/db" VERSION="2013013100"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd">
  <TABLES>
    <TABLE NAME="category">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" />
        <FIELD NAME="parent_id" TYPE="int" />
        <FIELD NAME="name" TYPE="char" />
        <FIELD NAME="description" TYPE="text" NOTNULL="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" FIELDS="id" />
        <KEY NAME="parent_name" FIELDS="parent_id,name" />
        <KEY NAME="parent" TYPE="foreign" FIELDS="parent_id" />
      </KEYS>
    </TABLE>
    <TABLE NAME="product">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" />
        <FIELD NAME="category_id" TYPE="int" />
        <FIELD NAME="name" TYPE="char" />
        <FIELD NAME="date" TYPE="int" DEFAULT="0" COMMENT="delivery time" />
        <FIELD NAME="description" TYPE="text" NOTNULL="false" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" FIELDS="id" />
        <KEY NAME="name" FIELDS="name" />
        <KEY NAME="category" FIELDS="category_id" REFTABLE="category" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="date" FIELDS="date" />
      </INDEXES>
    </TABLE>
  </TABLES>
</XMLDB>

In the markup is presented above the PREVIOUS and NEXT attributes missing. Also most of the other attributes used only when it necessary. By default, the key field in the table has name 'id', so there is no need to install attribute SEQUENCE="true". And if key is named 'primary' or key attribute 'FIELDS' is set 'id', key has type of primary key. And in most cases don't want to set a foreign key field REFFIELDS, as during design process name of the parent table key fields may change.

On completion design, using for the database structure shown in the page XSLT code can obtain the following output code:

<?xml version="1.0" encoding="utf-8"?>
<XMLDB 
   PATH="mod/module-name/db" VERSION="2013013100"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"
   COMMENT="XMLDB file for Moodle  mod/module-name"
>
  <TABLES>
    <TABLE NAME="category" NEXT="product" COMMENT="XMLDB Table 'category'">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" SEQUENCE="true" NOTNULL="true" NEXT="parent_id" COMMENT="XMLDB field 'id'" />
        <FIELD NAME="parent_id" TYPE="int" LENGTH="10" SEQUENCE="false" NOTNULL="true" PREVIOUS="id" NEXT="name" COMMENT="XMLDB field 'parent_id'" />
        <FIELD NAME="name" TYPE="char" LENGTH="255" SEQUENCE="false" NOTNULL="true" PREVIOUS="parent_id" NEXT="description" COMMENT="XMLDB field 'name'" />
        <FIELD NAME="description" TYPE="text" LENGTH="medium" SEQUENCE="false" NOTNULL="false" PREVIOUS="name" COMMENT="XMLDB field 'description'" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" NEXT="parent_name" COMMENT="XMLDB primary key 'primary'" />
        <KEY NAME="parent_name" TYPE="unique" FIELDS="parent_id,name" PREVIOUS="primary" NEXT="parent" COMMENT="XMLDB unique key 'parent_name'" />
        <KEY NAME="parent" TYPE="foreign" FIELDS="parent_id" REFTABLE="category" REFFIELDS="id" PREVIOUS="parent_name" COMMENT="XMLDB foreign key 'parent'" />
      </KEYS>
    </TABLE>
    <TABLE NAME="product" PREVIOUS="category" COMMENT="XMLDB Table 'product'">
      <FIELDS>
        <FIELD NAME="id" TYPE="int" LENGTH="10" SEQUENCE="true" NOTNULL="true" NEXT="category_id" COMMENT="XMLDB field 'id'" />
        <FIELD NAME="category_id" TYPE="int" LENGTH="10" SEQUENCE="false" NOTNULL="true" PREVIOUS="id" NEXT="name" COMMENT="XMLDB field 'category_id'" />
        <FIELD NAME="name" TYPE="char" LENGTH="255" SEQUENCE="false" NOTNULL="true" PREVIOUS="category_id" NEXT="date" COMMENT="XMLDB field 'name'" />
        <FIELD NAME="date" TYPE="int" LENGTH="10" SEQUENCE="false" NOTNULL="true" PREVIOUS="name" NEXT="description" COMMENT="delivery time" />
        <FIELD NAME="description" TYPE="text" LENGTH="medium" SEQUENCE="false" NOTNULL="false" PREVIOUS="date" COMMENT="XMLDB field 'description'" />
      </FIELDS>
      <KEYS>
        <KEY NAME="primary" TYPE="primary" FIELDS="id" NEXT="name" COMMENT="XMLDB primary key 'primary'" />
        <KEY NAME="name" TYPE="unique" FIELDS="name" PREVIOUS="primary" NEXT="category" COMMENT="XMLDB unique key 'name'" />
        <KEY NAME="category" TYPE="foreign" FIELDS="category_id" REFTABLE="category" REFFIELDS="id" PREVIOUS="name" COMMENT="XMLDB foreign key 'category'" />
      </KEYS>
      <INDEXES>
        <INDEX NAME="date" FIELDS="date" UNIQUE="false" COMMENT="XMLDB index 'date'" />
      </INDEXES>
    </TABLE>
  </TABLES>
</XMLDB>

Code of 'correct-xmldb-format.xslt' file

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>

  <!-- Copyright (C) Pavel Evgenjevich Timoshenko, 2013.
       The code can be used by "as-is" principle and 
       it is distributed by GPL v3 license (http://www.gnu.org/licenses/gpl.html). -->

  <xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'" />
  <xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'" />
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="//XMLDB">
    <XMLDB xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd">
      <xsl:attribute name="PATH">
        <xsl:choose>
          <xsl:when test="@PATH">
            <xsl:value-of select="@PATH"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:attribute name="VERSION">
        <xsl:choose>
          <xsl:when test="@VERSION">
            <xsl:value-of select="@VERSION"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:attribute name="COMMENT">
        <xsl:choose>
          <xsl:when test="@COMMENT">
            <xsl:value-of select="@COMMENT"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>XMLDB file for Moodle </xsl:text>
            <xsl:text> </xsl:text>
            <xsl:choose>
              <xsl:when test="substring(@PATH, string-length(@PATH)-string-length('/db')+1)='/db'">
                <xsl:value-of select="substring(@PATH, 1, string-length(@PATH)-string-length('/db'))" />
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="@PATH" />
              </xsl:otherwise>
            </xsl:choose>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:apply-templates />
    </XMLDB>
  </xsl:template>

  <xsl:template match="//XMLDB/TABLES">
    <TABLES>
      <xsl:apply-templates />
    </TABLES>
  </xsl:template>

  <xsl:template match="//XMLDB/TABLES/TABLE">
    <TABLE>
      <xsl:attribute name="NAME">
        <xsl:choose>
          <xsl:when test="@NAME">
            <xsl:value-of select="@NAME"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:if test="preceding-sibling::TABLE[1]/@NAME">
        <xsl:attribute name="PREVIOUS">
          <xsl:value-of select="preceding-sibling::TABLE[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="following-sibling::TABLE[1]/@NAME">
        <xsl:attribute name="NEXT">
          <xsl:value-of select="following-sibling::TABLE[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:attribute name="COMMENT">
        <xsl:choose>
          <xsl:when test="@COMMENT">
            <xsl:value-of select="@COMMENT"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>XMLDB Table '</xsl:text>
            <xsl:value-of select="@NAME"/>
            <xsl:text>'</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:apply-templates select="./FIELDS"/>
      <xsl:apply-templates select="./KEYS"/>
      <xsl:apply-templates select="./INDEXES"/>
    </TABLE>
  </xsl:template>

  <xsl:template match="//XMLDB/TABLES/TABLE/FIELDS">
    <FIELDS>
      <xsl:apply-templates/>
    </FIELDS>
  </xsl:template>
  <xsl:template match="//XMLDB/TABLES/TABLE/FIELDS/FIELD">
    <FIELD>
      <xsl:attribute name="NAME">
        <xsl:choose>
          <xsl:when test="@NAME">
            <xsl:value-of select="@NAME"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:attribute name="TYPE">
        <xsl:choose>
          <xsl:when test="@TYPE">
            <xsl:value-of select="@TYPE"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:choose>
        <xsl:when test="@LENGTH">
          <xsl:attribute name="LENGTH">
            <xsl:value-of select="@LENGTH"/>
          </xsl:attribute>
        </xsl:when>
        <xsl:when test="@TYPE='int'">
          <xsl:attribute name="LENGTH">
            <xsl:text>10</xsl:text>
          </xsl:attribute>
        </xsl:when>
        <xsl:when test="@TYPE='char'">
          <xsl:attribute name="LENGTH">
            <xsl:text>255</xsl:text>
          </xsl:attribute>
        </xsl:when>
        <xsl:when test="@TYPE='text'">
          <xsl:attribute name="LENGTH">
            <xsl:text>medium</xsl:text>
          </xsl:attribute>
        </xsl:when>
      </xsl:choose>
      <xsl:attribute name="SEQUENCE">
        <xsl:choose>
          <xsl:when test="@SEQUENCE">
            <xsl:value-of select="@SEQUENCE"/>
          </xsl:when>
          <xsl:when test="not(@SEQUENCE) and (@TYPE='int') and (translate(@NAME, $smallcase, $uppercase)='ID')">
            <xsl:text>true</xsl:text>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>false</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:attribute name="NOTNULL">
        <xsl:choose>
          <xsl:when test="@NOTNULL">
            <xsl:value-of select="@NOTNULL"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>true</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:if test="preceding-sibling::FIELD[1]/@NAME">
        <xsl:attribute name="PREVIOUS">
          <xsl:value-of select="preceding-sibling::FIELD[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="following-sibling::FIELD[1]/@NAME">
        <xsl:attribute name="NEXT">
          <xsl:value-of select="following-sibling::FIELD[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:attribute name="COMMENT">
        <xsl:choose>
          <xsl:when test="@COMMENT">
            <xsl:value-of select="@COMMENT"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>XMLDB field '</xsl:text>
            <xsl:value-of select="@NAME"/>
            <xsl:text>'</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
    </FIELD>
  </xsl:template>
  <xsl:template match="//XMLDB/TABLES/TABLE/KEYS">
    <KEYS>
      <xsl:apply-templates/>
    </KEYS>
  </xsl:template>
  <xsl:template match="//XMLDB/TABLES/TABLE/KEYS/KEY">
    <KEY>
      <xsl:attribute name="NAME">
        <xsl:choose>
          <xsl:when test="@NAME">
            <xsl:value-of select="@NAME"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:variable name="keyType">
        <xsl:choose>
          <xsl:when test="@TYPE">
            <xsl:value-of select="@TYPE"/>
          </xsl:when>
          <xsl:when test="@REFTABLE or @REFFIELDS">
            <xsl:text>foreign</xsl:text>
          </xsl:when>
          <xsl:when test="(translate(@FIELDS, $smallcase, $uppercase)='ID') or (translate(@NAME, $smallcase, $uppercase)='PRIMARY')">
            <xsl:text>primary</xsl:text>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>unique</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:attribute name="TYPE" >
        <xsl:value-of select="$keyType" />
      </xsl:attribute>
      <xsl:attribute name="FIELDS">
        <xsl:choose>
          <xsl:when test="@FIELDS">
            <xsl:value-of select="@FIELDS"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:if test="($keyType = 'foreign') or ($keyType = 'foreign-unique')">
        <xsl:variable name="RefTable">
          <xsl:choose>
            <xsl:when test="@REFTABLE">
              <xsl:value-of select="@REFTABLE"/>
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="parent::node()/parent::node()/@NAME"/>
            </xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:attribute name="REFTABLE">
          <xsl:value-of select="$RefTable"/>
        </xsl:attribute>
        <xsl:attribute name="REFFIELDS">
          <xsl:choose>
            <xsl:when test="@REFFIELDS">
              <xsl:value-of select="@REFFIELDS"/>
            </xsl:when>
            <xsl:otherwise>
              <xsl:variable name="RefTableNode" select="parent::node()/parent::node()/parent::node()/TABLE[@NAME=$RefTable]" />
              <xsl:variable name="RefFieldsTypePrimary" select="$RefTableNode/KEYS/KEY[@TYPE='primary']" />
              <xsl:variable name="RefFieldsNamePrimary" select="$RefTableNode/KEYS/KEY[translate(@NAME, $smallcase, $uppercase)='PRIMARY']" />
              <xsl:variable name="RefFieldsFieldID" select="$RefTableNode/KEYS/KEY[translate(@FIELDS, $smallcase, $uppercase)='ID']" />
              <xsl:variable name="RefFields" select="$RefFieldsTypePrimary | $RefFieldsNamePrimary[not($RefFieldsTypePrimary)] | $RefFieldsFieldID[not($RefFieldsNamePrimary)]" />
              <xsl:if test="not($RefFields)">
                <xsl:message terminate="yes" />
              </xsl:if>
              <xsl:for-each select="$RefFields">
                <xsl:if test="position()!=1">,</xsl:if>
                <xsl:value-of select="./@FIELDS" />
              </xsl:for-each>
            </xsl:otherwise>
          </xsl:choose>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="preceding-sibling::KEY[1]/@NAME">
        <xsl:attribute name="PREVIOUS">
          <xsl:value-of select="preceding-sibling::KEY[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="following-sibling::KEY[1]/@NAME">
        <xsl:attribute name="NEXT">
          <xsl:value-of select="following-sibling::KEY[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:attribute name="COMMENT">
        <xsl:choose>
          <xsl:when test="@COMMENT">
            <xsl:value-of select="@COMMENT"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>XMLDB</xsl:text>
            <xsl:text xml:space="preserve"> </xsl:text>
            <xsl:value-of select="$keyType"/>
            <xsl:text xml:space="preserve"> </xsl:text>
            <xsl:text>key '</xsl:text>
            <xsl:value-of select="@NAME"/>
            <xsl:text>'</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
    </KEY>
  </xsl:template>
  <xsl:template match="//XMLDB/TABLES/TABLE/INDEXES">
    <INDEXES>
      <xsl:apply-templates/>
    </INDEXES>
  </xsl:template>
  <xsl:template match="//XMLDB/TABLES/TABLE/INDEXES/INDEX">
    <INDEX>
      <xsl:attribute name="NAME">
        <xsl:choose>
          <xsl:when test="@NAME">
            <xsl:value-of select="@NAME"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:attribute name="FIELDS">
        <xsl:choose>
          <xsl:when test="@FIELDS">
            <xsl:value-of select="@FIELDS"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:message terminate="yes" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
      <xsl:variable name="uniqueIndex">
        <xsl:choose>
          <xsl:when test="@UNIQUE">
            <xsl:value-of select="@UNIQUE"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>false</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:attribute name="UNIQUE">
        <xsl:value-of select="$uniqueIndex"/>
      </xsl:attribute>
      <xsl:if test="preceding-sibling::INDEX[1]/@NAME">
        <xsl:attribute name="PREVIOUS">
          <xsl:value-of select="preceding-sibling::INDEX[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="following-sibling::INDEX[1]/@NAME">
        <xsl:attribute name="NEXT">
          <xsl:value-of select="following-sibling::INDEX[1]/@NAME"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:attribute name="COMMENT">
        <xsl:choose>
          <xsl:when test="@COMMENT">
            <xsl:value-of select="@COMMENT"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>XMLDB</xsl:text>
            <xsl:if test="$uniqueIndex = 'true'">
              <xsl:text xml:space="preserve"> </xsl:text>
              <xsl:text>unique</xsl:text>
            </xsl:if>
            <xsl:text xml:space="preserve"> </xsl:text>
            <xsl:text>index '</xsl:text>
            <xsl:value-of select="@NAME"/>
            <xsl:text>'</xsl:text>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:attribute>
    </INDEX>
  </xsl:template>
</xsl:stylesheet>

See also

• XMLDB editor
• XMLDB introduction
• XMLDB defining an XML structure
• XMLDB column types