Warning, /office/kmymoney/doc/details-database.docbook is written in an unsupported language. File is not indexed.

0001 <?xml version="1.0" encoding="UTF-8"?>
0002 <chapter id="details.database">
0003 <chapterinfo>
0004   <authorgroup>
0005     <author>&Tony.Bloomfield; &Tony.Bloomfield.mail;</author>
0006   </authorgroup>
0007   <date>2011-07-21</date>
0008   <releaseinfo>4.6</releaseinfo>
0009 </chapterinfo>
0011 <title>Database</title>
0013 <sect1 id="details.database.usage">
0014 <title>Using relational databases</title>
0016 <sect2>
0017 <title>Introduction</title>
0018 <para>
0019   As of release 1.0, &kmymoney; allows you to hold your data in a relational
0020   database. One of the advantages of using this industry-standard format is that
0021   it may allow you to view your data using one of the graphic front ends such as
0022   <trademark>OpenOffice.org</trademark> or <trademark>LibreOffice</trademark>,
0023   perhaps in some format that &kmymoney; currently doesn't
0024   provide. Also, a little knowledge of SQL (Structured Query Language, the
0025   language used world-wide to access relational databases) should enable you
0026   more easily to export data to an external program, for example, a budgeting
0027   application.
0028 </para>
0029 <para/>
0030 </sect2>
0032 <sect2><title>Preparation</title>
0034 <para>
0035   To access the database, &kmymoney; uses the SQL module supplied by &Qt;
0036   Software as part of their &Qt; programming system. This module supports a
0037   number of different database systems through a collection of drivers. Among
0038   the more popular open-source systems for which drivers are available are
0039   <trademark>MySQL</trademark>, SQLite (version 3 and upwards only), and PostgreSQL.
0040   The module also supports the 'heavier', more industrial, systems such as <trademark
0041   class="registered">Oracle</trademark> and IBM <trademark class="registered">DB2</trademark>.
0042 </para>
0044 <para>
0045   With the exception of SQLite, these systems use a client/server model, where
0046   the 'client' software sits on 'your' machine, while the server lives on the
0047   same machine as the database itself, which may be elsewhere on a network. Of
0048   course, in the normal scenario for a personal finance application such as
0049   &kmymoney;, 'your' machine acts as both client and server. Your first task
0050   therefore, having decided which database system you wish to use, is to install
0051   the client, and most probably server, software.
0052 </para>
0054 <para>
0055   In addition to the database software itself, you must also install the
0056   corresponding &Qt; driver module. Most distributions will include driver
0057   modules for the more popular databases. Otherwise, check with the <ulink
0058   url="https://www.qt.io/">&Qt; software web site</ulink> and search for
0059   'SQL drivers'.
0060 </para>
0062 <note>
0063   <para>
0064     SQLite does not operate on a client/server model; each database is held in a
0065     regular file, local or remote, accessed using the normal methods supplied by
0066     the underlying operating system. In this case, therefore, there is only one
0067     software package and the driver to install. Also, some of the following information,
0068     particularly that related to administration, may not apply to SQLite.
0069   </para>
0070 </note>
0071 </sect2>
0073 <sect2>
0074 <title>Administration</title>
0076 <para>
0077   Looking after databases is a little more complex than dealing with regular
0078   files. Each system has different methods for performing those necessary
0079   administrative tasks such as creating databases, assigning permissions to
0080   various users, producing backups, &etc; Describing these tasks is outside the
0081   scope of this manual, but all of the supported products provide comprehensive
0082   reference documentation, and a quick search of the web will point you at many
0083   tutorials on the subject.
0084 </para>
0086 <sect3>
0087 <title>Creating the database</title>
0089 <para>
0090   Code has been included to create an initial database to hold your data if one
0091   doesn't exist. However, it is strongly recommended that you pre-create a
0092   database, because most of the products provide a number of options which may
0093   be relevant. One that may be of particular importance to some would be the
0094   designation of the character set (&eg;, UTF-8) to be used for text
0095   fields.
0096 </para>
0098 <para>
0099   At this time, you will also need to specify permissions for various users to
0100   perform different operations on the database. In most systems, the user who
0101   creates the database will be automatically assigned all permissions, but this
0102   is an area in which the documentation should be consulted.
0103 </para>
0105 <para>
0106   For your first use of the database, and occasionally at other times when the
0107   database layout changes, you will need permission (also called privileges) to
0108   create and alter tables and views (see next paragraph). There may be different
0109   names for the permission/privilege in different systems, but something like
0110   <literal>CREATE</literal> and <literal>ALTER</literal> should be commonplace.
0111   For normal running, you will need to be able to read and write records; these
0112   are normally defined in SQL as <literal>SELECT</literal>, <literal>INSERT</literal>,
0113   <literal>UPDATE</literal>, and <literal>DELETE</literal> permissions.
0114 </para>
0115 </sect3>
0117 <sect3>
0118 <title>Creating Tables</title>
0120 <para>
0121   On your first use, &kmymoney; will attempt to create the necessary table
0122   structures. In order to achieve the maximum compatibility between various
0123   database types, only a subset of common data types are used. There may
0124   nevertheless be situations where a particular type is not supported, and in
0125   this case, provision has been made to generate the SQL code necessary to
0126   create tables. This code can then be modified as required and used to create
0127   the tables outside of &kmymoney;. Should you find yourself in this situation,
0128   help can usually be obtained from &devlist;. See <link
0129   linkend="details.database.generatesql">Manual Database Creation</link> for more information.
0130 </para>
0131 </sect3>
0132 </sect2>
0134 <sect2 id="details.database.selectdatabase">
0135 <title>Creating a Database</title>
0137 <para>
0138   Using &kmymoney;, open or import an existing data file, or create a new
0139   one. Then select <menuchoice><guimenu>File</guimenu><guimenuitem>Save as Database</guimenuitem></menuchoice>
0140   menu item. This will present the following dialog:
0141 </para>
0143 <screenshot>
0144   <mediaobject>
0145   <imageobject>
0146   <imagedata fileref="select_database.png" format="PNG" />
0147   </imageobject>
0148   </mediaobject>
0149 </screenshot>
0151 <para>
0152   Complete the fields appropriate to the database type you have selected
0153   (as usual, mandatory fields will be highlighted)
0154   and click <guibutton>OK</guibutton> to create the database.
0155 </para>
0157 <sect3>
0158 <title>Database Type</title>
0160 <para>
0161   This box lists all &Qt; SQL drivers installed on your system. Select the
0162   driver for your database type. If the one you want is not in the list, you
0163   need to install the appropriate driver. See your distribution documentation,
0164   or visit the <ulink url="https://www.qt.io/">&Qt; software web site</ulink>
0165   and search for 'SQL drivers'.
0166 </para>
0167 </sect3>
0169 <sect3>
0170 <title>File (SQLite only)</title>
0171 <para>
0172   SQLite has one database per file so enter the filename in which you wish to
0173   create the database.  To browse the file system, click the icon to the right
0174   of the filename.  For SQLite databases, the <guilabel>Host name</guilabel>,
0175   <guilabel>Username</guilabel>, and <guilabel>Password</guilabel> fields are
0176   not relevant. The SQLite file must have the appropriate read/write permissions
0177   set by the underlying file system to enable the appropriate access for the
0178   currently logged-in user.
0179 </para>
0180 </sect3>
0182 <sect3>
0183 <title>Database name (others)</title>
0184 <para>
0185   The default database name is <literal>KMyMoney</literal>, but you may choose some other
0186   name if you like. For some database types, &kmymoney; may not be able to create the
0187   database, so it must be pre-created using the appropriate administrative procedure.
0188   However, &kmymoney; will usually be able to create all table structures where necessary.
0189   If not, you can create them yourself. See <link linkend="details.database.generatesql">Manual
0190   Database Creation</link> for more information.
0191 </para>
0192 </sect3>
0194 <sect3><title>Host name</title>
0195 <para>
0196   For the average user, the default name of <quote>localhost</quote>, being the
0197   machine you are currently using, is correct. For networked databases, enter
0198   the connected host name.
0199 </para>
0201 </sect3>
0203 <sect3>
0204 <title>User name and password</title>
0205 <para>
0206   Check the permissions set up on your database, or contact the database
0207   administrator, for the correct values to use here. The user name must be
0208   capable of selecting, inserting, updating, and deleting records. If the user
0209   name is the same as your login name, a password is not normally required.
0210 </para>
0211 </sect3>
0212 </sect2>
0214 <sect2>
0215 <title>Accessing your data</title>
0217 <sect3>
0218 <title>Table design</title>
0220 <para>
0221   To access your data in &kmymoney;, use the <menuchoice><guimenu>File</guimenu><guimenuitem>Open
0222   Database</guimenuitem></menuchoice> menu item. This will
0223   open a dialog similar to the above.
0224 </para>
0226 <note>
0227 <para>
0228   If you created your database by first opening a file, and then doing
0229   <guimenuitem>Save as Database</guimenuitem>, as described above, then any
0230   subsequent changes to your data are saved only in the database, not in the
0231   file.  This means that you can use the file as a backup or snapshot of your
0232   data at a particular time.  To make a new backup like this, open your
0233   database, choose the <menuchoice><guimenu>File</guimenu><guimenuitem>Save
0234   As...</guimenuitem></menuchoice> menu item, giving an appropriate file name.  Remember
0235   to re-open your database, so &kmymoney; does not continue to use the file.
0236 </para>
0237 </note>
0239 <para>
0240   To access your data in other formats, you will need to know a little about how
0241   it is held in relational databases. By far the easiest way to get a feel for
0242   this is to open the database in a front-end such as <trademark>OpenOffice.org</trademark>.
0243   This provides a list of the various tables which make up the database, and
0244   enables you to see the layout of each of them.
0245 </para>
0247 <para>
0248   To extract data, &eg;, into a spreadsheet or external file, it is almost
0249   invariably necessary to select linked data from more than one table. This is
0250   done by 'joining' the tables, using a field which is common to each. You can
0251   find a lot more information about how this is done from the online database
0252   tutorials mentioned above. The following table lists the fields used to define
0253   these inter-table relationships.
0254 </para>
0256 <informaltable>
0257   <tgroup cols="3">
0258     <thead>
0259       <row>
0260         <entry valign="top">
0261           <para>Relationship</para>
0262         </entry>
0263         <entry valign="top">
0264           <para>Match</para>
0265         </entry>
0266         <entry valign="top">
0267           <para>With</para>
0268         </entry>
0269       </row>
0270     </thead>
0271     <tbody>
0272       <row>
0273         <entry valign="top">
0274           <para>Institutions and Accounts</para>
0275         </entry>
0276         <entry valign="top">
0277           <para><literal>kmmInstitutions.id</literal></para>
0278         </entry>
0279         <entry valign="top">
0280           <para><literal>kmmAccounts.institutionId</literal></para>
0281         </entry>
0282       </row>
0283       <row>
0284         <entry valign="top">
0285           <para>Accounts Parent/Child</para>
0286         </entry>
0287         <entry valign="top">
0288           <para><literal>kmmAccounts.id</literal></para>
0289         </entry>
0290         <entry valign="top">
0291           <para><literal>kmmAccounts.parentId</literal></para>
0292         </entry>
0293       </row>
0294       <row>
0295         <entry valign="top">
0296           <para>Transactions and Splits (see Note 1)</para>
0297         </entry>
0298         <entry valign="top">
0299           <para><literal>kmmTransactions.id</literal></para>
0300         </entry>
0301         <entry valign="top">
0302           <para><literal>kmmSplits.transactionId</literal></para>
0303         </entry>
0304       </row>
0305       <row>
0306         <entry valign="top">
0307           <para>Accounts and Splits</para>
0308         </entry>
0309         <entry valign="top">
0310           <para><literal>kmmAccounts.id</literal></para>
0311         </entry>
0312         <entry valign="top">
0313           <para><literal>kmmSplits.accountId</literal></para>
0314         </entry>
0315       </row>
0316       <row>
0317         <entry valign="top">
0318           <para>Payees and Splits</para>
0319         </entry>
0320         <entry valign="top">
0321           <para><literal>kmmPayees.id</literal></para>
0322         </entry>
0323         <entry valign="top">
0324           <para><literal>kmmSplits.payeeId</literal></para>
0325         </entry>
0326       </row>
0327       <row>
0328         <entry valign="top">
0329           <para>Schedules and Transactions</para>
0330         </entry>
0331         <entry valign="top">
0332           <para><literal>kmmSchedules.id</literal></para>
0333         </entry>
0334         <entry valign="top">
0335           <para><literal>kmmTransactions.id</literal></para>
0336         </entry>
0337       </row>
0338       <row>
0339         <entry valign="top">
0340           <para>Transactions and Currencies</para>
0341         </entry>
0342         <entry valign="top">
0343           <para><literal>kmmTransactions.currencyId</literal></para>
0344         </entry>
0345         <entry valign="top">
0346           <para><literal>kmmCurrencies.ISOCode</literal></para>
0347         </entry>
0348       </row>
0349       <row>
0350         <entry valign="top">
0351           <para><literal>Accounts and Securities (see Note 2)</literal></para>
0352         </entry>
0353         <entry valign="top">
0354           <para><literal>kmmAccounts.currencyId</literal></para>
0355         </entry>
0356         <entry valign="top">
0357           <para><literal>kmmSecurities.id</literal></para>
0358         </entry>
0359       </row>
0360       <row>
0361         <entry valign="top">
0362           <para>Securities and Prices</para>
0363         </entry>
0364         <entry valign="top">
0365           <para><literal>kmmSecurities.id</literal></para>
0366         </entry>
0367         <entry valign="top">
0368           <para><literal>kmmPrices.fromId</literal> or <literal>kmmPrices.toId</literal></para>
0369         </entry>
0370       </row>
0371       <row>
0372         <entry valign="top">
0373           <para>Currency Rates</para>
0374         </entry>
0375         <entry valign="top">
0376           <para><literal>kmmCurrencies.ISOCode</literal></para>
0377         </entry>
0378         <entry valign="top">
0379           <para><literal>kmmPrices.fromId</literal> or <literal>kmmPrices.toId</literal></para>
0380         </entry>
0381       </row>
0382     </tbody>
0383   </tgroup>
0384 </informaltable>
0386 <para>
0387   Notes:
0388 </para>
0390 <para>
0391   1 &#x2013; txType = &#x201C;N&#x201D; for normal transactions,
0392   &#x201C;S&#x201D; for scheduled transactions
0393 </para>
0395 <para>
0396 2 &#x2013; if kmmAccounts.isStockAccount = &#x201C;Y&#x201D;
0397 </para>
0398 </sect3>
0400 <sect3>
0401 <title>Field formats</title>
0403 <para>
0404   Several of the data fields are held in an internal format which may not be
0405   immediately useful to external programs. In these cases, the information has
0406   been duplicated in both internal and external formats.
0407 </para>
0409 <para>
0410   Monetary amounts and share values are shown both in numerator/denominator
0411   format, and, with a field name suffixed with '<literal>Formatted</literal>',
0412   in the form as shown on your screens.
0413 </para>
0415 <para>
0416   Similarly, some fields, such as account type appear both as a numeric code,
0417   and in a field suffixed '<literal>String</literal>' in the form and language
0418   of the application.
0419 </para>
0420 </sect3>
0422 <sect3>
0423 <title>Updating your data</title>
0425 <para>
0426   Having data in an industry standard format does give you the ability to modify
0427   it outside the &kmymoney; application. DO NOT DO IT unless you really know
0428   what you are doing, and always be certain to make a backup copy of your data
0429   first. If you get it wrong, &kmymoney; may not be able to access your data,
0430   and you could even end up losing it altogether. You have been warned!
0431 </para>
0432 </sect3>
0434 <sect3>
0435 <title>Stored queries</title>
0437 <para>
0438   Most database systems allow you to store commonly used queries and procedures,
0439   and in some cases, these may be held as tables or other objects within your
0440   database itself. As you will have guessed from the above, all the tables used
0441   by &kmymoney; begin with the lowercase letters '<literal>kmm</literal>'. This
0442   standard will be maintained, and only tables beginning with these letters will
0443   be updated. Thus, provided you avoid these in the naming of your queries &etc;,
0444   you should not experience any problems.
0445 </para>
0446 </sect3>
0447 </sect2>
0449 <sect2 id="details.database.generatesql">
0450 <title>Manual database creation</title>
0451 <note>
0452   <para>
0453     This section covers more advanced database usage and may be skipped by the general user.
0454   </para>
0455 </note>
0457 <sect3>
0458 <title>When to use</title>
0459 <para>
0460   There may be occasions when &kmymoney; is unable to create the database automatically, or creates it without some
0461   options required by the user. For example, the database system used may not completely conform
0462   to standard SQL usage, or support may be introduced for new systems which have not been fully tested in &kmymoney;.
0463 </para>
0464 <para>
0465   Prior to using this facility, you should try just creating the database
0466   entry itself (&ie; with the <literal>CREATE DATABASE</literal> statement).
0467   Provided the database exists, &kmymoney; may well be able to create the
0468   tables, &etc; in the normal database save procedure described above.
0469 </para>
0470 </sect3>
0472 <sect3>
0473 <title>Generating the SQL</title>
0474 <para>
0475   If this fails, it is possible to generate the basic SQL commands needed to
0476   create the various tables, views and indexes required by the application.
0477   Select the <menuchoice><guimenu>Tools</guimenu><guimenuitem>Generate Database
0478   SQL</guimenuitem></menuchoice> menu item. This will present the following dialog:
0479 </para>
0480 <screenshot>
0481   <mediaobject>
0482   <imageobject>
0483   <imagedata fileref="generate_sql.png" format="PNG" />
0484   </imageobject>
0485   </mediaobject>
0486 </screenshot>
0488 <para>
0489   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,
0490   or saved to a text file by clicking <guibutton>Save SQL</guibutton>. In the latter case, the database
0491   must be created using the administrative functions provided by the database system.
0492 </para>
0493 <para>
0494   If after editing the text in the dialog, you want &kmymoney; to create the database,
0495   you will need to complete the other fields in the dialog, as detailed in <link
0496   linkend="details.database.selectdatabase">Creating a Database</link> above,
0497   and click <guibutton>Create Tables</guibutton>. Note that, except in the case of
0498   SQLite, you will need either to include a suitable <literal>CREATE DATABASE</literal> statement
0499   as the first command, or have previously issued such a command externally to &kmymoney;.
0500 </para>
0501 </sect3>
0503 <sect3>
0504 <title>Warning</title>
0505 <para>
0506   You should be very careful editing the definitions of any of the basic tables or views
0507   (those with names beginning with '<literal>kmm</literal>'). Some changes,
0508   such as increasing the length of an integer field, may have little impact, but you should not
0509   remove or change the sequence of any fields, or &kmymoney; may refuse to function, or may corrupt your data.
0510 </para>
0511 <para>
0512   Whilst adding or removing indexes may improve performance, you should also be aware that the opposite
0513   may happen. Some knowledge of the internal operation of &kmymoney; may help to get the
0514   best performance in these circumstances.
0515 </para>
0516 </sect3>
0518 </sect2>
0520 <sect2>
0521 <title>Encryption</title>
0523 <para>
0524   Encryption of data in your database is not currently supported.
0525 </para>
0526 </sect2>
0527 </sect1>
0528 </chapter>