トップページ -> 研究道具箱と教材 -> Ruby による Web/データベース・プログラミング -> Ruby で SQLite3 のテーブル定義を扱う
[サイトマップへ]  

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

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

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

Ruby から SQLite を使う方法については:

SQliteが認識できる SQL について,日本語でのすばらしい説明が http://net-newbie.com/sqlite/lang.html にあります.


事前準備

必要となるソフトウエア

次のソフトウエアのインストールが済んでいること.「Ruby プログラミング」の Web ページを参考にしてください.

あらかじめ決めておく事項

使用する SQLite のデータベースのデータベースファイル名を決めておくこと. この Web ページでは,次のように書きます.Ruby の流儀で,Windows の場合は「\」のことを「\\」と書きます.


SQLite3 のテーブル定義を扱うプログラム

ここで扱うテーブル定義

ここに載せるプログラムは,リレーショナルデータベースのテーブル定義を扱うが, SQLite の SQL のテーブル定義文の全機能を扱うわけではない. 下記に、ここに載せるプログラムが扱うテーブル定義文を説明する

  1. SQLite のデータ型

    データ型の種類は,データベース管理システムごとに違う. SQLite では,扱えるデータ型として次の 5 種類がある.詳しい説明は http://www.sqlite.org/datatype3.html にある.

    ※ SQlite のデータ型と,SQL の標準が定めるデータ型の定義は異なる.大胆にまとめると,SQlite のデータ型の方がより大きな範囲のデータを扱える.

  2. SQL テーブル定義文 (create-table-statement)

    CREATE TABLE <table-name> (<column-def> の並び, [<table-constraint> の並び]);

    ※ 「 <table-constraint> の並び」は省略可能であることに注意

  3. 列定義 (column-def)

    <column-name> <type-name> [<column constraint> の並び]

    ※ 「<column constraint>」は省略可能であることに注意

  4. 列制約 (column-constraint) の例

    列定義 (column-def) の中に含める一貫性制約やデフォルト値の指定

  5. テーブル制約 (table-constraint) の例

    create-table-statement の中に含める一貫性制約のうち複数の属性に関わるものは table-constraint の形で記述することになる.

ソースコード

#! 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 の sqlite_master から読み込む
  # TableDef オブジェクトの配列を返す
  # (各TableDef オブジェクトの @table_name, @column_defs, @table_constraints, @sqlstmt は適切に設定された状態にする)
  # データベースオープン
  Dir.chdir( File.dirname( File.expand_path( dbname ) ) )
  db = SQLite3::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


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

# 使用例(1)
# SQLite の 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"

【実行結果の例】