SQL Scripts - Does the equivalent of a #define exist?

Question

I have a script that I use to construct both the tables and stored procedures. For example I have a column of type varchar. varchar requires a size parameter, that size I also use as parameters in stored procedures and within those procedures.

is it possible to have thequivalentnt of a #define for its size, so I can easily adjust the size without the necessity of having to change ithroughht the whole of the script?

I am using MySql workbench.

EDIT

I have tried SET and DECLARE

I have a script - this is (abridged)

CREATE TABLE `locations`
(
   `location`  VARCHAR(25)        NOT NULL
);

...
CREATE PROCEDURE AddLocation (IN  location VARCHAR(25)
BEGIN
...
END$$

What I am trying to achieve is replace the values 25 in the script with a constant - similar to a #define at the top of the script that creates the table and stored procedures, so I am able to easily change the 25 to another number.

Anybody has found a solution to this problem?

Answer 1

The C Pre Processor (cpp) is historically associated with C (hence the name), but it really is a generic text processor that can be used (or abused) for something else.

Consider this file, named location.src (more on that later).

// C++ style comments works here
/* C style works also */
-- plain old SQL comments also work,
-- but you should avoid using '#' style of comments,
-- this will confuse the C pre-processor ...

#define LOCATION_LEN 25

/* Debug helper macro */
#include "debug.src"

DROP TABLE IF EXISTS test.locations;
CREATE TABLE test.locations
(
   `location` VARCHAR(LOCATION_LEN) NOT NULL
);

DROP PROCEDURE IF EXISTS test.AddLocation;
delimiter $$
CREATE PROCEDURE test.AddLocation (IN location VARCHAR(LOCATION_LEN))
BEGIN
  -- example of macro
  ASSERT(length(location) > 0, "lost or something ?");

  -- do something
  select "Hi there.";
END
$$

delimiter ;

and file debug.src, which is included:

#ifdef HAVE_DEBUG
#define ASSERT(C, T)                                          \
  begin                                                       \
    if (not (C)) then                                         \
      begin                                                   \
        declare my_msg varchar(1000);                         \
        set my_msg = concat("Assert failed, file:", __FILE__, \
                            ", line: ", __LINE__,             \
                            ", condition ", #C,               \
                            ", text: ", T);                   \
        signal sqlstate "HY000" set message_text = my_msg;    \
     end;                                                     \
    end if;                                                   \
  end
#else
#define ASSERT(C, T) begin end
#endif

When compiled with:

cpp -E location.src -o location.sql

you get the code you are looking for, with cpp expanding #define values.

When compiled with:

cpp -E -DHAVE_DEBUG location.src -o location.sql

you get the same, plus the ASSERT macro (posted as a bonus, to show what could be done).

Assuming a build with HAVE_DEBUG deployed in a testing environment (in 5.5 or later since SIGNAL is used), the result looks like this:

mysql> call AddLocation("Here");
+-----------+
| Hi there. |
+-----------+
| Hi there. |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call AddLocation("");
ERROR 1644 (HY000): Assert failed, file:location.src, line: 24, condition length(location) > 0, text: lost or something ?

Note how the file name, line number, and condition points right at the place in the source code in location.src where the assert is raised, thanks again to the C pre processor.

Now, about the ".src" file extension:

• you can use anything.
• Having a different file extension helps with makefiles, etc, and prevents confusion.

EDIT: Originally posted as .xql, renamed to .src for clarity. Nothing related to xml queries here.

As with any tools, using cpp can lead to good things, and the use case for maintaining LOCATION_LEN in a portable way looks very reasonable. It can also lead to bad things, with too many #include, nested #ifdef hell, macros, etc that at the end obfuscate the code, so your mileage may vary.

With this answer, you get the whole thing (#define, #include, #ifdef, __FILE__, __LINE__, #C, command line options to build), so I hope it should cover it all.

Answer 2

Have you tried SET?

here is an example :

SET @var_name = expr

more examples here : http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Answer 3

It sounds like you're looking for user defined data types. Unfortunately for us all mySQL doesn't yet support user defined data types like SQL Server, Oracle, and others do.

Here's a list of supported data types: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Answer 4

For those that are interested:

I ended up writing a PHP script because:

a) The machine that can access the database does not belong to me and I cannot access the C preprocessor b) The other the two answers do not work. c) Seemed the simplest solution

Here is the script for those who might find it useful. I am using it to define the tables column widths and then use those same values in the stored procedures. This is due to the column widths have not yet been fully decided for production.

I have also built in that you can define strings that last over a few lines. This has the advantage that I can obey the 80 column width (hence printing looks readable).

Here is the script

<?php

if (1==count($argv))
{
?>
Processing #defines from stdin and send to SQL server:

This script will remove
   1. #define <name> <integer>
   2. #define <name> '<string>'
   3. #define <name> '<string>' \
                     '<continuation of string>'

and replace the occurances of name with the #define value as specified 

<name> is upper case alpha numberics or underscores, not starting with a
digit.

The arguments of this script is passed to the mysql executable.
<?php
   exit(1);
}
function replace(&$newValues, $a, $b, $c)
{
   return $a . (array_key_exists($b, $newValues) ? $newValues[$b] : $b) . $c;
}

// The patterns to be used
$numberPattern='/^#define[ \t]+([A-Z_][A-Z0-9_]*)[ \t]+(0|([1-9][0-9]*))'.
               '[ \t]*[\r\n]+$/';
$stringPattern= '/^#define[ \t]+([A-Z_][A-Z0-9_]*)[ \t]+\''.
                '((\\\'|[^\'\r\n])*)\'[ \t]*(\\\\{0,1})[\n\r]+$/';
$continuationPattern='/^[ \t]*\'((\\\'|[^\'\r\n])*)\'[ \t]*'.
                     '(\\\\{0,1})[\n\r]+$/';

// String to be evaluated to replace define values with a new value
$evalStr='replace($newValues, \'\1\', \'\2\', \'\3\');'; 

array_splice($argv, 0, 1);
// Open up the process
$mysql=popen("mysql ".implode(' ', $argv), 'w');

$newValues=array(); // Stores the defines new values

// Variables to control the replacement process
$define=false;
$continuation=false;
$name='';
$value='';

while ($line=fgets(STDIN))
{
   $matches=array();

   // #define numbers
   if (!$define &&
       1 == preg_match($numberPattern, $line, $matches))
   {
      $define = true;
      $continuation = false;
      $name = $matches[1];
      $value = $matches[2];
   }

   // #define strings
   if (!$define &&
       1 == preg_match($stringPattern,
                       $line, $matches))
   {
      $define = true;
      $continuation = ('\\' == $matches[4]);
      $name = $matches[1];
      $value = $matches[2];
   }

   // For #define strings that continue over more than one line
   if ($continuation &&
       1 == preg_match($continuationPattern,
                       $line, $matches))
   {
      $value .= $matches[1];
      $continuation = ('\\' == $matches[3]);
   }

   // Have a complete #define, add to the array
   if ($define && !$continuation)
   {
      $define = $continuation = false;
      $newValues[$name]=$value;
   }
   elseif (!$define)
   {
      // Do any replacements
      $line = preg_replace('/(^| |\()([A-Z_][A-Z0-9_]*)(\)| |$)/e',
                           $evalStr, $line);
      echo $line; // In case we need to have pure SQL.
      // Send it to be processed.
      fwrite($mysql, $line) or die("MySql has failed!");
   }
}
pclose($mysql);
?>