exe/dbmigrate in ezframe-0.2.0 vs exe/dbmigrate in ezframe-0.3.0
- old
+ new
@@ -1,118 +1,140 @@
#!/usr/bin/env ruby
#coding: utf-8
require "ezframe"
include Ezframe
-class DBInfo
- class << self
- def get_db_info(table_name)
- typ = get_database_type(Config[:database])
- case typ
- when "postgresql"
- return get_info_from_postgres(table_name)
- when "sqlite"
- return get_info_from_sqlite(table_name)
- else
- raise "unknown database"
+module Ezframe
+ module Model
+ class DBInfo
+ def initialize
+ @db_columns = {}
+ @db_typ = get_database_type(Config[:database])
+ case @db_typ
+ when "postgresql"
+ get_info_from_postgres
+ when "sqlite"
+ get_info_from_sqlite
+ else
+ raise "unknown database system"
+ end
end
- return nil
- end
- def get_info_from_postgres(table_name)
- dbcol = {}
- sql = "SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '#{table_name}'"
- begin
- data_a = DB.sequel[sql].all
- rescue
- return nil
+ def get_db_info(table_name)
+ return @db_columns[table_name.to_sym]
end
- # puts data_a
- data_a.each do |row|
- dbcol[row[:column_name].to_s.to_sym] = normalize_dbtype(row[:data_type])
+
+ def get_info_from_postgres
+ sql = "SELECT * FROM information_schema.columns"
+ begin
+ data_a = DB.sequel[sql].all
+ rescue => e
+ EzLog.error("get_info_from_postgres: #{e}")
+ return nil
+ end
+ data_a.each do |row|
+ table_name, col_name = row[:table_name], row[:column_name]
+ next if col_name.nil? || table_name.nil?
+ next unless ColumnSets.refer(table_name)
+ @db_columns[table_name.to_sym] ||= {}
+ @db_columns[table_name.to_sym][col_name.to_sym] = normalize_dbtype(row[:data_type])
+ end
end
- return dbcol
- end
- def get_info_from_sqlite(table_name)
- sql = "SELECT * from sqlite_master WHERE name = '#{table_name}';"
- data = DB.sequel[sql].first
- return nil unless data
- sql = data[:sql]
- return parse_create_sql(sql)
- end
+ def get_info_from_sqlite
+ sql = "SELECT * from sqlite_master;"
+ data_a = DB.sequel[sql].all
+ return nil unless data_a
+ data_a.each do |data|
+ sql = data[:sql]
+ parse_create_sql(sql)
+ end
+ end
- def parse_create_sql(sql)
- sql.gsub(/CREATE TABLE \`\w+\` \(/, "")
- column_a = sql.split(", ")
- dbcol = {}
- column_a.each do |line|
- if line =~ /\`(\w+)\`\s(\w+)/
- colkey, dbtype = $1, $2
- dbcol[colkey.to_sym] = dbtype
+ def parse_create_sql(sql)
+ sql.gsub(/CREATE TABLE \`(\w+)\` \(/i, "")
+ table_name = $1
+ @db_columns[table_name.to_sym] ||= {}
+ column_a = sql.split(", ")
+ column_a.each do |line|
+ if line =~ /\`(\w+)\`\s(\w+)/
+ colkey, dbtype = $1, $2
+ @db_columns[table_name.to_sym][colkey.to_sym] = dbtype
+ end
end
end
- return dbcol
- end
- def get_database_type(config)
- return config.split("://")[0]
- end
- end
-end
+ def get_database_type(config)
+ return config.split("://")[0]
+ end
-def exec_sql(sql)
- begin
- DB.sequel.run(sql)
- rescue => exception
- p exception
- end
-end
+ def exec_sql(sql)
+ begin
+ DB.sequel.run(sql)
+ rescue => e
+ EzLog.error("dbmigrate: exec_sql: #{e}")
+ end
+ end
-def normalize_dbtype(dbtype)
- return nil unless dbtype
- return "int" if dbtype == "integer"
- return "timestamp" if dbtype.index("timestamp")
- return dbtype
-end
+ def normalize_dbtype(dbtype)
+ return nil unless dbtype
+ return "int" if dbtype == "integer"
+ return "timestamp" if dbtype.index("timestamp")
+ return dbtype
+ end
-def set_default(column)
- default_value = column.attribute[:default]
- if default_value
- unless %w[int].include?(column.db_type)
- default_value = "'#{default_value}'"
- end
- exec_sql("ALTER TABLE #{column.parent.name} ALTER #{column.key} SET DEFAULT #{default_value}")
- end
-end
+ def set_default(column)
+ default_value = column.attribute[:default]
+ if default_value
+ unless %w[int].include?(column.db_type)
+ default_value = "'#{default_value}'"
+ end
+ exec_sql("ALTER TABLE #{column.parent.name} ALTER #{column.key} SET DEFAULT #{default_value}")
+ end
+ end
-def check_diff(column_set)
- table_name = column_set.name
- dbcols = DBInfo.get_db_info(table_name)
- unless dbcols
- ColumnSets.create_one_table(table_name, ColumnSets.get(table_name.to_sym))
- return
- end
- column_set.each do |column|
- next if %w[id updated_at created_at].include?(column.key.to_s)
- dbtype = dbcols[column.key]
- new_type = column.db_type
- unless dbtype
- exec_sql("ALTER TABLE #{table_name} ADD #{column.key} #{new_type};")
- set_default(column)
- next
+ def check_diff(column_set)
+ table_name = column_set.name
+ dbcols = get_db_info(table_name)
+ unless dbcols
+ ColumnSets.create_one_table(table_name, ColumnSets.get(table_name.to_sym))
+ return
+ end
+ column_set.each do |column|
+ next if %w[id updated_at created_at deleted_at].include?(column.key.to_s)
+ dbtype = dbcols[column.key]
+ new_type = column.db_type
+ if !new_type || new_type.empty?
+ EzLog.error("check_diff: no db_type: table=#{table_name}:key=#{column.key}")
+ next
+ end
+ unless dbtype
+ exec_sql("ALTER TABLE #{table_name} ADD #{column.key} #{new_type};")
+ set_default(column)
+ next
+ end
+ if dbtype != new_type
+ exec_sql("ALTER TABLE #{table_name} ALTER COLUMN #{column.key} DROP DEFAULT;")
+ begin
+ exec_sql("ALTER TABLE #{table_name} ALTER #{column.key} TYPE #{new_type};")
+ rescue => e
+ EzLog.error("alter column type error: #{e.class}:#{e}:#{e.backtrace}")
+ back_key = "#{column.key}_bak"
+ exec_sql("ALTER TABLE #{table_name} RENAME #{column.key} TO #{back_key};")
+ end
+ set_default(column)
+ end
+ end
+ # exec_sql("ALTER TABLE #{table_name} add COLUMN deleted_at timestamp;")
+ end
end
- if dbtype != new_type
- exec_sql("ALTER TABLE #{table_name} ALTER COLUMN #{column.key} DROP DEFAULT;")
- exec_sql("ALTER TABLE #{table_name} ALTER #{column.key} TYPE #{new_type};")
- set_default(column)
- end
end
end
Config.init
DB.init
ColumnSets.init
+dbinfo = Model::DBInfo.new
ColumnSets.each do |table_name, column_set|
- check_diff(column_set)
+ dbinfo.check_diff(column_set)
end