A method and system for implementing unified pagination of different data sources based on calcite

Abstract

The application relates to the computer technical field, solves the problem that all data sources cannot be uniformly paginated in an API client in the prior art, and discloses a method and system for realizing uniform pagination of different data sources based on calcite, which comprises the following steps: adding a pagination variable in a calcite extension file; calculating the position of the entire pagination in a calcite analysis sql process, and returning the pagination position, so that the calcite extension file supports returning the pagination position; and realizing pagination conversion and precompilation by analyzing a syntax tree, wherein the method can realize the consistency of the order of corresponding values of a $ variable parameter after precompilation sql analysis and return and the order of precompiled placeholders, guarantee the correctness of precompilation execution, and can realize uniform pagination of all data sources in an API client, reduce the difficulty of writing pagination sql, and support variable and precompilation functions.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a method and system for achieving unified pagination across different data sources based on Calcite. Background Technology

[0002] SQL (Structured Query Language) pagination is a feature present in almost every data source, but the syntax for pagination varies across different data sources. In APIs (Data Services), multiple data sources are typically provided to users. Therefore, it's necessary to provide a way to convert pagination from all data sources to a unified pagination standard. However, Apache Calcite (an open-source parsing framework) currently only supports regular SQL parsing and does not support pagination variables. Furthermore, native Calcite only parses the position of numeric terms in pagination, neglecting to parse the position of keywords such as limit, offset, and fetch. This makes implementing pagination conversion difficult, hindering the unification of pagination across all data sources on the API client. Summary of the Invention

[0003] The purpose of this application is to overcome the problem in the existing technology that it is difficult to unify the pagination of all data sources in the API client, and to provide a method and system for unifying the pagination of different data sources based on Calcite.

[0004] Firstly, a method for achieving unified pagination across different data sources based on Calcite is provided, including:

[0005] Add a pagination variable to the calcite extension file;

[0006] The Calcite extension calculates the position of the entire page during the SQL parsing process and returns the page position so that the Calcite extension file can support returning the page position.

[0007] Parse SQL to generate a Calcite syntax tree;

[0008] Convert the calcite syntax tree into a standard syntax tree with LimitNode nodes;

[0009] Obtain the parameter order of the variable, match the entire SQL using the variable's regular expression, and record each parameter in the matching order;

[0010] Calcite converts different data sources into standard pagination SQL for the corresponding data sources while maintaining the consistency of the pre-compiled order.

[0011] Furthermore, add pagination variables to the Calcite extension file, including:

[0012] Add the variable DOLLAR_VARIBLE to the calcite extension file parser.jj;

[0013] Add the DollarVariableParam method to the UnsignedNumericLiteralOrParam method in the calcite extension file parser.jj;

[0014] Implement the DollarVariableParam method in the parserImpls.ftl file. The DollarVariableParam method contains a variable pos of type SqlParserPos to record the position and a variable value of type String to record the current token.

[0015] Furthermore, the DollarVariableParam method is implemented in the parserImpls.ftl file, including:

[0016] Determine if the current TOKEN is a DOLLAR_VARIBLE variable;

[0017] If the judgment result is yes, a custom ExtendDollarVariable object is generated. The ExtendDollarVariable object inherits from SqlLiteral. The ExtendDollarVariable object contains a constructor, an unparse method, and a checkVariable method. The constructor includes a pos object to record the position and an NlsString object. An ExtendNlsString object is generated using the variable value of the token. Then, the ExtendDollarVariable object is generated through the constructor. The unparse method is used to output the current value. The checkVariable method is used to verify whether the value of the current object's NlsString is the variable $.

[0018] Furthermore, during the SQL parsing process in Calcite, the position of the entire page is calculated and returned, including:

[0019] Create a node ExtendSqlOrderBy to record the pagination position;

[0020] In the OrderedQueryOrExpr method, add a list of SqlParserPos. This list of SqlParserPos records the positions from the start of the pagination keyword to the end of the entire pagination. At the end of the calcite SQL parsing process, the position of the entire pagination is calculated using the positions in the list of SqlParserPos. Then, an ExtendSqlOrderBy object is generated, and the entire pagination position is passed into it.

[0021] Furthermore, the calcite syntax tree is converted into a standard syntax tree with LimitNode nodes, including:

[0022] Obtain the node ExtedndSqlOrderBy from the syntax tree;

[0023] Assign the offset and fetch properties of the ExtendSqlOrderBy node to the offset and rowCount values ​​of the LimitNode variable.

[0024] Assign the page position attribute pagePos in the ExtendSqlOrderBy node to the position attribute pos of the LimitNode variable;

[0025] Set the LimitNode variable under the SelectNode and UnionCall nodes respectively to obtain a standard syntax tree with LimitNode nodes.

[0026] Furthermore, Calcite is used to convert different data sources into standard pagination SQL for the corresponding data sources, while maintaining the consistency of the pre-compiled order, including:

[0027] Parse the standard syntax tree to obtain all LimitNode nodes;

[0028] Beforehand, replace all characters at the positions of LimitNode nodes with special fixed-length characters, and replace the rest with spaces;

[0029] Iterate through all LimitNode nodes and convert different data sources into a specified pagination syntax based on the offset and rowCount of the LimitNode nodes. If the pagination is a variable, a pre-compiled placeholder is used for replacement; if the pagination is not a variable, the original number is used. During the processing, if the offset and rowCount are swapped before and after the pagination syntax conversion, the corresponding parameter values ​​in the pre-compiled parameter list are swapped to ensure the consistency of the data source pagination syntax.

[0030] Specifically, the pagination syntax styles include offset fetch, limit offset, offset limit, and limit n,m.

[0031] Secondly, a system based on Calcite for unified pagination across different data sources is provided, including:

[0032] The pagination variable addition module is used to add pagination variables to Calcite extension files;

[0033] The pagination position return module is used to calculate the position of the entire page during the SQL parsing process of Calcite and return the pagination position so that Calcite extension files can support returning the pagination position.

[0034] The syntax tree generation module is used to parse SQL and generate a Calcite syntax tree;

[0035] The syntax tree conversion module is used to convert calcite syntax trees into standard syntax trees with LimitNode nodes;

[0036] The variable order retrieval module is used to obtain the parameter order of variables. It matches the entire SQL with the variable's regular expression and records each parameter in the matching order.

[0037] The pagination conversion module is used to convert different data sources into standard pagination SQL for the corresponding data sources using Calcite, while maintaining the consistency of the corresponding pre-compiled order.

[0038] Thirdly, a computer-readable storage medium is provided that stores program code for execution by a device, the program code including steps for performing a method as described in any of the implementations of the first aspect.

[0039] Fourthly, an electronic device is provided, the electronic device including a processor, a memory, and a program or instructions stored in the memory and executable on the processor, wherein the program or instructions, when executed by the processor, implement the method as in any of the implementations of the first aspect.

[0040] This application has the following beneficial effects: This method solves the problem of Calcite pagination not supporting variables by adding pagination variables, and achieves unified pagination for different data sources based on Calcite. At the same time, recording each parameter according to the matching order ensures the order of parameter values ​​in the pre-compiled SQL, ensuring the correctness of pre-compilation execution. Using this method, pagination for all data sources can be unified in the API client, which can greatly reduce the difficulty for users to write pagination for different data sources, and make a pagination method usable in different data sources. The pagination conversion is performed at the underlying level, which is very simple and efficient in actual use. Attached Figure Description

[0041] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments of this application and their descriptions are used to explain this application and do not constitute an undue limitation of this application.

[0042] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0043] Figure 1 This is a flowchart of a method for achieving unified pagination across different data sources based on Calcite, as described in Embodiment 1 of this application.

[0044] Figure 2 This is a structural block diagram of a system based on Calcite to achieve unified pagination for different data sources, as described in Embodiment 2 of this application.

[0045] Figure 3 This is a schematic diagram of the internal structure of the electronic device according to Embodiment 4 of this application.

[0046] Figure label:

[0047] 100. Module for adding pagination variables; 200. Module for returning pagination position; 300. Module for generating syntax trees; 400. Module for converting syntax trees; 500. Module for obtaining variable order; 600. Module for converting pagination. Detailed Implementation

[0048] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0049] Example 1

[0050] The method for unified pagination across different data sources based on Calcite, as described in Embodiment 1 of this application, includes: adding pagination variables to the Calcite extension file; calculating the entire pagination position during the Calcite SQL parsing process and returning the pagination position so that the Calcite extension file supports returning the pagination position; parsing the SQL to generate a Calcite syntax tree; converting the Calcite syntax tree into a standard syntax tree with LimitNode nodes; obtaining the parameter order of the variables, matching the entire SQL using regular expressions of the variables, and recording each parameter in the matching order; and converting different data sources into standard pagination SQL for the corresponding data sources using Calcite, while maintaining consistency in the pre-compiled order.

[0051] Specifically, Figure 1 The flowchart of the method for unifying pagination across different data sources based on Calcite in Embodiment 1 of the application is shown, including:

[0052] S100. Add a pagination variable to the calcite extension file to support the format of pagination parameters as variables;

[0053] Specifically, in S101, modify the `parser.jj` file of `calcite` and add a new variable `DOLLAR_VARIBLE`. This variable is used to support variables with `${var}` parameters. Example:

[0054]

[0055] S102. Modify the `parser.jj` file of `calcite`, adding a conditional method `DollarVariableParam` to `UnsignedNumericLiteralOrParam`. The `UnsignedNumericLiteralOrParam` method is used to parse the numbers following the pagination keywords. Adding the following condition will support `${var}` variables. Example:

[0056]

[0057]

[0058] S103. Implement the `DollarVariableParam` method in the `parserImpls.ftl` file. The `DollarVariableParam` method contains two local variables: `SqlParserPos`, which records the position, and `value`, a `String` variable, which records the current token. It primarily detects when the current `TOKEN` is a `DOLLAR_VARIBLE` variable and generates a custom `ExtendDollarVariable` object. The `ExtendDollarVariable` object inherits from `SqlLiteral` and contains a constructor, an `unparse` method, and a `checkVariable` method. The constructor takes two parameters: `pos`, which records the position, and an `NlsString` object. It generates an `ExtendNlsString` object using the token's `value`, and then generates the `ExtendDollarVariable` object using the constructor. The `unparse` method outputs the current value, and the `checkVariable` method verifies that the current object's `NlsString` value is a `$` variable. After these operations, the compiled parsing code supports pagination with `$` variables. Supported example: `select id,name from test LIMIT${limitVar}OFFSET${offsetVar}`.

[0059] Example:

[0060]

[0061]

[0062] S200. Calculate the position of the entire page during the calcite SQL parsing process and return the page position so that the calcite extension file can support returning the page position. The purpose is to enable nodes that support the syntax parse tree to return the page position.

[0063] S201. Create a node ExtendSqlOrderBy to record the page position. This node inherits from SqlOrderBy and internally maintains a variable SqlParserPos to record the position of the entire page.

[0064] S202. In the process of parsing SQL in Calcite, the parsing of pagination is mainly done in the OrderedQueryOrExpr method. In the native Calcite, the OrderedQueryOrExpr method is the core method for parsing a select statement. It mainly includes three parts: parsing the query expression body, parsing the order by clause, and parsing the pagination statement. In order to obtain the position information of the entire pagination, a SqlParserPos list needs to be added to the OrderedQueryOrExpr method to record the position from the start of the pagination keyword to the end of the entire pagination. Then, at the end of the parsing, the position of the entire pagination is calculated by the position in the SqlParserPos list above, and then an ExtendSqlOrderBy object is generated, and the entire pagination position is passed into it.

[0065] S300: Parse SQL to generate the Calcite syntax tree SqlNode;

[0066] S400: Convert the calcite syntax tree into a standard syntax tree with LimitNode nodes;

[0067] Specifically, the first step of parsing the syntax tree generates an internal standard syntax tree. In the process of generating the standard syntax tree, the pagination processing is mainly done in two nodes: UnionCall and SelectNode. When parsing these two corresponding syntaxes, a new LimitNode variable is maintained inside each of the two nodes. The ExtendSqlOrderBy is obtained from the syntax tree, and the offset and fetch properties in ExtendSqlOrderBy are assigned to the offset and rowCount of LimitNode. The page position property pagePos in ExtendSqlOrderBy is also assigned to the position property pos of LimitNode, thus obtaining LimitNode. LimitNode is then set under SelectNode and UnionCall nodes. After this series of operations, the standard syntax tree with LimitNode nodes is obtained.

[0068] S500: Obtain the parameter order of the variable, match the entire SQL using the variable's regular expression, and record each parameter in the matching order;

[0069] S600 uses Calcite to convert different data sources into standard pagination SQL for the corresponding data sources, while maintaining the consistency of the corresponding pre-compiled order;

[0070] Specifically, in processing the standard syntax tree and obtaining the final complete pre-compiled SQL, the standard syntax tree is parsed to obtain all LimitNodes. Characters at the positions of all LimitNodes are pre-replaced with special fixed-length characters, and the remaining parts are replaced with spaces. This ensures that the `pos` value in LimitNodes does not become invalid due to inconsistencies in pagination conversion lengths during pagination replacement. Then, all LimitNodes are traversed, and conversions are performed according to different data sources. Currently, there are four main pagination standard styles: `limit n,m`, `offset fetch`, `limitoffset`, and `offset limit`. During the conversion, based on the `offset` and `rowCount` of the LimitNode, the pagination syntax can be converted to the specified style according to different data sources. If the pagination is a variable, a pre-compiled placeholder `?` is used for replacement; otherwise, the original number is used. Furthermore, during processing, for cases where the `offset` and `rowCount` are swapped before and after pagination syntax conversion, the corresponding parameter values ​​in the pre-compiled parameter list are also swapped. This process ensures a consistent pagination syntax across the data sources, even if the current data source does not support the pagination syntax, it will be converted to the correct pagination method at the underlying level.

[0071] Example 2

[0072] The system for unified pagination of different data sources based on Calcite, as described in Embodiment 2 of this application, includes:

[0073] The pagination variable addition module 100 is used to add pagination variables to Calcite extension files;

[0074] The pagination position return module 200 is used to calculate the position of the entire page during the calcite SQL parsing process and return the pagination position so that the calcite extension file can support returning the pagination position.

[0075] Syntax tree generation module 300 is used to parse SQL and generate Calcite syntax trees;

[0076] Syntax tree conversion module 400 is used to convert calcite syntax trees into standard syntax trees with LimitNode nodes;

[0077] The variable order acquisition module 500 is used to obtain the parameter order of variables. It matches the entire SQL with the variable's regular expression and records each parameter in the matching order.

[0078] The pagination conversion module 600 is used to convert different data sources into standard pagination SQL for the corresponding data sources using Calcite, while maintaining the consistency of the corresponding pre-compiled order.

[0079] The system in this embodiment can solve the problem that Calcite pagination does not support variables, and it achieves unified pagination for different data sources based on Calcite, while ensuring the order of pre-compiled SQL parameter values. This implementation can greatly reduce the difficulty for users to write pagination for different data sources, and a single pagination method can be used in different data sources. The pagination conversion is handled by the underlying layer, making it simple and efficient to use.

[0080] Example 3

[0081] This application relates to a computer-readable storage medium in embodiment 3, which stores program code for execution by a device, the program code including steps for performing the method as in any implementation of embodiment 1 of this application;

[0082] The computer-readable storage medium may be a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM); the computer-readable storage medium may store program code, and when the program stored in the computer-readable storage medium is executed by a processor, the processor is used to perform the steps of the method in any of the implementations of Embodiment 1 of this application.

[0083] Example 4

[0084] like Figure 3 As shown, an electronic device according to Embodiment 4 of this application includes a processor, a memory, and a program or instructions stored in the memory and executable on the processor. When the program or instructions are executed by the processor, they implement the method in any of the implementations in Embodiment 1 of this application.

[0085] The processor can be a general-purpose central processing unit (CPU), microprocessor, application-specific integrated circuit (ASIC), graphics processing unit (GPU), or one or more integrated circuits, used to execute related programs to implement the method in any of the implementations of Embodiment 1 of this application.

[0086] The processor can also be an integrated circuit electronic device with signal processing capabilities. In implementation, each step of the method in any of the implementations of Embodiment 1 of this application can be completed by the integrated logic circuitry in the processor's hardware or by software instructions.

[0087] The aforementioned processor can also be a general-purpose processor, a digital signal processor, an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this application can be directly embodied in the execution of a hardware decoding processor, or can be executed by a combination of hardware and software modules in the decoding processor. The software modules can be located in random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, or other mature storage media in the art. The storage medium is located in memory; the processor reads information from the memory and, in conjunction with its hardware, completes the functions required by the units included in the data processing apparatus of the embodiments of this application, or executes the methods in any implementation of Embodiment 1 of this application.

[0088] The above are merely preferred embodiments of this application; however, the scope of protection of this application is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in this application, based on the technical solution and its improved concept, should be covered within the scope of protection of this application.

Claims

1. A method for achieving unified pagination across different data sources based on Calcite, characterized in that, include: Add a pagination variable to the calcite extension file; The Calcite extension calculates the position of the entire page during the SQL parsing process and returns the page position so that the Calcite extension file can support returning the page position. Parse SQL to generate a Calcite syntax tree; Convert the calcite syntax tree into a standard syntax tree with LimitNode nodes; Obtain the parameter order of the variable, match the entire SQL using the variable's regular expression, and record each parameter in the matching order; Calcite converts different data sources into standard pagination SQL for the corresponding data sources while maintaining the consistency of the pre-compiled order; Specifically, during the SQL parsing process in Calcite, the position of the entire page is calculated and returned, including: Create a node ExtendSqlOrderBy to record the pagination position; In the OrderedQueryOrExpr method, add a list of SqlParserPos, where the list of SqlParserPos is used to record the positions from the beginning of the pagination keyword to the end of the entire pagination. At the end of the calcite SQL parsing, the position of the entire pagination is calculated using the positions in the list of SqlParserPos, and then an ExtendSqlOrderBy object is generated, passing the entire pagination position into it. Convert the calcite syntax tree into a standard syntax tree with LimitNode nodes, including: Obtain the node ExtedndSqlOrderBy from the syntax tree; Assign the offset and fetch properties of the ExtendSqlOrderBy node to the offset and rowCount values ​​of the LimitNode variable. Assign the page position attribute pagePos in the ExtendSqlOrderBy node to the position attribute pos of the LimitNode variable; Set the LimitNode variable under the SelectNode node and UnionCall node respectively to obtain a standard syntax tree with LimitNode nodes; Calcite converts different data sources into standard pagination SQL for the corresponding data sources, while maintaining the consistency of the pre-compiled order, including: Parse the standard syntax tree to obtain all LimitNode nodes; Beforehand, replace all characters at the positions of LimitNode nodes with special fixed-length characters, and replace the rest with spaces; Iterate through all LimitNode nodes and convert different data sources into a specified pagination syntax based on the offset and rowCount of the LimitNode nodes. If the pagination is a variable, a pre-compiled placeholder is used for replacement; if the pagination is not a variable, the original number is used. During the processing, if the offset and rowCount are swapped before and after the pagination syntax conversion, the corresponding parameter values ​​in the pre-compiled parameter list are swapped to ensure the consistency of the data source pagination syntax.

2. The method for achieving unified pagination across different data sources based on Calcite according to claim 1, characterized in that, Add pagination variables to the Calcite extension file, including: Add the variable DOLLAR_VARIBLE to the calcite extension file parser.jj; Add the DollarVariableParam method to the UnsignedNumericLiteralOrParam method in the calcite extension file parser.jj; Implement the DollarVariableParam method in the parserImpls.ftl file. The DollarVariableParam method contains a variable pos of type SqlParserPos to record the position and a variable value of type String to record the current token.

3. The method for achieving unified pagination across different data sources based on Calcite according to claim 2, characterized in that, Implement the DollarVariableParam method in the parserImpls.ftl file, including: Determine if the current TOKEN is a DOLLAR_VARIBLE variable; If the judgment result is yes, a custom ExtendDollarVariable object is generated. The ExtendDollarVariable object inherits from SqlLiteral. The ExtendDollarVariable object contains a constructor, an unparse method, and a checkVariable method. The constructor includes a pos object to record the position and an NlsString object. An ExtendNlsString object is generated using the variable value of the token. Then, the ExtendDollarVariable object is generated through the constructor. The unparse method is used to output the current value. The checkVariable method is used to verify whether the value of the current object's NlsString is the variable $.

4. The method for achieving unified pagination across different data sources based on Calcite according to claim 1, characterized in that, The pagination syntax styles include offset fetch, limit offset, offset limit, and limit n,m.

5. A system for unified pagination across different data sources based on Calcite, characterized in that, include: The pagination variable addition module is used to add pagination variables to Calcite extension files; The pagination position return module is used to calculate the position of the entire page during the SQL parsing process of Calcite and return the pagination position so that Calcite extension files can support returning the pagination position. The syntax tree generation module is used to parse SQL and generate a Calcite syntax tree; The syntax tree conversion module is used to convert calcite syntax trees into standard syntax trees with LimitNode nodes; The variable order retrieval module is used to obtain the parameter order of variables. It matches the entire SQL with the variable's regular expression and records each parameter in the matching order. The pagination conversion module is used to convert different data sources into standard pagination SQL for the corresponding data sources using Calcite, while maintaining the consistency of the corresponding pre-compiled order; Specifically, during the SQL parsing process in Calcite, the position of the entire page is calculated and returned, including: Create a node ExtendSqlOrderBy to record the pagination position; In the OrderedQueryOrExpr method, add a list of SqlParserPos, where the list of SqlParserPos is used to record the positions from the beginning of the pagination keyword to the end of the entire pagination. At the end of the calcite SQL parsing, the position of the entire pagination is calculated using the positions in the list of SqlParserPos, and then an ExtendSqlOrderBy object is generated, passing the entire pagination position into it. Convert the calcite syntax tree into a standard syntax tree with LimitNode nodes, including: Obtain the node ExtedndSqlOrderBy from the syntax tree; Assign the offset and fetch properties of the ExtendSqlOrderBy node to the offset and rowCount values ​​of the LimitNode variable. Assign the page position attribute pagePos in the ExtendSqlOrderBy node to the position attribute pos of the LimitNode variable; Set the LimitNode variable under the SelectNode node and UnionCall node respectively to obtain a standard syntax tree with LimitNode nodes; Calcite converts different data sources into standard pagination SQL for the corresponding data sources, while maintaining the consistency of the pre-compiled order, including: Parse the standard syntax tree to obtain all LimitNode nodes; Beforehand, replace all characters at the positions of LimitNode nodes with special fixed-length characters, and replace the rest with spaces; Iterate through all LimitNode nodes and convert different data sources into a specified pagination syntax based on the offset and rowCount of the LimitNode nodes. If the pagination is a variable, a pre-compiled placeholder is used for replacement; if the pagination is not a variable, the original number is used. During the processing, if the offset and rowCount are swapped before and after the pagination syntax conversion, the corresponding parameter values ​​in the pre-compiled parameter list are swapped to ensure the consistency of the data source pagination syntax.

6. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores program code for execution by the device, the program code including steps for performing the method as described in any one of claims 1-4.

7. An electronic device, characterized in that, The electronic device includes a processor, a memory, and a program or instructions stored in the memory and executable on the processor, wherein the program or instructions, when executed by the processor, implement the method as described in any one of claims 1-4.

Images