Database Usage

Code has been included to create an initial database to hold your data if one doesn't exist (currently only for MySQL and SQLite). However it is strongly recommended that you pre-create a database, because most of the products provide a number of options which may be relevant. One that may be of particular importance to some would be the designation of the character set (e.g. UTF-8) to be used for text fields.

You will also need at this time to specify permissions for various users to perform different operations on the database. In most systems, the user who creates the database will be automatically assigned all permissions, but this is an area in which the documentation should be consulted.

For your first usage of the database, and occasionally at other times when the database layout changes, you will need permission to create and alter tables (see next paragraph). For normal running, you will need to be able to read and write records; these are normally defined in SQL as SELECT, INSERT, UPDATE and DELETE permissions.

On your first usage, KMyMoney will attempt to create the necessary table structures. In order to achieve the maximum compatibility between various database types, only a subset of common data types are used. There may nevertheless be situations where a particular type is not supported, and in this case, provision has been made to generate the SQL code necessary to create tables. This code can then be modified as required and used to create the tables outside of KMyMoney. Should you find yourself in this situation, help can usually be obtained from <kmymoney2-developer@lists.sourceforge.net>.

This box lists all Qt SQL drivers installed on your system. Select the driver for your database type. If the one you want is not in the list, you need to install the appropriate driver. See your distro documentation, or visit the Trolltech web site and search for 'SQL drivers'.

The default database name is KMyMoney, but you may choose some other name if you like. SQLite has one database per file; selecting this driver opens the file dialog. For database types other than MySQL, the database name must be pre-created, though KMyMoney will create all table structures where necessary.

For the average user, the default name of localhost, being the machine you are currently using, is correct. For networked databases, enter the connected host name.

Check the permissions set up on your database, or contact the database administrator, for the correct values to use here. The user name must be capable of selecting, deleting, inserting and updating records. If the user name is the same as your login name, a password is not normally required.

This button will generate the CREATE TABLE commands to a text file, which may be edited if the in-built commands do not work for your database system (see previous paragraph)..

To access your data in KMyMoney, use the Open Database entry in the File menu. This will open a dialog similar to the above.

To access your data in other formats, you will need to know a little about how it is held in relational databases. By far the easiest way to get a feel for this is to open the database in a front-end such as OpenOffice.org. This provides a list of the various tables which go to make up the database, and enables you to see the layout of each of them.

To extract data, e.g. into a spreadsheet or external file, it is almost invariably necessary to select linked data from one or more tables. This is done by 'joining' the tables, using a field which is common to each. You can find a lot more information about how this is done from the online tutorials mentioned above. The following table lists the fields used to define these inter-table relationships.

Notes:-

1 – txType = “N” for normal transactions, “S” for scheduled transactions

2 – if kmmAccounts.isStockAccount = “Y”

Several of the data fields are held in an internal format which may not be immediately useful to external programs. In these cases, the information has been duplicated in both internal and external formats.

Monetary amounts and share values are shown both in numerator/denominator format, and, with a field name suffixed with 'Formatted', in the form as shown on your screens.

Similarly, some fields such as account type appear both as a numeric code, and in a field suffixed 'String' in the form and language of the application.

Having data in an industry standard format does give you the ability to modify it outside the KMyMoney application. DON'T DO IT unless you really know what you're doing, and always be certain to make a backup copy of your data first. If you get it wrong, KMyMoney may not be able to access your data, and you could even end up losing it altogether. You have been warned!

Most database systems allow you to store commonly used queries and procedures, and in some cases, these may be held as tables within your database itself. As you will have guessed from the above, all the tables used by KMyMoney begin with the lower-case letters 'kmm'. This standard will be maintained, and only tables beginning with these letters will be updated. Thus provided you avoid these in the naming of your queries etc., you should not experience any problems.

If your table is of medium or large size, you can see considerable improvements in loading and storing times by creating an index on the Splits table. This is done from the command line as follows:

tonyb:~> mysql KMyMoney # or whatever your database is called
...
mysql> CREATE INDEX tx on kmmSplits (transactionId, splitId);
...
mysql> quit
Bye
tonyb@CROW:~>

To be supplied