Record Exists and reserveNewRecordId

records.php

<?php

class Record
{
    
    /**
     * DETERMINE IF A RECORD RESERVED (private method)
     * See if a record is reserved in the new_record_cache table
     *
     * @param       $project_id
     * @param       $record             
     * @param       $arm_id             
     * @return bool
     * @throws Exception
     */
    private static function recordReserved($project_id, $record, $arm_id)
    {
        // STEP 1: CHECK THE NEW_RECORD_CACHE FIRST FOR HIGH-HIT SCENARIOS
        $sql = sprintf("select 1 from redcap_new_record_cache
            where project_id = %d and arm_id = %d and record = '%s'",
            intval($project_id),
            intval($arm_id),
            db_escape($record)
        );
        $q   = db_query($sql);
        if (!$q) {
            throw new Exception("Unable to query redcap_new_record_cache - check your database connectivity");
        }
        return db_num_rows($q) > 0;
    }    

    
    /**
     * DETERMINE IF A RECORD EXISTS
     * (could also be made a public method / plugin method - that is why I supported null arm_id)
     * This method does NOT Guarantee that the record_id supplied is unique
     * and can be used - call the reserveRecordId method instead.
     *
     * @param       $project_id
     * @param       $record             
     * @param null  $arm_id             
     * @param bool  $useRecordListCache Use the cache table for record_id vs. query redcap_data
     * @return bool
     * @throws Exception
     */
    public static function recordExists($project_id, $record, $arm_id = null, $useRecordListCache = true)
    {

        global $Proj;
        /** @var \Project $P */
        $P = (empty($Proj) || $Proj->project_id !== $project_id) ? new \Project($Proj) : $Proj;

        $arm_id = empty($arm_id) ? $P->firstArmId : $arm_id;
        
        if (empty($arm_id) || empty($record)) {
            throw new \Exception ("Missing required arm_id or record missing");
        }
        
        // Check the record_list or redcap_data
        // since the new_record_cache doesn't include older records
        $recordListCacheStatus = \Records::getRecordListCacheStatus($project_id);
        ## USE RECORD LIST CACHE (if completed) (requires ARM)
        if ($useRecordListCache && $recordListCacheStatus == 'COMPLETE') {
            $sql = sprintf("select 1 from redcap_record_list
                where project_id = %d and arm_id = %d and record = '%s' limit 1",
                intval($project_id),
                intval($arm_id),
                db_escape($record)
            );
        } else {
            ## USE DATA TABLE (search within arm)
            $sql = sprintf("select 1 from redcap_data rd
                join redcap_events_metadata rem on rd.event_id = rem.event_id
                where rd.project_id = %d and rem.arm_id = %d
                and rd.record regexp '%s' and rd.field_name = '%s' limit 1",
                intval($project_id),
                intval($arm_id),
                db_escape($record),
                db_escape($P->table_pk)
            );
        }
        $q = db_query($sql);
        if (!$q) {
            throw new \Exception("Unable to query redcap_data for $record in project $project_id - check your database connectivity and system logs");
        }
        
        if (db_num_rows($q) > 0) {
            // Record is used
            return true;
        } else {
            // Record *probably* does not exist
            // (it could have been created as this method was running... use reserveNewRecordId to guarantee uniqueness)
            return false;
        }
    }


    /**
     * RESERVE A NEW RECORD ID
     * 
     * Try to reserve and guarantee a unique record_id in the project/arm.
     * If this returns true, the record id will be 'held' the new_record_cache table for ~1-3 hours 
     * You should write the record_id to redcap_data before that time to ensure it is still unique
     *
     * If the record is not saved before that time, it can be re-reserved in the future by another
     * process
     *
     * Event_id is optional.  If not supplied the first arm is assumed.
     * For multi-arm projects, you can provide any event_id from the intended record arm.  A record is only reserved
     * within its arm.  This will not guarantee the id is not used in other arms.
     *
     * @param      $project_id
     * @param      $record
     * @param null $event_id
     * @return bool
     * @throws \Exception
     */
    public static function reserveNewRecordId($project_id, $record, $event_id = null, $checkExistingRecords = false)
    {
        // Get the arm_id from the event_id
        /** @var \Project $P */
        global $Proj;
        $P = (empty($Proj) || $Proj->project_id !== $project_id) ? new \Project($Proj) : $Proj;
        if (empty($event_id)) $event_id = $P->firstEventId;
        $arm_id = $P->eventInfo[$event_id]['arm_id'];
        
        // See if it is already reserved - we could omit this, but seems better than the insert error
        if (self::recordReserved($project_id, $record, $arm_id)) return false;
        
        // Option to have this method also check existing record ids - the plugin method should default to this true
        if ($checkExistingRecords && self::recordExists($project_id, $record, $arm_id)) return false;
        
        // Attempt to insert into new_record_cache - if used by another process we will get a unique constraint violation
        $sql = sprintf("insert into redcap_new_record_cache
            (project_id, event_id, arm_id, record, creation_time)
            values (%d, %d, %d, '%s', '%s')",
            intval($project_id),
            intval($event_id),
            intval($arm_id),
            db_escape($record),
            db_escape(NOW)
        );
        if (db_query($sql)) {
            // Success
            return true;
        } else {
            // Duplicate or other error
            return false;
        }
    }

}