Conditional Filter and Update

Conditional filter and update include conditional filter and update, the latter refers to the array update.

1. Version Requirements

  • SDKs require versions above or equal to 3.55.0, and batch operations require versions above 3.64.0(Currently, the highest SDK available is only 3.55.0, which means batch operations do not support conditional updates and conditional filtering.).
  • Client tools require a version greater than or equal to 3.55.0 for conditional filtering.

2. Which Commands Support Conditional Updates?

The main data manipulation support condition update. For details, see the parameter description of each API. The API description is as follows

  • Query requests support conditional filtering, including
    • Get
    • FieldGet
    • IndexGet (i.e. GetByPartKey)
    • Traverse
    • ListGet
    • ListGetAll
    • ListTraverse
  • Update requests support conditional filtering and array updating, including
    • Update
    • Replace
    • Delete
    • FieldSet (i.e., FieldUpdate)
    • FieldIncrease
    • ListReplace
  • Batch query supports conditional filtering, but requires versions above 3.64.0, including
    • BatchGet
    • BatchGetByPartKey
    • BatchFieldGet
    • ListGetBatch
  • Update requests support conditional filtering and array updating, including, but requires versions above 3.64.0, including
    • BatchUpdate
    • BatchReplace
    • BatchDelete
    • ListDeleteBatch
    • ListUpdateBatch
    • ListReplaceBatch
  • Two requests for operations and array queries:
    • UpdateItem and ListUpdateItem are used to PUSH, SET, and POP array fields
    • Query and ListQuery are used to query parts of arrays, that is, GET operations on arrays
  • Due to historical reasons, the Increase requirement of the TDR protocol requires a version of 3.64.0 or higher to support conditional updates. For versions below 3.64.0, the Update command can be used, which supports field updates and self augmentation.
  • UpdateByPartKey and DeletePartKey require versions above 3.64.0 to support conditional updates due to historical reasons
  • For historical reasons, ListDeleteAll does not support conditional filtering temporarily

For more functional support of conditional filtering and updating, please contact Tcaplus.

3. Examples

TDR Table Example (C++)

USER u = {};
// Set primary key
u.dwId = 1;
strcpy(u.szName, "a");
record->SetData(&u, sizeof(u));
// For pre-condition filtering, first judge whether the gameids has 101
record->SetCondition("gameids NOT CONTAINS($ = 101)");
// For post-array operations, insert 101
record->SetOperation("PUSH gameids#[-1][$ = 101]");
// Send a write request
// ...
// Process the response return value
if (ret == COMMON_ERR_CONDITION_NOT_MATCHED) // If the conditions are not met, it indicates that there is 101 in gameids
{
    // ...
}

PB Table Example (C++)

user u;
// Set primary key
u.set_id(1);
u.set_name("a");

// Set the step size of increase
u.set_rank(1);
// When the rank reaches the upper limit of 100, it will not be increased; otherwise, it will be increased by one.
std::set<std::string> dottedpaths;
dottedpaths.insert("rank");
ret = api.FieldInc(dottedpaths, &u, "", "rank < 100");

More detailed examples are not provided here. The C++ SDK, Go SDK and Client of TcaplusDB all support conditional update. For partial interfaces of SDK, parameter condition is filled with conditional filtering text and parameter operation is filled with array operation text. For details, see the API Descriptions of Each SDK.

4. Conditional Filter Syntax

Filter conditions are the syntax of SQL-like WHERE statements, which can support the following filtering capabilities

  • Comparison, such as rank > 1. The comparators includes >, >=, <, <=, =, ==, !=, <>. In the context of comparison, = is an equal comparison, while in other contexts, it may be an assignment symbol.
  • Logical operation, such as rank > 1 AND rank < 10. The operator includes AND, OR, NOT.
  • Bit operation, supports "AND" only, such as filter & 8. When the third bit from low to high is 1, the expression is true.
  • CONTAINS and NOT CONTAINS, that is to determine whether the array contains values. For example, "mailbox CONTAINS(title == \"tcaplus\")" means that the mailbox is required to contain an element with a title equal to "tcaplus". CONTAINS brackets can contain more complex sub-conditions.
  • LIKE and NOT LIKE support fuzzy matching of string fields, such as "name LIKE 'abc%'". Like MySQL syntax, it ignores case by default.'%' can match any string and '_' can match any single character.
  • IN and NOT IN, that is to determine whether field values meet multiple hash values, such as "name IN ('abc', 'ABC', '123')".
  • It supports nested fields, such as mail.title, mailbox[0].title, and map_field['key'].value.
  • It supports built-in functions. Currently, it only supports the size() function, which is used to obtain the size of an array or map field. For example, "size(mailbox) = 0" determines whether the array is empty.
  • The built-in attribute $.LastAccessTime indicates the last update time of the record with a minimum precision of seconds. It can be used to compare with the time represented by a string, such as "2021", "2021-01-01" or "2021-01-01 00:00:00".
  • A reference $ to the current array element, such as "gameids NOT CONTAINS($==101)".

The complete syntax is as follows

condition_expr ::=
      array CONTAINS '(' condition ')'
    | array NOT CONTAINS '(' condition ')'
    | condition

condition ::=
      operand
    | operand comparator operand
    | condition AND condition
    | condition OR condition
    | NOT condition
    | operand bitwise_op operand
    | operand LIKE string
    | operand NOT LIKE string

comparator ::=
      ==
    | =
    | <
    | >
    | <=
    | >=
    | !=

bitwise_op ::=
    &

array ::=
    identifier

operand ::=
      identifier
    | number
    | string
    | $
    | $.LastAccessTime
  • Syntax description
    • identifier: A valid identifier. It can be the field name or field name path, such as name or mail.title.
    • number: Integer or floating number. Large integers are not supported.
    • string: A string enclosed by double quotation marks or single quotation marks. It supports C-like escape characters, such as "abc\t123".
  • compare
    • Integer or floating values of different precision can be compared with each other, which is consistent with the C++ language. For example, when int16 and int32 values are compared, the type of the former needs to be promoted and then compared. When integer and floating values are compared, the integer must first be promoted to a floating point.
    • Int and uint can be compared, and the sign bit will be compared first.
    • Floating-point equivalents may have precision deviation.
    • Strings can also be compared in alphabetical dictionary order, which is consistent with the comparison behavior of std:: string in C++.
    • Numeric types and strings can not be compared directly.
  • operator priority
    • In the conditional expression condition, the operator priority is comparator NOT AND OR from high to low. Such as "a==1 OR a>10 AND a<20", the result of AND will be calculated first and then OR.
    • Of course, brackets can be used to separate conditional expressions. Such as "(a==1 OR a>10) AND a<20", OR will be calculated first.
  • keyword
    • There are some syntax keywords, such as key, like, update, and so on. If the field name of the table and these keywords have the same name, there will be syntax errors, such as "key > 100". However, quotation marks ' can be used to enclose the field name to avoid it, such as ""key' > 100".
    • Because TcaplusDB is compatible with MySQL protocol, in principle, the keywords are the same as MySQL keywords. For details, you can query Keywords and Reserved Words.

Performance optimization suggestions:

The performance of condition filtering is related to the condition expression and the table structure definition. There are performance optimizations when the following rules are met (for reference only, specific implementations may be adjusted)

  • When the conditional expression only uses the key field (including the primary key and index fields) and the attribute field of the record (such as $.LastAccessTime), it can be determined only by the primary key or index of the record without reading the full data from the storage engine.
  • For the SortList table, there is also performance optimization when the conditional expression only uses the sort field.
  • For the SortList table, there is only one sort field defined in the table, and the conditional expression is a simple binary comparison (such as "field >= 1" and "field == 1"). If using binary search, better performance optimization can be achieved.

5. Array Operation Syntax

TcaplusDB provides command operations on arrays, including the following:

  • PUSH: insert new element data at a specified position in an array.
  • SET: modify the element data at a specified position in an array.
  • POP: delete some subscript ranges or elements that meet certain conditions in an array.
  • GET query: specify the key of the record, and query the array to return some subscript ranges or elements that meet certain conditions in the array (that is, only local data of the record).

For some interfaces of the C++SDK and Go SDK of TcaplusDB, parameter condition is the pre-filter condition, and parameter operation is the post-array operation. See the API Description of each SDK for more information.

Example

  • Insert an element at the tail of the mailbox array (assign values to the title and other fields in the element at the same time), where -1 represents the array subscript at the tail.
    • "PUSH mailbox #[-1] [title = 'tcaplus', content = '...']"
  • Insert element 101 at the head of the gameids array, where 0 represents the array subscript at the head and $ represents the array element of the current operation.
    • "PUSH gameids #[0] [$ = 101]"
  • Delete elements in the mailbox array whose subscript is in the range of 0 to 10 and whose title is not equal to "tcaplus".
    • "POP mailbox #[0-10] [title != 'tcaplus']"
  • Modify the element with the specified subscript of 1.
    • "SET gameids #[1] [$ = 101]"
  • Combination operation: insert elements at the head of the array and delete at the tail (deleting non-existent elements has no effect) to ensure that the size of the array is always within 100.
    • "PUSH gameids #[0][$=100]; POP gameids #[100]"

The complete operation syntax is as follows

push_expr ::=
    PUSH array # '[' index ']' '[' assign_expr [, assign_expr]* ']'

set_expr ::=
    SET array # '[' index ']' '[' assign_expr [, assign_expr]* ']'

pop_expr ::=
      POP array
    | POP array # '[' index_range ']'
    | POP array # '[' index_range ']' '[' condition ']'

get_expr ::=
      GET array
    | GET array # '[' index_range ']'
    | GET array # '[' index_range ']' '[' condition ']'

assign_expr ::=
      identifier = number | string
    | $ = number | string

index ::=
    integer

index_range ::=
      index [, index_range]*
    | index - index [, index_range]*

Syntax description

  • assign_expr: Assignment expression. If the array element is a combination type, it is the assignment title = 'tcaplus', content = '...' of the internal field. If the element is a basic type, use $ to reference the element itself, such as $=101.
  • index: The subscript of the array. 0 represents the subscript of the array head. If the array size is unknown, use -1 to represent the subscript of the array tail.
  • index_range: Array subscript range, such as "0 - -1" means all subscripts, or multiple discontinuous ranges, such as "0 - 8, -1".
  • condition: Nested filter conditions. Its syntax is consistent with the condition filtering recorded in the previous section. The difference is that the semantic context of the former is the data of the entire record, and here is the data of the array element.

Description of PUSH/SET

  • In the assignment expression, only integer, floating-point and string fields are currently supported.
  • In the assignment expression, different types of integer and floating-point can be assigned to each other. That is, type conversion will be forced, and truncation may occur. The behavior of type conversion is consistent with that in C++.
  • For SET, if the array size is N, the valid range of subscripts is 0 to (N - 1) or -1.
  • For PUSH, if the array size is N, the valid range of subscripts is 0 to N or -1.

Description of POP

  • POP is used to delete some ranges or elements that meet the conditions.
  • Based on the principle of `no error will be reported when deleting non-existent elements`, no error will be reported when pop specifies a non-existent subscript range. For example, if the array size is 10, 8-80 will delete the last 2 elements.

6. Notations

  • The maximum text length of parameters condition and operation is 1023.
  • The conditional filter and update of the TDR table depends on the Unpack ability of the TDR. The TDR SDK supports two ways to write data records, namely, Record.SetValue() and Record.SetData(). The complex data type (structure, array) written by SetValue() is not supported here.
  • The array field in the TDR table has a corresponding refer field (the actual size of the array), and the PUSH and POP operations on the array will automatically modify this refer field.

results matching ""

    No results matching ""