Jeder Round-Trip zur Datenbank kostet Zeit und Ressourcen. Oftmals hat man aber das Problem, dass man zu einem Datum noch eine unspezifizierte Anzahl von Relationen braucht. Meistens holt man die dann in einem Loop in der Applikation, denn üblicherweise bekommt man in den Ergebnisfeldern eines SQL-Queries nur flache Daten zurück. Mit PostgreSQL und JSON in der Datenbank geht das in manchen Fällen aber eleganter – und man kann ein gutes Dutzend Queries auf genau eine schnelle Abfrage reduzieren.
Ziel in diesem Beispiel ist es, für eine Suche nach einem Produktnamen jeweils pro eindeutigem Namen einen kleinen Block auszugeben, der diesen Produktnamen enthält, dazu das Bild zur bevorzugten zugehörigen Entität (z.B. bei Assassin’s Creed die PC-Version), den Link zur Produktseite dieser Entität, die Plattform (hier PC), ebenfalls verlinkt und dann in einer von der Redaktion vorgegebenen Reihenfolge von anderen Plattformen jeweils den Plattformnamen mit Link auf die zugehörige Produktseite. Wir haben also in dem benötigten Datensatz zu einem Namen genau einen Bild-Pfad, genau einen Namen und jeweils ein bis n Plattform-Bezeichner und URLs.
Wir haben eine Tabelle public.entity_autosuggest, die im Prinzip eine gruppierte materialized View unserer Produktdatenbank darstellt, d.h. für jeden eindeutigen Produktnamen gibt es ein Tupel mit u.a. einem Array der zugehörigen Entity-IDs und Entity-Type-IDs; die beiden Arrays sind automatisch in korrelierender Reihenfolge angelegt, d.h. die Position 1 im Entity-Type-ID-Array enthält dann die Type-ID für die gleiche Position im Entity-ID-Array. Diese Reihenfolge ist allerdings zufällig, es gibt keine Vor-Sortierung nach Plattform-Typen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE TABLE public.entity_autosuggest ( lname text NOT NULL, name text NOT NULL, name_syn text, arrids integer[], arrtids integer[], arrsids integer[], elcount integer NOT NULL DEFAULT 0, last_updated timestamp without time zone NOT NULL DEFAULT now(), genre_id integer, id serial NOT NULL, CONSTRAINT "entity_autosuggest_pkey" PRIMARY KEY (lname) )WITHOUT OIDS; -- Indexes CREATE INDEX idx_entity_autosuggest_arrids ON entity_autosuggest USING gist (arrids gist__intbig_ops); CREATE INDEX entity_autosuggest_idx4 ON entity_autosuggest USING btree (lname, arrsids, genre_id); CREATE INDEX trgm_idx_entity_autosuggest2 ON entity_autosuggest USING gist (lname gist_trgm_ops, arrtids, arrsids, arrmax(arrids)); CREATE INDEX trgm_idx_entity_autosuggest ON entity_autosuggest USING gist (lname gist_trgm_ops, arrtids, arrsids); -- Comments COMMENT ON COLUMN public.entity_autosuggest.lname IS 'unique lower cased common name of entities in this cluster, unique in lower case'; COMMENT ON COLUMN public.entity_autosuggest.name IS 'properly cased common name of entities in this cluster, unique in lower case'; COMMENT ON COLUMN public.entity_autosuggest.name_syn IS 'synonymous name, optional'; COMMENT ON COLUMN public.entity_autosuggest.arrids IS 'array of entity ids'; COMMENT ON COLUMN public.entity_autosuggest.arrtids IS 'array of type ids'; COMMENT ON COLUMN public.entity_autosuggest.arrsids IS 'array of site ids'; COMMENT ON COLUMN public.entity_autosuggest.elcount IS 'count of elements in this cluster'; COMMENT ON COLUMN public.entity_autosuggest.last_updated IS 'max last_updated of the associated entity ids'; COMMENT ON COLUMN public.entity_autosuggest.genre_id IS 'if at least one of the associated entity ids has a genre_id field of type integer, it''s stored here'; |
Die Tabelle eignet sich u.a. für Trigramm-Suchen, es gibt aber auch einen Sphinx-Index, der natürlich nur die id der matchenden entity_autosuggest-Tupel zurückliefert; passend dazu gibt es eine pl/pgsql-Funktion, mit der man zu einer solchen id alle Namen, alle Entity-IDs, alle Entity-Typ-Bezeichner und auch alle SEO-URLs für die Entitäten für eine gegebene Site bekommen kann, fertig gefiltert und sortiert nach einer Liste von Entity-Type-IDs. Der Kopf dieser Funktion ist dabei wie folgt:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE or REPLACE FUNCTION "public"."getentitiesfromautosuggest"( IN "_ausug_id" integer, IN "_site_id" integer, IN "_lst_type_ids" text, OUT "entity_name" text, OUT "arrentity_ids" integer[], OUT "arrentity_platforms" text[], OUT "arrentity_urls" text[]) RETURNS record AS … |
Beispiel für die Verwendung:
|
1 2 3 4 5 |
SELECT * FROM public.getentitiesfromautosuggest(27943,1,'217,5093,5104,5055,2240,2220,2400,5054,5048,279,764,2180,2160,278,761,184'); entity_name | arrentity_ids | arrentity_platforms | arrentity_urls ---------------------+-----------------+---------------------+--------------------------------------------------------------------- PlayStation Network | {213795,215375} | {PS3,PSP} | {/PlayStation-Network-PS3-213795/,/PlayStation-Network-PSP-215375/} (1 row) |
D.h. das Ding ist ein Record-Returning-Function, es liefert immer einen komplexen Datensatz mit den Feldern entity_name, arrentity_ids, arrentity_platforms und arrentity_urls zurück und wird verwendet wie eine Tabelle.
Jetzt bekomme ich von Sphinx eine Liste von entity_autosuggest.id-Werten – und ich möchte für die komplette Liste in einem einzigen Trip alles haben, was ich für die Ausgabe im Frontend benötige, d.h. für alle Treffer jeweils Name, Entity-IDs, URLs, Plattformen und ein Bild der als erstes angezeigten Entität. Das geht, aber die Syntax für den Zugriff auf die Felder der komplexen Rückgabewerte ist etwas gewöhnungsbedürftig – da braucht’s eine Klammer, damit PostgreSQL realisiert, dass es sich hier um einen Feldpfad handelt und nicht um ein Schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT row_to_json(einf.f) AS entity_info , e.default_image FROM ( SELECT public.getentitiesfromautosuggest(eauid,1,'217,5093,5104,5055,2240,2220,2400,5054,5048,279,764,2180,2160,278,761,184') AS f FROM unnest(ARRAY[15258,16384,15270,15271,15272,40252]) AS eauid ) AS einf JOIN public.entity e ON e.id = (einf.f).arrentity_ids[1] ORDER BY (einf.f).entity_name ASC; entity_info | default_image ------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------+-------------------------------- {"entity_name":"Microsoft Flight","arrentity_ids":[235596],"arrentity_platforms":["PC"],"arrentity_urls":["/Microsoft-Flight-PC-235596/"]} | 2012/03/boxmicrosoftflight.jpg {"entity_name":"Microsoft Flight Simulator 2004: A Century of Flight","arrentity_ids":[58080],"arrentity_platforms":["PC"],"arrentity_urls":["/Microsoft-Fli ght-Simulator-2004-A-Century-of-Flight-PC-58080/"]} | 2008/02/1202393600833.jpg {"entity_name":"Microsoft Train Simulator","arrentity_ids":[16486],"arrentity_platforms":["PC"],"arrentity_urls":["/Microsoft-Train-Simulator-PC-16486/"]} | 2002/09/trainsimulator_dt.jpg {"entity_name":"Microsoft Train Simulator 2","arrentity_ids":[65220],"arrentity_platforms":["PC"],"arrentity_urls":["/Microsoft-Train-Simulator-2-PC-65220/" ]} | {"entity_name":"Microsoft Train Simulator Add-on","arrentity_ids":[16920],"arrentity_platforms":["PC"],"arrentity_urls":["/Microsoft-Train-Simulator-Add-on- PC-16920/"]} | {"entity_name":"Ubisoft","arrentity_ids":[15823],"arrentity_platforms":["Firma"],"arrentity_urls":["/Ubisoft-Firma-15823/"]} | (6 rows) |
Laufzeit der Abfrage auf Live-Daten (>64.000 Produkte) einen winzigen Tick über 30 Millisekunden. Die Applikation muss dann nur noch server_info deserialisieren und bekommt mundgerecht aufbereitete Arrays für die Ausgabe.