File: mysqlIn.xsl

package info (click to toggle)
anymeal 0.30-7
links: PTS
area: main
in suites: lenny
size: 5,656 kB
ctags: 1,292
sloc: sh: 12,281; cpp: 11,600; perl: 2,730; lex: 1,098; makefile: 117
file content (601 lines) | stat: -rw-r--r-- 28,710 bytes
parent folder | download | duplicates (2)
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

  <xsl:output method="text" encoding="ISO-8859-1"/>

  <!-- ================ Primary templates ================================ -->

  <!-- Initialise database tables. -->
  <xsl:template match="create">
    <xsl:text>CREATE TABLE COOKBOOK(VERSION INT UNSIGNED NOT NULL,SUBVERSION INT UNSIGNED NOT NULL);&#xA;</xsl:text>

    <!-- Always update the version number before
         changing the database-structure!!!
         Also update the version-macro in file 'configure.in'. -->
    <xsl:text>INSERT INTO COOKBOOK VALUES(0,21);&#xA;</xsl:text>

    <xsl:text>CREATE TABLE EDIBLE(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,NAME VARCHAR(100) UNIQUE NOT NULL,PRIMARY KEY (ID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE CATEGORY(RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,ID INT UNSIGNED NOT NULL,CATEGORYID INT UNSIGNED NOT NULL REFERENCES CATEGORIES,KEY(RECIPEID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE CATEGORIES(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,NAME VARCHAR(100) UNIQUE NOT NULL,PRIMARY KEY(ID),KEY(NAME));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE RECIPE(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,TITLE VARCHAR(100) NOT NULL,AMOUNT INT UNSIGNED NOT NULL,UNIT VARCHAR(10) BINARY,LANGUAGE CHAR(2) NOT NULL,PRIMARY KEY(ID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE INSTRUCTIONS(ID INT UNSIGNED NOT NULL,RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,TITLE VARCHAR(100),INSTRUCTIONS TEXT NOT NULL,PRIMARY KEY(RECIPEID,ID),KEY(RECIPEID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE SECTION(ID INT UNSIGNED NOT NULL,RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,TITLE VARCHAR(100),PRIMARY KEY(RECIPEID,ID),KEY(RECIPEID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE INGREDIENT(RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,SECTIONID INT UNSIGNED NOT NULL,ID INT UNSIGNED NOT NULL,EDIBLEID INT UNSIGNED NOT NULL REFERENCES EDIBLE,AMOUNTDOUBLE FLOAT,AMOUNTNOMINATOR INT UNSIGNED,AMOUNTDENOMINATOR INT UNSIGNED,UNIT VARCHAR(20) BINARY,PREP VARCHAR(100) NOT NULL,KEY(RECIPEID,SECTIONID,EDIBLEID));&#xA;</xsl:text>
    <xsl:text>CREATE TABLE SELECTION(RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,USER VARCHAR(16) NOT NULL,PRIMARY KEY(USER,RECIPEID),KEY(RECIPEID));&#xA;</xsl:text>
  </xsl:template>
  
  <!-- Destruct database tables. -->
  <xsl:template match="destruct">
    <xsl:text>DROP TABLE IF EXISTS COOKBOOK;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS EDIBLE;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS CATEGORY;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS CATEGORIES;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS RECIPE;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS INSTRUCTIONS;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS SECTION;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS INGREDIENT;&#xA;</xsl:text>
    <xsl:text>DROP TABLE IF EXISTS SELECTION;&#xA;</xsl:text>
  </xsl:template>

  <!-- Query version-number. -->
  <xsl:template match="version">
    <xsl:text># OPEN version;&#xA;</xsl:text>
    <xsl:text>SELECT (VERSION*1000+SUBVERSION) FROM COOKBOOK;&#xA;</xsl:text>
    <xsl:text># CLOSE version;&#xA;</xsl:text>
  </xsl:template>

  <!-- Update database version. -->
  <xsl:template match="update">
    <xsl:if test="version &gt;= 13">
      <xsl:if test="version &lt;= 13">
        <xsl:text>CREATE TABLE SELECTION(RECIPEID INT UNSIGNED NOT NULL REFERENCES RECIPE,USER VARCHAR(16) NOT NULL,PRIMARY KEY(USER,RECIPEID),KEY(RECIPEID));&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=14;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 14">
        <!-- No values are specified (all IDs will be zero?). -->
        <xsl:text>ALTER TABLE CATEGORY ADD ID INT UNSIGNED NOT NULL AFTER RECIPEID;&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=15;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 15">
        <xsl:text>ALTER TABLE INGREDIENT ADD PREP VARCHAR(100) NOT NULL AFTER UNIT;&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=16;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 16">
        <xsl:text>CREATE TABLE CATEGORIES(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,NAME VARCHAR(100) UNIQUE NOT NULL,PRIMARY KEY(ID),KEY(NAME));&#xA;</xsl:text>
        <xsl:text>INSERT INTO CATEGORIES SELECT NULL,C.NAME FROM CATEGORY C GROUP BY C.NAME;&#xA;</xsl:text>
        <xsl:text>ALTER TABLE CATEGORY ADD CATEGORYID INT UNSIGNED NOT NULL REFERENCES CATEGORIES AFTER NAME;&#xA;</xsl:text>
        <xsl:text>UPDATE CATEGORY,CATEGORIES SET CATEGORY.CATEGORYID=CATEGORIES.ID WHERE CATEGORIES.NAME=CATEGORY.NAME;&#xA;</xsl:text>
        <xsl:text>ALTER TABLE CATEGORY DROP COLUMN NAME;&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=17;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 17">
        <xsl:text>ALTER TABLE RECIPE ADD LANGUAGE CHAR(2) NOT NULL AFTER UNIT;&#xA;</xsl:text>
        <xsl:text>UPDATE RECIPE SET LANGUAGE='en';&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=18;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 18">
        <xsl:text>ALTER TABLE INGREDIENT ADD ID INT UNSIGNED NOT NULL AFTER SECTIONID;&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=19;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 19">
        <!-- Resolve unit names for new database version. Unfortunately english only,
             because I don't want to clutter this script with different languages. -->
        <xsl:text>ALTER TABLE INGREDIENT CHANGE UNIT UNIT VARCHAR(20);&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='per serving' WHERE UNIT='x';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='small' WHERE UNIT='sm';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='medium' WHERE UNIT='md';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='large' WHERE UNIT='lg';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='can' WHERE UNIT='cn';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='package' WHERE UNIT='pk';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='pinch' WHERE UNIT='pn';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='drop' WHERE UNIT='dr';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='dash' WHERE UNIT='ds';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='carton' WHERE UNIT='ct';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='bunch' WHERE UNIT='bn';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='slice' WHERE UNIT='sl';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='each' WHERE UNIT='ea';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='teaspoon' WHERE UNIT='ts';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='tablespoon' WHERE UNIT='tb';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='tablespoon' WHERE UNIT='T';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='fluid ounce' WHERE UNIT='fl';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='cup' WHERE UNIT='c';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='pint' WHERE UNIT='pt';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='quart' WHERE UNIT='qt';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='gallon' WHERE UNIT='ga';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='ounce' WHERE UNIT='oz';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='pound' WHERE UNIT='lb';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='milliliter' WHERE UNIT='ml';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='cubic cm' WHERE UNIT='cb';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='centiliter' WHERE UNIT='cl';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='deciliter' WHERE UNIT='dl';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='liter' WHERE UNIT='l';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='milligram' WHERE UNIT='mg';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='centigram' WHERE UNIT='cg';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='decigram' WHERE UNIT='dg';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='gram' WHERE UNIT='g';&#xA;</xsl:text>
        <xsl:text>UPDATE INGREDIENT SET UNIT='kilogram' WHERE UNIT='kg';&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=20;&#xA;</xsl:text>
      </xsl:if>
      <xsl:if test="version &lt;= 20">
        <xsl:text>ALTER TABLE INGREDIENT CHANGE AMOUNT AMOUNTDOUBLE FLOAT;&#xA;</xsl:text>
        <xsl:text>ALTER TABLE INGREDIENT ADD AMOUNTNOMINATOR INT UNSIGNED AFTER AMOUNTDOUBLE;&#xA;</xsl:text>
        <xsl:text>ALTER TABLE INGREDIENT ADD AMOUNTDENOMINATOR INT UNSIGNED AFTER AMOUNTNOMINATOR;&#xA;</xsl:text>
        <xsl:text>UPDATE COOKBOOK SET SUBVERSION=21;&#xA;</xsl:text>
      </xsl:if>
    </xsl:if>
  </xsl:template>

  <!-- Retrieve list of categories. -->
  <xsl:template match="categories">
    <xsl:text># OPEN categories;&#xA;</xsl:text>
    <xsl:text>SELECT COUNT(CY.RECIPEID),CIES.NAME FROM CATEGORY CY,CATEGORIES CIES WHERE CIES.ID=CY.CATEGORYID GROUP BY CIES.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE categories;&#xA;</xsl:text>
  </xsl:template>

  <!-- Insert a bunch of recipes. -->
  <xsl:template match="insert">
    <xsl:apply-templates select="recipe"/>
  </xsl:template>

  <!-- Replace a single recipe. Calls delete-template. -->
  <xsl:template match="replace">

    <!-- Insert modified recipe. -->
    <xsl:apply-templates select="recipe"/>

    <!-- Update selection.. -->
    <xsl:text>UPDATE SELECTION SET RECIPEID=@RECIPE WHERE RECIPEID=</xsl:text>
    <xsl:value-of select="selection/single"/>
    <xsl:text>;&#xA;</xsl:text>

    <!-- Return new number of modified recipe. -->
    <xsl:text># OPEN recipe;&#xA;</xsl:text>
    <xsl:text>SELECT @RECIPE,'</xsl:text>
    <xsl:call-template name="escape-ws">
      <xsl:with-param name="text" select="recipe/title"/>
    </xsl:call-template>
    <xsl:text>';&#xA;</xsl:text>
    <xsl:text># CLOSE recipe;&#xA;</xsl:text>

    <!-- Remove old recipe. -->
    <xsl:apply-templates select="selection"/>
    <xsl:call-template name="delete"/>

  </xsl:template>

  <!-- Get number of recipes. -->
  <xsl:template match="count">
    <xsl:text># OPEN count;&#xA;</xsl:text>
    <xsl:choose>
      <!-- Optimized query for counting all recipes. -->
      <xsl:when test="selection/all">
        <xsl:text>SELECT COUNT(ID) FROM RECIPE;&#xA;</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates select="selection"/>
        <xsl:text>SELECT COUNT(ID) FROM TMP;&#xA;</xsl:text>
        <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:text># CLOSE count;&#xA;</xsl:text>
  </xsl:template>

  <!-- Extract some database values. -->
  <xsl:template match="statistics">
    <xsl:text># OPEN statistics;&#xA;</xsl:text>
    <xsl:text>SELECT COUNT(ID) FROM RECIPE;&#xA;</xsl:text>
    <xsl:text>SELECT COUNT(ID) FROM CATEGORIES;&#xA;</xsl:text>
    <xsl:text>SELECT COUNT(ID) FROM EDIBLE;&#xA;</xsl:text>
    <xsl:text># CLOSE statistics;&#xA;</xsl:text>
  </xsl:template>

  <!-- Delete recipes. Invokes delete-template. -->
  <xsl:template match="delete">
    <xsl:apply-templates select="selection"/>
    <xsl:text># OPEN delete;&#xA;</xsl:text>
    <xsl:call-template name="delete"/>
    <xsl:text># CLOSE delete;&#xA;</xsl:text>
  </xsl:template>

  <!-- Retrieve recipes. -->
  <xsl:template match="query">
    <xsl:apply-templates select="selection"/>
    <xsl:text># OPEN query;&#xA;</xsl:text>
    <xsl:text># OPEN title;&#xA;</xsl:text>
    <xsl:text>SELECT R.ID,R.TITLE,R.AMOUNT,R.UNIT FROM RECIPE R,TMP T WHERE R.ID=T.ID ORDER BY T.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE title;&#xA;</xsl:text>
    <xsl:text># OPEN categories;&#xA;</xsl:text>
    <xsl:text>SELECT CY.RECIPEID,CIES.NAME FROM CATEGORY CY,CATEGORIES CIES,TMP T WHERE CY.RECIPEID=T.ID AND CIES.ID=CY.CATEGORYID ORDER BY CY.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE categories;&#xA;</xsl:text>
    <xsl:text># OPEN section;&#xA;</xsl:text>
    <xsl:text>SELECT S.RECIPEID,S.ID,S.TITLE FROM SECTION S,TMP T WHERE S.RECIPEID=T.ID ORDER BY S.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE section;&#xA;</xsl:text>
    <xsl:text># OPEN ingredient;&#xA;</xsl:text>
    <xsl:text>SELECT I.RECIPEID,I.SECTIONID,I.AMOUNTDOUBLE,I.AMOUNTNOMINATOR,I.AMOUNTDENOMINATOR,I.UNIT,E.NAME,I.PREP FROM TMP T,INGREDIENT I,EDIBLE E WHERE I.RECIPEID=T.ID AND E.ID=I.EDIBLEID ORDER BY I.SECTIONID,I.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE ingredient;&#xA;</xsl:text>
    <xsl:text># OPEN instructions;&#xA;</xsl:text>
    <xsl:text>SELECT I.RECIPEID,I.TITLE,I.INSTRUCTIONS FROM INSTRUCTIONS I,TMP T WHERE I.RECIPEID=T.ID ORDER BY I.ID;&#xA;</xsl:text>
    <xsl:text># CLOSE instructions;&#xA;</xsl:text>
    <xsl:text># CLOSE query;&#xA;</xsl:text>
    <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
  </xsl:template>

  <!-- Change user's selection. -->
  <xsl:template match="select">
    <xsl:apply-templates select="selection"/>
    <xsl:choose>
      <xsl:when test="state=1">
        <xsl:text>INSERT IGNORE INTO SELECTION SELECT T.ID,'</xsl:text>
        <xsl:value-of select="@user"/>
        <xsl:text>' FROM TMP T;&#xA;</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>DELETE FROM SELECTION USING SELECTION,TMP WHERE SELECTION.USER='</xsl:text>
        <xsl:value-of select="@user"/>
        <xsl:text>' AND SELECTION.RECIPEID=TMP.ID;&#xA;</xsl:text>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
  </xsl:template>

  <!-- List recipe-id and title. -->
  <xsl:template match="list">

    <xsl:apply-templates select="selection"/>

    <!-- Add selection-information. -->
    <xsl:text>ALTER TABLE TMP ADD COLUMN SELECTED BIT NOT NULL;&#xA;</xsl:text>
    <xsl:text>UPDATE TMP T,SELECTION S SET T.SELECTED=1 WHERE T.ID=S.RECIPEID AND USER='</xsl:text>
    <xsl:value-of select="selection/@user"/>
    <xsl:text>';&#xA;</xsl:text>

    <xsl:text># OPEN results;&#xA;</xsl:text>
    <!-- Extract id-numbers and titles of remaining recipes. -->
    <xsl:text>SELECT T.ID,R.TITLE,T.SELECTED FROM TMP T,RECIPE R WHERE R.ID=T.ID </xsl:text>
    <xsl:if test="@ordered='true'">
      <xsl:text>ORDER BY R.TITLE </xsl:text>
    </xsl:if>
    <xsl:text>LIMIT 0,</xsl:text>
    <xsl:value-of select="@limit"/>
    <xsl:text>;&#xA;</xsl:text>
    <xsl:text># CLOSE results;&#xA;</xsl:text>
    <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>

  </xsl:template>

  <!-- =============== Secondary templates =========================== -->

  <!-- Select recipes. TMP is used as return value. -->
  <xsl:template match="selection">

    <xsl:text>CREATE TEMPORARY TABLE TMP(ID INT UNSIGNED NOT NULL REFERENCES RECIPE,PRIMARY KEY(ID));&#xA;</xsl:text>
    <xsl:choose>

      <!-- Select single recipe. -->
      <xsl:when test="single">
        <xsl:text>INSERT INTO TMP VALUES(</xsl:text>
        <xsl:value-of select="single"/>
        <xsl:text>);&#xA;</xsl:text>
      </xsl:when>

      <!-- Select recipes with id within a certain range. -->
      <xsl:when test="range">
        <xsl:text>INSERT INTO TMP SELECT ID FROM RECIPE LIMIT </xsl:text>
        <xsl:value-of select="range/skip"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="range/count"/>
        <xsl:text>;&#xA;</xsl:text>        
      </xsl:when>
      <xsl:when test="list">
        <xsl:for-each select="list/id">
          <xsl:text>INSERT INTO TMP VALUES(</xsl:text>
          <xsl:value-of select="."/>
          <xsl:text>);&#xA;</xsl:text>
        </xsl:for-each>
      </xsl:when>

      <!-- Search recipes. -->
      <xsl:when test="search">

        <xsl:choose>
          <xsl:when test="search/selection">
            <xsl:text>INSERT INTO TMP SELECT RECIPEID FROM SELECTION WHERE USER='</xsl:text>
            <xsl:value-of select="@user"/>
            <xsl:text>';&#xA;</xsl:text>
          </xsl:when>
          <xsl:otherwise>
            <xsl:text>INSERT INTO TMP SELECT ID FROM RECIPE;&#xA;</xsl:text>
          </xsl:otherwise>
        </xsl:choose>

        <!-- Select by title keywords.
         Mode 0: Single phrase.
             Mode 1: All words.
             Mode 2: Any word. -->
        <xsl:if test="search/title">
          <xsl:text>CREATE TEMPORARY TABLE TMP2(ID INT UNSIGNED NOT NULL REFERENCES RECIPE,PRIMARY KEY(ID));&#xA;</xsl:text>
          <xsl:text>INSERT INTO TMP2 SELECT T.ID FROM TMP T,RECIPE R WHERE T.ID=R.ID AND </xsl:text>
          <xsl:for-each select="search/title/word">
            <xsl:text>R.TITLE </xsl:text>
            <xsl:text>RLIKE '(^|[^0-9a-zA-Z])</xsl:text>
            <xsl:call-template name="escape-ws">
              <xsl:with-param name="text" select="."/>
            </xsl:call-template>
            <xsl:text>([^0-9a-zA-Z]|$)'</xsl:text>
            <xsl:choose>
              <xsl:when test="not(position()=last())">
                <xsl:text> AND </xsl:text>
              </xsl:when>
              <xsl:otherwise>
                <xsl:text>;&#xA;</xsl:text>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:for-each>
          <!-- Drop old list ... -->
          <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
          <!-- ... and replace with new filtered list. -->
          <xsl:text>ALTER TABLE TMP2 RENAME TMP;&#xA;</xsl:text>
        </xsl:if>

        <!-- Filter by recipe's categories -->
        <xsl:if test="search/category">
          <xsl:text>CREATE TEMPORARY TABLE TMP2(ID INT UNSIGNED NOT NULL REFERENCES CATEGORIES,PRIMARY KEY(ID));&#xA;</xsl:text>
          <xsl:text>INSERT INTO TMP2 SELECT ID FROM CATEGORIES WHERE NAME</xsl:text>
          <!-- Perform positive or negative query ((not) in category list). -->
          <xsl:if test="search/category/@positive='0'"> NOT</xsl:if>
          <xsl:text> IN (</xsl:text>
          <!-- The categories are forming a set. -->
          <xsl:for-each select="search/category/word">
            <xsl:text>'</xsl:text>
            <xsl:call-template name="escape-ws">
              <xsl:with-param name="text" select="."/>
            </xsl:call-template>
            <xsl:text>'</xsl:text>
            <xsl:if test="not(position()=last())">,</xsl:if>
          </xsl:for-each>
          <xsl:text>);&#xA;</xsl:text>
          <xsl:text>CREATE TEMPORARY TABLE TMP3(ID INT UNSIGNED NOT NULL REFERENCES RECIPE,PRIMARY KEY(ID));&#xA;</xsl:text>
          <xsl:text>INSERT INTO TMP3 SELECT T.ID FROM TMP T,CATEGORY C,TMP2 T2 WHERE C.RECIPEID=T.ID AND C.CATEGORYID=T2.ID GROUP BY T.ID;&#xA;</xsl:text>
          <xsl:text>DROP TEMPORARY TABLE TMP2;&#xA;</xsl:text>
          <!-- Drop old list ... -->
          <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
          <!-- ... and replace with new filtered list. -->
          <xsl:text>ALTER TABLE TMP3 RENAME TMP;&#xA;</xsl:text>
        </xsl:if>

        <!-- Filter recipes with ingredient-specification. -->
        <xsl:if test="search/ingredient">
          <xsl:for-each select="search/ingredient/word[@positive='1']">
            <xsl:text>CREATE TEMPORARY TABLE TMP2(ID INT UNSIGNED NOT NULL REFERENCES RECIPE,PRIMARY KEY(ID));&#xA;</xsl:text>
            <xsl:text>INSERT INTO TMP2 SELECT T.ID FROM TMP T,INGREDIENT I,EDIBLE E WHERE T.ID=I.RECIPEID AND I.EDIBLEID=E.ID AND E.NAME RLIKE '(^|[^0-9a-zA-Z])</xsl:text>
            <xsl:call-template name="escape-ws">
              <xsl:with-param name="text" select="."/>
            </xsl:call-template>
            <xsl:text>([^0-9a-zA-Z]|$)' GROUP BY T.ID;&#xA;</xsl:text>
            <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
            <xsl:text>ALTER TABLE TMP2 RENAME TMP;&#xA;</xsl:text>
          </xsl:for-each>
          <xsl:for-each select="search/ingredient/word[@positive='0']">
            <xsl:text>DELETE FROM TMP USING TMP,INGREDIENT,EDIBLE WHERE TMP.ID=INGREDIENT.RECIPEID AND INGREDIENT.EDIBLEID=EDIBLE.ID AND EDIBLE.NAME RLIKE '(^|[^0-9a-zA-Z])</xsl:text>
            <xsl:call-template name="escape-ws">
              <xsl:with-param name="text" select="."/>
            </xsl:call-template>
            <xsl:text>([^0-9a-zA-Z]|$)';&#xA;</xsl:text>
          </xsl:for-each>
        </xsl:if>
      </xsl:when>

      <!-- Select all recipes. -->
      <xsl:when test="all">
        <xsl:text>INSERT INTO TMP SELECT ID FROM RECIPE;&#xA;</xsl:text>
      </xsl:when>
    </xsl:choose>
  </xsl:template>

  <!-- Store recipe in database.
       The variable @RECIPE is used as return-value. -->
  <xsl:template match="recipe">

    <!-- Generate entry with title, yield and instructions. -->
    <xsl:text>INSERT INTO RECIPE VALUES(NULL,'</xsl:text>
    <xsl:call-template name="escape-ws">
      <xsl:with-param name="text" select="title"/>
    </xsl:call-template>
    <xsl:text>',</xsl:text>
    <xsl:value-of select="servings/amount"/>
    <xsl:text>,'</xsl:text>
    <xsl:call-template name="escape-ws">
      <xsl:with-param name="text" select="servings/unit"/>
    </xsl:call-template>
    <xsl:text>','</xsl:text>
    <xsl:variable name="lang-scope"
      select="(ancestor-or-self::*[@lang]
              |ancestor-or-self::*[@xml:lang])[last()]"/>
    <xsl:variable name="lang-attr"
      select="($lang-scope/@lang | $lang-scope/@xml:lang)[1]"/>
    <xsl:choose>
      <xsl:when test="string($lang-attr) = ''">en</xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$lang-attr"/>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:text>');&#xA;</xsl:text>

    <!-- Memorize recipe's id. -->
    <xsl:text>SET @RECIPE := LAST_INSERT_ID();&#xA;</xsl:text>

    <xsl:for-each select="instructions/section">
      <xsl:text>INSERT INTO INSTRUCTIONS VALUES(</xsl:text>
      <xsl:value-of select="position()"/>
      <xsl:text>,@RECIPE,'</xsl:text>
      <xsl:call-template name="escape-ws">
        <xsl:with-param name="text" select="title"/>
      </xsl:call-template>
      <xsl:text>','</xsl:text>
      <xsl:for-each select="par">
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text" select="."/>
        </xsl:call-template>
        <xsl:if test="not(position()=last())">\n</xsl:if>
      </xsl:for-each>
      <xsl:text>');&#xA;</xsl:text>
    </xsl:for-each>

    <!-- Create links for categories. -->
    <xsl:for-each select="categories/category">
      <xsl:text>INSERT IGNORE INTO CATEGORIES VALUES(NULL,'</xsl:text>
      <xsl:call-template name="escape-ws">
        <xsl:with-param name="text" select="."/>
      </xsl:call-template>
      <xsl:text>');&#xA;</xsl:text>
      <xsl:text>SELECT @CATEGORY:=ID FROM CATEGORIES WHERE NAME='</xsl:text>
      <xsl:call-template name="escape-ws">
        <xsl:with-param name="text" select="."/>
      </xsl:call-template>
      <xsl:text>';&#xA;</xsl:text>
      <xsl:text>INSERT INTO CATEGORY VALUES(@RECIPE,</xsl:text>
      <xsl:value-of select="position()"/>
      <xsl:text>,@CATEGORY);&#xA;</xsl:text>
    </xsl:for-each>

    <!-- Store information about ingredients. -->
    <xsl:for-each select="ingredients/section">

      <!-- Memorize section-number. -->
      <xsl:text>SET @SECTION := </xsl:text>
      <xsl:value-of select="position()"/>
      <xsl:text>;&#xA;</xsl:text>
      <!-- Store section title. -->
      <xsl:text>INSERT INTO SECTION VALUES(@SECTION,@RECIPE,'</xsl:text>
      <xsl:call-template name="escape-ws">
        <xsl:with-param name="text" select="title"/>
      </xsl:call-template>
      <xsl:text>');&#xA;</xsl:text>

      <xsl:for-each select="ingredient">
        <!-- Store name of ingredient. -->
        <xsl:text>INSERT IGNORE INTO EDIBLE VALUES(NULL,'</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text" select="name"/>
        </xsl:call-template>
        <xsl:text>');&#xA;</xsl:text>
        <!-- Get key of ingredient. -->
        <xsl:text>SELECT @INGREDIENT:=ID FROM EDIBLE WHERE NAME='</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text" select="name"/>
        </xsl:call-template>
        <xsl:text>';&#xA;</xsl:text>

        <!-- Make entry in ingredient table. -->
        <xsl:text>INSERT INTO INGREDIENT VALUES(@RECIPE,@SECTION,</xsl:text>
        <xsl:value-of select="position()"/>
        <xsl:text>,@INGREDIENT,</xsl:text>
        <xsl:choose>
          <xsl:when test="amount/float">
            <xsl:value-of select="amount/float"/>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
        <xsl:text>,</xsl:text>
        <xsl:choose>
          <xsl:when test="amount/fraction">
            <xsl:value-of select="amount/fraction/nominator"/>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
        <xsl:text>,</xsl:text>
        <xsl:choose>
          <xsl:when test="amount/fraction">
            <xsl:value-of select="amount/fraction/denominator"/>
          </xsl:when>
          <xsl:otherwise>NULL</xsl:otherwise>
        </xsl:choose>
        <xsl:text>,'</xsl:text>
        <xsl:value-of select="unit"/>
        <xsl:text>','</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text" select="prep"/>
        </xsl:call-template>
        <xsl:text>');&#xA;</xsl:text>
      </xsl:for-each>
    </xsl:for-each>

  </xsl:template>

  <!-- Delete template. TMP is used as argument. -->
  <xsl:template name="delete">
    <xsl:text>DELETE FROM RECIPE USING RECIPE,TMP WHERE RECIPE.ID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>DELETE FROM CATEGORY USING CATEGORY,TMP WHERE CATEGORY.RECIPEID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>DELETE FROM INSTRUCTIONS USING INSTRUCTIONS,TMP WHERE INSTRUCTIONS.RECIPEID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>DELETE FROM SECTION USING SECTION,TMP WHERE SECTION.RECIPEID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>DELETE FROM INGREDIENT USING INGREDIENT,TMP WHERE INGREDIENT.RECIPEID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>DELETE FROM SELECTION USING SELECTION,TMP WHERE SELECTION.RECIPEID=TMP.ID;&#xA;</xsl:text>
    <xsl:text>SELECT ID FROM TMP;&#xA;</xsl:text>
    <xsl:text>DROP TEMPORARY TABLE TMP;&#xA;</xsl:text>
  </xsl:template>

  <!-- Prepare text for use in SQL script. -->
  <xsl:template name="escape-ws">
    <xsl:param name="text"/>
    <xsl:choose>

      <!-- Escape backslash. -->
      <xsl:when test="contains($text,'\')">
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text">
            <xsl:value-of select="substring-before($text,'\')"/>
          </xsl:with-param>
        </xsl:call-template>
        <xsl:text>\\</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text"
            select="substring-after($text,'\')"/>
        </xsl:call-template>
      </xsl:when>

      <!-- Replace return by space -->
      <xsl:when test="contains($text,'&#xA;')">
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text">
            <xsl:value-of select="substring-before($text,'&#xA;')"/>
          </xsl:with-param>
        </xsl:call-template>
        <xsl:text> </xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text"
            select="substring-after($text,'&#xA;')"/>
        </xsl:call-template>
      </xsl:when>

      <!-- Escape tabulator commands. -->
      <xsl:when test="contains($text,'&#x9;')">
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text">
            <xsl:value-of select="substring-before($text,'&#x9;')"/>
          </xsl:with-param>
        </xsl:call-template>
        <xsl:text>\t</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text"
            select="substring-after($text,'&#x9;')"/>
        </xsl:call-template>
      </xsl:when>

      <!-- Escape quotation marks. -->
      <xsl:when test='contains($text,"&#x27;")'>
        <xsl:value-of select='substring-before($text,"&#x27;")'/>
        <xsl:text>''</xsl:text>
        <xsl:call-template name="escape-ws">
          <xsl:with-param name="text"
            select='substring-after($text,"&#x27;")'/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$text"/>
      </xsl:otherwise>
    </xsl:choose>

  </xsl:template>

</xsl:stylesheet>