pilot-qof

'Querying Palm databases as objects'

Release: 0.1.1

The GNU GENERAL PUBLIC LICENSE Version 2, June 1991

This documentation is part of pilot-qof.

pilot-qof is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.

Note

You can find a copy of the GNU General Public Licence in the COPYING file in the pilot-qof source or installed (e.g. in /usr/share/doc/pilot-qof/) by your distribution.

Table of Contents

1. PILOT-QOF

pilot-qof — Querying Palm databases as objects using QOF (Query Object Framework).

Chapter 1. PILOT-QOF

Table of Contents

pilot-qof — Querying Palm databases as objects using QOF (Query Object Framework).

Name

pilot-qof — Querying Palm databases as objects using QOF (Query Object Framework).

Synopsis

pilot-qof { [[-x] | [--xml-file ]] filename } [ [[-c] | [--category]] string ] [ [[-t] | [--date]] string ] [ [[-d] | [--database]] string ] [ [[-e] | [--exclude]] string ] [ [[-s] | [--sql]] string ] [ [[-f] | [--sql-file]] filename ] [ [[-w] | [--write]] filename ] [ [[--compress]] ]integer [ [[--debug]] ] [ [[--invoice-city] | [--invoice-vendor]] ] [ [[--use-locale]] ]

pilot-qof { [[-s] | [--sql]] string } [ [[-w] | [--write]] filename ] [ [[--invoice-city] | [--invoice-vendor]] ] [ [[--debug]] ] [ [[--use-locale]] ]

pilot-qof { [[-f] | [--sql-file]] filename } [ [[-w] | [--write]] filename ] [ [[--invoice-city] | [--invoice-vendor]] ] [ [[--debug]] ] [ [[--use-locale]] ]

pilot-qof { [[-a] | [--hot-query]] } [ [[-p] | [--port]] <port> ] [ [[-c] | [--category]] string ] [ [[-t] | [--date]] string ] [ [[-d] | [--database]] string ] [ [[-e] | [--exclude]] string ] [ [[-s] | [--sql]] string ] [ [[-f] | [--sql-file]] filename ] [ [[-w] | [--write]] filename ] [ [[-u] | [--upload]] filename ] [ [[--compress]] ]integer [ [[--debug]] ] [ [[--invoice-city] | [--invoice-vendor] | [ [[--use-locale]] ]] ]

pilot-qof { { [[--explain]] } [[-d] | [--database]] string }

pilot-qof { [[-l] | [--list]] }

pilot-qof { [[--version]] }

pilot-qof { [[-?] | [--help]] }

pilot-qof [ [[--usage]] ]

Description

pilot-qof provides a query interface to data on a Palm device, using pilot-link and QOF - the Query Object Framework.

pilot-qof supports reading addressbook, datebook, expenses and ToDo data from a Palm device to XML files using the generic XML format used by QOF, called QSF. pilot-qof runs SQL-type queries on the live data or a QSF XML file and results can be imported into other QOF applications or converted to other text based formats, including non-XML formats like vcard.

The Extensions Manual describes the stylesheets available in pilot-qof, how to use them with xsltproc and how to create your own XSL. Also included is the Palm Default Currency Table.

The Query Object Framework (QOF) used by pilot-qof allows data to be queried with SQL-type syntax without requiring a real database. QOF provides a generic framework so that any query can be executed, including queries designed by the user. QOF uses a generic XML format for object data, called QSF.

This is the pilot-qof 0.1.x series using pilot-link 0.12.

Purpose

Pilot-QOF is intended for use with pipes as well as ordinary files and so Pilot-QOF will always try to output only valid XML. Error messages will be output but if no errors are found, pilot-qof remains silent. A message "Your query matched no objects" would break this model because, in real usage, an empty XML book can actually be a usable result.

Commands

-x, --xml-file filename

Query the QSF XML file: filename.

pilot-qof also supports the new QOF SQLite backend, if installed. To load data from a QSF XML file and save it to a SQLite database, specify the sqlite: access method (the file: access method is the default and is optional). See under EXAMPLES.

-a, --hot-query

Activate/HotSync and query the Palm.

-l, --list

Lists all databases supported by the current QOF framework and exit.

--explain

List the fields within the specified database and exit, requires -d.

-s, --sql string

Specify a SQL query on the command line. When used without -x or -a, pilot-qof expects INSERT type SQL-commands to create some data. Inserted entities are automatically selected for output and therefore shorthand options like -t, -d, -c and -e are ignored.

-f, --sql-file filename

Specify one or more SQL queries contained in a file. When used without -x or -a, pilot-qof expects INSERT type SQL-commands to create some data. Inserted entities are automatically selected for output and therefore shorthand options like -t, -d, -c and -e are ignored.

Options

pilot-qof options

It makes little sense to use -d and -e together. -d excludes all other databases automatically. -d or -e can only be specified once.

-u requires -a.

--invoice-city or --invoice-vendor require -t. -c is recommended. Options -u, -d, -e, -s and -f are ignored. (pilot_todo is excluded automatically.)

-c, --category string

Shorthand to only query objects that are set to the specified category.

-t, --date string

Shorthand to only query objects that contain the specified date. Specify dates using YY-MM-DD, YY-MM or just YY. Single digits can omit the leading zero, e.g. 04-12-1 for 1st December 2004 - years less than 100 are assumed to be in the 21st century. Years prior to 2000 can be specified as YYYY-MM-DD. This value is taken as a range, 05-03-01 includes all times between Tue Mar 1 00:00:00 UTC 2005 and Tue Mar 1 23:59:59 UTC 2005. 05-03 includes all dates and times between Tue Mar 1 00:00:00 UTC 2005 and Thu Mar 31 23:59:59 UTC 2005. 05 includes all dates and times in 2005.

-u --upload filename

Upload QSF XML data from <filename> to the Palm. Requires -a. Pilot-QOF will insert the new data and proceed. If no query is specified, the default query (all objects) is run, producing an XML file containing the new and old data.

-w, --write filename

Write the results of any query to the output XML file. If the file exists, it will be overwritten.

pilot-qof also supports the new QOF SQLite backend, if installed. To load data from a SQLite database and save it to a QSF XML file, specify the sqlite: access method (the file: access method is the default and is optional). See EXAMPLES.

--use-locale

If the Palm data contains locale-specific characters, the default behaviour is to convert to UTF-8. If viewed in a text editor or via a terminal that is not UTF enabled, these characters will be prefixed by Unicode sequences that look like control sequences. --use-locale converts the XML to the current system locale so that these Unicode sequences are not required. Can be used with or without -w.

-d, --database string

Shorthand to only query objects within one specific supported database.

-e, --exclude string

Shorthand to exclude a supported database from the query. -e can only be specified once. For finer control over the databases to query, use -s or -f.

-s, --sql string

Specify a SQL query on the command line. Currently, SELECT * FROM and INSERT INTO commands are supported. Joins are not supported but can be approximated using sequential queries.

-f, --sql-file filename

Specify one or more SQL queries contained in a file. Currently, SELECT * FROM and INSERT INTO commands are supported. Joins are not supported but can be approximated using sequential queries.

--compress integer [0]

The level of compression to use on the output file. Use zero for no compression, a number from 1 to 9 increases the level of compression. Unless the file is very large, values over 4 produce little or no extra compression. The default is zero, no compression.

Compression is not available when using STDOUT.

The use of file extensions is entirely up to the user. Pilot-QOF does not require files to have any particular extension, compression is automatically detected if used.

Pilot-QOF does NOT, therefore, add a .gz extension for you. (Neither does it add or require a .xml or .xml.gz extension.)

--debug

Enable debug output using /tmp/pilot-qof.trace which will be created if it does not exist and overwritten if it does. If this fails, pilot-qof tries to create a temporary trace file in /tmp or uses stderr.

--invoice-city | --invoice-vendor

Shorthand to relate an appointment or an expense to a record in the contacts database on the Palm. Requires -t. Options -u, -d, -e, -s and -f are ignored. If both are supplied, only --invoice-city is set. pilot_todo is excluded by either --invoice-city or --invoice-vendor (In general, it is inadvisable to invoice for work that is yet to be completed.)

--invoice-city uses the value from the City field of the Expense to look for a match in the City field of the Address.

--invoice-vendor uses the value from the Vendor field of the Expense to look for a match in the Company or Title fields of the Address.

Depending on how you have configured your Palm, these can produce different results. Appointment descriptions are matched against all three address fields: City, Company and Title.

The Palm appointment and expense databases do not directly relate such an event to a specific contact record. Most users will make the connection themselves but software needs some help. To prepare an invoice from Palm data, pilot-qof looks for a match for relevant field(s) in pilot_expenses and pilot_datebook against string fields in pilot_address.

It is helpful to use -c 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.

If you have problems with the --invoice-city or --invoice-vendor options, you may want to tweak your contacts records to use the same terms as you use in either expenses or appointments. pilot-qof does attempt to use partial matches but abbreviations like Ltd. will not match against Limited. Alternatively, you may need a set of SQL-type queries in one or more files. Select the event(s) by date, select all contacts and save into one QSF XML file. Then load that file and select all events and the specific contact that matches the event(s).

conduit options

-p, --port <port>: Use device file port to communicate with the Palm handheld. If this is not specified, pilot-qof will look for the PILOTPORT environment variable. If neither are found or supplied, pilot-qof will exit with an error message.

help options

-h, --help: Display the help synopsis for pilot-qof.
--usage: Display a brief usage message and exit.
--version: Display version of pilot-qof.

Timezones

Pilot-link itself does not directly support users who cross timezones with their Palm. If timezone issues are important to your DateBook or Expenses data, a sensible precaution is to change the Palm Preferences to use the same timezone as the computer running pilot-qof. Once in QOF, all dates and times are UTC (Universal Coordinated Time) so the QSF XML files can be freely exchanged across timezones without complication (see also 'Translations' below).

Translations

Palm Appointments use the name of the weekday to indicate certain repeat events. This value is local to the timezone of the Palm and pilot-qof will translate the value to match. If you need to transfer such events to a user with a different locale, configure the host PC to the same locale as the end user. pilot-qof will then translate the weekday name and write it into the XML. Remember to also set the Palm to the locale of the end user so that the timezone information is preserved in UTC, as above.

Repeating Appointments

When an appointment repeats, the Palm stores the start date, the repeat frequency and end date as well as a series of exceptions that normally occur when the user deletes one appointment from a repeating series. This system is compact but not easy to query for specific dates in the chain. So pilot-qof creates temporary appointments for each of the repeats, skipping the exceptions. This makes it easy to find an event on a specific date, whether or not it was a repeating event. These extra appointments are written out to the QSF XML so that processes that parse and/or query the XML data do not have to understand the Palm repeat system. When datebook data is uploaded to the Palm, pilot-qof ignores all these extra appointments and reinstates the Palm record. If appointments are modified in the XML, pilot-qof will attempt to mark any gaps as additional exceptions in the Palm.

Currencies

Experimental

The Palm supports a list of default currencies and four custom currencies. pilot-qof supports the default currencies by looking up the Palm currency code (an arbitrary index integer) in a table of currency values created using the simple method of repeatedly using HotSync with each currency in turn. The table relates the code integer to the currency symbol used by the Palm (which may or may not be the same symbol used by other programmes) together with the currency mnemonic and currency fraction used by GnuCash and CashUtil. All default Palm currencies are in the ISO4217 namespace used by GnuCash and CashUtil. The mnemonic allows the currency to be easily related to the matching gnc_commodity and the fraction (the number of sub-divisions of the currency unit, pennies, cents, etc. per unit) solves a problem where pilot-qof would assume that all expense amounts were displayed to 2 decimal places.

See the Pilot-QOF Extensions Manual, usually installed in /usr/share/doc/pilot-qof/manual.html for the full list of Palm default currencies.

XSL stylesheets

pilot-qof includes some basic XSL stylesheets to convert QSF XML to other formats. Support will be increased in future releases and contributions are welcome.

Examples

List all currently supported databases.

pilot-qof -l

Print all objects in all supported databases that have the category Business using offline storage, to STDOUT.

pilot-qof -x offline.xml -c "Business"

Print all expenses in the file 'expenses.xml' that occurred during the month of May, 2004.

pilot-qof -x expenses.xml -t 2004-05

Print only the expenses from the file 'mypalm.xml'.

pilot-qof -x mypalm.xml -d pilot_expenses

Print everything except the expenses from the file 'mypalm.xml' that occured in February 2005 in the category 'Business'.

pilot-qof -x mypalm.xml -e pilot_expenses -c Business -t 2005-02

HotSync to the Palm and create the offline storage (all databases, all objects). This will overwrite the existing file, if any.

pilot-qof -a -p /dev/ttyUSB1 -w offline.xml

Command line SQL query of offline storage.

pilot-qof -x offline.xml -s "SELECT * from pilot_address where entryCity = 'London';"

Prepare an invoice for September 23rd 2005 from Palm data where the City field of the expense(s) match the contact to be invoiced.

pilot-qof -a -t 2005-09-23 --invoice-city -w invoice.xml

Read data from a QSF XML file and save as a SQLite database (Requires the QOF SQLite backend to be installed, libqof-backend-sqlite0.) (The file: access method is the default and is optional).

pilot-qof -x file:todo.xml -w sqlite:todo.sqlite

Read data from a SQLite database and save as a QSF XML file. (Requires the QOF SQLite backend to be installed, libqof-backend-sqlite0.) (The file: access method is the default and is optional).

pilot-qof -x sqlite:todo.sqlite -w file:todo.xml

Author

pilot-qof was written by Neil Williams <linux@codehelp.co.uk>.

This manual page was written by Neil Williams <linux@codehelp.co.uk>

Known Bugs

None known at this time.

Reporting Bugs

Please do NOT report bugs in pilot-QOF to pilot-link or GnuCash. Report bugs via the QOF-devel mailing list.

http://lists.sourceforge.net/lists/listinfo/qof-devel

Copyright

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.