Difference between revisions of "File:Opasnet base connection.ANA"

From Testiwiki
Jump to: navigation, search
(uploaded a new version of "Image:Opasnet base connection.ANA": The model passed all functionality tests. Now it is ready for test use. Farmed salmon model was removed from the file.)
(technical edits)
 
(61 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
[[Category:Opasnet]]
 +
[[Category:Opasnet Base]]
 
[[Category:Analytica tool]]
 
[[Category:Analytica tool]]
 
[[Category:SQL tool]]
 
[[Category:SQL tool]]
 
[[Category:Open assessment]]
 
[[Category:Open assessment]]
−
{{tool}}
+
{{tool|moderator=Jouni}}
−
This model is a compilation of [[:image:RDB connection.ANA]] and [[:image:RDB reader.ANA]]. It reads and writes [[variable]] [[result]]s from and to [[Opasnet base]].  
+
''This page relates to old versions of [[Opasnet Base (2008-2011)]] and its connections. For the current database version, see [[Opasnet Base]].
  
−
'''Reading
+
This model is an interface for uploading data to and downloading it from the [[Opasnet Base]]. For using the file, you need [[Analytica]] Enterprise.
  
−
Reading is allowed for everyone. The function Rdb_to_var reads the [[result]] of a [[variable]] from [[Opasnet base]] and transforms the information into the form of an [[Analytica]] node. Note that all indices required must be created before the function works properly. To learn about the indices, run the function Do_first.
+
[[Uploading to Opasnet Base]] helps you understand what data could and should be updated to [[Opasnet Base]] and what the recommended data structures and formats are. For technical instructions how to use the current upload software, see [[Opasnet Base connection]]. For a general description about the database, see [[Opasnet Base]] and for technical details about the database, see [[Opasnet Base structure]]. For details about downloading data, see [[Opasnet Base UI]].
  
 +
'''Some key parts of OBC explained
  
−
'''Writing
+
; Findid: This function gets an id from a table. It has the following parameters: '''in''': the property for which the id is needed. In MUST be unique in cond and it must contain index i. '''table''': the table from where the id is brought. The table MUST have .j as the column index, .i as the row index, and a column named 'id'. '''cond''': the name of the field that is compared with in. Cond must be text.
  
−
You can freely open and use the module, but you cannot actually write information into the [[Result database]] unless you have a password. Note that the necessary variable, index, dimension, and run information will be asked. You must fill in all tables before the process is completed. You can insert several variables at the same time. Each variable MUST have at least one index.
+
;Textify: Changes a number to a text value with up to 15 significant numbers. This bypasses the number formatting problem that tends to convert e.g. 93341 to '93.34K'. If the input is null, the result is ''.
  
−
==To do: improvement needs==
+
==See also==
  
−
*Run_list table needs the field Var_id. This will help identification of variables in each run without the need to go through result_id. The model should contain formula to fill it.
+
* [[Opasnet Base Connection for R]]
−
* Loc_of_result.Ind_id must be updated in the RDB. Now it is empty. Does the model contain formula to fill it?
+
* [[Opasnet Base]]
−
* There should be a button for converting nodes into edit tables. This is because some models are using external data that cannot be accessed with other versions than Analytica Enterprise. The button would simply do (Va1:= Va1) for each variable in the variable list.
+
* [[Opasnet Base structure]]
−
* The correct IP should be used so that the model works in the Internet as well. The correct IP is 193.167.179.97.
+
* [[Uploading to Opasnet Base]]
−
 
+
* [[Opasnet Base UI]]
−
===Functions===
+
* Previous version [[:image:RDB connection.ANA]] and [[:image:RDB reader.ANA]] were only for either reading or writing data.
−
 
+
[[Category:Opasnet Base]]
−
'''RDB_to_var:
+
* [http://cs.stanford.edu/people/thathoo/rmysql.pdf Guidance document for RMySQL] (the MySQL database connection for [[R]] software.
−
 
 
−
Brings the results from the [[Opasnet base]] and transforms it into variables of the correct form. NOTE! All necessary indices must be created before running this function. The necessary indices can be viewed by calling the function Do_first with the same two parameters as this function.
 
−
 
 
−
PARAMETERS:
 
−
* Var_name: the name of the variable in the result database.
 
−
* Run_id: the identifier of the run from which the results will be brought. If omitted, the newest result will be brought.
 
−
* Textornot: Tells whether the result is numerical or text. If parameter is omitted or false, numerical is assumed, otherwise text.
 
−
 
 
−
 
 
−
'''Do_first:
 
−
 
 
−
This function brings the variable from the [[Result database]] and analyses its structure. Each index used will be shown as a column along '.Ind_name', and each location of that index will be shown along '.K'. The last row of '.K' shows the samplesize of the variable.
 
−
Use this information to create the necessary indices for your model and to adjust the samplesize of the model. If the samplesize of the model is smaller than in the result database, the remaining samples are omitted; if larger, the cells with no results in the database are replaced with null. NOTE! The indices created should be lists of labels (not lists of numbers).
 
−
 
 
−
PARAMETERS (see also above):
 
−
* Var_name
 
−
* Run_id
 
−
 
 
−
==SQL code in the model==
 
−
 
 
−
'''SQL for Dimension table:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Dim_id, Dim_name
 
−
FROM `Dimension`
 
−
</sql-query>
 
−
 
 
−
'''SQL for Indices table:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Ind_id, Ind_name, Dimension.Dim_id, Dim_name
 
−
FROM `Index`, Dimension
 
−
WHERE `Index`.Dim_id=Dimension.Dim_id
 
−
</sql-query>
 
−
 
 
−
'''SQL for Locations table:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Loc_id, Location, Dimension.Dim_id, Dim_name
 
−
FROM `Location`, `Dimension` WHERE Location.Dim_id = Dimension.Dim_id
 
−
</sql-query>
 
−
 
 
−
'''SQL for Variables table:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Var_id, Var_name
 
−
FROM `Variable`
 
−
</sql-query>
 
−
 
 
−
'''Readdata: SQL query:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Variable.var_name, var_unit, Loc_of_result.result_id, result, sample, dim_name, ind_name, location, Run.run_id, run_method
 
−
FROM Variable, Result, Loc_of_result, Location, Dimension, `Index`, Run_list, Run
 
−
WHERE Variable.var_name = "'&Var_name&'"
 
−
AND Run.run_id = '&Run_id&'
 
−
AND Variable.var_id = Loc_of_result.var_id
 
−
AND Loc_of_result.result_id = Result.result_id
 
−
AND Loc_of_result.loc_id = Location.loc_id
 
−
AND Loc_of_result.ind_id = `Index`.ind_id
 
−
AND Location.dim_id = Dimension.dim_id
 
−
AND Loc_of_result.result_id = Run_list.result_id
 
−
AND Run_list.run_id = Run.run_id
 
−
</sql-query>
 
−
 
 
−
'''Newestrun SQL query:
 
−
 
 
−
<sql-query display="1">
 
−
SELECT Run_id
 
−
FROM Variable , Loc_of_result, Run_list
 
−
WHERE Variable.var_name = "'&Var_name&'"
 
−
AND Variable.var_id = Loc_of_result.var_id
 
−
AND Loc_of_result.result_id = Run_list.result_id
 
−
</sql-query>
 

Latest revision as of 19:01, 10 April 2015


This page relates to old versions of Opasnet Base (2008-2011) and its connections. For the current database version, see Opasnet Base.

This model is an interface for uploading data to and downloading it from the Opasnet Base. For using the file, you need Analytica Enterprise.

Uploading to Opasnet Base helps you understand what data could and should be updated to Opasnet Base and what the recommended data structures and formats are. For technical instructions how to use the current upload software, see Opasnet Base connection. For a general description about the database, see Opasnet Base and for technical details about the database, see Opasnet Base structure. For details about downloading data, see Opasnet Base UI.

Some key parts of OBC explained

Findid
This function gets an id from a table. It has the following parameters: in: the property for which the id is needed. In MUST be unique in cond and it must contain index i. table: the table from where the id is brought. The table MUST have .j as the column index, .i as the row index, and a column named 'id'. cond: the name of the field that is compared with in. Cond must be text.
Textify
Changes a number to a text value with up to 15 significant numbers. This bypasses the number formatting problem that tends to convert e.g. 93341 to '93.34K'. If the input is null, the result is .

See also

File history

Click on a date/time to view the file as it appeared at that time.

(newest | oldest) View ( | older 10) (10 | 20 | 50 | 100 | 250 | 500)
Date/TimeDimensionsUserComment
22:44, 28 December 2008 (341 KB)Jouni (talk | contribs)The test phase is not yet successful. Several write nodes need updates. An example model, Farmed salmon, is saved with this file.
23:28, 15 December 2008 (239 KB)Jouni (talk | contribs)All tasks listed with previous version are completed. Now write part should work but haven't tested. Then, read part should be updated as well.
14:50, 15 December 2008 (258 KB)Jouni (talk | contribs)Seems to work (almost). Tasks: Check Write_sett and Write_item. Make buttons for updating each table separately (is this needed for all tables?). Remove redundant attributes. Remove Old_parts. Think about updating procedure and make coherent.
23:49, 14 December 2008 (251 KB)Jouni (talk | contribs)New model starts to get shape. All database queries are now in Buttons, and query results are static nodes. Not all write nodes work, but most are pretty good already.
16:56, 8 December 2008 (150 KB)Jouni (talk | contribs)Large changes. Two thirds done. NOTE: this version does not work.
14:57, 4 December 2008 (126 KB)Jouni (talk | contribs)Small improvements
20:49, 3 December 2008 (76 KB)Jouni (talk | contribs)New ideas implemented but far from ready. Most read queries are now up-to-date. I haven't touched the insert queries yet.
14:37, 1 December 2008 (67 KB)Jouni (talk | contribs)SQL updates but only half way through. The Opasnet base structure is still changing.
05:38, 1 December 2008 (66 KB)Jouni (talk | contribs)Small edits
06:25, 29 November 2008 (66 KB)Jouni (talk | contribs)A compilation of image:RDB connection.ANA and image:RDB reader.ANA.
(newest | oldest) View ( | older 10) (10 | 20 | 50 | 100 | 250 | 500)
  • You cannot overwrite this file.

The following 3 pages link to this file:

Retrieved from "http://opasnet.org/testiwiki/index.php?title=File:Opasnet_base_connection.ANA&oldid=35358"