MySQL Proxy: yet another mysql shell

I'm just back from a little vacation in the winter-wonderland and had some time on the train to spend. Thanks to power-plugs the 8hrs were well spent and ended up in a first, rough cut of a lua-customizable, mysql-shell that is based on the internals of the MySQL Proxy.

Branch

     $ bzr branch lp:~jan-kneschke/mysql-proxy/mysql-shell

if you are interested and want to follow the development.

Everyone already has a mysql-shell, the mysql program that comes with each MySQL installation.

I wanted to take its idea a bit further and make it customizable with our well-known Lua interface. Even if this ongoing work, it already does something useful:

$ mysql-client
(not connected)> .connect
root@127.0.0.1:3306 []> select 1;
{
 ["1"] = 1,
}
OK (warnings: 0, auto-commit: true)
root@127.0.0.1:3306 []> explain extended select 1;
{
 ["id"] = 1,
 ["select_type"] = "SIMPLE",
 ["table"] = NULL,
 ["type"] = NULL,
 ["possible_keys"] = NULL,
 ["key"] = NULL,
 ["key_len"] = NULL,
 ["ref"] = NULL,
 ["rows"] = NULL,
 ["filtered"] = NULL,
 ["Extra"] = "No tables used",
}
OK (warnings: 1, auto-commit: true)
root@127.0.0.1:3306 []> .close
(not connected)> .quit

I used the dot-notation for internal commands as the sqlite shell does, IIRC. We even have simple tab-complete for those dot-commands.

The visuals are driven by a lua-script like the below:

function read_query_result(res)
    local s = { }

    for row in res.rows do
            s[#s + 1] = "{"
            for i = 1, #res.fields do
                    if not row[i] then
                            s[#s + 1] = (" [%q] = NULL,"):format(res.fields[i].name)
                    elseif type(row[i]) == "string" then
                            s[#s + 1] = (" [%q] = %q,"):format(res.fields[i].name, row[i])
                    else
                            s[#s + 1] = (" [%q] = %d,"):format(res.fields[i].name, row[i])
                    end
            end
            s[#s + 1] = "}"
            print(table.concat(s, "\n"))
    end

    if res.insert_id then
            print(("OK (warnings: %d, auto-commit: %s, affected rows: %d, insert-id: %d)"):format(
                    res.warning_count,
                    tostring(res.flags.auto_commit)),
                    res.affected_rows, 
                    res.insert_id
                    )
    else
            print(("OK (warnings: %d, auto-commit: %s)"):format(
                    res.warning_count, 
                    tostring(res.flags.auto_commit)))
    end
end

It is handling what is displayed and how.

On the long run I add more and more scripting to it to drive it. Basic ideas are:

automatic display of warnings
automatic EXPLAIN of slow queries
benchmarking
micro-second timestamps

I even have plans to give it screen-like interface that allows to open several connections in parallel, so you can jump on the other connect and kill the other without opening another terminal.

Ah, I completely forgot:

(not connected)> .os.execute("ls | head -1")
AUTHORS

The shell can also execute lua code which might be handy.

Comments