メインメニューを開く

差分

Tep3-onk-2

10,417 バイト追加, 2023年6月27日 (火) 09:43
→‎あとから外部キー制約を追加する手順
[[category:memo|{{PAGENAME}}]]
{{Font color}}
== tep5 準備 ==
プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]]
==== 2022-12 -14　othersテーブル ====
09-15以降の増分を取る15以降の増分を取る　nusiがfalseのもの
% psql -d update_dump -U jq -c "select id, user_id, jiin_id, ie_id, name, yomi, nusi, each_order, memo, generation, created_at, updated_at from clients_old where created_at > '2022-09-15' and nusi = false" -A -F, > ../others_add.csv
 
nusiがtrueのもの　両方必要
% psql -d update_dump -U jq -c "select id, user_id, jiin_id, ie_id, name, yomi, nusi, each_order, memo, generation, created_at, updated_at from clients_old where created_at > '2022-09-15' and nusi = true" -A -F, > ../others_nusi_add.csv
コピーコマンドで追加ができる
=# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER;
<nowiki>=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;
COPY 8</nowiki>
 
ここまでのバックアップを取る
% pg_dump -Fc tep5_development > ../202212151649.dump
==== 旧clientsテーブルをリストアする ====
=== placesテーブル ===
==== 12-16 ====
確認
<nowiki>latest_dump=# select count(*) from places;
count
-------
921</nowiki>
 
=# select * from places where created_at > '2022-09-15';
 
増分書き出し
% psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, zip, ken, city, etc, flag, created_at, updated_at from places where created_at > '2022-09-15'" -A -F, > ../places_add.csv
 
places_add.csv　memo列を,で作る　client_id(ie_id)の桁数を調整する
 
コピーで追加
=# \COPY places FROM '../places_add.csv' DELIMITER ',' CSV HEADER;
 
ここまでのバックアップを取る
% pg_dump -Fc tep5_development > ../202212160911.dump
 
==== 以前 ====
確認
<nowiki>heroku_dump=# select count(*) from places;
dump
% pg_dump --username=jq --table places_old heroku_dump > places_old.sql
 
==== 新データベースにリストア ====
<nowiki>% psql --username=jq tep5_development < ../places_old.sql
INSERT 0 918</nowiki>
==== generate ====
% rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text
=== schedulesテーブル ===
==== 12-16 ====
確認
<nowiki>=# select count(*) from schedules;
count
-------
3716
</nowiki>
 
<nowiki>=# select count(*) from schedules;
count
-------
4026
</nowiki>
 
書き出し　jiin_id(note_id)変更のため並べ変える
% psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, datetime, content, client_id, every_year, goji, created_at, updated_at from schedules where created_at >= '2022-09-13' order by jiin_id" -A -F, > ../schedules_add.csv
 
../schedules_add.csv　vimで修正する
jiin_id(note_id)2,3,4はそれぞれ20000,30000,40000を足す　gojiは修正する　:%s/,f,f/,f,/gc
 
copyで追加
=# \COPY schedules FROM '../schedules_add.csv' DELIMITER ',' CSV HEADER;
 
ここまでのバックアップを取る
% pg_dump -Fc tep5_development > ../202212161430.dump
 
==== 以前 ====
確認
<nowiki>heroku_dump=# select count(*) from schedules;
=== 仕訳帳テーブル ===
==== 12-16 17:18 ====
確認
<nowiki>latest_dump=# select count(*) from journals;
count
-------
14709(1 row)14885</nowiki>  % rails generate scaffold Journal user:references note:references account:references client_id:integer other_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 リストアして作業データベースにコピーする <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql% psql --username=jq update_heroku < ../new_journals.sql</nowiki> テーブルの変更 =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
新テーブルのclient_idに合わせる最新をテーブルダンプする <nowiki>update_heroku% pg_dump --username=# update jq --table journals SET client_id = client_id + 20000 where note_id = 2;UPDATE 3663update_heroku=# update journals SET client_id = client_id + 30000 where note_id = 3;UPDATE 1815update_heroku=# update journals SET client_id = client_id + 40000 where note_id = 4;UPDATE 719<latest_dump > ../nowiki>update_journals.sql
リストア　コピーupdate_dumpデータベースにリストアする <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sqljq@MacBook-Pro tep5 % psql --username=jq tep5_development update_dump < ../new_journalsupdate_journals.sql
SET
SET
SET
SET
ERROR: relation "journals" already existsCREATE TABLE
ALTER TABLE
ERROR: relation "journals_id_seq" already existsCREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 1470914885</nowiki>  setval update_dump=# ALTER TABLE journals RENAME COLUMN client_id TO other_id;-------- 15023(1 row) ALTER TABLE
ERROR: multiple primary keys for table "update_dump=# ALTER TABLE journals" are not allowedCREATE INDEXADD COLUMN client_id integer;CREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXERROR: relation "index_journals_on_user_id" already existsCREATE INDEXERROR: relation "public.jiins" does not existALTER TABLE</nowiki>
ダンプ ここまで形を整えたデータベースをバックアップしとく update_dump=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id; % pg_dump tep5_development > ../202209270816_tep5.sqlALTER TABLE
==== 替え玉 ==== update_heroku=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28')update journals set account_id = 99 where account_id is null; tep5_development=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28');UPDATE 10379
確認 <nowiki% pg_dump -t journals update_dump >tep5_development=# select * from accounts order by id desc limit 1; id | account_cd | content | bs_pl | debit_credit | created_at | updated_at ----+------------+---------+-------+--------------+---------------------+--------------------- 99 | 9999 | 替え玉 | 1 | 1 | 2022-09-28 00:00:00 | 2022-09-28 00:00:00(1 row)<../nowiki>journals_latest.sql
<nowiki>update_herokutep5_development=# delete from journals ;DELETE 14719tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY;TRUNCATE TABLEtep5_development=# select count(*) from journals where account_id is nulljournals_id_seq ; count last_value | log_cnt | is_called ------------+---------+----------- 10379( 1 row)| 0 | f</nowiki>
替え玉挿入 <nowiki>update_heroku=# update journals set account_id % psql --username= 99 where account_id is null;UPDATE 10379jq tep5_development <../nowiki>journals_latest.sqlSETSETSETSETSET set_config ------------ (1 row)
=== 摘要テーブル　変更あり ===
確認
<nowiki>heroku_dump=# select count(*) from descriptions;
count
-------
640
(1 row)</nowiki>
 
% rails generate scaffold Description user:references note:references account:references content:string money:integer desc_line:integer
 
（UUID削除 index追加）
 
% rails db:migrate
 
リストアして作業データベースにコピーする
% pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql
% psql --username=jq update_heroku < ../new_descriptions.sql
 
テーブルの変更
=# ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id;
 
リストア　コピー
% pg_dump --username=jq --table descriptions update_heroku > ../new_descriptions.sql
<nowiki>% psql --username=jq tep5_development < ../new_descriptions.sql
SET
SET
SET
SET
set_config ------------ (1 row) SETSETSETSETSETSETERROR: relation "descriptionsjournals" already existsALTER TABLEERROR: relation "descriptions_id_seqjournals_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 64014885
setval
--------
871 15199
(1 row)
ERROR: multiple primary keys for table "descriptionsjournals" are not allowedERROR: relation "index_descriptions_on_account_idindex_journals_on_account_id" already existsCREATE INDEXERROR: relation "index_journals_on_credit_and_credit_money_and_account_id" already existsERROR: relation "index_journals_on_credit_s" already existsERROR: relation "index_journals_on_debit_and_client_id_and_debit_money" already existsERROR: relation "index_journals_on_debit_s" already existsERROR: relation "index_descriptions_on_user_idindex_journals_on_description_id_and_schedule_id" already existsCREATE INDEXERROR: relation "index_journals_on_jiin_id" already existsALTER TABLEERROR: relation "index_journals_on_user_id" already existsERROR: relation "public.jiinsindex_journals_on_user_id_and_jiin_id_and_journal_number" does not existalready exists</nowiki>
ここまでのバックアップを取る
% pg_dump -Fc tep5_development > ../202212170834.dump
ダンプ ここまで形を整えたデータベースをバックアップしとく % pg_dump tep5_development > ../202209270816_tep5.sql === 戒名テーブル　変更あり = 以前 ====
確認
<nowiki>heroku_dump=# select count(*) from kaimyousjournals;
count
-------
3375 14709
(1 row)</nowiki>
% rails generate scaffold Kaimyou Journal user:references note:references clientaccount:references otherclient_id:references contentinteger other_id:string birthinteger journal_number:integer date death:date g_ageschedule_id:integer debit:string relationshipdebit_money:integer description_id:integer etc:string memocredit:string credit_money:integer debit_s:integer credit_s:text （UUID削除 index追加）integer
% rails db:migrate
リストアして作業データベースにコピーする
<nowiki>% pg_dump --username=jq --table kaimyous journals heroku_dump > ../new_kaimyousnew_journals.sql % psql --username=jq update_heroku < ../new_kaimyousnew_journals.sql</nowiki>
テーブルの変更
<nowiki>update_heroku=# ALTER TABLE kaimyous journals RENAME COLUMN jiin_id TO note_id;ALTER TABLE新テーブルのclient_idに合わせる <nowiki>update_heroku=# ALTER TABLE kaimyous RENAME COLUMN update journals SET client_id = client_id TO other_id+ 20000 where note_id = 2;ALTER TABLEUPDATE 3663update_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO update journals SET client_id = client_id;ALTER TABLEupdate_heroku+ 30000 where note_id =# ALTER TABLE kaimyous RENAME COLUMN date TO death3;ALTER TABLEUPDATE 1815update_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;ALTER TABLEupdate_herokuupdate journals SET client_id =# ALTER TABLE kaimyous DROP COLUMN ie_id_v;ALTER TABLEupdate_herokuclient_id + 40000 where note_id =# ALTER TABLE kaimyous ADD COLUMN memo text4;ALTER TABLEUPDATE 719</nowiki>
新テーブルのclient_id other_idに合わせる other_idは一意なので変更しないリストア　コピー <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku> ../new_journals.sqljq@MacBook-Pro tep5 % psql --username=# update kaimyous jq tep5_development < ../new_journals.sql SET client_id = client_id + 20000 where note_id = 2;UPDATE 587SETupdate_heroku=# update kaimyous SET client_id = client_id + 30000 where note_id = 3;UPDATE 662SETupdate_heroku=# update kaimyous SET client_id = client_id + 40000 where note_id = 4;UPDATE 256</nowiki> set_config ------------ (1 row)
SET
SET
SET
SET
SET
SET
ERROR: relation "journals" already exists
ALTER TABLE
ERROR: relation "journals_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 14709
setval
--------
15023
(1 row)
エラー　clientsテーブルのidとkaimyousテーブルのclient_idを比較するERROR: multiple primary keys for table "journals" are not allowedCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXERROR: relation "index_journals_on_user_id" already existsCREATE INDEXERROR: relation "public.jiins" does not existALTER TABLE</nowiki> ダンプ ここまで形を整えたデータベースをバックアップしとく % pg_dump tep5_development > ../202209270816_tep5.sql ==== 替え玉 ==== update_heroku=# SELECT id FROM clientsinsert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28');EXCEPT tep5_development=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28');SELECT client_id FROM kaimyous UNION ALL確認SELECT client_id FROM kaimyous EXCEPTSELECT id FROM clients <nowiki>tep5_development=# select * from accounts order by iddesc limit 1; id | account_cd | content | bs_pl | debit_credit | created_at | updated_at ----+------------+---------+-------+--------------+---------------------+--------------------- 99 | 9999 | 替え玉 | 211 | 1 | 2022-09-28 00:00:00 | 2022-09-28 00:00:00 27(1 row)</nowiki> 35 38 <nowiki>update_heroku=# select count(*) from journals where account_id is null; 39 count 44------- 46 10379 56(1 row)</nowiki> 57 63替え玉挿入 71 <nowiki>update_heroku=# update journals set account_id = 99 where account_id is null; 73UPDATE 10379</nowiki> 78 89=== 摘要テーブル　変更あり === 101確認 106 <nowiki>heroku_dump=# select count(*) from descriptions; 111 count 126 127------- 135 144 147 156 173 174 176 186 188 189 230 310 373 404 473 701 804 872 936 953 972 998 1590 1671 1777 2249 2290 22653 34681640(48 rows1 row)</nowiki>  % rails generate scaffold Description user:references note:references account:references content:string money:integer desc_line:integer
書き出す <nowiki>% psql update_heroku -c "SELECT id FROM clientsEXCEPTSELECT client_id FROM kaimyousUNION ALLSELECT client_id FROM kaimyousEXCEPTSELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv</nowiki>（UUID削除 index追加）
,挿入　改行削除 % rails db:%s/\n//gmigrate
削除する戒名を書き出すリストアして作業データベースにコピーする <nowiki>% psql update_heroku pg_dump --username=jq --c "select * from kaimyous where client_id in (21, 27, 35, 38, 39, 44, 46, 56, 57, 63, 71, 73, 78, 89, 101, 106, 111, 126, 127, 135, 144, 147, 156, 173, 174, 176, 186, 188, 189, 230, 310, 373, 404, 473, 701, 804, 872, 936, 953, 972, 998, 1590, 1671, 1777, 2249, 2290, 22653, 34681);" table descriptions heroku_dump > ../tmp/delete_kaimyounew_descriptions.csvsql % psql --username=jq update_heroku <../nowiki>new_descriptions.sql
削除テーブルの変更 <nowiki>update_heroku=# delete from kaimyous where client_id in (21, 27, 35, 38, 39, 44, 46, 56, 57, 63, 71, 73, 78, 89, 101, 106, 111, 126, 127, 135, 144, 147, 156, 173, 174, 176, 186, 188, 189, 230, 310, 373, 404, 473, 701, 804, 872, 936, 953, 972, 998, 1590, 1671, 1777, 2249, 2290, 22653, 34681)ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id;DELETE 81</nowiki>
ダンプ　リストアリストア　コピー <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous descriptions update_heroku > ../new_kaimyousnew_descriptions.sqljq@MacBook-Pro tep5 <nowiki>% psql --username=jq tep5_development < ../new_kaimyousnew_descriptions.sql
SET
SET
SET
SET
ERROR: relation "kaimyousdescriptions" already exists
ALTER TABLE
ERROR: relation "kaimyous_id_seqdescriptions_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 3294640
setval
--------
4230 871
(1 row)
ERROR: multiple primary keys for table "kaimyousdescriptions" are not allowedERROR: relation "index_kaimyous_on_client_idindex_descriptions_on_account_id" already existsERROR: relation "index_kaimyous_on_client_id_and_content" already existsERROR: relation "index_kaimyous_on_date" already existsERROR: relation "index_kaimyous_on_ie_id" already existsCREATE INDEXERROR: relation "index_kaimyous_on_jiin_idindex_descriptions_on_user_id" already existsERROR: relation "index_kaimyous_on_user_id" already existsCREATE INDEXERROR: relation "index_kaimyous_on_user_id_and_jiin_id" already existsALTER TABLE
ERROR: relation "public.jiins" does not exist</nowiki>
 
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202210031034_tep5202209270816_tep5.sql
=== 通帳テーブル 戒名テーブル　変更あり ======= 12-17 11:14 ====
確認
<nowiki>heroku_dumplatest_dump=# select count(*) from bankbookskaimyous;
count
-------
36 3445</nowiki> 増分を書き出す % psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, client_id, content, date, g_age, relationship, ie_id_v, created_at, updated_at from kaimyous where created_at >= '2022-09-13' order by jiin_id" -A -F, > ../kaimyous_add.csv  ie_id(client_id)は修正する　ie_id_vはmemoに対応するために書き出した コピーで追加 <nowiki>=# \COPY kaimyous FROM '../kaimyous_add.csv' DELIMITER ',' CSV HEADER;COPY 70</nowiki>
==== 以前 ====確認 % rails generate scaffold Bankbook user:references note:references content:string memo:text<nowiki>heroku_dump=# select count(*) from kaimyous; count ------- 3375(1 row)</nowiki>
やり直し % rails destroy generate scaffold Bankbook Kaimyou user:references note:references client:references other:references content:string memo:text % rails generate scaffold Bankbook userbirth:references notedate death:references contentdate g_age:string bank_linerelationship:integer string memo:text
（UUID削除 index追加）
 
% rails db:migrate
リストアして作業データベースにコピーする
% pg_dump --username=jq --table bankbooks kaimyous heroku_dump > ../new_bankbooksnew_kaimyous.sql % psql --username=jq update_heroku < ../new_bankbooksnew_kaimyous.sql
テーブルの変更
<nowiki>update_heroku=# ALTER TABLE bankbooks kaimyous RENAME COLUMN jiin_id TO note_id; ALTER TABLE ダンプ　リストア <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sqljq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sqlSETSETSETSETSET set_config ------------ (1 row) SETSETSETSETSETSETERROR: relation "bankbooks" already exists# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id;
ALTER TABLE
ERROR: relation "bankbooks_id_seq" already existsupdate_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO client_id;
ALTER TABLE
update_heroku=# ALTER SEQUENCETABLE kaimyous RENAME COLUMN date TO death;ALTER TABLEupdate_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;ALTER TABLEupdate_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v;
ALTER TABLE
COPY 36update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text; setval -------- 68(1 row)ALTER TABLE</nowiki>
ERROR: 新テーブルのclient_id other_idに合わせる other_idは一意なので変更しない multiple primary keys for table "bankbooks" are not allowed<nowiki>update_heroku=# update kaimyous SET client_id = client_id + 20000 where note_id = 2;UPDATE 587CREATE INDEXupdate_heroku=# update kaimyous SET client_id = client_id + 30000 where note_id = 3;ERROR: relation "index_bankbooks_on_user_id" already existsUPDATE 662CREATE INDEXupdate_heroku=# update kaimyous SET client_id = client_id + 40000 where note_id = 4;ERROR: relation "public.jiins" does not existUPDATE 256</nowiki>
ダンプ ここまで形を整えたデータベースをバックアップしとく
% pg_dump tep5_development > ../202211021454_tep5.sql
=== relationshipsテーブル ===確認エラー　clientsテーブルのidとkaimyousテーブルのclient_idを比較する <nowiki>latest_dumpupdate_heroku=# select count(*) from relationshipsSELECT id FROM clientsEXCEPTSELECT client_id FROM kaimyous UNION ALLSELECT client_id FROM kaimyous EXCEPTSELECT id FROM clients order by id; count id
-------
40</nowiki>21 27 35 38 39 44 46 56 57 63 71 73 78 89 101 106 111 126 127relationshipsテーブル　ダンプ 135 % pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql 144 147update_dumpデータベースにリストアしてロール列を削除する 156 % psql --username=jq update_dump < ../new_relationships.sql 173 =# ALTER TABLE relationships DROP COLUMN role; 174 176修正したテーブルをダンプする 186 % pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql 188 189リストアする 230 % psql -d tep5_development < ../relationships_dump.sql 310 373バックアップを取る　-Fc カスタム形式 圧縮ファイル形式 404 % pg_dump -Fc tep5_development > ../202212141027.dump 473 701== UUIDを有効にする == 804=== 設定 === 872 % rails generate migration enable_extension_for_uuid 936 953migrate編集 972 <nowiki>class EnableExtensionForUuid < ActiveRecord::Migration[7.0] 998 def change 1590 enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto') 1671 end1777end</nowiki> 2249 2290 % rails db:migrate22653 34681デフォルトのキーをuuidにする (48 rows)</nowiki>config/initializers/generators.rb 作成
Rails.application.config.generators do |g|書き出す g.orm :active_record, primary_key_type: :uuidend </nowiki>% psql update_heroku -c "SELECT id FROM clientsEXCEPT=== 途中からUUIDに変更する場合 ===SELECT client_id FROM kaimyousmigrationファイル追加UNION ALL SELECT client_id FROM kaimyousEXCEPTSELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv</nowiki> % rails generate migration enable_extension_for_uuid
,挿入　改行削除 :% rails generate migration change_user_id_type_to_uuid s/\n//g
削除する戒名を書き出す <nowiki>% rails destroy migration change_user_id_type_to_uuidpsql update_heroku -c "select * from kaimyous where client_id in (21, 27, 35, 38, 39, 44, 46, 56, 57, 63, 71, 73, 78, 89, 101, 106, 111, 126, 127, 135, 144, 147, 156, 173, 174, 176, 186, 188, 189, 230, 310, 373, 404, 473, 701, 804, 872, 936, 953, 972, 998, 1590, 1671, 1777, 2249, 2290, 22653, 34681);" > ../tmp/delete_kaimyou.csv</nowiki>
% rails generate migration change_users_id_type_to_uuid削除 <nowiki>update_heroku=# delete from kaimyous where client_id in (21, 27, 35, 38, 39, 44, 46, 56, 57, 63, 71, 73, 78, 89, 101, 106, 111, 126, 127, 135, 144, 147, 156, 173, 174, 176, 186, 188, 189, 230, 310, 373, 404, 473, 701, 804, 872, 936, 953, 972, 998, 1590, 1671, 1777, 2249, 2290, 22653, 34681);DELETE 81</nowiki>
ダンプ　リストア <nowiki>jq@MacBook-Pro tep5 % rails generate migration change_notes_id_type_to_uuidpg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sqljq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_kaimyous.sqlSETSETSETSET% rails generate migration change_lists_id_type_to_uuidSET set_config ------------ % rails generate migration change_clients_id_type_to_uuid(1 row)
% rails generate migration change_others_id_type_to_uuid</nowiki>SETSETSETSETSETSETERROR: relation "kaimyous" already existsALTER TABLEERROR: relation "kaimyous_id_seq" already existsALTER TABLEALTER SEQUENCEALTER TABLECOPY 3294 setval -------- 4230(1 row)
=== UUIDに変更したデータベースをDUMPする ===ERROR: multiple primary keys for table "kaimyous" are not allowedERROR: relation "index_kaimyous_on_client_id" already exists==== 修正した旧データのテーブルも一緒にDUMPしとく ====ERROR: relation "index_kaimyous_on_client_id_and_content" already exists==== 不要なテーブルを削除し新しいUUIDのデータベースをDUMPする ====ERROR: relation "index_kaimyous_on_date" already existsERROR: <nowiki>tep5_development=# drop table clients_old ;relation "index_kaimyous_on_ie_id" already existsDROP TABLEERROR: relation "index_kaimyous_on_jiin_id" already existstep5_development=# drop table jiins ;ERROR: relation "index_kaimyous_on_user_id" already existsDROP TABLEERROR: relation "index_kaimyous_on_user_id_and_jiin_id" already existstep5_development=# drop table users_old ;ERROR: relation "public.jiins" does not exist</nowiki>
ダンプ ここまで形を整えたデータベースをバックアップしとく % pg_dump tep5_development > tep5_uuid../202210031034_tep5.sql
== generate =通帳テーブル ===確認 <nowiki>heroku_dump=# select count(*) from bankbooks; count ------- 36</nowiki>
$ % rails generate scaffold User nameBankbook user:string email:string password_digest:string remember_digest:string role:integer activation_digest:string activated:boolean activated_atreferences note:datetime reset_digestreferences content:string reset_sent_atmemo:datetimetext
{{font color||yellow|migrationファイルを確認　indexを書く　以下同じ}}やり直し % rails destroy scaffold Bankbook user:references note:references content:string memo:text % rails generate scaffold Bankbook user:references note:references content:string bank_line:integer memo:text
（UUID削除 index追加） % rails generate scaffold Note userdb:references content:string kind:integer meeting_time:integer role:integer note_line:integermigrate
リストアして作業データベースにコピーする % rails generate scaffold List user:references note:references content:string list_line:integerpg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql % psql --username=jq update_heroku < ../new_bankbooks.sql
テーブルの変更 % rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:textupdate_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id; ALTER TABLE
ダンプ　リストア <nowiki>jq@MacBook-Pro tep5 % rails generate scaffold Other user:references note:references client:references name:string kana:string client_name:boolean other_line:integer memo:text generation:stringpg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sqljq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sqlSETSETSETSETSET % rails generate scaffold Category user:references note:references content:string category_line:integerset_config ------------ % rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer(1 row)
SETSETSETSETSETSETERROR: % rails generate model Relationship follower_idrelation "bankbooks" already existsALTER TABLEERROR:integer followed_id:integer relation "bankbooks_id_seq" already existsALTER TABLEALTER SEQUENCEALTER TABLECOPY 36 setval -------- 68(1 row)
ERROR: $ rails generate controller Relationshipsmultiple primary keys for table "bankbooks" are not allowedCREATE INDEXERROR: relation "index_bankbooks_on_user_id" already existsCREATE INDEXERROR: relation "public.jiins" does not exist</nowiki>
やり直しダンプ ここまで形を整えたデータベースをバックアップしとく % rails destroy scaffold User ..............pg_dump tep5_development > ../202211021454_tep5.sql
=== relationshipsテーブル ===確認 $ rails generate scaffold Post content:text user:references picture:string<nowiki>latest_dump=# select count(*) from relationships; count ------- 40</nowiki>
relationshipsテーブル　ダンプ $ rails generate model Relationship follower_id:integer followed_id:integer% pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql
update_dumpデータベースにリストアしてロール列を削除する $ rails db:migrate RAILS_ENV% psql --username=productionjq update_dump < ../new_relationships.sql =# ALTER TABLE relationships DROP COLUMN role;
修正したテーブルをダンプする $ bin% pg_dump --username=jq --table relationships update_dump > ../devrelationships_dump.sql
リストアする $ rails generate controller StaticPages home help about contact% psql -d tep5_development < ../relationships_dump.sql
エラーバックアップを取る　-Fc カスタム形式 圧縮ファイル形式 ActionView::Template::Error (The asset "tailwind% pg_dump -Fc tep5_development > .css" is not present in the asset pipeline./202212141027.dump
config/envitonments/production.rb== house_note ==旧データベースからダンプ config% pg_dump --username=jq --table house_notes latest_dump > ..assets/hns.compile = truesql
== サーバーへ転送 ==除外するファイルをまとめる <nowiki>% rsync -r --delete --exclude-from='exclude-file.txt production' ~/rails_app_tepnote/hns.rb database.yml<sql jq:/home/jq/nowiki>dump_data
リストア % rsync -r --delete --exclude-from='exclude-file$ psql update_dump < ..txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/hns.sql
clientsのidとhouse_noteのidをあわせる % rsync ~<nowiki>tep5_production=# update house_notes SET ie_id = ie_id + 20000 where jiin_id = 2;UPDATE 30tep5_production=# update house_notes SET ie_id = ie_id + 30000 where jiin_id = 3;UPDATE 114tep5_production=# update house_notes SET ie_id = ie_id + 40000 where jiin_id = 4;UPDATE 25</rails_app_tepnote/202210031034_tep5.sql jq:/home/jq/dump_datanowiki>
セレクトしてアップデートする tep5_production=# UPDATE clients as Cl SET memo = サーバー側 =(SELECT content FROM house_notes as Hn WHERE Hn.ie_id = $ bundle install --without test developmentCl.id);
{{font color||yellow|database.yml　username:などを確認する　（2箇所）}}削除 =# drop table house_notes ;
== UUIDを有効にする ===== 設定 === /config/environments/production.rb% rails generate migration enable_extension_for_uuid
データベース削除migrate編集 postgres=# DROP DATABASE tep5_production;<nowiki>class EnableExtensionForUuid < ActiveRecord::Migration[7.0] def change enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto') endend</nowiki>
データベース作成 $ createdb tep5_production% rails db:migrate
リストアデフォルトのキーをuuidにする <nowiki>$ psql tep5_production < ..config/dump_datainitializers/tep5_uuid_dumpgenerators.sql SETSETSETSETSET set_config ------------ (1 row)rb 作成
SETRails.application.config.generators do |g| g.orm :active_record, primary_key_type: :uuidend</nowiki> === 途中からUUIDに変更する場合 ===migrationファイル追加 <nowiki> % 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_uuidSETSET% rails generate migration change_others_id_type_to_uuid</nowiki>SETCREATE EXTENSION=== UUIDに変更したデータベースをDUMPする ===COMMENT==== 修正した旧データのテーブルも一緒にDUMPしとく ====SET==== 不要なテーブルを削除し新しいUUIDのデータベースをDUMPする ====SET <nowiki>tep5_development=# drop table clients_old ;CREATE DROP TABLEALTER tep5_development=# drop table jiins ;DROP TABLECREATE TABLEtep5_development=# drop table users_old ;</nowiki>  % 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 {{font color||yellow|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 scaffold Post user:references title:string content:text notification_time:datetime reminder_type:integer post_type:integer 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 === カラム追加 === <nowiki>% rails generate migration add_memo_to_schedules memo:text invoke active_record create db/migrate/20230311013341_add_memo_to_schedules.rb% rails db:migrate== 20230311013341 AddMemoToSchedules: migrating ===============================-- add_column(:schedules, :memo, :text) -> 0.0056s== 20230311013341 AddMemoToSchedules: migrated (0.0057s) ======================</nowiki> === 外部キー制約に変更 === rails generate migration AddScheduleReferenceToJournal schedule:references == サーバーへ転送 ==除外するファイルをまとめる <nowiki>exclude-file.txt production.rb database.yml</nowiki> すべて % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/ appのみ % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/app/ jq:/home/jq/tep5/app/ データベース % rsync ~/rails_app_tepnote/202212170834.dump jq:/home/jq/dump_data == サーバー側 ==リストア $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_ production ../dump_data/202212141027.dump  $ bundle install --without test development {{font color||yellow|database.yml　username:などを確認する　（2箇所）}}  /config/environments/production.rb データベース削除 ~$ dropdb tep5_production または postgres=# DROP DATABASE tep5_production; データベース作成 $ createdb tep5_productionALTER TABLECREATE TABLEリストアALTER TABLE <nowiki>$ psql tep5_production < ../dump_data/tep5_uuid_dump.sql CREATE TABLESETALTER TABLESETCREATE TABLESETALTER TABLESETCREATE TABLESETALTER TABLE set_config CREATE TABLE------------ALTER TABLE COPY (1row) SETCOPY 1295SETCOPY 1SETSETCOPY 14CREATE EXTENSIONCOPY 4747COMMENTCOPY 11SETCOPY 17SETCREATE TABLE
ALTER TABLE
CREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLECOPY 1COPY 1295COPY 1COPY 14COPY 4747COPY 11COPY 17ALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLECREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXCREATE INDEXALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLE</nowiki>  $ rails db:migrate RAILS_ENV=production === シーケンス操作 ===確認 <nowiki>=# select * from others_id_seq ; last_value | log_cnt | is_called ------------+---------+----------- 14 | 30 | t</nowiki> 最大値確認 <nowiki>=# select max(id) from others; max ------ 5428</nowiki> 変更 <nowiki>=# SELECT setval('others_id_seq', 5428, true); setval -------- 5428</nowiki> === バックアップ ===dump $ pg_dump -Fc tep5_production > ~/dump_data/202212180817.dump ローカル側からコピーして二箇所で保存する % scp jq:/home/jq/dump_data/202212180817.dump /Users/jq/rails_app_tepnote == データベース変更 ===== まるごと ===ダンプ　本番データベースバックアップ　データのみ $ pg_dump -a tep5_production > ~/dump_data/202212270953_data_only.sql ローカルからコピー % scp jq:/home/jq/dump_data/202212270953_data_only.sql /Users/jq/rails_app_tepnote ローカルデータベース削除 % rails db:drop データベース作成 % rails db:create マイグレーション変更 マイグレーション % rails db:migrate リストア % psql --username=jq tep5_development < ../202212270953_data_only.sql === カラム追加 boolean === % rails generate migration add_payment_to_descriptions payment:boolean  add_column :descriptions, :payment, :boolean, default: false  $ rails db:migrate === あとから外部キー制約を追加する手順 ===親テーブルと子テーブルのIDを比較する　不整合があればテーブルを修正する <nowiki>=# SELECT id FROM othersEXCEPTSELECT other_id FROM journals UNION ALLSELECT other_id FROM journals EXCEPTSELECT id FROM others order by id; id ------　2435</nowiki> 外部キー制約を追加するmigration ファイルをつくる <nowiki> % rails generate migration AddClientReferenceToJournal client:references invoke active_record create db/migrate/20230509044227_add_client_reference_to_journal.rb% rails generate migration AddOtherReferenceToJournal other:references invoke active_record create db/migrate/20230509044353_add_other_reference_to_journal.rb</nowiki> rails db:migrateでエラー すでにother_idカラムが存在するのでadd_referenceメソッドが実行できない 直接データベースを操作してother_idカラム名をhoge_idカラム名に変更する  ALTER TABLE journals RENAME COLUMN other_id TO hoge_id;  rails db:migrate hoge_idカラムの値をother_idカラムにコピーする UPDATE journals SET other_id = hoge_id; ローカルならrails consoleを使用して  Journal.update_all('other_id = hoge_id') hoge_idを削除する ALTER TABLE journals DROP COLUMN hoge_id; === エラー ===例えば待避したhoge_idを戻すとき次のようなエラーがでる <nowiki>=# UPDATE journals SET schedule_id = hoge_id;ERROR: insert or update on table "journals" violates foreign key constraint "fk_rails_9d4d4268db"DETAIL: Key (schedule_id)=(970) is not present in table "schedules".</nowiki>journalsテーブルのschedule_idがschedulesテーブルにないのでそれを拾い出して削除する <nowiki>=# delete from journals where hoge_id in (SELECT id FROM schedulesEXCEPTSELECT hoge_id FROM journals UNION ALLSELECT hoge_id FROM journals EXCEPTSELECT id FROM schedules);</nowiki> == gemアップデート ==確認 % gem -vアップデート対象Gemを一覧表示 % bundle outdated
== 備忘 == $ rails db:migrate RAILS_ENV=production現在UUIDコメントアウト中　config/initializers/generators.rb
「http://pokpok.jp/index.php/特別:携帯機器差分/3747...3905」から取得