MalariaSQLReport#

The MalariaSQL report outputs epidemiological and transmission data. Because of the quantity and complexity of the data, the report output is a multi-table SQLite relational database (see DB Browser for SQLite for more information). Use the configuration parameters to manage the size of the database.

Configuration#

To generate this report, configure the following parameters in the custom_report.json file:

Parameter

Data type

Min

Max

Default

Description

Include_Drug_Status_Table

boolean

NA

NA

0

A true value (1) includes the table that provides data at each time step for each drug the person has.

Include_Health_Table

boolean

NA

NA

1

A true value (1) includes the Health table which has data for each time step for the health of an individual.

Include_Infection_Data_Table

boolean

NA

NA

1

A true value (1) includes the InfectionData table which has data for each time step for each active infection.

End_Day

float

0

3.40E+38

3.40E+38

The day to stop collecting data for the report.

Start_Day

float

0

3.40E+38

0

The day to start distributing interventions.

{
    "Reports": [
        {
            "Include_Drug_Status_Table": 1,
            "Include_Health_Table": 1,
            "Include_Infection_Data_Table": 1,
            "Start_Day": 100,
            "End_Day": 900,
            "class": "MalariaSQLReport"
        }
    ],
    "Use_Defaults": 1
}

SQL database and table structures#

Because the output is a relational database, most of the tables have a primary key (highlighted in yellow on the figure below) that is a combination of RunNumber and another value. For example, in the Humans table, the primary key is the combination of RunNumber and HumanID. Because the RunNumber is part of the primary key, you can combine data from multiple runs.

The following chart and tables describe the SQL data tables produced by this report. The “many” and “1” notations in the chart show you which tables have a one-to-many row relationship and which have a many-to-one row relationship with the rows in another table.

../_images/malaria-sql-report.png

Schematic of the SQL data tables produced by this report#

Humans table#

The Humans table tracks of all the humans in the simulation. It contains one row for each individual in the scenario. This table has a one-to-many relationship with the Infections and Health tables.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

HumanID

integer

The unique ID of the individual in the simulation.

Gender

text

The gender of the individual. Possible values are M or F.

HomeNodeID

integer

The external ID (NodeID in demographics) of the home node where the individual starts out.

InitialAgeDays

float

The age, in days, of the individual when they entered the simulation.

SimTimeAdded

float

The time in the simulation when the individual was added.

Health table#

The Health table tracks the health of each individual while they are alive in the simulation. There is one record for each timestep during which an individual was alive. This table has a many-to-one relationship with the Humans table. Query the SevereCaseType table if you want to translate the integer value of ServerCaseTypeID to a text value.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

HumanID

integer

The unique ID of the individual in the simulation.

NodeID

integer

The external ID of the node the individual is in at this time.

SimTime

float

The time in the simulation that this data was collected.

Infectiousness

float

An infectiousness value based on the individual’s number of mature female gametocytes and fever status.

RelativeBitingRate

float

A number indicating if this individual is more likely to be bitten by mosquitoes. This can include any biting rates you set and/or a value based on the age/area of the individual.

IsClinicalCase

boolean

A value of true (1) means that the individual has a fever greater than both Clinical_Fever_Threshold_Low and Clinical_Fever_Threshold_High.

IsSevereCase

boolean

A value of true (1) means the individual has a fever greater than Clinical_Fever_Threshold_Low and was probabilistically determined to have severe disease.

SevereCaseTypeID

integer

The unique ID of the type of severe disease.

InvMicrolitersBlood

float

The inverse amount of blood the individual has in microliters.

RedBloodCellCount

float

The actual number of red blood cells the individual has.

Cytokines

float

The number of cytokines that have been stimulated by the presence of all malaria parasites.

HRP2

float

The amount of Histidine Rich Protein 2 in the individual’s blood stream.

PfEMP1VariantFraction

float

The number of PfEMP1 antibodies that an individual has divided by the total number possible in the scenario (Falciparum_PfEMP1_Variants).

SevereCaseType table#

This is a small, lookup table for translating the integer value of SevereCaseTypeID in the Health table into the actual name of the case type. The SevereCaseType names are based on the SevereCaseTypesEnum parameter.

ColumnName

Data type

Description

SevereCaseTypeID

integer

The unique ID of the severe case type.

Name

enum

The name of the cause of the sever disease. Possible values are NONE, ANEMIA, PARASITES, or FEVER.

Infections table#

The Infections table tracks the infections that occurred in humans during the simulation. There is one row for each infection that occurred. This table has a many-to-one relationship with the Infections and ParasiteGenomes tables.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

InfectionID

integer

The unique ID of the infection in the simulation.

HumanID

integer

The unique ID of the individual in the simulation.

GenomeID

integer

The unique ID of the genome derived from a hash code function on the nucleotide sequence of the genome.

SimTimeCreated

float

The time in the simulation that the infection was created.

InfectionData table#

The InfectionData table contains data about each infection while it was active in the simulation. This table has a one-to-many relationship with the Infections table.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

InfectionID

integer

The unique ID of the infection in the simulation.

SimTime

float

The time in the simulation that this data was collected.

InfectedRedBloodCells

integer

The number of infected red blood cells due to this infection at this time.

NumMatureGametocytesFemale

integer

The number of mature female gametocytes in this infection at this time.

NumMatureGametocytesMale

integer

The number of mature male gametocytes in this infection at this time.

ParasiteGenomes table#

The ParasiteGenomes table contains one row of information about each unique parasite genome. This table has a one-to many relationship with the GenomeSequenceData and Infections tables.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

GenomeID

integer

The unique ID of the genome derived from a hash code function on the nucleotide sequence of the genome.

BarcodeID

integer

The unique ID/hash of the barcode portion of the genome, based just on the barcode. It does not include the allele roots.

Barcode

string

The text representation of the barcode where each allele is A, C, G, or T.

DrugStatus table#

The DrugStatus table keeps track of the status of each drug in a person. Each person may have multiple records per time step, so the table may be large. Information on each drug will continue until the drug has expired.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

HumanID

integer

The unique ID of the person in the simulation.

SimTime

float

The time in the simulation that the data is being collected.

DrugName

string

The name of the drug indicated in the intervention as defined by the configuration parameter Malaria_Drug_Params. Depending on the intervention, this may be a concatenated value when a person takes multiple pills in one dose.

CurrentEfficacy

float

The current efficacy of the drug(s).

NumRemainingDoses

integer

The number of doses that the person has yet to take. Depending on the intervention, the dose may involve multiple pills.

GenomeSequenceData table#

This table contains the sequence and allele root information for each genome. The RunNumber and GenomeID indicate the particular genome, and the GenomeLocation indicates the location of the values. The number of rows for a particular genome depend on the number of barcode positions and whether or not the simulation tracked the MSP and PfEMP1 variants in the genome. This table has a many-to-one relationship with the ParasiteGenomes and Locations tables.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

GenomeID

integer

The unique ID of the genome derived from a hash code function on the nucleotide sequence of the genome.

NucleotideSequence

integer

The value of the allele at the genome location.

AlleleRoots

integer

the Infection ID from where this allele originated.

GenomeLocation

integer

The location in the genome, which is a value from 1 to 22790000.

Locations table#

The Locations table tracks what type of data is contained in a particular genome location. This table has a one-to-many relationship with the GenomeSequenceData table and a many-to-one relationship with the LocationType lookup table.

ColumnName

Data type

Description

RunNumber

integer

This value from the Run_Number parameter is the seed to the model’s random number generator.

GenomeLocation

integer

The location in the genome, which is a value from 1 to 22790000.

LocationTypeID

integer

The unique ID of the location type, which indicates if the location is used in the Barcode, Drug Resistance, HRPstatus, MSP, or Major Epitopes. Query the LocationType table to obtain the actual location name.

LocationType table#

This is a small lookup table that provides the full name of the location (via the keys in the Locations and GenomeSequenceData tables) used when querying the ParasiteGenomes table.

ColumnName

Data type

Description

LocationTypeID

integer

The unique ID of the location type.

Name

enum

The name of the location. Possible values are BARCODE, DRUG_RESISTANCE, HRP_STATUS, MPS, or PFEMP1_VARIANTS.