簡易データベースの実装

配列の配列を、SQLを使って操作できるようにしたものを作ってみました。簡易なので、制限は山ほどありますが、一部は以下のようなものです。

SQLと言っても、限られたものしか使えない
- SELECT,UPDATE,INSERT,DELETE,WHEREと不完全な CREATE TABLE
内部値は全てString型
基本的にメモリ上で動作

ファイルを使う場合は、CSVを標準入力から読み込ませることはできます。

require "./database.rb"

table = Database.new.create_table "stock", %w[ id name count ]
while line = gets
  table << line.split(",")
end
pp table.db.sql( <<SQL )

  SELECT NAME, count FROM stock WHERE NAME = "keyboard"

SQL

本体は150Lines程度。再構成の過程で、知らなかったこととかがあって勉強になりました。無理やり縮めたところも結構あります。
本体。

# database.rb
module SQL
  def sql(sql, db = self)
    cmd = nil
    sql.lstrip!
    if sql[/^CREATE/i]
      raise invalid(sql) unless sql[/^CREATE\s+TABLE\s+(\w+)\s*\((.+)\)$/i]
      table_name, cols = $1, $2
      return db.create_table(table_name, cols)
    elsif sql[/^SELECT/i]
      raise invalid(sql) unless sql[/^SELECT\s(.+)\sFROM\s+(\w+)(.*)$/i]
      cmd, colexp, table, rest = "select", $1, table(db, $2), $3
      cols = colexp.split(/,/).collect do |col|
        if col == "*" then table.columns else col.strip end
      end.select do |col| !col.empty? end.flatten
      cond = if rest[/^\s*WHERE\s(.+)$/i] then $1 else true end
      args = [cond, cols]
    elsif sql[/^UPDATE/i]
      raise invalid(sql) unless sql[/^UPDATE\s+(\w+)\sSET\s(.+)$/i]
      cmd, table, rest = "update", table(db, $1), $2
      a = rest.split(/WHERE/, 2)
      args = [ a[0], a[1] || true ] # assign, cond
    elsif sql[/^INSERT/i]
      raise invalid(sql) unless sql[/^INSERT\s+INTO\s+(\w+)\s+(.*)\s*VALUES\s*\((.+)\)\s*$/i]
      cmd, table, colexp, valexp = "insert", table(db, $1), $2, $3
      vals = valexp.split(/,/).collect do |val|
        val.strip!
        if val.include?("'")
          val = if val[/^'([^']+)'$/] then $1 else raise invalid(sql) end
        end
        val
      end
      args = [vals]
      unless colexp.empty?
        raise invalid(sql) unless colexp[/^\((.+)\)\s*$/i]
        cols = $1.split(/,/).collect do |col| col.split end
        raise invalid(sql) unless cols.size == vals.size
        args << cols
      end
    elsif sql[/^DELETE/i]
      raise invalid(sql) unless sql[/^DELETE\s+FROM\s+(\w+)(.*)$/i]
      cmd, table, rest = "delete", table(db, $1), $2.strip
      args = [ if rest.empty? then true else rest.sub(/^WHERE/i, "") end ]
    end
    table.send(cmd, *args) unless cmd.nil?
  end
  def table(db, table_name)
    table = db[table_name]
    if table.nil? then raise invalid(sql) else table end
  end
  def invalid(sql)
    "invalid SQL: #{sql}"
  end
end

class Table
  attr_reader :db, :name, :columns
  def initialize(db, name, cols, option)
    @db = db
    @name = name
    @columns = cols.collect do |c| c.to_dbsymbol end.freeze
    @rows = []
  end
  def column_index(col)
    return col if col.kind_of?(Numeric) and 0 <= col and col < @columns.size
    index = @columns.index(col.to_dbsymbol)
    if index.nil? then raise "unknown column: #{col}" else index end
  end
  def select(cond = true, cols = @columns)
    selected = @rows.select(&evaluator(cond))
    if cols.nil?
      selected.collect do |row| row.dup end
    else
      indexes = cols.collect do |col| column_index(col) end
      selected.collect do |row| row.values_at(*indexes) end
    end
  end
  def update(assign, cond = true)
    targets = @rows.select(&evaluator(cond))
    targets.each(&evaluator(assign, true)).size
  end
  def insert(vals, cols = @columns)
    row = Array.new(cols.size)
    if vals.kind_of?(Hash) and cols === @columns
      record = vals
    elsif vals.kind_of?(Array) and cols.kind_of?(Array)
      record = Hash[*cols[0...vals.size].zip(vals).flatten]
    else
      raise "invalid row type: #{ vals.class }, #{ cols.class }"
    end
    record.each do |col, val|
      k = col.to_dbsymbol
      raise "invalid column: #{col}" unless @columns.include?(k)
      row[@columns.index(k)] = val.to_s
    end
    @rows << row
    row
  end
  alias << insert
  def delete(cond = true)
    result = []
    proc = evaluator(cond)
    @rows.reject! do |row|
      result << row if deleted = proc.call(row)
      deleted
    end
    result
  end
  def evaluator(cond, do_assign = false)
    s = cond.to_s.split(/('[^']*'|\s+|[!<>]=|[\(\)=,<>]|[\w_]+)/).collect do |token|
      unless token.strip.empty? or token[/^'.*'$/]
        symbol = token.to_dbsymbol
        token = "row[#{@columns.index(symbol)}]" if @columns.include?(symbol)
        token = "==" if !do_assign and token == "="
        token = ";" if do_assign and token == ","
      end
      token
    end.join
    Proc.new do |row| eval(s) end
  end
end

class Database
  include SQL
  def initialize
    @tables = Hash.new
  end
  def [](name)
    @tables[name.to_dbsymbol]
  end
  def create_table(name, cols, option = {})
    name = name.to_dbsymbol
    @tables[name] = Table.new(self, name, cols, option)
  end
end

class Object
  def to_dbsymbol
    self.to_s.upcase.to_sym
  end
end

いくつかの見本。

require "./database.rb"
require "pp"

table = Database.new.create_table "stock", %w[ id name count ]

table << %w[ A1 HDD ]
table << %w[ A2 CDD 3 ]
table << %w[ A3 monitor 2 XXX ]
table << {:id => "H1", :name => "keyboard", :count => 10 }
table << {:id => "H2", :count => 1, :name => "SDRAM" }

db = table.db

pp table.select( <<QUERY, [:name, 2] )

  id[0,1] = 'A'

QUERY

db.sql( <<SQL )

  INSERT Into  stock  (id,count, name )  valuES ('A6', 37, 'CPU')

SQL

db.sql( <<SQL )

  DELETE from stock WHERE  id = 'A3'

SQL

db.sql( <<SQL )

  create table test (id, name, age)

SQL

pp db

見本の結果。

$ ruby databasetest.rb
[["HDD", nil], ["CDD", "3"], ["monitor", "2"]]
#<Database:0x100edfd0
 @tables=
  {:TEST=>
    #<Table:0x7ff964c8
     @columns=[:"ID, NAME, AGE"],
     @db=#<Database:0x100edfd0 ...>,
     @name=:TEST,
     @rows=[]>,
   :STOCK=>
    #<Table:0x7ff9aa40
     @columns=[:ID, :NAME, :COUNT],
     @db=#<Database:0x100edfd0 ...>,
     @name=:STOCK,
     @rows=
      [["A1", "HDD", nil],
       ["A2", "CDD", "3"],
       ["H1", "keyboard", "10"],
       ["H2", "SDRAM", "1"],
       ["A6", "CPU", "37"]]>}>
$

argius note

プログラミング関連

簡易データベースの実装