Ruby on Rails 5.0 でMariaDBを扱う

前回の続きです。
rubyお勉強を続けます。

1. MariaDBのインストール

1.1 本体

こちらを参考にMariaDBを入れます。何も説明する事はないくらい簡単です。

[root@localhost share]# yum -y install mariadb-server mariadb
..........
完了しました!
[root@localhost share]# yum -y install mariadb-devel.x86_64
..........
完了しました!

[root@localhost share]# systemctl start mariadb
[root@localhost share]# systemctl enable mariadb
..........
 8月 03 17:42:15 localhost.localdomain systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost share]# mysql
..........
MariaDB [(none)]> show databases;
..........

1.2 gem追加

こちらも何事もなく終了します。

[root@localhost my1stAPI]# vi Gemfile
# for MariaDB
gem 'mysql2'

[root@localhost my1stAPI]# bundle install --path vendor/bundle
..........
Bundle complete! 9 Gemfile dependencies, 49 gems now installed.
Bundled gems are installed into ./vendor/bundle.

2 configuration

前回作ったmy1stAPIにDB接続情報を書き足します。

# 2.1 ユーザー作成

MariaDB [(none)]> create user 'railsuser'@'localhost' identified by 'railspass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'railsuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.1 DB接続設定

DBを作成します。mysqlにログインするとcreate databaseされている事が確認できます。

[root@localhost my1stAPI]# rake db:create
Created database 'example'
Database 'example' already exists

database.ymlを編集します

my1stAPI/config/database.yml

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
default: &default
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: example
  pool: 5
  username: railsuser
  password: railspass
  host: localhost

development:
  <<: *default

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default

production:
  <<: *default

3. DBアプリを作ってみよう

こちらを参考にrailsコマンドでテーブル作成を行います。DAOまで一気に作っちゃいましょう。
今回はmecabのユーザー辞書を追加して吐き出すツールが欲しかったので登録用APIを作成します。
こちらをみながらテーブルを作成します。

3.1 モデル作成

1) rake db:create コマンドで.ymlに記載のDB作成
2) rails generate modelコマンドでデータ・モデルの作成
3) rake db:migrate　コマンドでmodelに記載のテーブル作成

表層形	左文脈ID	右文脈ID	コスト	品詞	品詞細分類1	品詞細分類2	品詞細分類3	活用形	活用型	原形	読み	発音
sTitle	iLeft_id	iRight_id	iCost	sClassification	sClass1	sClass2	sClass3	sConjugation	sConjugation_type	sOriginal	sPronunciation1	sPronunciation2
焼肉定食			1	名詞	一般	*	*	*	*	焼肉定食	ヤキニクテイショク	ヤキニクテイショク

[admin@localhost my1stAPI]$ rails generate model Mecabuserdic sTitle:string:index iLeft_id:integer iRight_id:integer iCost:integer sClassification:string sClass1:string sClass2:string sClass3:string sConjugation:string sConjugation_type:string sOriginal:string sPronunciation1:string sPronunciation2:string
Running via Spring preloader in process 4764
      invoke  active_record
      create    db/migrate/20160806060901_create_mecabuserdics.rb
      create    app/models/mecabuserdic.rb
      invoke    test_unit
      create      test/models/mecabuserdic_test.rb
      create      test/fixtures/mecabuserdics.yml
[admin@localhost my1stAPI]$ rake db:migrate
== 20160806060901 CreateMecabuserdics: migrating ==============================
-- create_table(:mecabuserdics)
   -> 0.0075s
-- add_index(:mecabuserdics, :sTitle)
   -> 0.0170s
== 20160806060901 CreateMecabuserdics: migrated (0.0247s) =====================

[admin@localhost my1stAPI]$ rails console
Running via Spring preloader in process 4842
Loading development environment (Rails 5.0.0)
irb(main):001:0> Mecabuserdic
=> Mecabuserdic (call 'Mecabuserdic.connection' to establish a connection)
irb(main):002:0> Mecabuserdic.new
=> #<Mecabuserdic id: nil, sTitle: nil, iLeft_id: nil, iRight_id: nil, iCost: nil, sClassification: nil, sClass1: nil, sClass2: nil, sClass3: nil, sConjugation: nil, sConjugation_type: nil, sOriginal: nil, sPronunciation1: nil, sPronunciation2: nil, created_at: nil, updated_at: nil>
irb(main):006:0* record=Mecabuserdic.new(sTitle: 'カツ丼定食',iCost: 1, sClassification: '名詞', sClass1: '一般', sClass2: '*', sClass3: '*', sConjugation: '*', sConjugation_type: '*', sOriginal: 'カツ丼定食', sPronunciation1: 'カツドンテイショク', sPronunciation2: 'カツドンテイショク')
=> #<Mecabuserdic id: nil, sTitle: "カツ丼定食", iLeft_id: nil, iRight_id: nil, iCost: 1, sClassification: "名詞", sClass1: "一般", sClass2: "*", sClass3: "*", sConjugation: "*", sConjugation_type: "*", sOriginal: "カツ丼定食", sPronunciation1: "カツドンテイショク", sPronunciation2: "カツドンテイショク", created_at: nil, updated_at: nil>
irb(main):007:0> record.valid?
=> true
irb(main):008:0> record.save
   (0.2ms)  BEGIN
  SQL (0.3ms)  INSERT INTO `mecabuserdics` (`sTitle`, `iCost`, `sClassification`, `sClass1`, `sClass2`, `sClass3`, `sConjugation`, `sConjugation_type`, `sOriginal`, `sPronunciation1`, `sPronunciation2`, `created_at`, `updated_at`) VALUES ('カツ丼定食', 1, '名詞', '一般', '*', '*', '*', '*', 'カツ丼定食', 'カツドンテイショク', 'カツドンテイショク', '2016-08-06 06:14:46', '2016-08-06 06:14:46')
   (1.9ms)  COMMIT
=> true
irb(main):012:0* Mecabuserdic.all
  Mecabuserdic Load (0.4ms)  SELECT `mecabuserdics`.* FROM `mecabuserdics`
=> #<ActiveRecord::Relation [#<Mecabuserdic id: 1, sTitle: "カツ丼定食", iLeft_id: nil, iRight_id: nil, iCost: 1, sClassification: "名詞", sClass1: "一般", sClass2: "*", sClass3: "*", sConjugation: "*", sConjugation_type: "*", sOriginal: "カツ丼定食", sPronunciation1: "カツドンテイショク", sPronunciation2: "カツドンテイショク", created_at: "2016-08-06 06:14:46", updated_at: "2016-08-06 06:14:46">]>

3.2 アプリ内にモデルを組み込み

こちらを参考にmodelのデータを更新するサンプルを作って見ました。
validationチェックとか楽にやる方法を探したいですね。

1) コントローラー追加
2) configfileに追記
3) コントローラーからモデル呼び出し

[admin@localhost my1stAPI]$ rails generate controller Mecabuserdic new create update edit destroy index show
Running via Spring preloader in process 5060
    conflict  app/controllers/mecabuserdic_controller.rb
Overwrite /home/admin/share/HelloRuby/my1stAPI/app/controllers/mecabuserdic_controller.rb? (enter "h" for help) [Ynaqdh] Y
       force  app/controllers/mecabuserdic_controller.rb
       route  get 'mecabuserdic/show'
       route  get 'mecabuserdic/index'
       route  get 'mecabuserdic/destroy'
       route  get 'mecabuserdic/edit'
       route  get 'mecabuserdic/update'
       route  get 'mecabuserdic/create'
       route  get 'mecabuserdic/new'
      invoke  test_unit
    conflict    test/controllers/mecabuserdic_controller_test.rb
  Overwrite /home/admin/share/HelloRuby/my1stAPI/test/controllers/mecabuserdic_controller_test.rb? (enter "h" for help) [Ynaqdh] Y
       force    test/controllers/mecabuserdic_controller_test.rb

APIなのでキャッシュをしない設定にします。
(環境別のファイルに書いたほうがよしconfig/environments/development.rb)

config/application.rb

module My1stAPI
  class Application < Rails::Application
.......................
  config.action_controller.perform_caching  = false
  end
end

ルーティングファイルをアップデートしてサーバー再起動

config/routes.rb

Rails.application.routes.draw do
  get 'mecabuserdic/show' => 'mecabuserdic#show'

  get 'mecabuserdic/new' => 'mecabuserdic#new'

  get 'mecabuserdic/create' => 'mecabuserdic#create'

  get 'mecabuserdic/update' => 'mecabuserdic#update'

  get 'mecabuserdic/edit' => 'mecabuserdic#edit'

  get 'mecabuserdic/destroy' => 'mecabuserdic#destroy'

  get 'mecabuserdic/index' => 'mecabuserdic#index'

  # For details on the DSL available within this file, see http://guides.rubyonrails.org/routing.html
  get 'example2' => 'example2#show'
  get 'example2/show2' => 'example2#show2'
end

app/controllers/mecabuserdic_controller.rb

class MecabuserdicController < ApplicationController
  before_action :idcheck, only: [:update, :destroy]

  def initialize
    @targets = Array["sTitle","iCost","sClassification","sClass1","sClass2","sClass3","sConjugation","sConjugation_type","sOriginal","sPronunciation1","sPronunciation2"]
  end

  def new
    render json: Mecabuserdic.new
  end

  def create
      record = {}
      @targets.each do |key, val|
        pval = params[key]
        Rails.logger.warn "Param #{key}: #{pval}"
        if params.has_key?(key)
          sprintf("exists: %s = %s",key,pval)
          record[key] = val
        end
      end

      colms = Mecabuserdic.new(record)
      if colms.valid?
        colms.save
      end
      render json: params
  end

  def update
    selected = Mecabuserdic.find(@id)

    @targets.each do |key, val|
      if params.has_key?(key)
        eval(sprintf("selected.%s = '%s'",key,params[key]))
      end
    end
    selected.save
    render json:selected
  end

  def edit
    testresult = [{"error":"not implemented"}]
    render json: testresult
  end

  def destroy
    selected = Mecabuserdic.find(@id)
    selected.destroy
    render json: selected
  end

  def index
    testresult = [{"error":"not implemented"}]
    render json: testresult
  end

  def show
    render json: Mecabuserdic.all
  end

  private
  def idcheck()
    if !params.has_key?("id")
      showerr(sprintf("validation error %s " ,params["id"]))
    else
      @id = params["id"]
    end
  end

  def showerr(msg)
    #Rails.logger.warn "validation error: "+ msg
    render json: {"msg": "validation error: "+ msg} and return
  end

end

リクエストサンプル

行のフィールド確認

http://192.168.10.5:3000/mecabuserdic/new

新規作成

http://192.168.10.5:3000/mecabuserdic/create?sTitle=天丼定食&iCost=1&sClassification=名詞&sClass1=一般&sClass2=*&sClass3=*&sConjugation=*&sConjugation_type=*&sOriginal=天丼定食&sPronunciation1=テンドンテイショク&sPronunciation2=テンドンテイショク

更新

http://192.168.10.5:3000/mecabuserdic/update?id=980190966&sTitle=親子丼定食&iCost=1&sClassification=名詞&sClass1=一般&sClass2=*&sClass3=*&sConjugation=*&sConjugation_type=*&sOriginal=親子丼定食&sPronunciation1=オヤコドンテイショク&sPronunciation2=オヤコドンテイショク

削除

http://192.168.10.5:3000/mecabuserdic/destroy?id=980190966

一覧

http://192.168.10.5:3000/mecabuserdic/show