<commit_before># 
# The Paleobiology Database
# 
#   OccurrenceTables.pm
# 

package OccurrenceTables;

use strict;

use base 'Exporter';

use Carp qw(carp croak);
use Try::Tiny;

use CoreFunction qw(activateTables);
use CollectionTables qw($COLL_MATRIX);
use IntervalTables qw($INTERVAL_DATA $SCALE_MAP);
use TaxonDefs qw(@TREE_TABLE_LIST);
use ConsoleLog qw(logMessage);

our (@EXPORT_OK) = qw(buildOccurrenceTables buildDiversityTables
		      $OCC_MATRIX $OCC_EXTRA $OCC_TAXON $OCC_REF $DIV_SAMPLE);

our $OCC_MATRIX = "occ_matrix";
our $OCC_EXTRA = "occ_extra";
our $OCC_TAXON = "occ_taxon";
our $OCC_REF = "occ_ref";

our $OCC_MATRIX_WORK = "omn";
our $OCC_EXTRA_WORK = "oen";
our $OCC_TAXON_WORK = "otn";
our $OCC_REF_WORK = "orn";


# buildOccurrenceTables ( dbh )
# 
# Build the occurrence matrix, recording which taxonomic concepts are
# associated with which collections in which geological and chronological
# locations.  This table is used to satisfy the bulk of the queries from the
# front-end application.  This function also builds an occurrence summary
# table, summarizing occurrence information by taxon.

sub buildOccurrenceTables {
    
    my ($dbh, $options) = @_;
    
    my ($sql, $result, $count, $extra);
    
    # Create a clean working table which will become the new occurrence
    # matrix.
    
    logMessage(1, "Building occurrence tables");
    
    $result = $dbh->do("DROP TABLE IF EXISTS $OCC_MATRIX_WORK");
    $result = $dbh->do("CREATE TABLE $OCC_MATRIX_WORK (
				occurrence_no int unsigned not null,
				reid_no int unsigned not null,
				collection_no int unsigned not null,
				taxon_no int unsigned not null,
				orig_no int unsigned not null,
				latest_ident boolean not null,
				genus_name varchar(255),
				genus_reso varchar(255),
				subgenus_name varchar(255),
				subgenus_reso varchar(255),
				species_name varchar(255),
				species_reso varchar(255),
				plant_organ varchar(255),
				plant_organ2 varchar(255),
				early_age decimal(9,5),
				late_age decimal(9,5),
				reference_no int unsigned not null,
				authorizer_no int unsigned not null,
				enterer_no int unsigned not null,
				modifier_no int unsigned not null,
				created timestamp null,
				modified timestamp null,
				primary key (occurrence_no, reid_no)) ENGINE=MyISAM");
    
    $result = $dbh->do("DROP TABLE IF EXISTS $OCC_EXTRA_WORK");
    $result = $dbh->do("CREATE TABLE $OCC_EXTRA_WORK (
				occurrence_no int unsigned not null,
				reid_no int unsigned not null,
				abund_value varchar(255),
				abund_unit varchar(255),
				comments text,
				primary key (occurrence_no, reid_no)) ENGINE=MyISAM");
    
    # Add one row for every occurrence in the database, to both $OCC_MATRIX
    # and $OCC_EXTRA.
    
    logMessage(2, "    inserting occurrences...");
    
    $sql = "	INSERT INTO $OCC_MATRIX_WORK
		       (occurrence_no, reid_no, latest_ident, collection_no, taxon_no, orig_no,
			genus_name, genus_reso, subgenus_name, subgenus_reso, 
			species_name, species_reso, plant_organ, plant_organ2,
			early_age, late_age, reference_no,
			authorizer_no, enterer_no, modifier_no, created, modified)
		SELECT o.occurrence_no, 0, true, o.collection_no, o.taxon_no, a.orig_no, 
			o.genus_name, o.genus_reso, o.subgenus_name, o.subgenus_reso,
			o.species_name, o.species_reso, o.plant_organ, o.plant_organ2,
			ei.early_age, li.late_age,
			if(o.reference_no > 0, o.reference_no, c.reference_no),
			o.authorizer_no, o.enterer_no, o.modifier_no, o.created, o.modified
		FROM occurrences as o JOIN coll_matrix as c using (collection_no)
			LEFT JOIN $INTERVAL_DATA as ei on ei.interval_no = c.early_int_no
			LEFT JOIN $INTERVAL_DATA as li on li.interval_no = c.late_int_no
			LEFT JOIN authorities as a using (taxon_no)";
    
    $extra = $dbh->do($sql);
    
    $sql = "	INSERT INTO $OCC_EXTRA_WORK
		       (occurrence_no, reid_no, comments, abund_value, abund_unit)
		SELECT o.occurrence_no, 0, o.comments, o.abund_value, o.abund_unit
		FROM occurrences as o";
    
    $count = $dbh->do($sql);
    
    logMessage(2, "      $count occurrences");
    
    # Then add one row for every reidentification in the database, to both
    # $OCC_MATRIX and $OCC_EXTRA
    
    $sql = "	INSERT INTO $OCC_MATRIX_WORK
		       (occurrence_no, reid_no, latest_ident, collection_no, taxon_no, orig_no,
			genus_name, genus_reso, subgenus_name, subgenus_reso,
			species_name, species_reso, plant_organ,
			early_age, late_age, reference_no,
			authorizer_no, enterer_no, modifier_no, created, modified)
		SELECT re.occurrence_no, re.reid_no, if(re.most_recent = 'YES', 1, 0), re.collection_no, re.taxon_no, a.orig_no, 
			re.genus_name, re.genus_reso, re.subgenus_name, re.subgenus_reso,
			re.species_name, re.species_reso, re.plant_organ,
			ei.early_age, li.late_age, re.reference_no,
			re.authorizer_no, re.enterer_no, re.modifier_no, re.created, re.modified
		FROM reidentifications as re JOIN coll_matrix as c using (collection_no)
			LEFT JOIN $INTERVAL_DATA as ei on ei.interval_no = c.early_int_no
			LEFT JOIN $INTERVAL_DATA as li on li.interval_no = c.late_int_no
			LEFT JOIN authorities as a using (taxon_no)";
    
    $count = $dbh->do($sql);
    
    $sql = "	INSERT INTO $OCC_EXTRA_WORK
		       (occurrence_no, reid_no, comments)
		SELECT re.occurrence_no, re.reid_no, re.comments
		FROM reidentifications as re";
    
    $extra = $dbh->do($sql);
    
    logMessage(2, "      $count re-identifications");
    
    # For each reidentification, the corresponding record in occ_matrix drawn
    # from the original occurrence record needs to be "de-selected".
    
    logMessage(2, "    updating re-identified occurrences...");
    
    $sql = "	UPDATE $OCC_MATRIX_WORK as m
			JOIN reidentifications as re on re.occurrence_no = m.occurrence_no 
				and re.most_recent = 'YES'
		SET m.latest_ident = false WHERE m.reid_no = 0";
    
    $count = $dbh->do($sql);
    
    # Now add some indices to the main occurrence relation, which is more
    # efficient to do now that the table is populated.
    
    logMessage(2, "    indexing by selection...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX selection (occurrence_no, selected)");
    
    logMessage(2, "    indexing by collection...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (collection_no)");
    
    logMessage(2, "    indexing by taxonomic identification...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (genus_name)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (subgenus_name)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (species_name)");
    
    logMessage(2, "    indexing by taxonomic concept...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (orig_no)");
    
    logMessage(2, "    indexing by age boundaries...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (early_age)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (late_age)");
    
    logMessage(2, "    indexing by reference_no...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (reference_no)");
    
    logMessage(2, "    indexing by person...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (authorizer_no)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (enterer_no)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (modifier_no)");
    <commit_msg>Bug fix: crashed on building occurrence matrix.<commit_after># 
# The Paleobiology Database
# 
#   OccurrenceTables.pm
# 

package OccurrenceTables;

use strict;

use base 'Exporter';

use Carp qw(carp croak);
use Try::Tiny;

use CoreFunction qw(activateTables);
use CollectionTables qw($COLL_MATRIX);
use IntervalTables qw($INTERVAL_DATA $SCALE_MAP);
use TaxonDefs qw(@TREE_TABLE_LIST);
use ConsoleLog qw(logMessage);

our (@EXPORT_OK) = qw(buildOccurrenceTables buildDiversityTables
		      $OCC_MATRIX $OCC_EXTRA $OCC_TAXON $OCC_REF $DIV_SAMPLE);

our $OCC_MATRIX = "occ_matrix";
our $OCC_EXTRA = "occ_extra";
our $OCC_TAXON = "occ_taxon";
our $OCC_REF = "occ_ref";

our $OCC_MATRIX_WORK = "omn";
our $OCC_EXTRA_WORK = "oen";
our $OCC_TAXON_WORK = "otn";
our $OCC_REF_WORK = "orn";


# buildOccurrenceTables ( dbh )
# 
# Build the occurrence matrix, recording which taxonomic concepts are
# associated with which collections in which geological and chronological
# locations.  This table is used to satisfy the bulk of the queries from the
# front-end application.  This function also builds an occurrence summary
# table, summarizing occurrence information by taxon.

sub buildOccurrenceTables {
    
    my ($dbh, $options) = @_;
    
    my ($sql, $result, $count, $extra);
    
    # Create a clean working table which will become the new occurrence
    # matrix.
    
    logMessage(1, "Building occurrence tables");
    
    $result = $dbh->do("DROP TABLE IF EXISTS $OCC_MATRIX_WORK");
    $result = $dbh->do("CREATE TABLE $OCC_MATRIX_WORK (
				occurrence_no int unsigned not null,
				reid_no int unsigned not null,
				collection_no int unsigned not null,
				taxon_no int unsigned not null,
				orig_no int unsigned not null,
				latest_ident boolean not null,
				genus_name varchar(255),
				genus_reso varchar(255),
				subgenus_name varchar(255),
				subgenus_reso varchar(255),
				species_name varchar(255),
				species_reso varchar(255),
				plant_organ varchar(255),
				plant_organ2 varchar(255),
				early_age decimal(9,5),
				late_age decimal(9,5),
				reference_no int unsigned not null,
				authorizer_no int unsigned not null,
				enterer_no int unsigned not null,
				modifier_no int unsigned not null,
				created timestamp null,
				modified timestamp null,
				primary key (occurrence_no, reid_no)) ENGINE=MyISAM");
    
    $result = $dbh->do("DROP TABLE IF EXISTS $OCC_EXTRA_WORK");
    $result = $dbh->do("CREATE TABLE $OCC_EXTRA_WORK (
				occurrence_no int unsigned not null,
				reid_no int unsigned not null,
				abund_value varchar(255),
				abund_unit varchar(255),
				comments text,
				primary key (occurrence_no, reid_no)) ENGINE=MyISAM");
    
    # Add one row for every occurrence in the database, to both $OCC_MATRIX
    # and $OCC_EXTRA.
    
    logMessage(2, "    inserting occurrences...");
    
    $sql = "	INSERT INTO $OCC_MATRIX_WORK
		       (occurrence_no, reid_no, latest_ident, collection_no, taxon_no, orig_no,
			genus_name, genus_reso, subgenus_name, subgenus_reso, 
			species_name, species_reso, plant_organ, plant_organ2,
			early_age, late_age, reference_no,
			authorizer_no, enterer_no, modifier_no, created, modified)
		SELECT o.occurrence_no, 0, true, o.collection_no, o.taxon_no, a.orig_no, 
			o.genus_name, o.genus_reso, o.subgenus_name, o.subgenus_reso,
			o.species_name, o.species_reso, o.plant_organ, o.plant_organ2,
			ei.early_age, li.late_age,
			if(o.reference_no > 0, o.reference_no, c.reference_no),
			o.authorizer_no, o.enterer_no, o.modifier_no, o.created, o.modified
		FROM occurrences as o JOIN coll_matrix as c using (collection_no)
			LEFT JOIN $INTERVAL_DATA as ei on ei.interval_no = c.early_int_no
			LEFT JOIN $INTERVAL_DATA as li on li.interval_no = c.late_int_no
			LEFT JOIN authorities as a using (taxon_no)";
    
    $extra = $dbh->do($sql);
    
    $sql = "	INSERT INTO $OCC_EXTRA_WORK
		       (occurrence_no, reid_no, comments, abund_value, abund_unit)
		SELECT o.occurrence_no, 0, o.comments, o.abund_value, o.abund_unit
		FROM occurrences as o";
    
    $count = $dbh->do($sql);
    
    logMessage(2, "      $count occurrences");
    
    # Then add one row for every reidentification in the database, to both
    # $OCC_MATRIX and $OCC_EXTRA
    
    $sql = "	INSERT INTO $OCC_MATRIX_WORK
		       (occurrence_no, reid_no, latest_ident, collection_no, taxon_no, orig_no,
			genus_name, genus_reso, subgenus_name, subgenus_reso,
			species_name, species_reso, plant_organ,
			early_age, late_age, reference_no,
			authorizer_no, enterer_no, modifier_no, created, modified)
		SELECT re.occurrence_no, re.reid_no, if(re.most_recent = 'YES', 1, 0), re.collection_no, re.taxon_no, a.orig_no, 
			re.genus_name, re.genus_reso, re.subgenus_name, re.subgenus_reso,
			re.species_name, re.species_reso, re.plant_organ,
			ei.early_age, li.late_age, re.reference_no,
			re.authorizer_no, re.enterer_no, re.modifier_no, re.created, re.modified
		FROM reidentifications as re JOIN coll_matrix as c using (collection_no)
			LEFT JOIN $INTERVAL_DATA as ei on ei.interval_no = c.early_int_no
			LEFT JOIN $INTERVAL_DATA as li on li.interval_no = c.late_int_no
			LEFT JOIN authorities as a using (taxon_no)";
    
    $count = $dbh->do($sql);
    
    $sql = "	INSERT INTO $OCC_EXTRA_WORK
		       (occurrence_no, reid_no, comments)
		SELECT re.occurrence_no, re.reid_no, re.comments
		FROM reidentifications as re";
    
    $extra = $dbh->do($sql);
    
    logMessage(2, "      $count re-identifications");
    
    # For each reidentification, the corresponding record in occ_matrix drawn
    # from the original occurrence record needs to be "de-selected".
    
    logMessage(2, "    updating re-identified occurrences...");
    
    $sql = "	UPDATE $OCC_MATRIX_WORK as m
			JOIN reidentifications as re on re.occurrence_no = m.occurrence_no 
				and re.most_recent = 'YES'
		SET m.latest_ident = false WHERE m.reid_no = 0";
    
    $count = $dbh->do($sql);
    
    # Now add some indices to the main occurrence relation, which is more
    # efficient to do now that the table is populated.
    
    logMessage(2, "    indexing by selection...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX selection (occurrence_no, latest_ident)");
    
    logMessage(2, "    indexing by collection...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (collection_no)");
    
    logMessage(2, "    indexing by taxonomic identification...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (genus_name)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (subgenus_name)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (species_name)");
    
    logMessage(2, "    indexing by taxonomic concept...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (orig_no)");
    
    logMessage(2, "    indexing by age boundaries...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (early_age)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (late_age)");
    
    logMessage(2, "    indexing by reference_no...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (reference_no)");
    
    logMessage(2, "    indexing by person...");
    
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (authorizer_no)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (enterer_no)");
    $result = $dbh->do("ALTER TABLE $OCC_MATRIX_WORK ADD INDEX (modifier_no)");
    
{'fraction_non_alphanumeric': 0.10559914882298178, 'fraction_numerical': 0.007314802500332491, 'mean_word_length': 3.6474042027194065, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3675225', 'n_tokens_mistral': 5882, 'n_tokens_neox': 5343, 'n_words': 1394}