Difference between revisions of "Opasnet Base UI"

From Testiwiki
Jump to: navigation, search
m (Basic object data)
(technical edits)
 
(13 intermediate revisions by one other user not shown)
Line 1: Line 1:
== Overview ==
+
[[category:extension]]
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.
+
[[Category:Opasnet Base]]
 +
[[Category:Opasnet]]
 +
{{method|moderator=Jouni}}
 +
'''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 it ==
+
How to use Opasnet Base UI?
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.
+
 
 +
== 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 ===
  
== Features ==
 
 
* Access all public variable/study data found in Opasnet Base
 
* Access all public variable/study data found in Opasnet Base
 
* Access to all versions of the data (upload history)
 
* Access to all versions of the data (upload history)
Line 14: Line 29:
 
* Results output to screen as html or as csv-file
 
* Results output to screen as html or as csv-file
  
== Parameters ==
+
=== 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.
 
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 ===
+
==== 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.
 
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 ====
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.
+
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 ===
+
sr = '0', sa = '0', No results will be loaded (just the row count)<br/>
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.
+
sr = '0', sa = '1', No results will be loaded (just the row count)<br/>
 +
sr = '1', sa = '0', 100 first rows of the result will be loaded<br/>
 +
sr = '1', sa = '1', The whole result will be loaded (can be huge!)
  
=== op ===
+
==== 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.
 
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 ===
+
==== 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').
 
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 ===
+
==== 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'.
 
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'.
  
Line 39: Line 59:
 
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.
 
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 ===
+
==== 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.
 
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 ==
+
== Rationale ==
 +
 
 +
=== Queries ===
 
This chapter describes the queries that Base UI executes.
 
This chapter describes the queries that Base UI executes.
  
=== Basic object data ===
+
==== Basic object data ====
 
<code>
 
<code>
 
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>"
 
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>"
 
</code>
 
</code>
  
=== Latest series id (for latest upload of data) ===
+
==== Latest act id (for latest upload of data) ====
 +
<code>
 +
SELECT act_id FROM actobj WHERE obj_id=<object id> ORDER BY act_id DESC LIMIT 0,1
 +
</code>
 +
 
 +
==== Series id ====
 
<code>
 
<code>
SELECT series_id FROM actobj WHERE obj_id = <object id> ORDER BY series_id DESC LIMIT 0,1;
+
SELECT series_id FROM actobj WHERE obj_id=<object id> AND act_id=<act id>
 
</code>
 
</code>
  
=== Object cell data ===
+
==== Series sample count ====
 
<code>
 
<code>
SELECT n, mean FROM cell LEFT JOIN actobj ON cell.actobj_id = actobj.id WHERE actobj.series_id = <act series id>;
+
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>
 
</code>
 
</code>
  
=== Wiki data ===
+
==== Wiki data ====
 
<code>
 
<code>
SELECT * FROM wiki WHERE id = <wiki id>;
+
SELECT * FROM wiki WHERE id = <wiki_id>;
 
</code>
 
</code>
  
=== Upload history ===
+
==== Act unit ====
 
<code>
 
<code>
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;
+
SELECT unit FROM actobj WHERE actobj.obj_id = <object id> AND actobj.act_id = <act id>;
 
</code>
 
</code>
  
=== Specific upload info ===
+
==== Act info ====
 
<code>
 
<code>
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;
+
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
 
</code>
 
</code>
  
=== Specific upload dimension count ===
+
==== Act indices ====
 
<code>
 
<code>
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>
+
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
 
</code>
 
</code>
  
=== Dimensions and locations ===
+
==== Act sample count ====
 
<code>
 
<code>
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
+
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>
 
</code>
 
</code>
  
=== Whole result row count ===
+
==== Act cell count ====
 
<code>
 
<code>
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>
+
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>
 
</code>
 
</code>
  
=== Excluded results row count ===
+
==== Upload history ====
 
<code>
 
<code>
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>)
+
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
 
</code>
 
</code>
  
=== Result ids that contain excluded location (for results filtering) ===
+
==== Locations of an index in act ====
 
<code>
 
<code>
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
+
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
 
</code>
 
</code>
  
=== Results view ===
+
==== Results view for an act ====
 
<code>
 
<code>
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
+
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>
 
</code>
 
</code>
  
=== Final results from results view ===
+
==== Final results from results view ====
 
<code>
 
<code>
SELECT * FROM results WHERE result_id NOT IN(<list of result ids to be excluded, separated by comma>) AND obs <= <sample count>
+
SELECT * FROM results WHERE obs <= <samples>
 
</code>
 
</code>
  
=== Whole Mean and SD row count ===
+
==== Whole Mean and SD row count ====
 
<code>
 
<code>
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>
+
TODO
 
</code>
 
</code>
  
=== Excluded Mean and SD count ===
+
==== Excluded Mean and SD count ====
 
<code>
 
<code>
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>)
+
TODO
 
</code>
 
</code>
  
=== Cell ids that contain excluded location (for Mean and SD filtering) ===
+
==== Cell ids that contain excluded location (for Mean and SD filtering) ====
 
<code>
 
<code>
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
+
TODO
 
</code>
 
</code>
  
=== Results view for Mean and SD ===
+
==== Results view for Mean and SD ====
 
<code>
 
<code>
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>
+
TODO
 
</code>
 
</code>
  
=== Final results for Mean and SD ===
+
==== Final results for Mean and SD ====
 
<code>
 
<code>
SELECT * FROM results WHERE cell_id NOT IN(<list of cell ids to be excluded, separated by comma>)
+
TODO
 
</code>
 
</code>
  
[[category:extension]]
+
== See also ==
[[Category:Opasnet Base]]
+
 
[[Category:Opasnet]]
+
{{Opasnet Base}}
 +
 
 +
== References ==
 +
 
 +
<references/>
 +
 
 +
== Related files ==

Latest revision as of 18:16, 10 April 2015


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

See also

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

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

References


Related files