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

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. The file must exist.

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. Any supported database can be listed.

-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. To see the full list of supported databases, use -l|--list.

-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.To see the full list of supported databases, use -l|--list.

-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 Data Freedom Extensions Manual, usually installed in /usr/share/doc/datafreedom/manual.html for the full list of Palm default currencies.

XSL STYLESHEETS

The recommended datafreedom-qsfxsl package includes some basic XSL stylesheets to convert QSF XML to other formats. Support will be increased in future releases and contributions are welcome.

DATA FREEDOM

Data Freedom is about being able to liberate your data from one application and make it seamlessly available on another. Data Freedom also seeks to help liberate your data from one platform and make it seamlessly available on another.

Rather than reverse engineering specific troublesome file formats, data-freedom concentrates on encouraging the development of implementation architectures and conceptual foundations to synchronise arbitrary data.

The goal is to encourage generic data handling using extensible mechanisms that are inter-related to allow free exchange of data between disparate applications, systems, architectures and platforms.

Each implementation is free to choose the most suitable method of providing data and receiving data. All that is required is that the method chosen is as open, extensible and generic as possible. Naturally, the eXtensible Markup Language - XML - features strongly, as does perl.

For more information on data freedom: m[blue]http://www.data-freedom.org/m[].

Splitting pilot-qof into specialised packages. The stylesheets, scripts and this extensions manual have previously been part of the single pilot-qof package. From pilot-qof >= 0.1.3, pilot-qof comprises of three main parts: the pilot-qof binary, the XSL stylesheets and a growing set of Perl scripts using the XML::QOFQSF module (available from CPAN or in Debian). The Extensions Manual has also been moved into the datafreedom-doc package.

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.

Note that USB support in pilot-link 0.12 has changed from a specific device to the usb: identifier.

Under certain circumstances, you may be unable to connect as an ordinary user but able to connect as root. Add yourself to the dialout group to fix this issue:

$ sudo gpasswd -a neil dialout
Adding user neil to group dialout
    

You will need to logout and log back in for the group membership to take effect.

pilot-qof -a -p usb: -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 [email protected].

This manual page was written by Neil Williams [email protected]

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 or use the bug tracking systems of your GNU/Linux distribution.

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

When reporting bugs, please consider running pilot-qof with the --debug option which creates a debug file named /tmp/pilot-qof.trace for each command. It is helpful if you can attach this file to your bugreport or make it available some other way. Please also indicate whether the bug is related to pilot-qof itself or only to the XSL stylesheets.

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 3 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, see <m[blue]http://www.gnu.org/licenses/m[]>.

COPYRIGHT