333 lines
12 KiB
Python
333 lines
12 KiB
Python
#!/usr/bin/env python
|
||
"""
|
||
old_rogdb から rogdb への全イベントデータ移行スクリプト（SQL生成方式）
|
||
FC岐阜の成功事例をベースに全てのイベントのteam/member/entry + GPS情報を移行
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
import django
|
||
from datetime import datetime
|
||
|
||
if __name__ == '__main__':
|
||
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
|
||
django.setup()
|
||
|
||
from django.db import transaction, connection
|
||
from rog.models import NewEvent2, Team, Entry, Member, NewCategory, CustomUser
|
||
|
||
print("📋 全イベントデータ移行スクリプト（SQL生成方式）を開始します")
|
||
|
||
# SQLファイル名
|
||
sql_file = "migrate_all_events_with_gps.sql"
|
||
|
||
try:
|
||
with transaction.atomic():
|
||
# === STEP 1: ユーザー確認 ===
|
||
print("\n=== STEP 1: ユーザー確認 ===")
|
||
|
||
admin_user, created = CustomUser.objects.get_or_create(
|
||
username='admin',
|
||
defaults={
|
||
'email': 'admin@example.com',
|
||
'is_staff': True,
|
||
'is_superuser': True
|
||
}
|
||
)
|
||
print(f"管理ユーザー: {'作成' if created else '既存'}")
|
||
|
||
# === STEP 2: イベントとカテゴリー情報取得 ===
|
||
print("\n=== STEP 2: 既存イベント・カテゴリー確認 ===")
|
||
|
||
existing_events = list(NewEvent2.objects.values_list('id', 'name'))
|
||
print(f"既存イベント数: {len(existing_events)}件")
|
||
|
||
if not existing_events:
|
||
print("❌ イベントが存在しません。先にイベントを作成してください。")
|
||
sys.exit(1)
|
||
|
||
existing_categories = list(NewCategory.objects.values_list('id', 'name'))
|
||
print(f"既存カテゴリー数: {len(existing_categories)}件")
|
||
|
||
if not existing_categories:
|
||
print("❌ カテゴリーが存在しません。先にカテゴリーを作成してください。")
|
||
sys.exit(1)
|
||
|
||
# === STEP 3: SQLファイル生成 ===
|
||
print(f"\n=== STEP 3: SQLファイル生成 ({sql_file}) ===")
|
||
|
||
with open(sql_file, 'w', encoding='utf-8') as f:
|
||
f.write("-- 全イベントデータ移行SQL（GPS情報含む）\n")
|
||
f.write(f"-- 生成日時: {datetime.now()}\n\n")
|
||
|
||
# 1. チーム移行SQL
|
||
f.write("-- ========================================\n")
|
||
f.write("-- 1. チーム移行（old_rogdb → rogdb）\n")
|
||
f.write("-- ========================================\n\n")
|
||
|
||
f.write("""
|
||
-- old_rogdbからチーム情報を移行
|
||
INSERT INTO rog_team (
|
||
id, name, owner_id, event_id, reg_date,
|
||
representative_name, representative_phone,
|
||
representative_email, is_deleted
|
||
)
|
||
SELECT DISTINCT
|
||
t.id,
|
||
t.name,
|
||
COALESCE(t.owner_id, {admin_user_id}) as owner_id,
|
||
t.event_id,
|
||
t.reg_date,
|
||
COALESCE(t.representative_name, t.name) as representative_name,
|
||
COALESCE(t.representative_phone, '') as representative_phone,
|
||
COALESCE(t.representative_email, '') as representative_email,
|
||
false as is_deleted
|
||
FROM dblink('host=postgres-db port=5432 dbname=old_rogdb user=user password=password',
|
||
'SELECT id, name, owner_id, event_id, reg_date, representative_name, representative_phone, representative_email FROM team WHERE is_deleted = false'
|
||
) AS t(
|
||
id INTEGER,
|
||
name TEXT,
|
||
owner_id INTEGER,
|
||
event_id INTEGER,
|
||
reg_date TIMESTAMP,
|
||
representative_name TEXT,
|
||
representative_phone TEXT,
|
||
representative_email TEXT
|
||
)
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM rog_newevent2 ne WHERE ne.id = t.event_id
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM rog_team rt WHERE rt.id = t.id
|
||
)
|
||
ORDER BY t.id;
|
||
|
||
""".format(admin_user_id=admin_user.id))
|
||
|
||
# 2. メンバー移行SQL
|
||
f.write("-- ========================================\n")
|
||
f.write("-- 2. メンバー移行（old_rogdb → rogdb）\n")
|
||
f.write("-- ========================================\n\n")
|
||
|
||
f.write("""
|
||
-- old_rogdbからメンバー情報を移行
|
||
INSERT INTO rog_member (
|
||
id, team_id, name, kana, is_leader,
|
||
phone, email, birthday, gender, si_number, is_deleted
|
||
)
|
||
SELECT DISTINCT
|
||
m.id,
|
||
m.team_id,
|
||
m.name,
|
||
COALESCE(m.kana, '') as kana,
|
||
COALESCE(m.is_leader, false) as is_leader,
|
||
COALESCE(m.phone, '') as phone,
|
||
COALESCE(m.email, '') as email,
|
||
m.birthday,
|
||
COALESCE(m.gender, '') as gender,
|
||
m.si_number,
|
||
false as is_deleted
|
||
FROM dblink('host=postgres-db port=5432 dbname=old_rogdb user=user password=password',
|
||
'SELECT id, team_id, name, kana, is_leader, phone, email, birthday, gender, si_number FROM member WHERE is_deleted = false'
|
||
) AS m(
|
||
id INTEGER,
|
||
team_id INTEGER,
|
||
name TEXT,
|
||
kana TEXT,
|
||
is_leader BOOLEAN,
|
||
phone TEXT,
|
||
email TEXT,
|
||
birthday DATE,
|
||
gender TEXT,
|
||
si_number TEXT
|
||
)
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM rog_team rt WHERE rt.id = m.team_id
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM rog_member rm WHERE rm.id = m.id
|
||
)
|
||
ORDER BY m.id;
|
||
|
||
""")
|
||
|
||
# 3. エントリー移行SQL
|
||
f.write("-- ========================================\n")
|
||
f.write("-- 3. エントリー移行（old_rogdb → rogdb）\n")
|
||
f.write("-- ========================================\n\n")
|
||
|
||
default_cat_id = existing_categories[0][0] if existing_categories else 1
|
||
|
||
f.write(f"""
|
||
-- old_rogdbからエントリー情報を移行（startテーブルと結合）
|
||
INSERT INTO rog_entry (
|
||
date, category_id, event_id, owner_id, team_id,
|
||
is_active, zekken_number, zekken_label, has_goaled,
|
||
has_participated, is_trial, staff_privileges,
|
||
can_access_private_events, team_validation_status
|
||
)
|
||
SELECT DISTINCT
|
||
ne.start_datetime as date,
|
||
{default_cat_id} as category_id,
|
||
t.event_id,
|
||
COALESCE(t.owner_id, {admin_user.id}) as owner_id,
|
||
t.team_id,
|
||
true as is_active,
|
||
COALESCE(s.zekken_number, 0) as zekken_number,
|
||
COALESCE(s.label, CONCAT(ne.name, '-', COALESCE(s.zekken_number, 0))) as zekken_label,
|
||
false as has_goaled,
|
||
false as has_participated,
|
||
false as is_trial,
|
||
false as staff_privileges,
|
||
false as can_access_private_events,
|
||
'approved' as team_validation_status
|
||
FROM dblink('host=postgres-db port=5432 dbname=old_rogdb user=user password=password',
|
||
'SELECT t.id as team_id, t.event_id, t.owner_id, s.zekken_number, s.label
|
||
FROM team t
|
||
LEFT JOIN start s ON t.id = s.team_id
|
||
WHERE t.is_deleted = false'
|
||
) AS t(
|
||
team_id INTEGER,
|
||
event_id INTEGER,
|
||
owner_id INTEGER,
|
||
zekken_number INTEGER,
|
||
label TEXT
|
||
)
|
||
JOIN rog_newevent2 ne ON ne.id = t.event_id
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM rog_team rt WHERE rt.id = t.team_id
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM rog_entry re WHERE re.team_id = t.team_id AND re.event_id = t.event_id
|
||
)
|
||
ORDER BY t.team_id;
|
||
|
||
""")
|
||
|
||
# 4. GPS情報移行SQL
|
||
f.write("-- ========================================\n")
|
||
f.write("-- 4. GPS情報移行（gifuroge → rogdb）\n")
|
||
f.write("-- ========================================\n\n")
|
||
|
||
f.write("""
|
||
-- gifurogeからGPS情報を移行（gps_information → gps_checkins）
|
||
INSERT INTO gps_checkins (
|
||
path_order, zekken_number, event_code, cp_number,
|
||
lattitude, longitude, image_address, image_receipt,
|
||
image_qr, validate_location, goal_time, late_point,
|
||
create_at, create_user, update_at, update_user,
|
||
buy_flag, colabo_company_memo, points, event_id,
|
||
team_id, validation_status
|
||
)
|
||
SELECT DISTINCT
|
||
0 as path_order,
|
||
gps.zekken_number,
|
||
gps.event_code,
|
||
gps.cp_number,
|
||
gps.lattitude,
|
||
gps.longitude,
|
||
COALESCE(gps.image_address, '') as image_address,
|
||
COALESCE(gps.image_receipt, '') as image_receipt,
|
||
COALESCE(gps.image_qr, false) as image_qr,
|
||
COALESCE(gps.validate_location, false) as validate_location,
|
||
COALESCE(gps.goal_time, '') as goal_time,
|
||
COALESCE(gps.late_point, 0) as late_point,
|
||
COALESCE(gps.create_at, NOW()) as create_at,
|
||
COALESCE(gps.create_user, '') as create_user,
|
||
COALESCE(gps.update_at, NOW()) as update_at,
|
||
COALESCE(gps.update_user, '') as update_user,
|
||
COALESCE(gps.buy_flag, false) as buy_flag,
|
||
COALESCE(gps.colabo_company_memo, '') as colabo_company_memo,
|
||
COALESCE(gps.points, 0) as points,
|
||
ent.event_id,
|
||
ent.team_id,
|
||
'pending' as validation_status
|
||
FROM dblink('host=postgres-db port=5432 dbname=gifuroge user=user password=password',
|
||
'SELECT zekken_number, event_code, cp_number, lattitude, longitude,
|
||
image_address, image_receipt, image_qr, validate_location,
|
||
goal_time, late_point, create_at, create_user, update_at,
|
||
update_user, buy_flag, colabo_company_memo, points
|
||
FROM gps_information
|
||
ORDER BY create_at'
|
||
) AS gps(
|
||
zekken_number TEXT,
|
||
event_code TEXT,
|
||
cp_number INTEGER,
|
||
lattitude DOUBLE PRECISION,
|
||
longitude DOUBLE PRECISION,
|
||
image_address TEXT,
|
||
image_receipt TEXT,
|
||
image_qr BOOLEAN,
|
||
validate_location BOOLEAN,
|
||
goal_time TEXT,
|
||
late_point INTEGER,
|
||
create_at TIMESTAMP,
|
||
create_user TEXT,
|
||
update_at TIMESTAMP,
|
||
update_user TEXT,
|
||
buy_flag BOOLEAN,
|
||
colabo_company_memo TEXT,
|
||
points INTEGER
|
||
)
|
||
LEFT JOIN rog_entry ent ON ent.zekken_number = CAST(gps.zekken_number AS INTEGER)
|
||
WHERE ent.id IS NOT NULL
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM gps_checkins gc
|
||
WHERE gc.zekken_number = gps.zekken_number
|
||
AND gc.event_code = gps.event_code
|
||
AND gc.cp_number = gps.cp_number
|
||
AND gc.create_at = gps.create_at
|
||
);
|
||
|
||
""")
|
||
|
||
# 5. 統計クエリ
|
||
f.write("-- ========================================\n")
|
||
f.write("-- 5. 移行結果確認クエリ\n")
|
||
f.write("-- ========================================\n\n")
|
||
|
||
f.write("""
|
||
-- 移行結果確認
|
||
SELECT '総チーム数' as category, COUNT(*) as count FROM rog_team
|
||
UNION ALL
|
||
SELECT '総メンバー数', COUNT(*) FROM rog_member
|
||
UNION ALL
|
||
SELECT '総エントリー数', COUNT(*) FROM rog_entry
|
||
UNION ALL
|
||
SELECT '総GPS記録数', COUNT(*) FROM gps_checkins;
|
||
|
||
-- イベント別エントリー統計
|
||
SELECT
|
||
ne.name as event_name,
|
||
COUNT(re.id) as entry_count,
|
||
COUNT(gc.id) as gps_count
|
||
FROM rog_newevent2 ne
|
||
LEFT JOIN rog_entry re ON ne.id = re.event_id
|
||
LEFT JOIN gps_checkins gc ON ne.id = gc.event_id
|
||
GROUP BY ne.id, ne.name
|
||
ORDER BY entry_count DESC;
|
||
|
||
""")
|
||
|
||
print(f"✅ SQLファイル生成完了: {sql_file}")
|
||
|
||
# === STEP 4: 実行方法の案内 ===
|
||
print("\n=== STEP 4: 実行方法 ===")
|
||
print(f"📝 生成されたSQLファイル: {sql_file}")
|
||
print("\n🚀 実行方法:")
|
||
print("1. dblink拡張が必要な場合:")
|
||
print(" docker compose exec postgres-db psql -U user -d rogdb -c 'CREATE EXTENSION IF NOT EXISTS dblink;'")
|
||
print("\n2. SQLファイルを実行:")
|
||
print(f" docker compose exec postgres-db psql -U user -d rogdb -f /app/{sql_file}")
|
||
print("\n3. 結果確認:")
|
||
print(" docker compose exec postgres-db psql -U user -d rogdb -c 'SELECT COUNT(*) FROM rog_entry;'")
|
||
print(" docker compose exec postgres-db psql -U user -d rogdb -c 'SELECT COUNT(*) FROM gps_checkins;'")
|
||
|
||
print("\n✅ SQL移行スクリプト生成が完了しました！")
|
||
print("🎯 上記のコマンドを実行して、全イベントデータ＋GPS情報を移行してください。")
|
||
|
||
except Exception as e:
|
||
print(f"❌ エラーが発生しました: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|