view release on metacpan or  search on metacpan

lib/App/Dochazka/REST/Model/Schedule.pm  view on Meta::CPAN

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

The 'disabled' field is intended go be used to control which schedules get
offered in, e.g., front-end dialogs when administrators choose which schedule
to assign to a new employee, and the like. For example, there may be schedules
in the database that were used in the past, but it is no longer desirable to 
offer these schedules in the front-end dialog, so the administrator can "remove"
them from the dialog by setting this field to 'true'.


=head3 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 C<schedintvls> table, with a record for each
time interval in the schedule. This table has triggers and a C<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 C<schedintvls>, it is 
"translated" (using a stored procedure) into a single JSON string, which is
stored in the C<schedules> table. This process is described in more detail below:  

First, if the schedule already exists in the C<schedules> table, nothing
more need be done -- we can skip to L<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 C<schedintvls>
table (sometimes referred to as the "scratch schedule" table because it is used
to store an intermediate product with only a short lifespan); (2) translate the
schedule to form the schedule's JSON representation; (3) insert the JSON string
into the C<schedules> table.

The C<schedintvls>, or "scratch schedule", table:

      CREATE SEQUENCE scratch_sid_seq;

      CREATE TABLE IF NOT EXISTS schedintvls (
        int_id  serial PRIMARY KEY,
        ssid    integer NOT NULL,
        intvl   tsrange NOT NULL,
        EXCLUDE USING gist (ssid WITH =, intvl WITH &&)
      )/,

As stated above, before the C<schedule> table is touched, a "scratch schedule"
must first be created in the C<schedintvls> table. Although this operation
changes the database, it should be seen as a "dry run". The C<gist> index and
a trigger assure that:

=over

=item * no overlapping entries are entered

=item * all the entries fall within a single 168-hour period

=item * all the times are evenly divisible by five minutes

=back

#
# FIXME: expand the trigger to check for "closed-open" C<< [ ..., ... ) >> tsrange
#

If the schedule is successfully inserted into C<schedintvls>, the next step is
to "translate", or convert, the individual intervals (expressed as tsrange
values) into the four-key hashes described in L<Schedules in the database>,
assemble the JSON string, and insert a new row in C<schedules>. 

To facilitate this conversion, a stored procedure C<translate_schedintvl> was
developed.

Successful insertion into C<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 C<schedintvls> table. 


=head2 Schedules in the Perl API


=head3 L<Schedintvls> class

=over 

=item * constructor (L<spawn>)

=item * L<reset> method (recycles an existing object)

=item * basic accessor (L<ssid>)

=item * L<intvls> accessor (arrayref containing all tsrange intervals in schedule) 

=item * L<schedule> accessor (arrayref containing "translated" intervals)

=item * L<load> method (load the object from the database and translate the tsrange intervals)

=item * L<insert> method (insert all the tsrange elements in one go)

=item * L<delete> method (delete all the tsrange elements when we're done with them)

=item * L<json> method (generate JSON string from the translated intervals)

=back

For basic workflow, see C<t/model/schedule.t>.


=head3 C<Schedule> class

=over

=item * constructor (L<spawn>)

=item * L<reset> method (recycles an existing object)

=item * basic accessors (L<sid>, L<schedule>, L<remark>)

=item * L<insert> method (inserts the schedule if it isn't in the database already)

=item * L<delete> method