lib/ezframe/database.rb in ezframe-0.1.1 vs lib/ezframe/database.rb in ezframe-0.2.0
- old
+ new
@@ -1,62 +1,174 @@
# frozen_string_literal: true
require "logger"
module Ezframe
- class Database
- attr_accessor :sequel
+ class DB
+ class << self
+ attr_accessor :sequel, :pool
- def initialize(dbfile = nil)
- @dbfile = dbfile
- connect
- end
+ def init(dbfile = nil, opts = {})
+ @dbfile = dbfile || ENV["EZFRAME_DB"] || Config[:database]
+ if Config[:use_connection_pool] || opts[:use_connection_pool]
+ @pool = Sequel::ConnectionPool(max_connections: 10) do
+ Sequel.connect(@dbfile, loggers: [Logger])
+ end
+ else
+ connect(@dbfile)
+ end
+ end
- def connect
- @dbfile ||= ENV["EZFRAME_DB"] || Config[:database] || "sqlite://db/devel.sqlite"
- # puts "Database.connect: dbfile=#{@dbfile}"
- @sequel = Sequel.connect(@dbfile, loggers: [Logger.new($stdout)])
- end
+ def connect(dbfile = nil)
+ dbfile ||= @dbfile
+ @sequel = Sequel.connect(dbfile, loggers: [Logger])
+ return @sequel
+ end
- def exec(sql)
- @sequel.run(sql)
- end
+ def disconnect
+ @sequel.disconnect
+ end
- def dataset(table_name)
- @sequel[table_name.to_sym]
- end
-
- def create_table(table_name, dbtype_h)
- %w[id created_at updated_at].each do |key|
- dbtype_h.delete(key.to_sym)
+ def get_conn
+ if @pool
+ @pool.hold {|conn| return conn }
+ else
+ @sequel
+ end
end
- # puts "create_table: #{table_name}"
- if @dbfile.index("postgres")
- @sequel.create_table(table_name) do
- primary_key :id, identity: true
- dbtype_h.each do |key, dbtype|
- column(key, dbtype)
+
+ def exec(sql, first: nil)
+ conn = get_conn
+ if first
+ return conn[sql].first
+ else
+ return conn[sql].all
+ end
+ end
+
+ def run(sql)
+ conn = get_conn
+ conn.run(sql)
+ end
+
+ def dataset(table_name)
+ @sequel[table_name.to_sym]
+ end
+
+ class JointHash < Hash
+ def initialize(default_table, values = {})
+ @default_table = default_table
+ self.update(values)
+ end
+
+ def []=(key, value)
+ super(key.to_s, value)
+ end
+
+ def [](key)
+ key = key.to_s
+ return fetch(key) if has_key?(key)
+ alt_key = "#{@default_table}.#{key}"
+ return fetch(alt_key) if has_key?(alt_key)
+ return nil
+ end
+ end
+
+ # テーブルを連結して、全てのデータを返す。
+ def get_join_table(structure, opts = {})
+ col_h = {}
+ reverse_col_h = {}
+ query_a = []
+ table_a = []
+ prefix="_x_"
+ structure[:column_list].each_with_index do |k, i|
+ key = "#{prefix}#{i+1}"
+ col_h[k.to_sym] = key.to_sym
+ reverse_col_h[key.to_sym] = k
+ query_a.push "#{k} AS #{key}"
+ end
+ tables = structure[:tables].clone
+ join_cond = structure[:join_condition]
+ tb = tables.shift
+ table_part = [ tb ]
+ tables.each do |table|
+ cond = join_cond[table.to_sym]
+ if cond
+ table_part.push " LEFT JOIN #{table} ON #{cond}"
+ else
+ table_part.push " LEFT JOIN #{table} ON #{tb}.#{table} = #{table}.id"
end
- column(:created_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
- column(:updated_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
end
- else
- @sequel.create_table(table_name) do
- primary_key :id, auto_increment: true
- dbtype_h.each do |key, dbtype|
- column(key, dbtype)
+ sql = "SELECT #{query_a.join(', ')} FROM #{table_part.join(' ')}"
+ sql += " WHERE #{opts[:where]}" if opts[:where]
+ sql += " ORDER BY #{opts[:order]}" if opts[:order]
+ sql += " LIMIT #{opts[:limit]}" if opts[:limit]
+ data_a = self.exec(sql)
+ res_a = []
+ data_a.each do |data|
+ new_data = JointHash.new(tb)
+ data.each do |k, v|
+ orig_key = reverse_col_h[k.to_sym]
+ next unless orig_key
+ new_data[orig_key] = v
end
- column(:created_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
- column(:updated_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
+ res_a.push(new_data)
end
+ return res_a
end
+
+ # テーブル生成
+ def create_table(table_name, dbtype_h)
+ %w[id created_at updated_at].each do |key|
+ dbtype_h.delete(key.to_sym)
+ end
+ # puts "create_table: #{table_name}"
+ if @dbfile.index("postgres")
+ @sequel.create_table(table_name) do
+ primary_key :id, identity: true
+ dbtype_h.each do |key, dbtype|
+ column(key, dbtype)
+ end
+ column(:created_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
+ column(:updated_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
+ end
+ else
+ @sequel.create_table(table_name) do
+ primary_key :id, auto_increment: true
+ dbtype_h.each do |key, dbtype|
+ column(key, dbtype)
+ end
+ column(:created_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
+ column(:updated_at, :timestamp, default: Sequel::CURRENT_TIMESTAMP)
+ end
+ end
+ end
+
+ def insert(table_name, val_h)
+ dataset(table_name).insert(val_h)
+ end
+
+ def update(dataset, val_h)
+ val_h.update({ updated_at: Time.now() })
+ dataset.update(val_h)
+ end
end
-
- def insert(table_name, val_h)
- dataset(table_name).insert(val_h)
- end
- def update(dataset, val_h)
- val_h.update({ updated_at: Time.now() })
- dataset.update(val_h)
- end
+ class Cache
+ class << self
+
+ def [](table)
+ @store ||= {}
+ dataset = DB.dataset(table.to_sym)
+ # Logger.debug("DB::Cache: #{table}")
+ unless @store[table.to_sym]
+ data_a = dataset.all
+ h = {}
+ data_a.each {|data| h[data[:id]] = data }
+ @store[table.to_sym] = h
+ end
+ # Logger.debug(@store[table.to_sym])
+ return @store[table.to_sym]
+ end
+ end
+ end
end
end