# 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 &knoda;tutorials) and API references available in the internet &knoda; documentation). 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)

See information for details.

## 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

- snap to grid x: used in forms and reports for positioning objects

- snap to grid y: used in forms and reports for positioning objects

- Metric system: If 'Relative' forms and reports size and position values are from 1% to 100% and 'Absolute' the values are in pixels. The advantage of 'Relative' is when you resize forms or reports the fields resize and keep their relative size. Example: Switch the page format of a report from "Portrait" to "Landscape" and the layout remains usable.

- Store password: per default the password will not be stored in connection files (which are used to allow a more convenient login), if set the password will be stored (might be a security problem!)

- Automatic data update: If set (default) the datasources will store changes automatically, otherwise it will be asked before storing the changes

### Appearance options

- 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

- Use single click: &knoda; uses double click in the database window (the left listview). If set you can use single click (not when using knoda --classic)

### 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

- Locale: Sets the used local charset in case you need to run knoda under an other locale than your computer has

- Measure system:

### Report options

Printcommand:Needed for the command line tools of hk_classes. In most cases 'lpr' is the correct value.

Embed fonts:If the printed text does not look like the preview, set this value to "yes"

## The database settings dialog

### Open and store files

Automatic data update: If set (default) the datasources will store changes automatically, otherwise it will be asked before storing the changes

Object handling: &knoda; (exactly &hk_classes;) is able to store queries,forms and reports within the database. Per default they are stored locally in a subdirectory called ~/.hk_classes.

#### Object handling

To do so, you need first to create a system table in the knoda menu with 'Create system table'. Then you can switch the storage points in the same dialog to "Central" (meaning "store and in the database, and not locally in files").

Then you will have to upload your previously designed forms and reports with Settings->Object handler. From now on, everybody will be able to access the centrally stored files (under the condition that on the clients the load and store mode is also set to "Central").

To restrict the write access you will have to use either the privilege system of Postgres (on table "HKCLASSES"), or let the users call this database by using knoda-rt (the runtime version of knoda).

### Regional

The following tab is only available for databases that don't have their own charset handling (like Xbase and SQLite). You can set the original charset of the database. &hk_classes; uses 'iconv' to convert charsets, use 'iconv --list' to get a full list of all available charsets.

#### Regional

## 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. If you want to use a socket for the connection, leave the "Host" field empty. &hk_classes; will then read from ~/.my.cnf. You can set the socket name in the section [hk_classes]. Example:

    [hk_classes]
    socket		= /var/lib/mysql/hk_classesmysql.sock

### 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). To write data you need pxlib version 0.6 or higher. 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;