docker exec -it tepnote-db-1 psql -U postgres
postgres=# \c tep5_production
tep5_production=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------------------------+-------+----------
public | accounts | table | postgres
public | active_storage_attachments | table | postgres
public | active_storage_blobs | table | postgres
public | active_storage_variant_records | table | postgres
public | ar_internal_metadata | table | postgres
public | bankbooks | table | postgres
public | categories | table | postgres
public | clients | table | postgres
public | descriptions | table | postgres
public | fam_client_district_class | table | postgres
public | fam_others_client_district_class | table | postgres
public | fam_others_client_district_class_kai_addr | table | postgres
public | fam_others_client_district_class_kai_addr2 | table | postgres
public | familymembers | table | postgres
public | familymembers3 | table | postgres
public | holidays | table | postgres
public | journals | table | postgres
public | kaimyous | table | postgres
public | lists | table | postgres
public | notes | table | postgres
public | others | table | postgres
public | places | table | postgres
public | places_cp | table | postgres
public | places_flag_false | table | postgres
public | places_flag_true | table | postgres
public | places_multi_flag_false_no_multi | table | postgres
public | posts | table | postgres
public | relationship_categories | table | postgres
public | relationships | table | postgres
public | schedules | table | postgres
public | schema_migrations | table | postgres
public | temp4_even | table | postgres
public | temp4_odd | table | postgres
public | users | table | postgres
(34 rows)
docker exec -it tepnote-db-1 psql -U postgres -d tep5_production -c "
COPY (
SELECT
c.note_id as temple_id,
c.content as repo_name,
c.client_line as repo_order,
c.memo as m_memo,
c.omairi_memo,
o.name as f_name,
o.kana,
o.client_name as isHead,
o.other_line as f_order,
o.memo as f_memo,
o.generation,
k.content,
k.birth,
k.death,
k.g_age,
k.relationship,
k.memo as k_memo,
l.content as d_name,
l.list_line as d_order,
cat.content as t_name
FROM
clients c
LEFT JOIN others o ON c.id = o.client_id
LEFT JOIN kaimyous k ON o.id = k.other_id
LEFT JOIN lists l ON c.list_id = l.id
LEFT JOIN relationship_categories rc ON c.id = rc.client_id
LEFT JOIN categories cat ON rc.category_id = cat.id
) TO STDOUT WITH CSV HEADER DELIMITER ',' ENCODING 'UTF8'
" > ~/tepnote_db_data/full_combined_data.csv
scp onk-server:~/tepnote_db_data/full_combined_data.csv scripts/Tepnote_data/full_combined_data_4.csv
ターミナルで直接実行する CSV処理（エラー時は0を代入）
csvcut -c 2,1,3,18 scripts/Tepnote_data/full_combined_data_5.csv | \
tail -n +2 | \
awk -F, '!seen[$1]++ {print $1 "," $2 "," $3 "," $4}' | \
jq -R -s '
def safe_tonumber:
if . == null or . == "" then 0
elif test("^[0-9]+$") then tonumber
else 0 end;
split("\n") |
map(select(length > 0)) |
map(split(",")) |
map({
(.[0]): {
temple_id: (.[1] | safe_tonumber),
repo_order: (.[2] | safe_tonumber),
d_name: (.[3] | if . == "" then null else . end)
}
}) |
add
' > repo_mapping.json
エラー箇所をスキップ
csvcut -c 5,3,6 scripts/Tepnote_data/clients_others_kaimyous.csv | \
tail -n +2 | \
awk -F',' '!seen[$1]++ {print $1 "," $2 "," $3}' | \
while IFS=, read -r content note_id client_line; do
if [[ $note_id =~ ^[0-9]+$ ]]; then
echo "{\"$content\": {\"note_id\": $note_id, \"client_line\": \"$client_line\"}}"
fi
done | \
jq -s 'add' > name_note_order_mapping.json
csvcut -c 5,3 Tepnote_data/clients_others_kaimyous.csv |
tail -n +2 |
awk -F',' '!seen[$1]++ {print $1 "," $2}' |
while IFS=, read -r content note_id; do
if [[ $note_id =~ ^[0-9]+$ ]]; then
echo "{\"$content\": $note_id}"
fi
done |
jq -s 'add' > name_note_mapping.json
エラーが発生する行番号を確認
csvcut -c 3 Tepnote_data/clients_others_kaimyous.csv | tail -n +2 | awk '{print NR ":" $0}' | grep -v '^[0-9]\+$'
DisplayGroupを作成
docker exec -it pokpok-node-app node scripts/inputDisplayGroup.js
リポジトリの作成 メンバーがぶら下がる
docker exec -it pokpok-node-app node scripts/createRepositoriesFromIds.js
メンバーのインポート
docker exec -it pokpok-node-app node scripts/import_members.js
jq@MacBook-Pro pokpok-app % node scripts/generate_dname_json.js
デプロイしたらサーバー側でもスクリプトを実行する
db.members.countDocuments()
docker exec tepnote-db-1 psql -U postgres -d tep5_production -c "
COPY (
SELECT
c.id AS client_id,
c.user_id,
c.note_id,
c.list_id,
c.content AS client_name,
c.client_line,
c.memo AS client_memo,
c.omairi_memo,
o.id AS other_id,
o.name AS other_name,
o.kana,
o.other_line,
o.memo AS other_memo,
o.generation
FROM
clients c
LEFT JOIN
others o ON c.id = o.client_id
) TO '/tmp/clients_join_others.csv' WITH CSV HEADER DELIMITER ',' ENCODING 'UTF8';
"
docker cp tepnote-db-1:/tmp/clients_join_others.csv ~/tepnote_db_data/
以下は、clients、others、kaimyous、listsの4つのテーブルを結合し、CSV形式で出力する完全なPostgreSQLクエリです。
docker exec tepnote-db-1 psql -U postgres -d tep5_production -c "
COPY (
SELECT
c.id AS client_id,
c.user_id,
c.note_id,
c.list_id,
c.content AS client_name,
c.client_line,
c.memo AS client_memo,
c.omairi_memo,
o.id AS other_id,
o.name AS other_name,
o.kana,
o.other_line,
o.memo AS other_memo,
o.generation,
k.id AS kaimyo_id,
k.content AS kaimyo,
k.birth,
k.death,
k.g_age,
k.relationship,
k.memo AS kaimyo_memo,
l.id AS list_id,
l.content AS list_name,
l.list_line,
l.pinned
FROM
clients c
LEFT JOIN
others o ON c.id = o.client_id
LEFT JOIN
kaimyous k ON o.id = k.other_id
LEFT JOIN
lists l ON c.list_id = l.id
) TO '/tmp/full_combined_data.csv' WITH CSV HEADER DELIMITER ',' ENCODING 'UTF8';
"
# ホストにコピー
docker cp tepnote-db-1:/tmp/full_combined_data.csv ~/tepnote_db_data/
docker exec tepnote-db-1 psql -U postgres -d tep5_production -c "
COPY (
SELECT
c.id, c.user_id, c.note_id, c.list_id, c.content, c.client_line, c.memo, c.omairi_memo,
o.id, o.name, o.kana, o.other_line, o.memo, o.generation,
k.id, k.content, k.birth, k.death, k.g_age, k.relationship, k.memo,
l.id, l.content, l.list_line, l.pinned
FROM
clients c
LEFT JOIN others o ON c.id = o.client_id
LEFT JOIN kaimyous k ON o.id = k.other_id
LEFT JOIN lists l ON c.list_id = l.id
) TO STDOUT WITH CSV HEADER DELIMITER ',' ENCODING 'UTF8'
" > ~/tepnote_db_data/full_combined_data.csv
client_id,user_id,note_id,list_id,client_name,client_line,client_memo,omairi_memo,other_id,other_name,kana,other_line,other_memo,generation,kaimyo_id,kaimyo,birth,death,g_age,relationship,kaimyo_memo,list_id,list_name,list_line,pinned
各テーブルの結合条件:
clients ↔ others: c.id = o.client_idothers ↔ kaimyous: o.id = k.other_idclients ↔ lists: c.list_id = l.id日本語を含むフィールドは自動的にクォートされます
大量のデータを出力する場合、以下のオプションを追加すると便利です:
# 圧縮転送 (rsyncの場合)
rsync -avz -e "ssh -p 22" user@host:/remote/path.csv.gz ~/local/
# 分割出力 (1万行ごと)
split -l 10000 full_combined_data.csv split_file_
権限エラーが発生する場合:
# 一時ディレクトリの権限変更
docker exec tepnote-db-1 chmod 777 /tmp
# またはpostgresユーザーで実行
docker exec -u postgres tepnote-db-1 ...
docker exec -it tepnote-db-1 psql -U postgres -d tep5_production -c "
COPY (
SELECT
c.id, c.user_id, c.note_id, c.list_id, c.content, c.client_line, c.memo, c.omairi_memo,
o.id, o.name, o.kana, o.other_line, o.memo, o.generation,
k.id, k.content, k.birth, k.death, k.g_age, k.relationship, k.memo,
l.id, l.content, l.list_line, l.pinned,
cat.id, cat.content, cat.category_line, cat.kind
FROM
clients c
LEFT JOIN others o ON c.id = o.client_id
LEFT JOIN kaimyous k ON o.id = k.other_id
LEFT JOIN lists l ON c.list_id = l.id
LEFT JOIN relationship_categories rc ON c.id = rc.client_id
LEFT JOIN categories cat ON rc.category_id = cat.id
) TO STDOUT WITH CSV HEADER DELIMITER ',' ENCODING 'UTF8'
" > ~/tepnote_db_data/full_combined_data.csv
必要に応じて、出力する列を調整したり、WHERE条件を追加してデータをフィルタリングすることもできます。
数値としてソートしたい場合
csvcut -c 1 pokpok-app/Tepnote_data/clients_others_kaimyous_lists.csv |
tail -n +2 |
sort -n |
uniq |
jq -R . |
jq -s '{unique_ids: map(tonumber)}' > unique_ids_sorted.json
JSONファイル内の要素数をカウントする
jq '.unique_ids | length' unique_ids_sorted.json
bash
csvcut -c 1,3 pokpok-app/Tepnote_data/clients_others_kaimyous_lists.csv |
tail -n +2 |
awk -F',' '!seen[$1]++ {print $1 ": " $2}' |
jq -R 'split(": ") | {(.[0]): .[1] | tonumber}' |
jq -s 'add' > id_note_object.json
出力例 (id_note_object.json):
json
{
"600": 1,
"1030": 1,
"512": 1,
"370": 1,
"32643": 3,
"374": 1,
"2674": 1
}
csvcut -c 5,3 Tepnote_data/clients_others_kaimyous_lists_category.csv |
tail -n +2 |
awk -F',' '!seen[$1]++' |
jq -R 'split(",") | {content: .[0], note_id: .[1] | tonumber}' |
jq -s 'map({(.content): .note_id}) | add' > name_note_mapping.json
エラー箇所をスキップ
csvcut -c 5,3 Tepnote_data/clients_others_kaimyous_lists_category.csv |
tail -n +2 |
awk -F',' '!seen[$1]++ {print $1 "," $2}' |
while IFS=, read -r content note_id; do
if [[ $note_id =~ ^[0-9]+$ ]]; then
echo "{\"$content\": $note_id}"
fi
done |
jq -s 'add' > name_note_mapping.json
エラーが発生する行番号を確認
csvcut -c 3 Tepnote_data/clients_others_kaimyous_lists_category.csv | tail -n +2 | awk '{print NR ":" $0}' | grep -v '^[0-9]\+$'