external sites pilot-link QOF xmlsoft GnuCash

Querying Palm databases as objects

Pilot-QOF converts Palm databases to queriable objects. A Palm database record is an instance of the object, called an Entity. Objects are collated to form one data source, called books, that can contain any number of different objects - depending on the application running QOF. Pilot-QOF writes selected palm data to QSF XML offline storage and running SQL-type queries on the live data or XML file.

Pilot-QOF currently supports the four main applications (Expenses, Contacts, Calendar and ToDo) and work is now progressing to support other free software Palm databases like PCash and FreeCoins.

Liberate your Palm data to use in any number of other applications and scripts.

pilot-qof (>= 0.1.6) (including all the datafreedom scripts) is licenced only under the GNU General Public Licence version 3 or later, instead of v2.


What is QOF?

QOF provides a set of C language utilities for performing generic structured complex queries on a set of data held by a set of C/C++ objects. This framework allows programmers to add query support to their applications without having to hook into a SQL Database.

QOF provides the framework, the QOF XML backend (QSF) provides the off-line storage and a format for a data stream between different applications, including converting QOF objects.

>= v0.1.2 (with libqof1 >=0.7.2) can also use the QOF SQLite backend, converting data to and from XML automatically.

What does QOF have to do with pilot-link, or vice versa?

QOF frees the data from the application, allowing queries, data mining, abstraction and data interchange without having to recompile the original application, let alone program the filters. Central to QOF are the ideas of databases and objects.

QOF then collates all supported objects to form one data source that can be queried. QOF allows the programmer to easily find all other instances of any one type, just by having one instance of that type. Objects are collected in books and a book can contain any number of different objects - depending on the application running QOF.

The book is the central data source within QOF and a book can be queried or written out to XML using the QOF Serialisation Format: QSF.

Pilot-link itself does not directly support users who cross timezones with their Palm - it may be wise that users are reminded that if timezone issues are important to their DateBook or Expenses data, the user should take the sensible precaution of changing the Palm Preferences to use the same timezone as the computer running pilot-qof. Once in QOF, all dates and times are UTC so the QSF XML files can be freely exchanged across timezones without complication.

Where does GnuCash come in?

QOF began in GnuCash - it's the GnuCash engine without the financial objects. It was natural to use QOF to enhance the accessibility of data within GnuCash and to enable GnuCash to use QOF to access data from other applications. Using XML, it is possible to exchange data between other applications.

Downloading pilotqof

Current release: 0.1.6 for libqof1 (>=0.7.2) and pilot-link (>=0.12.x).

As of v0.1.3, pilot-qof supports being split into multiple packages, allowing the main pilot-qof binary to be isolated from the documentation, XSL stylesheets and perl scripts. In future, the datafreedom contents may be split out from pilot-qof SVN.

You'll also need pilot-link >= 0.12.x which includes libpisock9 and libpisync0.

Using pilot-qof to create invoices.

This all started when I needed to collate data from my Palm PDA (using Palm OS 5) and create an invoice in GnuCash with the minimum of extra typing. The PDA databases contain details of the hours to be billed (Calendar), incidental expenses incurred during the invoiced work (Expenses) and contact details for the client and workplace (Contacts). By adding a user-configurable set of defaults, the other essential elements of an invoice are ready, item type (hours, material etc.), accounts to use for each type and the account to use for posting the invoice later.

pilot-qof (>= 0.0.8) includes the --invoice-city and --invoice-vendor options to relate expenses and appointments to the address details of the client to be invoiced. This fills a gap in how the Palm organises data but does rely on the user making the City or Vendor of any relevant expenses match the Description of the relevant appointment and certain fields in the Contacts record for the client. Each crosslink is made using the date specified to pilot-qof; this is why --invoice-city and --invoice-vendor require the -t | --date option.

If --invoice-city and --invoice-vendor are specified on the pilot-qof command line, --invoice-city is used.

When using --invoice-city the value from the City field of the Expense is matched with the City field of the address in Contacts. i.e. pilot-qof tries to locate the client according to where you where when you incurred the expense.

When using --invoice-vendor the value from the Vendor field of the Expense is matched with the Company or Title fields of the address in Contacts. i.e. pilot-qof tries to locate the client according to who you recorded as the Vendor of the expense. This is done in case the Palm user is actually recording client details in place of the vendor. It's just another way of trying to crosslink the data.

It is helpful to use -c | --category to limit the number of records to check, providing your Palm is configured to have all appropriate expenses, appointments and contacts in the same category.

In each case, pilot-qof uses the Description field from the appointment in the crosslink - just in case there is no expense. Therefore, the Description of an appointment should contain a string match for a unique string in the Contacts record for the relevant client. I realise this enforces some restrictions on the Palm user but without these matches, the Palm does not make it easy to relate an expense or an appointment to a specific client. If you cannot use such patterns, ask on the QOF-devel mailing list and ask about some SQL-type queries that will replicate the crosslinks using some other form of query.

Calculating invoices

New in version 0.2.0, two new perl scripts have been added, dfinvoice-future and dfinvoice-month which can be configured with mileage, currency, client and rates data in user editable files in ~/.datafreedom/. For more information or to add notes about how these scripts can be used, see the Mantis bug tracker or the Pilot-QOF Wiki.

More information:

This website, like other QOF related websites, is licenced under the GNU Free Documentation Licence without invariant content which makes it compatible with the Debian Free Software Guidelines (DFSG) under a recent decision by the Debian community. See GFDL-licensed works without unmodifiable sections are free, winner of the General Resolution on the GFDL vote which closed on Sunday 12th March, 2006.

Pilot QOF is a SourceForge project: (pilot-qof).

The copyright licensing notice below applies to this text.

Copyright © 2004-2006 Neil Williams

Permission is granted to copy, distribute, and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts. A copy of this license is included in the file copying.txt