SQL backend for GnuCash 2
Tony Bloomfield
tonyb.lx at btinternet.com
Thu Oct 26 06:48:45 EDT 2006
On Wednesday 25 October 2006 15:57, Phil Longstaff wrote:
> I'd be happy to look at what you have. It seems to me that there will
> be enough differences that I won't be able to use much. I assume you
> used the Qt SQL classes rather than libgda or libdbi so I can't use much
> (if anything) of your implementation.
Attached a copy of the DDL and a bit of description, mostly usage oriented. I
agree that it's not a realistic goal to create a common database format; I
was only proposing a possible exchange of ideas. If you think that any of the
code could be of use, I'm happy to send it, or you can get it from CVS
(sorry, don't use SVN yet) from Sourceforge via the KMM home page.
You'll note that the design is pretty simplistic. This was a deliberate
decision, in an attempt to make it as compatible as possible across various
RDB implementations, especially 'lite' ones.
For that reason, you won't find any CONSTRAINTS, or INDEXES (savvy users can
always create their own for performance in their particular scenario; also,
the user manual will be used to provide some advice on this).
As Phil says, we use the Qt classes. Sadly, these don't provide any support
for design changes, presumably because of the differences in implementation
by the various RDB providers. This is probably going to make future
extensions to the structure a bit of a problem. I'm hoping that the design of
the KVP table will enable simple extensions to be implemented, but it's
difficult to say without knowing what they'll be! As for larger changes, my
current implementation allows for creating new tables without too much
difficulty, but I'm not sure just how compatible are DDL statements to, for
example, add a column to a table. Is it best to try to do this in code, or to
provide separate 'database upgrade' scripts (which may need to be tailored)
with new releases?
Finally, the present KMM implementation merely treats the database as if it
were a serial file, with no in-place update. I'm working on a 'proper'
implementation as time permits, but if I understand correctly about your QOF
framework, you'll probably have a head start on me.
Good luck.
--
Cheers,
TonyB
-------------- next part --------------
#[1]The KMyMoney Handbook [2]The KMyMoney Handbook [3]Anonymous file
[4]Chapter 21. Reference
Database Usage
[5]Prev
[6]Next
Chapter 20. Database Usage
Tony Bloomfield <tonybloom at users.sourceforge.net>
Revision 0.9 (2005-12-16)
Database usage
Caution
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.
Introduction
As of release 1.0, KMyMoney 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©, perhaps in some format that KMyMoney 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 extract data to feed into some external program, as for
example a budgeting application.
It should be noted that the current release of the program merely treats the
database in the same fashion as an ordinary file, in that it reads all the
data into memory when opened, and writes updated data back when closed. A
more conventional method of database access, i.e. reading and updating data
only as required, is planned for a future release.
Preparation
To access the database, KMyMoney uses the SQL module supplied by Trolltech®
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®,
SQLite and PostgreSQL The module also supports the 'heavier', more
industrial, systems such as Oracle® and IBM DB2®
With the exception of SQLite, these systems use a client/server model, where
the 'client' software sits on 'your' machine, whilst 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
KMyMoney, '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.
(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.)
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
[7]Trolltech web site and search for 'SQL drivers'
Administration
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 initial creation, assigning permissions to
various users, producing backups etc. Describing these 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.
Creating the database
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.
Creating Tables
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 AT
lists.sourceforge.net).
Initial usage
Using KMyMoney, open or import an existing data file, or create a new one.
Then select 'Save as Database' from the File menu. This will present the
following dialog:
Database Type
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 [8]Trolltech web site and search for 'SQL drivers'.
Database name
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.
Host name
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.
User name and password
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.
Generate SQL
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)..
Accessing your data
Table design
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.
Relationship
Match
With
Institutions and Accounts
kmmInstitutions.id
kmmAccounts.institutionId
Accounts Parent/Child
kmmAccounts.id
kmmAccounts.parentId
Transactions and Splits (see Note 1)
kmmTransactions.id
kmmSplits.transactionId
Accounts and Splits
kmmAccounts.id
kmmSplits.accountId
Payees and Splits
kmmPayees.id
kmmSplits.payeeId
Schedules and Transactions
kmmSchedules.id
kmmTransactions.id
Transactions and Currencies
kmmTransactions.currencyId
kmmCurrencies.ISOCode
Accounts and Securities (see Note 2)
kmmAccounts.currencyId
kmmSecurities.id
Securities and Prices
kmmSecurities.id
kmmPrices.fromId or kmmPrices.toId
Currency Rates
kmmCurrencies.ISOCode
kmmPrices.fromId or kmmPrices.toId
Notes:-
1 – txType = “N†for normal transactions, “S†for scheduled transactions
2 – if kmmAccounts.isStockAccount = “Yâ€
Field formats
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.
Updating your data
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!
Stored queries
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.
Optimization
MySQL
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 at CROW:~>
SQLite
To be supplied
Encryption
Encryption of data on your database is not currently supported.
[9]Prev
[10]Next
[11]Home
Anonymous file
Reference
[12]Up
Would you like to make a comment or contribute an update to this page?
Send feedback to the [13]KDE Docs Team
References
1. file://localhost/tmp/index.html
2. file://localhost/tmp/index.html
3. file://localhost/tmp/details.formats.anonymous.html
4. file://localhost/tmp/reference.html
5. file://localhost/tmp/details.formats.anonymous.html
6. file://localhost/tmp/reference.html
7. http://www.trolltech.com/
8. http://www.trolltech.com/
9. file://localhost/tmp/details.formats.anonymous.html
10. file://localhost/tmp/reference.html
11. file://localhost/tmp/index.html
12. file://localhost/tmp/index.html
13. mailto:kde-docs at kdemail.net
-------------- next part --------------
CREATE TABLE kmmAccounts (
id varchar(32) PRIMARY KEY NOT NULL,
institutionId varchar(32),
parentId varchar(32),
lastReconciled datetime,
lastModified datetime,
openingDate date,
accountNumber text,
accountType varchar(16) NOT NULL,
accountTypeString text,
isStockAccount char(1),
accountName text,
description text,
currencyId varchar(32),
balance text,
balanceFormatted text
);
CREATE TABLE kmmCurrencies (
ISOcode char(3) PRIMARY KEY NOT NULL,
name text NOT NULL,
type smallint unsigned,
typeString text,
symbol1 smallint unsigned,
symbol2 smallint unsigned,
symbol3 smallint unsigned,
symbolString varchar(255),
partsPerUnit varchar(24),
smallestCashFraction varchar(24),
smallestAccountFraction varchar(24)
);
CREATE TABLE kmmFileInfo (
version varchar(16),
created date,
lastModified date,
baseCurrency char(3),
institutions bigint unsigned,
accounts bigint unsigned,
payees bigint unsigned,
transactions bigint unsigned,
splits bigint unsigned,
securities bigint unsigned,
prices bigint unsigned,
currencies bigint unsigned,
schedules bigint unsigned,
reports bigint unsigned,
kvps bigint unsigned,
dateRangeStart date,
dateRangeEnd date,
hiInstitutionId bigint unsigned,
hiPayeeId bigint unsigned,
hiAccountId bigint unsigned,
hiTransactionId bigint unsigned,
hiScheduleId bigint unsigned,
hiSecurityId bigint unsigned,
hiReportId bigint unsigned,
encryptData varchar(255),
updateInProgress char(1)
);
CREATE TABLE kmmInstitutions (
id varchar(32) PRIMARY KEY NOT NULL,
name text NOT NULL,
manager text,
routingCode text,
addressStreet text,
addressCity text,
addressZipcode text,
telephone text
);
CREATE TABLE kmmKeyValuePairs (
kvpType varchar(16) NOT NULL,
kvpId varchar(32) NOT NULL,
kvpKey varchar(255) NOT NULL,
kvpData text
);
CREATE TABLE kmmPayees (
id varchar(32) PRIMARY KEY NOT NULL,
name text,
reference text,
email text,
addressStreet text,
addressCity text,
addressZipcode text,
addressState text,
telephone text
);
CREATE TABLE kmmPrices (
fromId varchar(32) NOT NULL,
toId varchar(32) NOT NULL,
priceDate date NOT NULL,
price text NOT NULL,
priceFormatted text,
priceSource text
);
CREATE TABLE kmmReportConfig (
name varchar(255) NOT NULL,
XML longtext
);
CREATE TABLE kmmSchedulePaymentHistory (
schedId varchar(32) NOT NULL,
payDate date NOT NULL
);
CREATE TABLE kmmSchedules (
id varchar(32) PRIMARY KEY NOT NULL,
name text NOT NULL,
type tinyint unsigned NOT NULL,
typeString text,
occurence smallint unsigned NOT NULL,
occurenceString text,
paymentType tinyint unsigned,
paymentTypeString longtext,
startDate date NOT NULL,
endDate date,
fixed char(1) NOT NULL,
autoEnter char(1) NOT NULL,
lastPayment date,
nextPaymentDue date,
weekendOption tinyint unsigned NOT NULL,
weekendOptionString text
);
CREATE TABLE kmmSecurities (
id varchar(32) PRIMARY KEY NOT NULL,
name text NOT NULL,
symbol text,
type smallint unsigned NOT NULL,
typeString text,
smallestAccountFraction varchar(24),
tradingMarket text,
tradingCurrency char(3)
);
CREATE TABLE kmmSplits (
transactionId varchar(32) NOT NULL,
txType char(1),
splitId smallint unsigned NOT NULL,
payeeId varchar(32),
reconcileDate datetime,
action varchar(16),
reconcileFlag char(1),
value text NOT NULL,
valueFormatted text,
shares text NOT NULL,
sharesFormatted text,
memo text,
accountId varchar(32) NOT NULL,
checkNumber varchar(16)
);
CREATE TABLE kmmTransactions (
id varchar(32) PRIMARY KEY NOT NULL,
txType char(1),
postDate datetime,
memo text,
entryDate datetime,
currencyId char(3),
bankId text
);
More information about the gnucash-devel
mailing list