I need to look up a trade name, find its proper chemical name, then get properties of that chemical.
Relational query (SQLite)
- (Create and) open database using
_SQLite_Open().
- Create tables , indexes and view(s) (and insert records) using
_SQLite_Exec().
- Queries using
_SQLite_GetTable2d() return results as two-dimensional array.
Structure
SQLite data types. Inserting records to product and substance table may be automated.
![enter image description here]()
Table product
Defines trade names.
| id |
name |
| 1 |
dowanol pmb |
| 2 |
dowanol pma |
| 3 |
dipropylene glycol |
| 4 |
dowanol pnp |
| 5 |
glycol blend |
| 6 |
isopar e |
| 7 |
isopar c |
| 8 |
isopar h |
| 9 |
Polyether Polyol |
| 10 |
Voranol |
Creation:
CREATE TABLE IF NOT EXISTS product (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
Insert record:
INSERT INTO product (name) VALUES ('dowanol pmb');
Table substance
Defines chemical names (and properties).
| id |
name |
flare |
bay |
| 1 |
Polyether Polyol |
1 |
1 |
| 2 |
DIPROPYLENE GLYCOL |
1 |
4 |
| 3 |
GLYCOL |
2 |
3 |
| 4 |
PETROLEUM NAPHTA |
0 |
5 |
Creation:
CREATE TABLE IF NOT EXISTS substance (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
flare INTEGER,
bay INTEGER
);
Insert record:
INSERT INTO substance (name, flare, bay) VALUES ('Polyether Polyol', 1, 1);
Table relation
Defines relations between product and substance table's records (using foreign keys).
| id |
product |
substance |
| 1 |
1 |
2 |
| 2 |
2 |
2 |
| 3 |
3 |
2 |
| 4 |
4 |
2 |
| 5 |
5 |
3 |
| 6 |
6 |
4 |
| 7 |
7 |
4 |
| 8 |
8 |
4 |
| 9 |
9 |
4 |
| 10 |
10 |
1 |
Creation:
CREATE TABLE IF NOT EXISTS relation (
id INTEGER PRIMARY KEY,
product INTEGER REFERENCES product(id),
substance INTEGER REFERENCES substance(id),
UNIQUE( /* Constraint applies to *combination* of columns. */
product,
substance
)
);
Insert record:
INSERT INTO relation (
product,
substance
) VALUES (
(SELECT id FROM product WHERE name = 'dowanol pmb'),
(SELECT id FROM substance WHERE name = 'DIPROPYLENE GLYCOL')
);
Or just:
INSERT INTO relation (product, substance) VALUES (0, 1);
View view_relation
View (stored query), joining (combines) product and substance tables' fields, as per relation table's records (acting as virtual table, negating need to include underlying JOIN operators every query).
| name_product |
name_substance |
flare |
bay |
| dowanol pmb |
DIPROPYLENE GLYCOL |
1 |
4 |
| dowanol pma |
DIPROPYLENE GLYCOL |
1 |
4 |
| dipropylene glycol |
DIPROPYLENE GLYCOL |
1 |
4 |
| dowanol pnp |
DIPROPYLENE GLYCOL |
1 |
4 |
| glycol blend |
GLYCOL |
2 |
3 |
| isopar e |
PETROLEUM NAPTHA |
0 |
5 |
| isopar c |
PETROLEUM NAPTHA |
0 |
5 |
| isopar h |
PETROLEUM NAPTHA |
0 |
5 |
| Polyether Polyol |
PETROLEUM NAPTHA |
0 |
5 |
| Voranol |
Polyether Polyol |
1 |
1 |
Creation:
CREATE VIEW IF NOT EXISTS view_relation AS
SELECT
product.name AS name_product,
substance.name AS name_substance,
substance.flare,
substance.bay
FROM
relation
LEFT OUTER JOIN product ON relation.product = product.id
LEFT OUTER JOIN substance ON relation.substance = substance.id
ORDER BY
product.id ASC
;
Query by trade name
| name_product |
name_substance |
flare |
bay |
| Voranol |
Polyether Polyol |
1 |
1 |
Query:
SELECT
*
FROM
view_relation
WHERE
name_product = 'Voranol'
;
Or (without view):
SELECT
product.name AS name_product,
substance.name AS name_substance,
substance.flare,
substance.bay
FROM
relation
WHERE
product.name = 'Voranol'
LEFT OUTER JOIN product ON relation.product = product.id
LEFT OUTER JOIN substance ON relation.substance = substance.id
;
Query by substance
| name_product |
name_substance |
flare |
bay |
| dowanol pmb |
DIPROPYLENE GLYCOL |
1 |
4 |
| dowanol pma |
DIPROPYLENE GLYCOL |
1 |
4 |
| dipropylene glycol |
DIPROPYLENE GLYCOL |
1 |
4 |
| dowanol pnp |
DIPROPYLENE GLYCOL |
1 |
4 |
Query:
SELECT
*
FROM
view_relation
WHERE
name_substance = 'DIPROPYLENE GLYCOL'
;
Or (without view):
SELECT
product.name AS name_product,
substance.name AS name_substance,
substance.flare,
substance.bay
FROM
relation
WHERE
substance.name = 'DIPROPYLENE GLYCOL'
LEFT OUTER JOIN product ON relation.product = product.id
LEFT OUTER JOIN substance ON relation.substance = substance.id
;
AutoIt
Example creating (and inserting records to) a single table and query it:
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>
Global Const $g_iRecords = 50
Global Const $g_sFileDb = @ScriptDir & '\example.sqlite'
Global $g_aCreate = [ _
'CREATE TABLE IF NOT EXISTS example (id INTEGER PRIMARY KEY, name TEXT UNIQUE);' & @LF, _
'--CREATE INDEX IF NOT EXISTS index_example_name ON example(name);' & @LF _
]
Global Const $g_sInsert = 'INSERT INTO example (name) VALUES (%i);\n'
Global Const $g_sTransact1 = 'BEGIN TRANSACTION;' & @LF
Global Const $g_sTransact2 = 'END TRANSACTION;' & @LF
Global Const $g_sQuery = 'SELECT * FROM example ORDER BY id ASC;'
Global Const $g_sMsgError = 'sqlite.dll not found.' & @LF
Global $g_hDb = 0
Main()
Func Main()
Local $sQuery = ''
Local $iResultCol = 0
Local $iResultRow = 0
Local $aResult
For $i1 = 0 To UBound($g_aCreate, 1) -1
$sQuery &= $g_aCreate[$i1]
Next
For $i1 = 1 To $g_iRecords
$sQuery &= StringFormat($g_sInsert, _SQLite_FastEscape('example' & $i1))
Next
$sQuery = $g_sTransact1 & $sQuery & $g_sTransact2
ConsoleWrite($sQuery)
_SQLite_Startup()
If @error Then
ConsoleWrite($g_sMsgError)
Exit
EndIf
_SQLite_Open($g_sFileDb)
_SQLite_Exec($g_hDb, $sQuery)
_SQLite_GetTable2d($g_hDb, $g_sQuery, $aResult, $iResultRow, $iResultCol)
_SQLite_Close($g_hDb)
_SQLite_Shutdown()
_ArrayDisplay($aResult)
Exit
EndFunc
_FileReadToArrayfunction... – Idolla