Webbasierte Adressdatenbank

Die DBI und DBD Module

Architektur
Das DBI (Data Base Interface) Modul stellt eine API (Application Programming Interface) für den Datenbankzugriff unter Perl dar. Dabei ist es von der verwendeten Datenbank unabhänging. Dafür sind dann die unterschiedlichsten DBD (Data Base Driver) Module zuständig. Damit stellt DBI nur eine Standardschnittstelle und den (Software-) Rahmen zur Verfügung, innerhalb dessen sich die Treiber bewegen 

           |<- DBI-Architektur ->|
                       .-.   .--------------.   .-------------.
    .----------.       | |---| XYZ-Treiber  |   | XYZ-Engine  |
    | DBI-API- |       |S|   '--------------'   '-------------'
    | Methoden |  |A|  |w|   .--------------.   .-------------.
    | nutzendes|--|P|--|i|---|Oracle-Treiber|---|Oracle-Engine|
    | Perl-    |  |I|  |t|   '--------------'   '-------------'
    | Skript   |       |c|...
    '----------'       |h|... Weitere Treiber
                       | |...
                       '-'

Das Perlskript greift über das API auf die Funktionen und Variablen des DBI Modules zu. Der Switch ist für die Ausführung des eigentlichen Codes verantwortlich und spricht den benötigen DBD an, welcher dynamisch geladen wird.

Verwendung des DBI Moduls
Zuerst definiert man die notwendigen Variablen (die Benutzung von strict ist wie immer empfehlenswert). Im nachfolgenden Code verwenden wir das DBD::CSV Modul um auf eine Textdatei als Datenbank zuzugreifen, welche die Daten mit Komma getrennt enthält. Wechselt man die Datenbank, so sind in der Regel nur diese Zeilen an das entsprechende DBD Modul anzupassen. Gelegentlich ist auch eine Überarbeitung des SQL Codes notwendig, da nicht alle Datenbanken den gleichen SQL Dialekt sprechen. 

    use DBI;
    use strict

    my $DB_NAME     = "textfile"; 
    my $DB_DIR      = "/pfad/zu/";
    my $DB_DSN      = "DBI:CSV:f_dir=$DB_DIR$DB_NAME";
    my $DB_USER     = "";
    my $DB_PASSWD   = "";

Schreiben von Daten in die Datenbank
Angenommen wir wollten aus einem File, mit Komma separierten Daten, die Daten in eine Datenbank übertragen, könnte das ganze so aussehen: 

	foreach $line (<FILE>) {
      chomp $line;
      ($eins, $zwei, $drei, $vier) = split(/,/,$line);  
      $sql = qq[ 
          insert into $table (spalte_a, spalte_b, spalte_c, spalte_d)
          values($eins,$zwei,$drei,$vier)
      ];

      $dbh = DBI->connect($dsn,'login','password')
      $sth = $dbh->prepare($sql);
      $sth->execute;
      $dbh->disconnect;
    }

Wir lesen eine Zeile des Files ein, trennen die Daten mit split, definieren eine SQL Anweisung  $sql=qq[..] , verbinden uns mit der Datenbank  $dbh=DBI->connect... , bereiten die SQL Anweisung vor  $sth=$dbh->prepare... , führen sie aus  $sth=$dbh->execute  und trennen uns schlieslich wieder von der Datenbank  $dbh->disconnect . Das machen wir in der Schleife für jede Zeile des einzulesenden Files. Hat das File so ca 200.000 Zeilen und bei der Datenbank handelt es sich um MySQL (die ist recht schnell), dauert das ca eine Stunde, mit Oracle kann das Ganze schon einen halben Tag dauern. Unsere Methode ist also nicht sehr performant. Man sollte sich also merken, alles was nicht unbedingt in die Schleife muß, sollte außerhalb geschehen. Das Verbinden mit der Datenbank und das Trennen brauchen wir nur einmal: 

    $dbh = DBI->connect($dsn,'login','password');
   
    foreach $line (<FILE>) {
      chomp $line;
      ($eins, $zwei, $drei, $vier) = split(/,/,$line);
      $sql = qq]
          insert into $table (spalte_a, spalte_b, spalte_c, spalte_d)
          values($eins,$zwei,$drei,$vier)
      ];

      $sth = $dbh->prepare($sql);
      $sth->execute;
    }

    $dbh->disconnect;

Das reduziert die Ausführungszeit etwa um die Hälfte. Wir können noch mehr optimieren, wenn wir die Definition des SQL Statements und die Anweisung  $sth=$dbh->prepare...  aus der Schleife nehmen und das  $sth=$dbh->execute  Komando modifizieren: 

    $dbh = DBI->connect($dsn,'login','password');
    $sql = qq[
        insert into $table (spalte_a, spalte_b, spalte_c, spalte_d)
        values(?, ?, ?, ?)
    ];
    
    $sth = $dbh->prepare($sql);
    
    foreach $line (<FILE>) {
        chomp $line;
        ($eins, $zwei, $drei, $vier) = split(/,/,$line);
        $sth->execute($eins, $zwei, $drei, $vier);
    }

    $dbh->disconnect;

Beim SQL Statement übergeben wir jetzt nicht sofort die Werte, sondern verwenden Platzhalter. Erst beim Ausführen  $sth=$dbh->execute  übergeben wir die Werte. Dies reduziert die Ausführungszeit nochmal um ca 30 Prozent.

Lesen von Daten aus der Datenbank
Auch das Lesn aus der Datenbank ist recht einfach. Zuerst Verbinden, SQL Statement definieren, Ausführen und dann in einer Schleife solgange Daten lesen, bis keine mehr zurückgegeben werden;  fetchrow_...  erledigt das für uns. 

    $dbh = DBI->connect($dsn, 'login', 'passwd');

    $sql = qq[
        select * from $table
    ];

    $sth = $dbh->prepare($sql);
    $sth->execute;

    while (@row = $sth->fetchrow_array) {
        print "@row\n";
    }

    $dbh->disconnect;

Wir lesen hier eine Datenbanktabellenzeile in ein Array ein. Effizienter ist es, wenn wir eine Arrayreferenz verwenden: 

    $dbh = DBI->connect($dsn, 'login', 'passwd');

    $sql = qq[
        select * from $table
    ];

    $sth = $dbh->prepare($sql);
    $sth->execute;

    while ($row = $sth->fetchrow_arrayref) {
        print "@$row\n";
    }

    $dbh->disconnect;

Man kann natürlich auch die Daten in eine Hashreferenz einlesen, welche die Spaltennamen als Schlüssel benutzt: 

    $dbh = DBI->connect($dsn, 'login', 'passwd');

    $sql = qq[
        select * from $table
    ];

    $sth = $dbh->prepare($sql);
    $sth->execute;

    while ($ref = $sth->fetchrow_hashref) {
        foreach $key (keys %{$ref}) {
           print "$ref->{$key}, ";
        }
        print "\n";
    }

    $dbh->disconnect;

Anstelle des Vorbereitens der SQL Anweisung und Ausführen der SQL Anweisung, kann man das Ganze auch in einem Befehl ausführen: 

    $dbh = DBI->connect($dsn, 'login', 'passwd');

    $sql = qq[
        select * from $table
    ];

    $dbh->do($sql);

    while ($ref = $sth->fetchrow_hashref) {
        foreach $key (keys %{$ref}) {
           print "$ref->{$key}, ";
        }
        print "\n";
    }

    $dbh->disconnect;

Update von Datensätzen
Hier soll nur ein kleines Beispiel erwähnt werden, welches Platzhalter verwendet um die Syntax zu verdeutlichen. 

    $dbh = DBI->connect($dsn,'login','password');
    $sql = qq[
        update $table set spalte_b = ?, spalte_c = ?, spalte_d = ?
        where spalte_a = ?
    ];
    
    $sth = $dbh->prepare($sql);

    foreach $line (<FILE>) {
        chomp $line;
        ($eins, $zwei, $drei, $vier) = split(/,/,$line);
        $sth->execute($zwei, $drei, $vier, $eins);
    }

    $dbh->disconnect;

Fehlerbehandlung
Bei allen Codestücken wurde die Fehlerbehandlung nicht berücksichtigt, was in dem einen oder anderen Fall zu Problemen führen kann. Besser ist es, man fügt bei allen DBI Aufrufen ein  or die  Konstrukt hinzu: 

    $dbh = DBI->connect($dsn,'login','passwd') || die $DBI::errstr;
    $sth = $dbh->prepare($sql) || die $dbh->errstr;
    $sth->execute || die $sth->errstr;

Es gibt aber eine schönere Lösung. Jedes DBI Handle hat ein boolsches Attribut:  RaiseError . Wenn wir dieses auf wahr setzen, wird das Skript bei einem Fehler vom DBI Handle gestoppt und die entsprechende Fehlermeldung wird ausgegeben. Am besten man setzt dieses Attribut beim Verbinden mit der Datenbank: 

    $dbh = DBI->connect($dsn,'login','passwd', 
                { RaiseError => 1 });

Wir können das natürlich auch nachholen, wenn die Verbindung zur Datenbank bereits hergestellt ist: 

    $dbh->{RaiseError} = 1;

Literaturhinweise:

Zurück zum Anfang dieses Projekts.