Opasnet Base UI

Overview

Opasnet Base UI is a media wiki special page that implements user interface to fetch data from the Opasnet Base -database. This page describes the usage and the queries it executes.

How to use it

Every variable or study -page in Opasnet has a link to Base UI -special page if there is data for that specific object in the database. Just follow the link located in the box at the top right corner of the page and explore the results.

Features

1. Access all public variable/study data found in Opasnet Base
2. Access to all versions of the data (upload history)
3. Filtering of the result
4. Multi-sorting of the result
5. Selectable variable sample size for the result
6. Optionally view Mean & SD values of the data cells instead of absolute results

Queries

This chapter describes the queries that Base UI executes.

Basic object data

SELECT * FROM obj WHERE ident = "<object identifier>";

Latest series id (for latest upload of data)

SELECT series_id FROM actobj WHERE obj_id = <object id> ORDER BY series_id DESC LIMIT 0,1;

Object cell data

SELECT n, mean FROM cell LEFT JOIN actobj ON cell.actobj_id = actobj.id WHERE actobj.series_id = <act series id>;

Wiki data

SELECT * FROM wiki WHERE id = <wiki id>;

Upload history

SELECT act.id AS id, who, time, comments, n, acttype FROM act LEFT JOIN actobj ON act.id = actobj.act_id LEFT JOIN acttype ON acttype.id = act.acttype_id LEFT JOIN cell ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND (acttype.id = 4 OR acttype.id = 5) ORDER BY time DESC;

Specific upload info

SELECT act.id AS id, who, time, comments, n, acttype FROM act LEFT JOIN actobj ON act.id = actobj.act_id LEFT JOIN acttype ON acttype.id = act.acttype_id LEFT JOIN cell ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND (acttype.id = 4 OR acttype.id = 5) AND actobj.series_id = <series id> ORDER BY time DESC;

Specific upload dimension count

SELECT COUNT(DISTINCT ind.id) AS cnt FROM obj LEFT JOIN actobj ON obj.id = actobj.obj_id LEFT JOIN cell ON actobj.id = cell.actobj_id LEFT JOIN loccell ON cell.id = loccell.cell_id LEFT JOIN loc ON loccell.loc_id = loc.id RIGHT JOIN obj AS ind ON loc.obj_id_i=ind.id WHERE actobj.series_id = <series id> AND obj.id = <object id>

Dimensions and locations

SELECT DISTINCT ind.wiki_id AS wiki_id, obj.page AS wiki_page, loc.id AS location_id, location, ind.id AS index_id, ind.name AS index_name FROM obj LEFT JOIN actobj ON obj.id = actobj.obj_id LEFT JOIN cell ON actobj.id = cell.actobj_id LEFT JOIN loccell ON cell.id = loccell.cell_id LEFT JOIN loc ON loccell.loc_id = loc.id RIGHT JOIN obj AS ind ON loc.obj_id_i=ind.id WHERE actobj.series_id = <series id> AND obj.id = <object id> ORDER BY index_name, location

Whole result row count

SELECT COUNT(DISTINCT res.id) AS cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id>

Excluded results row count

SELECT COUNT(DISTINCT res.id) as cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<location ids, separated by comma>)

Result ids that contain excluded location (for results filtering)

SELECT DISTINCT res.id as result_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids separated by comma>) ORDER BY res.id

Results view

results AS SELECT res.id AS result_id, location, ind.id as index_id, obs, result FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> ORDER BY res.id, ind.name

Final results from results view

SELECT * FROM results WHERE result_id NOT IN(<list of result ids to be excluded, separated by comma>) AND obs <= <sample count>

Whole Mean and SD row count

SELECT COUNT(DISTINCT cell_id) AS cnt, ind.id as index_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id>

Excluded Mean and SD count

SELECT COUNT(DISTINCT cell.id) AS cnt FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>)

Cell ids that contain excluded location (for Mean and SD filtering)

SELECT DISTINCT cell.id AS cell_id FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN act ON actobj.act_id = act.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND loc.id IN(<list of location ids, separated by comma>) ORDER BY cell.id

Results view for Mean and SD

results AS SELECT ind.id AS index_id, actobj.obj_id as obj_id, cell_id, actobj.series_id AS upload, loc.location AS location, obj.unit, mean, sd FROM obj LEFT JOIN actobj ON actobj.obj_id = obj.id LEFT JOIN cell ON cell.actobj_id = actobj.id LEFT JOIN loccell ON loccell.cell_id = cell.id LEFT JOIN loc on loccell.loc_id = loc.id LEFT JOIN obj AS ind ON loc.obj_id_i = ind.id WHERE obj.id = <object id> AND actobj.series_id = <series id>

Final results for Mean and SD

SELECT * FROM results WHERE cell_id NOT IN(<list of cell ids to be excluded, separated by comma>)