# The &knoda; Handbook

Horst
Knorr

    hk_classes@knoda.org

2004
Horst Knorr
&FDLNotice;
11/22/2004
0.00.02

KDE
DBMS
Kapp
database
frontend
mysql
odbc
postgres
postgresql
sqlite
sqlite2
sqlite3
dbase
xbase
firebird
paradox
corel
Access
Microsoft
ms
python
knoda

## Introduction

&knoda; is a database frontend and comprises tables,queries,views, forms and reports, which are scriptable via Python. There are tutorials and API references available at ( or in the internet &knoda; scripting tutorial). Please see the &knoda; tutorial for further information.

Drivers for the following backends are available:

- Firebird

- Mysql

- Postgres

- ODBC

- SQLite (local file format)

- mdb (M$ Access) (local file format)

- Paradox (Corel) (local file format)

- DBase (XBase) (local file format)

## Installation

Knoda itself needs 2 packages, hk_classes and KDE.

### &hk_classes;

Requirements:

- Firebird, Mysql, Mdbtools (a current CVS version is strongly recommended), pxlib (Paradox), Postgresql, Sqlite, XBase or ODBC (either unixODBC or iodbc) has to be installed. If hk_classes claims that your database server does not exist on your system, make sure that the 'database'-devel.rpm package of your distribution is installed (the name of this package slightly differs from distribution to distribution , e.g. on Mandrake 8.2 the mysql-devel package is called 'libmysql10-devel-3.23.47-5mdk.i586.rpm').

- Python and its '-devel' packages have to be installed to use the scripting support

    # ./configure [options]

    For different Firebird directories on your machine, use the following options:
      --with-firebird-dir=PATH   Path to the Firebird directory
      --with-firebird-incdir=PATH   Path to the Firebird header files
      --with-firebird-libdir=PATH   Path to the Firebird library

    For different Mysql directories on your machine, use the following options:
      --with-mysql-dir=PATH   Path to the Mysql directory
      --with-mysql-incdir=PATH   Path to the Mysql header files
      --with-mysql-libdir=PATH   Path to the Mysql library
      --without-mysql          disable mysql plugins

    For different Postgres directories on your machine, use the following options:
      --with-postgres-dir=PATH   Path to the postgres directory
      --with-postgres-incdir=PATH   Path to the postgres header files
      --with-postgres-libdir=PATH   Path to the postgres library
      --without-postgres          disable postgres plugins

    For different ODBC directories on your machine, use the following options:
      --with-odbc-dir=PATH   Path to the odbc directory
      --with-odbc-incdir=PATH   Path to the odbc header files
      --with-odbc-libdir=PATH   Path to the odbc library
      --without-odbc          disable odbc plugins

    For different SQLite version 2.x directories on your machine, use the following options:
       --without-sqlite        disable sqlite2 plugins
      --with-sqlite-dir=PATH   Path to the sqlite2 directory
      --with-sqlite-incdir=PATH   Path to the sqlite2 header files
      --with-sqlite-libdir=PATH   Path to the sqlite2 library

    For different SQLite version 3.x directories on your machine, use the following options:
      --without-sqlite3       disable sqlite3 plugins
      --with-sqlite3-dir=PATH   Path to the sqlite3 directory
      --with-sqlite3-incdir=PATH   Path to the sqlite3 header files
      --with-sqlite3-libdir=PATH   Path to the sqlite3 library

    For different mdbtools directories on your machine, use the following options:
      --without-mdb           disable mdb (M$Access) plugins
      --with-mdb-dir=PATH   Path to the mdbtools directory
      --with-mdb-incdir=PATH   Path to the mdb header files
      --with-mdb-libdir=PATH   Path to the mdb library

    For different xbase directories on your machine, use the following options:
      --without-xbase         disable xbase plugins
      --with-xbase-dir=PATH   Path to the xbasetools directory
      --with-xbase-incdir=PATH   Path to the xbase header files
      --with-xbase-libdir=PATH   Path to the xbase library

    For different pxlib directories on your machine, use the following options:
      --without-paradox       disable paradox plugins
      --with-paradox-dir=PATH   Path to the pxlibs directory
      --with-paradox-incdir=PATH   Path to the paradox header files
      --with-paradox-libdir=PATH   Path to the paradox library

    After starting ./configure as described in section 3, call
    # make
    # su
    # make install

### &knoda;

&knoda; needs hk_classes and KDE installed. Then you can compile &knoda; with:

     ./configure
     make
     make install    (as root)

## The Options dialog

&knoda; can be highly configured to fit your needs.To do so press the "Options"-button in the main window.

### General options

In the 'General'- options menu you can set:

- the driverpath: the directory where the database drivers are located

- the default driver: the driver which is displayed in the &knoda; main window as default

- show maximised: Tables, queries, forms and reports can either be shown in their construction size or maximized

- Show all questions:Per default &knoda; asks questions like "Are you sure?" before executing actions

### Defaults options

The 'Defaults'- section allows you to define the look of your data.

- Default font: the font which is used to display grids, forms and reports

- Text alignment: the alignment of text data

- Number alignment: the alignment of numeric data

- Separator: whether numbers should include the thousands separator (only valid if this is supported by locale)

- Precision: the numeric precision of floating data

### Regional options

&knoda; uses the German format for displaying the date and time. If you want or need to change it, no problem. If you are not happy with the date "31.12.2001" and prefer "2001-12-31", enter Y-M-D in the date field. The 'Date & Time format' is needed for fields which have both, date and time data.

- D - Day

- M - Month

- Y - Year

- h - hour

- m - minute

- s - second

### Report options

There are some options you should set before using reports. First is your character set encoding. If you are using unicode, set the encoding to that which comes closest to your requirements. If you want to use KDE's new kprinter dialog, change the printer command from 'lpr' to 'kprinter'.

#### Character set encoding - Country table

ISO-8859-1
ISO-8859-15

  Western Europe, ISO 8859-15 additionally contains the EUR currency symbol

  French (fr), Spanish (es), Catalan (ca), Basque (eu), Portuguese (pt), Italian (it), Albanian (sq), Rhaeto-Romanic (rm), Dutch (nl), German (de), Danish (da), Swedish (sv), Norwegian (no), Finnish (fi), Faroese (fo), Icelandic (is), Irish (ga), Scottish (gd), and English (en), Afrikaans (af) and Swahili (sw), the entire American continent, Australia and much of Africa.

ISO-8859-2

  Central and Eastern Europe. Czech (cs), Hungarian (hu), Polish (pl), Romanian (ro), Croatian (hr), Slovak (sk), Slovenian (sl), Sorbian.

ISO-8859-3

  South European, Esperanto (eo) and Maltese (mt)

ISO-8859-4

  North European,Estonian (et), the Baltic languages Latvian (lv, Lettish) and Lithuanian (lt), Greenlandic (kl) and Lappish

ISO-8859-5

  Cyrillic letters, Bulgarian (bg), Byelorussian (be), Macedonian (mk), Russian (ru), Serbian (sr) and pre-1990 Ukrainian (uk)

ISO-8859-6

  Arabic

ISO-8859-7

  Greek

ISO-8859-8

  Hebrew (iw) and Yiddish (ji)

ISO-8859-9

  Turkish

ISO-8859-10

  Latvian, Inuit and non-Skolt Sami (Lappish) letters and Icelandic to cover the entire Nordic area

KOI8-R

  Cyrillic characters

KOI8-U

  Cyrillic characters, including Ukrainian

## Driver specific information

### Firebird

The password dialog has an entry called "Emulate boolean". Firebird itself does not support internally a boolean field type (even if you can use boolean in CREATE TABLE statements). If "Emulate boolean" is set to 'yes', &knoda; interpretes the field type CHAR(1) as a boolean field.

Firebird needs a database name with its full path as connect parameter. Another strange "feature" of Firebird is that it has no possibility to query for available databases on the server, so the database list remains empty.

### Mysql

The password dialog has an entry called "Emulate boolean". Mysql itself does not support internally a boolean field type (even if you can use boolean in CREATE TABLE statements). If "Emulate boolean" is set to 'yes', &knoda; interpretes the Mysql specific field type TINYINT as a boolean field.

### Postgres

Depending on the way Postgres is configured, the login parameters differ. Try to leave "hostname" empty.

Postgres has one -well- bit strange behaviour. It needs a database name as connect parameter, even to get a list of the existing database names on a server. Per default &hk_classes; uses 'template1' as name. If you don't have permission to connect to this database, you must enter an existing database name where you have the permission to connect. In all other cases you can leave the database name field empty.

### ODBC

For those wanting to connect to M$ SQLServer the project FreeTDS is recommended.

### SQLite

SQLite is a file based database system. Per default the files are stored in ~/.hk_classes/sqlite2/localhost (SQLite 2.x) and ~/.hk_classes/sqlite3/localhost (SQLite 3.x) respectively and have the extenstions .hk_sqlite(2|3). &knoda; can handle database files in other directories. You need to enter the full path in addition to the name (Menu point 'Open local database').

SQLite does not support to alter an existing table, so the table design window is disabled. The only exception is from SQLite version 3.2.0 on, which supports adding new columns.

### mdb - M$ Access

mdb is the Microsoft Access© database format. It is a file based database system. As the underlying mdbtools does not support writing data yet, this driver is read only.The SQL support is also very basic and error-prone, so SQL-support is deactivated. When mdbtools evolves, &knoda; will support these features.

&knoda; can handle database files in other directories. You need to enter the full path in addition to the name (Menu point 'Open local database').

### paradox - Corel Paradox

Paradox is a file based database system used by Corel© Paradox (former Borland© Paradox). As the underlying pxlib does not support writing data yet, this driver is read only. There is no SQL support.

&knoda; can handle database files in other directories. You need to enter the full path in addition to the name (Menu point 'Open local database').

### xbase (DBase)

DBase is a file based database system. Per default the files are stored in ~/.hk_classes/xbase/localhost/<DATABASENAME> and have the extenstions .dbf. &knoda; can handle database files in other directories. You need to enter the full path in addition to the name (Menu point 'Open local database').

## SQL syntax

You can use the SQL syntax of your backend server. There is one major difference you have to take account of: only use the ANSI SQL delimiters (both textdelimiters and identifier delimiters), which are

- textdelimiter: a single quote '

- identifier delimiter: a double quote "

The advantage of this is that you can copy whole databases between backends (e.g. from Mysql to Postgres) via drag&drop and the query still works.

So a correct SQL statement would be:

### Sample SQL statement

    SELECT "mytablealias"."fieldname" FROM "testtable" AS "mytablealias"
    WHERE "myfield"='mystring'

You can use all functions your SQL backend provides, but it is up to you to make sure that your SQL statement is compatible between backends. Do not use TRUE or FALSE in your query statements. Use %TRUE% and %FALSE% instead. Some SQL database servers do not support the use of boolean fields, so the &hk_classes; have to simulate boolean fields. A valid SQL statement would be:

    select * from mytable where myboolfield=%TRUE%

## Scripting &knoda;

&knoda; can be scripted using Python. See ( or in the internet &knoda; scripting tutorial). If you need a reference see ( or in the internet &knoda; scripting reference).

## Tips and tricks

### How can I learn using &knoda;?

See the ( or in the internet &knoda; tutorial).

### Which database backend should I use / do you recommend?

It depends totally on what you want to do. If you did use Access or if you want to exchange the whole database with others, the database should be stored in a single file. Then SQLite is your choice.

Multi user databases should prefer either Mysql or Postgres.

### &knoda; does not find my database driver!

Probably it is not compiled. &hk_classes; tries to autodetect the database backends. If your database server is installed, but &hk_classes; does not find it, you probably have to install the "database devel-rpm" of your distribution. Example: For Mysql it is called mysql-devel.rpm (exact name is distribution specific). Then you can re-configure and recompile &hk_classes;.

### Where are queries, forms and reports stored?

Per default all files are stored in the directory ~/.hk_classes/<SQLSERVER>/<HOST>/<DATABASENAME>.

Example: If you have a Mysql database named exampledb on your local host, it would be /home/horst/.hk_classes/mysql/localhost/exampledb

It is possible to store these objects within your database. Open the database and select 'Administration'->'Database settings'. There you first have to create a hidden system table called 'HKCLASSES' and then you can select which objects have to be stored where.

### How to copy databases and tables between different database backends

You want to convert a Mysql database to a Postgres database or vice versa? No problem, open the Mysql database you want to copy and any Postgres database. Drag the database name from the left window (there where you can see the tables, queries, forms and reports) and drop it in the left window of the target window, see and stun. A new database will be created and the data converted. This works from/to any backend.

Another possibility is to use the 'Export to database' menu or the 'Import from database' menu, which you can find in the 'File' menu.

#### The export/import manager

## Credits and License

&underFDL;

&underGPL;