search - Database, relational query

Question

Chemicals have a trade name (which is what it is commonly referred to as) and an actual chemical name. I need to look up a trade name, find its proper chemical name, then get properties of that chemical. For example:

$tradeName = "Voranol"

if $tradeName == "Voranol" then
    $productName = "Polyether Polyol"
    $flare = "List I"
    $bay = "1"
    $listPos = 3
EndIf

I have an .au3 file that contains a ton of products. It works fine but it's kind of tedious to do if $tradeName == "Name1" or $tradeName == "Name2" or $tradeName == "Name4" or $tradeName == "Name5". I will also need to be able to edit it programmaticly. This is what I'm using now:

if $product == "dowanol pmb" or $product == "dowanol pma" or $product == "dipropylene glycol" or $product == "dowanol pnp" or $productCheck2 == "dowanol pmb" or $productCheck2 == "dowanol pma" or $productCheck2 == "dipropylene glycol" or $productCheck2 == "dowanol pnp" then
    $result = "DIPROPYLENE GLYCOL"
    $bay = 4
    $useFlare = 1
    $listPos = 2

elseif $product == "glycol blend" then
    $result = "GLYCOL"
    $bay = 3
    $useFlare = 2
    $listPos = 1

elseif $product == "isopar e" or $product == "isopar c" or $product == "isopar h" or $productCheck2 == "isopar h" then
    $result = "PETROLEUM NAPTHA"
    $bay = 5
    $useFlare = 0
    $listPos = 1 

EndIf
; Note: 0 = No Flare, 1 = Normal Flare, 2 = CAS Flare

Answer 1

Another alternative to the database option is to use an XML document to store the product (chemical) information.

You could easily query the XML with XPath to get the product name/properties. Maintaining the file would be fairly easy too. You could even create an XML Schema to validate against to be sure your file is still valid after modifications.

The processing of the XML in AutoIt can be done a few different ways:

• Creating an MSXML object
• Running a command line tool like xmlstarlet
• Using a XPath/XQuery/XSLT processor from the command line (i.e. java to run Saxon)

Running something like Saxon is probably overkill for what you need.

MSXML wouldn't be too bad and there should be multiple UDFs that already exist.

Xmlstarlet would be my vote. (Note: I haven't used xmlstarlet in this fashion before. I'm a huge fan of Saxon and use it almost exclusively. Specifically for AutoIt, I've used a combination of MSXML and Saxon; Saxon to do the transforms of complicated data into a smaller, simpler subset and then MSXML to do simple xpath queries on that subset. However, if I was going to do something like this, I'd give xmlstarlet a serious look.)

Also, if your data grows to the point that a single XML file does not make sense, you could always split it into a collection of smaller files; individual products maybe. You might also reach a point that it might make sense to load those files into an actual XML database (eXistdb is an option).

Here's a simple example of what your XML (schema and instance) might look like:

XSD (products.xsd)

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="products">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" ref="product"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="product">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="name"/>
        <xs:element ref="tradenames"/>
      </xs:sequence>
      <xs:attribute name="bay" use="required" type="xs:integer"/>
      <xs:attribute name="listpos" use="required" type="xs:integer"/>
      <xs:attribute name="useflare" use="required" type="xs:integer"/>
    </xs:complexType>
  </xs:element>
  <xs:element name="tradenames">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" ref="name"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="name" type="xs:string"/>
</xs:schema>

XML (products.xml)

<products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="products.xsd">
    <product bay="4" useflare="1" listpos="2">
        <name>DIPROPYLENE GLYCOL</name>
        <tradenames>
            <name>dowanol pmb</name>
            <name>dowanol pma</name>
            <name>dipropylene glycol</name>
            <name>dowanol pnp</name>
        </tradenames>
    </product>
    <product bay="3" useflare="2" listpos="1">
        <name>GLYCOL</name>
        <tradenames>
            <name>glycol blend</name>
        </tradenames>
    </product>
    <product bay="5" useflare="0" listpos="1">
        <name>PETROLEUM NAPTHA</name>
        <tradenames>
            <name>isopar e</name>
            <name>isopar c</name>
            <name>isopar h</name>
        </tradenames>
    </product>
</products>

Here's a little bit of AutoIt that uses MSXML to demonstrate loading the XML (validates against XSD) and searching for products that have a trade name that contains "glycol".

AutoIt

;~  AutoIt Version: 3.3.14.2

;String to search on.
$searchString = "glycol"
ConsoleWrite("Search string: '" & $searchString & "'" & @CRLF)

;XPath for searching trade names. Search string is injected (code injection; escaping of strings would be a very good idea!).
$xpath_tradename = "/products/product[tradenames/name[contains(.,'" & $searchString & "')]]"
ConsoleWrite("XPath: '" & $xpath_tradename & "'" & @CRLF)

$msxml = ObjCreate('MSXML2.DOMDocument.6.0')
If IsObj($msxml) Then
    $msxml.async = False
    $msxml.validateOnParse = True
    $msxml.resolveExternals = True
    $msxml.setProperty("SelectionLanguage", "XPath")
    $msxml.load('products.xml')
    If $msxml.parseError.errorCode = 0 Then
        $prods = $msxml.SelectNodes($xpath_tradename)
        If IsObj($prods) And $prods.Length > 0 Then
            ConsoleWrite("Number of products found: '" & $prods.Length & "'" & @CRLF)
            For $prod In $prods
                ConsoleWrite(@CRLF & "------ PRODUCT ------" & @CRLF)
                ConsoleWrite("Product name: '" & $prod.SelectSingleNode('name').text & "'" & @CRLF)
                ConsoleWrite("Product bay: '" & $prod.getAttribute('bay') & "'" & @CRLF)
            Next
            ConsoleWrite(@CRLF)
        Else
            ConsoleWrite("PRODUCT NOT FOUND" & @CRLF)
        EndIf
    Else
        MsgBox(17, 'Error', 'Error opening XML file: ' & @CRLF & @CRLF & $msxml.parseError.reason)
        SetError($msxml.parseError.errorCode)
    EndIf
EndIf

Console Output

Search string: 'glycol'
XPath: '/products/product[tradenames/name[contains(.,'glycol')]]'
Number of products found: '2'

------ PRODUCT ------
Product name: 'DIPROPYLENE GLYCOL'
Product bay: '4'

------ PRODUCT ------
Product name: 'GLYCOL'
Product bay: '3'