「Tep3-onk-2」の版間の差分

提供: wikipokpok
移動先: 案内検索
(12-16)
(あとから外部キー制約を追加する手順)
 
(同じ利用者による、間の50版が非表示)
1行目: 1行目:
 
[[category:memo|{{PAGENAME}}]]
 
[[category:memo|{{PAGENAME}}]]
 
{{Font color}}
 
{{Font color}}
== 準備 ==
+
== tep5 準備 ==
 
プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]]
 
プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]]
  
340行目: 340行目:
  
 
==== 2022-12 -14 othersテーブル ====
 
==== 2022-12 -14 othersテーブル ====
09-15以降の増分を取る
+
09-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
 
  % 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
  
 
コピーコマンドで追加ができる
 
コピーコマンドで追加ができる
347行目: 350行目:
 
   
 
   
 
  =# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER;
 
  =# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER;
 +
 +
<nowiki>=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;
 +
COPY 8</nowiki>
  
 
ここまでのバックアップを取る
 
ここまでのバックアップを取る
615行目: 621行目:
 
INSERT 0 918</nowiki>
 
INSERT 0 918</nowiki>
  
==== generate ====
+
generate
 
  % rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text
 
  % rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text
  
895行目: 901行目:
  
 
=== 仕訳帳テーブル ===
 
=== 仕訳帳テーブル ===
==== 12-16 ====
+
==== 12-16 17:18 ====
 
確認
 
確認
 
  <nowiki>latest_dump=# select count(*) from journals;
 
  <nowiki>latest_dump=# select count(*) from journals;
931行目: 937行目:
 
COPY 14885</nowiki>
 
COPY 14885</nowiki>
  
==== 以前 ====
+
  update_dump=# ALTER TABLE journals RENAME COLUMN client_id TO other_id;
確認
+
  ALTER TABLE
  <nowiki>=# select count(*) from journals;
 
  count
 
-------
 
14709
 
(1 row)</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
+
  update_dump=# ALTER TABLE journals ADD COLUMN client_id integer;
 +
ALTER TABLE
  
  % rails db:migrate
+
  update_dump=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
 +
ALTER TABLE
  
リストアして作業データベースにコピーする
+
  =# update journals set account_id = 99 where account_id is null;
  <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql
+
UPDATE 10379
% psql --username=jq update_heroku < ../new_journals.sql</nowiki>
 
  
テーブルの変更
+
  % pg_dump -t journals update_dump > ../journals_latest.sql
  =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
 
  
新テーブルのclient_idに合わせる
+
  <nowiki>tep5_development=# delete from journals ;
  <nowiki>update_heroku=# update journals SET client_id = client_id + 20000 where note_id = 2;
+
DELETE 14719
UPDATE 3663
+
tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY;
update_heroku=# update journals SET client_id = client_id + 30000 where note_id = 3;
+
TRUNCATE TABLE
UPDATE 1815
+
tep5_development=# select * from journals_id_seq ;
update_heroku=# update journals SET client_id = client_id + 40000 where note_id = 4;
+
last_value | log_cnt | is_called
UPDATE 719</nowiki>
+
------------+---------+-----------
 +
          1 |      0 | f</nowiki>
  
リストア コピー
+
  <nowiki>% psql --username=jq tep5_development < ../journals_latest.sql
  <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql
 
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql              
 
 
SET
 
SET
 
SET
 
SET
983行目: 983行目:
 
ALTER SEQUENCE
 
ALTER SEQUENCE
 
ALTER TABLE
 
ALTER TABLE
COPY 14709
+
COPY 14885
 
  setval  
 
  setval  
 
--------
 
--------
   15023
+
   15199
 
(1 row)
 
(1 row)
  
 
ERROR:  multiple primary keys for table "journals" are not allowed
 
ERROR:  multiple primary keys for table "journals" are not allowed
CREATE INDEX
+
ERROR:  relation "index_journals_on_account_id" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_credit_and_credit_money_and_account_id" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_credit_s" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_debit_and_client_id_and_debit_money" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_debit_s" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_description_id_and_schedule_id" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_jiin_id" already exists
 
ERROR:  relation "index_journals_on_user_id" already exists
 
ERROR:  relation "index_journals_on_user_id" already exists
CREATE INDEX
+
ERROR:  relation "index_journals_on_user_id_and_jiin_id_and_journal_number" already exists</nowiki>
ERROR:  relation "public.jiins" does not exist
 
ALTER TABLE</nowiki>
 
  
ダンプ ここまで形を整えたデータベースをバックアップしとく
+
ここまでのバックアップを取る
  % pg_dump tep5_development > ../202209270816_tep5.sql
+
  % pg_dump -Fc tep5_development > ../202212170834.dump
 
 
==== 替え玉 ====
 
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');
 
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');
 
  
 +
==== 以前 ====
 
確認
 
確認
  <nowiki>tep5_development=# select * from accounts order by id desc limit 1;
+
  <nowiki>=# select count(*) from journals;
id | account_cd | content | bs_pl | debit_credit |    created_at      |    updated_at     
+
  count  
----+------------+---------+-------+--------------+---------------------+---------------------
 
99 | 9999      | 替え玉  |    1 |            1 | 2022-09-28 00:00:00 | 2022-09-28 00:00:00
 
(1 row)</nowiki>
 
 
 
<nowiki>update_heroku=# select count(*) from journals where account_id is null;
 
  count  
 
 
-------
 
-------
  10379
+
  14709
 
(1 row)</nowiki>
 
(1 row)</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
<nowiki>update_heroku=# update journals set account_id = 99 where account_id is null;
 
UPDATE 10379</nowiki>
 
 
 
=== 摘要テーブル 変更あり ===
 
確認
 
<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
 
  % rails db:migrate
  
 
リストアして作業データベースにコピーする
 
リストアして作業データベースにコピーする
  % pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql
+
  <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql
% psql --username=jq update_heroku < ../new_descriptions.sql
+
% psql --username=jq update_heroku < ../new_journals.sql</nowiki>
  
 
テーブルの変更
 
テーブルの変更
  =# ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id;
+
  =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
 +
 
 +
新テーブルのclient_idに合わせる
 +
<nowiki>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</nowiki>
  
 
リストア コピー
 
リストア コピー
  % pg_dump --username=jq --table descriptions update_heroku > ../new_descriptions.sql
+
  <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql
<nowiki>% psql --username=jq tep5_development < ../new_descriptions.sql
+
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql              
 
SET
 
SET
 
SET
 
SET
1,066行目: 1,049行目:
 
SET
 
SET
 
SET
 
SET
ERROR:  relation "descriptions" already exists
+
ERROR:  relation "journals" already exists
 
ALTER TABLE
 
ALTER TABLE
ERROR:  relation "descriptions_id_seq" already exists
+
ERROR:  relation "journals_id_seq" already exists
 
ALTER TABLE
 
ALTER TABLE
 
ALTER SEQUENCE
 
ALTER SEQUENCE
 
ALTER TABLE
 
ALTER TABLE
COPY 640
+
COPY 14709
 
  setval  
 
  setval  
 
--------
 
--------
    871
+
  15023
 
(1 row)
 
(1 row)
  
ERROR:  multiple primary keys for table "descriptions" are not allowed
+
ERROR:  multiple primary keys for table "journals" are not allowed
ERROR:  relation "index_descriptions_on_account_id" already exists
 
 
CREATE INDEX
 
CREATE INDEX
ERROR:  relation "index_descriptions_on_user_id" already exists
 
 
CREATE INDEX
 
CREATE INDEX
ALTER TABLE
+
CREATE INDEX
ERROR:  relation "public.jiins" does not exist</nowiki>
+
CREATE INDEX
 
+
CREATE INDEX
 +
CREATE INDEX
 +
CREATE INDEX
 +
ERROR:  relation "index_journals_on_user_id" already exists
 +
CREATE INDEX
 +
ERROR:  relation "public.jiins" does not exist
 +
ALTER TABLE</nowiki>
  
 
ダンプ ここまで形を整えたデータベースをバックアップしとく
 
ダンプ ここまで形を整えたデータベースをバックアップしとく
 
  % pg_dump tep5_development > ../202209270816_tep5.sql
 
  % pg_dump tep5_development > ../202209270816_tep5.sql
  
=== 戒名テーブル 変更あり ===
+
==== 替え玉 ====
 +
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');
 +
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');
 +
 
 
確認
 
確認
  <nowiki>heroku_dump=# select count(*) from kaimyous;
+
  <nowiki>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>
 +
 
 +
<nowiki>update_heroku=# select count(*) from journals where account_id is null;
 
  count  
 
  count  
 
-------
 
-------
  3375
+
10379
 
(1 row)</nowiki>
 
(1 row)</nowiki>
  
  rails generate scaffold Kaimyou user:references note:references client:references other:references content:string birth:date death:date g_age:string relationship:string memo:text
+
替え玉挿入
 +
  <nowiki>update_heroku=# update journals set account_id = 99 where account_id is null;
 +
UPDATE 10379</nowiki>
 +
 
 +
=== 摘要テーブル 変更あり ===
 +
確認
 +
<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追加)
 
(UUID削除 index追加)
1,105行目: 1,113行目:
  
 
リストアして作業データベースにコピーする
 
リストアして作業データベースにコピーする
  % pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql
+
  % pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql
  % psql --username=jq update_heroku < ../new_kaimyous.sql
+
  % psql --username=jq update_heroku < ../new_descriptions.sql
  
 
テーブルの変更
 
テーブルの変更
  <nowiki>update_heroku=# ALTER TABLE kaimyous RENAME COLUMN jiin_id TO note_id;
+
  =# ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id;
ALTER TABLE
+
 
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id;
+
リストア コピー
ALTER TABLE
+
% pg_dump --username=jq --table descriptions update_heroku > ../new_descriptions.sql
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO client_id;
+
<nowiki>% psql --username=jq tep5_development < ../new_descriptions.sql
 +
SET
 +
SET
 +
SET
 +
SET
 +
SET
 +
set_config
 +
------------
 +
 +
(1 row)
 +
 
 +
SET
 +
SET
 +
SET
 +
SET
 +
SET
 +
SET
 +
ERROR:  relation "descriptions" already exists
 
ALTER TABLE
 
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN date TO death;
+
ERROR:  relation "descriptions_id_seq" already exists
 
ALTER TABLE
 
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;
+
ALTER SEQUENCE
 
ALTER TABLE
 
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v;
+
COPY 640
ALTER TABLE
+
setval
update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text;
+
--------
ALTER TABLE</nowiki>
+
    871
 +
(1 row)
 +
 
 +
ERROR:  multiple primary keys for table "descriptions" are not allowed
 +
ERROR:  relation "index_descriptions_on_account_id" already exists
 +
CREATE INDEX
 +
ERROR:  relation "index_descriptions_on_user_id" already exists
 +
CREATE INDEX
 +
ALTER TABLE
 +
ERROR:  relation "public.jiins" does not exist</nowiki>
  
新テーブルのclient_id other_idに合わせる other_idは一意なので変更しない
 
<nowiki>update_heroku=# update kaimyous SET client_id = client_id + 20000 where note_id = 2;
 
UPDATE 587
 
update_heroku=# update kaimyous SET client_id = client_id + 30000 where note_id = 3;
 
UPDATE 662
 
update_heroku=# update kaimyous SET client_id = client_id + 40000 where note_id = 4;
 
UPDATE 256</nowiki>
 
  
 +
ダンプ ここまで形を整えたデータベースをバックアップしとく
 +
% pg_dump tep5_development > ../202209270816_tep5.sql
  
エラー clientsテーブルのidとkaimyousテーブルのclient_idを比較する
+
=== 戒名テーブル 変更あり ===
  <nowiki>update_heroku=# SELECT id FROM clients
+
==== 12-17 11:14 ====
EXCEPT
+
確認
SELECT client_id FROM kaimyous  
+
  <nowiki>latest_dump=# select count(*) from kaimyous;
UNION ALL
+
count
SELECT client_id FROM kaimyous
+
-------
EXCEPT
+
  3445</nowiki>
SELECT id FROM clients order by id;
+
 
  id 
+
増分を書き出す
 +
% 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>
 +
 
 +
==== 以前 ====
 +
確認
 +
<nowiki>heroku_dump=# select count(*) from kaimyous;
 +
count
 
-------
 
-------
    21
+
  3375
    27
+
(1 row)</nowiki>
    35
+
 
    38
+
rails generate scaffold Kaimyou user:references note:references client:references other:references content:string birth:date death:date g_age:string relationship:string memo:text
    39
+
 
    44
+
(UUID削除 index追加)
    46
+
 
    56
+
% rails db:migrate
    57
+
 
    63
+
リストアして作業データベースにコピーする
    71
+
% pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql
    73
+
% psql --username=jq update_heroku < ../new_kaimyous.sql
    78
+
 
    89
+
テーブルの変更
  101
+
<nowiki>update_heroku=# ALTER TABLE kaimyous RENAME COLUMN jiin_id TO note_id;
  106
+
ALTER TABLE
  111
+
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id;
  126
+
ALTER TABLE
  127
+
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO client_id;
  135
+
ALTER TABLE
  144
+
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN date TO death;
  147
+
ALTER TABLE
  156
+
update_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;
  173
+
ALTER TABLE
  174
+
update_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v;
  176
+
ALTER TABLE
  186
+
update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text;
  188
+
ALTER TABLE</nowiki>
  189
+
 
  230
+
新テーブルのclient_id other_idに合わせる other_idは一意なので変更しない
  310
+
<nowiki>update_heroku=# update kaimyous SET client_id = client_id + 20000 where note_id = 2;
  373
+
UPDATE 587
  404
+
update_heroku=# update kaimyous SET client_id = client_id + 30000 where note_id = 3;
  473
+
UPDATE 662
  701
+
update_heroku=# update kaimyous SET client_id = client_id + 40000 where note_id = 4;
  804
+
UPDATE 256</nowiki>
  872
+
 
  936
 
  953
 
  972
 
  998
 
  1590
 
  1671
 
  1777
 
  2249
 
  2290
 
22653
 
34681
 
(48 rows)</nowiki>
 
  
書き出す
+
エラー clientsテーブルのidとkaimyousテーブルのclient_idを比較する
  <nowiki>% psql update_heroku -c "SELECT id FROM clients
+
  <nowiki>update_heroku=# SELECT id FROM clients
 
EXCEPT
 
EXCEPT
SELECT client_id FROM kaimyous
+
SELECT client_id FROM kaimyous  
 
UNION ALL
 
UNION ALL
SELECT client_id FROM kaimyous
+
SELECT client_id FROM kaimyous  
 
EXCEPT
 
EXCEPT
SELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv</nowiki>
+
SELECT id FROM clients order by id;
 
+
  id 
,挿入 改行削除
+
-------
:%s/\n//g
+
    21
 
+
    27
削除する戒名を書き出す
+
    35
<nowiki>% psql update_heroku -c "select * from kaimyous where client_id in (21,    27,    35,    38,    39,    44,    46,    56,    57,    63,    71,    73,    78,   89,  101106111126127135144147156173174176186188189230310373404473701804872936953972,   998,  1590167117772249, 2290, 22653, 34681);" > ../tmp/delete_kaimyou.csv</nowiki>
+
    38
 
+
    39
削除
+
    44
<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);
+
    46
DELETE 81</nowiki>
+
    56
 
+
    57
ダンプ リストア
+
    63
<nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sql
+
    71
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_kaimyous.sql
+
    73
SET
+
    78
SET
+
    89
SET
+
   101
SET
+
  106
SET
+
  111
set_config
+
  126
------------
+
  127
+
  135
(1 row)
+
  144
 
+
  147
SET
+
  156
SET
+
  173
SET
+
  174
SET
+
  176
SET
+
  186
SET
+
  188
ERROR:  relation "kaimyous" already exists
+
  189
ALTER TABLE
+
  230
ERROR:  relation "kaimyous_id_seq" already exists
+
  310
ALTER TABLE
+
  373
ALTER SEQUENCE
+
  404
ALTER TABLE
+
  473
COPY 3294
+
  701
setval
+
  804
--------
+
  872
  4230
+
  936
(1 row)
+
  953
 +
  972
 +
  998
 +
   1590
 +
  1671
 +
  1777
 +
  2249
 +
  2290
 +
  22653
 +
34681
 +
(48 rows)</nowiki>
  
ERROR:  multiple primary keys for table "kaimyous" are not allowed
+
書き出す
ERROR: relation "index_kaimyous_on_client_id" already exists
+
  <nowiki>% psql update_heroku -c "SELECT id FROM clients
ERROR:  relation "index_kaimyous_on_client_id_and_content" already exists
+
EXCEPT
ERROR:  relation "index_kaimyous_on_date" already exists
+
SELECT client_id FROM kaimyous
ERROR:  relation "index_kaimyous_on_ie_id" already exists
+
UNION ALL
ERROR:  relation "index_kaimyous_on_jiin_id" already exists
+
SELECT client_id FROM kaimyous
ERROR:  relation "index_kaimyous_on_user_id" already exists
+
EXCEPT
ERROR:  relation "index_kaimyous_on_user_id_and_jiin_id" already exists
+
SELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv</nowiki>
ERROR:  relation "public.jiins" does not exist</nowiki>
 
  
ダンプ ここまで形を整えたデータベースをバックアップしとく
+
,挿入 改行削除
  % pg_dump tep5_development > ../202210031034_tep5.sql
+
  :%s/\n//g
  
=== 通帳テーブル ===
+
削除する戒名を書き出す
確認
+
  <nowiki>% psql 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>
  <nowiki>heroku_dump=# select count(*) from bankbooks;
 
count
 
-------
 
    36</nowiki>
 
  
  % rails generate scaffold Bankbook user:references note:references content:string memo:text
+
削除
 +
  <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>
  
やり直し
+
ダンプ リストア
% rails destroy scaffold Bankbook user:references note:references content:string memo:text
+
  <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sql
% rails generate scaffold Bankbook user:references note:references content:string bank_line:integer memo:text
+
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_kaimyous.sql
 
 
(UUID削除 index追加)
 
% rails db:migrate
 
 
 
リストアして作業データベースにコピーする
 
% pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql
 
% psql --username=jq update_heroku < ../new_bankbooks.sql
 
 
 
テーブルの変更
 
update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id;
 
ALTER TABLE
 
 
 
ダンプ リストア
 
  <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sql
 
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sql
 
 
SET
 
SET
 
SET
 
SET
1,299行目: 1,320行目:
 
SET
 
SET
 
SET
 
SET
ERROR:  relation "bankbooks" already exists
+
ERROR:  relation "kaimyous" already exists
 
ALTER TABLE
 
ALTER TABLE
ERROR:  relation "bankbooks_id_seq" already exists
+
ERROR:  relation "kaimyous_id_seq" already exists
 
ALTER TABLE
 
ALTER TABLE
 
ALTER SEQUENCE
 
ALTER SEQUENCE
 
ALTER TABLE
 
ALTER TABLE
COPY 36
+
COPY 3294
 
  setval  
 
  setval  
 
--------
 
--------
    68
+
  4230
 
(1 row)
 
(1 row)
  
ERROR:  multiple primary keys for table "bankbooks" are not allowed
+
ERROR:  multiple primary keys for table "kaimyous" are not allowed
CREATE INDEX
+
ERROR:  relation "index_kaimyous_on_client_id" already exists
ERROR:  relation "index_bankbooks_on_user_id" already exists
+
ERROR:  relation "index_kaimyous_on_client_id_and_content" already exists
CREATE INDEX
+
ERROR:  relation "index_kaimyous_on_date" already exists
 +
ERROR:  relation "index_kaimyous_on_ie_id" already exists
 +
ERROR:  relation "index_kaimyous_on_jiin_id" already exists
 +
ERROR:  relation "index_kaimyous_on_user_id" already exists
 +
ERROR:  relation "index_kaimyous_on_user_id_and_jiin_id" already exists
 
ERROR:  relation "public.jiins" does not exist</nowiki>
 
ERROR:  relation "public.jiins" does not exist</nowiki>
  
 
ダンプ ここまで形を整えたデータベースをバックアップしとく
 
ダンプ ここまで形を整えたデータベースをバックアップしとく
  % pg_dump tep5_development > ../202211021454_tep5.sql
+
  % pg_dump tep5_development > ../202210031034_tep5.sql
  
=== relationshipsテーブル ===
+
=== 通帳テーブル ===
 
確認
 
確認
  <nowiki>latest_dump=# select count(*) from relationships;
+
  <nowiki>heroku_dump=# select count(*) from bankbooks;
 
  count  
 
  count  
 
-------
 
-------
     40</nowiki>
+
     36</nowiki>
  
relationshipsテーブル ダンプ
+
  % rails generate scaffold Bankbook user:references note:references content:string memo:text
  % pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql
 
  
update_dumpデータベースにリストアしてロール列を削除する
+
やり直し
  % psql --username=jq update_dump < ../new_relationships.sql
+
  % rails destroy scaffold Bankbook user:references note:references content:string memo:text
  =# ALTER TABLE relationships DROP COLUMN role;
+
  % rails generate scaffold Bankbook user:references note:references content:string bank_line:integer memo:text
  
修正したテーブルをダンプする
+
(UUID削除 index追加)
  % pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql
+
  % rails db:migrate
  
リストアする
+
リストアして作業データベースにコピーする
  % psql -d tep5_development < ../relationships_dump.sql
+
% pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql
 +
  % psql --username=jq update_heroku < ../new_bankbooks.sql
  
バックアップを取る -Fc カスタム形式 圧縮ファイル形式
+
テーブルの変更
  % pg_dump -Fc tep5_development > ../202212141027.dump
+
  update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id;
 +
ALTER TABLE
  
== UUIDを有効にする ==
+
ダンプ リストア
=== 設定 ===
+
<nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sql
  % rails generate migration enable_extension_for_uuid
+
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sql
 +
SET
 +
SET
 +
SET
 +
SET
 +
SET
 +
set_config
 +
------------
 +
   
 +
(1 row)
  
migrate編集
+
SET
  <nowiki>class EnableExtensionForUuid < ActiveRecord::Migration[7.0]
+
SET
  def change                 
+
SET
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
+
SET
  end
+
SET
end</nowiki>
+
SET
 +
ERROR: relation "bankbooks" already exists
 +
ALTER TABLE
 +
ERROR: relation "bankbooks_id_seq" already exists
 +
ALTER TABLE
 +
ALTER SEQUENCE
 +
ALTER TABLE
 +
COPY 36
 +
setval
 +
--------
 +
    68
 +
(1 row)
  
  % rails db:migrate
+
ERROR: multiple primary keys for table "bankbooks" are not allowed
 
+
CREATE INDEX
デフォルトのキーをuuidにする
+
ERROR: relation "index_bankbooks_on_user_id" already exists
  <nowiki>config/initializers/generators.rb 作成
+
CREATE INDEX
 +
ERROR: relation "public.jiins" does not exist</nowiki>
  
Rails.application.config.generators do |g|
+
ダンプ ここまで形を整えたデータベースをバックアップしとく
  g.orm :active_record, primary_key_type: :uuid
+
% pg_dump tep5_development > ../202211021454_tep5.sql
end</nowiki>
 
  
=== 途中からUUIDに変更する場合 ===
+
=== relationshipsテーブル ===
migrationファイル追加
+
確認
  <nowiki> % rails generate migration enable_extension_for_uuid
+
  <nowiki>latest_dump=# select count(*) from relationships;
 +
count
 +
-------
 +
    40</nowiki>
  
% rails generate migration change_user_id_type_to_uuid
+
relationshipsテーブル ダンプ
 +
% pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql
  
% rails destroy migration change_user_id_type_to_uuid
+
update_dumpデータベースにリストアしてロール列を削除する
 +
% psql --username=jq update_dump < ../new_relationships.sql
 +
=# ALTER TABLE relationships DROP COLUMN role;
  
% rails generate migration change_users_id_type_to_uuid
+
修正したテーブルをダンプする
 +
% pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql
  
% rails generate migration change_notes_id_type_to_uuid
+
リストアする
 +
% psql -d tep5_development < ../relationships_dump.sql
  
% rails generate migration change_lists_id_type_to_uuid
+
バックアップを取る -Fc カスタム形式 圧縮ファイル形式
 +
% pg_dump -Fc tep5_development > ../202212141027.dump
  
% rails generate migration change_clients_id_type_to_uuid
+
== house_note ==
 +
旧データベースからダンプ
 +
% pg_dump --username=jq --table house_notes latest_dump > ../hns.sql
  
% rails generate migration change_others_id_type_to_uuid</nowiki>
+
サーバーへ転送
 +
% rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/hns.sql jq:/home/jq/dump_data
  
=== UUIDに変更したデータベースをDUMPする ===
+
リストア
==== 修正した旧データのテーブルも一緒にDUMPしとく ====
+
  $ psql update_dump < ../hns.sql
==== 不要なテーブルを削除し新しいUUIDのデータベースをDUMPする ====
 
  <nowiki>tep5_development=# drop table clients_old ;
 
DROP TABLE
 
tep5_development=# drop table jiins ;
 
DROP TABLE
 
tep5_development=# drop table users_old ;</nowiki>
 
  
  % pg_dump tep5_development > tep5_uuid.sql
+
clientsのidとhouse_noteのidをあわせる
 +
  <nowiki>tep5_production=# update house_notes SET ie_id = ie_id + 20000 where jiin_id = 2;
 +
UPDATE 30
 +
tep5_production=# update house_notes SET ie_id = ie_id + 30000 where jiin_id = 3;
 +
UPDATE 114
 +
tep5_production=# update house_notes SET ie_id = ie_id + 40000 where jiin_id = 4;
 +
UPDATE 25</nowiki>
  
== generate ==
+
セレクトしてアップデートする
 +
tep5_production=# UPDATE clients as Cl SET memo = (SELECT content FROM house_notes as Hn WHERE Hn.ie_id = Cl.id);
  
  $ 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
+
削除
 +
  =# drop table house_notes ;
  
{{font color||yellow|migrationファイルを確認 indexを書く 以下同じ}}
+
== UUIDを有効にする ==
 +
=== 設定 ===
 +
% rails generate migration enable_extension_for_uuid
  
  % rails generate scaffold Note user:references content:string kind:integer meeting_time:integer role:integer note_line:integer
+
migrate編集
 +
  <nowiki>class EnableExtensionForUuid < ActiveRecord::Migration[7.0]
 +
  def change                 
 +
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
 +
  end
 +
end</nowiki>
  
  % rails generate scaffold List user:references note:references content:string list_line:integer
+
  % rails db:migrate
  
  % rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:text
+
デフォルトのキーをuuidにする
 +
  <nowiki>config/initializers/generators.rb 作成
  
% 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.application.config.generators do |g|
 +
  g.orm :active_record, primary_key_type: :uuid
 +
end</nowiki>
  
  % rails generate scaffold Category user:references note:references content:string category_line:integer
+
=== 途中からUUIDに変更する場合 ===
 +
migrationファイル追加
 +
  <nowiki> % rails generate migration enable_extension_for_uuid
  
% rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer
+
% rails generate migration change_user_id_type_to_uuid
  
% rails generate model Relationship follower_id:integer followed_id:integer
+
% rails destroy migration change_user_id_type_to_uuid
  
$ rails generate controller Relationships
+
% rails generate migration change_users_id_type_to_uuid
  
やり直し
+
% rails generate migration change_notes_id_type_to_uuid
% rails destroy scaffold User .................
+
 
 +
% rails generate migration change_lists_id_type_to_uuid
  
$ rails generate scaffold Post content:text user:references picture:string
+
% rails generate migration change_clients_id_type_to_uuid
  
$ rails generate model Relationship follower_id:integer followed_id:integer
+
% rails generate migration change_others_id_type_to_uuid</nowiki>
  
  $ rails db:migrate RAILS_ENV=production
+
=== UUIDに変更したデータベースをDUMPする ===
 +
==== 修正した旧データのテーブルも一緒にDUMPしとく ====
 +
==== 不要なテーブルを削除し新しいUUIDのデータベースをDUMPする ====
 +
  <nowiki>tep5_development=# drop table clients_old ;
 +
DROP TABLE
 +
tep5_development=# drop table jiins ;
 +
DROP TABLE
 +
tep5_development=# drop table users_old ;</nowiki>
  
  $ bin/dev
+
  % pg_dump tep5_development > tep5_uuid.sql
  
$ rails generate controller StaticPages home help about contact
+
== 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
  ActionView::Template::Error (The asset "tailwind.css" is not present in the asset pipeline.
 
  
config/envitonments/production.rb
+
{{font color||yellow|migrationファイルを確認 indexを書く 以下同じ}}
config.assets.compile = true
 
  
== サーバーへ転送 ==
+
% rails generate scaffold Note user:references content:string kind:integer meeting_time:integer role:integer note_line:integer
除外するファイルをまとめる
+
 
  <nowiki>exclude-file.txt
+
  % rails generate scaffold List user:references note:references content:string list_line:integer
    production.rb
 
    database.yml</nowiki>
 
  
  % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/
+
  % rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:text
  
  % rsync ~/rails_app_tepnote/202210031034_tep5.sql jq:/home/jq/dump_data
+
  % 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
  $ bundle install  --without test development
 
  
{{font color||yellow|database.yml username:などを確認する (2箇所)}}
+
% rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer
  
  /config/environments/production.rb
+
  % rails generate model Relationship follower_id:integer followed_id:integer
  
データベース削除
+
  $ rails generate controller Relationships
  postgres=# DROP DATABASE tep5_production;
 
  
データベース作成
+
やり直し
  $ createdb tep5_production
+
  % rails destroy scaffold User .................
  
リストア
+
$ rails generate scaffold Post content:text user:references picture:string
  <nowiki>$ psql tep5_production < ../dump_data/tep5_uuid_dump.sql
+
 
SET
+
  $ rails generate scaffold Post user:references title:string content:text notification_time:datetime reminder_type:integer post_type:integer picture:string
SET
+
 
SET
+
$ rails generate model Relationship follower_id:integer followed_id:integer
SET
+
 
SET
+
$ rails db:migrate RAILS_ENV=production
  set_config
+
 
------------
+
  $ bin/dev
   
+
 
(1 row)
+
  $ rails generate controller StaticPages home help about contact
  
SET
+
エラー
SET
+
ActionView::Template::Error (The asset "tailwind.css" is not present in the asset pipeline.
SET
+
 
SET
+
config/envitonments/production.rb
CREATE EXTENSION
+
config.assets.compile = true
COMMENT
+
 
SET
+
=== カラム追加 ===
SET
+
<nowiki>% rails generate migration add_memo_to_schedules memo:text
CREATE TABLE
+
      invoke  active_record
ALTER TABLE
+
      create    db/migrate/20230311013341_add_memo_to_schedules.rb
CREATE TABLE
+
% rails db:migrate
ALTER TABLE
+
== 20230311013341 AddMemoToSchedules: migrating ===============================
CREATE TABLE
+
-- add_column(:schedules, :memo, :text)
ALTER TABLE
+
  -> 0.0056s
CREATE TABLE
+
== 20230311013341 AddMemoToSchedules: migrated (0.0057s) ======================</nowiki>
ALTER TABLE
+
 
CREATE TABLE
+
=== 外部キー制約に変更 ===
ALTER TABLE
+
rails generate migration AddScheduleReferenceToJournal schedule:references
CREATE TABLE
+
 
ALTER TABLE
+
== サーバーへ転送 ==
CREATE TABLE
+
除外するファイルをまとめる
ALTER TABLE
+
<nowiki>exclude-file.txt
COPY 1
+
    production.rb
COPY 1295
+
    database.yml</nowiki>
COPY 1
+
 
COPY 14
+
すべて
 +
% 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_production
 +
 
 +
リストア
 +
<nowiki>$ 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 4747
 
COPY 11
 
COPY 11
 
COPY 17
 
COPY 17
ALTER TABLE
+
ALTER TABLE
ALTER TABLE
+
ALTER TABLE
ALTER TABLE
+
ALTER TABLE
ALTER TABLE
+
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
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
+
ALTER TABLE
ALTER TABLE
+
ALTER TABLE
ALTER TABLE
+
ALTER TABLE
ALTER TABLE
+
ALTER TABLE
ALTER TABLE</nowiki>
+
ALTER 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 others
 +
EXCEPT
 +
SELECT other_id FROM journals
 +
UNION ALL
 +
SELECT other_id FROM journals
 +
EXCEPT
 +
SELECT 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 schedules
 +
EXCEPT
 +
SELECT hoge_id FROM journals
 +
UNION ALL
 +
SELECT hoge_id FROM journals
 +
EXCEPT
 +
SELECT id FROM schedules);</nowiki>
 +
 
 +
== gemアップデート ==
 +
確認
 +
% gem -v
 +
アップデート対象Gemを一覧表示
 +
% bundle outdated
  
  $ rails db:migrate RAILS_ENV=production
+
== 備忘 ==
 +
  現在UUIDコメントアウト中 config/initializers/generators.rb

2023年6月27日 (火) 09:43時点における最新版

目次

tep5 準備[編集]

プロジェクトディレクトリ作成 移動 バージョン確認

new[編集]

% rails new . -d postgresql --css tailwind または bootstrap
% bundle install
% rails db:create
または
% rails db:reset 
% rails db:migrate
% rails server

bootstrapのとき

package.json

{
  "name": "app",
  "private": "true",
  "dependencies": {
    "@hotwired/stimulus": "^3.0.1",
    "@hotwired/turbo-rails": "^7.1.0",
    "@popperjs/core": "^2.11.0",
    "bootstrap": "^5.1.3",
    "esbuild": "^0.14.9",
    "sass": "^1.45.1"
  },
  "scripts": {
    "build": "esbuild app/javascript/*.* --bundle --sourcemap --outdir=app/assets/builds",
    "build:css": "sass ./app/assets/stylesheets/application.bootstrap.scss ./app/assets/builds/application.css --no-source-map --load-path=node_modules"
  }
}

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

postgres dump restore[編集]

% pg_dump -Fc tep5_development > ../202212141027.dump
% pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_development  ../202212141027.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テーブル[編集]

2022-12 -14 clientsテーブル[編集]

latest_dumpデータベースからダンプして 作業データベースにリストアする

% pg_dump -t clients latest_dump > ../client_dump.sql
% psql -d update_dump < ../client_dump.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 5138
 setval 
--------
   5428
(1 row)

ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  relation "public.users" does not exist
ERROR:  relation "public.jiins" does not exist

重複確認

=# SELECT ie_id, COUNT(ie_id)
FROM clients
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
.
.
.
(131 rows)

桁数を変えて重複を解消する

update_dump=# update clients SET ie_id = ie_id + 20000 where jiin_id = 2;
UPDATE 777
update_dump=# update clients SET ie_id = ie_id + 30000 where jiin_id = 3;
UPDATE 826
update_dump=# update clients SET ie_id = ie_id + 40000 where jiin_id = 4;
UPDATE 334

主重複解消

update_dump=# update clients set nusi = false where id = 3703;
UPDATE 1
update_dump=# update clients set nusi = false where id = 1346;
UPDATE 1

不要データ削除 修正

update_dump=# delete from clients where id in (4028,3911,3912,2998,3007,4359);
DELETE 0
update_dump=# update clients set nusi = true where id = 523;
UPDATE 1
update_dump=# delete from clients where id = 845;
DELETE 1
update_dump=# update clients set ie_id = 1264 where id = 515;
UPDATE 1
update_dump=# update clients set ie_id = 1264 where id = 1463;
UPDATE 1
update_dump=# update clients set nusi = false where id = 515;
UPDATE 1
update_dump=# delete from clients where id = 217;
DELETE 1
update_dump=# update clients set nusi = false where id = 218;
UPDATE 1
update_dump=# update clients set ie_id = 204 where id = 218;
UPDATE 1
update_dump=# delete from clients where id = 811;
DELETE 1
update_dump=# update clients set nusi = true where id = 1792;
UPDATE 1
update_dump=# delete from clients where id = 810;
DELETE 1
update_dump=# delete from clients where ie_id = 22653;
DELETE 3

確認

update_dump=# select count(*) from clients;
 count 
-------
  5131
(1 row)

テーブル名変更

update_dump=# ALTER TABLE clients RENAME TO clients_old;

新テーブルのスキーマをダンプ リストア

% pg_dump --username=jq --schema-only --table clients tep5_development > ../clients_schema.sql
% psql --username=jq update_dump < ../clients_schema.sql

旧テーブルをセレクトして新テーブルにインサート

update_dump=# insert into clients
(
    id, user_id, note_id, content, client_line, memo, created_at, updated_at
)
select
    ie_id, user_id, jiin_id, name, table_order, memo, created_at, updated_at
from
    clients_old
where
    nusi = true;
INSERT 0 1304

テーブルの増分を求める

update_dump=# SELECT created_at FROM clients_new EXCEPT SELECT created_at FROM clients;
         created_at         
----------------------------
 2022-11-20 11:38:24.975644
 2021-10-09 17:50:13.19454
 2022-11-18 18:24:26.100683
 2022-12-02 14:31:59.651794
 2020-01-05 16:08:24.725981
 2022-10-13 18:18:47.00945
 2022-10-16 12:13:27.960598
 2022-09-19 17:04:20.793626
 2022-10-14 21:58:23.012871
 2022-09-26 18:41:40.679061
(10 rows)
update_dump=# select * from clients_new where created_at > '2022-09-19';

書き出す

% psql -d update_dump -U jq -c "select * from clients_new where created_at > '2022-09-19'" -F, > ../clients_add.csv

増分をインサートする

tep5_development=# INSERT INTO clients (id, user_id, note_id, list_id, content, created_at, updated_at) VALUES
(35375,2,3,1,'支所','2022-11-18 18:24:26.100683','2022-11-18 18:24:26.108051'),
(5360,2,1,1,'広島門会','2022-10-13 18:18:47.00945 ','2022-10-13 18:18:47.023412'),
.
.
.
(25362,13,2,1,'岡山族会','2022-10-16 12:13:27.960598','2022-10-16 12:13:27.968872');
INSERT 0 7

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212151515.dump

2022-12 -14 othersテーブル[編集]

09-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

コピーコマンドで追加ができる

memoの改行削除が必要 vim中でcontrol+v control+mで^Mが入力できる :%s/^M//gc

=# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER;
=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;
COPY 8

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212151649.dump

旧clientsテーブルをリストアする[編集]

% pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql
% 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テーブル[編集]

12-16[編集]

確認

latest_dump=# select count(*) from places;
 count 
-------
   921
=# 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

以前[編集]

確認

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テーブル[編集]

12-16[編集]

確認

=# select count(*) from schedules;
 count 
-------
  3716

=# select count(*) from schedules;
 count 
-------
  4026

書き出し 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

以前[編集]

確認

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;

データ修正

=# update accounts set debit_credit = 1 where id = 43;

変更したテーブルをリストアし アプリのデータベースにコピーする

% 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

仕訳帳テーブル[編集]

12-16 17:18[編集]

確認

latest_dump=# select count(*) from journals;
 count 
-------
 14885

最新をテーブルダンプする

% pg_dump --username=jq --table journals latest_dump > ../update_journals.sql

update_dumpデータベースにリストアする

% psql --username=jq update_dump < ../update_journals.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 14885
update_dump=# ALTER TABLE journals RENAME COLUMN client_id TO other_id;
ALTER TABLE
update_dump=# ALTER TABLE journals ADD COLUMN client_id integer;
ALTER TABLE
update_dump=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
ALTER TABLE
=# update journals set account_id = 99 where account_id is null;
UPDATE 10379
% pg_dump -t journals update_dump > ../journals_latest.sql
tep5_development=# delete from journals ;
DELETE 14719
tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY;
TRUNCATE TABLE
tep5_development=# select * from journals_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
% psql --username=jq tep5_development < ../journals_latest.sql
SET
SET
SET
SET
SET
 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 14885
 setval 
--------
  15199
(1 row)

ERROR:  multiple primary keys for table "journals" are not allowed
ERROR:  relation "index_journals_on_account_id" already exists
ERROR:  relation "index_journals_on_credit_and_credit_money_and_account_id" already exists
ERROR:  relation "index_journals_on_credit_s" already exists
ERROR:  relation "index_journals_on_debit_and_client_id_and_debit_money" already exists
ERROR:  relation "index_journals_on_debit_s" already exists
ERROR:  relation "index_journals_on_description_id_and_schedule_id" already exists
ERROR:  relation "index_journals_on_jiin_id" already exists
ERROR:  relation "index_journals_on_user_id" already exists
ERROR:  relation "index_journals_on_user_id_and_jiin_id_and_journal_number" already exists

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212170834.dump

以前[編集]

確認

=# select count(*) from journals;
 count 
-------
 14709
(1 row)
% 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

リストアして作業データベースにコピーする

% 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

リストア コピー

jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql               
SET
SET
SET
SET
SET
 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)

ERROR:  multiple primary keys for table "journals" are not allowed
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  relation "index_journals_on_user_id" already exists
CREATE INDEX
ERROR:  relation "public.jiins" does not exist
ALTER TABLE

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202209270816_tep5.sql

替え玉[編集]

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');
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');

確認

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)
update_heroku=# select count(*) from journals where account_id is null;
 count 
-------
 10379
(1 row)

替え玉挿入

update_heroku=# update journals set account_id = 99 where account_id is null;
UPDATE 10379

摘要テーブル 変更あり[編集]

確認

heroku_dump=# select count(*) from descriptions;
 count 
-------
   640
(1 row)
% 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
% psql --username=jq tep5_development < ../new_descriptions.sql
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "descriptions" already exists
ALTER TABLE
ERROR:  relation "descriptions_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 640
 setval 
--------
    871
(1 row)

ERROR:  multiple primary keys for table "descriptions" are not allowed
ERROR:  relation "index_descriptions_on_account_id" already exists
CREATE INDEX
ERROR:  relation "index_descriptions_on_user_id" already exists
CREATE INDEX
ALTER TABLE
ERROR:  relation "public.jiins" does not exist


ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202209270816_tep5.sql

戒名テーブル 変更あり[編集]

12-17 11:14[編集]

確認

latest_dump=# select count(*) from kaimyous;
 count 
-------
  3445

増分を書き出す

% 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に対応するために書き出した

コピーで追加

=# \COPY kaimyous FROM '../kaimyous_add.csv' DELIMITER ',' CSV HEADER;
COPY 70

以前[編集]

確認

heroku_dump=# select count(*) from kaimyous;
 count 
-------
  3375
(1 row)
rails generate scaffold Kaimyou user:references note:references client:references other:references content:string birth:date death:date g_age:string relationship:string memo:text

(UUID削除 index追加)

% rails db:migrate

リストアして作業データベースにコピーする

% pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql
% psql --username=jq update_heroku < ../new_kaimyous.sql

テーブルの変更

update_heroku=# ALTER TABLE kaimyous RENAME COLUMN jiin_id TO note_id;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO client_id;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous RENAME COLUMN date TO death;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v;
ALTER TABLE
update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text;
ALTER TABLE

新テーブルのclient_id other_idに合わせる other_idは一意なので変更しない

update_heroku=# update kaimyous SET client_id = client_id + 20000 where note_id = 2;
UPDATE 587
update_heroku=# update kaimyous SET client_id = client_id + 30000 where note_id = 3;
UPDATE 662
update_heroku=# update kaimyous SET client_id = client_id + 40000 where note_id = 4;
UPDATE 256


エラー clientsテーブルのidとkaimyousテーブルのclient_idを比較する

update_heroku=# SELECT id FROM clients
EXCEPT
SELECT client_id FROM kaimyous 
UNION ALL
SELECT client_id FROM kaimyous 
EXCEPT
SELECT id FROM clients order by id;
  id   
-------
    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
(48 rows)

書き出す

% psql update_heroku -c "SELECT id FROM clients
EXCEPT
SELECT client_id FROM kaimyous
UNION ALL
SELECT client_id FROM kaimyous
EXCEPT
SELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv

,挿入 改行削除

:%s/\n//g

削除する戒名を書き出す

% psql 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

削除

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

ダンプ リストア

jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_kaimyous.sql
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "kaimyous" already exists
ALTER TABLE
ERROR:  relation "kaimyous_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 3294
 setval 
--------
   4230
(1 row)

ERROR:  multiple primary keys for table "kaimyous" are not allowed
ERROR:  relation "index_kaimyous_on_client_id" already exists
ERROR:  relation "index_kaimyous_on_client_id_and_content" already exists
ERROR:  relation "index_kaimyous_on_date" already exists
ERROR:  relation "index_kaimyous_on_ie_id" already exists
ERROR:  relation "index_kaimyous_on_jiin_id" already exists
ERROR:  relation "index_kaimyous_on_user_id" already exists
ERROR:  relation "index_kaimyous_on_user_id_and_jiin_id" already exists
ERROR:  relation "public.jiins" does not exist

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202210031034_tep5.sql

通帳テーブル[編集]

確認

heroku_dump=# select count(*) from bankbooks;
 count 
-------
    36
% rails generate scaffold Bankbook user:references note:references content:string memo:text

やり直し

% 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 db:migrate

リストアして作業データベースにコピーする

% pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql
% psql --username=jq update_heroku < ../new_bankbooks.sql

テーブルの変更

update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id;
ALTER TABLE

ダンプ リストア

jq@MacBook-Pro tep5 % pg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sql
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "bankbooks" already exists
ALTER TABLE
ERROR:  relation "bankbooks_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 36
 setval 
--------
     68
(1 row)

ERROR:  multiple primary keys for table "bankbooks" are not allowed
CREATE INDEX
ERROR:  relation "index_bankbooks_on_user_id" already exists
CREATE INDEX
ERROR:  relation "public.jiins" does not exist

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202211021454_tep5.sql

relationshipsテーブル[編集]

確認

latest_dump=# select count(*) from relationships;
 count 
-------
    40

relationshipsテーブル ダンプ

% pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql

update_dumpデータベースにリストアしてロール列を削除する

% psql --username=jq update_dump < ../new_relationships.sql
=# ALTER TABLE relationships DROP COLUMN role;

修正したテーブルをダンプする

% pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql

リストアする

% psql -d tep5_development < ../relationships_dump.sql

バックアップを取る -Fc カスタム形式 圧縮ファイル形式

% pg_dump -Fc tep5_development > ../202212141027.dump

house_note[編集]

旧データベースからダンプ

% pg_dump --username=jq --table house_notes latest_dump > ../hns.sql

サーバーへ転送

% rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/hns.sql jq:/home/jq/dump_data

リストア

$ psql update_dump < ../hns.sql

clientsのidとhouse_noteのidをあわせる

tep5_production=# update house_notes SET ie_id = ie_id + 20000 where jiin_id = 2;
UPDATE 30
tep5_production=# update house_notes SET ie_id = ie_id + 30000 where jiin_id = 3;
UPDATE 114
tep5_production=# update house_notes SET ie_id = ie_id + 40000 where jiin_id = 4;
UPDATE 25

セレクトしてアップデートする

tep5_production=# UPDATE clients as Cl SET memo = (SELECT content FROM house_notes as Hn WHERE Hn.ie_id = Cl.id);

削除

=# drop table house_notes ;

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 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

カラム追加[編集]

% 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) ======================

外部キー制約に変更[編集]

rails generate migration AddScheduleReferenceToJournal schedule:references

サーバーへ転送[編集]

除外するファイルをまとめる

exclude-file.txt
    production.rb
    database.yml

すべて

% 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

database.yml username:などを確認する (2箇所)

/config/environments/production.rb

データベース削除

~$ dropdb tep5_production
または
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

シーケンス操作[編集]

確認

=# select * from others_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
         14 |      30 | t

最大値確認

=# select max(id) from others;
 max  
------
 5428

変更

=# SELECT setval('others_id_seq', 5428, true);
 setval 
--------
   5428

バックアップ[編集]

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を比較する 不整合があればテーブルを修正する

=# SELECT id FROM others
EXCEPT
SELECT other_id FROM journals 
UNION ALL
SELECT other_id FROM journals 
EXCEPT
SELECT id FROM others order by id;
  id  
------
 2435

外部キー制約を追加するmigration ファイルをつくる

 % 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

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を戻すとき次のようなエラーがでる

=# 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".

journalsテーブルのschedule_idがschedulesテーブルにないのでそれを拾い出して削除する

=# delete from journals where hoge_id in (
SELECT id FROM schedules
EXCEPT
SELECT hoge_id FROM journals 
UNION ALL
SELECT hoge_id FROM journals 
EXCEPT
SELECT id FROM schedules);

gemアップデート[編集]

確認

% gem -v

アップデート対象Gemを一覧表示

% bundle outdated

備忘[編集]

現在UUIDコメントアウト中 config/initializers/generators.rb