""" Migration: Drupal MySQL (nirta_letter) → PostgreSQL (jarvis) Migrates all 'page' content type nodes to the surat table. """ import asyncio import re import sys from datetime import datetime, date import aiomysql import asyncpg # ── config ────────────────────────────────────────────────────────────────── MYSQL_CFG = dict(host="127.0.0.1", port=3306, user="root", password="", db="nirta_letter", charset="utf8mb4") PG_DSN = "postgresql://dhardhirdhor@localhost:5432/jarvis" # ── helpers ────────────────────────────────────────────────────────────────── def auto_code(name: str, max_len: int = 10) -> str: words = re.findall(r'\w+', name.upper()) if len(words) == 1: return words[0][:max_len] return ''.join(w[0] for w in words)[:max_len] CATEGORY_MAP = { "surat_kuasa": "Surat Kuasa", "surat_keterangan": "Surat Keterangan", "surat_penawaran_harga":"Surat Penawaran Harga", "surat_pernyataan": "Surat Pernyataan", "surat_kessanggupan": "Surat Kesanggupan", # typo in Drupal "surat_permohonan": "Surat Permohonan", "bank": "Surat Permohonan", # mapped to same } # ── main ────────────────────────────────────────────────────────────────────── async def main(): print("Connecting to databases...") my = await aiomysql.connect(**MYSQL_CFG) pg = await asyncpg.connect(PG_DSN) try: # ── 1. load existing PG data ────────────────────────────────────── pg_companies = {r["name"]: r["id"] for r in await pg.fetch("SELECT id, name FROM companies")} pg_categories = {r["name"]: r["id"] for r in await pg.fetch("SELECT id, name FROM categories")} # ── 2. ensure Surat Permohonan category exists ──────────────────── if "Surat Permohonan" not in pg_categories: cid = await pg.fetchval( "INSERT INTO categories(name, code) VALUES($1,$2) RETURNING id", "Surat Permohonan", "SUPER2" ) pg_categories["Surat Permohonan"] = cid print(f" Created category: Surat Permohonan (id={cid})") # ── 3. migrate companies from Drupal ────────────────────────────── cur = await my.cursor(aiomysql.DictCursor) await cur.execute(""" SELECT nfd.nid, nfd.title, na.field_address_value AS address, ne.field_email_value AS email, np.field_phone_number_value AS phone, nn.field_nib_value AS nib FROM node_field_data nfd LEFT JOIN node__field_address na ON na.entity_id = nfd.nid LEFT JOIN node__field_email ne ON ne.entity_id = nfd.nid LEFT JOIN node__field_phone_number np ON np.entity_id = nfd.nid LEFT JOIN node__field_nib nn ON nn.entity_id = nfd.nid WHERE nfd.type = 'company' """) drupal_companies = await cur.fetchall() # nid → new pg company id company_id_map: dict[int, int] = {} for c in drupal_companies: name = c["title"] if name in pg_companies: company_id_map[c["nid"]] = pg_companies[name] print(f" Company already exists: {name} (id={pg_companies[name]})") else: code = auto_code(name) # ensure code uniqueness base, n = code, 1 existing_codes = {r["code"] for r in await pg.fetch("SELECT code FROM companies")} while code in existing_codes: code = f"{base}{n}"; n += 1 cid = await pg.fetchval( """INSERT INTO companies(name, code, address, email, phone, nib) VALUES($1,$2,$3,$4,$5,$6) RETURNING id""", name, code, c["address"], c["email"], c["phone"], c["nib"] ) pg_companies[name] = cid company_id_map[c["nid"]] = cid print(f" Created company: {name} (id={cid}, code={code})") # ── 4. fetch all page nodes with their fields ───────────────────── await cur.execute(""" SELECT nfd.nid, nfd.title, FROM_UNIXTIME(nfd.created) AS created_at, ns.field_no_surat_value AS no_surat, np.field_perihal_value AS perihal, nt.field_tanggal_value AS tanggal, ncm.field_company_target_id AS company_nid, ncat.field_category_value AS category, na.field_approve_value AS is_approved, nb.body_value AS body, ntm.field_tembusan_value AS tembusan FROM node_field_data nfd LEFT JOIN node__field_no_surat ns ON ns.entity_id = nfd.nid LEFT JOIN node__field_perihal np ON np.entity_id = nfd.nid LEFT JOIN node__field_tanggal nt ON nt.entity_id = nfd.nid LEFT JOIN node__field_company ncm ON ncm.entity_id = nfd.nid LEFT JOIN node__field_category ncat ON ncat.entity_id = nfd.nid LEFT JOIN node__field_approve na ON na.entity_id = nfd.nid LEFT JOIN node__body nb ON nb.entity_id = nfd.nid LEFT JOIN node__field_tembusan ntm ON ntm.entity_id = nfd.nid WHERE nfd.type = 'page' ORDER BY nfd.nid """) nodes = await cur.fetchall() await cur.close() # ── 5. insert into surat ────────────────────────────────────────── inserted = skipped = 0 for n in nodes: no_surat = n["no_surat"] if not no_surat: print(f" SKIP nid={n['nid']} — missing no_surat") skipped += 1 continue # resolve company company_nid = n["company_nid"] company_pg_id = company_id_map.get(company_nid) if not company_pg_id: print(f" SKIP nid={n['nid']} — unknown company nid={company_nid}") skipped += 1 continue # resolve category cat_drupal = n["category"] or "surat_permohonan" cat_name = CATEGORY_MAP.get(cat_drupal, "Surat Permohonan") category_pg_id = pg_categories.get(cat_name) if not category_pg_id: print(f" WARN nid={n['nid']} — category '{cat_drupal}' not resolved, using Surat Permohonan") category_pg_id = pg_categories["Surat Permohonan"] perihal = n["perihal"] or n["title"] # fallback to title tanggal_raw = n["tanggal"] tanggal = date.fromisoformat(tanggal_raw) if tanggal_raw else None is_approved = bool(n["is_approved"]) approved_at = datetime.utcnow() if is_approved else None created_at = n["created_at"] or datetime.utcnow() # check duplicate exists = await pg.fetchval( "SELECT id FROM surat WHERE no_surat=$1", no_surat ) if exists: print(f" SKIP nid={n['nid']} — no_surat '{no_surat}' already exists (id={exists})") skipped += 1 continue sid = await pg.fetchval(""" INSERT INTO surat( no_surat, subject, perihal, company_id, category_id, tanggal, body, tembusan, needs_director_sig, needs_manager_sig, is_approved, approved_at, created_at, updated_at ) VALUES($1,$2,$3,$4,$5,$6,$7,$8, false, false, $9,$10,$11,$11) RETURNING id """, no_surat, n["title"], perihal, company_pg_id, category_pg_id, tanggal, n["body"], n["tembusan"], is_approved, approved_at, created_at ) inserted += 1 print(f" ✓ nid={n['nid']} → surat id={sid} [{no_surat}]") print(f"\nDone. inserted={inserted} skipped={skipped}") finally: my.close() await pg.close() if __name__ == "__main__": asyncio.run(main())