<?xml version="1.0" encoding="UTF-8"?>
<chapter id="details.database">
<chapterinfo>
  <authorgroup>
    <author
>&Tony.Bloomfield; &Tony.Bloomfield.mail;</author>
  </authorgroup>
  <date
>2011-07-21</date>
  <releaseinfo
>4.6</releaseinfo>
</chapterinfo>

<title
>Database</title>

<sect1 id="details.database.usage">
<title
>Gebruik van relationele databases</title>

<sect2>
<title
>Inleiding</title>
<para
>As of release 1.0, &kappname; allows you to hold your data in a relational database. One of the advantages of using this industry-standard format is that it may allow you to view your data using one of the graphic front ends such as OpenOffice.org<trademark class="copyright"
></trademark
> or LibreOffice<trademark class="copyright"
></trademark
>, perhaps in some format that &kappname; currently doesn't provide. Also, a little knowledge of SQL (Structured Query Language, the language used world-wide to access relational databases) should enable you more easily to export data to an external program, for example, a budgeting application. </para>
<para/>
</sect2>

<sect2
><title
>Voorbereiding</title>

<para
>To access the database, &kappname; uses the SQL module supplied by &Qt; Software as part of their &Qt; programming system. This module supports a number of different database systems through a collection of drivers. Among the more popular open-source systems for which drivers are available are MySQL<trademark class="registered"
></trademark
>, SQLite (version 3 and upwards only), and PostgreSQL. The module also supports the 'heavier', more industrial, systems such as Oracle<trademark class="registered"
></trademark
> and IBM DB2<trademark class="registered"
></trademark
>. </para>

<para
>With the exception of SQLite, these systems use a client/server model, where the 'client' software sits on 'your' machine, while the server lives on the same machine as the database itself, which may be elsewhere on a network. Of course, in the normal scenario for a personal finance application such as &kappname;, 'your' machine acts as both client and server. Your first task therefore, having decided which database system you wish to use, is to install the client, and most probably server, software. </para>

<para
>In addition to the database software itself, you must also install the corresponding &Qt; driver module. Most distributions will include driver modules for the more popular databases. Otherwise, check with the <ulink url="http://qt-project.org"
>&Qt; software web site</ulink
> and search for 'SQL drivers' </para>

<note>
  <para
>SQLite does not operate on a client/server model; each database is held in a regular file, local or remote, accessed using the normal methods supplied by the underlying operating system. In this case, therefore, there is only one software package and the driver to install. Also, some of the following information, particularly that related to administration, may not apply to SQLite. </para>
</note>
</sect2>

<sect2>
<title
>Administratie</title>

<para
>Looking after databases is a little more complex than dealing with regular files. Each system has different methods for performing those necessary administrative tasks such as creating databases, assigning permissions to various users, producing backups, &etc; Describing these tasks is outside the scope of this manual, but all of the supported products provide comprehensive reference documentation, and a quick search of the web will point you at many tutorials on the subject. </para>

<sect3>
<title
>Een database aanmaken</title>

<para
>Code has been included to create an initial database to hold your data if one doesn't exist. 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 (&eg;, UTF-8) to be used for text fields. </para>

<para
>At this time, you will also need 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. </para>

<para
>For your first use of the database, and occasionally at other times when the database layout changes, you will need permission (also called privileges) to create and alter tables and views (see next paragraph). There may be different names for the permission/privilege in different systems, but something like CREATE and ALTER should be commonplace. 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. </para>
</sect3>

<sect3>
<title
>Tabellen aanmaken</title>

<para
>On your first use, &kappname; 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 &kappname;. Should you find yourself in this situation, help can usually be obtained from &devlist;. See <link linkend="details.database.generatesql"
>Manual Database Creation</link
> for more information. </para>
</sect3>
</sect2>

<sect2 id="details.database.selectdatabase">
<title
>Een database aanmaken</title>

<para
>Using &kappname;, open or import an existing data file, or create a new one. Then select <guimenuitem
>Save as Database</guimenuitem
> from the <guimenu
>File</guimenu
> menu. This will present the following dialog: </para>

<screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="select_database.png" format="PNG"/>
  </imageobject>
  </mediaobject>
</screenshot>

<para
>Complete the fields appropriate to the database type you have selected (as usual, mandatory fields will be highlighted) and click <guibutton
>OK</guibutton
> to create the database. </para>

<sect3>
<title
>Type database</title>

<para
>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 distribution documentation, or visit the <ulink url="http://qt-project.org/" type=""
>&Qt; software web site</ulink
> and search for 'SQL drivers'. </para>
</sect3>

<sect3>
<title
>File (SQLite only)</title>
<para
>SQLite has one database per file so enter the filename in which you wish to create the database. To browse the file system, click the icon to the right of the filename. For SQLite databases, the <guilabel
>Host name</guilabel
>, <guilabel
>Username</guilabel
>, and <guilabel
>Password</guilabel
> fields are not relevant. The SQLite file must have the appropriate read/write permissions set by the underlying file system to enable the appropriate access for the currently logged-in user. </para>
</sect3>

<sect3>
<title
>Database name (others)</title>
<para
>The default database name is KMyMoney, but you may choose some other name if you like. For some database types, &kappname; may not be able to create the database, so it must be pre-created using the appropriate administrative procedure. However, &kappname; will usually be able to create all table structures where necessary. If not, you can create them yourself. See <link linkend="details.database.generatesql"
>Manual Database Creation</link
> for more information. </para>
</sect3>

<sect3
><title
>Hostnaam</title>
<para
>For the average user, the default name of <quote
>localhost</quote
>, being the machine you are currently using, is correct. For networked databases, enter the connected host name. </para>

</sect3>

<sect3>
<title
>User name and password</title>
<para
>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, inserting, updating, and deleting records. If the user name is the same as your login name, a password is not normally required. </para>
</sect3>
</sect2>

<sect2>
<title
>Accessing your data</title>

<sect3>
<title
>Table design</title>

<para
>To access your data in &kappname;, use the <guimenuitem
>Open Database</guimenuitem
> entry in the <guimenu
>File</guimenu
> menu. This will open a dialog similar to the above. </para>

<note>
<para
>If you created your database by first opening a file, and then doing <guimenuitem
>Save as Database</guimenuitem
>, as described above, then any subsequent changes to your data are saved only in the database, not in the file. This means that you can use the file as a backup or snapshot of your data at a particular time. To make a new backup like this, open your database, do <menuchoice
><guimenu
>File</guimenu
><guimenuitem
>Save As...</guimenuitem
></menuchoice
>, giving an appropriate file name. Remember to re-open your database, so &kappname; does not continue to use the file. </para>
</note>

<para
>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 make up the database, and enables you to see the layout of each of them. </para>

<para
>To extract data, &eg;, into a spreadsheet or external file, it is almost invariably necessary to select linked data from more than one table. 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 database tutorials mentioned above. The following table lists the fields used to define these inter-table relationships. </para>

<informaltable>
  <tgroup cols="3">
    <thead>
      <row>
        <entry valign="top">
          <para
>Relatie</para>
        </entry>
        <entry valign="top">
          <para
>Komt overeen</para>
        </entry>
        <entry valign="top">
          <para
>Met</para>
        </entry>
      </row>
    </thead>
    <tbody>
      <row>
        <entry valign="top">
          <para
>Banken en rekeningen</para>
        </entry>
        <entry valign="top">
          <para
>kmmInstitutions.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmAccounts.institutionId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Rekeningen moeder/dochter</para>
        </entry>
        <entry valign="top">
          <para
>kmmAccounts.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmAccounts.parentId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Transacties en splitsingen (zie Note 1)</para>
        </entry>
        <entry valign="top">
          <para
>kmmTransactions.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmSplits.transactionId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Rekeningen en splitsingen</para>
        </entry>
        <entry valign="top">
          <para
>kmmAccounts.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmSplits.accountId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Begunstigden en splitsingen</para>
        </entry>
        <entry valign="top">
          <para
>kmmPayees.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmSplits.payeeId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Periodieken en transacties</para>
        </entry>
        <entry valign="top">
          <para
>kmmSchedules.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmTransactions.id</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Transacties en valuta</para>
        </entry>
        <entry valign="top">
          <para
>kmmTransactions.currencyId</para>
        </entry>
        <entry valign="top">
          <para
>kmmCurrencies.ISOCode</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Rekeningen en waardepapieren (zie Note 2)</para>
        </entry>
        <entry valign="top">
          <para
>kmmAccounts.currencyId</para>
        </entry>
        <entry valign="top">
          <para
>kmmSecurities.id</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Waardepapieren en prijzen</para>
        </entry>
        <entry valign="top">
          <para
>kmmSecurities.id</para>
        </entry>
        <entry valign="top">
          <para
>kmmPrices.fromId or kmmPrices.toId</para>
        </entry>
      </row>
      <row>
        <entry valign="top">
          <para
>Koersen</para>
        </entry>
        <entry valign="top">
          <para
>kmmCurrencies.ISOCode</para>
        </entry>
        <entry valign="top">
          <para
>kmmPrices.fromId or kmmPrices.toId</para>
        </entry>
      </row>
    </tbody>
  </tgroup>
</informaltable>

<para
>Notities: </para>

<para
>1 &#x2013; txType = &#x201C;N&#x201D; voor normale transacties, &#x201C;S&#x201D; voor periodieke transacties </para>

<para
>2 &#x2013; if kmmAccounts.isStockAccount = &#x201C;Y&#x201D; </para>
</sect3>

<sect3>
<title
>Veld-indelingen</title>

<para
>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. </para>

<para
>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. </para>
  
<para
>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. </para>
</sect3>

<sect3>
<title
>Updating your data</title>

<para
>Having data in an industry standard format does give you the ability to modify it outside the &kappname; application. DO NOT DO IT unless you really know what you are doing, and always be certain to make a backup copy of your data first. If you get it wrong, &kappname; may not be able to access your data, and you could even end up losing it altogether. You have been warned! </para>
</sect3>

<sect3>
<title
>Stored queries</title>

<para
>Most database systems allow you to store commonly used queries and procedures, and in some cases, these may be held as tables or other objects within your database itself. As you will have guessed from the above, all the tables used by &kappname; begin with the lowercase 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. </para>
</sect3>
</sect2>

<sect2 id="details.database.generatesql">
<title
>Manual database creation</title>
<note>
  <para
>This section covers more advanced database usage and may be skipped by the general user. </para>
</note>

<sect3>
<title
>When to use</title>
<para
>There may be occasions when &kappname; is unable to create the database automatically, or creates it without some options required by the user. For example, the database system used may not completely conform to standard SQL usage, or support may be introduced for new systems which have not been fully tested in &kappname;. </para>
<para
>Prior to using this facility, you should try just creating the database entry itself (&ie; with the CREATE DATABASE statement). Provided the database exists, &kappname; may well be able to create the tables, &etc; in the normal database save procedure described above. </para>
</sect3>

<sect3>
<title
>Generating the SQL</title>
<para
>If this fails, it is possible to generate the basic SQL commands needed to create the various tables, views and indexes required by the application. Select <guimenuitem
>Generate Database SQL</guimenuitem
> from the <guimenu
>Tools</guimenu
> menu. This will present the following dialog: </para>
<screenshot>
  <mediaobject>
  <imageobject>
  <imagedata fileref="generate_sql.png" format="PNG"/>
  </imageobject>
  </mediaobject>
</screenshot>

<para
>On selecting the database type, the appropriate SQL will appear in the <guilabel
>SQL for creation</guilabel
> text box; this can be edited by the user, or saved to a text file by clicking <guibutton
>Save SQL</guibutton
>. In the latter case, the database must be created using the administrative functions provided by the database system. </para>
<para
>If after editing the text in the dialog, you want &kappname; to create the database, you will need to complete the other fields in the dialog, as detailed in <link linkend="details.database.selectdatabase"
>Creating a Database</link
> above, and click <guibutton
>Create Tables</guibutton
>. Note that, except in the case of SQLite, you will need either to include a suitable CREATE DATABASE statement as the first command, or have previously issued such a command externally to &kappname; </para>
</sect3>

<sect3>
<title
>Waarschuwing</title>
<para
>You should be very careful editing the definitions of any of the basic tables or views (those with names beginning with 'kmm'). Some changes, such as increasing the length of an integer field, may have little impact, but you should not remove or change the sequence of any fields, or &kappname; may refuse to function, or may corrupt your data. </para>
<para
>Whilst adding or removing indexes may improve performance, you should also be aware that the opposite may happen. Some knowledge of the internal operation of &kappname; may help to get the best performance in these circumstances. </para>
</sect3>

</sect2>

<sect2>
<title
>Versleuteling</title>

<para
>Encryption of data in your database is not currently supported. </para>
</sect2>
</sect1>
</chapter>