-
common_schema rev. 218: QueryScript, throttling, processes, documentation
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.
Query throttling
Throttling for MySQL queries was suggested by means of elaborate query manipulation. It is now reduced into a single throttle function: one can now just invoke throttle(3) on one's query, so as to make the query execute for a longer time, while taking short sleep breaks during operation, easing up the query's demand for resources.
Read more on query throttling.
Process views
The processlist_grantees view provides with more details on the running processes. slave_hosts is a new view, listing hostnames of connected slaves.
Read more on process views.
help()
The common_schema documentation is now composed of well over 100 pages, including synopsis, detailed internals discussion, notes and examples. I can't exaggerate in saying that the documentation took the vast majority of time for this code to release.
The documentation is now made available inline, from within you mysql client, via the help() routine. Want to know more about redundant (duplicate) keys and how to find them? Just type:
call help('redundant');
and see what comes out!
The entire documentation, which is available online as well as a downloadable bundle, is embedded into common_schema itself. It's rather cool.
Tests
common_schema is tested. The number of tests in common_schema is rapidly growing, and new tests are introduced for new features as well as for older ones. There is not yet full coverage for all views, but I'm working hard at it. common_schema is a robust piece of code!
Get it!
Download common_schema on the common_schema project page.
Read the documentation online, or download it as well (or call for help())
common_schema is released under the BSD license.
-
QueryScript: SQL scripting language
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.
-
Adding dynamic fields to Signups on Drupal
In my day job at SkySQL I work with Drupal as our content management system. One thing we often need to do is provide a way for people to sign up for events and the like. One such event is the upcoming SkySQL and MariaDB: Solutions Day for the MySQL® Database and unlike other events we needed to take into account the dietary requirements of those wishing to attend.
For events registration we use the Signup module and use a theme template function to provide a set of standard fields. The code looks something like this:
function ourtheme_signup_user_form($node) { $form = array(); // If this function is providing any extra fields at all, the following // line is required for form form to work -- DO NOT EDIT OR REMOVE. $form['signup_form_data']['#tree'] = TRUE; $form['signup_form_data']['FirstName'] = array( '#type' => 'textfield', '#title' => t('First Name'), '#size' => 40, '#maxlength' => 64, '#required' => TRUE, ); $form['signup_form_data']['LastName'] = array( '#type' => 'textfield', '#title' => t('Last Name'), '#size' => 40, '#maxlength' => 64, '#required' => TRUE, );
And so on, building up the elements and then returning the form. This is great because it allows us to have a standard set of fields for all signup pages, making life a lot simpler when creating content that requires registration. But the Solutions Day event required an extra field. I could have done this a number of ways, including putting logic in the template file to check for that particular node and only display the field then, or perhaps some other hack specific to this node. I, however, don't like specifics and tend to look for a generic solution, as the exception invariably becomes the rule.
For this exercise I wanted to be able to have a way of specifying for a particular node any extra fields that are available for this form. So I now have in the template.php file the following code:
// If there is a special field required for this, check and display if (!empty($node->field_signup_extra) && !empty($node->field_signup_extra[0]['value'])) { $extras = explode("\n", $node->field_signup_extra[0]['value']); foreach ($extras as $field_def) { $field_def = trim($field_def); if (empty($field_def)) { continue; } $elems = explode('|', $field_def); $field_name = array_unshift($elems); $form['signup_form_data'][$field_name] = array(); foreach ($elems as $field_element) { list($key, $val) = explode('=',$field_element); if ($key == 'options') { $val = explode(',', $val); } $form['signup_form_data'][$field_name]['#' . $key] = $val; } } }
Now all I need to do is create a field that is non-displayable but contains information to build extra fields. For example the content that describes the Dietary Requirements field is:
dietary_requirements|title=Dietary Requirements|size=40|type=textfield
The production version does a little more analysis of the input to ensure there are no possible attack vectors, but I've left that out for clarity sake.
Now, if I have an event (or other content type) that needs extra signup fields, I ensure that the content type has the new Signup Extras field and fill it on the new content with a simple field definition that Signup can use.Original post blogged on Saki Envirotech Blogs.
-
Drizzle Day 2012
Henrik has already posted it over on the Drizzle Blog, but I thought I’d give a shout out here too.
We’re holding a Drizzle Day right after the Percona Live MySQL Conference and Expo in April. So, since you’re all like me and don’t book your travel this far in advance, it’ll be easy to stay for the extra day and come and learn awesome things about Drizzle.
I’m also pretty glad that my employer, Percona is sponsoring the event.
-
Three free MySQL webinars
I’m scheduled to deliver several free MySQL webinars via Percona and ODTUG in the upcoming weeks. I hope you can join me:
February 16 – Verifying Replication Integrity with Percona Toolkit via ODTUG.
February 22 – EXPLAIN Demystified via Percona.
March 14 – Optimizing MySQL Configuration via Percona.
Further Reading:Free webinar Wednesday: verifying replication integrity
Free webinar on MySQL performance this Thursday
Free webinar on preventing MySQL downtime
MySQL: Free Software but not Open Source
Get a free sample chapter of High Performance MySQL Second Edition
|
News Feeds