122 lines
4.8 KiB
JavaScript
122 lines
4.8 KiB
JavaScript
/**
|
||
* Migrationsskript: MongoDB → SQL-Dump
|
||
*
|
||
* Liest alle Einträge aus MongoDB und erzeugt eine Datei
|
||
* scripts/tabletten_dump.sql, die du über phpMyAdmin importieren kannst.
|
||
*
|
||
* Ausführen:
|
||
* node scripts/migrate-mongo-to-mysql.mjs
|
||
*
|
||
* Danach in phpMyAdmin:
|
||
* Datenbank "medizin" auswählen → Importieren → tabletten_dump.sql hochladen
|
||
*/
|
||
|
||
import { readFileSync, writeFileSync } from 'fs';
|
||
import { MongoClient } from 'mongodb';
|
||
|
||
// .env.local manuell einlesen (Next.js lädt es nicht automatisch bei node)
|
||
try {
|
||
const env = readFileSync(new URL('../.env.local', import.meta.url), 'utf8');
|
||
for (const line of env.split('\n')) {
|
||
const trimmed = line.trim();
|
||
if (!trimmed || trimmed.startsWith('#')) continue;
|
||
const eqIdx = trimmed.indexOf('=');
|
||
if (eqIdx === -1) continue;
|
||
const key = trimmed.slice(0, eqIdx).trim();
|
||
const val = trimmed.slice(eqIdx + 1).trim().replace(/^["']|["']$/g, '');
|
||
if (!(key in process.env)) process.env[key] = val;
|
||
}
|
||
} catch {
|
||
console.warn('Kein .env.local gefunden – nutze vorhandene Umgebungsvariablen.');
|
||
}
|
||
|
||
// ── MongoDB-Verbindung ──────────────────────────────────────────────────────
|
||
const MONGOHOST = process.env.MONGOHOST || 'localhost';
|
||
const MONGOPORT = process.env.MONGOPORT || '27017';
|
||
const MONGOAUTH = process.env.MONGOAUTH === 'true';
|
||
const MONGOUSRP = process.env.MONGOUSRP || '';
|
||
const MONGOBASE = process.env.MONGOBASE || 'medizin';
|
||
|
||
const mongoUrl = MONGOAUTH
|
||
? `mongodb://${MONGOUSRP}@${MONGOHOST}:${MONGOPORT}/?authSource=admin`
|
||
: `mongodb://${MONGOHOST}:${MONGOPORT}`;
|
||
|
||
// ── Daten aus MongoDB lesen ─────────────────────────────────────────────────
|
||
const mongoClient = new MongoClient(mongoUrl);
|
||
await mongoClient.connect();
|
||
|
||
const docs = await mongoClient
|
||
.db(MONGOBASE)
|
||
.collection('tabletten')
|
||
.find({})
|
||
.project({ _id: 0 })
|
||
.toArray();
|
||
|
||
await mongoClient.close();
|
||
console.log(`${docs.length} Einträge aus MongoDB gelesen.`);
|
||
|
||
// ── SQL-Dump erzeugen ───────────────────────────────────────────────────────
|
||
const lines = [];
|
||
|
||
// Spaltenreihenfolge im CREATE TABLE: tab, pday, cnt, at, akt, until, warn, rem, order
|
||
lines.push('-- Tabletten-Dump aus MongoDB');
|
||
lines.push(`-- Erstellt: ${new Date().toISOString()}`);
|
||
lines.push('-- Importieren in phpMyAdmin: Datenbank auswaehlen -> Importieren -> Datei hochladen');
|
||
lines.push('');
|
||
lines.push('SET NAMES utf8mb4;');
|
||
lines.push('SET SQL_MODE = "";');
|
||
lines.push('');
|
||
// Backticks NUR fuer reservierte Woerter: at, until, order
|
||
lines.push('CREATE TABLE IF NOT EXISTS tabletten (');
|
||
lines.push(' tab VARCHAR(255) NOT NULL,');
|
||
lines.push(' pday FLOAT NOT NULL DEFAULT 1,');
|
||
lines.push(' cnt INT NOT NULL DEFAULT 0,');
|
||
lines.push(' `at` DATE,');
|
||
lines.push(' akt FLOAT NOT NULL DEFAULT 0,');
|
||
lines.push(' `until` DATE,');
|
||
lines.push(' warn TINYINT(1) NOT NULL DEFAULT 0,');
|
||
lines.push(' rem VARCHAR(255) NOT NULL DEFAULT \'\',');
|
||
lines.push(' `order` VARCHAR(255) NOT NULL DEFAULT \'\',');
|
||
lines.push(' PRIMARY KEY (tab)');
|
||
lines.push(') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;');
|
||
lines.push('');
|
||
|
||
for (const d of docs) {
|
||
const tab = esc(d.tab ?? '');
|
||
const pday = parseFloat(d.pday) || 1;
|
||
const cnt = parseInt(d.cnt, 10) || 0;
|
||
const at = sqlDate(d.at);
|
||
const akt = parseFloat(d.akt) || 0;
|
||
const until = sqlDate(d.until);
|
||
const warn = d.warn ? 1 : 0;
|
||
const rem = esc(d.rem ?? '');
|
||
const order = esc(d.order ?? '');
|
||
|
||
const atVal = at ? `'${at}'` : 'NULL';
|
||
const untilVal = until ? `'${until}'` : 'NULL';
|
||
|
||
// Positionaler INSERT ohne Spaltennamen-Liste -> keine Backticks noetig
|
||
lines.push(
|
||
`INSERT INTO tabletten VALUES` +
|
||
` ('${tab}',${pday},${cnt},${atVal},${akt},${untilVal},${warn},'${rem}','${order}');`
|
||
);
|
||
console.log(` ✓ ${d.tab}`);
|
||
}
|
||
|
||
const outPath = new URL('tabletten_dump.sql', import.meta.url);
|
||
writeFileSync(outPath, lines.join('\n') + '\n', 'utf8');
|
||
console.log(`\nDump geschrieben: scripts/tabletten_dump.sql`);
|
||
console.log('→ In phpMyAdmin: Datenbank "medizin" → Importieren → Datei hochladen');
|
||
|
||
// ── Hilfsfunktionen ─────────────────────────────────────────────────────────
|
||
function esc(str) {
|
||
return String(str).replace(/\\/g, '\\\\').replace(/'/g, "\\'");
|
||
}
|
||
|
||
function sqlDate(val) {
|
||
if (!val) return null;
|
||
const d = new Date(val);
|
||
if (isNaN(d.getTime())) return null;
|
||
return d.toISOString().slice(0, 10);
|
||
}
|