These are the programs and views that are used in the entry and maintenance of the Babase Master tables. Their use is fully documented in the procedure manual. The summary written here provides a statement of purpose and a mention of all updated data. The operation and behavior of the programs and views supports the table and program characteristics documented in this manual. For more information on the actual capabilities of the programs and views see the documentation in the headings of the programs' source code and the source code of the views' triggers .
The programs and views are designed to upload data in “batch” -- each run of a program uploads a single file containing multiple lines of data, each of which is then inserted into the database as a row of data in one (or more[298]) database tables.
The views presented in this section are not intended to be useful when querying. They exist to provide an upload mechanism for updating tables, and are views rather than tables to simplify overall system maintenance.
Most of the upload programs, the exception being the Psionload program, take as input a file of data arranged in tabular format. The file is expected to contain plain text, with rows on separate lines and columns separated by a single tab character. This data structure can be produced by exporting data from a spreadsheet as tab delimited text.
The programs and views upload the data into the database in an all-or-nothing fashion. Ether all the data in a uploaded file is inserted into the database or, should any error occur, none of it is. After an error the processing of the uploaded file continues so as to catch additional errors. However the input line containing the erroneous data is ignored, so the “trial”[299] insertion into the database of the subsequent lines in the uploaded file may result in spurious errors due to the “missing” data. It is left to the operator to distinguish the real errors from the false positives.
When in doubt simply correct the errors that are clearly problems, notably the first error reported, and re-run the program or re-upload into the view.
For reasons of security most browsers will remove pathnames from forms. Should a program which imports data into Babase from a file find an error in the data, rather than re-enter the pathname of the file to be uploaded simply press the browser's "reload" button. This (usually, depending on the browser) redoes the upload using the previously entered file name -- but with the new, now-corrected, data content.
Each time any of the Babase web programs successfully uploads a file into a database Babase remembers the name of the file and the database. None of the Babase programs will allow the same user to re-upload a file of the same name into the same database, until either a file with a different name is loaded into that database or until the user logs out and back in.
For more information on whether data is required to present, as well as other required characteristics of the data values, see the documentation of the specific column into which the data is stored.
The SWERB_UPLOAD view takes the place of an upload program. The Upload program can be used to insert data into this view and thence into the various SWERB Data tables.
The upcen program updates the CENSUS table. It is accessed over the web and can be found on the Babase Web site.
The upcen program updates the CENSUS table on a group-by-group basis. A single run of upcen can update CENSUS with multiple days of data on multiple individuals, but all the data must be for a single group.
Rows inserted into the CENSUS table by
upcen have a CENSUS.Cen value of TRUE.
Should a data validation error occur during the execution of upcen the CENSUS table will not be updated at all. Upcen runs in an all or nothing fashion, either all of the data supplied to it is entered into the database or none is.
Upcen takes a single file of census data arranged in tabular format, a format very similar to the data sheets filled out in the field. The file is expected to contain plain text, with rows on separate lines and columns separated by a single tab character. This data structure can be produced by exporting data from a spreadsheet as tab delimited text.
The layout of the data in the file is as follows:
The first cell of data, the one in the first column of the first row, must contain the GROUPS.Gid code for the group.
The remainder of the first row of data, the entire first row excepting the first cell, must contain the dates on which the census was taken.
In order to avoid confusion between European and American date styles, and other sorts of foolery with Excel dates, it may be a good idea to have the spreadsheet format this data as text rather than as dates.
The remainder of the first column of data, the entire first column excepting the first cell, must contain the Snames of the censused individuals.
The remainder of the table, everything excepting the first row and first column, contains the census data. Each cell represents the census taken of an individual, who's Sname appears in the first column, on a census date, the date appearing in the first row. Each cell can contain one of three possible values:
N
When N (upper case
letter N, meaning “No data”)
appears in the cell there was no census of
the given individual on the given date.
Upcen does nothing to the CENSUS table.
When no data appears in the cell the
individual was censused present. The CENSUS
table is updated with a Status code of
C for that
individual/group/day.
0
When a 0 (digit
zero) appears in the cell then individual
was censused absent. The CENSUS table is
updated with a Status code of
A for that
individual/group/day.
The MPI_UPLOAD view takes the place of an upload program. The Upload program can be used to insert data into this view and thence into the various tables related to MPIS.
The updart program uploads darting data into Babase.
As with the other data entry programs all data in the uploaded file is recorded in the database in an all or nothing fashion; the database is unchanged if any errors occur.
The updart program accepts a variety of data formats depending on the type of darting data uploaded. The format of the uploaded data is determined by the menu selection used to invoke the updart program.
For any given darting logistic data must be uploaded first. The remaining data can be uploaded in any order.
The updart program will not overwrite data on the DARTINGS table. The textual note columns on DARTINGS must be NULL before being replaced with
a value. In some cases this will help prevent the uploading
of duplicate data.[300] Updart also reports an error when successive
lines in the uploaded file have identical
sname and dartdate
values.[301]
Because much of the darting data can involve collection of multiple sets of repeated data per darting there are few checks which prevent duplicate data.
By way of example, there are no restrictions which require that all the data which pertain to a given darting be recorded in contiguous rows so repetition of a darting in a later part of an uploaded file is not detected. Care must be taken not to upload the same data twice.
Each line in the uploaded file corresponds to the darting of a single individual. The uploaded file may contain leading or trailing empty lines. No data must be indicated by an empty cell.
The uploaded file must begin with a line of column headings with the names given in each section below in the order given in the sections below. The column headings are validated but otherwise unused, with the exception of the “numbered” columns that appear in sets as described in the next paragraph. The checking of column names is to assist in the detection of data entry errors. The content of each column is as described.
The “numbered” columns, such as the
columns labeled extra_anesthN,
extra_anesth_timeN, and
extra_anesth_amtN, must be supplied in
matching sets. The N in the column name
is presented here as a placeholder and the counting numbers
1, 2,
3, etc., must be substituted in actual
use. The set of columns may be repeated as many times as
needed, or not used at all, the restriction being that the
first occurrence must use column names ending in the number
1 with successive repetitions
incrementing the “column number” by one. When
the uploaded data has more sets of columns than are needed
for a given line, a given darting, the unneeded columns are
to be left empty.
Every data format accepted by the updart program begins with the following columns in the order written here:
name
The name of the darted individual. The given value is compared in a case-insensitive fashion with BIOGRAPH.Name but is otherwise unused.
This column must contain a value.
This data is not recorded in the database but is checked for validity to assist in detection of data entry errors.
sname
The BIOGRAPH.Sname of the darted individual.
This column must contain a value.
When supplied with other darting logistic data
this data is stored in the DARTINGS.Sname
column. Otherwise it is used together with the dartdate column to identify the related
DARTINGS row.
sex
The sex of the darted individual, either
M for male or F
for female.
This column must contain a value.
This data is not recorded in the database but is checked against BIOGRAPH.Sex to assist in detection of data entry errors. The data in this column is not otherwise used.
dartdate
The date the individual was darted. When
supplied with other darting logistic data this data is
stored in the DARTINGS.Date database column. Otherwise
it is used together with the sname column to identify the
related DARTINGS row.
Logistic data is uploaded into the DARTINGS, and ANESTHS tables.
In addition to the initial columns common to all the updart upload formats the logistic data format contains the following columns:
darttime
The time the individual was darted. This data is stored in the DARTINGS.Darttime database column.
downtime
The time the individual succumbed to the anesthetic, the DARTINGS.Downtime value.
pickuptime
The time the individual was picked up by the darting team. This value is stored in the DARTINGS.Pickuptime column.
dartdrug
The ANESTHS.Drug of the anesthetic delivered by dart. This value is stored in the DARTINGS.Drug column.
extra_anesthN
The type of extra anesthetic administered, an DRUGS.Drug value. This value is stored in the ANESTHS.Drug column.
extra_anesth_timeN
The time extra anesthetic was administered. This value is stored in the ANESTHS.Antime column.
extra_anesth_amtN
The amount of extra anesthetic administered. This value is stored in the ANESTHS.Anamount column.
other_notes
Textual notes related to darting logistics. This value is stored in the DARTINGS.Logisticnotes column.
comments
General comments on the darting. This value is stored in the DARTINGS.Dartcomments column.
Morphology data is uploaded into the DARTINGS, CROWNRUMPS, CHESTS, ULNAS, and HUMERUSES tables.
In addition to the initial columns common to all the updart upload formats the morphology data format contains the following columns:
bodymass
The individual's mass. This data is stored in the DARTINGS.Mass database column.
crownrumpN
The crownrump measurement. This data is stored in the CROWNRUMPS.CRlength database column.
crobserverN
The observer who took the crownrump measurement. This data is stored in the CROWNRUMPS.CRobserver database column.
chestcircumN
The chest circumference measurement. This data is stored in the CHESTS.Chcircum database column.
unadj_chestcircumN
The unadjusted chest circumference measurement. This data is stored in the CHESTS.Chunadjusted database column.
chobserverN
The observer who took the chest circumference measurement. This data is stored in the CHESTS.Chobserver database column.
ulnaN
The ulna measurement. This data is stored in the ULNAS.Ullength database column.
unadj_ulnaN
The unadjusted ulna measurement. This data is stored in the ULNAS.Ulunadjusted database column.
ulobserverN
The observer who took the ulna measurement. This data is stored in the ULNAS.Ulobserver database column.
humerusN
The humerus measurement. This data is stored in the HUMERUSES.Hulength database column.
unadj_humerusN
The unadjusted humerus measurement. This data is stored in the HUMERUSES.Huunadjusted database column.
huobserverN
The observer who took the humerus measurement. This data is stored in the HUMERUSES.Huobserver database column.
crnotes
Notes on the crownrump measurements This data is stored in the DARTINGS.CRnotes database column.
chnotes
Notes on the chest circumference measurements This data is stored in the DARTINGS.Chnotes database column.
ulnotes
Notes on the ulna measurements This data is stored in the DARTINGS.Ulnotes database column.
hunotes
Notes on the humerus measurements This data is stored in the DARTINGS.Hunotes database column.
Physiology data is uploaded into the DARTINGS, DPHYS, PCVS, and BODYTEMPS tables.
In addition to the initial columns common to all the updart upload formats the physiology data format contains the following columns:
hematocritN
The individual's PVC. This data is stored in the PCVS.PCV database column.
bodytempN
The individual's body temperature. This data is stored in the BODYTEMPS.Btemp database column.
bodytemptimeN
Time the individual's body temperature was taken. This data is stored in the BODYTEMPS.Bttime database column.
pulse
Individual's pulse. This data is stored in the DPHYS.Pulse database column.
respiration
Individual's respiration. This data is stored in the DPHYS.Respiration database column.
r_inguinal_lymph
State of the individual's right inguinal lymph node. This data is stored in the DPHYS.Ringnode database column.
l_inguinal_lymph
State of the individual's left inguinal lymph node. This data is stored in the DPHYS.Lingnode database column.
r_axillary_lymph
State of the individual's right axillary lymph node. This data is stored in the DPHYS.Raxnode database column.
l_axillary_lymph
State of the individual's left axillary lymph node. This data is stored in the DPHYS.Laxnode database column.
r_submandibular_lymph
State of the individual's right submandibular lymph node. This data is stored in the DPHYS.Rsubmandnode database column.
l_submandibular_lymph
State of the individual's left submandibular lymph node. This data is stored in the DPHYS.Lsubmandnode database column.
other_notes_measures
Notes on physiological features. This data is stored in the DARTINGS.Dphysnotes database column.
pcvnotes
Notes on PVC measurements. This data is stored in the DARTINGS.PCVnotes database column.
btempnotes
Notes on body temperature measurements. This data is stored in the DARTINGS.Bodytempnotes database column.
This program is no longer functional. It was used to add data to the DSAMPLES table, which has been replaced by the DART_SAMPLES table and DSAMPLES view.
Physical sample related data is uploaded into the DARTINGS and DSAMPLES tables.
In addition to the initial columns common to all the updart upload formats the physical sample data format contains the following columns:
(In progress, to be added later)
Data related to teeth is uploaded into the DARTINGS and TEETH tables.
In addition to the
initial columns common to all the updart upload
formats the teeth data format contains the following
columns. Most of these columns are “special”
in that the column name is used to designate a related TOOTHCODES row, indicating the position of the
tooth within the mouth. The text written into the upload
file's column names shown here as
TOOTHCODE must be replaced with the
actual tooth code. Data related to each tooth code is
presented as a set comprising the tooth's state (TSTATES) and the tooth's condition (TCONDITIONS).
Unlike the “numbered” column headers used with other sorts of repeating data all of the TEETH related columns need not be present. Their order is also not significant. However all of the columns pertaining to a particular tooth code must be adjacent.
TOOTHCODE_tstate
The state of the tooth. (E.g. present, erupting, missing, etc.) This data is stored in the TEETH.Tstate database column.
TOOTHCODE_tcondition
The condition of the tooth. (E.g. healthy, decayed, etc.) This data is stored in the TEETH.Tstate database column.
notes
General notes on the teeth. This data is stored in the DARTINGS.Teethnotes database column.
caninenotes
General notes on the canines. This data is stored in the DARTINGS.Caninenotes database column.
Testes related data is uploaded into the DARTINGS, TESTES_ARC, and TESTES_DIAM tables.
The determination of left or right testicle is not made based on the name of the column but by the value of the Testside or Testside column.
The left and right side measurements are separate and distinct “numbered column” set. This means there need not be as many left as right side measurements.[302]
In addition to the initial columns common to all the updart upload formats the testes data format contains the following columns:
ltesteslengthN
The length of the (left) testicle. This data is stored in the TESTES_ARC.Testlength and TESTES_DIAM.Testlength database columns.
ltesteswidthN
The width of the (left) testicle. This data is stored in the TESTES_ARC.Testwidth and TESTES_DIAM.Testwidth database columns.
ltestessideN
Indication of left or right testicle. It is
presumed but not required that a value of
L be supplied indicating the length
and width are of the left testicle. This data is
stored in the TESTES_ARC.Testside and TESTES_DIAM.Testside database
columns.
rtesteslengthN
The length of the (right) testicle. This data is stored in the TESTES_ARC.Testlength and TESTES_DIAM.Testlength database columns.
rtesteswidthN
The width of the (right) testicle. This data is stored in the TESTES_ARC.Testwidth and TESTES_DIAM.Testwidth database columns.
rtestessideN
Indication of left or right testicle. It is
presumed but not required that a value of
R be supplied indicating the length
and width are of the right testicle. This data is
stored in the TESTES_ARC.Testside and TESTES_DIAM.Testside database
columns.
other_notes_measures
Notes regarding testicle measurements. This data is stored in the DARTINGS.Testesnotes database column.
The uptick program uploads into Babase data on parasite infestation collected during dartings. For any given darting it must be run after the darting logistic data is uploaded.
Each line of the uploaded file corresponds to a parasite count of a particular body part taken during a specific darting -- corresponds to a row in the TICKS table.
As with the other data entry programs all data in the uploaded file is recorded in the database in an all or nothing fashion; the database is unchanged if any errors occur.
The uptick program will not overwrite data in the DARTINGS.Ticknotes
column. This column must be NULL before being replaced with
a value. In some cases this will help prevent the uploading
of duplicate data.
Because much of the darting data can involve collection of multiple sets of repeated data per darting there are few checks which prevent duplicate data.
By way of example, there are no restrictions which require that all the data which pertain to a given darting be recorded in contiguous rows so repetition of a darting in a later part of an uploaded file is not detected. Care must be taken not to upload the same data twice.
The uploaded file may contain leading or trailing empty lines. No data must be indicated by an empty cell.
The uploaded file must begin with a line of column headings with the names given below in the order given below. The column headings are validated but otherwise unused. This is to assist in the detection of data entry errors. The content of each column is as described.
Aside from the line containing the column headings the
uploaded rows can be ordered in any fashion. There is no
requirement that the rows pertaining to a single darting be
contiguous. However, because DARTINGS.Ticknotes cannot be overwritten only one
uploaded row per darting may have a non-empty
other_notes_measures cell.
name
As described in Updart.
sname
As described in Updart.
sex
As described in Updart.
dartdate
As described in Updart.
pcount
The number of parasites found on the designated body part. This data is stored in the TICKS.Tickcount database column.
bodypart
The body part examined for parasites. This data is stored in the TICKS.Bodypart database column.
pkind
The kind of parasite counted. This data is stored in the TICKS.Tickkind database column.
pstatus
The classification of the count itself. This data is stored in the TICKS.Tickstatus database column.
pnotes
Notes on the counting of the parasites. This data is stored in the TICKS.Tickbpnotes database column.
other_notes_measures
General notes on the counting of ticks and other parasites. This data is stored in the DARTINGS.Ticknotes database column.
Because the
other_notes_measures column is stored
on DARTINGS there can only be one per
darting. To ensure this only the first row for any
given darting may contain a value for
other_notes_measures, the remaining
cells for the darting must be empty.
Psionload transfers the output of the Psion palmtop computers' focal point sampling data into Babase.
The Psionload program only knows how to load data with
the semantics of the data structure described by DATA_STRUCTURES.Data_Structure value
1. This format is
documented on the Psion Data Format
page of the Babase
Wiki.
Note that the time recorded in a Psion ad-lib row is stored in both the Start and Stop columns of the INTERACT_DATA table.
This program makes a lot of assumptions about the contents of the STYPES, ACTIVITIES, POSTURES, and NCODES tables. It was written when those tables were laden with special values[303] to support two and only two sample types: samples on adult females and samples on juveniles of any sex. In the production database, these tables are appropriately configured and this program should function normally. But if installed in a new, "clean" database, this program will certainly NOT work.
The psionload program assumes that every program[304] that uses a setupfile[305] produces an output file having identical structure and semantics. If this assumption is violated then the data will either not load, or worse yet, will load in an incorrect fashion.
Any changes in the form or semantics of the data collected with the Psions must be indicated in the Psion data by way of a change in the Psion setup id string and the DATA_STRUCTURES row referenced thereby. If the setup id string does not reflect changes in the Psion data then the data will either not load, or worse yet, will load in an incorrect fashion.
Upload uploads data into any table or view. Its primary purpose is to upload data into views; at the time of this writing PostgreSQL and its various front ends are unable to import data into views.
The name of the table may be qualified with a schema name to upload data into tables or views that are not in the babase schema.
There are 2 ways to upload NULL data values. The
easiest is to omit the column. Columns without some other
default value will be given NULL values. The second is to
check the checkbox labeled "Upload NULL Values"
and supply a input value for NULL. Data values that match
the given NULL representation will then be given a NULL
value in the database.
The default NULL representation is the empty string,
no data at all. When this representation is used data that
are omitted in the input file becomes NULL when uploaded
into the database.
A space (or multiple spaces) may be chosen as the
NULL representation. This can be difficult to discern
while operating the program.
Data to be uploaded must be in tab delimited format. The first line of the input file must contain the column names, each separated by a tab. The remaining lines of the file contain the data to be uploaded. Each line is a row of data, each column is separated from its neighbor with a tab character.
A line need not contain as many tab separated data elements as there are column names given in the first line. All unspecified data elements will be given a blank value, the empty string, just as if the tabs occurred but no data were specified.[306]
A line must not have more tab separated data elements than there are column names given in the first line.
[298] Uploading into a single row of a view can update multiple tables, and programs designed to handle specialized input data formats may update arbitrary portions of the database as needed.
[299] Once an error occurs no changes will be committed to the database.
[300] This check will not detect duplicate darting logistic data because uploading darting logistic data creates new rows in DARTINGS.
[301] Note that the test is against the text of the
sname and dartdate
as entered in the uploaded file, not, e.g., the actual
date. So this test fails when the same date is written in
two different, but valid, forms.
[302] Alternately, as usual, the uploaded cells can be empty and nothing will be added to the database.
[304] See PID_String
[305] See SID_String
[306] This may or may not result in a NULL value in
the database, depending on how the program is
invoked.