Using the google graph API with MySQL stored functions

After reading Alex’s blog post on generating graphs using google’s graph API I decided to do something similar for MySQL. I created a stored function that takes a height, width and a graph type from the list of available graph types in the graph API. The function returns a url to a google graph that you can then use as you like. The possibilities are endless.

Besides the nice google graph API, the function shows how to use a cursor in combination with a handler to loop through it and retrieve values from it.

In the example I use a query that determines the relative size of each database on a MySQL server, but you can change that to whatever you like.

Follow the jump for the code.


DELIMITER $$

DROP FUNCTION IF EXISTS `temp`.`FNC_GOOGRAPH_DB_SIZE`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `FNC_GOOGRAPH_DB_SIZE`(
  p_chart_type CHAR,
  p_height INT,
  p_width INT) RETURNS varchar(255) CHARSET latin1
  READS SQL DATA
BEGIN
  /* Author:    Walter Heck - OlinData */
  /* Date:      20090216 */
  /* Note:      After an idea by Alex Gorbachev - Pythian */
  /*            http://www.pythian.com/blogs/1490/google-charts-for-dba-tablespaces-allocation */

  /* variable declaration */
  DECLARE v_done BOOLEAN default false;
  DECLARE v_url varchar(255);

  DECLARE v_schema_name varchar(255);
  DECLARE v_data_length_sum int;
  DECLARE v_data_length_total int;
    
  DECLARE v_legend_labels varchar(255);
  DECLARE v_chart_labels varchar(255);
  DECLARE v_chart_data varchar(255);

  /* Cursor declaration */
  DECLARE c_schema_sizes cursor for
      select
        t.table_schema,
        round(sum(t.data_length + t.index_length) / 1024 / 1024) as data_length_schema
      from
        information_schema.tables t
      group by
        t.table_schema
      order by
        t.table_schema;

  /* Handler declaration */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = true;

  /* Initialize the variables */
  SET v_legend_labels = ”;
  SET v_chart_labels = ”;
  SET v_chart_data = ”;

  /* Get the total data length + index_length for all tables */
  select
      round(sum(t.data_length + t.index_length) / 1024 / 1024) as data_length_total
  into
      v_data_length_total
  from
      information_schema.tables t;
    
  /* Open the cursor */
  OPEN c_schema_sizes;

  /* Loop through the cursor */
  get_data: LOOP

      /* Fetch the next row of data into our variables */
      FETCH c_schema_sizes INTO v_schema_name, v_data_length_sum;

      /* if there is no more data, v_done will be true */
      IF v_done THEN
        /* Exit the loop */
        LEAVE get_data;
      END IF;

      /* Add the schema name to the labels for the legend */
      IF v_legend_labels = ” THEN
        SET v_legend_labels = v_schema_name;
      ELSE
        SET v_legend_labels = concat(v_legend_labels, ‘|’, v_schema_name);
      END IF;

      /* Add the total size of the schema to the labels */
      IF v_chart_labels = ” THEN
        SET v_chart_labels = v_data_length_sum;
      ELSE
        SET v_chart_labels = concat(v_chart_labels, ‘|’, v_data_length_sum);
      END IF;

      /* Get the percentage of the total size as the graph’s data */
      IF v_chart_data = ” THEN
        SET v_chart_data = ROUND(v_data_length_sum / v_data_length_total, 2) * 100;
      ELSE
        SET v_chart_data = concat(v_chart_data, ‘,’, ROUND(v_data_length_sum / v_data_length_total, 2) * 100);
      END IF;

  END LOOP get_data;

  /* Close the cursor */
  CLOSE c_schema_sizes;
    
  /* Build up the google graph url */
  SET v_url = ‘http://chart.apis.google.com/chart?’;
  SET v_url = CONCAT(v_url, ‘cht=’, p_chart_type);
  SET v_url = CONCAT(v_url, ‘&chs=’, p_width , ‘x’, p_height);
  SET v_url = CONCAT(v_url, ‘&chtt=Database Sizes’);
  SET v_url = CONCAT(v_url, ‘&chl=’, v_chart_labels);
  SET v_url = CONCAT(v_url, ‘&chd=t:’, v_chart_data);
  SET v_url = CONCAT(v_url, ‘&chdl=’, v_legend_labels);

  /* return the url as the function’s result */
  RETURN v_url;
END$$

DELIMITER ;

Tags: , , ,

11 Responses to “Using the google graph API with MySQL stored functions”

  1. Shlomi Noach says:

    Kudos!
    Was just searching for something similar.
    Would love to see a sample function call.

    Regards

  2. Walter Heck says:

    Here’s a call:

    mysql> select temp.FNC_GOOGRAPH_DB_SIZE(’p', 200,400);
    +——————————————————————————————————————————————————————————————————————————+
    | temp.FNC_GOOGRAPH_DB_SIZE(’p', 200,400) |
    +——————————————————————————————————————————————————————————————————————————+
    | http://chart.apis.google.com/chart?cht=p&chs=400×200&chtt=Database Sizes&chl=20|38|235|2|0|1|7|0&chd=t:7.00,13.00,78.00,1.00,0.00,0.00,2.00,0.00&chdl=bible|yadda1|yadda2|yadda3|information_schema|mysql|sakila|world |
    +——————————————————————————————————————————————————————————————————————————+
    1 row in set (1.69 sec)

  3. Shlomi Noach says:

    Hi,

    thanks. Allow me to recommend that you post SQL code within quotes, and eliminate WordPress’s nasty habit of replacing \’ and \” with left-and-right matching quotes.

    Regards

  4. Matthew Montgomery says:

    Hello,

    It would be very nice if you could post this code snippet on http://forge.mysql.com/tools/ We’re trying to build a repository for this sort of clever SQL foo.

  5. [...] dibuja gráficas usando Google Graph mediante procedimientos almacenados de MySQL. Está sacado de este ejemplo, que a su vez está sacado de este otro para [...]

  6. [...] dibuja gráficas usando Google Graph mediante procedimientos almacenados de MySQL. Está sacado de este ejemplo, que a su vez está sacado de este otro para [...]

  7. This is great! We’ve added it to the list of related links in the documentation:

    http://groups.google.com/group/google-chart-api/web/useful-links-to-api-libraries?hl=en

    Thanks and keep up the cool work!

    – The Google Chart API Team

  8. [...] Faster MySQL Database Size Google Chart Abstract – As described by Walter Heck, MySQL database size can be visualized using Google Charts. With a minor code [...]

Leave a Reply