public static function data_objects_taxon_concepts() { // create a temporary table for this session $GLOBALS['db_connection']->query("DROP TABLE IF EXISTS `data_objects_taxon_concepts_tmp`"); $GLOBALS['db_connection']->query("CREATE TABLE `data_objects_taxon_concepts_tmp` (\n `taxon_concept_id` int unsigned NOT NULL,\n `data_object_id` int unsigned NOT NULL,\n PRIMARY KEY (`taxon_concept_id`, `data_object_id`),\n KEY `data_object_id` (`data_object_id`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); $GLOBALS['db_connection']->insert("CREATE TABLE IF NOT EXISTS data_objects_taxon_concepts LIKE data_objects_taxon_concepts_tmp"); // $GLOBALS['db_connection']->insert("CREATE TABLE IF NOT EXISTS data_objects_taxon_concepts_tmp LIKE data_objects_taxon_concepts"); // $GLOBALS['db_connection']->delete("TRUNCATE TABLE data_objects_taxon_concepts_tmp"); $start = 0; $stop = 0; $batch_size = 50000; $result = $GLOBALS['db_connection']->query("SELECT MIN(id) min, MAX(id) max FROM data_objects"); if ($result && ($row = $result->fetch_assoc())) { $start = $row['min']; $stop = $row['max']; } for ($i = $start; $i < $stop; $i += $batch_size) { debug("Inserting " . ($i - $start + $batch_size) / $batch_size . " of " . ceil(($stop - $start) / $batch_size)); $outfile = $GLOBALS['db_connection']->select_into_outfile("SELECT tc.id, do.id FROM taxon_concepts tc JOIN hierarchy_entries he ON (tc.id=he.taxon_concept_id) JOIN data_objects_hierarchy_entries dohe ON (he.id=dohe.hierarchy_entry_id) JOIN data_objects do ON (dohe.data_object_id=do.id) WHERE (tc.supercedure_id IS NULL OR tc.supercedure_id=0) AND (do.published=1 OR dohe.visibility_id!=" . Visibility::visible()->id . ") AND do.id BETWEEN {$i} AND " . ($i + $batch_size)); $GLOBALS['db_connection']->load_data_infile($outfile, 'data_objects_taxon_concepts_tmp'); unlink($outfile); $outfile = $GLOBALS['db_connection']->select_into_outfile("SELECT tc.id, do.id FROM taxon_concepts tc JOIN hierarchy_entries he ON (tc.id=he.taxon_concept_id) JOIN curated_data_objects_hierarchy_entries cdohe ON (he.id=cdohe.hierarchy_entry_id) JOIN data_objects do ON (cdohe.data_object_id=do.id) WHERE (tc.supercedure_id IS NULL OR tc.supercedure_id=0) AND (do.published=1 OR cdohe.visibility_id!=" . Visibility::visible()->id . ") AND do.id BETWEEN {$i} AND " . ($i + $batch_size)); $GLOBALS['db_connection']->load_data_infile($outfile, 'data_objects_taxon_concepts_tmp'); unlink($outfile); $outfile = $GLOBALS['db_connection']->select_into_outfile("SELECT tc.id, do.id FROM taxon_concepts tc JOIN users_data_objects udo ON (tc.id=udo.taxon_concept_id) JOIN data_objects do ON (udo.data_object_id=do.id) WHERE (tc.supercedure_id IS NULL OR tc.supercedure_id=0) AND (do.published=1 OR udo.visibility_id!=" . Visibility::visible()->id . ") AND do.id BETWEEN {$i} AND " . ($i + $batch_size)); $GLOBALS['db_connection']->load_data_infile($outfile, 'data_objects_taxon_concepts_tmp'); unlink($outfile); } $result = $GLOBALS['db_connection']->query("SELECT 1 FROM data_objects_taxon_concepts_tmp LIMIT 1"); if ($result && ($row = $result->fetch_assoc())) { $GLOBALS['db_connection']->swap_tables("data_objects_taxon_concepts", "data_objects_taxon_concepts_tmp"); } }
private function lookup_curated_best_entries($start, $limit) { $curated_best_entries = array(); $query = "SELECT c.taxon_concept_id, c.hierarchy_entry_id\n FROM curated_taxon_concept_preferred_entries c\n JOIN hierarchy_entries he ON (c.hierarchy_entry_id=he.id)\n WHERE c.taxon_concept_id BETWEEN {$start} AND " . ($start + $limit) . " AND he.published=1 AND he.visibility_id=" . Visibility::visible()->id; foreach ($this->mysqli->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $hierarchy_entry_id = $row[1]; $curated_best_entries[$taxon_concept_id] = $hierarchy_entry_id; } return $curated_best_entries; }
function lookup_ranks($start, $limit, &$taxon_concept_ids = array()) { debug("querying ranks"); $query = "SELECT he.taxon_concept_id, he.rank_id\n FROM hierarchy_entries he USE INDEX (taxon_concept_id)\n WHERE he.visibility_id=" . Visibility::visible()->id . "\n AND he.taxon_concept_id "; if ($taxon_concept_ids) { $query .= "IN (" . implode(",", $taxon_concept_ids) . ")"; } else { $query .= "BETWEEN {$start} AND " . ($start + $limit); } foreach ($this->mysqli_slave->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $rank_id = $row[1]; if ($rank_id) { if (!isset($this->ranks_ids[$taxon_concept_id][$rank_id])) { $this->ranks_ids[$taxon_concept_id][$rank_id] = 1; } else { $this->ranks_ids[$taxon_concept_id][$rank_id] += 1; } } } }
private static function concept_published_in_hierarchy($taxon_concept_id, $hierarchy_id) { $mysqli =& $GLOBALS['mysqli_connection']; $result = $mysqli->query("SELECT 1 FROM hierarchy_entries WHERE taxon_concept_id={$taxon_concept_id} AND hierarchy_id={$hierarchy_id} AND visibility_id=" . Visibility::visible()->id . " LIMIT 1"); if ($result && ($row = $result->fetch_assoc())) { return true; } return false; }
public function insert_gbif_references($row, $parameters) { self::debug_iterations("Inserting GBIF reference"); $this->commit_iterations("GBIFReference", 500); if ($this->archive_validator->has_error_by_line('http://rs.gbif.org/terms/1.0/reference', $parameters['archive_table_definition']->location, $parameters['archive_line_number'])) { write_to_resource_harvesting_log("ERROR: insert_agents: insert_gbif_references" . ",file_location:" . $parameters['archive_table_definition']->location . ",line_number:" . $parameters['archive_line_number']); return false; } $reference_id = @self::field_decode($row['http://purl.org/dc/terms/identifier']); $taxon_id = @self::field_decode($row['http://rs.tdwg.org/dwc/terms/taxonID']); // we really only need to insert the references that relate to taxa if (!isset($this->taxon_ids_inserted[$taxon_id])) { return; } $full_reference = @self::field_decode($row['http://purl.org/dc/terms/bibliographicCitation']); $title = @self::field_decode($row['http://purl.org/dc/terms/title']); $author = @self::field_decode($row['http://purl.org/dc/terms/creator']); $date = @self::field_decode($row['http://purl.org/dc/terms/date']); $description = @self::field_decode($row['http://purl.org/dc/terms/description']); // $subject = @self::field_decode($row['http://purl.org/dc/terms/subject']); $source = @self::field_decode($row['http://purl.org/dc/terms/source']); $language = Language::find_or_create_for_parser(@self::field_decode($row['http://purl.org/dc/terms/language'])); $type = @self::field_decode($row['http://purl.org/dc/terms/type']); if ($type != 'taxon') { return; } $reference_parts = array(); if ($author) { $reference_parts[] = $author; } if ($date) { $reference_parts[] = $date; } if ($title) { $reference_parts[] = $title; } if ($source) { $reference_parts[] = $source; } if ($description) { $reference_parts[] = $description; } $full_reference = implode(". ", $reference_parts); $full_reference = str_replace("..", ".", $full_reference); $full_reference = str_replace(" ", " ", $full_reference); if (!$full_reference) { return; } $title = null; $author = null; $date = null; $description = null; $source = null; $type = null; if ($taxon_info = @$this->taxon_ids_inserted[$taxon_id]) { self::uncompress_array($taxon_info); $params = array("provider_mangaed_id" => $reference_id, "full_reference" => $full_reference, "title" => $title, "authors" => $author, "publication_created_at" => @$created ?: '0000-00-00 00:00:00', "language_id" => @$language->id ?: 0); $reference = Reference::find_or_create($params); $he_id = $taxon_info['hierarchy_entry_id']; $this->mysqli->insert("INSERT IGNORE INTO hierarchy_entries_refs (hierarchy_entry_id, ref_id) VALUES ({$he_id}, {$reference->id})"); $this->mysqli->query("UPDATE refs SET published=1, visibility_id=" . Visibility::visible()->id . " WHERE id={$reference->id}"); // TODO: find_or_create doesn't work here because of the dual primary key // HierarchyEntriesRef::find_or_create(array( // 'hierarchy_entry_id' => $hierarchy_entry_id, // 'ref_id' => $reference->id)); } }
public static function update_taxon_concept_names($taxon_concept_ids) { if (!$taxon_concept_ids) { return false; } if (is_numeric($taxon_concept_ids)) { $taxon_concept_ids = array($taxon_concept_ids); } $mysqli =& $GLOBALS['db_connection']; $started_new_transaction = false; if (!$mysqli->in_transaction()) { $mysqli->begin_transaction(); $started_new_transaction = true; } $batches = array_chunk($taxon_concept_ids, 500); foreach ($batches as $batch_ids) { usleep(500000); $name_ids = array(); $matching_ids = array(); $query = "\n (SELECT he.taxon_concept_id, he.id, he.name_id, 'preferred' as type FROM hierarchy_entries he WHERE taxon_concept_id IN (" . implode(",", $batch_ids) . ") AND ((he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ") OR (he.published=0 AND he.visibility_id=" . Visibility::preview()->id . ")))\n UNION\n (SELECT he.taxon_concept_id, s.hierarchy_entry_id, s.name_id, 'synonym' as type\n FROM hierarchy_entries he\n JOIN synonyms s ON (he.id=s.hierarchy_entry_id)\n WHERE he.taxon_concept_id IN (" . implode(",", $batch_ids) . ")\n AND s.language_id=0\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('genbank common name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('common name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('blast name')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('genbank acronym')->id . "\n AND s.synonym_relation_id!=" . SynonymRelation::find_or_create_by_translated_label('acronym')->id . "\n AND ((he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ") OR (he.published=0 AND he.visibility_id=" . Visibility::preview()->id . ")))"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $hierarchy_entry_id = $row[1]; $name_id = $row[2]; $name_type = $row[3]; $name_ids[$name_id][$taxon_concept_id] = 1; $matching_ids[$taxon_concept_id][$name_id][$hierarchy_entry_id] = $name_type; } if ($name_ids) { //This makes sure we have a scientific name, gets the canonicalFormID $query = "SELECT n.id, n_match.id FROM names n JOIN canonical_forms cf ON (n.canonical_form_id=cf.id) JOIN names n_match ON (cf.id=n_match.canonical_form_id) WHERE n.id IN (" . implode(",", array_keys($name_ids)) . ") AND n_match.string=cf.string"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $original_name_id = $row[0]; $canonical_name_id = $row[1]; if ($original_name_id != $canonical_name_id) { foreach ($name_ids[$original_name_id] as $taxon_concept_id => $junk) { $matching_ids[$taxon_concept_id][$canonical_name_id][0] = 1; } } } } $common_names = array(); $preferred_in_language = array(); $query = "SELECT he.taxon_concept_id, he.published, he.visibility_id, s.id, s.hierarchy_id, s.hierarchy_entry_id, s.name_id, s.language_id, s.preferred, s.vetted_id FROM hierarchy_entries he JOIN synonyms s ON (he.id=s.hierarchy_entry_id) JOIN vetted v ON (s.vetted_id=v.id) WHERE he.taxon_concept_id IN (" . implode(",", $batch_ids) . ") AND s.language_id!=0 AND (s.synonym_relation_id=" . SynonymRelation::genbank_common_name()->id . " OR s.synonym_relation_id=" . SynonymRelation::common_name()->id . ") ORDER BY s.language_id, (s.hierarchy_id=" . Hierarchy::contributors()->id . ") DESC, v.view_order ASC, s.preferred DESC, s.id DESC"; foreach ($mysqli->iterate_file($query) as $row_num => $row) { $taxon_concept_id = $row[0]; $published = $row[1]; $visibility_id = $row[2]; $synonym_id = $row[3]; $hierarchy_id = $row[4]; $hierarchy_entry_id = $row[5]; $name_id = $row[6]; $language_id = $row[7]; $preferred = $row[8]; $vetted_id = $row[9]; // skipping Wikipedia common names entirely if ($hierarchy_id == @Hierarchy::wikipedia()->id) { continue; } $curator_name = $hierarchy_id == @Hierarchy::contributors()->id; $ubio_name = $hierarchy_id == @Hierarchy::ubio()->id; if ($curator_name || $ubio_name || $curator_name || $published == 1 && $visibility_id == Visibility::visible()->id) { if (isset($preferred_in_language[$taxon_concept_id][$language_id])) { $preferred = 0; } if ($preferred && $curator_name && ($vetted_id == Vetted::trusted()->id || $vetted_id == Vetted::unknown()->id)) { $preferred_in_language[$taxon_concept_id][$language_id] = 1; } else { $preferred = 0; } if (!isset($common_names[$taxon_concept_id])) { $common_names[$taxon_concept_id] = array(); } $common_names[$taxon_concept_id][] = array('synonym_id' => $synonym_id, 'language_id' => $language_id, 'name_id' => $name_id, 'hierarchy_entry_id' => $hierarchy_entry_id, 'preferred' => $preferred, 'vetted_id' => $vetted_id, 'is_curator_name' => $curator_name); } } // if there was no preferred name foreach ($common_names as $taxon_concept_id => $arr) { foreach ($arr as $key => $arr2) { if (@(!$preferred_in_language[$taxon_concept_id][$arr2['language_id']]) && ($arr2['vetted_id'] == Vetted::trusted()->id || $arr2['vetted_id'] == Vetted::unknown()->id)) { $common_names[$taxon_concept_id][$key]['preferred'] = 1; $preferred_in_language[$taxon_concept_id][$arr2['language_id']] = 1; } } } $mysqli->delete("DELETE FROM taxon_concept_names WHERE taxon_concept_id IN (" . implode(",", $batch_ids) . ")"); $tmp_file_path = temp_filepath(); if (!($LOAD_DATA_TEMP = fopen($tmp_file_path, "w+"))) { debug(__CLASS__ . ":" . __LINE__ . ": Couldn't open file: " . $tmp_file_path); return; } /* Insert the scientific names */ foreach ($matching_ids as $taxon_concept_id => $arr) { foreach ($arr as $name_id => $arr2) { foreach ($arr2 as $hierarchy_entry_id => $type) { $preferred = 0; if ($hierarchy_entry_id && $type == "preferred") { $preferred = 1; } fwrite($LOAD_DATA_TEMP, "{$taxon_concept_id}\t{$name_id}\t{$hierarchy_entry_id}\t0\t0\t{$preferred}\n"); } } } $mysqli->load_data_infile($tmp_file_path, 'taxon_concept_names'); unlink($tmp_file_path); $tmp_file_path = temp_filepath(); if (!($LOAD_DATA_TEMP = fopen($tmp_file_path, "w+"))) { debug(__CLASS__ . ":" . __LINE__ . ": Couldn't open file: " . $tmp_file_path); return; } /* Insert the common names */ foreach ($common_names as $taxon_concept_id => $arr) { foreach ($arr as $key => $arr2) { $synonym_id = $arr2['synonym_id']; $language_id = $arr2['language_id']; $name_id = $arr2['name_id']; $hierarchy_entry_id = $arr2['hierarchy_entry_id']; $preferred = $arr2['preferred']; $vetted_id = $arr2['vetted_id']; fwrite($LOAD_DATA_TEMP, "{$taxon_concept_id}\t{$name_id}\t{$hierarchy_entry_id}\t{$language_id}\t1\t{$preferred}\t{$synonym_id}\t{$vetted_id}\n"); } } $mysqli->load_data_infile($tmp_file_path, 'taxon_concept_names'); unlink($tmp_file_path); unset($matching_ids); unset($common_names); unset($name_ids); unset($preferred_in_language); $mysqli->commit(); } if ($started_new_transaction) { $mysqli->end_transaction(); } }
function add_data_object($hierarchy_entry, $options) { $d = $options[0]; $parameters = $options[1]; // Add default values from resource if (@(!$d->rights_statement) && $this->resource->rights_statement) { $d->rights_statement = $this->resource->rights_statement; } if (@(!$d->rights_holder) && $this->resource->rights_holder) { $d->rights_holder = $this->resource->rights_holder; } if (@(!$d->license_id) && $this->resource->license_id) { $d->license_id = $this->resource->license_id; } if (@(!$d->language_id) && $this->resource->language_id) { $d->language_id = $this->resource->language_id; } // print_r($d); list($data_object, $status, $existing_data_object) = DataObject::find_and_compare($this->resource, $d, $this->content_manager); $GLOBALS['db_connection']->commit(); if (@(!$data_object->id)) { return false; } $vetted_id = Vetted::unknown()->id; $visibility_id = Visibility::preview()->id; if ($existing_data_object) { // if($existing_data_object && ($this->resource->title != "Wikipedia" || $status == "Unchanged") && $v = $existing_data_object->best_vetted()) if ($existing_data_object && ($v = $existing_data_object->best_vetted())) { $vetted_id = $v->id; } if ($existing_data_object && ($v = $existing_data_object->best_visibility())) { if ($v != Visibility::visible()) { // if the existing object is visible - this will go on as preview // otherwise this will inherit the visibility (unpublished) $visibility_id = $v->id; } } } // we only delete the object's entries the first time we see it, to allow for multiple taxa per object if (!isset($this->harvested_data_object_ids[$data_object->id])) { $data_object->delete_hierarchy_entries(); } $this->harvested_data_object_ids[$data_object->id] = 1; $hierarchy_entry->add_data_object($data_object->id, $vetted_id, $visibility_id); $this->resource->harvest_event->add_data_object($data_object, $status); if ($status != "Reused") { $i = 0; $data_object->delete_agents(); foreach ($parameters['agents'] as &$a) { $agent = Agent::find_or_create($a); if ($agent->logo_url && !$agent->logo_cache_url) { if ($logo_cache_url = $this->content_manager->grab_file($agent->logo_url, "partner")) { $agent->logo_cache_url = $logo_cache_url; $agent->save(); } } $data_object->add_agent($agent->id, @$a['agent_role']->id ?: 0, $i); unset($a); $i++; } $data_object->delete_audiences(); foreach ($parameters['audiences'] as &$a) { $data_object->add_audience($a->id); unset($a); } $data_object->delete_info_items(); $data_object->delete_table_of_contents(); if (@$parameters['info_items']) { foreach ($parameters['info_items'] as &$ii) { $data_object->add_info_item($ii->id); unset($ii); } } $data_object->delete_refs(); if (@$parameters['refs']) { foreach ($parameters['refs'] as &$r) { if (@$r->id) { $data_object->add_reference($r->id); $r->publish(); } unset($r); } } } }
private function lookup_family($name, $synonyms, $ancestors) { $order = @$ancestors[15]; $class = @$ancestors[11]; $phylum = @$ancestors[6]; if (!$order && !$class && !$phylum) { echo "This is a line that doesnt have a order, class or phylum:\n{$line_number}: {$line} :: {$name}\n\n\n"; exit; } $synonyms[] = $name; $result = $this->mysqli->query("\n (SELECT n.id name_id, h.id hierarchy_id, h.browsable, he.taxon_concept_id, 'valid' match_type\n FROM canonical_forms cf\n JOIN names n ON (cf.id=n.canonical_form_id)\n JOIN hierarchy_entries he ON (n.id=he.name_id)\n JOIN hierarchies h ON (he.hierarchy_id=h.id)\n WHERE cf.string IN ('" . implode("','", $synonyms) . "')\n AND he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ")\n UNION\n (SELECT n.id name_id, h.id hierarchy_id, h.browsable, he.taxon_concept_id, 'synonym' match_type\n FROM canonical_forms cf\n JOIN names n ON (cf.id=n.canonical_form_id)\n JOIN synonyms s ON (n.id=s.name_id AND s.synonym_relation_id=" . SynonymRelation::synonym()->id . ")\n JOIN hierarchy_entries he ON (s.hierarchy_entry_id=he.id)\n JOIN hierarchies h ON (he.hierarchy_id=h.id)\n WHERE cf.string IN ('" . implode("','", $synonyms) . "')\n AND he.published=1 AND he.visibility_id=" . Visibility::visible()->id . ")"); if ($result && $result->num_rows) { return $this->get_best_concept_from_result($result, $name); } }
function get_word_count($taxon_concept_id, $chapter) { $concept_data_object_counts = array(); $text_id = DataType::find_or_create_by_schema_value('http://purl.org/dc/dcmitype/Text')->id; $trusted_id = Vetted::trusted()->id; $untrusted_id = Vetted::untrusted()->id; $unreviewed_id = Vetted::unknown()->id; if ($chapter == "brief summary") { $toc_id = TranslatedTableOfContent::find_or_create_by_label('Brief Summary')->table_of_contents_id; } elseif ($chapter == "comprehensive description") { $toc_id = TranslatedTableOfContent::find_or_create_by_label('Comprehensive Description')->table_of_contents_id; } $query = "SELECT dotoc.toc_id,do.description, dohe.vetted_id FROM data_objects_taxon_concepts dotc \n JOIN data_objects do ON dotc.data_object_id = do.id LEFT JOIN data_objects_table_of_contents dotoc ON do.id = dotoc.data_object_id \n JOIN data_objects_hierarchy_entries dohe on do.id = dohe.data_object_id\n WHERE do.published = 1 AND dohe.visibility_id =" . Visibility::visible()->id . " AND do.data_type_id = {$text_id} AND dotc.taxon_concept_id = {$taxon_concept_id} AND dotoc.toc_id = {$toc_id}\n UNION\n SELECT dotoc.toc_id,do.description, udo.vetted_id FROM data_objects_taxon_concepts dotc \n JOIN data_objects do ON dotc.data_object_id = do.id LEFT JOIN data_objects_table_of_contents dotoc ON do.id = dotoc.data_object_id \n JOIN users_data_objects udo on do.id = udo.data_object_id\n WHERE do.published = 1 AND udo.visibility_id =" . Visibility::visible()->id . " AND do.data_type_id = {$text_id} AND dotc.taxon_concept_id = {$taxon_concept_id} AND dotoc.toc_id = {$toc_id}"; $result = $this->mysqli_slave->query($query); while ($result && ($row = $result->fetch_assoc())) { $description = $row['description']; $vetted_id = $row['vetted_id']; $words_count = str_word_count(strip_tags($description), 0); @($concept_data_object_counts['total_w'] += $words_count); if ($vetted_id == $trusted_id) { @($concept_data_object_counts['t_w'] += $words_count); } elseif ($vetted_id == $untrusted_id) { @($concept_data_object_counts['ut_w'] += $words_count); } elseif ($vetted_id == $unreviewed_id) { @($concept_data_object_counts['ur_w'] += $words_count); } } return @$concept_data_object_counts['total_w']; }
private function load_all_user_object_associations() { $this->user_data_objects_taxa = array(); $query = "SELECT do.id, udo.taxon_concept_id\n FROM data_objects do\n JOIN users_data_objects udo ON (do.id=udo.data_object_id)\n WHERE do.published = 1\n AND udo.vetted_id IN (" . implode(",", array(Vetted::trusted()->id, Vetted::unknown()->id)) . ")\n AND udo.visibility_id = " . Visibility::visible()->id; foreach ($this->mysqli_slave->iterate($query) as $row) { $this->user_data_objects_taxa[$row['id']][$row['taxon_concept_id']] = true; } }
function get_data_objects_count($batch_size = 100000) { $image_id = DataType::image()->id; $text_id = DataType::text()->id; $video_id = DataType::video()->id; $sound_id = DataType::sound()->id; $flash_id = DataType::flash()->id; $youtube_id = DataType::youtube()->id; $iucn_id = DataType::iucn()->id; $data_type_label[$text_id] = 'text'; $data_type_label[$video_id] = 'video'; $data_type_label[$sound_id] = 'sound'; $data_type_label[$flash_id] = 'flash'; $data_type_label[$youtube_id] = 'youtube'; $data_type_label[$iucn_id] = 'iucn'; $data_type_order_in_file = array("text", "video", "sound", "flash", "youtube", "iucn"); $trusted_id = Vetted::trusted()->id; $untrusted_id = Vetted::untrusted()->id; $unreviewed_id = Vetted::unknown()->id; $raw_stats = array(); $concept_info_items = array(); $concept_references = array(); for ($i = $this->min_taxon_concept_id; $i <= $this->max_taxon_concept_id; $i += $batch_size) { $this->print_status($i, $batch_size); $sql = "SELECT do.guid,\n dotc.taxon_concept_id,\n do.data_type_id,\n doii.info_item_id,\n dor.ref_id,\n REPLACE(REPLACE(do.description, '\\\\n', ' '), '\\\\r', ' '),\n dohe.vetted_id,\n do.id\n FROM data_objects_taxon_concepts dotc\n STRAIGHT_JOIN data_objects do ON (dotc.data_object_id = do.id)\n JOIN data_objects_hierarchy_entries dohe ON (do.id=dohe.data_object_id)\n LEFT JOIN data_objects_info_items doii ON (do.id = doii.data_object_id)\n LEFT JOIN data_objects_refs dor ON (do.id = dor.data_object_id)\n WHERE do.published = 1 AND dohe.visibility_id = " . Visibility::visible()->id . " AND do.data_type_id != {$image_id}"; if ($this->test_taxon_concept_ids) { $sql .= " AND dotc.taxon_concept_id IN (" . $this->test_taxon_concept_ids . ")"; } else { $sql .= " AND dotc.taxon_concept_id BETWEEN {$i} AND " . ($i + $batch_size); } $counted_data_objects = array(); foreach ($this->mysqli_slave->iterate_file($sql) as $row_number => $row) { $taxon_concept_id = trim($row[0]); $data_type_id = trim($row[1]); $info_item_id = trim($row[2]); $ref_id = trim($row[3]); $description = trim($row[4]); $vetted_id = trim($row[5]); $data_object_id = trim($row[6]); if (isset($counted_data_objects[$taxon_concept_id][$data_object_id])) { continue; } $counted_data_objects[$taxon_concept_id][$data_object_id] = 1; $label = @$data_type_label[$data_type_id]; $words_count = str_word_count(strip_tags($description), 0); @$raw_stats[$taxon_concept_id][$label]['total']++; @($raw_stats[$taxon_concept_id][$label]['total_w'] += $words_count); if ($vetted_id == $trusted_id) { @$raw_stats[$taxon_concept_id][$label]['t']++; @($raw_stats[$taxon_concept_id][$label]['t_w'] += $words_count); } elseif ($vetted_id == $untrusted_id) { @$raw_stats[$taxon_concept_id][$label]['ut']++; @($raw_stats[$taxon_concept_id][$label]['ut_w'] += $words_count); } elseif ($vetted_id == $unreviewed_id) { @$raw_stats[$taxon_concept_id][$label]['ur']++; @($raw_stats[$taxon_concept_id][$label]['ur_w'] += $words_count); } $concept_info_items[$taxon_concept_id][$info_item_id] = ''; $concept_references[$taxon_concept_id][$ref_id] = ''; } foreach ($raw_stats as $taxon_concept_id => $stats) { $new_value = ""; # the stats need to go into the file in a certain order to be imported into the MySQL table foreach ($data_type_order_in_file as $data_type) { $new_value = @$stats[$data_type]['total']; $new_value .= "\t" . @$stats[$data_type]['t']; $new_value .= "\t" . @$stats[$data_type]['ut']; $new_value .= "\t" . @$stats[$data_type]['ur']; $new_value .= "\t" . @$stats[$data_type]['total_w']; $new_value .= "\t" . @$stats[$data_type]['t_w']; $new_value .= "\t" . @$stats[$data_type]['ut_w']; $new_value .= "\t" . @$stats[$data_type]['ur_w']; } $raw_stats[$taxon_concept_id] = $new_value; } $this->save_category_stats($raw_stats, "get_data_objects_count"); $raw_stats = array(); if ($this->test_taxon_concept_ids) { break; } } // $this->save_to_json_file($concept_info_items, "concept_info_items"); // unset($concept_info_items); // // $this->save_to_json_file($concept_references, "concept_references"); // unset($concept_references); }
function get_data_objects_count($batch_size = 100000) { $time_start = time_elapsed(); $concept_data_object_counts = array(); $concept_data_object_maps = array(); $concept_info_items = array(); $concept_references = array(); $image_id = DataType::image()->id; $map_id = DataType::map()->id; $text_id = DataType::text()->id; $video_id = DataType::video()->id; $sound_id = DataType::sound()->id; $flash_id = DataType::flash()->id; $youtube_id = DataType::youtube()->id; $iucn_id = DataType::iucn()->id; $data_type_label[$image_id] = 'image'; $data_type_label[$sound_id] = 'sound'; $data_type_label[$text_id] = 'text'; $data_type_label[$video_id] = 'video'; $data_type_label[$iucn_id] = 'iucn'; $data_type_label[$flash_id] = 'flash'; $data_type_label[$youtube_id] = 'youtube'; $trusted_id = Vetted::trusted()->id; $untrusted_id = Vetted::untrusted()->id; $unreviewed_id = Vetted::unknown()->id; for ($i = $this->min_taxon_concept_id; $i <= $this->max_taxon_concept_id; $i += $batch_size) { print "\n dataObjects, its infoItems, its references [2 of 14] {$i} \n"; $sql = "SELECT dotc.taxon_concept_id tc_id, do.data_type_id, doii.info_item_id, dor.ref_id, do.description, dohe.vetted_id, do.data_subtype_id\r\n FROM data_objects_taxon_concepts dotc \r\n JOIN data_objects do ON dotc.data_object_id = do.id \r\n LEFT JOIN data_objects_info_items doii ON do.id = doii.data_object_id \r\n LEFT JOIN data_objects_refs dor ON do.id = dor.data_object_id \r\n JOIN data_objects_hierarchy_entries dohe on do.id = dohe.data_object_id\r\n WHERE do.published=1 AND dohe.visibility_id=" . Visibility::visible()->id . " AND dohe.vetted_id != {$untrusted_id} "; //." AND do.data_type_id <> $image_id "; this has to be removed to count maps if (isset($GLOBALS['test_taxon_concept_ids'])) { $sql .= " and dotc.taxon_concept_id IN (" . implode(",", $GLOBALS['test_taxon_concept_ids']) . ")"; } else { $sql .= " AND dotc.taxon_concept_id BETWEEN {$i} AND " . ($i + $batch_size); } $sql .= "\r\n UNION\r\n SELECT dotc.taxon_concept_id tc_id, do.data_type_id, doii.info_item_id, dor.ref_id, do.description, udo.vetted_id, do.data_subtype_id\r\n FROM data_objects_taxon_concepts dotc \r\n JOIN data_objects do ON dotc.data_object_id = do.id \r\n LEFT JOIN data_objects_info_items doii ON do.id = doii.data_object_id \r\n LEFT JOIN data_objects_refs dor ON do.id = dor.data_object_id \r\n JOIN users_data_objects udo on do.id = udo.data_object_id\r\n WHERE do.published=1 AND udo.visibility_id=" . Visibility::visible()->id . "\r\n "; if (isset($GLOBALS['test_taxon_concept_ids'])) { $sql .= " and dotc.taxon_concept_id IN (" . implode(",", $GLOBALS['test_taxon_concept_ids']) . ")"; } else { $sql .= " AND dotc.taxon_concept_id BETWEEN {$i} AND " . ($i + $batch_size); } $outfile = $this->mysqli_slave->select_into_outfile($sql); $FILE = fopen($outfile, "r"); if (!$FILE) { print "!! ERROR: Could not read {$outfile}"; debug("!! ERROR: Could not read {$outfile}"); return; } $num_rows = 0; while (!feof($FILE)) { if ($line = fgets($FILE)) { $num_rows++; $line = trim($line); $fields = explode("\t", $line); $tc_id = trim($fields[0]); $data_type_id = trim($fields[1]); $info_item_id = trim($fields[2]); $ref_id = trim($fields[3]); $description = trim($fields[4]); $vetted_id = trim($fields[5]); $data_subtype_id = trim($fields[6]); $label = @$data_type_label[$data_type_id]; if ($data_subtype_id != $map_id) { $words_count = str_word_count(strip_tags($description), 0); @$concept_data_object_counts[$tc_id][$label]['total']++; @($concept_data_object_counts[$tc_id][$label]['total_w'] += $words_count); if ($vetted_id == $trusted_id) { @$concept_data_object_counts[$tc_id][$label]['t']++; @($concept_data_object_counts[$tc_id][$label]['t_w'] += $words_count); } elseif ($vetted_id == $untrusted_id) { @$concept_data_object_counts[$tc_id][$label]['ut']++; @($concept_data_object_counts[$tc_id][$label]['ut_w'] += $words_count); } elseif ($vetted_id == $unreviewed_id) { @$concept_data_object_counts[$tc_id][$label]['ur']++; @($concept_data_object_counts[$tc_id][$label]['ur_w'] += $words_count); } $concept_info_items[$tc_id][$info_item_id] = ''; $concept_references[$tc_id][$ref_id] = ''; } else { @$concept_data_object_maps[$tc_id][$label]['total']++; if ($vetted_id == $trusted_id) { @$concept_data_object_maps[$tc_id][$label]['t']++; } elseif ($vetted_id == $untrusted_id) { @$concept_data_object_maps[$tc_id][$label]['ut']++; } elseif ($vetted_id == $unreviewed_id) { @$concept_data_object_maps[$tc_id][$label]['ur']++; } } } } fclose($FILE); unlink($outfile); print "\n num_rows: {$num_rows}"; } self::save_to_json_file($concept_info_items, "concept_info_items"); unset($concept_info_items); self::save_to_json_file($concept_references, "concept_references"); unset($concept_references); //save map data to be accessed later self::save_to_json_file($concept_data_object_maps, "map_counts"); unset($concept_data_object_maps); //convert associative array to a regular array $data_type_order_in_file = array("text", "video", "sound", "flash", "youtube", "iucn"); foreach ($concept_data_object_counts as $taxon_concept_id => $taxon_object_counts) { $new_value = ""; foreach ($data_type_order_in_file as $data_type) { $new_value .= "\t" . @$taxon_object_counts[$data_type]['total']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['t']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ut']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ur']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['total_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['t_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ut_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ur_w']; } $concept_data_object_counts[$taxon_concept_id] = $new_value; } print "\n get_data_objects_count():" . (time_elapsed() - $time_start) / 60 . " minutes"; self::save_totals_to_cumulative_txt($concept_data_object_counts, "tpm_data_objects"); unset($concept_data_object_counts); }
private function lookup_links($start, $limit) { $hierarchy_ids = array(771, 759, 431, 123, 903, 596, 410, 143, 860); // COL 2011, NCBI, WORMS, Wikipedia, ITIS, IF, Wikimedia Commons, fishbase, avibase $query = "\n SELECT tc.id, he.id hierarchy_entry_id, he.identifier, he.source_url, h.label, h.outlink_uri, res.title, h.id, n.string,\n cf.string, he_parent.id parent_hierarchy_entry_id, he_parent.taxon_concept_id, tr.label, tcm.richness_score\n FROM taxon_concepts tc\n JOIN hierarchy_entries he ON (tc.id=he.taxon_concept_id)\n JOIN names n ON (he.name_id=n.id)\n JOIN hierarchies h ON (he.hierarchy_id=h.id)\n LEFT JOIN taxon_concept_metrics tcm ON (tc.id=tcm.taxon_concept_id)\n LEFT JOIN resources res ON (h.id=res.hierarchy_id)\n LEFT JOIN canonical_forms cf ON (n.ranked_canonical_form_id=cf.id)\n LEFT JOIN hierarchy_entries he_parent ON (he.parent_id=he_parent.id)\n LEFT JOIN (ranks r JOIN translated_ranks tr ON (r.id = tr.rank_id AND tr.language_id=152)) ON (he.rank_id=r.id)\n WHERE tc.id BETWEEN {$start} AND " . ($start + $limit) . "\n AND he.published=1 AND he.visibility_id=" . Visibility::visible()->id . "\n AND he.hierarchy_id IN (" . implode(",", $hierarchy_ids) . ")"; $links_from_hierarchy = array(); static $j = 0; foreach ($this->mysqli->iterate_file($query) as $row_num => $row) { if ($j % 10000 == 0) { echo "{$start} : {$j} : " . time_elapsed() . " : " . memory_get_usage() . "\n"; } $j++; $taxon_concept_id = $row[0]; $hierarchy_entry_id = $row[1]; $identifier = $row[2]; $source_url = $row[3]; $hierarchy_label = $row[4]; $outlink_uri = $row[5]; $resource_title = $row[6]; $hierarchy_id = $row[7]; $string = trim($row[8]); $ranked_canonical_form = trim($row[9]); $parent_hierarchy_entry_id = $row[10]; $parent_page_id = $row[11]; $rank_label = $row[12]; $richness_score = $row[13]; if (!$parent_hierarchy_entry_id || $parent_hierarchy_entry_id == "NULL") { $parent_hierarchy_entry_id = 0; } if (!$parent_page_id || $parent_page_id == "NULL") { $parent_page_id = 0; } if (!$richness_score || $richness_score == "NULL") { $richness_score = 0; } if (!$rank_label || $rank_label == "NULL") { $rank_label = ''; } if ($rank_label == "gen.") { $rank_label = 'genus'; } if ($rank_label == "sp.") { $rank_label = 'species'; } if ($rank_label == "subsp.") { $rank_label = 'subspecies'; } if ($rank_label == "var.") { $rank_label = 'variety'; } $richness_score = round($richness_score * 100, 2); if ($resource_title != 'NULL' && ($t = trim($resource_title))) { $title = $t; } elseif ($hierarchy_label != 'NULL' && ($t = trim($hierarchy_label))) { $title = $t; } if (@$links_from_hierarchy[$taxon_concept_id][$hierarchy_id]) { continue; } $name = null; if ($ranked_canonical_form != 'NULL' && !Name::is_surrogate($ranked_canonical_form)) { $name = $ranked_canonical_form; } elseif ($string != 'NULL') { $name = $string; } if (!$name) { continue; } fwrite($this->LINKS_OUT, "{$taxon_concept_id}\t{$hierarchy_entry_id}\t{$name}\t{$parent_page_id}\t{$parent_hierarchy_entry_id}\t{$richness_score}\t{$rank_label}\t{$identifier}\t{$title}\t"); if ($link = $this->prepare_link($row)) { fwrite($this->LINKS_OUT, $link['url']); } fwrite($this->LINKS_OUT, "\n"); $links_from_hierarchy[$taxon_concept_id][$hierarchy_id] = 1; } }
function get_data_from_result($outfile, $delete = true) { $parent_ids = array(); $last_hierarchy_entry_id = 0; $top_images = array(); $top_unpublished_images = array(); $hierarchy_entry_ids = array(); $visible_id = Visibility::visible()->id; if (!($RESULT = fopen($outfile, "r"))) { debug(__CLASS__ . ":" . __LINE__ . ": Couldn't open file: " . $outfile); return; } while (!feof($RESULT)) { if ($line = fgets($RESULT, 4096)) { //he.id hierarchy_entry_id, he.parent_id, do.id, do.data_rating, do.visibility_id, do.vetted_id, do.published $fields = explode("\t", trim($line)); $hierarchy_entry_id = $fields[0]; $parent_id = $fields[1]; $data_object_id = $fields[2]; $data_rating = $fields[3]; $visibility_id = $fields[4]; $vetted_id = $fields[5]; $published = $fields[6]; if ($parent_id) { $parent_ids[$parent_id] = 1; } // this is a new entry so commit existing data before adding more if ($hierarchy_entry_id != $last_hierarchy_entry_id) { $this->process_top_images($top_images, $top_unpublished_images); if ($top_images) { $hierarchy_entry_ids[] = $last_hierarchy_entry_id; } $last_hierarchy_entry_id = $hierarchy_entry_id; unset($top_images); unset($top_unpublished_images); unset($used_data_objects); $top_images = array(); $top_unpublished_images = array(); $used_data_objects = array(); } if (isset($used_data_objects[$data_object_id])) { continue; } $used_data_objects[$data_object_id] = 1; $vetted_sort_order = isset($this->vetted_sort_orders[$vetted_id]) ? $this->vetted_sort_orders[$vetted_id] : 5; if ($visibility_id == $visible_id && $published == 1) { $top_images[$hierarchy_entry_id][$vetted_sort_order][$data_rating][$data_object_id] = "{$hierarchy_entry_id}\t{$data_object_id}"; } else { $top_unpublished_images[$hierarchy_entry_id][$vetted_sort_order][$data_rating][$data_object_id] = "{$hierarchy_entry_id}\t{$data_object_id}"; } } } fclose($RESULT); unlink($outfile); $this->process_top_images($top_images, $top_unpublished_images); if ($top_images) { $hierarchy_entry_ids[] = $last_hierarchy_entry_id; } if ($delete) { $split_ids = array_chunk($hierarchy_entry_ids, 5000); while (list($key, $chunk) = each($split_ids)) { $this->mysqli->delete("DELETE FROM top_images_tmp WHERE hierarchy_entry_id IN (" . implode($chunk, ",") . ")"); $this->mysqli->delete("DELETE FROM top_unpublished_images_tmp WHERE hierarchy_entry_id IN (" . implode($chunk, ",") . ")"); } } return $parent_ids; }
public function total_user_added_data() { return $this->mysqli_slave->select_value("\n SELECT COUNT(*) count\n FROM user_added_data\n WHERE visibility_id = " . Visibility::visible()->id . "\n AND deleted_at IS NULL"); }