QueryScript: SQL scripting language

February 8, 2012

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the 'sakila' database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
}

Shard your data across multiple schemata:

foreach($shard: {USA, GBR, JAP, FRA})
{
  CREATE DATABASE db_:$shard;
  CREATE TABLE db_:$shard.city LIKE world.City;
  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;
}

This tight integration between script and SQL, with the power of iteration, conditional statements, variables, variable expansion, throttling etc., makes QueryScript a power tool, with capabilities superseding those of stored routines, and allowing for simplified, dynamic code.

QueryScript code is interpreted. It's just a text, so it can be read from a @user_defined_variable, a table column, text file, what have you. For example:

mysql> set @script := "while (TIME(SYSDATE()) < '17:00:00') SELECT * FROM world.City WHERE id = 1 + FLOOR((RAND()*4079));";
mysql> call run(@script);

For more details, consult the QueryScript site.

If you're a MySQL user/DBA, better read the common_schema QueryScript documentation, to better understand the specific common_schema implementation and enhanced features.

common_schema, including the QueryScript interpreter, can be downloaded from the common_schema project page.

tags: , , , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

9 Comments to "QueryScript: SQL scripting language"

  1. ik_5 wrote:

    In PostgreSQL and Firebird we do not really need such tools.

    On FB PSQL have all of that, and on Postgre, we additional programming language to use in stored procedure.

    Here is how you declare a variable: http://www.firebirdsql.org/refdocs/langrefupd25-psql-declare.html

    Another example, this is for flow usage:
    http://www.firebirdsql.org/manual/nullguide-conditionals-loops.html

  2. shlomi wrote:

    @ik_5,
    Thanks.
    Can you also do meta-scripting, as suggested in the 2nd and 3rd examples? i.e. dynamically create tables, databases, etc?

  3. ik_5 wrote:

    I do not know regarding creating databases, but for creating tables:
    http://www.firebirdfaq.org/faq69/

    Also Fb and PostgreSQL support afaik temporary tables just for the execution block that will be removed when the block is done executing

  4. shlomi wrote:

    @ik_5
    firebird's coding abilities are impressive, and are easier to handle than MySQL's.

    But to pursue the subject you present with table creation via dynamic SQL: the invocation of a 'create table ...' string, dynamically executed. This means this ability is not integrated into the code. If you wanted the table name to be dynamic, you would have to make for text manipulation (using CONCAT() or whatever). Same goes for MySQL, and it's one of the things that make MySQL server side scripting a mess.
    Server side cursors are somewhat easier on firebird than on mysql, but still you have to declare-open-loop-fetch-check-exit-close.

    That is not to say I'm arguing against the firebird coding (as I suspect you are inclined to believe). But when you say FB "has all that" - I would rather be more precise on that.

  5. Holger Thiel wrote:

    Hello Shlomi,

    this is a very interesting feature for MySQL you have written. It is for sure not needed on PostgreSQL. But if you use MySQL it will be very nice indeed to do some things on the server - not in the client.

    Regards,
    Holger

  6. shlomi wrote:

    @Holger,

    Thank you. I am well aware of other databases capabilities, including, for example, PostgreSQL's python/perl/... language routines, which make for a much easier scripting capabilities.
    There are some advantages to QueryScript (including some I'm still working on and are not released yet) over existing implementations. But, I would say, if I were to come from the PstgreSQL world, I would probably not have the urge to embark upon this coding mission.

    I have no immediate intentions for any specific database other than MySQL. But as I created a language, I decided it should, by definition, be a general one.
    I do not presume that this language will catch with other databases, but am interested in leaving this open.

    Regards,
    Shlomi

  7. ik_5 wrote:

    @shlomi,

    There are two specific restrictions in Firebird:
    1. You can not create a command that require new transactions inside an existed transactions (there is a way to solve it though, using autonomous transactions)

    2. You can not change structure in execution block (stored procedures, anonymous procedure like the last link and triggers), however you can use the execute block instead.

    Here is the way you suggested it that works btw, and you are right, but still I've created a new table inside an execution block :)

    SET TERM !! ;

    EXECUTE BLOCK AS
    declare a_name varchar(10) = 'EMPLOYEE';
    BEGIN
    if (not exists(select 1 from rdb$relations where rdb$relation_name = :a_name)) then
    execute statement 'create table ' || :a_name ||' ( id integer ); ';
    END!!

    SET TERM ; !!
    commit;

    The double pipe is the concat action.
    In theory I can also create the table inside the system tables with insert, but that way, it's cleaner.

    Like all databases, it's not perfect, but I really like the power. I really hope that Mysql based will be better in that area, instead of using external tools such as yours.

    Your work is very good, but I really prefer such tools as part of the database itself, instead of external plugins.

  8. Jouni "rautamiekka" Järvinen wrote:

    The syntax looks much like PHP. At first I thought about JavaScript.

  9. shlomi wrote:

    @Jouni,
    It's a mixture, partly due to limitations by the SQL syntax itself (i.e. some things have to be distinctively interpreted by the script).

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org