201 lines
7.7 KiB
Python
201 lines
7.7 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
export_translations.py
|
|
|
|
Genera SQL para importar todos los posts traducidos (ID > 42760)
|
|
de la DB local a producción, con remapeo correcto de language IDs (FR↔PT).
|
|
"""
|
|
|
|
import pymysql
|
|
|
|
DB_LOCAL = dict(host='172.18.0.2', port=3306, user='wordpress_user',
|
|
password='wordpress_pass', database='wordpress_db', charset='utf8mb4',
|
|
cursorclass=pymysql.cursors.DictCursor)
|
|
|
|
# Local lang term_taxonomy_id → Production term_taxonomy_id
|
|
# Local: en=1407, es=1404, fr=1419, it=1415, pt=1411
|
|
# Prod: en=1407, es=1404, fr=1411, it=1415, pt=1419
|
|
LANG_MAP = {1407: 1407, 1404: 1404, 1419: 1411, 1415: 1415, 1411: 1419}
|
|
|
|
def esc(s):
|
|
if s is None:
|
|
return 'NULL'
|
|
return "'" + str(s).replace('\\', '\\\\').replace("'", "\\'") + "'"
|
|
|
|
db = pymysql.connect(**DB_LOCAL)
|
|
c = db.cursor()
|
|
|
|
lines = []
|
|
lines.append("SET NAMES utf8mb4;")
|
|
lines.append("SET foreign_key_checks = 0;")
|
|
lines.append("")
|
|
|
|
# ── 1. wp_posts ───────────────────────────────────────────────────────────────
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 1. POSTS (ID > 42760)")
|
|
lines.append("-- ============================================================")
|
|
|
|
c.execute("""
|
|
SELECT ID, post_author, post_date, post_date_gmt, post_content, post_title,
|
|
post_excerpt, post_status, comment_status, ping_status, post_password,
|
|
post_name, to_ping, pinged, post_modified, post_modified_gmt,
|
|
post_content_filtered, post_parent, guid, menu_order, post_type,
|
|
post_mime_type, comment_count
|
|
FROM wp_posts
|
|
WHERE ID > 42760 AND post_status='publish' AND post_type='post'
|
|
ORDER BY ID
|
|
""")
|
|
posts = c.fetchall()
|
|
lines.append(f"-- {len(posts)} posts")
|
|
|
|
for p in posts:
|
|
cols = ['ID','post_author','post_date','post_date_gmt','post_content','post_title',
|
|
'post_excerpt','post_status','comment_status','ping_status','post_password',
|
|
'post_name','to_ping','pinged','post_modified','post_modified_gmt',
|
|
'post_content_filtered','post_parent','guid','menu_order','post_type',
|
|
'post_mime_type','comment_count']
|
|
vals = ', '.join(esc(p[col]) for col in cols)
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_posts ({', '.join(cols)}) VALUES ({vals});"
|
|
)
|
|
|
|
lines.append("")
|
|
|
|
# ── 2. wp_term_relationships — language ──────────────────────────────────────
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 2. LANGUAGE ASSIGNMENTS (remapped FR↔PT)")
|
|
lines.append("-- ============================================================")
|
|
|
|
post_ids = [p['ID'] for p in posts]
|
|
fmt = ','.join(str(i) for i in post_ids)
|
|
|
|
c.execute(f"""
|
|
SELECT object_id, term_taxonomy_id
|
|
FROM wp_term_relationships
|
|
WHERE object_id IN ({fmt})
|
|
AND term_taxonomy_id IN (1404,1407,1411,1415,1419)
|
|
""")
|
|
lang_rels = c.fetchall()
|
|
lines.append(f"-- {len(lang_rels)} language assignments")
|
|
|
|
for r in lang_rels:
|
|
prod_ttid = LANG_MAP[r['term_taxonomy_id']]
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) "
|
|
f"VALUES ({r['object_id']}, {prod_ttid}, 0);"
|
|
)
|
|
|
|
lines.append("")
|
|
|
|
# ── 3. wp_terms + wp_term_taxonomy — post_translations groups ─────────────────
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 3. POST_TRANSLATIONS GROUPS (term_taxonomy_id 2705-3043)")
|
|
lines.append("-- ============================================================")
|
|
|
|
c.execute(f"""
|
|
SELECT DISTINCT tt.term_taxonomy_id, tt.term_id, tt.taxonomy, tt.description,
|
|
tt.parent, tt.count, t.name, t.slug, t.term_group
|
|
FROM wp_term_taxonomy tt
|
|
JOIN wp_terms t ON tt.term_id=t.term_id
|
|
JOIN wp_term_relationships tr ON tt.term_taxonomy_id=tr.term_taxonomy_id
|
|
WHERE tt.taxonomy='post_translations'
|
|
AND tr.object_id IN ({fmt})
|
|
ORDER BY tt.term_taxonomy_id
|
|
""")
|
|
pt_groups = c.fetchall()
|
|
lines.append(f"-- {len(pt_groups)} translation groups")
|
|
|
|
for g in pt_groups:
|
|
# wp_terms
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_terms (term_id, name, slug, term_group) "
|
|
f"VALUES ({g['term_id']}, {esc(g['name'])}, {esc(g['slug'])}, {g['term_group']});"
|
|
)
|
|
# wp_term_taxonomy
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_term_taxonomy (term_taxonomy_id, term_id, taxonomy, description, parent, count) "
|
|
f"VALUES ({g['term_taxonomy_id']}, {g['term_id']}, 'post_translations', "
|
|
f"{esc(g['description'])}, {g['parent']}, {g['count']});"
|
|
)
|
|
|
|
lines.append("")
|
|
|
|
# ── 4. wp_term_relationships — post_translations (ALL members of each group) ──
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 4. POST_TRANSLATIONS RELATIONSHIPS (all group members)")
|
|
lines.append("-- ============================================================")
|
|
|
|
pt_ttids = [g['term_taxonomy_id'] for g in pt_groups]
|
|
fmt_tt = ','.join(str(i) for i in pt_ttids)
|
|
|
|
c.execute(f"""
|
|
SELECT object_id, term_taxonomy_id
|
|
FROM wp_term_relationships
|
|
WHERE term_taxonomy_id IN ({fmt_tt})
|
|
ORDER BY term_taxonomy_id, object_id
|
|
""")
|
|
pt_rels = c.fetchall()
|
|
lines.append(f"-- {len(pt_rels)} translation group relationships")
|
|
|
|
for r in pt_rels:
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) "
|
|
f"VALUES ({r['object_id']}, {r['term_taxonomy_id']}, 0);"
|
|
)
|
|
|
|
lines.append("")
|
|
|
|
# ── 5. wp_term_relationships — categories ─────────────────────────────────────
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 5. CATEGORY ASSIGNMENTS")
|
|
lines.append("-- ============================================================")
|
|
|
|
c.execute(f"""
|
|
SELECT tr.object_id, tr.term_taxonomy_id
|
|
FROM wp_term_relationships tr
|
|
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id
|
|
WHERE tr.object_id IN ({fmt})
|
|
AND tt.taxonomy='category'
|
|
ORDER BY tr.object_id
|
|
""")
|
|
cat_rels = c.fetchall()
|
|
lines.append(f"-- {len(cat_rels)} category assignments")
|
|
|
|
for r in cat_rels:
|
|
lines.append(
|
|
f"INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) "
|
|
f"VALUES ({r['object_id']}, {r['term_taxonomy_id']}, 0);"
|
|
)
|
|
|
|
lines.append("")
|
|
lines.append("-- ============================================================")
|
|
lines.append("-- 6. UPDATE term_taxonomy counts")
|
|
lines.append("-- ============================================================")
|
|
lines.append("""
|
|
UPDATE wp_term_taxonomy tt
|
|
SET count = (
|
|
SELECT COUNT(*) FROM wp_term_relationships tr
|
|
JOIN wp_posts p ON tr.object_id=p.ID
|
|
WHERE tr.term_taxonomy_id=tt.term_taxonomy_id
|
|
AND p.post_status='publish'
|
|
)
|
|
WHERE tt.taxonomy IN ('language','post_translations','category');
|
|
""")
|
|
|
|
lines.append("SET foreign_key_checks = 1;")
|
|
lines.append(f"-- Export complete: {len(posts)} posts, {len(pt_groups)} translation groups")
|
|
|
|
db.close()
|
|
|
|
output = '\n'.join(lines)
|
|
with open('/tmp/translations_export.sql', 'w', encoding='utf-8') as f:
|
|
f.write(output)
|
|
|
|
print(f"SQL written to /tmp/translations_export.sql")
|
|
print(f" Posts: {len(posts)}")
|
|
print(f" Language rels: {len(lang_rels)}")
|
|
print(f" Translation groups: {len(pt_groups)}")
|
|
print(f" Group rels: {len(pt_rels)}")
|
|
print(f" Category rels: {len(cat_rels)}")
|
|
print(f" File size: {len(output)//1024} KB")
|