Tep3-onk-2
提供: wikipokpok
目次
準備
プロジェクトディレクトリ作成 移動 バージョン確認
new
% rails new . -d postgresql --css tailwind % bundle install % rails db:create または % rails db:reset % rails db:migrate % rails server
daisyui
tailwind css UIコンポーネント インストール
% yarn add daisyui
config/tailwind.config.js
plugins: [
require('@tailwindcss/forms'),
require('@tailwindcss/aspect-ratio'),
require('@tailwindcss/typography'),
require('daisyui'), // ← 追記する
],
app/views/layouts/application.html.erb
<html data-theme="light"> // テーマを選ぶ
データベース移行
Herokuからdumpしてローカルにheroku_dumpデータベースを新設しリストアする
$ heroku pg:backups:capture $ heroku pg:backups:download $ createdb mydb $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
作り直すアプリはデータベース構造を変えるのでテーブルごとにダンプ・リストアする
データ数確認
=# select count(*) from clients ; count ------- 5067 (1 row)
heroku_dumpデータベースでテーブル名を変える
テーブル毎に変える
=# ALTER TABLE users RENAME TO users_old; =# ALTER TABLE clients RENAME TO clients_old;
旧テーブルダンプ
テーブル毎にダンプする
% pg_dump --username=jq --table users_old heroku_dump > users_old.sql
userテーブル
旧テーブルを新アプリのデータベースにリストア
% psql --username=jq tep5_development < ../old_users.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 17
setval
--------
51
(1 row)
ERROR: relation "users_pkey" already exists
ERROR: relation "index_users_on_email" already exists
CREATE INDEX
旧テーブルをセレクトして新テーブルにインサートする
=# insert into users
(
id, name, email, created_at, updated_at, password_digest, remember_digest, activation_digest, activated, activated_at, reset_digest, reset_sent_at
)
select
id, name, email, created_at, updated_at, password_digest, remember_digest, activation_digest, activated, activated_at, reset_digest, reset_sent_at
from
users_old;
INSERT 0 17
noteテーブル
旧jiinテーブルをリストアする
% psql --username=jq tep5_development < ../old_jiins.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 14
setval
--------
14
(1 row)
ALTER TABLE
CREATE INDEX
ERROR: there is no unique constraint matching given keys for referenced table "users_old"
noteテーブルにインサートする
=# insert into notes
(
id, content, user_id, created_at, updated_at, kind
)
select
id, jiin_name, user_id, created_at, updated_at, kind
from
jiins;
INSERT 0 14
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
clientsテーブル othersテーブル
% pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql
旧clientsテーブルをリストアする
% psql --username=jq tep5_development < ../old_other.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET SET SET CREATE TABLE ALTER TABLE ERROR: relation "clients_id_seq" already exists ALTER TABLE ALTER SEQUENCE ALTER TABLE COPY 5067 setval -------- 5351 (1 row) ERROR: relation "clients_pkey" already exists CREATE INDEX ERROR: relation "index_clients_on_user_id" already exists CREATE INDEX CREATE INDEX ERROR: there is no unique constraint matching given keys for referenced table "users_old" ALTER TABLE
clientsテーブルにインサートする
旧clientsテーブルをclientsとotherに分ける
新clientsテーブルは代表者名テーブルになる 旧clientsテーブルのnusiをセレクトして新clientsテーブルにインサートする nusiのie_idを新clientsテーブルのidにする その前にie_idの重複を修正する
重複確認
=# SELECT ie_id, COUNT(ie_id) FROM clients_old where nusi = true GROUP BY ie_id HAVING COUNT(ie_id) > 1 ORDER BY ie_id; ie_id | count -------+------- 2090 | 2 2129 | 2 2167 | 2 2174 | 2
桁数を変えて重複を解消する
=# update clients_old SET ie_id = ie_id + 20000 where jiin_id = 2; UPDATE 776 =# update clients_old SET ie_id = ie_id + 30000 where jiin_id = 3; UPDATE 790 =# update clients_old SET ie_id = ie_id + 40000 where jiin_id = 4; UPDATE 307
主重複修正
=# update clients_old set nusi = false where id = 3703; =# update clients_old set nusi = false where id = 1346;
不要データ削除 修正
=# delete from clients_old where id in (4028,3911,3912,2998,3007,4359);
tep5_development=# update clients_old set nusi = true where id = 523; UPDATE 1 tep5_development=# delete from clients_old where id = 845; DELETE 1 tep5_development=# update clients_old set ie_id = 1264 where id = 515; UPDATE 1 tep5_development=# update clients_old set ie_id = 1264 where id = 1463; UPDATE 1 tep5_development=# update clients_old set nusi = false where id = 515; UPDATE 1 tep5_development=# delete from clients_old where id = 217; DELETE 1 tep5_development=# update clients_old set nusi = false where id = 218; UPDATE 1 tep5_development=# update clients_old set ie_id = 204 where id = 218; UPDATE 1 tep5_development=# delete from clients_old where id = 811; DELETE 1 tep5_development=# update clients_old set nusi = true where id = 1792; UPDATE 1 tep5_development=# delete from clients_old where id = 810; DELETE 1 tep5_development=# delete from clients_old where ie_id = 22653; DELETE 3
listsテーブルに一つデータをいれる
=# insert into lists (user_id , note_id, content, created_at, updated_at)VALUES (1, 1, 'リスト', '2022-9-18', '2022-9-18');
nusiをセレクトして新clientsにインサート
=# insert into clients
(
id, user_id, note_id, content, client_line, created_at, updated_at
)
select
ie_id, user_id, jiin_id, name, table_order, created_at, updated_at
from
clients_old
where
nusi = true;
INSERT 0 1292
othersテーブルにインサートする
親テーブルie_id 子テーブルie_id 差分削除
旧clientsテーブルに使用されていない重複した氏名がある 新clientsテーブルとclients_oldテーブルの差分をとり削除する
% diff aa_clients_id_ie_id.csv bb_clients_old_ie_id.csv > diff.csv
=# delete from clients_old where ie_id in (21,22,23,24,26,27,30,32,33,34,35,36,39,41,43,44,45,46,48,49,51,52,53,56,57,62,63,64,66,67,68,69,71,73,78,79,80,82,87,90,92,93,94,95,96,97,98,99,100,101,102,104,105,106,108,109,110,111,113,115,116,117,118,121,122,123,124,126,127,128,129,131,132,133,135,136,138,139,140,141,144,145,147,148,150,151,152,153,157,158,159,161,162,166,170,171,173,174,176,177,179,180,182,183,184,185,186,188,189,190,191,192,194,197,199,201,202,205,222,228,230,231,232,249,264,272,310,355,372,373,375,404,461,472,473,490,491,531,550,561,590,649,673,674,676,679,683,686,689,691,694,701,741,754,800,802,804,805,808,814,815,821,848,872,875,876,921,923,936,953,972,993,998,1009,1010,1013,2072,5158,6053)
データ修正したclients_oldテーブルをすべてothersテーブルにインサートする
=# insert into others
(
id, name, kana, client_id, client_name, user_id, note_id, created_at, updated_at, memo, other_line, generation
)
select
id, name, yomi, ie_id, nusi, user_id, jiin_id, created_at, updated_at, memo, each_order, generation
from
clients_old;
INSERT 0 4747
2つのテーブルが同じならコピーされる
jq@MacBook-Pro tep5 % pg_dump --username=jq --table clients update_heroku > ../new_clients.sql jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_clients.sql
jq@MacBook-Pro tep5 % pg_dump --username=jq --table others update_heroku > ../new_others.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_others.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
ERROR: relation "others" already exists
ALTER TABLE
ERROR: relation "others_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 4747
setval
--------
1
(1 row)
ERROR: multiple primary keys for table "others" are not allowed
ERROR: relation "index_others_on_client_id" already exists
ERROR: relation "index_others_on_client_id_and_other_line" already exists
ERROR: relation "index_others_on_note_id" already exists
ERROR: relation "index_others_on_user_id" already exists
ERROR: relation "index_others_on_user_id_and_note_id" already exists
ERROR: constraint "fk_rails_10377da6e2" for relation "others" already exists
ERROR: constraint "fk_rails_26cdbbd90e" for relation "others" already exists
ERROR: constraint "fk_rails_54f6a74f65" for relation "others" already exists
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
placesテーブル
確認
heroku_dump=# select count(*) from places; count ------- 919 (1 row)
テーブル名変更
heroku_dump=# ALTER TABLE places RENAME TO places_old;
dump
% pg_dump --username=jq --table places_old heroku_dump > places_old.sql
新データベースにリストア
% psql --username=jq tep5_development < ../places_old.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET SET SET CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE ALTER TABLE COPY 919 setval -------- 1070 (1 row) ALTER TABLE CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ERROR: relation "public.clients_old" does not exist ERROR: relation "public.users_old" does not exist ERROR: relation "public.jiins" does not exist
tep5_development=# update places_old SET ie_id = ie_id + 20000 where jiin_id = 2; UPDATE 213 tep5_development=# update places_old SET ie_id = ie_id + 30000 where jiin_id = 3; UPDATE 151 tep5_development=# update places_old SET ie_id = ie_id + 40000 where jiin_id = 4; UPDATE 67
=# insert into places (id, user_id, note_id, client_id, zip, ken, city, etc, flag, created_at, updated_at) select id, user_id, jiin_id, ie_id, zip, ken, city, etc, flag, created_at, updated_at from places_old; INSERT 0 918
generate
% rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text
indexを追加 uuid消す
migrationファイル名の日時をenable_extension_for_uuid.rb以前にする
% rails db:migrate:status database: tep5_development Status Migration ID Migration Name -------------------------------------------------- up 20220917020659 Create users up 20220917023135 Create notes up 20220917023510 Create lists up 20220917023750 Create clients up 20220917024207 Create others down 20220917035020 Create places up 20220918004020 Enable extension for uuid up 20220918004824 Change users id type to uuid up 20220918004853 Change notes id type to uuid up 20220918004934 Change lists id type to uuid up 20220918005011 Change clients id type to uuid up 20220918005027 Change others id type to uuid up 20220920062901 Create categories up 20220920063011 Create relationship categories up 20220925035934 Create relationships
すべてdownにする
schema.rbを消す
% rails db:drop
EnableExtensionForUuid以下のmigrationを移動する
% rails db:create
% psql tep5_development < ../202209261601_tep5.sql
% rails db:migrate
schedulesテーブル
確認
heroku_dump=# select count(*) from schedules; count ------- 3873 (1 row)
% rails generate scaffold Schedule user:references note:references client:references datetime:timestamp content:string other_id:integer every_year:boolean goji:integer
% rails db:migrate
リストアして作業データベースにコピーする
% pg_dump --username=jq --table schedules heroku_dump > ../new_schedules.sql % psql --username=jq update_heroku < ../new_schedules.sql
テーブルの変更
update_heroku=# ALTER TABLE schedules RENAME COLUMN jiin_id TO note_id; ALTER TABLE update_heroku=# ALTER TABLE schedules RENAME COLUMN client_id TO other_id; ALTER TABLE update_heroku=# ALTER TABLE schedules RENAME COLUMN ie_id TO client_id; ALTER TABLE update_heroku=# ALTER TABLE schedules DROP COLUMN goji; ALTER TABLE update_heroku=# ALTER TABLE schedules ADD COLUMN goji integer; ALTER TABLE
変更したテーブルをリストアし アプリのデータベースにコピーする
% pg_dump --username=jq --table schedules update_heroku > ../new_schedules.sql % psql --username=jq tep5_development < ../new_schedules.sql
エラー client_idのないものが844ある
update_heroku=# select count(*) from schedules where client_id is null; count ------- 844 (1 row)
そのnote_idを確認し替え玉をつくる
update_heroku=# select distinct on (note_id) note_id from schedules where client_id is null;
note_id
---------
1
2
4
(3 rows)
アプリから替え玉を作って確認
tep5_development=# select * from clients where id = 1; id | user_id | note_id | list_id | content | client_line | memo | created_at | updated_at ----+---------+---------+---------+---------+-------------+------+----------------------------+---------------------------- 1 | 1 | 1 | 1 | 替え玉 | | | 2022-09-27 01:42:10.816742 | 2022-09-27 01:42:10.816742 (1 row) tep5_development=# select * from clients where id = 2; id | user_id | note_id | list_id | content | client_line | memo | created_at | updated_at ----+---------+---------+---------+---------+-------------+------+----------------------------+---------------------------- 2 | 1 | 2 | 3 | 替え玉 | | | 2022-09-27 01:46:11.282471 | 2022-09-27 01:46:11.282471 (1 row) tep5_development=# select * from clients where id = 3; id | user_id | note_id | list_id | content | client_line | memo | created_at | updated_at ----+---------+---------+---------+---------+-------------+------+----------------------------+---------------------------- 3 | 1 | 4 | 4 | 替え玉 | | | 2022-09-27 01:56:31.755069 | 2022-09-27 01:56:31.755069 (1 row)
替え玉のidでschedulesのclient_id null を埋める
update_heroku=# update schedules set client_id = 1 where note_id = 1 and client_id is null; UPDATE 499 update_heroku=# update schedules set client_id = 2 where note_id = 2 and client_id is null; UPDATE 255 update_heroku=# update schedules set client_id = 3 where note_id = 4 and client_id is null; UPDATE 90
新テーブルのclient_idに合わせる
update_heroku=# update schedules SET client_id = client_id + 40000 where note_id = 4; UPDATE 283 update_heroku=# update schedules SET client_id = client_id + 30000 where note_id = 3; UPDATE 427 update_heroku=# update schedules SET client_id = client_id + 20000 where note_id = 2; UPDATE 958
替え玉は元のIDにもどす
update_heroku=# update schedules set client_id = 2 where client_id = 20002; UPDATE 255 update_heroku=# update schedules set client_id = 3 where client_id = 40003; UPDATE 90
エラー clientsテーブルのidとschedulesテーブルのclient_idを比較する
update_heroku=# SELECT id FROM clients EXCEPT SELECT client_id FROM schedules UNION ALL SELECT client_id FROM schedules EXCEPT SELECT id FROM clients order by id;
差分を削除する
=# delete from schedules where client_id in (132, 212, 537, 550, 2697, 2843, 2844, 2845, 2846, 2848, 2849, 2850, 2861, 3049, 3050, 3076, 20081, 22584, 22585, 22599, 22653, 22657, 22697, 22887, 22892, 23073, 23976, 33056, 33057, 33058, 33059, 33062, 33063, 33064, 33065, 33066, 33068, 33069, 33070, 33077, 33078, 33079, 33081); DELETE 168
改めてリストア コピーする
jq@MacBook-Pro tep5 % pg_dump --username=jq --table schedules update_heroku > ../new_schedules.sql jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_schedules.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET SET SET ERROR: relation "schedules" already exists ALTER TABLE ERROR: relation "schedules_id_seq" already exists ALTER TABLE ALTER SEQUENCE ALTER TABLE COPY 3701 setval -------- 4603 (1 row) ERROR: multiple primary keys for table "schedules" are not allowed ERROR: relation "index_schedules_on_client_id" already exists ERROR: relation "index_schedules_on_content" already exists ERROR: relation "index_schedules_on_datetime" already exists ERROR: relation "index_schedules_on_ie_id" already exists ERROR: relation "index_schedules_on_jiin_id" already exists ERROR: relation "index_schedules_on_user_id" already exists
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
科目テーブル
確認
heroku_dump=# select count(*) from accounts;
count
-------
98
(1 row)
% rails generate scaffold Account account_cd:string content:string bs_pl:integer debit_credit:integer
% rails db:migrate
リストアして作業データベースにコピーする
% pg_dump --username=jq --table accounts heroku_dump > ../new_accounts.sql % psql --username=jq update_heroku < ../new_accounts.sql
テーブルの変更
=# ALTER TABLE accounts RENAME COLUMN account TO content;
変更したテーブルをリストアし アプリのデータベースにコピーする
% pg_dump --username=jq --table accounts update_heroku > ../new_accounts.sql
j% psql --username=jq tep5_development < ../new_accounts.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
ERROR: relation "accounts" already exists
ALTER TABLE
ERROR: relation "accounts_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 98
setval
--------
98
(1 row)
ERROR: multiple primary keys for table "accounts" are not allowed
ERROR: relation "index_accounts_on_account_cd" already exists
CREATE INDEX
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
仕訳帳テーブル
確認
=# select count(*) from journals; count ------- 14709 (1 row)
% rails generate scaffold Journal user:references note:references client_id:integer other_id:integer account_id:integer journal_number:integer date:date schedule_id:integer debit:string debit_money:integer description_id:integer etc:string credit:string credit_money:integer debit_s:integer credit_s:integer
% rails db:migrate
リストアして作業データベースにコピーする
% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql % psql --username=jq update_heroku < ../new_journals.sql
テーブルの変更
=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
新テーブルのclient_idに合わせる
update_heroku=# update journals SET client_id = client_id + 20000 where note_id = 2; UPDATE 3663 update_heroku=# update journals SET client_id = client_id + 30000 where note_id = 3; UPDATE 1815 update_heroku=# update journals SET client_id = client_id + 40000 where note_id = 4; UPDATE 719
client_idが無いデータの確認
update_heroku=# select count(*) from journals where client_id is null; count ------- 9806 (1 row)
替え玉のidでjournalsのclient_id null を埋める
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202209270816_tep5.sql
UUIDを有効にする
設定
% rails generate migration enable_extension_for_uuid
migrate編集
class EnableExtensionForUuid < ActiveRecord::Migration[7.0]
def change
enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
end
end
% rails db:migrate
デフォルトのキーをuuidにする
config/initializers/generators.rb 作成 Rails.application.config.generators do |g| g.orm :active_record, primary_key_type: :uuid end
途中からUUIDに変更する場合
migrationファイル追加
% rails generate migration enable_extension_for_uuid % rails generate migration change_user_id_type_to_uuid % rails destroy migration change_user_id_type_to_uuid % rails generate migration change_users_id_type_to_uuid % rails generate migration change_notes_id_type_to_uuid % rails generate migration change_lists_id_type_to_uuid % rails generate migration change_clients_id_type_to_uuid % rails generate migration change_others_id_type_to_uuid
UUIDに変更したデータベースをDUMPする
修正した旧データのテーブルも一緒にDUMPしとく
不要なテーブルを削除し新しいUUIDのデータベースをDUMPする
tep5_development=# drop table clients_old ; DROP TABLE tep5_development=# drop table jiins ; DROP TABLE tep5_development=# drop table users_old ;
% pg_dump tep5_development > tep5_uuid.sql
generate
$ rails generate scaffold User name:string email:string password_digest:string remember_digest:string role:integer activation_digest:string activated:boolean activated_at:datetime reset_digest:string reset_sent_at:datetime
migrationファイルを確認 indexを書く 以下同じ
% rails generate scaffold Note user:references content:string kind:integer meeting_time:integer role:integer note_line:integer
% rails generate scaffold List user:references note:references content:string list_line:integer
% rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:text
% rails generate scaffold Other user:references note:references client:references name:string kana:string client_name:boolean other_line:integer memo:text generation:string
% rails generate scaffold Category user:references note:references content:string category_line:integer
% rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer
% rails generate model Relationship follower_id:integer followed_id:integer
$ rails generate controller Relationships
やり直し % rails destroy scaffold User .................
$ rails generate scaffold Post content:text user:references picture:string
$ rails generate model Relationship follower_id:integer followed_id:integer
$ rails db:migrate RAILS_ENV=production
$ bin/dev
$ rails generate controller StaticPages home help about contact
エラー
ActionView::Template::Error (The asset "tailwind.css" is not present in the asset pipeline.
config/envitonments/production.rb
config.assets.compile = true
サーバーへ転送
除外するファイルをまとめる
exclude-file.txt
production.rb
database.yml
% rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/
% rsync ~/rails_app_tepnote/tep5_uuid_dump.sql jq:/home/jq/dump_data
サーバー側
$ bundle install --without test development
database.yml username:などを確認する （2箇所）
/config/environments/production.rb
データベース削除
postgres=# DROP DATABASE tep5_production;
データベース作成
$ createdb tep5_production
リストア
$ psql tep5_production < ../dump_data/tep5_uuid_dump.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE EXTENSION COMMENT SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COPY 1 COPY 1295 COPY 1 COPY 14 COPY 4747 COPY 11 COPY 17 ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE
$ rails db:migrate RAILS_ENV=production
