sdr/update/SDRServiceCache.php5

<?php

abstract class SDRServiceCache extends SObject {

    public static function updateResourceCache($identifier) {
        $version = SDRService::getVersion($identifier);
        $resource = SDRService::getResource($identifier);
        // If there was an error or if the resource doesn't exist, return false
        if ($version === false || !$resource)
            return false;
        // If there is no approved version, destroy the cache
        if ($version === null) {
            self::deleteSearchCaches($resource->cserdId);
            return null;
        }
        // If there is an approved version, update the cache
        $success = true;
        if (!self::updateSearchWordCache($version)) {
            self::setStaticWarning('Error updating quick search cache.');
            $success = false;
        }
        if (self::updateResourceCacheHelper($resource) === false) {
            self::setStaticWarning('Error updating resource cache.');
            $success = false;
        }
        return $success;
    }

    public function updateAllResourceCaches() {
        $DBI = SDRService::getDBI();
        // Clear and re-cache all resources
        $rsrcIdQuery = 'SELECT cserdId from SDRResource';
        $results = $DBI->query($rsrcIdQuery);
        if (!$results)
            return false;
        foreach($results as $result) {
            $id = $result['cserdId'];
            if (!self::updateResourceCache($id)) {
                self::setStaticWarning('Could not update cache for id ' . $id);
            }
        }
        return true;
    }

    public static function updateAllCaches() {
        self::updateValueCache();
        self::updateAllResourceCaches();
    }

    public static function updateValueCache() {
        $DBI = SDRService::getDBI();
        // Clear and re-cache all values
        $valCacheQuery = 'TRUNCATE SDRValueCache';
        $DBI->query($valCacheQuery);
        $valCacheQuery = 'INSERT INTO SDRValueCache '
                        . 'SELECT valueId, entry from SDRTextValue '
                        . 'UNION SELECT valueId, entry from SDRDateValue '
                        . 'UNION SELECT valueId, entry from SDRIntValue;';
        $DBI->query($valCacheQuery);
        return true;
    }

    public function updateSearchWordCache($version) {
        if (!($version instanceOf SDRVersion)) {
            self::setStaticError('Version provided is not an SDRVersion object');
            return false;
        }
        $DBI = SDRService::getDBI();
        $skippers = array('the', 'and', 'to', 'of', 'a', 'in', 'is', 'it', 'or');
        $wordSplitPattern = "/[\s\,\/\\\.\:\;\!]+/";
        $cserdId = $version->cserdId;
        $counts = array();
        $fields = array();
        $vfvs = SDRVersionFieldValue::getList(array('versionId' => $version->id));
        if (!$vfvs)
            return false;
        foreach($vfvs as $vfv) {
            $val = new SDRValue(array('id' => $vfv->valueId));
            if (!$val) {
                self::setStaticWarning('Could not populate value with id ' . $vfv->valueId
                                        . ' for caching.');
                continue;
            }
            $words = preg_split($wordSplitPattern, $val->getValue());
            //$words = explode(array(' ', '/', '.', ','), $val->getValue());
            foreach ($words as $word) {
                // trim the word to remove extra spaces
                $word = trim($word);
                if ($word == '')
                    continue;
                $word = strtolower($word);
                // skip word if it is so common as to hold no relevance
                if (in_array($word, $skippers))
                    continue;
                // add an index for storing the word
                if (!isset($counts[$word])) {
                    $counts[$word] = array();
                }
                // increment or add index for storing field-to-word binding
                if (!isset($counts[$word][$vfv->fieldId])) {
                    $counts[$word][$vfv->fieldId] = 1;
                    $fields[$vfv->fieldId] = true;
                } else {
                    $counts[$word][$vfv->fieldId]++;
                }
            }
        }

        // Delete any existing cache for this resource
        if (!self::deleteSearchWordCache($cserdId))
            return false;

        // Insert new cache entries
        foreach($counts as $word => $fwcount) {
            // Find or insert search words
            $select = 'SELECT id FROM SDRSearchWord WHERE word="'
                                            . mysql_escape_string($word) . '";';
            //print($select);
            $result = $DBI->query($select);
            if (!$result)
                return false;
            if ($result->numRows() > 0) {
                $row = $result->getRow(0);
                $wordId = $row['id'];
            } else {
                $insert = 'INSERT INTO SDRSearchWord SET word="'
                                            . mysql_escape_string($word) . '";';
                //print($insert);
                $result = $DBI->query($insert);
                if (!$result)
                    return false;
                $wordId = $result->getInsertId();
            }
            if ($wordId == '') {
                self::setStaticError('Error finding/generating a wordId for caching.');
                return false;
            }
            // Insert individual cache entries mapping resource/word/field/count
            foreach($fwcount as $fieldId => $wcount) {
                $query = 'REPLACE INTO SDRSearchWordCount SET cserdId="' . $cserdId . '", '
                            . 'fieldId="' . $fieldId .'", searchWordId="' . $wordId . '", '
                            . 'count="' . $wcount . '"';
                if (!$DBI->query($query))
                    return false;
            }
        }
        return true;
    }

    public function updateResourceCacheHelper($mixed) {
        $DBI = SDRService::getDBI();
        // Get a resource object from the input
        if ($mixed instanceOf SDRResource) {
            $res = $mixed;
        } else {
            $res = SDRService::getResource($mixed);
        }
        if (!$res) {
            self::setStaticError('Could not find resource ' . $mixed);
            return false;
        }
        //Get all versions for this resource and update the cache accordingly
        $verList = SDRVersion::getList(array('cserdId' => $res->cserdId, 
                                'or'=>array('state'=>array('live','dev','pending'))));
        if (count($verList) == 0) {
            // If there is no active version of the metadata available, remove the CSERD ID
            // from the cache table (likely means that resource has no live or dev version).
            if (!self::deleteResourceCache($res->cserdId))
                return false;
            return null;
        }

        // Build Version updating SQL
        $versionStates = array();
        foreach ($verList as $updateVersion) {
            $versionStates[$updateVersion->state] = $updateVersion->id;
            
            $md = $updateVersion->getMetadata();
            $title = $description = '';
            if ($md->attributeExists('Title'))
                $title = $md->Title[0];
            if ($md->attributeExists('Description'))
                $description = $md->Description[0];

            // Insert an updated entry for each version
            $query = 'REPLACE INTO SDRVersionCache SET '
                        . 'cserdId="' . $res->cserdId . '", '
                        . 'versionId="' . $updateVersion->id . '", '
                        . 'Title="' . mysql_escape_string($title) . '", '
                        . 'Description="' . mysql_escape_string($description) . '", '
                        . 'MetadataXMLCache="' . $md->getXML() . '", '
                        . 'created = "' . $updateVersion->created . '", '
                        . 'modified = "' . $updateVersion->modified . '";';
            if (!$DBI->query($query))
                return false;
        }
        
        // Build resource cache links
        $versionToUpdate = '';
        if (array_key_exists(SDRVersion::STATE_LIVE, $versionStates)) {
            $versionToUpdate .= ', liveVersionId="' . $versionStates[SDRVersion::STATE_LIVE] . '"';
            // If there's a live version but not dev, put live id in dev cache
            if (!array_key_exists(SDRVersion::STATE_DEV, $versionStates)) {
                $versionToUpdate .= ', devVersionId="' . $versionStates[SDRVersion::STATE_LIVE] . '"';
                // If there's also not pending, put live id in pending cache
                if (!array_key_exists(SDRVersion::STATE_PENDING, $versionStates))  
                    $versionToUpdate .= ', pendingVersionId="' . $versionStates[SDRVersion::STATE_LIVE] . '"';
            }
        } 
        if (array_key_exists(SDRVersion::STATE_DEV, $versionStates)) {
            $versionToUpdate .= ', devVersionId="' . $versionStates[SDRVersion::STATE_DEV] . '"';
            // If there's a dev version but not pending, put dev id in pending cache
            if (!array_key_exists(SDRVersion::STATE_PENDING, $versionStates))
                $versionToUpdate .= ', pendingVersionId="' . $versionStates[SDRVersion::STATE_DEV] . '"';
        }
        if (array_key_exists(SDRVersion::STATE_PENDING, $versionStates)) {
            $versionToUpdate .= ', pendingVersionId="' . $versionStates[SDRVersion::STATE_PENDING] . '"';
        }
        
        $project = $res->getParentPrimaryProject();
        $projectName = $project->name;
        
        // Insert/replace an updated version of the cache for this resource/active version combo
        $query = 'REPLACE INTO SDRResourceCache SET '
                    . 'cserdId="' . $res->cserdId . '", '
                    . 'releaseFlag="' . $res->releaseFlag . '", '
                    . 'Url="' . mysql_escape_string($res->url) . '", '
                    . 'AverageReviewRating="' . $res->AverageReviewRating . '", '
                    . 'ShodorProject="' . $projectName . '", '
                    . 'Hierarchy="0", '
                    . 'created = "' . $res->created . '"'
                    . $versionToUpdate . ';';
        if (!$DBI->query($query))
            return false;
        return true;
        

    }

    public static function updateAverageReviewRating($cserdId) {
        $CSERD_DBI = CSERD::getDBI();
        $query = 'SELECT AVG(response.response+1) AS average FROM response '
                . 'LEFT JOIN question ON response.questionId = question.questionId '
                . 'LEFT JOIN review ON response.reviewId = review.reviewId '
                . 'WHERE question.ref = \'usability\' '
                . 'AND review.state = "published" '
                . 'AND review.active = 1 '
                . 'AND review.cserdId = "' . $cserdId . '";';
        $result = $CSERD_DBI->query($query);

        if (!$result)
            return false;
        if (count($result) == 0)
            $average = 0;
        else
            $average = $result[0]['average'];
        if ($average == null)
            $average = 0;

        $SDR_DBI = SDR::getDBI();
        $query = 'UPDATE SDRResource '
                    . 'SET AverageReviewRating = "' . $average . '" '
                    . 'WHERE cserdId = "' . $cserdId . '";';
        $SDR_DBI->query($query);
        if ($SDR_DBI->hasError())
            return false;

        $query = 'UPDATE SDRResourceCache '
                    . 'SET AverageReviewRating = "' . $average . '" '
                    . 'WHERE cserdId = "' . $cserdId . '";';
        $SDR_DBI->query($query);
        
        if ($SDR_DBI->hasError())
            return false;
        return true;
    }

    public static function deleteResourceCache($cserdId) {
        $DBI = SDRService::getDBI();
        $query = 'DELETE FROM SDRResourceCache WHERE cserdId="'
                            . mysql_escape_string($cserdId) . '"';
        if (!$DBI->query($query)){
            self::setStaticError('Could not delete resource cache.');
            return false;
        }
        return true;
    }

    public static function deleteSearchWordCache($cserdId) {
        $DBI = SDRService::getDBI();
        $delete = 'DELETE FROM SDRSearchWordCount WHERE cserdId="'
                            . mysql_escape_string($cserdId) . '";';
        if (!$DBI->query($delete))
            return false;
        return true;
    }

    public static function deleteSearchCaches($cserdId) {
        return (self::deleteResourceCache($cserdId) && self::deleteSearchWordCache($cserdId));
    }

    public function updateResourceVersionCaches($resource, $version) {
        // Update search words and resource cache
        $status = true;
        if (!self::updateSearchWordCache($version)) {
            self::setStaticWarning('Error updating quick search cache.');
            $status = false;
        }
        if (self::updateResourceCacheHelper($resource) === false) {
            self::setStaticWarning('Error updating resource cache.');
            $status = false;
        }
        if (!self::updateValueCache()) {
            self::setStaticWarning('Error updating value cache.');
            $status = false;
        }
        return $status;
    }
    
    // Update all the resources that have a particular value associated with them
    public static function updateCacheFromValue($value) {
        $DBI = SDRService::getDBI();
        $query = "SELECT distinct SDRVersion.cserdId 
                    FROM SDRVersionFieldValue 
                    LEFT JOIN SDRVersion ON SDRVersion.id = SDRVersionFieldValue.versionId
                    WHERE valueId = $value->id";

        $results = $DBI->query($query);
        foreach($results as $row) {
            self::updateResourceCache($row['cserdId']);
        }
    }

    // Update all the resources that have this field. 
    // (Takes a long time because it's usually a lot of resources.  Often times out.)
    public static function updateCacheFromField($field) {
        $DBI = SDRService::getDBI();
        $query = "SELECT distinct SDRVersion.cserdId 
                            FROM SDRVersionFieldValue 
                            LEFT JOIN SDRVersion ON SDRVersion.id = SDRVersionFieldValue.versionId
                            WHERE fieldId = $field->id";

        $results = $DBI->query($query);
        foreach($results as $row) {
            self::updateResourceCache($row['cserdId']);
        }
    }
}
?>

---