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