Because of COVID-19 related restrictions our Churches are operating under rules which drastically reduce their capacity. Our local parish church, for example, would usually sit 200+ comfortably; we can currently only fit in 30-40 people within the social distancing requirements.

We therefore needed a booking system to allow people to ensure they had seats for Mass, and also to collect tracing information so that they can be contacted in the event that someone who subsequently tests positive was at the same Mass. It made sense to create a system which could be used across our entire Diocese - 150+ churches, of which 50-ish are open at this stage.

I've used CouchCMS to create this.

• Extended Users deals with sign-up and user management.
• An additional field for users allows them to choose their local parishes to take a step out of the booking process for them.
• A further additional field lets them be set as 'stewards' for their local church, with the ability to view and print booking lists for use as people arrive
• Cloned pages hold details for Churches, including capacities for Mass, map details and websites.
• Cloned pages also hold details of Mass times (such as '10am on Sunday at [x] church'.
• A cron job creates individual Mass records for the coming week.
• The booking process uses a form and db_persist to create a 'booking' cloned page, with relationships to the Mass record and the user and containing the number of seats - or a family pew - booked.
• Cron jobs also delete data after 21 days (the limit for tracing requirements) to comply with GDPR as data is then no longer needed.
• basic editable regions allow the content of the main fairly static pages to be managed

Every part of this has been achieved with standard Couch functionality, or odd bits from the forums.

It's at https://massbooking.uk. Please do not sign up for accounts or book places, unless of course you are within the Diocese of Nottingham (if you need to know more, feel free to contact me). If any CouchCMS users are within the Diocese, I'd love to hear from you as you could help with some maintenance!

[trendoman]

Cool! Would you be willing to shed some light upon the process of developing with Couch?

Like what was your initial experience before starting this project, how much time the Couch part took to completion and what did you learn in process - these are some example talk points that, by all means, would be an interesting read for the visitors.

Please do find time to post a bit from behind the scenes - it will really help to connect to the showcased website. Moreover it is a coding forum Thanks!

Now here is some tests, if I may.

Lightweight pages do okay:

Home page (https://massbooking.uk/) https://www.dareboost.com/en/report/a_1 ... 419bdf4531

A cloned page (https://massbooking.uk/parish.php?p=835) a tad worse with almost 4 seconds to load https://gtmetrix.com/reports/massbooking.uk/Ya3lhig7

Worst of all is the implementation of the heaviest page (with related_pages, I suppose?) for concurrent requests for visitors from opened 50+ churches https://www.webpagetest.org/result/2008 ... ee946fa9b/

Screenshot_2020-08-03 WebPageTest Test Result - Dulles massbooking uk parish php - 08 03 20 16 50 56.png (54.54 KiB) Viewed 16768 times

I encourage you to read existing forum topics about speeding up and optimizations.

Those performance times looked distinctly odd, but I think I've found two issues, so thanks for pointing them out! This thing really was far more of a rush job than I wanted it to be, mainly because Government requirements were not exactly crystal clear....

The parish listing page is one of the simplest on the site - it contains:

• A single <cms:related_pages> bringing back parishes related to the logged in user (ie Favorited parishes), then
• a <cms:folders> over ten folders, with a <cms:pages> within each loop

and a very small amount of processing to add classes so that Javascript on the front end can handle showing open/all/related parishes.

However, I've just realised that I wasn't actually testing for a logged in user before doing the related_pages call. Fixing that seems to have brought the response time down significantly - pingdom (using UK servers) reports under 0.7s now for the listing page to load.

An individual parish page loads in pingdom in under half a second, with some of that taken up by the map. WebPageTest seems far more conservative on time for loading the map.

I also need to stop the CKEditor being loaded where it's not needed!

I have found myself wishing for a Couch function which would return pages together with related data in one database call at times, especially where the relationship is singular! I've not got into any optimisations involving <cms:query>, really since I can't find any straightforward explanation of how to build the queries.

I'll try to find time for a bit of a more discursive post on the process and so on at some point....

So, following up @trendoman 's comment about optimisation - this is the step that is probably slow for a specific parish page:

• I have a template "masses.php" which contains fields for the numbers of pews and individual seats available for a specific Mass
• I have a template "bookings.php" which contains a relationship to one of these Masses, and fields for the numbers of pews and individual seats booked
• Each time a booking is made, a "bookings.php" page is created
• So to find out the remaining capacity for each Mass, I have to do this:

Code: Select all

<cms:set pews_free = "<cms:show mass_capacity_pew />" 'global'/>
<cms:set indiv_free = "<cms:show mass_capacity_indiv />" 'global'/>

<cms:reverse_related_pages 'booking_mass' masterpage='bookings.php'>
   <cms:if booking_places_pew >
      <cms:set pews_free = "<cms:sub pews_free "<cms:show booking_places_pew/>" />" 'global'/>
   </cms:if>                        
   <cms:if booking_places_indiv >
      <cms:set indiv_free = "<cms:sub indiv_free "<cms:show booking_places_indiv/>" />" 'global'/>
   </cms:if>
</cms:reverse_related_pages>

I hope it's clear which fields are in each template - I've been quite careful to use a consistent labelling format

This happens for each Mass listed for a church over the next seven days, which may be quite a few.

It would be better to optimise the calculation step by a SQL query to return (using sort-of-SQL code):

Code: Select all

SELECT SUM(booking_places_pew) as pews_booked, SUM(booking_places_indiv) as indiv_booked 
FROM bookings 
WHERE booking_mass = [this mass]

I haven't been able to work out from the examples in the forums how to make that happen with the Couch data structure, though, and I'd appreciate any help to do so.

[trendoman]

Well, Couch is not (yet?) a framework to build custom SQL queries. It gives you tools to query database and use results, if you know bits of query language. Couch provides a learning playground though - pages tag can return ready sql query with parameter return_sql='1'. Combine that with enhanced capabilities of pages tag (viewtopic.php?f=5&t=8581) and you can see examples of most complicated queries that involve relations. Believe me, once you get a grasp of it, adding SUM is not a problem at all.

Sometimes, optimization can be made in the form of removing redundant calculations completely. If you can sub the booked number from the mass page when the booking is created, then mass page will always have the available amount of seats. Does it make sense?

I'll have a trawl through some queries and see what I can do.

The problem with subtracting the totals as bookings are made is that bookings can also be deleted - either within the system by a user, or from the admin panel if there is a difficulty with a user account. Deletions within the system could be handled, but deletions in the admin panel would be more troublesome.

It's also possible for the capacity to change, especially as churches are first opening and working out how they can manage seats etc.

[trendoman]

Please post your findings with query language. I'll be happy to follow your progress.

I can see that booking is not something set in stone.
However, roadblocks are only temporary while gains are permanent.

Absence of complex aggregation and calculations will surely avoid DoS and sets your app on a future-proof and flood-proof rails (in this part, at least). In other words, let's stick to the best solution and find the ways to get to it. If you are with me on that, the page can have 2 fields, field #1 is available number, can be adjusted freely by admins as they go and field #2 is the increasing number of booked seats, read-only for admins. Fetch both and do a sub #1 - #2 = result. This is trivial.

Deletions, on the contrary, is truly a roadblock, but Couch processes deletions via common interface a function delete in page.php:1547 that is called from wherever we delete a page (admin panel or in code via tag db_delete). Thanks to Couch events subsystem, we can track this event

Code: Select all

// HOOK: page_deleted
$FUNCS->dispatch_event( 'page_deleted', array(&$this) );

Check this thread viewtopic.php?f=4&t=11828#p32000 to see an example of working with events. (I found it among many with search request https://www.google.com/search?q=site%3A ... t_listener )
That ex. shows some action done on page_saved (i.e. when a page is saved ).

The logic for your app could be as follows - when a booking is deleted without errors (and changes are about to be committed to database), get the booked seats field's value and sub it from the field #2 of related mass page.

Fetching a field from related page may sound complicated but with various good examples in forum (like this one viewtopic.php?f=4&t=11513#p30704 ) it is a manageable task for a coder. Tasks like this are a good practice for advanced work with Couch.

Hmm. I'm not getting far with this!

To get the numbers of individual bookings for all bookings for each mass at a specific parish (the one with page ID 945, for test purposes), I need to do this:

Code: Select all

<cms:pages masterpage='parish.php' id="945" >
<cms:show parish_name /><cms:show k_page_id /><br />
    <cms:reverse_related_pages 'mass_parish' masterpage='masses.php' orderby='mass_date_time' order='asc'>
    <cms:show k_page_name/><br />
        <cms:reverse_related_pages 'booking_mass' masterpage='bookings.php'>
            <cms:show k_page_name /> : <cms:show booking_places_indiv /><br />
        </cms:reverse_related_pages> 
    </cms:reverse_related_pages>
</cms:pages>

That's giving me the list I'd expect.

So I add return_sql='1' to the innermost reverse_related_pages tag (the one on masterpage 'bookings.php'), and it returns:

Code: Select all

SELECT p.id, p.template_id FROM couch_pages p
 inner join couch_relations rel on rel.pid = p.id 
WHERE rel.cid=2084 AND rel.fid=43 AND p.template_id='18' 
AND p.publish_date < '2020-08-12 21:13:45' 
AND NOT p.publish_date = '0000-00-00 00:00:00' 
AND p.parent_id=0 ORDER BY rel.weight desc LIMIT 0, 1000

Examining the database, using phpmyadmin, I put in

Code: Select all

SELECT * FROM `couch_data_text` WHERE page_id='2562'

..knowing that 2562 is the page ID of a booking. The result tells me that fields 76 and 77 hold my numbers (as text) for the booked places (individual and pews respectively).

So I try to get the total on one of those fields, with the sql provided by the reverse_related_pages tag and an inner join to couch_data_text, and omitting the order and limits which shouldn't be relevant here:

Code: Select all

            SELECT SUM(CAST(`value` as int)) AS indiv_total, p.id, p.template_id 
                FROM 
                    couch_pages p 
                    INNER JOIN couch_data_text d ON d.page_id=p.id AND d.field_id='76' 
                    INNER JOIN couch_relations rel on rel.pid = p.id 
                WHERE 
                    rel.cid=2084
                    AND rel.fid=43 
                    AND p.template_id='18' 
                    AND p.publish_date < '2020-08-12 21:13:45' 
                    AND NOT p.publish_date = '0000-00-00 00:00:00' 
                    AND p.parent_id=0 

with help from the discussion at viewtopic.php?f=4&t=11255&hilit=sum#p29580

..but entering that into phpmyadmin, I'm getting a null response - and the same, naturally, within a cms:query tag.

Am I missing something?

[trendoman]

Keep going This path requires a thorough refreshement of SQL.

with help from the discussion at viewtopic.php?f=4&t=11255&hilit=sum#p29580

That topic is not an authoriatave source, I am afraid. OP does not have it right either (I presume his code is different now and seemingly he abandoned that topic).

Unfortunately at the moment I really don't have much time to get into in-depth study of SQL. I'm almost single-handedly managing and supporting a website handling thousands of bookings from users at around 50 locations, with more locations coming into the system pretty well daily!

But I did manage to get there today. The key point was that I had the SQL in the wrong place - I'd left in an unnecessary reverse_related_pages, so the page_id was the wrong one. Then the value filed needed qualifying as coming from the right table.

Ideally I wanted both the totals at once. A second inner join onto couch_data_text with a different alias sorted that.

So, possibly final version:

Code: Select all

  SELECT SUM(CAST(d.value as int)) AS indiv_total, SUM(CAST(e.value as int)) AS pew_total 
        FROM 
            couch_pages p 
            INNER JOIN couch_data_text d ON d.page_id=p.id AND d.field_id='76'
            INNER JOIN couch_data_text e ON e.page_id=p.id AND e.field_id='77'
            INNER JOIN couch_relations rel on rel.pid = p.id 
        WHERE 
            rel.cid='<cms:show mass_id />' 
            AND rel.fid='43' 
            AND p.template_id='18' 
            AND NOT p.publish_date = '0000-00-00 00:00:00' 
            AND p.parent_id=0 

I say 'possibly final' as I need to test this thoroughly against the previous calculations to make sure there are no differences. For that I'm going to output the relevant figures - using each calculation in turn - for a significant sample of the events in the system, using pagination to avoid building too long a task at once, into a table, with flags shown if there are any discrepancies. if there are none, then it's job done.