Difference between revisions of "AMI Realtime Database"
m (Add that that some IDs are not guaranteed unique) |
|||
(211 intermediate revisions by 10 users not shown) | |||
Line 24: | Line 24: | ||
= Realtime Tables = | = Realtime Tables = | ||
− | == | + | == CREATE PUBLIC TABLE == |
+ | '''Overview''' | ||
− | |||
This command is used to create a new, empty (no rows) public table with a specified set of columns (names, types, no-null restriction). If another public table already exists with the <span style="color: blue;">tblname</span>, the create table will fail. | This command is used to create a new, empty (no rows) public table with a specified set of columns (names, types, no-null restriction). If another public table already exists with the <span style="color: blue;">tblname</span>, the create table will fail. | ||
Line 39: | Line 39: | ||
** <span style="color: red;">"TEXT"</span>: The table will be persisted to disk using a slow, but easy to read text file | ** <span style="color: red;">"TEXT"</span>: The table will be persisted to disk using a slow, but easy to read text file | ||
*** <span style="color: red;">"persist_dir" = "/path/to/directory/for/storing/file"</span> - this is the directory where the .txt text file will be saved/loaded from | *** <span style="color: red;">"persist_dir" = "/path/to/directory/for/storing/file"</span> - this is the directory where the .txt text file will be saved/loaded from | ||
+ | ** <span style="color: red;">"HISTORICAL"</span>: The table will be persisted using disk based historical engine. Optimized for storing large volumes of data and querying on demand. See [[AMI Historical Database|HDB manual]] for more details. | ||
* <span style="color: red;">PersistEngine="''Custom"''</span>: A custom persist engine will be used (see AMI plugins documentation). Note, that <span style="color: red;">PersistOptions="..."</span> is available to pass options to the custom plugin | * <span style="color: red;">PersistEngine="''Custom"''</span>: A custom persist engine will be used (see AMI plugins documentation). Note, that <span style="color: red;">PersistOptions="..."</span> is available to pass options to the custom plugin | ||
* <span style="color: red;">Broadcast="true"</span>: Front end visualizations & external listeners will be notified as data is updated in the table | * <span style="color: red;">Broadcast="true"</span>: Front end visualizations & external listeners will be notified as data is updated in the table | ||
− | * <span style="color: red;">RefreshPeriodMs="'' | + | * <span style="color: red;">RefreshPeriodMs="''duration_millis''"</span>: The period that the table will conflate and broadcast changes to front end at. For example, if a cells' value changes 10 times in one second and the refresh period is 500ms, then only ~2 updates will be broadcast out (the other 8 will be conflated) |
* <span style="color: red;">OnUndefColumn</span> - Behaviour to take when a realtime Object record contains an undefined column | * <span style="color: red;">OnUndefColumn</span> - Behaviour to take when a realtime Object record contains an undefined column | ||
** <span style="color: red;">"REJECT"</span> - The record will be rejected. This is the default | ** <span style="color: red;">"REJECT"</span> - The record will be rejected. This is the default | ||
Line 47: | Line 48: | ||
** <span style="color: red;">"ADD"</span> - The table will automatically have the column added | ** <span style="color: red;">"ADD"</span> - The table will automatically have the column added | ||
* <span style="color: red;">InitialCapacity="number_of_rows"</span> - the number of rows to allocate memory for when the table is created. Must be at least 1. The default initial capacity is 1,000 rows | * <span style="color: red;">InitialCapacity="number_of_rows"</span> - the number of rows to allocate memory for when the table is created. Must be at least 1. The default initial capacity is 1,000 rows | ||
+ | '''Syntax''' | ||
− | |||
<span style="font-family: courier new; color: red;">CREATE PUBLIC TABLE [IF NOT EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> (<span style="font-family: courier new; color: blue;">col1</span> <span style="font-family: courier new; color: blue;"><u>public_column_type</u> ''['', col2 <u>public_column_type</u> ...'']''<span style="font-family: courier new; color: black;">)</span> | <span style="font-family: courier new; color: red;">CREATE PUBLIC TABLE [IF NOT EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> (<span style="font-family: courier new; color: blue;">col1</span> <span style="font-family: courier new; color: blue;"><u>public_column_type</u> ''['', col2 <u>public_column_type</u> ...'']''<span style="font-family: courier new; color: black;">)</span> | ||
− | ''[''<span style="font-family: courier new; color: red;">USE </span>'' [''<span style="font-family: courier new; color: red;">PersistEngine</span>''="[''<span style="font-family: courier new; color: red;">FAST</span> | + | ''<span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">USE </span>'' <span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">PersistEngine</span>''<span style="font-family: courier new; color: grey;">="[</span>''<span style="font-family: courier new; color: red;">FAST</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">TEXT</span><span style="font-family: courier new; color: grey;">|</span>''<span style="font-family: courier new; color: grey;">custom</span><span style="font-family: courier new; color: grey;">]"]</span>'' |
− | ''[''<span style="font-family: courier new; color: red;">PersistOptions</span>''=<span style="font-family: courier new; color: grey;">"custom_options"</span> | + | ''<span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">PersistOptions</span>''<span style="font-family: courier new; color: grey;">=</span><span style="font-family: courier new; color: grey;">"custom_options"]</span>'' |
− | ''[''<span style="font-family: courier new; color: red;">Broadcast</span>''="[''<span style="font-family: courier new; color: red;">true</span> | + | ''<span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">Broadcast</span>''<span style="font-family: courier new; color: grey;">="[</span>''<span style="font-family: courier new; color: red;">true</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">false</span>''<span style="font-family: courier new; color: grey;">]"]</span>'' |
− | ''[''<span style="font-family: courier new; color: red;">RefreshPeriodMs</span>''=<span style="font-family: courier new; color: grey;">"duration_millis"</span> | + | ''<span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">RefreshPeriodMs</span>''<span style="font-family: courier new; color: grey;">=</span><span style="font-family: courier new; color: grey;">"duration_millis"]</span>'' |
− | ''[''<span style="font-family: courier new; color: red;">OnUndefColumn</span>''="''<span style="font-family: courier new; color: red;">REJECT</span> | + | ''<span style="font-family: courier new; color: grey;">[</span>''<span style="font-family: courier new; color: red;">OnUndefColumn</span>''<span style="font-family: courier new; color: grey;">="</span>''<span style="font-family: courier new; color: red;">REJECT</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">IGNORE</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">ADD</span>''<span style="font-family: courier new; color: grey;">"]</span>'' |
<span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to create''</span> | <span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to create''</span> | ||
Line 65: | Line 66: | ||
<span style="font-family: courier new; color: grey;">'''''colN''' - string name of Nth column to create''</span> | <span style="font-family: courier new; color: grey;">'''''colN''' - string name of Nth column to create''</span> | ||
− | + | '''Example''' | |
+ | |||
<span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE countries (country String NoNull, language String, GDP Double) USE PersistEngine="TEXT" Broadcast="false" InitialCapacity="100"</span> | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE countries (country String NoNull, language String, GDP Double) USE PersistEngine="TEXT" Broadcast="false" InitialCapacity="100"</span> | ||
− | == | + | == CREATE PUBLIC TABLE AS == |
+ | '''Overview''' | ||
− | |||
This command is used to create a new table which has the same schema definition as the result set of a <span style="color: purple;">select_clause</span>. If another table already exists with the <span style="color: blue;">tblname</span>, the create table will fail. | This command is used to create a new table which has the same schema definition as the result set of a <span style="color: purple;">select_clause</span>. If another table already exists with the <span style="color: blue;">tblname</span>, the create table will fail. | ||
All rows from the <span style="color: purple;">select_clause</span>'s result set will be inserted into the table. For valid <span style="color: red;">USE</span> options, see <span style="color: purple;">create_public_table_clause</span> Options. | All rows from the <span style="color: purple;">select_clause</span>'s result set will be inserted into the table. For valid <span style="color: red;">USE</span> options, see <span style="color: purple;">create_public_table_clause</span> Options. | ||
− | + | '''Syntax''' | |
+ | |||
<span style="font-family: courier new; color: red;">CREATE PUBLIC TABLE [IF NOT EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; color: grey;">''[''(</span><span style="font-family: courier new; color: blue;">col1 public_column_type</span> <span style="font-family: courier new; color: grey;">''[,''</span> <span style="font-family: courier new; color: blue;">col2 public_column_type</span> <span style="font-family: courier new; color: grey;">...'']''</span><span style="font-family: courier new; color: red;">)</span><span style="font-family: courier new; color: grey;">'']''</span> <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">USE</span> <span style="font-family: courier new; color: grey;">...</span> <span style="font-family: courier new; color: grey;">'']''</span> <span style="font-family: courier new; color: red;">AS</span> <span style="font-family: courier new; color: purple;">select_clause</span> | <span style="font-family: courier new; color: red;">CREATE PUBLIC TABLE [IF NOT EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; color: grey;">''[''(</span><span style="font-family: courier new; color: blue;">col1 public_column_type</span> <span style="font-family: courier new; color: grey;">''[,''</span> <span style="font-family: courier new; color: blue;">col2 public_column_type</span> <span style="font-family: courier new; color: grey;">...'']''</span><span style="font-family: courier new; color: red;">)</span><span style="font-family: courier new; color: grey;">'']''</span> <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">USE</span> <span style="font-family: courier new; color: grey;">...</span> <span style="font-family: courier new; color: grey;">'']''</span> <span style="font-family: courier new; color: red;">AS</span> <span style="font-family: courier new; color: purple;">select_clause</span> | ||
Line 86: | Line 89: | ||
<span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to create''</span> | <span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to create''</span> | ||
− | + | '''Example''' | |
+ | |||
<span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE names USE PersistEngine="FAST" AS SELECT first_name, last_name FROM employees</span> | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE names USE PersistEngine="FAST" AS SELECT first_name, last_name FROM employees</span> | ||
− | == | + | == ALTER PUBLIC TABLE == |
+ | '''OVERVIEW''' | ||
− | |||
This command alters the schema (column names and types) of an existing table. You can add columns (<span style="color: red;">ADD</span>), rename columns (<span style="color: red;">RENAME … TO</span>), delete columns (<span style="color: red;">DROP</span>), and change the type of columns (<span style="color: red;">MODIFY</span>). Note, when multiple alterations are supplied, they are evaluated left to right. When changing types, a best effort cast will be applied. | This command alters the schema (column names and types) of an existing table. You can add columns (<span style="color: red;">ADD</span>), rename columns (<span style="color: red;">RENAME … TO</span>), delete columns (<span style="color: red;">DROP</span>), and change the type of columns (<span style="color: red;">MODIFY</span>). Note, when multiple alterations are supplied, they are evaluated left to right. When changing types, a best effort cast will be applied. | ||
Line 100: | Line 104: | ||
<span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to alter''</span> | <span style="font-family: courier new; color: grey;">'''''tblname''' - string name of the table to alter''</span> | ||
− | + | '''Examples''' | |
+ | |||
<span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings ADD UserId long, RENAME AccountId TO Account_Id, DROP UserInfo</span> | <span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings ADD UserId long, RENAME AccountId TO Account_Id, DROP UserInfo</span> | ||
− | <span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings | + | <span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings MODIFY UserId AS UserId INT</span> |
− | <span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings RENAME UserId TO UserIds, ADD UserId STRING</span> <span style="font-family: courier new; color: red;">AS</span> | + | <span style="font-family: courier new; color: blue;">ALTER PUBLIC TABLE AccountHoldings RENAME UserId TO UserIds, ADD UserId STRING</span> <span style="font-family: courier new; color: red;">AS</span> |
− | == | + | === MODIFY colname === |
<span style="font-family: courier new; color: red;">MODIFY</span> <span style="font-family: courier new; color: blue;">colname</span> <span style="font-family: courier new; color: red;">AS</span> <span style="font-family: courier new; color: blue;">newname public_column_type</span> <span style="font-family: courier new; color: blue;">|</span><span style="font-family: courier new; color: red;">ADD</span> <span style="font-family: courier new; color: blue;">newname</span> <span style="font-family: courier new; color: blue;">public_column_type [</span><span style="font-family: courier new; color: red;">BEFORE</span> <span style="font-family: courier new; color: blue;">beforecol</span><span style="font-family: courier new; color: blue;">]|</span><span style="font-family: courier new; color: red;">DROP</span> <span style="font-family: courier new; color: blue;">colname|</span><span style="font-family: courier new; color: red;">RENAME</span> <span style="font-family: courier new; color: blue;">colname</span> <span style="font-family: courier new; color: red;">TO</span> <span style="font-family: courier new; color: blue;">newname</span> | <span style="font-family: courier new; color: red;">MODIFY</span> <span style="font-family: courier new; color: blue;">colname</span> <span style="font-family: courier new; color: red;">AS</span> <span style="font-family: courier new; color: blue;">newname public_column_type</span> <span style="font-family: courier new; color: blue;">|</span><span style="font-family: courier new; color: red;">ADD</span> <span style="font-family: courier new; color: blue;">newname</span> <span style="font-family: courier new; color: blue;">public_column_type [</span><span style="font-family: courier new; color: red;">BEFORE</span> <span style="font-family: courier new; color: blue;">beforecol</span><span style="font-family: courier new; color: blue;">]|</span><span style="font-family: courier new; color: red;">DROP</span> <span style="font-family: courier new; color: blue;">colname|</span><span style="font-family: courier new; color: red;">RENAME</span> <span style="font-family: courier new; color: blue;">colname</span> <span style="font-family: courier new; color: red;">TO</span> <span style="font-family: courier new; color: blue;">newname</span> | ||
Line 116: | Line 121: | ||
<span style="font-family: courier new; color: grey;">'''''beforecol''' - String name of the existing column to add the new column before''</span> | <span style="font-family: courier new; color: grey;">'''''beforecol''' - String name of the existing column to add the new column before''</span> | ||
− | == | + | == RENAME PUBLIC TABLE == |
+ | '''Overview''' | ||
− | |||
This command renames an existing table. If another table already exists with the <span style="color: grey;">newname</span>, the existing table will be implicitly dropped and replaced with the renamed table. | This command renames an existing table. If another table already exists with the <span style="color: grey;">newname</span>, the existing table will be implicitly dropped and replaced with the renamed table. | ||
− | + | '''Syntax''' | |
+ | |||
<span style="font-family: courier new; color: red;">RENAME PUBLIC TABLE</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; color: red;">TO</span> <span style="font-family: courier new; color: blue;">newname</span> | <span style="font-family: courier new; color: red;">RENAME PUBLIC TABLE</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; color: red;">TO</span> <span style="font-family: courier new; color: blue;">newname</span> | ||
Line 128: | Line 134: | ||
<span style="font-family: courier new; color: grey;">'''''newname''' - string name of the new name for the table''</span> | <span style="font-family: courier new; color: grey;">'''''newname''' - string name of the new name for the table''</span> | ||
− | + | '''Example''' | |
<span style="font-family: courier new; color: blue;">RENAME PUBLIC TABLE AccountHoldings TO Accounts</span> | <span style="font-family: courier new; color: blue;">RENAME PUBLIC TABLE AccountHoldings TO Accounts</span> | ||
− | == | + | == DROP PUBLIC TABLE == |
+ | '''Overview''' | ||
− | |||
This command drops an existing table, along with its schema and all rows. You can specify multiple tables, separated by commas. | This command drops an existing table, along with its schema and all rows. You can specify multiple tables, separated by commas. | ||
− | + | '''Syntax''' | |
+ | |||
<span style="font-family: courier new; color: red;">DROP PUBLIC TABLE [IF EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; ">''[''</span><span style="font-family: courier new; color: blue;">, tblname ...</span><span style="font-family: courier new; ">'']''</span> | <span style="font-family: courier new; color: red;">DROP PUBLIC TABLE [IF EXISTS]</span> <span style="font-family: courier new; color: blue;">tblname</span> <span style="font-family: courier new; ">''[''</span><span style="font-family: courier new; color: blue;">, tblname ...</span><span style="font-family: courier new; ">'']''</span> | ||
− | + | '''Examples''' | |
+ | |||
<span style="font-family: courier new; color: blue;">DROP PUBLIC TABLE Accounts</span> | <span style="font-family: courier new; color: blue;">DROP PUBLIC TABLE Accounts</span> | ||
− | == | + | == Public Column Types == |
The following types are supported for columns in public tables. | The following types are supported for columns in public tables. | ||
Line 242: | Line 250: | ||
== Reserved columns on public Tables == | == Reserved columns on public Tables == | ||
− | If any of the following columns are declared they will be automatically populated by the AMI engine. Note that when creating the table, the column type MUST match the type specified below, otherwise table creation will fail. Note all times are since unix epoc in milliseconds. | + | If any of the following columns are declared they will be automatically populated by the AMI engine. Columns A, D, T, and W will be populated even if they are not declared. Note that when creating the table, the column type MUST match the type specified below, otherwise table creation will fail. Note all times are since unix epoc in milliseconds. |
{| class="wikitable" | {| class="wikitable" | ||
!Name | !Name | ||
Line 249: | Line 257: | ||
!Mutable | !Mutable | ||
!Description | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">A</span> | ||
+ | |<span style="font-family: courier new; color: blue;">String</span> | ||
+ | |AMI-Center | ||
+ | | | ||
+ | |Stores the name of the AMI Center. | ||
|- | |- | ||
|<span style="font-family: courier new; color: red;">C</span> | |<span style="font-family: courier new; color: red;">C</span> | ||
|<span style="font-family: courier new; color: blue;">Long</span> | |<span style="font-family: courier new; color: blue;">Long</span> | ||
− | |Created | + | |Created Time |
| | | | ||
|Time that the record was inserted into the table | |Time that the record was inserted into the table | ||
Line 258: | Line 272: | ||
|<span style="font-family: courier new; color: red;">D</span> | |<span style="font-family: courier new; color: red;">D</span> | ||
|<span style="font-family: courier new; color: blue;">Long</span> | |<span style="font-family: courier new; color: blue;">Long</span> | ||
− | |AMI ID | + | |AMI-ID |
| | | | ||
|An auto-generated incrementing unique id for the row. Unique across all tables. | |An auto-generated incrementing unique id for the row. Unique across all tables. | ||
Line 264: | Line 278: | ||
|<span style="font-family: courier new; color: red;">E</span> | |<span style="font-family: courier new; color: red;">E</span> | ||
|<span style="font-family: courier new; color: blue;">Long</span> | |<span style="font-family: courier new; color: blue;">Long</span> | ||
− | |Expires | + | |Expires Time |
|INSERT, UPDATE | |INSERT, UPDATE | ||
|Time when the row should be automatically deleted. | |Time when the row should be automatically deleted. | ||
Line 274: | Line 288: | ||
|- | |- | ||
|<span style="font-family: courier new; color: red;">I*</span> | |<span style="font-family: courier new; color: red;">I*</span> | ||
− | |<span style="font-family: courier new; color: blue;"> | + | |<span style="font-family: courier new; color: blue;">Object</span> |
|UniqueID | |UniqueID | ||
|INSERT | |INSERT | ||
Line 281: | Line 295: | ||
|<span style="font-family: courier new; color: red;">M</span> | |<span style="font-family: courier new; color: red;">M</span> | ||
|<span style="font-family: courier new; color: blue;">Long</span> | |<span style="font-family: courier new; color: blue;">Long</span> | ||
− | |Modified | + | |Modified Time |
| | | | ||
|Most recent time that the record was updated (initially the time the row was inserted) | |Most recent time that the record was updated (initially the time the row was inserted) | ||
Line 287: | Line 301: | ||
|<span style="font-family: courier new; color: red;">P</span> | |<span style="font-family: courier new; color: red;">P</span> | ||
|<span style="font-family: courier new; color: blue;">String</span> | |<span style="font-family: courier new; color: blue;">String</span> | ||
− | | | + | |Application |
|INSERT | |INSERT | ||
|The Login Id (<span style="color: red;">I</span>) value supplied from the realtime messaging api on the Login (<span style="color: red;">L</span>) message | |The Login Id (<span style="color: red;">I</span>) value supplied from the realtime messaging api on the Login (<span style="color: red;">L</span>) message | ||
Line 298: | Line 312: | ||
|- | |- | ||
|<span style="font-family: courier new; color: red;">T</span> | |<span style="font-family: courier new; color: red;">T</span> | ||
− | |<span style="font-family: courier new; color: blue;"> | + | |<span style="font-family: courier new; color: blue;">String</span> |
|Table Name | |Table Name | ||
| | | | ||
|The name of the table, all cells will contain the same value (this is for backwards compatibility) | |The name of the table, all cells will contain the same value (this is for backwards compatibility) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">W</span> | ||
+ | |<span style="font-family: courier new; color: blue;">Long</span> | ||
+ | |Current Time | ||
+ | | | ||
+ | |Stores the current Unix timestamp. | ||
+ | |||
|} | |} | ||
<nowiki>*</nowiki><span style="color: red;">I</span> (and <span style="color: red;">P</span>) column behavior can be thought of as a convenient way of supporting the "upsert" concept: | <nowiki>*</nowiki><span style="color: red;">I</span> (and <span style="color: red;">P</span>) column behavior can be thought of as a convenient way of supporting the "upsert" concept: | ||
Line 311: | Line 332: | ||
=Realtime Indexes= | =Realtime Indexes= | ||
− | == | + | == CREATE INDEX == |
+ | '''Overview''' | ||
− | |||
This command allows you to dramatically speed up common queries. Without indexes, a <span style="color: red;">SELECT … WHERE</span> clause needs to scan and consider each row. But if there is an index on the column referenced in the WHERE clause, then the query optimizer will use the index instead. The first column listed is the "top" level column indexed, and so on. | This command allows you to dramatically speed up common queries. Without indexes, a <span style="color: red;">SELECT … WHERE</span> clause needs to scan and consider each row. But if there is an index on the column referenced in the WHERE clause, then the query optimizer will use the index instead. The first column listed is the "top" level column indexed, and so on. | ||
Line 320: | Line 341: | ||
* HASH: Under the hood this is a hashmap. This is the fastest most versatile indexing approach but is only useful for queries that directly use equality, because the data is stored in an unsorted fashion. | * HASH: Under the hood this is a hashmap. This is the fastest most versatile indexing approach but is only useful for queries that directly use equality, because the data is stored in an unsorted fashion. | ||
* SORT: Under the hood this is a treemap. This has additional overhead to a HASH index for both inserting and querying but can be used with ordinality comparisons, such as less than, greater than as well as equals. | * SORT: Under the hood this is a treemap. This has additional overhead to a HASH index for both inserting and querying but can be used with ordinality comparisons, such as less than, greater than as well as equals. | ||
− | * SERIES: Under the hood this is a sorted array. This is a special purpose and highly optimized index useful for when data will be inserted in ascending order, as entries can quickly be added to the end of the array, and queries (including ordinal searches) can use a binary lookup. If data is | + | * SERIES: Under the hood this is a sorted array. This is a special purpose and highly optimized index useful for when data will be inserted in ascending order, as entries can quickly be added to the end of the array, and queries (including ordinal searches) can use a binary lookup. If data is out of order the array is forced to do a memcopy which can be very expensive. |
Constraints can be added to an index to determine the outcome of a key collision. Three different types of constraints are supported: | Constraints can be added to an index to determine the outcome of a key collision. Three different types of constraints are supported: | ||
Line 340: | Line 361: | ||
''<span style="font-family: courier new; color: grey;">'''col_name''' - name of the column to put the index on.</span>'' | ''<span style="font-family: courier new; color: grey;">'''col_name''' - name of the column to put the index on.</span>'' | ||
− | === Examples === | + | |
+ | '''Joint Indexes''' | ||
+ | |||
+ | Joint indexes are indexes that span across multiple columns in a table. The syntax is: <span style="font-family: courier new; color: blue;">CREATE INDEX myIndex ON MyTable(A <Type>,B <Type>,C <Type>,....);</span> | ||
+ | Joint indexes are helpful when multiple columns participate in your query’s where clause. It will enable the query optimizer to find out the best path to return the results by reducing the search space. Here are some rules you need to follow: | ||
+ | *Rule1: Order does matter. | ||
+ | Note that the order does matter. If you do <span style="font-family: courier new; color: red;"> CREATE INDEX myIndex ON MyTable(A HASH,B HASH);</span>, it is different from doing <span style="font-family: courier new; color: red;">CREATE INDEX myIndex ON MyTable(B HASH,A HASH);</span> | ||
+ | |||
+ | If you create an index on column A and column B, it first creates the index on column A and for every value in column A, it creates the second index on column B. | ||
+ | |||
+ | *Rule2: High Cardinality Column First. | ||
+ | Knowing that you need a joint index, it is best practice to first create the index on the column with the highest cardinality. By doing so, you are more likely to reduce the search space much more in the initial lookup. | ||
+ | |||
+ | '''Examples - joint indexes''' | ||
+ | |||
+ | Given the table ''orders'': | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE ORDERS(orderId int, Symbol string, Region string, Quantity int, Price double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">List regionList = new list("Asia","North America","South America","Europe","Africa");</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">for(int i=0;i<1000000;i++){ | ||
+ | INSERT INTO ORDERS VALUES(rand(10000), "sym"+rand(1000),(string) (regionList.get(rand(5))), 500+rand(1000), rand()*200); | ||
+ | }</span> | ||
+ | |||
+ | Since Symbol has a cardinality of 500 and Region of 5. It is advisable to first create index on Symbol then Region, ie:<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE INDEX jointIndex ON orders(Symbol HASH, Region HASH);</span> | ||
+ | |||
+ | Now let's run some concrete queries to see how this joint index helps increase the query speed. | ||
+ | |||
+ | * (1.1) case1: full usage of the joint index | ||
+ | <span style="font-family: courier new; color: blue;">SELECT * FROM orders WHERE Symbol=="sym500" AND Region=="North America"; //use both part of the jointIndex</span> | ||
+ | |||
+ | * (1.2) case2: partial usage of the joint index | ||
+ | <span style="font-family: courier new; color: blue;">SELECT * FROM orders WHERE Symbol=="sym500"; //use only "Symbol" part of the jointIndex</span> | ||
+ | |||
+ | * (1.2) case2: no usage of the joint index | ||
+ | <span style="font-family: courier new; color: blue;">SELECT * FROM orders WHERE Region=="North America"; //not using index, do a hard forward scan</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SELECT * FROM orders WHERE Region=="North America" AND Symbol=="sym500"; //not using index, do a hard forward scan</span> | ||
+ | |||
+ | '''Examples2''' | ||
+ | |||
This example will add an index to the MyTable's Account and Price column. Queries that use the Account column or the Account and Price column will be faster. Note that queries using only the Price column will not sped up by this index. This is an ideal index if we know where going to be running queries of the form: select ... from MyIndex where Account==''somevalue'' and Price[ <, ==, > ] ''somevalue'' | This example will add an index to the MyTable's Account and Price column. Queries that use the Account column or the Account and Price column will be faster. Note that queries using only the Price column will not sped up by this index. This is an ideal index if we know where going to be running queries of the form: select ... from MyIndex where Account==''somevalue'' and Price[ <, ==, > ] ''somevalue'' | ||
Line 346: | Line 410: | ||
<span style="font-family: courier new; color: blue;">CREATE INDEX MyIndex ON MyTable(Name HASH) USE CONSTRAINT="PRIMARY"</span> | <span style="font-family: courier new; color: blue;">CREATE INDEX MyIndex ON MyTable(Name HASH) USE CONSTRAINT="PRIMARY"</span> | ||
+ | |||
+ | == DROP INDEX == | ||
+ | '''Overview''' | ||
+ | |||
+ | Removes the specified index from the given table, as created by CREATE INDEX | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DROP INDEX [IF EXISTS]</span> <span style="font-family: courier new; color: blue;">index_name</span> <span style="font-family: courier new; color: red;">ON</span> <span style="font-family: courier new; color: blue;">tbl_name</span> ''<span style="font-family: courier new; color: grey;">[,index_name</span> <span style="font-family: courier new; color: grey;">ON</span> <span style="font-family: courier new; color: grey;">tbl_name...]</span>'' | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will drop the index named MyIndex from the MyTable table. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">Drop index MyIndex on MyTable</span> | ||
=Realtime Triggers= | =Realtime Triggers= | ||
+ | |||
+ | == Custom AMI Script == | ||
+ | '''Overview''' | ||
+ | |||
+ | This command allows you to add script that can be executed as data is inserted, updated or deleted from a specified table. There are 5 different events that can cause the trigger to be executed, you must specify at least one of: | ||
+ | |||
+ | * <span style="color: red;">onInsertingScript</span> - This script will be executed '''before a row is inserted''' into ''table_name'' table. The values of the row to be inserted will available as local variables to the executing script. | ||
+ | * <span style="color: red;">onInsertedScript</span> - This script will be executed '''after a row is inserted''' into ''table_name''. The values of the row just inserted will available as local variables to the executing script. | ||
+ | * <span style="color: red;">onUpdatingScript</span> - This script will be executed '''before an update''' on ''table_name'' table's rows. The values of the row prior to the update will be available as local variable with the form old_varname. The values of the row after the update will be available as local variable with the form new_varname. | ||
+ | * <span style="color: red;">onUpdatedScript</span> - This script will be executed '''after an update''' on ''table_name'' table's rows. The values of the row just updated will be available as local variables to the executing script. | ||
+ | * <span style="color: red;">onDeletingScript</span> - This script will be executed '''before a row is deleted''' from the ''table_name'' table. The values of the row to be deleted will available as local variables to the executing script. | ||
+ | * <span style="color: red;">canMutateRow</span> - If true, then any values of the row changed inside the onInsertingScript will reflect back on the row to be inserted. For onUpdatingScript, any changes to the new_varname values will reflect on the row to be updated. Note, this only applies to the onInsertingScript and onUpdatingScript options. See Mutating Trigger example below | ||
+ | * <span style="color: red;">rowVar</span> - a placeholder(can be any custom variable name) that contains the map that reflects the row change in the table(either insert, update or delete).Note that rowVar is a '''read-only map''' and the available methods include:'''boolean containsValue()''','''boolean containsKey()'''. The rowVar map can also be used in onUpdatingScript and prefixed with new_ and old_. See [[AMI_Realtime_Database#rowVar_Example_using_onUpdatingScript|rowVar Example using onUpdatingScript]] for more details. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER</span> <span style="font-family: courier new; color: blue;">trigger_name</span> <span style="font-family: courier new; color: red;">OFTYPE AMISCRIPT ON</span> <span style="font-family: courier new; color: blue;">table_name</span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">]</span> <span style="font-family: courier new; color: red;">USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">vars="</span> ''<span style="font-family: courier new; color: grey;">Variables shared by the trigger, a comma delimited list of type varname</span>''<span style="font-family: courier new; color: red;">"</span>''<span style="font-family: courier new; color: grey;">]</span>'' | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onStartupScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run When the trigger is created''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onInsertingScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run Prior to insert of a row on defined Tables''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onInsertedScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run After an insert of a row on defined Tables''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onUpdatingScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run Prior to updating a row on defined Tables''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onUpdatedScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run After updating a row on defined Tables''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onDeletingScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to Run Prior to deleting a row on defined Tables''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">canMutateRow="</span><span style="font-family: courier new; color: grey;">''true or false''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">rowVar="</span><span style="font-family: courier new; color: grey;">''_row''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''trigger_name''' - name of the trigger to create, must be unique within the database''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''table_name''' - name of the table(s) that will cause the trigger to execute''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''priority''' - a number, triggers with lowest value are executed first. This also controls the order in which triggers are started up/initialized</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | In this example let us assume we have a table ''MyTable(id int,price double)'' and a ''MyAlertTable(type String,id int,diff double) .'' When a row is deleted from the MyTable this trigger will insert an alert into the ''MyAlert'' Table including the id and price of the record deleted. In the case of an update, a row will be inserted into the ''MyAlert'' table including the id and change in price due to the update. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MyTrigger OFTYPE AMISCRIPT ON MyTable USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onDeletingScript="insert into MyAlert(type,id) values(\"RECORD DELETED\",id,0-price)"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onUpdatingScript="insert into MyAlert(type,id,diff) values(\"CHANGE\",id,new_price - old_price)"</span> | ||
+ | |||
+ | === Mutating Trigger Example === | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE MutateSample(px double,qty int,val double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MutateTrigger OFTYPE AMISCRIPT ON MutateSample USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">canMutateRow="true" onInsertingScript="val=px*qty"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onUpdatingScript="new_val=new_px*new_qty";</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">INSERT INTO MutateSample(px,qty) VALUES(125.2,100);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SELECT * from MutateSample;</span> | ||
+ | {| class="wikitable" | ||
+ | |+<span style="font-family: courier new; color: blue;">MutateSample</span> | ||
+ | !<span style="font-family: courier new; color: blue;">px</span> | ||
+ | <span style="font-family: courier new; color: blue;">Double</span> | ||
+ | !<span style="font-family: courier new; color: blue;">qty</span> | ||
+ | <span style="font-family: courier new; color: blue;">Integer</span> | ||
+ | !<span style="font-family: courier new; color: blue;">val</span> | ||
+ | <span style="font-family: courier new; color: blue;">Double</span> | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">125.2</span> | ||
+ | |<span style="font-family: courier new; color: blue;">100</span> | ||
+ | |<span style="font-family: courier new; color: blue;">12520.0</span> | ||
+ | |} | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">UPDATE MutateSample set qty=qty+10;</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SELECT * from MutateSample;</span> | ||
+ | {| class="wikitable" | ||
+ | |+<span style="font-family: courier new; color: blue;">MutateSample</span> | ||
+ | !<span style="font-family: courier new; color: blue;">px</span> | ||
+ | <span style="font-family: courier new; color: blue;">Double</span> | ||
+ | !<span style="font-family: courier new; color: blue;">qty</span> | ||
+ | <span style="font-family: courier new; color: blue;">Integer</span> | ||
+ | !<span style="font-family: courier new; color: blue;">val</span> | ||
+ | <span style="font-family: courier new; color: blue;">Double</span> | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">125.2</span> | ||
+ | |<span style="font-family: courier new; color: blue;">110</span> | ||
+ | |<span style="font-family: courier new; color: blue;">13772.0</span> | ||
+ | |} | ||
+ | |||
+ | === Mutating Trigger Example Special Case === | ||
+ | In the case of using deferred statements (statements in the format of <span style="font-family: courier new; color: blue;">use ds=<your_datasource> execute ...</span> in the mutating trigger, It is strongly recommended that we put all the deferred statements at the end of the script after mutations occur. For example, adding a little tweak to the example before, if we also want to run a deferred statement against another datasource while mutation is happening: | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE MutateSample(px double,qty int,val double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MutateTriggerWithDeferredStatement OFTYPE AMISCRIPT ON MutateSample USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">canMutateRow="true" onInsertingScript="val=px*qty"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onUpdatingScript="new_val=new_px*new_qty;</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;"> use ds=<another_datasource> execute ....;";//must be placed after the mutation</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">INSERT INTO MutateSample(px,qty) VALUES(125.2,100);</span> | ||
+ | |||
+ | === rowVar Example basic === | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE src(id long, sym string);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE tgt(rows string, arg1 string, arg2 string); </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER t1 OFTYPE AMISCRIPT ON src USE </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">rowVar="_row" </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onInsertingScript="insert into tgt values(_row.toJson(),_row.get(\"id\"),_row.get(\"sym\"))";</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">INSERT INTO src VALUES(123,"aapl");</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SELECT * FROM tgt;</span> | ||
+ | |||
+ | {| class="wikitable" | ||
+ | |+<span style="font-family: courier new; color: blue;">tgt</span> | ||
+ | !<span style="font-family: courier new; color: blue;">rows</span> | ||
+ | <span style="font-family: courier new; color: blue;">String</span> | ||
+ | !<span style="font-family: courier new; color: blue;">arg1</span> | ||
+ | <span style="font-family: courier new; color: blue;">String</span> | ||
+ | !<span style="font-family: courier new; color: blue;">arg2</span> | ||
+ | <span style="font-family: courier new; color: blue;">String</span> | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">{"id":123,"sym":"aapl"}</span> | ||
+ | |<span style="font-family: courier new; color: blue;">123</span> | ||
+ | |<span style="font-family: courier new; color: blue;">aapl</span> | ||
+ | |} | ||
+ | |||
+ | === rowVar Example using onUpdatingScript === | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create public table orders(id string, val int);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create index idx on orders(id HASH) use CONSTRAINT="PRIMARY";</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create public table alerts(comments string);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create trigger rowVarTrigger oftype amiscript on orders use rowVar="row"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">onUpdatingScript="""</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;"> if(new_row.containsKey("val")){</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;"> insert into alerts values("newrow: "+new_row+ "oldrow: " + old_row + "containVal?: " + new_row.containsKey("val"));</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;"> }"""; </span> | ||
+ | |||
+ | Case1: Upsert(insert causes update due to primary index)<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;"> insert into orders values("order1",2),("order1",3);//trigger upsert </span> | ||
+ | |||
+ | {| class="wikitable" | ||
+ | |+<span style="font-family: courier new; color: blue;">alerts</span> | ||
+ | !<span style="font-family: courier new; color: blue;">comments</span> | ||
+ | <span style="font-family: courier new; color: blue;">String</span> | ||
+ | |||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">newrow: [id=order1, val=3]; oldrow: [id=order1, val=2]; containsVal?:true</span> | ||
+ | |} | ||
+ | |||
+ | Case2: Direct update<br> | ||
+ | Note that only columns that participate in the update clause will appear in the new_rowVar map<br> | ||
+ | <span style="font-family: courier new; color: blue;">update orders set val=10; </span> | ||
+ | {| class="wikitable" | ||
+ | |+<span style="font-family: courier new; color: blue;">alerts</span> | ||
+ | !<span style="font-family: courier new; color: blue;">comments</span> | ||
+ | <span style="font-family: courier new; color: blue;">String</span> | ||
+ | |||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">newrow: [val=10]; oldrow: [id=order1, val=3]; containVal?:true</span> | ||
+ | |} | ||
+ | |||
+ | == Aggregation == | ||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER mytrigger OFTYPE AGGREGATE ON sourceTable,targetTable USE ...</span> | ||
+ | |||
+ | '''Overview''' | ||
+ | |||
+ | This command binds an existing source table and an existing target table together such that the target table will be an aggregate representation of the source table. The following rules apply: | ||
+ | |||
+ | *As the ''source'' table has rows inserted, updated or deleted the target table will be automatically updated accordingly | ||
+ | *The ''target'' table is no longer modifiable (inserts, updates and deletes to the ''target'' table will not succeed) | ||
+ | *Multiple aggregate triggers can share the same source table | ||
+ | *Multiple aggregate triggers can not share the same target table (a given table can only participate in one aggregate trigger as a ''target'' table) | ||
+ | *Streaming triggers can be daisy chained, meaning a given table can be a target for one aggregate trigger and a source for another aggregate trigger | ||
+ | *The creation of the trigger will clear out and rebuild the ''target'' table to properly reflect the aggregation of the source table's data | ||
+ | |||
+ | There are three USE options: | ||
+ | |||
+ | *<span style="color: red;">groupBys</span> - A comma delimited list of expressions to group rows by, each expression being of the form: | ||
+ | **''targetTableColumn = expression_on_sourceTableColumns'' [,''targetTableColumn = expression_on_sourceTableColumns ...]'' | ||
+ | *<span style="color: red;">selects</span> - A comma delimited list of expressions on how to populate target columns from source columns. | ||
+ | **''targetTableColumn = aggregate_on_sourceTableColumns'' [,''targetTableColumn = aggregate_on_sourceTableColumns ...]'' | ||
+ | *<span style="color: red;">allowExternalUpdates</span> - Optional. Value is either true or false (false by default). If true, then other processes (i.e triggers, UPDATEs) are allowed to perform UPDATEs on the target table. Please use precaution when using this feature, since updating cells controlled by the aggregate trigger will result into an undesirable state. | ||
+ | |||
+ | The following rules apply for the <span style="color: red;">groupBys</span> and <span style="color: red;">selects</span> use options: | ||
+ | |||
+ | * ''targetTableColumns'' must only reference columns in the target table's shcema | ||
+ | * ''sourceTableColumns'' must only reference columns in the source table's schema | ||
+ | * A ''targetTableColumn'' can only be referenced once | ||
+ | * For aggregate expressions only ''count, countUnique, first, last, max, min, stdev, stdevs, sum'' and ''var'' functions are supported | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER</span> <span style="font-family: courier new; color: blue;">trigger_name</span> <span style="font-family: courier new; color: red;">OFTYPE AGGREGATE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">ON</span> <span style="font-family: courier new; color: blue;">source_table_name, target_table_name</span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">]</span> <span style="font-family: courier new; color: red;">USE groupBys="</span><span style="font-family: courier new; ">list of group by expressions</span><span style="font-family: courier new; color: red;">" selects="</span><span style="font-family: courier new; ">list of aggregate expressions</span><span style="font-family: courier new; color: red;">"</span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">allowExternalUpdates="</span><span style="font-family: courier new; color: grey;>true or false</span><span style="font-family: courier new; color: red;>"</span><span style="font-family: courier new; color: grey;">]</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''trigger_name''' - name of the trigger to create, must be unique within the database''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''source_table_name''' - name of the table that will cause the trigger to execute''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''target_table_name''' - name of the table that will be updates by the trigger''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''priority''' - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table''</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Lets assume the two tables exist:</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Accounts(account String,region String,qty int,px double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Summary(act String,region String,cnt int,value double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//The below trigger will build a realtime aggregation on accounts reflected in the summary table: | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//(try inserting into the Accounts table and then take a look at the Summary table)</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MyTrigger OFTYPE AGGREGATE ON Accounts,Summary USE groupBys="act=account,region=region" selects="cnt=count(*),value=sum(qty*px)";</span> | ||
+ | |||
+ | == Projection and Filter == | ||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER mytrigger OFTYPE PROJECTION ON sourceTable(s),targetTable USE ...</span> | ||
+ | |||
+ | '''Overview''' | ||
+ | |||
+ | This command binds one or more existing source tables and an existing target table together such that the target table will be populated using the source table(s). The following rules then apply: | ||
+ | |||
+ | *As the source table(s) have rows inserted, updated or deleted the target table will automatically have it's corresponding projected rows inserted, updated, deleted accordingly. | ||
+ | *When there are multiple source tables, the resulting target is a UNION of the source tables (not a join) | ||
+ | *The target table's rows that are a result of this projection can not be modified (inserts, updates and deletes to the target table's managed rows will not succeed) | ||
+ | *Multiple projection triggers can share the same source table | ||
+ | *Multiple projection triggers can share the same target table | ||
+ | *The creation of the trigger will clear out and rebuild the target table to properly reflect the aggregation of the source table's data. | ||
+ | *If a source tables' row is updated such that the evaluation of the wheres clause changes, then the row will be inserted/deleted from the target table accordingly. | ||
+ | |||
+ | There are two USE options: | ||
+ | |||
+ | *<span style="color: red;">wheres</span> - A comma delimited list of boolean expressions that must all be true on a source table's row in order for it to be projected into the target table: | ||
+ | **''expression_on_sourceTableColumns,[ expression_on_sourceTableColumns ...]'' | ||
+ | |||
+ | *<span style="color: red;">selects</span> - A comma delimited list of expressions on how to populate target columns from source columns: | ||
+ | **''targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]'' | ||
+ | |||
+ | *<span style="color: red;">allowExternalUpdates</span> - Optional. Value is either true or false (false by default). If true, then other processes (i.e triggers, UPDATEs) are allowed to perform UPDATEs on the target table. Please use precaution when using this feature, since updating cells controlled by the aggregate trigger will result into an undesirable state. | ||
+ | |||
+ | There following rules apply for the <span style="color: red;">wheres</span> and <span style="color: red;">selects</span> use options (important when there are multiple source tables): | ||
+ | *Using the form ''tablename.columnname'' will isolate the expression to only apply to the specified table. | ||
+ | *Using the form ''columnname'' means that the expression will apply to all tables with the supplied ''columnname''. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER</span> <span style="font-family: courier new; color: blue;">trigger_name</span> <span style="font-family: courier new; color: red;">OFTYPE PROJECTION ON</span> <span style="font-family: courier new; color: blue;">source_table_name</span><span style="font-family: courier new; color: grey;">[,</span><span style="font-family: courier new; color: blue;"> source_table_name</span><span style="font-family: courier new; color: grey;">...], </span><span style="font-family: courier new; color: blue;">target_table_name</span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">]</span> <span style="font-family: courier new; color: red;">USE wheres="</span><span style="font-family: courier new; ">list of where expressions</span><span style="font-family: courier new; color: red;">" selects="</span><span style="font-family: courier new; ">list of assignment expressions</span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''trigger_name''' - name of the trigger to create, must be unique within the database''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''source_table_name(s)''' - name of the tables that will cause the trigger to execute''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''target_table_name''' - name of the table that will be updated by the trigger''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''priority''' - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table''</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Lets assume the three tables exist:</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE MissingRegion(account String, value double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//The below trigger will build a realtime view of accounts that have a null region:</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//(try inserting into the Accounts1 table and then take a look at the MissingRegions table)</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MyTrigger OFTYPE PROJECTION ON </span> <span style="font-family: courier new; color: blue;">Accounts1,Accounts2,MissingRegion USE wheres="region==null" selects="account=account,value=qty * px";</span> | ||
+ | |||
+ | == Join == | ||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER mytrigger OFTYPE JOIN ON leftTable,rightTable,targetTable USE ...</span> | ||
+ | |||
+ | '''Overview''' | ||
+ | |||
+ | This command allows for realtime joining across two tables. | ||
+ | |||
+ | *Changes to the leftTable or/or RightTable automatically update the targetTable, equivalent to using the ... JOIN ... ON ... syntax of the SELECT statement. See the ''Join Examples'' section for an illustration of the various types of joins | ||
+ | *Effectively, the target table can be considered a read only table, and can no longer be modified directly | ||
+ | *Multiple JOIN triggers can share the same source table | ||
+ | *Multiple JOIN triggers can not share the same target table. | ||
+ | *The creation of the trigger will clear out and rebuild the target table to properly reflect the JOIN of the left and right tables. | ||
+ | |||
+ | There are three <span style="color: red;">USE</span> options: | ||
+ | |||
+ | *<span style="color: red;">type</span> - How to join the left and right tables. Supported types include: <span style="color: red;">''LEFT''</span>, <span style="color: red;">''RIGHT''</span>, <span style="color: red;">''INNER''</span>, <span style="color: red;">''OUTER''</span>, <span style="color: red;">''LEFT ONLY''</span>, <span style="color: red;">''RIGHT ONLY''</span>, <span style="color: red;">''OUTER ONLY''</span>. (See Join Examples section for details on the behavior of the various configurations). | ||
+ | *<span style="color: red;">on</span> - An expression for how to relate the two tables in the form: | ||
+ | **''"leftColumn == rightColumn [ && leftColumn == rightColumn ... ]"'' | ||
+ | *<span style="color: red;">selects</span> - A comma delimited list of expressions on how to populate target columns from columns of the left and right tables. | ||
+ | **''targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]'' | ||
+ | |||
+ | The following rules apply for the <span style="color: red;">wheres</span> and <span style="color: red;">selects</span> use options: | ||
+ | |||
+ | *Using the form ''tablename.columnname'' will explicitly determine which table (left or right) is to be referenced. | ||
+ | *Using the form ''columnname'' means that the column name must be unique to either the left or right table. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER </span> <span style="font-family: courier new; color: blue;">trigger_name </span> <span style="font-family: courier new; color: red;">OFTYPE JOIN ON </span> <span style="font-family: courier new; color: blue;">left_table_name</span><span style="font-family: courier new; color: grey;">, </span><span style="font-family: courier new; color: blue;">right_table_name</span><span style="font-family: courier new; color: grey;">, </span><span style="font-family: courier new; color: blue;">target_table_name </span><span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">'']''</span> <span style="font-family: courier new; color: red;">USE type="</span><span style="font-family: courier new; ">''join type''</span><span style="font-family: courier new; color: red;">" </span> <span style="font-family: courier new; color: red;">on="</span><span style="font-family: courier new; ">''cross table comparison expression''</span><span style="font-family: courier new; color: red;">" selects="</span><span style="font-family: courier new; ">''list of assignment expressions''</span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''trigger_name''' - name of the trigger to create, must be unique within the database''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''left_table_name''' - name of the left table to join against''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''right_table_name''' - name of the right table to join against''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''target_table_name''' - name of the table that will be updated by the trigger''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''priority''' - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table''</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Lets assume the three tables exist:</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE UnmatchedAccounts(account1 String, account2 String);</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//The below trigger will build a realtime view of accounts that only in the Accounts1 table</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//or only in the Accounts2 table. (see OUTER ONLY for details)</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//(try inserting into the Accounts1 table and then take a look at the UnmatchedAccounts table)</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TRIGGER MyTrigger OFTYPE JOIN ON Accounts1,Accounts2, UnmatchedAccounts USE type="OUTER ONLY" </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">on="Accounts1.account==Accounts2.account" selects="account1=Accounts1.account, account2=Accounts2.account";</span> | ||
+ | |||
+ | == Decorate == | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER mytrigger OFTYPE DECORATE ON sourceTable,targetTable USE...</span> | ||
+ | |||
+ | '''Overview''' | ||
+ | |||
+ | This trigger is used to automatically decorate (update) a target table based on changes from a source table | ||
+ | |||
+ | *When a row is updated/inserted in the source table all matching rows (based on the ON clause) in the target table are updated (based on the SELECTS clause). | ||
+ | *Multiple DECORATE triggers can share the same source table. | ||
+ | *Multiple DECORATE triggers can share the same target table. | ||
+ | *The creation of the trigger will immediately update the target table based on source table values. | ||
+ | *When a row is inserted into the target table the last known matching values (if any) from the source table will be used to immediately update the new row. | ||
+ | *Deletes from the source table will not affect the target table. | ||
+ | |||
+ | There are three <span style="color: red;">USE</span> options: | ||
+ | |||
+ | *on - An expression for how to relate the two tables in the form: "leftColumn == rightColumn [ && leftColumn == rightColumn ... ]" | ||
+ | *selects - A comma delimited list of expressions on how to populate target columns from source columns. Form is: targetTableColumn = sourceTableColumn [,targetTableColumn = sourceTableColumns] | ||
+ | *keysChange- Either true or false. Default is false. If it's expected that columns participating in the ON clause can change, then set to true. Note that setting to true adds additional overhead. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER</span> <span style="font-family: courier new; color: blue;">trigger_name</span> <span style="font-family: courier new; color: red;">OFTYPE DECORATE</span> <span style="font-family: courier new; color: red;">ON</span> <span style="font-family: courier new; color: blue;">source_table_name, target_table_name</span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">]</span> <span style="font-family: courier new; color: red;">USE on="</span><span style="font-family: courier new; ">cross table comparison expression</span><span style="font-family: courier new; color: red;">" selects="</span><span style="font-family: courier new; ">list of assignment expressions</span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="color: grey;">trigger_name - name of the trigger to create, must be unique within the database</span> | ||
+ | |||
+ | <span style="color: grey;">source_table_name - name of the table to listen for updates from </span> | ||
+ | |||
+ | <span style="color: grey;">target_table_name - name of the table to update on changes </span> | ||
+ | |||
+ | <span style="color: grey;">priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Let's assume the two tables exist: | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create public table MktData(sym String,price double); create public table Orders(id String,sym String,price double); </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//When the MktData is updated, all Orders with the same symbol will now have the price updated | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create trigger tt oftype decorate on MktData,Orders use on="Orders.sym==MktData.sym" selects="price=price"</span> | ||
+ | |||
+ | == Relay == | ||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER relayTrigger OFTYPE RELAY ON sourceTable USE...</span> | ||
+ | |||
+ | '''Overview'''<br> | ||
+ | This command allows messages to be sent through the relay when (a) specific action(s) (insert, update or delete) occurs from the source table. | ||
+ | |||
+ | ''-- OPTIONS FOR CONNECTING TO THE RELAY --''<br> | ||
+ | Required: '''host, port, login'''<br> | ||
+ | Optional: '''keystoreFile,keystorePass'''<br> | ||
+ | <br> | ||
+ | |||
+ | ''-- OTHER OPTIONS (all optional) --''<br> | ||
+ | '''target''' - targetTable<br> | ||
+ | '''derivedValues''' - key=expression, key=expression, ... syntax<br> | ||
+ | '''inserts,updates,deletes''' - key,key,key,... syntax specifying which columns (or derived values) are sent on each event type. <br> | ||
+ | '''where''' - conditional statement which messages are to be sent to the target table<br> | ||
+ | <br> | ||
+ | See the Options description for more details on how to use the options<br> | ||
+ | |||
+ | '''OPTIONS Description''' | ||
+ | |||
+ | * <span style="color: red;">host</span> - hostname of the relay instance | ||
+ | * <span style="color: red;">port</span> - port for the relay instance defined by the property ami.port | ||
+ | * <span style="color: red;">login</span> - the unique id to identify the process/application See the login command: | ||
+ | https://docs.3forge.com/mediawiki/Realtime_Messaging_API#Outbound_Instruction_Type_-_Login_.28L.29 | ||
+ | * <span style="color: red;">keystoreFile</span> - location of a keystore file | ||
+ | |||
+ | * <span style="color: red;">keystorePass</span> - the keystore password, this will be encrypted using the strEncrypt method first | ||
+ | * <span style="color: red;">target</span> - the name of the target table, if not defined assumes the same name as the source | ||
+ | * <span style="color: red;">derivedValues</span> - key=expression,...[key=expression] pattern to map source columns to target columns, if the option is omitted, all source columns will map to the target columns of the same given name, if target columns are omitted, it will map the target column to a source column of the same given name | ||
+ | * <span style="color: red;">inserts</span> - comma delimited list of target columns to be sent on an onInserted event on the source table, if your target table has a unique constraint, in most cases you will want to add that column(s) to this list | ||
+ | * <span style="color: red;">updates</span> - comma delimited list of target columns to be sent on an onUpdated event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list | ||
+ | |||
+ | * <span style="color: red;">deletes</span> - comma delimited list of target columns to be sent on an onDeleted event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list | ||
+ | |||
+ | * <span style="color: red;">where</span> - a conditional statement which needs to evaluate to a boolean expression on the source rows, filters what messages should be sent to the target table, false indicates the message will be skipped. | ||
+ | <br> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TRIGGER </span> <span style="font-family: courier new; color: blue;"> trigger_name </span> <span style="font-family: courier new; color: red;">OFTYPE RELAY ON</span> <span style="font-family: courier new; color: blue;"> source_table_name </span> <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY</span> <span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">]</span> <span style="font-family: courier new; color: red;">USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">host="</span> ''<span style="font-family: courier new; color: grey;">hostname of the relay instance | ||
+ | </span>''<span style="font-family: courier new; color: red;">"</span>''<span style="font-family: courier new; color: grey;">]</span>'' | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">port="</span><span style="font-family: courier new; color: grey;">''port for the relay instance defined by the property ami.port''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">login="</span><span style="font-family: courier new; color: grey;">''the unique id to identify the process/application See the login command:''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">target="</span><span style="font-family: courier new; color: grey;">''the name of the target table, if not defined assumes the same name as the source | ||
+ | ''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">derivedValues="</span><span style="font-family: courier new; color: grey;">''key=expression,...[key=expression] pattern to map source columns to target columns, if the option is omitted, all source columns will map to the target columns of the same given name, if target columns are omitted, it will map the target column to a source column of the same given name | ||
+ | ''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">inserts="</span><span style="font-family: courier new; color: grey;">''comma delimited list of target columns to be sent on an onInserted event on the source table, if your target table has a unique constraint, in most cases you will want to add that column(s) to this list | ||
+ | ''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">updates="</span><span style="font-family: courier new; color: grey;">''comma delimited list of target columns to be sent on an onUpdated event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">deletes="</span><span style="font-family: courier new; color: grey;">''comma delimited list of target columns to be sent on an onDeleted event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list | ||
+ | ''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">where="</span><span style="font-family: courier new; color: grey;">''a conditional statement which needs to evaluate to a boolean expression on the source rows, filters what messages should be sent to the target table, false indicates the message will be skipped''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | * Note inserts,updates and deletes options define what columns will be added to a relay message | ||
+ | * Note that if the derivedValues,inserts,updates or deletes options are omitted, all values are sent. | ||
+ | * Note if the inserts,updates or deletes options are set to "" then the event type is skipped. | ||
+ | * Note the trigger uses AmiClient to connect to the ami.port if it's unable to connect it will drop messages, it is configured to auto reconnect | ||
+ | |||
+ | '''Example'''<br> | ||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE orders1(id Integer,date Integer,name String,price Double) USE PersistEngine="FAST";<br> | ||
+ | <span style="font-family: courier new; color: blue;">CREATE INDEX orders1_idx2 ON orders1(date HASH) USE Constraint="NONE";<br> | ||
+ | <span style="font-family: courier new; color: blue;">CREATE INDEX orders1_idx1 ON orders1(id HASH) USE Constraint="PRIMARY";<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PUBLIC TABLE orders2(id Integer,date Integer,name String,price Double) USE PersistEngine="FAST";<br> | ||
+ | <span style="font-family: courier new; color: blue;">CREATE INDEX orders2_idx2 ON orders2(date HASH) USE Constraint="NONE";<br> | ||
+ | <span style="font-family: courier new; color: blue;">CREATE INDEX orders2_idx1 ON orders2(id HASH) USE Constraint="PRIMARY";<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">create trigger relayTrigger oftype relay on orders1 use host="localhost" port="4589" login="demo" target="orders2" derivedValues="id=id,date=date,name=name,price=price" updates="id,name" where="price>100.0";<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">insert into orders1 values(2,2000,"hello", 50.75);<br> | ||
+ | <span style="font-family: courier new; color: blue;">insert into orders1 values(3,2000,"hello", 50.75);<br> | ||
+ | <span style="font-family: courier new; color: blue;">insert into orders1 values(4,2000,"hello", 50.75);<br> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">update orders1 set name="test",price=99.9 where id == 3;<br> | ||
+ | <span style="font-family: courier new; color: gray;">''Results:''<br> | ||
+ | <span style="font-family: courier new; color: brown;">''select * from orders1;select * from orders2;''<br> | ||
+ | [[File:RelayTriggerResult.png]] | ||
+ | |||
+ | == DROP TRIGGER == | ||
+ | '''Overview''' | ||
+ | |||
+ | This command permanently drops one or more triggers. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DROP TRIGGER</span> <span style="font-family: courier new; color: blue;">trigger_name</span> ''<span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: grey;">, trigger_name</span> <span style="font-family: courier new; color: grey;">...]</span>'' | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will drop the trigger MyTrigger from the database. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">DROP TRIGGER MyTrigger</span> | ||
+ | |||
+ | == Triggers Action Guide == | ||
+ | AMISCRIPT type triggers can have up to 5 different trigger scripts, '''onInserting''', '''onInserted''', '''onUpdating''', '''onUpdated''', and '''ondDeleting'''. When running a SQL command against a table with an AMISCRIPT trigger, some of these will run. Note that they run in the order displayed below from '''onInserting''' to '''onDeleting'''. The table below shows which script will run depending on the SQL command, whether the command changes anything, and whether there is a primary index on the table. We will be using the table '''''ORDERS(OrderID String, Quantity Integer, Price double)''''' with a primary index '''''IDX on ORDERS (OrderID Hash) use Constraint="Primary";'''''. | ||
+ | |||
+ | {| class="wikitable" | ||
+ | |+ | ||
+ | !Description | ||
+ | !Command | ||
+ | !onInserting | ||
+ | !onInserted | ||
+ | !onUpdating | ||
+ | !onUpdated | ||
+ | !onDeleting | ||
+ | |- | ||
+ | |New Row | ||
+ | |<syntaxhighlight lang="amiscript">insert into ORDERS values ("ord01", 100, 75.0);</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Existing Row No Changes | ||
+ | |<syntaxhighlight lang="amiscript">insert into ORDERS values ("ord01", 100, 75.0);</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Existing Row With Changes | ||
+ | |<syntaxhighlight lang="amiscript">insert into ORDERS values ("ord01", 100, 92.0);</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Update No Changes | ||
+ | |<syntaxhighlight lang="amiscript">update ORDERS set AvgPx=75 where OrderID == "ord01";</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Update With Changes | ||
+ | |<syntaxhighlight lang="amiscript">update ORDERS set AvgPx=100 where OrderID == "ord02";</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Delete Non-Existing Row | ||
+ | |<syntaxhighlight lang="amiscript">delete from ORDERS where OrderID=="ord05";</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Delete Row | ||
+ | |<syntaxhighlight lang="amiscript">delete from ORDERS where OrderID=="ord03";</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Sync No Changes | ||
+ | |<syntaxhighlight lang="amiscript">sync into ORDERS(OrderID, LeavesQty, AvgPx) from select "ord01", 100, 75.0;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Sync With Changes | ||
+ | |<syntaxhighlight lang="amiscript">sync into ORDERS from select "ord02", 125, 75.0;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Sync on (ID) No Changes | ||
+ | |<syntaxhighlight lang="amiscript">sync into ORDERS(OrderID, LeavesQty, AvgPx) on (OrderID) from select "ord01", 100, 75.0;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Sync on (ID) With Changes | ||
+ | |<syntaxhighlight lang="amiscript">sync into ORDERS(OrderID, LeavesQty, AvgPx) on (OrderID) from select "ord02", 130, 75.0;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Truncate/Delete from | ||
+ | |<syntaxhighlight lang="amiscript">truncate ORDERS;/Delete from ORDER;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |- | ||
+ | |Alter table Add Column(s) | ||
+ | |<syntaxhighlight lang="amiscript">ALTER TABLE ORDERS ADD Sym String;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Alter table With Default Values | ||
+ | |<syntaxhighlight lang="amiscript">ALTER TABLE ORDERS ADD Sym="ABC";</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: green;">Yes</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Alter table Drop Column(s) | ||
+ | |<syntaxhighlight lang="amiscript">ALTER TABLE ORDERS DROP AvgPx;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Rename Column | ||
+ | |<syntaxhighlight lang="amiscript">ALTER TABLE ORDERS RENAME LeavesQty To LeavesQuantity;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Modify Column Data Type | ||
+ | |<syntaxhighlight lang="amiscript">ALTER TABLE ORDERS MODIFY AvgPx AS AvgPrice Int;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |- | ||
+ | |Drop Table | ||
+ | |<syntaxhighlight lang="amiscript">Drop Table ORDERS;</syntaxhighlight> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |<span style="font-family: courier new; color: red;">No</span> | ||
+ | |} | ||
+ | |||
+ | Side Note:<br> | ||
+ | 1. Mutating the row: when set canMutateRow="true", Mutating the values in onInserted, onUpdated or onDeleting will not change the underlying value or row.<br> | ||
+ | 2. Trigger Event Return False: If you return '''false''' in the triggerEvents: onInserting, onUpdating and onDeleting, it will prevent the row from being inserted, updated or deleting and will also prevent further trigger events<br> | ||
+ | 3. Return False with a Sync: If you do a sync command where the onDeleting trigger event returns false, onDeleting will fire but no deletes will occur and will be followed by onInserting, onUpdating and onUpdated<br> | ||
+ | 4. Return False Sync without Primary Key:If you didn't have a primary key, that same action previously will insert another row, keeping the original and will also fire the onDeleting (with no deletes), onInserting and onInserted events.<br> | ||
+ | 5. Changed vs Unchanged Row: For the onUpdating and onUpdated trigger events if the row hasn't changed, the trigger wont fire. The same hold true when you mutate the row back to it's original values, the next trigger event wont fire.<br> | ||
=Realtime Timers= | =Realtime Timers= | ||
+ | |||
+ | == CREATE TIMER == | ||
+ | |||
+ | '''Overview''' | ||
+ | |||
+ | This command allows you to add script that will be executed on a periodic basis. A special variable _time will be available as a local variable to the executing script | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE TIMER [IF NOT EXISTS] </span><span style="font-family: courier new; color: blue;">timer_name </span><span style="font-family: courier new; color: red;">OFTYPE AMISCRIPT ON </span><span style="font-family: courier new; color: grey;">"</span><span style="font-family: courier new; color: blue;">schedule</span><span style="font-family: courier new; color: grey;">" </span><span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">PRIORITY </span><span style="font-family: courier new; color: blue;">priority</span><span style="font-family: courier new; color: grey;">] </span><span style="font-family: courier new; color: red;">USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">script="</span><span style="font-family: courier new; color: grey;">''AmiScript to run when timer is executed''</span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">logging="</span><span style="font-family: courier new; color: grey;">set the logging level when the timer gets called</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | 1. off (default): no logging. | ||
+ | 2. on: logs the time when the timer is called and when it completes. | ||
+ | 3. verbose: equivalent of using '''show_plan=ON''' in AMIDB. Logs the time that a timer starts and finishes and also each query step. | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">vars="</span><span style="font-family: courier new; color: grey;">''Variables shared by the timer, a comma delimited list of type varname''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">onStartupScript="</span><span style="font-family: courier new; color: grey;">''AmiScript to run when the timer is created''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">timeout="</span><span style="font-family: courier new; color: grey;">''Timeout in milliseconds, default is 100000 (100 seconds)''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">limit="</span><span style="font-family: courier new; color: grey;">''Row limit for queries, default is 10000''</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">'']''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''timer_name''' - name of the timer to create, each timer's name must be unique within the database''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''priority''' - a number, triggers with lowest value are executed first. Only considered when two or more timers have the same exact scheduled time.''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''Schedule''' - either:''</span> | ||
+ | |||
+ | *<span style="font-family: courier new; color: grey;">A positive number defining the period in milliseconds between timer executions.</span> | ||
+ | |||
+ | *<span style="font-family: courier new; color: grey;">Empty string ("") to never run timer, useful for timers that should just run at startup, see onStartupScript.</span> | ||
+ | |||
+ | *<span style="font-family: courier new; color: grey;">Crontab style entry declaring the schedule of when the timer should be execute:</span> | ||
+ | [[File:CreateTimerClause.Crontab2.jpg]] | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | In these examples assume we have the tables A(a boolean) and table B(time utc, cnt int). These timers, when executed, will insert a row into table B with the count of records in A. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Executes every 500 milliseconds</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TIMER t1 OFTYPE AMISCRIPT ON "500" USE script="int cnt; cnt =select count(*) from A; INSERT INTO b(_time,cnt)"</span> | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Executes every second, on the second</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TIMER t2 OFTYPE AMISCRIPT ON "* * * * * * UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"</span> | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Executes every fifth minute</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TIMER t3 OFTYPE AMISCRIPT ON "0 */5 * * * * UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"</span> | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Executes on the first second of every hour Monday through friday</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TIMER t4 OFTYPE AMISCRIPT ON "0 0 * * * MON-FRI UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"</span> | ||
+ | |||
+ | |||
+ | <span style="font-family: courier new; color: blue;">//Execute every Monday at 9:30 eastern standard time</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE TIMER t5 OFTYPE AMISCRIPT ON "0 30 9 * * MON EST5EDT" USE script=" int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"</span> | ||
+ | |||
+ | == DROP TIMER == | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DROP TIMER [IF EXISTS] </span><span style="font-family: courier new; color: blue;">timer_name</span></span>''<span style="font-family: courier new; color: grey;">[,timer_name,...]</span>'' | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will drop the timer MyTimer from the database | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">DROP TIMER MyTimer</span> | ||
+ | |||
+ | == Timer - Advanced == | ||
+ | The Actual timer execution period might differ from the one we specified. Consider the following cases:<br> | ||
+ | There are two timers that are scheduled to run on 5 second periods but take at least 30 seconds to finish.<br> | ||
+ | These two commands query the database from MySQL, and are considered deferred statements, where the timer will wait for the last execution to finish before running the next job.<br> | ||
+ | <syntaxhighlight lang="SQL">create timer timerms oftype amiscript on "5000" use script="create table x as use ds=world EXECUTE SELECT sleep(30) as x;"; | ||
+ | create timer timercron oftype amiscript on "*/5 * * * * * UTC" use script="create table x as use ds=world EXECUTE SELECT sleep(30) as x;";</syntaxhighlight> | ||
+ | (1). If the timer has a specified timeout, the timer will execute again once the timeout has been reached.<br> | ||
+ | (2). For the timer with the Cron schedule, the timer will wait until the next valid time to execute. <br> | ||
+ | (3). In both cases, the timers will either execute every 30 seconds or wait until the previous request finishes executing or has timedout.<br> | ||
=Realtime Procedures= | =Realtime Procedures= | ||
+ | == CREATE PROCEDURE == | ||
+ | '''Overview''' | ||
+ | |||
+ | This command allows you to create stored procedures that can be called via amiscript to execute a sequence of statements and return a value. Arguments can be supplied when calling the stored procedure (see <span style="font-family: courier new; color: red;">CALL PROCEDURE</span>): | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CREATE PROCEDURE [IF NOT EXISTS] </span><span style="font-family: courier new; color: grey;">procedure_name </span><span style="font-family: courier new; color: red;">OFTYPE AMISCRIPT USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">arguments="</span><span style="font-family: courier new; color: grey;">type name, type name ... </span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">script="</span><span style="font-family: courier new; color: grey;">AmiScript to run when proc is called</span><span style="font-family: courier new; color: red;">"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">logging="</span><span style="font-family: courier new; color: grey;">set the logging level when the procedure gets called</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">]</span> | ||
+ | 1. off/quiet (default): no logging. | ||
+ | 2. on: logs the time when the procedure is called and when it completes. | ||
+ | 3. verbose: equivalent of using '''show_plan=ON''' in AMIDB. Logs the time that the procedure starts and finishes and also each query step. | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">vars="</span><span style="font-family: courier new; color: grey;">Variables shared by the procedure, a comma delimited list of type varname</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">]</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">onStartupScript="</span><span style="font-family: courier new; color: grey;">AmiScript to run When the procedure is created</span><span style="font-family: courier new; color: red;">"</span><span style="font-family: courier new; color: grey;">]</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">procedure_name - name of the procedure to be created, each procedure's name must be unique within the database</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | In this example lets assume we have a table MyTable(id int,price double). We will make a simple procedure that inserts a row into that table and deletes any other rows with an equal or lesser price. (See <span style="font-family: courier new; color: red;">CALL PROCEDURE</span> clause for example of calling this procedure) | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CREATE PROCEDURE MyProcedure OFTYPE AMISCRIPT USE</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">arguments="int _id,double _price"</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">script="DELETE FROM MyTable WHERE price<_price;INSERT INTO </span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">MyTable(id,price) VALUES(_id,_price)"</span> | ||
+ | |||
+ | == CALL == | ||
+ | '''Overview''' | ||
+ | |||
+ | This command allows you to execute stored procedures, typically declared using the <span style="font-family: courier new; color: red;">CREATE PROCEDURE</span> clause. When calling procedures you must supply the appropriate arguments expected for the declared procedure. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">CALL </span><span style="font-family: courier new; color: blue;">proc_name </span><span style="font-family: courier new; color: red;">(</span><span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: blue;">arg1 </span><span style="font-family: courier new; color: grey;">[,</span><span style="font-family: courier new; color: blue;">arg2 </span><span style="font-family: courier new; color: grey;">...]]</span><span style="font-family: courier new; color: red;">)</span> | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | In this example we will call the procedure declared in the <span style="font-family: courier new; color: red;">CREATE PROCEDURE</span> clause. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">CALL MyProcedure(17,45.2)</span> | ||
+ | |||
+ | == DROP PROCEDURE == | ||
+ | <span style="font-family: courier new; color: red;">DROP PROCEDURE [IF EXISTS] </span><span style="font-family: courier new; color: blue;">proc_name</span></span><span style="font-family: courier new; color: grey;">''[,proc_name</span>...]'' | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will drop the procedure MyProcedure from the database | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">DROP PROCEDURE MyProcedure</span> | ||
+ | |||
+ | =Using Methods in the AMI Database= | ||
+ | |||
+ | AMIDB methods can be used anywhere within the console, and on top of other functionalities like triggers, procedures and timers. | ||
+ | |||
+ | == SHOW METHODS == | ||
+ | |||
+ | To view all available methods, use: | ||
+ | |||
+ | <span style="font-family: courier;color: blue;">show methods;</span> | ||
+ | |||
+ | [[File:Using Methods in the AMI Database.01.jpg]] | ||
+ | |||
+ | == CREATE METHOD == | ||
+ | |||
+ | To create a custom method, the syntax is as follows: | ||
+ | |||
+ | <span style="font-family: courier;color: blue;">CREATE METHOD return_type method_name (data_type arg1, data_type arg2) { //code goes here; return result; }</span> | ||
+ | |||
+ | For example, to create a simple method (called foo) to return the sum of two numbers (of Integer type): | ||
+ | |||
+ | <span style="font-family: courier;color: blue;">CREATE METHOD int foo (int a, int b) { return a + b; };</span> | ||
+ | |||
+ | This method can now be called/used inside of timers/procedures/triggers. | ||
+ | E.g. for a procedure | ||
+ | |||
+ | <span style="font-family: courier;color: blue;">CREATE PROCEDURE myProcedure OFTYPE AMISCRIPT USE arguments="int a, int b" script="int i = foo(a,b); INSERT INTO table_name VALUES (i);"</span> | ||
+ | |||
+ | == DROP METHOD == | ||
+ | |||
+ | To drop our newly created method foo: | ||
+ | |||
+ | <span style="font-family: courier;color: blue;">DROP METHOD foo(int a, int b);</span> | ||
+ | |||
+ | ==Method overloading== | ||
+ | Like in Java, method overloading is supported by AMI, which allows for methods with the same name but different parameters to be created. | ||
+ | |||
+ | For example, if we currently have <span style="font-family: courier;color: blue;">int foo(int a, int b)</span>, which returns the sum of two Integers, we can overload the method to return the sum of two doubles as well, with: | ||
+ | <span style="font-family: courier;color: blue;">CREATE METHOD double foo (double a, double b) { return a + b; };</span> | ||
+ | |||
+ | AMI will auto-resolve the method to use based on the parameters used in the method call. | ||
+ | |||
+ | [[File:Using Methods in the AMI Database.02.jpg]] | ||
+ | |||
+ | '''NOTE''': Multiple methods can have the same name as long as the number and/or type of arguments are different. | ||
=Realtime Tools= | =Realtime Tools= | ||
+ | == SHOW == | ||
+ | '''Overview''' | ||
+ | |||
+ | Provides a table listing all records and relevant Metadata of a particular type with optional filtering and sorting. Note, for tables, only PUBLIC tables are listed. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">SHOW </span><span style="font-family: courier new; color: blue;">object_type </span><span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">WHERE </span><span style="font-family: courier new; color: blue;">where_expr </span><span style="font-family: courier new; color: grey;">'']'' ''[''</span><span style="font-family: courier new; color: red;">ORDER BY </span><span style="font-family: courier new; color: purple;">expr </span><span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">ASC</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">DESC</span><span style="font-family: courier new; color: grey;">'']'' ''['',</span><span style="font-family: courier new; color: purple;"> expr </span><span style="font-family: courier new; color: grey;">''[''</span><span style="font-family: courier new; color: red;">ASC</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">DESC</span><span style="font-family: courier new; color: grey;">'']'' ''...]]''</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">object_type</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''''Either TABLES, COLUMNS, COMMANDS, DATASOURCES, DATASOURCE_TYPES, PROCEDURES, | ||
+ | PROPERTIES, RESOURCES, TRIGGERS, TIMERS, CONNECTIONS, RELAYS, PLUGINS, SESSIONS, VARS, PROCESSES, METHODS</span> | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will return all tables whose name contains "data" and will order the results by the number of columns in the table. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SHOW TABLES WHERE TableName =~ "data" ORDER BY ColumnsCount</span> | ||
+ | |||
+ | === SHOW TABLE === | ||
+ | '''Overview''' | ||
+ | |||
+ | Provides a table listing all columns and relevant Metadata of a particular table with optional filtering and sorting. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">SHOW TABLE </span><span style="font-family: courier new; color: blue;">table_expr </span><span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">WHERE </span><span style="font-family: courier new; color: blue;">where_expr </span><span style="font-family: courier new; color: grey;">] [</span><span style="font-family: courier new; color: red;">ORDER BY </span><span style="font-family: courier new; color: purple;">expr </span><span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">ASC</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">DESC</span><span style="font-family: courier new; color: grey;">] </span><span style="font-family: courier new; color: grey;">[, </span><span style="font-family: courier new; color: purple;">expr </span><span style="font-family: courier new; color: grey;">[</span><span style="font-family: courier new; color: red;">ASC</span><span style="font-family: courier new; color: grey;">|</span><span style="font-family: courier new; color: red;">DESC</span><span style="font-family: courier new; color: grey;">] </span><span style="font-family: courier new; color: grey;"> ...]]</span> | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will return all columns for the myorders table, alphabetized by name. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">SHOW TABLE myorders order by ColumnName</span> | ||
+ | |||
+ | == DESCRIBE == | ||
+ | '''Overview''' | ||
+ | |||
+ | Provides a table containing with an "SQL" column containing the statement(s) necessary to reconstruct the supplied table,trigger,timer,procedure or index. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DESCRIBE TABLE </span><span style="font-family: courier new; color: blue;">table_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DESCRIBE TRIGGER </span><span style="font-family: courier new; color: blue;">trigger_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DESCRIBE TIMER </span><span style="font-family: courier new; color: blue;">timer_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DESCRIBE PROCEDURE </span><span style="font-family: courier new; color: blue;">procedure_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DESCRIBE INDEX </span><span style="font-family: courier new; color: blue;">index_name </span><span style="font-family: courier new; color: red;">ON </span><span style="font-family: courier new; color: blue;">table_name</span> | ||
+ | |||
+ | '''Example''' | ||
+ | |||
+ | This example will return the create statement that can be used to create the __COLUMN table and its pk index. | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">DESCRIBE TABLE __COLUMN;</span> | ||
+ | |||
+ | == DISABLE and ENABLE== | ||
+ | '''Overview''' | ||
+ | |||
+ | Used to disable/enable triggers and timers by name. Disabled triggers and timers will not get executed when they otherwise would. By default, when triggers and timers are created they are enabled. Check the enabled/disabled status using <span style="font-family: courier new; color: red;">SHOW TRIGGERS</span> and <span style="font-family: courier new; color: red;">SHOW TIMERS</span>. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DISABLE TRIGGER </span><span style="font-family: courier new; color: blue;">trigger_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">ENABLE TRIGGER </span><span style="font-family: courier new; color: blue;">trigger_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">DISABLE TIMER </span><span style="font-family: courier new; color: blue;">trigger_name</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">ENABLE TIMER </span><span style="font-family: courier new; color: blue;">trigger_name</span> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | This example will disable the trigger named my_trigger and enable the timer my_timer | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">DISABLE TRIGGER my_trigger;</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">ENABLE TIMER my_timer;</span> | ||
+ | |||
+ | == DIAGNOSE == | ||
+ | '''Overview''' | ||
+ | |||
+ | Returns a table providing details on the approximate memory footprint of tables, columns and indexes. | ||
+ | |||
+ | <syntaxhighlight lang="amiscript" line="1"> | ||
+ | DIAGNOSE TABLE table_name [,table_name ...] | ||
+ | DIAGNOSE COLUMN column_name on table_name [,column_name on table_name ...] | ||
+ | DIAGNOSE INDEX index_name ON table_name [,index_name on table_name ...] | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | '''Examples''' | ||
+ | |||
+ | This example will return the memory used by the __COLUMN table | ||
+ | |||
+ | <syntaxhighlight lang="amiscript" line="1"> | ||
+ | DIAGNOSE TABLE __COLUMN; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | == SETLOCAL == | ||
+ | '''Overview''' | ||
+ | |||
+ | Controls local variables for the command line session. These local variables dictate behavior of the command line interface. Run <span style="font-family: courier new; color: red;">SETLOCAL</span> to see the list of variables that can be changed. Note, this command cannot be run from the AMIDB Shell Tool. | ||
+ | |||
+ | <span style="font-family: courier new; color: red;">SETLOCAL </span><span style="font-family: courier new; color: blue;">varname = value</span> | ||
+ | |||
+ | '''Variables''' | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">limit</span> | ||
+ | |The default limit to apply to queries (similar to the limit field in the datamodel), -1 means no default limit | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">max_print_chars</span> | ||
+ | |Total number of characters to print to the console for a query result. This prevents from very large query results overwhelming terminal | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">multiline</span> | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"off"</span> then pressing enter will automatically execute the user entered | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"on"</span> then pressing enter will progress to a new line for additional input. Pressing enter twice in succession will execute the users entered text. | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">show_plan</span> | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"off"</span> the engine will not show the query plan, this is more efficient as some overhead is necessary to gather and display this importation. | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"on"</span> then the engine will produce verbose output on the steps and time taking for each step in the query. This can be useful for optimizing your queries. | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">timeout</span> | ||
+ | |Amount of time in milliseconds that the console will hang waiting for a response. It must be a positive number | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">unprintable_chars</span> | ||
+ | |Determines how to print unprintable ASCII characters, for example 0x01 (SOH). | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"marker"</span> then an upside down question mark (¿) is printed in place of any unprintable chars (default) | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"show"</span> the raw, unprintable, is sent to stdout. | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"hide"</span> then nothing is printed. | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"show_code"</span> then an upside down question mark (¿) is printed followed by the chars 4 digit hexcode is printed. For example, the SOH would be printed as: ¿0001 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">datetime_format</span> | ||
+ | |The format to use when printing UTC and UTCN columns. If set to empty string, the Raw long value (unix epoch) is printed. The default is ''YYYY-MM-DD'' ''HH:mm:ss.SSS.z'' | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">timezone</span> | ||
+ | |Works in conjunction with datetime_format, to determine local time for display. For example ''EST5EDT'' would be used for New York. Default is ''UTC'' | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">string_template</span> | ||
+ | |Should commands interpret string templates (default is off) | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"on"</span> then ${...} will be evaluated and replaced with the contents | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"off"</span> then ${...} will be treated as a literal. | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">password_encrypt</span> | ||
+ | |Should commands interpret string templates (default is off) | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"on"</span> then ''login'' command should be supplied with an encrypted password. The password will be decrypted using the key stored on the AMI server. Note, use strEncrypt(...) method to get the encrypted version of a plain text password. Ex: select strEncrypt("demopass"); | ||
+ | |- | ||
+ | | | ||
+ | |If set to <span style="font-family: courier new; color: blue;">"off"</span> then login command should be supplied with plain text password. | ||
+ | |} | ||
+ | '''Example''' | ||
+ | |||
+ | <span style="font-family: courier new; color: blue;">setlocal timeout=30000</span> | ||
+ | |||
+ | =System Tables= | ||
+ | ==__COLUMN== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TableName</span> | ||
+ | |Name of all the tables | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ColumnName</span> | ||
+ | |Name of columns within each table | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ColumnFormula</span> | ||
+ | |Formula associated with column | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DataType</span> | ||
+ | |Data type of column (String, Enum, Integer, etc.) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">NoNull</span> | ||
+ | |If column is Nullable | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Position</span> | ||
+ | |Position of column within the associated table | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |Which user defined (USER, AMI, SYSTEM) | ||
+ | |} | ||
+ | |||
+ | ==__COMMAND== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">CI </span> | ||
+ | |Connection ID of the connection that this command was registered using | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RI </span> | ||
+ | |Relay ID that this command was registered using | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ID </span> | ||
+ | |Purpose of the command (Cancel order command), not guaranteed unique | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">AR </span> | ||
+ | |Input arguments for form fields. ({"form":{"inputs":[{"label":"Symbol","required":true, "var":"symbol","type":"text"}]}}) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PR </span> | ||
+ | |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.) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">NA </span> | ||
+ | |Name of the command that appears on the GUI after the user right-clicks on a row (order.cancel) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">FL </span> | ||
+ | |Command filter. An expression that will determine which rows the command will be available at a panel level (panel.types=="Orders" or panel.types=="Executions") | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">WH </span> | ||
+ | |Conditional "where" clause. It determines which rows the command will be available at a row/node level (qty>0 && user.canbust=="true") | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">HP </span> | ||
+ | |Explanation/outcome of the command. (This command is used to cancel order(s)) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">SM </span> | ||
+ | |Constrains the number of rows that can be selected when running the command (1-10) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">AmiScript </span> | ||
+ | |AmiScript will reflect here, if any | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">N</span> | ||
+ | |Enabled where (expression). (E="Quantity==300"; the command will only be enabled where the Quantity = 300) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">L</span> | ||
+ | |Command level. Permissions Level of 0 means remove command, and any other number is used for entitlements as part of the AMI entitlement engine | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">F</span> | ||
+ | |Fields; returns the values of specified fields (price, status, level) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">I</span> | ||
+ | |Relay name with command name. (e.g. 29002751:CancelCmd) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">P</span> | ||
+ | |Login name used to access the realtime database ("demo") | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">V</span> | ||
+ | |Number of times a row is being updated | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">M</span> | ||
+ | |Command modified time in miliseconds | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">C</span> | ||
+ | |Command created time in miliseconds | ||
+ | |} | ||
+ | |||
+ | ==__CONNECTION== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">AI </span> | ||
+ | |Login ID associated with this connection (not guaranteed unique) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">EC</span> | ||
+ | |Connection/login attempt error count | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">CI</span> | ||
+ | |Connection ID of logged in user | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">MA</span> | ||
+ | |ID remote machine of logged in user | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">MC</span> | ||
+ | |Message count from current connection | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">O</span> | ||
+ | |Used to supply options about the current session’s connection. The following options are available and can be used in conjunction by comma delimiting | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">QUIET</span> - AMI will not send any information back to the client (statuses, etc). Note execute commands (E) will still be send to the client | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">LOG</span> - Force AMI relay to log data to / from this session (default file = AmiSession.log) | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">UNIX</span> - Force AMI to not send \r on messages | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">WINDOWS</span> - Force AMI to send \r on messages | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">TTY</span> - teletype terminal (for UNIX) is for interactively working with AMI backend | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PL</span> | ||
+ | |A fully qualified java class name to an AMI relay plugin. The class must implement the com.vortex.agent.AmiPlugin interface | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RI</span> | ||
+ | |The Relay which was used to run on (relay_0) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RP</span> | ||
+ | |Remote host port used | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RH</span> | ||
+ | |Remote host name, localhost, IP address | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">CT</span> | ||
+ | |Time of connection made | ||
+ | |} | ||
+ | |||
+ | ==__DATASOURCE== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">NM</span> | ||
+ | |Datasource name, self-specified | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">AD</span> | ||
+ | |Adapter type (RestAPI, MySQL) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">UR</span> | ||
+ | |Datasource URL (protocol//[hosts][/database][?properties], https://example.com/api/data) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">US</span> | ||
+ | |Username, credentials to access datasource URL | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PW</span> | ||
+ | |Unencrypted password, credentials to access datasource URL (Deprecated) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Password</span> | ||
+ | |Encrypted password, credentials to access datasource URL | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">OP</span> | ||
+ | |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 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RelayId</span> | ||
+ | |Relay to run on option in dropdown (relay_0) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PermittedOverrides</span> | ||
+ | |Combination of URL, USERNAME, PASSWORD, OPTIONS, RELAY | ||
+ | |} | ||
+ | |||
+ | ==__DATASOURCE_TYPE== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Description</span> | ||
+ | |Description of associated datasource (Shell Command, AMIDB, MySQL JDBC) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">I</span> | ||
+ | |Identifier of datasource type (SHELL, AMIDB, MYSQL) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ClassType</span> | ||
+ | |Class of plugin associated with datasource (com.f1.ami.center.ds.AmiShellDatasourcePlugin, com.f1.ami.center.ds.AmiAmiDbDatasourcePlugin, com.f1.ami.plugins.mysql.AmiMysqlDatasourcePlugin) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Icon</span> | ||
+ | |Icon (svg file) associated with the datasource | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Properties</span> | ||
+ | |Map of properties associated with the datasource | ||
+ | |} | ||
+ | |||
+ | ==__GUI== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Type</span> | ||
+ | |Types of GUI objects (TABLE, PROCEDURE, TRIGGER) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Name</span> | ||
+ | |Name of objects (table name, procedure name, trigger name) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">X</span> | ||
+ | |X coordinate position in the data modeler | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Y</span> | ||
+ | |Y coordinate position in the data modeler | ||
+ | |} | ||
+ | |||
+ | ==__INDEX== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">IndexName</span> | ||
+ | |Name of index (primary key, relay id) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TableName</span> | ||
+ | |Table associated with the index | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ColumnName</span> | ||
+ | |Column of table associated with the index | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">IndexType</span> | ||
+ | |Type of indexing (SORT, HASH) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">IndexPosition</span> | ||
+ | |Position of column in table associated with the index | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Constraint</span> | ||
+ | |Constraints of index (PRIMARY, NONE) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |Which user defined (USER, AMI, SYSTEM) | ||
+ | |} | ||
+ | |||
+ | ==__PLUGIN== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PluginName</span> | ||
+ | |Name of plugin (MYSQL, ORACLE, SSH, MSEXCEL) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PluginType</span> | ||
+ | |Plugin Type (DATASOURCE, PROCEDURE, TIMER, TRIGGER) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ClassType</span> | ||
+ | |Class of plugin (com.f1.ami.center.ds.AmiShellDatasourcePlugin, com.f1.ami.center.ds.AmiAmiDbDatasourcePlugin, com.f1.ami.plugins.mysql.AmiMysqlDatasourcePlugin) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Arguments</span> | ||
+ | |Arguments/Parameters used in conjunction with plugin | ||
+ | |} | ||
+ | |||
+ | ==__PROCEDURE== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ProcedureName</span> | ||
+ | |Name of procedure (__ADD_DATASOURCE, __REMOVE_DATASOURCE, __RESET_TIMER_STATS) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ProcedureType</span> | ||
+ | |Type of procedure (AMISCRIPT, __SYSTEM) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ReturnType</span> | ||
+ | |Return type after running procedure (Object, String, Integer) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Arguments</span> | ||
+ | |Arguments/Parameters used in conjunction with procedure | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Options</span> | ||
+ | |Additional options defined (scripts, arguments) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |Which user defined (USER, AMI, SYSTEM) | ||
+ | |} | ||
+ | |||
+ | ==__PROPERTY== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PropertyName</span> | ||
+ | |Name of property | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PropertyValue</span> | ||
+ | |Value of property | ||
+ | |} | ||
+ | |||
+ | ==__RELAY== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">MachineUid</span> | ||
+ | |Unique Machine ID of the host (UID_DESKTOP-RVO4948) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ProcessUid</span> | ||
+ | |System Generated Process Id (F1-Inw6XE0p8YaqdbREAp5MxN) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">StartTime</span> | ||
+ | |Starting time in UTC (2022-01-20 03:03:06.101 EST) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ServerPort</span> | ||
+ | |Connection port (3289) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RelayId</span> | ||
+ | |Relay ID (relay_0) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Hostname</span> | ||
+ | |Host name (DESKTOP-RVO4948) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ConnectTime</span> | ||
+ | |Connection time in UTC (2022-01-20 03:03:28.299 EST) | ||
+ | |} | ||
+ | |||
+ | ==__RESOURCE== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">I</span> | ||
+ | |Name of the image (3ForgeLogo.png) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ModifiedOn</span> | ||
+ | |Modified time in milliseconds | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Checksum</span> | ||
+ | |Unique checksum value used for validation | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">FileSize</span> | ||
+ | |File size in bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ImageWidth</span> | ||
+ | |Image width in pixels | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">ImageHeight</span> | ||
+ | |Image height in pixels | ||
+ | |} | ||
+ | |||
+ | ==__TABLE== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TableName</span> | ||
+ | |Name of table | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Broadcast</span> | ||
+ | |"true": Front end visualizations & external listeners will be notified as data is updated in the table | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">RefreshPeriodMs</span> | ||
+ | |"duration_millis": The period that the table will conflate and broadcast changes to front end at. For example, if a cells' value changes 10 times in one second and the refresh period is 500ms, then only ~2 updates will be broadcast out (the other 8 will be conflated) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">SourceTableNames</span> | ||
+ | |Name of the tables to listen for updates from (cause the trigger to execute) | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">PersistEngine</span> | ||
+ | |<span style="font-family: courier new; color: blue;">FAST</span>: The table will be persisted to disk using a fast, binary protocol. Additional options include: | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"persist_compact_percent" = ".95"</span> - This is the directory where the fast binary file will be saved/loaded from | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"persist_dir" = "/path/to/directory/for/storing/file"</span> - A number between 0 and 1 which says how much of the file should contain updates & deletes before it's compacted. Because the FAST table uses a journaling strategy, updates and deletes will get appended and can create large files. Setting a value of .95 would indicate that if the file becomes 95% updates and deletes then the file should be rebuilt | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">TEXT</span>: The table will be persisted to disk using a slow, but easy to read text file | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"persist_dir" = "/path/to/directory/for/storing/file"</span> - This is the directory where the .txt text file will be saved/loaded from | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">PersistEngine="Custom"</span> - A custom persist engine will be used (see AMI plugins documentation). Note, that PersistOptions="..." is available to pass options to the custom plugin | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">OnUndefColumn</span> | ||
+ | |Behaviour to take when a realtime Object record contains an undefined column | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"REJECT"</span> - The record will be rejected. This is the default | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"IGNORE"</span> - The record will be inserted, but the undefined values will be ignored | ||
+ | |- | ||
+ | | | ||
+ | |<span style="font-family: courier new; color: blue;">"ADD"</span> - The table will automatically have the column added | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |The ID of the owner | ||
+ | |} | ||
+ | |||
+ | ==__TIMER== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TimerName</span> | ||
+ | |Name of the Timer created | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TimerType</span> | ||
+ | |Either AMISCRIPT type or custom timer type created using timer plugin defined in the imdb schema | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Priority</span> | ||
+ | |A number that is set when two or more timers have the same exact scheduled time. The lowest value is executed first | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Scheule</span> | ||
+ | |A positive number defining the period in milliseconds between timer executions | ||
+ | |- | ||
+ | | | ||
+ | |Empty string ("") to never run timer, useful for timers that should just run at startup | ||
+ | |- | ||
+ | | | ||
+ | |Crontab style entry declaring the schedule of when the timer should be executed | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Options</span> | ||
+ | |AmiScript to run when timer is executed | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |The ID of the owner | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">LastRunTime</span> | ||
+ | |Timestamp when the timer is last ran | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">NextRunTime</span> | ||
+ | |Timestamp that the next timer would run | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Enabled</span> | ||
+ | |Is the timer currently active | ||
+ | |} | ||
+ | |||
+ | ==__TRIGGER== | ||
+ | {| class="wikitable" | ||
+ | !Variables | ||
+ | !Description | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TriggerName</span> | ||
+ | |Name of the trigger to create, must be unique within the database | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TableName</span> | ||
+ | |Name of the table(s) that will cause the trigger to execute | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">TriggerType</span> | ||
+ | |Either AMISCRIPT type or custom timer type created using trigger plugin defined in the imdb schema | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Priority</span> | ||
+ | |A number that is set when two or more timers have the same exact scheduled time. The lowest value is executed first | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Options</span> | ||
+ | |AmiScript to run when timer is executed. | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">DefinedBy</span> | ||
+ | |The ID of the owner | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: blue;">Enabled</span> | ||
+ | |Is the timer currently active | ||
+ | |} | ||
=Capacity Planning= | =Capacity Planning= | ||
+ | ==AMI In-memory Database Capacity Planning== | ||
+ | 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 the AMI in memory database, one should run this on each table and aggregate the results. | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''table_size_mb''' = '''row_cnt''' x (128 + '''data_size''' + '''idx_cnt''' * 100 + '''null_col_cnt''' * 1/8 ) / 1,048,576</span> | ||
+ | |||
+ | <span style="font-family: courier new; color: grey;">'''table_suggested_capacity_mb'''= '''total_size_mb''' * 2</span> | ||
+ | {| class="wikitable" | ||
+ | |<span style="font-family: courier new; color: grey;">'''table_size_mb'''</span> | ||
+ | |the total number meg you can expect the table to require | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: grey;">'''table_suggested_capacity_mb'''</span> | ||
+ | |3Forge Recommendation for hardware capacity planning | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: grey;">'''row_cnt'''</span> | ||
+ | |number of rows | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: grey;">'''idx_cnt'''</span> | ||
+ | |number of indexes on the table, composite indexes are larger | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: grey;">'''null_col_cnt'''</span> | ||
+ | |number of non-string columns that allow null | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: grey;">'''data_size'''</span> | ||
+ | |sum(column_type_sizes) See column types below | ||
+ | |} | ||
+ | {| class="wikitable" | ||
+ | |'''Column Type''' | ||
+ | |'''Data Size (bytes)''' | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">UTC,UTCN,Double,Long </span> | ||
+ | |8 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Int,Float</span> | ||
+ | |4 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Char, Short</span> | ||
+ | |2 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Byte</span> | ||
+ | |1 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Boolean</span> | ||
+ | |1/8 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Enum (legacy)</span> | ||
+ | |4 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String bitmap under 256 unique values</span> | ||
+ | |1 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String bitmap 256 to 65536 unique values</span> | ||
+ | |2 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String bitmap over 65536 unique values</span> | ||
+ | |3 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String Compact</span> | ||
+ | |String Size * 2 + 6 bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String Compact Ascii</span> | ||
+ | |String Size * 1 + 5 bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String</span> | ||
+ | |String Size * 2 + 64 bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Binary</span> | ||
+ | |Length in Bytes + 56 bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">String OnDisk</span> | ||
+ | |8 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">Binary OnDisk</span> | ||
+ | |8 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">BIGDECIMAL</span> | ||
+ | |68+size of number in bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">BIGINTEGER</span> | ||
+ | |64+size of number in bytes | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">COMPLEX</span> | ||
+ | |68 | ||
+ | |- | ||
+ | |<span style="font-family: courier new; color: red;">UUID</span> | ||
+ | |64 | ||
+ | |} | ||
+ | |||
+ | ==Capacity Planning Example== | ||
+ | '''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. | ||
+ | |||
+ | [[File:CapacityPlanningMath01.jpg]] | ||
+ | |||
+ | == 100,000,000 Executions & Associated Memory Usage == | ||
+ | [[File:CapacityPlanning01.png]] | ||
+ | |||
+ | == Large Capacity Planning Example == | ||
+ | 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. | ||
+ | |||
+ | [[File:CapacityPlanningMath02.jpg]] | ||
+ | |||
+ | [[File:CapacityPlanning02.png]] |
Latest revision as of 09:29, 9 January 2024
Goals
The 3Forge Realtime database builds on all of the features available in the AMI SQL language. The real-time datamodeling
- High Speed - 100k+ inserts per second, with 1 billion+ rows
- Persistent - Tables can be configured for long-term persistence
- Data Virtualization - Access external sources seamlessly
- Realtime Streaming - Chaining streams of updates into aggregations and other projections
- Source Synchronizing - Synchronizing in-memory tables based on external datasources
- Extendable - Create custom triggers, procedures, etc.
Broadly speaking, the logic steps involved in data-visualization are:
- Data is "streamed" into the AMI real-time database through a combination of methods
- High Speed Messaging
- JDBC
- Synchronizing from External Datasources
- User updates, from the front end visualization layer
- Data is fed through logical components which can aggregate, etc.
- Visualizations are built on the real-time tables and streams.
About Realtime (PUBLIC) tables
For the AMI Center, by default tables that are created are only visible to the current session and will be cleaned up when the connection is terminated. When a table is created with the PUBLIC directive (ex CREATE PUBLIC TABLE ...) the table will now be accessible to all sessions (external connections, triggers, etc) and front ends for real-time viewing. Public tables will continue to exist until they are explicitly dropped. Please see the reserved columns on public tables section for details on specialized columns that can be added.
Realtime Tables
CREATE PUBLIC TABLE
Overview
This command is used to create a new, empty (no rows) public table with a specified set of columns (names, types, no-null restriction). If another public table already exists with the tblname, the create table will fail.
At least one column must be specified, and each column must have a unique name. Columns with the nonull constraint cannot hold a null value. Updates and inserts in violation of the rule will fail.
The Following Use Options can be applied to a table:
- PersistEngine -
- "FAST": The table will be persisted to disk using a fast, binary protocol. Additional options include:
- "persist_dir" = "/path/to/directory/for/storing/file" - this is 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. Because the FAST table uses a journaling strategy, updates and deletes will get appended and can create large files. Setting a value of .95 would indicate that if the file becomes 95% updates and deletes then the file should be rebuilt
- "TEXT": The table will be persisted to disk using a slow, but easy to read text file
- "persist_dir" = "/path/to/directory/for/storing/file" - this is the directory where the .txt text file will be saved/loaded from
- "HISTORICAL": The table will be persisted using disk based historical engine. Optimized for storing large volumes of data and querying on demand. See HDB manual for more details.
- "FAST": The table will be persisted to disk using a fast, binary protocol. Additional options include:
- PersistEngine="Custom": A custom persist engine will be used (see AMI plugins documentation). Note, that PersistOptions="..." is available to pass options to the custom plugin
- Broadcast="true": Front end visualizations & external listeners will be notified as data is updated in the table
- RefreshPeriodMs="duration_millis": The period that the table will conflate and broadcast changes to front end at. For example, if a cells' value changes 10 times in one second and the refresh period is 500ms, then only ~2 updates will be broadcast out (the other 8 will be conflated)
- OnUndefColumn - Behaviour to take when a realtime Object record contains an undefined column
- "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
- InitialCapacity="number_of_rows" - the number of rows to allocate memory for when the table is created. Must be at least 1. The default initial capacity is 1,000 rows
Syntax
CREATE PUBLIC TABLE [IF NOT EXISTS] tblname (col1 public_column_type [, col2 public_column_type ...])
[USE [PersistEngine="[FAST|TEXT|custom]"]
[PersistOptions="custom_options"]
[Broadcast="[true|false]"]
[RefreshPeriodMs="duration_millis"]
[OnUndefColumn="REJECT|IGNORE|ADD"]
tblname - string name of the table to create
colN - string name of Nth column to create
Example
CREATE PUBLIC TABLE countries (country String NoNull, language String, GDP Double) USE PersistEngine="TEXT" Broadcast="false" InitialCapacity="100"
CREATE PUBLIC TABLE AS
Overview
This command is used to create a new table which has the same schema definition as the result set of a select_clause. If another table already exists with the tblname, the create table will fail.
All rows from the select_clause's result set will be inserted into the table. For valid USE options, see create_public_table_clause Options.
Syntax
CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS select_clause
CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS analyze_clause
CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS prepare_clause
CREATE PUBLIC TABLE [IF NOT EXISTS] tblname [USE ... ] [, tblname [USE ... ] ...] AS execute_clause
tblname - string 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
OVERVIEW
This command alters the schema (column names and types) of an existing table. You can add columns (ADD), rename columns (RENAME … TO), delete columns (DROP), and change the type of columns (MODIFY). Note, when multiple alterations are supplied, they are evaluated left to right. When changing types, a best effort cast will be applied.
When adding columns, the new cells will default to null. The column will be added to the end (right-most) of the table.
ALTER PUBLIC TABLE tblname alter_public_col_expr [, alter_public_col_expr ...]
tblname - string name of the table to alter
Examples
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
MODIFY colname
MODIFY colname AS newname public_column_type |ADD newname public_column_type [BEFORE beforecol]|DROP colname|RENAME colname TO newname
colname - String name of the column to alter
newname - String name of the column to create
beforecol - String name of the existing column to add the new column before
RENAME PUBLIC TABLE
Overview
This command renames an existing table. If another table already exists with the newname, the existing table will be implicitly dropped and replaced with the renamed table.
Syntax
RENAME PUBLIC TABLE tblname TO newname
tblname - string name of the table to rename
newname - string name of the new name for the table
Example
RENAME PUBLIC TABLE AccountHoldings TO Accounts
DROP PUBLIC TABLE
Overview
This command drops an existing table, along with its schema and all rows. You can specify multiple tables, separated by commas.
Syntax
DROP PUBLIC TABLE [IF EXISTS] tblname [, tblname ...]
Examples
DROP PUBLIC TABLE Accounts
Public Column Types
The following types are supported for columns in public tables.
Note on Nulls: If the NONULL attribute is included, the column does not support null values, and inserts/updates of null will fail.
Note on NOBROADCAST: 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.
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).
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).
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).
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).
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).
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).
UTC [NONULL] [NOBROADCAST] 64-bit unix epoc timestamp in milliseconds. If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).
UTCN [NONULL] [NOBROADCAST] 64-bit unix epoc timestamp in nanoseconds. If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).
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).
BOOLEAN [NONULL] [NOBROADCAST] 1-bit signed Boolean (see java.lang.Boolean for details on range).
STRING [NONULL] [NOBROADCAST] variable length string. Each cell is backed by a java.lang.String instance
STRING COMPACT [NONULL] [NOBROADCAST] 4-byte index into a continuous char array for all values in the column. Values are limited to 32,766 characters in length. Max total chars per column (include 2 byte EOL) is 2^31.
STRING COMPACT ASCII [NONULL] [NOBROADCAST] 4-byte index into a continuous byte array for all values in the column. Extended ascii not supported. Values are limited to 126 chars. Max total chars per column (include 1 byte EOL) is 2^31
ENUM [NONULL] [NOBROADCAST] 32-bit index into map of global Strings (map is shared across all public tables)
STRING BITMAP [NONULL] [NOBROADCAST] 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 is 8-bit, 256 - 65,535 unique entries is 16-bit, otherwise 32-bit addressing is used. Note that as new unique values are added, the addressing will automatically escalate as needed. Note that entries from the underlying map are never removed, even as cells are updated deleted.
BINARY [NONULL] [NOBROADCAST] variable length byte array, useful for storing images and other media.
STRING ONDISK [NONULL] [CACHE=nnnMB][NOBROADCAST] Same as a String, but only a 64-bit pointer is kept in memory, which resolves to a Block on disk, 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, then in bytes), example to cache 2 gigabytes: STRING ONDISK CACHE=2GB. Cache-misses will result in reading from disk which can slow down query performance.
BINARY ONDISK [NONULL] [CACHE=nnnMB][NOBROADCAST] Same as a Binary, but only a 64-bit pointer is kept in memory, which resolves to a Block on disk, meaning that binaries 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, then in bytes), example to cache ~100 megs: BINARY ONDISK CACHE=100000000. Cache-misses will result in reading from disk which can slow down query performance.
BIGDECIMAL [NONULL] [NOBROADCAST] Unbounded(variable length) signed decimal (see java.lang.BigDecimal for details on range).
BIGINTEGER [NONULL] [NOBROADCAST] Unbounded(variable length) signed number (see java.lang.BigInteger for details on range).
COMPLEX [NONULL] [NOBROADCAST] A 128-bit Complex number, composed of 64-bit signed real and 64-bit signed imaginary parts.
UUID [NONULL] [NOBROADCAST] a 128-bit Universally Unique Identifier.
Choosing the Best Column Type for String
Simple rules: For data that never repeats and is queried often STRING is the best. For columns with data that does not repeat but is not queried often STRING COMPACT (or STRING COMPACT ASCII for non-extended ascii data) offer a lower memory footprint. For repeating data, generally STRING BITMAP is best. Below is a table outlining the advantages/disadvantages of each.
Type | Storage Method | Advantage | Disadvantage | Usage Scenario |
---|---|---|---|---|
STRING | Each cell is stored as a java.lang.String object. | 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. Another use would be when the total data in the column (adding up the lengths of all cells in the column) exceeds 2 billion chars. |
STRING COMPACT | Each cell contains a 32-bit address into a continuous char array.15-bits are used to store string length. Each cell has a unique address. | 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 either beyond 126 chars or have extended ascii. But is not accessed frequently (ex, as part of a where clause). For example comment columns |
STRING COMPACT ASCII | Each cell contains a 32-bit address into a continuous byte array. 7-bits are used to store string length. Each cell has a unique address | 1/2 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 asci (not extended asci) |
STRING BITMAP | Each cell contains a 8,16 or 32-bit address into a map of unique java.lang.String Objects local to the column. Multiple cells containing the same value will share an address. | Most Compact. | Only used for repeating data (lower cardinality). Retrieval is slightly slower than a simple STRING column, but much faster than COMPACT type columns. | Any scenario where columns have lower cardinality and fewer than approximately 32,000 unique values. |
ENUM | Each cell contains a 32-bit address into a map of unique java.lang.String Objects, global to the entire database. Multiple cells containing the same value will share an address, even if those cells are on different columns of type ENUM. | Compact. Note: this is generally for legacy, BITMAP is generally preferred | Must be used only on columns with low cardinality. Note this is mostly for legacy support and STRING BITMAP is preferred. | When several columns all use a similar set of values (ex: BUY,SELL, SHORT) then all columns can be set to use ENUM. |
STRING ONDISK | Each cell contains a 64-bit address into a random-access file. And only the most recent values read/wrote are cached in memory | 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 |
Reserved columns on public Tables
If any of the following columns are declared they will be automatically populated by the AMI engine. Columns A, D, T, and W will be populated even if they are not declared. Note that when creating the table, the column type MUST match the type specified below, otherwise table creation will fail. Note all times are since unix epoc 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 | An auto-generated incrementing unique id for the row. Unique across all tables. | |
E | Long | Expires Time | INSERT, UPDATE | Time when the row should be automatically deleted.
* Negative Number - Offset from now. Ex: -4000 remove four seconds from now * Zero or Null - don't automatically delete * Positive number - time since epoc to. Ex: 2510712000000L remove 07/24/2049 |
I* | Object | UniqueID | INSERT | The Id(I) value supplied from the real-time streaming on the Object (O) message |
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 the 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. |
*I (and P) column behavior can be thought of as a convenient way of supporting the "upsert" concept:
- If the I column is defined (but not the P column) AMI treats I as a (pseudo) primary key. An attempt to insert a row with the same non-null I value as an existing row will result in an update to the existing row instead of an insert. This is different from a standard PRIMARY key in that two rows can exist in a table with the same key if the key is null. Please see CREATE INDEX ... USE CONSTRAINT="PRIMARY" for standard primary keys.
If both the P and I columns are defined, AMI treats the P + I combination as a (pseudo) primary key. An attempt to insert a row with the same non-null P and I values as an existing row will result in an update to the existing row instead of an insert. This is different from a standard PRIMARY key in that two rows can exist in a table with the same key if the key contains a null value. Please see CREATE INDEX ... USE CONSTRAINT="PRIMARY" for standard primary keys.
Realtime Indexes
CREATE INDEX
Overview
This command allows you to dramatically speed up common queries. Without indexes, a SELECT … WHERE clause needs to scan and consider each row. But if there is an index on the column referenced in the WHERE clause, then the query optimizer will use the index instead. The first column listed is the "top" level column indexed, and so on.
There are three different methods supported for indexing:
- HASH: Under the hood this is a hashmap. This is the fastest most versatile indexing approach but is only useful for queries that directly use equality, because the data is stored in an unsorted fashion.
- SORT: Under the hood this is a treemap. This has additional overhead to a HASH index for both inserting and querying but can be used with ordinality comparisons, such as less than, greater than as well as equals.
- SERIES: Under the hood this is a sorted array. This is a special purpose and highly optimized index useful for when data will be inserted in ascending order, as entries can quickly be added to the end of the array, and queries (including ordinal searches) can use a binary lookup. If data is out of order the array is forced to do a memcopy which can be very expensive.
Constraints can be added to an index to determine the outcome of a key collision. Three different types of constraints are supported:
- NONE: If a constraint is not supplied, this is the default. There is no restriction on having multiple rows with the same key.
- UNIQUE: An attempt to insert (or update) a row such that two rows in the table will have the same key will fail.
- PRIMARY: An attempt to insert a row with the same key as an existing row will cause the existing row to be updated instead of a new row being inserted (specifically, those cells specified and not participating in the index will be updated). This can be thought of as an "UPSERT" in other popular databases. An attempt to update a row such that two rows in the table will have the same key will fail. Each table can have at most one PRIMARY index.
CREATE INDEX [IF NOT EXISTS] idx_name
ON tbl_name(col_name [HASH|SORT|SERIES] [,col_name [HASH|SORT|SERIES] ...])
[USE CONSTRAINT="[NONE|UNIQUE|PRIMARY]"]
idx_name - name of this index to create, each indexs name must be unique for the table
tbl_name - name of the table to add the index to
col_name - name of the column to put the index on.
Joint Indexes
Joint indexes are indexes that span across multiple columns in a table. The syntax is: CREATE INDEX myIndex ON MyTable(A <Type>,B <Type>,C <Type>,....); Joint indexes are helpful when multiple columns participate in your query’s where clause. It will enable the query optimizer to find out the best path to return the results by reducing the search space. Here are some rules you need to follow:
- Rule1: Order does matter.
Note that the order does matter. If you do CREATE INDEX myIndex ON MyTable(A HASH,B HASH);, it is different from doing CREATE INDEX myIndex ON MyTable(B HASH,A HASH);
If you create an index on column A and column B, it first creates the index on column A and for every value in column A, it creates the second index on column B.
- Rule2: High Cardinality Column First.
Knowing that you need a joint index, it is best practice to first create the index on the column with the highest cardinality. By doing so, you are more likely to reduce the search space much more in the initial lookup.
Examples - joint indexes
Given the table orders:
CREATE PUBLIC TABLE ORDERS(orderId int, Symbol string, Region string, Quantity int, Price double);
List regionList = new list("Asia","North America","South America","Europe","Africa");
for(int i=0;i<1000000;i++){ INSERT INTO ORDERS VALUES(rand(10000), "sym"+rand(1000),(string) (regionList.get(rand(5))), 500+rand(1000), rand()*200); }
Since Symbol has a cardinality of 500 and Region of 5. It is advisable to first create index on Symbol then Region, ie:
CREATE INDEX jointIndex ON orders(Symbol HASH, Region HASH);
Now let's run some concrete queries to see how this joint index helps increase the query speed.
- (1.1) case1: full usage of the joint index
SELECT * FROM orders WHERE Symbol=="sym500" AND Region=="North America"; //use both part of the jointIndex
- (1.2) case2: partial usage of the joint index
SELECT * FROM orders WHERE Symbol=="sym500"; //use only "Symbol" part of the jointIndex
- (1.2) case2: no usage of the joint index
SELECT * FROM orders WHERE Region=="North America"; //not using index, do a hard forward scan
SELECT * FROM orders WHERE Region=="North America" AND Symbol=="sym500"; //not using index, do a hard forward scan
Examples2
This example will add an index to the MyTable's Account and Price column. Queries that use the Account column or the Account and Price column will be faster. Note that queries using only the Price column will not sped up by this index. This is an ideal index if we know where going to be running queries of the form: select ... from MyIndex where Account==somevalue and Price[ <, ==, > ] somevalue
CREATE INDEX MyIndex ON MyTable(Account HASH, Price SORT)
CREATE INDEX MyIndex ON MyTable(Name HASH) USE CONSTRAINT="PRIMARY"
DROP INDEX
Overview
Removes the specified index from the given table, as created by CREATE INDEX
DROP INDEX [IF EXISTS] index_name ON tbl_name [,index_name ON tbl_name...]
Example
This example will drop the index named MyIndex from the MyTable table.
Drop index MyIndex on MyTable
Realtime Triggers
Custom AMI Script
Overview
This command allows you to add script that can be executed as data is inserted, updated or deleted from a specified table. There are 5 different events that can cause the trigger to be executed, you must specify at least one of:
- onInsertingScript - This script will be executed before a row is inserted into table_name table. The values of the row to be inserted will available as local variables to the executing script.
- onInsertedScript - This script will be executed after a row is inserted into table_name. The values of the row just inserted will available as local variables to the executing script.
- onUpdatingScript - This script will be executed before an update on table_name table's rows. The values of the row prior to the update will be available as local variable with the form old_varname. The values of the row after the update will be available as local variable with the form new_varname.
- onUpdatedScript - This script will be executed after an update on table_name table's rows. The values of the row just updated will be available as local variables to the executing script.
- onDeletingScript - This script will be executed before a row is deleted from the table_name table. The values of the row to be deleted will available as local variables to the executing script.
- canMutateRow - If true, then any values of the row changed inside the onInsertingScript will reflect back on the row to be inserted. For onUpdatingScript, any changes to the new_varname values will reflect on the row to be updated. Note, this only applies to the onInsertingScript and onUpdatingScript options. See Mutating Trigger example below
- rowVar - a placeholder(can be any custom variable name) that contains the map that reflects the row change in the table(either insert, update or delete).Note that rowVar is a read-only map and the available methods include:boolean containsValue(),boolean containsKey(). The rowVar map can also be used in onUpdatingScript and prefixed with new_ and old_. See rowVar Example using onUpdatingScript for more details.
CREATE TRIGGER trigger_name OFTYPE AMISCRIPT ON table_name [PRIORITY priority] USE
[vars=" Variables shared by the trigger, a comma delimited list of type varname"]
[onStartupScript="AmiScript to Run When the trigger is created"]
[onInsertingScript="AmiScript to Run Prior to insert of a row on defined Tables"]
[onInsertedScript="AmiScript to Run After an insert of a row on defined Tables"]
[onUpdatingScript="AmiScript to Run Prior to updating a row on defined Tables"]
[onUpdatedScript="AmiScript to Run After updating a row on defined Tables"]
[onDeletingScript="AmiScript to Run Prior to deleting a row on defined Tables"]
[canMutateRow="true or false"]
[rowVar="_row"]
trigger_name - name of the trigger to create, must be unique within the database
table_name - name of the table(s) that will cause the trigger to execute
priority - a number, triggers with lowest value are executed first. This also controls the order in which triggers are started up/initialized
Examples
In this example let us assume we have a table MyTable(id int,price double) and a MyAlertTable(type String,id int,diff double) . When a row is deleted from the MyTable this trigger will insert an alert into the MyAlert Table including the id and price of the record deleted. In the case of an update, a row will be inserted into the MyAlert table including the id and change in price due to the update.
CREATE TRIGGER MyTrigger OFTYPE AMISCRIPT ON MyTable USE
onDeletingScript="insert into MyAlert(type,id) values(\"RECORD DELETED\",id,0-price)"
onUpdatingScript="insert into MyAlert(type,id,diff) values(\"CHANGE\",id,new_price - old_price)"
Mutating Trigger Example
CREATE PUBLIC TABLE MutateSample(px double,qty int,val double);
CREATE TRIGGER MutateTrigger OFTYPE AMISCRIPT ON MutateSample USE
canMutateRow="true" onInsertingScript="val=px*qty"
onUpdatingScript="new_val=new_px*new_qty";
INSERT INTO MutateSample(px,qty) VALUES(125.2,100);
SELECT * from MutateSample;
px
Double |
qty
Integer |
val
Double |
---|---|---|
125.2 | 100 | 12520.0 |
UPDATE MutateSample set qty=qty+10;
SELECT * from MutateSample;
px
Double |
qty
Integer |
val
Double |
---|---|---|
125.2 | 110 | 13772.0 |
Mutating Trigger Example Special Case
In the case of using deferred statements (statements in the format of use ds=<your_datasource> execute ... in the mutating trigger, It is strongly recommended that we put all the deferred statements at the end of the script after mutations occur. For example, adding a little tweak to the example before, if we also want to run a deferred statement against another datasource while mutation is happening:
CREATE PUBLIC TABLE MutateSample(px double,qty int,val double);
CREATE TRIGGER MutateTriggerWithDeferredStatement OFTYPE AMISCRIPT ON MutateSample USE
canMutateRow="true" onInsertingScript="val=px*qty"
onUpdatingScript="new_val=new_px*new_qty;
use ds=<another_datasource> execute ....;";//must be placed after the mutation
INSERT INTO MutateSample(px,qty) VALUES(125.2,100);
rowVar Example basic
CREATE PUBLIC TABLE src(id long, sym string);
CREATE PUBLIC TABLE tgt(rows string, arg1 string, arg2 string);
CREATE TRIGGER t1 OFTYPE AMISCRIPT ON src USE
rowVar="_row"
onInsertingScript="insert into tgt values(_row.toJson(),_row.get(\"id\"),_row.get(\"sym\"))";
INSERT INTO src VALUES(123,"aapl");
SELECT * FROM tgt;
rows
String |
arg1
String |
arg2
String |
---|---|---|
{"id":123,"sym":"aapl"} | 123 | aapl |
rowVar Example using onUpdatingScript
create public table orders(id string, val int);
create index idx on orders(id HASH) use CONSTRAINT="PRIMARY";
create public table alerts(comments string);
create trigger rowVarTrigger oftype amiscript on orders use rowVar="row"
onUpdatingScript="""
if(new_row.containsKey("val")){
insert into alerts values("newrow: "+new_row+ "oldrow: " + old_row + "containVal?: " + new_row.containsKey("val"));
}""";
Case1: Upsert(insert causes update due to primary index)
insert into orders values("order1",2),("order1",3);//trigger upsert
comments
String |
---|
newrow: [id=order1, val=3]; oldrow: [id=order1, val=2]; containsVal?:true |
Case2: Direct update
Note that only columns that participate in the update clause will appear in the new_rowVar map
update orders set val=10;
comments
String |
---|
newrow: [val=10]; oldrow: [id=order1, val=3]; containVal?:true |
Aggregation
CREATE TRIGGER mytrigger OFTYPE AGGREGATE ON sourceTable,targetTable USE ...
Overview
This command binds an existing source table and an existing target table together such that the target table will be an aggregate representation of the source table. The following rules apply:
- As the source table has rows inserted, updated or deleted the target table will be automatically updated accordingly
- The target table is no longer modifiable (inserts, updates and deletes to the target table will not succeed)
- Multiple aggregate triggers can share the same source table
- Multiple aggregate triggers can not share the same target table (a given table can only participate in one aggregate trigger as a target table)
- Streaming triggers can be daisy chained, meaning a given table can be a target for one aggregate trigger and a source for another aggregate trigger
- The creation of the trigger will clear out and rebuild the target table to properly reflect the aggregation of the source table's data
There are three USE options:
- groupBys - A comma delimited list of expressions to group rows by, each expression being of the form:
- targetTableColumn = expression_on_sourceTableColumns [,targetTableColumn = expression_on_sourceTableColumns ...]
- selects - A comma delimited list of expressions on how to populate target columns from source columns.
- targetTableColumn = aggregate_on_sourceTableColumns [,targetTableColumn = aggregate_on_sourceTableColumns ...]
- allowExternalUpdates - Optional. Value is either true or false (false by default). If true, then other processes (i.e triggers, UPDATEs) are allowed to perform UPDATEs on the target table. Please use precaution when using this feature, since updating cells controlled by the aggregate trigger will result into an undesirable state.
The following rules apply for the groupBys and selects use options:
- targetTableColumns must only reference columns in the target table's shcema
- sourceTableColumns must only reference columns in the source table's schema
- A targetTableColumn can only be referenced once
- For aggregate expressions only count, countUnique, first, last, max, min, stdev, stdevs, sum and var functions are supported
CREATE TRIGGER trigger_name OFTYPE AGGREGATE
ON source_table_name, target_table_name [PRIORITY priority] USE groupBys="list of group by expressions" selects="list of aggregate expressions" [allowExternalUpdates="true or false"]
trigger_name - name of the trigger to create, must be unique within the database
source_table_name - name of the table that will cause the trigger to execute
target_table_name - name of the table that will be updates by the trigger
priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table
Examples
//Lets assume the two tables exist:
CREATE PUBLIC TABLE Accounts(account String,region String,qty int,px double);
CREATE PUBLIC TABLE Summary(act String,region String,cnt int,value double);
//The below trigger will build a realtime aggregation on accounts reflected in the summary table:
//(try inserting into the Accounts table and then take a look at the Summary table)
CREATE TRIGGER MyTrigger OFTYPE AGGREGATE ON Accounts,Summary USE groupBys="act=account,region=region" selects="cnt=count(*),value=sum(qty*px)";
Projection and Filter
CREATE TRIGGER mytrigger OFTYPE PROJECTION ON sourceTable(s),targetTable USE ...
Overview
This command binds one or more existing source tables and an existing target table together such that the target table will be populated using the source table(s). The following rules then apply:
- As the source table(s) have rows inserted, updated or deleted the target table will automatically have it's corresponding projected rows inserted, updated, deleted accordingly.
- When there are multiple source tables, the resulting target is a UNION of the source tables (not a join)
- The target table's rows that are a result of this projection can not be modified (inserts, updates and deletes to the target table's managed rows will not succeed)
- Multiple projection triggers can share the same source table
- Multiple projection triggers can share the same target table
- The creation of the trigger will clear out and rebuild the target table to properly reflect the aggregation of the source table's data.
- If a source tables' row is updated such that the evaluation of the wheres clause changes, then the row will be inserted/deleted from the target table accordingly.
There are two USE options:
- wheres - A comma delimited list of boolean expressions that must all be true on a source table's row in order for it to be projected into the target table:
- expression_on_sourceTableColumns,[ expression_on_sourceTableColumns ...]
- selects - A comma delimited list of expressions on how to populate target columns from source columns:
- targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]
- allowExternalUpdates - Optional. Value is either true or false (false by default). If true, then other processes (i.e triggers, UPDATEs) are allowed to perform UPDATEs on the target table. Please use precaution when using this feature, since updating cells controlled by the aggregate trigger will result into an undesirable state.
There following rules apply for the wheres and selects use options (important when there are multiple source tables):
- Using the form tablename.columnname will isolate the expression to only apply to the specified table.
- Using the form columnname means that the expression will apply to all tables with the supplied columnname.
CREATE TRIGGER trigger_name OFTYPE PROJECTION ON source_table_name[, source_table_name...], target_table_name [PRIORITY priority] USE wheres="list of where expressions" selects="list of assignment expressions"
trigger_name - name of the trigger to create, must be unique within the database
source_table_name(s) - name of the tables that will cause the trigger to execute
target_table_name - name of the table that will be updated by the trigger
priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table
Examples
//Lets assume the three tables exist:
CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);
CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);
CREATE PUBLIC TABLE MissingRegion(account String, value double);
//The below trigger will build a realtime view of accounts that have a null region:
//(try inserting into the Accounts1 table and then take a look at the MissingRegions table)
CREATE TRIGGER MyTrigger OFTYPE PROJECTION ON Accounts1,Accounts2,MissingRegion USE wheres="region==null" selects="account=account,value=qty * px";
Join
CREATE TRIGGER mytrigger OFTYPE JOIN ON leftTable,rightTable,targetTable USE ...
Overview
This command allows for realtime joining across two tables.
- Changes to the leftTable or/or RightTable automatically update the targetTable, equivalent to using the ... JOIN ... ON ... syntax of the SELECT statement. See the Join Examples section for an illustration of the various types of joins
- Effectively, the target table can be considered a read only table, and can no longer be modified directly
- Multiple JOIN triggers can share the same source table
- Multiple JOIN triggers can not share the same target table.
- The creation of the trigger will clear out and rebuild the target table to properly reflect the JOIN of the left and right tables.
There are three USE options:
- type - How to join the left and right tables. Supported types include: LEFT, RIGHT, INNER, OUTER, LEFT ONLY, RIGHT ONLY, OUTER ONLY. (See Join Examples section for details on the behavior of the various configurations).
- on - An expression for how to relate the two tables in the form:
- "leftColumn == rightColumn [ && leftColumn == rightColumn ... ]"
- selects - A comma delimited list of expressions on how to populate target columns from columns of the left and right tables.
- targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]
The following rules apply for the wheres and selects use options:
- Using the form tablename.columnname will explicitly determine which table (left or right) is to be referenced.
- Using the form columnname means that the column name must be unique to either the left or right table.
CREATE TRIGGER trigger_name OFTYPE JOIN ON left_table_name, right_table_name, target_table_name [PRIORITY priority] USE type="join type" on="cross table comparison expression" selects="list of assignment expressions"
trigger_name - name of the trigger to create, must be unique within the database
left_table_name - name of the left table to join against
right_table_name - name of the right table to join against
target_table_name - name of the table that will be updated by the trigger
priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table
Examples
//Lets assume the three tables exist:
CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);
CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);
CREATE PUBLIC TABLE UnmatchedAccounts(account1 String, account2 String);
//The below trigger will build a realtime view of accounts that only in the Accounts1 table
//or only in the Accounts2 table. (see OUTER ONLY for details)
//(try inserting into the Accounts1 table and then take a look at the UnmatchedAccounts table)
CREATE TRIGGER MyTrigger OFTYPE JOIN ON Accounts1,Accounts2, UnmatchedAccounts USE type="OUTER ONLY"
on="Accounts1.account==Accounts2.account" selects="account1=Accounts1.account, account2=Accounts2.account";
Decorate
CREATE TRIGGER mytrigger OFTYPE DECORATE ON sourceTable,targetTable USE...
Overview
This trigger is used to automatically decorate (update) a target table based on changes from a source table
- When a row is updated/inserted in the source table all matching rows (based on the ON clause) in the target table are updated (based on the SELECTS clause).
- Multiple DECORATE triggers can share the same source table.
- Multiple DECORATE triggers can share the same target table.
- The creation of the trigger will immediately update the target table based on source table values.
- When a row is inserted into the target table the last known matching values (if any) from the source table will be used to immediately update the new row.
- Deletes from the source table will not affect the target table.
There are three USE options:
- on - An expression for how to relate the two tables in the form: "leftColumn == rightColumn [ && leftColumn == rightColumn ... ]"
- selects - A comma delimited list of expressions on how to populate target columns from source columns. Form is: targetTableColumn = sourceTableColumn [,targetTableColumn = sourceTableColumns]
- keysChange- Either true or false. Default is false. If it's expected that columns participating in the ON clause can change, then set to true. Note that setting to true adds additional overhead.
CREATE TRIGGER trigger_name OFTYPE DECORATE ON source_table_name, target_table_name [PRIORITY priority] USE on="cross table comparison expression" selects="list of assignment expressions"
trigger_name - name of the trigger to create, must be unique within the database
source_table_name - name of the table to listen for updates from
target_table_name - name of the table to update on changes
priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table
Examples
//Let's assume the two tables exist:
create public table MktData(sym String,price double); create public table Orders(id String,sym String,price double);
//When the MktData is updated, all Orders with the same symbol will now have the price updated
create trigger tt oftype decorate on MktData,Orders use on="Orders.sym==MktData.sym" selects="price=price"
Relay
CREATE TRIGGER relayTrigger OFTYPE RELAY ON sourceTable USE...
Overview
This command allows messages to be sent through the relay when (a) specific action(s) (insert, update or delete) occurs from the source table.
-- OPTIONS FOR CONNECTING TO THE RELAY --
Required: host, port, login
Optional: keystoreFile,keystorePass
-- OTHER OPTIONS (all optional) --
target - targetTable
derivedValues - key=expression, key=expression, ... syntax
inserts,updates,deletes - key,key,key,... syntax specifying which columns (or derived values) are sent on each event type.
where - conditional statement which messages are to be sent to the target table
See the Options description for more details on how to use the options
OPTIONS Description
- host - hostname of the relay instance
- port - port for the relay instance defined by the property ami.port
- login - the unique id to identify the process/application See the login command:
https://docs.3forge.com/mediawiki/Realtime_Messaging_API#Outbound_Instruction_Type_-_Login_.28L.29
- keystoreFile - location of a keystore file
- keystorePass - the keystore password, this will be encrypted using the strEncrypt method first
- target - the name of the target table, if not defined assumes the same name as the source
- derivedValues - key=expression,...[key=expression] pattern to map source columns to target columns, if the option is omitted, all source columns will map to the target columns of the same given name, if target columns are omitted, it will map the target column to a source column of the same given name
- inserts - comma delimited list of target columns to be sent on an onInserted event on the source table, if your target table has a unique constraint, in most cases you will want to add that column(s) to this list
- updates - comma delimited list of target columns to be sent on an onUpdated event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list
- deletes - comma delimited list of target columns to be sent on an onDeleted event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list
- where - a conditional statement which needs to evaluate to a boolean expression on the source rows, filters what messages should be sent to the target table, false indicates the message will be skipped.
CREATE TRIGGER trigger_name OFTYPE RELAY ON source_table_name [PRIORITY priority] USE
[host=" hostname of the relay instance "]
[port="port for the relay instance defined by the property ami.port"]
[login="the unique id to identify the process/application See the login command:"]
[target="the name of the target table, if not defined assumes the same name as the source "]
[derivedValues="key=expression,...[key=expression] pattern to map source columns to target columns, if the option is omitted, all source columns will map to the target columns of the same given name, if target columns are omitted, it will map the target column to a source column of the same given name "]
[inserts="comma delimited list of target columns to be sent on an onInserted event on the source table, if your target table has a unique constraint, in most cases you will want to add that column(s) to this list "]
[updates="comma delimited list of target columns to be sent on an onUpdated event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list"]
[deletes="comma delimited list of target columns to be sent on an onDeleted event on the source table, if your target table has a unique constraint, a unique identifier column(s) needs to be in this list "]
[where="a conditional statement which needs to evaluate to a boolean expression on the source rows, filters what messages should be sent to the target table, false indicates the message will be skipped"]
- Note inserts,updates and deletes options define what columns will be added to a relay message
- Note that if the derivedValues,inserts,updates or deletes options are omitted, all values are sent.
- Note if the inserts,updates or deletes options are set to "" then the event type is skipped.
- Note the trigger uses AmiClient to connect to the ami.port if it's unable to connect it will drop messages, it is configured to auto reconnect
Example
CREATE PUBLIC TABLE orders1(id Integer,date Integer,name String,price Double) USE PersistEngine="FAST";
CREATE INDEX orders1_idx2 ON orders1(date HASH) USE Constraint="NONE";
CREATE INDEX orders1_idx1 ON orders1(id HASH) USE Constraint="PRIMARY";
CREATE PUBLIC TABLE orders2(id Integer,date Integer,name String,price Double) USE PersistEngine="FAST";
CREATE INDEX orders2_idx2 ON orders2(date HASH) USE Constraint="NONE";
CREATE INDEX orders2_idx1 ON orders2(id HASH) USE Constraint="PRIMARY";
create trigger relayTrigger oftype relay on orders1 use host="localhost" port="4589" login="demo" target="orders2" derivedValues="id=id,date=date,name=name,price=price" updates="id,name" where="price>100.0";
insert into orders1 values(2,2000,"hello", 50.75);
insert into orders1 values(3,2000,"hello", 50.75);
insert into orders1 values(4,2000,"hello", 50.75);
update orders1 set name="test",price=99.9 where id == 3;
Results:
select * from orders1;select * from orders2;
DROP TRIGGER
Overview
This command permanently drops one or more triggers.
DROP TRIGGER trigger_name [, trigger_name ...]
Example
This example will drop the trigger MyTrigger from the database.
DROP TRIGGER MyTrigger
Triggers Action Guide
AMISCRIPT type triggers can have up to 5 different trigger scripts, onInserting, onInserted, onUpdating, onUpdated, and ondDeleting. When running a SQL command against a table with an AMISCRIPT trigger, some of these will run. Note that they run in the order displayed below from onInserting to onDeleting. The table below shows which script will run depending on the SQL command, whether the command changes anything, and whether there is a primary index on the table. We will be using the table ORDERS(OrderID String, Quantity Integer, Price double) with a primary index IDX on ORDERS (OrderID Hash) use Constraint="Primary";.
Description | Command | onInserting | onInserted | onUpdating | onUpdated | onDeleting |
---|---|---|---|---|---|---|
New Row | insert into ORDERS values ("ord01", 100, 75.0);
|
Yes | Yes | No | No | No |
Existing Row No Changes | insert into ORDERS values ("ord01", 100, 75.0);
|
Yes | No | No | No | No |
Existing Row With Changes | insert into ORDERS values ("ord01", 100, 92.0);
|
Yes | No | Yes | Yes | No |
Update No Changes | update ORDERS set AvgPx=75 where OrderID == "ord01";
|
No | No | No | No | No |
Update With Changes | update ORDERS set AvgPx=100 where OrderID == "ord02";
|
No | No | Yes | Yes | No |
Delete Non-Existing Row | delete from ORDERS where OrderID=="ord05";
|
No | No | No | No | No |
Delete Row | delete from ORDERS where OrderID=="ord03";
|
No | No | No | No | Yes |
Sync No Changes | sync into ORDERS(OrderID, LeavesQty, AvgPx) from select "ord01", 100, 75.0;
|
No | No | No | No | Yes |
Sync With Changes | sync into ORDERS from select "ord02", 125, 75.0;
|
Yes | Yes | No | No | Yes |
Sync on (ID) No Changes | sync into ORDERS(OrderID, LeavesQty, AvgPx) on (OrderID) from select "ord01", 100, 75.0;
|
No | No | No | No | Yes |
Sync on (ID) With Changes | sync into ORDERS(OrderID, LeavesQty, AvgPx) on (OrderID) from select "ord02", 130, 75.0;
|
No | No | Yes | Yes | Yes |
Truncate/Delete from | truncate ORDERS;/Delete from ORDER;
|
No | No | No | No | Yes |
Alter table Add Column(s) | ALTER TABLE ORDERS ADD Sym String;
|
No | No | No | No | No |
Alter table With Default Values | ALTER TABLE ORDERS ADD Sym="ABC";
|
No | No | Yes | Yes | No |
Alter table Drop Column(s) | ALTER TABLE ORDERS DROP AvgPx;
|
No | No | No | No | No |
Rename Column | ALTER TABLE ORDERS RENAME LeavesQty To LeavesQuantity;
|
No | No | No | No | No |
Modify Column Data Type | ALTER TABLE ORDERS MODIFY AvgPx AS AvgPrice Int;
|
No | No | No | No | No |
Drop Table | Drop Table ORDERS;
|
No | No | No | No | No |
Side Note:
1. Mutating the row: when set canMutateRow="true", Mutating the values in onInserted, onUpdated or onDeleting will not change the underlying value or row.
2. Trigger Event Return False: If you return false in the triggerEvents: onInserting, onUpdating and onDeleting, it will prevent the row from being inserted, updated or deleting and will also prevent further trigger events
3. Return False with a Sync: If you do a sync command where the onDeleting trigger event returns false, onDeleting will fire but no deletes will occur and will be followed by onInserting, onUpdating and onUpdated
4. Return False Sync without Primary Key:If you didn't have a primary key, that same action previously will insert another row, keeping the original and will also fire the onDeleting (with no deletes), onInserting and onInserted events.
5. Changed vs Unchanged Row: For the onUpdating and onUpdated trigger events if the row hasn't changed, the trigger wont fire. The same hold true when you mutate the row back to it's original values, the next trigger event wont fire.
Realtime Timers
CREATE TIMER
Overview
This command allows you to add script that will be executed on a periodic basis. A special variable _time will be available as a local variable to the executing script
CREATE TIMER [IF NOT EXISTS] timer_name OFTYPE AMISCRIPT ON "schedule" [PRIORITY priority] USE
script="AmiScript to run when timer is executed"
[logging="set the logging level when the timer gets called"]
1. off (default): no logging. 2. on: logs the time when the timer is called and when it completes. 3. verbose: equivalent of using show_plan=ON in AMIDB. Logs the time that a timer starts and finishes and also each query step.
[vars="Variables shared by the timer, a comma delimited list of type varname"]
[onStartupScript="AmiScript to run when the timer is created"]
[timeout="Timeout in milliseconds, default is 100000 (100 seconds)"]
[limit="Row limit for queries, default is 10000"]
timer_name - name of the timer to create, each timer's name must be unique within the database
priority - a number, triggers with lowest value are executed first. Only considered when two or more timers have the same exact scheduled time.
Schedule - either:
- A positive number defining the period in milliseconds between timer executions.
- Empty string ("") to never run timer, useful for timers that should just run at startup, see onStartupScript.
- Crontab style entry declaring the schedule of when the timer should be execute:
Examples
In these examples assume we have the tables A(a boolean) and table B(time utc, cnt int). These timers, when executed, will insert a row into table B with the count of records in A.
//Executes every 500 milliseconds
CREATE TIMER t1 OFTYPE AMISCRIPT ON "500" USE script="int cnt; cnt =select count(*) from A; INSERT INTO b(_time,cnt)"
//Executes every second, on the second
CREATE TIMER t2 OFTYPE AMISCRIPT ON "* * * * * * UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"
//Executes every fifth minute
CREATE TIMER t3 OFTYPE AMISCRIPT ON "0 */5 * * * * UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"
//Executes on the first second of every hour Monday through friday
CREATE TIMER t4 OFTYPE AMISCRIPT ON "0 0 * * * MON-FRI UTC" USE script="int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"
//Execute every Monday at 9:30 eastern standard time
CREATE TIMER t5 OFTYPE AMISCRIPT ON "0 30 9 * * MON EST5EDT" USE script=" int cnt; cnt=select count(*) from A; INSERT INTO b(_time,cnt)"
DROP TIMER
DROP TIMER [IF EXISTS] timer_name[,timer_name,...]
Example
This example will drop the timer MyTimer from the database
DROP TIMER MyTimer
Timer - Advanced
The Actual timer execution period might differ from the one we specified. Consider the following cases:
There are two timers that are scheduled to run on 5 second periods but take at least 30 seconds to finish.
These two commands query the database from MySQL, and are considered deferred statements, where the timer will wait for the last execution to finish before running the next job.
create timer timerms oftype amiscript on "5000" use script="create table x as use ds=world EXECUTE SELECT sleep(30) as x;";
create timer timercron oftype amiscript on "*/5 * * * * * UTC" use script="create table x as use ds=world EXECUTE SELECT sleep(30) as x;";
(1). If the timer has a specified timeout, the timer will execute again once the timeout has been reached.
(2). For the timer with the Cron schedule, the timer will wait until the next valid time to execute.
(3). In both cases, the timers will either execute every 30 seconds or wait until the previous request finishes executing or has timedout.
Realtime Procedures
CREATE PROCEDURE
Overview
This command allows you to create stored procedures that can be called via amiscript to execute a sequence of statements and return a value. Arguments can be supplied when calling the stored procedure (see CALL PROCEDURE):
CREATE PROCEDURE [IF NOT EXISTS] procedure_name OFTYPE AMISCRIPT USE
arguments="type name, type name ... "
script="AmiScript to run when proc is called"
[logging="set the logging level when the procedure gets called"]
1. off/quiet (default): no logging. 2. on: logs the time when the procedure is called and when it completes. 3. verbose: equivalent of using show_plan=ON in AMIDB. Logs the time that the procedure starts and finishes and also each query step.
[vars="Variables shared by the procedure, a comma delimited list of type varname"]
[onStartupScript="AmiScript to run When the procedure is created"]
procedure_name - name of the procedure to be created, each procedure's name must be unique within the database
Examples
In this example lets assume we have a table MyTable(id int,price double). We will make a simple procedure that inserts a row into that table and deletes any other rows with an equal or lesser price. (See CALL PROCEDURE clause for example of calling this procedure)
CREATE PROCEDURE MyProcedure OFTYPE AMISCRIPT USE
arguments="int _id,double _price"
script="DELETE FROM MyTable WHERE price<_price;INSERT INTO
MyTable(id,price) VALUES(_id,_price)"
CALL
Overview
This command allows you to execute stored procedures, typically declared using the CREATE PROCEDURE clause. When calling procedures you must supply the appropriate arguments expected for the declared procedure.
CALL proc_name ([arg1 [,arg2 ...]])
Example
In this example we will call the procedure declared in the CREATE PROCEDURE clause.
CALL MyProcedure(17,45.2)
DROP PROCEDURE
DROP PROCEDURE [IF EXISTS] proc_name[,proc_name...]
Example
This example will drop the procedure MyProcedure from the database
DROP PROCEDURE MyProcedure
Using Methods in the AMI Database
AMIDB methods can be used anywhere within the console, and on top of other functionalities like triggers, procedures and timers.
SHOW METHODS
To view all available methods, use:
show methods;
CREATE METHOD
To create a custom method, the syntax is as follows:
CREATE METHOD return_type method_name (data_type arg1, data_type arg2) { //code goes here; return result; }
For example, to create a simple method (called foo) to return the sum of two numbers (of Integer type):
CREATE METHOD int foo (int a, int b) { return a + b; };
This method can now be called/used inside of timers/procedures/triggers. E.g. for a procedure
CREATE PROCEDURE myProcedure OFTYPE AMISCRIPT USE arguments="int a, int b" script="int i = foo(a,b); INSERT INTO table_name VALUES (i);"
DROP METHOD
To drop our newly created method foo:
DROP METHOD foo(int a, int b);
Method overloading
Like in Java, method overloading is supported by AMI, which allows for methods with the same name but different parameters to be created.
For example, if we currently have int foo(int a, int b), which returns the sum of two Integers, we can overload the method to return the sum of two doubles as well, with: CREATE METHOD double foo (double a, double b) { return a + b; };
AMI will auto-resolve the method to use based on the parameters used in the method call.
NOTE: Multiple methods can have the same name as long as the number and/or type of arguments are different.
Realtime Tools
SHOW
Overview
Provides a table listing all records and relevant Metadata of a particular type with optional filtering and sorting. Note, for tables, only PUBLIC tables are listed.
SHOW object_type [WHERE where_expr ] [ORDER BY expr [ASC|DESC] [, expr [ASC|DESC] ...]]
object_type
Either TABLES, COLUMNS, COMMANDS, DATASOURCES, DATASOURCE_TYPES, PROCEDURES, PROPERTIES, RESOURCES, TRIGGERS, TIMERS, CONNECTIONS, RELAYS, PLUGINS, SESSIONS, VARS, PROCESSES, METHODS
Example
This example will return all tables whose name contains "data" and will order the results by the number of columns in the table.
SHOW TABLES WHERE TableName =~ "data" ORDER BY ColumnsCount
SHOW TABLE
Overview
Provides a table listing all columns and relevant Metadata of a particular table with optional filtering and sorting.
SHOW TABLE table_expr [WHERE where_expr ] [ORDER BY expr [ASC|DESC] [, expr [ASC|DESC] ...]]
Example
This example will return all columns for the myorders table, alphabetized by name.
SHOW TABLE myorders order by ColumnName
DESCRIBE
Overview
Provides a table containing with an "SQL" column containing the statement(s) necessary to reconstruct the supplied table,trigger,timer,procedure or index.
DESCRIBE TABLE table_name
DESCRIBE TRIGGER trigger_name
DESCRIBE TIMER timer_name
DESCRIBE PROCEDURE procedure_name
DESCRIBE INDEX index_name ON table_name
Example
This example will return the create statement that can be used to create the __COLUMN table and its pk index.
DESCRIBE TABLE __COLUMN;
DISABLE and ENABLE
Overview
Used to disable/enable triggers and timers by name. Disabled triggers and timers will not get executed when they otherwise would. By default, when triggers and timers are created they are enabled. Check the enabled/disabled status using SHOW TRIGGERS and SHOW TIMERS.
DISABLE TRIGGER trigger_name
ENABLE TRIGGER trigger_name
DISABLE TIMER trigger_name
ENABLE TIMER trigger_name
Examples
This example will disable the trigger named my_trigger and enable the timer my_timer
DISABLE TRIGGER my_trigger;
ENABLE TIMER my_timer;
DIAGNOSE
Overview
Returns a table providing details on the approximate memory footprint of tables, columns and indexes.
1DIAGNOSE TABLE table_name [,table_name ...]
2DIAGNOSE COLUMN column_name on table_name [,column_name on table_name ...]
3DIAGNOSE INDEX index_name ON table_name [,index_name on table_name ...]
Examples
This example will return the memory used by the __COLUMN table
1DIAGNOSE TABLE __COLUMN;
SETLOCAL
Overview
Controls local variables for the command line session. These local variables dictate behavior of the command line interface. Run SETLOCAL to see the list of variables that can be changed. Note, this command cannot be run from the AMIDB Shell Tool.
SETLOCAL varname = value
Variables
Variables | Description |
---|---|
limit | The default limit to apply to queries (similar to the limit field in the datamodel), -1 means no default limit |
max_print_chars | Total number of characters to print to the console for a query result. This prevents from very large query results overwhelming terminal |
multiline | If set to "off" then pressing enter will automatically execute the user entered |
If set to "on" then pressing enter will progress to a new line for additional input. Pressing enter twice in succession will execute the users entered text. | |
show_plan | If set to "off" the engine will not show the query plan, this is more efficient as some overhead is necessary to gather and display this importation. |
If set to "on" then the engine will produce verbose output on the steps and time taking for each step in the query. This can be useful for optimizing your queries. | |
timeout | Amount of time in milliseconds that the console will hang waiting for a response. It must be a positive number |
unprintable_chars | Determines how to print unprintable ASCII characters, for example 0x01 (SOH). |
If set to "marker" then an upside down question mark (¿) is printed in place of any unprintable chars (default) | |
If set to "show" the raw, unprintable, is sent to stdout. | |
If set to "hide" then nothing is printed. | |
If set to "show_code" then an upside down question mark (¿) is printed followed by the chars 4 digit hexcode is printed. For example, the SOH would be printed as: ¿0001 | |
datetime_format | The format to use when printing UTC and UTCN columns. If set to empty string, the Raw long value (unix epoch) is printed. The default is YYYY-MM-DD HH:mm:ss.SSS.z |
timezone | Works in conjunction with datetime_format, to determine local time for display. For example EST5EDT would be used for New York. Default is UTC |
string_template | Should commands interpret string templates (default is off) |
If set to "on" then ${...} will be evaluated and replaced with the contents | |
If set to "off" then ${...} will be treated as a literal. | |
password_encrypt | Should commands interpret string templates (default is off) |
If set to "on" then login command should be supplied with an encrypted password. The password will be decrypted using the key stored on the AMI server. Note, use strEncrypt(...) method to get the encrypted version of a plain text password. Ex: select strEncrypt("demopass"); | |
If set to "off" then login command should be supplied with plain text password. |
Example
setlocal timeout=30000
System Tables
__COLUMN
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
Variables | Description |
---|---|
CI | Connection ID of the connection that this command was registered using |
RI | Relay ID that this command was registered using |
ID | Purpose of the command (Cancel order command), 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 (order.cancel) |
FL | Command filter. An expression that will determine which rows the command will be available at a panel level (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 (qty>0 && user.canbust=="true") |
HP | Explanation/outcome of the command. (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 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 (price, status, level) |
I | Relay name with command name. (e.g. 29002751:CancelCmd) |
P | Login name used to access the realtime database ("demo") |
V | Number of times a row is being updated |
M | Command modified time in miliseconds |
C | Command created time in miliseconds |
__CONNECTION
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 and can be used in conjunction by comma delimiting |
QUIET - AMI will not send any information back to the client (statuses, etc). Note execute commands (E) will still be send to the client | |
LOG - Force AMI relay to log data to / from this session (default file = AmiSession.log) | |
UNIX - Force AMI to not send \r on messages | |
WINDOWS - Force AMI to send \r on messages | |
TTY - teletype terminal (for UNIX) is for interactively working with AMI backend | |
PL | A fully qualified java class name to an AMI relay plugin. The class must implement the com.vortex.agent.AmiPlugin interface |
RI | The Relay which was used to run on (relay_0) |
RP | Remote host port used |
RH | Remote host name, localhost, IP address |
CT | Time of connection made |
__DATASOURCE
Variables | Description |
---|---|
NM | Datasource name, self-specified |
AD | Adapter type (RestAPI, MySQL) |
UR | Datasource URL (protocol//[hosts][/database][?properties], 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 (relay_0) |
PermittedOverrides | Combination of URL, USERNAME, PASSWORD, OPTIONS, RELAY |
__DATASOURCE_TYPE
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 (com.f1.ami.center.ds.AmiShellDatasourcePlugin, com.f1.ami.center.ds.AmiAmiDbDatasourcePlugin, com.f1.ami.plugins.mysql.AmiMysqlDatasourcePlugin) |
Icon | Icon (svg file) associated with the datasource |
Properties | Map of properties associated with the datasource |
__GUI
Variables | Description |
---|---|
Type | Types of GUI objects (TABLE, PROCEDURE, TRIGGER) |
Name | Name of objects (table name, procedure name, trigger name) |
X | X coordinate position in the data modeler |
Y | Y coordinate position in the data modeler |
__INDEX
Variables | Description |
---|---|
IndexName | Name of index (primary key, relay id) |
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
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) |
Arguments | Arguments/Parameters used in conjunction with plugin |
__PROCEDURE
Variables | Description |
---|---|
ProcedureName | Name of procedure (__ADD_DATASOURCE, __REMOVE_DATASOURCE, __RESET_TIMER_STATS) |
ProcedureType | Type of procedure (AMISCRIPT, __SYSTEM) |
ReturnType | Return type after running procedure (Object, String, Integer) |
Arguments | Arguments/Parameters used in conjunction with procedure |
Options | Additional options defined (scripts, arguments) |
DefinedBy | Which user defined (USER, AMI, SYSTEM) |
__PROPERTY
Variables | Description |
---|---|
PropertyName | Name of property |
PropertyValue | Value of property |
__RELAY
Variables | Description |
---|---|
MachineUid | Unique Machine ID of the host (UID_DESKTOP-RVO4948) |
ProcessUid | System Generated Process Id (F1-Inw6XE0p8YaqdbREAp5MxN) |
StartTime | Starting time in UTC (2022-01-20 03:03:06.101 EST) |
ServerPort | Connection port (3289) |
RelayId | Relay ID (relay_0) |
Hostname | Host name (DESKTOP-RVO4948) |
ConnectTime | Connection time in UTC (2022-01-20 03:03:28.299 EST) |
__RESOURCE
Variables | Description |
---|---|
I | Name of the image (3ForgeLogo.png) |
ModifiedOn | Modified time in milliseconds |
Checksum | Unique checksum value used for validation |
FileSize | File size in bytes |
ImageWidth | Image width in pixels |
ImageHeight | Image height in pixels |
__TABLE
Variables | Description |
---|---|
TableName | Name of table |
Broadcast | "true": Front end visualizations & external listeners will be notified as data is updated in the table |
RefreshPeriodMs | "duration_millis": The period that the table will conflate and broadcast changes to front end at. For example, if a cells' value changes 10 times in one second and the refresh period is 500ms, then only ~2 updates will be broadcast out (the other 8 will be conflated) |
SourceTableNames | Name of the tables to listen for updates from (cause the trigger to execute) |
PersistEngine | FAST: The table will be persisted to disk using a fast, binary protocol. Additional options include: |
"persist_compact_percent" = ".95" - This is the directory where the fast binary file will be saved/loaded from | |
"persist_dir" = "/path/to/directory/for/storing/file" - A number between 0 and 1 which says how much of the file should contain updates & deletes before it's compacted. Because the FAST table uses a journaling strategy, updates and deletes will get appended and can create large files. Setting a value of .95 would indicate that if the file becomes 95% updates and deletes then the file should be rebuilt | |
TEXT: The table will be persisted to disk using a slow, but easy to read text file | |
"persist_dir" = "/path/to/directory/for/storing/file" - This is the directory where the .txt text file will be saved/loaded from | |
PersistEngine="Custom" - A custom persist engine will be used (see AMI plugins documentation). Note, that PersistOptions="..." is available to pass options to the custom plugin | |
OnUndefColumn | Behaviour to take when a realtime Object record contains an undefined column |
"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 |
__TIMER
Variables | Description |
---|---|
TimerName | Name of the Timer created |
TimerType | Either AMISCRIPT type or custom timer type created using timer plugin defined in the imdb schema |
Priority | A number that is set when two or more timers have the same exact scheduled time. The lowest value is executed first |
Scheule | A positive number defining the period in milliseconds between timer executions |
Empty string ("") to never run timer, useful for timers that should just run at startup | |
Crontab style entry declaring the schedule of when the timer should be executed | |
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 type or custom timer type created using trigger plugin defined in the imdb schema |
Priority | A number that is set when two or more timers have the same exact scheduled time. The lowest value is executed first |
Options | AmiScript to run when timer is executed. |
DefinedBy | The ID of the owner |
Enabled | Is the timer currently active |
Capacity Planning
AMI In-memory Database Capacity Planning
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 the AMI in memory database, one should run this on each table and aggregate the results.
table_size_mb = row_cnt x (128 + data_size + idx_cnt * 100 + null_col_cnt * 1/8 ) / 1,048,576
table_suggested_capacity_mb= total_size_mb * 2
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 |
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 bitmap under 256 unique values | 1 |
String bitmap 256 to 65536 unique values | 2 |
String bitmap over 65536 unique values | 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 | 68 |
UUID | 64 |
Capacity Planning Example
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.
100,000,000 Executions & Associated Memory Usage
Large Capacity Planning Example
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.