/ 
SQL-Steno-0.1.2
River stage zero No dependents
/ SQL::Steno

NAME

SQL::Steno - Short hand for SQL and compact output

SYNOPSIS

Type some short-hand, see the corresponding SQL and its output:

steno> TABLE1;somecolumn > 2    -- ; after tables means where
select * from TABLE1 where somecolumn > 2;
prepare: 0.000s   execute: 0.073s   rows: 14
  id|column1                                    |column2
    |                                           |    |column3
    |                                           |    | |somecolumn
----|-------------------------------------------|----|-|-|
  27|foo                                        |    |a|7|
  49|bar                                        |abcd|a|3|
  81|baz\nbazinga\nbazurka                      |jk  |b|9|
1984|bla bla bla bla bla bla bla bla bla bla bla|xyz |c|5|
...
steno> /abc|foo/#TBL1;.socol > 2    -- /regexp/ grep, #tableabbrev, .columnabbrev
select * from TABLE1 where somecolumn > 2;
prepare: 0.000s   execute: 0.039s   rows: 14
id|column1
  |   |column2
  |   |    |[column3=a]
  |   |    |somecolumn
--|---|----|-|
27|foo|    |7|
49|bar|abcd|3|
steno> .c1,.c2,.some;#TE1#:ob2d3    -- ; before tables means from, 2nd # alias, :macro
select column1,column2,somecolumn from TABLE1 TE1 order by 2 desc, 3;
...
steno> n(), yr(), cw(,1,2,3)    -- functionabbrev before (, can have initial default arg
select count(*), year(now()), concat_ws(',',1,2,3);
...
steno> .col1,.clm2,.sn;#TBL1:jTBL2 u(id);mydate :b :m+3d and :d-w    -- :jTABLEABBREV and :+/- family
select column1,column2,somecolumn from TABLE1 join TABLE2 using(id) where mydate between date_format(now(),"%Y-%m-01")+interval 3 day and curdate()-interval 1 week;
...

DESCRIPTION

You're the command-line type, but are tired of typing select * from TABLE where CONDITION, always forgetting the final ;? Output always seems far too wide and at least mysql cli messes up the format when it includes newlines?

This module consists of the function convert which implements a configurable ultra-compact language that maps to SQL. Then there is run which performs normal SQL queries but has various tricks for narrowing the output. It can also grep on whole rows, rather than having to list all fields that you expect to match. They get combined by the function shell which converts and runs in an endless loop.

This is work in progress, only recently isolated from a monolithic script. Language elements and API may change as the need arises, e.g. :macro used to be @macro, till the day I wanted to use an SQL-variable and noticed the collision. In this early stage, you are more than welcome to propose ammendments, especially if they make the language more powerful and/or more consistent. Defaults are for MariaDB/MySQL, though the mechanism also works with other DBs.

convert

This function takes a short-hand query in $_ and transforms it to SQL. See "shell" for more run time oriented features.

First it looks for :macro. These are mostly simple text-replacements stored in %Macros. There are also some dynamic macros. Those starting with :j (join) or :lj (left join) may continue into a table spec without the leading #. E.g. :ljtbl#t might expand to left join table t.

Those starting with :gb (group by) or :ob (order by) may be followed by result columns numbers from 1-9, each optionally followed by a or d for asc or desc.

Then there are the time macros, where an optional leading letter indicates a base time, and an optional trailing letter with an optional count means the offset. The letters are:

y

(this) year. E.g. :y+2m is march this year.

q

(this) quarter. E.g. :q+0 is this quarter, :q+q is next quarter.

m

(this) month. E.g. :-3m is whatever precedes, minus 3 months.

w

(this) week (starting on $weekstart). E.g. :w+3d is this week thursday (or wednesday).

d

(this) day. E.g. :d-w is midnight one week ago.

h

(this) hour. E.g. :h+30M is half past current hour.

M

(this) minute. E.g. :+10M is whatever precedes, plus 10min.

s

(this) second. E.g. :s-2h is exactly 2h ago.

Then it looks for #tbl, #tbl# or #tbl#alias. Here tbl is a key of %Tables or any abbreviation of known tables in @Tables. If followed by #, the abbreviation is used as an alias, unless an alias directly follows, in which case that is used.

Then it looks for .col, .col. .col.alias. Here col is a key of %Columns or any abbreviation of columns of any table recognized in the query. If followed by ., the abbreviation is used as an alias, unless an alias directly follows, in which case that is used. It tries to be clever about whether the 1st . needs to be preserved, i.e. following a table name.

Finally it looks for func(. Here func is a key of %Functions or any abbreviation of known functions in @Functions, which includes words typically followed by an opening parenthesis, such as u( for using(. i( is in(, so that in( is free for matching ifnull(, but more importantly, because it has a smart brother: I( is also in(, where quoting is applied as necessary, and unless you give an initial comma I(,, the elements are separated on space. E.g. I(a 3 a's q"q) and I(,a,3,a's,q"q) both give in("a",3,"a's",'q"q'). If the 1st argument of a function is empty and the abbrev or function is found in %DefaultArguments the value becomes the 1st argument.

Finally it picks on the structure of the statement: These keywords can be abbreviated: se(lect), ins(ert), upd(ate) or del(ete). If none of these or set is present, select is assumed as default (more keywords need to be recognized in the future).

For select, semicolons are alternately replaced by from (the 1st being optional if it starts with a table name) and where. If no result columns are given, they default to *, see "SYNOPSIS". For update, semicolons are frst replaced by set and then where.

shell

This function reads, converts and (if $dbh is set) runs in an end-less loop (i.e. till end of file or ^D). Reading is a single line affair, unless you request otherwise. This can happen either, as in Unix Shell, by using continuation lines as long as you put a backslash at the end of your lines. Or there is a special case, if the 1st line starts with \\, then everything up to \\ at the end of one of the next lines, constitutes one entry.

In addition to converting it offers a few extra features, performed in this order (i.e. &xyz can return /regexp/=literal sql etc.):

&query arg, ...
&query( arg, ... ) following text
&{Perl code} following text

These allow canned entries and are more complex than macros, in that they take arguments and replacement can depend on the argument.

Run Perl code. It sees the following text in $_ and may modify it. If it returns undef this statement is skipped. If it returns a DBI statement handle run that instead of this statement. Else replace with what it returns.

Reprocess result as a shell entry (i.e. it may return another &query).

{Perl code}statement

Call Perl code for every output row returned by the statement with the array of values as first and the array of column names as second argument. It may modify individual values, but not the array. If it returns false, the row is skipped.

/regexp/statement

This will treat the statement normally, but will join each output row with '|' characters (which need to be matched as \/) and check that against the regexp. Only matching rows are considered. You may combine {Perl code}/regexp/.

=literal sql

A preceding = prevents conversion, useful for hitherto untreated keywords or where the conversion doesn't play well with your intention.

?

Help prefix. Alone it will give an overview. You can follow up with any of the special syntaxes, with or without an abbreviation. E.g. ?( will show all function abbreviations, whereas ?abbrev( will show only those functions matching abbrev or ?#abbrev only those tables matching abbrev.

??statemment

Will convert and show, but not perform statement. If $dbh is not set, this is the default behaviour.

!System Shell code

Run it.

>filename
>>filename

Redirect or append next statement's output to filename. If it has a suffix .csv, an according format is used.

|System Shell code

Pipe next statement's output through System Shell code.

YOUR SCRIPT

package SQL::Steno;		# doesn't export yet, so get the functions easily
use SQL::Steno;
use DBI;
our $sql = DBI->connect( ... ); # preferably mysql, but other DBs should work (with limitations).
# If you want #tbl and .col to work, (only) one of:
init_from_query;		# fast, defaults to mysql information_schema, for which you need read permission
init;			# slow, using DBI dbh methods.
# Set any of the variables mentioned above to get you favourite abbreviations.
shell;

LICENSE

This program is free software; you may redistribute it and/or modify it under the same terms as Perl itself.

SEE ALSO

DBI, SQL::Interp, SQL::Preproc, SQL::Yapp, Jade

AUTHOR

(C) 2015 by Daniel Pfeiffer <occitan@esperanto.org>.