最新版 |
編集中の文章 |
27行目: |
27行目: |
| ==== Users ==== | | ==== Users ==== |
| * はじめにtepnoteのユーザーをエクスポートし tep3にインポートする | | * はじめにtepnoteのユーザーをエクスポートし tep3にインポートする |
− | ==== Notes ==== | + | ==== Jiins → Notes ==== |
| * tepnoteのjiinsをエクスポートし tep3のnotesにインポートする | | * tepnoteのjiinsをエクスポートし tep3のnotesにインポートする |
| <pre> | | <pre> |
37行目: |
37行目: |
| \COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ','; | | \COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ','; |
| </pre> | | </pre> |
− | :- このときJiinsのidとNotesのidを合わせる Notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID) | + | :- このときjiinsのidとnotesのidを合わせる notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID) |
− | <pre>
| |
− | update notes set user_id = 1;
| |
− | </pre>
| |
| <pre> | | <pre> |
| tep3_development=# select * from notes; | | tep3_development=# select * from notes; |
61行目: |
58行目: |
| (14 rows) | | (14 rows) |
| </pre> | | </pre> |
− | | + | ==== ClientsをClientsとOthersに分ける ==== |
− | ==== Clients ==== | + | * 新clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す |
− | * 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする | + | * 今回はスキーマを変更したので寺ごとに出し入れする |
− | :- 新Clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す
| |
| <pre> | | <pre> |
| エクスポート | | エクスポート |
− | \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 1 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | + | \COPY (select ie_id, user_id, jiin_id, created_at, updated_at from clients where jiin_id = 1 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; |
− | | |
− | \COPY (select ie_id, user_id, jiin_id, table_order, created_at, updated_at from clients where jiin_id = 2 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− | | |
− | ...
| |
− | </pre>
| |
− | <pre>
| |
− | インポート
| |
− | \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',';
| |
| | | |
− | \COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ','; | + | \COPY (select ie_id, user_id, jiin_id, created_at, updated_at from clients where jiin_id = 2 and nusi = true order by ie_id asc, jiin_id asc) TO '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; |
| | | |
| ... | | ... |
| </pre> | | </pre> |
− | :- シーケンス編集
| |
| <pre> | | <pre> |
− | シーケンスの最大値確認
| |
− | select max(id) from clients;
| |
− |
| |
− | シーケンスを最大値以上に設定
| |
− | select setval ('clients_id_seq', 5128);
| |
− | </pre>
| |
− | :- list_idにnullがあるとき
| |
− | <pre>
| |
− | リスト「未分類」を作りそのidを入れる
| |
− | update clients set list_id = 102 where list_id is null;
| |
− | </pre>
| |
− |
| |
− | ==== Others ====
| |
− | * 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
| |
− | <pre>
| |
− | エクスポート
| |
− | \COPY (select user_id, jiin_id, ie_id, name, yomi, nusi, each_order, generation, created_at, updated_at from clients where jiin_id = 1 order by ie_id asc) TO '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− |
| |
− | ...
| |
− | </pre>
| |
− | <pre>
| |
− | インポート
| |
− | \COPY others(user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',';
| |
− |
| |
− | ...
| |
| </pre> | | </pre> |
− |
| |
− | ==== おまけ ====
| |
− | * エラー
| |
− | :- Othersのclient_idがClientsのidにないとき
| |
| <pre> | | <pre> |
− | ERROR: insert or update on table "others" violates foreign key constraint "fk_rails_10377da6e2"
| |
− | DETAIL: Key (client_id)=(26) is not present in table "clients".
| |
| </pre> | | </pre> |
− | :- others_1.csvから不要なclient_idを含む行の削除方法
| |
| <pre> | | <pre> |
− | 作業用データベースを作る
| |
− | % psql postgres
| |
− | postgres=# create database x_0226;
| |
− |
| |
− | 作業用テーブルを作る
| |
− | % psql x_0226
| |
− | x_0226=# CREATE TABLE clients(id serial, client_id int);
| |
− | x_0226=# CREATE TABLE others(id serial, client_id int);
| |
− |
| |
− | エクスポートしたcsvから不要な列を削除してそれぞれにインポートする
| |
− | x_0226=# \COPY clients(client_id) FROM '/Users/jq/3tep_gomi/clients_1.csv' WITH CSV DELIMITER ',';
| |
− | x_0226=# \COPY others(client_id) FROM '/Users/jq/3tep_gomi/others_1_c.csv' WITH CSV DELIMITER ',';
| |
− |
| |
− | EXCEPTを使ってClientsにないOthersのclient_idを拾う
| |
− | x_0226=# SELECT client_id FROM others EXCEPT SELECT client_id FROM clients order by client_id asc;
| |
− |
| |
− | 作業用テーブルを作る
| |
− | x_0226=# create table x_others(id serial, user_id int, note_id int, client_id int, name varchar, yomi varchar, nusi boolean, line int, generation varchar, created_at timestamp, updated_at timestamp);
| |
− |
| |
− | others_1.csvをインポートする
| |
− | x_0226=# \COPY x_others(user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',';
| |
− |
| |
− | ClientsにないOthersのclient_idを含む行を削除する
| |
− | x_0226=# delete from x_others where client_id in (SELECT client_id FROM others EXCEPT SELECT client_id FROM clients order by client_id asc);
| |
− |
| |
− | エクスポート
| |
− | x_0226=# \COPY (select user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at from x_others order by client_id asc) TO '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− |
| |
− | tep3のOthersにインポートする
| |
− | tep3_development=# \COPY others(user_id, note_id, client_id, name, yomi, nusi, line, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others_1.csv' WITH CSV DELIMITER ',';
| |
| </pre> | | </pre> |
− |
| |
− | ==== Lists ====
| |
− | * 旧Categoriesのset_id1を寺ごとに抜き出し Listsに入れる
| |
| <pre> | | <pre> |
− | エクスポート
| |
− | \COPY (select id, user_id, jiin_id, content, line, created_at, updated_at from categories where jiin_id = 1 and content <> '' and set_id = 1 order by line asc) TO '/Users/jq/3tep_gomi/list_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− |
| |
− | ...
| |
| </pre> | | </pre> |
| <pre> | | <pre> |
− | インポート
| |
− | \COPY lists(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/list_1.csv' WITH CSV DELIMITER ',';
| |
− |
| |
− | ...
| |
| </pre> | | </pre> |
− | :- シーケンス編集
| |
− | <pre>
| |
− | シーケンスの最大値確認
| |
− | select max(id) from lists;
| |
| | | |
− | シーケンスを最大値以上に設定
| + | === インポート === |
− | select setval ('lists_id_seq', 128);
| + | \COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ','; |
− | </pre>
| |
| | | |
− | ==== ListsのidをClientsのlist_idに入れる ====
| + | \COPY others(user_id, note_id, client_id, name, yomi, nusi, order1, order2, generation, created_at, updated_at) FROM '/Users/jq/3tep_gomi/others.csv' WITH CSV DELIMITER ','; |
− | <pre>
| |
− | tepnote::DATABASE=> select * from categories where id = 70;
| |
− | id | user_id | jiin_id | content | set_id | created_at | updated_at | line
| |
− | ----+---------+---------+---------+--------+----------------------------+----------------------------+------
| |
− | 70 | 1 | 1 | 別所 | 1 | 2021-06-16 02:13:29.941705 | 2021-06-30 23:36:32.003691 | 2
| |
− | </pre>
| |
− | <pre>
| |
− | tepnote::DATABASE=> select * from properties where jiin_id = 1 and ie_id = 2597;
| |
− | id | user_id | jiin_id | client_id | ie_id | tiku_id | category_id | tana | tuki | nenga | yaku | eitai | name | created_at | updated_at | category_3 | category_4 | category_5 | nenkai_h | goji | haru | aki | yaku_g
| |
− | ----+---------+---------+-----------+-------+---------+-------------+------+------+-------+------+-------+------+----------------------------+----------------------------+------------+------------+------------+----------+------+------+-----+--------
| |
− | 64 | 2 | 1 | 1060 | 2597 | 70 | 101 | t | f | t | t | f | f | 2019-03-07 16:16:19.197973 | 2021-08-25 23:47:50.265849 | | | | t | t | t | t | f
| |
− | </pre>
| |
− | * 旧CategoriesとPropertiesをjoinしてie_id(新Clientsのidは旧ie_id)とlist_idを抜き出す
| |
− | <pre>
| |
− | エクスポート
| |
− | \COPY (select properties.ie_id as id, categories.id as list_id from categories join properties on categories.id = properties.tiku_id where categories.jiin_id = 1 order by properties.ie_id asc) TO '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− | </pre>
| |
− | * tep3_developmentデータベース内に作業用一時テーブルを作る(後で削除) Clientsのlist_idに上で抜き出したlist_idを入れるため
| |
− | <pre>
| |
− | CREATE TABLE x_list_id (client_id int, list_id int);
| |
− | </pre>
| |
− | <pre>
| |
− | インポート
| |
− | \COPY x_list_id(client_id, list_id) FROM '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',';
| |
− | </pre>
| |
− | * Clientsをアップデートしてlist_idを入れる
| |
− | <pre>
| |
− | アップデート
| |
− | UPDATE clients SET list_id = x_list_id.list_id FROM x_list_id WHERE clients.id = x_list_id.client_id;
| |
− | </pre>
| |
− | | |
− | ==== Categories ====
| |
− | * 旧Categories set_id 2 が主なカテゴリ
| |
− | <pre>
| |
− | * エクスポート
| |
− | \COPY (select id, user_id, jiin_id, content, line, created_at, updated_at from categories where jiin_id = 1 and set_id = 2 and content <> '' order by line asc) TO '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER; | |
− | ...
| |
− | </pre>
| |
− | <pre>
| |
− | * インポート
| |
− | \COPY categories(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/category_1_1.csv' WITH CSV DELIMITER ',';
| |
− | </pre>
| |
− | :- シーケンス編集
| |
− | <pre>
| |
− | シーケンスの最大値確認
| |
− | select max(id) from categories;
| |
− | | |
− | シーケンスを最大値以上に設定
| |
− | select setval ('categories_id_seq', 128);
| |
− | </pre>
| |
− | <pre>
| |
− | id | user_id | note_id | content | line | defa | created_at | updated_at
| |
− | ----+---------+---------+---------+------+------+---------------------+---------------------
| |
− | 1 | 1 | 1 | 檀家 | 1 | t | 2022-02-24 07:00:00 | 2022-02-24 07:00:00
| |
− | </pre>
| |
− | | |
− | ==== Client_categories ====
| |
− | *
| |
− | <pre>
| |
− | エクスポート
| |
− | \COPY (select properties.user_id, properties.jiin_id, properties.ie_id, properties.category_id, properties.created_at, properties.updated_at from properties join categories on properties.category_id = categories.id where properties.jiin_id = 1 and categories.set_id = 2 order by ie_id asc, category_id asc) TO '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
| |
− | | |
− | ...
| |
− | </pre>
| |
− | <pre>
| |
− | インポート
| |
− | \COPY client_categories(user_id, note_id, client_id, category_id, created_at, updated_at) FROM '/Users/jq/3tep_gomi/client_category_1_1.csv' WITH CSV DELIMITER ',';
| |
− | | |
− | ...
| |
− | </pre>
| |
− | <pre>
| |
− | </pre>
| |
− | <pre>
| |
− | </pre>
| |
− | <pre>
| |
− | </pre>
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | <pre>
| |
− | </pre>
| |
− | <pre>
| |
− | </pre>
| |