sql editor written in perl

jayakumar has asked for the wisdom of the Perl Monks concerning the following question:

bowing down to salute monks.

I would like to create a bare minimum command line editor for SQL. I have libarary Term::ReadKey; -- and nothing else. Anyone has already done this editor code that I can leverage? ( basically that allows user to move cursor, type and insert and delete ). Any general advice on how to write/design this ?

Background: Currently my team uses "isql -S .. -U.." in Linux cmd prompt and the output is often too wide, wrapped in many lines - resulting in misaligned output. I would be very happy if I could write sql editor tool replacing this "isql" .

I have written a DBI:: module, that extends DBI and Statement handler classes. This makes my team's DB coding tasks (remote load, saving in file/FPT, mailing query output as report) much faster. I could create a nice "HTML table like tabulated display" of sql output that team is happy. I want to add the editor mentioned above to make this complete.

thanks, Jay

Comment on sql editor written in perl

Replies are listed 'Best First'.
Re: sql editor written in perl by marto (Cardinal) on Jun 17, 2022 at 07:04 UTC
You may want to check out dbish.	[reply]
Re: sql editor written in perl by LanX (Saint) on Jun 16, 2022 at 22:20 UTC
> I have libarary Term::ReadKey;-- and nothing else Why? If I were you I'd use Term::ReadLine instead of reinventing all with Term::ReadKey ... > Any general advice on how to write/design this ? ... and "borrow" the editing concepts from the `perldebugger` ° update °) or have a look at those 112 other dists depending on Term::ReadLine. FWIW there is also a handful mentioning SQL. :) Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]
Re: sql editor written in perl by Corion (Patriarch) on Jun 17, 2022 at 11:57 UTC
While it's no editor, the DBIx::RunSQL module includes run-sql.pl, which allows for convenient execution of SQL, either from the command line or from a file. If you want something more interactive, I've found editing a file and then (automatically) re-running the query to display the results in a browser quite useful. I've written App::sqldisplay, which watches an (SQL) file and reruns the query whenever the file is updated. The results are then displayed in a web browser. You might find this helpful.	[reply]
Re: sql editor written in perl by Discipulus (Canon) on Jun 17, 2022 at 07:23 UTC
Hello jayakumar, ..and welcome to the monastery and to the wonderful world of Perl! (<-- added later..) As LanX said I'd use Term::ReadLine and probably Term::ReadKey merely to hide password submission ( using `ReadMode 2;` then reverting to 0). The only thing I can suggest is to maximize the autocomplete feature of Term::ReadLine to speed up things. You can autocomplete all SQL syntax and or all table names or even column names if needed. I suspect that autocmplete feature can also help you in quoting: typing `INSERT INTO USE` then hitting `TAB` and magically get back `"USERS"` with double quotes around it (untested!). See Re^3: about Term::ReadLine and it's imported function The Solution with two demos on how to use `$term->Attribs->{completion_function}` correctly. Share your progresses! L* There are no rules, there are no thumbs.. Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.	[reply] [d/l] [select]
Re: sql editor written in perl by erix (Prior) on Jun 17, 2022 at 07:39 UTC
What isql? What DBMS? What OS? https://en.wikipedia.org/wiki/Isql	[reply]
Re: sql editor written in perl by perlfan (Parson) on Jun 21, 2022 at 08:51 UTC
I can echo the utility of dbish. But I can also describe (but not share) a utility that my `$WORK` uses internally. Our applications use YAML files to describe the different DB (mysql) servers to connect to on the different tiers - production, testing, development. We have a utility that is effectively a wrapper around `mysql` and `mysqldump`. It gets the authentication information from the same YAML file that the application(s) use on whatever box we're on. In practice, we can do things at the commandline, like: `# send some SQL: $ ourutil dbname < some.sql $ cat some.sql \| ourutil dbname $ echo "SELECT * ..." \| ourutil dbname # dump out stuff $ ourutil -D dbname [table] -- [extra mysqldump options]` [download] Similarly, we can easily get to the shell via, `$ ourutil dbname` [download] And if we just want to print the command string with the auth information in + options it would have executed, we can do: `$ ourutil --cmd # for mysql, or $ ourutil -D --cmd # for mysqldump` [download] I wish I could share the code, but the above interface should be readily implemented and customized to fit your existing environment. Update: just wanted to add, this utility I describe rarely changes and is extremely useful on a daily basis.	[reply] [d/l] [select]

update