Opasnet Base UI

From Testiwiki
Revision as of 18:43, 11 July 2010 by Jouni (talk | contribs) (Added category: 'Opasnet')
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 (every result including these locations will be removed from the output). List consists of hexadecimal location indexes separated by 'x' character. The location index here is the order number in the result given by database query of object's dimensions and locations (see Queries chapter below). This kind of approach was chosen to compress the length of the url, which can grow long if several locations are being filtered. Location's id number can be a large integer compared to order number which always starts counting from one. Hexadecimal encoding, compared to decimal system, saves characters needed to express the location indices. The 'x' character as a list separator in url is only a one character where e.g. url-encoded comma would take three characters (%2C).

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

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