rake で Mariadb の CRUD

# ------------------------------------------------------------
# task :default => :hello
task default: :hello

desc "hello"
task :hello, 'firstname', 'lastname'
task :hello do |task,args|
    puts "*** start ***"
    puts "Hello Rake!!"
    puts "Hello, #{args['firstname']} #{args['lastname']}"
    puts "Firstname, #{args['firstname']}"
    puts "Lastname, #{args['lastname']}"
    puts "Hello, #{args['firstname']} #{args['lastname']}"
    puts "*** こんにちは ***"
    puts "*** end ***"
end
#
# ------------------------------------------------------------
desc "create"
task :create do
    require 'mysql'
    puts "*** 開始 ***"
#
    host = "127.0.0.1"
    user = "scott"
    password = "tiger123"
    data_base = 'city'
    connection = Mysql::new(host, user,password,data_base)
#
    dict_aa=prepare_data_proc()
#
    drop_proc(connection)
    create_proc(connection)
    dict_aa.each {|key,value |
        insert_proc(connection,key,value['name'], \
            value['population'],value['date_mod'])
        }
#
    connection.close
#
    puts    "*** 終了 ***"
#
end
#
# ------------------------------------------------------------
def prepare_data_proc ()
    dict_aa = {}
dict_aa=dict_append_proc(dict_aa,'t3321',"岡山",729138,"2006-9-14")
dict_aa=dict_append_proc(dict_aa,'t3322',"倉敷",318475,"2006-2-27")
dict_aa=dict_append_proc(dict_aa,'t3323',"津山",182564,"2006-8-8")
dict_aa=dict_append_proc(dict_aa,'t3324',"玉野",572148,"2006-11-15")
dict_aa=dict_append_proc(dict_aa,'t3325',"笠岡",893157,"2006-7-21")
dict_aa=dict_append_proc(dict_aa,'t3326',"井原",167589,"2006-9-7")
dict_aa=dict_append_proc(dict_aa,'t3327',"総社",241937,"2006-4-8")
dict_aa=dict_append_proc(dict_aa,'t3328',"高梁",438129,"2006-10-12")
dict_aa=dict_append_proc(dict_aa,'t3329',"新見",519472,"2006-6-9")
    return  dict_aa
end
# ---------------------------------------------------------------------
def dict_append_proc (dict_aa,id,name,population,date_mod)
    unit = {}
    unit['name'] = name
    unit['population'] = population
    unit['date_mod'] = date_mod
    key = id.to_s
    dict_aa[key] = unit
    return dict_aa
end
# ---------------------------------------------------------------------
def create_proc (connection)
    sql_str="create TABLE cities (" \
        + "id varchar(10) NOT NULL PRIMARY KEY," \
        + "name varchar(20)," \
        + "population int," \
        + "date_mod varchar(40))"
    connection.query(sql_str)
end
# ------------------------------------------------------------
def drop_proc (connection)
    sql_str="drop table cities"
    connection.query(sql_str)
end
# ------------------------------------------------------------
def insert_proc (connection,id,name,population,date_mod)
    sql_str="INSERT into cities " \
        + "(id, Name, Population, date_mod) values \
        ('#{id}', '#{name}',#{population},'#{date_mod}')"
    connection.query(sql_str)
end
# ------------------------------------------------------------
desc "read"
task :read do
    require 'mysql'
    puts "*** 開始 ***"
#
    host = "127.0.0.1"
    user = "scott"
    password = "tiger123"
    data_base = 'city'
    connection = Mysql::new(host, user,password,data_base)

    sql_str = "SELECT id,name,population,date_mod FROM cities order by ID"
    begin
        result = connection.query(sql_str)
        result.each do |row|
            print "#{row[0]}\t#{row[1]}\t#{row[2]}\t#{row[3]}\n"
        end
    end
#
    connection.close
#
    puts "*** 終了 ***"
end
# ------------------------------------------------------------
desc "update"
task :update, 'key', 'population'
task :update do |task,args|
    require 'mysql'
    require 'date'
    puts "*** 開始 ***"
#
    key_in = "#{args['key']}"
    population_in = "#{args['population']}"
#
    puts key_in
    puts population_in
#
    host = "127.0.0.1"
    user = "scott"
    password = "tiger123"
    data_base = 'city'
    connection = Mysql::new(host, user,password,data_base)
#
    update_proc(connection,key_in,population_in)
#
    connection.commit
    connection.close
#
    puts "*** 終了 ***"
end
#
def update_proc (connection,id,population)
    date_mod=Date.today
    sql_str="UPDATE cities SET population='#{population}', DATE_MOD='#{date_mod}' where ID = '#{id}'"
    connection.query(sql_str)
end
#
# ------------------------------------------------------------
desc "delete"
task :delete, 'key'
task :delete do |task,args|
    require 'mysql'
    puts "*** 開始 ***"
#
    key_in = "#{args['key']}"
#
    puts key_in
#
    host = "127.0.0.1"
    user = "scott"
    password = "tiger123"
    data_base = 'city'
    connection = Mysql::new(host, user,password,data_base)
#
    delete_proc(connection,key_in)
#
    connection.commit
    connection.close
#
    puts "*** 終了 ***"
end
#
def delete_proc (connection,key)
    sql_str="DELETE from cities where ID = '#{key}'"
    connection.query(sql_str)
end
#
# ------------------------------------------------------------

rake create

rake read

rake update[t3324,90000]

rake delete[t3328]