497 lines
34 KiB
TypeScript
497 lines
34 KiB
TypeScript
/**
|
||
* # 函数 WordPress → PostgreSQL 全量导入脚本
|
||
* 从 MariaDB（备份库）读取 wp_* 表，写入新 PG 数据模型。
|
||
* 幂等：先清空业务表再导入。用法：bun run scripts/wordpress-import/import.ts
|
||
*/
|
||
import mysql from "mysql2/promise";
|
||
import { SQL } from "bun";
|
||
|
||
import { phpUnserialize } from "./php-unserialize";
|
||
|
||
/* ---------- 连接 ---------- */
|
||
const legacy = await mysql.createPool({
|
||
host: process.env.LEGACY_DB_HOST || "127.0.0.1",
|
||
port: Number(process.env.LEGACY_DB_PORT || 13306),
|
||
user: process.env.LEGACY_DB_USER || "root",
|
||
password: process.env.LEGACY_DB_PASSWORD || "root",
|
||
database: process.env.LEGACY_DB_NAME || "wp",
|
||
connectionLimit: 4,
|
||
charset: "utf8mb4",
|
||
});
|
||
|
||
const sql = new SQL(process.env.DATABASE_URL || "postgres://dusuniot:dusuniot@127.0.0.1:25432/dusuniot");
|
||
|
||
const log = (...a: unknown[]) => console.log(new Date().toISOString().slice(11, 19), ...a);
|
||
|
||
/** # 成员变量 分类映射：WP term_id → 新分类 ID（分类步骤填充，或从 PG 加载） **/
|
||
const catMap = new Map<number, number>();
|
||
/** # 成员变量 属性映射：属性 slug → 新属性 ID **/
|
||
const attrMap = new Map<string, number>();
|
||
/** # 成员变量 属性值映射：`属性ID:值slug` → 新值 ID **/
|
||
const attrValMap = new Map<string, number>();
|
||
/** # 函数 部分重跑时从 PG 恢复映射表（categories/attributes 已导入的前提） */
|
||
async function loadMaps() {
|
||
if (catMap.size === 0) for (const r of await sql`SELECT id, legacy_id FROM categories WHERE legacy_id IS NOT NULL`.values()) catMap.set(r[1] as number, r[0] as number);
|
||
if (attrMap.size === 0) {
|
||
for (const r of await sql`SELECT id, slug FROM product_attributes`.values()) attrMap.set(r[1] as string, r[0] as number);
|
||
for (const r of await sql`SELECT v.id, v.slug, a.slug AS attr FROM product_attribute_values v JOIN product_attributes a ON a.id=v.attribute_id`.values())
|
||
attrValMap.set(`${r[2]}:${r[1]}`, r[0] as number);
|
||
}
|
||
}
|
||
|
||
/** # 成员变量 部分导入：传 --only=step1,step2 只跑指定步骤（缺省全量） **/
|
||
const only = process.argv[2] && process.argv[2].startsWith("--only=") ? process.argv[2].slice(7).split(",") : null;
|
||
const should = (name: string) => !only || only.includes(name);
|
||
/** # 函数 截断字符串，防止超出 varchar 宽度 */
|
||
const cut = (v: string | undefined, n: number) => (v || "").slice(0, n);
|
||
|
||
/* ---------- 清空（按外键无关顺序） ---------- */
|
||
if (should("reset")) { log("清空业务表...");
|
||
await sql`TRUNCATE TABLE content_terms, product_terms, product_attribute_map, order_items, payments, cart_items, menu_items, content_revisions, content_translations, product_translations, product_attribute_values, product_attributes, audit_logs RESTART IDENTITY CASCADE`;
|
||
await sql`TRUNCATE TABLE contents, categories, products, users, sessions, carts, orders, inquiries, inquiry_forms, media, redirects, filters, languages, jobs, settings, legacy_id_mappings, menus RESTART IDENTITY CASCADE`;
|
||
}
|
||
|
||
/* ---------- 1. 媒体 ---------- */
|
||
if (should("media")) { log("导入媒体...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_title, meta.path, a.alt,
|
||
JSON_UNQUOTE(JSON_EXTRACT(m.meta, '$.width')) width,
|
||
JSON_UNQUOTE(JSON_EXTRACT(m.meta, '$.height')) height
|
||
FROM wp_posts p
|
||
JOIN (SELECT post_id, meta_value path FROM wp_postmeta WHERE meta_key='_wp_attached_file') meta ON meta.post_id=p.ID
|
||
LEFT JOIN (SELECT post_id, meta_value alt FROM wp_postmeta WHERE meta_key='_wp_attachment_image_alt') a ON a.post_id=p.ID
|
||
LEFT JOIN (SELECT post_id, meta_value meta FROM wp_postmeta WHERE meta_key='_wp_attachment_metadata') m ON m.post_id=p.ID`
|
||
);
|
||
let width, height;
|
||
for (const r of rows) {
|
||
try { width = r.width ? parseInt(r.width) : null; height = r.height ? parseInt(r.height) : null; } catch { width = height = null; }
|
||
const mime = r.path.endsWith(".pdf") ? "application/pdf" : r.path.endsWith(".svg") ? "image/svg+xml" : r.path.endsWith(".mp4") ? "video/mp4" : `image/${r.path.split(".").pop()?.toLowerCase().replace("jpg", "jpeg") || "jpeg"}`;
|
||
await sql`INSERT INTO media (path, url, mime_type, width, height, alt_text, title, legacy_id, created_at)
|
||
VALUES (${r.path}, ${"/uploads/" + r.path}, ${mime}, ${width}, ${height}, ${r.alt || ""}, ${r.post_title || ""}, ${r.ID}, now())
|
||
ON CONFLICT (path) DO NOTHING`;
|
||
}
|
||
log(`媒体 ${rows.length} 条`);
|
||
}
|
||
|
||
/** legacyId → media id 缓存 */
|
||
const mediaMap = new Map<number, number>();
|
||
for (const r of await sql`SELECT id, legacy_id FROM media WHERE legacy_id IS NOT NULL`.values()) mediaMap.set(r[1] as number, r[0] as number);
|
||
const mediaId = (legacyId: number | null) => (legacyId && mediaMap.get(legacyId)) || null;
|
||
|
||
/* ---------- 2. 用户 ---------- */
|
||
if (should("users")) { log("导入用户...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT u.ID, u.user_login, u.user_email, u.display_name, u.user_pass, u.user_registered,
|
||
MAX(CASE WHEN m.meta_key='wp_capabilities' THEN m.meta_value END) caps,
|
||
GROUP_CONCAT(CASE WHEN m.meta_key LIKE 'billing_%' THEN CONCAT(m.meta_key,'=',m.meta_value) END SEPARATOR '|') billing,
|
||
GROUP_CONCAT(CASE WHEN m.meta_key LIKE 'shipping_%' THEN CONCAT(m.meta_key,'=',m.meta_value) END SEPARATOR '|') shipping
|
||
FROM wp_users u LEFT JOIN wp_usermeta m ON m.user_id=u.ID GROUP BY u.ID`
|
||
);
|
||
/** # 成员变量 后台初始管理员密码（导入结束时打印） **/
|
||
let adminPwd = "";
|
||
for (const r of rows) {
|
||
const caps = typeof r.caps === "string" && r.caps ? (phpUnserialize(r.caps) as Record<string, unknown>) : {};
|
||
const isAdmin = "administrator" in caps;
|
||
const kv = (s: string | null) => {
|
||
const o: Record<string, string> = {};
|
||
for (const pair of (s || "").split("|")) { const idx = pair.indexOf("="); if (idx > 0) o[pair.slice(0, idx)] = pair.slice(idx + 1); }
|
||
return o;
|
||
};
|
||
if (isAdmin) {
|
||
if (!adminPwd) {
|
||
adminPwd = "Dusun#Admin2026!";
|
||
const hash = Bun.password.hashSync(adminPwd, { algorithm: "bcrypt", cost: 10 });
|
||
await sql`INSERT INTO users (email, username, display_name, password_hash, role, legacy_id, created_at)
|
||
VALUES (${r.user_email || "admin@dusuniot.local"}, ${r.user_login}, ${r.display_name}, ${hash}, 'admin', ${r.ID}, now())
|
||
ON CONFLICT (email) DO UPDATE SET role='admin', password_hash=EXCLUDED.password_hash, legacy_id=EXCLUDED.legacy_id`;
|
||
}
|
||
continue;
|
||
}
|
||
await sql`INSERT INTO users (email, username, display_name, legacy_password_hash, role, billing, shipping, legacy_id, created_at)
|
||
VALUES (${r.user_email || r.user_login + "@legacy.invalid"}, ${r.user_login}, ${r.display_name}, ${r.user_pass}, 'customer',
|
||
${kv(r.billing)}, ${kv(r.shipping)}, ${r.ID}, ${new Date(r.user_registered)})
|
||
ON CONFLICT (email) DO NOTHING`;
|
||
}
|
||
log(`用户 ${rows.length} 条（含 1 个后台管理员）`);
|
||
if (adminPwd) console.log(`>>> 后台管理员: admin <原站管理员邮箱> 密码 ${adminPwd}`);
|
||
}
|
||
|
||
const userIdMap = new Map<number, number>();
|
||
for (const r of await sql`SELECT id, legacy_id FROM users WHERE legacy_id IS NOT NULL`.values()) userIdMap.set(r[1] as number, r[0] as number);
|
||
|
||
/* ---------- 3. 分类/标签 ---------- */
|
||
if (should("categories")) { log("导入分类与标签...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT t.term_id, t.name, t.slug, tt.taxonomy, tt.parent, tt.description, tt.count
|
||
FROM wp_terms t JOIN wp_term_taxonomy tt ON tt.term_id=t.term_id
|
||
WHERE tt.taxonomy IN ('category','post_tag','product_cat','product_tag')`
|
||
);
|
||
// 先插父级再插子级（parent 引用）
|
||
for (const pass of [0, 1]) {
|
||
for (const r of rows) {
|
||
if ((r.parent ? 1 : 0) !== pass) continue;
|
||
const parentId = r.parent ? catMap.get(r.parent) ?? null : null;
|
||
const [res]: any = await sql`INSERT INTO categories (taxonomy, name, slug, parent_id, description, sort, legacy_id)
|
||
VALUES (${r.taxonomy}, ${r.name}, ${r.slug}, ${parentId}, ${r.description || ""}, 0, ${r.term_id}) RETURNING id`;
|
||
catMap.set(r.term_id, res.id);
|
||
}
|
||
}
|
||
log(`分类/标签 ${rows.length} 条`);
|
||
}
|
||
|
||
/* ---------- 4. 产品属性 + 可选值 ---------- */
|
||
if (should("attributes")) { log("导入产品属性...");
|
||
const [attrs] = await legacy.query<any[]>(`SELECT attribute_id, attribute_name, attribute_label FROM wp_woocommerce_attribute_taxonomies`);
|
||
for (const a of attrs) {
|
||
const [res]: any = await sql`INSERT INTO product_attributes (name, slug, filterable, sort) VALUES (${a.attribute_label}, ${a.attribute_name}, true, 0) RETURNING id`;
|
||
attrMap.set(a.attribute_name, res.id);
|
||
const taxName = `pa_${a.attribute_name}`; // attribute_name 为纯 slug，无注入风险
|
||
const [vals] = await legacy.query<any[]>(
|
||
`SELECT t.term_id, t.name, t.slug FROM wp_terms t JOIN wp_term_taxonomy tt ON tt.term_id=t.term_id WHERE tt.taxonomy='${taxName}' ORDER BY t.name`
|
||
);
|
||
for (const v of vals) {
|
||
const [vr]: any = await sql`INSERT INTO product_attribute_values (attribute_id, value, slug) VALUES (${res.id}, ${v.name}, ${v.slug}) RETURNING id`;
|
||
attrValMap.set(`${a.attribute_name}:${v.slug}`, vr.id);
|
||
}
|
||
}
|
||
log(`属性 ${attrs.length} 个，可选值 ${attrValMap.size} 个`);
|
||
}
|
||
|
||
/* ---------- 5. 产品 ---------- */
|
||
if (should("products")) { log("导入产品..."); await loadMaps();
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_name, p.post_title, p.post_excerpt, p.post_content, p.post_date, p.post_status,
|
||
pm.price, pm.regular, pm.sku, pm.stock, pm.stock_status, pm.thumb, pm.gallery, pm.attrs, pm.spec, pm.el, pm.seo_t, pm.seo_d
|
||
FROM wp_posts p
|
||
LEFT JOIN (SELECT post_id, MAX(CASE WHEN meta_key='_price' THEN meta_value END) price,
|
||
MAX(CASE WHEN meta_key='_regular_price' THEN meta_value END) regular,
|
||
MAX(CASE WHEN meta_key='_sku' THEN meta_value END) sku,
|
||
MAX(CASE WHEN meta_key='_stock' THEN meta_value END) stock,
|
||
MAX(CASE WHEN meta_key='_stock_status' THEN meta_value END) stock_status,
|
||
MAX(CASE WHEN meta_key='_thumbnail_id' THEN meta_value END) thumb,
|
||
MAX(CASE WHEN meta_key='_product_image_gallery' THEN meta_value END) gallery,
|
||
MAX(CASE WHEN meta_key='_product_attributes' THEN meta_value END) attrs,
|
||
MAX(CASE WHEN meta_key='_specification url' THEN meta_value END) spec,
|
||
MAX(CASE WHEN meta_key='_elementor_data' THEN meta_value END) el,
|
||
MAX(CASE WHEN meta_key='_rank_math_title' THEN meta_value END) seo_t,
|
||
MAX(CASE WHEN meta_key='_rank_math_description' THEN meta_value END) seo_d
|
||
FROM wp_postmeta WHERE meta_key IN ('_price','_regular_price','_sku','_stock','_stock_status','_thumbnail_id','_product_image_gallery','_product_attributes','_specification url','_elementor_data','_rank_math_title','_rank_math_description') GROUP BY post_id) pm ON pm.post_id=p.ID
|
||
WHERE p.post_type='product' AND p.post_status IN ('publish','draft')`
|
||
);
|
||
for (const r of rows) {
|
||
const attrs = typeof r.attrs === "string" && r.attrs ? (phpUnserialize(r.attrs) as Record<string, any>) : {};
|
||
const gallery = (typeof r.gallery === "string" && r.gallery ? r.gallery.split(",").map((s: string) => parseInt(s.trim())).filter(Boolean) : [])
|
||
.map((id: number) => mediaId(id)).filter(Boolean);
|
||
const [res]: any = await sql`INSERT INTO products (sku, slug, title, short_description, description, price, regular_price,
|
||
stock, stock_status, featured_media_id, gallery, status, seo_title, seo_description, spec_url, meta, legacy_id, created_at, updated_at)
|
||
VALUES (${r.sku || null}, ${r.post_name}, ${r.post_title}, ${r.post_excerpt || ""}, ${r.post_content || ""},
|
||
${r.price || null}, ${r.regular || null}, ${r.stock !== null && r.stock !== "" ? parseInt(r.stock) : null}, ${r.stock_status || "instock"},
|
||
${mediaId(r.thumb ? parseInt(r.thumb) : null)}, ${gallery}, ${r.post_status === "publish" ? "published" : "draft"},
|
||
${r.seo_t || null}, ${r.seo_d || null}, ${r.spec || null}, ${{ elementor: r.el ? JSON.parse(r.el) : null }},
|
||
${r.ID}, ${new Date(r.post_date)}, now())
|
||
ON CONFLICT (slug) DO UPDATE SET updated_at=now() RETURNING id`;
|
||
// 产品分类
|
||
const [terms] = await legacy.query<any[]>(
|
||
`SELECT tt.term_id FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id=tr.term_taxonomy_id
|
||
WHERE tr.object_id=${r.ID} AND tt.taxonomy='product_cat'`
|
||
);
|
||
for (const t of terms) { const c = catMap.get(t.term_id); if (c) await sql`INSERT INTO product_terms (product_id, category_id) VALUES (${res.id}, ${c}) ON CONFLICT DO NOTHING`; }
|
||
// 属性值（全局属性走 pa_* 分类，自定义属性直接存值）
|
||
for (const [key, def] of Object.entries<any>(attrs)) {
|
||
if (!def?.is_taxonomy) continue;
|
||
const attrSlug = key.replace(/^pa_/, "");
|
||
if (!attrMap.has(attrSlug)) continue;
|
||
const [vals] = await legacy.query<any[]>(
|
||
`SELECT t.slug FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN wp_terms t ON t.term_id=tt.term_id
|
||
WHERE tr.object_id=${r.ID} AND tt.taxonomy='${key}'`
|
||
);
|
||
for (const v of vals) {
|
||
const vid = attrValMap.get(`${attrSlug}:${v.slug}`);
|
||
if (vid) await sql`INSERT INTO product_attribute_map (product_id, value_id) VALUES (${res.id}, ${vid}) ON CONFLICT DO NOTHING`;
|
||
}
|
||
}
|
||
}
|
||
log(`产品 ${rows.length} 个`);
|
||
}
|
||
|
||
/* ---------- 6. 内容（页面/文章/落地页） ---------- */
|
||
if (should("contents")) { log("导入内容...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_type, p.post_name, p.post_title, p.post_excerpt, p.post_content, p.post_date, p.post_status,
|
||
pm.thumb, pm.el, pm.seo_t, pm.seo_d, pm.author
|
||
FROM wp_posts p
|
||
LEFT JOIN (SELECT post_id,
|
||
MAX(CASE WHEN meta_key='_thumbnail_id' THEN meta_value END) thumb,
|
||
MAX(CASE WHEN meta_key='_elementor_data' THEN meta_value END) el,
|
||
MAX(CASE WHEN meta_key='_rank_math_title' THEN meta_value END) seo_t,
|
||
MAX(CASE WHEN meta_key='_rank_math_description' THEN meta_value END) seo_d,
|
||
MAX(CASE WHEN meta_key='_edit_last' THEN meta_value END) author
|
||
FROM wp_postmeta WHERE meta_key IN ('_thumbnail_id','_elementor_data','_rank_math_title','_rank_math_description','_edit_last') GROUP BY post_id) pm ON pm.post_id=p.ID
|
||
WHERE p.post_type IN ('page','post','e-landing-page') AND p.post_status IN ('publish','draft')`
|
||
);
|
||
const typeMap: Record<string, string> = { page: "page", post: "post", "e-landing-page": "landing" };
|
||
for (const r of rows) {
|
||
// 页面里排除 WooCommerce 桩页面（shop/cart/checkout/my-account 由新系统路由实现）
|
||
const isShopStub = r.post_type === "page" && [5, 6, 7, 8].includes(r.ID);
|
||
if (isShopStub) continue;
|
||
const slug = r.post_name || `legacy-${r.ID}`; // 部分落地页无别名，用 legacy-<id> 兜底
|
||
const [res]: any = await sql`INSERT INTO contents (type, slug, title, excerpt, html, status, author_id, featured_media_id, template,
|
||
menu_order, seo_title, seo_description, meta, legacy_id, published_at, created_at, updated_at)
|
||
VALUES (${typeMap[r.post_type]}, ${slug}, ${r.post_title}, ${r.post_excerpt || ""}, ${r.post_content || ""},
|
||
${r.post_status === "publish" ? "published" : "draft"}, ${r.author ? userIdMap.get(parseInt(r.author)) ?? null : null},
|
||
${mediaId(r.thumb ? parseInt(r.thumb) : null)}, ${r.post_type === "page" && r.ID === 18 ? "home" : "default"},
|
||
0, ${r.seo_t || null}, ${r.seo_d || null},
|
||
${{ elementor: r.el ? JSON.parse(r.el) : null }}, ${r.ID}, ${new Date(r.post_date)}, ${new Date(r.post_date)}, now())
|
||
RETURNING id`;
|
||
const [terms] = await legacy.query<any[]>(
|
||
`SELECT tt.term_id FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id=tr.term_taxonomy_id
|
||
WHERE tr.object_id=${r.ID} AND tt.taxonomy IN ('category','post_tag')`
|
||
);
|
||
for (const t of terms) { const c = catMap.get(t.term_id); if (c) await sql`INSERT INTO content_terms (content_id, category_id) VALUES (${res.id}, ${c}) ON CONFLICT DO NOTHING`; }
|
||
}
|
||
log(`内容 ${rows.length} 条`);
|
||
}
|
||
|
||
/* ---------- 7. 浏览数 ---------- */
|
||
if (should("views")) { log("导入浏览数...");
|
||
const [rows] = await legacy.query<any[]>(`SELECT id, SUM(count) c FROM wp_post_views GROUP BY id`);
|
||
for (const r of rows) {
|
||
await sql`UPDATE products SET views=${Number(r.c)} WHERE legacy_id=${r.id}`;
|
||
await sql`UPDATE contents SET views=${Number(r.c)} WHERE legacy_id=${r.id}`;
|
||
}
|
||
log(`浏览数更新 ${rows.length} 条`);
|
||
}
|
||
|
||
/* ---------- 8. 订单 ---------- */
|
||
if (should("orders")) { log("导入订单...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_status, p.post_date,
|
||
MAX(CASE WHEN m.meta_key='_order_total' THEN m.meta_value END) total,
|
||
MAX(CASE WHEN m.meta_key='_order_shipping' THEN m.meta_value END) shipping,
|
||
MAX(CASE WHEN m.meta_key='_order_tax' THEN m.meta_value END) tax,
|
||
MAX(CASE WHEN m.meta_key='_payment_method' THEN m.meta_value END) pay_method,
|
||
MAX(CASE WHEN m.meta_key='_payment_method_title' THEN m.meta_value END) pay_title,
|
||
MAX(CASE WHEN m.meta_key='_transaction_id' THEN m.meta_value END) txn,
|
||
MAX(CASE WHEN m.meta_key='_customer_user' THEN m.meta_value END) customer,
|
||
MAX(CASE WHEN m.meta_key='_customer_note' THEN m.meta_value END) note,
|
||
MAX(CASE WHEN m.meta_key='_billing_email' THEN m.meta_value END) bill_email,
|
||
MAX(CASE WHEN m.meta_key='_date_paid' THEN m.meta_value END) date_paid
|
||
FROM wp_posts p LEFT JOIN wp_postmeta m ON m.post_id=p.ID
|
||
WHERE p.post_type='shop_order' AND p.post_status LIKE 'wc-%' GROUP BY p.ID`
|
||
);
|
||
for (const r of rows) {
|
||
const total = r.total || "0", ship = r.shipping || "0", tax = r.tax || "0";
|
||
const subtotal = (parseFloat(total) - parseFloat(ship) - parseFloat(tax)).toFixed(2);
|
||
const billing: Record<string, string> = {};
|
||
const shippingInfo: Record<string, string> = {};
|
||
const [metas] = await legacy.query<any[]>(`SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id=${r.ID} AND (meta_key LIKE '_billing_%' OR meta_key LIKE '_shipping_%')`);
|
||
for (const m of metas) {
|
||
if (m.meta_key.startsWith("_billing_")) billing[m.meta_key.slice(8)] = m.meta_value || "";
|
||
else shippingInfo[m.meta_key.slice(9)] = m.meta_value || "";
|
||
}
|
||
const paid = !!r.date_paid;
|
||
const [res]: any = await sql`INSERT INTO orders (order_number, user_id, status, currency, subtotal, shipping_total, tax_total, total,
|
||
payment_method, payment_status, transaction_id, billing, shipping, customer_note, legacy_id, created_at, updated_at)
|
||
VALUES (${r.ID}, ${r.customer ? userIdMap.get(parseInt(r.customer)) ?? null : null}, ${r.post_status.slice(3)}, 'USD',
|
||
${subtotal}, ${ship}, ${tax}, ${total}, ${r.pay_method || null}, ${paid ? "paid" : "unpaid"}, ${r.txn || null},
|
||
${billing}, ${shippingInfo}, ${r.note || ""}, ${r.ID}, ${new Date(r.post_date)}, now()) RETURNING id`;
|
||
const [items] = await legacy.query<any[]>(
|
||
`SELECT oi.order_item_id, oi.order_item_name, MAX(CASE WHEN im.meta_key='_product_id' THEN im.meta_value END) pid,
|
||
MAX(CASE WHEN im.meta_key='_qty' THEN im.meta_value END) qty,
|
||
MAX(CASE WHEN im.meta_key='_line_total' THEN im.meta_value END) line_total,
|
||
MAX(CASE WHEN im.meta_key='_line_subtotal' THEN im.meta_value END) line_sub
|
||
FROM wp_woocommerce_order_items oi LEFT JOIN wp_woocommerce_order_itemmeta im ON im.order_item_id=oi.order_item_id
|
||
WHERE oi.order_id=${r.ID} AND oi.order_item_type='line_item' GROUP BY oi.order_item_id`
|
||
);
|
||
for (const it of items) {
|
||
const qty = parseInt(it.qty) || 1;
|
||
const price = (parseFloat(it.line_sub || it.line_total) / qty).toFixed(2);
|
||
await sql`INSERT INTO order_items (order_id, product_id, name, qty, price, total)
|
||
VALUES (${res.id}, ${it.pid ? (await sql`SELECT id FROM products WHERE legacy_id=${parseInt(it.pid)}`.values())[0]?.[0] ?? null : null},
|
||
${it.order_item_name}, ${qty}, ${price}, ${it.line_total || "0"})`;
|
||
if (paid && r.txn) await sql`INSERT INTO payments (order_id, method, status, transaction_id, amount) VALUES (${res.id}, ${r.pay_method || "unknown"}, 'succeeded', ${r.txn + "-" + it.order_item_id}, ${it.line_total || "0"}) ON CONFLICT (transaction_id) DO NOTHING`;
|
||
}
|
||
}
|
||
log(`订单 ${rows.length} 个`);
|
||
}
|
||
|
||
/* ---------- 9. 询盘（Flamingo + Elementor 提交） ---------- */
|
||
if (should("flamingo")) { log("导入询盘（Flamingo）...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_status, p.post_date, p.post_title,
|
||
MAX(CASE WHEN m.meta_key='_from' THEN m.meta_value END) from_all,
|
||
MAX(CASE WHEN m.meta_key='_from_name' THEN m.meta_value END) from_name,
|
||
MAX(CASE WHEN m.meta_key='_from_email' THEN m.meta_value END) from_email,
|
||
MAX(CASE WHEN m.meta_key='_meta' THEN m.meta_value END) meta,
|
||
GROUP_CONCAT(CASE WHEN m.meta_key LIKE '\\\\_field_%' THEN CONCAT(m.meta_key,'=',REPLACE(m.meta_value,'|','\\\\|')) END SEPARATOR '|') fields_raw
|
||
FROM wp_posts p LEFT JOIN wp_postmeta m ON m.post_id=p.ID
|
||
WHERE p.post_type='flamingo_inbound' AND p.post_status IN ('publish','flamingo-spam') GROUP BY p.ID`
|
||
);
|
||
const NAME_KEYS = ["your-name", "name", "contact-person", "contact"];
|
||
const EMAIL_KEYS = ["your-email", "email", "business-email"];
|
||
const PHONE_KEYS = ["phone", "phone-number", "tel", "telephone"];
|
||
const COMPANY_KEYS = ["company", "company-name", "your-company"];
|
||
const MSG_KEYS = ["your-message", "message", "comments", "requirement", "inquiry"];
|
||
const PRODUCT_KEYS = ["product", "products", "interest-product", "customer-product"];
|
||
const COUNTRY_KEYS = ["country", "country-region", "region", "location"];
|
||
const pick = (fields: Record<string, string>, keys: string[]) => { for (const k of keys) if (fields[k]) return fields[k]; return ""; };
|
||
for (const r of rows) {
|
||
const fields: Record<string, string> = {};
|
||
const utm: Record<string, string> = {};
|
||
for (const pair of (r.fields_raw || "").split("|")) {
|
||
const idx = pair.indexOf("=");
|
||
if (idx <= 1) continue;
|
||
let key = pair.slice(0, idx).replace(/^_field_/, "");
|
||
const val = pair.slice(idx + 1);
|
||
if (key.startsWith("utm_")) { utm[key] = val; continue; }
|
||
if (key === "gclid" || key.startsWith("acceptance")) { utm[key] = val; continue; }
|
||
fields[key] = val;
|
||
}
|
||
await sql`INSERT INTO inquiries (form_name, name, email, phone, company, country, message, product_interest, fields, status, channel, utm, legacy_id, created_at)
|
||
VALUES (${'Contact Form 7'}, ${cut(pick(fields, NAME_KEYS) || r.from_name, 255)}, ${cut(pick(fields, EMAIL_KEYS) || r.from_email, 255)},
|
||
${cut(pick(fields, PHONE_KEYS), 128)}, ${cut(pick(fields, COMPANY_KEYS), 255)}, ${cut(pick(fields, COUNTRY_KEYS), 128)}, ${pick(fields, MSG_KEYS)},
|
||
${cut(pick(fields, PRODUCT_KEYS), 255)}, ${fields}, ${r.post_status === "flamingo-spam" ? "spam" : "new"},
|
||
${r.post_title || ""}, ${utm}, ${r.ID}, ${new Date(r.post_date)})`;
|
||
}
|
||
log(`Flamingo 询盘 ${rows.length} 条`);
|
||
}
|
||
if (should("elementor")) { log("导入询盘（Elementor 表单）...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT s.id, s.form_name, s.referer, s.user_ip, s.user_agent, s.created_at_gmt, s.status,
|
||
GROUP_CONCAT(CONCAT(REPLACE(v.key,'|','\\\\|'),'=',REPLACE(v.value,'|','\\\\|')) SEPARATOR '|') vals
|
||
FROM wp_e_submissions s LEFT JOIN wp_e_submissions_values v ON v.submission_id=s.id
|
||
WHERE s.status='new' GROUP BY s.id`
|
||
);
|
||
for (const r of rows) {
|
||
const fields: Record<string, string> = {};
|
||
for (const pair of (r.vals || "").split("|")) {
|
||
const idx = pair.indexOf("=");
|
||
if (idx > 0) fields[pair.slice(0, idx)] = pair.slice(idx + 1);
|
||
}
|
||
await sql`INSERT INTO inquiries (form_name, name, email, phone, company, country, message, fields, status, source_page, ip, utm, legacy_id, created_at)
|
||
VALUES (${'Elementor: ' + r.form_name}, ${cut(fields.name || fields["your-name"] || fields["form_field_name"], 255)}, ${cut(fields.email || fields["email"] || fields["form_field_email"], 255)},
|
||
${cut(fields.phone || fields["tel"], 128)}, ${cut(fields.company, 255)}, ${cut(fields.country, 128)}, ${fields.message || fields["your-message"] || ""},
|
||
${fields}, 'new', ${cut(r.referer, 2000)}, ${cut(r.user_ip, 64)}, ${{ user_agent: r.user_agent || "" }}, ${r.id}, ${new Date(r.created_at_gmt)})`;
|
||
}
|
||
log(`Elementor 询盘 ${rows.length} 条`);
|
||
}
|
||
|
||
/* ---------- 10. CF7 表单定义 ---------- */
|
||
if (should("forms")) { log("导入询盘表单定义...");
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_title, p.post_name, f.form FROM wp_posts p
|
||
LEFT JOIN (SELECT post_id, meta_value form FROM wp_postmeta WHERE meta_key='_form') f ON f.post_id=p.ID
|
||
WHERE p.post_type='wpcf7_contact_form'`
|
||
);
|
||
for (const r of rows) {
|
||
const names = [...new Set([...((r.form || "").matchAll(/name="([^"]+)"/g))].map((m: RegExpMatchArray) => m[1]))];
|
||
await sql`INSERT INTO inquiry_forms (name, form_key, fields, legacy_id) VALUES (${r.post_title}, ${r.post_name}, ${names}, ${r.ID})`;
|
||
}
|
||
log(`表单定义 ${rows.length} 个`);
|
||
}
|
||
|
||
/* ---------- 11. 菜单 ---------- */
|
||
if (should("menus")) { log("导入菜单...");
|
||
const locations: Record<string, number> = { "menu-1": 699, "menu-2": 807, max_mega_menu_1: 840, max_mega_menu_2: 847 };
|
||
const itemMap = new Map<number, number>(); // 旧 menu_item id → 新 id
|
||
// slug → 新 URL 前缀 解析
|
||
const pageUrl = new Map<number, string>(); // legacy post id → path
|
||
for (const r of await sql`SELECT id, legacy_id, type, slug FROM contents WHERE legacy_id IS NOT NULL`.values()) {
|
||
const [legacyId, type, slug] = [r[1] as number, r[2] as string, r[3] as string];
|
||
pageUrl.set(legacyId, type === "page" ? `/${slug}/` : `/${slug}/`);
|
||
}
|
||
for (const r of await sql`SELECT id, legacy_id, slug FROM products WHERE legacy_id IS NOT NULL`.values()) pageUrl.set(r[1] as number, `/product/${r[2]}/`);
|
||
for (const [loc, termId] of Object.entries(locations)) {
|
||
const [nameRows] = await legacy.query<any[]>(`SELECT name FROM wp_terms WHERE term_id=${termId}`);
|
||
const [res]: any = await sql`INSERT INTO menus (name, location, legacy_id) VALUES (${nameRows[0]?.name || loc}, ${loc}, ${termId}) RETURNING id`;
|
||
const [items] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.menu_order, m.parent, m.object, m.object_id, m.url, m.target, p.post_title
|
||
FROM wp_posts p
|
||
LEFT JOIN (SELECT post_id, MAX(CASE WHEN meta_key='_menu_item_menu_item_parent' THEN meta_value END) parent,
|
||
MAX(CASE WHEN meta_key='_menu_item_object' THEN meta_value END) object,
|
||
MAX(CASE WHEN meta_key='_menu_item_object_id' THEN meta_value END) object_id,
|
||
MAX(CASE WHEN meta_key='_menu_item_url' THEN meta_value END) url,
|
||
MAX(CASE WHEN meta_key='_menu_item_target' THEN meta_value END) target
|
||
FROM wp_postmeta WHERE meta_key LIKE '_menu_item%' GROUP BY post_id) m ON m.post_id=p.ID
|
||
WHERE p.post_type='nav_menu_item' AND p.post_status='publish'
|
||
AND p.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy='nav_menu' AND term_id=${termId}))
|
||
ORDER BY p.menu_order`
|
||
);
|
||
for (const it of items) {
|
||
let url = it.url || "";
|
||
if (it.object === "custom") { /* 保留原 URL */ }
|
||
else if (it.object === "page" || it.object === "post" || it.object === "product") url = pageUrl.get(parseInt(it.object_id)) || url || "#";
|
||
const parent = parseInt(it.parent) ? itemMap.get(parseInt(it.parent)) ?? null : null;
|
||
const [ir]: any = await sql`INSERT INTO menu_items (menu_id, parent_id, title, url, target, sort, legacy_id)
|
||
VALUES (${res.id}, ${parent}, ${it.post_title || it.url || "item"}, ${url || "#"}, ${it.target || "_self"}, ${it.menu_order}, ${it.ID}) RETURNING id`;
|
||
itemMap.set(it.ID, ir.id);
|
||
}
|
||
}
|
||
log(`菜单 4 个，菜单项 ${itemMap.size} 条`);
|
||
}
|
||
|
||
/* ---------- 12. 筛选配置 ---------- */
|
||
if (should("filters")) { log("导入筛选配置..."); await loadMaps();
|
||
const [rows] = await legacy.query<any[]>(
|
||
`SELECT p.ID, p.post_title, p.post_name, p.post_content c FROM wp_posts p WHERE p.post_type='filter-field' AND p.post_status='publish'`
|
||
);
|
||
let pos = 0;
|
||
for (const r of rows) {
|
||
if (/^TEST/i.test(r.post_title)) continue; // 跳过测试筛选
|
||
const cfg = r.c && r.c.trim().startsWith("a:") ? (phpUnserialize(r.c) as Record<string, string>) : {};
|
||
const eName = String(cfg.e_name || "");
|
||
let type = "attribute", attributeId = null as number | null, taxonomy = null as string | null;
|
||
if (eName.startsWith("pa_")) { attributeId = attrMap.get(eName.slice(3)) ?? null; }
|
||
else if (eName === "product_cat" || eName === "category") { type = "category"; taxonomy = eName; }
|
||
else { taxonomy = eName || null; }
|
||
pos++;
|
||
await sql`INSERT INTO filters (name, slug, type, attribute_id, taxonomy, position, enabled, legacy_id)
|
||
VALUES (${r.post_title}, ${r.post_name}, ${type}, ${attributeId}, ${taxonomy}, ${pos}, true, ${r.ID})`;
|
||
}
|
||
log(`筛选 ${pos} 个`);
|
||
}
|
||
|
||
/* ---------- 13. 重定向 ---------- */
|
||
if (should("redirects")) { log("导入重定向...");
|
||
const [rows] = await legacy.query<any[]>(`SELECT id, url, action_data, action_code, regex FROM wp_redirection_items`);
|
||
for (const r of rows) {
|
||
await sql`INSERT INTO redirects (source, target, code, is_regex, hits, enabled, legacy_id)
|
||
VALUES (${r.url}, ${r.action_data || "/"}, ${r.action_code || 301}, ${!!r.regex}, 0, true, ${r.id})`;
|
||
}
|
||
log(`重定向 ${rows.length} 条`);
|
||
}
|
||
|
||
/* ---------- 14. 语言 ---------- */
|
||
if (should("languages")) { log("导入语言配置...");
|
||
const LANGS: [string, string][] = [["en", "English"], ["af", "Afrikaans"], ["sq", "Shqip"], ["am", "አማርኛ"], ["ar", "العربية"], ["hy", "Հայերեն"], ["az", "Azərbaycan dili"], ["eu", "Euskara"], ["be", "Беларуская"], ["bn", "বাংলা"], ["bs", "Bosanski"], ["bg", "Български"], ["ca", "Català"], ["ceb", "Cebuano"], ["ny", "Chichewa"], ["zh-CN", "简体中文"], ["zh-TW", "繁體中文"], ["co", "Corsu"], ["hr", "Hrvatski"], ["cs", "Čeština"], ["da", "Dansk"], ["nl", "Nederlands"], ["eo", "Esperanto"], ["et", "Eesti"], ["tl", "Filipino"], ["fi", "Suomi"], ["fr", "Français"], ["fy", "Frysk"], ["gl", "Galego"], ["ka", "ქართული"], ["de", "Deutsch"], ["el", "Ελληνικά"], ["gu", "ગુજરાતી"], ["ht", "Kreyol ayisyen"], ["ha", "Hausa"], ["haw", "ʻŌlelo Hawaiʻi"], ["iw", "עברית"], ["hi", "हिन्दी"], ["hmn", "Hmoob"], ["hu", "Magyar"], ["is", "Íslenska"], ["ig", "Igbo"], ["id", "Bahasa Indonesia"], ["ga", "Gaeilge"], ["it", "Italiano"], ["ja", "日本語"], ["jw", "Basa Jawa"], ["kn", "ಕನ್ನಡ"], ["kk", "Қазақ тілі"], ["km", "ខ្មែរ"], ["ko", "한국어"], ["ku", "Kurdî"], ["ky", "Кыргызча"], ["lo", "ລາວ"], ["la", "Latina"], ["lv", "Latviešu"], ["lt", "Lietuvių"], ["lb", "Lëtzebuergesch"], ["mk", "Македонски"], ["mg", "Malagasy"], ["ms", "Bahasa Melayu"], ["ml", "മലയാളം"], ["mt", "Malti"], ["mi", "Māori"], ["mr", "मराठी"], ["mn", "Монгол"], ["my", "မြန်မာ"], ["ne", "नेपाली"], ["no", "Norsk"], ["ps", "پښتو"], ["fa", "فارسی"], ["pl", "Polski"], ["pt", "Português"], ["pa", "ਪੰਜਾਬੀ"], ["ro", "Română"], ["ru", "Русский"], ["sm", "Samoa"], ["gd", "Gàidhlig"], ["sr", "Српски"], ["st", "Sesotho"], ["sn", "Shona"], ["sd", "سنڌي"], ["si", "සිංහල"], ["sk", "Slovenčina"], ["sl", "Slovenščina"], ["so", "Soomaali"], ["es", "Español"], ["su", "Basa Sunda"], ["sw", "Kiswahili"], ["sv", "Svenska"], ["tg", "Тоҷикӣ"], ["ta", "தமிழ்"], ["te", "తెలుగు"], ["th", "ไทย"], ["tr", "Türkçe"], ["uk", "Українська"], ["ur", "اردو"], ["uz", "Oʻzbekcha"], ["vi", "Tiếng Việt"], ["cy", "Cymraeg"], ["xh", "isiXhosa"], ["yi", "ייִדיש"], ["yo", "Yorùbá"], ["zu", "isiZulu"]];
|
||
for (const [code, name] of LANGS) {
|
||
await sql`INSERT INTO languages (code, name, is_default, enabled, machine, sort) VALUES (${code}, ${name}, ${code === "en"}, ${code === "en"}, ${code !== "en"}, 0)`;
|
||
}
|
||
log(`语言 ${LANGS.length} 个（en 默认启用，其余为 GTranslate 机翻待升级状态）`);
|
||
}
|
||
|
||
/* ---------- 15. 设置 + ID 映射 ---------- */
|
||
if (should("final")) { log("写入设置与 ID 映射...");
|
||
await sql`INSERT INTO settings (key, value) VALUES
|
||
('site', ${{ name: "Dusun IoT", url: "https://dusuniot.com", currency: "USD", timezone: "Asia/Shanghai" }}),
|
||
('woocommerce', ${{ currency: "USD", guestCheckout: false, signupFromCheckout: true, pricesIncludeTax: false, allowedCountries: "all_except", categoryBase: "collection", productBase: "product", tagBase: "product-tag" }}),
|
||
('mail', ${{ smtp: "wp-mail-smtp 迁移，需配置 SMTP 凭据" }}),
|
||
('seo', ${{ sitemap: true, robots: "all" }})`;
|
||
|
||
for (const [entity, table] of [["post", "contents"], ["product", "products"], ["media", "media"], ["user", "users"], ["order", "orders"], ["category", "categories"]] as const) {
|
||
await sql`INSERT INTO legacy_id_mappings (entity, legacy_id, new_id) SELECT ${entity}, legacy_id, id FROM ${sql(table)} WHERE legacy_id IS NOT NULL ON CONFLICT DO NOTHING`;
|
||
}
|
||
|
||
}
|
||
|
||
/* ---------- 汇总校验 ---------- */
|
||
const counts: Record<string, number> = {};
|
||
for (const t of ["contents", "content_translations", "categories", "products", "product_attribute_values", "product_attribute_map", "users", "orders", "order_items", "payments", "inquiries", "inquiry_forms", "media", "redirects", "filters", "menus", "menu_items", "languages", "settings", "legacy_id_mappings"]) {
|
||
const r = (await sql`SELECT COUNT(*) FROM ${sql(t)}`.values())[0];
|
||
counts[t] = Number(r[0]);
|
||
}
|
||
console.table(counts);
|
||
await legacy.end();
|
||
sql.end();
|
||
log("导入完成");
|