194 lines
9.5 KiB
Python
194 lines
9.5 KiB
Python
import re
|
|
import csv
|
|
import sys
|
|
import os
|
|
import shutil
|
|
import oracledb
|
|
|
|
csv.field_size_limit(sys.maxsize)
|
|
|
|
SQL_FILE = "sisvietnamvn_Trang chính thức hiện tại/Database/sisvietnam_db.sql"
|
|
UPLOADS_DIR = "sisvietnamvn_Trang chính thức hiện tại/sisvietnam.vn/DocumentRoot/wp-content/uploads"
|
|
STATIC_UPLOAD = "sisvietnamvn_main/src/main/resources/static/upload"
|
|
|
|
def parse_sql_dump(filepath, table_name):
|
|
rows = []
|
|
columns = []
|
|
in_insert = False
|
|
with open(filepath, 'r', encoding='utf-8') as f:
|
|
for line in f:
|
|
if line.startswith(f"INSERT INTO `{table_name}`"):
|
|
col_match = re.search(r'\((.*?)\)', line)
|
|
if col_match:
|
|
columns = [c.strip('` ') for c in col_match.group(1).split(',')]
|
|
in_insert = True
|
|
continue
|
|
if in_insert:
|
|
if line.startswith('('):
|
|
val_str = line.strip().rstrip(',;')
|
|
if val_str.endswith(')'):
|
|
inner = val_str[1:-1].replace("\\'", "''").replace("\\n", "\n").replace("\\r", "\r").replace("\\t", "\t")
|
|
try:
|
|
for parsed_row in csv.reader([inner], quotechar="'", escapechar="\\", skipinitialspace=True):
|
|
if len(parsed_row) == len(columns):
|
|
rows.append(dict(zip(columns, parsed_row)))
|
|
except Exception:
|
|
pass
|
|
if line.strip().endswith(';'):
|
|
in_insert = False
|
|
return rows
|
|
|
|
print("Loading data...")
|
|
posts = {r['ID']: r for r in parse_sql_dump(SQL_FILE, "sis_posts")}
|
|
postmeta = parse_sql_dump(SQL_FILE, "sis_postmeta")
|
|
terms = {r['term_id']: r for r in parse_sql_dump(SQL_FILE, "sis_terms")}
|
|
term_taxonomy = {r['term_taxonomy_id']: r for r in parse_sql_dump(SQL_FILE, "sis_term_taxonomy")}
|
|
term_relationships = parse_sql_dump(SQL_FILE, "sis_term_relationships")
|
|
|
|
meta_dict = {}
|
|
for m in postmeta:
|
|
pid = m['post_id']
|
|
if pid not in meta_dict: meta_dict[pid] = {}
|
|
meta_dict[pid][m['meta_key']] = m['meta_value']
|
|
|
|
term_to_tax = {tx['term_id']: tx for tx in term_taxonomy.values()}
|
|
|
|
post_terms = {}
|
|
for rel in term_relationships:
|
|
pid = rel['object_id']
|
|
tax_id = rel['term_taxonomy_id']
|
|
if tax_id in term_taxonomy:
|
|
term_id = term_taxonomy[tax_id]['term_id']
|
|
tax_type = term_taxonomy[tax_id]['taxonomy']
|
|
if pid not in post_terms: post_terms[pid] = {}
|
|
if tax_type not in post_terms[pid]: post_terms[pid][tax_type] = []
|
|
if term_id in terms: post_terms[pid][tax_type].append(terms[term_id])
|
|
|
|
conn = oracledb.connect(user='sisvietnam', password='sisvietnam', dsn='localhost:1521/sisvietnam')
|
|
cursor = conn.cursor()
|
|
|
|
print("Migrating Categories...")
|
|
for t in terms.values():
|
|
tid = t['term_id']
|
|
if tid in term_to_tax and term_to_tax[tid]['taxonomy'] == 'category':
|
|
cursor.execute("""
|
|
MERGE INTO sis_category dest
|
|
USING (SELECT :id as id, :name as name, :slug as slug, :description as description FROM dual) src
|
|
ON (dest.slug = src.slug)
|
|
WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.description = src.description
|
|
WHEN NOT MATCHED THEN INSERT (id, name, slug, description, created_by, created_date, last_modified_by, last_modified_date)
|
|
VALUES (src.id, src.name, src.slug, src.description, 'system', sysdate, 'system', sysdate)
|
|
""", id=tid, name=t['name'], slug=t['slug'], description=term_to_tax[tid]['description'])
|
|
conn.commit()
|
|
|
|
print("Migrating Attachments...")
|
|
for pid, p in posts.items():
|
|
if p['post_type'] == 'attachment':
|
|
guid = p.get('guid', '')
|
|
if guid:
|
|
file_name = guid.split('/')[-1]
|
|
rel_path = guid.split('wp-content/uploads/')[-1] if 'wp-content/uploads/' in guid else file_name
|
|
src = os.path.join(UPLOADS_DIR, rel_path)
|
|
dst = os.path.join(STATIC_UPLOAD, rel_path)
|
|
if os.path.exists(src):
|
|
os.makedirs(os.path.dirname(dst), exist_ok=True)
|
|
shutil.copy2(src, dst)
|
|
|
|
cursor.execute("""
|
|
MERGE INTO sis_media dest
|
|
USING (SELECT :id as id, :name as original_filename, :stored as stored_filename, :url as file_url FROM dual) src
|
|
ON (dest.id = src.id)
|
|
WHEN MATCHED THEN UPDATE SET dest.original_filename = src.original_filename, dest.stored_filename = src.stored_filename, dest.file_url = src.file_url
|
|
WHEN NOT MATCHED THEN INSERT (id, original_filename, stored_filename, file_url, media_type, created_by, created_date, last_modified_by, last_modified_date)
|
|
VALUES (src.id, src.original_filename, src.stored_filename, src.file_url, 'IMAGE', 'system', sysdate, 'system', sysdate)
|
|
""", id=pid, name=file_name, stored=file_name, url=f"/upload/{rel_path}")
|
|
conn.commit()
|
|
|
|
print("Migrating Posts...")
|
|
for pid, p in posts.items():
|
|
if p['post_type'] == 'post' and p['post_status'] == 'publish':
|
|
cat_id = None
|
|
if pid in post_terms and 'category' in post_terms[pid]:
|
|
# find mapped category id using slug
|
|
slug = post_terms[pid]['category'][0]['slug']
|
|
cursor.execute("SELECT id FROM sis_category WHERE slug = :slug", slug=slug)
|
|
res = cursor.fetchone()
|
|
if res: cat_id = res[0]
|
|
|
|
thumb_id = meta_dict.get(pid, {}).get('_thumbnail_id')
|
|
featured_image = ""
|
|
if thumb_id and thumb_id in posts:
|
|
guid = posts[thumb_id].get('guid', '')
|
|
if 'wp-content/uploads/' in guid:
|
|
featured_image = "/upload/" + guid.split('wp-content/uploads/')[-1]
|
|
|
|
# Limit content/excerpt sizes if needed, or rely on CLOB
|
|
try:
|
|
cursor.execute("""
|
|
MERGE INTO sis_post dest
|
|
USING (SELECT :id as id, :title as title, :slug as slug, :content as content, :excerpt as excerpt, :img as img, :cat as cat FROM dual) src
|
|
ON (dest.id = src.id)
|
|
WHEN MATCHED THEN UPDATE SET dest.title = src.title, dest.slug = src.slug, dest.content = src.content, dest.featured_image_url = src.img
|
|
WHEN NOT MATCHED THEN INSERT (id, title, slug, content, excerpt, featured_image_url, category_id, status, created_by, created_date, last_modified_by, last_modified_date)
|
|
VALUES (src.id, src.title, src.slug, src.content, src.excerpt, src.img, src.cat, 'PUBLISHED', 'system', sysdate, 'system', sysdate)
|
|
""", id=pid, title=p['post_title'], slug=p['post_name'], content=p['post_content'], excerpt=p['post_excerpt'], img=featured_image, cat=cat_id)
|
|
except Exception as e:
|
|
# Duplicate slug error or something else, handle gracefully
|
|
pass
|
|
conn.commit()
|
|
|
|
print("Migrating Doctors...")
|
|
for t in terms.values():
|
|
tid = t['term_id']
|
|
if tid in term_to_tax and term_to_tax[tid]['taxonomy'] == 'specialties':
|
|
# we don't have slug on specialty right now, use ID merge
|
|
cursor.execute("""
|
|
MERGE INTO sis_specialty dest
|
|
USING (SELECT :id as id, :name as name, :desc as description FROM dual) src
|
|
ON (dest.id = src.id)
|
|
WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.description = src.description
|
|
WHEN NOT MATCHED THEN INSERT (id, name, description, created_by, created_date, last_modified_by, last_modified_date)
|
|
VALUES (src.id, src.name, src.desc, 'system', sysdate, 'system', sysdate)
|
|
""", id=tid, name=t['name'], desc=term_to_tax[tid]['description'])
|
|
conn.commit()
|
|
|
|
for pid, p in posts.items():
|
|
if p['post_type'] == 'employee' and p['post_status'] == 'publish':
|
|
spec_id = None
|
|
if pid in post_terms and 'specialties' in post_terms[pid]:
|
|
spec_id = post_terms[pid]['specialties'][0]['term_id']
|
|
|
|
m = meta_dict.get(pid, {})
|
|
position = m.get('position', '')
|
|
gender = m.get('gender', '')
|
|
birthday = m.get('birthday', '')
|
|
phone = m.get('phone_number', '')
|
|
email = m.get('email', '')
|
|
address = m.get('address', '')
|
|
|
|
raw_title = p['post_title'].strip()
|
|
parts = raw_title.split(' ', 1)
|
|
title = parts[0] if '.' in parts[0] else ''
|
|
name = parts[1] if title and len(parts) > 1 else raw_title
|
|
if not title: name = raw_title
|
|
|
|
thumb_id = meta_dict.get(pid, {}).get('_thumbnail_id')
|
|
avatar = ""
|
|
if thumb_id and thumb_id in posts:
|
|
guid = posts[thumb_id].get('guid', '')
|
|
if 'wp-content/uploads/' in guid:
|
|
avatar = "/upload/" + guid.split('wp-content/uploads/')[-1]
|
|
|
|
cursor.execute("""
|
|
MERGE INTO sis_doctor dest
|
|
USING (SELECT :id as id, :name as name, :title as title, :avatar as avatar, :pos as pos, :spec as spec FROM dual) src
|
|
ON (dest.id = src.id)
|
|
WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.title = src.title, dest.avatar_url = src.avatar, dest.position = src.pos, dest.specialty_id = src.spec
|
|
WHEN NOT MATCHED THEN INSERT (id, name, title, avatar_url, position, specialty_id, active, created_by, created_date, last_modified_by, last_modified_date)
|
|
VALUES (src.id, src.name, src.title, src.avatar, src.pos, src.spec, 1, 'system', sysdate, 'system', sysdate)
|
|
""", id=pid, name=name, title=title, avatar=avatar, pos=position, spec=spec_id)
|
|
conn.commit()
|
|
|
|
print("Migration Complete!")
|
|
conn.close()
|