Stored procedure reflection API

15 Mar 2016 - by 'Maurits van der Schee'

If you are following this blog, then you know I'm working a lot on API software architectures. I noticed that a lot of people that are building an API are actually building the same thing (except for the data model they expose). Typically they simply expose their tables using JSON REST operations (typically Create, Read, Update and Delete) or they expose their more sophisticated stored procedures via their API. This post is about that last category.

Stored Procedures

A stored procedure is code in SQL or in a procedural variant that can have input, output and in/out variables. It has a name and it may return one or more result sets. The simplest stored procedure you may think of looks something like this:

DELIMITER ;;
DROP PROCEDURE IF EXISTS `get_home_page_posts`;;
CREATE PROCEDURE `get_home_page_posts`()
BEGIN
  select * from posts limit 10;
END;;

This stored procedure has no input or output variables and has one 'unbound' select statement and thus returns one result set. Now consider this stored procedure:

DELIMITER ;;
DROP PROCEDURE IF EXISTS `get_post_count_for_user`;;
CREATE PROCEDURE `get_post_count_for_user`(IN user_id INT,OUT post_count BIGINT)
BEGIN
  SELECT count(posts.id) INTO post_count FROM posts WHERE posts.user_id = user_id;
END;;

This stored procedure has one input and one output parameter. Since it does not have any 'unbound' select statements it does not return a result set.

Reflection

The 'INFORMATION_SCHEMA' reflection database has a view named 'ROUTINES' that can be used for inspection of the defined stored procedures. There is also a view called 'PARAMETERS' that can be used to learn more about the parameters of the stored procedures.

SELECT 
  `SPECIFIC_NAME`
FROM
  `INFORMATION_SCHEMA`.`ROUTINES`
WHERE 
  `ROUTINE_TYPE` = 'PROCEDURE' AND
  `ROUTINE_SCHEMA` = 'my_database' AND
  `SPECIFIC_NAME` = 'get_post_count_for_user';

This can be used to detect whether or not a stored procedure exists.

SELECT
  `ORDINAL_POSITION`,
  `PARAMETER_MODE`,
  `PARAMETER_NAME`,
  `DTD_IDENTIFIER`
FROM
  `INFORMATION_SCHEMA`.`PARAMETERS`
WHERE
  `SPECIFIC_SCHEMA` = 'my_database' AND
  `SPECIFIC_NAME` = 'get_post_count_for_user'
ORDER BY
  `ORDINAL_POSITION`;

When this SQL query is executed it will return:

+------------------+----------------+----------------+----------------+
| ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DTD_IDENTIFIER |
+------------------+----------------+----------------+----------------+
|                1 | IN             | user_id        | int(11)        |
|                2 | OUT            | post_count     | bigint(20)     |
+------------------+----------------+----------------+----------------+
2 rows in set (0,00 sec)

As you can see it contains important details about the parameters of the stored procedure.

First release

So, I used these commands to create a very first reflection based, thus generic, stored procedure API. You can find PHP-SP-API on my Github page. It allows you to list and execute stored procedures. It is easy to use: enter your database configuration in the "api.php" file and upload it and you are ready to go! Here is how you can invoke it if you have a Linux box with PHP enabled and the file in '/var/www/html':

curl 'http://localhost/api.php/get_post_count_for_user' --data 'user_id=1'

This will actually execute the stored procedure above and return:

[[{"post_count":"12"}]]

Note that this is a response consisting of a set of result sets. In this case there are no result sets, so you might expect an empty answer. This is not the case as a pseudo result set is added that contains the output parameters of the stored procedure (always single row).

What's next

In the end I hope this script will be just as successful as it's bigger brother, the PHP-CRUD-API project. Right now this project is in proof-of-concept state and only does some bare minimum tasks. I will be adding features one-by-one, such as database drivers for PostgreSQL and SQLServer and Swagger API specification support (for documentation).

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.

TQ
dev.com