Opasnet Base UI

This page is a knowledge crystal of subtype method. The page identifier is Op_en4045
Moderator:Jouni (see all)
Upload data {{#opasnet_base_link:Op_en4045}}

Opasnet Base user interface (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.

Question

How to use Opasnet Base UI?

Answer

The Opasnet Base UI (special page) can be found in the following address:

http://en.opasnet.org/w/Special:Opasnet_Base

The initial page shows all data objects currently in the Opasnet Base -database. Click any object's name and start exploring it with the provided tools.

Also every object (variable, study...) -page in Opasnet has a direct link to the Opasnet 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

• Access all public variable/study data found in Opasnet Base
• Access to all versions of the data (upload history)
• Filtering of the result
• Multi-sorting of the result
• Selectable variable sample size for the result
• Optionally view Mean & SD values of the data cells instead of absolute results
• Results output to screen as html or as csv-file

Parameters

This chapter describes the URL-parameters being used. Normally these are controlled by the user interface itself, but manual control is also completely possible if needed.

id

Object identifier string which equals to value of the ident field in database obj table. This is the only mandatory parameter which must be given.

run

Act identifier aka act.id. This parameter can be used for specifying the version of the data fetched for the result. By default the latest act_id will be used.

sr, sa

Boolean variables which determine the result output in the web browser. Usage described below.

sr = '0', sa = '0', No results will be loaded (just the row count)
sr = '0', sa = '1', No results will be loaded (just the row count)
sr = '1', sa = '0', 100 first rows of the result will be loaded
sr = '1', sa = '1', The whole result will be loaded (can be huge!)

op

String value that determines the results output type. If this variable is set to 'smp' then the user interface works on absolute result values. Other possibility is to use 'mean' which tells the system to deal with Mean & SD values instead.

smp

Integer value to determine the number of samples to include in the result. For variable type object this can be anything from one to maximum number of samples available. For study type object this must always be zero as study doesn't have any samples. It's also important to remember that this variable makes any difference only if output is set for absolute values (op variable is set to 'smp').

el

List of locations to be excluded from the result (any result row containing any of these locations won't be shown). The list is encoded by first creating a binary string having one bit for each location (order comes from database query of object's indices and locations ). The bit corresponding to a location needs to be 1 to exclude the location from the result, whereas included locations have bits with value 0. After this the binary string must be encoded to string of hexadecimal numbers by first dividing binary into sequences of 4 bits and then converting those sequences to hexadecimal digits (one digit for each sequence). For this purpose the binary string must have length that can be divided by four and for this trailing zeros can be added. E.g. if there is total of 6 locations and the 3rd one is to be excluded then the binary string would be '00100000' and finally when converted to hexadecimal representation it becomes '20'.

Each index (its hexadecimal string) must be separated with 'x'-character. The order for the indices comes from the indices query.

This kind of encoding was chosen to compress the total length of the URL. With this approach the exclusion of four locations can be represented by one character in URL parameter string. In other words, if variable/study has total of 1000 locations it takes only 250 characters to deliver the exclusion information as URL parameter.

srt

List of sorting criteria which consists of sort field identifier and sort direction flag pairs separated by 'x' character. Sort field identifier can be a decimal number (the dimension row id in database) for a dimension or string (result|mean|sd) for result, mean or SD as a sorting criteria. The sort direction flag can be either character 'a' or 'd' (ascending or descending). Field identifier and flag character are also separated by 'x' character.

Rationale

Queries

This chapter describes the queries that Base UI executes.

Basic object data

SELECT obj.id,ident,name,objtype_id,page,wiki_id,objtype FROM obj LEFT JOIN objtype ON obj.objtype_id = objtype.id WHERE ident="<object identifier>"

Latest act id (for latest upload of data)

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

Series id

SELECT series_id FROM actobj WHERE obj_id=<object id> AND act_id=<act id>

Series sample count

SELECT actobj.id, (SELECT n FROM cell WHERE cell.actobj_id = actobj.id limit 1) as samples FROM actobj WHERE actobj.obj_id = <object id> AND series_id = <series id>

Wiki data

SELECT * FROM wiki WHERE id = <wiki_id>;

Act unit

SELECT unit FROM actobj WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id>;

Act info

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

Act indices

SELECT page, ind.wiki_id AS wiki_id, ind.id AS index_id, ind.name AS index_name FROM cell 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 cell.id = (SELECT cell.id FROM cell LEFT JOIN actobj ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id> LIMIT 0,1) ORDER BY index_name

Act sample count

SELECT actobj.id, (SELECT n FROM cell WHERE cell.actobj_id = actobj.id limit 1) as samples FROM actobj WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id>

Act cell count

SELECT count(*) AS cnt FROM actobj LEFT JOIN cell ON cell.actobj_id = actobj.id WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id>

Upload history

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

Locations of an index in act

SELECT DISTINCT loc.id AS location_id, location FROM obj LEFT JOIN actobj ON obj.id = actobj.obj_id LEFT JOIN actloc ON actobj_id = actobj.id LEFT JOIN loc ON loc.id = actloc.loc_id WHERE loc.obj_id_i = <index id> AND actobj.series_id = <series id> AND obj.id = <object id> AND actobj.act_id <= <act id> ORDER BY location

Results view for an act

CREATE OR REPLACE VIEW results AS SELECT res.id AS result_id, location, ind.id as index_id, obs, result, restext, cell.id as cell_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 LEFT JOIN res ON res.cell_id = cell.id WHERE obj.id = <object id> AND actobj.series_id = <series id> AND actobj.act_id <= <act id>

Final results from results view

SELECT * FROM results WHERE obs <= <samples>

Whole Mean and SD row count

TODO

Excluded Mean and SD count

TODO

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

TODO

Results view for Mean and SD

TODO

Final results for Mean and SD

TODO