Low-code data service API development and treatment method

Abstract

The invention relates to the field of data management and authority control, and discloses a low-code data service API (Application Program Interface) development and treatment method, which comprises the following steps of: defining and registering an API interface, configuring a name, a request path, a data source and a basic query SQL (Structured Query Language) template of the API, and generating a unique identifier apiId; configuring a caller authorization list and a row-column level permission rule for the API; an external request is received, a request path is analyzed, a corresponding apiId is matched, and the authority is verified according to the identity of a caller; generating a dynamic SQL (Structured Query Language) statement based on the apiId and the authority rule, and rewriting the SQL statement; executing the rewritten SQL statement to obtain a query result; and carrying out standardization processing on the query result and returning. The API development process is simplified in a low-code mode, flexible dynamic SQL generation and refined authority control are supported, the safety, development efficiency and system response capability of data services are improved, and the method is suitable for complex and changeable data service scenes.

Description

Technical Field

[0001] The present invention relates to the technical field of data management and permission control, and specifically to a low-code data service API development and governance method. Background Art

[0002] As enterprises go deeper into digital transformation, data has become an important core asset. As an important means of data sharing and interaction, data service API plays a key role in enterprise informatization. Currently, most enterprises use data service API to achieve data interoperability between internal systems and docking with external business interfaces. However, the existing API development and governance methods have many deficiencies, resulting in the efficiency and security of data services being difficult to meet complex business needs.

[0003] Traditional API development usually requires developers to manually write a large amount of templated code, including interface definition, business logic implementation, and encapsulation of the database access layer. This development method is labor-intensive and requires high technical capabilities of developers. Especially in scenarios with frequent changes or rapid iterations, development efficiency is significantly reduced. In addition, the query logic of existing APIs usually uses static SQL statements, and query conditions need to be implemented through hard coding. When business requirements change, such as adjusting from precise queries to fuzzy queries, the code needs to be modified and redeployed, which cannot flexibly adapt to changing requirements, affecting the scalability of the system.

[0004] In terms of data permission control, most existing technologies only support coarse-grained API-level permission management, which restricts whether the caller can access a certain interface, but it is difficult to perform fine-grained control on the row or column level of data. This approach requires users with different roles to access different data through multiple independent interfaces, increasing the number of interfaces and the complexity of system maintenance. In addition, data permission rules are often static and cannot be dynamically adjusted according to business scenarios, which limits the flexibility and security of permission control.

[0005] At the same time, the existing API management methods lack the ability to manage the entire life cycle, such as version upgrades, online and offline management, and synchronization management of permission rule changes. This makes it easy for APIs to have problems in function expansion or operation and maintenance management, further increasing development and operation and maintenance costs. In terms of performance optimization, existing query engines often ignore the optimization of query efficiency under dynamic conditions and large data volume scenarios, resulting in insufficient system responsiveness.

[0006] In summary, the existing API development and governance methods have obvious deficiencies in development efficiency, query flexibility, permission control precision, full life cycle management, and performance optimization, and cannot meet the complex and ever-changing data service needs of enterprises. Therefore, a new technical solution is urgently needed that can simplify the API development process through low-code methods, support dynamic SQL generation and refined permission control, and realize API full life cycle management and system performance optimization, and comprehensively improve data service capabilities. Summary of the invention

[0007] In response to the shortcomings of the existing technology, the present invention provides a low-code data service API development and governance method, which solves the problems of low development efficiency, poor query flexibility, insufficient permission control granularity, and lack of full life cycle management of existing data service APIs.

[0008] To achieve the above objectives, the present invention is implemented through the following technical solutions: a low-code data service API development and governance method, comprising the following steps: Define and register the API interface, configure the API name, request path, data source, and basic query SQL template, and generate a unique identifier apiId; Configure the caller authorization list and row and column-level permission rules for each API. The row permission rules define the row-level access conditions for the caller to the data, and the column permission rules define the column-level access scope for the caller to the data. Receive external requests, parse the request path, match the corresponding apiId, and verify permissions based on the caller's identity; Generate a dynamic SQL statement containing permission filtering conditions based on the apiId and the caller's row and column permission rules; Rewrite dynamic SQL statements based on query conditions passed in by external requests; Execute the rewritten SQL statement to obtain the query results; The query results are converted into a standardized format and returned to the requester.

[0009] Preferably, the configuration of the row and column level permission rules includes: Row permission rules are dynamically configured to set up multi-condition combination filtering based on data attribute values; Column permission rules limit the range of fields that the caller can access through a field whitelist.

[0010] Preferably, the query conditions passed in by the external request include paging parameters and conditional filtering parameters, the paging parameters include page numbers and the number of records per page, and the conditional filtering parameters include matching rules for designated fields.

[0011] Preferably, the generated dynamic SQL statement includes: Load the basic SQL template corresponding to apiId from the cache or database; Insert the filtering conditions generated according to the row and column permission rules into the basic SQL template.

[0012] Preferably, rewriting the dynamic SQL statement includes: Insert exact match conditions, fuzzy query conditions, or range query conditions based on the query conditions in the external request; Use paging parameters to perform paging on dynamic SQL statements.

[0013] Preferably, the step of executing the rewritten SQL statement is completed by a data connector module, which is responsible for: Establish a connection with the target data source; Call a unified data operation interface that is compatible with the data source; Manage database connection pools.

[0014] Preferably, the step of converting the query results into a standardized format comprises: Return query results in JSON or XML format; Perform field mapping and formatting on query results.

[0015] Preferably, the step of verifying permissions based on the caller's identity includes: Load the row and column permission rules corresponding to apiId from the permission management module according to the caller identity; Reject external requests when the caller does not have API access permissions.

[0016] Preferably, when the permission rules are changed, the system automatically clears the cached SQL statements related to the changed rules.

[0017] The present invention also provides a low-code data service API development and governance system, including: API management module, which is used to define and register API interfaces, configure API names, request paths, data sources, and basic query SQL templates, and generate unique identifiers such as apiId; The permission management module is used to configure the authorization list and row-level permission rules of each API caller, and verify the caller's access rights when making a request; Dynamic query engine module, used to generate dynamic SQL statements based on apiId and row and column permission rules, and rewrite dynamic SQL statements based on external request conditions; The data connector module is used to connect to the target data source and execute dynamic SQL statements.

[0018] The present invention provides a low-code data service API development and governance method. It has the following beneficial effects: 1. The present invention simplifies the API definition and management process through a low-code development model. Developers can quickly complete API registration and configuration without writing lengthy templated codes, including the definition of basic SQL templates and the setting of permission rules, which greatly shortens the development cycle and improves development efficiency.

[0019] 2. The present invention provides a dynamic configuration mechanism for row and column-level permission rules, which can finely restrict the access scope of data according to the identity of the caller. For example, the caller can be restricted to access only the data of a specific department or certain specific fields. By dynamically adjusting the permission rules, the system can adapt to the complex needs of different business scenarios and ensure the security and compliance of data access.

[0020] 3. The dynamic query engine module of the present invention supports dynamic generation of SQL statements based on basic SQL templates, caller permission rules and external request conditions, and optimizes the execution plan of the database at the same time, significantly improving the execution efficiency of complex queries, and is particularly suitable for efficient query scenarios of large-scale data.

[0021] 4. The data connector module of the present invention shields the differences between different database types through a unified interface and supports seamless integration with multiple databases (such as MySQL, PostgreSQL, and Oracle). This compatibility not only reduces the development and operation and maintenance costs of the system, but also improves the flexibility of the system in a multi-database environment.

[0022] 5. The present invention standardizes the query results, including field screening, name mapping, paging information supplement, etc., and supports multiple formatted output methods such as JSON and XML. The unified response structure design simplifies the caller's data parsing work and improves the versatility and adaptability of the API.

[0023] 6. Through flexible dynamic SQL generation, multi-dimensional permission control and standardized response structure design, the present invention can adapt to the data service needs of enterprises in different business scenarios, such as complex multi-table joint query, real-time data sharing and multi-role data isolation, and has broad application prospects. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1 It is a schematic diagram of the method flow of the present invention; Figure 2 This is the API full life cycle management flow chart of the present invention; Figure 3 Schematic diagram of the system architecture of the present invention. DETAILED DESCRIPTION

[0025] The following will be combined with the drawings in the specification of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0026] Please see attached Figure 1 -Attached Figure 3 The present invention provides a low-code data service API development and governance method, which can realize rapid development, flexible query and refined permission control of data interfaces.

[0027] like Figure 1 As shown, the low-code data service API development and governance method may include the following steps: S1. Define and register API interface; S2. Configure the authorization list and row-level permission rules for each API caller; S3, receive external requests and parse the request path; S4, generating a dynamic SQL statement including permission filtering conditions; S5. rewrite the dynamic SQL statement according to the query conditions passed in by the external request; S6. Execute the rewritten SQL statement to obtain the query result; S7. Convert the query result into a standardized format and return it to the requester.

[0028] In this embodiment, step S1 mainly involves the definition and registration of the API interface, including the configuration of basic API information, the definition and management of SQL templates, the generation of the unique identifier apiId, and the support of version control functions. This step simplifies the development process of the API interface through low-code technical means, improves development efficiency, and lays the foundation for subsequent permission management and dynamic SQL generation.

[0029] As an option, developers can complete the definition and registration of APIs through the low-code development interface provided by the system (such as a graphical drag-and-drop tool or a form-based configuration page). Specifically, developers can enter or select information such as the API name, request path, associated data source, and basic query SQL template in the interface. It should be noted that the configuration of this information can be completed through visual operations without manual code writing, thereby lowering the development threshold.

[0030] In a possible implementation, the name of the API is user-defined and is used to describe the function of the API. For example, "query employee information interface" can be defined as the name of the API. The request path is the calling address of the API, which usually adopts the RESTful style, such as / api / v1 / employees, so that external callers can access the interface through the HTTP protocol.

[0031] Exemplarily, the data source refers to the target database or other data storage system associated with the API. In some embodiments, the data source may include a relational database (such as MySQL, PostgreSQL, Oracle) and a non-relational database (such as MongoDB, Redis). The system establishes a connection with the database by configuring the connection information of the data source (such as JDBC URL, user name, password, etc.) and binds the connection information to the API.

[0032] It is understandable that the basic query SQL template is the core logic of the API interface to operate data. Developers need to define a common SQL query statement based on actual business needs, which will be used as the basis for subsequent dynamic queries. Specifically, the SQL template can be a common query statement, for example: SELECT * FROM employees WHERE 1=1 It should be noted that this SQL template is a basic structure that supports the subsequent addition of dynamic conditions, such as permission filtering conditions and query conditions for external requests. In this way, developers only need to define the core logic of the SQL template without considering the specific dynamic condition splicing, thus simplifying the development work.

[0033] As an option, after the API definition is completed, the system automatically generates a unique identifier apiId to identify the API. apiId can be a string generated based on UUID (Universally Unique Identifier), such as 123e4567-e89b-12d3-a456-426614174000. In subsequent request processing, apiId will be used as the unique identifier of the API to quickly match external requests with the corresponding API.

[0034] In one possible implementation, the system also supports the version control function of the API. Specifically, when the API needs to be changed or upgraded, it can be managed by creating a new version. For example, the original version of / api / v1 / employees can be upgraded to / api / v2 / employees in subsequent function expansion, thereby ensuring the compatibility of the old and new versions. It should be noted that the version control function avoids adverse effects on the caller and makes the maintenance and iteration of the API more flexible.

[0035] In some embodiments, the system also supports change management of APIs, such as modifying the name, path, or data source configuration of a registered API. Such changes will trigger the system's automatic update mechanism to ensure that relevant configuration information is synchronously updated to other modules, such as the permission management module and the dynamic query engine module.

[0036] It should be noted that in some embodiments, in order to improve development efficiency and standardization, the system can also provide preset API templates. For example, for common single-table query operations, the system can pre-define a set of standardized API templates, and developers only need to select the target data table to complete the definition and registration of the API. This method further reduces the workload of developers while ensuring the consistency of interface design.

[0037] It is understandable that API definition and registration are the basic steps of the method of the present invention, and its core lies in quickly completing the basic configuration of the API through low-code tools and providing complete context information for subsequent permission management, dynamic query and data response. Through the API management module, developers can easily define, change and version control the interface, thereby greatly improving development efficiency and reducing the template code writing work that may exist in the traditional API development process.

[0038] Combination Figure 2 After the API registration is completed, the API management module will automatically record the registration information, including the basic configuration of the API, data source information and basic SQL template. This information will serve as the core input for subsequent modules (such as the permission management module and the dynamic query engine module) to support permission verification and dynamic SQL generation operations. In addition, Figure 2 As shown, the API management module also supports change management of registered APIs, such as modifying the API path or data source configuration to adapt to changing business needs.

[0039] In summary, the implementation of step S1 makes the definition and registration process of the API interface simple and efficient through a series of configuration and automation tools, which can well meet the needs of rapid development and iteration of enterprises, and at the same time provides strong support for the scalability and maintainability of the system.

[0040] In this embodiment, step S2 mainly involves the configuration of the authorization list and row-level permission rules for each API caller. This step aims to ensure the security and compliance of data through a flexible and fine-grained permission control mechanism, while supporting different callers to accurately define the access scope of data.

[0041] As an option, the configuration of the caller authorization list can be managed based on the dimension of user identity or role. Specifically, developers can specify accessible users or roles for each API through the low-code configuration interface. For example, for a data query API, the authorized user can be set to a specific user ID (such as "user1", "user2") or a specific role (such as "admin", "developer"). It should be noted that this user- or role-based authorization mechanism can achieve flexible management of API access rights, thereby adapting to different usage scenarios.

[0042] In a possible implementation, the system further supports the configuration of row and column level permission rules based on the caller authorization list. Specifically, the row and column level permission rules include row permission rules and column permission rules, which are used to limit the row level access scope and column level access scope of the caller to the data respectively.

[0043] It is understandable that row permission rules are used to define the data range that the caller can access, and are usually configured based on the filtering conditions of data attribute values. For example, developers can set row permission rules through the configuration interface, for example: Only the caller is allowed to access data whose department is "R&D"; Allows the caller to access only data where the job title contains "Engineer".

[0044] In the above configuration, the row permission rule can be composed of multiple conditions, which can be connected by logical operators (such as AND, OR). For example, for the combined filtering condition of department and position, its configuration can be expressed as: "The department is 'R&D Department' and the position contains 'Engineer'". This row permission rule based on multiple conditions can meet complex permission management needs.

[0045] As an option, column permission rules are used to limit the range of data fields that the caller can access. Specifically, column permission rules can be configured in the form of field whitelists, that is, only the caller is allowed to access the specified fields, and the rest of the fields will be automatically filtered by the system. For example, the column permission rules of an API can be set to only allow the caller to access the fields "name", "age", and "department", thereby achieving fine control over the scope of data access.

[0046] In a possible implementation, the configuration results of row and column-level permission rules will be stored in a structured manner in the permission management module so that they can be called later in the dynamic SQL generation process. For example, the system can store row permission rules in JSON format, including specific field names, operators, filter values ​​and other information, and store column permission rules as a field list. It should be noted that this structured storage method facilitates dynamic loading and parsing of rules.

[0047] In some embodiments, the system also supports dynamic adjustment of permission rules. Specifically, developers can modify the API authorization list and row-level permission rules at any time through the configuration interface. When the system detects that the permission rules have changed, it will automatically synchronize and update them to the permission management module and clear the relevant cache content to ensure the consistency of data access.

[0048] Exemplarily, dynamic adjustment of permission rule configuration can be applied to the following scenarios: When a new employee joins the company, add his / her user ID to the authorization list; When data sensitivity is upgraded, adjust column permission rules to hide some fields; When business requirements change, modify row permission rules to change the scope of data access.

[0049] It is understandable that the dynamic adjustment mechanism of permission rules not only improves the flexibility of permission management, but also ensures that callers can always access data based on the latest rules after the rules are changed.

[0050] In another possible implementation, the system supports version management of permission rule configuration. Specifically, each time a permission rule is modified, the system automatically generates a new rule version number, and developers can roll back to the historical version based on the version number to restore the previous configuration. It should be noted that this version management mechanism can effectively reduce the risks that may arise during the permission adjustment process.

[0051] like Figure 2 As shown, permission configuration is an important part of API full life cycle management. By setting row and column level permission rules for callers, the system implements a refined permission control mechanism. It is understandable that the configuration of permission rules not only supports static rules, but also allows dynamic adjustment. For example, for different business scenarios, the filter conditions of row permission rules or the field range of column permissions can be dynamically modified. The permission management module will synchronize the changed rules to the API management module in real time to provide the latest permission information for subsequent request processing.

[0052] It should be emphasized that the core of this step is to achieve precise control of the caller's access rights. By configuring the caller authorization list and row-level permission rules, the system can make fine-grained definitions of the access rights of different callers in different data dimensions, thereby ensuring data security while meeting the requirements for permission flexibility in business scenarios.

[0053] In summary, step S2 provides comprehensive support for API access control through the configuration of the caller authorization list and row and column level permission rules. Through the convenient operation of the low-code interface and the dynamic management mechanism of rules, the present invention can achieve precise control of data access and provide necessary permission constraint information for subsequent dynamic SQL generation.

[0054] In this embodiment, step S3 mainly involves receiving external requests, parsing request paths, matching corresponding apiIds, and verifying permissions based on the identity of the caller. This step ensures that external requests can be quickly and accurately matched to registered APIs through a series of automated processes, while also implementing dynamic verification of caller permissions, providing assurance for the security and correctness of subsequent data access.

[0055] As an option, external requests are sent through the standard HTTP protocol. The system supports common HTTP methods, such as GET, POST, PUT, DELETE, etc. Specifically, the external caller initiates a request by specifying the API request path (such as / api / v1 / employees) and related parameters (such as query conditions, paging information, etc.). It should be noted that when the system receives a request, it will automatically parse the request path and request header information to provide the necessary data for subsequent path matching and permission verification.

[0056] In one possible implementation, the system parses the request path through the API management module and matches it with the registered API path. The matching rule can be a complete match or a path parameterized match. For example: For the path / api / v1 / employees, the system can directly match the registered API.

[0057] For the path / api / v1 / employees / {id}, the system can extract the value of id through the path parameter matching mechanism and pass the value as a parameter to subsequent queries.

[0058] It is understandable that each API generates a unique identifier apiId when it is registered, which is used to identify the corresponding API after the path is matched. After the path is resolved successfully, the system passes the extracted apiId to the permission management module for further verification of the caller's permissions.

[0059] In some embodiments, the system supports extracting the identity information of the caller from the request header. For example, the identity information can be carried by JWT (JSON Web Token) or other forms of authentication tokens. After extracting the identity information, the system will pass the information to the permission management module for verification to ensure that the request comes from an authorized caller.

[0060] As an option, the permission verification process includes two parts: verification of API access rights and loading of row and column permission rules. Specifically, the system first checks whether the caller is in the API authorization list based on the caller's identity information. If the caller does not have access rights, the system will immediately reject the request and return an appropriate error message. For example, returning the HTTP status code 403 Forbidden means that the caller does not have access to the API.

[0061] It should be noted that if the caller has access rights, the system will further load its row and column permission rules for subsequent dynamic SQL generation. For example, the system can load the following information from the permission management module: Row permission rules: restrict the caller to access data of a certain department or position; Column permission rules: restrict the caller to access only certain specific fields.

[0062] For example, assume that the caller is user1, and his access rights are configured as follows: Authorization API: / api / v1 / employees Row permission rule: department = 'R&D department' AND position LIKE '%engineer%' Column permission rules: name, age, department After verification, the system will pass these permission rules as context information to the dynamic query engine module to ensure that the caller can only access data that complies with the permission rules.

[0063] In another possible implementation, the system supports a cache mechanism for permission verification. When the same caller accesses the same API multiple times, the system can directly load the verified permission results from the cache, thereby reducing the performance overhead caused by repeated verification. It should be noted that when the API permission configuration or the caller's identity information changes, the system will automatically clear the relevant cache content to ensure the security and consistency of data access.

[0064] Combination Figure 3It is understandable that when checking permissions, the permission management module will dynamically load the corresponding row and column permission rules according to the caller's identity. These permission rules are loaded in real time when the API is first called and stored in the cache of the permission management module to speed up the subsequent permission verification process. When the permission rules change, the system will automatically clear the relevant cache and load the updated permission information to ensure that the caller's permissions are always consistent with the system configuration.

[0065] In summary, step S3 successfully implements the processing and security control of external requests through path resolution, apiId matching and permission verification. Through caller identity authentication and dynamic loading of permission rules, the system can provide accurate context information for subsequent dynamic SQL generation, thereby ensuring the security and accuracy of data access.

[0066] In this embodiment, step S4 mainly involves generating a dynamic SQL statement containing permission filtering conditions according to the apiId and the row and column permission rules of the caller. By generating dynamic SQL statements, the system can accurately control the query scope of data in combination with the caller permission rules, thereby ensuring the security and flexibility of data access.

[0067] As an option, the system first obtains the basic SQL template associated with the current apiId from the API management module. The basic SQL template is predefined by the developer in step S1 and usually has a common query logic.

[0068] It should be noted that this basic SQL template only contains the basic structure of the query and does not contain any dynamic conditions. The subsequent dynamic SQL generation process will insert permission filtering conditions and external query conditions based on this template.

[0069] In one possible implementation, the system rewrites the basic SQL template according to the caller's row permission rules, specifically including converting the permission rules into filter conditions for SQL statements. For example, if the caller's row permission rules are "only data with the department 'R&D' is allowed to be accessed" and "only data with the job title containing 'engineer' is allowed to be accessed", the system will insert the following filter conditions into the basic SQL template: AND department = 'R&D Department' AND position LIKE '% Engineer%' The rewritten SQL statement is as follows: SELECT * FROM employees WHERE 1=1 AND department ='R&D department' AND position LIKE'%engineer%' It should be noted that the dynamic insertion of row permission rules can flexibly combine multiple conditions through logical operators (such as AND, OR) to meet complex permission configuration requirements.

[0070] As an option, the system will also filter the query fields according to the caller's column permission rules, thereby limiting the range of fields that the caller can access. Column permission rules are usually expressed in the form of a field whitelist, for example, only allowing access to the fields name, age, and department. In this case, the system will replace SELECT * in the basic SQL template with the specified field list: SELECT name, age, department The final generated dynamic SQL statement is: SELECT name, age, department FROM employees WHERE 1=1 AND department='R&D department' AND position LIKE'%engineer%' It is understandable that the dynamic insertion of row and column permission rules and field filtering are the core steps for the system to generate dynamic SQL statements, and its purpose is to achieve fine-grained control over the scope of data access through the splicing of dynamic conditions.

[0071] In some embodiments, in order to improve performance and security, the system also supports hierarchical management of the dynamic SQL statement generation process. For example, the insertion of row permission rules can be completed at the logical layer, while the filtering of column permission rules can be completed at the physical layer (database query execution layer). This hierarchical management method not only optimizes query efficiency, but also can further reduce the implementation complexity of the system.

[0072] In another possible implementation, the system supports a caching mechanism for the dynamic SQL statement generation process. Specifically, when the same caller accesses the same API multiple times and the permission rules have not changed, the system can directly load the generated dynamic SQL statements from the cache, thereby avoiding the performance overhead caused by repeated calculations. It should be noted that when the permission rules or the basic SQL template of the API change, the system will automatically clear the relevant cache content to ensure that the generated SQL statements are consistent with the latest permission configuration.

[0073] For example, if the caller accesses the employee information query API, and its row and column permission rules are: Row permissions: department = 'R&D department' AND position LIKE '%engineer%' Column permissions: name, age, department The dynamic SQL statement generated by the system may be as follows: SELECT name, age, department FROM employees WHERE department = 'R&D Department' AND position LIKE '% Engineer%' Combination Figure 3 It is understandable that the dynamic query engine module is not only responsible for generating dynamic SQL statements, but also supports the optimization of SQL statements. For example, after generating SQL, the dynamic query engine will request the database engine to analyze the query plan to select the optimal execution path. This optimization mechanism can significantly improve the performance of complex queries, especially when processing large-scale data.

[0074] In summary, step S4 in this embodiment realizes the automatic generation of dynamic SQL statements by dynamically inserting row and column permission rules. Through the loading of basic SQL templates, dynamic splicing of permission rules and field filtering operations, the system can provide accurate statement support for subsequent query execution and ensure the security and compliance of data access. In addition, combined with the cache optimization mechanism and hierarchical management strategy, the present invention further improves the performance and flexibility of dynamic SQL generation.

[0075] In this embodiment, step S5 mainly involves rewriting the dynamic SQL statement according to the query conditions passed in by the external request. This step is intended to further enhance the flexibility of the dynamic SQL statement, so that it can dynamically respond to the query requirements of the external caller and efficiently execute the query based on the row and column permission rules.

[0076] As an option, the query conditions included in the external request may include but are not limited to field filter conditions, fuzzy query conditions, range query conditions and paging parameters. After receiving the external request, the system will parse these query conditions and combine them with the dynamic SQL statement to complete the rewriting of the SQL statement.

[0077] Specifically, the query condition is parsed by the API management module, which extracts query parameters from the request, such as the value range of the field, the matching mode, or the paging information. In one possible implementation, the query condition is formatted in a structured form, such as a JSON object, to facilitate subsequent processing. For example, an external request may contain the following query conditions: Query condition: age BETWEEN 20 AND 30 Paging parameters: pageIndex = 0 and pageSize = 20 It should be noted that these query conditions are usually passed in a form customized by the caller, and the system needs to dynamically parse and convert them into a standard SQL format.

[0078] In another possible implementation, the system dynamically inserts the parsed query condition into the SQL statement. For example, assuming the original dynamic SQL statement is: SELECT name, age, department FROM employees WHERE department = 'R&D Department' AND position LIKE '% Engineer%' When the external request contains the query condition age BETWEEN 20 AND 30, the system inserts the condition into the WHERE clause. The rewritten SQL statement is as follows: SELECT name, age, department FROM employees WHERE department = 'R&D Department' AND position LIKE '%engineer%' AND age BETWEEN 20 AND 30 It is understandable that the processing of paging parameters is performed after the query condition is rewritten. The system will calculate the paging offset and number of records based on the pageIndex and pageSize values ​​provided in the request, and append the paging parameters to the end of the SQL statement. For example, when the paging parameters are pageIndex = 0 and pageSize = 20, the system will add the following statement to the SQL: LIMIT 20 OFFSET 0 The final rewritten SQL statement is as follows: SELECT name, age, department FROM employees WHERE department = 'R&D Department' AND position LIKE '%engineer%' AND age BETWEEN 20 AND 30 LIMIT 20 OFFSET 0 It should be noted that the insertion order of the query conditions and paging parameters is optimized to ensure the execution efficiency of the SQL statement. Generally, the query conditions are inserted into the WHERE clause first, and the paging parameters are appended in the LIMIT or OFFSET part.

[0079] In some embodiments, the system also supports rewriting complex query conditions, including combined conditions of multiple fields. For example, an external request may require "querying employee data where the age is between 20 and 30 and the name contains 'Zhang'". At this time, the system will dynamically parse the query conditions into the following filtering logic: AND age BETWEEN 20 AND 30 AND name LIKE '%Zhang%' and insert it into the dynamic SQL statement to generate the final query statement.

[0080] As an option, during the process of rewriting the dynamic SQL statement, the system strictly follows the row and column permission rules of the caller. For example, even if the caller passes in a field filtering condition, the field must be within the column permissions of the caller. If the column permission rules of the caller do not authorize access to a certain field, the system will automatically ignore the query condition for that field and return an appropriate prompt message in the response.

[0081] In a possible implementation, the system supports syntax checking of the rewritten SQL statement to ensure the correctness and executability of the SQL statement. Specifically, the system will simulate the execution of the query logic once after the SQL statement is generated (without actually accessing the database) to capture possible syntax errors or conflicts.

[0082] It can be understood that the process of rewriting the dynamic SQL statement is based on the comprehensive processing of the caller's permission rules and the query conditions of the external request. By dynamically parsing and inserting the query conditions, the system can flexibly adapt to the diverse needs of external requests while ensuring the security and compliance of data access.

[0083] In summary, step S5 in this embodiment successfully realizes the rewriting of the dynamic SQL statement through the dynamic parsing and insertion of query conditions, the efficient calculation of paging parameters, and the strict verification of permission rules. While ensuring data security, the system can quickly respond to the query needs of the caller and provide efficient and accurate query statement support for subsequent data execution.

[0084] In this embodiment, step S6 mainly involves executing the rewritten dynamic SQL statement and obtaining the query result. This step interacts with the target data source through the data connector module to ensure that the rewritten SQL statement can be executed efficiently and correctly, and returns the result to the subsequent processing module.

[0085] As an option, the data connector module is a bridge for the system to interact with the data source, responsible for managing database connections, executing SQL statements and returning query results. Specifically, the data connector module supports a variety of data sources, including but not limited to relational databases (such as MySQL, PostgreSQL, Oracle) and non-relational databases (such as MongoDB). The system shields the differences between different data sources through a unified interface calling mechanism, so that developers do not need to worry about the underlying implementation.

[0086] In one possible implementation, before executing a dynamic SQL statement, the system first establishes a connection with the target data source through the data connector module. The data connector module initializes the connection pool according to the data source configuration (such as database URL, user name, password, etc.) provided by the API management module, and obtains an available database connection from the connection pool. It should be noted that the introduction of the connection pool can significantly improve the performance and resource utilization of the system, especially in high-concurrency scenarios, reducing the overhead of frequently establishing and closing database connections.

[0087] It is understandable that the execution of the dynamic SQL statement is completed by the data connector module. The system submits the rewritten dynamic SQL statement in step S5 to the data connector module, which is responsible for interacting with the database and executing the query. For example, for a query statement: SELECT name, age, department FROM employees WHERE department = 'R&D Department' AND position LIKE '%engineer%' AND age BETWEEN 20 AND 30 LIMIT 20 OFFSET 0 The data connector module sends the statement to the target database and obtains the query results.

[0088] In some embodiments, in order to ensure the integrity and consistency of the query results, the data connector module supports transaction management functions. Specifically, in some scenarios where multiple SQL statements need to be executed together, the data connector module will encapsulate these statements in a transaction to ensure that the results are submitted only after all statements are successfully executed. If any of the statements fails to execute, the system will automatically roll back the transaction to avoid data inconsistency.

[0089] As an option, the data connector module also supports preliminary processing of query results. For example, when the query results contain fields that the caller is not authorized to access, the data connector module will automatically filter out these fields according to the column permission rules to ensure that the returned data complies with the permission configuration.

[0090] In one possible implementation, the system optimizes the performance of the execution process of dynamic SQL statements to improve query efficiency. For example, the data connector module supports the execution plan analysis of SQL statements. Before the SQL is submitted for execution, the system will request the database to optimize the statement, such as selecting the best index or adjusting the connection order. It should be noted that the optimization of the execution plan can significantly improve the response speed of complex queries, especially when processing large-scale data.

[0091] For example, if the query statement involves multi-table association or sorting operations, the data connector module can request the database engine to generate an execution plan and adjust the query structure according to the returned plan. For example, for a complex query statement: SELECT e.name, e.age, d.department_name FROM employees JOIN departments d ON e.department_id = d.department_id WHERE e.age>25 ORDER BY e.name The data connector module requests the database to select the best index and optimize the sorting method of the ORDER BY operation to reduce query time.

[0092] In some embodiments, in order to further improve performance, the data connector module supports a caching mechanism for query results. Specifically, when the same caller accesses the same API multiple times and the query conditions have not changed, the system can directly obtain the query results from the cache without repeatedly executing the SQL statement. It should be noted that when the configuration or permission rules of the API change, the system will automatically clear the relevant cache content to ensure that the returned data is always the latest.

[0093] Combination Figure 3It is understandable that the data connector module supports seamless switching of multiple database types, such as MySQL, PostgreSQL, and Oracle. Specifically, the system shields the differences between different databases through a standardized interface call mechanism, so that API developers do not need to pay attention to the implementation details of the underlying database. In addition, the data connector module also supports automatic formatting of query results, such as directly converting the result set of a relational database into a JSON object, which facilitates subsequent standardized processing.

[0094] In summary, step S6 completes the execution and result acquisition of dynamic SQL statements through the data connector module. Through a unified data source interface, transaction management, execution plan optimization, and cache mechanism, the system can not only efficiently process complex queries, but also ensure that the query results comply with the caller's authority rules, providing reliable data support for subsequent standardized processing.

[0095] In this embodiment, step S7 mainly involves converting the query results into a standardized format and returning them to the requesting party. This step ensures that the returned data complies with the response specification of the API design and meets the caller's requirements for the data format by standardizing the query results.

[0096] As an option, the standardization of query results is completed by the API management module, which is responsible for parsing and formatting the original query results after the dynamic SQL statement is executed. It should be noted that the core of standardization is to convert the query results returned by the database into the response format expected by the caller, such as JSON or XML format.

[0097] As an option, the standardization of query results is completed by the API management module, which is responsible for parsing and formatting the original query results after the dynamic SQL statement is executed. It should be noted that the core of standardization is to convert the query results returned by the database into the response format expected by the caller, such as JSON or XML format.

[0098] In one possible implementation, the system supports mapping field names to meet the caller's requirements for field naming. For example, the field name in the database can be mapped to fullName in the response, and the field age can be mapped to employeeAge. The mapping rules are defined by the configuration file of the API management module, and the system dynamically loads these rules during standardization processing.

[0099] For example, if the query result is: [ {"name": "Zhang San", "age": 25, "department": "R & D Department", "sensitive_info": "Internal Confidential"}, {"name": "Li Si", "age": 30, "department": "R & D Department", "sensitive_info": "Internal Confidential"} After filtering by column permission rules and mapping field names, the final standardized result is: {"fullName": "Zhang San", "employeeAge": 25}, {"fullName": "Li Si", "employeeAge": 30} It can be understood that field filtering and name mapping are key steps in the standardization process, aiming to ensure that the structure of the query result is consistent with the caller's expectations and prevent unauthorized data leakage.

[0100] In some embodiments, the system also supports supplementing paging information for the query result. For example, when the caller specifies paging parameters (such as pageIndex and pageSize) in the request, the system will add paging metadata to the response, such as the total number of records, the current page number, and the number of records per page. Exemplarily, the paging metadata can be represented as: { "data": {"fullName": "Zhang San", "employeeAge": 25}, {"fullName": "Li Si", "employeeAge": 30} , "pagination": { "pageIndex": 0, "pageSize": 2, "totalRecords": 100 } } It should be noted that the generation of paging information is based on the query result statistics information returned by the data connector module, such as the result of the COUNT query.

[0101] ​​​In another possible implementation, the system supports switching between multiple response formats, such as JSON, XML, or CSV. Specifically, the caller can specify the expected response format through the Accept parameter in the request header, and the system will dynamically select the corresponding serialization method based on the parameter. For example: If the caller requests the JSON format, the system will serialize the query results into a standard JSON string.

[0102] If the caller requests XML format, the system will generate structured data that complies with the XML specification.

[0103] If the caller requests CSV format, the system will return the query results as comma-delimited text.

[0104] It is understandable that the diverse support for response formats can meet the personalized needs of different callers and improve the applicability of the API.

[0105] As an option, the system will also perform a security check on the data before returning the query results. For example, for fields containing sensitive information, the system can mask the field content through permission rules or configuration file settings (such as replacing the middle part of the ID number with asterisks). For example, the field id_number may be processed as follows: {"id_number":"1234****5678"} This processing method can protect the privacy of sensitive information while providing data.

[0106] like Figure 3 As shown in the figure, after completing the standardized processing of the query results, the API management module will generate a unified response structure, including the data part and the metadata part. For example, the system will merge the data part of the query result with the paging metadata, and serialize it according to the response format specified by the caller (such as JSON or XML). It should be noted that this standardized design of the response structure not only simplifies the caller's work of parsing data, but also improves the versatility and compatibility of API services.

[0107] In summary, step S7 in this embodiment successfully converts the query result into standardized response data through field screening, name mapping, paging information supplementation and formatting. By supporting multiple response formats and security checks, the system can not only meet the personalized needs of the caller, but also ensure the security and consistency of the data, providing a complete data service capability for the present invention.

[0108] In general, the present invention defines and registers the API interface through the API management module, configures the request path, data source and basic SQL template, and generates a unique identifier apiId; configures row and column level permission rules through the permission management module to achieve refined access control; after receiving external requests, parses the path and verifies permissions, and dynamically embeds permission rules into SQL statements; dynamically rewrites SQL statements in combination with external query conditions, and executes the rewritten SQL statements through the data connector module to obtain query results; finally, the query results are standardized, including field screening, name mapping and paging information supplement, and returned to the requester. Through low-code development and flexible governance, the present invention greatly improves the development efficiency, security and responsiveness of data service APIs, and is suitable for complex and changeable data service scenarios.

[0109] The low-code data service API development and governance system described below and the low-code data service API development and governance method described above can be referenced to each other.

[0110] Please see attached Figure 3 The present invention also provides a low-code data service API development and governance system, including: API management module, which is used to define and register API interfaces, configure API names, request paths, data sources, and basic query SQL templates, and generate unique identifiers such as apiId; The permission management module is used to configure the authorization list and row-level permission rules of each API caller, and verify the caller's access rights when making a request; Dynamic query engine module, used to generate dynamic SQL statements based on apiId and row and column permission rules, and rewrite dynamic SQL statements based on external request conditions; The data connector module is used to connect to the target data source and execute dynamic SQL statements.

[0111] The system of this embodiment can be used to execute the above method embodiments, and its principles and technical effects are similar, which will not be repeated here.

[0112] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A low-code data service API development and governance method, characterized by: The following steps are involved: Define and register the API interface, configure the API name, request path, data source, and basic query SQL template, and generate a unique identifier apiId; Configure the caller authorization list and row and column-level permission rules for each API. The row permission rules define the row-level access conditions for the caller to the data, and the column permission rules define the column-level access scope for the caller to the data. Receive external requests, parse the request path, match the corresponding apiId, and verify permissions based on the caller's identity; Generate a dynamic SQL statement containing permission filtering conditions based on the apiId and the caller's row and column permission rules; Rewrite dynamic SQL statements based on query conditions passed in by external requests; Execute the rewritten SQL statement to obtain the query results; The query results are converted into a standardized format and returned to the requester.

2. A low-code data service API development and governance method according to claim 1, characterized in that: The configuration of the row and column level permission rules includes: Row permission rules are dynamically configured to set up multi-condition combination filtering based on data attribute values; Column permission rules limit the range of fields that the caller can access through a field whitelist.

3. A low-code data service API development and governance method according to claim 1, characterized in that: The query conditions passed in by the external request include paging parameters and conditional filtering parameters. The paging parameters include page numbers and the number of records per page. The conditional filtering parameters include matching rules for specified fields.

4. A low-code data service API development and governance method according to claim 1, characterized in that: The generated dynamic SQL statement includes: Load the basic SQL template corresponding to apiId from the cache or database; Insert the filtering conditions generated according to the row and column permission rules into the basic SQL template.

5. A low-code data service API development and governance method according to claim 1, characterized in that: The rewriting of the dynamic SQL statement includes: Insert exact match conditions, fuzzy query conditions, or range query conditions based on the query conditions in the external request; Use paging parameters to perform paging on dynamic SQL statements.

6. A low-code data service API development and governance method according to claim 1, characterized in that: The step of executing the rewritten SQL statement is completed by the data connector module, which is responsible for: Establish a connection with the target data source; Call a unified data operation interface that is compatible with the data source; Manage database connection pools.

7. A low-code data service API development and governance method according to claim 1, characterized in that: The step of converting the query results into a standardized format comprises: Return query results in JSON or XML format; Perform field mapping and formatting on query results.

8. A low-code data service API development and governance method according to claim 1, characterized in that: The step of verifying permissions based on the caller's identity includes: Load the row and column permission rules corresponding to apiId from the permission management module according to the caller identity; Reject external requests when the caller does not have API access permissions.

9. A low-code data service API development and governance method according to claim 1, characterized in that: When the permission rules are changed, the system automatically clears the cached SQL statements related to the changed rules.

10. A low-code data service API development and governance system, applied to the method according to any one of claims 1 to 9, characterized in that: include: API management module, which is used to define and register API interfaces, configure API names, request paths, data sources, and basic query SQL templates, and generate unique identifiers such as apiId; The permission management module is used to configure the authorization list and row-level permission rules of each API caller, and verify the caller's access rights when making a request; Dynamic query engine module, used to generate dynamic SQL statements based on apiId and row and column permission rules, and rewrite dynamic SQL statements based on external request conditions; The data connector module is used to connect to the target data source and execute dynamic SQL statements.

Images