Grimpi IT Blog

enero 8, 2011

Desglosando la metada en PostgreSQL 8.2 o superior

Filed under: Metada, PostgreSQL — grimpi @ 2:59 pm

Después de haber probado como extraer la información del schema de todas las bases de datos del mercado, debo decir que por lejos, SQL Server tiene el método mas sencillo y potente de acceso a su metada y PostgreSQL el método mas entrincado y críptico. No me refiero a traer un simple listado de tablas, sino a cosas más complejas, como por ejemplo traer el detalle de las constraints o de los índices.

Traer todas las tablas de una base de datos:

SELECT c.* FROM pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’ and pg_catalog.pg_table_is_visible(c.oid)
and n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)

Con esto le decimos que nos traiga todos los objetos de la base de tipo tabla que no sean invisibles y que no pertenezcan a los schemas pg_catalog y pg_toast, que son schemas internos de PostgreSQL.
El campo relkind determina qué tipo de objeto queremos buscar. Esta es la tabla de equivalencias:

r Table
S Sequence
i Index
v View
c Composite Type


Traer todas las primary key con sus columnas por tabla de una base de datos:

SELECT cc.relname as tabla, cc.oid as cid, a.attnum, c.conname as Name, a.attname, c.oid as ID FROM pg_constraint c, pg_class cc, pg_attribute a
WHERE contype = ‘p’
and cc.oid = c.conrelid
and a.attrelid = cc.oid
and a.attnum = ANY(c.conkey)
order by cc.oid, c.oid

La table pg_constraint nos trae todas las constraints y el campo contype el tipo de constraint. Esta es la tabla de equivalencias:

p Primary Key
u Unique Key
f Foreign Key
c Check Constraint
t Trigger Constraint
X Exclusive Constraint


Traer el listado de permisos de un objeto:

Dentro de la tabla pg_class, existe un campo vectorial llamado relacl, que contiene una lista de todos los permisos del objeto.

SELECT relacl FROM pg_class

Este campo devuelve un valor similar a este: “{=awdxt/postgres,postgres=awdxt/postgres,crm=arw*d*x*t*/postgres}”. Que significa este resultado?
En esa cadena de string aparentemente inentendible, tenemos toda la información sobre los permisos del objeto.
Recordemos que este campo es un vector, por lo tanto, cada carácter “,” significa un nuevo ítem en el vector. Por lo tanto, este resultado de ejemplo, tiene 3 ítems. Cada posición del vector, corresponde a un usuario/grupo distinto con permisos en el objeto.
Cada ítem del vector, tiene 3 datos: Usuario, Permisos y Owner del objeto.
Ejemplo: “crm=arw*d*x*t*/postgres”
En este caso, el usuario es crm (es el valor que esta antes del “=”), los permisos son “arw*d*x*t*” y el owner es postgres.
Ahora, que significa “arw*d*x*t*” realmente? Simple: cada uno de estos caracteres, es una operación posible (INSERT, DELETE, etc) sobre el objeto.
Esta es la tabla de equivalencias:

“a” INSERT
“r” SELECT
“w” UPDATE
“d” DELETE
“x” REFERENCES
“R” RULE
“t” TRIGGER
“X” EXECUTE
“U” ALTER
“c” CONNECT
“C” CREATE
“*” HABILITA LA OPCION GRAN OPTION A CADA OPERACION

Por lo tanto, este objeto tiene permisos de INSERT, SELECT, UPDATE, DELETE, TRIGGER y REFERENCES, de las cuales para las operaciones UPDATE, DELETE, REFERENCES y TRIGGER, la opción WITH GRAN OPTION esta activada.

=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO public;

(si no aparece un nombre usuario, significa que el permiso pertenece al rol “public”)

postgres=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO postgres;

 

crm=arw*d*x*t*/postgres GRANT INSERT, SELECT ON TABLE “Nombre de la tabla” TO “crm”;

GRANT UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO “crm” WITH GRANT OPTION;

 



Traer todos los sequences de la base de datos

SELECT * FROM pg_class c where relkind = ‘S’

El problema de esta query, es que nos trae solo el nombre del sequence, pero no el detalle del mismo (valor máximo y mínimo, valor actual, etc.). Si solo queremos el nombre, perfecto, pero si necesitamos mas, la tabla pg_class no nos sirve.
Para traer entonces el detalle de la información de un sequence simplemente tenemos que hacer un SELECT sobre el mismo como si fuera una vista o una tabla.

SELECT * FROM “nombre_del_sequence”

sequence_name last_value start_value increment_by max_value min_value cache_value is_cycled
nombre_del_sequence 1 1 1 922337203685 1 1 f

Dejo en este link algunos scripts de ejemplo de para extraer la metadata de distintos objetos de PostgreSQL.

septiembre 28, 2008

sql_expression_dependencies y Filtered Index en SQL Server 2008

Filed under: Metada, SQL Server, SQL Server 2008 — Etiquetas: , — grimpi @ 2:06 am

SQL Server incorpora montón de nuevas novedades, muy interesantes.
Una de ellas, son los Índices Filtrados, (Filtered Index), que permite indexar pero solo para determinado rango de valores dada una expresión. No es intención del post entrar en detalles de esto. Si quieren información al respecto, recomiendo ver este link.

Ahora, como hago para saber los objetos que están siendo referenciados en la expresión del filtro del índice? SQL Server 2008 incorpora una nueva vista de sistema, que se llama sys.sql_expression_dependencies y que contiene una fila para cada dependencia por nombre en una entidad definida por el usuario en la base de datos actual.

Dada esta vista, saber por ejemplo todas las columnas usadas dentro de expresiones en un índice filtrado, resulta sumamente fácil:

SELECT I.name AS IndexName, OBJECT_NAME(I.object_id) AS TableName, C.name
FROM sys.sql_expression_dependencies SED
INNER JOIN sys.indexes I ON I.object_id = SED.referencing_id AND I.index_id = SED.referencing_minor_id
INNER JOIN sys.columns C ON C.object_id = SED.referenced_id AND C.column_id = SED.referenced_minor_id

Para más información sobre esta Vista, ver este link.

Crea un blog o un sitio web gratuitos con WordPress.com.