Ruby で SQLite 3 のテーブル定義を扱う

Ruby 言語を使い， 組み込み型のリレーショナルデータベース管理システム SQLite 3 のテーブル定義を扱ってみます． このページでは次のことを行う．

• テーブル定義を行う SQL プログラムの生成

  Ruby のデータ構造に，属性名，データ型，列制約，テーブル制約を格納しておくと，それらからテーブル定義を行う SQL プログラムを生成する

• SQLite 3 のテーブル定義の解析の簡単なもの

  SQLite 3の sqlite_master テーブルには TEMPORARY TABLE 以外のテーブルのテーブル定義が格納されている． このテーブル定義を読み取り，解析して， 解析の結果 column_def と table_constraints を配列に入れる．但し，SQLite 3の SQL テーブル定義文の一部の文法のみに対応．

SQLite 3の SQL に関する詳しい説明は：

• http://www.hwaci.com/sw/sqlite/lang.html にあります．

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

def table_constaint? (s)
  # 文字列の先頭に PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY がくる場合
  # true. 但し，大文字，小文字を区別しない
  ss = s.strip.downcase
  if /^primary[\s]+key/ =~ ss
    return true
  elsif /^unique/ =~ ss
    return true
  elsif /^check/ =~ ss
    return true
  elsif /^foreign[\s]+key/ =~ ss
    return true
  else
    return false
  end
end

#
#
#

def parse_sqlite3_create_table_sql_stmt (s)
  # SQLite 3の create table SQL 文を解析し，table_constraints と column_defs を取り出す

  # body は s の本体．但し，\t, \r \n, \f は半角空白文字に置き換える
  # 例えば，a = "hoge => create table HOGE (id integer, x REAL);" のとき
  # body = 例えば "hoge => id integer, x REAL"
  # つまり、括弧「()」の中身を取り出す
  s.gsub(/\t|\r|\n|\f/,' ').match /([^\(]+)\((.+)\)(.)*/
  body = $2

  # "id char(20,20), name char(10,10)" のような場合，単純に「,」で区切るとうまくいかない．入れ子の中の場合には、「前」に連結する
  lines = Array.new
  depth = 0
  body.split(/,/).each do |a|
    if ( depth == 0 )
      lines.push(a)
    else
      lines.last.concat(',').concat(a)
    end
    # '( x * ( y + z ) )'.count('(') => 2
    depth += a.count('(') - a.count(')')
  end

  # SQL の create table の本体が lines(配列)に入ったので，
  # それを使って table_constraints と column_defs を取り出す
  table_constraints = Array.new
  column_defs = Array.new
  lines.each do |a|
    # SQL の create table 本体の各行についての処理
    if ( table_constaint? a)
      table_constraints.push(a)     
    else
      tokens = a.strip.split(nil)
      # 先頭
      column_name = tokens.shift
      # 2 つめ以降
      type_name_and_column_constraints = tokens.join(' ')
      column_defs.push(column_name => type_name_and_column_constraints)
    end
  end

  return column_defs, table_constraints
end

#
#
#

def has_elem(array)
  # nil でも empty でもなければ true
  if array.nil?
    return false
  elsif array.empty?
    return false
  else
    return true
  end
end

#
#
#

# テーブル定義に関するデータを格納するためのクラス．
#   @table_name がテーブル名
#   @column_defs が属性（column_name とその型と制約を格納したハッシュテーブル）
#   @table_constraints は制約（配列）
#   @sqlstmt は create table の SQL 文
class TableDef
  attr_accessor :table_name, :column_defs, :table_constraints, :sqlstmt
  def initialize(table_name)
    @table_name = table_name
    @column_defs = nil
    @table_constraints = nil
    @sqlstmt = nil
  end

  # @sqlstmt から @column_defs, @table_constraints を生成
  def parse_SQL_stmt
    @column_defs, @table_constraints = parse_sqlite3_create_table_sql_stmt @sqlstmt
  end

  # @table_name, @column_defs, @table_constraints から @sqlstmt を生成
  def generate_SQL_stmt
    out = "create table " + @table_name + " (\n"

    # column_defs 部分
    j = 0
    if !column_defs.nil?
      column_defs.each do |key, value|
        out.concat "  #{key} \t"
          out.concat value
        if ( ( j < (@column_defs.size - 1) ) || has_elem(@table_constraints) )
          # 最後の属性でないときは「,」で継続
      out.concat ",\n"
        end
        j += 1
      end
    end

    # table_constraints 部分
    j = 0
    if !table_constraints.nil?
      table_constraints.each do |key, value|
        out.concat "  #{key} \t"
          out.concat value
        if  ( j < (@table_constraints - 1) )
          # 最後の属性でないときは「,」で継続
      out.concat ",\n"
        end
        j += 1
      end
    end

    out.concat" );\n"
    @sqlstmt = out
  return
  end

end

#
#
#

def read_sqlite_master( dbname )
  # SQL で書かれたテーブル定義 SQLite 3の sqlite_master から読み込む
  # TableDef オブジェクトの配列を返す
  # (各TableDef オブジェクトの @table_name, @column_defs, @table_constraints, @sqlstmt は適切に設定された状態にする）
  # データベースオープン
  Dir.chdir( File.dirname( File.expand_path( dbname ) ) )
  db = SQLite 3::Database.new( dbname )

  # SQL による問い合わせ
  sql = "SELECT * FROM sqlite_master;"

  tables = Array.new
  # db.execute(...) do |row| ... end は，結果が複数の行になるときの決まり文句
  db.execute(sql) do |row|
    # row の 0 列目 : {"table", "index"} のいずれか
    # row の 2 列目 : tbl_name
    # row の 4 列目 : SQL で書かれたテーブル定義
    if ( row[0] == "table" )
      # row[2] は例えば "hoge"
      # row[4] は例えば "create table HOGE (id integer, x REAL);"
      t = TableDef.new row[2]
      t.sqlstmt = row[4]
      t.parse_SQL_stmt
      tables.push(t)
    end

  end

  # データベースクローズ
  db.close

return tables
end


# SQLite 3 のデータベースファイル名を DBNAME に設定してください．
# Windows の場合. 「C:」のようにドライブ名を付ける
# DBNAME = "C:/SQLite/testdb"
# Linux の場合
DBNAME = "/var/SQLite/testdb"

# 使用例(1)
# SQLite 3の sqlite_master を読み込んで解析

pp read_sqlite_master(DBNAME)
print "-------------------------------\n"


# 使用例(2)
# テーブル定義に関するデータを格納し，create table の SQL 文を生成
products_def = TableDef.new "products"
products_def.column_defs =
  Hash[
  "id",       "INTEGER  PRIMARY KEY autoincrement not null",
  "product_name", "BLOB   UNIQUE not null",
  "type",     "TEXT   not null",
  "cost",     "REAL",
  "created_at",   "DATETIME not null",
  "updated_at",   "DATETIME not null"]

products_def.generate_SQL_stmt
pp products_def    
print "-------------------------------\n"