Acceso al código fuente de los objetos ejecutables de la base de datos
Todos los principales servidores de base de datos permiten la creación de objetos ejecutables, como funciones o procedimientos almacenados, utilizando algún tipo de lenguaje de extensión del SQL, como PLSQL en Oracle o T-SQL en SQL Server, de manera que podemos crear aplicaciones de acceso y manipulación de datos más sofisticadas. En este artículo voy a mostrar cómo podemos acceder al código fuente de estos objetos desde cualquier programa utilizando las vistas del catálogo de tres de los RDBMS más populares, MySQL, Oracle y SQL Server.
Obtención de la lista de objetos de código
Los objetos de código que puedes encontrar en la base de datos son, básicamente, los procedimientos almacenados, las funciones y los disparadores. En Oracle también existen los paquetes, que son agrupaciones de varios procedimientos almacenados y funciones en un solo bloque de código.
MySQL
Las vistas del catálogo están en el esquema INFORMATION_SCHEMA, en concreto, para listar los procedimientos y funciones se utiliza la vista ROUTINES, mientras que para los disparadores existe la vista TRIGGERS.
Los campos más relevantes de la vista ROUTINES son los siguientes:
- ROUTINE_SCHEMA: Indica el esquema al que pertenece el objeto.
- ROUTINE_NAME: El nombre del objeto.
- ROUTINE_TYPE: El tipo del objeto, PROCEDURE o FUNCTION.
- DATA_TYPE: El tipo de datos que devuelve el objeto, si se trata de una función.
La vista TRIGGERS tiene una estructura similar:
- TRIGGER_SCHEMA: El esquema al que pertenece el disparador.
- TRIGGER_NAME: EL nombre del disparador.
- EVENT_MANIPULATION: El evento que lo desencadena.
- EVENT_OBJECT_SCHEMA: El esquema de la tabla a la que está ligada el disparador.
- EVENT_OBJECT_TABLE: El nombre de la tabla.
Con una sentencia como esta podemos listar todos los procedimientos almacenados, funciones y disparadores que tenemos en la base de datos:
select ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_TYPE,
DATA_TYPE as TYPE,
NULL as PARENT_SCHEMA,
NULL as PARENT_NAME
from INFORMATION_SCHEMA.ROUTINES
union
select TRIGGER_SCHEMA,
TRIGGER_NAME,
'TRIGGER',
EVENT_MANIPULATION,
EVENT_OBJECT_SCHEMA,
EVENT_OBJECT_TABLE
from INFORMATION_SCHEMA.TRIGGERS;
SQL Server
SQL Server también utiliza la vista INFORMATION_SCHEMA.ROUTINES, con una estructura muy similar a la de MySQL. Para obtener un listado de procedimientos almacenados y funciones podemos utilizar los mismos campos. Sin embargo, no existe la vista TRIGGERS. Podemos listar los disparadores utilizando una consulta como esta:
select st.name as [trigger schema],
t.name as [trigger name],
te.type_desc as [event],
s.name as [table schema],
o.name as [table name]
from sys.triggers t
join sys.objects o on t.parent_id = o.object_id
join sys.objects ot on t.object_id = ot.object_id
join sys.schemas s on o.schema_id = s.schema_id
join sys.schemas st on ot.schema_id = st.schema_id
join sys.trigger_events te on t.object_id = te.object_id
Oracle
En Oracle disponemos de una sola vista para listar todos los objetos que contienen código ejecutable, la vista ALL_PROCEDURES. Estos son los campos más relevantes:
- OWNER: Esquema donde está definido el objeto.
- OBJECT_NAME: El nombre del objeto.
- PROCEDURE_NAME: Donde se listan los diferentes nombres de procedimiento o función de un PACKAGE.
- OBJECT_TYPE: El tipo de objeto, que puede ser PROCEDURE, FUNCTION, PACKAGE o TRIGGER.
Para los objetos de tipo PACKAGE obtendremos un registro por cada uno de los procedimientos o funciones que contenga el paquete. Si solo queremos listar el nombre del paquete, podemos filtrar la consulta con el filtro PROCEDURE_NAME IS NULL:
select OWNER,
OBJECT_NAME,
OBJECT_TYPE
from all_procedures
where PROCEDURE_NAME IS NULL
Acceso al código fuente de los procedimientos, funciones y disparadores
Una vez que sabemos cuáles son los objetos ejecutables que contiene la base de datos, el siguiente paso es conocer cómo podemos acceder al código fuente. Esto nos puede ser útil a la hora de implementar editores o de buscar una determinada cadena de texto o expresión regular a través de todo el código contenido en la base de datos o uno o más esquemas determinados.
MySQL
El código fuente de un procedimiento almacenado o función lo podemos encontrar en el campo ROUTINE_DEFINITION de la vista ROUTINES de INFORMATION_SCHEMA. El problema es que en este campo se encuentra el cuerpo del procedimiento o función, pero no se indica nada sobre sus parámetros. Para esto debemos acudir a la vista INFORMATION_SCHEMA.PARAMETERS, cuyos campos más importantes son los siguientes:
- SPECIFIC_SCHEMA: Esquema donde se encuentra el procedimiento o la función.
- SPECIFIC_NAME: Nombre del procedimiento o función.
- ORDINAL_POSITION: Indica la posición del parámetro en la lista de llamada.
- PARAMETER_MODE: Indica si es un parámetro de entrada o de salida.
- PARAMETER_NAME: Nombre del parámetro.
- DTD_IDENTIFIER: La definición del parámetro con su tamaño y precisión.
Con estos datos y los que se encuentran en la vista INFORMATION_SCHEMA.ROUTINES podemos construir la sentencia CREATE PROCEDURE o CREATE FUNCTION para contemplar el código fuente del objeto. Esta sentencia, por ejemplo, obtiene los parámetros de uno de los procedimientos almacenados del esquema sakila que suele venir como ejemplo en MySQL:
select r.ROUTINE_TYPE,
r.ROUTINE_SCHEMA,
r.ROUTINE_NAME,
p.PARAMETER_MODE,
p.PARAMETER_NAME,
p.DTD_IDENTIFIER
from information_schema.routines r,
information_schema.parameters p
WHERE r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
AND r.ROUTINE_NAME = p.SPECIFIC_NAME
AND (r.ROUTINE_SCHEMA,r.ROUTINE_NAME) = ('sakila','film_in_stock')
ORDER BY p.ORDINAL_POSITION
SQL Server
En SQL Server el código fuente de los procedimientos almacenados y funciones también se encuentra en el campo ROUTINE_DEFINITION de INFORMATION_SCHEMA.ROUTINES, y además en este RDBMS sí que incluye la sentencia completa, con el CREATE PROCEDURE y sus parámetros, por lo que no es necesario recurrir a otras vistas para localizarlos.
En cualquier caso, si necesitamos información sobre los parámetros, podemos recurrir también a la vista INFORMATION_SCHEMA.PARAMETERS, al igual que en MySQL. Sin embargo, en este caso no disponemos del campo DTD_IDENTIFIER, por lo que si queremos saber el tamaño y la precisión de un parámetro, habrá que recurrir a los campos DATA_TYPE, para obtener el tipo de datos, CHARACTER_MAXIMUM_LENGTH, para campos de texto y NUMERIC_PRECISION y NUMERIC_SCALE para los tipos numéricos.
Otra forma de acceder al código fuente es utilizar la función OBJECT_DEFINITION de T-SQL. Debemos pasarle como parámetro el identificador del objeto, que podemos obtener con la función OBJECT_ID, pasándole el nombre del objeto como una cadena de texto de la forma esquema.nombre_de_objeto:
SELECT OBJECT_DEFINITION(OBJECT_ID(‘schema.procname’))
Esta función puede ser útil para obtener el código en procedimientos muy grandes, con los que la vista puede devolver un código fuente incompleto.
Oracle
Para terminar, vamos a ver cómo obtener el código fuente en una base de datos Oracle. Este es el más sencillo de todos los casos. Para ello, existe una vista del catálogo del sistema llamada ALL_SOURCE, que contiene un registro por cada línea de código. Este sistema permite tener código de un tamaño mucho mayor que el que permite cualquier tipo de datos pero, a cambio, perdemos la posibilidad de buscar texto que abarque más de una línea de código, debiendo reconstruir primero el texto en algún sitio antes de hacer la búsqueda.
Los campos que nos pueden interesar de la vista son:
- OWNER: Nombre del esquema que contiene el objeto.
- NAME: Nombre del objeto.
- LINE: El número de línea, imprescindible para ordenar el texto.
- TEXT: El texto de la línea.
Como en el caso anterior, el código incluye toda la definición del objeto, incluyendo la sentencia SQL completa que lo crea, pero si, por cualquier motivo, queremos obtener los parámetros, podemos utilizar la vista ALL_ARGUMENTS. Estos son los campos más útiles:
- OWNER: El esquema al que pertenece el objeto.
- OBJECT_NAME: Nombre del procedimiento o función.
- PACKAGE_NAME: Si el objeto está incluido en un paquete PLSQL, contiene el nombre de dicho paquete.
- ARGUMENT_NAME: Nombre del parámetro.
- POSITION: Número de orden del parámetro.
- DATA_TYPE: Tipo de datos del parámetro.
- IN_OUT: Indica si el parámetro es de entrada o de salida.
- CHAR_LENGTH: Tamaño del parámetro para los tipos de carácter.
- DATA_PRECISION y DATA_SCALE: Tamaño del campo para los tipos numéricos.
