wiki:dbchoice
Last modified 21 months ago Last modified on 10/18/12 14:35:32

Using a database from Rappture

Goals

  • Don't make the tool developer add ODBC to their code.
  • Handle only simple use cases initially. This can get complicated quickly.
  • Add more security details after we work out initial semantics.

Example 1: A simple list of items (single select)

  <dataselector id="excipients">
    <about>
      <label>Excipients</label>
      <description>List the available excipients.</description>
    </about>
    <request>
      <dataview>http://pharmahub.org/resources/112:viewname</dataview>
      <quantity>cn=Compendial Name</quantity>
      <quantity>desc=Description</quantity>
      <quantity>date=Date Measured</quantity>
    </request>
    <select>single</select>
    <show>${cn} (${desc})</show>
    <report><columns>cn date</columns><format>csv</format></report>
  </dataselector> 

May be a simple single list (dropdown) of information. When you click on an item you highlight that row.

<request>
Initialization section is used to populate the list. It will access the specified database and get a list of values.
<use>
Name of the database to use. This is the equivalent of providing the host, port, user, and password.
<select>
This is the select statement used to query the database. This will be normally provided by the DataStore viewer. In its final form (for all users), it should be a stored procedure.

How do you select a database? Ultimately, the mysqlclient code needs the host, port, user, and password.

Example 2. Using a specific criteria to select items

  <dataselector id="excipients">
    <request>
      <dataview>http://pharmahub.org/resources/112:viewname</dataview>
      <quantity>cn=Compendial Name</quantity>
      <quantity>desc=Description</quantity>
      <quantity>date=Date Measured</quantity>
    </request>
    <filter>${cn} == "*mono*" || col(cn) == input.choice(foo)</filter>
    <select>single</select>
    <show>${cn} (${desc})</show>
    <report><columns>cn date</columns><format>csv</format></report>
  </dataselector> 

Example 3. Using a specific criteria from another Rappture control.

  <choice id="excipients">
    <request>
      <use>myDB</use>
      <select>
         SELECT NAME from excipients WHERE type = @input.string(type)@
      </select>
    </request>
  </choice> 

Uses the value of another control as a input to the SELECT call. Will automatically request new list whenever the control changes.

Example 4. Cascading controls to select items

  <choice id="excipients">
    <request>
      <use>myDB</use>
      <select>
         SELECT NAME from excipients WHERE type = @input.string(type)@
      </select>
    </request>
  </choice> 
 <choice id="products">
    <request>
      <use>myDB</use>
      <select>
         SELECT NAME from products WHERE excipient = @input.choice(excipients)@ 
      </select>
    </request>
  </choice> 

Use of one another dropdown to affect the next. If the "type" is changed, the both lists are updated.

Example 5. A single list of items (multiple select)

  <choice id="excipients">
    <request>
      <use>myDB</use>
      <select>
         SELECT excipient, product, NAME, RATIO from excipients WHERE type = @input.string(type)@
      </select>
    </request>
    <mode>multiple</mode>
  </choice> 
<mode>
Selects the mode (single or multiple). Drag out multiple selections. Can use control/shift button combinations.

In multiple mode the items are returned as comma separated values (CSV). An optional top can supply the column headers.

Example 6: A list of one or more columns (single select)

  <table id="lots">
    <about>
      <label>Lots</label>
      <description>List the lots available.</description>
    </about>
    <request>
      <use>myDB</use>
      <select>
         SELECT NAME from lots where excipients = * products = *
      </select>
    </request>
    <default>0</default>
  </table> 

This widget is is table with column headers that can be sorted and filters. It will produces a CSV current value.

Example 7. Launch Rappture tool from DataStore, passing the current view.

Rappture reads the launch file and fills in the <request> elements. It may be only the <select> element.

  <dataselector id="lots">
    <about>
      <label>Lots</label>
      <description>List the lots available.</description>
    </about>
    ...
  </table> 

Tie into parameter specifications: https://nees.org/groups/parampass/wiki/ParameterFileFormat

datastore(lots):132/stdview/8,27,52