最新版 |
編集中の文章 |
1行目: |
1行目: |
| [[category:memo|{{PAGENAME}}]] | | [[category:memo|{{PAGENAME}}]] |
| {{Font color}} | | {{Font color}} |
− | == tep5 準備 == | + | == 準備 == |
| プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] | | プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] |
| | | |
178行目: |
178行目: |
| === clientsテーブル othersテーブル === | | === clientsテーブル othersテーブル === |
| | | |
− | ==== 2022-12 -14 clientsテーブル ==== | + | ==== 2022-12 -14==== |
| latest_dumpデータベースからダンプして 作業データベースにリストアする | | latest_dumpデータベースからダンプして 作業データベースにリストアする |
| % pg_dump -t clients latest_dump > ../client_dump.sql | | % pg_dump -t clients latest_dump > ../client_dump.sql |
231行目: |
231行目: |
| 2167 | 2 | | 2167 | 2 |
| 2174 | 2 | | 2174 | 2 |
− | . | + | .............</nowiki> |
− | . | |
− | . | |
− | (131 rows)</nowiki>
| |
| | | |
| 桁数を変えて重複を解消する | | 桁数を変えて重複を解消する |
253行目: |
250行目: |
| <nowiki>update_dump=# delete from clients where id in (4028,3911,3912,2998,3007,4359); | | <nowiki>update_dump=# delete from clients where id in (4028,3911,3912,2998,3007,4359); |
| DELETE 0</nowiki> | | DELETE 0</nowiki> |
− | <nowiki>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</nowiki>
| |
| | | |
− | 確認
| + | ==== 旧clientsテーブルをリストアする ==== |
− | <nowiki>update_dump=# select count(*) from clients;
| + | % pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql |
− | count | |
− | -------
| |
− | 5131
| |
− | (1 row)</nowiki>
| |
| | | |
− | テーブル名変更
| + | <nowiki>% psql --username=jq tep5_development < ../old_other.sql |
− | update_dump=# ALTER TABLE clients RENAME TO clients_old;
| + | SET |
− | | + | SET |
− | 新テーブルのスキーマをダンプ リストア
| + | SET |
− | % pg_dump --username=jq --schema-only --table clients tep5_development > ../clients_schema.sql
| |
− | % psql --username=jq update_dump < ../clients_schema.sql
| |
− | | |
− | 旧テーブルをセレクトして新テーブルにインサート
| |
− | <nowiki>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</nowiki>
| |
− | | |
− | テーブルの増分を求める
| |
− | <nowiki>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)</nowiki>
| |
− | | |
− | 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
| |
− | | |
− | 増分をインサートする
| |
− | <nowiki>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</nowiki>
| |
− | | |
− | ここまでのバックアップを取る
| |
− | % 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;
| |
− | | |
− | <nowiki>=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;
| |
− | COPY 8</nowiki>
| |
− | | |
− | ここまでのバックアップを取る
| |
− | % pg_dump -Fc tep5_development > ../202212151649.dump
| |
− | | |
− | ==== 旧clientsテーブルをリストアする ====
| |
− | % pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql
| |
− | | |
− | <nowiki>% psql --username=jq tep5_development < ../old_other.sql | |
− | SET | |
− | SET | |
− | SET | |
| SET | | SET |
| SET | | SET |
537行目: |
431行目: |
| | | |
| === placesテーブル === | | === 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; | | <nowiki>heroku_dump=# select count(*) from places; |
568行目: |
441行目: |
| dump | | dump |
| % pg_dump --username=jq --table places_old heroku_dump > places_old.sql | | % pg_dump --username=jq --table places_old heroku_dump > places_old.sql |
− |
| |
| ==== 新データベースにリストア ==== | | ==== 新データベースにリストア ==== |
| <nowiki>% psql --username=jq tep5_development < ../places_old.sql | | <nowiki>% psql --username=jq tep5_development < ../places_old.sql |
621行目: |
493行目: |
| 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 |
| | | |
665行目: |
537行目: |
| | | |
| === schedulesテーブル === | | === 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; | | <nowiki>heroku_dump=# select count(*) from schedules; |
901行目: |
746行目: |
| | | |
| === 仕訳帳テーブル === | | === 仕訳帳テーブル === |
− | ==== 12-16 17:18 ====
| |
| 確認 | | 確認 |
− | <nowiki>latest_dump=# select count(*) from journals; | + | <nowiki>=# select count(*) from journals; |
| count | | count |
| ------- | | ------- |
− | 14885</nowiki> | + | 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 |
− | % pg_dump --username=jq --table journals latest_dump > ../update_journals.sql | |
| | | |
− | update_dumpデータベースにリストアする
| + | % rails db:migrate |
− | <nowiki>% psql --username=jq update_dump < ../update_journals.sql | + | |
| + | リストアして作業データベースにコピーする |
| + | <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=# 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> |
| + | |
| + | リストア コピー |
| + | <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 |
929行目: |
791行目: |
| SET | | SET |
| SET | | SET |
− | CREATE TABLE
| + | ERROR: relation "journals" already exists |
| ALTER TABLE | | ALTER TABLE |
− | CREATE SEQUENCE
| + | ERROR: relation "journals_id_seq" already exists |
| ALTER TABLE | | ALTER TABLE |
| ALTER SEQUENCE | | ALTER SEQUENCE |
| ALTER TABLE | | ALTER TABLE |
− | COPY 14885</nowiki> | + | COPY 14709 |
− | | + | setval |
− | update_dump=# ALTER TABLE journals RENAME COLUMN client_id TO other_id;
| + | -------- |
− | ALTER TABLE
| + | 15023 |
| + | (1 row) |
| | | |
− | update_dump=# ALTER TABLE journals ADD COLUMN client_id integer; | + | ERROR: multiple primary keys for table "journals" are not allowed |
− | ALTER TABLE | + | 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</nowiki> |
| | | |
− | update_dump=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;
| + | ダンプ ここまで形を整えたデータベースをバックアップしとく |
− | ALTER TABLE | + | % pg_dump tep5_development > ../202209270816_tep5.sql |
| | | |
− | =# update journals set account_id = 99 where account_id is null; | + | ==== 替え玉 ==== |
− | UPDATE 10379 | + | 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'); |
| | | |
− | % pg_dump -t journals update_dump > ../journals_latest.sql
| + | 確認 |
− | | + | <nowiki>tep5_development=# select * from accounts order by id desc limit 1; |
− | <nowiki>tep5_development=# delete from journals ; | + | id | account_cd | content | bs_pl | debit_credit | created_at | updated_at |
− | DELETE 14719
| + | ----+------------+---------+-------+--------------+---------------------+--------------------- |
− | tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY;
| + | 99 | 9999 | 替え玉 | 1 | 1 | 2022-09-28 00:00:00 | 2022-09-28 00:00:00 |
− | TRUNCATE TABLE
| + | (1 row)</nowiki> |
− | tep5_development=# select * from journals_id_seq ; | |
− | last_value | log_cnt | is_called | |
− | ------------+---------+----------- | |
− | 1 | 0 | f</nowiki>
| |
| | | |
− | <nowiki>% psql --username=jq tep5_development < ../journals_latest.sql | + | <nowiki>update_heroku=# select count(*) from journals where account_id is null; |
− | SET
| + | count |
− | SET
| + | ------- |
− | SET
| + | 10379 |
− | SET
| + | (1 row)</nowiki> |
− | SET
| + | |
− | set_config | + | 替え玉挿入 |
− | ------------
| + | <nowiki>update_heroku=# update journals set account_id = 99 where account_id is null; |
− | | + | UPDATE 10379</nowiki> |
− | (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</nowiki>
| |
− | | |
− | ここまでのバックアップを取る
| |
− | % pg_dump -Fc tep5_development > ../202212170834.dump
| |
− | | |
− | ==== 以前 ==== | |
| 確認 | | 確認 |
− | <nowiki>=# select count(*) from journals; | + | <nowiki>heroku_dump=# select count(*) from descriptions; |
| count | | count |
| ------- | | ------- |
− | 14709
| + | 640 |
| (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 | + | % 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 |
| | | |
| リストアして作業データベースにコピーする | | リストアして作業データベースにコピーする |
− | <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql | + | % pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql |
− | % psql --username=jq update_heroku < ../new_journals.sql</nowiki> | + | % psql --username=jq update_heroku < ../new_descriptions.sql |
| | | |
| テーブルの変更 | | テーブルの変更 |
− | =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id; | + | =# ALTER TABLE descriptions 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>
| |
| | | |
| リストア コピー | | リストア コピー |
− | <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql | + | % pg_dump --username=jq --table descriptions update_heroku > ../new_descriptions.sql |
− | jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql
| + | <nowiki>% psql --username=jq tep5_development < ../new_descriptions.sql |
| SET | | SET |
| SET | | SET |
1,049行目: |
880行目: |
| SET | | SET |
| SET | | SET |
− | ERROR: relation "journals" already exists | + | ERROR: relation "descriptions" already exists |
| ALTER TABLE | | ALTER TABLE |
− | ERROR: relation "journals_id_seq" already exists | + | ERROR: relation "descriptions_id_seq" already exists |
| ALTER TABLE | | ALTER TABLE |
| ALTER SEQUENCE | | ALTER SEQUENCE |
| ALTER TABLE | | ALTER TABLE |
− | COPY 14709 | + | COPY 640 |
| setval | | setval |
| -------- | | -------- |
− | 15023
| + | 871 |
| (1 row) | | (1 row) |
| | | |
− | ERROR: multiple primary keys for table "journals" are not allowed | + | ERROR: multiple primary keys for table "descriptions" 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 |
− | CREATE INDEX
| + | ALTER TABLE |
− | CREATE INDEX
| + | ERROR: relation "public.jiins" does not exist</nowiki> |
− | 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>tep5_development=# select * from accounts order by id desc limit 1; | + | <nowiki>heroku_dump=# select count(*) from kaimyous; |
− | 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
| + | 3375 |
| (1 row)</nowiki> | | (1 row)</nowiki> |
| | | |
− | <nowiki>update_heroku=# select count(*) from journals where account_id is null; | + | 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 |
− | count
| |
− | -------
| |
− | 10379
| |
− | (1 row)</nowiki>
| |
− | | |
− | 替え玉挿入
| |
− | <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,113行目: |
919行目: |
| | | |
| リストアして作業データベースにコピーする | | リストアして作業データベースにコピーする |
− | % pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql | + | % pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql |
− | % psql --username=jq update_heroku < ../new_descriptions.sql | + | % psql --username=jq update_heroku < ../new_kaimyous.sql |
| | | |
| テーブルの変更 | | テーブルの変更 |
− | =# ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id; | + | <nowiki>update_heroku=# ALTER TABLE kaimyous 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
| |
− | set_config
| |
− | ------------
| |
− |
| |
− | (1 row)
| |
− | | |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | ERROR: relation "descriptions" already exists
| |
| ALTER TABLE | | ALTER TABLE |
− | ERROR: relation "descriptions_id_seq" already exists
| + | update_heroku=# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id; |
| ALTER TABLE | | ALTER TABLE |
− | ALTER SEQUENCE | + | 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 | | ALTER TABLE |
− | COPY 640
| + | update_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v; |
− | 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 | | ALTER TABLE |
− | ERROR: relation "public.jiins" does not exist</nowiki>
| + | update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text; |
| + | ALTER TABLE</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
| |
− |
| |
− | === 戒名テーブル 変更あり ===
| |
− | ==== 12-17 11:14 ====
| |
− | 確認
| |
− | <nowiki>latest_dump=# select count(*) from kaimyous;
| |
− | count
| |
− | -------
| |
− | 3445</nowiki>
| |
| | | |
− | 増分を書き出す
| + | エラー clientsテーブルのidとkaimyousテーブルのclient_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
| + | <nowiki>update_heroku=# SELECT id FROM clients |
− | | + | EXCEPT |
− | ie_id(client_id)は修正する ie_id_vはmemoに対応するために書き出した
| + | SELECT client_id FROM kaimyous |
− | | + | UNION ALL |
− | コピーで追加
| + | SELECT client_id FROM kaimyous |
− | <nowiki>=# \COPY kaimyous FROM '../kaimyous_add.csv' DELIMITER ',' CSV HEADER; | + | EXCEPT |
− | COPY 70</nowiki>
| + | SELECT id FROM clients order by id; |
− | | + | id |
− | ==== 以前 ====
| |
− | 確認
| |
− | <nowiki>heroku_dump=# select count(*) from kaimyous;
| |
− | count
| |
| ------- | | ------- |
− | 3375
| + | 21 |
− | (1 row)</nowiki>
| + | 27 |
− | | + | 35 |
− | 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
| + | 38 |
− | | + | 39 |
− | (UUID削除 index追加)
| + | 44 |
− | | + | 46 |
− | % rails db:migrate
| + | 56 |
− | | + | 57 |
− | リストアして作業データベースにコピーする
| + | 63 |
− | % pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql
| + | 71 |
− | % psql --username=jq update_heroku < ../new_kaimyous.sql
| + | 73 |
− | | + | 78 |
− | テーブルの変更
| + | 89 |
− | <nowiki>update_heroku=# ALTER TABLE kaimyous RENAME COLUMN jiin_id TO note_id;
| + | 101 |
− | ALTER TABLE
| + | 106 |
− | update_heroku=# ALTER TABLE kaimyous RENAME COLUMN client_id TO other_id;
| + | 111 |
− | ALTER TABLE
| + | 126 |
− | update_heroku=# ALTER TABLE kaimyous RENAME COLUMN ie_id TO client_id;
| + | 127 |
− | ALTER TABLE
| + | 135 |
− | update_heroku=# ALTER TABLE kaimyous RENAME COLUMN date TO death;
| + | 144 |
− | ALTER TABLE
| + | 147 |
− | update_heroku=# ALTER TABLE kaimyous ADD COLUMN birth date;
| + | 156 |
− | ALTER TABLE
| + | 173 |
− | update_heroku=# ALTER TABLE kaimyous DROP COLUMN ie_id_v;
| + | 174 |
− | ALTER TABLE
| + | 176 |
− | update_heroku=# ALTER TABLE kaimyous ADD COLUMN memo text;
| + | 186 |
− | ALTER TABLE</nowiki>
| + | 188 |
| + | 189 |
| + | 230 |
| + | 310 |
| + | 373 |
| + | 404 |
| + | 473 |
| + | 701 |
| + | 804 |
| + | 872 |
| + | 936 |
| + | 953 |
| + | 972 |
| + | 998 |
| + | 1590 |
| + | 1671 |
| + | 1777 |
| + | 2249 |
| + | 2290 |
| + | 22653 |
| + | 34681 |
| + | (48 rows)</nowiki> |
| | | |
− | 新テーブルのclient_id other_idに合わせる other_idは一意なので変更しない
| + | 書き出す |
− | <nowiki>update_heroku=# update kaimyous SET client_id = client_id + 20000 where note_id = 2; | + | <nowiki>% psql update_heroku -c "SELECT id FROM clients |
− | 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>
| |
− | | |
− | | |
− | エラー clientsテーブルのidとkaimyousテーブルのclient_idを比較する
| |
− | <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; | + | SELECT id FROM clients order by id;" > ../hikaku/clients_kaimyous_client_id.csv</nowiki> |
− | id | + | |
− | -------
| + | ,挿入 改行削除 |
− | 21
| + | :%s/\n//g |
− | 27
| + | |
− | 35
| + | 削除する戒名を書き出す |
− | 38
| + | <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> |
− | 39
| + | |
− | 44
| + | 削除 |
− | 46
| + | <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); |
− | 56
| + | DELETE 81</nowiki> |
− | 57
| + | |
− | 63
| + | ダンプ リストア |
− | 71
| + | <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sql |
− | 73
| + | jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_kaimyous.sql |
− | 78
| + | SET |
− | 89
| + | SET |
− | 101
| + | SET |
− | 106
| + | SET |
− | 111
| + | SET |
− | 126
| + | set_config |
− | 127
| + | ------------ |
− | 135
| + | |
− | 144
| + | (1 row) |
− | 147
| + | |
− | 156
| + | SET |
− | 173
| + | SET |
− | 174
| + | SET |
− | 176
| + | SET |
− | 186
| + | SET |
− | 188
| + | SET |
− | 189
| + | ERROR: relation "kaimyous" already exists |
− | 230
| + | ALTER TABLE |
− | 310
| + | ERROR: relation "kaimyous_id_seq" already exists |
− | 373
| + | ALTER TABLE |
− | 404
| + | ALTER SEQUENCE |
− | 473
| + | ALTER TABLE |
− | 701
| + | COPY 3294 |
− | 804
| + | setval |
− | 872
| + | -------- |
− | 936
| + | 4230 |
− | 953
| + | (1 row) |
− | 972
| + | |
− | 998
| + | ERROR: multiple primary keys for table "kaimyous" are not allowed |
− | 1590
| + | ERROR: relation "index_kaimyous_on_client_id" already exists |
− | 1671
| + | ERROR: relation "index_kaimyous_on_client_id_and_content" already exists |
− | 1777
| + | ERROR: relation "index_kaimyous_on_date" already exists |
− | 2249
| + | ERROR: relation "index_kaimyous_on_ie_id" already exists |
− | 2290
| + | ERROR: relation "index_kaimyous_on_jiin_id" already exists |
− | 22653 | + | ERROR: relation "index_kaimyous_on_user_id" already exists |
− | 34681 | + | ERROR: relation "index_kaimyous_on_user_id_and_jiin_id" already exists |
− | (48 rows)</nowiki> | + | ERROR: relation "public.jiins" does not exist</nowiki> |
| | | |
− | 書き出す
| + | ダンプ ここまで形を整えたデータベースをバックアップしとく |
− | <nowiki>% psql update_heroku -c "SELECT id FROM clients | + | % pg_dump tep5_development > ../202210031034_tep5.sql |
− | 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</nowiki>
| |
| | | |
− | ,挿入 改行削除
| + | === 通帳テーブル === |
− | :%s/\n//g | + | 確認 |
| + | <nowiki>heroku_dump=# select count(*) from bankbooks; |
| + | count |
| + | ------- |
| + | 36</nowiki> |
| + | |
| + | % 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 |
| | | |
− | 削除する戒名を書き出す
| + | リストアして作業データベースにコピーする |
− | <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> | + | % pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql |
| + | % psql --username=jq update_heroku < ../new_bankbooks.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); | + | update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id; |
− | DELETE 81</nowiki>
| + | ALTER TABLE |
| | | |
| ダンプ リストア | | ダンプ リストア |
− | <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table kaimyous update_heroku > ../new_kaimyous.sql | + | <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_kaimyous.sql | + | jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sql |
| SET | | SET |
| SET | | SET |
1,320行目: |
1,113行目: |
| SET | | SET |
| SET | | SET |
− | ERROR: relation "kaimyous" already exists | + | ERROR: relation "bankbooks" already exists |
| ALTER TABLE | | ALTER TABLE |
− | ERROR: relation "kaimyous_id_seq" already exists | + | ERROR: relation "bankbooks_id_seq" already exists |
| ALTER TABLE | | ALTER TABLE |
| ALTER SEQUENCE | | ALTER SEQUENCE |
| ALTER TABLE | | ALTER TABLE |
− | COPY 3294 | + | COPY 36 |
| setval | | setval |
| -------- | | -------- |
− | 4230
| + | 68 |
| (1 row) | | (1 row) |
| | | |
− | ERROR: multiple primary keys for table "kaimyous" are not allowed | + | ERROR: multiple primary keys for table "bankbooks" are not allowed |
− | ERROR: relation "index_kaimyous_on_client_id" already exists
| + | CREATE INDEX |
− | ERROR: relation "index_kaimyous_on_client_id_and_content" already exists
| + | ERROR: relation "index_bankbooks_on_user_id" already exists |
− | ERROR: relation "index_kaimyous_on_date" already exists
| + | CREATE INDEX |
− | 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 > ../202210031034_tep5.sql | + | % pg_dump tep5_development > ../202211021454_tep5.sql |
| | | |
− | === 通帳テーブル === | + | === relationshipsテーブル === |
| 確認 | | 確認 |
− | <nowiki>heroku_dump=# select count(*) from bankbooks; | + | <nowiki>latest_dump=# select count(*) from relationships; |
| count | | count |
| ------- | | ------- |
− | 36</nowiki> | + | 40</nowiki> |
| | | |
− | % rails generate scaffold Bankbook user:references note:references content:string memo:text | + | relationshipsテーブル ダンプ |
| + | % pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql |
| | | |
− | やり直し
| + | update_dumpデータベースにリストアしてロール列を削除する |
− | % rails destroy scaffold Bankbook user:references note:references content:string memo:text | + | % psql --username=jq update_dump < ../new_relationships.sql |
− | % rails generate scaffold Bankbook user:references note:references content:string bank_line:integer memo:text | + | =# ALTER TABLE relationships DROP COLUMN role; |
| | | |
− | (UUID削除 index追加)
| + | 修正したテーブルをダンプする |
− | % rails db:migrate | + | % pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql |
| | | |
− | リストアして作業データベースにコピーする
| + | リストアする |
− | % pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql
| + | % psql -d tep5_development < ../relationships_dump.sql |
− | % psql --username=jq update_heroku < ../new_bankbooks.sql | |
| | | |
− | テーブルの変更
| + | バックアップを取る -Fc カスタム形式 圧縮ファイル形式 |
− | update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id; | + | % pg_dump -Fc tep5_development > ../202212141027.dump |
− | ALTER TABLE
| |
| | | |
− | ダンプ リストア
| + | == UUIDを有効にする == |
− | <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
| + | % rails generate migration enable_extension_for_uuid |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | set_config | |
− | ------------
| |
− |
| |
− | (1 row)
| |
| | | |
− | SET
| + | 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> |
− | 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
| + | % rails db:migrate |
− | CREATE INDEX
| |
− | ERROR: relation "index_bankbooks_on_user_id" already exists
| |
− | CREATE INDEX
| |
− | ERROR: relation "public.jiins" does not exist</nowiki>
| |
| | | |
− | ダンプ ここまで形を整えたデータベースをバックアップしとく
| + | デフォルトのキーをuuidにする |
− | % pg_dump tep5_development > ../202211021454_tep5.sql | + | <nowiki>config/initializers/generators.rb 作成 |
| | | |
− | === relationshipsテーブル ===
| + | Rails.application.config.generators do |g| |
− | 確認
| + | g.orm :active_record, primary_key_type: :uuid |
− | <nowiki>latest_dump=# select count(*) from relationships;
| + | end</nowiki> |
− | count
| |
− | -------
| |
− | 40</nowiki>
| |
| | | |
− | relationshipsテーブル ダンプ
| + | === 途中からUUIDに変更する場合 === |
− | % pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql | + | migrationファイル追加 |
| + | <nowiki> % rails generate migration enable_extension_for_uuid |
| | | |
− | update_dumpデータベースにリストアしてロール列を削除する
| + | % rails generate migration change_user_id_type_to_uuid |
− | % psql --username=jq update_dump < ../new_relationships.sql
| |
− | =# ALTER TABLE relationships DROP COLUMN role;
| |
| | | |
− | 修正したテーブルをダンプする
| + | % rails destroy migration change_user_id_type_to_uuid |
− | % pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql
| |
| | | |
− | リストアする
| + | % rails generate migration change_users_id_type_to_uuid |
− | % psql -d tep5_development < ../relationships_dump.sql
| |
| | | |
− | バックアップを取る -Fc カスタム形式 圧縮ファイル形式
| + | % rails generate migration change_notes_id_type_to_uuid |
− | % pg_dump -Fc tep5_development > ../202212141027.dump
| |
| | | |
− | == house_note ==
| + | % rails generate migration change_lists_id_type_to_uuid |
− | 旧データベースからダンプ
| |
− | % pg_dump --username=jq --table house_notes latest_dump > ../hns.sql
| |
| | | |
− | サーバーへ転送
| + | % rails generate migration change_clients_id_type_to_uuid |
− | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/hns.sql jq:/home/jq/dump_data
| |
| | | |
− | リストア
| + | % rails generate migration change_others_id_type_to_uuid</nowiki> |
− | $ psql update_dump < ../hns.sql
| |
| | | |
− | clientsのidとhouse_noteのidをあわせる
| + | === UUIDに変更したデータベースをDUMPする === |
− | <nowiki>tep5_production=# update house_notes SET ie_id = ie_id + 20000 where jiin_id = 2; | + | ==== 修正した旧データのテーブルも一緒にDUMPしとく ==== |
− | UPDATE 30
| + | ==== 不要なテーブルを削除し新しいUUIDのデータベースをDUMPする ==== |
− | tep5_production=# update house_notes SET ie_id = ie_id + 30000 where jiin_id = 3;
| + | <nowiki>tep5_development=# drop table clients_old ; |
− | UPDATE 114
| + | DROP TABLE |
− | tep5_production=# update house_notes SET ie_id = ie_id + 40000 where jiin_id = 4;
| + | tep5_development=# drop table jiins ; |
− | UPDATE 25</nowiki>
| + | DROP TABLE |
| + | tep5_development=# drop table users_old ;</nowiki> |
| | | |
− | セレクトしてアップデートする
| + | % pg_dump tep5_development > tep5_uuid.sql |
− | tep5_production=# UPDATE clients as Cl SET memo = (SELECT content FROM house_notes as Hn WHERE Hn.ie_id = Cl.id); | |
| | | |
− | 削除
| + | == generate == |
− | =# drop table house_notes ;
| |
| | | |
− | == UUIDを有効にする ==
| + | $ 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 |
− | === 設定 ===
| |
− | % rails generate migration enable_extension_for_uuid | |
| | | |
− | migrate編集
| + | {{font color||yellow|migrationファイルを確認 indexを書く 以下同じ}} |
− | <nowiki>class EnableExtensionForUuid < ActiveRecord::Migration[7.0]
| |
− | def change
| |
− | enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
| |
− | end
| |
− | end</nowiki>
| |
| | | |
− | % rails db:migrate | + | % rails generate scaffold Note user:references content:string kind:integer meeting_time:integer role:integer note_line:integer |
| | | |
− | デフォルトのキーをuuidにする
| + | % rails generate scaffold List user:references note:references content:string list_line:integer |
− | <nowiki>config/initializers/generators.rb 作成 | |
| | | |
− | Rails.application.config.generators do |g|
| + | % rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:text |
− | g.orm :active_record, primary_key_type: :uuid
| |
− | end</nowiki>
| |
| | | |
− | === 途中からUUIDに変更する場合 ===
| + | % rails generate scaffold Other user:references note:references client:references name:string kana:string client_name:boolean other_line:integer memo:text generation:string |
− | migrationファイル追加
| |
− | <nowiki> % rails generate migration enable_extension_for_uuid | |
| | | |
− | % rails generate migration change_user_id_type_to_uuid | + | % rails generate scaffold Category user:references note:references content:string category_line:integer |
| | | |
− | % rails destroy migration change_user_id_type_to_uuid | + | % rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer |
| | | |
− | % rails generate migration change_users_id_type_to_uuid | + | % rails generate model Relationship follower_id:integer followed_id:integer |
| | | |
− | % rails generate migration change_notes_id_type_to_uuid
| + | $ rails generate controller Relationships |
| | | |
− | % rails generate migration change_lists_id_type_to_uuid | + | やり直し |
| + | % rails destroy scaffold User ................. |
| | | |
− | % rails generate migration change_clients_id_type_to_uuid
| + | $ rails generate scaffold Post content:text user:references picture:string |
| | | |
− | % rails generate migration change_others_id_type_to_uuid</nowiki>
| + | $ rails generate model Relationship follower_id:integer followed_id:integer |
| | | |
− | === UUIDに変更したデータベースをDUMPする ===
| + | $ rails db:migrate RAILS_ENV=production |
− | ==== 修正した旧データのテーブルも一緒に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>
| |
| | | |
− | % pg_dump tep5_development > tep5_uuid.sql | + | $ bin/dev |
| | | |
− | == generate ==
| + | $ rails generate controller StaticPages home help about contact |
| | | |
− | $ 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. |
| | | |
− | {{font color||yellow|migrationファイルを確認 indexを書く 以下同じ}}
| + | config/envitonments/production.rb |
| + | 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 |
| + | production.rb |
| + | database.yml</nowiki> |
| | | |
− | % rails generate scaffold List user:references note:references content:string list_line:integer | + | % 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 | + | == サーバー側 == |
| + | $ bundle install --without test development |
| | | |
− | % rails generate scaffold Category user:references note:references content:string category_line:integer
| + | {{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 | + | データベース削除 |
| + | postgres=# DROP DATABASE tep5_production; |
| | | |
− | $ rails generate controller Relationships | + | データベース作成 |
| + | $ createdb tep5_production |
| | | |
− | やり直し
| + | リストア |
− | % rails destroy scaffold User ................. | + | <nowiki>$ psql tep5_production < ../dump_data/tep5_uuid_dump.sql |
− | | + | SET |
− | $ rails generate scaffold Post content:text user:references picture:string | + | SET |
− | | + | SET |
− | $ rails generate scaffold Post user:references title:string content:text notification_time:datetime reminder_type:integer post_type:integer picture:string | + | SET |
| + | SET |
| + | set_config |
| + | ------------ |
| + | |
| + | (1 row) |
| | | |
− | $ rails generate model Relationship follower_id:integer followed_id:integer
| + | SET |
− | | + | SET |
− | $ rails db:migrate RAILS_ENV=production
| + | SET |
− | | + | SET |
− | $ bin/dev
| + | CREATE EXTENSION |
− | | + | COMMENT |
− | $ rails generate controller StaticPages home help about contact
| + | SET |
− | | + | SET |
− | エラー
| + | CREATE TABLE |
− | ActionView::Template::Error (The asset "tailwind.css" is not present in the asset pipeline.
| + | ALTER TABLE |
− | | + | CREATE TABLE |
− | config/envitonments/production.rb
| + | ALTER TABLE |
− | config.assets.compile = true
| + | CREATE TABLE |
− | | + | ALTER TABLE |
− | === カラム追加 ===
| + | CREATE TABLE |
− | <nowiki>% rails generate migration add_memo_to_schedules memo:text
| + | ALTER TABLE |
− | invoke active_record
| + | CREATE TABLE |
− | create db/migrate/20230311013341_add_memo_to_schedules.rb
| + | ALTER TABLE |
− | % rails db:migrate
| + | CREATE TABLE |
− | == 20230311013341 AddMemoToSchedules: migrating ===============================
| + | ALTER TABLE |
− | -- add_column(:schedules, :memo, :text)
| + | CREATE TABLE |
− | -> 0.0056s
| + | ALTER TABLE |
− | == 20230311013341 AddMemoToSchedules: migrated (0.0057s) ======================</nowiki>
| + | COPY 1 |
− | | + | COPY 1295 |
− | === 外部キー制約に変更 ===
| + | COPY 1 |
− | rails generate migration AddScheduleReferenceToJournal schedule:references
| + | COPY 14 |
− | | + | COPY 4747 |
− | == サーバーへ転送 ==
| + | COPY 11 |
− | 除外するファイルをまとめる
| + | COPY 17 |
− | <nowiki>exclude-file.txt
| + | ALTER TABLE |
− | production.rb
| + | ALTER TABLE |
− | database.yml</nowiki>
| + | ALTER TABLE |
− | | + | ALTER TABLE |
− | すべて
| + | ALTER TABLE |
− | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/
| + | ALTER TABLE |
− | | + | ALTER TABLE |
− | appのみ
| + | CREATE INDEX |
− | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/app/ jq:/home/jq/tep5/app/
| + | CREATE INDEX |
− | | + | CREATE INDEX |
− | データベース
| + | CREATE INDEX |
− | % rsync ~/rails_app_tepnote/202212170834.dump jq:/home/jq/dump_data
| + | CREATE INDEX |
− | | + | CREATE INDEX |
− | == サーバー側 ==
| + | CREATE INDEX |
− | リストア
| + | CREATE INDEX |
− | $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_ production ../dump_data/202212141027.dump
| + | CREATE INDEX |
− | | + | CREATE INDEX |
− | $ bundle install --without test development
| + | ALTER TABLE |
− | | + | ALTER TABLE |
− | {{font color||yellow|database.yml username:などを確認する (2箇所)}}
| + | ALTER TABLE |
− | | + | ALTER TABLE |
− | /config/environments/production.rb
| + | ALTER TABLE |
− | | + | ALTER TABLE |
− | データベース削除
| + | ALTER TABLE |
− | ~$ dropdb tep5_production
| + | ALTER TABLE |
− | または
| + | ALTER TABLE</nowiki> |
− | postgres=# DROP DATABASE tep5_production;
| |
| | | |
− | データベース作成
| + | $ rails db:migrate RAILS_ENV=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 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</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
| |
− | | |
− | == 備忘 ==
| |
− | 現在UUIDコメントアウト中 config/initializers/generators.rb
| |