Apache FOP 2.0の新機能(AES256bit暗号、OpenTypeなど)   Leave a comment

1.Apache FOP2.0の新機能
2012/10/20 FOP1.1がリリースされて、約2年6ヶ月後の2015/06/03日に、2.0がリリースされました。
OpenTypeフォントの対応は、要望が多かったと思います。OpenTypeのみのフォントの場合、重宝しそうです。
個人的には、前々から希望していた、AES256bitの暗号化対応が、されていて、とても良かったです。これで、セキュリティがより高まったと思います。
その他の機能の詳細は、右記サイトで確認できます。 https://xmlgraphics.apache.org/fop/2.0/releaseNotes_2.0.html
JDKでAES256bitを使用する場合は、「Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy」が必要です。
下記のORACLEのURLからダウンロードできます。

JDK 7用JCE http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html
JDK 8用JCE http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html

図 1. EncryptPDF.javaでは、46行目で高強度のAES 256bitを使用するようにしています。「Apache™ FOP: PDF encryption.」にて、レンダラのオプションで設定できる項目が記載されています。「fop.xconf」の設定ファイル及び、プログラム内部でパスワード等の設定ができます。文書を開くパスワードで、PDFを開いた場合と、権限パスワードで、PDFを開いた場合で、PDFの文書プロパティの「PDFの文書に関する制限の概要」に違いがあります。巷には、PDFのセキュリティを解除するソフトや、オンラインのサービスが展開されていますので、PDFのパスワードの管理はしっかりしたいところです。パスワードの長さは、8桁では数時間で解析できる環境があるので、注意が必要です。

2.フォントファミリ名設定の確認方法
フォントファミリ名を指定してあるけで、思ったとおりにフォントが出力されない場合は、初回実行時に、下記場所に、フォントキャッシュが作成されるので、%UserProfile%\.fop\fop-fonts.cache
fop-fonts.cacheをテキストエディタで開き、フォントファイル名で検索する。その後、その付近にあるフォント名を確認する。
フォントキャッシュは、削除すると自動で再作成されます。フォントのインストールした場合は、削除して再作成する必要があります。

(SyntaxHighlighterを使用して、プログラムを掲載しています。ダブルクリックで全選択されます。注意点として、「さらに記事を読み込む」のリンクで、この記事を読み込んだ場合に、SyntaxHighlighterによる表示が不正になる場合がありますので、恐れ入りますが、タイトルをクリックして再表示して下さい。)

図 1. EncryptPDF.java

package com.wordpress.okulejp.encryptpdf;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.sax.SAXResult;
import javax.xml.transform.stream.StreamSource;

import org.apache.fop.apps.FOUserAgent;
import org.apache.fop.apps.Fop;
import org.apache.fop.apps.FopConfParser;
import org.apache.fop.apps.FopFactory;
import org.apache.fop.apps.FopFactoryBuilder;
import org.apache.fop.apps.MimeConstants;
import org.apache.fop.pdf.PDFEncryptionParams;

public class EncryptPDF {
	public static void main(String[] args) throws TransformerException, TransformerConfigurationException, FileNotFoundException, IOException {
		OutputStream out = null;
		try {
			FopConfParser fopConfParser = new FopConfParser(new File("fop.xconf"));
			FopFactoryBuilder fopFactoryBuilder = fopConfParser.getFopFactoryBuilder();
			FopFactory fopFactory = fopFactoryBuilder.build();
			FOUserAgent foUserAgent = fopFactory.newFOUserAgent();			
			
			out = new FileOutputStream("blog/encryptPDF.pdf");
			out = new BufferedOutputStream(out);
			
			//セキュリティ設定
			PDFEncryptionParams params = new PDFEncryptionParams(); 
			params.setOwnerPassword("owner");
			params.setUserPassword("user");
			params.setAllowCopyContent(false); 
			params.setAllowEditContent(false);
			params.setAllowEditAnnotations(false);
			//暗号鍵の鍵長の指定
			params.setEncryptionLengthInBits(256);
			foUserAgent.getRendererOptions().put("encryption-params", params);
			
			Fop fop = fopFactory.newFop(MimeConstants.MIME_PDF, foUserAgent, out);
			String fo = "blog/encryptPDF.fo";
			
			Result result = new SAXResult(fop.getDefaultHandler());
			Source src = new StreamSource(new File(fo));

			TransformerFactory factory = TransformerFactory.newInstance();
			Transformer transformer = factory.newTransformer();
			//PDF出力
			transformer.transform(src, result);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			out.close();
		}
	}
}

図 2. encryptPDF.fo

<?xml version="1.0" encoding="UTF-8" ?>
<fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format" xml:lang="ja">
  <fo:layout-master-set>
    <fo:simple-page-master page-width="210mm" page-height="297mm" margin-top="0mm" margin-left="0mm" margin-right="0mm" margin-bottom="0mm" master-name="PageMaster">
      <fo:region-body margin-top="0mm" margin-left="0mm" margin-right="0mm" margin-bottom="0mm"/>
      <fo:region-before extent="0mm" />
      <fo:region-after extent="0mm" />
      <fo:region-start extent="0mm" />
      <fo:region-end extent="0mm" />
    </fo:simple-page-master>
  </fo:layout-master-set>
  <fo:page-sequence master-reference="PageMaster">
    <fo:flow flow-name="xsl-region-body">
      <fo:block>
        <fo:instream-foreign-object content-width="210mm" content-height="297mm">
          <svg xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/2000/svg"
               width="210mm" height="297mm" font-size="20" stroke-width="0.1mm">
            <text x="4.9mm" y="10.5mm" fill="black">AES256bit暗号化PDF</text>
          </svg>
        </fo:instream-foreign-object>
      </fo:block>
    </fo:flow>
  </fo:page-sequence>
</fo:root>

図 3. 暗号化したPDF(クリックするとPDFを表示します。権限パスワードは、「owner」です。文書を開くパスワードは、「user」です。)
(このサンプルでは、encryptPDF.foの18行目に、font-family=”aoyagireisyo2″と追記して、OpenTypeフォントの「青柳隷書しも」を使用しております。)

2.最後に
このブログ記事が、実際のシステム開発に役立った方は、ぜひいいねボタンを押してください。またコメントもよろしくお願いいたします。(コメントするのに電子メールアドレス、名前、ウェブサイトは必須ではありません。入力なしで匿名でコメント可能です。)

©中條勝徳 and okulejp.com, 2012.

okule による FOP, SVG, XML への投稿 (FOP, SVG, XML)

DB2 Express-C 10.5 FP1(10.5.1)がリリースされました。   Leave a comment

DB2 Express-C 10.5 FP1(10.5.1)がリリースされました。

DB2 Express-C 10.5がリリースされた当初、Text Searchの機能が利用できなかったので、あきらめていましたが、今回のリリースで、Text Searchの機能を利用できるようになったので良かったです。Text Searchがインストールできない件を日本語のサイトで検索しても、検索できなかったことに対して残念に思っていました。全文検索機能のText Searchは、非常に便利なのにもかかわらず、注目されていないようで、すごくがっかりです。XMLのデータに対しても、全文検索機能を利用できます。

下記フォーラムは、Text Searchがインストールできない件での質疑応答があります。(英語サイトです。)
DB2 Express-C 10.5: Text Search component is not installed

参照
DB2 for LUW V10.5 FP1 公開のお知らせ(DM-13-024)

okule による DB2 への投稿 (DB2)

XSLT2.0によるコード自動生成(DDL、テストデータ用SQL)   Leave a comment

1.概要

Open Office Calc 形式のドキュメントから、テーブル定義のDDL及び、テスト用データのSQLを自動生成します。main.xslは、ddl_gen.xsl、sql_gen.xsl、common.xsl及びfunction.xslをインクルードします。common.xsl.は、sql.xsl 及び ddl.xsl で使用されるテンプレートを定義しています。function.xsl には、使用する全ての関数が定義されています。DDLまたは、SQLを自動生成するかは、シート名により判断します。シート名に「ddl」が含まれていれば、DDLを生成し、「sql」が含まれていれば、sqlを自動生成します。DDL及び、SQLを自動生成するシートは、一つにまとめたり、複数に分散することも可能です。sql_gen.xslには、データの入力チェックがあります。入力チェックの種類は、使用するデータベースの文字エンコーディングに基づいたサイズチェック及び、それぞれの型に基づいた妥当性チェックの機能があります。入力チェックエラーがあった場合に、セルのアドレスを含んだエラーメッセージを出力して処理を終了するので、エラーデータの場所をすぐに特定できます。またすべての入力チェックエラーを出力します。ODS(open office spreadsheets) ファイルは、ZIP形式のファイルで、いくつかのファイルを含んでいます。
content.xml は、ODS ファイルを構成するパートで、ドキュメントのテキストを全て含んでいます。content.xmlから、データを加工しやすいように、いくつかのモデルに変換して最終的に、DDL及びSQLを自動生成しています。

ddl_gen.xslの処理の流れ
content.xml - 第一モデル - 第二モデル - 第三モデル - 第四モデル - DDL
sql_gen.xslの処理の流れ
content.xml - 第一モデル - 第二モデル - insert文
                                      - 第三モデル - update文及びdelete文

XSLT、ODSファイル、Javaファイル一式

図 1. ddlシート

図 2. sqlシート

 

2.目的

モデルデザイン技術と、XSLT2.0の強力な関数の使用方法を学びます。ここで掲載しているサンプルコードは、有益なXSLT2.0の関数と、それに適したモデルを使用しています。ここで掲載しているXSLT2.0のサンプルコードを深く理解した場合、コードジェネレーターにより、ODS形式のファイルから、一段階ではなく、複数の段階をへてDDL及び、SQLに変換していることが理解できると思います。また、XSLT変換の難易度が、いくつかの段階を踏むことで下がり、幅広く難易度が下がった分、それぞれのXSLT変換の保守性が良くなっています。使用しているXSLT2.0の関数は、xsl:for-each-group, xsl:function, xsl:result-documentです。また、使用しているXpath2.0の演算子は、is演算子です(common.xslの15行目)。このサイトで学んでことを適用すれば、読者のみなさんが、オリジナルのコードジェネレーターを作成して、ユーザーインターフェース仕様書、画面設計書及びデータベース定義書から、PHP、ASP、SQL、DDL、Data Access Object(DAO)、JSF、JSP、Servlet、EJB、JavaBeanなどを自動生成することができるようになります。
 

3.文字色・背景色設定とDB環境依存設定

どのセルが、テーブル名、列名などを示しているかは、フォント色と背景色を使用して区別しています。以下が設定です。好みに合わせ変えることができます。
DDL作成時の色設定
テーブル名:文字色 blue5(#0047ff)
ヘッダー:背景色 blue6(#0099ff)

SQL作成時の色設定
テーブル名:文字色 blue5(#0047ff)
カラム名は:背景色 blue6(#0099ff)
プライマリキー:文字色 light green(#00ff00)
型名:背景色 blue gray(#e6e6ff)

function.xslでは、DBのエンコードは、UTF-8を前提しています。108行目のコメントを参照して、EUC-JPまたは、Shift-JISも対応可能です。
193-282行目で、DB2で使用する型チェックを行っています。対応する型チェックは、char、number、varchar、smallint、integer、bigint、date及びtimestampです。応用すれば、Oracle、MySQL、PostgreSQLにも対応できます。
 

4.Excel VBA vs XSLT2.0 + Open Office Calc

Excelは、商用のソフトウェアであるが、Open Office Calcは、オープンソースのソフトウェアで、誰もが自由に使用することができます。私が実際に、Excel VBAとXSLT2.0を使用してコード生成した時、XSLT2.0の方が優れていましたし、少ないコーディングでより多くのことが簡単に実現できす。Excel VBAは、それ自体に使用できるメモリに制限があるのに対し、XSLT2.0は、システムで利用できるメモリの範囲内において制限がないからです。またXSLT2.0には、より多くの関数が使用できす。しかし、セルの情報を取得設定する事に関しては、Excel VBAの方が優れています。
 

5.今日のキーポイント

難しい単体の層を、いくつかのより易しい層に分解すること。難しいロジックも、複数の易しいロジックを組み合わせて実現することで、保守性も向上し、バグの発生を抑えることができます。
 

6.謝辞

下記の記事は、私自信のスキルアップに多いに役立ちました。6年前に、初めてこの記事を読んだ時は、システム設計書に使用するソフトウェアの保存形式は、独自のバリナリー形式が一般的でした。XML技術が浸透した現在では、XML形式で保存できるので、それらのドキュメントから、プログラムの自動生成をするのに、XSLT2.0を使用することができます。
IBM技術文書:XSLT 2.0によるコード生成 第1回: XSLT 2.0でSQLを生成する

図 3. SimpleTransform.java (codeGeneration.odsからcontent.xmlを取り出し、SaxonのXSLT2.0プロセッサを呼び出しています。 )

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.OutputKeys;

public class SimpleTransform {
public static void main(String[] args) {
try {
TransformerFactory trf = TransformerFactory.newInstance("net.sf.saxon.TransformerFactoryImpl", null);
Transformer transformer = trf.newTransformer(new StreamSource("main.xsl"));
java.util.Properties xmlProps = new java.util.Properties();
xmlProps.setProperty(OutputKeys.VERSION, "1.0");
xmlProps.setProperty(OutputKeys.INDENT, "yes");
transformer.setOutputProperties(xmlProps);
String odsFileName = "C:/Users/okule/Documents/open_office/work/codeGeneration.ods";
transformer.transform(new StreamSource(SimpleTransform.getContentXMLFile(odsFileName)),
new StreamResult(new OutputStreamWriter(System.out, "UTF-8")));
} catch (Exception e) {
e.printStackTrace();
}
}

public static String getContentXMLFile(String fileName) throws Exception {
ZipInputStream zipInputStream = null;
FileChannel outputChannel = null;
String zipDecompFileName = null;
try {
zipInputStream = new ZipInputStream(new BufferedInputStream(new FileInputStream(fileName)));
ZipEntry zipEntry = null;
while ((zipEntry = zipInputStream.getNextEntry()) != null) {
if ("content.xml".equals(zipEntry.getName())) {
File tempFile = File.createTempFile("content", ".xml");
zipDecompFileName = tempFile.getAbsolutePath();
FileOutputStream ostream = new FileOutputStream(tempFile);
outputChannel = ostream.getChannel();
ByteBuffer buffer = ByteBuffer.allocate(10000);
byte[] bufferArry = buffer.array();
int i = 0;
while ((i = zipInputStream.read(bufferArry)) != -1) {
buffer.limit(i);
outputChannel.write(buffer);
buffer.position(0);
}
}
zipInputStream.closeEntry();
}
return zipDecompFileName;
} catch (Exception e) {
System.out.println(e);
throw e;
} finally {
if (outputChannel != null) {
outputChannel.close();
}
if (zipInputStream != null) {
zipInputStream.close();
}
}
}
}


図 4. main.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
version="2.0" exclude-result-prefixes="#all">
<xsl:include href="sql_gen.xsl"/>
<xsl:include href="ddl_gen.xsl"/>
<xsl:include href="common.xsl"/>
<xsl:include href="function.xsl"/>

<xsl:template match="/" >
<!-- create ddl  -->
<xsl:apply-templates select="office:document-content/office:body/office:spreadsheet" mode="ddl_gen"/>
<!-- create sql  -->
<xsl:apply-templates select="office:document-content/office:body/office:spreadsheet" mode="sql_gen"/>
</xsl:template>

</xsl:stylesheet>


図 5. ddl_gen.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
xmlns:sql-gen="http://okule.wordpress.com"
version="2.0" exclude-result-prefixes="#all">

<xsl:output method="text" name="ddl_gen"/>
<xsl:template match="office:spreadsheet" mode="ddl_gen">
<xsl:variable name="initial_model">
<rows>
<xsl:for-each select="table:table[contains(@table:name,'ddl')]">
<xsl:for-each select="table:table-row[table:table-cell/text:p]">
<xsl:variable name="cell_y_address"
select="count(preceding-sibling::table:table-row[not(@table:number-rows-repeated)])
+  sum((preceding-sibling::table:table-row/@table:number-rows-repeated)) + 1"/>
<row>
<xsl:for-each select="table:table-cell">
<xsl:variable name="cell_x_address"
select="count(preceding-sibling::table:table-cell[not(@table:number-columns-repeated)])
+ count(preceding-sibling::table:covered-table-cell[not(@table:number-columns-repeated)])
+ sum((preceding-sibling::table:table-cell/@table:number-columns-repeated))
+ 1"/>
<xsl:variable name="table:style-name" select="@table:style-name"/>
<!-- blue5(#0047ff) font color                                             :table name  -->
<!-- blue6(#0099ff) background color                                       :header      -->
<xsl:choose>
<!-- make elements of table name -->
<xsl:when test="/office:document-content/office:automatic-styles/
style:style[@style:name eq $table:style-name]/style:text-properties/@fo:color eq '#0047ff'">
<name>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</name>
</xsl:when>
<!-- make elements of header -->
<xsl:when test="/office:document-content/office:automatic-styles/
style:style[@style:name eq $table:style-name]/style:table-cell-properties/@fo:background-color eq '#0099ff'">
<header>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</header>
</xsl:when>
<!-- make elements of field -->
<xsl:when test="text:p">
<field>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</field>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</row>
</xsl:for-each>
</xsl:for-each>
</rows>
</xsl:variable>

<!-- The first model is made of necessary data extracted from content.xml -->
<xsl:copy-of select="$initial_model"/>
<xsl:text disable-output-escaping="yes">

</xsl:text>

<!-- The secound model is separated with TABLE unit from the first model  -->
<xsl:variable name="initial_model_sepa_table">
<xsl:call-template name="separate_table_units">
<xsl:with-param name="data" select="$initial_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$initial_model_sepa_table"/>
<xsl:text disable-output-escaping="yes">
</xsl:text>

<!-- The third model is grouped by column name -->
<xsl:variable name="pre_ddl_model">
<xsl:call-template name="create_pre_ddl_model">
<xsl:with-param name="data" select="$initial_model_sepa_table"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$pre_ddl_model"/>
<xsl:text disable-output-escaping="yes">
</xsl:text>

<!-- The fourth model haves element of column, pk and index -->
<xsl:variable name="ddl_model">
<xsl:call-template name="create_ddl_model">
<xsl:with-param name="data" select="$pre_ddl_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$ddl_model"/>
<xsl:text disable-output-escaping="yes">
</xsl:text>

<!-- generate create table ddl -->
<xsl:variable name="ddl_sentence">
<xsl:call-template name="create_ddl">
<xsl:with-param name="ddl_model" select="$ddl_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$ddl_sentence"/>
<xsl:result-document  href="blog/wordpress/output/ddl_sentence.txt" format="ddl_gen">
<xsl:copy-of select="$ddl_sentence"/>
</xsl:result-document>

<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:template>

<xsl:template name="create_pre_ddl_model">
<xsl:param name="data"/>
<tables>
<xsl:for-each select="$data/tables/table">
<table>
<xsl:attribute name="id" select="@id"/>
<xsl:attribute name="name" select="row[name]"/>
<xsl:for-each select="row[position()>2]">
<xsl:variable name="index" select="position()"/>
<field>
<xsl:attribute name="name" select="field[2]"/>
<xsl:for-each select="field[position()>2]">
<xsl:attribute name="{lower-case(sql-gen:getRelatedHeader(../../row/header, @cell_address))}" select="."/>
</xsl:for-each>
</field>
</xsl:for-each>
</table>
</xsl:for-each>
</tables>
</xsl:template>

<xsl:template name="create_ddl_model">
<xsl:param name="data"/>
<tables>
<xsl:for-each select="$data/tables/table">
<table name="{@name}">
<!-- column -->
<xsl:for-each select="field">
<column>
<xsl:if test="@not_null">
<xsl:attribute name="not_null" select="@not_null"/>
</xsl:if>
<xsl:attribute name="name" select="@name"/>
<xsl:attribute name="type" select="sql-gen:type_contain_size(@type, @size)"/>
<xsl:if test="@default">
<xsl:attribute name="default" select="@default"/>
</xsl:if>
</column>
</xsl:for-each>

<!-- primary key -->
<xsl:for-each select="field[@pk]">
<xsl:sort select="@pk" data-type="number"/>
<pk>
<xsl:attribute name="pk" select="@pk"/>
<xsl:value-of select="@name"/>
</pk>
</xsl:for-each>

<!-- index -->
<xsl:for-each select="field[@index]">
<xsl:sort select="@index"/>
<index>
<xsl:choose>
<xsl:when test="contains(@index,'-')">
<xsl:attribute name="index1" select="substring-before(@index, '-')"/>
<xsl:attribute name="index2" select="substring-after(@index, '-')"/>
</xsl:when>
<xsl:otherwise>
<xsl:attribute name="index1" select="@index"/>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="@indexname">
<xsl:attribute name="indexname" select="@indexname"/>
</xsl:if>
<xsl:value-of select="@name"/>
</index>
</xsl:for-each>
</table>
</xsl:for-each>
</tables>
</xsl:template>

<!-- generate create table ddl -->
<xsl:template name="create_ddl">
<xsl:param name="ddl_model"/>
<xsl:for-each select="$ddl_model/tables/table">
<xsl:variable name="tableName" select="@name"/>
<!-- make create statement -->
<xsl:text disable-output-escaping="yes">
</xsl:text>
<xsl:text>CREATE TABLE </xsl:text><xsl:value-of select="@name"/>
<xsl:text> (
</xsl:text>
<xsl:for-each select="column">
<xsl:text disable-output-escaping="yes">	</xsl:text><xsl:value-of select="@name"/><xsl:text> </xsl:text>
<xsl:value-of select="@type"/>
<xsl:if test="@not_null eq 'yes'">
<xsl:text> NOT NULL</xsl:text>
</xsl:if>
<xsl:if test="@default">
<xsl:text> DEFAULT </xsl:text>
<xsl:value-of select="@default"/>
</xsl:if>

<xsl:if test="position() != last()">
<xsl:text disable-output-escaping="yes">,
</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text>
);
</xsl:text>

<!-- generate primary key constraint ddl -->
<xsl:for-each-group select="pk" group-by="true()">
<xsl:text disable-output-escaping="yes">
</xsl:text>
<xsl:text/>ALTER TABLE <xsl:value-of select="$tableName"/>
<xsl:text/> ADD CONSTRAINT PK_<xsl:value-of select="$tableName"/>
<xsl:text disable-output-escaping="yes">
	</xsl:text>
<xsl:text/>PRIMARY KEY(<xsl:value-of select="current-group()" separator=", "/>);
</xsl:for-each-group>

<!-- generate index ddl -->
<xsl:for-each-group select="index" group-by="@index1">
<xsl:text disable-output-escaping="yes">
</xsl:text>
<xsl:text/>CREATE INDEX <xsl:value-of select="@indexname"/>
<xsl:text disable-output-escaping="yes">
	</xsl:text>
<xsl:text/>ON <xsl:value-of select="$tableName"/> (<xsl:value-of select="current-group()" separator=", "/>);<xsl:text/>
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:for-each-group>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>


図 6. sql_gen.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
xmlns:sql-gen="http://okule.wordpress.com"
version="2.0" exclude-result-prefixes="#all">

<xsl:template match="office:spreadsheet" mode="sql_gen">
<xsl:variable name="initial_model">
<rows>
<xsl:for-each select="table:table[contains(@table:name,'sql')]">
<xsl:for-each select="table:table-row[table:table-cell/text:p]">
<xsl:variable name="cell_y_address"
select="count(preceding-sibling::table:table-row[not(@table:number-rows-repeated)])
+  sum((preceding-sibling::table:table-row/@table:number-rows-repeated)) + 1"/>
<row>
<xsl:for-each select="table:table-cell">
<xsl:variable name="cell_x_address"
select="count(preceding-sibling::table:table-cell[not(@table:number-columns-repeated)])
+ count(preceding-sibling::table:covered-table-cell[not(@table:number-columns-repeated)])
+ sum((preceding-sibling::table:table-cell/@table:number-columns-repeated))
+ 1"/>
<xsl:variable name="table:style-name" select="@table:style-name"/>
<!-- blue5(#0047ff) font color                                             :table name  -->
<!-- blue6(#0099ff) background color                                       :column name -->
<!-- light green(#00ff00) font color in the column name                    :primary key -->
<!-- blue gray(#e6e6ff) background color                                   :type name   -->
<xsl:choose>
<!-- make elements of table name -->
<xsl:when test="/office:document-content/office:automatic-styles/
style:style[@style:name eq $table:style-name]/style:text-properties/@fo:color eq '#0047ff'">
<name>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</name>
</xsl:when>
<!-- make elements of column -->
<xsl:when test="/office:document-content/office:automatic-styles/
style:style[@style:name eq $table:style-name]/style:table-cell-properties/@fo:background-color eq '#0099ff'">
<xsl:choose>
<xsl:when test="/office:document-content/office:automatic-styles/
style:style[@style:name eq $table:style-name]/style:text-properties/@fo:color eq '#00ff00'">
<column primary="yes">
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</column>
</xsl:when>
<xsl:otherwise>
<column>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</column>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<!-- make elements of type name -->
<xsl:when test="/office:document-content/office:automatic-styles/style:style[@style:name eq $table:style-name]
/style:table-cell-properties/@fo:background-color eq '#e6e6ff'">
<type>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</type>
</xsl:when>
<!-- make elements of data -->
<xsl:when test="text:p">
<data>
<xsl:attribute name="cell_address" select="concat(sql-gen:num_to_alpah($cell_x_address),',',$cell_y_address)"/>
<xsl:value-of select="text:p/text()"/>
</data>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</row>
</xsl:for-each>
</xsl:for-each>
</rows>
</xsl:variable>

<!-- The first model is made of necessary data extracted from content.xml -->
<xsl:copy-of select="$initial_model"/>
<xsl:text disable-output-escaping="yes">

</xsl:text>

<!-- The secound model is separated with TABLE unit from the first model  -->
<xsl:variable name="sql_model">
<xsl:call-template name="separate_table_units">
<xsl:with-param name="data" select="$initial_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$sql_model"/>
<xsl:text disable-output-escaping="yes">

</xsl:text>

<xsl:variable name="check_model_result">
<xsl:call-template name="input_check">
<xsl:with-param name="modyfyNode" select="$sql_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$check_model_result"/>
<xsl:text disable-output-escaping="yes">

</xsl:text>

<!-- make insert statement -->
<xsl:call-template name="create_insert_sentence">
<xsl:with-param name="sql_model" select="$sql_model"/>
</xsl:call-template>

<!-- The third model for creating the where clause of the delete statement and update statement -->
<xsl:variable name="where_sql_model">
<xsl:call-template name="create_where_sql_model">
<xsl:with-param name="sql_model" select="$sql_model"/>
</xsl:call-template>
</xsl:variable>
<xsl:copy-of select="$where_sql_model"/>
<xsl:text disable-output-escaping="yes">

</xsl:text>

<!-- make update statement -->
<xsl:call-template name="create_update_sentence">
<xsl:with-param name="data_model" select="$sql_model"/>
<xsl:with-param name="where_model" select="$where_sql_model"/>
</xsl:call-template>

<!-- make delete statement -->
<xsl:call-template name="create_delete_sentence">
<xsl:with-param name="where_model" select="$where_sql_model"/>
</xsl:call-template>

</xsl:template>

<xsl:template name="input_check">
<xsl:param name="modyfyNode"/>
<xsl:variable name="errorMessage">
<xsl:for-each select="$modyfyNode/tables/table">
<xsl:variable name="columnCount" select="count(row[column]/column)"/>
<xsl:variable name="typeCount" select="count(row[type]/type)"/>
<xsl:choose>
<xsl:when test="$columnCount ne $typeCount">
<error>
<xsl:text disable-output-escaping="yes">The table of </xsl:text>
<xsl:value-of select="row/name"/><xsl:text>(</xsl:text>
<xsl:value-of select="row/name/@cell_address"/>
<xsl:text>) is error data. Because the count of column isn't count of type.</xsl:text>
</error>
</xsl:when>
<xsl:otherwise>
<xsl:for-each select="row[data]">
<xsl:variable name="dataCount" select="count(data)"/>
<xsl:variable name="errorColumnData">
<xsl:if test="$columnCount ne $dataCount">
<error>
<xsl:text disable-output-escaping="yes">The line of (</xsl:text>
<xsl:value-of select="data[1]/@cell_address"/>
<xsl:text>) is error data. Because the count of column isn't count of data.</xsl:text>
</error>
</xsl:if>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length($errorColumnData) gt 0">
<xsl:copy-of select="$errorColumnData"/>
</xsl:when>
<xsl:otherwise>
<xsl:variable name="type" select="preceding-sibling::row[type]"/>
<xsl:for-each select="data">
<xsl:variable name="cuuretn_posiotn" select="position()"/>
<xsl:call-template name="chack_input_type">
<xsl:with-param name="type" select="$type/type[$cuuretn_posiotn]"/>
<xsl:with-param name="data" select="."/>
</xsl:call-template>
</xsl:for-each>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:variable>
<xsl:if test="string-length($errorMessage) gt 0">
<xsl:call-template name="error">
<xsl:with-param name="errorMessage" select="$errorMessage"/>
</xsl:call-template>
</xsl:if>
</xsl:template>

<xsl:template name="chack_input_type">
<xsl:param name="type"/>
<xsl:param name="data"/>
<xsl:variable name="lowerCaseType" select="lower-case($type)"/>
<xsl:variable name="digit" select="normalize-space(substring-after(substring-before($type, ')'), '('))"/>
<xsl:variable name="decimalPointFlg" select="contains($digit, ',')"/>
<xsl:variable name="allDigit" select="normalize-space(substring-before($digit, ','))"/>
<xsl:variable name="decimalPart" select="normalize-space(substring-after($digit, ','))"/>
<xsl:choose>
<xsl:when test="substring-before($lowerCaseType, '(') = 'number' and not($decimalPointFlg)">
<xsl:variable name="patern" select="concat('^\d{1,', $digit, '}$')"/>
<xsl:if test="not(matches($data, $patern))">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>
</xsl:if>
</xsl:when>
<xsl:when test="substring-before($lowerCaseType, '(') = 'number' and $decimalPointFlg and number($decimalPart) eq 0">
<xsl:variable name="patern" select="concat('^\d{1,', $allDigit, '}$')"/>
<xsl:if test="not(matches($data, $patern))">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>
</xsl:if>
</xsl:when>
<xsl:when test="substring-before($lowerCaseType, '(') = 'number' and $decimalPointFlg and number($decimalPart) ne 0">
<xsl:variable name="patern" select="concat('^\d{1,', number($allDigit)-number($decimalPart), '}.\d{0,}$')"/>
<xsl:if test="not(matches($data, $patern))">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>

</xsl:if>
</xsl:when>

<xsl:when test="substring-before($lowerCaseType, '(') = 'char' or substring-before($lowerCaseType, '(') = 'varchar' ">
<xsl:if test="number($digit) &lt; sql-gen:unicodeSize($data)">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/><xsl:text>  Error: Number of characters</xsl:text>
</error>
</xsl:if>
</xsl:when>

<!-- DB2 SMALLINT TYPE -->
<xsl:when test="substring-before($lowerCaseType, '(') = 'smallint'">
<xsl:if test="-32768 lt number($data) and number($data) lt 32767">
<error>
<xsl:text>cell:</xsl:text>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>
</xsl:if>
</xsl:when>

<!-- DB2 INTEGER TYPE -->
<xsl:when test="substring-before($lowerCaseType, '(') = 'integer'">
<xsl:if test="-2147483648 lt number($data) and number($data) lt 2147483647">
<error>
<xsl:text>cell:</xsl:text>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>
</xsl:if>
</xsl:when>

<!-- DB2 BIGINT TYPE -->
<xsl:when test="substring-before($lowerCaseType, '(') = 'bigint'">
<xsl:if test="-9223372036854775808 lt number($data) and number($data) lt 9223372036854775807">
<error>
<xsl:text>cell:</xsl:text>
<xsl:text>  Error: Maximum number of digit</xsl:text>
</error>
</xsl:if>
</xsl:when>

<xsl:when test="$lowerCaseType = 'date'">
<xsl:variable name="patern">^(((19|20)[0-9]{2}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]))|sysdate|current_date)$</xsl:variable>
<xsl:if test="not(matches($data, $patern))">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/>
<xsl:text>  Error: Date format</xsl:text>
</error>
</xsl:if>
</xsl:when>
<xsl:when test="$lowerCaseType = 'timestamp'">
<xsl:variable name="patern">^((19|20)[0-9]{2}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])( (0[0-9]|[1][0-9]|2[0-3]):(0[0-9]|[1-5][0-9]):(0[0-9]|[1-5][0-9]))?|sysdate|current_date)$</xsl:variable>
<xsl:if test="not(matches($data, $patern))">
<error>
<xsl:text>cell:</xsl:text>
<xsl:value-of select="$data/@cell_address"/>
<xsl:text>  Error: Date format</xsl:text>
</error>
</xsl:if>
</xsl:when>
<xsl:otherwise></xsl:otherwise>
</xsl:choose>
</xsl:template>

<xsl:template name="error">
<xsl:param name="errorMessage"/>
<xsl:result-document indent="yes">
<errorList>
<xsl:copy-of select="$errorMessage"/>
</errorList>
</xsl:result-document>
<xsl:message terminate="yes" />
</xsl:template>

<!-- make insert statement -->
<xsl:template name="create_insert_sentence">
<xsl:param name="sql_model"/>
<xsl:for-each select="$sql_model/tables/table">
<xsl:variable name="name" select="row/name"/>
<xsl:variable name="column" select="row/column"/>
<xsl:variable name="type" select="row/type"/>
<xsl:variable name="row_data" select="row[data]"/>
<xsl:variable name="fixedSentnce">
<xsl:text/>INSERT INTO <xsl:value-of select="$name"/> (<xsl:text/>
<xsl:for-each select="$column">
<xsl:choose>
<xsl:when test="position() ne last()"><xsl:value-of select="."/>, </xsl:when>
<xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
</xsl:choose>
<xsl:if test="position() ne last() and position() mod 5 eq 0">
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text disable-output-escaping="yes">) 
VALUES (</xsl:text>
</xsl:variable>

<xsl:for-each select="$row_data">
<xsl:value-of select="$fixedSentnce"/>
<xsl:for-each select="data">
<xsl:variable name="data_index" select="position()"/>
<xsl:choose>
<xsl:when test="position() eq 1"><xsl:value-of select="sql-gen:to_sql_type($type[$data_index],.)"/>, </xsl:when>
<xsl:when test="position() eq last()"><xsl:value-of select="sql-gen:to_sql_type($type[$data_index],.)"/>
<xsl:text disable-output-escaping="yes">);

</xsl:text>
</xsl:when>
<xsl:when test="position() ne last()"><xsl:value-of select="sql-gen:to_sql_type($type[$data_index],.)"/>, </xsl:when>
</xsl:choose>
<xsl:if test="position() ne last() and position() mod 5 eq 0">
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:if>
</xsl:for-each>
</xsl:for-each>
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:for-each>
</xsl:template>

<!-- converted to a data model for creating the where clause of the delete statement and update statement -->
<xsl:template name="create_where_sql_model">
<xsl:param name="sql_model"/>
<tables>
<xsl:for-each select="$sql_model/tables/table[row/column/@primary = 'yes']">
<xsl:variable name="position" select="position()"/>
<table>
<xsl:attribute name="id" select="@id"/>
<xsl:variable name="name" select="row/name"/>
<xsl:variable name="column" select="row/column"/>
<xsl:variable name="type" select="row/type"/>
<xsl:variable name="row_data" select="row[data]"/>
<xsl:variable name="primary_index">
<indexs>
<xsl:for-each select="$column">
<xsl:if test="@primary">
<index><xsl:value-of select="position()"/></index>
</xsl:if>
</xsl:for-each>
</indexs>
</xsl:variable>
<row><name><xsl:copy-of select="$name"/></name></row>
<row>
<xsl:for-each select="$column[@primary = 'yes']">
<xsl:copy-of select="."/>
</xsl:for-each>
</row>
<row>
<xsl:for-each select="$type">
<xsl:variable name="data_index" select="position()"/>
<xsl:if test="$primary_index/indexs/index[. = $data_index]">
<xsl:copy-of select="."/>
</xsl:if>
</xsl:for-each>
</row>
<xsl:for-each select="$row_data">
<row>
<xsl:for-each select="data">
<xsl:variable name="data_index" select="position()"/>
<xsl:if test="$primary_index/indexs/index[. = $data_index]">
<xsl:copy-of select="."/>
</xsl:if>
</xsl:for-each>
</row>
</xsl:for-each>
</table>
</xsl:for-each>
</tables>
</xsl:template>

<!-- make update statement -->
<xsl:template name="create_update_sentence">
<xsl:param name="data_model"/>
<xsl:param name="where_model"/>
<xsl:for-each select="$where_model/tables/table">
<xsl:variable name="row_data" select="$data_model/tables/table[@id eq current()/@id]/row[data]"/>
<xsl:variable name="column" select="$data_model/tables/table[@id eq current()/@id]/row/column"/>
<xsl:variable name="type" select="$data_model/tables/table[@id eq current()/@id]/row/type"/>
<xsl:variable name="name" select="$where_model/tables/table[@id eq current()/@id]/row/name"/>
<xsl:variable name="row_data_where" select="$where_model/tables/table[@id eq current()/@id]/row[data]"/>
<xsl:variable name="column_where" select="$where_model/tables/table[@id eq current()/@id]/row/column"/>
<xsl:variable name="type_where" select="$where_model/tables/table[@id eq current()/@id]/row/type"/>
<!-- creating a set clause -->
<xsl:for-each select="$row_data">
<xsl:variable name="row_data_index" select="position()"/>
<xsl:text/>UPDATE <xsl:value-of select="$name"/> SET <xsl:text/>
<xsl:for-each select="data">
<xsl:variable name="data_index" select="position()"/>
<xsl:choose>
<xsl:when test="position() ne last()">
<xsl:value-of select="$column[$data_index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type[$data_index],.)"/><xsl:text>, </xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$column[$data_index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type[$data_index],.)"/>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="position() ne last() and position() mod 5 eq 0">
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:if>
</xsl:for-each>
<!-- creating a where clause -->
<xsl:for-each select="$row_data_where[$row_data_index]/data">
<xsl:variable name="index" select="position()"/>
<xsl:choose>
<xsl:when test="position() eq 1">
<xsl:text>
WHERE </xsl:text>
<xsl:value-of select="$column_where[$index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type_where[$index],.)"/>
</xsl:when>
<xsl:otherwise>
<xsl:text/> AND <xsl:value-of select="$column_where[$index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type_where[$index],.)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
<xsl:text disable-output-escaping="yes">;

</xsl:text>
</xsl:for-each>
</xsl:for-each>
</xsl:template>

<!-- make delete statement -->
<xsl:template name="create_delete_sentence">
<xsl:param name="where_model"/>
<xsl:for-each select="$where_model/tables/table">
<xsl:variable name="name" select="row/name"/>
<xsl:variable name="column" select="row/column"/>
<xsl:variable name="type" select="row/type"/>
<xsl:variable name="row_data" select="row[data]"/>
<xsl:for-each select="$row_data">
<xsl:text>DELETE FROM </xsl:text><xsl:value-of select="$name"/>
<xsl:for-each select="data">
<xsl:variable name="index" select="position()"/>
<xsl:choose>
<xsl:when test="position() eq 1">
<xsl:text> WHERE </xsl:text><xsl:value-of select="$column[$index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type[$index],.)"/>
</xsl:when>
<xsl:otherwise> AND <xsl:value-of select="$column[$index]"/> = <xsl:value-of select="sql-gen:to_sql_type($type[$index],.)"/></xsl:otherwise>
</xsl:choose>
</xsl:for-each>
<xsl:text disable-output-escaping="yes">;

</xsl:text>
</xsl:for-each>
<xsl:text disable-output-escaping="yes">
</xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>


図 7. common.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

<!-- Conversion to the data model separated TABLE units -->
<xsl:template name="separate_table_units">
<xsl:param name="data"/>
<tables>
<xsl:for-each select="$data/rows/row[name]">
<xsl:variable name="position" select="position()"/>
<table>
<xsl:attribute name="id" select="$position"/>
<xsl:copy-of select="."/>
<xsl:variable name="rowName" select="."/>
<xsl:for-each select="$data/rows/row[not(name)]">
<xsl:if test="preceding-sibling::row[name][1] is $rowName">
<xsl:copy-of select="."/>
</xsl:if>
</xsl:for-each>
</table>
</xsl:for-each>
</tables>
</xsl:template>

</xsl:stylesheet>


図 8. function.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql-gen="http://okule.wordpress.com" version="2.0">

<xsl:function name="sql-gen:getRelatedHeader">
<xsl:param name="header"/>
<xsl:param name="cell_adress"/>
<xsl:value-of select="$header[substring-before(@cell_address, ',') eq substring-before($cell_adress,',')]"/>
</xsl:function>

<xsl:function name="sql-gen:type_contain_size">
<xsl:param name="type"/>
<xsl:param name="size"/>
<xsl:choose>
<xsl:when test="$size"><xsl:value-of select="upper-case($type)"/>(<xsl:value-of select="$size"/>)</xsl:when>
<xsl:otherwise><xsl:value-of select="upper-case($type)"/></xsl:otherwise>
</xsl:choose>
</xsl:function>

<xsl:function name="sql-gen:num_to_alpah">
<!-- CELL AMJ -->
<xsl:param name="num"/>
<xsl:choose>
<xsl:when test="(($num)-1) idiv 26 eq 0"> </xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 1">A</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 2">B</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 3">C</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 4">D</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 5">E</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 6">F</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 7">G</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 8">H</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 9">I</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 10">J</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 11">K</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 12">L</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 13">M</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 14">N</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 15">O</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 16">P</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 17">Q</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 18">R</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 19">S</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 20">T</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 21">U</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 22">V</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 23">W</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 24">X</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 25">Y</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 26">Z</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 27">AA</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 28">AB</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 29">AC</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 30">AD</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 31">AE</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 32">AF</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 33">AG</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 34">AH</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 35">AI</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 36">AJ</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 37">AK</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 38">AL</xsl:when>
<xsl:when test="(($num)-1) idiv 26 eq 39">AM</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$num mod 26 eq 1">A</xsl:when>
<xsl:when test="$num mod 26 eq 2">B</xsl:when>
<xsl:when test="$num mod 26 eq 3">C</xsl:when>
<xsl:when test="$num mod 26 eq 4">D</xsl:when>
<xsl:when test="$num mod 26 eq 5">E</xsl:when>
<xsl:when test="$num mod 26 eq 6">F</xsl:when>
<xsl:when test="$num mod 26 eq 7">G</xsl:when>
<xsl:when test="$num mod 26 eq 8">H</xsl:when>
<xsl:when test="$num mod 26 eq 9">I</xsl:when>
<xsl:when test="$num mod 26 eq 10">J</xsl:when>
<xsl:when test="$num mod 26 eq 11">K</xsl:when>
<xsl:when test="$num mod 26 eq 12">L</xsl:when>
<xsl:when test="$num mod 26 eq 13">M</xsl:when>
<xsl:when test="$num mod 26 eq 14">N</xsl:when>
<xsl:when test="$num mod 26 eq 15">O</xsl:when>
<xsl:when test="$num mod 26 eq 16">P</xsl:when>
<xsl:when test="$num mod 26 eq 17">Q</xsl:when>
<xsl:when test="$num mod 26 eq 18">R</xsl:when>
<xsl:when test="$num mod 26 eq 19">S</xsl:when>
<xsl:when test="$num mod 26 eq 20">T</xsl:when>
<xsl:when test="$num mod 26 eq 21">U</xsl:when>
<xsl:when test="$num mod 26 eq 22">V</xsl:when>
<xsl:when test="$num mod 26 eq 23">W</xsl:when>
<xsl:when test="$num mod 26 eq 24">X</xsl:when>
<xsl:when test="$num mod 26 eq 25">Y</xsl:when>
<xsl:when test="$num mod 26 eq 0">Z</xsl:when>
</xsl:choose>
</xsl:function>

<xsl:function name="sql-gen:unicodeSize">
<xsl:param name="data"/>
<xsl:variable name="result">
<reuslt>
<xsl:variable name="dataLength" select="string-length($data)"/>
<xsl:for-each select="1 to $dataLength">
<xsl:variable name="subStr" select="substring($data, ., 1)"/>
<xsl:choose>
<!-- The database encoding is UTF-8 -->
<xsl:when test="'	' le $subStr and $subStr le ''"><data>1</data></xsl:when>
<xsl:when test="'€' le $subStr and $subStr le '߿'"><data>2</data></xsl:when>
<xsl:when test="'ࠀ' le $subStr and $subStr le '�'"><data>3</data></xsl:when>
<xsl:when test="'𐀀' le $subStr and $subStr le '󯿽'"><data>4</data></xsl:when>
<!-- The database encoding is either of EUC-JP and Shift-JIS -->
<!--
<xsl:when test="'	' le $subStr and $subStr le ''"><data>1</data></xsl:when>
<xsl:when test="'ࠀ' le $subStr"><data>2</data></xsl:when>
-->
</xsl:choose>
</xsl:for-each>
</reuslt>
</xsl:variable>
<xsl:variable name="reusltSum" select="sum($result/reuslt/data)"/>
<xsl:copy-of select="$reusltSum"/>
</xsl:function>

<xsl:function name="sql-gen:to_sql_type">
<xsl:param name="type"/>
<xsl:param name="data"/>
<xsl:variable name="lowerCaseType" select="lower-case($type)"/>
<xsl:choose>
<xsl:when test="$lowerCaseType eq 'date' and (lower-case($data) eq 'sysdate' or lower-case($data) eq 'current_date')">
<xsl:value-of select="$data"/>
</xsl:when>
<xsl:when test="$lowerCaseType eq 'timestamp' and (lower-case($data) eq 'sysdate' or lower-case($data) eq 'current_date')">
<xsl:value-of select="$data"/>
</xsl:when>
<xsl:when test="contains($lowerCaseType,'char') or contains($lowerCaseType,'varchar') or
contains($lowerCaseType,'date') or contains($lowerCaseType,'timestamp')">
<xsl:text>'</xsl:text><xsl:value-of select="$data"/><xsl:text>'</xsl:text>
</xsl:when>
<xsl:otherwise><xsl:value-of select="$data"/></xsl:otherwise>
</xsl:choose>
</xsl:function>

<xsl:function name="sql-gen:power">
<xsl:param name="base"/>
<xsl:param name="exponent"/>
<xsl:value-of select="sql-gen:power($base, $exponent, 1, $exponent)"/>
</xsl:function>

<xsl:function name="sql-gen:power">
<xsl:param name="base"/>
<xsl:param name="exponent"/>
<xsl:param name="result"/>
<xsl:param name="count"/>
<xsl:choose>
<xsl:when test="number($count) gt 0"><xsl:value-of select="sql-gen:power($base, $exponent, $base*$result, ($count)-1)"/></xsl:when>
<xsl:otherwise><xsl:value-of select="$result"/></xsl:otherwise>
</xsl:choose>
</xsl:function>

</xsl:stylesheet>


図 9. content.xml ODSファイルの一部です。

<?xml version="1.0" encoding="UTF-8"?>
<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"
xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:presentation="urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"
xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"
xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0"
xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events"
xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rpt="http://openoffice.org/2005/report"
xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:grddl="http://www.w3.org/2003/g/data-view#"
xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0" office:version="1.2" grddl:transformation="http://docs.oasis-open.org/office/1.2/xslt/odf2rdf.xsl">
<office:scripts />
<office:font-face-decls>
<style:font-face style:name="Consolas" svg:font-family="Consolas" />
<style:font-face style:name="Arial" svg:font-family="Arial" style:font-family-generic="swiss" style:font-pitch="variable" />
<style:font-face style:name="Lucida Sans Unicode" svg:font-family="&apos;Lucida Sans Unicode&apos;" style:font-family-generic="system"
style:font-pitch="variable" />
<style:font-face style:name="Mangal" svg:font-family="Mangal" style:font-family-generic="system" style:font-pitch="variable" />
<style:font-face style:name="Tahoma" svg:font-family="Tahoma" style:font-family-generic="system" style:font-pitch="variable" />
<style:font-face style:name="MS Pゴシック" svg:font-family="&apos;MS Pゴシック&apos;" style:font-family-generic="system" style:font-pitch="variable" />
</office:font-face-decls>
<office:automatic-styles>
<style:style style:name="co1" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.8925in" />
</style:style>
<style:style style:name="co2" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.4256in" />
</style:style>
<style:style style:name="co3" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.1382in" />
</style:style>
<style:style style:name="co4" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.8646in" />
</style:style>
<style:style style:name="co5" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.061in" />
</style:style>
<style:style style:name="co6" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.7665in" />
</style:style>
<style:style style:name="co7" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.8319in" />
</style:style>
<style:style style:name="co8" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.9409in" />
</style:style>
<style:style style:name="co9" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.3654in" />
</style:style>
<style:style style:name="co10" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.7772in" />
</style:style>
<style:style style:name="co11" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.6189in" />
</style:style>
<style:style style:name="co12" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="0.9744in" />
</style:style>
<style:style style:name="co13" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.2339in" />
</style:style>
<style:style style:name="co14" style:family="table-column">
<style:table-column-properties fo:break-before="auto" style:column-width="1.4091in" />
</style:style>
<style:style style:name="ro1" style:family="table-row">
<style:table-row-properties style:row-height="0.1783in" fo:break-before="auto" style:use-optimal-row-height="true" />
</style:style>
<style:style style:name="ta1" style:family="table" style:master-page-name="Default">
<style:table-properties table:display="true" style:writing-mode="lr-tb" />
</style:style>
<number:date-style style:name="N84">
<number:year number:style="long" />
<number:text>-</number:text>
<number:month number:style="long" />
<number:text>-</number:text>
<number:day number:style="long" />
</number:date-style>
<number:boolean-style style:name="N99">
<number:boolean />
</number:boolean-style>
<number:text-style style:name="N100">
<number:text-content />
</number:text-style>
<style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
<style:text-properties fo:color="#0047ff" />
</style:style>
<style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:background-color="#0099ff" fo:border="0.0008in solid #000000" />
<style:text-properties style:use-window-font-color="true" style:font-name="Arial" />
</style:style>
<style:style style:name="ce3" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:border="0.0008in solid #000000" />
</style:style>
<style:style style:name="ce4" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties style:text-align-source="fix" style:repeat-content="false" fo:background-color="transparent"
fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="start" fo:margin-left="0in" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce5" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties style:text-align-source="fix" style:repeat-content="false" fo:background-color="transparent"
fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="start" fo:margin-left="0in" />
<style:text-properties style:use-window-font-color="true" style:font-name="Arial" style:font-name-asian="MS Pゴシック"
style:font-name-complex="Mangal" />
</style:style>
<style:style style:name="ce6" style:family="table-cell" style:parent-style-name="Default">
<style:text-properties style:use-window-font-color="true" style:text-outline="false" style:text-line-through-style="none"
style:font-name="Consolas" fo:font-size="10pt" fo:language="en" fo:country="US" fo:font-style="normal" fo:text-shadow="none" style:text-underline-style="none"
fo:font-weight="normal" style:text-underline-mode="continuous" style:text-overline-mode="continuous" style:text-line-through-mode="continuous"
style:font-size-asian="10pt" style:language-asian="en" style:country-asian="US" style:font-style-asian="normal" style:font-weight-asian="normal"
style:font-size-complex="5.59999990463257pt" style:language-complex="en" style:country-complex="US" style:font-style-complex="normal"
style:font-weight-complex="normal" style:text-emphasize="none" style:font-relief="none" style:text-overline-style="none" style:text-overline-color="font-color" />
</style:style>
<style:style style:name="ce7" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties style:text-align-source="fix" style:repeat-content="false" fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="start" fo:margin-left="0in" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce8" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N100">
<style:table-cell-properties style:text-align-source="fix" style:repeat-content="false" fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="start" fo:margin-left="0in" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce9" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:background-color="#0099ff" fo:border="0.0008in solid #000000" />
<style:text-properties style:use-window-font-color="true" style:text-outline="false" style:text-line-through-style="none"
style:font-name="Arial" fo:font-size="10pt" fo:language="en" fo:country="US" fo:font-style="normal" fo:text-shadow="none" style:text-underline-style="none"
fo:font-weight="normal" style:text-underline-mode="continuous" style:text-overline-mode="continuous" style:text-line-through-mode="continuous"
style:font-size-asian="10pt" style:language-asian="en" style:country-asian="US" style:font-style-asian="normal" style:font-weight-asian="normal"
style:font-size-complex="5.65000009536743pt" style:language-complex="en" style:country-complex="US" style:font-style-complex="normal"
style:font-weight-complex="normal" style:text-emphasize="none" style:font-relief="none" style:text-overline-style="none" style:text-overline-color="font-color" />
</style:style>
<style:style style:name="ce10" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N99">
<style:table-cell-properties fo:border="0.0008in solid #000000" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce11" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:border="0.0008in solid #000000" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce12" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:border="0.0008in solid #000000" />
<style:text-properties style:use-window-font-color="true" style:text-outline="false" style:text-line-through-style="none"
style:font-name="Consolas" fo:font-size="10pt" fo:language="en" fo:country="US" fo:font-style="normal" fo:text-shadow="none" style:text-underline-style="none"
fo:font-weight="normal" style:text-underline-mode="continuous" style:text-overline-mode="continuous" style:text-line-through-mode="continuous"
style:font-size-asian="10pt" style:language-asian="en" style:country-asian="US" style:font-style-asian="normal" style:font-weight-asian="normal"
style:font-size-complex="5.65000009536743pt" style:language-complex="en" style:country-complex="US" style:font-style-complex="normal"
style:font-weight-complex="normal" style:text-emphasize="none" style:font-relief="none" style:text-overline-style="none" style:text-overline-color="font-color" />
</style:style>
<style:style style:name="ce13" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:background-color="#0099ff" style:text-align-source="fix" style:repeat-content="false"
fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="center" fo:margin-left="0in" />
<style:text-properties fo:color="#00ff00" />
</style:style>
<style:style style:name="ce14" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:background-color="#e6e6ff" style:text-align-source="fix" style:repeat-content="false"
fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="center" fo:margin-left="0in" />
<style:text-properties style:use-window-font-color="true" />
</style:style>
<style:style style:name="ce15" style:family="table-cell" style:parent-style-name="Default">
<style:table-cell-properties fo:background-color="#0099ff" style:text-align-source="fix" style:repeat-content="false"
fo:border="0.0008in solid #000000" />
<style:paragraph-properties fo:text-align="center" fo:margin-left="0in" />
</style:style>
<style:style style:name="ce16" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N84">
<style:table-cell-properties fo:border="0.0008in solid #000000" />
</style:style>
<style:style style:name="ta_extref" style:family="table">
<style:table-properties table:display="false" />
</style:style>
</office:automatic-styles>
<office:body>
<office:spreadsheet>
<table:table table:name="ddl" table:style-name="ta1" table:print="false">
<table:table-column table:style-name="co1" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co2" table:default-cell-style-name="ce3" />
<table:table-column table:style-name="co3" table:default-cell-style-name="ce4" />
<table:table-column table:style-name="co4" table:default-cell-style-name="ce4" />
<table:table-column table:style-name="co5" table:default-cell-style-name="ce4" />
<table:table-column table:style-name="co6" table:default-cell-style-name="ce7" />
<table:table-column table:style-name="co7" table:default-cell-style-name="ce8" />
<table:table-column table:style-name="co8" table:default-cell-style-name="ce10" />
<table:table-column table:style-name="co9" table:default-cell-style-name="ce12" />
<table:table-column table:style-name="co1" table:default-cell-style-name="ce12" />
<table:table-column table:style-name="co1" table:default-cell-style-name="ce3" />
<table:table-row table:style-name="ro1" table:number-rows-repeated="2">
<table:table-cell />
<table:table-cell table:style-name="Default" table:number-columns-repeated="10" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:style-name="ce1" office:value-type="string" table:number-columns-spanned="2" table:number-rows-spanned="1">
<text:p>CUSTOMER</text:p>
</table:table-cell>
<table:covered-table-cell table:style-name="Default" />
<table:table-cell table:style-name="Default" table:number-columns-repeated="8" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>NO</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>COLUMN</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>TYPE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>SIZE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>PK</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce2" office:value-type="string">
<text:p>INDEX</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce9" office:value-type="string">
<text:p>NOT_NULL</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce9" office:value-type="string">
<text:p>INDEXNAME</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce9" office:value-type="string">
<text:p>DEFAULT</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce9" office:value-type="string">
<text:p>REMARKS</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell office:value-type="float" office:value="1">
<text:p>1</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>ID</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>char</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="5">
<text:p>5</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="1">
<text:p>1</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B5]+1" office:value-type="float" office:value="2">
<text:p>2</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>ID2</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>char</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="7">
<text:p>7</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="2">
<text:p>2</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B6]+1" office:value-type="float" office:value="3">
<text:p>3</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>NAME</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>varchar</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="20">
<text:p>20</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2" />
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce11" table:number-columns-repeated="2" />
<table:table-cell />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B7]+1" office:value-type="float" office:value="4">
<text:p>4</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>TEL</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>char</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="14">
<text:p>14</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>2</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>IDX_CUS_TEL</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B8]+1" office:value-type="float" office:value="5">
<text:p>5</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>FAX</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>char</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="14">
<text:p>14</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>3</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>IDX_CUS_FAX</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B9]+1" office:value-type="float" office:value="6">
<text:p>6</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>DEPT_NO</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce6" office:value-type="string">
<text:p>decimal</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="5">
<text:p>5</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>1-1</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>IDX_CUS_DPT</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B10]+1" office:value-type="float" office:value="7">
<text:p>7</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>DEPT_NAME</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>varchar</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="10">
<text:p>10</text:p>
</table:table-cell>
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>1-2</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce11" table:number-columns-repeated="2" />
<table:table-cell />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B11]+1" office:value-type="float" office:value="8">
<text:p>8</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce5" office:value-type="string">
<text:p>COUNTRY</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>varchar</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="3">
<text:p>3</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2" />
<table:table-cell table:style-name="ce11" table:number-columns-repeated="2" />
<table:table-cell table:style-name="ce11" office:value-type="string">
<text:p>&apos;N/A&apos;</text:p>
</table:table-cell>
<table:table-cell />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B12]+1" office:value-type="float" office:value="9">
<text:p>9</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>CREATE_DATE</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>timestamp</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3" />
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce11" />
<table:table-cell table:style-name="ce11" office:value-type="string">
<text:p>SYSDATE</text:p>
</table:table-cell>
<table:table-cell />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:formula="of:=[.B13]+1" office:value-type="float" office:value="10">
<text:p>10</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce5" office:value-type="string">
<text:p>UPDATE_DATE</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>timestamp</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3" />
<table:table-cell office:value-type="string">
<text:p>yes</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce11" />
<table:table-cell table:style-name="ce11" office:value-type="string">
<text:p>SYSDATE</text:p>
</table:table-cell>
<table:table-cell />
</table:table-row>
</table:table>
<table:table table:name="sql" table:style-name="ta1" table:print="false">
<table:table-column table:style-name="co1" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co10" table:default-cell-style-name="ce3" />
<table:table-column table:style-name="co11" table:default-cell-style-name="ce3" />
<table:table-column table:style-name="co12" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co13" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co3" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co14" table:number-columns-repeated="2" table:default-cell-style-name="Default" />
<table:table-column table:style-name="co3" table:number-columns-repeated="3" table:default-cell-style-name="Default" />
<table:table-row table:style-name="ro1" table:number-rows-repeated="2">
<table:table-cell />
<table:table-cell table:style-name="Default" table:number-columns-repeated="2" />
<table:table-cell table:number-columns-repeated="8" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:style-name="ce1" office:value-type="string" table:number-columns-spanned="2" table:number-rows-spanned="1">
<text:p>CUSTOMER</text:p>
</table:table-cell>
<table:covered-table-cell table:style-name="Default" />
<table:table-cell table:number-columns-repeated="8" />
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:style-name="ce13" office:value-type="string">
<text:p>ID</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce13" office:value-type="string">
<text:p>ID2</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>NAME</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>TEL</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>FAX</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>DEPT_NO</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>DEPT_NAME</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>COUNTRY</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>CREATE_DATE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" office:value-type="string">
<text:p>UPDATE_DATE</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>char(5)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>char(7)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>varchar(20)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>char(14)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>char(14)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>decimal(5)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>varchar(10)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>varchar(3)</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>timestamp</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce14" office:value-type="string">
<text:p>timestamp</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>00001</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>0000001</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>TOM</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9987</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9997</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="float" office:value="11111">
<text:p>11111</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>DEP1</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>US</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" office:value-type="date" office:date-value="2012-05-09">
<text:p>2012-05-09</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" table:formula="of:=[.J6]" office:value-type="date" office:date-value="2012-05-09">
<text:p>2012-05-09</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>00001</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>0000002</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>JIM</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9988</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9998</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="float" office:value="11112">
<text:p>11112</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>DEP2</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>US</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" table:formula="of:=[.J6]+3" office:value-type="date" office:date-value="2012-05-12">
<text:p>2012-05-12</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" table:formula="of:=[.J7]" office:value-type="date" office:date-value="2012-05-12">
<text:p>2012-05-12</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
<table:table-cell />
<table:table-cell office:value-type="string">
<text:p>00001</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>0000003</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>MARIKO</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9989</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>9-999-999-9999</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="float" office:value="11113">
<text:p>11113</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>DEP3</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce3" office:value-type="string">
<text:p>JP</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" table:formula="of:=[.J7]+3" office:value-type="date" office:date-value="2012-05-15">
<text:p>2012-05-15</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce16" table:formula="of:=[.J8]" office:value-type="date" office:date-value="2012-05-15">
<text:p>2012-05-15</text:p>
</table:table-cell>
</table:table-row>
</table:table>
</office:spreadsheet>
</office:body>
</office:document-content>

図 10. 第一モデル(content.xmlから必要なデータのみ抽出)

<?xml version="1.0" encoding="UTF-8"?>
<rows>
<row>
<name cell_address="B,3">CUSTOMER</name>
</row>
<row>
<header cell_address="B,4">NO</header>
<header cell_address="C,4">COLUMN</header>
<header cell_address="D,4">TYPE</header>
<header cell_address="E,4">SIZE</header>
<header cell_address="F,4">PK</header>
<header cell_address="G,4">INDEX</header>
<header cell_address="H,4">NOT_NULL</header>
<header cell_address="I,4">INDEXNAME</header>
<header cell_address="J,4">DEFAULT</header>
<header cell_address="K,4">REMARKS</header>
</row>
<row>
<field cell_address="B,5">1</field>
<field cell_address="C,5">ID</field>
<field cell_address="D,5">char</field>
<field cell_address="E,5">5</field>
<field cell_address="F,5">1</field>
<field cell_address="H,5">yes</field>
</row>
<row>
<field cell_address="B,6">2</field>
<field cell_address="C,6">ID2</field>
<field cell_address="D,6">char</field>
<field cell_address="E,6">7</field>
<field cell_address="F,6">2</field>
<field cell_address="H,6">yes</field>
</row>
<row>
<field cell_address="B,7">3</field>
<field cell_address="C,7">NAME</field>
<field cell_address="D,7">varchar</field>
<field cell_address="E,7">20</field>
<field cell_address="H,7">yes</field>
</row>
<row>
<field cell_address="B,8">4</field>
<field cell_address="C,8">TEL</field>
<field cell_address="D,8">char</field>
<field cell_address="E,8">14</field>
<field cell_address="G,8">2</field>
<field cell_address="H,8">yes</field>
<field cell_address="I,8">IDX_CUS_TEL</field>
</row>
<row>
<field cell_address="B,9">5</field>
<field cell_address="C,9">FAX</field>
<field cell_address="D,9">char</field>
<field cell_address="E,9">14</field>
<field cell_address="G,9">3</field>
<field cell_address="H,9">yes</field>
<field cell_address="I,9">IDX_CUS_FAX</field>
</row>
<row>
<field cell_address="B,10">6</field>
<field cell_address="C,10">DEPT_NO</field>
<field cell_address="D,10">decimal</field>
<field cell_address="E,10">5</field>
<field cell_address="G,10">1-1</field>
<field cell_address="H,10">yes</field>
<field cell_address="I,10">IDX_CUS_DPT</field>
</row>
<row>
<field cell_address="B,11">7</field>
<field cell_address="C,11">DEPT_NAME</field>
<field cell_address="D,11">varchar</field>
<field cell_address="E,11">10</field>
<field cell_address="G,11">1-2</field>
<field cell_address="H,11">yes</field>
</row>
<row>
<field cell_address="B,12">8</field>
<field cell_address="C,12">COUNTRY</field>
<field cell_address="D,12">varchar</field>
<field cell_address="E,12">3</field>
<field cell_address="J,12">'N/A'</field>
</row>
<row>
<field cell_address="B,13">9</field>
<field cell_address="C,13">CREATE_DATE</field>
<field cell_address="D,13">timestamp</field>
<field cell_address="H,13">yes</field>
<field cell_address="J,13">SYSDATE</field>
</row>
<row>
<field cell_address="B,14">10</field>
<field cell_address="C,14">UPDATE_DATE</field>
<field cell_address="D,14">timestamp</field>
<field cell_address="H,14">yes</field>
<field cell_address="J,14">SYSDATE</field>
</row>
</rows>

図 11. 第二モデル(第一モデルをテーブル単位にグルーピング)

<tables>
<table id="1">
<row>
<name cell_address="B,3">CUSTOMER</name>
</row>
<row>
<header cell_address="B,4">NO</header>
<header cell_address="C,4">COLUMN</header>
<header cell_address="D,4">TYPE</header>
<header cell_address="E,4">SIZE</header>
<header cell_address="F,4">PK</header>
<header cell_address="G,4">INDEX</header>
<header cell_address="H,4">NOT_NULL</header>
<header cell_address="I,4">INDEXNAME</header>
<header cell_address="J,4">DEFAULT</header>
<header cell_address="K,4">REMARKS</header>
</row>
<row>
<field cell_address="B,5">1</field>
<field cell_address="C,5">ID</field>
<field cell_address="D,5">char</field>
<field cell_address="E,5">5</field>
<field cell_address="F,5">1</field>
<field cell_address="H,5">yes</field>
</row>
<row>
<field cell_address="B,6">2</field>
<field cell_address="C,6">ID2</field>
<field cell_address="D,6">char</field>
<field cell_address="E,6">7</field>
<field cell_address="F,6">2</field>
<field cell_address="H,6">yes</field>
</row>
<row>
<field cell_address="B,7">3</field>
<field cell_address="C,7">NAME</field>
<field cell_address="D,7">varchar</field>
<field cell_address="E,7">20</field>
<field cell_address="H,7">yes</field>
</row>
<row>
<field cell_address="B,8">4</field>
<field cell_address="C,8">TEL</field>
<field cell_address="D,8">char</field>
<field cell_address="E,8">14</field>
<field cell_address="G,8">2</field>
<field cell_address="H,8">yes</field>
<field cell_address="I,8">IDX_CUS_TEL</field>
</row>
<row>
<field cell_address="B,9">5</field>
<field cell_address="C,9">FAX</field>
<field cell_address="D,9">char</field>
<field cell_address="E,9">14</field>
<field cell_address="G,9">3</field>
<field cell_address="H,9">yes</field>
<field cell_address="I,9">IDX_CUS_FAX</field>
</row>
<row>
<field cell_address="B,10">6</field>
<field cell_address="C,10">DEPT_NO</field>
<field cell_address="D,10">decimal</field>
<field cell_address="E,10">5</field>
<field cell_address="G,10">1-1</field>
<field cell_address="H,10">yes</field>
<field cell_address="I,10">IDX_CUS_DPT</field>
</row>
<row>
<field cell_address="B,11">7</field>
<field cell_address="C,11">DEPT_NAME</field>
<field cell_address="D,11">varchar</field>
<field cell_address="E,11">10</field>
<field cell_address="G,11">1-2</field>
<field cell_address="H,11">yes</field>
</row>
<row>
<field cell_address="B,12">8</field>
<field cell_address="C,12">COUNTRY</field>
<field cell_address="D,12">varchar</field>
<field cell_address="E,12">3</field>
<field cell_address="J,12">'N/A'</field>
</row>
<row>
<field cell_address="B,13">9</field>
<field cell_address="C,13">CREATE_DATE</field>
<field cell_address="D,13">timestamp</field>
<field cell_address="H,13">yes</field>
<field cell_address="J,13">SYSDATE</field>
</row>
<row>
<field cell_address="B,14">10</field>
<field cell_address="C,14">UPDATE_DATE</field>
<field cell_address="D,14">timestamp</field>
<field cell_address="H,14">yes</field>
<field cell_address="J,14">SYSDATE</field>
</row>
</table>
</tables>

図 12. 第三モデル(カラム名でグルーピング)

<tables>
<table id="1" name="CUSTOMER">
<field name="ID" not_null="yes" pk="1" size="5" type="char" />
<field name="ID2" not_null="yes" pk="2" size="7" type="char" />
<field name="NAME" not_null="yes" size="20" type="varchar" />
<field name="TEL" index="2" indexname="IDX_CUS_TEL" not_null="yes" size="14" type="char" />
<field name="FAX" index="3" indexname="IDX_CUS_FAX" not_null="yes" size="14" type="char" />
<field name="DEPT_NO" index="1-1" indexname="IDX_CUS_DPT" not_null="yes" size="5" type="decimal" />
<field name="DEPT_NAME" index="1-2" not_null="yes" size="10" type="varchar" />
<field name="COUNTRY" default="'N/A'" size="3" type="varchar" />
<field name="CREATE_DATE" default="SYSDATE" not_null="yes" type="timestamp" />
<field name="UPDATE_DATE" default="SYSDATE" not_null="yes" type="timestamp" />
</table>
</tables>

13. 第四モデル(列名、プライマリキー及びインデックス要素の作成)

<tables>
<table name="CUSTOMER">
<column not_null="yes" name="ID" type="CHAR(5)" />
<column not_null="yes" name="ID2" type="CHAR(7)" />
<column not_null="yes" name="NAME" type="VARCHAR(20)" />
<column not_null="yes" name="TEL" type="CHAR(14)" />
<column not_null="yes" name="FAX" type="CHAR(14)" />
<column not_null="yes" name="DEPT_NO" type="DECIMAL(5)" />
<column not_null="yes" name="DEPT_NAME" type="VARCHAR(10)" />
<column name="COUNTRY" type="VARCHAR(3)" default="'N/A'" />
<column not_null="yes" name="CREATE_DATE" type="timestamp" default="SYSDATE" />
<column not_null="yes" name="UPDATE_DATE" type="timestamp" default="SYSDATE" />
<pk pk="1">ID</pk>
<pk pk="2">ID2</pk>
<index index1="1" index2="1" indexname="IDX_CUS_DPT">DEPT_NO</index>
<index index1="1" index2="2">DEPT_NAME</index>
<index index1="2" indexname="IDX_CUS_TEL">TEL</index>
<index index1="3" indexname="IDX_CUS_FAX">FAX</index>
</table>
</tables>

図 14. テーブル定義、プライマリキー制約、インデックス作成のddl

CREATE TABLE CUSTOMER (
ID CHAR(5) NOT NULL,
ID2 CHAR(7) NOT NULL,
NAME VARCHAR(20) NOT NULL,
TEL CHAR(14) NOT NULL,
FAX CHAR(14) NOT NULL,
DEPT_NO DECIMAL(5) NOT NULL,
DEPT_NAME VARCHAR(10) NOT NULL,
COUNTRY VARCHAR(3) DEFAULT 'N/A',
CREATE_DATE TIMESTAMP NOT NULL DEFAULT SYSDATE,
UPDATE_DATE TIMESTAMP NOT NULL DEFAULT SYSDATE
);

ALTER TABLE CUSTOMER ADD CONSTRAINT PK_CUSTOMER
PRIMARY KEY(ID, ID2);

CREATE INDEX IDX_CUS_DPT
ON CUSTOMER (DEPT_NO, DEPT_NAME);

CREATE INDEX IDX_CUS_TEL
ON CUSTOMER (TEL);

CREATE INDEX IDX_CUS_FAX
ON CUSTOMER (FAX);

図 15. 第一モデル(content.xmlから必要なデータのみ抽出)

<rows>
<row>
<name cell_address="B,3">CUSTOMER</name>
</row>
<row>
<column primary="yes" cell_address="B,4">ID</column>
<column primary="yes" cell_address="C,4">ID2</column>
<column cell_address="D,4">NAME</column>
<column cell_address="E,4">TEL</column>
<column cell_address="F,4">FAX</column>
<column cell_address="G,4">DEPT_NO</column>
<column cell_address="H,4">DEPT_NAME</column>
<column cell_address="I,4">COUNTRY</column>
<column cell_address="J,4">CREATE_DATE</column>
<column cell_address="K,4">UPDATE_DATE</column>
</row>
<row>
<type cell_address="B,5">char(5)</type>
<type cell_address="C,5">char(7)</type>
<type cell_address="D,5">varchar(20)</type>
<type cell_address="E,5">char(14)</type>
<type cell_address="F,5">char(14)</type>
<type cell_address="G,5">decimal(5)</type>
<type cell_address="H,5">varchar(10)</type>
<type cell_address="I,5">varchar(3)</type>
<type cell_address="J,5">timestamp</type>
<type cell_address="K,5">timestamp</type>
</row>
<row>
<data cell_address="B,6">00001</data>
<data cell_address="C,6">0000001</data>
<data cell_address="D,6">TOM</data>
<data cell_address="E,6">9-999-999-9987</data>
<data cell_address="F,6">9-999-999-9997</data>
<data cell_address="G,6">11111</data>
<data cell_address="H,6">DEP1</data>
<data cell_address="I,6">US</data>
<data cell_address="J,6">2012-05-09</data>
<data cell_address="K,6">2012-05-09</data>
</row>
<row>
<data cell_address="B,7">00001</data>
<data cell_address="C,7">0000002</data>
<data cell_address="D,7">JIM</data>
<data cell_address="E,7">9-999-999-9988</data>
<data cell_address="F,7">9-999-999-9998</data>
<data cell_address="G,7">11112</data>
<data cell_address="H,7">DEP2</data>
<data cell_address="I,7">US</data>
<data cell_address="J,7">2012-05-12</data>
<data cell_address="K,7">2012-05-12</data>
</row>
<row>
<data cell_address="B,8">00001</data>
<data cell_address="C,8">0000003</data>
<data cell_address="D,8">MARIKO</data>
<data cell_address="E,8">9-999-999-9989</data>
<data cell_address="F,8">9-999-999-9999</data>
<data cell_address="G,8">11113</data>
<data cell_address="H,8">DEP3</data>
<data cell_address="I,8">JP</data>
<data cell_address="J,8">2012-05-15</data>
<data cell_address="K,8">2012-05-15</data>
</row>
</rows>

図 16. 第二モデル(第一モデルをテーブル単位にグルーピング)

<tables>
<table id="1">
<row>
<name cell_address="B,3">CUSTOMER</name>
</row>
<row>
<column primary="yes" cell_address="B,4">ID</column>
<column primary="yes" cell_address="C,4">ID2</column>
<column cell_address="D,4">NAME</column>
<column cell_address="E,4">TEL</column>
<column cell_address="F,4">FAX</column>
<column cell_address="G,4">DEPT_NO</column>
<column cell_address="H,4">DEPT_NAME</column>
<column cell_address="I,4">COUNTRY</column>
<column cell_address="J,4">CREATE_DATE</column>
<column cell_address="K,4">UPDATE_DATE</column>
</row>
<row>
<type cell_address="B,5">char(5)</type>
<type cell_address="C,5">char(7)</type>
<type cell_address="D,5">varchar(20)</type>
<type cell_address="E,5">char(14)</type>
<type cell_address="F,5">char(14)</type>
<type cell_address="G,5">decimal(5)</type>
<type cell_address="H,5">varchar(10)</type>
<type cell_address="I,5">varchar(3)</type>
<type cell_address="J,5">timestamp</type>
<type cell_address="K,5">timestamp</type>
</row>
<row>
<data cell_address="B,6">00001</data>
<data cell_address="C,6">0000001</data>
<data cell_address="D,6">TOM</data>
<data cell_address="E,6">9-999-999-9987</data>
<data cell_address="F,6">9-999-999-9997</data>
<data cell_address="G,6">11111</data>
<data cell_address="H,6">DEP1</data>
<data cell_address="I,6">US</data>
<data cell_address="J,6">2012-05-09</data>
<data cell_address="K,6">2012-05-09</data>
</row>
<row>
<data cell_address="B,7">00001</data>
<data cell_address="C,7">0000002</data>
<data cell_address="D,7">JIM</data>
<data cell_address="E,7">9-999-999-9988</data>
<data cell_address="F,7">9-999-999-9998</data>
<data cell_address="G,7">11112</data>
<data cell_address="H,7">DEP2</data>
<data cell_address="I,7">US</data>
<data cell_address="J,7">2012-05-12</data>
<data cell_address="K,7">2012-05-12</data>
</row>
<row>
<data cell_address="B,8">00001</data>
<data cell_address="C,8">0000003</data>
<data cell_address="D,8">MARIKO</data>
<data cell_address="E,8">9-999-999-9989</data>
<data cell_address="F,8">9-999-999-9999</data>
<data cell_address="G,8">11113</data>
<data cell_address="H,8">DEP3</data>
<data cell_address="I,8">JP</data>
<data cell_address="J,8">2012-05-15</data>
<data cell_address="K,8">2012-05-15</data>
</row>
</table>
</tables>

図 17. insert文

INSERT INTO CUSTOMER (ID, ID2, NAME, TEL, FAX,
DEPT_NO, DEPT_NAME, COUNTRY, CREATE_DATE, UPDATE_DATE)
VALUES ('00001', '0000001', 'TOM', '9-999-999-9987', '9-999-999-9997',
11111, 'DEP1', 'US', '2012-05-09', '2012-05-09');

INSERT INTO CUSTOMER (ID, ID2, NAME, TEL, FAX,
DEPT_NO, DEPT_NAME, COUNTRY, CREATE_DATE, UPDATE_DATE)
VALUES ('00001', '0000002', 'JIM', '9-999-999-9988', '9-999-999-9998',
11112, 'DEP2', 'US', '2012-05-12', '2012-05-12');

INSERT INTO CUSTOMER (ID, ID2, NAME, TEL, FAX,
DEPT_NO, DEPT_NAME, COUNTRY, CREATE_DATE, UPDATE_DATE)
VALUES ('00001', '0000003', 'MARIKO', '9-999-999-9989', '9-999-999-9999',
11113, 'DEP3', 'JP', '2012-05-15', '2012-05-15');

図 18. 第三モデル(delete文とupdate文のWHERE句のためのモデル)

<tables>
<table id="1">
<row>
<name>
<name cell_address="B,3">CUSTOMER</name>
</name>
</row>
<row>
<column primary="yes" cell_address="B,4">ID</column>
<column primary="yes" cell_address="C,4">ID2</column>
</row>
<row>
<type cell_address="B,5">char(5)</type>
<type cell_address="C,5">char(7)</type>
</row>
<row>
<data cell_address="B,6">00001</data>
<data cell_address="C,6">0000001</data>
</row>
<row>
<data cell_address="B,7">00001</data>
<data cell_address="C,7">0000002</data>
</row>
<row>
<data cell_address="B,8">00001</data>
<data cell_address="C,8">0000003</data>
</row>
</table>
</tables>


図 19. update文

UPDATE CUSTOMER SET ID = '00001', ID2 = '0000001', NAME = 'TOM', TEL = '9-999-999-9987', FAX = '9-999-999-9997',
DEPT_NO = 11111, DEPT_NAME = 'DEP1', COUNTRY = 'US', CREATE_DATE = '2012-05-09', UPDATE_DATE = '2012-05-09'
WHERE ID = '00001' AND ID2 = '0000001';

UPDATE CUSTOMER SET ID = '00001', ID2 = '0000002', NAME = 'JIM', TEL = '9-999-999-9988', FAX = '9-999-999-9998',
DEPT_NO = 11112, DEPT_NAME = 'DEP2', COUNTRY = 'US', CREATE_DATE = '2012-05-12', UPDATE_DATE = '2012-05-12'
WHERE ID = '00001' AND ID2 = '0000002';

UPDATE CUSTOMER SET ID = '00001', ID2 = '0000003', NAME = 'MARIKO', TEL = '9-999-999-9989', FAX = '9-999-999-9999',
DEPT_NO = 11113, DEPT_NAME = 'DEP3', COUNTRY = 'JP', CREATE_DATE = '2012-05-15', UPDATE_DATE = '2012-05-15'
WHERE ID = '00001' AND ID2 = '0000003';

図 20. delete文

DELETE FROM CUSTOMER WHERE ID = '00001' AND ID2 = '0000001';

DELETE FROM CUSTOMER WHERE ID = '00001' AND ID2 = '0000002';

DELETE FROM CUSTOMER WHERE ID = '00001' AND ID2 = '0000003';

©Katsunori Nakajo and okule, 2012.