Firebird-Spalte ohne Groß-Klein-Schreibung sortieren

[Perlsau]

Hallo Forengemeinde und Firebird-Spezialisten!

Wenn ich in einer Firebird-Datenbank eine Spalte, die Text (Varchar 200) enthält, sortiere, dann erscheinen in meiner Tabelle zuerst die Großbuchstaben nach ABC sortiert und danach die Kleinbuchstaben. Das heißt, die Spalte mit dem String "Die Große Versuchung" steht vor der Spalte mit dem String "Die alte Welt". Ich möchte aber bei der Sortierungt auf die Berücksichtigung von Groß-Kleinschreibung verzichten, so daß ein a vor dem B sortiert wird.

Hat das überhaupt etwas mit der Datenbank zu tun? Ich sortiere mit Dataset.DoSort.

Daten:
Firebird 2.1
Radstudio 2009 pro
arbeite vorwiegend mit Delphi
FibPlus-Komponenten
Win-XP Pro

[mkinzler]

Lege einen expression index auf Upper() an oder eine entsprechende collation (Sortierreihenfolge)

[idefix2]

Ein Expression index hilft da gar nichts. Ein Index dient prinzipiell dazu, eine Suche bzw die Sortierung zu beschleunigen, aber wenn für eine Query keine case-insensitive Collation angegeben ist, wird der expression Index zwar in der Datenbank vorhanden sein, aber für die Query einfach nicht verwendet werden.

Das Vorhandensein oder nicht Vorhandensein eines Index beeinflusst in keiner Weise die Reihenfolge, in der Datensätze von einer Query geliefert werden.

entweder die Collation der entsprechenden Spalte ist von vorneherein caseinsensitiv, oder man muss bei der Query eine entsprechende collate Klausel angeben. Ist eine Spalte mit einer case insensitiven Collation definiert, dann ist auch ein Index auf die Spalte automatisch case insensitiv, ein expression index auf upper() ist dann unnötig.

[Bummi]

TQuery und 'Order by UPPER()' ??

[idefix2]

Zitat:

Zitat von Bummi (Beitrag 1074795)

TQuery und 'Order by UPPER()' ??

Das ist sinnvoll, wenn man einmal ausnahmsweise bei einer Query die Gross/Kleinschreibung unberücksichtigt lassen will, aber sonst sehr wohl eine unterschiedliche Behandlung haben will. Wenn in einer Spalte die Sortierung prinzipiell nicht zwischen Gross- und Kleinschreibung unterscheiden soll, ist die Angabe einer geeigneten Collating Sequence direkt bei der Spaltendefinition auf jeden Fall besser.

Es gibt glaube ich bei Firebird auch spezielle Collations, die zwar zwischen Gross- und Kleinschreibung unterscheiden, aber nur dann, wenn es keine anderen Unterschiede zwischen den Spaltenwerten gibt also z.B. AA < Aa < aa < AB < Ab < ab < AB1.

[mjustin]

Einige Hinweise habe ich hier gefunden:

http://www.destructor.de/firebird/ca...tivesearch.htm

expression index (ab 2.0) und UNICODE_CI (ab 2.1.2) sehen interessant aus

[Hansa]

Zitat:

Zitat von idefix2 (Beitrag 1074807)

Zitat:

Zitat von Bummi (Beitrag 1074795)

TQuery und 'Order by UPPER()' ??

Das ist sinnvoll, wenn man einmal ausnahmsweise bei einer Query die Gross/Kleinschreibung unberücksichtigt lassen will.

Die Regel ist, also das zu unterscheiden, oder wie ? :shock: Wer das macht, der ist echt selber schuld. Aber was soll denn das ? Man sorgt zu 99,9 % für unerwünschtes Ergebnis. Im Firebird-Umfeld ist es noch krasser : einmal GrOss/Kleinschreibung, immer Gross/Kleinschreibung. D.h. "FelD" <> "Feld" ! Da handelt man sich sogar noch "QuotedStr" ein. Es ist schon schlimm genug, wenn die Sortierreihenfolge nicht stimmt. Noch schlimmer ist aber, dass eventuell die Ergebnisse nicht stimmen, Suche nicht geht und bestenfalls die DB nur nicht neu compiliert wird. :lol: Nur weil für einen Buchstaben Caps-Lock aus Versehen eingeschaltet war oder die Finger verrutscht sind. :P

[idefix2]

Ich glaube, Du hast das Thread-Thema missverstanden: Es geht nicht um Gross/Kleinschreibung bei Spaltennamen - da stimme ich Dir übrigens 100%ig zu, diese zwischen Hochkomma gesetzten Spaltennamen erscheinen mir auch sinnlos und nur dazu geeignet, Verwirrung zu stiften - sondern darum, die Spalteninhalte unabhängig von der Gross-Kleinschreibung zu sortieren.

Zitat:

Die Regel ist, also das zu unterscheiden, oder wie ?

Eine allgemeine Regel gibt es da nicht. Ob man zwei Feldinhalte, die sich nur in bezug auf die Gross/Kleinschreibung unterscheiden, als völlig gleich behandeln will (-> zufällige Reihenfolge im Result Set, wenn es keine anderen Kriterien gibt), oder ober man sie zwar in der Sortierung beisammen haben will, aber doch immer das Feld mit Grossbuchstaben vor dem mit Kleinbuchstaben im Resultset aufscheinen soll, oder ob die Daten strikt nach ASCII-Werten sortiert werden sollen (also Gross- und Kleinbuchstaben weit auseinander) hängt wohl von der Anwendung und ihren Anforderungen ab, wobei mir zugegebenermassen für die letzte Variante kaum ein sinnvolles Praxisbeispiel einfällt.

[Perlsau]

Zitat:

Zitat von Bummi (Beitrag 1074795)

TQuery und 'Order by UPPER()' ??

Vielen Dank, das hat geholfen! :-D

Verwende zwar kein TQuery, sondern weiterhin TDataset und kann auch für alle anderen Spalten weiterhin DoSort verwenden. Diese eine Spalte, in der Groß- und Kleinschreibung vorkommt, unterziehe ich jetzt einer Sonderbehandlung, indem ich sie aus dem CASE-Construct ausgliedere und die Sortierung direkt dem SelectSQL-String zuweise.

Vorher:

Delphi-Quellcode:

PROCEDURE Sort_Titel(Spalte : INTEGER);

VAR
   Merk    : INTEGER;
   Aus     : STRING;

BEGIN
     Merk   := DatMod.FibSet_Titel.FieldByName('INDEX').AsInteger;
     IF Spalte = SortIndex THEN SortOrder := NOT SortOrder ELSE SortOrder := TRUE;

     CASE Spalte OF
      0 : DatMod.FibSet_Titel.DoSort(['INDEX'],          [SortOrder]);
      1 : DatMod.FibSet_Titel.DoSort(['TITEL'],          [SortOrder]);
      2 : BEGIN
               MeldungEin;
               DatMod.FibSet_Titel.DoSort(['VRegie'],    [SortOrder]);
               MeldungAus;
          END;
      ...
      END;

Nachher:

Delphi-Quellcode:

PROCEDURE Sort_Titel(Spalte : INTEGER);

VAR
   Merk    : INTEGER;
   Aus     : STRING;

BEGIN
     Merk   := DatMod.FibSet_Titel.FieldByName('INDEX').AsInteger;
     IF Spalte = SortIndex THEN SortOrder := NOT SortOrder ELSE SortOrder := TRUE;

     IF (Spalte <> 1) AND (SortIndex = 1) THEN
     BEGIN
          DatMod.FibSet_Titel.Close;
          DatMod.FibSet_Titel.SQLs.SelectSQL.Text := 'SELECT * FROM TITEL';
          DatMod.FibSet_Titel.Open;
     END ELSE
     IF Spalte = 1 THEN
     BEGIN
          DatMod.FibSet_Titel.Close;
          Aus := 'SELECT * FROM TITEL ORDER BY LOWER(TITEL) ';
          IF SortOrder THEN Aus := Aus + 'ASC' ELSE Aus := Aus + 'DESC';
          DatMod.FibSet_Titel.SQLs.SelectSQL.Text := Aus;
          DatMod.FibSet_Titel.Open;
     END;

     CASE Spalte OF
      0 : DatMod.FibSet_Titel.DoSort(['INDEX'],          [SortOrder]);
      {ausgeklammert: 1 : DatMod.FibSet_Titel.DoSort(['TITEL'],          [SortOrder]);}
      2 : BEGIN
               MeldungEin;
               DatMod.FibSet_Titel.DoSort(['VRegie'],    [SortOrder]);
               MeldungAus;
          END;
      ...
      END;

Kurze Erläuterung: SortIndex ist die Spaltennummer, nach der aktuell sortiert ist und die nach jeder neuen Sortierung gesetzt wird. SortOrder hat bei ASC den Wert TRUE und bei DESC den Wert FALSE. Beide Variablen sind global. So kann ich wie bei Excel durch einen Klick auf den Spaltentitel die Sortierung vornehmen, wobei ein zweiter Klick auf denselben Spaltentitel die Sortierung umkehrt.

[idefix2]

Warum um Himmels Willen machst Du es Dir so kompliziert? Und wenn die Spalte später noch in einem anderen Grid auftaucht oder eine andere Spalte in einer anderen Tabelle dazukommt, die Du caseinsensitiv haben willst, hast Du jedesmal von Neuem das Theater.

Mit einer geeigneten Collation bei der Spalte brauchst Du keine einzige Zeile zusätzlich zu programmieren, um die eine Spalte caseinsensitiv zu sortieren. Das ist gerade mal eine Klausel bei der Spaltendefinition!

Dazu kommt, dass mit der Wahl einer geeigneten Collation auch die Umlaute sinnvoll mitsortiert werden, was bei der Standard Firebirdcollation nicht der Fall ist.

[Perlsau]

Zitat:

Zitat von idefix2 (Beitrag 1075131)

Warum um Himmels Willen machst Du es Dir so kompliziert? Und wenn die Spalte später noch in einem anderen Grid auftaucht oder eine andere Spalte in einer anderen Tabelle dazukommt, die Du caseinsensitiv haben willst, hast Du jedesmal von Neuem das Theater.

Wohl weil ich's nicht besser weiß, mach ich's so kompliziert. Dennoch leuchtet mir dein Argument auf Anhieb ein.

Zitat:

Zitat von idefix2 (Beitrag 1075131)

Mit einer geeigneten Collation bei der Spalte brauchst Du keine einzige Zeile zusätzlich zu programmieren, um die eine Spalte caseinsensitiv zu sortieren. Das ist gerade mal eine Klausel bei der Spaltendefinition!

Ich trage jetzt also mal in der Spalte TITEL, um die es mir geht, als Collation DE-DE ein. Charset ist ISO8859_1, das lasse ich so stehen. Mal schauen, was passiert ...

Nun, es werden wieder die Großbuchstaben vor den Kleinbuchstaben sortiert ...

Zitat:

Zitat von idefix2 (Beitrag 1075131)

Dazu kommt, dass mit der Wahl einer geeigneten Collation auch die Umlaute sinnvoll mitsortiert werden, was bei der Standard Firebirdcollation nicht der Fall ist.

Welche Collation sollte ich denn bei der Spaltendefinition in der Spalte COLLATION eintragen?

[idefix2]

Die meisten Standard Firebird collations sind case sensitiv.
Aber mit dem folgenden Befehl erstellst Du Dir in der Datenbank auf einfachste Art eine case-insensitive Collation:

Code:

create collation meine_collation
    for ISO8859_1
    from de_de
    case insensitive;

Dabei ist ISO8859_1 der zugrundeliegende Zeichensatz (könnte auch ein beliebiger anderer sein) und de_de die zu diesem Zeichensatz gehörende casesensitive Sortierung (könnte auch eine beliebige andere sein).


Bei den entsprechenden Spaltendefinitionen schreibst Du dann: ... collate meine_collation

[Perlsau]

Zitat:

Zitat von idefix2 (Beitrag 1075391)

Die meisten Standard Firebird collations sind case sensitiv.
Aber mit dem folgenden Befehl erstellst Du Dir in der Datenbank auf einfachste Art eine case-insensitive Collation:

Code:

create collation meine_collation
    for ISO8859_1
    from de_de
    case insensitive;

Dabei ist ISO8859_1 der zugrundeliegende Zeichensatz (könnte auch ein beliebiger anderer sein) und de_de die zu diesem Zeichensatz gehörende casesensitive Sortierung (könnte auch eine beliebige andere sein).
Bei den entsprechenden Spaltendefinitionen schreibst Du dann: ... collate meine_collation

Okay, ich hab jetzt also den SQL-Editor innerhalb von IBExpert light aufgerufen und eine collation mit dem Namen COL_INSENSI erstellt, wie du es oben beschrieben hast. Das hat Firebird anstandslos kompiliert. Als nächstes hab ich mittels Doppelklick auf die betreffende Spalte in den Feld-Definitionen den Eintrag in der Collation-Spalte von DE_DE auf COL_INTENSI umgestellt. Nachdem ich dann meine umständliche Lösung – Order by LOWER(TITEL) – in der Delphi-IDE rückgängig gemacht und den ursprünglichen Quellcode wieder hergestellt habe, zeigt mir meine Tabelle jedoch wieder die falsche Sortierung:

Der String "In The" wird vor dem String "In drei" aufgeführt (bei aufwärtiger Sortierung). Hat leider nicht funktioniert ...

Vielleicht kannst du ja anhand meiner ausführlichen Dokumentation meiner Schritte erkennen, ob ich dabei einen Fehler gemacht habe.

Hab dennoch herzlichen Dank für deine bisherigen Bemühungen, mir auf die Sprünge zu helfen :wink:

Edit will noch wissen, wo denn die von mir erstellte Collation abgespeichert wird, bzw. wo ich sie eventuell ändern kann.

[idefix2]

So unmittelbar fällt mir nichts auf, was Du falsch gemacht haben könntest (ich gehe davon aus, das die unterschiedliche Schreibung nur ein Tippfehler im Posting ist und die Bezeichnung in der Datenabnk richtig ist). Hast Du irgend ein Tool installiert, um direkt in der Datenbank zu fuhrwerken, das würde das Debugging denke ich wesentlich vereinfachen? Ich verwende Database workbench lite für Firebird, das kann ich nur wärmstens empfehlen: es ist gratis und absolut optiomal, um sich in Firebird und SQL einzuarbeiten: Du kannst z.B. ein SQL statement eingeben und siehst unmittelbar das Ergebnis-Set, Du kannst Dir alle Tabellen- und Spalteneigenschaften anzeigen lassen etc.

So wie es aussieht, hat die Spalte die neue Collation nicht zugeordnet bekommen, ich kenn aber IBExpert nicht und hab keine Ahnung, woran es liegen könnte - wenn Du Dir die Spalteneigenschaften anzeigen lässt, dürfte dort nach wie vor de-de drinstehen, oder?

Um den Fehler einzugrenzen, könntest Du ja probeweise eine Collation-Klausel direkt in der SQL Query angeben - das hat dann höhere Priorität als die Default-Collation der Spalte - und schauen, ob dann das richtige Ergebnis angezeigt wird, Wenn ja, ist die Collation richtig angelegt und es hat nur die Zuordnung zur Spalte aus irgend einem Grund nicht geklappt, wenn nein, dann ist schon die Definition der Collation schiefgegangen.

Neue Collations ebenso wie die Zuordnung der Collation zu den Spalten wird in den Systemtabellen der Datenbank festgehalten, ich habe es jetzt nicht im Detail im Kopf, aber sie müssten sich auch mit irgendeinem alter-Statement ändern lassen.