Opasnet base structure: Difference between revisions

From Opasnet
Jump to navigation Jump to search
(better positioning for tables)
(marked outdated)
 
(94 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{tool}}
[[Category:SQL tool]]
'''Result database''' is a storage and retrieval system for variable results. It is basically an SQL database with the following functionalities:
[[Category:Open assessment]]
# Storage of results of variables with uncertainties when necessary, and as multidimensional arrays when necessary.{{disclink|Should all variables go to result distribution database?}}
[[Category:Tool]]
# Automatic retrieval of results when called from the collaborative workspace or other platforms.
{{variable|moderator = Jouni
# Description and handling of the dimensions that the variables may take.
| reference = {{publication
# Storage and retrieval system for items that are needed to calculate the results of variables.
| authors        = Juha Villman, Einari Happonen, Jouni T. Tuomisto
# A platform for performing computer runs to update the results of variables.
| page          = Opasnet Base structure
# Follow-up of the linkages between variables, the data about a particular variable, and the computing formula of the variable, in respect to their impact on the variable result.
| explanation    =
# Follow-up of the age and validity of the content based on the previous point.
| publishingyear = 2010
# A platform for planning computer runs based on the update need, CPU demand, and CPU availability.
| urn            =
| elsewhere      =
}}
}}


==Functionalities of the result database==
:''This page is about the '''old structure of Opasnet Base''' used in ca. 2008-2011. For a description about the current database, see [[Opasnet base 2]].


===Storage and retrieval of results of variables===
==Question==


The most important functionality is to store and retrieve the results of variables. Because variables may take very different forms (from a single value such as natural constant to an uncertain spatio-temporal concentration field over the whole Europe), the database must be very flexible. The basic solution is described in the [[variable]] page, and it is only briefly summarised here. The result is described as
[[image:Opasnet Base structure.png|thumb|400px|Structure and connections (lines) of the tables (boxes) in the [[Opasnet Base]]. All table identifiers are called id (so they can be called by like obj.id). When obj.id is referred to in another table such as actobj, it is called actobj.obj_id. The latter end of a one-to-many relationship is marked with a ring. Important substantive fields are listed inside table boxes.]]


  P(R|x<sub>1</sub>,x<sub>2</sub>,...)
'''Opasnet Base''' is a storage and retrieval system for [[result]]s of [[variable]] and [[data]] from [[study|studies]]. What is the structure of [[Opasnet Base]] such that it enables the following functionalities?
# Storage of results of variables with uncertainties when necessary, and as multidimensional arrays when necessary.{{reslink|Should all variables go to result distribution database?}}
# Automatic retrieval of results when called from [[Opasnet wiki]] or other platforms or modelling systems.
# Description and handling of the [[index|indices]]s that a [[variable]] may take.
# It is possible to protect some results and data from reading by unauthorised persons.
# If is possible to build user interfaces for easily entering observations into the Base.


where P(R) is the probability distribution of the result and x<sub>1</sub> and x<sub>2</sub> are defining locations where a particular P(R) applies. A ''dimension'' means a property along which there are multiple locations and the result of the variable may have different values when the location changes. In this case, x<sub>1</sub> and x<sub>2</sub> are dimensions, and particular values of x<sub>1</sub> and x<sub>2</sub> are locations. A variable can have zero, one, or more dimensions. Even if a dimension is continuous, it is usually operationalised in practice as a list of discrete locations. Such a list is called an ''index'', and each location is called a ''row'' of the index. In the general information structure of the new risk assessment method, dimensions are [[Help:Class|Classes]] with a special purpose. An index can be thought of as a variable that inherits its plausible range from a dimension (class).
==Answer==


Uncertainty about the true value of the variable is operationalised as a random sample from the probability distribution, in such a way that the samples are located along an index ''Sample'', which is a list of integers 1,2,3...n, where n=number of samples.
Opasnet base is a [[:en:MySQL|MySQL]] database located at http://base.opasnet.org.


The dimensions of a variable are determined by the parent variables (by inheritance) and the formula used to calculate the result. Thus, there is not a place where the dimensions of a particular variable are explicitly asked for. In addition, the indices (as operationalisations of dimensions) are NOT properties of variables but of risk assessments. This may sound unintuitive, but the reasoning is that indices are just practical ways to handle dimensions, and these practical needs may change from one assessment to another.
===Data structure===


The tables '''Variable''' and '''Result''' contain the result data. In addition, '''Location, Dimension, Index''', and '''Rows''' contain data about the dimensions and indices used. These tables together offer the functionalities of data storage and retrieval, and handling of multidimensionality and uncertainty.
:''Main article: '''[[Data structures in Opasnet]]'''''


===Calculation of the updated results===
All data should be convertible into the following format:


The result of a variable can be calculated when four things are known:
{| {{prettytable}}
# The list of of upstream variables (parents) (Definition/causality attribute),
! colspan="3"| || colspan="3"  style="background-color: #FFD8F0;"|Observation
# The results of the parent variables,
|-----
# The data used to derive the result (Definition/data attribute), and
!  style="background-color: #CCDFC8;"|Year ||  style="background-color: #CCDFC8;"|Sex ||  style="background-color: #CCDFC8;"|Age ||  style="background-color: #DFB8D0;"|Height ||  style="background-color: #DFB8D0;"|Weight ||  style="background-color: #DFB8D0;"|Description
# The formula used to calculate the result based on the items above (Definition/formula attribute).
|-----
The three sub-attributes of the Definition are represented by three tables in the result database: '''Causality''', '''Formula''', and '''Data'''. In addition, the results of the parents can be obtained from the Result table. The [[Help:Variable transfer protocol|variable transfer protocol]] is used to extract these data from the result database, send them to an external software such as R to calculate the result, and store the calculated result into the Result table of the database. The technical solutions to do this in practice have to be developed.
|  style="background-color: #ECFFE8;"|2009 ||  style="background-color: #ECFFE8;"|Male ||  style="background-color: #ECFFE8;"|20 ||  style="background-color: #ECDFE8;"|178 ||  style="background-color: #ECDFE8;"|70 ||  style="background-color: #ECDFE8;"|An optional column for descriptive text about each row.
|-----
|  style="background-color: #ECFFE8;"|2009 ||  style="background-color: #ECFFE8;"|Male ||  style="background-color: #ECFFE8;"|30 ||  style="background-color: #ECDFE8;"|174 ||  style="background-color: #ECDFE8;"|79 ||  style="background-color: #ECDFE8;"|
|-----
|  style="background-color: #ECFFE8;"|2010 ||  style="background-color: #ECFFE8;"|Male ||  style="background-color: #ECFFE8;"|25 ||  style="background-color: #ECDFE8;"|183 ||  style="background-color: #ECDFE8;"|84 ||  style="background-color: #ECDFE8;"|
|-----
|  style="background-color: #ECFFE8;"|2010 ||  style="background-color: #ECFFE8;"|Female ||  style="background-color: #ECFFE8;"|22 ||  style="background-color: #ECDFE8;"|168 ||  style="background-color: #ECDFE8;"|65 ||  style="background-color: #ECDFE8;"|
|}


When a variable result is calculated, the computing software must know, which indices must be used with which variables. This can be automatically resolved using the following reasoning algorithm.
where
* Make a list of all unfinished risk assessments.
* Make a list of all indices in these risk assessments.
* Compile all indices of a particular dimension into one large "super-index" with all the locations.
* Use these "super-indices" in the calculations.
* Apply a particular "super-index" for a particular variable, if that variable has the dimension in question.
A wild use of occasional indices is discouraged, because they cause heavy computing needs with little benefit. Therefore, there should be a "standard risk assessment" that is constantly kept unfinished. It would then contain recommended indices for all major dimensions. This way, at least the standard indices are always used in computations, and the need for users to develop their own indices is smaller.
 
When the new results are stored in the database, the old results of the variables are deleted. The different versions of the variable results are NOT permanently stored anywhere. However, when a risk assessment report is created using the [[Help:Reporting tool|reporting tool]], the result distributions used for that report are stored, together with the definitions and other data about all variables. Thus, a full copy of everything that relates to a particular assessment can be downloaded and stored outside the result database.
 
===Follow-up of validity===
 
The result of a variable is valid since its update until something that affects its content (i.e., the four things listed above) changes. Therefore, there must be a system that follows what things these are for a particular variable, and whether they have changed since the last calculation of the variable result. When the data in Causality, Formula, and Data tables is combined with the data of the dates when the parent variables were run, it can be automatically concluded whether the variable is valid or not. If the variable is older than its determinants, there is a need to recalculate the result. This cannot be done fully automatically, because some variables are probably being actively edited, and this would create a constant need to update everything downstream. In addition, some complex variables may take even weeks to compute.
 
Therefore, there should be a planning system for result updates. This can easily be done by adding tables '''Run''' and '''Run_list''' to the database. These tables contain information about the runs that have been performed or are being planned to be performed. The user can add variables to and delete them from the lists of planned runs. The needs for updating can be combined into practical collections of variables, given their connections, computer time needed, and computer time available. Then, when the task has been defined and the resources are available, a computer run can automatically be performed.
 
===Suggested techniques to get started===
 
'''[[:en:MySQL|MySQL]]:'''
 
The current idea is to describe the variables in Mediawiki, which is a text-based software. It would therefore become very difficult to operate these functionalities from there. Instead, if we store the data into the most convenient way, it can be effectively utilised. The most convenient way is to use an SQL database, which is the standard for large databanks. Among all SQL software, MySQL is the best due to several reasons:
* It is freely available open access software.
* It is easy to use.
* It has powerful functionalities.


{| {{prettytable}}
|  style="background-color: #CCDFC8;"|Names of explanation columns, also known as indices.
|-----
|  style="background-color: #ECFFE8;"|Explanation data, also known as locations. You can use these columns as search criteria.
|-----
|  style="background-color: #FFD8F0;"|Observation index, typically called "Observation". Common name for all observation columns
|-----
|  style="background-color: #DFB8D0;"|Names of observation columns. These are the parameters of interest.
|-----
|  style="background-color: #ECDFE8;"|Observation data. These are the actual measurements.
|-----
|}


To make this work out, we need a '''[[Help:Variable transfer protocol|variable transfer protocol]]''' so that the result of a variable can be retrieved either automatically by a calculating software, or manually by the user who wants to explore the result. Fancy presenting software can be built on top of the database, so that the user does not see huge lists of numbers, but nice distributions instead. The development of this software is, again, technically straightforward, because:
===Table structure in the database===
* It is only communicating with the MySQL database, except some launch codes must be placed in other parts of the [[Help:Intarese toolbox|toolbox]]. Thus, the development can easily be decentralized.
* Something applicable probably exists in the open code world.
* It is not needed in the early life stages of the toolbox.


==== All tables ====


==A suggested table and column structure for the database==
{| VALIGN="top" BORDER="0"
{| VALIGN="top" BORDER="0"
|-
|-
|
|VALIGN="top"|
'''Variable'''
{| {{prettytable}}
{| {{prettytable}}
|----
|colspan=5|'''act'''
| '''FIELD'''
|-
| '''TYPE'''
|colspan=5|'''Uploads, updates, and other actions'''
| '''EXTRA'''
|-
|----
|Field||Type||Null||Extra||Key
| Var_id
|-
| mediumint(8)
|id||int(10) unsigned||NO||auto_increment||PRI
| primary
|-
|----
|acttype_id||tinyint(3) unsigned||NO||||MUL
| Var_name
|-
| varchar(20)
|who||varchar(50)||NO||||
| unique
|-
|----
|comments||varchar(250)||YES||||
| Var_title
|-
| varchar(100)
|time||timestamp||NO||||
|  
|-
|----
|temp_id||int(10) unsigned||NO||||MUL
| Var_scope
| varchar(1000)
|  
|----
| Var_unit
| varchar(16)
|  
|----
| Page_id
| mediumint(8)
|  
|----
| Wiki_id
| tinyint(3)
|  
|----
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Result'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''actloc'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Locations of an act'''
|----
|-
| Result_id
|Field||Type||Null||Extra||Key
| int(10)
|-
| primary
|actobj_id||int(10) unsigned||NO||||PRI
|----
|-
| Var_id
|loc_id||int(10) unsigned||NO||||PRI
| mediumint(8)
|  
|----
| Result
| varchar(1000)
|  
|----
| Sample
| smallint(5)
|  
|----
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Location'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''actobj'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Acts of an object'''
|----
|-
| Loc_id
|Field||Type||Null||Extra||Key
| mediumint(8)
|-
| primary
|id||int(10) unsigned||NO||auto_increment||PRI
|----
|-
| Dim_id
|act_id||int(10) unsigned||NO||||MUL
| mediumint(8)
|-
|  
|obj_id||int(10) unsigned||NO||||MUL
|----
|-
| Location
|series_id||int(10) unsigned||NO||||MUL
| varchar(1000)
|-
|  
|unit||varchar(64)||YES||||
|----
|}
|}
|-
|-
|VALIGN="top"|
|
'''Dimension'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''acttype'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''List of action types'''
|----
|-
| Dim_id
|Field||Type||Null||Extra||Key
| mediumint(8)
|-
| primary
|id||int(10) unsigned||NO||auto_increment||PRI
|----
|-
| Dim_name
|acttype||varchar(250)||NO||||UNI
| varchar(100)
|  
|----
| Dim_title
| varchar(100)
|  
|----
| Dim_unit
| varchar(16)
|  
|----
| Page_id
| mediumint(8)
|  
|----
| Wiki_id
| tinyint(3)
|  
|----
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Index'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''cell'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Cells of an object'''
|----
|-
| Ind_id
|Field||Type||Null||Extra||Key
| int(10)
|-
| primary
|id||int(12) unsigned||NO||auto_increment||PRI
|----
|-
| Ind_name
|actobj_id||int(10) unsigned||NO||||MUL
| varchar(100)
|-
|  
|mean||float||YES||||
|----
|-
| Dim_id
|sd||float||NO||||
| mediumint(8)
|-
|  
|n||int(10)||NO||||
|----
|-
|sip||varchar(2000)||YES||||
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Rows'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''loc'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Location information'''
|----
|-
| Ind_id
|Field||Type||Null||Extra||Key
| int(10)
|-
| unique
|id||int(10) unsigned||NO||auto_increment||PRI
|----
|-
| Row_number
|std_id||int(10) unsigned||NO||||MUL
| int(10)
|-
| unique
|obj_id_i||int(10) unsigned||NO||||MUL
|----
|-
| Loc_id
|location||varchar(100)||NO||||
| mediumint(8)
|-
|  
|roww||mediumint(8) unsigned||NO||||
|----
|-
|description||varchar(150)||NO||||
|}
|}
|-
|-
|VALIGN="top"|
|
'''Loc_of_result'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''loccell'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Locations of a cell'''
|----
|-
| Loc_id
|Field||Type||Null||Extra||Key
| mediumint(8)
|-
| unique
|cell_id||int(10) unsigned||NO||||PRI
|----
|-
| Result_id
|loc_id||int(10) unsigned||NO||||PRI
| int(10)
|
|----
| Var_id
| mediumint(8)
|  
|----
| Ind_id
| mediumint(8)
|  
|----
| N
| mediumint(8)
|  
|----
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Risk_assessment'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''obj'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Object information (all objects)'''
|----
|-
| RA_id
|Field||Type||Null||Extra||Key
| smallint(5)
|-
| primary
|id||int(10) unsigned||NO||auto_increment||PRI
|----
|-
| RA_name
|ident||varchar(20)||NO||||UNI
| varchar(100)
|-
|  
|name||varchar(200)||NO||||
|----
|-
| RA_scope
|objtype_id||tinyint(3) unsigned||NO||||MUL
| varchar(1000)
|-
|  
|page||int(10) unsigned||NO||||
|----
|-
| RA_started
|wiki_id||tinyint(3) unsigned||NO||||
| date
|  
|----
| RA_finished
| date
|  
|----
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''RA_vars'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''objtype'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Types of objects'''
|----
|-
| RA_id
|Field||Type||Null||Extra||Key
| smallint(5)
|-
| unique
|id||tinyint(3)||NO||||PRI
|----
|-
| Var_id
|objtype||varchar(30)||NO||||
| mediumint(8)
| unique
|----
|}
|}
|-
|-
|VALIGN="top"|
|
'''RA_indices'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''res'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Result distribution (actual values)'''
|----
|-
| RA_id
|Field||Type||Null||Extra||Key
| smallint(5)
|-
| unique
|id||bigint(20) unsigned||NO||auto_increment||PRI
|----
|-
| Ind_id
|cell_id||int(12) unsigned||NO||||MUL
| int(10)
|-
| unique
|obs||int(10) unsigned||NO||||
|----
|-
|result||float||NO||||
|-
|restext||varchar(250)||YES||||
|-
|implausible||binary(1)||YES||||
|}
|}
|VALIGN="top"|
|VALIGN="top"|
'''Causality'''
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|colspan=5|'''wiki'''
| '''TYPE'''
|-
| '''EXTRA'''
|colspan=5|'''Wiki information'''
|----
|-
| Var_id
|Field||Type||Null||Extra||Key
| mediumint(8)
|-
|  
|id||tinyint(3)||NO||||PRI
|----
|-
| Causality_date
|url||varchar(255)||NO||||
| date
|-
|  
|wname||varchar(20)||NO||||
|----
|}
| Parent_id
| mediumint(8)
|  
|----
|}
|}
|VALIGN="top"|
 
'''Formula'''
====Contents of selected tables====
 
{|
|
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|+ Table objtype
| '''TYPE'''
! id!! objtype
| '''EXTRA'''
|----
|----
| Var_id
|| 1|| Variable
| mediumint(8)
|  
|----
|----
| Formula_date
|| 2|| Study
| date
|  
|----
|----
| Software
|| 3|| Method
| varchar(100)
|  
|----
|----
| Formula
|| 4|| Assessment
| varchar(100)
|  
|----
|----
|}
|| 5|| Class
|-
|VALIGN="top"|
'''Data'''
{| {{prettytable}}
| '''FIELD'''
| '''TYPE'''
| '''EXTRA'''
|----
|----
| Var_id
|| 6|| Index
| mediumint(8)
|  
|----
|----
| Data_date
|| 7|| Nugget
| date
|  
|----
|----
| URL
|| 8|| Encyclopedia article
| varchar(100)
|  
|----
|----
|}
|| 9|| Run
|VALIGN="top"|
'''Run'''
{| {{prettytable}}
| '''FIELD'''
| '''TYPE'''
| '''EXTRA'''
|----
| Run_id
| mediumint(8)
| primary
|----
| Run_date
| date
|
|----
| Run_who
| varchar(50)
|
|----
| Run_method
| varchar(200)
|
|----
|----
|}
|}
|VALIGN="top"|
 
'''Run_list'''
|
 
{| {{prettytable}}
{| {{prettytable}}
| '''FIELD'''
|+ Table acttype
| '''TYPE'''
! id|| acttype
| '''EXTRA'''
|----
|----
| Run_id
|| 1|| Start object
| int(16)
|  
|----
|----
| Run_order
|| 2|| Finish assessment
| varchar(100)
|  
|----
|----
| Var_id
|| 3|| Update formula
| int(16)
|  
|----
|----
| Result_id
|| 4|| Upload data (replace)
| int(10)
|
|----
|----
|}
|| 5|| Upload data (append)
|-
|VALIGN="top"|
'''Wiki_location'''
{| {{prettytable}}
| '''FIELD'''
| '''TYPE'''
| '''EXTRA'''
|----
|----
| Wiki_id
|| 6|| Review scope
| tinyint(3)
| primary
|----
|----
| URL
|| 7|| Review definition
| varchar(60)
|  
|----
|----
| Wiki_name
|| 8|| Add object info
| varchar(20)
|  
|----
|----
|}
|}
|}
|}


==Rationale==


===Data===


====Software====


----
Because Opasnet base will contain very large amounts of mostly numerical information, the state-of-the-art structure is a [[:en:SQL|SQL]] database. Because of its flexibility, ease of use, and cost, [[:en:MySQL|MySQL]] is an optimal choice among SQL software. In addition to the database software, a [[variable transfer protocol]] is needed on top of that so that the results of variables can be retrieved and  new results stored either automatically by a calculating software, or manually by the user. Fancy presenting software can be built on top of the database, but that is not the topic of this page.


'''Result database''' is for storing variable results in a way that they can be used independently of the assessment they were created in.
====Storage and retrieval of results of variables====


==Possible uses of the database==
The most important functionality is to store and retrieve the results of variables. Because variables may take very different forms (from a single value such as natural constant to an uncertain spatio-temporal concentration field over the whole Europe), the database must be very flexible. The basic solution is described in the [[variable]] page, and it is only briefly summarised here. The result is described as
 
  P(R|x<sub>1</sub>,x<sub>2</sub>,...)


===Making value-of-information analyses===
where P(R) is the probability distribution of the result and x<sub>1</sub> and x<sub>2</sub> are defining [[location]]s of an [[index]] where a particular P(R) applies. Typically locations are operationalised as discrete [[Index|indices]]. A variable must have at least one [[index]]. [[Uncertainty]] about the true value of the variable is operationalised as a random sample from the probability distribution, in such a way that the samples are located along an index ''Sample'', which is a list of integers 1,2,3...n, where n=number of samples.


[[:en:Value of information|Value of information]] (VOI) is a [[:en:decision analysis|decision analysis]] tool for estimating the importance of remaining uncertainty for decision-making. For detailed description, see [[:en:Value of information|Value of information]]. Result database can be used to perform a large number of VOI analyses, because all variables are in the right format for that: as random samples from uncertain variables. The analysis is done by optimising an [[indicator]] by adjusting a [[decision variable]] so that the variable under analysis is conditionalised to different values. All this can in theory be done in the result database by just listing the indicator, the decision variable, and the variable of interest. Practical tools should be developed for this. After that, systematic VOI analyses can be made over a wide range of environmental health issues.


==The improvement of the quality of a variable in time==
* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base&oldid=7856 Old description of the structure]


All results that have once been stored in the result database remain there. Although the old results are not interesting for environmental health assessments after the updated result has been stored, they can be very interesting for other purposes. Some potential uses are listed below:
===Dependencies===
* The [[informativeness]] and [[calibration]] (see [[performance]]) can be evaluated for a single variable in time against the newest information.
* Critical pieces of information that had made major contribution to the informativeness and calibration can be identified afterwards.
* Large number of variables can be assessed and e.g. following questions can be asked:
** How much work is needed to make a variable to have reasonable performance for practical applications?
** What are the critical steps after which the variable performance is saturated, i.e., does not improve much despite additional effort?


* [[Opasnet structure]]
* [[Open assessment]]


==Some useful syntax==
====Replacing some cells====


http://www.baycongroup.com/sql_join.htm
It is possible that there is a large data, where there is a need to update only a few cells while all others remain the same. How should this be done? There are a few potential alternatives.
# Use the current replace functionality. Replace all cells but most of them with the original value.
# Use a new act_type that is similar to the current append functionality. This should be understood in a way that if there are two (or more) identical cells (based on cell indices and locations), then the newest result is used and all older ones are discarded. (If the old ''append'' is used, then new info is just seen as a new row in the data table, not a replacement of an existing row.
# Add a new field into the cell (?) table with an updated cell_id (in a similar way than act_id and series_id are used in the actobj table). This way, the new cell can automatically inherit all locations of the old cell.


----
===Formula structure===


* [[:image:RDB connection.ANA|Result database connection module]] for Analytica: for writing variable results into the database. This requires a password.
Now it has become clear that it is not enough to have samples of the result distributions. It must be possible to completely recalculate the result based on the information in the [[Opasnet Base]]. There are different approaches:
* [[:image:RDB reader.ANA|Result database reader module]] for Analytica: for reading variable results into the database.
* Calculate the result based on a formula that may refer to other variables called parents. This is a deterministic approach.
* Calculate the result based on the marginal distribution and (conditional) rank correlations with parent variables. This is a probabilistic approach.


* For SQL used by [[:image:RDB reader.ANA|RDB reader.ANA]] and [[:image:RDB connection.ANA|RDB connection.ANA]], see the respective pages.
This approach requires new tables, namely Formula and Language.


===Useful queries that are not (yet) part of a model of procedure===


'''List all dimensions that have indices, and the indices concatenated:
: {{comment|11|Do we need tables DIF and DIP like Uninet?|--[[User:Jouni|Jouni]] 21:50, 30 December 2009 (UTC)}}
* DIP
** DIP_node_id
** DIP_parent_node_id
** DIP_corr_coeff
** DIP_parent_index
* DIF
** DIF_node_id
** DIF_formula
** DIF_varnames_in_formula


Select Dim_name, dim_title, dim_unit, Group_concat(Ind_name order by ind_name separator ', ') as Indices
===Universal Opasnet Base===
from Dimension, `Index`
where Dimension.dim_id = `Index`.Dim_id
group by Dim_name
order by Dimension.dim_id


The idea of universal Opasnet Base says that it should be possible to store results in such a way that the results themselves are public but their interpretation is limited. For example, patient symptoms and clinical test results should be openly available for research, but information about whose results they are should be private. This can be achieved with the following database structure.


'''List all indices, and their locations concatenated:
[[File:Universal Opasnet Base structure.png|thumb|400px|Universal Opasnet Base has some parts that exist in different versions depending on the privacy level. The yellow areas are e.g. a public area and a private area. The parts that are white are public.]]


Select Dim_name, Dim_title, Dim_unit, Ind_name, Group_concat(Location order by row_number separator ', ') as Locations
Let's say that it is enough to have two security levels, public and private. A person wants to record personal health information into the database. She logs in with her personal user name. The private profile gives the name (say, Liisa) and social security number of the person, while the public profile says only "30-40-year-old woman in Finland". Liisa writes down her symptoms or medical information and saves them. This is what is stored in the databases:
from `Index`, Location, Rows, Dimension
where `Index`.ind_id= Rows.ind_id and Rows.loc_id = Location.loc_id and `Index`.dim_id = Dimension.dim_id
group by Ind_name
order by Dim_name, `Index`.ind_name


{| {{prettytable}}
|+ '''Information stored in the public and private databases. The private database can read tables from the public one but not vice versa.
! Table, field
! Private database
! Public database
|----
| act.who
| Liisa, 010175-1024
| Woman, 30-40 a
|----
| act.when
| 2011-03-09 22:09:10
| 2011-03
|----
| obj.name
| N/A. Data is taken from public side.
| Pregnancy test
|----
| loccell.loc_id (locations and indices explained)
| Person = 010175-1024 <br>Time = 2011-03-09 <br>Test = Clearblue digital test
| Age = 30-40 <br>Sex = Female <br>Country = Finland <br>Time = 2011-03 <br>Test = Clearblue digital test
|----
| res.restext
| N/A. Data is taken from public side.
| Pregnant 1-2 weeks.
|----
|}


'''List all variables and their runs, and also list all dimensions (concatenated) used for each variable for each run.
Based on the information, anyone can see that there is a woman in Finland who has used a Clearblue pregnancy test and the result was positive. But there is no way an outsider could connect this information to any particular person, because all information that could be used for linking is located in the private website. However, an authorised person from health case could see the data in the private database and connect Liisa and the test result.


SELECT Var_id, Run_id, Var_name, Var_title, GROUP_CONCAT(Dim_name SEPARATOR ', ') as Dimensions, n, Run_method
==See also==
FROM
    (SELECT Loc_of_result.Var_id, Run_list.Run_id, Var_name, Var_title, Dim_name, n, Run_method
    FROM Loc_of_result, Run_list, Run, Variable, Location, Dimension
    WHERE Loc_of_result.Result_id = Run_list.Result_id
    AND Run_list.Run_id = Run.Run_id
    AND Loc_of_result.Var_id = Variable.Var_id
    AND Loc_of_result.Loc_id = Location.Loc_id
    AND Location.Dim_id = Dimension.Dim_id
    GROUP BY Dimension.Dim_id, Loc_of_result.Var_id, Run_list.Run_id
    ORDER BY Loc_of_result.Var_id, Run_list.Run_id) as temp1
GROUP BY Var_id, Run_id


* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base_structure&oldid=18900#All_tables:_Overview A previous discussion about the structure]
* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base_structure&oldid=18900#Main_tables A previous structure and related discussions]
* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base_structure&oldid=18900#Tasks_performed Previous tasks performed]


===Other queries===
; A basic query for retrieving the full result of a variable upload (an example):


'''This query was used to transform the Var_id data from the table Result to Loc_of_result. This was a one-time operation that is recorded for historical interest only.
{{#sql-query:
SELECT obj.ident, obj.name, obj.unit, obj.page, obj.wiki_id, comments, mean, sd, n, location, ind.ident, obs, result, restext
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.ident = "Op_en1912"
AND actobj.series_id = 190
LIMIT 0,100
}}


UPDATE Loc_of_result,
;Some useful syntax
  (SELECT Variable.Var_id, Var_name, Loc_of_result.Loc_id, Loc_of_result.Result_id
* http://www.baycongroup.com/sql_join.htm
    FROM Variable, Loc_of_result, Result
* [[:image:Opasnet base connection.ANA|Opasnet base connection.ANA]] for Analytica: for writing and reading variable results into and from the database. Writing requires a password. For SQL used in the model, see the model page.
    WHERE Variable.Var_id = Result.Var_id and
* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base&oldid=7181#Other_queries Some historical queries]
    Loc_of_result.Result_id = Result.Result_id
* [http://en.opasnet.org/en-opwiki/index.php?title=Opasnet_base_structure&oldid=14214#Some_useful_syntax Some historical queries 2]
    GROUP BY Loc_of_result.Result_id, Loc_of_result.Loc_id) as temp1
[[Category:Opasnet Base]]
SET Loc_of_result.Var_id = temp1.Var_id
[[Category:Data]]
WHERE Loc_of_result.Loc_id = temp1.Loc_id and
[[Category:Opasnet]]
    Loc_of_result.Result_id = temp1.Result_id

Latest revision as of 12:01, 10 January 2014



This page is about the old structure of Opasnet Base used in ca. 2008-2011. For a description about the current database, see Opasnet base 2.

Question

Structure and connections (lines) of the tables (boxes) in the Opasnet Base. All table identifiers are called id (so they can be called by like obj.id). When obj.id is referred to in another table such as actobj, it is called actobj.obj_id. The latter end of a one-to-many relationship is marked with a ring. Important substantive fields are listed inside table boxes.

Opasnet Base is a storage and retrieval system for results of variable and data from studies. What is the structure of Opasnet Base such that it enables the following functionalities?

  1. Storage of results of variables with uncertainties when necessary, and as multidimensional arrays when necessary.R↻
  2. Automatic retrieval of results when called from Opasnet wiki or other platforms or modelling systems.
  3. Description and handling of the indicess that a variable may take.
  4. It is possible to protect some results and data from reading by unauthorised persons.
  5. If is possible to build user interfaces for easily entering observations into the Base.

Answer

Opasnet base is a MySQL database located at http://base.opasnet.org.

Data structure

Main article: Data structures in Opasnet

All data should be convertible into the following format:

Observation
Year Sex Age Height Weight Description
2009 Male 20 178 70 An optional column for descriptive text about each row.
2009 Male 30 174 79
2010 Male 25 183 84
2010 Female 22 168 65

where

Names of explanation columns, also known as indices.
Explanation data, also known as locations. You can use these columns as search criteria.
Observation index, typically called "Observation". Common name for all observation columns
Names of observation columns. These are the parameters of interest.
Observation data. These are the actual measurements.

Table structure in the database

All tables

act
Uploads, updates, and other actions
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
acttype_id tinyint(3) unsigned NO MUL
who varchar(50) NO
comments varchar(250) YES
time timestamp NO
temp_id int(10) unsigned NO MUL
actloc
Locations of an act
Field Type Null Extra Key
actobj_id int(10) unsigned NO PRI
loc_id int(10) unsigned NO PRI
actobj
Acts of an object
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
act_id int(10) unsigned NO MUL
obj_id int(10) unsigned NO MUL
series_id int(10) unsigned NO MUL
unit varchar(64) YES
acttype
List of action types
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
acttype varchar(250) NO UNI
cell
Cells of an object
Field Type Null Extra Key
id int(12) unsigned NO auto_increment PRI
actobj_id int(10) unsigned NO MUL
mean float YES
sd float NO
n int(10) NO
sip varchar(2000) YES
loc
Location information
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
std_id int(10) unsigned NO MUL
obj_id_i int(10) unsigned NO MUL
location varchar(100) NO
roww mediumint(8) unsigned NO
description varchar(150) NO
loccell
Locations of a cell
Field Type Null Extra Key
cell_id int(10) unsigned NO PRI
loc_id int(10) unsigned NO PRI
obj
Object information (all objects)
Field Type Null Extra Key
id int(10) unsigned NO auto_increment PRI
ident varchar(20) NO UNI
name varchar(200) NO
objtype_id tinyint(3) unsigned NO MUL
page int(10) unsigned NO
wiki_id tinyint(3) unsigned NO
objtype
Types of objects
Field Type Null Extra Key
id tinyint(3) NO PRI
objtype varchar(30) NO
res
Result distribution (actual values)
Field Type Null Extra Key
id bigint(20) unsigned NO auto_increment PRI
cell_id int(12) unsigned NO MUL
obs int(10) unsigned NO
result float NO
restext varchar(250) YES
implausible binary(1) YES
wiki
Wiki information
Field Type Null Extra Key
id tinyint(3) NO PRI
url varchar(255) NO
wname varchar(20) NO

Contents of selected tables

Table objtype
id objtype
1 Variable
2 Study
3 Method
4 Assessment
5 Class
6 Index
7 Nugget
8 Encyclopedia article
9 Run
Table acttype
id acttype
1 Start object
2 Finish assessment
3 Update formula
4 Upload data (replace)
5 Upload data (append)
6 Review scope
7 Review definition
8 Add object info

Rationale

Data

Software

Because Opasnet base will contain very large amounts of mostly numerical information, the state-of-the-art structure is a SQL database. Because of its flexibility, ease of use, and cost, MySQL is an optimal choice among SQL software. In addition to the database software, a variable transfer protocol is needed on top of that so that the results of variables can be retrieved and new results stored either automatically by a calculating software, or manually by the user. Fancy presenting software can be built on top of the database, but that is not the topic of this page.

Storage and retrieval of results of variables

The most important functionality is to store and retrieve the results of variables. Because variables may take very different forms (from a single value such as natural constant to an uncertain spatio-temporal concentration field over the whole Europe), the database must be very flexible. The basic solution is described in the variable page, and it is only briefly summarised here. The result is described as 

  P(R|x1,x2,...)

where P(R) is the probability distribution of the result and x1 and x2 are defining locations of an index where a particular P(R) applies. Typically locations are operationalised as discrete indices. A variable must have at least one index. Uncertainty about the true value of the variable is operationalised as a random sample from the probability distribution, in such a way that the samples are located along an index Sample, which is a list of integers 1,2,3...n, where n=number of samples.


Dependencies

Replacing some cells

It is possible that there is a large data, where there is a need to update only a few cells while all others remain the same. How should this be done? There are a few potential alternatives.

  1. Use the current replace functionality. Replace all cells but most of them with the original value.
  2. Use a new act_type that is similar to the current append functionality. This should be understood in a way that if there are two (or more) identical cells (based on cell indices and locations), then the newest result is used and all older ones are discarded. (If the old append is used, then new info is just seen as a new row in the data table, not a replacement of an existing row.
  3. Add a new field into the cell (?) table with an updated cell_id (in a similar way than act_id and series_id are used in the actobj table). This way, the new cell can automatically inherit all locations of the old cell.

Formula structure

Now it has become clear that it is not enough to have samples of the result distributions. It must be possible to completely recalculate the result based on the information in the Opasnet Base. There are different approaches:

  • Calculate the result based on a formula that may refer to other variables called parents. This is a deterministic approach.
  • Calculate the result based on the marginal distribution and (conditional) rank correlations with parent variables. This is a probabilistic approach.

This approach requires new tables, namely Formula and Language.


----11: . Do we need tables DIF and DIP like Uninet? --Jouni 21:50, 30 December 2009 (UTC) (type: truth; paradigms: science: comment)
  • DIP
    • DIP_node_id
    • DIP_parent_node_id
    • DIP_corr_coeff
    • DIP_parent_index
  • DIF
    • DIF_node_id
    • DIF_formula
    • DIF_varnames_in_formula

Universal Opasnet Base

The idea of universal Opasnet Base says that it should be possible to store results in such a way that the results themselves are public but their interpretation is limited. For example, patient symptoms and clinical test results should be openly available for research, but information about whose results they are should be private. This can be achieved with the following database structure.

Universal Opasnet Base has some parts that exist in different versions depending on the privacy level. The yellow areas are e.g. a public area and a private area. The parts that are white are public.

Let's say that it is enough to have two security levels, public and private. A person wants to record personal health information into the database. She logs in with her personal user name. The private profile gives the name (say, Liisa) and social security number of the person, while the public profile says only "30-40-year-old woman in Finland". Liisa writes down her symptoms or medical information and saves them. This is what is stored in the databases:

Information stored in the public and private databases. The private database can read tables from the public one but not vice versa.
Table, field Private database Public database
act.who Liisa, 010175-1024 Woman, 30-40 a
act.when 2011-03-09 22:09:10 2011-03
obj.name N/A. Data is taken from public side. Pregnancy test
loccell.loc_id (locations and indices explained) Person = 010175-1024
Time = 2011-03-09
Test = Clearblue digital test 		Age = 30-40
Sex = Female
Country = Finland
Time = 2011-03
Test = Clearblue digital test
res.restext N/A. Data is taken from public side. Pregnant 1-2 weeks.

Based on the information, anyone can see that there is a woman in Finland who has used a Clearblue pregnancy test and the result was positive. But there is no way an outsider could connect this information to any particular person, because all information that could be used for linking is located in the private website. However, an authorised person from health case could see the data in the private database and connect Liisa and the test result.

See also

A basic query for retrieving the full result of a variable upload (an example)

{{#sql-query: SELECT obj.ident, obj.name, obj.unit, obj.page, obj.wiki_id, comments, mean, sd, n, location, ind.ident, obs, result, restext 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.ident = "Op_en1912" AND actobj.series_id = 190 LIMIT 0,100 }}

Some useful syntax
Retrieved from "https://en.opasnet.org/index.php?title=Opasnet_base_structure&oldid=31327"