Skip to content

# QueryScript: SQL scripting language

MySQL Administration common_schema Development Open Source QueryScript scripts 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
9
Leave a Reply

9 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
ik_5

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

Author
shlomi

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

Guest
ik_5

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

Author
shlomi

@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… Read more »

Guest
Holger Thiel

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

Author
shlomi

@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.… Read more »

Guest
ik_5

@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… Read more »

Guest
Jouni "rautamiekka" Järvinen

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

Author
shlomi

@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).

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.