{"id":4596,"date":"2012-02-08T11:43:19","date_gmt":"2012-02-08T09:43:19","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4596"},"modified":"2012-02-08T11:43:19","modified_gmt":"2012-02-08T09:43:19","slug":"queryscript-sql-scripting-language","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/queryscript-sql-scripting-language","title":{"rendered":"QueryScript: SQL scripting language"},"content":{"rendered":"

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

QueryScript is available fro MySQL via common_schema<\/a>, which adds MySQL-specific usage.<\/p>\n

What does QueryScript look like?<\/em> Here are a few code samples:<\/p>\n

Turn a bulk DELETE operation into smaller tasks. Throttle in between.<\/p>\n

\n
while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)\r\n{\r\n\u00a0 throttle 2;\r\n}<\/pre>\n<\/blockquote>\n
Convert all InnoDB tables in the ‘sakila’ database to compressed format:<\/p>\n
\n
foreach ($table, $schema, $engine: table in sakila)\r\n{\r\n  if ($engine = 'InnoDB')\r\n    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;\r\n}<\/pre>\n<\/blockquote>\n
Shard your data across multiple schemata:<\/p>\n
\n
foreach($shard: {USA, GBR, JAP, FRA})\r\n{\r\n  CREATE DATABASE db_:$shard;\r\n  CREATE TABLE db_:$shard.city LIKE world.City;\r\n  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;\r\n}<\/pre>\n<\/blockquote>\n
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.<\/p>\n
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:<\/p>\n
\n
mysql> set<\/strong> @script := \"while (TIME(SYSDATE()) < '17:00:00') SELECT * FROM world.City WHERE id = 1 + FLOOR((RAND()*4079));\";\r\nmysql> call<\/strong> run(@script);<\/pre>\n<\/blockquote>\n
For more details, consult the QueryScript<\/a><\/strong> site.<\/p>\n
If you’re a MySQL user\/DBA, better read the common_schema QueryScript documentation<\/strong><\/a>, to better understand the specific common_schema<\/em> implementation and enhanced features.<\/p>\n
common_schema<\/em>, including the QueryScript interpreter, can be downloaded from the common_schema project page<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[79,67,120,57,76,50],"class_list":["post-4596","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-administration","tag-common_schema","tag-development","tag-open-source","tag-queryscript","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1c8","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4596"}],"version-history":[{"count":27,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4596\/revisions"}],"predecessor-version":[{"id":4702,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4596\/revisions\/4702"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}