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();
}
}
}
}
<?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>
<?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>
<?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) < 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>
<?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>
<?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="'Lucida Sans Unicode'" 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="ＭＳ Ｐゴシック" svg:font-family="'ＭＳ Ｐゴシック'" 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="ＭＳ Ｐゴシック" 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>'N/A'</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>
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.
XMLを使いこなそう 