99 lines
3.5 KiB
JavaScript
99 lines
3.5 KiB
JavaScript
/**
|
|
* Export des données de la base Sandbox SONUM vers un script SQL d'injection.
|
|
* Tables exportées : blocs_fonctionnels, editeurs, etablissements, solutions,
|
|
* logiciels_etablissements, consultations, demandes_contact, user_etablissements
|
|
* Tables exclues : users, local_credentials, __drizzle_migrations
|
|
*/
|
|
import mysql from 'mysql2/promise';
|
|
import fs from 'fs';
|
|
|
|
const TABLES_TO_EXPORT = [
|
|
'blocs_fonctionnels',
|
|
'editeurs',
|
|
'etablissements',
|
|
'solutions',
|
|
'logiciels_etablissements',
|
|
'consultations',
|
|
'demandes_contact',
|
|
'user_etablissements',
|
|
];
|
|
|
|
const conn = await mysql.createConnection(process.env.DATABASE_URL);
|
|
|
|
let sql = `-- Export données SONUM Sandbox → VPS
|
|
-- Généré le ${new Date().toISOString()}
|
|
-- Tables : ${TABLES_TO_EXPORT.join(', ')}
|
|
-- ATTENTION : exclut users, local_credentials, __drizzle_migrations
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
|
|
|
|
`;
|
|
|
|
for (const table of TABLES_TO_EXPORT) {
|
|
console.log(`Exporting table: ${table}...`);
|
|
|
|
// Récupérer la structure de la table
|
|
const [[createResult]] = await conn.execute(`SHOW CREATE TABLE \`${table}\``);
|
|
const createSQL = createResult['Create Table'];
|
|
|
|
// Récupérer les données
|
|
const [rows] = await conn.execute(`SELECT * FROM \`${table}\``);
|
|
|
|
sql += `-- ─────────────────────────────────────────────────────────────────\n`;
|
|
sql += `-- Table: ${table} (${rows.length} lignes)\n`;
|
|
sql += `-- ─────────────────────────────────────────────────────────────────\n`;
|
|
sql += `TRUNCATE TABLE \`${table}\`;\n\n`;
|
|
|
|
if (rows.length === 0) {
|
|
sql += `-- (aucune donnée)\n\n`;
|
|
continue;
|
|
}
|
|
|
|
// Générer les INSERT par lots de 50
|
|
const columns = Object.keys(rows[0]);
|
|
const colList = columns.map(c => `\`${c}\``).join(', ');
|
|
|
|
const BATCH_SIZE = 50;
|
|
for (let i = 0; i < rows.length; i += BATCH_SIZE) {
|
|
const batch = rows.slice(i, i + BATCH_SIZE);
|
|
const values = batch.map(row => {
|
|
const vals = columns.map(col => {
|
|
const v = row[col];
|
|
if (v === null || v === undefined) return 'NULL';
|
|
if (typeof v === 'number' || typeof v === 'bigint') return String(v);
|
|
if (typeof v === 'boolean') return v ? '1' : '0';
|
|
if (v instanceof Date) return `'${v.toISOString().slice(0, 19).replace('T', ' ')}'`;
|
|
// Escape string
|
|
const escaped = String(v)
|
|
.replace(/\\/g, '\\\\')
|
|
.replace(/'/g, "\\'")
|
|
.replace(/\n/g, '\\n')
|
|
.replace(/\r/g, '\\r')
|
|
.replace(/\0/g, '\\0');
|
|
return `'${escaped}'`;
|
|
});
|
|
return `(${vals.join(', ')})`;
|
|
});
|
|
sql += `INSERT INTO \`${table}\` (${colList}) VALUES\n${values.join(',\n')};\n`;
|
|
}
|
|
sql += `\n`;
|
|
}
|
|
|
|
sql += `SET FOREIGN_KEY_CHECKS = 1;\n`;
|
|
sql += `-- Fin de l'export\n`;
|
|
|
|
await conn.end();
|
|
|
|
const outputPath = '/home/ubuntu/sonum_data_export.sql';
|
|
fs.writeFileSync(outputPath, sql, 'utf8');
|
|
|
|
const stats = fs.statSync(outputPath);
|
|
console.log(`\nExport terminé : ${outputPath}`);
|
|
console.log(`Taille : ${(stats.size / 1024).toFixed(1)} KB`);
|
|
console.log(`\nRésumé par table :`);
|
|
for (const table of TABLES_TO_EXPORT) {
|
|
const matches = sql.match(new RegExp(`-- Table: ${table} \\((\\d+) lignes\\)`));
|
|
if (matches) console.log(` ${table}: ${matches[1]} lignes`);
|
|
}
|