Exhibit C-3 Evidence: commit 854f62c
Committed · 854f62c0a71db46dae59e239e353f5ba13512ffe
The next free slot was found by reading every slot since 2019
The repository itself is private; this page carries the commit as it is recorded there.
Commit message
The change
Text files only, as recorded in git. Lines starting with + were added, lines with − removed.
Files changed
---
site/wp-content/plugins/hc-booking-slots/hc-booking-slots.php | 93 ++++++++++++++++++++++++++++---
tests/phpunit/repair/BookingSlotsTest.php | 78 ++++++++++++++++++++++++++
tests/phpunit/repair/Php8WarningsTest.php | 1 +
3 files changed, 164 insertions(+), 8 deletions(-)diff --git a/site/wp-content/plugins/hc-booking-slots/hc-booking-slots.php b/site/wp-content/plugins/hc-booking-slots/hc-booking-slots.php
index ed42f08..d520cea 100644
--- a/site/wp-content/plugins/hc-booking-slots/hc-booking-slots.php
+++ b/site/wp-content/plugins/hc-booking-slots/hc-booking-slots.php
@@ -2,11 +2,13 @@
/**
* Plugin Name: HC Booking Slots
* Description: Appointment slots synced from the practice calendar, and the "Next open appointment" line on the home page.
- * Version: 0.9.3
- * Author: Previous web developer (fictional)
+ * Version: 1.0.0
+ * Author: Hollis Creek Family Dental (fictional demo)
* Text Domain: hc-booking-slots
+ * Requires PHP: 8.1
*
- * Slots are stored as rows of post meta (_hcb_slot = "Y-m-d H:i|status") on one calendar post.
+ * Slots live in their own table, indexed by (status, slot_at), so the next open slot is one index lookup.
+ * Version 0.9 kept them as post meta rows; the repair migration moves them (see hc_booking_migrate()).
*
* @package HC\BookingSlots
*/
@@ -14,7 +16,17 @@
defined( 'ABSPATH' ) || exit;
/**
- * The calendar post type that holds the slots.
+ * The slots table.
+ *
+ * @return string
+ */
+function hc_booking_table() {
+ global $wpdb;
+ return $wpdb->prefix . 'hc_booking_slots';
+}
+
+/**
+ * The calendar post type that held the slots in version 0.9.
*/
function hc_booking_register() {
register_post_type(
@@ -27,6 +39,72 @@ function hc_booking_register() {
}
add_action( 'init', 'hc_booking_register' );
+/**
+ * Creates or updates the slots table.
+ */
+function hc_booking_install_table() {
+ global $wpdb;
+ require_once ABSPATH . 'wp-admin/includes/upgrade.php';
+ $table = hc_booking_table();
+ $charset = $wpdb->get_charset_collate();
+ dbDelta(
+ "CREATE TABLE {$table} (
+ id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ slot_at datetime NOT NULL,
+ status varchar(12) NOT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY slot_at (slot_at),
+ KEY status_slot (status,slot_at)
+ ) {$charset};"
+ );
+}
+register_activation_hook( __FILE__, 'hc_booking_install_table' );
+
+/**
+ * Repair migration: moves version 0.9's post meta slots into the table, then deletes the meta rows.
+ *
+ * @return string
+ */
+function hc_booking_migrate() {
+ global $wpdb;
+ hc_booking_install_table();
+ $table = hc_booking_table();
+ // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-off data move; the table name is ours.
+ $moved = (int) $wpdb->query(
+ "INSERT IGNORE INTO {$table} (slot_at, status)
+ SELECT STR_TO_DATE( SUBSTRING_INDEX( meta_value, '|', 1 ), '%Y-%m-%d %H:%i' ), SUBSTRING_INDEX( meta_value, '|', -1 )
+ FROM {$wpdb->postmeta} WHERE meta_key = '_hcb_slot'"
+ );
+ $deleted = (int) $wpdb->query( "DELETE FROM {$wpdb->postmeta} WHERE meta_key = '_hcb_slot'" );
+ $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
+ // phpcs:enable
+ return sprintf( 'moved %d slots out of post meta (%d meta rows deleted); table holds %d slots', $moved, $deleted, $total );
+}
+
+/**
+ * Registers the move with the repair migrations (wp hollis migrate).
+ *
+ * @param array $steps Steps.
+ * @return array
+ */
+function hc_booking_migration_step( $steps ) {
+ $steps['move-booking-slots-to-table'] = 'hc_booking_migrate';
+ return $steps;
+}
+add_filter( 'hollis_migrations_steps', 'hc_booking_migration_step' );
+
+/**
+ * The SQL that finds the next open slot (also used by the measurement script to EXPLAIN it).
+ *
+ * @param string $now Local time, Y-m-d H:i:s.
+ * @return string
+ */
+function hc_booking_next_open_sql( $now ) {
+ global $wpdb;
+ $table = hc_booking_table();
+ return $wpdb->prepare( "SELECT slot_at FROM {$table} WHERE status = 'open' AND slot_at >= %s ORDER BY slot_at ASC LIMIT 1", $now ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- our table name.
+}
+
/**
* The next open slot.
*
@@ -34,12 +112,11 @@ add_action( 'init', 'hc_booking_register' );
*/
function hc_booking_next_open() {
global $wpdb;
- $now = current_time( 'Y-m-d H:i' );
- $row = $wpdb->get_var( "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = '_hcb_slot' AND meta_value >= '$now' AND meta_value LIKE '%|open' ORDER BY meta_value ASC LIMIT 1" );
- if ( ! $row ) {
+ $slot = $wpdb->get_var( hc_booking_next_open_sql( current_time( 'mysql' ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- prepared in hc_booking_next_open_sql().
+ if ( ! $slot ) {
return null;
}
- $at = strtotime( substr( $row, 0, 16 ) );
+ $at = strtotime( $slot );
return array(
'label' => date_i18n( 'l j F', $at ),
'time' => date_i18n( 'g:i a', $at ),
diff --git a/tests/phpunit/repair/BookingSlotsTest.php b/tests/phpunit/repair/BookingSlotsTest.php
new file mode 100644
index 0000000..269ae5f
--- /dev/null
+++ b/tests/phpunit/repair/BookingSlotsTest.php
@@ -0,0 +1,78 @@
+<?php
+/**
+ * Exhibit C-3: the next open slot is found with an index, not by scanning every slot ever synced.
+ *
+ * @package Hollis\Tests
+ */
+
+/**
+ * Booking slots query.
+ */
+class BookingSlotsTest extends WP_UnitTestCase {
+
+ /**
+ * Earliest future open slot written by the fixture.
+ *
+ * @var string
+ */
+ private $first_open;
+
+ public function set_up() {
+ parent::set_up();
+ global $wpdb;
+ $wpdb->query( 'DROP TABLE IF EXISTS ' . hc_booking_table() ); // phpcs:ignore
+ $calendar = self::factory()->post->create( array( 'post_type' => 'hcb_calendar' ) );
+ $rows = array();
+ $start = strtotime( gmdate( 'Y-m-d 08:00', strtotime( '-400 days', current_time( 'timestamp' ) ) ) ); // phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp.Requested
+ $now = current_time( 'Y-m-d H:i' );
+ for ( $i = 0; $i < 6000; $i++ ) {
+ $at = gmdate( 'Y-m-d H:i', $start + $i * 6 * HOUR_IN_SECONDS );
+ $status = $at < $now ? 'booked' : ( 0 === $i % 5 ? 'open' : 'booked' );
+ if ( 'open' === $status && null === $this->first_open ) {
+ $this->first_open = $at;
+ }
+ $rows[] = $wpdb->prepare( '(%d, %s, %s)', $calendar, '_hcb_slot', $at . '|' . $status );
+ }
+ $wpdb->query( "INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value) VALUES " . implode( ',', $rows ) ); // phpcs:ignore
+ }
+
+ public function tear_down() {
+ global $wpdb;
+ $wpdb->query( 'DROP TABLE IF EXISTS ' . hc_booking_table() ); // phpcs:ignore
+ parent::tear_down();
+ }
+
+ public function test_migration_moves_every_slot_out_of_post_meta() {
+ global $wpdb;
+ $results = hollis_migrations_run();
+ $this->assertStringContainsString( 'moved 6000 slots', $results['move-booking-slots-to-table'] );
+ $this->assertSame( '0', $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = '_hcb_slot'" ) ); // phpcs:ignore
+ $this->assertSame( '6000', $wpdb->get_var( 'SELECT COUNT(*) FROM ' . hc_booking_table() ) ); // phpcs:ignore
+ }
+
+ public function test_next_open_slot_is_the_earliest_future_open_one() {
+ hollis_migrations_run();
+ $slot = hc_booking_next_open();
+ $this->assertNotNull( $slot );
+ $at = strtotime( $this->first_open );
+ $this->assertSame( date_i18n( 'g:i a', $at ), $slot['time'] );
+ $this->assertSame( date_i18n( 'l j F', $at ), $slot['label'] );
+ }
+
+ public function test_query_uses_the_status_slot_index_and_reads_one_row() {
+ global $wpdb;
+ hollis_migrations_run();
+ $wpdb->query( 'ANALYZE TABLE ' . hc_booking_table() ); // phpcs:ignore
+ $plan = json_decode( $wpdb->get_var( 'ANALYZE FORMAT=JSON ' . hc_booking_next_open_sql( current_time( 'mysql' ) ) ), true ); // phpcs:ignore
+ $table = $plan['query_block']['table'] ?? $plan['query_block']['nested_loop'][0]['table'];
+ $this->assertSame( 'status_slot', $table['key'] );
+ $this->assertLessThanOrEqual( 2, $table['r_rows'] );
+ }
+
+ public function test_running_again_changes_nothing() {
+ hollis_migrations_run();
+ $again = hollis_migrations_run();
+ $this->assertStringContainsString( 'moved 0 slots', $again['move-booking-slots-to-table'] );
+ $this->assertStringContainsString( 'holds 6000 slots', $again['move-booking-slots-to-table'] );
+ }
+}
diff --git a/tests/phpunit/repair/Php8WarningsTest.php b/tests/phpunit/repair/Php8WarningsTest.php
index ffae7a5..2f79920 100644
--- a/tests/phpunit/repair/Php8WarningsTest.php
+++ b/tests/phpunit/repair/Php8WarningsTest.php
@@ -26,6 +26,7 @@ class Php8WarningsTest extends WP_UnitTestCase {
}
public function test_home_page_renders_when_no_slot_is_open() {
+ hc_booking_install_table();
$this->go_to( home_url( '/' ) );
ob_start();
include get_stylesheet_directory() . '/front-page.php';The proving test
tests/phpunit/repair/BookingSlotsTest.php
tests/phpunit/repair/BookingSlotsTest.php, as committed.
<?php
/**
* Exhibit C-3: the next open slot is found with an index, not by scanning every slot ever synced.
*
* @package Hollis\Tests
*/
/**
* Booking slots query.
*/
class BookingSlotsTest extends WP_UnitTestCase {
/**
* Earliest future open slot written by the fixture.
*
* @var string
*/
private $first_open;
public function set_up() {
parent::set_up();
global $wpdb;
$wpdb->query( 'DROP TABLE IF EXISTS ' . hc_booking_table() ); // phpcs:ignore
$calendar = self::factory()->post->create( array( 'post_type' => 'hcb_calendar' ) );
$rows = array();
$start = strtotime( gmdate( 'Y-m-d 08:00', strtotime( '-400 days', current_time( 'timestamp' ) ) ) ); // phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp.Requested
$now = current_time( 'Y-m-d H:i' );
for ( $i = 0; $i < 6000; $i++ ) {
$at = gmdate( 'Y-m-d H:i', $start + $i * 6 * HOUR_IN_SECONDS );
$status = $at < $now ? 'booked' : ( 0 === $i % 5 ? 'open' : 'booked' );
if ( 'open' === $status && null === $this->first_open ) {
$this->first_open = $at;
}
$rows[] = $wpdb->prepare( '(%d, %s, %s)', $calendar, '_hcb_slot', $at . '|' . $status );
}
$wpdb->query( "INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value) VALUES " . implode( ',', $rows ) ); // phpcs:ignore
}
public function tear_down() {
global $wpdb;
$wpdb->query( 'DROP TABLE IF EXISTS ' . hc_booking_table() ); // phpcs:ignore
parent::tear_down();
}
public function test_migration_moves_every_slot_out_of_post_meta() {
global $wpdb;
$results = hollis_migrations_run();
$this->assertStringContainsString( 'moved 6000 slots', $results['move-booking-slots-to-table'] );
$this->assertSame( '0', $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = '_hcb_slot'" ) ); // phpcs:ignore
$this->assertSame( '6000', $wpdb->get_var( 'SELECT COUNT(*) FROM ' . hc_booking_table() ) ); // phpcs:ignore
}
public function test_next_open_slot_is_the_earliest_future_open_one() {
hollis_migrations_run();
$slot = hc_booking_next_open();
$this->assertNotNull( $slot );
$at = strtotime( $this->first_open );
$this->assertSame( date_i18n( 'g:i a', $at ), $slot['time'] );
$this->assertSame( date_i18n( 'l j F', $at ), $slot['label'] );
}
public function test_query_uses_the_status_slot_index_and_reads_one_row() {
global $wpdb;
hollis_migrations_run();
$wpdb->query( 'ANALYZE TABLE ' . hc_booking_table() ); // phpcs:ignore
$plan = json_decode( $wpdb->get_var( 'ANALYZE FORMAT=JSON ' . hc_booking_next_open_sql( current_time( 'mysql' ) ) ), true ); // phpcs:ignore
$table = $plan['query_block']['table'] ?? $plan['query_block']['nested_loop'][0]['table'];
$this->assertSame( 'status_slot', $table['key'] );
$this->assertLessThanOrEqual( 2, $table['r_rows'] );
}
public function test_running_again_changes_nothing() {
hollis_migrations_run();
$again = hollis_migrations_run();
$this->assertStringContainsString( 'moved 0 slots', $again['move-booking-slots-to-table'] );
$this->assertStringContainsString( 'holds 6000 slots', $again['move-booking-slots-to-table'] );
}
}