Skip to content

# Tag: Administration

## common_schema rev. 218: QueryScript, throttling, processes, documentation

MySQL Administration common_schema Development Open Source QueryScript scripts February 8, 2012

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

- QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
- Throttling for queries is now made available via the throttle() function.
- Enhancements to processlist-related views, including the new slave_hosts view.
- Inline documentation/help is available via the help() routine.
- more…

#### QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side scripts, interpreted by common_schema, which allow for easy syntax and greater power than was otherwise previously available on the MySQL server. For example:

>     foreach($table, $schema, $engine: table like '%')
>       if ($engine = 'ndbcluster')
>         ALTER ONLINE TABLE :$schema.:$table REORGANIZE PARTITION;

QueryScript includes flow control, conditional branching, variables & variable expansion, script throttling and more.

Read more on common_schema’s QueryScript implementation.

#### Continue reading » “common_schema rev. 218: QueryScript, throttling, processes, documentation”

## 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;
>     }

Continue reading » “QueryScript: SQL scripting language”