common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.
common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.
There’s no Perl nor Python, and no dependencies to install. It’s just a schema.
Some highlights for the new revision:
- foreach(), aka $(): loop through a collection, execute callback commands per element.
- repeat_exec(): a repeat-until device: execute queries until some condition holds.
- exec_file(): execute files a-la SOURCE, but on server side
- Query analysis: analyze query text, view or routine definitions to detect dependency objects.
- Improvements to views and routines, new routines introduced.
Let’s take a closer look:
rpbouman
I’m very happy to have Roland Bouman working on this project. He introduced some sophisticated code without which some functionality could not take place. I’m sure I don’t need to introduce his great capabilities; I’ll just pass the note that it is very good working with him!
foreach()
Introducing a looping device which can iterate a collection and execute callback commands.
What’s a collection? A range of numbers; a set of constants; the result set of a SELECT query; tables in your database and more.
What is a callback? A query or set of queries to invoke on the specific elements in the collection. For example:
call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPRESSED');
I’ll publish dedicated posts on foreach(), aka $(), following this post. Official documentation is here.
repeat_exec()
Repeat executing queries in a given interval, until some condition holds.
What kind of condition? You can loop forever, or until a given time has passed, a given number of iteration has passed.
You can iterate until no rows are affected by your commands (your callbacks), or until some dynamic condition holds (a query evaluates to true).
For example: purge rows from a table until no more rows are affected; in interval of 3 second:
call repeat_exec(3, 'DELETE FROM test.event WHERE ts < CURDATE() ORDER BY id LIMIT 1000', 0);
Official documentation is here.
exec_file()
If you need to execute commands from a file, you usually invoke SOURCE:
mysql> SOURCE '/tmp/somefile.sql';
Or you invoke mysql client and redirect its input to read from file:
bash$ mysql some_db < /tmp/somefile.sql
SOURCE is a MySQL client command. The file must reside on your client. Running the mysql client is great, but you need to work it out from outside the server.
call_exec() will let you import a file on server side, from within the server:
call exec_file('/tmp/some_file.sql');
You will need to have the file readable; it is limited to 64K at this moment; it may not use DELIMITER, and it may not include dynamic SQL. These are the limitations.
Official documentation is here.
exec() / exec_single()
All of the above rely on the exec() / exec_single() routines, which dynamically execute a set of queries. One one hand, it’s no big deal: they only have to use prepared statements in order to invoke the queries. But then, they knows how to parse multiple queries (find the “;” delimiter correctly), plus they allow for configuration: if you set @common_schema_dryrun, queries are not actually executes; just printed out. If you set @common_schema_verbose, queries are verbosed in addition to being executed. Since all execution routines rely on these,we get a standardized pattern.
Official documentation is here.
Query analysis
Query parsing routines allow for detection of dependencies within queries. While not full-blown SQL parser, these allow one to realize on which tables or routines a view depends on; or a routines depends on; or an event; or just any query.
These routines can analyze the text of not only a SELECT query, but also UPDATE, DELETE, CREATE, etc. They can read the code of a stored routines, including queries and control flow constructs; thus, they are also able to analyze events and triggers.
At this stage forward-dependencies resolution is supported. This can eventually lead to dependency graphs or to reverse-dependency resolution (i.e. “which view, routine, trigger or event depend on table t?”)
Example:
mysql> call get_view_dependencies('sakila', 'actor_info');
+-------------+---------------+-------------+--------+
| schema_name | object_name | object_type | action |
+-------------+---------------+-------------+--------+
| sakila | actor | table | select |
| sakila | category | table | select |
| sakila | film | table | select |
| sakila | film_actor | table | select |
| sakila | film_category | table | select |
+-------------+---------------+-------------+--------+
The query analysis routines are in BETA stage.
Official documentation is here.
Test quite
common_schema is now tested. Not all code is as yet under tests; all new code is, and some of the older code. Work is in progress to add more and more tests.
Further changes:
- candidate_keys does not give higher score for PRIMARY KEYs any longer. It ranks all unique keys according to its own heuristic; it also provides with the is_primary and is_nullable columns.
- Added candidate_keys_recommended view, recommending best candidate key per table (while noting whether it qualifies as PRIMARY KEY in terms of NULLable columns).
- Added many text parsing and text manipulation routines, such as better trim, tokenizing, etc. Improved existing code significantly.
Get it
common_schema is available for downloaded. It is released under the BSD license, and is free.
I’ve put very hard work into common_schema’s documentation. It is very thorough and provides with clear examples. The documentation is also available for download.
If you encounter problems, please report on the issues page.
common_schema is meant to be downloaded & installed on any MySQL server. It provides with general and essential functionality. Spread the word!