Firebird UDR mit FreePascal

[martin.koeditz]

Guten Morgen,

ich benötige etwas Unterstützung im Bereich UDR / FreePascal. Ich möchte den Levenshtein-Algorithmus als UDR implementieren. Leider bin ich nicht der Pascal-Experte und hänge an einer Stelle.

Dies ist der relevante Auszug der UDR:

Code: Alles auswählen

unit SynDeskLevenshtein;

interface

uses Firebird, SysUtils, Math;

const
  vcFb = 32765;

type
  IncInMessage = record
    v1: record       // Erste Zeichenkette
      Length: Word;
      Value: array [0..vcFb - 1] of AnsiChar;
      Null: WordBool;
    end;
    v1Null: WordBool;
    v2: record       // Zweite Zeichenkette
      Length: Word;
      Value: array [0..vcFb - 1] of AnsiChar;
      Null: WordBool;
    end;
    v2Null: WordBool;
  end;

  IncInMessagePtr = ^IncInMessage;

  IncOutMessage = record
    Result: integer;
    resultNull: wordbool;
  end;

  IncOutMessagePtr = ^IncOutMessage;

  IncFunction = class(IExternalFunctionImpl)
    procedure dispose(); override;

    procedure getCharSet(status: iStatus; context: iExternalContext;
      Name: pansichar; nameSize: cardinal); override;

    procedure Execute(status: iStatus; context: iExternalContext;
      inMsg: Pointer; outMsg: Pointer); override;
  end;

  IncFactory = class(IUdrFunctionFactoryImpl)
    procedure dispose(); override;

    procedure setup(status: iStatus; context: iExternalContext;
      metadata: iRoutineMetadata; inBuilder: iMetadataBuilder;
      outBuilder: iMetadataBuilder); override;

    function newItem(status: iStatus; context: iExternalContext;
      metadata: iRoutineMetadata): IExternalFunction; override;
  end;


implementation

procedure IncFunction.dispose();
begin
  Destroy;
end;

procedure IncFunction.getCharSet(status: iStatus; context: iExternalContext;
  Name: pansichar; nameSize: cardinal);
begin
end;

procedure IncFunction.Execute(status: iStatus; context: iExternalContext;
  inMsg: Pointer; outMsg: Pointer);
var
  xInput: IncInMessagePtr;
  xOutput: IncOutMessagePtr;
  s1: string;
  s2: string;
begin
  xInput := IncInMessagePtr(inMsg);
  xOutput := IncOutMessagePtr(outMsg);

  s1 := xInput^.v1.Value;
  s2 := xInput^.v2.Value;

  xOutput^.resultNull := xInput^.v2.Null;
  xOutput^.Result := Length(s1) + Length(s2);   // Länge von s2 ist immer 0
end;


procedure IncFactory.dispose();
begin
  Destroy;
end;

procedure IncFactory.setup(status: iStatus; context: iExternalContext;
  metadata: iRoutineMetadata; inBuilder: iMetadataBuilder; outBuilder: iMetadataBuilder);
begin
end;

function IncFactory.newItem(status: iStatus; context: iExternalContext;
  metadata: iRoutineMetadata): IExternalFunction;
begin
  Result := IncFunction.Create;
end;

end.

In der DB registriere ich diese und führe sie aus:

Code: Alles auswählen

create function Inc (
    v1 varchar(100),
    v2 varchar(100)
) returns integer
    external name 'SynDeskUDR!syndesk_levenshtein'
    engine udr;
    
select inc('bklm', 'test') from rdb$database;

Das Ergebnis gibt mir immer nur die Anzahl der Zeichen für den ersten Parameter aus. Was mache ich falsch?

Vielen Dank für eure Mühen
Martin

[martin.koeditz]

Der Vollständigkeit halber habe ich das Lazarus-Projekt online gestellt.
https://github.com/MartinKoeditz/SynDeskUDR

[martin.koeditz]

Dank des Artikels (https://www.ibase.ru/files/firebird/udr.pdf) habe ich die Ursache gefunden.

Statt

Code: Alles auswählen

IncInMessage = record
    v1: record       // Erste Zeichenkette
      Length: Word;
      Value: array [0..vcFb - 1] of AnsiChar;
      Null: WordBool;
    end;
    v1Null: WordBool;
    v2: record       // Zweite Zeichenkette
      Length: Word;
      Value: array [0..vcFb - 1] of AnsiChar;
      Null: WordBool;
    end;
    v2Null: WordBool;
  end;

muss der Code so lauten:

Code: Alles auswählen

IncInMessage = record
    v1: record       // Erste Zeichenkette
      Length: Smallint;
      Value: array [0..vcFb - 1] of AnsiChar;
    end;
    v1Null: WordBool;
    v2: record       // Zweite Zeichenkette
      Length: Smallint;
      Value: array [0..vcFb - 1] of AnsiChar;
    end;
    v2Null: WordBool;
  end;          

Dann funktioniert alles wie erwartet.

[bfuerchau]

Und wie sind die Ergebnisse?
Performance?
Ab welchem FB Release verwendbar?

[martin.koeditz]

Ausgangssituation:
Tabelle mit 20.860 Datensätzen.

Abfrage:

Code: Alles auswählen

select media_name, Levinshtein_Distance('K44-H54-AR-K-BP-W054-03-fH- Wannenbad EG.pdf', M.MEDIA_NAME)
from tbl_media m
order by 2;

Ergebnis:
Prepared: 0,124 sek.
Processed: 1.862 sek.

Beim Levenshtein-Algorithmus steigt die Verarbeitungszeit im Quadrat zur Länge der Zeichenkette. Kürzere Vergleiche laufen also nochmals deutlich schneller ab.

Ursprünglich hatte ich die Implementierung auf Basis der DB selbst gemacht (vgl. https://www.bafami.de/firebird/levenshtein_1.php). Die Laufzeit für die o.a. Abfrage betrug hier jedoch fast 2 Minuten.

[martin.koeditz]

Eins hatte ich noch vergessen: UDRs werden ab Firebird 3.0 unterstützt. Für die Vorversionen müsste dann eine UDF geschrieben werden. Da 2.5 aber EOL ist...

[martin.koeditz]

Ich habe die UDR nun im produktiven Einsatz. Auf einem "richtigen" Server läuft diese noch deutlich schneller. Dies ist allerdings ein Linux-System. Eventuell macht das ja auch nochmal einen Unterschied.

Ergebnis:
Prepared: 0,103 sek.
Processed: 0,915 sek.

[vr2]

Interessant!

In Deinem zugehörigen github-Projekt https://github.com/MartinKoeditz/SynDeskUDR wird ein UdrInc benutzt, was ich in SynDeskLevenshtein.pas nicht finde. Ist das aus einer Vorversion? So wie es dort steht, dürfte das nicht compilieren, oder ich verstehe das "UdrInc in 'SynDeskLevenshtein.pas';" in der SynDeskUDR.lpr nicht richtig.

Implementierung mit DB-Bordmitteln https://www.bafami.de/firebird/levenshtein_1.php: So wie es dort umgesetzt wurde, sieht das sehr unperformant aus, wegen der häufigen update Statements. Das wäre evtl ein klassischer Anwendungsfall für positioned updates mittels Cursor.

Für die Levenshtein-Distanz braucht man keinen DB-Kontext. UDRs laufen aber immer im DB-Kontext, auch wenn Du ihn nicht nutzt. In diesem Fall wird eine veraltete udf Größenordnungen schneller sein, da eben nicht bei jedem Aufruf erneut der DB-Kontext bereitgestellt wird. Das ist mir schon seit FB 3 aufgefallen und wäre einen Feature Request wert (UDR ohne DB-Kontext). Mit udfs konnte man ohne Probleme den Server abschießen, wenn sie schlecht programmiert waren, und daher ist es richtig, dass sie aus dem Verkehr gezogen wurden bzw seit FB 3 explizit freigeschaltet werden müssen. Aber der einzige große Vorteil von udfs gegenüber UDRs ist eben, dass sie minimalen Overhead haben und damit sehr performant sind.

Warum hast Du die UDR nicht in C++ geschrieben? Dafür ist die Doku besser und Du bist doch fit in C++.

Testbasis: Bekommen wir gemeinsame Testdaten organisiert, so dass unsere Ergebnisse vergleichbar sind? In Deinem Beispiel waren es 20k Dateinamen, kannst Du die hier reinstellen oder können wir irgendwas nehmen, was wir alle nutzen können?

UDR-Doku https://www.ibase.ru/files/firebird/udr.pdf - hat jemand von euch einen DeepL-Account? Ich nicht und bin mit dem Versuch, das pdf dort nach Deutsch übersetzen zu lassen, an deren Größenbeschränkung gescheitert. Dann hab ich mit pdftk die ersten 20 Seiten extrahiert, doch dann hatte Google bereits meine IP und Browserprofil und bot mir nur noch die Registrierung an. Ich habe keine Seite gefunden, die das pdf übersetzt, ohne dass man mit weiterverkaufbaren Daten oder Geld bezahlen muss.

Grüße, Volker

[martin.koeditz]

Moin Volker,

danke für den Hinweis. Ich prüfe nochmals den Code. Eventuell habe ich da was durcheinander gebracht und committet.

Die Übersetzung ins Englische ist bereits fertig. Ich werde diese Woche noch mit den Jungs Rücksprache halten. Wäre schön, wenn wir die Beschreibungen in die offizielle Dokumentation aufnehmen können.

Gruß
Martin

[vr2]

Die Übersetzung ins Englische ist bereits fertig.

Super! Noch die Frage, warum hast Du die UDR nicht in C++ geschrieben, da Du die Sprache doch besser kannst?

Grüße, Volker