CSV-Import-Produkte

Der Inhalt der CSV-Dateien mit dem Produktdaten kann in Abhängigkeit von der Quelle (source) 1 - Affilinet API, 2 - Affilinet-CSV und ggf. der publisher_id (nur bei Affilinet-CSV) unterscheiden.

Im schlimmsten Fall fehlen Pflichtfelder.

In diesem Fall werden die Daten nicht übernommen und eine Fehlermeldung generiert.

Für die Prüfung wird jedes Feld des CSV-Datei geprüft.

Befindet sich das Feld in der Zuordnungstabelle TABLE_FIELD_LIST_SOURCE für die angebene Quelle + pub_id, wird der entsprechende Aliasname zurückgegeben und für dieses Feld verwendet. Der Status wird dabei nicht interpretiert.

Gibt es das Feld noch nicht, wird nach einem Eintrag in der Tabelle TABLE_FIELD_LIST_ALIAS gesucht. Ist ein passender vorhanden, wird die Zuordnung in der Tabelle TABLE_FIELD_LIST_SOURCE gespeichert, ansonsten erfolgt ein Eintrag in der Tabelle TABLE_FIELD_LIST_SOURCE mit field_id = 0!

Für nichtzugeordnete Felder (field_id = 0!) wird der übergebene Feldname in Kleinbuchstaben (bei doppelten Feldnamen + lfd. Nummer) verwendet, varchar(255).

Wenn alle Felder geprüft sind, wird die Tabelle shop_[shop_id]_suffix erstellt. Dazu wird der Name der aktuell nichtaktiven Tabelle über eine Funktion abgefragt.

Die Produkttabelle pro Shop enthält alle Pflichtfelder + verfügbare Zusatzfelder. Die Felder Shopname und die ShopID werden nicht in der CSV-Datei übergeben und aus Performancegründen nicht zusätzlich in die Tabelle eingetragen.

Einlesen der Daten in die Shoptabellen

Das hier beschriebene Verfahren ist geeignet für das Einlesen der Daten aus CSV-Tabellen, die in der ersten Zeile die Feldnamen enthalten, wie z.B. bei Affilinet.

Die Daten werden nach Möglichkeit als gezippte Datei geladen und auf dem Server entpackt.^¹

Für das Einlesen der Daten in die Datenbank wird die entpackte Datei zeilenweise eingelesen.

Einlesen der Felder

Es wird zunächst die erste Zeile in ein array ($label) eingelesen. Jeder Feldname aus diesem array wird geprüft, ob es bereits eine Zuordnung zu einem interen (bestätigten) Feldnamen gibt, wenn ja wird dieser verwendet (einschl. der zugehörigen Eigenschaften).

Existiert kein passender Eintrag, wird der übergebene Feldname in Kleinbuchstaben umgewandelt und Leerzeichen und andere ungültige Zeichen ersetzt.

Für noch nicht definierte Felder werden als Eigenschaften

Feldtyp (typ) =varchar(245),
Auto-Replace (repl) =1

verwendet.

Die Eigenschaft Auto-Replace (repl) wird bei der Übernahme der Daten interpretiert, 1 (allg. String-Codierung).

Erstellen der Shop-Tabelle

Zunächst wird der Name der nicht aktiven Shop-Tabelle abgefragt.

Diese wird wenn noch vorhanden gelöscht (DROP IF EXISTS).

Das CREATE TABLE wird dynamisch erstellt, dazu werden zunächst in einer Schleife alle Pflichtfelder generiert und anschl. in einer 2. Schleife alle Felder die soeben eingelesen wurden und nicht zu den Pflichtfeldern gehören, generiert.

Einlesen der Produkte

Nachdem die Felder passend zugeordnet sind und die Tabelle erstellt ist, werden alle weiteren Zeilen eingelesen und in die Datenbank eingetragen.

Dabei werden ungültige Zeilen (Produkte), die nicht den Bedingungen entsprechen übersprungen und als Fehler gezählt.

kein gültiger Produktlink zum Shop (deep_link1) und kein alternativer Link (affilinet_url)
keine bzw. keine gültige ProduktID (id)^²
fehlende image_id (nur bei Parameter - nur Produkte mit Bildern laden)
ungültige bzw. nicht vorhandene image_url (nur bei Parameter - nur Produkte mit Bildern laden)

Löschen der CSV-Datei (geplant)^³

Nach erfolgreichem Einlesen der Daten, wird die CSV-Datei nicht mehr benötigt.

Bearbeitung der Shop-Tabelle (mögliche Erweiterungen)

Wenn ggf. notwendig könnten jetzt noch Umsetzungen erfolgen, z.B.

Erstellung von ggf. sinnvollen Indizies
Zuordnung eigener Kategorien (cat_id)
Umsetzung von Textfeldern in IDs, z.B. Hersteller, Marken, Keywords^⁴

Shop-Tabelle aktiv setzen

Mit dem Switch bzw. Neueintrag, wird die Shop-Tabelle sofort aktiv.

Dies wirkt sich nicht nachteilig auf die Seitenladezeit der Übersichtsseiten aus, weil diese für die zeitintensive Zusammenstellung der Produkte (jeweils aus verschiedenen Shops) nicht auf die Shop-Tabellen zugreifen.

Nur wenn das Laden der Produkttabelle des Shops erfolgreich war, wird diese aktiv gesetzt.

Löschen der nicht mehr aktiven alten Shoptabelle

War das aktiv-Setzen erfolgreich, wird die alte – nicht mehr aktive Produkttabelle des Shops - ohne weitere Prüfung gelöscht.

Das Übertragen der Produkte in die "Gesamttabelle" und die Erstellung der Domaintabellen erfolgt in einem anderen CRON-Job (cron_prd_domain.php = CRON3), welcher erst nach Beendigung aller parallel laufender CRONs für das Shop-Update (cron.php = CRON 0 - 2) gestartet wird.

Tabellen

MySQL-Tabelle: TABLE_FIELD_LIST (ap_field_list)

Diese Tabelle enthält alle Produktfelder, die geprüft und zugeordnet sind. Nur Felder, die in dieser Tabelle eingetragen sind können für weitere Feldlisten und in Modulen verwendet werden.

Die Shop-Tabellen enthalten zusätzlich auch alle Felder, die noch nicht zugeordnet sind. Diese Felder können im Frontend nicht verwendet werden.

Diese Tabelle kann, wenn sie fehlt im admin über cron_check_db.php wiederhergestellt werden. Allerdings gehen dabei alle Eingaben seit der letzten Sicherung verloren.

Die Tabelle befindet sich deshalb im Hauptschema (afpark1) und sollte regelmäßig gesichert werden.

CREATE TABLE `ap_field_list`

CREATE TABLE `ap_field_list` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(245) default NULL,

`table_source` int(11) default '1',

`description` text,

`type` varchar(245) default NULL,

`default` varchar(245) default NULL,

`repl` int(1) default '0',

`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Feldname (name)

Der Feldname enthält den im gesamten Projekt verwendeten einheitlichen Feldnamen, die ggf. abweichend definierten Namen aus den verschiedenen Quellen werden über die Tabelle

TABLE_FIELD_ALIAS (ap_field_alias) bereits beim Import der Daten zugeordnet.

Anmerkung für Programmierer: - Feld repl ggf. verschieben

Das Feld repl muss ggf. in die Tabelle TABLE_FIELD_ALIAS (ap_field_alias) oder TABLE_FIELD_LIST_SOURCE (ap_field_list_source) verschoben werden, da sich die notwendige Umsetzung je nach Quelle unterscheiden könnte. Aktuell passt die Umsetzung jedoch für alle. Der Punkt ist nur interessant, wenn zusätzlich zu Affilinet weitere Datenquellen verwendet werden sollen.

Feldtyp (type)

Der Feldtyp enthält die Vorgaben für das CREATE TABLE, einschl. NOT NULL, wenn erforderlich.

Standard (default)

Der Standard enthält die Vorgaben für das CREATE TABLE, ohne "default".

Der Eintrag "NULL" wird wird entsprechend in default NULL umgesetzt.

Bei NULL wird kein default generiert, z.B. bei Textfeldern (type= text).

Auto-Replace (repl)

repl	Bedeutung	Umsetzung
0	keine Umsetzung	mysql_real_escape_string
1	Standard	+ utf8_decode und Standardumsetzungen (utf8-Codierungsfehler)
2	Marken, Hersteller	+ utf8 und Umsetzung für Marken, Hersteller (z.B. Felder mit ungültigem Inhalt nicht verwenden)^⁵
3	Preise	Umsetzung in gültige Zahlen, wenn nötig

MySQL-Tabelle: TABLE_FIELD_ALIAS (ap_field_alias)

Diese Tabelle enthält alle Aliasnamen mit Zuordnung der field_id (id aus der TABLE_FIELD_LIST (ap_field_list).

MySQL-Tabelle: TABLE_FIELD_LIST_SOURCE (ext_field_list_source)

Diese Tabelle enthält alle Produktfelder für alle eingelesenen Quellen, einschl. geprüfte und zugeordnete, sowie neue Felder. Diese Tabelle muss nicht extra gesichert werden.

Die Tabelle wird bei jedem Einlesen, bei Bedarf neu erstellt und neu gefüllt.

Sie dient, als Basis für die Zuordnung von neuen Feldern.^⁶ Diese werden dazu in den Tabellen oben eingetragen, danach ist diese Tabelle nur aus Performancegründen, für eine schnellere Zuordnung notwendig, der Zeitgewinn beträgt allerdings nur wenige Sekunden.

Es ist ggf. sinnvoll diese Tabelle aus Performancegründen, und um "verlorengegangene" Datenquellen zu bereinigen.

MySQL-Tabelle: TABLE_FIELD_LIST_DEST (ap_field_list_dest)

Diese Tabelle enthält alle Produktfelder (field_id) für die zu erstellenden Produkttabellen.

CREATE TABLE

CREATE TABLE `ap_field_list_dest` (

`id` int(11) NOT NULL auto_increment,

`field_id` int(11) default NULL,

`table_dest` int(1) default '1',

`last_update` timestamp NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`id`),

UNIQUE KEY `UK_DEST_FID` (`table_dest`,`field_id`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Zieltabelle (table_dest)

table_dest	Bedeutung	Verwendung
1	Standard	prd_products + prd_prducts_[domain_name]
2	Domaintabellen	dom_[dom_id]
3	Domain, Menüzuordnung	dom_menu_[dom_id]
4	Publishertabellen	pub_[pub_id] ⁷
5	Domain, Suchergebnisse⁸

Aus Sicherheitsgründen ist für die Tabelle der eindeutige Schlüssel

UNIQUE KEY `UK_DEST_FID` (`table_dest`,`field_id`)

definiert. Somit wird bei manuellen Eintragungen per SQL ein versehentliches doppeltes Eintragen des gleichen Feldes für eine Tabelle verhindert.

Alle Produkttabellen enthalten das eindeutige Feld id als Schlüsselfeld (Primary key), dieses Feld muss in den Listen nicht explizit aufgeführt werden, stört aber auch nicht, wenn es zusätzlich eingetragen wird, weil es aus Sicherheitsgründen (doppelter Felder) im Programmcode geprüft wird.

In Produkttabellen enthält dieses Feld die product_id.

Tabellen auf mehrere Datenbanken verteilen

Datenbanken auf dem selben Server

Um relativ unkompliziert - vor allem aus Übersichtsgründen, die Tabellen auf mehrere Datenbanken zu verteilen, ohne dabei den Programmcode überarbeiten zu müssen, besteht die Option einem User die Rechte für alle zuhehörigen Datenbanken zu übertragen. Auf dem bqr.de ist das so eimngerichtet.

Die Verteilung Tabellen erfolgt über die Zuordnung, wie in der Datei tablenames.php.

Die Datei tablenames.php ist im Frontend und im Backend identisch!

Ob diese Aufteilung ggf. auch Geschwindigkeitsvorteile bringt ist aktuell nicht abzuschätzen. Mehr Übersichtlichkeit und ein einfacheres Sichern der Stammdaten, sowie der manuell eingepflegter Daten, weil sich diese dann in einer getrennten Datenabank befinden, ist in jedem Fall ein Vorteil.

Die Produkttabellen der Shops befinden sich aktuell im Schema afpark1c, alternativ dazu kann auch ein sprechender Name verwendet werden, z.B. afpark_shop.

1Für das Laden der gezippten CSV-Listen ist der Programmcode von PL, ab Version 3.x.0 eingebaut

2Affilinet verwendet eine eindeutige ProductID für alle Produkte, diese ID wird im Projekt auch intern für die Zuordnung verwendet, d.h. wenn zusätzliche Quellen - mit eigenen Nummernkreisen dazukommen, muss dann ggf. eine entsprechend große Zahl addiert werden, damit die Produkte eindeutig bleiben.
Bei Quellen die keine ID mitbringen, müsste beim Eintragen in die Tabellen eine eindeutige ID generiert werden. Die Prüfung der ID bzw. das Generieren der ID in Abhängigkeit von der Quelle, müsste dann entsprechend noch programmiert werden.

3Pano fragen: Das Bereinigen der CSV-Dateien passiert irgendwie von alleine.

4Was hier sinnvoll, notwendig und "bezahlbar" ist, wird während der Optimierung noch gepüft. Aktuell sind an dieser Stelle keine Umsetzungen eingetragen.

5Bei der Umsetzung der Zuordnung von Ids für diese Felder, ist diese spezielle Behandlung nicht mehr notwendig!

6Die Zuordnung erfolgt bis auf weiteres nur durch den Programmierer, direkt in SQL.

7Die Idee Publisher-Tabellen zu verwenden wurde aus Performancegründen verworfen. Die Datenbank belegte mit Publisher-Tabellen über 60GB und wurde dabei sehr viel langsamer.
Die geplante Verwendung wurde durch die Tabelle prd_deeplinks (mit Switch) und die Korrektur des gültigen Publisher bei Aufruf des Links (in der Datei: direkt-zum-produkt.php), sowie den prd_products – pro Domain ersetzt.

8Aktuell nicht umgesetzt.