Ejemplo de ltree de Postgresql

F.21.1. Definiciones

F.21.2. Operadores y funciones

F.21.3. Índices

F.21.4. Ejemplo

F.21.5. Transforma

F.21.6. Autores

Este módulo implementa un tipo de datos ltree para representar etiquetas de datos almacenados en una estructura jerárquica en forma de árbol. Se proporcionan amplias facilidades para buscar a través de árboles de etiquetas.

Este módulo se considera “de confianza“, es decir, puede ser instalado por no superusuarios que tengan CREATE privilegio sobre la base de datos actual.

F.21.1. Definiciones

A etiqueta es una secuencia de caracteres alfanuméricos y guiones bajos (por ejemplo, en la configuración regional C, los caracteres A-Za-z0-9_ están permitidos). Las etiquetas deben tener menos de 256 caracteres.

Ejemplos: 42, Personal_Services

A ruta de la etiqueta es una secuencia de cero o más etiquetas separadas por puntos, por ejemplo L1.L2.L3, que representa una ruta desde la raíz de un árbol jerárquico hasta un nodo en particular. La longitud de la ruta de una etiqueta no puede exceder las 65535 etiquetas.

Ejemplo: Top.Countries.Europe.Russia

los ltree El módulo proporciona varios tipos de datos:

• ltree almacena una ruta de etiqueta.

• lquery representa un patrón similar a una expresión regular para hacer coincidir ltree valores. Una palabra simple coincide con esa etiqueta dentro de una ruta. Un símbolo de estrella*) coincide con cero o más etiquetas. Estos se pueden unir con puntos para formar un patrón que debe coincidir con la ruta completa de la etiqueta. Por ejemplo:

  foo         Match the exact label path foo
  *.foo.*Matchany label path containing the label foo
  *.foo       Matchany label path whose last label is foo
  

  Tanto los símbolos de estrella como las palabras simples se pueden cuantificar para restringir la cantidad de etiquetas que pueden coincidir:

  *n        Match exactly n labels
  *n,       Match at least n labels
  *n,m      Match at least n but not more than m labels
  *,m       Match at most m labels — same as*0,m
  foon,m    Match at least n but not more than m occurrences of foo
  foo,      Matchany number of occurrences of foo, including zero
  

  En ausencia de un cuantificador explícito, el valor predeterminado para un símbolo de estrella es coincidir con cualquier número de etiquetas (es decir, ,) mientras que el valor predeterminado para un elemento sin estrella es coincidir exactamente una vez (es decir, 1).

  Hay varios modificadores que se pueden poner al final de una no estrella lquery elemento para que coincida con algo más que la coincidencia exacta:

  @           Matchcase-insensitively,for example [email protected] matches A
  *Matchany label with this prefix,for example foo* matches foobar
  %Match initial underscore-separated words
  

  El comportamiento de % es un poco complicado. Intenta hacer coincidir palabras en lugar de toda la etiqueta. Por ejemplo foo_bar% partidos foo_bar_baz pero no foo_barbaz. Si se combina con *, la coincidencia de prefijo se aplica a cada palabra por separado, por ejemplo foo_bar%* partidos foo1_bar2_baz pero no foo1_br2_baz.

  Además, puede escribir varios elementos sin estrella posiblemente modificados separados por | (O) para que coincida con cualquiera de esos elementos, y puede poner ! (NO) al comienzo de un grupo sin estrellas para que coincida con cualquier etiqueta que no coincida con ninguna de las alternativas. Un cuantificador, si lo hay, va al final del grupo; significa cierto número de coincidencias para el grupo en su conjunto (es decir, cierto número de etiquetas que coinciden o no coinciden con ninguna de las alternativas).

  Aquí hay un ejemplo anotado de lquery:

  Top.*0,2.sport*@.!football|tennis1,.Russ*|Spain
  a.  b.     c.      d.                   e.

  Esta consulta coincidirá con cualquier ruta de etiqueta que:

  1. comienza con la etiqueta Top

  2. y siguiente tiene de cero a dos etiquetas antes

  3. una etiqueta que comienza con el prefijo que no distingue entre mayúsculas y minúsculas sport

  4. luego tiene una o más etiquetas, ninguna de las cuales coincide football ni tennis

  5. y luego termina con una etiqueta que comienza con Russ o exactamente igual Spain.

• ltxtquery representa un patrón de búsqueda de texto completo para hacer coincidir ltree valores. Un ltxtquery el valor contiene palabras, posiblemente con los modificadores @, *, % al final; los modificadores tienen los mismos significados que en lquery. Las palabras se pueden combinar con & (Y), | (O), ! (NO) y paréntesis. La diferencia clave de lquery es eso ltxtquery coincide con las palabras sin tener en cuenta su posición en la ruta de la etiqueta.

  Aquí hay un ejemplo ltxtquery:

  Europe & Russia*@ &!Transportation
  

  Esto coincidirá con las rutas que contienen la etiqueta. Europe y cualquier etiqueta que comience con Russia (no distingue entre mayúsculas y minúsculas), pero no las rutas que contienen la etiqueta Transportation. La ubicación de estas palabras dentro del camino no es importante. Además, cuando % se utiliza, la palabra se puede hacer coincidir con cualquier palabra separada por guiones bajos dentro de una etiqueta, independientemente de la posición.

Nota: ltxtquery permite espacios en blanco entre símbolos, pero ltree y lquery no.

F.21.2. Operadores y funciones

Escribe ltree tiene los operadores de comparación habituales =, <>, <, >, <=, >=. La comparación se ordena en el orden de recorrido de un árbol, con los hijos de un nodo ordenados por texto de etiqueta. Además, están disponibles los operadores especializados que se muestran en la Tabla F.13.

Cuadro F.13. ltree Operadores

Los operadores <@, @>, @ y ~ tener análogos ^<@, ^@>, ^@, ^~, que son iguales excepto que no usan índices. Estos son útiles solo con fines de prueba.

Las funciones disponibles se muestran en la Tabla F.14.

Cuadro F.14. ltree Funciones

F.21.3. Índices

ltree admite varios tipos de índices que pueden acelerar los operadores indicados:

• Índice de árbol B sobre ltree: <, <=, =, >=, >

• Índice GiST sobre ltree (gist_ltree_ops opclass): <, <=, =, >=, >, @>, <@, @, ~, ?

  gist_ltree_ops La opclass GiST se aproxima a un conjunto de etiquetas de ruta como una firma de mapa de bits. Su parámetro de entero opcional siglen determina la longitud de la firma en bytes. La longitud predeterminada de la firma es de 8 bytes. Los valores válidos de longitud de la firma están entre 1 y 2024 bytes. Las firmas más largas conducen a una búsqueda más precisa (escaneando una fracción más pequeña del índice y menos páginas del montón), a costa de un índice más grande.

  Ejemplo de creación de un índice de este tipo con la longitud de firma predeterminada de 8 bytes:

  CREATEINDEX path_gist_idx ON test USING GIST (path);

  Ejemplo de creación de un índice de este tipo con una longitud de firma de 100 bytes:

  CREATEINDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100));

• Índice GiST sobre ltree[] (gist__ltree_ops opclass): ltree[] <@ ltree, ltree @> ltree[], @, ~, ?

  gist__ltree_ops GiST opclass funciona de manera similar a gist_ltree_ops y también toma la longitud de la firma como parámetro. El valor predeterminado de siglen en gist__ltree_ops es de 28 bytes.

  Ejemplo de creación de un índice de este tipo con la longitud de firma predeterminada de 28 bytes:

  CREATEINDEX path_gist_idx ON test USING GIST (array_path);

  Ejemplo de creación de un índice de este tipo con una longitud de firma de 100 bytes:

  CREATEINDEX path_gist_idx ON test USING GIST (array_path gist__ltree_ops(siglen=100));

  Nota: este tipo de índice tiene pérdidas.

F.21.4. Ejemplo

Este ejemplo utiliza los siguientes datos (también disponibles en el archivo contrib/ltree/ltreetest.sql en la distribución fuente):

CREATETABLE test (path ltree);INSERTINTO test VALUES('Top');INSERTINTO test VALUES('Top.Science');INSERTINTO test VALUES('Top.Science.Astronomy');INSERTINTO test VALUES('Top.Science.Astronomy.Astrophysics');INSERTINTO test VALUES('Top.Science.Astronomy.Cosmology');INSERTINTO test VALUES('Top.Hobbies');INSERTINTO test VALUES('Top.Hobbies.Amateurs_Astronomy');INSERTINTO test VALUES('Top.Collections');INSERTINTO test VALUES('Top.Collections.Pictures');INSERTINTO test VALUES('Top.Collections.Pictures.Astronomy');INSERTINTO test VALUES('Top.Collections.Pictures.Astronomy.Stars');INSERTINTO test VALUES('Top.Collections.Pictures.Astronomy.Galaxies');INSERTINTO test VALUES('Top.Collections.Pictures.Astronomy.Astronauts');CREATEINDEX path_gist_idx ON test USING GIST (path);CREATEINDEX path_idx ON test USINGBTREE(path);

Ahora tenemos una mesa test poblado con datos que describen la jerarquía que se muestra a continuación:

                        Top
                     /   |  
             Science Hobbies Collections
                 /       |              
        Astronomy   Amateurs_Astronomy Pictures
           /                              |
Astrophysics  Cosmology                Astronomy
                                        /  |    
                                 Galaxies Stars Astronauts

Podemos hacer herencia:

ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
                path
------------------------------------
 Top.Science
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(4 rows)

A continuación, se muestran algunos ejemplos de coincidencia de rutas:

ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
                     path
-----------------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Collections.Pictures.Astronomy
 Top.Collections.Pictures.Astronomy.Stars
 Top.Collections.Pictures.Astronomy.Galaxies
 Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)

ltreetest=> SELECT path FROM test WHERE path ~ '*[email protected]*';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

A continuación, se muestran algunos ejemplos de búsqueda de texto completo:

ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & [email protected]';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Hobbies.Amateurs_Astronomy
(4 rows)

ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & [email protected]';
                path
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

Construcción de caminos usando funciones:

ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
                 ?column?
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

Nosotros podría simplificar esto creando una función SQL que inserta una etiqueta en una posición específica en una ruta:

CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
    AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
    LANGUAGE SQL IMMUTABLE;

ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy';
                ins_label
------------------------------------------
 Top.Science.Space.Astronomy
 Top.Science.Space.Astronomy.Astrophysics
 Top.Science.Space.Astronomy.Cosmology
(3 rows)

F.21.5. Transforma

Hay extensiones adicionales disponibles que implementan transformaciones para el ltree escriba para PL / Python. Las extensiones se llaman ltree_plpythonu, ltree_plpython2u, y ltree_plpython3u (ver Sección 45.1 para la convención de nomenclatura PL / Python). Si instala estas transformaciones y las especifica al crear una función, ltree los valores se asignan a listas de Python. (Sin embargo, actualmente no se admite lo contrario).

Precaución

Se recomienda encarecidamente que las extensiones de transformación se instalen en el mismo esquema que ltree. De lo contrario, existen riesgos de seguridad durante la instalación si el esquema de una extensión de transformación contiene objetos definidos por un usuario hostil.

F.21.6. Autores

Todo el trabajo fue realizado por Teodor Sigaev (<[email protected]>) y Oleg Bartunov (<[email protected]>). Ver http://www.sai.msu.su/~megera/postgres/gist/ para informacion adicional. Los autores desean agradecer a Eugeny Rodichev por sus útiles discusiones. Los comentarios y los informes de errores son bienvenidos.