1153 lines
34 KiB
Markdown
1153 lines
34 KiB
Markdown
# Krempl-Daten Migration zu PostgreSQL
|
|
|
|
## 🎯 Problem
|
|
|
|
Die MySQL-Tabelle `krempl_artikelgeraet_22` enthält **61 Millionen Zeilen** für N:N-Mapping zwischen Ersatzteilen und Geräten:
|
|
- 68.667 unique Ersatzteile
|
|
- 1.446.180 unique Geräte
|
|
- Durchschnittlich 888 Geräte pro Ersatzteil
|
|
- Worst Case: 129.819 Geräte pro Ersatzteil
|
|
|
|
**Konsequenzen:**
|
|
- Queries ohne LIMIT können 100.000+ Zeilen laden
|
|
- JOINs über 61 Mio Zeilen → Timeouts (504)
|
|
- Schlechte MySQL Query-Pläne trotz korrekter Indexe
|
|
- 1.3 GB Tabellengröße
|
|
|
|
## ✅ Lösung: PostgreSQL mit Array-Aggregation
|
|
|
|
**Idee:** Statt 61 Mio einzelne Zeilen → **68.667 Zeilen** (eine pro Ersatzteil) mit Arrays
|
|
|
|
```
|
|
VORHER (MySQL):
|
|
ersatzteil | geraet
|
|
-----------+--------
|
|
123 | 1
|
|
123 | 2
|
|
123 | 3
|
|
... (129.819 Zeilen für Ersatzteil 123)
|
|
|
|
NACHHER (Postgres):
|
|
ersatzteil_id | geraet_ids | geraet_count
|
|
--------------+-------------------------+-------------
|
|
123 | [1,2,3,...,129819] | 129819
|
|
```
|
|
|
|
**Warum PostgreSQL?**
|
|
- Native Array-Datentypen (`BIGINT[]`)
|
|
- GIN-Index für schnelle Array-Suchen
|
|
- Full-Text Search integriert
|
|
- Polyglot Persistence: MySQL für Shop, Postgres nur für Krempl
|
|
|
|
---
|
|
|
|
## 📋 Architektur
|
|
|
|
```
|
|
┌─────────────────────────────────────────────────────────────┐
|
|
│ MYSQL (Shop-DB) │
|
|
│ ┌──────────────────────────────────────────────────────┐ │
|
|
│ │ items │ │
|
|
│ │ - id │ │
|
|
│ │ - krempl_id (BIGINT) ←──────┐ │ │
|
|
│ │ - navision_id │ │ │
|
|
│ └────────────────────────────┼────────────────────────┘ │
|
|
└─────────────────────────────────┼──────────────────────────┘
|
|
│
|
|
│ PHP Abfrage
|
|
↓
|
|
┌─────────────────────────────────────────────────────────────┐
|
|
│ POSTGRESQL (Krempl-DB) │
|
|
│ ┌──────────────────────────────────────────────────────┐ │
|
|
│ │ ersatzteil_mapping │ │
|
|
│ │ - ersatzteil_id (PK) │ │
|
|
│ │ - geraet_ids BIGINT[] ← GIN Index │ │
|
|
│ │ - geraet_count INT │ │
|
|
│ └──────────────────┬───────────────────────────────────┘ │
|
|
│ │ │
|
|
│ │ JOIN │
|
|
│ ↓ │
|
|
│ ┌──────────────────────────────────────────────────────┐ │
|
|
│ │ geraete (1.4 Mio Zeilen) │ │
|
|
│ │ - id (PK) │ │
|
|
│ │ - nr VARCHAR ← Full-Text Index │ │
|
|
│ │ - marke, typ, modell_bezeichnung │ │
|
|
│ └──────────────────────────────────────────────────────┘ │
|
|
└─────────────────────────────────────────────────────────────┘
|
|
```
|
|
|
|
**2 Haupt-Abfragen:**
|
|
1. **Artikelseite:** Ersatzteil → Geräteliste (vorwärts)
|
|
2. **Gerätesuche:** Geräte-Suche → Ersatzteile → Shop-Artikel (rückwärts)
|
|
|
|
---
|
|
|
|
## 🗄️ PostgreSQL Schema
|
|
|
|
### Schritt 1: Datenbank erstellen
|
|
|
|
```sql
|
|
-- Als postgres User
|
|
CREATE DATABASE krempl_data
|
|
WITH ENCODING 'UTF8'
|
|
LC_COLLATE='de_DE.UTF-8'
|
|
LC_CTYPE='de_DE.UTF-8';
|
|
|
|
CREATE USER krempl_user WITH PASSWORD 'SICHERES_PASSWORT';
|
|
GRANT ALL PRIVILEGES ON DATABASE krempl_data TO krempl_user;
|
|
|
|
\c krempl_data
|
|
|
|
GRANT ALL ON SCHEMA public TO krempl_user;
|
|
```
|
|
|
|
### Schritt 2: Tabellen erstellen
|
|
|
|
```sql
|
|
-- 1. GERÄTE (ca. 1.4 Mio Zeilen, 227 MB CSV)
|
|
CREATE TABLE geraete (
|
|
id BIGINT PRIMARY KEY,
|
|
nr VARCHAR(100),
|
|
marke VARCHAR(100),
|
|
typ VARCHAR(200),
|
|
zusatz_nummer VARCHAR(100),
|
|
modell_bezeichnung VARCHAR(200),
|
|
produktionsstart VARCHAR(50),
|
|
produktionsende VARCHAR(50),
|
|
wgtext1 VARCHAR(200),
|
|
wgtext2 VARCHAR(200),
|
|
zusatz TEXT,
|
|
bezeichnung_original TEXT,
|
|
typ_de VARCHAR(200),
|
|
typ_fr VARCHAR(200),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexe für schnelle Suche
|
|
CREATE INDEX idx_geraete_nr ON geraete(nr);
|
|
CREATE INDEX idx_geraete_marke ON geraete(marke);
|
|
CREATE INDEX idx_geraete_nr_trgm ON geraete USING GIN(nr gin_trgm_ops);
|
|
CREATE INDEX idx_geraete_typ_trgm ON geraete USING GIN(typ gin_trgm_ops);
|
|
|
|
-- Full-Text Index
|
|
CREATE INDEX idx_geraete_fts ON geraete
|
|
USING GIN(to_tsvector('german',
|
|
COALESCE(nr,'') || ' ' ||
|
|
COALESCE(typ,'') || ' ' ||
|
|
COALESCE(marke,'')
|
|
));
|
|
|
|
-- 2. ERSATZTEIL-MAPPING (68k Zeilen statt 61 Mio!)
|
|
CREATE TABLE ersatzteil_mapping (
|
|
ersatzteil_id BIGINT PRIMARY KEY,
|
|
geraet_ids BIGINT[], -- Array statt Millionen Zeilen!
|
|
geraet_count INT,
|
|
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- GIN Index für Array-Suche "Welche Teile passen zu Gerät X?"
|
|
CREATE INDEX idx_mapping_geraet_array ON ersatzteil_mapping
|
|
USING GIN(geraet_ids);
|
|
|
|
-- 3. ERSATZTEILE (optional - falls Metadaten gewünscht)
|
|
CREATE TABLE ersatzteile (
|
|
id BIGINT PRIMARY KEY,
|
|
navision_id BIGINT,
|
|
originalnummer VARCHAR(100),
|
|
marke VARCHAR(100),
|
|
ean VARCHAR(50),
|
|
altenr VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_ersatzteile_navision ON ersatzteile(navision_id);
|
|
|
|
-- 4. PASSENDWIE (optional)
|
|
CREATE TABLE passendwie (
|
|
id BIGINT PRIMARY KEY,
|
|
navision_id BIGINT,
|
|
vertreiber_id VARCHAR(100),
|
|
vertreiber VARCHAR(100),
|
|
bestellcode VARCHAR(100)
|
|
);
|
|
|
|
CREATE INDEX idx_passendwie_navision ON passendwie(navision_id);
|
|
```
|
|
|
|
### Schritt 3: Extensions aktivieren
|
|
|
|
```sql
|
|
-- Für Trigram-Suche (ähnliche Strings)
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
-- Für schnellere Array-Operationen
|
|
CREATE EXTENSION IF NOT EXISTS intarray;
|
|
```
|
|
|
|
---
|
|
|
|
## 🐍 Import-Script
|
|
|
|
### Datei: `modules/import/krempl_postgres_import.py`
|
|
|
|
```python
|
|
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""
|
|
Krempl-Daten Import für PostgreSQL
|
|
Aggregiert 61 Mio Zeilen zu 68k Zeilen mit Arrays
|
|
"""
|
|
|
|
import psycopg2
|
|
import csv
|
|
import os
|
|
import sys
|
|
from collections import defaultdict
|
|
from datetime import datetime
|
|
from configparser import ConfigParser
|
|
|
|
# Konfiguration
|
|
PG_CONFIG = {
|
|
'host': 'localhost',
|
|
'port': 5432,
|
|
'database': 'krempl_data',
|
|
'user': 'krempl_user',
|
|
'password': 'PASSWORT_HIER'
|
|
}
|
|
|
|
CSV_PATHS = {
|
|
'geraete': '/var/www/vhosts/intelectra.de/httpdocs/upload/geraete_Export.csv',
|
|
'artikel': '/var/www/vhosts/intelectra.de/httpdocs/upload/artikel_Export.csv',
|
|
'mapping': '/var/www/vhosts/intelectra.de/httpdocs/upload/artikelgeraet_Export.csv',
|
|
'passendwie': '/var/www/vhosts/intelectra.de/httpdocs/upload/passendwie_Export.csv'
|
|
}
|
|
|
|
|
|
def connect_postgres():
|
|
"""Verbindung zu PostgreSQL herstellen"""
|
|
try:
|
|
conn = psycopg2.connect(**PG_CONFIG)
|
|
print(f"✅ Verbunden mit PostgreSQL: {PG_CONFIG['database']}")
|
|
return conn
|
|
except Exception as e:
|
|
print(f"❌ Postgres-Verbindung fehlgeschlagen: {e}")
|
|
sys.exit(1)
|
|
|
|
|
|
def import_geraete(conn):
|
|
"""Import Geräte-Tabelle (1.4 Mio Zeilen)"""
|
|
print("\n[1/4] Importiere Geräte...")
|
|
|
|
csv_path = CSV_PATHS['geraete']
|
|
if not os.path.exists(csv_path):
|
|
print(f"⚠️ CSV nicht gefunden: {csv_path}")
|
|
return
|
|
|
|
cursor = conn.cursor()
|
|
|
|
# Tabelle leeren
|
|
cursor.execute("TRUNCATE TABLE geraete RESTART IDENTITY CASCADE")
|
|
|
|
# CSV einlesen
|
|
with open(csv_path, 'r', encoding='utf-8') as f:
|
|
reader = csv.DictReader(f, delimiter=';')
|
|
|
|
batch = []
|
|
total = 0
|
|
|
|
for row in reader:
|
|
values = (
|
|
int(row['id']) if row['id'] else None,
|
|
row.get('nr'),
|
|
row.get('marke'),
|
|
row.get('typ'),
|
|
row.get('zusatzNummer'),
|
|
row.get('modellBezeichnung'),
|
|
row.get('produktionsstart'),
|
|
row.get('produktionsende'),
|
|
row.get('wgtext1'),
|
|
row.get('wgtext2'),
|
|
row.get('zusatz'),
|
|
row.get('bezeichnungoriginal'),
|
|
row.get('typDE'),
|
|
row.get('typFR')
|
|
)
|
|
batch.append(values)
|
|
|
|
if len(batch) >= 1000:
|
|
cursor.executemany("""
|
|
INSERT INTO geraete
|
|
(id, nr, marke, typ, zusatz_nummer, modell_bezeichnung,
|
|
produktionsstart, produktionsende, wgtext1, wgtext2,
|
|
zusatz, bezeichnung_original, typ_de, typ_fr)
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
nr = EXCLUDED.nr,
|
|
marke = EXCLUDED.marke,
|
|
typ = EXCLUDED.typ
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
batch.clear()
|
|
print(f" → {total:,} Geräte importiert...")
|
|
|
|
# Letzte Batch
|
|
if batch:
|
|
cursor.executemany("""
|
|
INSERT INTO geraete
|
|
(id, nr, marke, typ, zusatz_nummer, modell_bezeichnung,
|
|
produktionsstart, produktionsende, wgtext1, wgtext2,
|
|
zusatz, bezeichnung_original, typ_de, typ_fr)
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
nr = EXCLUDED.nr
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
|
|
print(f"✅ {total:,} Geräte importiert")
|
|
cursor.close()
|
|
|
|
|
|
def import_ersatzteile(conn):
|
|
"""Import Ersatzteil-Metadaten"""
|
|
print("\n[2/4] Importiere Ersatzteile...")
|
|
|
|
csv_path = CSV_PATHS['artikel']
|
|
if not os.path.exists(csv_path):
|
|
print(f"⚠️ CSV nicht gefunden: {csv_path}")
|
|
return
|
|
|
|
cursor = conn.cursor()
|
|
cursor.execute("TRUNCATE TABLE ersatzteile RESTART IDENTITY")
|
|
|
|
with open(csv_path, 'r', encoding='utf-8') as f:
|
|
reader = csv.DictReader(f, delimiter=';')
|
|
|
|
batch = []
|
|
total = 0
|
|
|
|
for row in reader:
|
|
values = (
|
|
int(row['id']) if row['id'] else None,
|
|
int(row['navisionid']) if row.get('navisionid') else None,
|
|
row.get('originalnummer'),
|
|
row.get('marke'),
|
|
row.get('ean'),
|
|
row.get('altenr')
|
|
)
|
|
batch.append(values)
|
|
|
|
if len(batch) >= 1000:
|
|
cursor.executemany("""
|
|
INSERT INTO ersatzteile
|
|
(id, navision_id, originalnummer, marke, ean, altenr)
|
|
VALUES (%s,%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO NOTHING
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
batch.clear()
|
|
|
|
if batch:
|
|
cursor.executemany("""
|
|
INSERT INTO ersatzteile
|
|
(id, navision_id, originalnummer, marke, ean, altenr)
|
|
VALUES (%s,%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO NOTHING
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
|
|
print(f"✅ {total:,} Ersatzteile importiert")
|
|
cursor.close()
|
|
|
|
|
|
def import_mapping_aggregated(conn):
|
|
"""
|
|
HAUPTFUNKTION: Import Mapping mit Aggregation
|
|
61 Mio Zeilen → 68k Zeilen mit Arrays
|
|
"""
|
|
print("\n[3/4] Importiere Mapping (AGGREGIERT)...")
|
|
|
|
csv_path = CSV_PATHS['mapping']
|
|
if not os.path.exists(csv_path):
|
|
print(f"❌ CSV nicht gefunden: {csv_path}")
|
|
return
|
|
|
|
file_size_gb = os.path.getsize(csv_path) / (1024**3)
|
|
print(f" CSV-Größe: {file_size_gb:.2f} GB")
|
|
print(" ⏳ Lese und aggregiere Daten (kann 5-10 Min dauern)...")
|
|
|
|
# Schritt 1: CSV in Memory aggregieren
|
|
mapping = defaultdict(list)
|
|
|
|
with open(csv_path, 'r', encoding='utf-8') as f:
|
|
reader = csv.DictReader(f, delimiter=';')
|
|
|
|
count = 0
|
|
for row in reader:
|
|
ersatzteil = int(row['ersatzteil'])
|
|
geraet = int(row['geraet'])
|
|
mapping[ersatzteil].append(geraet)
|
|
|
|
count += 1
|
|
if count % 1_000_000 == 0:
|
|
print(f" → {count:,} Zeilen gelesen...")
|
|
|
|
print(f" ✓ {count:,} Zeilen gelesen")
|
|
print(f" ✓ {len(mapping):,} unique Ersatzteile gefunden")
|
|
|
|
# Schritt 2: In Postgres schreiben (aggregiert!)
|
|
print(" 💾 Schreibe aggregierte Daten in Postgres...")
|
|
|
|
cursor = conn.cursor()
|
|
cursor.execute("TRUNCATE TABLE ersatzteil_mapping RESTART IDENTITY")
|
|
|
|
batch = []
|
|
total = 0
|
|
|
|
for ersatzteil_id, geraet_list in mapping.items():
|
|
batch.append((
|
|
ersatzteil_id,
|
|
geraet_list, # Als Python-Liste → wird zu Postgres Array!
|
|
len(geraet_list)
|
|
))
|
|
|
|
if len(batch) >= 500:
|
|
cursor.executemany("""
|
|
INSERT INTO ersatzteil_mapping
|
|
(ersatzteil_id, geraet_ids, geraet_count)
|
|
VALUES (%s, %s, %s)
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
batch.clear()
|
|
print(f" → {total:,}/{len(mapping):,} Ersatzteile geschrieben...")
|
|
|
|
# Letzte Batch
|
|
if batch:
|
|
cursor.executemany("""
|
|
INSERT INTO ersatzteil_mapping
|
|
(ersatzteil_id, geraet_ids, geraet_count)
|
|
VALUES (%s, %s, %s)
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
|
|
print(f"✅ {total:,} Ersatzteile mit Arrays gespeichert")
|
|
|
|
# Statistik
|
|
cursor.execute("SELECT MAX(geraet_count), AVG(geraet_count) FROM ersatzteil_mapping")
|
|
max_count, avg_count = cursor.fetchone()
|
|
print(f" 📊 Max Geräte pro Teil: {max_count:,}")
|
|
print(f" 📊 Ø Geräte pro Teil: {avg_count:.0f}")
|
|
|
|
cursor.close()
|
|
|
|
|
|
def import_passendwie(conn):
|
|
"""Import PassendWie-Daten (optional)"""
|
|
print("\n[4/4] Importiere PassendWie...")
|
|
|
|
csv_path = CSV_PATHS['passendwie']
|
|
if not os.path.exists(csv_path):
|
|
print(f"⚠️ CSV nicht gefunden: {csv_path}")
|
|
return
|
|
|
|
cursor = conn.cursor()
|
|
cursor.execute("TRUNCATE TABLE passendwie RESTART IDENTITY")
|
|
|
|
with open(csv_path, 'r', encoding='utf-8') as f:
|
|
reader = csv.DictReader(f, delimiter=';')
|
|
|
|
batch = []
|
|
total = 0
|
|
|
|
for row in reader:
|
|
values = (
|
|
int(row['id']) if row['id'] else None,
|
|
int(row['navisionid']) if row.get('navisionid') else None,
|
|
row.get('vertreiberid'),
|
|
row.get('vertreiber'),
|
|
row.get('bestellcode')
|
|
)
|
|
batch.append(values)
|
|
|
|
if len(batch) >= 1000:
|
|
cursor.executemany("""
|
|
INSERT INTO passendwie
|
|
(id, navision_id, vertreiber_id, vertreiber, bestellcode)
|
|
VALUES (%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO NOTHING
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
batch.clear()
|
|
|
|
if batch:
|
|
cursor.executemany("""
|
|
INSERT INTO passendwie
|
|
(id, navision_id, vertreiber_id, vertreiber, bestellcode)
|
|
VALUES (%s,%s,%s,%s,%s)
|
|
ON CONFLICT (id) DO NOTHING
|
|
""", batch)
|
|
conn.commit()
|
|
total += len(batch)
|
|
|
|
print(f"✅ {total:,} PassendWie-Einträge importiert")
|
|
cursor.close()
|
|
|
|
|
|
def analyze_tables(conn):
|
|
"""Tabellen-Statistiken aktualisieren"""
|
|
print("\n[FINAL] Aktualisiere Tabellen-Statistiken...")
|
|
cursor = conn.cursor()
|
|
cursor.execute("ANALYZE geraete")
|
|
cursor.execute("ANALYZE ersatzteil_mapping")
|
|
cursor.execute("ANALYZE ersatzteile")
|
|
cursor.execute("ANALYZE passendwie")
|
|
print("✅ ANALYZE abgeschlossen")
|
|
cursor.close()
|
|
|
|
|
|
def main():
|
|
"""Hauptfunktion"""
|
|
print("=" * 60)
|
|
print("KREMPL POSTGRESQL IMPORT")
|
|
print("Aggregiert 61 Mio Zeilen → 68k Zeilen mit Arrays")
|
|
print("=" * 60)
|
|
|
|
start_time = datetime.now()
|
|
|
|
conn = connect_postgres()
|
|
|
|
try:
|
|
import_geraete(conn)
|
|
import_ersatzteile(conn)
|
|
import_mapping_aggregated(conn) # WICHTIGSTE FUNKTION!
|
|
import_passendwie(conn)
|
|
analyze_tables(conn)
|
|
|
|
duration = datetime.now() - start_time
|
|
print("\n" + "=" * 60)
|
|
print(f"✅ IMPORT ERFOLGREICH in {duration}")
|
|
print("=" * 60)
|
|
|
|
except Exception as e:
|
|
print(f"\n❌ FEHLER: {e}")
|
|
conn.rollback()
|
|
raise
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|
|
```
|
|
|
|
**Ausführen:**
|
|
```bash
|
|
python3 modules/import/krempl_postgres_import.py
|
|
```
|
|
|
|
---
|
|
|
|
## 🔧 PHP-Änderungen (2 Stellen)
|
|
|
|
### 1. Postgres-Connection Helper
|
|
|
|
**Neue Datei:** `core/postgres_connection.php`
|
|
|
|
```php
|
|
<?php
|
|
/**
|
|
* PostgreSQL Connection für Krempl-Daten
|
|
*/
|
|
|
|
class PostgresKremplDB {
|
|
private static $instance = null;
|
|
private $pdo;
|
|
|
|
private function __construct() {
|
|
try {
|
|
$this->pdo = new PDO(
|
|
'pgsql:host=localhost;dbname=krempl_data',
|
|
'krempl_user',
|
|
'PASSWORT_HIER',
|
|
[
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
PDO::ATTR_PERSISTENT => true // Connection Pool
|
|
]
|
|
);
|
|
} catch (PDOException $e) {
|
|
error_log("Postgres Krempl Connection failed: " . $e->getMessage());
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
public static function getInstance() {
|
|
if (self::$instance === null) {
|
|
self::$instance = new self();
|
|
}
|
|
return self::$instance;
|
|
}
|
|
|
|
public function getConnection() {
|
|
return $this->pdo;
|
|
}
|
|
|
|
/**
|
|
* Gibt Geräte für ein Ersatzteil zurück
|
|
*/
|
|
public function getGeraeteByErsatzteil($ersatzteil_id, $limit = 100) {
|
|
$stmt = $this->pdo->prepare("
|
|
SELECT
|
|
em.geraet_count,
|
|
g.id, g.nr, g.marke, g.typ, g.modell_bezeichnung,
|
|
g.produktionsstart, g.produktionsende
|
|
FROM ersatzteil_mapping em
|
|
JOIN geraete g ON g.id = ANY(em.geraet_ids)
|
|
WHERE em.ersatzteil_id = :ersatzteil_id
|
|
LIMIT :limit
|
|
");
|
|
|
|
$stmt->bindValue(':ersatzteil_id', $ersatzteil_id, PDO::PARAM_INT);
|
|
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
|
|
return [
|
|
'geraete' => $stmt->fetchAll(),
|
|
'total_count' => $this->getGeraeteCount($ersatzteil_id)
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Gibt Anzahl Geräte für ein Ersatzteil zurück
|
|
*/
|
|
public function getGeraeteCount($ersatzteil_id) {
|
|
$stmt = $this->pdo->prepare("
|
|
SELECT geraet_count
|
|
FROM ersatzteil_mapping
|
|
WHERE ersatzteil_id = :id
|
|
");
|
|
$stmt->execute([':id' => $ersatzteil_id]);
|
|
$result = $stmt->fetch();
|
|
return $result ? $result['geraet_count'] : 0;
|
|
}
|
|
|
|
/**
|
|
* Sucht Geräte nach Nummer/Typ und gibt Ersatzteil-IDs zurück
|
|
*/
|
|
public function searchGeraeteGetErsatzteile($search_term, $limit = 50) {
|
|
// 1. Finde Geräte
|
|
$stmt = $this->pdo->prepare("
|
|
SELECT id
|
|
FROM geraete
|
|
WHERE
|
|
to_tsvector('german', COALESCE(nr,'') || ' ' || COALESCE(typ,''))
|
|
@@ plainto_tsquery('german', :search)
|
|
LIMIT :limit
|
|
");
|
|
$stmt->bindValue(':search', $search_term, PDO::PARAM_STR);
|
|
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
|
|
$geraet_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
if (empty($geraet_ids)) {
|
|
return [];
|
|
}
|
|
|
|
// 2. Finde Ersatzteile die zu diesen Geräten passen
|
|
$geraet_ids_str = implode(',', array_map('intval', $geraet_ids));
|
|
|
|
$stmt = $this->pdo->query("
|
|
SELECT DISTINCT ersatzteil_id
|
|
FROM ersatzteil_mapping
|
|
WHERE geraet_ids && ARRAY[$geraet_ids_str]::bigint[]
|
|
LIMIT 500
|
|
");
|
|
|
|
return $stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
}
|
|
}
|
|
```
|
|
|
|
### 2. Artikeldetailseite anpassen
|
|
|
|
**Datei:** `web/intelectra_shop/php/intelectra_item_additional.inc.php`
|
|
|
|
```php
|
|
<?php
|
|
// ALTE VERSION (ca. Zeile 100-105):
|
|
/*
|
|
if ($obj->krempl_id && (!isset($result) || $result->num_rows < 1)) {
|
|
$sql = "SELECT * FROM krempl_artikelgeraet_22 ka
|
|
left join krempl_geraete_22 kg on kg.id=ka.geraet
|
|
WHERE ka.ersatzteil=".$this->db->real_escape_string($obj->krempl_id)." AND not isnull(nr)";
|
|
$result = $this->db->query($sql);
|
|
}
|
|
*/
|
|
|
|
// NEUE VERSION (PostgreSQL):
|
|
if ($obj->krempl_id && (!isset($result) || $result->num_rows < 1)) {
|
|
try {
|
|
require_once(__DIR__ . '/../../../core/postgres_connection.php');
|
|
$pg = PostgresKremplDB::getInstance();
|
|
|
|
$data = $pg->getGeraeteByErsatzteil($obj->krempl_id, 100);
|
|
$geraete = $data['geraete'];
|
|
$total_count = $data['total_count'];
|
|
|
|
// Wenn mehr als 100 Geräte: Warnung anzeigen
|
|
if ($total_count > 100) {
|
|
echo '<div class="alert alert-info">';
|
|
echo 'Dieses Ersatzteil passt zu ' . number_format($total_count, 0, ',', '.') . ' Geräten. ';
|
|
echo 'Es werden die ersten 100 angezeigt.';
|
|
echo '</div>';
|
|
}
|
|
|
|
// Geräte-Liste rendern
|
|
if (!empty($geraete)) {
|
|
echo '<div class="krempl-geraete-liste">';
|
|
echo '<h3>Passende Geräte</h3>';
|
|
echo '<table class="table table-striped">';
|
|
echo '<thead><tr><th>Gerätenummer</th><th>Marke</th><th>Typ</th><th>Modell</th></tr></thead>';
|
|
echo '<tbody>';
|
|
|
|
foreach ($geraete as $geraet) {
|
|
echo '<tr>';
|
|
echo '<td>' . htmlspecialchars($geraet['nr']) . '</td>';
|
|
echo '<td>' . htmlspecialchars($geraet['marke']) . '</td>';
|
|
echo '<td>' . htmlspecialchars($geraet['typ']) . '</td>';
|
|
echo '<td>' . htmlspecialchars($geraet['modell_bezeichnung']) . '</td>';
|
|
echo '</tr>';
|
|
}
|
|
|
|
echo '</tbody></table></div>';
|
|
}
|
|
|
|
} catch (Exception $e) {
|
|
error_log("Krempl Postgres Error: " . $e->getMessage());
|
|
|
|
// FALLBACK zu MySQL bei Fehler
|
|
$sql = "SELECT kg.* FROM krempl_artikelgeraet_22 ka
|
|
LEFT JOIN krempl_geraete_22 kg ON kg.id=ka.geraet
|
|
WHERE ka.ersatzteil=".$this->db->real_escape_string($obj->krempl_id)."
|
|
AND NOT ISNULL(nr)
|
|
LIMIT 100";
|
|
$result = $this->db->query($sql);
|
|
|
|
// Alte Rendering-Logik hier...
|
|
}
|
|
}
|
|
?>
|
|
```
|
|
|
|
### 3. Gerätesuche anpassen
|
|
|
|
**Datei:** `web/intelectra_shop/php/itemsearch.class.php`
|
|
|
|
```php
|
|
<?php
|
|
// ALTE VERSION (ca. Zeile 278-285):
|
|
/*
|
|
private function _getItembyKremplNumber($searchString_ready) {
|
|
$query = "
|
|
SELECT i.id FROM krempl_artikelgeraet_22 ka
|
|
LEFT JOIN krempl_geraete_22 kg ON kg.id = ka.geraet
|
|
LEFT JOIN krempl_id2022 ki ON ersatzteil = ki.id
|
|
LEFT JOIN items i ON ki.navisionid = i.attribute_7
|
|
WHERE not isnull(nr) AND nr LIKE '%".$this->db->real_escape_string($searchString_ready)."%' LIMIT 150;
|
|
";
|
|
// ...
|
|
}
|
|
*/
|
|
|
|
// NEUE VERSION (PostgreSQL):
|
|
private function _getItembyKremplNumber($searchString_ready) {
|
|
try {
|
|
require_once(__DIR__ . '/../../../core/postgres_connection.php');
|
|
$pg = PostgresKremplDB::getInstance();
|
|
|
|
// 1. Suche Geräte in Postgres → gibt Ersatzteil-IDs zurück
|
|
$ersatzteil_ids = $pg->searchGeraeteGetErsatzteile($searchString_ready, 50);
|
|
|
|
if (empty($ersatzteil_ids)) {
|
|
return [];
|
|
}
|
|
|
|
// 2. Finde Shop-Artikel in MySQL via krempl_id
|
|
$ids_list = implode(',', array_map('intval', $ersatzteil_ids));
|
|
|
|
$query = "
|
|
SELECT DISTINCT i.id
|
|
FROM items i
|
|
WHERE i.krempl_id IN ($ids_list)
|
|
AND i.active = 1
|
|
LIMIT 150
|
|
";
|
|
|
|
$result = $this->db->query($query);
|
|
$items = [];
|
|
|
|
while ($row = $result->fetch_assoc()) {
|
|
$items[] = $row['id'];
|
|
}
|
|
|
|
return $items;
|
|
|
|
} catch (Exception $e) {
|
|
error_log("Krempl Search Error: " . $e->getMessage());
|
|
|
|
// FALLBACK zu MySQL
|
|
$query = "
|
|
SELECT i.id FROM krempl_artikelgeraet_22 ka
|
|
LEFT JOIN krempl_geraete_22 kg ON kg.id = ka.geraet
|
|
LEFT JOIN krempl_id2022 ki ON ersatzteil = ki.id
|
|
LEFT JOIN items i ON ki.navisionid = i.attribute_7
|
|
WHERE NOT ISNULL(nr)
|
|
AND nr LIKE '%".$this->db->real_escape_string($searchString_ready)."%'
|
|
LIMIT 150
|
|
";
|
|
|
|
// Alte Logik...
|
|
}
|
|
}
|
|
?>
|
|
```
|
|
|
|
---
|
|
|
|
## 🚀 Installation & Deployment
|
|
|
|
### Voraussetzungen
|
|
|
|
**Plesk Server:**
|
|
1. Im Plesk Panel → Tools & Settings → Updates & Upgrades
|
|
2. Add/Remove Components → PostgreSQL Server auswählen
|
|
3. Installieren
|
|
|
|
**Oder manuell (Debian/Ubuntu):**
|
|
```bash
|
|
sudo apt update
|
|
sudo apt install postgresql postgresql-contrib php-pgsql python3-psycopg2
|
|
|
|
# Postgres starten
|
|
sudo systemctl start postgresql
|
|
sudo systemctl enable postgresql
|
|
```
|
|
|
|
**Nach Installation prüfen:**
|
|
```bash
|
|
# PostgreSQL läuft?
|
|
systemctl status postgresql
|
|
|
|
# Welche Version?
|
|
PGPASSWORD='PLESK_PG_PASSWORD' psql -U root -h localhost -d postgres -c "SELECT version();"
|
|
|
|
# PHP Extension vorhanden?
|
|
php -m | grep pdo_pgsql
|
|
```
|
|
|
|
### Schritt-für-Schritt (Plesk)
|
|
|
|
```bash
|
|
# 1. Plesk PostgreSQL Passwort holen
|
|
# Panel → Tools & Settings → Database Servers → PostgreSQL
|
|
# Passwort notieren!
|
|
|
|
# 2. Datenbank + User erstellen
|
|
PLESK_PW='DEIN_PLESK_PG_PASSWORD'
|
|
|
|
PGPASSWORD=$PLESK_PW psql -U root -h localhost -d postgres -c "
|
|
CREATE DATABASE krempl_data WITH ENCODING 'UTF8';
|
|
"
|
|
|
|
PGPASSWORD=$PLESK_PW psql -U root -h localhost -d postgres -c "
|
|
CREATE USER krempl_user WITH PASSWORD 'KremplSecure2025';
|
|
"
|
|
|
|
PGPASSWORD=$PLESK_PW psql -U root -h localhost -d postgres -c "
|
|
GRANT ALL PRIVILEGES ON DATABASE krempl_data TO krempl_user;
|
|
"
|
|
|
|
# 3. Schema laden
|
|
cd /var/www/vhosts/DEINE_DOMAIN/httpdocs/core/postgres
|
|
PGPASSWORD=$PLESK_PW psql -U root -h localhost -d krempl_data -f schema.sql
|
|
|
|
# 4. Rechte setzen
|
|
PGPASSWORD=$PLESK_PW psql -U root -h localhost -d krempl_data -c "
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO krempl_user;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO krempl_user;
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO krempl_user;
|
|
"
|
|
|
|
# 5. PHP PDO Extension prüfen (sollte schon da sein)
|
|
php -m | grep pdo_pgsql
|
|
|
|
# 6. Python-Modul installieren
|
|
pip3 install psycopg2-binary
|
|
|
|
# 7. config.ini anpassen
|
|
cp config.ini.example config.ini
|
|
nano config.ini
|
|
# Passwort eintragen!
|
|
|
|
# 8. Import ausführen
|
|
python3 import.py 2>&1 | tee /tmp/krempl_import.log
|
|
|
|
# Dauert ca. 10-20 Minuten für 1.3 GB Daten
|
|
# In anderem Terminal Fortschritt beobachten:
|
|
# tail -f /tmp/krempl_import.log
|
|
```
|
|
|
|
---
|
|
|
|
## 🧪 Testing-Checkliste
|
|
|
|
### Funktionale Tests
|
|
|
|
- [ ] **Artikelseite**: Ersatzteil mit 5 Geräten → Liste wird angezeigt
|
|
- [ ] **Artikelseite**: Ersatzteil mit 50.000 Geräten → Nur 100 angezeigt + Warnung
|
|
- [ ] **Gerätesuche**: Suche "AEG 123" → Shop-Artikel werden gefunden
|
|
- [ ] **Gerätesuche**: Suche "Bosch XYZ" → Keine Treffer = leere Liste
|
|
- [ ] **Fallback**: Postgres offline → MySQL Fallback funktioniert
|
|
|
|
### Performance Tests
|
|
|
|
```sql
|
|
-- Test 1: Geräte zu Ersatzteil (sollte < 100ms sein)
|
|
EXPLAIN ANALYZE
|
|
SELECT g.*
|
|
FROM ersatzteil_mapping em
|
|
JOIN geraete g ON g.id = ANY(em.geraet_ids)
|
|
WHERE em.ersatzteil_id = 7764466071
|
|
LIMIT 100;
|
|
|
|
-- Test 2: Ersatzteile zu Gerät (sollte < 200ms sein)
|
|
EXPLAIN ANALYZE
|
|
SELECT ersatzteil_id
|
|
FROM ersatzteil_mapping
|
|
WHERE geraet_ids @> ARRAY[123456]::bigint[];
|
|
|
|
-- Test 3: Full-Text Suche (sollte < 300ms sein)
|
|
EXPLAIN ANALYZE
|
|
SELECT id FROM geraete
|
|
WHERE to_tsvector('german', nr || ' ' || typ) @@ plainto_tsquery('german', 'AEG Kühlschrank')
|
|
LIMIT 50;
|
|
```
|
|
|
|
### Vergleich MySQL vs PostgreSQL
|
|
|
|
```bash
|
|
# Altes System (MySQL):
|
|
time curl "http://testshop.intelectra.de/artikel/12345"
|
|
# Erwartet: 5-15 Sekunden bei vielen Geräten
|
|
|
|
# Neues System (PostgreSQL):
|
|
time curl "http://testshop.intelectra.de/artikel/12345"
|
|
# Erwartet: < 1 Sekunde
|
|
```
|
|
|
|
---
|
|
|
|
## 🔄 Rollback-Plan
|
|
|
|
Falls Probleme auftreten:
|
|
|
|
```php
|
|
// In postgres_connection.php:
|
|
// Setze Flag auf false um PostgreSQL zu deaktivieren
|
|
define('USE_POSTGRES_KREMPL', false);
|
|
|
|
// Dann in den PHP-Dateien:
|
|
if (defined('USE_POSTGRES_KREMPL') && USE_POSTGRES_KREMPL) {
|
|
// Postgres Code
|
|
} else {
|
|
// MySQL Fallback
|
|
}
|
|
```
|
|
|
|
**Oder:** Einfach die alten Dateien wiederherstellen aus Git.
|
|
|
|
---
|
|
|
|
## 📊 Monitoring
|
|
|
|
### PostgreSQL Queries überwachen
|
|
|
|
```sql
|
|
-- Langsame Queries finden
|
|
SELECT query, calls, total_time, mean_time
|
|
FROM pg_stat_statements
|
|
WHERE query LIKE '%krempl%'
|
|
ORDER BY mean_time DESC
|
|
LIMIT 10;
|
|
|
|
-- Index-Usage prüfen
|
|
SELECT schemaname, tablename, indexname, idx_scan
|
|
FROM pg_stat_user_indexes
|
|
WHERE schemaname = 'public'
|
|
ORDER BY idx_scan ASC;
|
|
```
|
|
|
|
### PHP Error Log
|
|
|
|
```bash
|
|
tail -f /var/log/php-fpm/error.log | grep -i krempl
|
|
```
|
|
|
|
---
|
|
|
|
## 🎯 Erwartete Verbesserungen
|
|
|
|
| Metrik | Vorher (MySQL) | Nachher (Postgres) |
|
|
|--------|----------------|-------------------|
|
|
| **DB-Größe** | 1.3 GB | ~100 MB |
|
|
| **Zeilen** | 61.000.000 | 68.667 |
|
|
| **Artikelseite (viele Geräte)** | 5-15 Sek | 50-200 ms |
|
|
| **Gerätesuche** | 10-30 Sek | 100-500 ms |
|
|
| **504 Timeouts** | Häufig | Keine |
|
|
|
|
---
|
|
|
|
## 📚 Weiterführende Optimierungen
|
|
|
|
Falls noch schneller gewünscht:
|
|
|
|
1. **Redis Cache** vor Postgres setzen
|
|
```php
|
|
$redis->setex("krempl:$id", 3600, json_encode($geraete));
|
|
```
|
|
|
|
2. **Materialized View** für Top 10k Ersatzteile
|
|
```sql
|
|
CREATE MATERIALIZED VIEW top_ersatzteile AS
|
|
SELECT * FROM ersatzteil_mapping
|
|
ORDER BY geraet_count DESC LIMIT 10000;
|
|
|
|
REFRESH MATERIALIZED VIEW top_ersatzteile; -- täglich
|
|
```
|
|
|
|
3. **PostgreSQL Tuning** (`postgresql.conf`)
|
|
```ini
|
|
shared_buffers = 2GB
|
|
effective_cache_size = 6GB
|
|
maintenance_work_mem = 1GB
|
|
work_mem = 50MB
|
|
```
|
|
|
|
---
|
|
|
|
## ❓ FAQ
|
|
|
|
**Q: Muss MySQL weg?**
|
|
A: Nein! Nur die Krempl-Tabellen wandern zu Postgres. Shop-DB bleibt MySQL.
|
|
|
|
**Q: Kann ich beide Systeme parallel testen?**
|
|
A: Ja! Beide Tabellen können gleichzeitig existieren. Feature-Flag in Code.
|
|
|
|
**Q: Was wenn Postgres ausfällt?**
|
|
A: Try-Catch mit MySQL-Fallback ist im Code drin.
|
|
|
|
**Q: Wie oft Import?**
|
|
A: So oft wie Krempl neue Daten liefert. Script ist idempotent (mehrfach ausführbar).
|
|
|
|
**Q: Performance auf Shared Hosting?**
|
|
A: Postgres braucht ~2 GB RAM. Bei Shared Hosting ggf. dedicated Server nötig.
|
|
|
|
---
|
|
|
|
**Viel Erfolg beim Testen! 🚀**
|
|
|
|
---
|
|
|
|
## 🔴 PLESK-SPEZIFISCHE TROUBLESHOOTING
|
|
|
|
### Problem: "password authentication failed for user krempl_user"
|
|
|
|
**Ursache:** Sonderzeichen im Passwort können Probleme machen
|
|
|
|
**Lösung:**
|
|
```bash
|
|
# Passwort ohne Sonderzeichen setzen (nur A-Z, a-z, 0-9)
|
|
PGPASSWORD='PLESK_PG_PASSWORD' psql -U root -h localhost -d postgres -c "
|
|
ALTER USER krempl_user WITH PASSWORD 'KremplSecure2025';
|
|
"
|
|
|
|
# Testen:
|
|
PGPASSWORD='KremplSecure2025' psql -U krempl_user -h localhost -d krempl_data -c "SELECT 1;"
|
|
```
|
|
|
|
### Problem: "permission denied for table geraete"
|
|
|
|
**Ursache:** Tabellen-Rechte wurden nicht gesetzt
|
|
|
|
**Lösung:**
|
|
```bash
|
|
PGPASSWORD='PLESK_PG_PASSWORD' psql -U root -h localhost -d krempl_data -c "
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO krempl_user;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO krempl_user;
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO krempl_user;
|
|
"
|
|
```
|
|
|
|
### Problem: "ModuleNotFoundError: No module named 'psycopg2'"
|
|
|
|
**Ursache:** Python-Modul nicht installiert
|
|
|
|
**Lösung:**
|
|
```bash
|
|
pip3 install psycopg2-binary
|
|
|
|
# Prüfen:
|
|
python3 -c "import psycopg2; print('psycopg2 installiert:', psycopg2.__version__)"
|
|
```
|
|
|
|
### Problem: Log-Datei kann nicht erstellt werden
|
|
|
|
**Ursache:** Verzeichnis existiert nicht
|
|
|
|
**Lösung 1 - Kein Log:**
|
|
```ini
|
|
# In config.ini:
|
|
[logging]
|
|
log_file =
|
|
```
|
|
|
|
**Lösung 2 - Log-Verzeichnis erstellen:**
|
|
```bash
|
|
mkdir -p /var/www/vhosts/DEINE_DOMAIN/httpdocs/logs
|
|
chmod 755 /var/www/vhosts/DEINE_DOMAIN/httpdocs/logs
|
|
```
|
|
|
|
### Wichtig: PostgreSQL User bei Plesk
|
|
|
|
**Plesk verwendet `root` statt `postgres` als Admin-User!**
|
|
|
|
```bash
|
|
# FALSCH:
|
|
sudo -u postgres psql
|
|
|
|
# RICHTIG bei Plesk:
|
|
PGPASSWORD='PLESK_PG_PASSWORD' psql -U root -h localhost -d postgres
|
|
```
|
|
|
|
### PostgreSQL Admin-Passwort finden (Plesk)
|
|
|
|
1. Plesk Panel → Tools & Settings
|
|
2. Database Servers → PostgreSQL
|
|
3. Passwort anzeigen/kopieren
|
|
|
|
Oder per CLI (als root):
|
|
```bash
|
|
cat /etc/postgresql-common/pwfile 2>/dev/null
|
|
```
|
|
|
|
---
|
|
|
|
**Stand:** Oktober 2025 - Getestet auf Plesk Obsidian mit PostgreSQL 12.22
|