「Tep3」の版間の差分

(ClientsをClientsとOthersに分ける)
(Categories → Lists)
 
(同じ利用者による、間の46版が非表示)
27行目: 27行目:
 
==== Users ====
 
==== Users ====
 
* はじめにtepnoteのユーザーをエクスポートし tep3にインポートする
 
* はじめにtepnoteのユーザーをエクスポートし tep3にインポートする
==== Jiins → Notes ====
+
==== 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;
58行目: 61行目:
 
(14 rows)
 
(14 rows)
 
</pre>
 
</pre>
==== ClientsをClientsとOthersに分ける ====
+
 
* 新clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す
+
==== Clients ====
* 今回はスキーマを変更したので寺ごとに出し入れする
+
* 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
 +
:- 新Clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す
 
<pre>
 
<pre>
 
エクスポート
 
エクスポート
\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 = 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 (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;
+
\COPY clients(id, user_id, note_id, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/clients_2.csv' WITH CSV DELIMITER ',';
  
 
...
 
...
 
</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;
  
=== インポート ===
+
シーケンスを最大値以上に設定
\COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ',';
+
select setval ('lists_id_seq', 128);
 +
</pre>
  
  \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 ',';
+
==== ListsのidをClientsのlist_idに入れる ====
 +
<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>

2022年3月1日 (火) 07:17時点における最新版

% ruby -v
% rbenv versions
% rbenv local 2.7.5
% rails -v
% rails new tep3 -d postgresql
database.yml username
rails db:create
$ yarn add bootstrap@next
$ yarn add @popperjs/core
app/javascript/packs/application.js
import "bootstrap";
import "../stylesheets/application";
% mkdir app/javascript/stylesheets/
% touch app/javascript/stylesheets/application.scss
@import "bootstrap";
app/views/layouts/application.html.erb
<%= stylesheet_pack_tag 'application', media: 'all', 'data-turbolinks-track': 'reload' %>

目次

データベース移行編集

エクスポート インポート編集

Users編集

  • はじめにtepnoteのユーザーをエクスポートし tep3にインポートする

Notes編集

  • tepnoteのjiinsをエクスポートし tep3のnotesにインポートする
エクスポート
\COPY (select user_id, jiin_name, kind, meeting_time, role_num, created_at, updated_at from jiins order by user_id asc) TO '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '' HEADER;
インポート
\COPY notes(user_id, name, kind, meeting_time, role, created_at, updated_at) FROM '/Users/jq/3tep_gomi/notes.csv' WITH CSV DELIMITER ',';
- このときJiinsのidとNotesのidを合わせる Notesのuser_idは1にする(1は淨久寺グループが代々引き継ぐアカウントのユーザーID)
update notes set user_id = 1;
tep3_development=# select * from notes;
 id | user_id |        name        | kind | meeting_time | role | belong |         created_at         |         updated_at         
----+---------+--------------------+------+--------------+------+--------+----------------------------+----------------------------
  1 |       1 | 淨久寺             |      |              |    1 |        | 2019-01-30 01:12:29.847655 | 2020-02-28 18:17:08.925978
  2 |       1 | 光源寺             |      |              |    1 |        | 2019-02-01 13:04:29.292541 | 2020-02-28 18:17:45.068205
  3 |       1 | 廣國院             |      |              |    1 |        | 2019-02-01 13:04:37.908007 | 2020-08-25 14:25:08.311179
  4 |       1 | 安養寺             |      |              |    1 |        | 2019-02-01 13:04:50.543585 | 2020-02-28 18:18:04.200338
  5 |       1 | 太陽               |      |              |    1 |        | 2019-05-11 12:28:30.551584 | 2020-02-28 18:18:15.454727
  6 |       1 | 家族               |      |              |    1 |        | 2019-06-20 11:28:05.371417 | 2020-02-28 18:18:28.641819
  7 |       1 | SANデザイン事務所  |      |              |    1 |        | 2019-08-11 12:43:11.472652 | 2020-12-15 15:39:00.831541
  8 |       1 | 一般               |      |              |    1 |        | 2019-12-24 11:27:21.384603 | 2019-12-24 11:27:21.384603
  9 |       1 | 親戚               |      |              |    1 |        | 2019-12-24 13:11:43.345268 | 2019-12-24 13:11:43.345268
 10 |       1 | 光源寺護持会       |      |              |    3 |        | 2020-02-15 17:39:30.312985 | 2020-03-06 11:35:27.445689
 11 |       1 | 淨久寺護持会       |      |              |    3 |        | 2020-02-28 18:16:01.176519 | 2020-02-28 18:16:01.176519
 12 |       1 | 廣國院護持会       |      |              |    3 |        | 2020-03-02 13:18:23.289695 | 2020-03-02 13:18:23.289695
 13 |       1 | 安養寺護持会       |      |              |    3 |        | 2020-03-02 13:22:51.367168 | 2020-03-02 13:22:51.367168
 14 |       1 | 光源寺晋山結制会計 |      |              |    3 |        | 2020-03-25 11:33:09.239345 | 2020-03-25 11:33:09.239345
(14 rows)

Clients編集

  • 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
- 新Clients(家テーブル) 旧Clientsから主を抜き出す ie_idを新Clientsのidにする そのidにOthersがぶらさがる 旧Clientsのmemoは別テーブルに移す
エクスポート
\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, 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;

...
インポート
\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 ',';

...
- シーケンス編集
シーケンスの最大値確認
select max(id) from clients;

シーケンスを最大値以上に設定
select setval ('clients_id_seq', 5128);
- list_idにnullがあるとき
リスト「未分類」を作りそのidを入れる
update clients set list_id = 102 where list_id is null;

Others編集

  • 旧ClientsをClientsとOthersに分ける 寺ごと(ノートごと)に出し入れする
エクスポート
\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;

...
インポート
\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 ',';

...

おまけ編集

  • エラー
- Othersのclient_idがClientsのidにないとき
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".
- others_1.csvから不要なclient_idを含む行の削除方法
作業用データベースを作る
% 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 ',';

Lists編集

  • 旧Categoriesのset_id1を寺ごとに抜き出し Listsに入れる
エクスポート
\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;

...
インポート
\COPY lists(id, user_id, note_id, content, line, created_at, updated_at) FROM '/Users/jq/3tep_gomi/list_1.csv' WITH CSV DELIMITER ',';

...
- シーケンス編集
シーケンスの最大値確認
select max(id) from lists;

シーケンスを最大値以上に設定
select setval ('lists_id_seq', 128);

ListsのidをClientsのlist_idに入れる編集

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
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
  • 旧CategoriesとPropertiesをjoinしてie_id(新Clientsのidは旧ie_id)とlist_idを抜き出す
エクスポート
\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;
  • tep3_developmentデータベース内に作業用一時テーブルを作る(後で削除) Clientsのlist_idに上で抜き出したlist_idを入れるため
CREATE TABLE x_list_id (client_id int, list_id int);
インポート
\COPY x_list_id(client_id, list_id) FROM '/Users/jq/3tep_gomi/ie_id_list_id_1.csv' WITH CSV DELIMITER ',';
  • Clientsをアップデートしてlist_idを入れる
アップデート
UPDATE clients SET list_id = x_list_id.list_id FROM x_list_id WHERE clients.id = x_list_id.client_id;

Categories編集

  • 旧Categories set_id 2 が主なカテゴリ
* エクスポート
\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;
...
* インポート
\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 ',';
- シーケンス編集
シーケンスの最大値確認
select max(id) from categories;

シーケンスを最大値以上に設定
select setval ('categories_id_seq', 128);
 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

Client_categories編集

エクスポート
\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;

...
インポート
\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 ',';

...