[tcallusdb knowledge base] restfulapi2 0 for generic table - [Pb] indexquery2 0 Introduction

brief introduction

Query records through the global index and support SQL syntax, as shown in the appendix. To use this interface, you need to add global index fields to the table on Tencent cloud console or local Docker Web platform in advance.

POST http://{Tcaplus_REST_URL}

Request syntax

Http request

#Tencent cloud console RESTful Endpoint, ip:80, port: 80 by default
http://172.17.0.22
#Tcallusdb local docker RESTful Endpoint, ip:31001, port default 31001
http://9.135.8.93:31001

Http head

Example:

x-tcaplus-target:Tcaplus.IndexQuery
x-tcaplus-app-id:3
x-tcaplus-zone-id:1
x-tcaplus-protocol-version:2.0
x-tcaplus-table-name:game_players
x-tcaplus-pwd-md5:4e81984efccfb4982333aeb1ff7968d5
x-tcaplus-result-flag:2
x-tcaplus-version:Tcaplus3.50.0
x-tcaplus-data-version-check: 3
x-tcaplus-idl-type:protobuf

Data

json format is used to represent relevant information. Parameter information:

• Query: you must specify the SQL statement of the query.

Example: game_server_id index field as query criteria

#Example 1,
{
    "Query": "select player_id, player_name,player_email, game_server_id, pay.amount from game_players where game_server_id>0 limit 1 offset 0"
}

Full request example

curl -i -XPOST -H 'x-tcaplus-target: Tcaplus.IndexQuery' -H 'x-tcaplus-app-id: 70' -H 'x-tcaplus-zone-id: 1' -H 'x-tcaplus-protocol-version: 2.0' -H 'x-tcaplus-table-name: game_players' -H 'x-tcaplus-pwd-md5: 0972ad76decf4d11a69e2e0d9af335da' -H 'x-tcaplus-result-flag: 2' -H 'x-tcaplus-version: Tcaplus3.50.0' -H 'x-tcaplus-data-version-check: 1' -H 'x-tcaplus-idl-type: protobuf' http://172.17.32.17 -d '{
    "Query": "select player_id, player_name,player_email, game_server_id, pay.amount from game_players where game_server_id>0 limit 1 offset 0"
}'

Return syntax

Return parameter description

Return example

Successful return example

{
    "ErrorCode": 0,
    "ErrorMsg": "Succeed",
    "MultiRecords": [{
        "RecordVersion": 1,
        "Record": {
            "game_server_id": 55,
            "pay": {
                "amount": 55
            },
            "player_email": "55",
            "player_id": 5,
            "player_name": "5"
        }
    }],
    "TotalNum": 1
}

Failure return example

{"ErrorCode":-13841,"ErrorMsg":"proxy_err_query_index_field_not_exist"}

Error code

Reference[ PB] common error codes

Supported SQL syntax

Condition query

Support =, >, > =, <, < =,! =, Between, in, not in, like, not like, and, or, for example:

select * from table where a > 100 and b < 1000;
​
select * from table where a between 1 and 100 and b < 1000;
​
select * from table where str like "test";
​
select * from table where a > 100 or b < 1000;

Note: during between query, between a and b, the corresponding query range is [a, b]. For example, between 1 and 100 will contain the values of 1 and 100, that is, the query range is [1100].

Note: like query supports fuzzy matching, in which "%" wildcard matches 0 or more characters; “_” Wildcard, matching 1 character.

Paging query

Paging limit offset query is supported. For example:

select * from table whre a > 100 limit 100 offset 0;

Note: the current limit must be used with offset, that is, limit 1 or limit 0,1 is not supported.

Aggregate query

Currently supported aggregate queries include sum, count, max, min, avg, for example:

select sum(a), count(*), max(a), min(a), avg(a) from table where a > 1000;

Note: aggregate query does not support limit offset, that is, limit offset does not take effect;

Note: at present, only count supports distinct, that is, select count (distinct (a)) from table where a > 1000; Distinct is not supported in other cases.

Partial field query

select a, b from table where a > 1000;

For the pb table, you can also query the values of nested fields by points, similar to:

select field1.field2.field3, a, b from table where a > 1000;

Unsupported SQL syntax

Mixing aggregate query with non aggregate query is not supported

select *, a, b from table where a > 1000;

select sum(a), a, b from table where a  > 1000;

select count(*), * from table where a  > 1000;

Limited support for Order by query

select * from table where a > 1000 order by a;

group by query is not supported

select * from table where a > 1000 group by a;

having queries are not supported

select sum(a) from table where  a > 1000 group by a having sum(a) > 10000;

Multi table joint query is not supported

select * from table1 where table1.a > 1000 and table1.a = table2.b;

Nested SELECT queries are not supported

select * from table where a > 1000 and b in (select b from table where b < 5000);

Aliases are not supported

select sum(a) as sum_a from table where a > 1000;

Other unsupported queries

• join query is not supported;
• union query is not supported;
• Queries like select a + B from table where a > 1000 are not supported;
• Queries like select * from table where a + b > 1000 are not supported;
• Queries like select * from table where a > = B are not supported;
• Other queries not mentioned are not supported.

Tcallusdb is a distributed NoSQL database produced by Tencent. The storage and scheduling code is completely self-developed. It has the characteristics of cache + floor fusion architecture, PB level storage, millisecond delay, lossless horizontal expansion and complex data structure. At the same time, it has the characteristics of rich ecology, convenient migration, extremely low operation and maintenance cost and five nine high availability. Customers cover games, Internet, government affairs, finance, manufacturing, Internet of things and other fields.