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.
To convert into other text based formats, including non-XML formats
like vcard, see the extensions packages: datafreedom-qsfxsl,
datafreedom-perl and datafreedom-doc.
The Extensions Manual contained in datafreedom-doc describes the
stylesheets available in datafreedom-qsfxsl, how to use them with
xsltproc and how to create your own XSL. Also included is the Palm
Default Currency Table and documentation for the datafreedom-perl perl
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.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. 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: http://www.data-freedom.org/.
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 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 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 <http://www.gnu.org/licenses/>.
SEE ALSO
For information on the XML used by QOF (termed QSF) and using XSL with
pilot-qof XML files, see The Data Freedom Extensions Manual. Depending
on your distribution, you may find it at
/usr/share/doc/datafreedom/manual.html
http://pilot-qof.sourceforge.net/
http://sourceforge.net/projects/pilot-qof/
COPYRIGHT