#!ruby -Ks

        ##
        ##  DB作成スクリプト
        ##

require "phi"
require "rdb"
require "rgui"
require "dialogs"   ##  message_dlg
include Phi
include RDB

Phi.date_format = "yyyy/mm/dd"

        ##
        ##  データベースの指定。
        ##
        ##  上がローカルＩＢ用で
        ##  下が InterBase@Linux 用、
        ##  といっても、何も変わらないね。
        ##
DB = Database_ib.new("IBLocal","SYSDBA","masterkey")
##DB = Database_ib.new("APOLLO","apollo","apollo")

        ##
        ##  画面設計
        ##
form = RGUI::Form.new
form.add(
  RGUI::Vbox.new.add(
      label_msg  = Label.new( form,:b1, <<EOT

サンプルＤＢを作成します。
既存のテーブルは消去されます。

運用開始後には実行しないでください。

データベースエクスプローラなどで
当該データベースを開いていると、失敗します。
当該データベースにを使用しているアプリが
ないことを確認してください。

EOT
      ),
      button_create_db  = Button.new(form,:b1, "実行" )
  )
)

        ##
        ##  ボタンを押したときのイベントハンドラ
        ##
button_create_db.on_click = proc{
  button_create_db.caption = "実行中"
  button_create_db.enabled = false;
  begin

        ##
        ##  制約の解除
        ##
        ##  制約を解除しないとテーブルの変更はできない。
        ##
        ##  問題となる制約は
        ##  VIEW
        ##  FOREIGN KEY
        ##  （など？）
        ##
        ##  問題となる制約は全部削除して、後で全部再構築すれば簡単。
        ##

        ##
        ##  VIEW を削除する。
        ##
        ##  VIEW は TABLE を参照しているので、
        ##  VIEW を先に削除しないと TABLE を削除できない。
        ##
    puts 'drop VIEWs'
    DB.view_drop "ap01den_gokei",  true
    DB.view_drop "ap01meisai_mei", true
    DB.view_drop "ap01den_mei",    true

        ##
        ##  FOREIGN KEY を削除するには、
        ##  作成時に「制約名」を指定しておかなければならない。
        ##
        ##  ADD CONSTRAINT ap01den_id_fk
        ##      FOREIGN KEY ( den_id )    REFERENCES ap01den( den_id ),
        ##  をパックした named_foreign_key_add
        ##  で作成する。
        ##
    puts "drop CONSTRAINTs"
    DB.constraint_drop "ap01meisai", "ap01den_id_fk" ,     true
    DB.constraint_drop "ap01meisai", "ap01shohin_id_fk" ,  true
    DB.constraint_drop "ap01den",    "ap01kokyaku_id_fk" , true

        ##
        ##  GENERATOR（カウンタ）を削除する。
        ##
    puts 'drop GENERATORs'
    DB.counter_drop 'ap01gen1',  true
    DB.counter_drop 'ap01gen2',  true
    DB.counter_drop 'ap01gen3',  true

        ##
        ##  GENERATOR（カウンタ）を作成する。
        ##
        ##  GENERATORはInterBase専用。OracleのSEQUENCEに相当する。
        ##  ParadoxのAutoInc型に相当する機能を実現できる。
        ##
        ##  カウンタからの取り出しは「DB.counter_inc」で行う。
        ##  カウンタ取り出しで使うダミーのテーブルも作る。
        ##
    puts 'create GENERATORs'
    DB.counter_create 'ap01gen1' unless DB.counter_exist? "ap01gen1"
    DB.counter_create 'ap01gen2' unless DB.counter_exist? "ap01gen2"
    DB.counter_create 'ap01gen3' unless DB.counter_exist? "ap01gen3"

        ##
        ##  テーブルの定義。
        ##
        ##  「table_recreate」で、
        ##  既存のテーブルがあれば有無を言わさず削除して、
        ##  新しいテーブルを作成している。
        ##
        ##  table_recreate table_name , col_def
        ##  は最終的には次のようなＳＱＬ文になる（rdb.rb）。
        ##  DB.execute "CREATE TABLE #{table_name} ( #{col_def} )"
        ##
        ##  データベースの設計、新規作成の場合には便利な手法だが、
        ##  運用開始後は実行してはならない。データが消える。
        ##
        ##  ここで FOREIGN KEY を定義すると、
        ##  後の方のテーブルの削除が出来なくなる。
        ##  FOREIGN KEY などは全部のテーブルを作ってから、
        ##  ALTER TABLE で追加する。
        ##
        ##  FOREIGN KEYで参照している方のテーブルから定義すること。
        ##  これも、上と同じ理由。
        ##
        ##  運用開始後に構造を変更する場合には
        ##  このスクリプトを改造して、
        ##  （１）新しいデータベースを作成して、
        ##  （２）古いデータベースからデータを吸い上げる
        ##  という手順を踏んだ方がよい。
        ##
        ##  ALTER TABLE で ADD や DROP で少しずつ変更したり
        ##  ＤＢＥＸでビジュアルに変更することもできる、
        ##  はずだが、制約を解除して、テーブルを変更して、制約を設定し直す
        ##  という手順を踏まなければならないので、面倒かつ間違いやすい。
        ##
        ##  NULL が入ると面倒なので、
        ##  機械的に処理する必要がある項目には「NOT NULL」を付けた方がよい。
        ##  メモなどは、領域節約のために NULL を許してもよいかもしれない。
        ##  「どうでもよいもの」は「DEFAULT」を付けるのがよい。
        ##
    puts 'create TABLEs'

        ##
        ##  伝票の明細のテーブル
        ##
        ##  プライマリキーは複数のＩＤで構成されている。
        ##  複数の項目でキーを構成する場合には、このように書く。
        ##
        ##  単価は商品テーブルからコピーする。修正可能とする。
        ##
        ##  明細テーブルはタイトルテーブルのden_idに
        ##  FOREIGN KEY 参照を（後で）設定する。
        ##
    DB.table_recreate 'ap01meisai' , <<EOT
        den_id        INTEGER NOT NULL,
        meisai_id     INTEGER NOT NULL,
        shohin_id     INTEGER NOT NULL,
        tanka         INTEGER NOT NULL,
        suryo         INTEGER NOT NULL,
        PRIMARY KEY( den_id, meisai_id )
EOT
        ##
        ##  伝票のタイトル部分のテーブル
        ##
        ##  プライマリキーはひとつの項目で出来ている場合には
        ##  項目定義の後に書ける。
        ##
        ##  DATE 型は個人的には好きではない。
        ##  検索しにくい。
        ##  "YYYYMMDD" という文字列が好きだ。
        ##
    DB.table_recreate 'ap01den' , <<EOT
        den_id        INTEGER NOT NULL PRIMARY KEY,
        den_date      DATE    NOT NULL,
        kokyaku_id    INTEGER NOT NULL
EOT
        ##
        ##  商品テーブル
        ##
        ##  単価は別のテーブルの方がよいか？。
        ##  一つの商品に複数の単価がある場合が考えられる。
        ##
        ##  在庫数量などは別のテーブルの方がよいだろう。
        ##  更新頻度が異なるものは別にした方がよい。
        ##
        ##  商品コードと商品名にもインデックスをつける。
        ##  商品名もUNIQUEとする。同一の商品名はまずいだろう。
        ##
        ##  memoの「BLOB SUB_TYPE 1」はInterBase依存。
        ##  メモ型（SUB_TYPE 1）のBinary Large Object。
        ##  「SUB_TYPE 0」ならバイナリ（グラフィックなど）。
        ##
    DB.table_recreate 'ap01shohin' , <<EOT
        shohin_id     INTEGER     NOT NULL PRIMARY KEY,
        shohin_code   VARCHAR(6)  NOT NULL,
        shohin_mei    VARCHAR(14) NOT NULL,
        tanka         INTEGER     NOT NULL,
        memo          BLOB SUB_TYPE 1
EOT
    DB.execute "CREATE UNIQUE INDEX ap01shohin_code ON ap01shohin(shohin_code)"
    DB.execute "CREATE UNIQUE INDEX ap01shohin_mei  ON ap01shohin(shohin_mei)"

        ##
        ##  顧客テーブルの定義。
        ##
        ##  住所は１４桁では足りない。faxも欲しい、
        ##  が、サンプルで使わないので手抜きする。
        ##
    DB.table_recreate 'ap01kokyaku' , <<EOT
        kokyaku_id    INTEGER NOT NULL PRIMARY KEY,
        kokyaku_mei   VARCHAR(14) NOT NULL,
        jusho         VARCHAR(14) DEFAULT "",
        tel           VARCHAR(14) DEFAULT "",
        memo          BLOB SUB_TYPE 1
EOT
    DB.execute "CREATE INDEX ap01kokyaku_mei ON ap01kokyaku( kokyaku_mei )"

        ##
        ##  FOREIGN KEY 制約の定義。
        ##
        ##  複数のテーブルに関係する制約なので、
        ##  すべてのテーブルの作成が終わってから定義する。
        ##
        ##  DBEX で見ると、TRIGGER で実現されているらしい。
        ##  /*  Trigger CHECK_30  */
        ##  CREATE TRIGGER CHECK_30 FOR AP01DEN AFTER DELETE POSITION 1
        ##
        ##  「ON DELETE CASCADE」オプションは
        ##  Delphi5 の Local InterBase では使えるが、
        ##  Linux の InterBase ver.4.0G では使えなかった。
        ##  （『データベース Linux』の付録のバージョン）。
        ##  マスターを削除したら参照側も削除されるというオプション。
        ##
    puts 'alter TABLEs for constraint'

    DB.named_foreign_key_add "ap01meisai", "ap01den_id_fk",
                "den_id", "ap01den( den_id )"
    DB.named_foreign_key_add "ap01meisai", "ap01shohin_id_fk",
                "shohin_id", "ap01shohin( shohin_id )"
    DB.named_foreign_key_add "ap01den", "ap01kokyaku_id_fk",
                "kokyaku_id", "ap01kokyaku( kokyaku_id )"

        ##
        ##  VIEW の定義
        ##
        ##  VIEW を作る目的は、
        ##  （１）複数のテーブルを合成して見れるようにする。
        ##  （２）基本テーブルを一般ユーザから見られないようにして、
        ##      一般ユーザに解放してよい部分を VIEW にしておく。
        ##
        ##  既存の VIEW の削除はテーブル定義の前で行う。
        ##
    puts 'create VIEWs'

    DB.execute <<EOT
      CREATE VIEW ap01meisai_mei
      (         shohin_mei, tanka, suryo,
                shokei,
                den_id, meisai_id, shohin_id )
      AS
      SELECT    S.shohin_mei, M.tanka, M.suryo,
                ( M.tanka * M.suryo ) AS shokei,
                M.den_id, M.meisai_id, M.shohin_id
      FROM ap01meisai M, ap01shohin S
      WHERE ( M.shohin_id = S.shohin_id )
EOT

        ##
        ##  伝票の明細の合計の VIEW。
        ##
        ##  別の VIEW を利用して、
        ##  SUM、GROUP BY などの複雑なＳＱＬを利用している。
        ##  このようなものも VIEW にしておけば簡単になる。
        ##
        ##  puts DB.table "ap01den_gokei"
        ##  とするだけで、うまくできたかを確認できる。
        ##
    DB.execute <<EOT
      CREATE VIEW ap01den_gokei ( den_id, gokei )  AS
      SELECT den_id, SUM( shokei ) AS gokei
      FROM ap01meisai_mei
      GROUP BY den_id
EOT
        ##
    DB.execute <<EOT
      CREATE VIEW ap01den_mei AS
      SELECT den_date, kokyaku_mei, den_id,kokyaku_id
      FROM ap01den D, ap01kokyaku K
      WHERE ( D.kokyaku_id = K.kokyaku_id )
EOT

        ##
        ##  初期データの挿入。
        ##
    puts 'insert DATA'

    insert_kokyaku ( "もりきゅう", "四国某所", "tel ??" )
    insert_kokyaku ( "たけ(tk)",   "関東某所", "03-5555-1234" )
        ##
    insert_shohin ( "M001" , "緑茶アポロ"   ,  100 )
    insert_shohin ( "D001" , "デルファイ飴" , 1000 )
    insert_shohin ( "R001" , "ルビーの小枝" ,  300 )

        ##
        ##  伝票の挿入
        ##
    begin
     insert_denpyo(
        [ DateTime.new.date,  "もりきゅう"  ],
        [[ "緑茶アポロ"    , 20 ],
         [ "デルファイ飴"  , 10 ],
         [ "ルビーの小枝"  , 30 ]] )
     insert_denpyo(
        [ DateTime.new.date,  "たけ(tk)" ],
        [[  "緑茶アポロ"     , 10 ],
         [  "デルファイ飴"   ,  1 ],
         [  "ルビーの小枝"   ,  3 ]] )
    rescue
      Phi.message_dlg("伝票の挿入に失敗しました[#{$!}]")
      raise
    end

        ##
        ##  いくつかの VIEW、TABLE を表示する。
        ##
    puts "succeed!!"
    puts DB.table('ap01den_mei').fields
    puts DB.table('ap01den_mei')

    puts DB.table('ap01meisai_mei').fields
    puts DB.table('ap01meisai_mei')

    puts DB.table('ap01shohin').fields
    puts DB.table('ap01shohin')

        ##
        ##  ボタン表示を変更
        ##
        ##  もう一度押したら終了。
        ##
    button_create_db.caption = "成功しました・・終了します"
  rescue
    button_create_db.caption = "失敗！・・終了します"
    raise
  ensure
    button_create_db.enabled = true
    button_create_db.on_click = proc{ form.close }
    button_create_db.set_focus
  end
}

        ##
        ##  サブルーティン群
        ##
        ##  Database_ex#lookup は what、from、where を引数に取り、
        ##  "SELECT #{what} FROM #{from} WHERE #{where}"
        ##  でデータセットを取り出し、
        ##  最初のレコードの最初の項目の value を返す。
        ##

        ##
        ##  顧客名から顧客ＩＤを返す。
        ##  商品名から商品ＩＤを返す。
        ##
def kokyaku_mei_to_id kokyaku_mei
  DB.lookup "kokyaku_id", "ap01kokyaku", "kokyaku_mei = '#{kokyaku_mei}'"
end

def shohin_mei_to_id shohin_mei
  DB.lookup "shohin_id", "ap01shohin", "shohin_mei = '#{shohin_mei}'"
end

def shohin_id_to_tanka shohin_id
  DB.lookup "tanka", "ap01shohin", "shohin_id = #{shohin_id}"
end

def shohin_id_to_mei shohin_id
  DB.lookup "shohin_mei", "ap01shohin", "shohin_id = #{shohin_id}"
end

        ##
        ##  新規レコードの INSERT。
        ##

        ##
        ##  insert_denpyo
        ##
        ##  引数は den,meisai
        ##  den     （１）タイトル部分の配列。
        ##  meisai  （２）項目部分の配列の配列。
        ##
        ##  （１）タイトル部分の配列は（１１）日付、（１２）顧客名／ＩＤ
        ##  （２）項目部分は項目の配列の配列で、
        ##       （２１）商品名／ＩＤ、（２２）数量、［（２３）単価］
        ##
        ##  （１２）（２１）の名前／ＩＤは、引数の型が
        ##  引数の型が数値であればＩＤとみなし、
        ##  文字列であれば名前とみなす。
        ##  名前の場合には「名称→ＩＤ」のメソッドを呼び出す。
        ##
        ##  （２３）単価は無ければ商品テーブルの単価を使用する。
        ##
        ##  複数のテーブル（den＋meisai）に同時に書き込むので、
        ##  トランザクションを使って全体をセーブすることを保証する。
        ##
        ##  insert_meisai と insert_den は要素としてのセーブを行う。
        ##  単体で呼び出してはならない。
        ##
        ##  とりあえず配列でやっているが、引数のクラスを定義した方がよい。
        ##  個々の名前があった方が間違いが少なくなる。
        ##
        ##  文法エラーの場合はcommitされる？。
        ##
def insert_denpyo den,meisai
  DB.transaction {
    den_id = insert_den ( den[0],  den[1] )
    meisai.each{ |m| insert_meisai den_id, m[0], m[1], m[2] }
  }
end

def insert_meisai den_id, shohin_id, suryo, tanka=nil
  shohin_id = shohin_mei_to_id( shohin_id ) unless shohin_id.is_a? Integer
  tanka = shohin_id_to_tanka(shohin_id) unless tanka
  meisai_id = DB.counter_inc( "ap01gen2" )
  DB.execute <<EOT
    INSERT INTO ap01meisai
           (   den_id ,   meisai_id  ,   shohin_id ,   tanka  ,   suryo )
    VALUES ( #{den_id}, #{meisai_id} , #{shohin_id}, #{tanka} , #{suryo} )
EOT
  return meisai_id
end

def insert_den den_date, kokyaku_id
  kokyaku_id = kokyaku_mei_to_id( kokyaku_id ) unless kokyaku_id.is_a? Integer
  den_id = DB.counter_inc( "ap01gen2" )
  DB.internal_query{|query|
    query.close
    query.sql.text = <<EOT
      INSERT INTO ap01den
             (  den_id ,  den_date,  kokyaku_id )
      VALUES ( :den_id , :den_date, :kokyaku_id )
EOT
    query.params["den_id"] = den_id
    query.params["den_date"] = den_date
    query.params["kokyaku_id"] = kokyaku_id
    query.execute
  }
  return den_id
end

        ##
        ##  顧客テーブルなどは単体で完結しているので自由に呼び出し可能。
        ##
def insert_kokyaku kokyaku_mei,jusho,tel
  kokyaku_id = DB.counter_inc( "ap01gen1" )
  DB.execute <<EOT
    INSERT INTO ap01kokyaku
           (   kokyaku_id  ,    kokyaku_mei  ,    jusho  ,    tel )
    VALUES ( #{kokyaku_id} , "#{kokyaku_mei}", "#{jusho}", "#{tel}" )
EOT
  return kokyaku_id
end

def insert_shohin shohin_code,shohin_mei,tanka
  shohin_id = DB.counter_inc( "ap01gen1" )
  DB.execute <<EOT
    INSERT INTO ap01shohin
           (   shohin_id  ,    shohin_code   ,    shohin_mei   ,   tanka )
    VALUES ( #{shohin_id} , "#{shohin_code}" , "#{shohin_mei}" , #{tanka} )
EOT
  return shohin_id
end

        ##
        ##  メイン  ＝＝
        ##
form.layout
form.show
Phi.mainloop

DB.close

raise
