nocodb sync

gistfile1.txt

import axios from "axios";
import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import dotenv from "dotenv";
import pLimit from "p-limit";

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

/** ========= CONFIG ========= */
const SRC = {
  host: process.env.SRC_HOST,
  token: process.env.SRC_TOKEN,
  projectSlug: process.env.SRC_PROJECT_SLUG, // pode ser SLUG ou ID
};
const DST = {
  host: process.env.DST_HOST,
  token: process.env.DST_TOKEN,
  projectSlug: process.env.DST_PROJECT_SLUG, // pode ser SLUG ou ID
};
const PAGE_LIMIT = parseInt(process.env.PAGE_LIMIT ?? "1000", 10);
const BULK_CHUNK = parseInt(process.env.BULK_CHUNK ?? "1000", 10);
const CREATE_SCHEMA = String(process.env.CREATE_SCHEMA ?? "true") === "true";
const CONCURRENCY = parseInt(process.env.CONCURRENCY ?? "6", 10);

/** ========= HELPERS ========= */
const hdr = (token) => ({
  headers: { "xc-token": token, "Content-Type": "application/json" },
  timeout: 60_000,
});

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

async function withRetry(fn, { retries = 4, baseDelay = 500 } = {}) {
  let attempt = 0;
  for (;;) {
    try {
      return await fn();
    } catch (err) {
      attempt++;
      if (attempt > retries) throw err;
      const wait = baseDelay * Math.pow(2, attempt - 1);
      console.warn(`Retry ${attempt}/${retries} in ${wait}ms: ${err?.message || err}`);
      await sleep(wait);
    }
  }
}

/** ========= META (SCHEMA) – leitura origem =========
 * NOTA: estes endpoints podem variar por versão. Ajuste se necessário.
 */
async function getProjectBySlugOrId(host, token, slugOrId) {
  // Tenta buscar por slug, senão assume que já é ID
  try {
    const { data } = await withRetry(() =>
      axios.get(`${host}/api/v2/meta/projects?limit=2000`, hdr(token))
    );
    const list = data?.list || data?.projects || [];
    const found = list.find((p) => p.title === slugOrId || p.slug === slugOrId || p.id === slugOrId);
    if (found) return found;
  } catch (_) {}
  // fallback: retorno mínimo com id=slugOrId
  return { id: slugOrId, slug: slugOrId, title: slugOrId };
}

async function listTables(host, token, projectId) {
  const out = [];
  let offset = 0;
  const limit = 200;
  for (;;) {
    const url = `${host}/api/v2/meta/tables?projectId=${encodeURIComponent(
      projectId
    )}&limit=${limit}&offset=${offset}`;
    const { data } = await withRetry(() => axios.get(url, hdr(token)));
    const chunk = data?.list || data?.tables || [];
    out.push(...chunk);
    const tot = data?.pageInfo?.total ?? chunk.length;
    if (offset + limit >= tot || chunk.length === 0) break;
    offset += limit;
  }
  return out;
}

async function listColumns(host, token, tableId) {
  const url = `${host}/api/v2/meta/tables/${tableId}/columns?limit=5000`;
  const { data } = await withRetry(() => axios.get(url, hdr(token)));
  return data?.list || data?.columns || [];
}

/** ========= META (SCHEMA) – criação destino =========
 * Dependente da versão/perm da Meta API. Ajuste os payloads conforme sua instância.
 */
async function createTable(host, token, projectId, tableDef) {
  // tableDef: { title, table_name, ... }
  const payload = {
    title: tableDef.title || tableDef.table_name,
    table_name: tableDef.table_name || tableDef.title,
    project_id: projectId,
  };
  const url = `${host}/api/v2/meta/tables`;
  const { data } = await withRetry(() => axios.post(url, payload, hdr(token)));
  return data;
}

function mapColumnType(col) {
  // Mapeamento simples → ajuste para seus casos
  const t = (col?.uidt || col?.type || "").toLowerCase();
  if (t.includes("text") || t === "longtext" || t === "richtext") return "text";
  if (t.includes("number") || t === "numeric") return "number";
  if (t.includes("bool")) return "boolean";
  if (t.includes("date") || t.includes("time")) return "date";
  if (t.includes("select")) return "select";
  if (t.includes("lookup")) return "lookup";
  if (t.includes("formula")) return "formula";
  if (t.includes("link")) return "link";
  if (t.includes("json")) return "json";
  if (t.includes("attachment") || t.includes("file")) return "attachment";
  return "text";
}

function buildColumnPayload(col, destTableId, tableNameToId) {
  const type = mapColumnType(col);
  const base = {
    title: col.title || col.column_name,
    column_name: col.column_name || col.title,
    uidt: type,
    table_id: destTableId,
    // required, default, precision, options, etc — ajuste conforme sua necessidade
  };

  if (type === "select" && Array.isArray(col?.options)) {
    base.options = col.options;
  }

  if (type === "link") {
    // link columns precisam saber a tabela alvo
    // Muitas instâncias usam col.relation?/fk_table_id?/fk_table_name?
    const targetTableName =
      col?.fk_table_name || col?.ref_table_name || col?.meta?.refTableName;
    const targetId = tableNameToId.get(targetTableName);
    if (!targetId) {
      console.warn(
        `Atenção: coluna link "${base.column_name}" aponta para tabela desconhecida: ${targetTableName}`
      );
    } else {
      base.ref_table_id = targetId;
    }
  }

  return base;
}

async function createColumn(host, token, colPayload) {
  const url = `${host}/api/v2/meta/columns`;
  const { data } = await withRetry(() => axios.post(url, colPayload, hdr(token)));
  return data;
}

/** ========= DATA API ========= */
async function listAllRecords(host, token, tableId, pageLimit = 1000) {
  const out = [];
  let offset = 0;
  for (;;) {
    const url = `${host}/api/v2/tables/${tableId}/records?limit=${pageLimit}&offset=${offset}`;
    const { data } = await withRetry(() => axios.get(url, hdr(token)));
    const rows = data?.list || data?.records || [];
    out.push(...rows);
    const tot = data?.pageInfo?.total ?? rows.length;
    if (offset + pageLimit >= tot || rows.length === 0) break;
    offset += pageLimit;
  }
  return out;
}

async function bulkInsert(host, token, tableId, records, chunkSize = 1000) {
  for (let i = 0; i < records.length; i += chunkSize) {
    const slice = records.slice(i, i + chunkSize);
    const url = `${host}/api/v2/tables/${tableId}/records/bulk`;
    await withRetry(() => axios.post(url, { records: slice }, hdr(token)));
  }
}

async function updateRecord(host, token, tableId, recId, payload) {
  const url = `${host}/api/v2/tables/${tableId}/records/${recId}`;
  await withRetry(() => axios.patch(url, payload, hdr(token)));
}

/** ========= PIPELINE PRINCIPAL ========= */
(async () => {
  // 1) identificar projetos origem/destino
  const srcProject = await getProjectBySlugOrId(SRC.host, SRC.token, SRC.projectSlug);
  const dstProject = await getProjectBySlugOrId(DST.host, DST.token, DST.projectSlug);
  console.log("SRC project:", srcProject.id, srcProject.slug || srcProject.title);
  console.log("DST project:", dstProject.id, dstProject.slug || dstProject.title);

  // 2) ler schema da origem
  const srcTables = await listTables(SRC.host, SRC.token, srcProject.id);

  // montar dicionário tabela → colunas
  const schema = [];
  for (const t of srcTables) {
    const cols = await listColumns(SRC.host, SRC.token, t.id);
    schema.push({
      table: { id: t.id, title: t.title, table_name: t.table_name || t.title },
      columns: cols.map((c) => ({
        id: c.id,
        title: c.title,
        column_name: c.column_name || c.title,
        uidt: c.uidt || c.type, // tipo
        options: c.options ?? undefined,
        meta: c.meta ?? undefined,
        fk_table_name:
          c.fk_table_name || c.ref_table_name || c.meta?.refTableName || undefined, // para links
      })),
    });
  }

  // salvar manifesto do schema
  const manifestPath = path.join(__dirname, "manifest.schema.json");
  fs.writeFileSync(manifestPath, JSON.stringify({ srcProject, dstProject, schema }, null, 2));
  console.log(`Schema manifesto salvo em: ${manifestPath}`);

  // 3) criar schema no destino (tabelas + colunas), se habilitado
  // mantermos um mapa: srcTableName -> { srcId, dstId, columnMaps }
  const tableNameToId_DST = new Map();
  const tableMaps = new Map(); // key = srcTableName, value = { srcId, dstId, colMap: Map<srcColName, dstColName> }

  if (CREATE_SCHEMA) {
    console.log("Criando schema no destino…");
    // criar tabelas
    for (const entry of schema) {
      const { table } = entry;
      const tname = table.table_name || table.title;
      const created = await createTable(DST.host, DST.token, dstProject.id, {
        title: table.title,
        table_name: tname,
      });
      const dstTableId = created?.id || created?.table?.id || created?.table_id || created?.uuid;
      if (!dstTableId) {
        throw new Error(`Não foi possível obter ID da tabela destino para "${tname}".`);
      }
      tableNameToId_DST.set(tname, dstTableId);
      tableMaps.set(tname, { srcId: table.id, dstId: dstTableId, colMap: new Map() });
      console.log(`- Tabela criada: ${tname} → ${dstTableId}`);
    }

    // criar colunas
    // importante: criar primeiro as colunas não-link; depois voltamos nas link
    const linkColsQueue = [];

    for (const entry of schema) {
      const { table, columns } = entry;
      const tname = table.table_name || table.title;
      const dstTableId = tableNameToId_DST.get(tname);

      for (const col of columns) {
        const type = mapColumnType(col);
        if (type === "link") {
          linkColsQueue.push({ tname, dstTableId, col });
          continue;
        }
        const payload = buildColumnPayload(col, dstTableId, tableNameToId_DST);
        const created = await createColumn(DST.host, DST.token, payload);
        const dstColName =
          created?.column_name || created?.title || payload.column_name || payload.title;
        tableMaps.get(tname).colMap.set(col.column_name || col.title, dstColName);
        console.log(`  - Coluna criada: ${tname}.${dstColName} [${type}]`);
      }
    }

    // agora criar colunas de link (já temos tabela alvo mapeada)
    for (const { tname, dstTableId, col } of linkColsQueue) {
      const payload = buildColumnPayload(col, dstTableId, tableNameToId_DST);
      const created = await createColumn(DST.host, DST.token, payload);
      const dstColName =
        created?.column_name || created?.title || payload.column_name || payload.title;
      tableMaps.get(tname).colMap.set(col.column_name || col.title, dstColName);
      console.log(`  - Coluna LINK criada: ${tname}.${dstColName}`);
    }
  } else {
    // Descobrir IDs das tabelas já existentes no destino por nome
    console.log("CREATE_SCHEMA=false → assumindo que o schema já existe no destino.");
    const dstTables = await listTables(DST.host, DST.token, dstProject.id);
    for (const entry of schema) {
      const tname = entry.table.table_name || entry.table.title;
      const dstT = dstTables.find(
        (x) => (x.table_name || x.title) === tname || x.title === tname
      );
      if (!dstT) throw new Error(`Tabela "${tname}" não encontrada no destino.`);
      tableNameToId_DST.set(tname, dstT.id);
      const dstCols = await listColumns(DST.host, DST.token, dstT.id);
      const colMap = new Map();
      for (const c of entry.columns) {
        // mapeamento simples por nome
        const match = dstCols.find(
          (dc) =>
            (dc.column_name || dc.title) === (c.column_name || c.title) ||
            dc.title === (c.column_name || c.title)
        );
        if (match) colMap.set(c.column_name || c.title, match.column_name || match.title);
      }
      tableMaps.set(tname, { srcId: entry.table.id, dstId: dstT.id, colMap });
    }
  }

  // 4) migrar dados “simples” (sem preencher colunas Link ainda)
  console.log("Migrando dados (primeira passada) …");
  // guardaremos IDs origem→destino por tabela
  const idMaps = new Map(); // tableName -> Map<sourceId, destId>

  for (const entry of schema) {
    const tname = entry.table.table_name || entry.table.title;
    const srcTableId = entry.table.id;
    const dstTableId = tableNameToId_DST.get(tname);
    const cols = entry.columns;

    // listar registros origem
    const rows = await listAllRecords(SRC.host, SRC.token, srcTableId, PAGE_LIMIT);
    console.log(`- ${tname}: ${rows.length} registros encontrados.`);

    if (rows.length === 0) {
      idMaps.set(tname, new Map());
      continue;
    }

    // construir payload sem colunas LINK / LOOKUP / FORMULA (essas recalculam depois)
    const linkColNames = new Set(
      cols
        .filter((c) => {
          const tt = mapColumnType(c);
          return tt === "link" || tt === "lookup" || tt === "formula";
        })
        .map((c) => c.column_name || c.title)
    );

    const colMap = tableMaps.get(tname).colMap;

    const simpleRecords = [];
    const srcIdToPayloadIndex = new Map();

    rows.forEach((r, idx) => {
      const payload = {};
      Object.entries(r).forEach(([k, v]) => {
        if (k === "id" || k === "_rowId" || k === "_id") return; // ID da origem
        if (linkColNames.has(k)) return; // pula link/lookup/formula
        const dstCol = colMap.get(k) || k;
        // attachments como URL (se string/array de string)
        payload[dstCol] = v;
      });
      simpleRecords.push(payload);
      // guardamos o índice para correlacionar depois
      const srcId = r.id ?? r._rowId ?? r._id ?? String(idx);
      srcIdToPayloadIndex.set(srcId, simpleRecords.length - 1);
    });

    // enviar em bulk
    await bulkInsert(DST.host, DST.token, dstTableId, simpleRecords, BULK_CHUNK);

    // Precisamos descobrir os IDs gerados no destino para mapear origem→destino.
    // Estratégia: reler registros destino ordenados por created/seq e assumir mesma ordem.
    // Caso sua instância permita, troque para “bulk insert com retorno”
    const dstRows = await listAllRecords(DST.host, DST.token, dstTableId, PAGE_LIMIT);
    if (dstRows.length < simpleRecords.length) {
      console.warn(
        `  Aviso: menos linhas no destino do que inseridas (esperado=${simpleRecords.length}, atual=${dstRows.length}).`
      );
    }
    // Mapeamento por heurística (melhore conforme seu caso, ex: por chave única)
    const map = new Map();
    const minLen = Math.min(rows.length, dstRows.length);
    for (let i = 0; i < minLen; i++) {
      const s = rows[i];
      const d = dstRows[i];
      const sId = s.id ?? s._rowId ?? s._id ?? String(i);
      const dId = d.id ?? d._rowId ?? d._id ?? String(i);
      map.set(String(sId), String(dId));
    }
    idMaps.set(tname, map);

    console.log(`  - Inseridos ${simpleRecords.length}. Mapeados ${map.size} IDs.`);
  }

  // 5) segunda passada: popular colunas de LINK usando os mapas de IDs
  console.log("Preenchendo relações (segunda passada) …");
  const limit = pLimit(CONCURRENCY);

  for (const entry of schema) {
    const tname = entry.table.table_name || entry.table.title;
    const srcTableId = entry.table.id;
    const dstTableId = tableNameToId_DST.get(tname);
    const cols = entry.columns.filter((c) => mapColumnType(c) === "link");
    if (cols.length === 0) continue;

    const rowsSrc = await listAllRecords(SRC.host, SRC.token, srcTableId, PAGE_LIMIT);
    const rowsDst = await listAllRecords(DST.host, DST.token, dstTableId, PAGE_LIMIT);
    const srcToDst = idMaps.get(tname);

    // índice destino por posição (mesma heurística)
    const indexByPos = new Map();
    const minLen = Math.min(rowsSrc.length, rowsDst.length);
    for (let i = 0; i < minLen; i++) {
      const sId = rowsSrc[i].id ?? rowsSrc[i]._rowId ?? rowsSrc[i]._id ?? String(i);
      const dId = rowsDst[i].id ?? rowsDst[i]._rowId ?? rowsDst[i]._id ?? String(i);
      indexByPos.set(String(sId), String(dId));
    }

    // Para cada linha origem, montar payload com colunas link mapeadas e dar PATCH
    const tasks = [];
    for (let i = 0; i < rowsSrc.length; i++) {
      const s = rowsSrc[i];
      const sId = String(s.id ?? s._rowId ?? s._id ?? String(i));
      const dId = indexByPos.get(sId) || srcToDst.get(sId);
      if (!dId) continue;

      const payload = {};
      for (const col of cols) {
        const srcVal = s[col.column_name || col.title];
        if (srcVal == null) continue;

        const targetTableName =
          col.fk_table_name || col.ref_table_name || col.meta?.refTableName;
        if (!targetTableName) continue;

        const targetMap = idMaps.get(targetTableName);
        if (!targetMap) {
          console.warn(
            `  (skip) relação ${tname}.${col.column_name} → ${targetTableName} sem mapa de IDs`
          );
          continue;
        }

        // link pode ser simples (um id) ou múltiplo (array de ids)
        if (Array.isArray(srcVal)) {
          const mapped = srcVal
            .map((orig) => targetMap.get(String(orig)) || null)
            .filter(Boolean);
          payload[col.column_name || col.title] = mapped;
        } else {
          const mapped = targetMap.get(String(srcVal));
          if (mapped) payload[col.column_name || col.title] = mapped;
        }
      }

      if (Object.keys(payload).length > 0) {
        tasks.push(
          limit(() => updateRecord(DST.host, DST.token, dstTableId, dId, payload))
        );
      }
    }

    await Promise.all(tasks);
    console.log(`  - ${tname}: atualizadas ${tasks.length} linhas com relações.`);
  }

  console.log("✅ Concluído. Schema (se habilitado) + dados + relações migrados.");
})().catch((err) => {
  console.error("Falhou:", err?.response?.data || err);
  process.exit(1);
});