tksql

Tksql is an editor for postgreSQL databases. Can be used from the command line:

$ tksql -db sandro 
$ tksql -db sandro -tbl tel 
$ tksql -host localhost -db erm -sql "select * from area" 

A click on the table name will pop up a menu proposing a menu to choose if editing the table in 'Table' View or in 'Mask' view. Both of them can coexist in the meanwhile.

You can also inhibit the editing of some columns adding option -noup "attr_list"

Filtering capabilities

You can pop the filter widget by just clicking on the attribute title (head of the column in table view or label of the field in mask view). All the field where you write something will be used s a filter, the same attribute can be "doubled" as in the exampled where we wanted some events in the month of march.

You can also change order of fields by dragging names (the red one in the figure, the mouse disappeared from the screenshot ;-)

Search capabilities

A "search" in tksql is limited to what has already been filtered, meaning it does not issue a new query: is a way to hilight all the cells that contain a string (a regexp, really). It can be limited to a single column or widened to all comlumns.

Totals and subtotals of columns

It is possible to ask tksql to add all values of a column so that any new query (mainly filter) we see the result at the bottom of the column. Just click on the name of the column (i.e. the head).

A click on a column will show subtotals at any change of the value of said column. Es. If you have a table w/ invoices, you can ask to write totals of a column (Amount) and to write subtotal at any change of a client.

Subtotals are triggered at any change in value, so can be any type, addition are computed only on numeric type (you bet!...)

In the following image you can see:

• the menu popping on click on title of the column
• the data after a query when totals and subtotals have been computed (totals are in european stile, Íll insert the choice within few days)
• some yellow cells (hilighted by the search tool, line 'find' of the filter panel )
• the filter panel in which I put two "where" conditions, on attribute "incassato" and "billing_code"
• columns have different colors according to types of data.

Note that the query is editable (but totals will only refresh on request).

You can also ask to use special function while grouping for subtotals. For date type week, month, quarter, semster and year are supplied, more can be defined directly by the user/programmer on a per type or per attribute base.

You can have subtotals displayed in a separate table: this can dramatically increase speed when there are many rows/subtotals so that any new line needs shifting a lot of rows.

Queries

• sql query
• filter attributes
• mode (form or table)
• max n. of rows
• columns on which you have totals/sub totals
• string you highlited
• owner of the query
• people allowed to see this query
• read only flags
• constraints (sql conditions that will be issued beyound your normal filter, this is usefull to limit the seen records to other people)
• Number notation used
• Layout: thanks to a very simple mechanism you can create layouts for form mode just writing name of fields

To create a query is no more difficult than deciding a name, the rest is automatically done by tksql. You still retain the possibility to edit what tksql would like to save.

More information can be found in the manual page

Queries and dates

To turn queries more usefull, you can use symbolic references in dates. Suppose you want to create a report of expenses of previous week, and you want the query to return a table with only those lines. You can input in the filter widget a string in 'date' field: @m-. These 3 charachters will be expanded today (12.10.02) by tksql into:

several symbols can be used to speed up the entry, these will be interpreted when reloading the database. A complete list follows,

d, w, m, y, D, W, M, Y:

will be replaced respectively by current day, first day of the week (w) or last day of the week (W), first (m) or last (M) day of the month, first (y) or last (Y) day of the year.

@w, @m, @y:

will be replace by two conditions so as to delimit current week (@w), current month (@m), current year (@y)

+n, -n:

any of the preceding strings can be used in conjunction w/ a numeric modifier that will shift the dates of that many periods so that you can match the last month writing @m-, next week @w+ translating @m- with: attr >= '1/9/02' and attr <= '30/9/02' (European date convention in the example).

%d, %W, %m, %y:

will be replaced by current day, week (number in the year), month (0-12), year (2 digits).

Examples

symbol	expanded to
@m-	date >= '1/9/02' and date <= '30/9/02'
@y+	date >= '1/1/03'and date <= '31/12/03'
@y+2	date >= '1/1/04'and date <= '31/12/04'
@w-2	date >= '30/09/02' and date <= '07/10/02'
M+	date = '1/11/01'
10/%m/%y	date = '10/10/02'

Nicks

TkSQL allows you to create nicknames to designate a particular setup, so that you can simply recall that setup by his nick. As you can see in the picture a nick is constitued by a name, a host, a type (pg or mysql), a db name, a user. It can also contain a decription, a username, a password, a table, the name of a query, a sql statement or a port.

When you have defined such a nick you can enter tksql from the command line just using the name of the nick as argument to tksql. You can edit nicks from 'Known connections' menu entry. These data are stored in ~/.tksqldb