Center

Realtime Tables

This page contains information on how to create and update realtime tables using AMI SQL.

Overview

The Center has 2 types of tables:

1. Realtime tables (which can be in-memory or on-disk)
2. Historical tables

Realtime tables are SQL compliant tables that can be stored in-memory or on-disk. They are optimized for fast read and write speed.

Commands

There are 5 commands for creating and modifying realtime table schemas in the Center:

• CREATE PUBLIC TABLE
• CREATE PUBLIC TABLE AS
• ALTER PUBLIC TABLE
• RENAME PUBLIC TABLE
• DROP PUBLIC TABLE

Each command's section provides information on its syntax, usage, and other case-specific behaviors.

Note

• Parameters in square brackets ([]) are optional
• Pipe-delimited (|) values indicate valid options for a given key

CREATE PUBLIC TABLE

A new public table must have:

• A unique name across existing public tables (however a temporary and public table can share the same name)
• At least one specified column
• Uniquely named columns

Warning

Attempting to create a public table with an existing name will cause the command to fail, as will updating/inserting column values with null if they have the NONULL attribute

Syntax

To create a new, empty (no rows) public table, use the following command:

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname (col1 column_type [, col2 column_type ...])
[USE  [PersistEngine="[FAST|TEXT|HISTORICAL|custom]"]]
[PersistOptions="custom_options"]
[Broadcast="true|false"]
[RefreshPeriodMs="duration_millis"]
[OnUndefColumn="REJECT|IGNORE|ADD"]
[InitialCapacity="number_of_rows_min_1"]

• tblname: Name of the table
• colN: Name of Nth column
• column_type: Accepted data type for that column

Example

CREATE PUBLIC TABLE countries (country String NoNull, language String, GDP Double) USE PersistEngine="TEXT" Broadcast="false" InitialCapacity="100"

USE Options

The following USE options can also be supplied:

• PersistEngine

• Broadcast

• RefreshPeriodMs

• OnUndefColumn

• InitialCapacity

Persist EngineBroadcastRefresh PeriodUndefined ColumnInitial Capacity

The Persist Engine is responsible for persisting Center data onto disk so it can be re-accessed. For more information on using the Persist Engine, see this section.

The persist engine can take one of the following options:

"FAST""TEXT""HISTORICAL""Custom"

USE PersistEngine="FAST" 

The table will be persisted to disk using a fast, binary protocol.

Additional Options:

• persist_dir = "/path/to/directory/for/storing/file"

  • The directory where the .fast binary file will be saved/loaded from

• persist_compact_percent = ".95"

  • A number between 0 and 1 which says how much of the file should contain updates & deletes before it's compacted
  • E.g: a value of .95 indicates that if the file becomes 95% updates and deletes, then the file should be rebuilt
  • The FAST table uses a journaling strategy, updates and deletes will get appended and can create large files

• persist_encrypter="default"

  • For encrypting persisted data

  Note

  We currently only support 3forge's default encrypter which is an AES encrypter. If you require a custom encrypter, please reach out to us at support@3forge.com.

USE PersistEngine="TEXT" 

The table will be persisted to disk as a slower but human-readable text file. 3forge backs up the entire text file after every change (except insertion) making write speed slower compared to FAST.

Additional Options:

• persist_dir="/path/to/directory/for/storing/file"

  • The directory where the .txt text file will be saved/loaded from

• persist_encrypter="default"

  • For encrypting persisted data

  Note

  We currently only support 3forge's default encrypter which is an AES encrypter. If you require a custom encrypter, please reach out to us at support@3forge.com.

USE PersistEngine="HISTORICAL"

The table will be persisted as a historical table using a disk-based historical engine. Optimized for storing large volumes of data and querying on demand.

Additional Options:

• None

USE PersistEngine="Custom"

A custom user-designed Persist Engine will be used (see this page for more information).

Additional Options:

• PersistOptions="..."
  • Used to pass options to the custom plugin

Whether to notify front end visualizations and external listeners when data in the table is updated.

USE Broadcast="true" 

The period that the table will conflate and broadcast changes to the front end in milliseconds.

If a cell's value changes more frequently than the refresh period, those changes will be conflated.

USE RefreshPeriodMs="500"

Behavior when a realtime Object record contains an undefined column. Valid options are:

"REJECT""IGNORE""ADD"

USE onUndefColumn="REJECT"

The record will be rejected. This is the default.

USE onUndefColumn="IGNORE"

The record will be inserted, but the undefined values will be ignored.

USE onUndefColumn="ADD"

The table will automatically have the column added.

The number of rows to allocate memory for when the table is created. The default initial capacity is 1,000 rows and there must be at least 1.

USE InitialCapacity="1000"

Persist Engine

Creating tables in the Center saves the schema (its definition) but not the table's data by default. To keep data after a session is closed, the data needs to be "persisted" into the Center.

The Persist Engine is responsible for writing table data to disk. The data can then be accessed again on restart.

Persisted data can either be:

• System tables (__CENTER.dat, __DATASOURCE.dat,__REPLICATION.dat)
• User-created public tables (file type is dependent on which Persist Engine type is used)

Note

System tables are updated during Center Replication. See this page for more information.

There are 4 protocols:

1. FAST
   • Stored as binary .fast files
   • Efficient storage for rapidly changing/updating data
2. TEXT
   • Stored as plaintext .dat files
   • Ideal for small or static tables as any new changes will trigger a new back up resulting in slower write speeds (compared to FAST)
   • System tables for replication are stored as .dat files
3. HISTORICAL
   • Designed for storing large volumes of data, optimized for fast retrieval
   • Useful for archiving data
   • See this page for more information
4. Custom
   • A custom user-written protocol for persisting data
   • Please see this page for implementation details

Persist File Location

By default, persisted tables are saved to amione/persist. You can change this directory with the following properties in your local.properties file:

# For user-created tables
ami.db.persist.dir=path/to/dir

# For system tables
ami.db.persist.dir.system.tables=path/to/dir 

# To change the location of individual system tables
ami.db.persist.dir.system.table.__REPLICATION=path/to/dir 
ami.db.persist.dir.system.table.__CENTER=path/to/dir

Note

Historical tables are stored in a separate Historical Database (amione/hdb) directory. More information can be found on the Historical Tables page.

CREATE PUBLIC TABLE AS

Use this command to create a new, uniquely named public table whose schema definition is the result set of an AmiScript query clause or USE...EXECUTE clause.

All rows from the query's result set will be inserted into the table.

Syntax

To create new public tables from a query:

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 column_type [, col2 column_type ...])] [USE ... ] AS select_clause

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 column_type [, col2 column_type ...])] [USE ... ] AS analyze_clause

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 column_type [, col2 column_type ...])] [USE ... ] AS prepare_clause

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [USE ... ]  [, tblname [USE ... ] ...] AS execute_clause

• tblname: Name of the table to create.

Example

CREATE PUBLIC TABLE names USE PersistEngine="FAST" AS SELECT first_name, last_name FROM employees

ALTER PUBLIC TABLE

This command alters the schema (column names and types) of an existing table.

There are 4 types of ALTER commands:

1. ADD
   • Add new columns
2. RENAME ... TO
   • Rename columns
3. DROP
   • Delete columns
4. MODIFY
   • Change column data type

Syntax

To alter the schema of a table, use the following command:

ALTER PUBLIC TABLE tblname alter_public_col_expr [, alter_public_col_expr ...]

• tblname: Name of the table to alter

• alter_public_col_expr: The alter command's expression

  ADDRENAME ... TODROPMODIFY

  ADD new_name column_type [BEFORE before_col]
  

  • new_name: Name of new column
  • column_type: Data type of new column
  • before_col: Name of an existing column where the new column will go before

  Note

  If no BEFORE expression is specified, new columns will be added to the end of the table. New cells will default to null.

  RENAME col_name TO new_name
  

  • col_name: Name of existing column to change
  • new_name: New name of column

  DROP col_name 
  

  • col_name: Name of the column to delete

  MODIFY col_name AS new_name column_type
  

  • col_name: Name of the existing column to modify
  • new_name: New name of the column (can be the same)
  • column_type: New data type of the column (a best effort cast will be applied)

If multiple ALTER expressions are supplied, they are evaluated from left-to-right

Example

ALTER PUBLIC TABLE AccountHoldings ADD UserId long, RENAME AccountId TO Account_Id, DROP UserInfo

ALTER PUBLIC TABLE AccountHoldings MODIFY UserId AS UserId INT

ALTER PUBLIC TABLE AccountHoldings RENAME UserId TO UserIds, ADD UserId STRING AS

RENAME PUBLIC TABLE

This command renames an existing table.

Warning

If another table already exists with the new name, the existing table will be implicitly dropped and replaced with the renamed table.

Syntax

RENAME PUBLIC TABLE tblname TO new_name

• tblname: Name of the table to rename.

• new_name: New name for the table.

Example

RENAME PUBLIC TABLE AccountHoldings TO Accounts

DROP PUBLIC TABLE

This command drops an existing table, along with its schema and all rows. Multiple tables can be specified in a comma-delimited list.

Syntax

DROP PUBLIC TABLE [IF EXISTS] tblname [, tbl2name ...]

• tblname: Name of table to delete

Example

DROP PUBLIC TABLE Accounts

Supported Data Types

Public tables support a large variety of data types for column values:

1. Numeric Types

   BYTE,SHORT,INT,LONG,DOUBLE,FLOAT,UTC,UTCN,DATE

2. Other Reference Types

   BOOLEAN,CHAR,STRING,ENUM,BINARY,BIGDECIMAL,BIGINTEGER,COMPLEX,UUID

Note

Types are case insensitive. Both STRING and String are valid syntax.

A full description of data types including storage size is listed in the tables below.

Optional arguments are denoted by square brackets ([]). The global attributes are:

1. NONULL

   • If included, the column will not accept null values
   • Inserts/updates of null will fail

2. NOBROADCAST

   • If included, columns with broadcast disabled will not be included in updates sent to front end visualizations & external listeners
   • This is a more granular version of the Broadcast option at the table level

Numeric Types

Type	Description	Example
BYTE [NONULL] [NOBROADCAST]	8-bit signed number (see java.lang.Byte for details on range) If NONULL is omitted, then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	age Byte NONULL
SHORT [NONULL] [NOBROADCAST]	16-bit signed number (see java.lang.Short for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	temperature SHORT NOBROADCAST
INT [NONULL] [NOBROADCAST]	32-bit signed number (see java.lang.Integer for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	population int
LONG [NONULL] [NOBROADCAST]	64-bit signed number (see java.lang.Long for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	id Long NONULL NOBROADCAST
DOUBLE [NONULL] [NOBROADCAST]	64-bit signed float (see java.lang.Double for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	tot_price double
FLOAT [NONULL] [NOBROADCAST]	32-bit signed float (see java.lang.Float for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	price float nonull
UTC [NONULL] [NOBROADCAST]	64-bit unix epoch timestamp in milliseconds If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	ts_millis UTC nobroadcast
UTCN [NONULL] [NOBROADCAST]	64-bit unix epoch timestamp in nanoseconds If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	transaction_time UTCN
DATE [NONULL] [NOBROADCAST]	128-bit integer representing days since Unix epoch (1970-01-01) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	report_date DATE

Other Reference Types

Type	Description	Example
BOOLEAN [NONULL] [NOBROADCAST]	1-bit signed Boolean (see java.lang.Boolean for details on range)	isactive boolean NONULL
CHAR [NONULL] [NOBROADCAST]	16-bit unicode character (see java.lang.Character for details on range) If NONULL is omitted then an extra 1-bit per row is used (precisely: 64-bits per each 64 rows)	dept_id Char
STRING [NONULL] [NOBROADCAST] [COMPACT] [COMPACT ASCII] [BITMAP] [ONDISK [CACHE="nnnMB"]]	Variable length string Each cell is backed by a java.lang.String instance See this section for more information on the different String directives and how they impact performance	ticker STRING
ENUM [NONULL] [NOBROADCAST]	32-bit index into map of global Strings (map is shared across all public tables) Multiple cells containing the same value will share an address, even if those cells are on different ENUM columns Legacy data type for low cardinality data, but we recommend using STRING BITMAP instead	buy_or_sell ENUM NONULL
BINARY [NONULL] [NOBROADCAST] [ONDISK [CACHE="nnnMB"]]	Variable length byte array, useful for storing images and other media The ONDISK directive uses a 64-bit pointer that is kept in memory which resolves to a Block on disk (random-access file), meaning that binaries of any length will take up the same 64-bits in memory If using ONDISK, you can optionally supply the CACHE directive which specifies the maximum RAM to use for caching the most recently used values Supported units are: KB, MB, GB, and TB (if no unit is specified, defaults to Bytes) Only the most recent values read/wrote are cached in memory Cache-misses will result in reading from disk which can slow down query performance	filedata BINARY NOBROADCAST To cache \~100 MB: cached_file BINARY ONDISK CACHE="100000000"
BIGDECIMAL [NONULL] [NOBROADCAST]	Unbounded (variable length) signed decimal (see java.math.BigDecimal for details on range)	tax_rate BIGDECIMAL
BIGINTEGER [NONULL] [NOBROADCAST]	Unbounded (variable length) signed number (see java.lang.BigInteger for details on range)	mod_key BigInteger
COMPLEX [NONULL] [NOBROADCAST]	A 128-bit Complex number, composed of 64-bit signed real and 64-bit signed imaginary parts	complement COMPLEX NONULL NOBROADCAST
UUID [NONULL] [NOBROADCAST]	A 128-bit Universally Unique Identifier	uid UUID nobroadcast

String Directives

Strings have additional optional directives for performance optimization.

Directive	Storage	Example
ONDISK [CACHE="nnnMB"]	A 64-bit pointer is kept in memory which resolves to a Block on disk (random-access file), meaning that strings of any length will take up the same 64 bits in memory The optional CACHE directive specifies the maximum RAM to use for caching the most recently used values Supported units are: KB, MB, GB, and TB (if no unit is specified, defaults to Bytes) Only the most recent values read/wrote are cached in memory Cache-misses will result in reading from disk which can slow down query performance	To cache 2 gigabytes: cached_string_data STRING ONDISK CACHE="2GB"
COMPACT	4-byte/32-bit index into a continuous char array for all values in the column where each cell has a unique address 15 bits are used to store string length Values are limited to 32,766 characters in length Max total chars per column (including 2-byte EOL) is 2^31	name STRING COMPACT
COMPACT ASCII	4-byte/32-bit index into a continuous byte array for all values in the column where each cell has a unique address 7 bits are used to store string length Extended ascii not supported Values are limited to 126 characters in length Max total chars per column (including 1-byte EOL) is 2^31	firstname STRING COMPACT ASCII
BITMAP	8-bit, 16-bit, or 32-bit address into an underlying map of Strings (local to the column) The addressing depends on the number of unique values encountered in the column: 0 - 255 unique entries: 8-bit 256 - 65,535 unique entries: 16-bit Everything else: 32-bit addressing is used As new unique values are added, the addressing will automatically escalate as needed Multiple cells containing the same value will share an address Note that entries from the underlying map are never removed, even as cells are updated/deleted	permissions string bitmap

Determining which type of String is best will be case-dependent and ultimately up to developer discretion. We generally recommend the following set of guidelines to help:

• For data that never repeats and is queried often, STRING is the most flexible and preferred choice
• For columns with data that does not repeat but is not queried often, STRING COMPACT (or STRING COMPACT ASCII for non-extended ascii data) offers a lower memory footprint
• For repeating data, generally STRING BITMAP is best

Below is a table outlining the advantages/disadvantages of each.

String Type Comparison

Type	Size	Advantage	Disadvantage	Usage Scenario
STRING	Variable	Fastest retrieval	High memory usage Internally, each entry is stored as a Java object	For columns that have very high cardinality and accessed often, or participate in indexes If the amount of total column data (summation of the cell lengths in the column) exceeds 2 billion chars
STRING COMPACT	32 bits	Low memory/object usage A single large continuous array	Slower retrieval As cell values are used, a temporary String object may be constructed Frequent deletes or updates that change the cell content lengths may result in array-data compaction	For columns that have high cardinality and whose cells are greater than 126 chars or have extended ascii, but not accessed frequently (e.g, part of a "where" clause) For example: comment columns
STRING COMPACT ASCII	32 bits	Half the memory of a STRING COMPACT	Slower retrieval As cell values are used, a temporary String object may be constructed Frequent deletes or updates that change the cell content lengths may result in array-data compaction	Same as STRING COMPACT, except limited to shorter strings that are basic ascii (not extended)
STRING BITMAP	8, 16, or 32 bits depending on number of unique values	Most compact, especially with fewer unique values Faster retrieval than STRING COMPACT type columns	Only used for repeating data (lower cardinality) Slightly slower retrieval than standard STRING columns	Any scenario where columns have lower cardinality and fewer than approximately 32,000 unique values
ENUM	32-bit address	Compact	Must be used only on columns with low cardinality	When several columns all use a similar set of values For example: BUY, SELL, SHORT Then all columns can be set to use ENUM Note: this is mostly for legacy support and STRING BITMAP is preferred
STRING ONDISK	64-bit address	Fixed memory size, regardless of String length	Slower read times for un-cached Strings	For large text documents or those that do not need to be searched on

Temporary Tables and Collection Types

Data types like List, Set, and Map are not valid column types for public tables but can be used in temporary tables. This is because the data cannot be persisted to disk.

We generally discourage the use of temporary tables, however if you are using them please be aware of the following:

• Temporary tables are not persisted into the Center schema
• Temporary tables are session specific to both the user's Web and Center session
• Temporary tables are dropped when the session is closed

Reserved Columns

3forge has a number of reserved columns listed in the table below. If any of these columns are declared, they will be automatically populated by the 3forge engine.

Columns A, D, T, and W will be populated even if they are not declared.

Warning

When declaring reserved columns, the column's data type must match the data type specified below, otherwise table creation will fail.

All times are since Unix epoch in milliseconds.

Name	Type	Label	Mutable	Description
A	String	AMI-Center		Stores the name of the AMI Center
C	Long	Created Time		Time that the record was inserted into the table
D	Long	AMI-ID		A unique auto-generated incrementing id for the row. Unique across all tables.
E	Long	Expires Time	INSERT, UPDATE	Time when the row should automatically be deleted (expires). Negative Number: Offset from now, e.g: -4000: Remove row four seconds from now 0 or Null: Don't automatically delete Positive number: Time since epoch to. E.g: 2510712000000L: Remove row on 07/24/2049
I	Object	UniqueID	INSERT	The Id (I) value supplied from the real-time streaming on the Object (O) message. See this page for more information.
M	Long	Modified Time		Most recent time that the record was updated (initially the time the row was inserted).
P	String	Application	INSERT	The Login Id (I) value supplied from the realtime messaging API on login (L) message.
V	Int	Revision		The number of times the record has been updated (initially zero).
T	String	Table Name		The name of the table. All cells will contain the same value (this is for backwards compatibility).
W	Long	Current Time		Stores the current Unix timestamp.

AMI-ID (I) Column

The I column (and P by extension) behavior can be used to support upserting. When defined, 3forge treats the I column as a pseudo-primary key.

Note

If both I and P are defined, 3forge uses the combination as a pseudo-primary key instead.

Attempting to insert an Object into a table with an existing non-null I value will update the existing row instead of inserting a new one.

Note

This is different from standard primary keys as two rows can exist in a table with the same key if the key is null

To use standard primary keys, please see this page.

Capacity Planning

Overview

Below is the formula to use for calculating the amount of memory to expect public tables to consume, based on the types of data being stored. For total capacity planning of 3forge in memory database, one should run this on each table and aggregate the results.

table_size_mb = row_cnt * (128 + data_size + idx_cnt * 100 + null_col_cnt * 1/8 ) / 1,048,576

table_suggested_capacity_mb= total_size_mb * 2

Where each variable is:

Variable	Definition
table_size_mb	The total number meg you can expect the table to require
table_suggested_capacity_mb	3forge Recommendation for hardware capacity planning
row_cnt	Number of rows
idx_cnt	Number of indexes on the table, composite indexes are larger
null_col_cnt	Number of non-string columns that allow null
data_size	Sum(column_type_sizes), see column types below

Where data size is defined below:

Column Type	Data Size (bytes)
UTC,UTCN,Double,Long	8
Int,Float	4
Char,Short	2
Byte	1
Boolean	1/8
Enum (legacy)	4
String BITMAP1	1
String BITMAP2	2
String BITMAP3	3
String COMPACT	String Size * 2 + 6 bytes
String COMPACT ASCII	String Size * 1 + 5 bytes
String	String Size * 2 + 64 bytes
Binary	Length in Bytes + 56 bytes
String ONDISK	8
Binary ONDISK	8
BIGDECIMAL	68+size of number in bytes
BIGINTEGER	64+size of number in bytes
COMPLEX	128
UUID	128

Examples

Example 1: Small Table

100,000,000 Executions = ~25 GB RAM USED = Suggested ~50GB of RAM be reserved.

Below, we walk through the math given the below table, assuming 10 million executions and 1 index on symbol:

Below is the actual experienced capacity for 100,000,000 executions being sent into an AMI instance:

Example 2: Large Table

Test of 20,000,000,000 (20 billion) cells using 33 columns x 607,000,000 rows = ~171 GB RAM Used = Suggested ~ 342 GB RAM be reserved.

List of System Tables

__COLUMN

Column information for tables in the Center.

Variables	Description
TableName	Name of all the tables
ColumnName	Name of columns within each table
ColumnFormula	Formula associated with column
DataType	Data type of column (String, Enum, Integer, etc.)
NoNull	If column is Nullable
Position	Position of column within the associated table
DefinedBy	Which user defined (USER, AMI, SYSTEM)

__COMMAND

Command definition and information for user-defined commands for realtime messaging.

Variables	Description
CI	Connection ID of the connection that this command was registered using
RI	Relay ID that this command was registered using
ID	Command ID of the command, typically the purpose of the command (e.g: "cancel_order"), not guaranteed unique
AR	Input arguments for form fields. ({"form":{"inputs":[{"label":"Symbol","required":true, "var":"symbol","type":"text"}]}})
PR	Priority for display in the menu. Commands with a higher priority are listed in the context menu above those with lower priority (0 = highest priority, 1 = 2nd highest, etc.)
NA	Name of the command that appears on the GUI after the user right-clicks on a row in AMI (e.g: "order.cancel")
FL	Command filter. An expression that will determine which rows the command will be available at on a panel level (e.g: panel.types=="Orders" or panel.types=="Executions")
WH	Conditional "where" clause. It determines which rows the command will be available at a row/node level (e.g: qty>0 && user.canbust=="true")
HP	Explanation/outcome of the command. (E.g: "This command is used to cancel order(s)")
SM	Constrains the number of rows that can be selected when running the command (1-10)
AmiScript	AmiScript will reflect here, if any
N	Enabled where (expression). (E="Quantity==300"; the command E will only be enabled where the Quantity = 300)
L	Command level. Permissions Level of 0 means remove command, and any other number is used for entitlements as part of the AMI entitlement engine
F	Fields; returns the values of specified fields (e.g: price, status, level)
I	Relay name with command name (e.g: 29002751:CancelCmd)
P	Login name used to access the realtime database (e.g: "demo")
V	Number of times a row is being updated
M	Command modified time in milliseconds
C	Command created time in milliseconds

__CONNECTION

Information on connections to the Center.

Variables	Description
AI	Login ID associated with this connection, not guaranteed unique
EC	Connection/login attempt error count
CI	Connection ID of logged in user
MA	ID remote machine of logged in user
MC	Message count from current connection
O	Used to supply options about the current session’s connection. The following options are available: QUIET - 3forge will not send any information back to the client (statuses, etc). Execute commands (E) will still be sent to the client LOG - Force 3forge Relay to log data to/from this session (default file = AmiSession.log) UNIX - Force 3forge to not send \r on messages WINDOWS - Force 3forge to send \r on messages TTY - teletype terminal (for UNIX) is for interactively working with 3forge backend
PL	A fully qualified Java class name to a 3forge Relay plugin. The class must implement the com.vortex.agent.AmiPlugin interface
RI	The Relay which was used to run on (e.g: relay_0)
RP	Remote host port used
RH	Remote host name, localhost, IP address
CT	Time of connection made

__DATASOURCE

Information on datasources available to this Center.

Variables	Description
NM	Datasource name, self-specified
AD	Adapter type (RestAPI, MySQL)
UR	Datasource URL (protocol://[host]/[database][?properties], e.g: https://example.com/api/data)
US	Username, credentials to access datasource URL
PW	Unencrypted password, credentials to access datasource URL (Deprecated)
Password	Encrypted password, credentials to access datasource URL
OP	Additional options For servers requiring keyboard interactive authentication: authMode=keyboardInteractive To use a public/private key for authentication: publicKeyFile=/path/to/key/file (Note, this is often /<your_home_dir>/.ssh/id_rsa) To request a dumb pty connection: useDumbPty=true
RelayId	Relay to run on option in dropdown (e.g: relay_0)
PermittedOverrides	Combination of URL, USERNAME, PASSWORD, OPTIONS, RELAY

__DATASOURCE_TYPE

Information on the list of supplied datasource adapters for this Center.

Variables	Description
Description	Description of associated datasource (Shell Command, AMIDB, MySQL JDBC)
I	Identifier of datasource type (SHELL, AMIDB, MYSQL)
ClassType	Class of plugin associated with datasource (e.g: com.f1.ami.center.ds.AmiShellDatasourcePlugin, com.f1.ami.center.ds.AmiAmiDbDatasourcePlugin, com.f1.ami.plugins.mysql.AmiMysqlDatasourcePlugin, etc)
Icon	Icon (svg file) associated with the datasource
Properties	Map of properties associated with the datasource

__INDEX

Information on column indexes across tables in the Center.

Variables	Description
IndexName	Name of index (e.g: pk, relayId, etc)
TableName	Table associated with the index
ColumnName	Column of table associated with the index
IndexType	Type of indexing (SORT, HASH)
IndexPosition	Position of column in table associated with the index
Constraint	Constraints of index (PRIMARY, NONE)
DefinedBy	Which user defined (USER, AMI, SYSTEM)

__PLUGIN

Information on all the plugins including datasource adapters available to the Center.

Variables	Description
PluginName	Name of plugin (MYSQL, ORACLE, SSH, MSEXCEL)
PluginType	Plugin Type (DATASOURCE, PROCEDURE, TIMER, TRIGGER)
ClassType	Class of plugin (com.f1.ami.center.ds.AmiShellDatasourcePlugin, com.f1.ami.center.ds.AmiAmiDbDatasourcePlugin, com.f1.ami.plugins.mysql.AmiMysqlDatasourcePlugin, etc)
Arguments	Arguments/Parameters used in conjunction with plugin

__PROCEDURE

Information on procedures stored in the Center.

Variables	Description
ProcedureName	Name of procedure (e.g: __ADD_DATASOURCE, __REMOVE_DATASOURCE, __RESET_TIMER_STATS, etc)
ProcedureType	Type of procedure (e.g: AMISCRIPT, __SYSTEM)
ReturnType	Return type after running procedure (e.g: Object, String, Integer, etc)
Arguments	Arguments/Parameters used in conjunction with procedure
Options	Additional procedure-specific options (scripts, arguments, etc)
DefinedBy	Which user defined the procedure (USER, AMI, SYSTEM)

__PROPERTY

Information on configured properties found in local.properties that use the prefix ami.property..

E.g: ami.property.someprop=123 corresponds to the record PropertyName="someprop", PropertyValue="123"`.

Variables	Description
PropertyName	Name of property
PropertyValue	Value of property

__RELAY

Information on relays that this Center is connected to.

Variables	Description
MachineUid	Unique Machine ID of the host (e.g: UID_DESKTOP-RVO4948)
ProcessUid	System Generated Process Id (e.g: F1-Inw6XE0p8YaqdbREAp5MxN)
StartTime	Starting time in UTC (e.g: 2022-01-20 03:03:06.101 EST)
ServerPort	Connection port (e.g: 3289)
RelayId	Relay ID (e.g relay_0)
Hostname	Host name (e.g: DESKTOP-RVO4948)
ConnectTime	Connection time in UTC (e.g: 2022-01-20 03:03:28.299 EST)

__RESOURCE

Information on resources available to the Center (by default amione/resources).

Variables	Description
I	Name of file
ModifiedOn	Modified time in milliseconds since Unix epoch
Checksum	Unique checksum value used for validation
FileSize	File size in bytes
ImageWidth	If image, width in pixels (-1 if not)
ImageHeight	If image, height in pixels (-1 if not)

__TABLE

Information on all tables in the Center.

Variables	Description
TableName	Name of table
Broadcast	Whether front end visualizations & external listeners will be notified as data is updated in the table (true or false)
RefreshPeriodMs	The period in milliseconds that the table will conflate and broadcast changes to front end at. If a cell's value changes more frequently than the refresh period, those changes will be conflated
PersistEngine	If the table is persisted, which Persist Engine protocol was used
OnUndefColumn	Behavior when a realtime Object record contains an undefined column. Valid options are: "REJECT": The record will be rejected. This is the default "IGNORE": The record will be inserted, but the undefined values will be ignored "ADD": The table will automatically have the column added
DefinedBy	The ID of the owner
InitialCapacity	The number of rows to allocate memory for when the table is created. The default initial capacity is 1,000 rows and there must be at least 1

__TIMER

Information on any timers in the Center.

Variables	Description
TimerName	Name of the Timer created.
TimerType	Either AMISCRIPT or a user-defined custom timer type created using timer plugin.
Priority	A number that is set when two or more timers have the same exact scheduled time. The lowest value is executed first.
Schedule	A positive number defining the period in milliseconds between timer executions Use an empty string ("") to never run the timer. Useful for timers that should only run at startup Crontab-style entries are also valid
Options	AmiScript to run when timer is executed
DefinedBy	The ID of the owner
LastRunTime	Timestamp when the timer is last ran
NextRunTime	Timestamp that the next timer would run
Enabled	Is the timer currently active

__TRIGGER

Variables	Description
TriggerName	Name of the trigger to create, must be unique within the database
TableName	Name of the table(s) that will cause the trigger to execute
TriggerType	Either AMISCRIPT or a user-defined custom trigger type created using trigger plugin.
Priority	A number that is set when two or more triggers are triggered at the same time. The lowest value is executed first
Options	AmiScript to run when trigger is executed
DefinedBy	The ID of the owner
Enabled	Is the trigger currently active