Subscribe to this rss feed RSS

myterm - extensible mysql command line client

Posted November 5th by Bjorn Melinder
4 Comments
What if I type this:

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

and Firefox says:Myterm pie

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:Myterm hbar

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

4 comments

Add your own

Justin Swanhart | November 5th at 13:45
Why not just use the pager feature of the MySQL client?

mysql> \P grep info
PAGER set to 'grep info'
mysql> show databases;
| information_schema |
9 rows in set (0.01 sec)

mysql>

You can chain together any set of pipes that the shell can set up, using all the great *nix tools available.
Bjorn | November 5th at 14:23
Justin, good point :) I'd say grep is maybe not the perfect killer-feature for myterm, but rather an example of the things you can do once you tap into the result set stream. What I believe pager can't do is give you the data back to further work with it. I'm making this example up since the piped query feature at the end does not work yet :)

SHOW PROCESSLIST | grep bad_user | cols 1 | KILL $1

Working in bash easily gives you unreadable one-liners once you start wanting to do some real stuff with it. Wrapping this in scripts/plugins makes them easier to reuse with less typing. Especially when things like connection handling has been taken care of.
anonymous | November 7th at 22:53
Can it do history buffer? Eg. in mysql client, when you press the arrow key, you can see the history of what you typed.
Bjorn | November 8th at 02:36
Yes, there's history buffer, using the readline library.

It also has tab auto completion and you can edit the auto completion word list using the abbrev command. For instance, type

sel<tab>

and it expands into

select * from


Post a comment


Name (required)

Email (will not be published) (required)

Website



Notify me of followup comments via e-mail



Subscribe without commenting


Your email: