The primary focus of this document is periodic
data manipulations required for IS4C. Tasks can
be managed through Dayend Polling => Scheduled
Tasks. This is just a light wrapper around
cron; manipulating your own crontab directly
is perfectly fine, too.
The Transaction Shuffle
The most important nightly task to understand is
transaction archiving.
Sooner or later, depending on one's
hardware budget, reporting queries against all transaction
data, ever get unbearably slow, so transaction data
is segmented into monthly tables. At any given time:
- dtransactions on the transaction database (default: is4c_trans)
contains today's transactions.
- transarchive on the transaction database (default: is4c_trans)
contains the previous 90 days' transactions.
- transArchiveYYYYMM on the archive database (default: trans_archive)
contains transactions for the month MM in year YYYY.
At some point each night, dtransactions' data is copied into both transarchive
and the correct monthly table, then dtransactions is truncated in preparation
for the next day.
The included task nightly.dtrans.php handles this rotation. Any other
nightly tasks that examine or manipulate the current day's transactions need
to know where that data is.
The Transaction Shuffle, Pt. 2
Fannie supports an alternate archiving structure using partitions instead of
monthly tables. This feature is experimental at the moment and only supported
with MySQL. MySQL's performance with multiple monthly archive tables is very
poor; this alternative addresses that problem. The structure changes to this:
- dtransactions on the transaction database (default: is4c_trans)
contains today's transactions.
- transarchive on the transaction database (default: is4c_trans)
contains the previous 90 days' transactions.
- bigArchive on the archive database (default: trans_archive)
contains all transactions prior to the current day
and is partitioned by month
To use this alternative, select "partitions" instead of "tables" as the Archive Method
on Fannie's install/config page. The same included task nightly.dtrans.php
handles the rotation and creates new partitions as needed.
Batches
There are two related tasks here. nightly.batch.php takes all items
off sale (resetting them to normal_price), then applies any current sales
batches based on batch start and end dates. Schedule this batch at or shortly
after midnight to make start and end dates behave sensibly.
The other task, nightly.pcbatch.php updates items' regular, non-sale
price (i.e., normal_price) based on any price change batches with a matching
start date. Price change batches are only applied once, so their end date
is irrelevant. This task should also run after midnight.
It doesn't matter which batch task runs first. Scheduling them at the
exact same time might be unsafe though; leave a couple minutes in between at
least.
Equity Tracking
Equity purchases are stored in the table stockpurchases. To keep this
up to date, run the task nightly.equity.php nightly. This task should
be run after the dtransactions rotation and after midnight.
Table Copies
The included task nightly.table.snapshot.php copies the contents
of table to backup tables. These are no substitute for a proper database
backup, but allow for retreiving a small bit of old information from
without loading up a backup of the entire database. Currently, it
copies products and custdata to productBackup and custdataBackup.