概要
後でsqliteのデータを追加したくなった時の対処法
記述
当初のデータは
bob% cat lib/recorder/db.rb
#encoding: utf-8
require 'fileutils'
require 'active_record'
module Recorder
module DB
def self.create_table_if_not_exists(path)
create_database_path path
connection = ActiveRecord::Base.connection
return if connection.table_exists?(:data)
connection.create_table :data do |d|
d.column :weight, :real, null: false
d.column :bodyfat, :real, null: true
d.column :date, :text, null: true
d.timestamps
end
connection.add_index :data, :created_at
connection.add_index :data, :date
end
end
end
であったが,memoを追加したくなった.
ActiveRecordで動的に追加することも可能なようであるが,railsによるmigrationを用いたやり方しか見つけられなかった.そこで,sqliteを直接いじって,関連するcodeを修正することにした.
sqlite3
tableの確認
bob% sqlite3 ~/.recorder/recorder.sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .tables
data
sqlite> .headers ON
sqlite> .mode columns
sqlite> PRAGMA table_info(data);
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 id INTEGER 1 1
1 weight real 1 0
2 bodyfat real 0 0
3 date text 0 0
4 created_at datetime 1 0
5 updated_at datetime 1 0
sqlite> SELECT type, name, tbl_name FROM sqlite_master WHERE type='index';
type name tbl_name
---------- ------------------------ ----------
index index_data_on_created_at data
index index_data_on_date data
sqlite> .quit
memoの追加
sqlite> alter table data add column memo text;
で追加できる.
以下は,確認も含めた一連のコマンド.
sqlite> .schema data
CREATE TABLE "data" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "weight" real NOT NULL, "bodyfat" real, "date" text, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
CREATE INDEX "index_data_on_created_at" ON "data" ("created_at");
CREATE INDEX "index_data_on_date" ON "data" ("date");
sqlite> alter table data add column memo text;
sqlite> .schema data
CREATE TABLE "data" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "weight" real NOT NULL, "bodyfat" real, "date" text, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, memo text);
CREATE INDEX "index_data_on_created_at" ON "data" ("created_at");
CREATE INDEX "index_data_on_date" ON "data" ("date");
sqlite> PRAGMA table_info(data);
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 id INTEGER 1 1
1 weight real 1 0
2 bodyfat real 0 0
3 date text 0 0
4 created_at datetime 1 0
5 updated_at datetime 1 0
6 memo text 0 0
sqlite> .quit
weight-recorderの修正
以降の修正は,
bundle exec rackup
で確認しながらすべき.
lib/recorder/db.rb
d.column :memo, :text, null: true
lib/application.rb
data.update_attributes!(
weight: params[:weight],
bodyfat: params[:bodyfat],
date: params[:date],
memo: params[:memo]
)
lib/view/index.html
以下を追加.
%th Memo
%td= d.memo
lib/view/_form.haml
.control-group
%label.col-sm-2.control-label(for='memo') Memo
.col-sm-4
%input.form-control(name='memo' placehoder='' type='text'){value: @data.memo}