man pilot-qof (Commandes) - 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]] 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]] pilot-qof {[-d | --database] string} {--explain} pilot-qof {[-l | --list]} pilot-qof {--version} pilot-qof {[-? | --help]} pilot-qof [--usage]

DESCRIPTION

Pilot-QOF runs SQL-type queries against Palm databases. A Palm database record is a single instance of a specific object. Instances are collated to form one data source, called a book, that can contain any number of different objects and any number of instances. Pilot-QOF writes selected palm data to QSF XML offline storage and runs SQL-type queries on the live data or XML file. Pilot-QOF can liberate your Palm data to use in any number of other applications and scripts.

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.0.x series using pilot-link 0.11.8.

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

Use exactly one of -x, -a, -l or --explain.

-x, --xml-file filename
Query the QSF XML file: filename.
-a, --hot-query
Activate/HotSync and query the Palm.
-l, --list
Lists all databases supported by the current QOF framework.
--explain
List the fields within the specified database and exit, requires -d.

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 (it is ignored by -x, -l or --explain).

--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 file. If the file exists, it will be overwritten.
-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.
-s, --sql string
Specify a SQL query on the command line.
-f, --sql-file filename
Specify one or more SQL queries contained in a file.
--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.
--version
Display version of pilot-qof.

help options

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

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.

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 -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

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

SQL-like queries that work on the command line using -s may give SQL Parser errors when put into a file and used with -f. This is partly a problem in QOF and partly in Pilot-QOF.

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.

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.

SEE ALSO

For more information on QOF, see pilot-qof (3).

For information on the XML used by QOF (termed QSF) see pilot-qof (5).

http://pilot-qof.sourceforge.net/ http://sourceforge.net/projects/pilot-qof/