「Tep3-onk-2」の版間の差分
(→参考) |
(→あとから外部キー制約を追加する手順) |
||
(同じ利用者による、間の221版が非表示) | |||
1行目: | 1行目: | ||
[[category:memo|{{PAGENAME}}]] | [[category:memo|{{PAGENAME}}]] | ||
{{Font color}} | {{Font color}} | ||
− | == 準備 == | + | == tep5 準備 == |
プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] | プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] | ||
== new == | == new == | ||
− | % rails new . -d postgresql --css tailwind | + | % rails new . -d postgresql --css tailwind または bootstrap |
% bundle install | % bundle install | ||
% rails db:create | % rails db:create | ||
12行目: | 12行目: | ||
% rails db:migrate | % rails db:migrate | ||
% rails server | % rails server | ||
+ | |||
+ | bootstrapのとき | ||
+ | <nowiki> | ||
+ | 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" | ||
+ | } | ||
+ | }</nowiki> | ||
=== daisyui === | === daisyui === | ||
29行目: | 50行目: | ||
== データベース移行 == | == データベース移行 == | ||
− | === | + | === Herokuからdumpしてローカルに{{font color||yellow|heroku_dumpデータベースを新設し}}リストアする === |
$ heroku pg:backups:capture | $ heroku pg:backups:capture | ||
36行目: | 57行目: | ||
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump | $ 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 | ||
=== 作り直すアプリはデータベース構造を変えるのでテーブルごとにダンプ・リストアする === | === 作り直すアプリはデータベース構造を変えるのでテーブルごとにダンプ・リストアする === | ||
148行目: | 172行目: | ||
jiins; | jiins; | ||
INSERT 0 14</nowiki> | INSERT 0 14</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
=== clientsテーブル othersテーブル === | === clientsテーブル othersテーブル === | ||
+ | |||
+ | ==== 2022-12 -14 clientsテーブル ==== | ||
+ | latest_dumpデータベースからダンプして 作業データベースにリストアする | ||
+ | % pg_dump -t clients latest_dump > ../client_dump.sql | ||
+ | |||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | 重複確認 | ||
+ | <nowiki>=# 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)</nowiki> | ||
+ | |||
+ | 桁数を変えて重複を解消する | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | 主重複解消 | ||
+ | <nowiki>update_dump=# update clients set nusi = false where id = 3703; | ||
+ | UPDATE 1 | ||
+ | update_dump=# update clients set nusi = false where id = 1346; | ||
+ | UPDATE 1</nowiki> | ||
+ | |||
+ | 不要データ削除 修正 | ||
+ | <nowiki>update_dump=# delete from clients where id in (4028,3911,3912,2998,3007,4359); | ||
+ | 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> | ||
+ | |||
+ | 確認 | ||
+ | <nowiki>update_dump=# select count(*) from clients; | ||
+ | count | ||
+ | ------- | ||
+ | 5131 | ||
+ | (1 row)</nowiki> | ||
+ | |||
+ | テーブル名変更 | ||
+ | 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 | ||
+ | |||
+ | 旧テーブルをセレクトして新テーブルにインサート | ||
+ | <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 | % pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql | ||
− | |||
<nowiki>% psql --username=jq tep5_development < ../old_other.sql | <nowiki>% psql --username=jq tep5_development < ../old_other.sql | ||
SET | SET | ||
245行目: | 452行目: | ||
DELETE 1 | DELETE 1 | ||
tep5_development=# delete from clients_old where ie_id = 22653; | tep5_development=# delete from clients_old where ie_id = 22653; | ||
− | DELETE 3 | + | DELETE 3</nowiki> |
− | |||
− | + | listsテーブルに一つデータをいれる | |
− | + | =# insert into lists (user_id , note_id, content, created_at, updated_at)VALUES (1, 1, 'リスト', '2022-9-18', '2022-9-18'); | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
nusiをセレクトして新clientsにインサート | nusiをセレクトして新clientsにインサート | ||
288行目: | 488行目: | ||
clients_old; | clients_old; | ||
INSERT 0 4747</nowiki> | INSERT 0 4747</nowiki> | ||
+ | |||
+ | ==== 2つのテーブルが同じならコピーされる ==== | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
+ | |||
+ | === 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; | ||
+ | count | ||
+ | ------- | ||
+ | 919 | ||
+ | (1 row)</nowiki> | ||
+ | テーブル名変更 | ||
+ | heroku_dump=# ALTER TABLE places RENAME TO places_old; | ||
+ | dump | ||
+ | % pg_dump --username=jq --table places_old heroku_dump > places_old.sql | ||
+ | |||
+ | ==== 新データベースにリストア ==== | ||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | <nowiki>=# 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</nowiki> | ||
+ | |||
+ | 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消す | ||
+ | |||
+ | {{font color||yellow|migrationファイル名の日時をenable_extension_for_uuid.rb以前にする}} | ||
+ | |||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | すべてdownにする | ||
+ | |||
+ | schema.rbを消す | ||
+ | |||
+ | % rails db:drop | ||
+ | |||
+ | EnableExtensionForUuid以下のmigrationを移動する | ||
+ | |||
+ | % rails db:create | ||
+ | |||
+ | % psql tep5_development < ../202209261601_tep5.sql | ||
+ | |||
+ | % rails db:migrate | ||
+ | |||
+ | === 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; | ||
+ | count | ||
+ | ------- | ||
+ | 3873 | ||
+ | (1 row)</nowiki> | ||
+ | |||
+ | % 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 | ||
+ | |||
+ | テーブルの変更 | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | 変更したテーブルをリストアし アプリのデータベースにコピーする | ||
+ | % pg_dump --username=jq --table schedules update_heroku > ../new_schedules.sql | ||
+ | % psql --username=jq tep5_development < ../new_schedules.sql | ||
+ | |||
+ | エラー client_idのないものが844ある | ||
+ | <nowiki>update_heroku=# select count(*) from schedules where client_id is null; | ||
+ | count | ||
+ | ------- | ||
+ | 844 | ||
+ | (1 row)</nowiki> | ||
+ | |||
+ | そのnote_idを確認し替え玉をつくる | ||
+ | <nowiki>update_heroku=# select distinct on (note_id) note_id from schedules where client_id is null; | ||
+ | note_id | ||
+ | --------- | ||
+ | 1 | ||
+ | 2 | ||
+ | 4 | ||
+ | (3 rows)</nowiki> | ||
+ | |||
+ | アプリから替え玉を作って確認 | ||
+ | <nowiki>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)</nowiki> | ||
+ | |||
+ | 替え玉のidでschedulesのclient_id null を埋める | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | 新テーブルのclient_idに合わせる | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | 替え玉は元のIDにもどす | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | エラー clientsテーブルのidとschedulesテーブルのclient_idを比較する | ||
+ | <nowiki>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;</nowiki> | ||
+ | |||
+ | 差分を削除する | ||
+ | <nowiki>=# 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</nowiki> | ||
+ | |||
+ | 改めてリストア コピーする | ||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
+ | |||
+ | === 科目テーブル === | ||
+ | 確認 | ||
+ | <nowiki>heroku_dump=# select count(*) from accounts; | ||
+ | count | ||
+ | ------- | ||
+ | 98 | ||
+ | (1 row)</nowiki> | ||
+ | |||
+ | % rails generate scaffold Account account_cd:string content:string bs_pl:integer debit_credit:integer | ||
+ | |||
+ | % rails db:migrate | ||
+ | |||
+ | リストアして作業データベースにコピーする | ||
+ | <nowiki>% pg_dump --username=jq --table accounts heroku_dump > ../new_accounts.sql | ||
+ | % psql --username=jq update_heroku < ../new_accounts.sql</nowiki> | ||
+ | |||
+ | テーブルの変更 | ||
+ | =# ALTER TABLE accounts RENAME COLUMN account TO content; | ||
+ | |||
+ | データ修正 | ||
+ | =# update accounts set debit_credit = 1 where id = 43; | ||
+ | |||
+ | 変更したテーブルをリストアし アプリのデータベースにコピーする | ||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | |||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
+ | |||
+ | === 仕訳帳テーブル === | ||
+ | ==== 12-16 17:18 ==== | ||
+ | 確認 | ||
+ | <nowiki>latest_dump=# select count(*) from journals; | ||
+ | count | ||
+ | ------- | ||
+ | 14885</nowiki> | ||
+ | |||
+ | 最新をテーブルダンプする | ||
+ | % pg_dump --username=jq --table journals latest_dump > ../update_journals.sql | ||
+ | |||
+ | update_dumpデータベースにリストアする | ||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | 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 | ||
+ | |||
+ | <nowiki>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</nowiki> | ||
+ | |||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | ここまでのバックアップを取る | ||
+ | % pg_dump -Fc tep5_development > ../202212170834.dump | ||
+ | |||
+ | ==== 以前 ==== | ||
+ | 確認 | ||
+ | <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 | ||
+ | |||
+ | % rails db:migrate | ||
+ | |||
+ | リストアして作業データベースにコピーする | ||
+ | <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql | ||
+ | % psql --username=jq update_heroku < ../new_journals.sql</nowiki> | ||
+ | |||
+ | テーブルの変更 | ||
+ | =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id; | ||
+ | |||
+ | 新テーブルのclient_idに合わせる | ||
+ | <nowiki>update_heroku=# 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 | ||
+ | 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</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % 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; | ||
+ | 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 | ||
+ | ------- | ||
+ | 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追加) | ||
+ | |||
+ | % rails db:migrate | ||
+ | |||
+ | リストアして作業データベースにコピーする | ||
+ | % pg_dump --username=jq --table descriptions heroku_dump > ../new_descriptions.sql | ||
+ | % psql --username=jq update_heroku < ../new_descriptions.sql | ||
+ | |||
+ | テーブルの変更 | ||
+ | =# ALTER TABLE descriptions RENAME COLUMN jiin_id TO note_id; | ||
+ | |||
+ | リストア コピー | ||
+ | % pg_dump --username=jq --table descriptions update_heroku > ../new_descriptions.sql | ||
+ | <nowiki>% psql --username=jq tep5_development < ../new_descriptions.sql | ||
+ | SET | ||
+ | SET | ||
+ | SET | ||
+ | SET | ||
+ | SET | ||
+ | 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</nowiki> | ||
+ | |||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202209270816_tep5.sql | ||
+ | |||
+ | === 戒名テーブル 変更あり === | ||
+ | ==== 12-17 11:14 ==== | ||
+ | 確認 | ||
+ | <nowiki>latest_dump=# select count(*) from kaimyous; | ||
+ | count | ||
+ | ------- | ||
+ | 3445</nowiki> | ||
+ | |||
+ | 増分を書き出す | ||
+ | % psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, client_id, content, date, g_age, relationship, ie_id_v, created_at, updated_at from kaimyous where created_at >= '2022-09-13' order by jiin_id" -A -F, > ../kaimyous_add.csv | ||
+ | |||
+ | ie_id(client_id)は修正する ie_id_vはmemoに対応するために書き出した | ||
+ | |||
+ | コピーで追加 | ||
+ | <nowiki>=# \COPY kaimyous FROM '../kaimyous_add.csv' DELIMITER ',' CSV HEADER; | ||
+ | COPY 70</nowiki> | ||
+ | |||
+ | ==== 以前 ==== | ||
+ | 確認 | ||
+ | <nowiki>heroku_dump=# select count(*) from kaimyous; | ||
+ | count | ||
+ | ------- | ||
+ | 3375 | ||
+ | (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 | ||
+ | |||
+ | (UUID削除 index追加) | ||
+ | |||
+ | % rails db:migrate | ||
+ | |||
+ | リストアして作業データベースにコピーする | ||
+ | % pg_dump --username=jq --table kaimyous heroku_dump > ../new_kaimyous.sql | ||
+ | % psql --username=jq update_heroku < ../new_kaimyous.sql | ||
+ | |||
+ | テーブルの変更 | ||
+ | <nowiki>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</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> | ||
+ | |||
+ | |||
+ | エラー clientsテーブルのidとkaimyousテーブルのclient_idを比較する | ||
+ | <nowiki>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)</nowiki> | ||
+ | |||
+ | 書き出す | ||
+ | <nowiki>% 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</nowiki> | ||
+ | |||
+ | ,挿入 改行削除 | ||
+ | :%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>update_heroku=# delete from kaimyous where client_id in (21, 27, 35, 38, 39, 44, 46, 56, 57, 63, 71, 73, 78, 89, 101, 106, 111, 126, 127, 135, 144, 147, 156, 173, 174, 176, 186, 188, 189, 230, 310, 373, 404, 473, 701, 804, 872, 936, 953, 972, 998, 1590, 1671, 1777, 2249, 2290, 22653, 34681); | ||
+ | DELETE 81</nowiki> | ||
+ | |||
+ | ダンプ リストア | ||
+ | <nowiki>jq@MacBook-Pro tep5 % 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</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202210031034_tep5.sql | ||
+ | |||
+ | === 通帳テーブル === | ||
+ | 確認 | ||
+ | <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 | ||
+ | |||
+ | リストアして作業データベースにコピーする | ||
+ | % 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 | ||
+ | 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</nowiki> | ||
+ | |||
+ | ダンプ ここまで形を整えたデータベースをバックアップしとく | ||
+ | % pg_dump tep5_development > ../202211021454_tep5.sql | ||
+ | |||
+ | === relationshipsテーブル === | ||
+ | 確認 | ||
+ | <nowiki>latest_dump=# select count(*) from relationships; | ||
+ | count | ||
+ | ------- | ||
+ | 40</nowiki> | ||
+ | |||
+ | 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をあわせる | ||
+ | <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> | ||
+ | |||
+ | セレクトしてアップデートする | ||
+ | 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を有効にする == | == UUIDを有効にする == | ||
+ | === 設定 === | ||
% rails generate migration enable_extension_for_uuid | % rails generate migration enable_extension_for_uuid | ||
307行目: | 1,475行目: | ||
g.orm :active_record, primary_key_type: :uuid | g.orm :active_record, primary_key_type: :uuid | ||
end</nowiki> | end</nowiki> | ||
+ | |||
+ | === 途中からUUIDに変更する場合 === | ||
+ | migrationファイル追加 | ||
+ | <nowiki> % rails generate migration enable_extension_for_uuid | ||
+ | |||
+ | % rails generate migration change_user_id_type_to_uuid | ||
+ | |||
+ | % rails destroy migration change_user_id_type_to_uuid | ||
+ | |||
+ | % rails generate migration change_users_id_type_to_uuid | ||
+ | |||
+ | % rails generate migration change_notes_id_type_to_uuid | ||
+ | |||
+ | % rails generate migration change_lists_id_type_to_uuid | ||
+ | |||
+ | % rails generate migration change_clients_id_type_to_uuid | ||
+ | |||
+ | % rails generate migration change_others_id_type_to_uuid</nowiki> | ||
+ | |||
+ | === 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> | ||
+ | |||
+ | % pg_dump tep5_development > tep5_uuid.sql | ||
== generate == | == generate == | ||
321行目: | 1,518行目: | ||
% 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 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 | ||
やり直し | やり直し | ||
326行目: | 1,531行目: | ||
$ rails generate scaffold Post content:text user:references picture:string | $ 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 generate model Relationship follower_id:integer followed_id:integer | ||
340行目: | 1,547行目: | ||
config/envitonments/production.rb | config/envitonments/production.rb | ||
config.assets.compile = true | config.assets.compile = true | ||
+ | |||
+ | === カラム追加 === | ||
+ | <nowiki>% rails generate migration add_memo_to_schedules memo:text | ||
+ | invoke active_record | ||
+ | create db/migrate/20230311013341_add_memo_to_schedules.rb | ||
+ | % rails db:migrate | ||
+ | == 20230311013341 AddMemoToSchedules: migrating =============================== | ||
+ | -- add_column(:schedules, :memo, :text) | ||
+ | -> 0.0056s | ||
+ | == 20230311013341 AddMemoToSchedules: migrated (0.0057s) ======================</nowiki> | ||
+ | |||
+ | === 外部キー制約に変更 === | ||
+ | rails generate migration AddScheduleReferenceToJournal schedule:references | ||
+ | |||
+ | == サーバーへ転送 == | ||
+ | 除外するファイルをまとめる | ||
+ | <nowiki>exclude-file.txt | ||
+ | production.rb | ||
+ | database.yml</nowiki> | ||
+ | |||
+ | すべて | ||
+ | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/ | ||
+ | |||
+ | appのみ | ||
+ | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/app/ jq:/home/jq/tep5/app/ | ||
+ | |||
+ | データベース | ||
+ | % rsync ~/rails_app_tepnote/202212170834.dump jq:/home/jq/dump_data | ||
+ | |||
+ | == サーバー側 == | ||
+ | リストア | ||
+ | $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_ production ../dump_data/202212141027.dump | ||
+ | |||
+ | $ bundle install --without test development | ||
+ | |||
+ | {{font color||yellow|database.yml username:などを確認する (2箇所)}} | ||
+ | |||
+ | /config/environments/production.rb | ||
+ | |||
+ | データベース削除 | ||
+ | ~$ dropdb tep5_production | ||
+ | または | ||
+ | postgres=# DROP DATABASE tep5_production; | ||
+ | |||
+ | データベース作成 | ||
+ | $ createdb tep5_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 |
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