Object Relational Mapping, aus Feldern werden Attribute und umgekehrt
Viele Anwendungen benötigen persistente Daten und in vielen Fällen sind die Daten für namentlich benannte Einträge als Schlüssel-Werte-Paare definiert. Nennen wir einen namentlich benannten Eintrag address, so hätten wir beispielsweise eine Datenstruktur wie folgt:
my %address =(
name => '',
vname => '',
ort => '',
plz => '',
str => '',
);
Gut zu sehen, das Datenmodell ist nach dem Muster Entity, Attribute, Value (EAV) aufgebaut, Entity ist der Name des Eintrags, im Beispiel address. Eine solche Datenstruktur können wir auch als Objekt auffassen, ein Objekt nicht als Instanz einer Klasse sondern einfach nur als eine Ansammlung von Daten.
Nun können derartige Objekte verschiedene Attribute haben, sowohl namentlich als auch in deren Anzahl. Das Datenobjekt für den hier vorliegenden Artikel hat z.B. unter anderem die Attribute title, descr, body und der Name des Eintrags, Entity ist /dal.html.
Betrachte untenstehendes Insert-Statement:
my %book = ( author => 'Martin Selber', title => 'Mit Spule Draht und Morsetaste' );
@fields = keys %book;
$dbh->do(qq(
insert into books (@{[join ',', map{$dbh->quote_identifier($_)}@fields]})
values(@{[join ',', map{"?"}1..scalar @fields]}
)),{}, @book{@fields});
Herzstück ist ein sogenannter Hash-Slice. Bekanntlich ist ein Hash ein assoziatives Array, welches zu einem besimmten Schlüssel einen bestimmten Wert liefert. Ein Hash-Slice nun, liefert zu einer Liste von Schlüsseln eine Liste mit den dazugehörigen Werten in derselben Reihenfolge. Wichtig ist in diesem Zusammenhang, daß für fehlende Eigenschaften im Tabellenentwurf ein Default-Wert definiert ist:
$dbh->do(q(
create table if not exists books(
id int(10) not null auto_increment,
isbn varchar(255) not null default '',
author varchar(255) not null default '',
title varchar(255) not null default '',
primary key (id)
)
)) or die $@;
So wird jeder gegebene Wert in das der Eigenschaft entsprechende (gleichnamige) Feld eingefügt. Über diesen Mechanismus lässt sich eine allgemeine Insertfunktion definieren:
# Eine Methode für die Factory
sub insert{
my $self = shift;
my $dbh = shift;
my $tabn = shift;
my %hunt = @_;
my @fields = keys %hunt;
$dbh->do(qq(
INSERT INTO @{[$dbh->quote_identifier($tabn)]} (@{[join ',', map{$dbh->quote_identifier($_)}@fields]})
VALUES( @{[join ',', map{$dbh->quote($_)}@hunt{@fields}]} )
));
}
# Aufruf mit einer beliebigen Instanz
$mock->insert( $dbh, "books",
title => 'Zwei Freunde und hundert Ideen',
author => 'A. Golovin, Vladimir'
);
Definition Data-Abstraction-Layer: Der DAL vermittelt zwischen dem Speicherort und der darin abgelegten Datenstruktur.
In Perl ist die Datenstruktur ein gewöhnlicher Hash mit Schlüssel-Werte-Paaren für eine bestimmte Entity. Aus Perl-Sicht erfolgt der Zugriff auf die Daten nicht etwa über einen Zugriff auf eine Datei oder über ein SQL-Statement, vielmehr erfolgt der Zugriff über die vom DAL gelieferte Datenstruktur.
Beispiel zum Einbinden verschiedener DALs, untenstehender Code verdeutlicht die Abstraktion:
# Speicherort Datei
my $dal = FastEAV->new( file => '/path/personen.data');
# Speicherort MySQL
my $dal = MySQLEAV->new( tabn => 'personen', base => 'webdaten' );
Der DAL stellt, nach der Bildung der Instanz in $dal, Methoden zur Verfügung für den Zugriff auf die Daten, Erstellen, Einfügen, Ändern, Speichern, Löschen, ein paar Beispiele untenstehend:
$dal->checkin('addr_1', %address);
$dal->update('addr_1', ort => 'Henneberg');
$dal->delete('addr_1');
$dal->write();
Allein diese Methoden, bzw. deren Anwendung, zeigen, dass im Programmcode keinerlei FileHandle oder DataBaseHandle zum Einsatz kommen müssen. Für Perl ist der DAL transparent, das heißt: Der Speicherort ist für das Programm uninteressant, es wird einfach nur auf die Daten zugegriffen und es werden Methoden angewandt.
Merke: Wenn die verschiedenen Layer allesamt die Methoden namentlich gleich zur Verfügung stellen, ist der DAL austauschbar mit einem Minimum an Programmieraufwand.
Natürlich gibt es Unterschiede, was die Performance, den RAM- und CPU-Bedarf betrifft. Der größte Overhead entsteht in dem Moment, wenn die Verbindung zu MySQL aufgebaut wird, dass kann in einer Webanwendung zu einer merklichen, jedoch nicht wesentlichen Verzögerung führen. Demgegenüber deutlich schneller ist das Erstellen eines DateiHandles. Eine Datei muss jedoch komplett deserialisiert werden, bevor die Datenstruktur im RAM zur Verfügung steht. Mit einem zweckmäßigen Serializer, kurzum als Richtwert: Das Einlesen einer Datei mit einer Größe bis zu 5MB einschließlich der Erstellung der Datenstruktur als Hash braucht auf einer heutigen Hardware weniger Zeit, als der Aufbau einer Verbindung zu MySQL.
Nachdem die Verbindung zu MySQL jedoch einmal aufgebaut ist, das DataBaseHandle liegt vor, ist der Zugriff auf die Daten sehr schnell, die Zeiten liegen im Millisekundenbereich, auch dann, wenn in einer Tabelle bspw. 4 Millionen Einträge vorliegen. Das Geheimnis für diese Performance ist die Wirksamkeit eines Index, der in der WHERE-Klause dem Feld entsprechend greift.
Im Folgenden beschreibe ich die Vorgehensweise zum Entwickeln eines einfachen Data-Abstraktion-Layer zum Speichern von Hashes nach dem EAV-Muster in MySQL. Auch hier werden die Daten nach einem bestimmten Algorithmus serialisiert, denn aufgrunddessen, dass die Namen der Attribute nicht vorhersehbar sind, ist es nicht möglich, den Attributen entsprechend gleichnamige Tabellenfelder anzulegen.
Die Tabelle für einen einfachen Data-Abstraction-Layer, den wir auch als ORM (Object Relational Mapping) bezeichnen können, hat lediglich drei Felder für Entity, Attribute, Value. Diese Tabelle ist nicht normalisiert, verfügt jedoch über einen Index, welcher für performante Abfragen sorgt. Sie ist so aufgebaut, dass sowohl UTF-8-Kodierte Zeichenketten als auch reine Binaries nebeneinander darin gespeichert werden können.
# Tabelle wird ggf. neu erstellt
sub _create{
my $self = shift;
my $q = qq(
CREATE TABLE IF NOT EXISTS $self->{TABN}(
`ent` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`att` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`val` text COLLATE utf8_bin,
PRIMARY KEY (`ent`,`att`),
KEY ent(ent),
KEY att(att)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
);
$self->{DBH}->do($q);
}
Die Tabelle mit den drei Spalten ent, att, val (EAV) wird angelegt. Die Collation wird auf binary gesetzt, damit wird auch in den Schlüsseln zwischen Groß- und Kleinschreibung unterschieden. So ist beispielsweise eine ent = 'ADDR' neben ent = 'addr' möglich, das sind also unterschiedliche Einträge. Dasselbe Verhalten gilt sinngemäß auch für die anderen beiden Datenfelder.
Die festgelegte Collation passt zur Zeichenkodierung UTF-8. Perl kann damit vollständig zeichenorientiert mit der Datenbank kommunizieren und z.B. die MySQL-Funktionen UPPER(), LOWER() nutzen. Sofern Perl jedoch nur byteorientiert mit der Datenbank kommuniziert, ist es auch möglich, reine Binaries in das Textfeld einzufügen (GIF, JPEG, PDF...).
sub new{
my $class = shift;
my %cfg = (
host => 'localhost',
port => 3306,
utf8 => 0, # Zeichenorientierung ein/aus
user => '', # Benutzername
pass => '', # Passwort
base => '', # Name der DB
tabn => '', # Name der Tabelle
create => 0, # Die Tabelle wird bei Bedarf angelegt
@_);
return eval{
croak "Argument 'tabn' is required" unless $cfg{tabn};
croak "Argument 'base' is required" unless $cfg{base};
my $self = bless{}, $class;
$self->{DBH} = $self->_dbh(%cfg) or croak $@;
# Die .pm wird gelockt damit DB-Zugriffe atomar sind
my $file = $INC{"$class.pm"};
if($file && -e $file){
my $fh = IO::File->new;
$fh->open( $file, 'r');
flock($fh, LOCK_EX) or carp "Your system does not support flock, all procs are not atomar.";
}
# maskiere den Namen der Tabelle
$self->{TABN} = $self->{DBH}->quote_identifier($cfg{tabn});
if($cfg{create}){ $self->_create; }
$self;
};
}
# Data Base Handle
sub _dbh{
my $self = shift;
my %cfg = @_;
my @utf8en = $cfg{utf8} ? (mysql_enable_utf8 => 1) : ();
return
DBI->connect_cached(
"DBI:mysql:$cfg{base}:$cfg{host}:$cfg{port}",
$cfg{user}, $cfg{pass},
{RaiseError => 1, PrintError => 0, @utf8en}
);
}
Der Konstruktor erstellt die Instanz der Klasse, dabei wird die Verbindung zur DB hergestellt und der Name der Tabelle wird MySQL-gerecht maskiert. RaiseError wird auf 1 gesetzt, damit werden alle möglichen Fehler in den Status einer Exception erhoben. Das hat den Vorteil einer relativen Code-Vereinfachung, es erübrigen sich explizite Abfragen, ob etwa ein Statement fehlgeschlagen ist und es müssen keine Texte für eigene Fehlermeldungen erstellt werden.
### PUBLIC METHODS ########################################################
# Alle Einträge werden gelöscht und neu eingetragen
# Multiple Insert
sub checkin{
my $self = shift;
my $ent = shift;
my %av = @_;
return eval{
$self->delete($ent);
my @vals = ();
foreach my $att(keys %av){
push @vals, sprintf("(%s,%s,%s)", $self->{DBH}->quote($ent), $self->{DBH}->quote($att), $self->{DBH}->quote($av{$att}));
}
my $q = qq(INSERT INTO $self->{TABN} (ent,att,val) VALUES @{[join ',', @vals]} );
$self->{DBH}->do($q);
};
}
# Attribute, Value für einen Eintrag als hashref
sub checkout{
my $self = shift;
my $ent = shift;
return eval{
if(! exists $self->{STH}{CHECKOUT}){
$self->{STH}{CHECKOUT} = $self->{DBH}->prepare("SELECT att, val FROM $self->{TABN} WHERE ent=?");
}
$self->{STH}{CHECKOUT}->execute($ent);
my $aref = $self->{STH}{CHECKOUT}->fetchall_arrayref({});
my %hunt = map{ $_->{att} => $_->{val} } @$aref;
scalar keys %hunt ? \%hunt : undef;
};
}
# Anzahl der Entities oder Liste der Entities
sub count{
my $self = shift;
return eval{
my $q = qq(SELECT DISTINCT ent FROM $self->{TABN});
my @ents = map{ $_->[0] } @{$self->{DBH}->selectall_arrayref($q)};
wantarray ? @ents : scalar @ents;
};
}
sub delete{
my $self = shift;
my $ent = shift;
return eval{
if(! exists $self->{STH}{DELETE}){
$self->{STH}{DELETE} = $self->{DBH}->prepare("DELETE FROM $self->{TABN} WHERE ent=?");
}
$self->{STH}{DELETE}->execute($ent);
};
}
# vorhandende Keys überschreiben
# neue Keys eintragen
sub update{
my $self = shift;
my $ent = shift;
my %av = @_;
my $legacy = $self->checkout($ent) || return;
my %res = (%$legacy, %av);
$self->checkin($ent, %res);
}
# DUMMY
# Macht diesen DAL kompatibel, zu anderen DALs welche die Methode
# write implementieren
sub write{1}
Die Methoden arbeiten mit Prepared Statements. Das jeweilige Statement wird beim ersten Aufruf der jeweiligen Methode erstellt und als Attribut im der Instanz gespeichert. Das erhöht die Performance bei mehreren aufeinanderfolgenden Methodenaufrufen und die übergebenen Argumente werden MySQL-gerecht maskiert, so dass SQL-Injektionen nicht möglich sind.
Die EAV-Tabelle ist nicht normalisiert denn das ist nicht das eigentliche Ziel eines DAL, was darin besteht, namentlich benannte Datenobjekte (Entity) mit beliebigen Attributen in beliebiger Anzahl persistent zu machen. Wobei: Die Struktur der Datenobjekte wird einzig und allein vom Programmcode bestimmt.
Dennoch ist es möglich, die Mächtigkeit von MySQL für gezielte Abfragen zu nutzen, nämlich dann, wenn alle Datenobjekte stets dieselbe Struktur, sprich, dieselben Attribute haben. Als Beispiel die Attribute name, vname, ort, parent sozusagen als Quasi-Datenfelder. Betrachte untenstehendes SQL-Statement:
SELECT
a.ent as entity,
`name`.val as `name`,
`vname`.val as `vname`,
`ort`.val as `ort`
FROM eav a
JOIN `eav` `name` USING(ent)
JOIN `eav` `vname` USING(ent)
JOIN `eav` `ort` USING(ent)
WHERE a.val = 'foo' # WERT 'foo' für WHERE parent => 'foo'
AND `name`.att = 'name' AND `vname`.att = 'vname'
AND `ort`.att = 'ort'
AND a.att = 'parent' # FELD 'parent' für WHERE parent => 'foo'
Über SELF-JOINs wird eine Relation rekonstruiert und obenstehende Abfrage liefert ein auf Felder ausgerichtetes Ergebnis, wenn die in der Tabelle enthaltenen Objekte die Attribute name, vname, ort, parent besitzen und diese mit Werten bestückt sind.
Der SELF-JOIN ist einfach zu verstehen, die Tabelle wird sooft mit sich selbst gejoint, wie Quasi-Felder auf Attribute gemappt werden. Quasi-Felder sind name, vname, ort. Das Quasi(WHERE parent = 'foo') wird erzeugt durch zwei Bedingungen in WHERE, oben gekennzeichnet. Als Beschleuniger für die Abfrage dienen die Indizies KEY ent(ent), KEY att(att). Dass diese Keys greifen, zeigt ein dem Statement vorangestelltes explain.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | a | ref | PRIMARY,ent,att | att | 386 | const | 1 | Using where |
| 1 | SIMPLE | name | eq_ref | PRIMARY,ent,att | PRIMARY | 772 | myweb.a.ent,const | 1 | Using where |
| 1 | SIMPLE | vname | eq_ref | PRIMARY,ent,att | PRIMARY | 772 | myweb.a.ent,const | 1 | Using where |
| 1 | SIMPLE | ort | eq_ref | PRIMARY,ent,att | PRIMARY | 772 | myweb.vname.ent,const | 1 | Using where |
Die EAV-Tabelle für diese Abfrage hat 8_719_848 Einträge, das sind fast 9 Millionen Zeilen, die Ergebnismenge jedoch hat nur 4 Zeilen, siehe Spalte rows.
Erweiteren wir unseren DAL um die folgende Methode, welche den JOIN zusammenbaut:
# Diese Methode erwartet als erstes Argument
# eine Array-Ref mit den gewünschten Feldern
# Das zweite und dritte Argument ist ein Schlüssel und ein Wert
# für die Abfrage-Bedingung, welcher Wert für das
# gemeinsame Attribut gegeben sein soll
sub entities_for_fields{
my $self = shift;
my $fields = shift;
my $attname = shift;
my $attvalue = shift;
return eval{
my @select = ();
my @join = ();
my @where = ();
foreach my $field(@$fields){
my $unfield = $field;
$field = $self->{DBH}->quote_identifier($field);
push @select, sprintf("%s.val as %s", $field, $field);
push @join, sprintf("JOIN %s %s USING(ent)", $self->{TABN}, $field);
push @where, sprintf("%s.att = %s", $field, $self->{DBH}->quote($unfield));
}
push @where, sprintf("a.att = %s", $self->{DBH}->quote($attname));
my $statement = qq(
SELECT
a.ent as entity,
).join(",\n", @select).qq(
FROM $self->{TABN} as a
).join("\n", @join)."\n".sprintf("WHERE a.val = %s\n", $self->{DBH}->quote($attvalue))."AND ".join(' AND ', @where);
$self->{DBH}->selectall_arrayref($statement, {Slice => {}});
};
}
Der Aufruf dieser Methode liefert untenstehendes Ergebnis:
my $e = MySQLEAV->new(%cfg) or die $@;
$e->checkin('d1', parent => 'foo', name => 'Rost', vname => 'Rolf', ort => 'Oppenheim');
$e->checkin('d2', parent => 'foo', name => 'Lottermann', vname => 'Erwin', ort => 'Henneberg');
$e->checkin('addr', parent => 'foo', name => 'Hammel', vname => 'Horst', ort => 'Hammelburg');
# Selectiere alle Einträge mit dem parent => 'foo'
# Und erfasse die Felder name, vname, ort
print Dumper $e->entities_for_fields(['name','vname','ort'], parent => 'foo');
# Ergebnis
$VAR1 = [
{
'entity' => 'd1',
'ort' => '',
'name' => '',
'vname' => ''
},
{
'entity' => 'd2',
'ort' => '',
'name' => '',
'vname' => ''
},
{
'entity' => 'addr',
'ort' => '',
'name' => '',
'vname' => ''
}
];
Bei diesem Statement wird der Index genutzt und damit ist die Abfrage auch performant. ORM bedeutet in diesem Fall: Das Mapping beschreibt eine Transformation, aus Attributen werden Datenfelder und umgekehrt.
Anwendungen sind mit einem solchen DAL schnell entwickelt. Die Datenfelder werden einzig und allein vom Programmcode bestimmt, in den verlinkten Beispielen sind die Atribute (Datenfelder) mit JavaScript festgelegt.
Datenschutzerklärung: Diese Seite dient rein privaten Zwecken. Auf den für diese Domäne installierten Seiten werden grundsätzlich keine personenbezogenen Daten erhoben. Das Loggen der Zugriffe mit Ihrer Remote Adresse erfolgt beim Provider soweit das technisch erforderlich ist. sos@rolfrost.de.