Opasnet Base UI
|Moderator:Jouni (see all)|
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.
- 1 Question
- 2 Answer
- 3 Rationale
- 3.1 Queries
- 3.1.1 Basic object data
- 3.1.2 Latest act id (for latest upload of data)
- 3.1.3 Series id
- 3.1.4 Series sample count
- 3.1.5 Wiki data
- 3.1.6 Act unit
- 3.1.7 Act info
- 3.1.8 Act indices
- 3.1.9 Act sample count
- 3.1.10 Act cell count
- 3.1.11 Upload history
- 3.1.12 Locations of an index in act
- 3.1.13 Results view for an act
- 3.1.14 Final results from results view
- 3.1.15 Whole Mean and SD row count
- 3.1.16 Excluded Mean and SD count
- 3.1.17 Cell ids that contain excluded location (for Mean and SD filtering)
- 3.1.18 Results view for Mean and SD
- 3.1.19 Final results for Mean and SD
- 3.1 Queries
- 4 See also
- 5 References
- 6 Related files
How to use Opasnet Base UI?
The Opasnet Base UI (special page) can be found in the following address:
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.
- 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
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.
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.
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.
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!)
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.
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').
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.
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.
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
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>
SELECT * FROM wiki WHERE id = <wiki_id>;
SELECT unit FROM actobj WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id>;
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
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>
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
Excluded Mean and SD count
Cell ids that contain excluded location (for Mean and SD filtering)
Results view for Mean and SD
Final results for Mean and SD
|Pages related to Opasnet Base||
Opasnet Base · Uploading to Opasnet Base · Data structures in Opasnet · Opasnet Base UI · Modelling in Opasnet · Special:Opasnet Base Import · Opasnet Base Connection for R (needs updating) · Converting KOPRA data into Opasnet Base · Poll · Working with sensitive data · Saved R objects
|Pages related to the 2008-2011 version of Opasnet Base||
Opasnet base connection for Analytica · Opasnet base structure · Related Analytica file (old version File:Transferring to result database.ANA) · Analytica Web Player · Removed pages and other links · Standard run · OpasnetBaseUtils