|P is for Practical|
Using perl as an editor for sql queries (code)by deprecated (Priest)
|on Jan 16, 2002 at 23:07 UTC||Need Help??|
update: okay, so i'm an idiot, i didnt post the code. here it is: http://envy.posixnap.net/~alex/perlcode/pist.pl.txt
I use Postgres as my database of choice. Its "interactive terminal" is psql, which leaves something to be desired. When compiled with readline, it has an almost bash-like or tcsh-like ability to "up-arrow" and "down-arrow" to move among commands. And when \e is issued, you can even launch vi (although I imagine its possible to use emacs) and edit your query in vi-mode.
Allow me to wax eloquent about my editor for a moment. vim allows me to have syntax highlighting, vertical and horizontal window splitting, various abbreviated commands, text folding, macros, and all kinds of other goodies that vi just doesnt. Thusly, psql has none of these things.
I needed to change that.
I figured, gee, I've already written a mail client that used vim for a front end, why not use some of the code from that to wrap around psql instead? This way I can edit multiline plpgsql functions, have syntax coloring, and even save my queries in a directory so I can read them later!
It actually worked out rather well. I called it 'pist' for perl interactive sql terminal. Yes, dammit, vim is interactive (though you dont have to agree with me there). One simply launches pist, issues queries, and lets perl plumb them through DBI -- and lets DBI return the errors. DBI even has a function to return the output for you, semiformatted!
What I find most cool about this is I didnt have to write an editor. I didnt have to use any wonky module, vim already does that stuff. It just executes the sql for me. It's also amazingly short, less than 70 lines.
Because it uses a ~/.pistrc, you can use whatever shell or editor you want, and apply flags to both. You can also determine where your saved sql files go, and determine how you want to form your DBI line (I find it criminal that DBD::Pg and DBD::mysql's DBI->connect lines are so different! What does DBI mean after all, folks?!!) using your config file. This means risacher can continue to use his emacs, and I can continue to use my vim.
I'd really like to know if anyone finds this useful and if more features would be cool. I certainly use it enough.
my vimrc is available at http://scorch.posixnap.net/_vimrc for the curious