The key elements of the Database
are Tables each containing
a number of Fields (Columns).
Machine-readable names of elements Table and Field
represented as attribute 'name'.
name : mainversion : 1.0xsi:schemaLocation : http://crystallography.net/xml/schema/relational-database http://crystallography.net/xml/schema/relational-database-restfuldb/relational-database-restfuldb_v0.1.0-rc.18.xsd
FROM
(((((((SELECT
`exper`.`label` AS `label`,
`exper`.`id` AS `id_tsa`,
`exper`.`kb_obs` AS `kb_obs_tsa`,
`exper`.`compound_max_concentration` AS `cmpd_maxc_tsa`,
NULL AS `id_itc`,
NULL AS `kb_obs_itc`,
NULL AS `id_sfa`,
NULL AS `kb_obs_sfa`,
NULL AS `cmpd_maxc_sfa`,
NULL AS `id_spr`,
NULL AS `kb_obs_spr`,
NULL AS `cmpd_maxc_spr`,
`exper`.`temperature` AS `temperature`,
`exper`.`ph` AS `ph`,
`exper`.`compound_id` AS `compound_id`,
`exper`.`protein_label` AS `protein_label`,
`exper`.`primary_group_label` AS `primary_group_label`,
`exper`.`public` AS `public`,
`exper`.`revision_id` AS `revision_id`
FROM
`BVTS`.`experiment_tsa_protein_compound_view` `exper`
WHERE
`exper`.`public` = 1) UNION SELECT
`exper`.`label` AS `label`,
NULL AS `id_tsa`,
NULL AS `kb_obs_tsa`,
NULL AS `cmpd_maxc_tsa`,
`exper`.`id` AS `id_itc`,
`exper`.`kb_obs` AS `kb_obs_itc`,
NULL AS `id_sfa`,
NULL AS `kb_obs_sfa`,
NULL AS `cmpd_maxc_sfa`,
NULL AS `id_spr`,
NULL AS `kb_obs_spr`,
NULL AS `cmpd_maxc_spr`,
`exper`.`temperature` AS `temperature`,
`exper`.`ph` AS `ph`,
`exper`.`compound_id` AS `compound_id`,
`exper`.`protein_label` AS `protein_label`,
`exper`.`primary_group_label` AS `primary_group_label`,
`exper`.`public` AS `public`,
`exper`.`revision_id` AS `revision_id`
FROM
`BVTS`.`experiment_itc_protein_compound_view` `exper`
WHERE
`exper`.`public` = 1 UNION SELECT
`exper`.`label` AS `label`,
NULL AS `id_tsa`,
NULL AS `kb_obs_tsa`,
NULL AS `cmpd_maxc_tsa`,
NULL AS `id_itc`,
NULL AS `kb_obs_itc`,
`exper`.`id` AS `id_sfa`,
1 / (`exper`.`ic50` / 1000000000) AS `kb_obs_sfa`,
`exper`.`compound_max_concentration` AS `cmpd_maxc_sfa`,
NULL AS `id_spr`,
NULL AS `kb_obs_spr`,
NULL AS `cmpd_maxc_spr`,
`exper`.`temperature` AS `temperature`,
`exper`.`ph` AS `ph`,
`exper`.`compound_id` AS `compound_id`,
`exper`.`protein_label` AS `protein_label`,
`exper`.`primary_group_label` AS `primary_group_label`,
`exper`.`public` AS `public`,
`exper`.`revision_id` AS `revision_id`
FROM
`BVTS`.`experiment_sfa_protein_compound_view` `exper`
WHERE
`exper`.`public` = 1 UNION SELECT
`exper`.`label` AS `label`,
NULL AS `id_tsa`,
NULL AS `kb_obs_tsa`,
NULL AS `cmpd_maxc_tsa`,
NULL AS `id_itc`,
NULL AS `kb_obs_itc`,
NULL AS `id_sfa`,
NULL AS `kb_obs_sfa`,
NULL AS `cmpd_maxc_sfa`,
`exper`.`id` AS `id_spr`,
1 / `exper`.`k_ed_obs` AS `kb_obs_spr`,
`exper`.`compound_max_concentration` AS `cmpd_maxc_spr`,
`exper`.`temperature` AS `temperature`,
`exper`.`ph` AS `ph`,
`exper`.`compound_id` AS `compound_id`,
`exper`.`protein_label` AS `protein_label`,
`exper`.`primary_group_label` AS `primary_group_label`,
`exper`.`public` AS `public`,
`exper`.`revision_id` AS `revision_id`
FROM
`BVTS`.`experiment_spr_protein_compound_view` `exper`
WHERE
`exper`.`public` = 1) `experu`
JOIN `BVTS`.`compound` `c` ON (`c`.`id` = `experu`.`compound_id`))
LEFT JOIN `BVTS`.`protein_group` `pg` ON (`pg`.`label` = `experu`.`primary_group_label`))
JOIN `BVTS`.`protein` `p` ON (`p`.`label` = `experu`.`protein_label`))
JOIN `BVTS`.`best_compound_protonation_pka` `protcpka` ON ((`experu`.`compound_id` = `protcpka`.`compound_id`)
AND ABS(`experu`.`temperature` - `protcpka`.`temperature`) < 3)
JOIN `BVTS`.`best_protein_protonation_pka` `protppka` ON ((`experu`.`protein_label` = `protppka`.`protein_label`)
AND ABS(`experu`.`temperature` - `protppka`.`temperature`) < 3)))
GROUP BY `c`.`id` , `pg`.`name`
ORDER BY `c`.`id` , `pg`.`name`
concat_ws('-',c.id, experu.label)
c.id
c.name
c.formula_file
c.smiles
group_concat(DISTINCT p.label ORDER BY p.label SEPARATOR ' ')
group_concat(DISTINCT p.name ORDER BY p.name SEPARATOR ' ')
pg.name
GROUP_CONCAT(DISTINCT experu.temperature ORDER BY experu.temperature SEPARATOR ' ')
AVG(experu.temperature)
group_concat(DISTINCT experu.id_tsa ORDER BY experu.id_tsa SEPARATOR ' ')
AVG(LOG10(1 / (experu.kb_obs_tsa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
COUNT(DISTINCT experu.id_tsa)
MIN(LOG10(1 / (experu.kb_obs_tsa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(LOG10(1 / (experu.kb_obs_tsa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
STD(LOG10(1 / (experu.kb_obs_tsa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(experu.cmpd_maxc_tsa)
group_concat(DISTINCT experu.id_itc ORDER BY experu.id_itc SEPARATOR ' ')
AVG(LOG10(1 / (experu.kb_obs_itc *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
COUNT(DISTINCT experu.id_itc)
MIN(LOG10(1 / (experu.kb_obs_itc *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(LOG10(1 / (experu.kb_obs_itc *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
STD(LOG10(1 / (experu.kb_obs_itc *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
group_concat(DISTINCT experu.id_sfa ORDER BY experu.id_spr SEPARATOR ' ')
AVG(LOG10(1 / (experu.kb_obs_sfa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
COUNT(DISTINCT experu.id_sfa)
MIN(LOG10(1 / (experu.kb_obs_sfa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(LOG10(1 / (experu.kb_obs_sfa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
STD(LOG10(1 / (experu.kb_obs_sfa *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(experu.cmpd_maxc_sfa)
group_concat(DISTINCT experu.id_spr ORDER BY experu.id_spr SEPARATOR ' ')
AVG(LOG10(1 / (experu.kb_obs_spr *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
COUNT(DISTINCT experu.id_spr)
MIN(LOG10(1 / (experu.kb_obs_spr *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(LOG10(1 / (experu.kb_obs_spr *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
STD(LOG10(1 / (experu.kb_obs_spr *
(1 + power(10, protcpka.pka - experu.ph)) *
(1 + power(10, experu.ph - protppka.pka_zn)))
))
MAX(experu.cmpd_maxc_spr)
MAX(experu.revision_id)
FROM
(SELECT
exper.label AS label,
exper.id AS id_tsa,
1 / exper.kb_obs AS kd_obs_tsa,
exper.compound_max_concentration AS cmpd_maxc_tsa,
NULL AS id_itc,
NULL AS kd_obs_itc,
NULL AS id_sfa,
NULL AS ic50,
NULL AS cmpd_maxc_sfa,
NULL AS id_spr,
NULL AS k_ed_obs,
NULL AS cmpd_maxc_spr,
exper.temperature AS temperature,
exper.compound_id AS compound_id,
exper.protein_label AS protein_label,
exper.primary_group_label AS primary_group_label,
exper.public AS public,
exper.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS exper
WHERE
`exper`.`public` = 1 UNION SELECT
exper.label AS label,
NULL AS id_tsa,
NULL AS kd_obs_tsa,
NULL AS cmpd_maxc_tsa,
exper.id AS id_itc,
1 / exper.kb_obs AS kd_obs_itc,
NULL AS id_sfa,
NULL AS ic50,
NULL AS cmpd_maxc_sfa,
NULL AS id_spr,
NULL AS k_ed_obs,
NULL AS cmpd_maxc_spr,
exper.temperature AS temperature,
exper.compound_id AS compound_id,
exper.protein_label AS protein_label,
exper.primary_group_label AS primary_group_label,
exper.public AS public,
exper.revision_id AS revision_id
FROM
`BVTS`.experiment_itc_protein_compound_view AS exper
WHERE
`exper`.`public` = 1 UNION SELECT
exper.label AS label,
NULL AS id_tsa,
NULL AS kd_obs_tsa,
NULL AS cmpd_maxc_tsa,
NULL AS id_itc,
NULL AS kd_obs_itc,
exper.id AS id_sfa,
exper.ic50 / 1000000000 AS ic50,
exper.compound_max_concentration AS cmpd_maxc_sfa,
NULL AS id_spr,
NULL AS k_ed_obs,
NULL AS cmpd_maxc_spr,
exper.temperature AS temperature,
exper.compound_id AS compound_id,
exper.protein_label AS protein_label,
exper.primary_group_label AS primary_group_label,
exper.public AS public,
exper.revision_id AS revision_id
FROM
`BVTS`.experiment_sfa_protein_compound_view AS exper
WHERE
`exper`.`public` = 1 UNION SELECT
exper.label AS label,
NULL AS id_tsa,
NULL AS kd_obs_tsa,
NULL AS cmpd_maxc_tsa,
NULL AS id_itc,
NULL AS kd_obs_itc,
NULL AS id_sfa,
NULL AS ic50,
NULL AS cmpd_maxc_sfa,
exper.id AS id_spr,
exper.k_ed_obs AS k_ed_obs,
exper.compound_max_concentration AS cmpd_maxc_spr,
exper.temperature AS temperature,
exper.compound_id AS compound_id,
exper.protein_label AS protein_label,
exper.primary_group_label AS primary_group_label,
exper.public AS public,
exper.revision_id AS revision_id
FROM
`BVTS`.experiment_spr_protein_compound_view AS exper
WHERE
`exper`.`public` = 1) AS experu
JOIN
`BVTS`.compound AS c ON (c.id = experu.compound_id)
LEFT JOIN
`BVTS`.protein_group AS pg ON (pg.label = experu.primary_group_label)
JOIN
`BVTS`.protein AS p ON (p.label = experu.protein_label)
GROUP BY compound_id , protein_group
ORDER BY compound_id , protein_group
concat_ws('-',c.id, experu.label)
c.id
c.name
c.formula_file
c.smiles
group_concat(DISTINCT p.label ORDER BY p.label SEPARATOR ' ')
group_concat(DISTINCT p.name ORDER BY p.name SEPARATOR ' ')
pg.name
GROUP_CONCAT(DISTINCT experu.temperature ORDER BY experu.temperature SEPARATOR ' ')
AVG(experu.temperature)
group_concat(DISTINCT experu.id_tsa ORDER BY experu.id_tsa SEPARATOR ' ')
AVG(LOG10(experu.kd_obs_tsa))
COUNT(DISTINCT experu.id_tsa)
MIN(LOG10(experu.kd_obs_tsa))
MAX(LOG10(experu.kd_obs_tsa))
STD(LOG10(experu.kd_obs_tsa))
MAX(experu.cmpd_maxc_tsa)
group_concat(DISTINCT experu.id_itc ORDER BY experu.id_itc SEPARATOR ' ')
AVG(LOG10(experu.kd_obs_itc))
COUNT(DISTINCT experu.id_itc)
MIN(LOG10(experu.kd_obs_itc))
MAX(LOG10(experu.kd_obs_itc))
STD(LOG10(experu.kd_obs_itc))
group_concat(DISTINCT experu.id_sfa ORDER BY experu.id_spr SEPARATOR ' ')
AVG(LOG10(experu.ic50))
COUNT(DISTINCT experu.id_sfa)
MIN(LOG10(experu.ic50))
MAX(LOG10(experu.ic50))
STD(LOG10(experu.ic50))
MAX(experu.cmpd_maxc_sfa)
group_concat(DISTINCT experu.id_spr ORDER BY experu.id_spr SEPARATOR ' ')
AVG(LOG10(experu.k_ed_obs))
COUNT(DISTINCT experu.id_spr)
MIN(LOG10(experu.k_ed_obs))
MAX(LOG10(experu.k_ed_obs))
STD(LOG10(experu.k_ed_obs))
MAX(experu.cmpd_maxc_spr)
MAX(experu.revision_id)
FROM `BVTS`.best_buffer_protonation AS bbp
JOIN `BVTS`.`buffer` AS b
ON bbp.buffer_id = b.id
bbp.id
bbp.notes
bbp.revision_id
FROM `BVTS`.`best_compound_protonation_pka` AS bcpp
JOIN `BVTS`.`compound` AS c ON c.id = bcpp.compound_id
WHERE bcpp.public = 1
GROUP BY bcpp.compound_id , temperature , reliability
bcpp.id
c.name
bcpp.author_ids
bcpp.compound_id
bcpp.notes
bcpp.revision_id
FROM `BVTS`.`best_compound_protonation_dah` AS bcpd
JOIN `BVTS`.`compound` AS c ON c.id = bcpd.compound_id
WHERE bcpd.public = 1
GROUP BY bcpd.compound_id , temperature , reliability
bcpd.id
c.name
bcpd.author_ids
bcpd.compound_id
bcpd.notes
bcpd.revision_id
FROM `BVTS`.`best_protein_protonation_pka` AS bppp
JOIN `BVTS`.`protein_batch_view` AS pbv ON pbv.protein_id = bppp.protein_id
WHERE bppp.public = 1
GROUP BY bppp.protein_id , temperature , reliability
bppp.id
bppp.protein_label
pbv.name
bppp.author_ids
bppp.protein_id
bppp.notes
bppp.revision_id
FROM `BVTS`.`best_protein_protonation_dah` AS bppd
JOIN `BVTS`.`protein_batch_view` AS pbv ON pbv.protein_id = bppd.protein_id
WHERE bppd.public = 1
GROUP BY bppd.protein_id , temperature , reliability
bppd.id
bppd.protein_label
pbv.name
bppd.author_ids
bppd.protein_id
bppd.notes
bppd.revision_id
FROM `BVTS`.`buffer`
revision_id
FROM `BVTS`.`compound_batch_view` WHERE public = 1
revision_id
FROM `BVTS`.`compound` AS c
WHERE c.public = 1
c.id
c.name
c.notes
c.revision_id
FROM `BVTS`.`crystal_structure`
WHERE public = 1
revision_id
FROM `BVTS`.`device` WHERE public = 1
revision_id
FROM `BVTS`.`experiment_tsa_protein_compound_view`
WHERE public = 1
revision_id
FROM `BVTS`.`file` WHERE public = 1
revision_id
FROM `BVTS`.`experiment_itc_protein_compound_view`
WHERE public = 1
revision_id
FROM
(SELECT
`eitc`.`label` AS `label`,
`etsa`.`id` AS `id_tsa`,
- 8.31446 * (`etsa`.`temperature` + 273.15)
* LN(`etsa`.`kb_obs` * (1 + POW(10, `protcpka`.`pka` - `etsa`.`ph`))
* (1 + POW(10, `etsa`.`ph` - `protppka`.`pka_zn`))) / 1000 AS `dbg_int_tsa`,
`eitc`.`id` AS `id_itc`,
- 8.31446 * (`eitc`.`temperature` + 273.15)
* LN(`eitc`.`kb_obs` * (1 + POW(10, `protcpka`.`pka` - `eitc`.`ph`))
* (1 + POW(10, `eitc`.`ph` - `protppka`.`pka_zn`))) / 1000 AS `dbg_int_itc`,
`eitc`.`dbh_obs` + (`protcdah`.`dah` - `protbuff`.`dah`)
/ (1 + POW(10, `eitc`.`ph` - `protcpka`.`pka`)) - (`protpdah`.`dah_zn` - `protbuff`.`dah`)
/ (1 + POW(10, `protppka`.`pka_zn` - `eitc`.`ph`)) AS `dbh_int_itc`,
`eitc`.`temperature` AS `temperature`,
`eitc`.`ph` AS `ph`,
`eitc`.`compound_id` AS `compound_id`,
`eitc`.`protein_label` AS `protein_label`,
`eitc`.`primary_group_label` AS `primary_group_label`,
(`eitc`.`public`) AND (`etsa`.`public`) AS `public`,
GREATEST(`eitc`.`revision_id`, `etsa`.`revision_id`,
`protcpka`.`revision_id`, `protppka`.`revision_id`,
`protcdah`.`revision_id`, `protpdah`.`revision_id`,
`protbuff`.`revision_id`) AS `revision_id`
FROM
`BVTS`.`experiment_itc_protein_compound_view` `eitc`
JOIN `BVTS`.`best_compound_protonation_pka` `protcpka`
ON (`eitc`.`compound_id` = `protcpka`.`compound_id`)
AND (ABS(`eitc`.`temperature` - `protcpka`.`temperature`) < 3)
JOIN `BVTS`.`best_protein_protonation_pka` `protppka`
ON (`eitc`.`protein_label` = `protppka`.`protein_label`)
AND (ABS(`eitc`.`temperature` - `protppka`.`temperature`) < 3)
JOIN `BVTS`.`best_compound_protonation_dah` `protcdah`
ON (`eitc`.`compound_id` = `protcdah`.`compound_id`)
AND (ABS(`eitc`.`temperature` - `protcdah`.`temperature`) < 3)
JOIN `BVTS`.`best_protein_protonation_dah` `protpdah`
ON (`eitc`.`protein_label` = `protpdah`.`protein_label`)
AND (ABS(`eitc`.`temperature` - `protpdah`.`temperature`) < 3)
JOIN `BVTS`.`best_buffer_protonation` `protbuff`
ON (`eitc`.`buffer_id` = `protbuff`.`buffer_id`)
AND (ABS(`eitc`.`temperature` - `protbuff`.`temperature`) < 3)
JOIN `BVTS`.`experiment_tsa_protein_compound_view` `etsa`
ON ((`eitc`.`compound_id` = `etsa`.`compound_id`)
AND (`eitc`.`primary_group_label` = `etsa`.`primary_group_label`)
AND (ABS(`eitc`.`temperature` - `etsa`.`temperature`) < 3))
) AS `experu`
JOIN `BVTS`.`compound` `c` ON (`c`.`id` = `experu`.`compound_id`)
LEFT JOIN `BVTS`.`protein_group` `pg` ON `pg`.`label` = `experu`.`primary_group_label`
JOIN `BVTS`.`protein` `p` ON `p`.`label` = `experu`.`protein_label`
WHERE `experu`.`public` = 1
GROUP BY `c`.`id` , `pg`.`name` , `experu`.`temperature`
ORDER BY `c`.`id` , `pg`.`name` , `experu`.`temperature`
concat_ws('-',c.id, experu.label)
c.id
c.name
c.formula_file
c.smiles
group_concat(DISTINCT p.label ORDER BY p.label SEPARATOR ' ')
group_concat(DISTINCT p.name ORDER BY p.name SEPARATOR ' ')
pg.name
GROUP_CONCAT(DISTINCT experu.temperature ORDER BY experu.temperature SEPARATOR ' ')
group_concat(DISTINCT experu.id_tsa ORDER BY experu.id_tsa SEPARATOR ' ')
COUNT(DISTINCT experu.id_tsa)
AVG(experu.dbg_int_tsa)
MIN(experu.dbg_int_tsa)
MAX(experu.dbg_int_tsa)
STD(experu.dbg_int_tsa)
group_concat(DISTINCT experu.id_itc ORDER BY experu.id_itc SEPARATOR ' ')
COUNT(DISTINCT experu.id_itc)
AVG(experu.dbg_int_itc)
MIN(experu.dbg_int_itc)
MAX(experu.dbg_int_itc)
STD(experu.dbg_int_itc)
AVG(experu.dbh_int_itc)
MIN(experu.dbh_int_itc)
MAX(experu.dbh_int_itc)
STD(experu.dbh_int_itc)
MAX(GREATEST(`experu`.`revision_id`, `c`.`revision_id`, `p`.`revision_id`, `pg`.`revision_id`))
FROM `BVTS`.`intrinsic_parameters` AS ip
JOIN `BVTS`.`compound` AS c ON c.id = ip.compound_id
JOIN `BVTS`.`protein_batch_view` AS pbv ON pbv.protein_id = ip.protein_id
WHERE ip.public = 1
GROUP BY protein_id, compound_id, temperature, best_intrinsic_tsa_ids, best_intrinsic_itc_ids, reliability
ip.id
c.formula_file
ip.protein_id
ip.revision_id
FROM
(
`BVTS`.compound AS c
JOIN(
SELECT
kb_int AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0001'
UNION
SELECT NULL AS
ca01,
kb_int AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0002'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
kb_int AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0003'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
kb_int AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0004'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
kb_int AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0005'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
kb_int AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0006'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
kb_int AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0007'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
kb_int AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0008'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
kb_int AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0009'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
kb_int AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0010'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
kb_int AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0011'
UNION
SELECT NULL AS
ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
kb_int AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p
ON
p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0012'
) AS iptu
ON
(c.id = iptu.compound_id)
)
GROUP BY
c.id,
iptu.temperature
ORDER BY
c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(ca01)
avg(ca02)
avg(ca03)
avg(ca04)
avg(ca05a)
avg(ca05b)
avg(ca06)
avg(ca07)
avg(ca09)
avg(ca12)
avg(ca13)
avg(ca14)
c.id
max(iptu.revision_id)
FROM
(`BVTS`.compound AS c
JOIN (SELECT
kb_int AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0001' UNION SELECT
NULL AS ca01,
kb_int AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0002' UNION SELECT
NULL AS ca01,
NULL AS ca02,
kb_int AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0003' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
kb_int AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0004' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
kb_int AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0005' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
kb_int AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0006' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
kb_int AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0007' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
kb_int AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0008' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
kb_int AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0009' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
kb_int AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0010' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
kb_int AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0011' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
kb_int AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0012')
AS iptu ON (c.id = iptu.compound_id))
GROUP BY c.id , iptu.temperature
ORDER BY c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(1/ca01)
avg(1/ca02)
avg(1/ca03)
avg(1/ca04)
avg(1/ca05a)
avg(1/ca05b)
avg(1/ca06)
avg(1/ca07)
avg(1/ca09)
avg(1/ca12)
avg(1/ca13)
avg(1/ca14)
c.id
max(iptu.revision_id)
FROM
(`BVTS`.compound AS c
JOIN (SELECT
etsa.kb_obs AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0001' UNION SELECT
NULL AS ca01,
etsa.kb_obs AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0002' UNION SELECT
NULL AS ca01,
NULL AS ca02,
etsa.kb_obs AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0003' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
etsa.kb_obs AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0004' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
etsa.kb_obs AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0005' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
etsa.kb_obs AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0006' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
etsa.kb_obs AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0007' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
etsa.kb_obs AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0008' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
etsa.kb_obs AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0009' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
etsa.kb_obs AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0010' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
etsa.kb_obs AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0011' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
etsa.kb_obs AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0012') ept ON (c.name = ept.compound_name))
GROUP BY c.id , ept.temperature
ORDER BY c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(1/ca01)
avg(1/ca02)
avg(1/ca03)
avg(1/ca04)
avg(1/ca05a)
avg(1/ca05b)
avg(1/ca06)
avg(1/ca07)
avg(1/ca09)
avg(1/ca12)
avg(1/ca13)
avg(1/ca14)
c.id
max(ept.revision_id)
FROM `BVTS`.`protein_batch_view` WHERE public = 1
revision_id
FROM `BVTS`.`protein` AS p
LEFT JOIN `BVTS`.`protein_batch_view` AS pbv ON p.id = pbv.protein_id
LEFT JOIN `BVTS`.`organism` AS o ON o.id = p.organism_id
WHERE `p`.`public` = 1
GROUP BY p.id
p.id
p.name
p.label
pbv.primary_group_label
concat(o.name, ' (', o.species, ')')
p.notes
p.revision_id
FROM `BVTS`.`publication` WHERE public = 1
revision_id
FROM `BVTS`.`compound_publication` AS cp
JOIN `BVTS`.`compound` AS c ON c.id = cp.compound_id
JOIN `BVTS`.`publication` AS p ON p.id = cp.publication_id
WHERE (p.public = 1) AND (c.public = 1)
cp.id
cp.notes
cp.revision_id
FROM `BVTS`.`itc_file` AS itcf
JOIN `BVTS`.`file` AS f ON f.id = itcf.file_id
JOIN `BVTS`.`experiment_itc_protein_compound_view` AS itc ON itc.id = itcf.experiment_itc_id
WHERE (f.public = 1) AND (itc.public = 1)
itcf.id
itcf.notes
itcf.revision_id
FROM `BVTS`.`sfa_file` AS sfaf
JOIN `BVTS`.`file` AS f ON f.id = sfaf.file_id
JOIN `BVTS`.`experiment_sfa_protein_compound_view` AS sfa ON sfa.id = sfaf.experiment_sfa_id
WHERE (f.public = 1) AND (sfa.public = 1)
sfaf.id
sfaf.notes
sfaf.revision_id
FROM `BVTS`.`spr_file` AS sprf
JOIN `BVTS`.`file` AS f ON f.id = sprf.file_id
JOIN `BVTS`.`experiment_spr_protein_compound_view` AS spr ON spr.id = sprf.experiment_spr_id
WHERE (f.public = 1) AND (spr.public = 1)
sprf.id
sprf.notes
sprf.revision_id
FROM `BVTS`.`tsa_file` AS tsaf
JOIN `BVTS`.`file` AS f ON f.id = tsaf.file_id
JOIN `BVTS`.`experiment_tsa_protein_compound_view` AS tsa ON tsa.id = tsaf.experiment_tsa_id
WHERE (f.public = 1) AND (tsa.public = 1)
tsaf.id
tsaf.notes
tsaf.revision_id
FROM `BVTS`.`tsa_publication` AS tsap
JOIN `BVTS`.`publication` AS p ON p.id = tsap.publication_id
JOIN `BVTS`.`experiment_tsa_protein_compound_view` AS tsa ON tsa.id = tsap.experiment_tsa_id
WHERE (p.public = 1) AND (tsa.public = 1)
tsap.id
tsap.notes
tsap.revision_id
FROM `BVTS`.`revision`
SUBSTRING_INDEX(remote_user, '.', 1)
FROM `BVTS`.`experiment_sfa_protein_compound_view`
WHERE public = 1
revision_id
FROM `BVTS`.`experiment_spr_protein_compound_view`
WHERE public = 1
revision_id
FROM
`BVTS`.`crystal_structure` `cs`
LEFT JOIN
`BVTS`.`compound_batch_view` `cbv` ON (`cs`.`compound_batch_id` = `cbv`.`id`)
LEFT JOIN
`BVTS`.`protein_batch_view` `pbv` ON (`cs`.`protein_batch_id` = `pbv`.`id`)
LEFT JOIN
`BVTS`.`protein_group` `pg` ON (`pbv`.`primary_group_label` = `pg`.`label`)
LEFT JOIN
(SELECT
best_int_tsa.id AS id,
best_int_tsa.compound_id AS compound_id,
POW(10, AVG(LOG10(best_int_tsa.kb_int))) AS kb_int,
AVG(best_int_tsa.dbg_int) AS dbg_int,
best_int_tsa.temperature AS temperature,
COUNT(best_int_tsa.id) AS count,
pgp.protein_group_id AS protein_group_id,
pg.label AS pg_label
FROM
`BVTS`.`best_intrinsic_tsa` AS best_int_tsa
LEFT JOIN `BVTS`.`protein_group_protein` AS pgp ON best_int_tsa.protein_id = pgp.protein_id
LEFT JOIN `BVTS`.`protein_group` AS pg ON pgp.protein_group_id = pg.id
WHERE best_int_tsa.public = 1
GROUP BY compound_id , protein_group_id , temperature) AS `bitsa` ON (`bitsa`.`pg_label` = `pbv`.primary_group_label
AND `bitsa`.`compound_id` = `cbv`.`compound_id`)
LEFT JOIN
(SELECT
best_int_itc.id AS id,
best_int_itc.compound_id AS compound_id,
AVG(best_int_itc.dbh_int) AS dbh_int,
best_int_itc.temperature AS temperature,
COUNT(best_int_itc.id) AS count,
pgp.protein_group_id AS protein_group_id,
pg.label AS pg_label
FROM
`BVTS`.`best_intrinsic_itc` AS best_int_itc
LEFT JOIN `BVTS`.`protein_group_protein` AS pgp ON best_int_itc.protein_id = pgp.protein_id
LEFT JOIN `BVTS`.`protein_group` AS pg ON pgp.protein_group_id = pg.id
WHERE best_int_itc.public = 1
GROUP BY compound_id , protein_group_id , temperature) AS `biitc` ON (`biitc`.`pg_label` = `pbv`.primary_group_label
AND `biitc`.`compound_id` = `cbv`.`compound_id`)
LEFT JOIN
(SELECT
exper_tsa.id AS id,
exper_tsa.compound_id AS compound_id,
POW(10, AVG(LOG10(exper_tsa.kb_obs))) AS kb_obs,
exper_tsa.temperature AS temperature,
COUNT(exper_tsa.id) AS count,
pgp.protein_group_id AS protein_group_id,
pg.label AS pg_label
FROM
`BVTS`.`experiment_tsa_protein_compound_view` AS exper_tsa
LEFT JOIN `BVTS`.`protein_group_protein` AS pgp ON exper_tsa.protein_id = pgp.protein_id
LEFT JOIN `BVTS`.`protein_group` AS pg ON pgp.protein_group_id = pg.id
WHERE exper_tsa.public = 1
GROUP BY compound_id , protein_group_id , temperature) `etsa` ON (`cbv`.`compound_id` = `etsa`.`compound_id`
AND `pbv`.`primary_group_label` = `etsa`.`pg_label`)
WHERE
`cs`.`public` = 1
GROUP BY `cs`.`id` , `etsa`.`temperature` , `biitc`.`temperature`
cs.id
cs.uuid
cs.name
cs.label
cs.compound_batch_id
cbv.compound_id
cbv.name
cs.protein_batch_id
pbv.protein_id
pbv.name
IFNULL(pg.name, 'NiG')
etsa.temperature
(1 / etsa.kb_obs)
(-8.31446 * (etsa.temperature + 273.15) * ln(etsa.kb_obs) / 1000)
1 / bitsa.kb_int
bitsa.dbg_int
biitc.temperature
biitc.dbh_int
cs.notes
GREATEST(cs.revision_id, IFNULL(cbv.revision_id, 0), IFNULL(pbv.revision_id, 0))
FROM
(SELECT
COUNT(label) AS cmpds,
NULL AS etsa,
NULL AS eitc,
NULL AS esfa,
NULL AS espr,
NULL AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`compound`
WHERE
`BVTS`.`compound`.`public` = 1
UNION SELECT
NULL AS cmpds,
COUNT(label) AS etsa,
NULL AS eitc,
NULL AS esfa,
NULL AS espr,
NULL AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`experiment_tsa_protein_compound_view`
WHERE
`BVTS`.`experiment_tsa_protein_compound_view`.`public` = 1
UNION SELECT
NULL AS cmpds,
NULL AS etsa,
COUNT(label) AS eitc,
NULL AS esfa,
NULL AS espr,
NULL AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`experiment_itc_protein_compound_view`
WHERE
`BVTS`.`experiment_itc_protein_compound_view`.`public` = 1
UNION SELECT
NULL AS cmpds,
NULL AS etsa,
NULL AS eitc,
COUNT(label) AS esfa,
NULL AS espr,
NULL AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`experiment_sfa_protein_compound_view`
WHERE
`BVTS`.`experiment_sfa_protein_compound_view`.`public` = 1
UNION SELECT
NULL AS cmpds,
NULL AS etsa,
NULL AS eitc,
NULL AS esfa,
COUNT(label) AS espr,
NULL AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`experiment_spr_protein_compound_view`
WHERE
`BVTS`.`experiment_spr_protein_compound_view`.`public` = 1
UNION SELECT
NULL AS cmpds,
NULL AS etsa,
NULL AS eitc,
NULL AS esfa,
NULL AS espr,
COUNT(label) AS xstr,
NULL as last_rev,
NULL as last_time
FROM
`BVTS`.`crystal_structure`
WHERE
`BVTS`.`crystal_structure`.`public` = 1
UNION SELECT
NULL AS cmpds,
NULL AS etsa,
NULL AS eitc,
NULL AS esfa,
NULL AS espr,
NULL AS xstr,
MAX(id) as last_rev,
MAX(`timestamp`) as last_time
FROM
`BVTS`.`revision`
) AS stats
GROUP BY id
0
MAX(stats.cmpds)
MAX(stats.etsa)
MAX(stats.eitc)
MAX(stats.esfa)
MAX(stats.espr)
MAX(stats.xstr)
MAX(stats.last_rev)
MAX(stats.last_time)
FROM `BVTS`.`version`
revision_id
FROM
(`BVTS`.compound AS c
JOIN (SELECT
dbg_int AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0001' UNION SELECT
NULL AS ca01,
dbg_int AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0002' UNION SELECT
NULL AS ca01,
NULL AS ca02,
dbg_int AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0003' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
dbg_int AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0004' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
dbg_int AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0005' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
dbg_int AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0006' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
dbg_int AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0007' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
dbg_int AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0008' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
dbg_int AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0009' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
dbg_int AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0010' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
dbg_int AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0011' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
dbg_int AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipt.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_tsa AS ipt
JOIN `BVTS`.protein AS p ON p.id = ipt.protein_id
WHERE
ipt.public = 1 AND p.primary_group_label = 'PG-0012')
AS iptu ON (c.id = iptu.compound_id))
GROUP BY c.id , iptu.temperature
ORDER BY c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(ca01)
avg(ca02)
avg(ca03)
avg(ca04)
avg(ca05a)
avg(ca05b)
avg(ca06)
avg(ca07)
avg(ca09)
avg(ca12)
avg(ca13)
avg(ca14)
c.id
max(iptu.revision_id)
FROM
(`BVTS`.compound AS c
JOIN (SELECT
etsa.kb_obs AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0001' UNION SELECT
NULL AS ca01,
etsa.kb_obs AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0002' UNION SELECT
NULL AS ca01,
NULL AS ca02,
etsa.kb_obs AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0003' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
etsa.kb_obs AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0004' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
etsa.kb_obs AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0005' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
etsa.kb_obs AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0006' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
etsa.kb_obs AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0007' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
etsa.kb_obs AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0008' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
etsa.kb_obs AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0009' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
etsa.kb_obs AS ca12,
NULL AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0010' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
etsa.kb_obs AS ca13,
NULL AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0011' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
etsa.kb_obs AS ca14,
etsa.temperature AS temperature,
etsa.compound_name AS compound_name,
etsa.revision_id AS revision_id
FROM
`BVTS`.experiment_tsa_protein_compound_view AS etsa
WHERE
etsa.public = 1
AND etsa.primary_group_label = 'PG-0012') ept ON (c.name = ept.compound_name))
GROUP BY c.id , ept.temperature
ORDER BY c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca01) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca02) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca03) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca04) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca05a) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca05b) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca06) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca07) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca09) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca12) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca13) / 1000) -- in kJ/(mol*K)
avg(-8.31446 * (ept.temperature + 273.15) * ln(ept.ca14) / 1000) -- in kJ/(mol*K)
c.id
max(ept.revision_id)
FROM
(`BVTS`.compound AS c
JOIN (SELECT
dbh_int AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0001' UNION SELECT
NULL AS ca01,
dbh_int AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0002' UNION SELECT
NULL AS ca01,
NULL AS ca02,
dbh_int AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0003' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
dbh_int AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0004' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
dbh_int AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0005' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
dbh_int AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0006' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
dbh_int AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0007' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
dbh_int AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0008' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
dbh_int AS ca09,
NULL AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0009' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
dbh_int AS ca12,
NULL AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0010' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
dbh_int AS ca13,
NULL AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0011' UNION SELECT
NULL AS ca01,
NULL AS ca02,
NULL AS ca03,
NULL AS ca04,
NULL AS ca05a,
NULL AS ca05b,
NULL AS ca06,
NULL AS ca07,
NULL AS ca09,
NULL AS ca12,
NULL AS ca13,
dbh_int AS ca14,
temperature AS temperature,
compound_id AS compound_id,
ipi.revision_id AS revision_id
FROM
`BVTS`.intrinsic_parameters_itc AS ipi
JOIN `BVTS`.protein AS p ON p.id = ipi.protein_id
WHERE
ipi.public = 1 AND p.primary_group_label = 'PG-0012')
AS ipiu ON (c.id = ipiu.compound_id))
GROUP BY c.id , ipiu.temperature
ORDER BY c.id
concat_ws('-', c.id, temperature)
c.name
c.formula_file
temperature
avg(ca01)
avg(ca02)
avg(ca03)
avg(ca04)
avg(ca05a)
avg(ca05b)
avg(ca06)
avg(ca07)
avg(ca09)
avg(ca12)
avg(ca13)
avg(ca14)
c.id
max(ipiu.revision_id)

