# The Kmysqladmin Handbook

Rajko
Albrecht

    ral@alwins-world.de

1999
2003
2004
2005
Rajko Albrecht
&FDLNotice;
08/07/2005
0.7.2

&kapp; is an application for managing &mysql;-databases. This document describes &kapp; Version 0.7.2.

KDE
kdeutils
Kapp
kmysqladmin
database
mysql

## Introduction

&kapp; is a tool for managing &mysql; databases. Please report any problems and feature requests to the author.

It can:

- Displaying and edit the contents of tables within a separate view.

- export results of a query into another format (ASCII, Latex, HTML, CSV)

- Edit user rights

- Creating databases and tables

- watching status and threads continuously

## Using &kapp;

### The main window

Here's a snapshot of &kapp; The Main Window

The main part of this is the log-window. &kmysqladmin; will stores all activities during the work here. The most used commands are stored inside the toolbar. If you are not connected, most of them are disabled.

#### Connect/Disconnect SQL Server

To connect to a &mysql;-server klick on most left button of the button bar or hit Ctrl c and a login screen apears. &kmysqladmin; will store the values of the last connected server so you can select "Restore last login" from the "File" menu.

Fill out the required fields inside the login window. &kmysqladmin; shows the last values set except the password. Hit the OK-Button to proceed.

For disconnect hit Ctrl d or select File Disconnect .

#### Quick connects

For using connection profiles select File Quick connects .

You can setup connection profiles from within the setup menu

### Browsing through the tables

The most important thing - looking trough the stored datas inside the connected sql server. Snapshot of browsing dialog The Table browse dialog That screenshot is the oldstyle one. The new (and default) one ist mostly the same but elements are arranged different. There are no functional differences between both.

#### Displaying the content of a table

Select the database and table you want browse. You will only see the databases and tables you have "select"-access. If you want to see the result sortet, select the sort-criteria from the last list in the upper left corner. If you check "Desc" the sort order will be descending.

Now you can hit the "Go!" button and &kapp; will generate the right statement und display the result. It will limit the result up to 99999 elements 'cause it will run into troubles if there are more elements to display.

When retrieving "BLOB" fields it &kapp; can display them inline when them contains images. See how to setup it.

#### Specify data to display

If you don't want all columns displayed you can select in the listbox in the upper middle which you're interested in. No selection means "Display all".

#### Limit the results

You can limit the results by entering the required values into the spinboxes "limit offset" and "limit count". "Limit count" is, of course, the number of results &kapp; should fetch. The offset means the point starting the count.

Example:

Your table has 1000 entries. You want to display the entries 50-100. So you will enter "50" as offset and "50" as count. Be carefull: The results depends on how you sort the table! Means, the limits will be set by &mysql; itself 'cause &kapp; generates a "select <whatever> LIMIT <offset>,<count>" statement.

#### Searching for specific entries

When you hit on the "Search" - button a dialog appear containing all columns of current displayed table. There you can input values you're looking for. This time &kapp; just produce simple "select" statement with a "WHERE <column> LIKE <content>" modifier. The values of the other elements (limits, data to display, sort order) will of course be respected.

Remember that the wildcard character is not the star (*) but the percent sign (%)!

#### Save the result

If you have result you can save into various formats. Simply hit the "Save" button and select filename and format to save.

#### Adding a new row

For inserting new data just klick on "Add Row". Than a dialog comes up you can fill out with the required values. For AUTOINCREMENTING fields you can not enter a value, them will be hidden.

After hitting "Ok" &kmysqladmin; creates a sql statement inserting the new values und execute it. You can see the result inside the log-window.

#### Modifying a row

This option is only enabled if your result contains an unique key for identifing the entry select. Means, if you did not display a PRIMARY or UNIQUE Key (or your table contains no one) modify is not possible.

Otherwise: mark a single row you want to modify und hit enter, doubleclick it or hit the "Modify row" button. Now mostly the same dialog as while searching appears. But the columns containing autoincrementing values will be hidden.

Make your changes and hit "Ok". Then the data will changed (if possible) and you can see the change inside the result list.

#### Deleting a row

Like while modifing this options is possible if you have displayed a unique key. Otherwise the button will be disabled.

Just mark the row you want to delete and hit the "Delete selected" button. After the answering "Yes" to the following secure question &kapp; will delete the entry and generate a "delete from ..." statement and execute it on the server.

#### Deleting all

"Delete all" empties the whole table. Means: it just generate a "flush <table>". So be realy carefull! Thats why this button has no shortcut so must explizit select it via mouse or TAB key.

### Modify access rights

You can modify the accessrights to the current connected &mysql; server using the standard tableview dialog, of course. 'Cause this is sometimes a little bit difficult, &kapp; has a special dialog for modifing the grant table of a sql server.

You can open this dialog selecting Server Edit access rights within the mainwindow. After that a window displaying the userrights appears.

If you're not sure about the meaning of the grants, please consult the &mysql; documentation.

Inside each of the tabs you can select an entry of the list for displaying the detailed information for this entry.

#### Add new host rights

Remember: The usage of this rights is deprecated!

To add rights for a new host select the first tab called "Host". Then hit the "Add" button (Alt a) and a new dialog box appears.

Fill out the fields as you want. If the values seems to be plausible the &okbutton; will be enabled. If you hit ok, &kapp; inserts the values into the grant table of the &mysql; server and reload the grants.

#### Add new user rights

For adding new user specific rights select one of the tabs "Users", "DBs", "Tables" or "Columns". A wizard based dialog appears which will guide through the settings required.

First you have to insert the username and hostname. The username can left blank as described in the &mysql; documentation. The passwords you have to insert twice to make sure that you enter the right one 'cause input is - of course - hidden. If this is done, click on "Next".

In the next page you can select the database and table the rights should be set. A asterisk ("*") means for all databases or tables. If the database/table isn't on the &mysql; server yet you can enter them manual. Otherwise simply select them from the dropdown list. If done, hit "Next".

Now you can select the rights you want set. If you had gave an existing database and tablename you can create column specific rights by marking the "Enable single column rights" checkbox. If you have selected the rights simply hit the &finishbutton; and &kapp; generates the right GRANT statement for inserting the new rights.

#### Changing existing rights

Mark the rights entry you want to change and hit the "Edit" button. Depending on the type of right a dialog box appears where you can change the rights. The work on this is like described above while adding new rights.

### The ScriptEditor

With the script editor you can evaluate sql scripts you have written. With &kde; 3.1 and &Qt; 3.1 libs it supports syntaxhighlighting so you'll (hopefull) get a better feeling for what you're doing.

Snapshot of browing dialog The script editor

#### Loading and saving a script

For loading an existing sql script, select Ctrl o File Load script. If you had open or saved the file before you can look into File Recent files for faster finding. The editor will switch to the first tab and loads the script into the textarea.

If you have syntax highlighting enabled, be carefull. At the moment it isn't the best, so in large files (&eg; larger than 500kByte) it realy slows down! (As in most kde-based editors).

To save a file simply press Ctrl s.

#### Executing a script

To execute a script simply hit Alt x or click on the "Execute" button.

&kapp; now splits the entire script, remove the comments and let the server execute each statement. The results for each statement (if someone exists) will displayed inside the "Result" - Tab. If an error occures, the execution stops and the editor switch to the error tab where the error will displayed.

#### Script format

The Script format is mostly the same as &mysql; in batch mode uses. Means:

- A statement must be finished with a ";"

- A statement can written over more than one line.

- Comments are ignored.

  Comments can be single line comments and multi-line comments. The singleline comments can start after the text on one line. So you can write:

            select * from mytable // mytable is the whole one
            where id<500;

  The text after // will be filtered out up to the end of line. The same rule is working if you start with # instead of the double slashes. &eg;:

            select * from mytable # mytable is the whole one
            where id<500;

  has the same effect.

  For larger comments you can use multiline comments (a special of this editor). It works like comments in C++/C or PHP. A Multiline comment starts with slash-star (/*) sequenz and stops with a star-slash (*/) sequenz. All text between both will not be parsed.

              /*
               * this script is made to do nothing
               * usefull.
               */
               use test;
               show tables; # one usefull ;)

All other restrictions are given by the &mysql;-server itself, so if you not familiar with it, read the &mysql; documentation how the statements are build.

#### Settings for the script editor

Settings for the scripteditor can made under the menu "Settings".

- Show result as richtext: If set, the result will format the output window with html tags.

- Use highlighting for sql scripts: (only on &Qt; 3.1 systems) Enable/disable syntax highlighting for the editor

- Setup script color and style: (only on &Qt; 3.1 systems) Shows a small dialog where you can setup the colors and the style for the elements of a sql script.

### Settings for &kapp;

#### Setting up fonts

Just select the fonts you want to use inside &kapp;. The "proportional font" is used inside all tabbed views eg. the table browsing dialog for instance.

#### Timers

- Poll threads: How often should the thread list read from server if displayed

- Refresh status: How often should the status view refreshed if displayed

#### Miscellaneous

Here you setup some rules for displaying fetched data.

Display blob as images

  If you fetch blobfields &kapp; will be able to check if them contains image-data. If yes, them will displayed inline instead just a simple information "[BLOB]". It depends on your &Qt; library which image types &kapp; will display.

Display original size

  When displaying images display them in size them are stored inside &mysql; server. Be carefull: If them are big it looks a little bit strong...

Display images scaled

  Or scale images down. Them will just downscaled, not upscaled. E.g., if your setup up a maximum size of 60x60 pixels and the retrieved image is 48x48 pixels &kapp; display them as stored. Images will scaled this way that them into this range. E.g. it will never be larger as these values, but smaller if needed.

Use compact layout

  If activated most dialogs like browsing tables are displayed inside the main window itself and not seperate.

Use old tablebrowser

  If activated use the old tablebrowser instead of the newer one.

### The Menu Entries (main window)

#### The File Menu

Ctrl c File Connect to SQL Server

  Connecting and Login to a &mysql; server

Ctrl d File Disconnect

  Disconnect from &mysql; server

File Restore last login

  Connecting and Login to a &mysql; server using the last successfull login

File Quick connect

  Logging in to a mysql server using a connection profile

File Clear output

  Clears the output of the logwindow

Ctrl s File Log output to file

  Logs the output to a file instead only to the window

Ctrl n File New

  Creates a new window

Ctrl w File Close Window

  Close the current active window, disconnects from sql server. If it is the last window quits.

Ctrl q File Quit

  Quits &kapp;

#### The Server menu

Server Flush host cache

  Flushes the cache of &mysql; server connected to

Server Close open tables

  Close all open tables of &mysql; server connected to

Server Begin new server log

  Attempts to restart the log of &mysql; server connected to

Server Reload permissions

  Let the &mysql; server reread the permissions tables

Server Shutdown server

  Stops the server connected to

Server Edit access rights

  Opens a dialog for editing the grant entries of &mysql; server

Server Show status

  Opens a window with some status infos of the server

Server View threads

  Opens a window listing the current threads of &mysql; server (depends on your rights)

Ctrl e Server Sql Editor

  Opens the build in editor for editing and executing sqlscripts

#### The DB menu

Ctrl b Database Browse Tables

  Open the dialog for browsing through the data stored in the &mysql;-server (See here)

Database New Database

  Let you creating a new database

Database Create table

  Let you creating a new table

Database Alter table

  Let you modify the structure of a table

Database Rename table

  Let you rename an existing table

Database Drop table

  Delete a complete table

### Menue entries (script editor)

#### The File menu

Ctrl n File New script

  Creates a new script and forget the content of the editor

Ctrl o File Load script

  Read a existing script into the editor

Ctrl s File Save script

  Save the content of editor to file

File Save script as...

  Save the script to a new file

File Open recent

  Opens a recent open or saved file

File Save result

  Save the content of the result tab

Ctrl w File Close

  Close the editor window

#### The edit menu

Ctrl f Edit Find...

  Search for string in the current active tab

F3 File Find next

  Search again for string in the current tab

#### The Settings menu

Settings Show result as richtext

  Weather to display the results richtext (HTML) formatted or not

Settings Use highlighting for sql scripts

  Weather to display the script highlighted or not. Only if &kapp; is compiled with &Qt; 3.1 or above.

Settings Setup script colors and style

  Opens a dialog to select colors and style for syntaxhighlighting. Only if &kapp; is compiled with &Qt; 3.1 or above.

## Questions and Answers

&reporting.bugs; &updating.documentation;

Why I can not edit or delete any row within the table browser window?

Your result doesn't have any unique key displayed. Check out the Browsing tables for what you can doing.

When putting large BLOB or TEXT data to server it will lose connections. Why?

The max_allowed_package parameter of the server is to small. Increase them in /etc/my.cnf - and read the &mysql; handbook about these values.

## Credits and License

&kapp;

Program copyright 2003 Rajko Albrechtral@alwins-world.de

&underFDL;

&underGPL;

## Installation

### How to obtain Kmysqladmin

&kapp; can be found at www.alwins-world.de.

### Requirements

In order to successfully use &kapp;, you need at least &kde; 3.0. and &Qt; 3.0 mysql-shared-libs are required at runtime. For some specials &kde; 3.1 or &Qt; 3.1 or above is needed (like syntaxhighlighting inside the script editor)

You'll find a list of changes inside the changelog.

### Compilation and Installation

In order to compile and install &kapp; on your system, type the following in the base directory of the distribution:

    % ./configure
    % make
    % make install

Since &kapp; uses autoconf and automake you should have not trouble compiling it. Should you run into problems please report them to the KDE mailing lists and to the author.

### Configuration

&documentation.index;