Descubriendo la metadata de una tabla (postgreSQL)
Mientras desarrollaba una idea de un ORM básico para PHP que auto-descubriera la metadata de una tabla (tal y como se hacen en mySQL con “DESCRIBE”), me encuentro con el problema de que en postgreSQL no existe nada parecido a DESCRIBE o a SHOW CREATE de mySQL; por ende, nos toca interrogar a los pg_catalogs para obtener la información de metadatos de la tabla.
En este caso, queriamos obtener una estructura semejante a esta:
column_name => cedula, datatype=>integer, key=>PRI, is_nullable=>NO, max_length=>8, column_default =>
Donde:
datatype: es un tipo de datos interno de postgresql
key: UNI = Unique, el campo es un indice unico, PRI = Primary, el campo es un indice primario, FK = Foreign key, el campo es un indice de una clave foránea.
max_length: Si el campo es integer, muestra la precisión del entero (2,4,8), si es un varchar, la longitud en caracteres (ej. 75)
column_default: muestra el tipo de valor por defecto de la tabla; si la tabla es serial, veremos la llamada al nextval de la secuencia:
ej. nexval(’personas_cliente_id_seq’::regclass)
Lo que nos permite determinar que campo de nuestra tabla es serial (auto-incremental).
Entendiendo los esquemas:
Para entender; cada tabla del pg_catalog debe ser interrogada con el oid de la tabla, que lo sacamos de pg_class.
Los campos y sus atributos, los sacamos de la tabla pg_attribute.
El tipo de datos lo sacamos de la tabla pg_type
los constraints de la tabla los obtenemos de la tabla pg_constraint
y el valor por defecto, lo sacamos de la tabla pg_attrdef.
La sentencia construida para sacar esa información de una sola vez de todas las tablas es esta:
SELECT a.attname as column_name, t.typname as data_type,
CASE
WHEN cc.contype=’p’ THEN ‘PRI’
WHEN cc.contype=’u’ THEN ‘UNI’
WHEN cc.contype=’f’ THEN ‘FK’
ELSE ” END AS key,
CASE WHEN a.attnotnull=false THEN ‘YES’ ELSE ‘NO’ END AS is_nullable,
CASE WHEN a.attlen=’-1′ THEN (a.atttypmod - 4) ELSE a.attlen END as max_length,
d.adsrc as column_default
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
LEFT JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
LEFT JOIN pg_catalog.pg_constraint cc ON cc.conrelid = c.oid AND cc.conkey[1] = a.attnum
LEFT JOIN pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND a.attnum = d.adnum
WHERE c.relname = ‘<TABLA>’ AND a.attnum > 0 AND t.oid = a.atttypid
donde <TABLA> representa el nombre de la tabla a la que queremos interrogar para obtener sus metadatos.
Espero que les sirva; en la siguiente entrega, descubrir los metadatos de una tabla de mySQL y de una entrada de LDAP.
February 26th, 2008 at 1:27 am
Loco prende ese telefono!!! llevo 2 semanas intentando hablar contigo.
February 26th, 2008 at 2:57 am
Mmm, estas seguro que ninguno de los drivers/librerias de php para pgsql implementa esto ya? Suena como fastidioso hacer esto, ya que es posible que lo necesites siempre.
Me refiero, ya lo he hecho, hace ya bastantes años, con Java y JDBC, y esta tarea está definida en las API estandar, e implementada por los drivers especificos de pgsql, mysql, y un largo etc.
Un caso practico es algo asi como:
ResultSet rs = stmt.executeQuery(”SELECT campo, campo2, campo3 FROM mitabla”);
ResultSetMetaData rsmd = rs.getMetaData();
Y obtengo la data con getColumnName(1), getColumnType(1), getColumnTypeName(1), getPrecision(1) , etc etc etc.
Y no tienes que reimplementar el codigo para cada DBMS , suena mucho mas consistente
Saludos.
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSetMetaData.html
February 26th, 2008 at 6:25 pm
saludos, jesus queria hacerte una pregunta en cuanto a tu framework tomates que ha pasado con este proyecto? tienes algún svn? etc una vez me lo dieron para probarlo y me gusto bastante la forma como se manejan muchas cosas y me gustaria ensuciarme mas las manos con este framework y bueno de alguna manera ayudar en lo que se pueda…
Éxitos…
February 27th, 2008 at 7:39 am
Buenas compa, espero que no te moleste que te pida un consejo sobre este tema, yo en este momento estoy muy interesado en aprender sobre diseño y mantenimiento de base de datos en Linux y me interesa principalmente dos aplicaciones, una es Gambas y la otra PostgreSQL pero me toca aprender por mi cuenta porque no he encontrado ninguna institución que brinde cursos sobre este tema y dado que me encontré con este articulo quisiera aprovechar para pedirte algún consejo sobre la forma más eficiente de aprender a trabajar con PostgreSQL por mi cuenta. Cualquier consejo sobre este tema es bienvenido y si te parece que mi petición está de más lo entenderé.
saludos…
March 2nd, 2008 at 12:53 pm
Fenix; estoy en proceso de transferirte tu dominio; en estos momentos con los peos de dólares me es dificil mantener un ancho de banda abultado y otras cosas en el server; además lo tengo dedicado a más de 10 clientes en este momento (+trabajo, +viajes, +etc, +etc); dame chance que esté un poco más desocupado y hablamos si?…
Mil disculpas …
March 2nd, 2008 at 1:00 pm
Gerardo; ventajas de los que tienen un namespace!; en el caso de PHP no existe; por lo que cada “extensión” de conector de datos (pgsql, oci, mysql, mysqli, etc) tiene una forma COMPLETAMENTE DISTINTA de extraer los metadatos; de esta manera, hay una unica forma probable (y para mí más limpia y compatible entre todas las DB) que es interrogar directamente al information_schema (en el caso de LDAP, al schema respectivo) e iterar un único resultset; en mi último test de velocidad con un debug profiler; consultar el information_schema es mucho más rápido que consultar + luego pedir los metadatos (aunque en la extensión mysqli los metadatos si se vienen junto con la consulta); ¿a que se debe esta “distrofia” en las extensiones?; no sé; es de esas incongruencias de PHP que a veces fastidian; aunque si te soy sincero, prefiero consultar para luego interar un resultset; que hacer llamadas a x cantidad de funciones para cada una de las cosas que requiero conocer de los metadatos de una tabla.
Ya entiendes por qué la gente prefiere hacer un mapa de la tabla en XML o en YAML en vez de pegarse en vivo en la DB?; amerita conocer la estructura DDL de todas las DB a las que les quieras hacer un conector ORM.
March 2nd, 2008 at 1:04 pm
Hola Cachi; el proyecto tiene SVN más aún no tiene página (mis labores no me permiten dedicarle todo el tiempo que quisiera); el repo es http://svn.covetel.com.ve/tomates/trunk/; el SVN permite checkout anónimo así que os puedes descargar sin problemas.
Claro que puedes participar!; si deseas ensuciarte las manos con código; puedo darte posibilidades de commit al SVN y cosas así; hacerte llegar la documentación (que aun es escasa :p) y en lo que puedas colaborar estoy pendiente! … pronto haré un artículo y colocaré una página oficial.
March 2nd, 2008 at 1:12 pm
La forma más práctica yo la dividiría en 3 ramas (ninguna te costará mucho); la primera es instalar un postgreSQL 8 en tu máquina y descargar proyectos que usen postgreSQL como backend para que entiendas algo de como se construyen databases, schemas, tables, constraints, etc.
La segunda forma es el PDF oficial; tiene muy buena información (creo que ya está portado al español).
La tercera forma (super-practica) es que crees una DB demo y comiences a jugar con sus opciones; toma bases de datos mySQL y usa un ejemplo como (http://www.jesuslara.com.ve/blog/2007/05/13/experimento-con-datos-migrando-a-postgresql-tercera-parte/) para migrar datos a postgreSQL.
La otra cosa que debes conocer es ANSI SQL; las bases de datos en SL son muy respetuosas del ANSI SQL 99/2003; así que no tendrás muchos problemas en entender la forma de gestión.
En cuanto a gambas, tengo entendido que es un Basic más orientado a objetos que VB y con soporte a formularios via GTK; no he visto mucho por ahi ni ejemplos ni nada; si logras algo avisas! …
Suerte!
March 2nd, 2008 at 1:14 pm
Se me olvidaba Fral la cuarta forma no comentada (es obvia así que se me olvidó ponerla! jajaja), pregunta!, estamos a la orden …
March 2nd, 2008 at 4:10 pm
Ah bueno excelente si puedes hacerme llegar la Documentación así sea escasa no importa y bueno la idea es ayudar en lo que se pueda.
deivinsontejeda (at) gmail.com
March 7th, 2008 at 12:38 am
Muchas gracias por tu respuesta amigo y tendré muy en cuenta tus consejos, ya creo tener suficiente material para empezar el proceso así que a ver a como nos toca jeje.
Gracias de nuevo y que estés bien.
saludos…
July 4th, 2008 at 1:13 am
Hola como estas? debo hacer una conexion a una o varias bases de datos.. estoy desarrollando bajo kumbia php apache msql.. es decir con una LAMP.. necesito q a traves de unos parametros de conexion que ya tengo solicitados pueda saber q bases de datos le pertenece o q coincida con esos parametros.. y poder llenar un combo con esas bases de datos y a sus llenar otro con los tablas.. Cualquier ayuda sera valorada.. Gracias de antemano
July 6th, 2008 at 7:56 pm
Hola Veronica; no discutiré si usas kumbia (en mi gusto, prefiero codeIgniter o en su defecto Prado) pero obtener la metadata (que es lo que tu necesitas) para mySQL es bastante sencillo; simplemente ejecuta:
SHOW DATABASES;
Para obtener una lista de todas las bases de datos que el usuario con el que te estás conectando tiene derecho a ver; posteriormente ejecutas:
SHOW TABLES;
para ver las tablas que posee el sistema
Como ultimo al seleccionar una tabla; puedes ejecutar:
SHOW COLUMNS FROM nombredetabla
o un DESCRIBE nombredetabla
Para obtener la metadata de la tabla; es esto lo que necesitas?
July 21st, 2008 at 12:10 pm
Hola chino! Soy nueva con PostgreSQL, y mi pregunta es la siguiente… Tengo una base de datos que tiene aproximadamente 727 tablas, y me piden una aplicación para generar reportes… Según lo que he empezado a investigar, aplicaciones como esa ya existen en el mercado, y bueno supongo que bastará guiarme por una de ellas, para poder trabajar… Mi dilema está en que me piden que debo llevar esas tablas a su mínima expresión para poder trabajar en postgresql junto php para generar los reportes… Como haría para poder reducir el número de tablas?? dado que en el reporte debo mostrar, las relaciones que existen entre varias tablas…
Gracias..
July 22nd, 2008 at 12:45 pm
Hola!, novata en este mundo de postgre y PHP. Pasa lo siguiente: Debo realizar un generador de reportes que me permita de una tabla detalle cualquiera, mostrar la o las maestro que están relacionadas a ellas, y a su vez, poder escoger uno o varios campos de una de esas tablas maestro. El código que tienes por allí funciona para generarme las tablas detalles, pero realmente desconozco como hacer para generar las maestro a partir de la detalle seleccionada.. La base de datos con la cual estoy trabajando posee 727 tablas y la idea principal es que pueda a partir de este código minimizar la cantidad de tablas, pero también poder utilizar cualquier campo de las tablas maestro cuando sea requerido… Crees tener algo que pueda servirme??? Te lo agradecería enormemente…..
P.D. Phenobarbital con soda…?? Buen seudónimo para tu blog!!