Database Logic

The internal database logic operations provide a powerful, yet simple, mechanism for performing advanced data manipulation. The database logic consists of a set of basic operations, which can be combined to form complex functions.

The database logic is supported by the following products:

Every input to an operation can be either a constant value or a database location. The output of an operation is stored into the database. Operations can be cascaded by specifying the output database location of one operation as an input database location of another.

Each operation is executed one at a time, in sequential order, until all operations have been performed. After the final operation is executed, the first operation will be executed again after a delay. The delay may be adjusted using the “Scan Rate” field.

All operations support an “Enable Trigger” which allows the operation to be dynamically enabled or disabled. This is especially useful when combined with the compare operation to restrict other operations to execute only under certain circumstances.

All operations also support scaling on all database inputs and the output via the “Multiplier” field. The multiplier is a floating point number which is applied by multiplying the input value read from the database and by dividing the output value before writing to the database. The scaling feature is useful for those values which are stored in the database as scaled values so that the input values can be normalized before applying an operation and then re-scaled after the operation is executed. While normalization may not be necessary for all operations, certain database logic operations such as Exponential, Nth Root, and Logarithm require it to obtain the correct result.

The Not, And, Or, and Exclusive Or operations can be performed on either a bitwise or logical basis, depending on the selection of the “Operation Type”. When a logical operation type is chosen, non-zero input values are considered to be “true” and zero input values are considered to be “false”. The output value of the logical operation will then be written to the database as “1” for true and “0” for false.

Not

The Not operation outputs the inverse of the input value. The bitwise Not operation will invert each bit in the input value. The logical Not operation will evaluate the input as either true or false and output the opposite value.

And

The And operation outputs the logical conjunction of two input values. The bitwise And operation will output a 1 for each bit position in which both input value bits are 1. The logical And operation will evaluate the inputs as either true or false and output true if both input values are true.

Or

The Or operation outputs the logical disjunction of two input values. The bitwise Or operation will output a 1 for each bit position in which either input value bit is 1. The logical Or operation will evaluate the inputs as either true or false and output true if either input value is true.

Exclusive Or

The Exclusive Or operation outputs the logical exclusive disjunction of two input values. The bitwise Exclusive Or operation will output a 1 for each bit position in which one input value bit is 1 and the other is 0. The logical Exclusive Or operation will evaluate the inputs as either true or false and output true if one input value is true and the other is false.

Copy

The Copy operation outputs the input value. While simple, this operation is one of the most useful database logic operations when building logical functions, in that it can be used to set database locations to constant values or the value of another database location. Additionally, the enable trigger allows the Copy operation to be used as a latch to preserve output values over multiple execution cycles.

Bit Copy

The Bit Copy operation outputs the value of a single bit from the input database location to a single bit in the output database location. No other bits in the output database location are modified by this operation.

Indirect Copy

The Indirect Copy operation outputs the value at the database location specified by the input source to the database location specified by the output destination. This operation can be used to access different database locations dynamically. It could also be used to create reusable database logic subroutines by selecting a different input and output location for the subroutine during each execution cycle.

Shift

The Shift operation performs a bitwise shift in the direction specified by the “Shift Direction” field. The Shift operation outputs the input value bit-shifted by the shift amount.

Compare

The Compare operation outputs a “1” if the comparison evaluates to true, otherwise it outputs a “0”. The available comparisons are Equal, Not Equal, Less Than, Greater Than, Less Than Or Equal, and Greater Than Or Equal. This operation is useful for controlling the execution of other database logic operations by setting the operation’s “Enable Trigger” database location to the database location of the Compare operation’s output.

Flag Test & Set

The Flag Test & Set operation tests if the bit flags specified in the input mask are set in the input value and sets the bit flags specified in the output mask in the output value. This operation can test for ALL flags set/cleared or ANY flags set/cleared. If the flag test evaluates as true, all bit flags specified in the output mask in the output value are set, otherwise the flags are cleared. Only the bits specified in the output mask in the output value are modified by this operation.

Value Change Detection

The Value Change Detection operation outputs a “1” if a change is detected in the input value between the last execution cycle and the current execution cycle, otherwise it outputs a “0”.

Multiplexer

The Multiplexer operation outputs one of its two inputs, depending on the selection. If Selection is zero, Input 1 is output. If Selection is non-zero, Input 2 is output.

Byte Reverse

The Byte Reverse operation reverses the byte order of the input value and outputs the result.

Add

The Add operation outputs the sum of the two inputs. It is evaluated as Input 1 + Input 2.

Subtract

The Subtraction operation outputs the difference of the two inputs. It is evaluated as Input 1 - Input 2.

Multiply

The Multiply operation outputs the product of the two inputs. It is evaluated as Input 1 × Input 2.

Divide

The Divide operation outputs the quotient of the two inputs. It is evaluated as Input 1 ÷ Input 2.

Modulo

The Modulo operation outputs the remainder of the division of Input 1 by Input 2.

Exponential

The Exponential operation outputs the power of the input raised by the exponent. It is evaluated as InputExponent. The input value may be a database value, constant value, or the value e.

Nth Root

The Nth Root operation outputs the root of degree n of the input. It is evaluated as Degree√Input.

Logarithm

The Logarithm operation outputs the exponent to which the base value must be raised to produce the input value. It is evaluated as logbase(Input). The base value may be a database value, constant value, or the value e.

Random

The Random operation outputs a random number between Input 1 and Input 2. Note that the operation is limited to producing only 32,768 unique values.

Sine

The Sine operation calculates the expression sin(Input 1), where Input1 is in radians.

Cosine

The Cosine operation calculates the expression cos(Input 1), where Input1 is in radians.

Tangent

The Tangent operation calculates the expression tan(Input 1), where Input1 is in radians.

Arc Sine

The Arc Sine operation calculates the expression sin-1(Input 1), where output is in radians.

Arc Cosine

The Arc Cosine operation calculates the expression cos-1(Input 1), where output is in radians.

Arc Tangent

The Arc Tangent operation calculates the expression tan-1(Input 1), where output is in radians.

Debounce Filter

The Debounce Filter outputs the input value only after it has remained constant for the amount of time defined by the “Stable Time” field. This filter is useful for removing undesired, rapid changes of a value.

Hysteresis Filter

The Hysteresis Filter outputs the input value only after it has remained within the tolerance range for the amount of time defined by the “Stable Time” field. When the input value changes, the new value is recorded and output after the stable time, as long as subsequent values during the stable time are within the range defined by the recorded value plus or minus the tolerance value. This filter is useful for removing undesired, rapid changes within a certain range of a value.

Now that we have our basic building blocks of database logic operations, we can combine multiple operations to create database logic functions. This section gives some examples of what can be done with the database logic. All examples are created using the ICC Configuration Studio software.

When creating any function, we first need to plan out what operations are required and how they combine to obtain the desired functionality. This can be accomplished by following a three-phase process.

Logic Diagram
The first phase is to draw a logic diagram using the symbols of the database logic operations that will combine to complete our function. This helps us plan out what operations we need to use and how to connect them together.

Sequenced Logic Diagram
The second phase is to arrange our logic diagram into a sequential order. Standard logic diagrams may have operations which occur simultaneously, or in parallel, because their inputs and outputs do not depend on one another. These are frequently drawn at the same step or level in a logic diagram. But because the database logic operations are executed one at a time, we must order each operation into one sequence of operations. The sequence derived in this step will be the sequence in which we add the database logic operations in our configuration using the ICC Configuration Studio.

Project File
The third phase is to implement our function by adding the database logic operations to the device configuration in the ICC Configuration Studio. During this phase, we assign database locations to each connecting line in our logic diagram. For connecting lines which connect an operation’s output to another operation’s input or enable trigger, we use the same database location for the first operation’s output as we do for the next operation’s input or enable trigger.

When communicating from an automation system, or similar, it may be required to know if communications is interrupted between the system and the ICC device. This example shows how to create a “heartbeat”, i.e. toggling a bit periodically, using database logic and mapping it to a BACnet object. This heartbeat object can then be monitored by the automation system to detect if the bit has stopped toggling, indicating that communications has been interrupted.

The communication heartbeat is implemented using the Not database logic operation. By assigning a single database location to both the input and output of the operation, the value at that location will toggle each time the database logic runs.

Logic Diagram
Logic Diagram

Sequenced Logic Diagram

Project File
XLTR-1000 Communication Heartbeat.icsproj

When working with parameters which are unsigned integers, as is the case when using the Modbus protocol, values may need to be scaled by both a multiplier and an offset. This can be accomplished by using database logic to create a "y=mx+b" style function.

Linear scaling can be implemented using a single Add database logic operation. Because every operation includes a multiplier on each input, the multiplier in the Add operation can be used as the linear scaling multiplier.

Logic Diagram
Linear Scaling

Sequenced Logic Diagram
Sequenced Logic Diagram

Project File
XLTR-1000 Linear Scaling.icsproj

When working with temperature sensing equipment, it may be necessary to convert between Fahrenheit and Celsius, or vice-versa. These examples show how to use database logic to perform the conversions. The conversions are implemented using a combination of Add, Subtract, Multiply, and Divide operations.

Celsius to Fahrenheit Logic Diagram
Celsius to Fahrenheit Logic Diagram

Celsius to Fahrenheit Sequenced Logic Diagram
Celsius to Fahrenheit Sequenced Logic Diagram

Fahrenheit to Celsius Logic Diagram
Fahrenheit to Celsius Logic Diagram

Fahrenheit to Celsius Sequenced Logic Diagram
Fahrenheit to Celsius Sequenced Logic Diagram

Project File
PicoPort Temperature Conversion.icsproj

When multiple devices are on a single control system, it may be desirable to command all devices at once. This example shows how to use database logic to enable commanding three devices simultaneously.

This is implemented by using three Copy operations and their enable triggers to copy a single command value to the command value for each device. The command all feature is enabled by writing to a single database location, so that the devices can either be commanded separately or simultaneously.

Logic Diagram
Logic Diagram

Sequenced Logic Diagram
Command Multiple Devices Sequenced

Project File
ETH-1000 Command Multiple Devices.icsproj

This example uses database logic to detect when a value is in a specific range. It is implemented by using an And operation to combine the output of two Compare operations.

Logic Diagram
Value Range Detection

Sequenced Logic Diagram
Value Range Detection Sequenced

Project File
ETH-1000 Value Range Detection.icsproj

This example shows how to make an incrementing value roll over to 0 after reaching 100. It is implemented using an Add operation with its output connected to its input to use as the counter. A Compare operation is used to detect when the value reaches 100 and triggers a Copy operation to reset the value of the counter to 0.

Logic Diagram
Rollover Counter

Sequenced Logic Diagram
Rollover Counter Sequenced

Project File
ETH-1000 Rollover Counter.icsproj

This example detects when a database value has changed and sets a flag. The flag must then be manually cleared to detect further changes.

The detection is implemented by using a compare operation to detect when the current value is not equal to the last value and a copy instruction to save the last value into the database so that the database logic can detect changes on the next cycle. An additional copy operation is used to set a flag byte in the database to indicate the value has changed.

Logic Diagram
Value Change Detection

Sequenced Logic Diagram
Value Change Detection Sequenced

Project File
XLTR-1000 Value Change Detection.icsproj

The PicoPort includes GPIO pins capable of being configured as analog inputs to sense voltage levels. These pins use a 10-bit analog to digital converter (ADC). The pin senses a voltage from 0 to VCC (3.3V) and converts the voltage into a value from 0 to 1023. Some sensors, however, vary resistance instead of voltage. The resistance of a sensor, or any other resistive element, can be measured by using a simple divider circuit and some database logic. This example shows how to measure the resistance of R2 when R1 is fixed, and how to measure R1 when R2 is fixed.

Using the voltage divider circuit below, we can use the following equations to calculate either R1 or R2.
Resistance Measurement Equations

The voltage divider circuit in this example uses a 1KΩ resistor, measured at 985Ω. Because the voltage across R1 is converted by the analog input to a value between 0 and 1023, we can simply use a value of 1023 for VCC, since a voltage value of VCC is converted to a value of 1023 by the ADC.
Resistance Measurement Circuit

R2 Measurement, R1 Fixed Logic Diagram
R2 Measurement, R1 Fixed Logic Diagram

R2 Measurement, R1 Fixed Sequenced Logic Diagram
R2 Measurement, R1 Fixed Sequenced Logic Diagram

R1 Measurement, R2 Fixed Logic Diagram
R1 Measurement, R2 Fixed Logic Diagram

R1 Measurement, R2 Fixed Sequenced Logic Diagram
R1 Measurement, R2 Fixed Sequenced Logic Diagram

Project File
PicoPort Resistance Measurement.icsproj

This example uses database logic to calculate the absolute value of a signed integer. It is implemented using two Compare operations to detect whether the value is positive or negative and two Copy operations to copy either the positive or negative value.

Logic Diagram
Absolute Value

Sequenced Logic Diagram
Absolute Value Sequenced

Project File
PicoPort Absolute Value.icsproj

This example shows how to place high and low limits on a value using database logic. The logic detects if a value is outside of a certain range, in this example between 20 and 80, and if it is, overwrites the value with the high or low limit.

This is implemented by using two Compare operations which each trigger a Copy operation.

Logic Diagram
Setpoint Range Limit

Sequenced Logic Diagram
Setpoint Range Limit Sequenced

Project File
ETH-1000 Setpoint Range Limit.icsproj

This example uses database logic to store the last two values of a parameter in the database. When the parameter value changes, the old values are copied into different database locations. This example builds on the Value Change Detection example.

The value history is implemented by comparing the last value of a database location with its current value. Copy operations are enabled by a Compare operation to form a value queue in the database. Each time the current value is different from the last value, the values in the queue are shifted. This example could be expanded to create any length queue, and therefore any number of previous values.

Logic Diagram
Value History

Sequenced Logic Diagram
Value History Sequenced

Project File
XLTR-1000 Value History.icsproj

This example shows how to use database logic to keep two values synchronized so that when one value changes, the other changes as well. This could be used to keep two device synchronized with each other, or in conjunction with other database logic, such as scaling functions, where the scaled value must be both readable and writable. This example builds upon the Value Change Detection example.

The value synchronization is implemented by using two Compare operations, one on each of the two values to be synchronized, along with two Copy operations to detect if either of the values has changed. Two additional Copy operations are used, one for each value, to copy the value of the changed database location to the other.

Logic Diagram
Value Synchronization

Sequenced Logic Diagram
Value Synchronization Sequenced

Project File
ETH-1000 Value Synchronization.icsproj

In certain automation applications, it may be desired to increment a parameter up to a certain value, then decrement the parameter back down, repeating this sequence. This is known as a ramp function and can be implemented using database logic.

This specific example is to control a DMX-512 light so that the color macro parameter ramps up and down, changing the color of the light. The ramp function is implemented by using an Add and Subtract operation to increment or decrement the value, two Compare operations to detect when the value is at the lower or upper limit, and four Copy operations to enable/disable the increment or decrement of the value. This example also uses a Copy operation to set the mode of the DMX light to a fixed value, which puts it into the color macro mode. This Copy operation is not shown in the logic diagrams.

Logic Diagram
Ramp Function

Sequenced Logic Diagram
Ramp Function Sequenced

Project File
XLTR-1000 Ramp Function.icsproj

This example shows a common application for using the Hysteresis Filter database logic operation. In an application where a HVAC system is to be controlled based on the indoor temperature and a temperature setpoint, it is not desirable for the system to turn on the heating or cooling for every small change in temperature which deviates from the setpoint. The Hysteresis Filter operation can be used to ignore these small changes. This specific example uses a tolerance of 2 degrees, so that temperature changes 2 degrees and smaller either above or below the setpoint do not trigger the system to turn on the heating or cooling. Once the temperature increases or decreases more than 2 degrees, the database logic turns the heating or cooling command on until the temperature reaches the setpoint.

This is implemented by using a Hysteresis Filter operation on the current temperature reading to produce a filtered stable temperature. The heating and cooling commands are controlled by two Compare operations to check if the stable temperature is either above or below the setpoint. Two Copy operations are used to control the value tolerance of the Hysteresis Filter so that when the current temperature equals the setpoint, the tolerance is set to 0. This is necessary so that the Hysteresis Filter is always reset properly when the current temperature reaches the setpoint, whether that occurs by the temperature changing or the setpoint being changed.

Logic Diagram
Heating Cooling Control

Sequenced Logic Diagram
Heating Cooling Control Sequenced

Project File
XLTR-1000 Heating Cooling Control.icsproj

During commissioning of a system, it is often convenient to inject test values into the system to analyze how the system responds, and then, restore the system back to its original operating state when finished. This can be done by overriding a point and then releasing it. This example shows how to accomplish this functionality using database logic.

There are three different states that must be handled to achieve the override/release functionality: Override Disabled, Override Enabled, and the transition from Override Enabled back to Override Disabled which we will call Override Released.

When the override is disabled, the system is functioning normally. The only thing we must do is backup the value of the setpoint into a location in the database. This is done by using a Copy operation which is triggered when the override is disabled, which we detect by using a Not operation on the override enable value. This ensures that we have the original value of the setpoint before any override has occurred so that we can restore it later.

When the override is enabled, we must overwrite the setpoint with the override value. This is done by using a Copy instruction which is triggered when the override is enabled.

When the override is released, we must restore the original setpoint value. This state requires us to detect the transition of the override enable database location from enabled to disabled. This is done using the concepts presented in the Value Change Detection example. The release is detected using a Compare operation between the override enable value and the last override enable value. A Copy operation is then enabled by the detection result which copies the original setpoint (which is the backup copy we made while the override was disabled in a previous execution cycle) back to the setpoint’s database location.

Logic Diagram
Override Release Setpoint

Sequenced Logic Diagram
Override Release Setpoint Sequenced

Project File
ETH-1000 Override Release Setpoint.icsproj

It may be useful to perform an action periodically, say once every minute, or once every hour, or even at a certain time each day. To do this, some sort of time reference must be used. This example shows how to create a 24-hour clock using database logic.

The basic idea behind implementing the clock is taken from the Rollover Counter example. In this case, however, we simply have multiple rollover counters which trigger the next counter to increment when they roll over. The clock implemented in this example counts hours, minutes, seconds, and milliseconds, each having its own rollover counter and triggering the next higher unit to increment each time it rolls over. The value of the “Scan Rate” field for the database logic is important in this example, since it defines what value our millisecond counter will increment by. In this example, the scan rate is set to 50ms.

Logic Diagram
Clock

Sequenced Logic Diagram
Clock Sequenced

Project File
PicoPort Clock.icsproj

As described in the Resistance Measurement example, the PicoPort can be configured to measure the resistance of a sensor or other resistive element by using a voltage divider and database logic. In some sensors, such as a thermistor, the resistance measured is related to a real-world parameter, temperature in this case. Unfortunately, this relationship is not linear and some calculation must be made to accurately convert the resistance of a thermistor to a temperature. This example shows how to calculate a precise temperature using the resistance measurement of a thermistor by implementing the Steinhart-Hart equation using database logic. The resistance measurement in this example is of R2 when R1 is fixed, as in the Resistance Measurement example.

The Steinhart-Hart equation is as follows:
Steinhart-Hart Mathematical Equation
Where T is the temperature in kelvins, R is the resistance of the thermistor, and A, B, and C are the Steinhart-Hart coefficients. The coefficients used in this example are 1.40 x 10-3, 2.37 x 10-4, and 9.90 x 10-8, respectively.

In the example, the final calculated temperature is converted into Celsius. Note that the database logic operations used in this example make extensive use of the input and output multiplier scaling so that precision is kept when storing intermediate results into the database.

Logic Diagram
Steinhart-Hart Equation

Sequenced Logic Diagram
Steinhart-Hart Equation Sequenced

Project File
PicoPort Steinhart-Hart Equation.icsproj

Certain protocols, such as DeviceNet, define a native byte ordering for the protocol. Sometimes it is necessary to reverse the byte ordering on individual values in order to comply with the native byte ordering of a protocol. This is especially true when string data is stored in the values. A single value’s byte order can be reversed using the Byte Reverse operation.

Usually, however, there is not just a single value you will need to apply the Byte Reverse operation to, but multiple values. This is accomplished by creating a subroutine. A subroutine reuses a single operation, or combination of operations, by passing different input values into the operation and copying the output value to another location during each execution cycle.

This is implemented by using two Indirect Copy operations. The first Indirect Copy is configured to have a variable input and fixed output. This is used to copy the input value from different database locations to the Byte Reverse operation. The second Indirect Copy is configured to have a fixed input and variable output. This is used to copy the output of the Byte Reverse operation to different database locations.

Subroutines will typically use one or more Add operations to calculate the database addresses used for the inputs and outputs of the subroutine. Compare and Copy operations are also commonly used to reset the database addresses back to the first input and output location.

In this example, word-sized (2 byte) values stored at database addresses 0 to 14 are byte swapped and stored into database addresses 16 to 30 using a single Byte Reverse operation subroutine. During each cycle, an input value is copied to the input of the Byte Reverse operation and an output value is copied from the output of the operation. The additional Add, Compare, and Copy operations are used to calculate the input and output database addresses for the next execution cycle. In this example, the output address is always the input address offset by 16.

Logic Diagram
Byte Reverse Subroutine

Sequenced Logic Diagram
Byte Reverse Subroutine Sequenced

Project File
DNET-1000 Byte Reverse Subroutine.icsproj