HIRIS: HIV-1 Reservoirs Integration Sites “high-rise”

Glossary

Data type and attribute terms are defined below. You should also refer to the Data usage guidelines in the manual for more information.

landmark

The chromosome or RefSeq accession in which the reported integration site is found. See the section on landmarks in the manual.

orientation

Either F or R (forward or reverse) to indicate the direction of the provirus with respect to the chromosome/landmark

ltr_end

Either 5p or 3p to indicate which viral LTR was sequenced out of to determine the IS

integration_environment

Either in vivo or in vitro indicating where the integration event took place. Refer to the Data usage guidelines in the manual for more information.

gene_type

A fixed set of names for various classes of genes as used by NCBI Gene.

text

An arbitrary blob of text, which may be very short or very long

varchar

A short bit of text, usually limited in length

integer

A whole number

bigint

A whole number which may be very large

int4range

A range between two whole numbers, useful for checking region overlap, containment, adjacency, and other similar operations

jsonb

A JSON document, which can store many optional fields and values. These are used to record information which varies from source to source. Subject and PubMed ID (if any) are stored in a jsonb column, for example.

text[]

varchar[]

integer[]

An array (or list) of text, varchar, or integer values, respectively. For tables exported as CSV files, values will be separated by a pipe character |.

not null

Indicates a column which cannot be empty. All rows will have a value for the column.

refers to another table

Indicates a column whose value refers to a row in another table. These form the relationships between rows and tables in a relational database.

Sample document fields

If you’re writing SQL to query the database, you’re likely going to want to extract data fields out of the sample JSON document associated with each integration site observation. The document contains optional fields such as subject, pcr, pubmed_id. Below is an example query which demonstrates how to extract those fields into a column in a custom SQL query:

SELECT *,
       sample->>'subject'   as subject,
       sample->>'pcr'       as pcr,
       sample->>'pubmed_id' as pubid
  FROM integration

The following table summarizes what sample fields are currently in use: