title: Table of Contents 
alias: TOC
  - [Welcome to SQLView]
  - Description of the GUI
    -- [Database Browser]
    -- [SQL Editor]
    -- [Data Viewer]
  - [SQLite3 functions]  
  - [SQLite3 window functions]
  - [To Do]
  - [About]
  - [History]
  - [License]
  - [Links]

-------
title: Welcome to SQLView
alias: overview

SQLView  is a  standalone  application,  but at the same time a Tcl/Tk  widget
which can be added to  applications  in Tcl/TK, R or Python if using  Tkinter.
SQLView is the  predecessor  of the old  DGDBrowser a cross platform and cross
database  application.  With  SQLView you can explore and query  SQLite, ODBC,
PostgreSQL and MySQL databases with the same interface.

The advantages of SQLView are: 
  * cross platform: different Unix variations, Linux, OSX, Windows
  * cross database: different databases like SQLite, MySQL, PostgreSQL 
  * cross programming language: usable in Tcl, R, Perl and Python
  * standalone application as well as a widget component

 
-----
title: Database Browser

In the lower  left is the  database  browser.  Shown are in a  exandable  tree
widget  tables,  views and  columns  of the  database.  The user can click and
expand the tree to inspect  table and  columnnames.  Using  double click on an
item the user can copy and  paste  the  table and  column  names  into the 
[SQL Editor] to it's right. Furthermore the user can:

  * open an other database using the file open button
  * refresh the database schema if the schema was changed
  * invoke the application help by pressing the help button
  * exit the application by pressing the exit button on the right

  
-----
title: SQL Editor

The SQL  Editor  allows  the  user to enter  SQL  queries  or data  definition
commands in a text  window. The results of those  quieres can be seen below in
the [Data Viewer].  The SQL Editor  provides  syntax  highlighting  to aid in
formulating the queries. Furthermore it is possible to open new editor tabs by
clickin  on the tab above of the editor and  pressing  ''<Control-t>''.  Using
mouse  right  click  on the  tab  the  user  can  rename  the tab on top.  Tab
duplication and renaming is mirrored at the [Data Viewer]. Every editor window
has it's own [Data Viewer] widget attached to it. Working in different tabs with
the editor and with different  results views greatly  improves the development
of complex queries.

Not required  code parts can be  uncommented  in the editor  using the the double
hyphen at the beginning of a line like here:

  " -- this is a SQL comment "
  
The buttons at the top provide standard functionality of an text editor.

  * open a new file
  * open an existing file
  * save the current file
  * save file with a new file name
  * undo 
  * redo
  * execute query, the button on the very right
  
The  editor  has  two  standard   shortcuts  to  execute  the  current  query:
''<Shift-Return>''  and  ''<Control-x><Control-s>''.  The results of the query
can be seen in the [Data Viewer].

-----
title: Data Viewer

The data viewer window display the query results which are entered in the [SQL Editor]. 
Per default a maximum of 10.000  results will be  displayed. If there
are more rows a hint  about this will be shown in the  message  line below the
table widget. Errors in the queries will be as well displayed  there. The Data
Viewer  provides  also sorting  facilities  by clicking on the table  headers,
repeated  clicking  inverses  the sort  order. Be aware if you have  more than
10.000 rows in your data set  ordering  only affect s the visible part of your
data. You should use the  ''order by column''  clause in your query to achieve
correct ordering.

Example:
  select * from employees order by employee_id asc

-----
title: SQLite3 functions

The following core functions are available in sqlite3:

  * abs(X)
  * changes()
  * char(X1,X2,...,XN)
  * coalesce(X,Y,...)
  * glob(X,Y)
  * hex(X)
  * ifnull(X,Y)
  * instr(X,Y)
  * last_insert_rowid()
  * length(X)
  * like(X,Y)
  * like(X,Y,Z)
  * likelihood(X,Y)
  * likely(X)
  * load_extension(X)
  * load_extension(X,Y)
  * lower(X)
  * ltrim(X)
  * ltrim(X,Y)
  * max(X,Y,...)
  * min(X,Y,...)
  * nullif(X,Y)
  * printf(FORMAT,...)
  * quote(X)
  * random()
  * randomblob(N)
  * replace(X,Y,Z)
  * round(X)
  * round(X,Y)
  * rtrim(X)
  * rtrim(X,Y)
  * soundex(X)
  * sqlite_compileoption_get(N)
  * sqlite_compileoption_used(X)
  * sqlite_offset(X)
  * sqlite_source_id()
  * sqlite_version()
  * substr(X,Y)
  * substr(X,Y,Z)
  * total_changes()
  * trim(X)
  * trim(X,Y)
  * typeof(X)
  * unicode(X)
  * unlikely(X)
  * upper(X)
  * zeroblob(N)

Documentation     about    those     functions     can    be    found    here:
https://www.sqlite.org/lang_corefunc.html

The following aggregate functions might are available also:

  * avg(X)
  * count(*)
  * count(X)
  * group_concat(X)
  * group_concat(X,Y)
  * max(X)
  * min(X)
  * sum(X)
  * total(X)

Documentation     about    those     functions     can    be    found    here:
https://www.sqlite.org/lang_aggfunc.html
  
The  SQLite3  databases  might be loaded further with the set of  extension functions
available in the RSQLite extension for the R programming language.

Beside the standard functions listed above the following functions might be available:

'''Math''': acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference,
degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp,
log, log10, power, sign, sqrt, square, ceil, floor, pi.

'''String''': replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim,
replace, reverse, proper, padl, padr, padc, strfilter.

'''Aggregate''': stdev, variance, mode, median, lower_quartile,
upper_quartile.

You  check  if those  functions  are  available  by  executing  the  following
statement:

  select cos(radians(45));

These  extension  functions  where taken from the github  source  repository of
RSQLite: 
  https://raw.githubusercontent.com/r-dbi/RSQLite/master/src/vendor/sqlite3/extension-functions.c

-----
title: SQLite3 window functions

With  version  3.25 also  11 window  functions  are  available  in  SQLite3.  For
documentation about those functions check: https://www.sqlite.org/windowfunctions.html

The window functions are:

  * row_number()
  * rank()
  * dense_rank()
  * percent_rank() 
  * cume_dist() 
  * ntile(N)  
  * lag(expr)
  * lag(expr, offset)
  * lag(expr, offset, default)
  * lead(expr)
  * lead(expr, offset)
  * lead(expr, offset, default)
  * first_value(expr)
  * last_value(expr)
  * nth_value(expr, N) 

-----
title: To Do

  * balloon help tips
  * show log file by click in [SQL Editor]
  * check for PostgreSQL, MySQL, ODBC
  * Login hsells for PostgreSQL and MySQL
  * check on Windows, OSX

-----
title: Author
alias: About

@ Dr. Detlef Groth, Schwielowsee, Germany, 2018.


Thanks:

  * Jean-Claude Wippler for inventing Tclkits
  * William Duquette for his great Snit package, which uses mainly  delegation for object oriented programming
  * HyperHelp system by Keith Vetter and Richard Suchenwirth https://wiki.tcl-lang.org/page/A+Hypertext+Help+System
  * The Tclcore-Team for their incredible piece of software

Software:
  * Tcl/Tk $::tcl_patchLevel
  * TDBC [[package require tdbc]]
  * sqlite3 [[package require sqlite3]]
  * sqlview [[package require dgw::sqlview]]
  
-----
title: History

SQLView  started  on  Saturday  2018-10-27  in the Berlin  S-Bahn.

  * Version 0.0 Oct 27, 2018
  * Version 0.1 Oct 30, 2018, release to students
  * Version 0.2 Nov 11, 2018
    -- regex command
    -- stats from R embedded
    -- red background in case of errors
    -- busy widget to indicate statement execution
    -- possibilty to extend sqlite with tcl functions started
    -- fix: sort for integer and double columns

-----
title: License

Copyright (c) 2018 Dr. Detlef Groth

All rights reserved.

Redistribution and use in source and binary forms are permitted
provided that the above copyright notice and this paragraph are
duplicated in all such forms and that any documentation,
advertising materials, and other materials related to such
distribution and use acknowledge that the software was developed
by the <organization>. The name of the
<organization> may not be used to endorse or promote products derived
from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED ``AS IS'' AND WITHOUT ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.


-----
title: Links

  * http://www.sqlite.org
  * https://www.tcl.tk/
  * https://wiki.tcl.tk/
