# Scripts Component

The OI terminal can execute shell or PHP scripts to provide advanced functionality. Scripts can be passed tags and can also read/write tags through an XML RPC interface.

Note

Scripts are only available when using OI's native embedded runtime. Scripts are not executed when operating in OI-Win runtime. OI-Win runtime provides a 'Generic Run' component to execute command line programs.

# Sample Program

The sample program includes one OIB program - prod_demo.oi. Copy this as well as the directories to the OI to run the demo.

Sample Program

Screenshot

Included in the sample are an

HTTP script to simulate production data,
Shell script to run the HTTP script in the background
OI Program which also logs production data to a database.

# HTTP Script

The HTTP script is a simple production simulator. It reads the current value of several tags, and writes values to others based on the tags it read. Currently only PHP scripts are supported.

# Shell Script

The shell script runs the HTTP script above.

The sample program provides a background-launcher and a utility to merge and output error messages to the OI screen.

Shell scripts can return values that are set to tags, as discussed below.

3 Shell scripts are provided:

bglaunch.sh: This is a tool used to monitor the directory and launch multiple scripts.
mergeoutput.php: This is a tool to merge the output of multiple scripts into one result for OI.
prodsim_launcher.sh: Simple script to call the production simulator php script.

The included scripts provide a template for launching multiple shellscripts and managing their operation. The bglaunch.sh script runs each launcher in a background task to prevent scripts from directly affecting the performance of OI, though scripts that tax the processor can still affect the overall performance of OI.

# Script Sequence

OI writes the "second" value of system time to a tag.
OI watches the "second" tag and triggers the shell script bglauncher.sh.
The shell script finds all the "launcher" scripts in the shell script directory and runs each one (unless they are already running).
The prodsim_launcher.sh script is run, which simply runs the httpscript productionsimulator.php
The productionsimulator.php script requests tag values from OI and writes new values to tags.
The next time the launcher is run, the scripts output is packaged and returned to OI.

# Improvements

If we leave the demo running for several days we'll notice that OI will start complaining about the script not finishing during the execution.

There are 2 reasons for this, DBTable view and the Hourly Totals query. To improve the performance of the whole program, we will want to minimize the work we are doing and only perform that work when necessary.

# DBTable View

We can reduce the load of the DBTable view by modifying the dataSource to include a row limit:

select * from productionlog
order by __db_store_time__ desc limit 20

Then, we can change how often the table refreshes by setting the following properties: DBTable Improvements

Property	Value	Description
refreshTag	partComplete	This will trigger refreshes based on the tag value.
refreshOnEvent	ZeroToNonZero	This will refresh only when the tag goes from zero to one.
refreshTime	0	This will disable automatically refreshing at a set time interval, we will only refresh when partComplete goes from zero to nonzero.

# Query Improvement

The query we constructed will perform a considerable amount of work searching through all the entries, performing date operations on each one and creating hourly summaries for all production.

We can help limit the work the query is performing by letting the database filter out rows that aren't needed by using a where clause:

where __db_store_time__ >= datetime('now','-24 hour', 'localtime')

The final query will look like:

select statkey, substr(statkey, -2) as displaykey,
coalesce(sideACount, 0) as sideACount,
coalesce(sideBCount, 0) as sideBCount

from
(select strftime('%Y%m%d%H',datetime('now', 'localtime' )) as statkey union
select strftime('%Y%m%d%H',datetime('now','-1 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-2 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-3 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-4 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-5 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-6 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-7 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-8 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-9 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-10 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-11 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-12 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-13 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-14 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-15 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-16 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-17 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-18 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-19 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-20 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-21 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-22 hour', 'localtime' )) union
select strftime('%Y%m%d%H',datetime('now','-23 hour', 'localtime' )) ) as statkeys
left outer join
(select
	strftime('%Y%m%d%H', __db_store_time__) as statkey,
	sum("sideA") as sideACount,
	sum("sideB") as sideBCount
	from productionlog
	where __db_store_time__ >= datetime('now','-24 hour', 'localtime')
	group by statkey
) as prod using (statkey)
order by displaykey

# Results

These changes have been tested with a table containing hundreds of thousands of entries. While this may not be the best avenue for creating a production log, this exercise shows how to use the database to accomplish a fairly complicated task, shows some possible performance issues, and some ways to address those issues. By using scripts and built in database functions, extremely complex interactions can be supported.

← PDFs Videos →