The KMySql Handbook

September 21, 1999

This Handbook describes KMySql Version 1.2.0

Overview

What is KMySql ?
Installation
Running kmysql

Basic features

Installing a plugin
Connecting to a server
Browsing database
Sending queries
Viewing the resulting tables
Settings

Using forms and static queries

Why should I use forms
Using forms in KMySql
A simple example
Static queries

Creating tables and managing columns
Using the table editor
Frequently Asked Questions

KMySql crashed with undefined symbol: forEveryItem__9KTreeList...
KMySql doesn't compile / KMysql crashes...
Is there a way to edit table entries with KMySQL ?
Are there plugins available others that the one for MySQL ?
I want to write a plugin!
Questions about MySQL
I can't add a server beacause the server-type combo is empty. What happens?
I can't connect with KMySql, alhought it works with the mysql client.

Contact

1. Overview

1.1. What is KMySql ?

KMySql is a database client primarily designed for MySQL. Since it uses a plugin system to access database, it may be used to access others databases.

The first goal of KMySql is to provide a fully usable client in remplacement of the standard text mode client of mysql. This is mostly complete since KMySql lets the user send any SQL requests to the server. On top of that, KMySql can manage several servers at the same time.

Secondary, KMySql is indented to provide easy GUI access to database. This is far from being complete, but some features are already present: tree view making databases easy to browse, a tabular view displaying query results, a mostly completed table editor, a form feature.

What KMySql is not: A database server. MS Access. A finished stable application.

1.2. Installation

KMySql archives are available from the main KMySql site. You can also find it at the KDE ftp site. KMySql is available in source format (tar.gz) and i386 binary format (rpm). To install binary archive, the following instruction should work fine (if not refer to rpm manual page):

rpm -Uvh kmysql-x.x.x-i386.rpm

To compile and install source package, follow these simple steps:

tar zxfv kmysql-x.x.x.tar.gz
cd kmysql-x.x.x
./configure
mmake
make install

(as root)
You must also install one or more plugins to make KMySql working. Please refer to their specific manuals to install them.

1.3. Running KMySql

After installation, you can run KMySql just typing kmysql & at the command line. You can also run it from the panel, after restarting it if necessary. In all cases, this will only work if your KDE bin dir (probably /opy/kde/bin) appears in your PATH. If you need help on this subject, please refer to your KDE documentation

2. Basic features

2.1. Installing a plugin

Before anything else, you must say to KMySql which plugin you want to use. To install one, select the File/Modules... menu, and then click Add. The you must indicate the plugin path. A plugin name is of the form: kmp_xxxxxx.so. By default they are installed in $KDEDIR/lib/kmysql/plugins.

When starting, kmysql will try to detect installed plugins in the default plugin directory, and will install them. This behaviour can be disabled in the setup window.

2.2. Connecting to a server

After installing at least one plugin, you can connect to a server selecting File/Add server and filling the appearing form.

If you leave blank the password entry, you will be asked the password when connecting, and it won't be stored in the config file. If you enter your password here, you won't be asked for it each time you connect, but your password will APPEAR IN CLEAR in the config file, which is by default WORLD READABLE! In this case you really should change permissions on this file (~/.kde/share/config/kmysqlrc by default).

Use the combo box to select the server type you want to connect. Each plugin corresponds to a specific server type. After clicking on Ok and if everything goes ok, you will see a new element in the tree view on the left. To connect to this server, use the context menu (right click on it) and select Connect, or double click on it. You will then be able to browse this server in the tree view.

2.3. Browsing database

The tree view is the control center of KMySql, via context menus. Each element of the tree view has a specific context menu, please try them out! You can also double click on some of them. For example, double-clicking a a table does a select * on it. You can also create/drop bases and tables via context menus. Note that you only see databases you can connect to.

When an action returns data (select ... for example), it is displayed in the tabular view on the right side on the main window. You can also look at the status bar for informations about what happens.

2.4. Sending queries

So far only basic features are GUI-accessible. You will surely want to send hand-made queries to your server. To do that, just type them in the edit box just under the tree view and click the Submit button. You can also press Ctrl-Enter to submit your query. You will notice that every new and correct query you send is displayed in the list at the bottom of the main window, which is the queries history. You can recall these queries double-clicking on one of them. This will either re-send it or put it in the edit box depending on the settings (see 2.6. Settings).

2.5. Viewing the resuling tables

When a query returns a table, it is displayed on the right side of the main window. By default, the Default view is used, but wou can create others views, via the Views/New menu or entering a name in the edit-box in the toolbar and pressing Enter. To switch between different views, use the combo-box in the same toolbar. You can put a view in an extrnal window selecting the Views/Windowize menu.

You can also export the current view into a HTML table, via the Views/Export HTML menu.

2.6. Settings

Select File/Settings menu to set up KMySql. Note that setting includes current plugins and servers configurations, and if you save settings, KMySql will remember installed settings and servers which are present in the tree view.

3. Using forms and static queries

3.1. Why should I use forms ?

Forms are useful when you want to make queries from data submiteted in a nice GUI window. This can be useful for several reasons:

This prevent you for writing the same query n times just changing some values here and here.
Ones who don't know anything about SQL can use forms to access database. Of course, forms must before be prepared by someone who knows SQL.

3.2. Using forms and static queries in KMySql

In KMySql forms are associated with bases. To create a form, right-click on a base and select New form. The dialog editor shows up...

A form is composed of a frame containg several widgets:

Labels: only display some text.
Text fields: allows user to type text in.
Combo boxes: allows user to select an item from a list.
Push buttons: produce an event when clicked.
Check-box: The well-known widget.
Date picker: Allows user to select a date.

To create a new widget, just click on it in the toolbar. Each widget, as well as the frame, has a context menu. To set a widget properties, select Properties on this contextual menu. For every widget you must specify a name, wich will be used to retreive the widget's value (see below). Beside that, the properties window will depend on the widget's type.

Labels: This widget can just display some text. Its (useless) value will be the sams as its text.

Text fields: This widget lets your form's user enter some text in a box. The value of this widget will be the containted text.

Combo boxes: Combo boxes gives the user a choice between several values. You can give explicitly all avaible values. In this case the widget's value will be the same as the selected text. Another way to give a combo its values is just to specify a SQL query that will return available values. This query must return one or two columns, and as many rows as you want. If the SQL query returns one column, the widget's value will be the same as its selected text. If there are two columns, the combo's texts will be picked in the first column, and the corresponding value will be the value of the second column in the same row.
Example: Le'ts say you have the following table (named, say, MyTable):

Id Name Other fields...

51 Angelo blah...

07 Jones blah...

42 Ignatius blah...

29 Lane blah...

You can give the combo the following query: SELECT Name,Id FROM MyTable.
Then if the user selects the Ignatius entry the combo's value (in the query) will be 42.

Id	Name	Other fields...
51	Angelo	blah...
07	Jones	blah...
42	Ignatius	blah...
29	Lane	blah...

Check boxes: This widgets has and a label and lets the user check or uncheck it. Its value will depend on its state. So you must give it two values, one for the checked state (checked value) and one for the unchecked state (unchecked value). You can also specify the initial state of the check-box, with the initital state check-box.

Date pickers: This widget displays a calendar and lets the user select a date. This widget's value will be the selected date in the MySql format (YYYY-MM-DD).

Buttons: A button produces an action when it is clicked. You have the choices between 3 actions: Cancel close the forms and doeas nothing else; Submit submits the query, and Help show the help message specified in the form's properties.

To set the form's properties, do the same thing on the frame itself.

Then you must enter one or more queries that will be executed when your form will be submited. This is done in the forms's properties dialog. Of course, this will be usefull only if you use data submited in the form. In order to do that, you can use the value of each field in the query, just type its name between '$'. For example, if you have a field named Name, you can get the value entered in this field by the user of your form by $Name$.

If you want your form to execute several queries, separate them with a semicolon (';'). This caracter will of course be ignored when it appears in a string (either simple or double-quoted).

When your form is completed, select the Form/Save menu. If nothing goes wrong, you will see a new form in the tree-view appearing under the selected base. Right click on it and a contextual menu will appear.

To execute your form, select Execute in the contextual menu, or double-click on your form.

Note that the forms are stored in the database itself. So you must set permissions correctly to be able to create forms in a given base. If you want to control access to forms specifically, you must know that KMySql uses two tables to store forms: __KmysqlForms and __KmysqlFields. They are not shown in the tree view but you can access them via SQL queries. These two tables are created in every base where forms are used. Please contact your database administrator if you encounter problems. A good idea would be to give only read-access to these two tables for the final user, and give write access to a forms-manager.

3.3. A simple example

Let's say you have a very simple table named Friends, with two fields named name and city containig text (VARCHAR(127) for example). Of course, you can enter rows in it by hand,entering a INSERT INTO Friends VALUES... query. But let's see how this could be done via a form.

Right-click on the base containing our table and select New form. Give it a name like New friend. Then add two text fields named Name and City. Then enter the following query:

INSERT INTO Friends (name, city) VALUES ('$Name$', '$City$')

This will add a new entry in your table using the data collected in the form. Note that if you expect the form's user to enter text in a field you must supply quotes by yourself in the query, otherwise you will get a syntax error. Now save your form and try it!

We will now create a form to find users within your table. Let's create a new form named Look for friend and containing the same two fields Name and City. Then enter the following query:

SELECT * FROM Friends where ('$Name$'='' or name='$Name$') and ('$City$'='' or city='$City$')

Note that this query lets the form's user to left one or more fields blanks using '$Field$'='' in a OR statement.

3.4. Static queries

Sometimes you will want to have esay access to a complex query, but you don't need a form because the query is always the same. KMySql introduces static queries, that are shown in the tree view, and are executed directly when double-clicked.

To create one, just select New static query in the base context-menu. You will then be able to execute, edit and delete it right-clicking on it (double-click executes).

Like in forms you can put in several queries, separating them with a semicolon (';').

4. Creating tables and managing columns

To create a table, select the correponding menu item in the base's contextual menu.

You can edit, view and drop table's columns via contextual menus (right-clicking on a table or a column).

You can also rename tables selecting Rename in the menu.

5. Using the table editor

To edit a table's contents, select Edit in it's contextual menu. The entire table is then displayed, and you can edit it. to edit a cell, double-click on it, edit it's text and press Enter. Press Escape or click anywhere in the table to cancel. When finished, select the Table/Done menu or click the corresponding item in the toolbar.

6. Frequently Asked Questions

6.1. KMySql crashed with something like: `undefined symbol: forEveryItem__9KTreeListPM9KTreeListFP13KTreeListItemPv_bPv`

This means that there is an incompatibility between the kmysql binary and your installed libs. The only way to solve this problem is to compile it yourself. Don't be afraid about that, it's so easy! (see 1.2. Installation)

6.2. KMySql doesn't compile / KMysql crashes...

Please send me a mail describing precisely your system and the reported error. KMySql is supposed to work properly with Qt >= 1.42 and KDE >= 1.1.1. If you use older versions, please upgrade before sending emails ;)

6.3. Is there a way to edit table entries with KMySQL ?

Yes. See 4. The table editor.

6.4. Are there plugins available others that the one for MySQL ?

Yes, for mini-SQL (also known as mSQL) and PostgreSQL. They work for simple use but extended KMySQL functions will not work (forms, table creator and so on).

6.5. I want to write a plugin!

Cool! Just mail me before, in order to prevent the same plugin to be written several times! I recommend to look at the mysql plugin to see how to do that. Note that KMySql installs all needed headers files in $KDEDIR/include/kmysql/.

6.6. Questions about MySQL

Please don't ask me questions about MySQL itself. Please consult its very complete manual or contact http://www.mysql.org. I don't have time enough to answer such questions, and I surely don't know everything about MySQL!

6.7. I can't add a server because the server-type combo is empty. What happens?

Before adding a server, you must install a plugin via the File/Modules menu. See chapter 2.1.

6.8. I can't connect with KMySql, alhought it works with the mysql client.

First of all, you must know that there are two ways to connect to a local server: via TCP sockets or UNIX sockets. If you give 'localhost', the mysqlclient lib will use unix sockets, and if you use someting like 'myhost.mydomain.org', it will use TCP sockets.

When KMySql fails to connect via Unix socket, you probably get a message like this one:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

This is due to the fact that the actual location of the socket on thefile system depends on the type of distribution of MySQL (not KMySql). Typically, MySQL RPMs use /var/lib/mysql since the default configure setting is /tmp.

The standard mysql client works because it has surely be compiled the same way than the server. So it looks for the socket in the right place. But if you use a pre-compiled version of the plugin, the default socket location may not be the same as the server's one.

Solution: You must tell KMySql where is the socket in your case. This is done in a MySql-specific configuration file. The user-specific one is ~/.my.cnf, and the global one is /etc/my.cnf. You have to add the following to one of these files:

[client]
socket=/path/to/the/socket

(Please replace /path/to/the/socket by your actual socket path (eg: /tmp/mysql.sock).

The above will make all mysql clients to use /path/to/the/socket as the Unix socket. If you prefer this configuration to be kmsql-specific, put the following instead:

[kmysql]
socket=/path/to/the/socket

Please refer to the MySql documentation for further information.

7. Contact

Here is the KMySql hompage: www.xnot.com/kmysql.
If you have problems with KMySql, please mail here: kmysql@penguinpowered.com
There is a mailing list for KMySql. To subscribe, send a mail to kmysql-subscribe@xnot.com
Author: Frédérik BILHAUT bilhaut_f@mail.cpod.fr
Some links: KDE, MySQL, Freshmeat.net