NAME
App::Dochazka::REST::Model::Schedule - schedule functions
VERSION
Version 0.080
SYNOPSIS
use App::Dochazka::REST::Model::Schedule;
...DESCRIPTION
A description of the schedule data model follows.
Schedules in the database
Table
Schedules are stored the schedules table. For any given schedule, there is always only one record in the table -- i.e., individual schedules can be used for multiple employees. (For example, an organization might have hundreds of employees on a single, unified schedule.)
CREATE TABLE IF NOT EXISTS schedules (
sid serial PRIMARY KEY,
schedule text UNIQUE NOT NULL,
remark text
);The value of the 'schedule' field is a JSON array which looks something like this:
[
{ low_dow:"MON", low_time:"08:00", high_dow:"MON", high_time:"12:00" ],
{ low_dow:"MON", low_time:"12:30", high_dow:"MON", high_time:"16:30" ],
{ low_dow:"TUE", low_time:"08:00", high_dow:"TUE", high_time:"12:00" ],
{ low_dow:"TUE", low_time:"12:30", high_dow:"TUE", high_time:"16:30" ],
...
] Or, to give an example of a more convoluted schedule:
[
{ low_dow:"WED", low_time:"22:15", high_dow:"THU", high_time:"03:25" ],
{ low_dow:"THU", low_time:"05:25", high_dow:"THU", high_time:"09:55" ],
{ low_dow:"SAT", low_time:"19:05", high_dow:"SUN", high_time:"24:00" ]
] The intervals in the JSON string must be sorted and the whitespace, etc. must be consistent in order for the UNIQUE constraint in the 'schedule' table to work properly. However, these precautions will no longer be necessary after PostgreSQL 9.4 comes out and the field type is changed to 'jsonb'.
Process for creating new schedules
It is important to understand how the JSON string introduced in the previous section is assembled -- or, more generally, how a schedule is created. Essentially, the schedule is first created in a schedintvls table, with a record for each time interval in the schedule. This table has triggers and a gist index that enforce schedule data integrity so that only a valid schedule can be inserted. Once the schedule has been successfully built up in schedintvls, it is "translated" (using a stored procedure) into a single JSON string, which is stored in the schedules table. This process is described in more detail below:
First, if the schedule already exists in the schedules table, nothing more need be done -- we can skip to Schedhistory
If the schedule we need is not yet in the database, we will have to create it. This is a three-step process: (1) build up the schedule in the schedintvls table (sometimes referred to as the "scratch schedule" table); (2) translate the schedule to form the schedule's JSON representation; (3) insert the JSON string into the schedules table.
The schedintvls, or "scratch schedule", table:
CREATE SEQUENCE scratch_sid_seq;
CREATE TABLE IF NOT EXISTS schedintvls (
scratch_sid integer NOT NULL,
intvl tsrange NOT NULL,
EXCLUDE USING gist (scratch_sid WITH =, intvl WITH &&)
);As stated above, before the schedule table is touched, a "scratch schedule" must first be created in the schedintvls table. Although this operation changes the database, it should be seen as a "dry run". The gist index and a trigger assure that:
no overlapping entries are entered
all the entries fall within a single 168-hour period
all the times are evenly divisible by five minutes
# # FIXME: expand the trigger to check for "closed-open" [ ..., ... ) tsrange #
If the schedule is successfully inserted into schedintvls, the next step is to "translate", or convert, the individual intervals (expressed as tsrange values) into the four-key hashes described in "Schedules in the database", assemble the JSON string, and insert a new row in schedules.
To facilitate this conversion, a stored procedure translate_schedintvl was developed.
Successful insertion into schedules will generate a Schedule ID (SID) for the schedule, enabling it to be used to make Schedhistory objects.
At this point, the scratch schedule is deleted from the schedintvls table.
Schedules in the Perl API
Schedintvls class
constructor (spawn)
reset method (recycles an existing object)
basic accessors (scratch_sid and remark)
intvls accessor (arrayref containing all tsrange intervals in schedule)
schedule accessor (arrayref containing "translated" intervals)
load method (load the object from the database and translate the tsrange intervals)
insert method (insert all the tsrange elements in one go)
delete method (delete all the tsrange elements when we're done with them)
json method (generate JSON string from the translated intervals)
For basic workflow, see t/007-schedule.t.
Schedule class
constructor (spawn)
reset method (recycles an existing object)
insert method (inserts the schedule if it isn't in the database already)
delete method
load method (not implemented yet)
get_json function (get JSON string associated with a given SID)
For basic workflow, see t/007-schedule.t.
EXPORTS
This module provides the following exports:
get_json
METHODS
spawn
Constructor. See Employee.pm->spawn for general comments.
reset
Instance method. Resets object, either to its primal state (no arguments) or to the state given in PARAMHASH.
Accessor methods
Basic accessor methods for all the fields of Schedule table. These functions return whatever value happens to be associated with the object, with no guarantee that it matches the database.
sid
Accessor method.
schedule
Accessor method.
remark
Accessor method.
insert
Instance method. Attempts to INSERT a record into the 'schedules' table. Field values are taken from the object. Returns a status object.
update
There is no update method for schedules. To update a schedule, delete it and then re-create it (see Spec.pm for a description of how to do this, or refer to t/007-schedule.t).
delete
Instance method. Attempts to DELETE a schedule record. This may succeed if no other records in the database refer to this schedule.
load_by_sid
Given a SID, load the schedule into the object. Returns a status value.
FUNCTIONS
get_json
Given a database handle and a SID, queries the database for the JSON string associated with the SID. Returns undef if not found.
AUTHOR
Nathan Cutler, <presnypreklad@gmail.com>