select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` from information_{schema.TABLES} where table_schema = "db_webqtl" order by data_length;

97 rows in set, 1 warning (0.01 sec)

All *Data tables are large

According to the meta data:

This table tracks access time and IP addresses. Used for logging in registered users and tracking cookies.

select * from AccessLog limit 5;

select * from AccessLog order by accesstime desc limit 5;

Quite a few trait page hits:

select count(*) from AccessLog;

show indexes from AccessLog;

This table is being used by both GN1 and GN2 from the trait pages!

grep -ir AccessLog *|grep -e "^gn1\|^gn2"|grep \.py|grep -v doc

gn1/web/webqtl/showTrait/ShowTraitPage.py: query = "SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn1/web/webqtl/showTrait/ShowTraitPage.py: self.cursor.execute("insert into AccessLog(accesstime,ip_address) values(Now(),%s)" ,user_ip) gn1/web/webqtl/textUI/cmdClass.py: query = """SELECT count(id) FROM AccessLog WHERE ip_address = %s AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(accesstime)<86400""" gn1/web/webqtl/textUI/cmdClass.py: query = """INSERT INTO AccessLog(accesstime,ip_address) values(Now(),%s)""" gn2/wqflask/wqflask/show_trait/show_traitpage.py: query = "SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn2/wqflask/wqflask/show_trait/show_traitpage.py: self.cursor.execute("insert into AccessLog(accesstime,ip_address) values(Now(),%s)", user_ip)

When looking at the code in GN1 and GN2 it restricts the daily use of the trait data page (set to 1,000 - whoever reaches that?). Unlike mentioned in the schema description, this table does not keep track of cookies.

From the code it looks like GN2 uses a mixture of Redis and sqlalchemy to keep track of logged in sessions (see gn2/wqflask/wqflask/user_manager.py) and cookies through a user_uuid in model.py.

In gn2/wqflask/wqflask/templates/collections/view_{anonymous.html} it show_traitpage appears to be loaded (need to check).

Probesetfreeze refers to AvgMethod

Snp table (all snps)

Mapping in GN1 shows snps when you select a chromosome.

Metadata

?

Default mm9, column for mm8

Menu for BXD (linkouts)

Arthur?

Information/metadata

Arthur private/public

Hooked in API (URL encoding)

GN2 only (see menu bar)

Probe information

(will be deprecated)

Linkout and not important

Not important. GeneWiki notes function classification.

Deprecate.

Interspecies gene comparison

Track info

Rat list

Link outs

Wiki info (nightly updated from NCBI)

XRef should be foreign keys

SNP or marker info

Belongs to someone else

Allele info

Big menu (Freeze refers to menu)

SE standard err, not used

Very important. Key links between Geno, GenoData

GO terms

Heritability for probeset(?)

Homology, not used much

Group in menu

Indel Snp browser (variant browser Gn1)

Infra system PhP

Data Info button

Infosystem users has separate entries

Also Investigators, User, Organizations,

Lit. Correlations Prof. Ramin

GN2 login info

Old

GN1

GN2

pheno publishfreeze (menu) xref (keys) xref links to publish (pubmed), phenotype, pubishdata geno genofreeze xref (keys) xref links to publish (pubmed), genotype, genodata probeset/expr. probesetfreeze xref (keys) xref links to publish (pubmed), probeset, probesetdata probe/expr. probefreeze xref (keys) xref links to publish (pubmed), probe, probedata

Each dataset has 3 values (real value (1), number of samples (2), stderr (3))

NStrain = number of phenotype samples

ProbesetFreeze contains all data, incl. metabolomic.

This table contains names, full descriptions, and short symbols for traits and phenotype used primarily in the Published Phenotypes databases.

Contains 10k rows, March 2016, of which 5000 are for the BXDs).

5 rows in set (0.00 sec)

Table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be deletable since it is only used by the Probe Table display in GN1. Not used in GN2 (double-check).