This document describes how to setup and use ETRACK, a package to track expenses: query, add, delete, update.
This document applies to ETRACK 0.9913.
Setting up to use ETRACK can be viewed as more fun than actually using it,
depending on your particular proclivities. After installing ETRACK, you need
to create a configuration file, and arrange for ETRACK users to set their
ETRACK_CONFIG environment variable to point to this file.
If someone else has already taken care of these things for you, feel free to skip to the next chapter which explains how to use ETRACK (see Session).
This section documents the configuration file, which is at the heart of the
ETRACK setup. The file contains directives describing the configuration name,
which backend database to use, the expense attributes, and various flavors of
queries. Directives have the form of Scheme expressions with the car
the name of the directive.
Comments begin with semicolon (;) and go to the end of the line.
Additionally, you can comment-out a directive by quoting it with single quote
before the opening parenthesis.
The examples under this section are all taken from the example configuration
file, which you might find easier to peruse directly, with the shell command
etrack --display-example-config.
The sockdir names the directory where the PostgreSQL database
keeps its connection socket (typically named .s.PGSQL.5432).
This should be an absolute filename. For example:
(sockdir . "/usr/local/var/etrack")
See Maintenance, for how to set this value when migrating the database from one cluster to another.
The name is a for-human-consumption string that identifies the
configuration, while the database is the name of the PostgreSQL
database used to actually keep the data. The database name is not quoted.
For example:
(name . "Example Expenses")
(database . etrack_example)
The attributes directive defines the attributes which, in turn, define
the attribute codes (also known as "attcodes"). Because attcodes are basically
the first letters of the attribute names, you need to be somewhat careful not
to define overlapping names. For example:
(attributes . (vehicle
household
food
utilities
entertainment
rent
misc
insurance))
Here, the attcodes would be v, h, f, u, e, r, m and i.
There are several flavors of query: simple, drill-down and custom. The first two rely solely on the attribute codes. By default, a simple query is created for each of the attributes, so you don't have to define them yourself.
The simple query has the form:
(query . (simple "DESCRIPTION" QUERY-CODE))
(query . (simple-list "DESC-1" Q-CODE-1 "DESC-2" Q-CODE-2 ...))
where QUERY-CODE is "!!" for "all", or a series of attribute codes which are ANDed together. If an attribute code is prefixed with "!", its sense is inverted. The DESCRIPTIONS are short strings that describe the query, and may include embedded spaces. For example:
(query . (simple-list
"all" !!
"all no rent" !r
"eating out" fe
"fun not food" e!f
"food not fun" f!e
))
The drill-down query has the form:
(query . (drill-down ATTRIBUTE (DETAILS ...)))
(query . (drill-down-list CAT-1 (DET-1 ...) CAT-2 (DET-2 ...) ...))
where ATTRIBUTE is one of the defined attributes. DETAILS is a
list of strings that are searched in the first two slots of the details
field. The description for the query is formed like so: "ATTRIBUTE / DETAIL".
For example:
(query . (drill-down-list
utilities ("cable"
"sdge"
"phone")
vehicle ("gas"
"car"
"moto"
"bike")))
Lastly, the custom query has the form:
(query . (custom "DESCRIPTION" QUERY-THUNK))
where QUERY-THUNK is a Scheme procedure that takes no arguments and must
finish with a query using either select
(see select),
or one-row-table-query.
Return a result comprising two rows formed from labels and vals. This object satisfies
pg-result?.
You can use one-row-table-query to present arbitrary data in a way that
etrack understands. For example:
(query . (custom "sum of numbers from 0-10"
(lambda ()
(one-row-table-query
`(,@(iota 11) sum)
`(,@(iota 11) ,(apply + (iota 11))))))))
The order of the query directives is preserved in the query menu during ETRACK operation (see Query command).
This section lists various shell commands to accomplish database related
tasks. Of primary importance in setup phase is creating a database and
granting users access to it. Before you do that, make sure the configuration
file has at least the database field specified (see Configuration file).
Creating a new database and initializing it:
etrack -b initdb
Granting access to a user:
etrack -b grant USER
Revoking access from a user:
etrack -b revoke USER
See Maintenance, for commands to use once ETRACK has been set up and is in regular use.
The last setup step is to set the environment variable ETRACK_CONFIG to point to the the configuration file you created or modified, as described in the previous section (see Configuration file). Supposing you name this file /etc/etrack/cherry-lane.config, the following would work for Bourne shell and variants:
ETRACK_CONFIG=/etc/etrack/cherry-lane.config
export ETRACK_CONFIG
And for csh and variants:
setenv ETRACK_CONFIG /etc/etrack/cherry-lane.config
These fragments should be added to the appropriate shell initialization file for each user who is to have access to this configuration, or to the system shell initialization file if for all users.
To specify more than one configuration file, separate each filename with a colon. For example:
ETRACK_CONFIG=/etc/etrack/c1:/etc/etrack/c2:/etc/etrack/c3
This example (in Bourne shell syntax) specifies three files c1, c2 and c3, all in directory /etc/etrack.
Another environment variable that the etrack executable consults is
EMACS. If unset, or if it has the value t (commonly the case
when running ETRACK as a subprocess of Emacs), the default value is taken to
be simply “emacs”.
When ETRACK starts, it looks for file ~/.etrack.el and loads it. This
allows you to customize the behavior of ETRACK (somewhat) and Emacs (more so).
Here is a complete example:
;;; ~/.etrack.el --- configure the ETRACK user interface
(set-language-environment "Latin-1")
(blink-cursor-mode -1)
(fringe-mode 0)
(when (and window-system (member "etrack" command-line-args))
(set-face-background 'default "darkgreen")
(set-face-foreground 'default "cyan")
(set-face-foreground 'mode-line "white")
(set-face-background 'mode-line "black"))
;;; ~/.etrack.el ends here
This first sets the language environment so that toggle-input-method
(normally bound to C-\) works without asking you for the default input
method; then continues to set up the display.
The expression (member "etrack" command-line-args) is true when ETRACK
is started from the shell, and false when started with ‘M-x etrack RET’
in an already running Emacs session.
There are two ways to start ETRACK:
etrack
A variant is etrack -nw.
If you are using a windowing system, this command starts
ETRACK in the current window instead of creating another one
(nw stands for no window system).
M-x etrack into Emacs
If there is an already-active session, this simply selects the ETRACK buffer instead of starting a new session. In this case, Emacs also displays ‘(Resuming already-active session)’ in the echo area.
If you have more than one configuration specified, ETRACK asks you to choose one of them. After this is decided, ETRACK clears the screen and displays the main menu:
Welcome to ETRACK for NAME!
Please report bugs to ttn.
=== Page 1 ===
Choose a command by typing a number or letter:
(1) query
(2) add
(3) delete
(4) update
(5) prune duplicates
(t) add using template
(T) edit templates
(c) calendar
(m) mail session log
(a) about this program
(x) export data
(v) view mode
(q) quit
Your choice:
Here, NAME is the name given in the configuration file referenced by
the ETRACK_CONFIG env var (see Environment).
Type 1, 2, 3 or 4, to query, add, delete, or update expense entries; or 5 to prune (possible) duplicate expense entries; or one of the other letters in parentheses to work with templates, see a calendar, mail this session log to someone, read some news about ETRACK, go into View Mode, export data, or quit. You do not need to type <RET> after this letter.
The Query command is used to query the database and display the result.
ETRACK displays the query menu. Select one by typing the number associated with the query description, and then <RET>. ETRACK displays the results and goes into View mode (see View mode) so that you can examine the query or ask for a monthly breakdown, if applicable.
The query menu is formed by listing those queries defined in the configuration file (see Configuration file), followed by the "standard queries", which are:
If you specify a query that doesn't exist, ETRACK reports an error, but goes into View mode anyway.
The Add command is used to add an expense entry.
ETRACK prompts you for an amount, a date, one or more attribute codes, and zero
or more details; adds this entry upon confirmation; then loops. To quit out
of the loop, type <RET> when asked for the amount.
To save typing, you can use
M-p and M-n to select and edit previous entries.
When specifying the attribute, you only need to type the first letter of its
name. ETRACK expands and displays the full name for you automatically. Use
DEL to erase mistakes. Use ? to display the list of available
attributes.
Normally, there are no default values, except for the date, which is
today, in YYYY-MM-DD format. You can specify a group of default
values by selecting a template (see Template commands), and edit the
values further prior to confirmation.
During entry, you may wish to change the value of a previously-entered (and exited) field. To restart, type C-r. The previously-entered values are used as the default.
The Delete command is used to delete an expense entry.
ETRACK prompts you for an entry index number and deletes that entry, no questions asked; then loops. To quit out of the loop, type <RET> alone. You can find out an entry's index number by looking at the first column of a query (see Query command).
The Update command is used to modify one or more expense entries. The precise mechanism depends on whether or not there are marked entries (see View mode). If no entries are marked, update is done one entry at a time. Otherwise, the marked entries can be edited and updated more or less in parallel.
ETRACK asks for an entry index number and retrieves that entry for update.
For each field, ETRACK allows you to edit the current value. To save typing,
you can use M-p and M-n to select and edit previous entries.
When you are done, ETRACK asks for confirmation and updates that entry.
Note that the details field must be in the form of a list of strings.
For example, if the desired details are:
Then, this must be entered into ETRACK like so:
("movie" "One Flew Over the Cuckoo's Nest")
If the original entry did not have any details, this will be shown as
nil. We will probably hide this implementation detail in a future
version of ETRACK.
ETRACK shows the marked entries in a special format and goes into a recursive editing mode. In this mode, TAB moves to the next editable field, circling around to the top at the bottom of the page. If you try to modify text outside an editable field, ETRACK signals a “text-read-only” error.
When you are done editing the contents of the entries, type C-c C-c. What happens next depends on whether the entries were changed:
Regardless of the changes (or non-changes), ETRACK goes into View mode as the last step.
The Prune duplicates command is used to examine and prune (possible) duplicate entries. See Query command, for the "(possible duplicates)" query.
ETRACK computes the list of possible duplicates (may take a few seconds) and
displays the count of pairs. For each pair, starting with the most recent,
ETRACK displays the pair and goes into Prune mode. To get out of Prune mode,
type q.
Here is a table of keys and what they do in Prune mode:
After Prune mode, ETRACK displays the number of entries deleted, and goes into View mode (see View mode).
A template is a partially completed entry that can be used to initialize new entries. Unlike normal entries which have a numeric identification number, each tempate has a unique name, instead. Another difference is that fields may be left unspecified, also known as having a NULL value.
From the main menu you can add using a template (selecting one if none are currently selected) as well as edit the available templates.
The Add Using Template command sets the current template and collects entries to add using it. You can also add without any template (see Add command). ETRACK lists the available templates, one per line, and you type in a name to select one of them.
When a template is selected, its name is shown next to the menu item for the Add Using Template command in double-quotes, and is the default choice for the next time this command is invoked.
Like the Add command, you can use C-r to restart an entry.
The Edit templates command prompts you to choose a template to edit. You can type <TAB> to see a list of available template names, and also do completion on partial input. If you specify a name not in the list, a new template will be created with that name.
There are two editing operations: delete and modify. ETRACK first gives you the option to delete the template. If you decline deletion, it is assumed that you wish to modify the template and ETRACK prompts you for values for each of the fields: amount, date, attribute code, and details. To leave a field unspecified, simply delete all the text in the minibuffer (for example, with C-a followed by C-k).
When the template has been either deleted or modified, ETRACK prompts for another template to edit. You can continue as above, or type <RET> to finish editing.
The Calendar display command displays a calendar (wow!).
ETRACK prompts you for a month to display. Type a month number, or a month number followed by space followed by a year number, or just <RET> for the current month. ETRACK displays the requested month(s) and goes into View mode (see View mode).
The Mail session log command mails the session log (up to and including the Mail session log command) to someone.
ETRACK prompts you for a recipient for the mail, a subject line, and a short one-line message; and uses this info to compose and send the email message. Afterwards, ETRACK goes into View mode (see View mode).
This command shows some information about ETRACK and goes into View mode (see View mode).
This command allows you to export data to a file in tab-separated format,
i.e., with a tab character between the fields id, date,
amount, attcode, details (in that order), and also with a
tab character between each of the elements of details. Other programs
can process this file format easily.
When invoked, ETRACK displays a small menu in the echo area:
Source: (m) marked, (p) page, (d) date range, (a) all
The ‘(m) marked’ appears only if there are marked entries (see View mode). Type one of the indicated letters to choose the source of the export. If you type p, ETRACK asks for a page number; if d, a date range, which is specified like so:
YYYY[-MM[-DD]],YYYY[-MM[-DD]]
You can also use a colon (:) instead of a comma. Month and days are optional.
The first YYYY-MM-DD is the start date and the second, the end date.
If only one date is given (no comma or colon), then only entries from that
precise date are exported. If the start date is omitted, it defaults to the
earliest date. If the end date is omitted, it defaults to the latest date.
If you specify an end date of 2005 (for example), that stands for
2005-01-01 and not 2005-12-31. This behavior will
probably change prior to ETRACK 1.0 release to be more intuitive.
You can enter View mode from the main menu by typing v. Also, some
commands enter View mode automatically before returning to the main menu.
When you are in View mode, you can move about the session log, all around the current page, or even to previous pages (and back). The current position is marked by the cursor. You can also scroll the screen left and right.
View mode is also used to mark entries (and unmark them, of course). To
get out of View mode, type q. Here is a table of keys and what they do
in View mode:
P.
N.
When there are marked entries, the main menu displays ‘(4) update
marked’, and the menu item ‘(u) unmark all’ appears.
In the case of View mode after a Query command, typing a number jumps to the query selection page (skipping the main menu) so that you can begin another query straight away. Also, three additional keys are available to show monthly breakdowns of the query (if applicable), and to export data.
m, ETRACK also displays percentages and a
histogram.
A month range has one of two forms: either YYYY-MM, which indicates one
month; or YYYY-MM,YYYY-MM, which indicates an inclusive range of
months. Some examples:
2001-2 February 2001
2001-2,2002-3 February 2001 through March 2003
This command works as if you had returned to the main menu and typed x m or x p there (see Export Data command), except that the page number is computed automatically in the case of x p.
Aside from interactive usage (see Session), it is often more convenient to record new entries offline and then add them in bulk to the database at a later time. This chapter describes the diary format that ETRACK recognizes, as well as the commands to use, for this purpose.
The diary format is a straightforward text format with four columns, each
separated by whitespace (one or more tab or space characters). The columns
appear left-to-right and are named date, amount, attcodes
and details, respectively. More precisely:
dateYYYY-MM-DD (numerical year, month and day).
If the numerical value of month or day is less than 10, write it
with a leading 0 (zero), for example 04 for April.
amount-ABCDE.FG (digits, minus-sign optional).
You can use however many digits are necessary to the left of the decimal
point — the five shown here are just an example. However, to the right of
the decimal point, two digits are always required, for example 42.00.
attcodeslist-attcodes to see a list:
etrack -b list-attcodes
Characters in attcodes are case-sensitive; for example,
s and S specify two different attributes.
detailsHere is an example with two entries:
2003-03-23 20.00 f "torta" "vigoni" "x mamma"
2003-03-27 6.00 sh "tessera coop"
In the first entry, there are three strings in the details column, in
the second, only one. The columns are separated by multiple spaces.
You can import diary format data into the database with the
backend shell command import-diary-file. There are two invocation
variations, depending on whether you run ETRACK locally or remotely (via ssh).
For the following examples we assume the diary file is named expenses
in the current working directory (locally).
Local ETRACK invocation:
etrack -b import-diary-file expenses
Remote ETRACK invocation:
ssh -X REMOTE-HOST etrack -b import-diary-file - < expenses
Note that the portion etrack -b import-diary-file is the same for both
local and remote invocations. For the latter, the - < expenses
construct arranges for ssh to feed the file to ETRACK using the standard
input channel (and ETRACK will use the term “standard input” in messages
instead of the filename).
There are two distinct steps to the import process: validation, which checks the format of the file; and insertion, which adds entries to the database.
If validation fails, ETRACK does not continue, but instead displays an error message and lists the line number, reason for failure, and text of the invalid lines. ETRACK will also display some format hints, including a list of valid attcodes, and exits with non-success exit code. For example:
etrack: Sorry, no entries added to the database due to
errors encountered in the following lines:
24: bad details
2003-02-24 2.00 hs "spina telefono
192: bad details (empty)
2003-02-26 51.94 f "esselungga" ""
:
(etc)
On the other hand, if validation is completely successful, ETRACK goes ahead with the insertion. Note that ETRACK does not check for duplicate insertions; you need to do that yourself (see Prune duplicates command).
Normally, once ETRACK is set up (see Setup), operation is driven in
large part by the user (see Session). Occasionally, however, there
arise tasks that require administrator involvement. This chapter lists
the etrack -b shell commands available and ends with some short
examples of their usage.
When using the Emacs front end, client encoding is automatically deduced from
language environment (see Customization). For shell command interaction,
however, you may need to explicitly specify how you expect the output to be
encoded with the -E client-encoding option (if unspecified the
default is UNICODE).
For a list of possible client-encodings, see section “Character Set
Support” in the PostgreSQL documentation.
Some of the following commands can be very dangerous; be careful!
Most require that a configuration
file be specified (see Configuration file). Two in particular are
interactive: repl and shell; the rest do their job,
display their results to stdout, and exit when done.
Usage: etrack -b [-E CLIENT-ENCODING] CMD [ARG] where CMD is one of: --help -- display this message and exit succesfully about -- display some info about ETRACK change-attcode OLDNEW -- OLDNEW is one arg of two adjacent chars char-attributes-alist -- display attributes keyed by char number check-config FILE -- check FILE and summarize, or signal error delete-row ROW -- delete ROW from the table do-nothing -- do absolutely nothing at all, truthfully dump (FILE TYPE SPEC) -- dump to FILE based on date or id grant USER -- grant db access to a USER import-diary-file FILE -- import diary-format data from FILE import-tab-sep-file FILE -- import tab-separated data from FILE initdb -- initialize database insert LS -- insert new data LIST into the table list-attcodes -- list attcodes and associated attributes list-queries -- list queries in two columns possible-duplicates -- display single list of possible duplicates query-one-row-alist ROW -- display ROW as an Emacs-friendly alist query-one-row ROW -- display ROW query-two-rows (ROW-1 . ROW-2) -- query two rows query NUM -- submit a query and display the result redisplay-last-result-by-month MSPEC -- as advertized repl -- for use primarily by the emacs front end replicate DIR -- copy database to DIR revoke USER -- revoke db access from a USER shell -- like repl but w/ prompt and no parens required template-munge (OP NAME [ARGS...]) -- add/del/mod a template by NAME templates-list -- display single list of all templates update-row (ROW . DATA) -- update ROW with new DATA upgrade-db -- fix old misunderstandings upward-compatibly
Dumping an existing database:
etrack -b dump-alist > DUMP
This writes the contents of table expenses to file DUMP,
in an alist format. The alist keys are the table's column names.
Loading data into an existing database:
# NOTE: This does not replace the current data.
etrack -b load-alist-file DUMP
Note that the "i" field is not preserved when doing load-alist-file.
See Prune duplicates command.
Upgrading the database schema (see DB):
etrack -b upgrade-db
Migrating the database to another cluster (see socket directory):
etrack -b replicate NEW-DIR
sed '/sockdir/s|. ".*"|. "NEW-DIR"|' CONFIG-FILE > NEW-CONFIG-FILE
This chapter describes some of the innards of ETRACK. The programmer hopes these explanations are clear enough to reference, critique and improve upon, later.
The first two DB design versions had a single table. Going from version 1 to
version 2 renamed the column catcode to attcode. Version 3
uses type integer instead of
float for column amount. Version 3 also introduces a simple
table etrackmetainfo with columns key and value, both of
type text. Version 4 (the current one, starting with ETRACK 0.999)
adds the table templates.
Here is the list of keys available in the etrackmetainfo table:
*db-design-version*expensesThe expenses table is defined like so:
((i serial)
(date timestamp "WITH TIME ZONE" "NOT NULL")
(amount integer "NOT NULL")
(attcode text "NOT NULL")
(details text[]))
The templates table is defined like so:
((name text "PRIMARY KEY")
;; rest similar to table `expenses' but allowing NULL
(date timestamp "WITH TIME ZONE")
(amount integer)
(attcode text)
(details text[]))
Some schools of database design consider using arrays to be harmful, so while ETRACK hasn't advanced enough to make/break that point of view just yet, it's a distinct possibility for the future.
From the user's point of view, the application is a single command from Emacs:
M-x etrack. This used to start a big synchronous event loop (with many
small internal event loops) described in Session. Nowadays, we use the
(somewhat perfunctory) Etrack major mode. Other implementation issues:
amount column, Emacs is only aware of
floating point numbers (via string-to-number in functions
--etrack-collect-new-entry and --etrack-cmd:update).
Conversion to/from the integer type used by the backend is done
by the backend. This centralizes those conversions at the expense of
numerical safety in the frontend input routines.
ETRACK 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, or (at your option) any later version.
ETRACK 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 GNU Emacs; see the file COPYING. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.