myterm - extensible mysql command line client

1. myterm> SELECT engine, count(*) AS count FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC | chart pie | browser

and Firefox says:

What's that?

I just launched an open-source project on launchpad called myterm. Myterm is a crossover between the standard mysql command line client and the concept of pipes and filters in bash. You can use it to run queries and filter the produced result set in various ways using pipe chaining. This lends itself to quite a lot of different use cases, for instance graphical charts, md5 checksums and different presentation forms to name a few. It has browser integration using shell exec, which means it can render html result sets or charts in your browser. And since most stuff is written using plugins, it will work well to serve as a hub for hooking in more and more tools for data transfer, dumping, backup, simplify monitoring and so on. Sort of like inversed bash-scripting; you start inside the db going out.

The model is based on commands, filters, presenters and dests. The COMMAND (usually a query) produces a result set which is sent of to a chain of filters. Each FILTER can process the result set and reformat the data. A PRESENTER takes a result set and renders it in some form, e.g. plain text, tab separated values, html table or chart. Finally, a DEST is simply the destination of the rendered output, such as standard out, a file or the browser. A full command chain:

COMMAND --resultset--> FILTER --resultset--> PRESENTER --mime--> DEST

Some examples

First some standard output:

1. myterm> SHOW PROCESSLIST
2. +------+------+-----------------+------+---------+------+-------+------------------+
3. | Id | User | Host | db | Command | Time | State | Info |
4. +------+------+-----------------+------+---------+------+-------+------------------+
5. | 4789 | root | 127.0.0.1:59047 | test | Query | 0 | | SHOW PROCESSLIST |
6. +------+------+-----------------+------+---------+------+-------+------------------+
7. 1 row in set (0.000 sec)

Then, lets use the cols filter:

1. myterm> SHOW PROCESSLIST | cols 2-3
2. +------+-----------------+
3. | User | Host |
4. +------+-----------------+
5. | root | 127.0.0.1:59047 |
6. +------+-----------------+
7. 1 row in set (0.000 sec)

which filters out some columns. Similarly, a basic grep filter exists. Lets hope people won't stop using indexes just because of this:

1. myterm> SHOW DATABASES | grep info
2. +--------------------+
3. | Database |
4. +--------------------+
5. | information_schema |
6. +--------------------+
7. 1 row in set (0.001 sec)

For chart rendering, myterm uses libchart by Jean-Marc Tremeaux. Take a look at the biggest tables:

1. myterm> SELECT CONCAT(table_schema, '.', table_name) AS 'Table', data_length + index_length AS Bytes FROM information_schema.tables ORDER BY 2 DESC | other 7 | chart hbar

The other filter just reduces any rows after 6 into a total item named Other. Resulting bar chart:

The insertify plugin will reverse engineer a result set into a create statement and an insert statement. This is not as rock-solid or performant as CREATE...SELECT (it won't pick up indexes), but works for creating temporary snapshots or test data:

1. myterm> SHOW PROCESSLIST|insertify|tsv -N -E
2. CREATE TABLE 'some_table' (
3. 'Id' bigint(11) NOT NULL DEFAULT '',
4. 'User' varchar(16) NOT NULL DEFAULT '',
5. 'Host' varchar(64) NOT NULL DEFAULT '',
6. 'db' varchar(64) DEFAULT NULL,
7. 'Command' varchar(16) NOT NULL DEFAULT '',
8. 'Time' int(7) UNSIGNED NOT NULL DEFAULT '',
9. 'State' varchar(30) DEFAULT NULL,
10. 'Info' varchar(100) DEFAULT NULL
11. ) ENGINE=InnoDB;
12. INSERT INTO some_table (Id, User, Host, db, Command, Time, State, Info) VALUES ('4789', 'root', '127.0.0.1:59047', 'test', 'Query', '0', NULL, 'SHOW PROCESSLIST');
13. 2 rows in set (0.000 sec)

Here are the plugins so far:

1. myterm> plugins list
2. 15 plugins loaded:
4. Filters:
5. cols Filters columns and column ranges.
6. grep Filters lines containing the specified text in any column.
7. insertify Creates insert statements based on a result set.
8. other Reduces a result to max N rows, collapsing any extra rows to a row titled Other at the end.
10. Presenters:
11. chart Renders a chart using php Libchart.
12. html Formats the data to a html table.
13. md5 Calculates an md5 checksum of all rows and column values.
14. plain Formats the data to the default plain text table grid.
15. tsv Formats the data to tab-separated-values plain text.
16. vert Presents the data in a vertical plain-text fashion, similar to mysql \G output format.
17. vhtml Formats the data to a record by record, vertical html table.
19. Dests:
20. browser Sends the output to the browser.
21. file Sends output to a file with the given filename.
22. mailto Sends output to the registered email application using a mailto: link
23. std Sends output to standard out.

Current status

It's currently written in PHP, which is kind of bad, because of PHP:s limited console integration, signal handling (ctrl-c) and threading (think +asynchronous / multi-threaded queries). Maybe a rewrite in Python would fix these issues. Eric Day has initiated a similar project at Portland State University which is about to start... ...and they're thinking of Python! So I've contacted him about possibly combining these projects in some way later on, once both projects have gotten further.

Download

To download it, you need bazaar and php 5.2+ command line with mysqli extension. I've only tested it on Ubuntu Linux. Type:

1. bzr branch lp:myterm

then follow the README file.

Contribute

For now, myterm parsing and option handling is limited (don't do multi-line queries, comments or too much quoting), but most basic stuff works. There are probably tons of bugs in it and things that don't work, I know :) So... if you'd like to contribute, join the project and mailing list on launchpad. There, you can also look at the blueprints which are some ideas on additional features.

Feel free to leave comments and feature suggestions!