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.
@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
@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.
The syntax looks much like PHP. At first I thought about JavaScript.
@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).