A method and system for dynamically compiling views

Abstract

The present application relates to the technical field of view access, and provides a method and system for dynamically compiling a view, comprising: when a view is created, constructing a query tree structure and a database dependent object table dependent on the view, and saving the constructed query tree structure and the database dependent object table into a view system table; when a view reference is parsed, performing a dependency check on the database dependent object table in the view system table; and according to the dependency check result, dynamically compiling the view. The method and system for dynamically compiling a view can reduce development work; when a database object dependent on the view changes, only the OID information of the database object in the existing query tree of the view needs to be updated, and the entire query tree does not need to be regenerated; and by updating the database dependent object table and the query tree structure to the view system table after being updated again, the latest query tree structure can be obtained when the view is accessed again, and manual deletion and reconstruction of the view at the application layer are avoided.

Description

Technical Field

[0001] This invention relates to the field of view access technology, and more particularly to a method and system for dynamically compiling views. Background Technology

[0002] A view is a virtual table whose structure and data are derived from one or more base tables. It can be queried like a regular table, but it doesn't actually store any data. In databases, views can be seen as a way to access data; they hide the complexity of the underlying tables, providing a simple and easy-to-understand data access interface, facilitating users' querying, analysis, and reporting operations.

[0003] When creating a view, openGauss parses the queries that the view depends on into a query tree structure, serializes the query tree structure, and saves it to a system table. When other SQL statements access the view, they retrieve the serialized query tree from the system table, deserialize it, and merge it into the query tree of the current SQL statement, thus enabling view access. However, when generating the query tree, openGauss converts all database objects into corresponding object identifiers (OIDs). OIDs are internal identifiers, while SQL operations are performed using database object names. When database objects are deleted and recreated, or recompiled (DDL operations), the OIDs of the database objects change. This causes the OIDs in the view's query tree information to become invalid, and the changed database object OIDs are unpredictable, leading to view failure.

[0004] Currently, openGauss uses dependency mechanisms to detect view failures, informing users that a view has expired and allowing them to take appropriate action. However, these dependency mechanisms are relatively coarse-grained, and dependency checks are primarily performed when database objects are deleted. Therefore, when database objects such as tables and partitions that a view depends on are rebuilt or recompiled, the view is either cascaded down when the dependent objects are deleted, or the view becomes invalid during subsequent execution due to the absence of the dependent objects. Users who wish to continue using the view must manually recreate it.

[0005] Therefore, how to provide a view compilation method that dynamically compiles the view when changes to the database object are detected, re-updates the query tree structure of the view, and avoids operation failures or view re-creation due to view invalidation has become an urgent technical problem to be solved. Summary of the Invention

[0006] In view of this, in order to overcome the shortcomings of the prior art, the present invention aims to provide a method and system for dynamically compiling views.

[0007] According to a first aspect of the present invention, a method for dynamically compiling views is provided, the method comprising:

[0008] When creating a view, construct the query tree structure that the view depends on and the database dependency object table, and save the constructed query tree structure and database dependency object table to the view system table;

[0009] When resolving view references, a dependency check is performed on the database dependency object tables in the view system tables;

[0010] The view is dynamically compiled based on the dependency check results.

[0011] Preferably, in the method for dynamically compiling views according to the present invention, when creating a view, a query tree structure and a database dependency object table are constructed, and the constructed query tree structure and database dependency object table are saved to the view system table, including:

[0012] When creating a view, the query tree structure of the view's dependencies is constructed by parsing the query statements that the view depends on.

[0013] By extracting the database object information that the view depends on from the constructed query tree structure, and by saving the extracted database object information that the view depends on, a database dependency object table is constructed.

[0014] The query tree structure and database dependency object tables are serialized and saved to the system tables.

[0015] Preferably, in the method for dynamically compiling views according to the present invention, the database object information includes the object identifier, name, and type information of the database object.

[0016] Preferably, in the method for dynamically compiling views according to the present invention, when resolving view references, a dependency check is performed on the database dependency object table in the view system table, including: when other SQL statements access the view, obtaining the query tree structure that the view depends on from the view system table, and before deserializing and merging the obtained query tree structure into the query tree of the current SQL statement, performing a dependency check on the database dependency object table that the view depends on.

[0017] Preferably, in the method for dynamically compiling views according to the present invention, when resolving view references, a dependency check is performed on the database dependency object table in the view system table, including: obtaining the database dependency object table from the view system table and checking whether the object identifier of each database dependency object in the database dependency object table is valid.

[0018] Preferably, in the method for dynamically compiling a view according to the present invention, dynamically compiling the view based on the dependency check results includes:

[0019] If all object identifiers of the database dependent objects in the database dependent object table are valid, execute the view access;

[0020] If one or more database dependency objects in the database dependency object table have invalid object identifiers, the database dependency objects are resolved, and the view is dynamically compiled based on the resolution results.

[0021] Preferably, in the method for dynamically compiling a view according to the present invention, if one or more database dependency objects in the database dependency object table have invalid object identifiers, the database dependency objects are resolved, and the view is dynamically compiled based on the resolution result, including:

[0022] The database dependency objects are resolved based on their names and types, and the database dependency objects that meet the view dependency conditions are queried based on the resolution results.

[0023] When no database dependency object matching the view dependency conditions is found, the dependency check ends, the view is determined to be invalid, and a view error message is sent to the user.

[0024] When a database dependency object that matches the view dependency conditions is found, the database dependency object table and query tree structure of the view are updated based on the object identifier of the found database dependency object.

[0025] Preferably, in the method for dynamically compiling a view according to the present invention, updating the database dependency object table and query tree structure on which the view depends includes: updating the object identifiers of the corresponding objects in the database dependency object table and query tree structure on which the view depends based on the object identifiers of the queried database dependency objects, and serializing and saving the updated database dependency object table and query tree structure to the view system table.

[0026] According to a second aspect of the present invention, a system for dynamically compiling views is provided. The system includes a dynamic view compilation server, which is configured to: construct a query tree structure and a database dependency object table when creating a view, and save the constructed query tree structure and database dependency object table to a view system table; perform dependency checks on the database dependency object table in the view system table when resolving view references; and dynamically compile the view based on the dependency check results.

[0027] Preferably, the dynamic compilation view server of the system of the present invention includes:

[0028] The view creation module is used to build the query tree structure and database dependency object table that the view depends on when creating a view, and save the built query tree structure and database dependency object table to the view system table.

[0029] The View Resolution module is used to perform dependency checks on the database dependency object tables in the view system tables when resolving view references.

[0030] The dynamic compilation module is used to dynamically compile views based on the results of dependency checks.

[0031] According to a third aspect of the present invention, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first aspect of the present invention.

[0032] The method and system for dynamically compiling views in this invention can effectively utilize the existing query tree structure and view system table in the openGauss database, concentrating the dynamic compilation work in the semantic processing stage of the database. This limits the scope of impact and reduces a significant amount of development work. When the database objects that the view depends on change, only the OID information of the database objects in the existing query tree of the view needs to be updated, without regenerating the entire query tree. By updating the updated database dependency object table and query tree structure to the view system table, the latest query tree structure can be obtained when the view is accessed again, avoiding the need for the application layer to manually delete and rebuild the view. Attached Figure Description

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

[0034] Figure 1 This is a schematic diagram of a system for a method of dynamically compiling views applicable to embodiments of this application;

[0035] Figure 2 This is an example architecture diagram of a dynamic compilation view server for a system based on the dynamic compilation view of the present invention.

[0036] Figure 3 This is a flowchart illustrating the steps of a method for dynamically compiling a view according to an embodiment of the present invention;

[0037] Figure 4 This is a flowchart illustrating the execution of a method for dynamically compiling views according to an embodiment of the present invention.

[0038] Figure 5 This is a schematic diagram of the structure of the device provided by the present invention. Detailed Implementation

[0039] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0040] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0041] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0042] Figure 1 An exemplary system for a method of dynamically compiling views applicable to embodiments of this application is shown. For example... Figure 1 As shown, the system may include a dynamic compilation view server 101, a communication network 102, and / or one or more dynamic compilation view clients 103. Figure 1 The example in the text is a client 103 with multiple dynamically compiled views.

[0043] The dynamically compiled view server 101 can be any suitable server used to store information, data, programs, and / or any other suitable type of content. In some embodiments, the dynamically compiled view server 101 can perform appropriate functions. For example, in some embodiments, the dynamically compiled view server 101 can be used to dynamically compile views. As an optional example, in some embodiments, the dynamically compiled view server 101 can be used to implement dynamically compiled views through dependency checking. For example, the dynamically compiled view server 101 can be used to construct the query tree structure and database dependency object table that the view depends on when creating the view, and save the constructed query tree structure and database dependency object table to the view system table; when resolving view references, perform dependency checks on the database dependency object table in the view system table; and dynamically compile the view based on the dependency check results.

[0044] Figure 2 This is an example architecture diagram of a dynamic compilation view server for a system based on the dynamic compilation view of the present invention, as shown below. Figure 2As shown, the dynamic compilation view server in the system of the dynamic compilation view of this embodiment of the invention includes:

[0045] The view creation module is used to build the query tree structure and database dependency object table that the view depends on when creating a view, and save the built query tree structure and database dependency object table to the view system table.

[0046] The View Resolution module is used to perform dependency checks on the database dependency object tables in the view system tables when resolving view references.

[0047] The dynamic compilation module is used to dynamically compile views based on the results of dependency checks.

[0048] As another example, in some embodiments, the dynamic compilation view server 101 may send the dynamic compilation view method to the dynamic compilation view client 103 for user use, based on a request from the dynamic compilation view client 103.

[0049] As an optional example, in some embodiments, the dynamic compilation view client 103 is used to provide a visual dynamic compilation interface, which is used to receive a selection input operation of the user's dynamic compilation view, and to obtain and display the dynamic compilation interface corresponding to the option selected by the selection input operation from the dynamic compilation view server 101 in response to the selection input operation, wherein the dynamic compilation interface displays at least information of the dynamic compilation view and operation options for the information of the dynamic compilation view.

[0050] In some embodiments, communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, communication network 102 can include any one or more of the following: the Internet, intranet, wide area network (WAN), local area network (LAN), wireless network, digital subscriber line (DSL) network, frame relay network, asynchronous transfer mode (ATM) network, virtual private network (VPN), and / or any other suitable communication network. Dynamic compile-view client 103 can connect to communication network 102 via one or more communication links (e.g., communication link 104), which can be linked to dynamic compile-view server 101 via one or more communication links (e.g., communication link 105). Communication links can be any communication link suitable for transmitting data between dynamic compile-view client 103 and dynamic compile-view server 101, such as network links, dial-up links, wireless links, hardwired links, any other suitable communication links, or any suitable combination of such links.

[0051] The dynamic compile view client 103 may include any one or more clients that present an interface related to high-speed access to ORC external tables in a suitable form for user use and operation. In some embodiments, the dynamic compile view client 103 may include any suitable type of device. For example, in some embodiments, the dynamic compile view client 103 may include a mobile device, tablet computer, laptop computer, desktop computer, and / or any other suitable type of client device.

[0052] Although the dynamic compilation view server 101 is illustrated as a single device, in some embodiments, any suitable number of devices can be used to perform the functions performed by the dynamic compilation view server 101. For example, in some embodiments, multiple devices can be used to implement the functions performed by the dynamic compilation view server 101. Alternatively, cloud services can be used to implement the functions of the dynamic compilation view server 101.

[0053] Based on the above system, this application provides a method for dynamically compiling views, which is described below through the following embodiments. (Refer to...) Figure 3 The flowchart illustrates the steps of a method for dynamically compiling a view according to an embodiment of this application. Figure 4 This is a flowchart illustrating the execution of a method for dynamically compiling views according to an embodiment of the present invention.

[0054] like Figure 3 and Figure 4 As shown, the method for dynamically compiling views in this embodiment can be executed on a dynamic compiling view server. The method for dynamically compiling views includes the following steps:

[0055] Step S201: When creating a view, construct the query tree structure and database dependency object table that the view depends on, and save the constructed query tree structure and database dependency object table to the view system table.

[0056] In existing technologies, a corresponding query tree is generated when a view is created. This query tree is then serialized and saved to a system table. Simultaneously, the database objects that the view depends on are retrieved and saved to a dependency object table. However, the granularity of these dependency objects is relatively large, only storing the dependency relationships between database object OIDs, and dependency detection is primarily performed when deleting database objects. The method of this invention defines a fine-grained database object dependency table. When generating the query tree structure of the view, it simultaneously extracts information such as the OID, name, and type of the dependent database objects. When saving the view query tree to a system table, this dependency table is also serialized and saved to the system table.

[0057] As an optional example, in the method of this embodiment of the invention, when creating a view, a query tree structure dependent on the view is constructed by parsing the query statements that the view depends on. After completing the construction of the query tree structure dependent on the view, this embodiment of the method extracts the database object information dependent on the view from the constructed query tree structure, and constructs a database dependency object table by saving the extracted database object information dependent on the view. It should be noted that the database object information in this embodiment of the method includes the object identifier, name, and type information of the database object. In practical applications, when those skilled in the art use the method of this invention to construct the database dependency object table, they can extract other database object information according to the actual application scenario, and this embodiment does not impose any restrictions on this. After completing the construction of the query tree structure dependent on the view and the database dependency object table, this embodiment of the method serializes and saves the query tree structure and the database dependency object table to a system table.

[0058] Step S202: When resolving view references, perform dependency checks on the database dependency object tables in the view system tables.

[0059] In existing technologies, view references are primarily handled in the semantic module. The view's query tree is retrieved from the system tables, deserialized, and directly merged into the current SQL query tree as a subquery. If a view's dependent objects change, either the view is cascaded down when the dependent object is deleted, or an error is triggered in subsequent execution due to the non-existence of the dependent object. This embodiment's method performs dependency checks on the view's query tree before merging it into the current SQL query tree. If any changes to dependent objects are found, dynamic compilation is performed.

[0060] As an optional example, in the method of this embodiment of the invention, when other SQL statements access the view, the query tree structure that the view depends on is obtained from the view system table. Before the obtained query tree structure is deserialized and merged into the query tree of the current SQL statement, a dependency check is performed on the database dependency object table that the view depends on. Specifically, in the method of this embodiment of the invention, the database dependency object table is obtained from the view system table, and the object identifier of each database dependency object in the database dependency object table is checked to see if it is valid.

[0061] Step S203: Dynamically compile the view based on the dependency check results.

[0062] If all object identifiers of database dependent objects in the database dependent object table are valid, it means that the database dependent objects have not changed substantially, and view access is performed.

[0063] If one or more database dependency objects in the database dependency object table have invalid object identifiers, it means that the database dependency objects have changed. The database dependency objects need to be resolved, and the view needs to be dynamically compiled based on the resolution results.

[0064] Specifically, the database dependency objects are parsed based on their names and types. The parsing results are used to query database dependency objects that match the view dependency conditions. If no matching database dependency object is found, it indicates that the database dependency object has been deleted, the dependency check ends, the view is deemed invalid, and a view error message is sent to the user. If a matching database dependency object is found, it indicates that the database dependency object has been recreated or recompiled. Based on the object identifier of the retrieved database dependency object, the database dependency object table and query tree structure are updated. It is important to note that in this embodiment of the invention, the object identifiers of the corresponding objects in the database dependency object table and query tree structure are updated based on the retrieved database dependency object's object identifier. The updated database dependency object table and query tree structure are serialized and saved to the view system table. Based on the updated query tree structure and database dependency object table, dependency checks, dynamic view compilation, and view access are performed according to the method of this embodiment of the invention.

[0065] The method and system for dynamically compiling views in this invention introduce dynamic detection of changes to dependent database objects in the database view access mechanism, and perform dynamic compilation upon detection of changes to dependent objects. When creating a view, not only is the serialized query plan saved, but also a table of database object dependencies referenced by the view query plan is saved, including internal identifiers (OIDs), object names, and object types. When accessing the view, the dependent object table is first retrieved from the system table, and a change check for dependent objects is performed. If no change is detected, normal processing proceeds; if a change is detected, the OID of the database object is re-parsed based on the name in the dependency table, and the database object OID information in the query plan is updated before continuing with normal processing logic.

[0066] The method of this invention can effectively utilize the existing query tree structure and view system tables in the openGauss database, concentrating the dynamic compilation work in the semantic processing stage of the database. This limits the scope of impact and reduces a significant amount of development work. When the database objects that the view depends on change, only the OID information of the database objects in the existing query tree of the view needs to be updated, without regenerating the entire query tree. By updating the updated database dependency object table and query tree structure to the view system tables, the latest query tree structure can be obtained when the view is accessed again, avoiding the need for the application layer to manually delete and rebuild the view.

[0067] It should be noted that the method of this embodiment can also be applied to other databases. By combining the method and system of this embodiment with the view mechanism of other databases, similar beneficial technical effects can be obtained.

[0068] like Figure 5 As shown, the present invention also provides a device including a processor 310, a communication interface 320, a memory 330 for storing processor-executable computer programs, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 implements the above-described method of dynamically compiled views by running the executable computer program.

[0069] The computer program in memory 330, when implemented as a software functional unit and sold or used as an independent product, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0070] The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected based on actual needs to achieve the purpose of this embodiment. Those skilled in the art can understand and implement this without any creative effort.

[0071] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods of various embodiments or some parts of embodiments.

[0072] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for dynamically compiling views, characterized in that, The method includes: S201. When creating a view, construct the query tree structure and database dependency object table that the view depends on, and save the constructed query tree structure and database dependency object table to the view system table, including: When creating a view, the query tree structure of the view's dependencies is constructed by parsing the query statements that the view depends on. By extracting the database object information that the view depends on from the constructed query tree structure, and by saving the extracted database object information that the view depends on, a database dependency object table is constructed. Serialize and save the query tree structure and database dependency object table to a system table; S202. When resolving view references, perform dependency checks on the database dependency object tables in the view system tables, including: When other SQL statements access the view, the query tree structure that the view depends on is obtained from the view system table. Before the obtained query tree structure is deserialized and merged into the query tree of the current SQL statement, a dependency check is performed on the database dependency object table that the view depends on. Retrieve the database dependency object table from the tables in the view system, and check whether the object identifier of each database dependency object in the database dependency object table is valid; S203. Based on the dependency check results, dynamically compile the view, including: If all object identifiers of the database dependent objects in the database dependent object table are valid, execute the view access; If one or more database dependency objects in the database dependency object table have invalid object identifiers, the database dependency objects are resolved, and the view is dynamically compiled based on the resolution results.

2. The method for dynamically compiling views according to claim 1, characterized in that, Database object information includes the object identifier, name, and type information of the database object.

3. The method for dynamically compiling views according to claim 1, characterized in that, If one or more database dependency objects in the database dependency object table have invalid object identifiers, the database dependency objects are resolved, and the view is dynamically compiled based on the resolution results, including: The database dependency objects are resolved based on their names and types, and the database dependency objects that meet the view dependency conditions are queried based on the resolution results. When no database dependency object matching the view dependency conditions is found, the dependency check ends, the view is determined to be invalid, and a view error message is sent to the user. When a database dependency object that matches the view dependency conditions is found, the database dependency object table and query tree structure of the view are updated based on the object identifier of the found database dependency object.

4. The method for dynamically compiling views according to claim 3, characterized in that, Update the database dependency object table and query tree structure that the view depends on, including: updating the object identifiers of the corresponding objects in the database dependency object table and query tree structure that the view depends on based on the object identifiers of the queried database dependency objects, and serializing and saving the updated database dependency object table and query tree structure to the view system table.

5. A system for dynamically compiling views, characterized in that, The system implements the steps of the method for dynamically compiling a view according to any one of claims 1-4 during runtime. The system includes a dynamic compiling view server, which is used to: construct a query tree structure and a database dependency object table when creating a view, and save the constructed query tree structure and database dependency object table to a view system table; and perform dependency checks on the database dependency object table in the view system table when resolving view references. The view is dynamically compiled based on the dependency check results.

6. The system for dynamically compiling views according to claim 5, characterized in that, The dynamic compilation view server includes: The view creation module is used to build the query tree structure and database dependency object table that the view depends on when creating a view, and save the built query tree structure and database dependency object table to the view system table. The View Resolution module is used to perform dependency checks on the database dependency object tables in the view system tables when resolving view references. The dynamic compilation module is used to dynamically compile views based on the results of dependency checks.

Images