Opasnet Base UI

From Testiwiki
Revision as of 07:16, 7 March 2011 by Ehac (talk | contribs) (→‎Object cell data)
Jump to: navigation, search

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

  • 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

Run identifier aka series_id in the actobj table. This parameter can be used for specifying the version of the data fetched for the result. Every replacing upload creates a new series_id and this parameter tells which one to use. By default the latest series_id will be used.

sr

Boolean variable which determines if the results should be downloaded from the database and shown in the browser window. Value '1' loads and shows the whole result (excluding filtered rows), '0' shows only the results row count.

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.

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=529 ORDER BY act_id DESC LIMIT 0,1

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>;

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>)

Retrieved from "http://www.opasnet.org/testiwiki/index.php?title=Opasnet_Base_UI&oldid=18833"