| tksql - A GUI to edit postgreSQL tables |
|
tksql nickname tksql [-db dbname] [-host host] [-tbl table] [-sql sql_statement] [-pop] [-ro] [-mask] [-total attributes] [-u user] [-limit n] [-query query_name] [-noinit] [-nick nickname] |
| Tksql is a GUI (grafical user interface) to edit postgreSQL tables. The main feature is the easy interface that allows you to filter the tables in a very quick way. You can edit data in View mode or in Mask (forms). You can even have them together. Tksql can be called in several way that allow you to edit just one table or you can browse the whole database. Tksql can also edit one table of a join statement, (eg.: edit a table with code while having description taken from another table via a join). |
| Search ability are provided and they differ from filter ability in the fact that they search in the data already retrieved from the database and highlight patterns |
| -host |
| hostname tksql will connect to that host, defaults to localhost |
| -db |
| dbname tksql will connect directly to that database |
| -u |
| user This opt will force to try to connect as user |
| -tbl |
| table Tksql will edit directly that table |
| -sql |
| sql_statement Tksql will issue a sql statement and will allow to edit the result. If the sql statement is a join of more tables only the first one will be edited, the others will be in a read-only mode |
| -noup |
| attribute_list A list of attribute whose updating will be inhibited |
| -ro |
| Forces read only mode. Read only mode will also be switched if the sql statement does not have a primary key or oid. |
| -mask |
| If -tbl or -sql options have been used, tksql will default in Table mode This flag will force Mask mode |
| -extra |
| extra condition to be addes at any sql query, so as to limit the result of any query. Usefull if you want to conceal part of the database or just avoid adding too many coditions any time you send a query. |
| -total |
| attribute_list this option asks tksql to put a value for the sum of the values of a column at the bottom of the column. Any time a query is issued, the total is recalculated for each of the columns attr1, attr2 |
| -limit |
| n this option adds "limit n" to the sql query, limiting the answer to n tuples. |
| -nnot |
| notation This option forces number notation to be notation namely one of EU, US, none or NO. EU (European) means: "," as decimal separator; "." as thousand separators. US the opposite while none does not use thousand separators and uses "." as decimal separator. NO doesn't even show a possibility to transform numbers format. I don't really know which would have been a better naming, probably US should have written "scientific?". |
| -pop |
| attrs when in mask/form mode, and tksql is used to edit a single table, we can use this option to tell which attributes are to be shown in a separate window to browse the result of the query/the whole table. attrs can be empty. In this case tksql will try to figure out reasonable ones (!). NOTE: if you don't use this option no sql result will be shown while opening tksql in mask mode even if you used option -sql. |
| -type |
| pgsql|mysql sets the type of connection to be tempted. Defaults to pgsql, if it fails to connect a graphical tools appear where you can select a different connection type. -mysql alias to -type mysql |
| -p |
| password sets the password for the connection |
| -query |
| query_name You can name a query so that tksql will issue that query directly. (If a query is named "default", it will be issued whithout need to name it directly) See below. NOTE: this options requires you use also use option -tbl. (In fact a query is defined uniquely by query_name/table pair) |
| -noinit |
| Don't read initialization file (~/.tksqlrc under *nix or tksql under Windows) |
| -nick |
| A nickname referring to locally stored connection data (see below). |
| Nicknames are a handy way to store information on connections. Nickname are symbolic names of data you can enter through a simple interface and are stored locally in a file that is called ~.tksqldb under *nix systems or .tksqldb under windows systems. The data collected under a nickname can range from single host to host/type/db/port/table/query/passwd. If any nicknames are defined tksql will try to match any argument passed on the command line with stored data to pop a choice among them. |
| Tksql allows to create queries in a very straitforward way. Really creating a query is no more that savein a situationa and giving it a nickname and possibly a description. Queries are saved in a table whose name is tksql_query. The structure of such table is: |
| query |
| The name of the query. If the name is default that query will be issued as a default access to the table even if you didn't explicitly name any query. A nick can not contain spaces. As of this writing a query w/ name default will be used only if it's mode is the same you want. This leads to the fact that you can only have 1 default. I accept suggestion for preferred behaviour. |
| tbl |
| that's the name of the first table in the 'FROM' part of thte select statement. Nick and table toghether are a PRIMARY KEY for this table. |
| owner |
| the person that recorded the query |
| read |
| list of people allowed to 'read' the query, those who can use and that will see it popping up as a possible query. all is a way of indicating everybody can see it. |
| description |
| a description of the query. If any this will be written instead of the query name in the list of available queries. |
| sql |
| a sql statement used to create the result. This may be a simple select or a join on several tables. |
| ro |
| a boolean indicating if the query shoud be read-only |
| form |
| a boolean indicating if the query should be a table of a form/mask |
| filter attributes |
| Attributes that are meant to be used as filter in the query and default values. These files will show up in a separate window and will be used to build up a sql statement at each 'reload'. See below for contraints |
| constraint |
| a WHERE CONDITION that should be issued at every new 'reload' even if not explicitly indicated by the user. This is essential to create queries that offer to the user a limited portion of the table. This can be used to prevent access to private data. (beware that at the moment there is no way to prevent user from editing and possibly deleting this field). |
| nnot |
| Numer Notation: US, EU, none, NO are allowed values |
| limit |
| maximum number of tuples we want in the result (LIMIT part of the SQL statement). |
| pop |
| boolean indicating whether we want the filter widget. |
| totals |
| In case we are viewing our query in table format, we can see titals and possibly subtotals. |
| find |
| string that should be hightlighted. This is usefull when you have many data represented in the table and you want to easily see them. This does not issue a select to the database. |
| width |
| requested width for each column, when in table mode. At the moment the width of the table as a whole is not regitered. |
| layout |
| in a very simple way you can define a layout for your table. See below LAYOUT section |
| Filter is the real reason why I developed tksql and are in my opinion one of the best plus of this software. The way to browse the database is normally to set filter conditions and see the result. TkSql provides an efficent way to do that through a filter widget that can be popped cliccking ob the names of the fields, if you are using a form, or choosing the first item of the menu that you get cliccking on the titles of the columns when in table mode. |
| For each new attribute you click on, you'll see one more line in the filter widget. Such a line has: |
| Attribute name |
| If you click on this you will get a second line on the same attribute. Usefull when using more constrains on the same attribute. |
| Operator |
| You can select an operator to be used while composing the sql statement (query). Different lists of operators will appear according to the type of the attribute. |
| Input string |
| String to be matched according to the required operator. An empty string will be discarded. |
| INPUT STRING FOR 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: @w-. This 3 charachters will be expanded today (12.10.02) by tksql into: |
| attr >= '1/9/02' and attr <= '30/9/02' |
| several symbols can be used to speed up the entry, a complete list follows, these will be interpreted at the moment of reloading the database. |
| 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. |
| %d, %W, %m, %y |
| will be replaced by current day, ween (number in the year), month (0-12), year (2 digits). |
| @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). |
|
More examples: >= 15/%m-1/%y after 15 of last month <= 20/%m-1/%y-2 before 20th of month before this, two years ago |
| Tksql will compose a layout when in form/mask mode based on some simple algorithms. Entries will be forced to be 11 chars unless this is already more than allowed by the database, in which case the width of the database is used. text attributes will have 2 times the space of the other fields. Boolean will be presented with checkbuttons. A default display is 4 attribute each raw. |
| It is pretty easy to change these defaults and it must be done using queries. You create a query (just use tksql and then 'Save the Query'), if in form/mask mode tksql will present you a layout, mainly the names of the attributes written in a text widget. At this point you can rearrange them as you like, but you can also add some simple format modifiers: |
| #- ##- ###- comment |
| This is the way to get a line (border) and a comment on the line. Usefull to group attriubutes. 3 '#' will end up in a bigger label (default navy blu). 2 '#' will end up in a gray label w/ raised relief, 1 single '#' will just place a label w/o reised backgroud. The appearance of the labels can be set in setup.tcl ot ~/.tksqlrc. The '-' reminds you it is placed on the line... |
| # comment |
| as above but the comment uses a big label rather than a thin one and is not placed on the border. |
| TT= |
| a text widget will be used. A text widget is an area that spans many rows. you can force dimensions appending :r.c: TT=var:5.20 will require a text widget of 5 rows each 20 chars long. |
| c= |
| will force a checkbutton. Eg.: c=form |
| :n.m |
| will force dimensions. The label descruibing the filed will be n char width and tha entry for the input will be m chars width. :.m or :n. are admitted. |
| { attrs } {| attrs } {= attrs } {|= ...} |
| curly braces will group attributes in a single 'space'. A | attached to the opening curly brace forces a frame (border) to be drawn. A = forces names of entry to be placed on tob of the entry rather than on the left side. Curly braces can be nested |
| -- tcl commands |
| You can also use tcl commands (for exemples to set variables). You should put them at the beginning, before a '--' in a separate line. eg.: |
set a "name phone" set b "address -" -- $a $bVariable define in the first part will be substituted. |
| Tksql is written in tcl/tk, uses tktable widget to display data in a matrix way. Tksql is really a very simple script that uses other three packages that do the dirty work: sdsql, sdtcl and layout. Programmers are encouraged to have a look at the many possibilities of the sdsql package better explained in the demo Sql.tcl |
| Since version 0.8.0 tksql added some functions to handle table. It is now possible to create tables, alter the name of tables and attributes, edit tksql_attinfo (see below) |
| tksql_attinfo is a table where some information are stored that allow tksql to behave more friendly to the user in the following way: |
| tips |
| in the description field you can input a text that will be popped in a yellow hint when the mouse will pass over the field in the mask (form) view. |
| admitted values |
| you can input admitted values in the form of regular expression. NOTE: a regular expression must match also beginning and end of the word!! eg.: ^(yes|no)$ will force the field to be just 'yes' or 'no' yes|no will just force the field to contain 'yes' or 'no' |
| foreign table / foreign key / foreign descr |
| If the field mus contain values that must be present in another table, you can write here wich table/field it must be and you can also write the name of other fields that will be used to explain what the fiels is. Eg.: if table 'film' has titles of film and a field is the cathegory that is defined in table 'cathegory' with field 'code' and 'cat_name', you can add a line in tksql_attinfo a row for table film, attribute title, foreign table |
| This way a check on the validity of the content of field cathegory will be triggered abandoning the field, and if you want to know which are the possibilities you can input you can press '<F1>'. |
| NOTE: This is a less strong contraint that using foreingn key in the database structure, but there are reasons some times it is more difficult to inplement that contraint. No check is done for example when you delete a field so it is not a "solid" way to implement data integrity. Nevertheless it is really handy in many circumstancies. |
| Tksql first reads |
| setup.tcl |
| paced in sdtcl subdir of tcl lib directory, under linux sistem can probably be /usr/lib (possibly w/ a link to /etc/tksql. Read that file to see what can be configured Tksql then reads |
| look.tcl |
| placed in sdtcl subdir of tcl lib directory. As the name suggests, in this file you have defaults that change the look of some objects. |
| If installing with .deb or .rpm packages, the configuration files will be placed under /etc/tksql and a link will show the also in sdtcl directory. |
| ~/.tksqlrc |
| (.tksqlrc in windows systems) where several things can be defined: |
set setUp(DateStyle) "SQL,European"
set setUp(printers) {{|lpr -Pgw} {|lpr -Pfile} {|lpr -Pg} Excel}
set setUp(host) localhost
set setUp(defaultDb) danze
set sd::setUp(dbg) 0 ;# don't show me debug info
~/.tksqldb (.tksqldb in windows systems) where nicks are defined. Nicks are written thought an interface |
| Variables set in .tksqlrc will hide variable defined in setup.tcl, so you can put all interesting variables in .tksqlrc |
| tksql |
| Tksql will pop a mask to enter connection data. Data can be read from a file (see above). |
| tksql -host pcco2.mi.infn.it -db stat -tbl web |
| tksql will connect to database stat on host pcco2 and open a table "web" |
| tksql -sql 'select descr,import from tbl where code > 10 ' -total import |
| tksql will issue a query to the (default) database and show the result adding the total for the column "import". |
| tksql -sql 'select s.*, p.descr from sold s, products p where s.code = p.code' |
| tksql will issue a query and pop a table to edit the result in a way that all fields from table sold will be updatable, descr from table product will be used just as a reminder not editible. |
| I guess many, but nobody complains. The software is being used in a production environment and continuously tested but maybe other situation generate different problems. Please let me know. |
| Report bugs to <tcl-bug@bee-side.com>. |
| Written by Sandro Dentella |
|
Copyright 2001-2002 sandro dentella This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
| A very rich demo should be found in /usr/lib/sdtcl/demo |
| More documentation should be available in html format under /usr/doc/tksql/doc/html or http://www.tcltk.org |