RubyOnRails を使ってみる 【第 9 回】 Rails で XML-DB にチャレンジ

<?xml version="1.0" encoding="UTF-8"?>
<Recipe number="0">
    <recipename>ホワイトレディー</recipename>
    <style>ショート</style>
    <glass>カクテルグラス</glass>
    <ingredients>
        <ingredient>
            <name>ドライジン</name><measure unit="proportion">2/4</measure>
        </ingredient>
        <ingredient>
            <name>ホワイトキュラソー</name><measure unit="proportion">1/4</measure>
        </ingredient>
        <ingredient>
            <name>レモンジュース</name><measure unit="proportion">1/4</measure>
        </ingredient>
    </ingredients>
    <instruments>シェーカー</instruments>
    <procedure>材料をシェーカーに入れて、シェークする。</procedure>
    <taste>酸味</taste>
    <taste>甘み</taste>
    <strength>strong</strength>
    <photo>whitelady.jpg</photo>
</Recipe>

db2start

SQL1063N  DB2START の処理が正常に終了しました。

SQL1026N  データベース・マネージャはすでにアクティブになっています。

db2 create database softd_d on c using codeset utf-8 territory jp

rails softdir
cd softdir
ruby script/generate controller Bar index
ruby script/generate model Cocktail

require 'rexml/document'

# REXML を include すれば、"REXML::"のプレフィックスを省略できる。
include REXML

class ApplicationController < ActionController::Base
  before_filter :specification_encode

  def specification_encode
    @headers["Content-Type"] = "text/html; charset=UTF-8"
  end

end

development:
  adapter: ibm_db2
  database: softd_d
  username: db2admin
  password: db2admin
  schema:
  encoding: utf8

class CreateCocktails < ActiveRecord::Migration
  def self.up
    create_table :cocktails do |t|
       t.column "recipe", :xml
       t.column "stop_flg", :boolean, :default => false, :null => false
       t.column "created_on", :datetime, :null => false
       t.column "updated_on", :datetime
    end
  end

  def self.down
    drop_table :cocktails
  end
end

rake db:migrate

CREATE TABLE "DB2ADMIN"."COCKTAILS"  (
		  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
		    START WITH +100
		    INCREMENT BY +1
		    MINVALUE +100
		    MAXVALUE +2147483647
		    NO CYCLE
		    CACHE 20
		    NO ORDER ) ,
		  "RECIPE" XML ,
		  "STOP_FLG" SMALLINT NOT NULL WITH DEFAULT 0 ,
		  "CREATED_ON" TIMESTAMP NOT NULL ,
		  "UPDATED_ON" TIMESTAMP )
		 IN "USERSPACE1" ;
ALTER TABLE "DB2ADMIN"."COCKTAILS"
	ADD PRIMARY KEY
		("ID");

db2look -d softd_d -e -o softd_d.ddl -i db2admin -w db2admin

<?xml version="1.0" encoding="UTF-8"?>
<Recipe number="0">
    <recipename>ホワイトレディー</recipename>
    <style>ショート</style>
    <glass>カクテルグラス</glass>
    <ingredients>
        <ingredient>
            <name>ドライジン</name><measure unit="proportion">2/4</measure>
        </ingredient>
        <ingredient>
            <name>ホワイトキュラソー</name><measure unit="proportion">1/4</measure>
        </ingredient>
        <ingredient>
            <name>レモンジュース</name><measure unit="proportion">1/4</measure>
        </ingredient>
    </ingredients>
    <instruments>シェーカー</instruments>
    <procedure>材料をシェーカーに入れて、シェークする。</procedure>
    <taste>酸味</taste>
    <taste>甘み</taste>
    <strength>strong</strength>
    <photo>whitelady.jpg</photo>
</Recipe>

"0",<XDS FIL='Recipe0.xml'/>,0,"2006-11-24-22.42.08.000000","2006-11-24-22.42.08.000000"

db2 connect to softd_d user db2admin using db2admin
db2 import from recipe\recipe.del of del xml from recipe modified by identityignore replace into cocktails

db2 select * from cocktails

ruby script/generate scaffold Cocktail
ruby script/server

db2 "xquery for $recipe in db2-fn:xmlcolumn('COCKTAILS.RECIPE') order by fn:abs($recipe//@number) return <Recipe> {$recipe//@number,$recipe//recipename/text()} </Recipe>"

--------------------------------------------------------------------
<Recipe number="0">ホワイトレディー</Recipe>
<Recipe number="1">マンハッタン</Recipe>
<Recipe number="2">ドライマンハッタン</Recipe>
<Recipe number="3">ニューヨーク</Recipe>
<Recipe number="4">ゴッドファーザー</Recipe>

 # File app/controllers/bar_controller.rb, line 19
19:   def index
20:     @cocktails = Cocktail.recipe_items
21:   end

 # File app/models/cocktail.rb, line 3
3:   def self.recipe_items
4:     find_by_sql(
5:       "xquery
6:         for $recipe in db2-fn:xmlcolumn('COCKTAILS.RECIPE')
7:         order by fn:abs($recipe//@number)
8:         return <Recipe> {$recipe//@number,$recipe//recipename/text()} </Recipe>" )
9:   end

<h1>カクテルリスト</h1>

<table cellpadding="5" cellspacing="0">
  <tr>
    <th>No.</th>
    <th>カクテル</th>
  </tr>
<% for cocktail in @cocktails %>
  <tr valign="top" class="ListLine<%= cycle(0, 1) %>">
    <% doc = Document.new cocktail["1"] %>
    <td><%=h num = XPath.match(doc,"//@number")[0].value %></td>
    <td width="60%"><%=h XPath.match(doc,"//text()")[0].value %></td>

    <td><%= link_to 'レシピ詳細', :action => 'show_by_ingredient', :id => num %></td>
  </tr>
<% end %>
</table>

<br />

<%= link_to '新しいカクテル', :action => 'new' %>

db2 "xquery let $ingnames := fn:distinct-values(db2-fn:xmlcolumn('COCKTAILS.RECIPE')//ingredient/name/text()) for $ing in $ingnames order by $ing return <Ingredient>{$ing ,<Recipes>{ for $recipe in db2-fn:xmlcolumn('COCKTAILS.RECIPE')[//ingredient/name = $ing] return <Recipe> {$recipe//@number, $recipe//recipename/text()}</Recipe> } </Recipes> }</Ingredient>"

--------------------------------------------------------------------
<Ingredient>アプリコットブランデー<Recipes><Recipe number="15">パラダイス</Recipe></Recipes></Ingredient>
<Ingredient>アマレット<Recipes><Recipe number="4">ゴッドファーザー</Recipe><Recipe number="11">フレンチコネクション</Recipe></Recipes></Ingredient>
<Ingredient>アロマチックビターズ<Recipes><Recipe number="1">マンハッタン</Recipe><Recipe number="2">ドライマンハッタン</Recipe></Recipes></Ingredient>

 # File app/controllers/bar_controller.rb, line 23
23:   def ingredient
24:     @cocktails = Cocktail.ingredient_items
25:   end

 # File app/models/cocktail.rb, line 26
26:   def self.ingredient_items
27:     find_by_sql(
28:       "xquery
29:         let $ingnames := fn:distinct-values(db2-fn:xmlcolumn('COCKTAILS.RECIPE')//ingredient/name/text())
30:         for $ing in $ingnames
31:         order by $ing
32:         return <Ingredient>{$ing ,<Recipes>{
33:           for $recipe in db2-fn:xmlcolumn('COCKTAILS.RECIPE')[//ingredient/name = $ing]
34:           return <Recipe> {$recipe//@number, $recipe//recipename/text()}</Recipe> } </Recipes> }</Ingredient>" )
35:   end

<h1>材料別カクテルリスト</h1>
<% for cocktail in @cocktails %>
  <% doc = Document.new cocktail["1"] %>

  <h2><%=h XPath.match(doc,"//text()")[0].value %></h2>

  <% x = XPath.match(doc,"//Recipes/Recipe/@number") %>
  <% y = XPath.match(doc,"//Recipes/Recipe/text()") %>

  <table>
    <tr>
      <th>No.</th>
      <th>カクテル</th>
    </tr>
    <% x.zip(y){|a,b| %>
    <tr>
      <td> <%=h a %></td>
      <td> <%=h b %></td>
      <td><%= link_to 'レシピ詳細', :action => 'show_by_ingredient', :id => a %></td>
    </tr>
    <% } %>
  </table>

<% end %>
<%= link_to 'カクテル一覧', :action => 'index' %>

db2 select id,recipe from cocktails where xmlexists('$r/Recipe[@number = \"0\"]' passing COCKTAILS.RECIPE as \"r\")"

--------------------------------------------------------------------
180 <Recipe number="0"><recipename>ホワイトレディー</recipename><style>ショート</style><glass>カクテルグラス</glass><ingredients><ingredient><name>ドライジン</name><measure unit="proportion">2/4</measure></ingredient><ingredient><name>ホワイトキュラソー</name><measure unit="proportion">1/4</measure></ingredient><ingredient><name>レモンジュース</name><measure unit="proportion">1/4</measure></ingredient></ingredients><instruments>シェーカー</instruments><procedure>材料をシェーカーに入れて、シェークする。</procedure><taste>酸味</taste><taste>甘み</taste><strength>strong</strength><photo>whitelady.jpg</photo></Recipe>

 # File app/controllers/bar_controller.rb, line 27
27:   def show_by_ingredient
28:     @cocktail = Cocktail.ingredient_to_recipe(params[:id])
29:     render :template => 'bar/show'
30:   end

 # File app/models/cocktail.rb, line 11
11:   def self.ingredient_to_recipe(id)
12:     rs = find_by_sql([
13:       "select * from cocktails
14:         where xmlexists('$r/Recipe[@number = \"?\" ]'
15:                 passing COCKTAILS.RECIPE as \"r\")" ,id.to_i ])
16:     return rs[0]
17:   end

<h1>カクテルのレシピ</h1>

<div id="mydiv"><p><%= link_to_remote 'スキーマを確認', :update => 'mydiv', :url => {:action => 'va lidate', :id => @cocktail} %></p></div>
<% doc = Document.new @cocktail.recipe %>
<p><b>No.</b> <%=h XPath.match(doc,"/Recipe/@number")[0].value %></p>
<p><b>カクテル:</b> <%=h XPath.match(doc,"//recipename/text()")[0].value %></p>
<p><b>スタイル:</b> <%=h XPath.match(doc,"//style/text()")[0].value %></p>
<p><b>グラス:</b> <%=h XPath.match(doc,"//glass/text()")[0].value %></p>

<% x = XPath.match(doc,"//ingredient/name/text()") %>
<% y = XPath.match(doc,"//ingredient/measure/text()") %>
<% z = XPath.match(doc,"//ingredient/measure/@unit") %>

<p><b>材料</b></p>
<table>
<% x.zip(y,z){|a,b,c| %>
<tr>
<td><b>名前:</b> <%=h a %></td>
<td><b>分量:</b> <%=h b %></td>
<td><b>単位:</b> <%=h c %></td>
</tr>
<% } %>
</table>

<p><b>道具:</b> <%=h XPath.match(doc,"//instruments/text()").collect{|attr| attr.value}[0] %></p>
<p><b>作り方:</b> <%=h XPath.match(doc,"//procedure/text()").collect{|attr| attr.value}[0] %></p>

<% for taste in XPath.match(doc,"//taste/text()") %>
<p><b>味:</b> <%=h taste %></p>
<% end %>

<p><b>強さ:</b> <%=h XPath.match(doc,"//strength/text()")[0].value %></p>
<p><b>写真:</b> <%=h XPath.match(doc,"//photo/text()")[0].value %></p>

<p><b>中止フラグ:</b> <%=h @cocktail.stop_flg %></p>
<p><b>作成日時:</b> <%=h @cocktail.created_on.strftime("%Y/%m/%d %H:%M:%S") %></p>
<p><b>更新日時:</b> <%=h @cocktail.updated_on.strftime("%Y/%m/%d %H:%M:%S") %></p>

<%= link_to '修正', :action => 'edit', :id => @cocktail %> |
<%= link_to 'カクテルリスト', :action => 'index' %>

<?xml version="1.0" encoding="UTF-8"?>
<Recipe number="4">
    <recipename>ゴッドファーザー</recipename>
    <style>ロック</style>
    <glass>オールドファッションドグラス</glass>
    <ingredients>
        <ingredient>
            <name>ウィスキー</name><measure unit="proportion">3/4</measure>
        </ingredient>
        <ingredient>
            <name>アマレット</name><measure unit="proportion">1/4</measure>
        </ingredient>
    </ingredients>
    <procedure>材料をグラスに直接入れて軽くステアする。</procedure>
    <taste>甘み</taste>
    <strength>strong</strength>
    <photo>godfather.jpg</photo>
</Recipe>

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="instruments" type="xsd:string"/>
  <xsd:element name="glass" type="xsd:string"/>
  <xsd:element name="Recipe">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="recipename" minOccurs="1"/>
        <xsd:element ref="style"/>
        <xsd:element ref="glass"/>
        <xsd:element ref="ingredients" minOccurs="1"/>
        <xsd:element ref="instruments"/>
        <xsd:element ref="procedure" minOccurs="1"/>
        <xsd:element maxOccurs="unbounded" ref="taste"/>
        <xsd:element ref="strength"/>
        <xsd:element ref="photo"/>
      </xsd:sequence>
      <xsd:attribute name="number" type="xsd:string"/>
　　　　　　｜
　　　　　　中略
　　　　　　｜
</xsd:schema>

db2 register xmlschema http://recipe from recipe.xsd as xsr.recipe
db2 complete xmlschema xsr.recipe

db2 select xmlvalidate(recipe according to xmlschema id xsr.recipe) from cocktails where id = 180
--------------------------------------------------------------------
<Recipe number="0"><recipename>ホワイトレディー</recipename><style>ショート</style><glass>カクテルグラス</glass><ingredients><ingredient><name>ドライジン</name><measure unit="proportion">2/4</measure></ingredient><ingredient><name>ホワイトキュラソー</name><measure unit="proportion">1/4</measure></ingredient><ingredient><name>レモンジュース</name><measure unit="proportion">1/4</measure></ingredient></ingredients><instruments>シェーカー</instruments><procedure>材料をシェーカーに入れて、シェークする。</procedure><taste>酸味</taste><taste>甘み</taste><strength>strong</strength><photo>whitelady.jpg</photo></Recipe>
1 レコードが選択されました。

db2 select xmlvalidate(recipe according to xmlschema id xsr.recipe) from cocktails where id = 184
--------------------------------------------------------------------

SQL16196N  XML 文書に正しく指定されていない要素 "procedure"が含まれています。理由コード = "31"。  SQLSTATE=2200M

 # File app/controllers/bar_controller.rb, line 52
52:   def validate
53:     if Cocktail.validate_recipe?(params[:id])
54:       flash[:notice] = 'スキーマに準拠しています(^_^)v'
55:     else
56:       flash[:notice] = 'スキーマに準拠していません(T_T)'
57:     end
58:       render :layout => false
59:   end

 # File app/models/cocktail.rb, line 19
19:   def self.validate_recipe?(id)
20:     rs = find_by_sql([
21:       "select xmlvalidate(recipe according to xmlschema id xsr.recipe) from cocktails
22:         where id = ?",id.to_i ])
23:     return rs[0]
24:   end

    # File app/controllers/bar_controller.rb, line 40
40:   def update
41:     y = params[:cocktail]
42:     y["recipe"] = recipe_create(params[:recipe_xml])
43:     @cocktail = Cocktail.find(params[:id])
44:     if @cocktail.update_attributes(y)
45:       flash[:notice] = 'Cocktail was successfully updated.'
46:       redirect_to :action => 'show', :id => @cocktail
47:     else
48:       render :action => 'edit'
49:     end
50:   end

    # File app/controllers/bar_controller.rb, line 63
63:   def recipe_create(x)
64:     doc = REXML::Document.new()
65:     doc << REXML::XMLDecl.new('1.0','UTF-8')
66:
67:     e0 =  REXML::Element.new('Recipe')
68:     e0.attributes['number'] = x['number']
69:     e0.add_element('recipename').add_text(x['recipename'])
70:     e0.add_element('style').add_text(x['style'])
71:     e0.add_element('glass').add_text(x['glass'])
72:     e1 = e0.add_element('ingredients')
73:     s = x['name'].values
74:     t = x['measure'].values
75:     u = x['unit'].values
76:     s.zip(t,u){|a,b,c|
77:       e2 = e1.add_element('ingredient')
78:       e2.add_element('name').add_text(a)
79:       e2.add_element('measure', {'unit' => c}).add_text(b)
80:     }
81:     e0.add_element('instruments').add_text(x['instruments'])
82:     e0.add_element('procedure').add_text(x['procedure'])
83:     x['taste'].each{ |key,value| e0.add_element('taste').add_text(value)}
84:     e0.add_element('strength').add_text(x['strength'])
85:     e0.add_element('photo').add_text(x['photo'])
86:
87:     doc << e0
88:     return doc.to_s
89:   end