Substance properties

Here is how we compute substance properties:

Purchasability

substance_best_purchasability -- Highest purchasability level for sub_id
CREATE OR REPLACE FUNCTION substance_best_purchasability (sid INTEGER) 
RETURNS INTEGER 
IMMUTABLE 
AS $$
SELECT CASE WHEN 
(MAX(purchasability) IS NULL) THEN 0 
ELSE MAX(purchasability) END 
AS purchasability 
FROM (SELECT (CASE 
WHEN (ci.sub_id_fk IS NULL) THEN 0 ELSE MAX(c.purchasable) END) AS purchasability FROM catalog AS c 
LEFT JOIN catalog_item AS ci ON ci.cat_id_fk=c.cat_id 
WHERE sub_id_fk=sid AND NOT ci.depleted 
GROUP BY ci.sub_id_fk
) AS _t
$$ LANGUAGE SQL;

free

substance_is_free -- Is in any free catalog
CREATE OR REPLACE FUNCTION substance_is_free (sid INTEGER) 
RETURNS INTEGER 
IMMUTABLE 
AS $$
SELECT MAX(free) AS free FROM (SELECT 
(CASE WHEN ci.sub_id_fk IS NULL THEN 0 ELSE max(cast(c.free as integer)) END) AS free FROM catalog AS c
LEFT JOIN catalog_item AS ci ON ci.cat_id_fk=c.cat_id 
WHERE sub_id_fk=sid 
GROUP BY ci.sub_id_fk
) AS _t
$$ 
LANGUAGE sql;

reactivity

substance_worst_reactivity -- Worst reactivity score for this substance
CREATE OR REPLACE FUNCTION substance_worst_reactivity (sid INTEGER) 
RETURNS INTEGER 
IMMUTABLE 
AS $$
SELECT MAX(reactivity) AS reactivity 
FROM (SELECT (CASE 
WHEN sp.sub_id_fk IS NULL THEN 0 
ELSE max(CASE WHEN p.pat_type_fk = 14 THEN 0 ELSE p.reactive END) 
END) 
AS reactivity
FROM pattern AS p 
LEFT JOIN subpat AS sp ON sp.pattern_fk=p.pattern_id 
WHERE sub_id_fk=sid
GROUP BY sp.sub_id_fk
) AS _t
$$ 
LANGUAGE sql;

building blocks

work in progress

tranche 2-tuple

blah blah