/**
* Performs the actual task of table population.
*/
public static function populate_cache_table($db, $table, $last_run_date)
{
$queries = kohana::config("cache_builder.{$table}");
try {
$count = cache_builder::get_changelist($db, $table, $queries, $last_run_date);
if ($count > 0) {
cache_builder::do_delete($db, $table, $queries);
// preprocess some of the tags in the queries
if (is_array($queries['update'])) {
foreach ($queries['update'] as $key => &$sql) {
$sql = str_replace('#join_needs_update#', $queries['join_needs_update'], $sql);
}
} else {
$queries['update'] = str_replace('#join_needs_update#', $queries['join_needs_update'], $queries['update']);
}
cache_builder::run_statement($db, $table, $queries['update'], 'update');
// preprocess some of the tags in the queries
if (is_array($queries['insert'])) {
foreach ($queries['insert'] as $key => &$sql) {
$sql = str_replace('#join_needs_update#', $queries['join_needs_update'] . ' and (nu.deleted=false or nu.deleted is null)', $sql);
}
} else {
$queries['insert'] = str_replace('#join_needs_update#', $queries['join_needs_update'] . ' and (nu.deleted=false or nu.deleted is null)', $queries['insert']);
}
cache_builder::run_statement($db, $table, $queries['insert'], 'insert');
if (isset($queries['extra_multi_record_updates'])) {
cache_builder::run_statement($db, $table, $queries['extra_multi_record_updates'], 'final update');
}
if (!variable::get("populated-{$table}")) {
$cacheQuery = $db->query("select count(*) from cache_{$table}")->result_array(false);
if (isset($queries['count'])) {
$totalQuery = $db->query($queries['count'])->result_array(false);
} else {
$totalQuery = $db->query("select count(*) from {$table} where deleted='f'")->result_array(false);
}
$percent = round($cacheQuery[0]['count'] * 100 / $totalQuery[0]['count']);
echo "{$table} population in progress - {$percent}% done";
}
echo '<br/>';
}
$db->query("drop table needs_update_{$table}");
} catch (Exception $e) {
error::log_error('Building cache', $e);
echo $e->getMessage();
$db->query("drop table needs_update_{$table}");
}
}
/**
* Hook into the task scheduler. This uses the queries defined in the cache_builder.php
* file to create and populate cache tables. The tables are not always up to date as they
* are only updated when the scheduler runs, but they have the advantage of simplifying
* the data model for reporting as well as reducing the need to join in queries, therefore
* significantly improving report performance.
* @param string $last_run_date Date last run, or null if never run
* @param object $db Database object.
*/
function cache_builder_scheduled_task($last_run_date, $db)
{
if (isset($_GET['force_cache_rebuild'])) {
$last_run_date = date('Y-m-d', time() - 60 * 60 * 24 * 365 * 200);
} elseif ($last_run_date === null) {
// first run, so get all records changed in last day. Query will automatically gradually pick up the rest.
$last_run_date = date('Y-m-d', time() - 60 * 60 * 24);
}
try {
foreach (kohana::config('cache_builder') as $table => $queries) {
cache_builder::populate_cache_table($db, $table, $last_run_date);
if (!variable::get("populated-{$table}")) {
// don't bother populating the next table, as there can be dependencies.
break;
}
}
} catch (Exception $e) {
echo $e->getMessage();
}
}
/**
* Handles any index rebuild requirements as a result of new or updated records, e.g. in
* samples or occurrences. Also handles joining of occurrence_associations to the
* correct records
*/
private function postProcess()
{
if (class_exists('cache_builder')) {
if (!empty(self::$changedRecords['insert']['occurrence'])) {
cache_builder::insert($this->db, 'occurrences', self::$changedRecords['insert']['occurrence']);
}
if (!empty(self::$changedRecords['update']['occurrence'])) {
cache_builder::update($this->db, 'occurrences', self::$changedRecords['update']['occurrence']);
}
if (!empty(self::$changedRecords['delete']['occurrence'])) {
cache_builder::delete($this->db, 'occurrences', self::$changedRecords['delete']['occurrence']);
}
$samples = array();
if (!empty(self::$changedRecords['insert']['sample'])) {
$samples = self::$changedRecords['insert']['sample'];
}
if (!empty(self::$changedRecords['update']['sample'])) {
$samples += self::$changedRecords['update']['sample'];
}
if (!empty($samples)) {
postgreSQL::insertMapSquaresForSamples($samples, 1000, $this->db);
postgreSQL::insertMapSquaresForSamples($samples, 2000, $this->db);
postgreSQL::insertMapSquaresForSamples($samples, 10000, $this->db);
} else {
// might be directly inserting an occurrence. No need to do this if inserting a sample, as the above code does the
// occurrences in bulk.
$occurrences = array();
if (!empty(self::$changedRecords['insert']['occurrence'])) {
$occurrences = self::$changedRecords['insert']['occurrence'];
}
if (!empty(self::$changedRecords['update']['occurrence'])) {
$occurrences += self::$changedRecords['update']['occurrence'];
}
if (!empty($occurrences)) {
postgreSQL::insertMapSquaresForOccurrences($occurrences, 1000, $this->db);
postgreSQL::insertMapSquaresForOccurrences($occurrences, 2000, $this->db);
postgreSQL::insertMapSquaresForOccurrences($occurrences, 10000, $this->db);
}
}
}
if (!empty(self::$changedRecords['insert']['occurrence_association'])) {
// We've got some associations between occurrences that could not have the to_occurrence_id
// foreign key filled in yet, since the occurrence referred to did not exist at the time of
// saving
foreach (Occurrence_association_Model::$to_occurrence_id_pointers as $associationId => $pointer) {
if (!empty($this->dynamicRowIdReferences["occurrence:{$pointer}"])) {
$this->db->from('occurrence_associations')->set('to_occurrence_id', $this->dynamicRowIdReferences["occurrence:{$pointer}"])->where('id', $associationId)->update();
}
}
}
}
/**
* Handles any index rebuild requirements as a result of new or updated records, e.g. in
* samples or occurrences.
*/
private function postProcess()
{
if (class_exists('cache_builder')) {
if (!empty(self::$changedRecords['insert']['occurrence'])) {
cache_builder::insert($this->db, 'occurrences', self::$changedRecords['insert']['occurrence']);
}
if (!empty(self::$changedRecords['update']['occurrence'])) {
cache_builder::update($this->db, 'occurrences', self::$changedRecords['update']['occurrence']);
}
if (!empty(self::$changedRecords['delete']['occurrence'])) {
cache_builder::delete($this->db, 'occurrences', self::$changedRecords['delete']['occurrence']);
}
$samples = array();
if (!empty(self::$changedRecords['insert']['sample'])) {
$samples = self::$changedRecords['insert']['sample'];
}
if (!empty(self::$changedRecords['update']['sample'])) {
$samples += self::$changedRecords['update']['sample'];
}
if (!empty($samples)) {
postgreSQL::insertMapSquaresForSamples($samples, 1000, $this->db);
postgreSQL::insertMapSquaresForSamples($samples, 2000, $this->db);
postgreSQL::insertMapSquaresForSamples($samples, 10000, $this->db);
} else {
// might be directly inserting an occurrence. No need to do this if inserting a sample, as the above code does the
// occurrences in bulk.
$occurrences = array();
if (!empty(self::$changedRecords['insert']['occurrence'])) {
$occurrences = self::$changedRecords['insert']['occurrence'];
}
if (!empty(self::$changedRecords['update']['occurrence'])) {
$occurrences += self::$changedRecords['update']['occurrence'];
}
if (!empty($occurrences)) {
postgreSQL::insertMapSquaresForOccurrences($occurrences, 1000, $this->db);
postgreSQL::insertMapSquaresForOccurrences($occurrences, 2000, $this->db);
postgreSQL::insertMapSquaresForOccurrences($occurrences, 10000, $this->db);
}
}
}
}