[halb-gelöst] Nach Dateiendung sortieren.

[Libo Neuling]

Und schon wieder ich

Also ich benutze immer noch

LibreOffice Version 3.6.4.3
Das Ganze auf WinXp 32 bit

Dieses mal aber die integrierte hsql

Zu meinem Problem:

Mein Bruder hat mich gefragt, ob ich ihm wohl Eine Datenbank erstellen könnte in der er
alle auf seiner Festplatte befindlichen Daten sortiert nach Art, also Filme, Bilder, Texte etc.
aufgelistet bekommen könnte.

Eine Liste habe ich mit FTPList von zu Hause aus erstellen können.

Hier ein Ausschnitt:

ftp://sein-host.bla/home/Setup.exe
ftp://sein-host.bla/home/Neues Textdokument.txt
ftp://sein-host.bla/home/Landschaft.jpg
ftp://sein-host.bla/home/Die Katze.jpg
ftp://sein-host.bla/home/VID_1.mp4
ftp://sein-host.bla/home/VID_2.mp4
ftp://sein-host.bla/home/VID_3.mp4
ftp://sein-host.bla/home/VID_1.3gp
ftp://sein-host.bla/home/Install.exe
ftp://sein-host.bla/home/.thumbnails/.End-of-time.jpg

Die Daten stehen in der Tabelle ('Alle Daten')
Mit den Feldern

ID int auto
URL Text varchar (255)

Jetzt hatte ich angefangen für jede Dateiendung eine Ansicht zu erstellen. Was ich aber ganz
schnell wieder aufgegeben habe, da es sich bei Ihm um über 15.000 Dateien handelt und
dem entsprechen viele Endungen hat er dann auch.

Ich weiß das es möglich ist nur die Dateiendung aus zu lesen und diese dann in eine Tabelle
bzw. Ansicht zu speichern. Nur weiß ich leider nicht wie.

Und dann gibt es ja noch das Problem, dass die Endungen nicht immer gleich lang sind,
also müsste (so stelle ich mir das zumindest vor) immer bis zum ersten Punkt von rechts
der jeweilige Datensatz ausgelesen werden.

Wie z.B. die Endung .mpg oder mpeg

Ich hoffe ihr könnt mir helfen!!!

Ich habe im Forum gesucht und noch keine Antwort gefunden aber sollte diese Frage schon
gestellt und gelöst worden seien bitte nicht böse sein und mir den Link hier senden.

Anscheinend gibt es da in PHP eine Lösung, aber da kenne ich mich ja noch weniger mit aus
was ja nicht gerade viel zu bedeuten hat

aber hier mal der link:

http://phpforum.de/forum/showthread.php?t=136120

[Libo Neuling]

ich wäre schon dankbar, wenn mir jemand sagen könnte wie die Anweisung lautet um Datensätze von hinten aus zu lesen.
Dann hätte ich wenigstens einen Anhaltspunkt wo ich weiter machen könnte, denn da finde ich auch nichts. Aber ich hatte mal vor etlichen Jahren ein wenig adabas gelernt und da gab es diese Funktion auch. Aber lang lang ist es her

[juetho]

Laut Base-Handbuch Seite 272 gibt es dazu die folgenden Funktionen:

• RIGHT gibt die mit count angegebene Zeichenzahl vom Textende aus wieder.
• Für die benötigte Teillänge kannst du mit LOCATE die Position des Punktes bestimmen (auch von rechts her).

Damit kannst du das in den SQL-Befehl einbauen. (Alternativ geht das natürlich auch über Makros, aber direkt per SQL sollte es praktischer und deutlich schneller sein.)

Nebenbei: Du hattest dich angemeldet. Dann hättest du deinen ersten Beitrag mit Ändern ergänzen können; das ist meistens praktischer und übersichtlicher - jedenfalls solange von dir oder einem Helfer nichts Neues zur Lösung beigetragen wird. Jürgen

[Libo Neuling]

Laut Base-Handbuch Seite 272 gibt es dazu die folgenden Funktionen:

• RIGHT gibt die mit count angegebene Zeichenzahl vom Textende aus wieder.
• Für die benötigte Teillänge kannst du mit LOCATE die Position des Punktes bestimmen (auch von rechts her).

Damit kannst du das in den SQL-Befehl einbauen. (Alternativ geht das natürlich auch über Makros, aber direkt per SQL sollte es praktischer und deutlich schneller sein.)

Nebenbei: Du hattest dich angemeldet. Dann hättest du deinen ersten Beitrag mit Ändern ergänzen können; das ist meistens praktischer und übersichtlicher - jedenfalls solange von dir oder einem Helfer nichts Neues zur Lösung beigetragen wird. Jürgen

Ok werde ich mir merken.


Ich bin aber inzwischen auch schon etwas weiter gekommen.

Also hiermit bekomme ich alle 4 stelligen Dateiendungen:

SELECT RIGHT( "Url", 4 ) AS "Dateiendung" FROM "Alle-Daten" WHERE POSITION( '.' IN RIGHT( "Url", 5 ) ) = 1 GROUP BY RIGHT( "Url", 4 )

und hiermit alle 3 stelligen Dateiendungen:

SELECT RIGHT( "Url", 3 ) AS "Dateiendung" FROM "Alle-Daten" WHERE POSITION( '.' IN RIGHT( "Url", 4 ) ) = 1 GROUP BY RIGHT( "Url", 3 )

Aber ich bekomme die Abfrage nicht so hin, dass sie in eine Tabelle geschrieben wird.

Also in etwa so:

insert into "Dateiendungen" ("Dateiendung")
SELECT RIGHT( "Url", 4 ) AS "Dateiendung" FROM "Alle-Daten" WHERE POSITION( '.' IN RIGHT( "Url", 5 ) ) = 1 GROUP BY RIGHT( "Url", 4 )

insert into "Dateiendungen" ("Dateiendung")
SELECT RIGHT( "Url", 3 ) AS "Dateiendung" FROM "Alle-Daten" WHERE POSITION( '.' IN RIGHT( "Url", 4 ) ) = 1 GROUP BY RIGHT( "Url", 3 )

Und danke für deine Mühen!

[juetho]

Meine Antwort zeigte zunächst auch nur den Weg, wie du aus der URL die Endung per SQL herausholen kannst. Wie das sinnvoll weiter verarbeitet werden kann, ist in der Tat die nächste Frage. Dazu zunächst ein paar Hinweise:

• GROUP BY ist bei deiner Aufgabe nur dann sinnvoll, wenn die vorhandenen Einträge aktuell angezeigt und dabei sortiert werden sollen. Dir geht es aber darum, jeden Eintrag nur einmal zu erhalten; dafür ist DISTINCT besser.
• Bei deinem Verfahren werden 3- und 4-stellige Endungen getrennt behandelt. (Eigentlich gibt es auch Endungen mit 2 oder mit mehr Stellen. Die sind vielleicht bei deiner Anwendung irrelevant; aber bei einer sauberen Lösung sollte man sie beachten.)
• Bei deiner Verknüpfung von WHERE, POSITION und RIGHT wird für jede Zeile RIGHT dreimal ausgeführt; das geht besser.

Denken wir einmal schrittweise (alle Schreibweisen ungetestet):

1. Suche die Länge des Textes (dann können wir uns auf das Ende des Textes beschränken):
   LENGTH("Url")
2. Suche damit den letzten Punkt im String. Das geht mit LOCATE (Schreibweise ungetestet):
   LOCATE('.' , "Url", LENGTH("Url") - 10 ) /* 10 nur als grobe Begrenzung, kann auch kleiner sein */
3. Alles, was danach kommt, ist die Endung.

Damit haben wir (zunächst einmal als SELECT) folgende Zwischenlösung (ob RIGHT oder SUBSTR verwendet wird, ist nebensächlich):

Code: Alles auswählen

SELECT DISTINCT SUBSTR( "Url", LOCATE('.' , "Url", LENGTH("Url") - 10 ) + 1 ) AS "Dateiendung"
  FROM "Alle-Daten"

Willst du für die Typenzuordnung wirklich eine Tabelle "Dateiendungen" vorsehen und nicht besser eine Tabelle "Dateitypen" mit Einträgen wie Film, Bild, Text? Dafür scheint mir folgendes Verfahren sinnvoller zu sein:

1. Die Tabelle "Dateitypen" bekommt eine ID und eine Spalte mit der Bezeichnung (Film usw.).
2. Die Tabelle, die die URL enthält, bekommt eine weitere Spalte Dateityp_ID, die später (!) ein Fremdschlüssel wird.
3. Die Werte dafür werden durch einen UPDATE eingetragen, etwa so:

   Code: Alles auswählen

   UPDATE "Alle-Daten"
      SET Dateityp_ID = ( CASE SUBSTR( "Url", LOCATE('.' , "Url", LENGTH("Url") - 10 ) + 1 )
             WHEN "jpg" THEN 2  /* ID für Bild */
             WHEN "txt" THEN 1   /* ID für Text usw. */
           END )

Das sind nur Ratschläge und Hinweise. Wie es genau aussehen soll, hängt von deinem konkreten Plan ab. Beispielsweise kannst du UPDATE auch wiederholt mit WHERE benutzen:

Code: Alles auswählen

UPDATE "Alle-Daten"
   SET Dateityp_ID = 2
   WHERE SUBSTR( "Url", LOCATE('.' , "Url", LENGTH("Url") - 10 ) + 1 ) = "jpg"

Das hängt von der Menge der Dateitypen und deinen Vorlieben zur Arbeitsweise ab.
Gruß Jürgen

PS. All das kann sicher noch besser geregelt werden. Ich wollte aber keine fertige Lösung anbieten, sondern vor allem Anregungen.

[Libo Neuling]

Als aller erstes möchte ich mich bei juetho für seine überaus ausführliche und verständliche Antwort bedanken.

Natürlich habe ich nicht vor einfach nur eine Tabelle mit Dateierweiterungen zu erstellen. Diese sollte lediglich erst einmal zur einfachen Darstellung meiner Frage her halten. Später sollen selbstverständlich noch genauere Informationen zu den einzelnen Dateiendungen gespeichert werden. Wie zum Bsp: Dateiendungstyp (Film, Musik, Archiv …..) eine allgemeine Beschreibung also (Welche Anwendung nutzt sie?, Für welches Betriebssystem ist sie?) usw.

Jetzt zu deinem schon sehr guten Lösungsansatz. Ich beschränke mich hier aber auf deine erste SELECT Anweisung, da das ja die eigentliche Frage war.

Original Syntax:

Code: Alles auswählen

SELECT DISTINCT SUBSTR( "Url", LOCATE('.' , "Url", LENGTH("Url") - 10 ) + 1 ) AS "Dateiendung"
  FROM "Alle-Daten"

Eergebnis:


Dateiendung

Banner.jpg
Baum.jpg
Bause.gif
dat
dat_old
db
ftp://mein-spassnetz.net/GoFlex Home


Wie man sehen kann werden Datensätze teilweise komplett angezeigt und andere haben innerhalb der letzten 10 Zeichen mehr als nur einen Punkt.

Fehlersuche:

Als erstes habe ich die Abfrage auf die letzten 4 Stellen begrenzt (Dieses erscheint mir für meine Zwecke auch ausreichend). Ansonsten müsste ich die Abfrage so anpassen, dass er tatsächlich immer nur den ersten Punkt von hinten wählt, was aber meine Fähigkeiten in SQL bei weitem überschreitet. (zumindest jetzt noch) .

Dann wollte ich wissen welchen Wert mir LOCATE eigentlich zurückgibt. Also habe ich ein Feld namens Wert hinzugefügt.

Code: Alles auswählen

SELECT DISTINCT LOCATE( '.', "Url", LENGTH( "Url" ) -4 ) AS "Wert", SUBSTR( "Url", LOCATE( '.', "Url", LENGTH( "Url" ) - 4 ) + 1 ) AS "Dateiendung" FROM "Alle-Daten"

Ergebnis:

Wert Dateiendung

0 ftp://mein-spassnetz.net/GoFlex Hom
60 exe
61 exe


Hier gibt LOCATE in einigen Zeilen den Wert 0 zurück. Dieses bedeutet, dass es in den letzten 4 Zeichen keinen Punkt gibt und diese Dateien von daher vermutlich auch keine Ausführbaren -oder Mediendateien sind. Also sind sie zumindest für meine Zwecke irrelevant.
Lass ich LOCATE die letzten 10 Zeichen absuchen habe ich immer noch Datensätze die den Wert 0 ergeben. Aber das nur am Rande.

Fehlerbehebung:

Nun gut was habe ich daraus gelernt?
Ich habe mir überlegt, die Datensätze mit einem Wert von 0 zu ignorieren. Weshalb ich noch eine
WHERE Klausel der Abfrage hinzugefügt habe.

Code: Alles auswählen

SELECT DISTINCT LOCATE( '.', "Url", LENGTH( "Url" ) -4 ) AS "Wert", SUBSTR( "Url", LOCATE( '.', "Url", LENGTH( "Url" ) - 4 ) + 1 ) AS "Dateiendung" FROM "Alle-Daten" WHERE LOCATE( '.', "Url", LENGTH( "Url" ) - 4 ) > 0

Ergebnis:


Wert Dateiendung

67 mp3
68 dat
68 db
68 jpg
68 lock
68 mp3
68 avi


Jetzt nehme ich noch das Feld Wert raus.

Code: Alles auswählen

SELECT DISTINCT SUBSTR( "Url", LOCATE( '.', "Url", LENGTH( "Url" ) - 4 ) + 1 ) AS "Dateiendung" FROM "Alle-Daten" WHERE LOCATE( '.', "Url", LENGTH( "Url" ) - 4 ) > 0

Ergebnis:


Dateiendung

3gp
apk
avi
AVI
bak
dat
db
divx


Und siehe da endlich habe ich das Ergebnis, nach welchem ich die ganze Zeit gesucht habe.

[Libo Neuling]

Da das da oben, wie Juetho schon sagte keine saubere Lösung ist suche ich immer noch nach einer besseren Lösung und
bin auf diesen Beitrag gestoßen.

viewtopic.php?f=8&t=60641

Dort wurde SELECT CASE angeboten.

Was, wie ich glaube, dass Problim mit den variablen stellen des Punktes für die Endung einer Datei die Lösung sein könnte.
Denn, so wie ich das verstehe, liest SELCT CASE so lange weiter bis eine Vorgegebene Bestimmung wahr ist (aslo gleich true).

Allerdings scheine ich die Syntax nicht zu verstehen, oder hsql kennt SELECT CASE gar nicht. Hier mal mein Versuch. Der wohl total daneben ist, da es immer nur einen SYNTAX Fehler gibt .

Code: Alles auswählen

Set x=0

SELECT 

case
When LOCATE( '.', "Url", LENGTH( "Url" ) - x ) > 0
then SELECT DISTINCT SUBSTR( "Url", LOCATE( '.', "Url", LENGTH( "Url" ) - x ) ) AS "Endung" FROM "Alle-Daten"
set x=x+1
else
end

Mal in Worten was ich da vor hatte (EIGENTLICH!!!!)

Setze x auf 0
Wenn die Länge des Datensatzes von LOCATE( '.', "Url", LENGTH( "Url" ) - x ) einen größeren Wert als 0 ergibt hast du den Punkt gefunden.
In dem Fall gebe alle Zeichen bis zu diesem Punkt aus
sonst rechne x+1 und suche neu
bis die Länge des Datensatzes von LOCATE( '.', "Url", LENGTH( "Url" ) - x ) einen größeren Wert als 0 ergibt

Und ich bin mir nicht sicher was passiert, wenn es gar keinen ( . ) in der Zelle gibt. !!!

[juetho]

Es tut mir leid, wenn ich nicht mehr auf all deine Gesichtspunkte eingehe. Das wird mir zu unübersichtlich (zumal ich nach wie vor nicht sehe, wo und wie die Daten verarbeitet und gespeichert werden sollen). Zu SELECT und CASE kann ich Folgendes sagen:

SELECT ist der Hauptteil einer Abfrage, die ein Ergebnis liefern soll. Eine Abfrage liefert grundsätzlich eine (unsortierte) Datenmenge. Etwas Variables, das sich innerhalb einer Abfrage ändern soll (bei dir die Variable x) hat dort überhaupt nichts zu suchen. (Bei manchen DBMS ist es in manchen Situationen möglich und zulässig, aber hier nicht.)

CASE gibt es auch bei HSQL, und zwar in beiden Varianten; bei dir ist wahrscheinlich die komplexe Variante zu verwenden:

Code: Alles auswählen

CASE
WHEN (Bedingung 1 erfüllt) THEN (Rückgabewert 1)
WHEN (Bedingung 2 erfüllt) THEN (Rückgabewert 2)
ELSE (Rückgabewert als "default")
END [CASE]

(Ob am Ende END oder END CASE stehen muss, ist auszuprobieren.) CASE...END gehört in das eigentliche SELECT als eine der Spalten, die im Ergebnis auftauchen soll. Dazu ist alles in Klammern zu setzen und erhält vorzugsweise mit AS einen eigenen Spaltennamen. Die Klammern bei den Bedingungen und Rückgabewerten sind nur nötig, wenn dort mehr (komplexer) Inhalt auftaucht.

Es ist zwar möglich, innerhalb von WHEN oder THEN weitere SELECTs als "Unterabfragen" einzubauen. Das macht es aber nur noch viel komplizierter und unübersichtlicher und dürfte eigentlich nicht nötig sein. Mehr zu CASE siehe die Einführung in SQL.

Gruß Jürgen

PS. Eine PN mit Fragen, die ins Forum gehören, wird gerne ignoriert.