summaryrefslogtreecommitdiffstats
path: root/doc/en/details-database.docbook
diff options
context:
space:
mode:
authortpearson <tpearson@283d02a7-25f6-0310-bc7c-ecb5cbfe19da>2011-07-04 22:38:03 +0000
committertpearson <tpearson@283d02a7-25f6-0310-bc7c-ecb5cbfe19da>2011-07-04 22:38:03 +0000
commitdadc34655c3ab961b0b0b94a10eaaba710f0b5e8 (patch)
tree99e72842fe687baea16376a147619b6048d7e441 /doc/en/details-database.docbook
downloadkmymoney-dadc34655c3ab961b0b0b94a10eaaba710f0b5e8.tar.gz
kmymoney-dadc34655c3ab961b0b0b94a10eaaba710f0b5e8.zip
Added kmymoney
git-svn-id: svn://anonsvn.kde.org/home/kde/branches/trinity/applications/kmymoney@1239792 283d02a7-25f6-0310-bc7c-ecb5cbfe19da
Diffstat (limited to 'doc/en/details-database.docbook')
-rw-r--r--doc/en/details-database.docbook461
1 files changed, 461 insertions, 0 deletions
diff --git a/doc/en/details-database.docbook b/doc/en/details-database.docbook
new file mode 100644
index 0000000..7c87293
--- /dev/null
+++ b/doc/en/details-database.docbook
@@ -0,0 +1,461 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<chapter id="details.database">
+<chapterinfo>
+ <authorgroup>
+ <author>
+ <firstname>Tony</firstname>
+ <surname>Bloomfield</surname>
+ <affiliation>
+ <address><email></email></address>
+ </affiliation>
+ </author>
+ </authorgroup>
+ <date>2009-08-05</date>
+ <releaseinfo>1.0</releaseinfo>
+</chapterinfo>
+<title>Database</title>
+
+<sect1 id="details.database.usage">
+<title>Caution when using the database</title>
+
+<caution>
+ <para>
+ At the time of writing, the software described in this chapter is still in
+ an experimental stage, and loss of data is possible. Please ensure that you
+ have an alternative backup copy of your data before committing to database
+ usage.
+ </para>
+</caution>
+
+<sect2>
+<title>Introduction</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"/>, 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>Preparation</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"/>, SQLite, and PostgreSQL. The module also
+ supports the 'heavier', more industrial, systems such as Oracle<trademark
+ class="registered"/> and IBM DB2<trademark class="registered"/>.
+</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.nokia.com">&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 to install. Also, some of the following information,
+ particularly that related to administration, may not apply to SQLite.
+ </para>
+
+ <para>
+ Due to limitations in prior releases, only version 3 of SQLite is
+ supported. Unfortunately, the release of the &Qt; toolkit used by &kappname;
+ does not provide a driver for this version. However, an unofficial,
+ extensively tested, driver is available; if you wish to use SQLite, ask on
+ &devlist; for further information.
+ </para>
+</note>
+</sect2>
+
+<sect2>
+<title>Administration</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>Creating the database</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 (e.g., 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>Creating Tables</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;.
+</para>
+</sect3>
+</sect2>
+
+<sect2 id="details.database.selectdatabase">
+<title>Initialisation</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>
+
+<sect3>
+<title>Database Type</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.nokia.com/" type="">&Qt; software web
+ site</ulink> and search for 'SQL drivers'.
+</para>
+</sect3>
+
+<sect3>
+<title>Database name</title>
+<para>
+ 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 will open
+ the standard 'Open File' dialog from which a file may be selected or
+ created. 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 create all table structures where
+ necessary.
+</para>
+</sect3>
+
+<sect3><title>Host name</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>
+
+<note>
+ <para>
+ NOTE: For SQLite databases, the Host name, User name, and Password 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>
+</note>
+</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>
+
+<sect3>
+<title>Generate SQL</title>
+<para>
+ This button will generate the CREATE TABLE commands and save them to a text
+ file, which may be edited if the in-built commands do not work for your
+ database system (see previous paragraph).
+</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>
+
+<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, e.g., 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">
+ <colspec colname="c1" colwidth="4.5cm"/>
+ <colspec colname="c2" colwidth="6.2cm"/>
+ <colspec colname="c3" colwidth="6.3cm"/>
+ <thead>
+ <row>
+ <entry valign="top">
+ <para>Relationship</para>
+ </entry>
+ <entry valign="top">
+ <para>Match</para>
+ </entry>
+ <entry valign="top">
+ <para>With</para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry valign="top">
+ <para>Institutions and Accounts</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmInstitutions.id</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmAccounts.institutionId</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Accounts Parent/Child</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmAccounts.id</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmAccounts.parentId</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Transactions and Splits (see 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>Accounts and Splits</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmAccounts.id</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmSplits.accountId</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Payees and Splits</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmPayees.id</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmSplits.payeeId</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Schedules and Transactions</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmSchedules.id</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmTransactions.id</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Transactions and Currencies</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmTransactions.currencyId</para>
+ </entry>
+ <entry valign="top">
+ <para>kmmCurrencies.ISOCode</para>
+ </entry>
+ </row>
+ <row>
+ <entry valign="top">
+ <para>Accounts and Securities (see 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>Securities and Prices</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>Currency Rates</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>
+ Notes:
+</para>
+
+<para>
+ 1 &#x2013; txType = &#x201C;N&#x201D; for normal transactions,
+ &#x201C;S&#x201D; for scheduled transactions
+</para>
+
+<para>
+2 &#x2013; if kmmAccounts.isStockAccount = &#x201C;Y&#x201D;
+</para>
+</sect3>
+
+<sect3>
+<title>Field formats</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>
+<title>Encryption</title>
+
+<para>
+ Encryption of data in your database is not currently supported.
+</para>
+</sect2>
+</sect1>
+</chapter>