A method for implementing database physical modeling based on openGauss Data Studio

Abstract

The present application relates to a kind of based on openGauss Data Studio implementation database physical modeling method and device.The present application includes preparing development environment and dependence, configure the menu and operation panel of physical modeling, realize front-end physical modeling based on H5 and Antv X6, UI is fused to openGauss Data Studio to the front-end modeling content is transmitted to back-end JDBC and is executed.The present application can be based on the development environment of database, realize the design of visual physical modeling, can directly show the physical storage structure of database during design process, including table space, table, constraint and various objects and its relationship, and one-key type is completed the execution of modeling DDL, it is saved in different tools back and forth switching and facing complex modeling syntax, greatly simplify the modeling process, improve the development efficiency of research and development team, reduce human cost.

Description

Technical Field

[0001] This invention belongs to the technical field of database physical modeling methods, and particularly relates to a method and apparatus for implementing database physical modeling based on openGauss DataStudio. Background Technology

[0002] With the development of information technology, databases have become a key carrier for storing and managing data in various industries. However, how to design an efficient and easy-to-maintain database storage structure has always been an important issue for database administrators and developers.

[0003] Traditional database design methods require writing numerous SQL DDL statements to create tablespaces, tables, constraints, and other database objects. This is not only incredibly labor-intensive but also prone to errors. If the design needs to be changed, a large number of statements must be modified and tested again, making it difficult to guarantee efficiency and quality.

[0004] To address these issues, numerous database design tools have emerged, offering visual interfaces to assist in designing physical models. These tools reduce the difficulty of database design and improve design quality and flexibility in changes. However, these tools operate independently of the database. Users must write DDL statements using the corresponding database syntax based on the designed physical model, and then execute the DDL statements through a database driver or JDBC / ODBC client tool. The entire execution process involves a physical modeling design platform, a DDL syntax writing tool, and a JDBC (ODBC) client environment. The modeling process and environment remain relatively complex and cumbersome, and development efficiency needs further improvement. Summary of the Invention

[0005] To overcome the shortcomings of existing physical modeling methods, such as complex modeling processes and low development efficiency, we propose a new method for implementing database physical modeling based on openGauss Data Studio.

[0006] We envision a system that enables visual physical modeling design within a database development environment (IDE). This system would allow for a clear visualization of the database's physical storage structure, including tablespaces, tables, constraints, and their relationships. Furthermore, it would enable one-click execution of modeling DDL, eliminating the need to switch between different tools and deal with cumbersome modeling syntax. This would significantly simplify the modeling process, improve development efficiency, and reduce labor costs. Based on this vision, we propose a method and apparatus for visual physical modeling using openGauss Data Studio. This method allows users to design the storage structure of database physical tables within the openGauss Data Studio visual interface by filling in and dragging information on a canvas. Users can then execute the storage structure creation command with a single click, quickly completing physical modeling within the openGauss database.

[0007] The objective of this invention is to achieve visual physical modeling of the openGauss database using openGauss Data Studio. This invention utilizes openGauss Data Studio, a PL / SQL integrated database development environment (IDE) based on the Eclipse RCP framework, developed by the GIS team of the openGauss community. This invention leverages the following features of openGauss Data Studio:

[0008] Object browsing tree: Used to manage database objects, especially database objects, schemas, tables, columns, indexes, constraints, and partition objects, to perform intuitive object queries and editing. It also provides the ability to right-click on objects, making it easy to add new menus (mainly referring to adding the "Physical Modeling" menu item to database objects).

[0009] JDBC compatibility: openGauss Data Studio has embedded the driver package for the openGauss database, enabling communication with the openGauss database.

[0010] Interface Model: In terms of UI design, openGauss Data Studio has divided the main interface into a menu bar, a left-side browser bar, and a main operation panel bar, such as... Figure 7 As shown, when adding database modeling functionality to openGauss Data Studio, you can quickly create and define modeling menus and open the main interface panel, and then import the Webkit browser engine into the main interface panel to complete modeling and rendering.

[0011] In terms of technical implementation, this invention adopts Eclipse RCP+JDBC+JavaFX+Antv X6 as the main technical framework. Figure 6 The diagram shows the hierarchical structure of the technical framework of this invention. The following are the roles of these four technical framework elements in the database modeling method of this invention:

[0012] Eclipse RCP: A client application that uses its own plugin mechanism to quickly build the main body and appearance of desktop applications. It is the basic dependency for implementing the UI of openGauss Data Studio.

[0013] JDBC: Primarily refers to the openGauss database driver, which interacts with the database and is the main communication method for querying data and completing modeling.

[0014] JavaFX: A rich GUI framework. In the implementation of database modeling, its main role is to use JavaFXwebview to provide the browser engine Webkit, and to use Webkit to further run the front-end modeling framework (Antv X6).

[0015] Antv X6 is an enterprise-level visual front-end solution. In database modeling, it serves as the implementation of the front-end modeling canvas, providing the ability to intuitively display canvas drag-and-drop and node associations.

[0016] Specifically, this invention provides a method for implementing database physical modeling based on openGauss Data Studio, the method comprising:

[0017] S1. Prepare the development environment and dependencies;

[0018] S2. Configure the menu and operation panel for physical modeling;

[0019] S3. Implement front-end physical modeling based on H5 and Antv X6;

[0020] S4. Integrates front-end modeling UI into openGauss Data Studio;

[0021] S5. Pass the front-end modeling content to the back-end JDBC for execution.

[0022] Furthermore, according to some embodiments of the present invention, the preparation of the development environment and dependencies in step S1 of the method for implementing database physical modeling based on openGauss Data Studio includes:

[0023] (1) Obtain the openGauss Data Studio open source code;

[0024] (2) Prepare the Antv X6 front-end JavaScript dependency plugins;

[0025] (3) Prepare JavaFX webview dependency plugins;

[0026] (4) Use the IDE to import and set up the openGauss Data Studio development environment, and introduce the JavaFX webview dependency plugin in the org.opengauss.mppdbide.view module.

[0027] Preferably, the IDE mentioned in the above method for implementing database physical modeling based on openGauss Data Studio is Eclipse RCP or IntelliJ IDEA.

[0028] Furthermore, according to some embodiments of the present invention, the menu and operation panel for configuring physical modeling in step S2 of the method for implementing database physical modeling based on openGauss Data Studio includes:

[0029] (1) Add the configuration of the modeling menu in the plugin configuration file Application.e4xmi of openGauss Data Studio, and modify plugin.xml to mount the menu to the database object so that the right mouse button of the database object displays the "Physical Modeling" menu item;

[0030] (2) Create a modeling panel in the org.opengauss.mppdbide.view module, import the JavaFX canvas into the panel, and create a new browser engine object in the canvas;

[0031] (3) Open the Eclipse e4 model configuration file Application.e4xmi, add an event handler for the modeling menu, and implement the opening of the menu and display of the modeling panel in the event.

[0032] Furthermore, according to some embodiments of the present invention, the step S3 of the method for implementing database physical modeling based on openGauss Data Studio, which describes front-end physical modeling based on H5 and Antv X6, includes:

[0033] (1) Outside of openGauss Data Studio, create an HTML static page and the corresponding index.js. Input the x6.js dependency in the HTML. In index.js, use the X6 Graph, Node and Edge objects to generate a canvas and draggable node elements. Add tables and foreign key associations to the canvas as needed.

[0034] (2) Use HTML to generate the operation panel on the right. The panel contains "Add Table" and "Add Association" buttons. Click events are added to the buttons. Clicking the buttons will complete the action of adding nodes and node associations to the canvas.

[0035] Furthermore, according to some embodiments of the present invention, the step S4 of the method for implementing database physical modeling based on openGauss Data Studio, which involves integrating the front-end modeling UI into openGauss Data Studio, includes:

[0036] Copy the front-end modeling UI code to the org.opengauss.mppdbide.view module, and use the JavaFX API method WebView.getEngine().load() to load the HTML, thus displaying the front-end modeling HTML in openGauss Data Studio.

[0037] Furthermore, according to some embodiments of the present invention, the step S5 of the method for implementing database physical modeling based on openGauss Data Studio, which involves passing the front-end modeling content to the back-end JDBC for execution, includes:

[0038] The browser engine WebEngine of JavaFX is used to connect the HTML and JavaFX, convert the physical model in the HTML into the corresponding DDL, and complete the one-click DDL execution through the JDBC module of openGauss Data Studio.

[0039] Furthermore, according to some embodiments of the present invention, the method for implementing database physical modeling based on openGauss Data Studio includes the following steps:

[0040] (1) Open openGauss Data Studio, create a database login connection, and enter the openGauss database login account and password to establish a login connection between openGauss Data Studio and the database.

[0041] (2) Expand the object browsing tree on the left, select the database with the mouse and open the modeling design window with the right-click menu;

[0042] (3) Add tables and foreign key constraints to the canvas, and display and edit canvas elements by clicking and dragging the canvas;

[0043] (4) After completing the design of the table model, click Submit. The background will automatically assemble the DDL modeling commands according to the openGauss syntax rules and execute the modeling commands automatically through JDBC. Then, the tool will automatically refresh the object browsing tree on the left and display the latest table objects and foreign key information in the object browsing tree on the left.

[0044] On the other hand, the present invention also provides an apparatus for implementing database physical modeling based on openGauss Data Studio, the apparatus comprising:

[0045] Middle layer: includes a JDBC framework, which is an openGauss database driver that interacts with the database and provides a communication method for querying data and completing modeling;

[0046] Business layer: Includes the Eclipse RCP framework, which uses its own plugin mechanism to build the main body and appearance of the desktop application for the UI implementation of openGauss Data Studio;

[0047] Presentation layer: includes Antv X6 and JavaFX; Antv X6 is a visual front-end modeling framework used for implementing the front-end modeling canvas, and also provides canvas drag-and-drop and appearance definition capabilities; the role of JavaFX is to use JavaFXwebview to provide the browser engine Webkit, and to use Webkit to further run the front-end modeling framework Antv X6.

[0048] Finally, the present invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the above-described method for implementing database physical modeling based on openGauss Data Studio.

[0049] In summary, the database physical modeling method based on openGauss Data Studio overcomes the shortcomings of existing physical modeling methods, such as complex modeling processes and low development efficiency. This method enables visual physical modeling design within a database development environment (IDE). During the design process, the physical storage structure of the database, including tablespaces, tables, constraints, and other objects and their relationships, can be intuitively displayed. Furthermore, the execution of modeling DDL can be completed with a single click, eliminating the need to switch between different tools and deal with cumbersome modeling syntax. This significantly simplifies the modeling process, improves the development efficiency of the R&D team, and reduces labor costs. Using this method, users can design the storage structure of database physical tables through simple drag-and-drop and UI interaction within the visual interface of openGauss Data Studio, and execute the command to create the storage structure with a single click, quickly completing physical modeling in the OpenGauss database. Attached Figure Description

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

[0051] Figure 1 This is a flowchart illustrating the overall implementation process of the method of the present invention.

[0052] Figure 2 This is a schematic diagram of the login interface for establishing a login connection between openGauss Data Studio and a database according to an embodiment of the present invention.

[0053] Figure 3 This is a schematic diagram of an interface for expanding the left-side object browser, specifying a database, and opening a modeling and design window according to an embodiment of the present invention.

[0054] Figure 4 This is a schematic diagram of an interface for adding tables and foreign key constraints to a canvas according to an embodiment of the present invention.

[0055] Figure 5 This is a schematic diagram of an interface for editing canvas elements by clicking and dragging the canvas according to an embodiment of the present invention.

[0056] Figure 6 This is a schematic diagram of the hierarchical structure of the technical framework of the device of the present invention.

[0057] Figure 7 This is a schematic diagram of the interface settings of openGauss Data Studio used in the method of the present invention.

[0058] Figure 8 This is a schematic diagram illustrating the key dependencies of a method according to an embodiment of the present invention on the UI. Figure 1 .

[0059] Figure 9 This is a schematic diagram illustrating the key dependencies of a method according to an embodiment of the present invention on the UI. Figure 2 . Detailed Implementation

[0060] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. This invention can also be implemented or applied through other different specific embodiments, and the details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this invention.

[0061] At the same time, it should be understood that the scope of protection of the present invention is not limited to the specific embodiments described below; it should also be understood that the terminology used in the embodiments of the present invention is for describing specific embodiments and not for limiting the scope of protection of the present invention.

[0062] Example: A method for implementing database physical modeling based on openGauss Data Studio

[0063] like Figure 1 As shown, this method includes the following steps:

[0064] (1) Prepare the development environment and dependencies

[0065] openGauss Data Studio open source code;

[0066] Antv X6 front-end JavaScript dependency plugin;

[0067] The JavaFX webview depends on a plugin, and this implementation uses a stable version: openjfx-17.0.4_windows-x64.

[0068] Use a suitable IDE (Eclipse RCP or IntelliJ IDEA) to import and set up the openGauss DataStudio development environment, and add the JavaFX webview dependency plugin to the org.opengauss.mppdbide.view module.

[0069] The key dependencies are described in Table 1.

[0070] Table 1. Critical Dependency Description

[0071]

[0072]

[0073] (2) Configure the menu and operation panel for physical modeling.

[0074] The purpose of this step is to create the entry menu and main display interface for physical modeling.

[0075] Add the modeling menu configuration to the Application.e4xmi plugin configuration file in openGauss Data Studio, and modify plugin.xml to mount the menu to the database object so that the "Physical Modeling" menu item is displayed when the right mouse button of the database object is clicked.

[0076] In the org.opengauss.mppdbide.view module, create a modeling panel called PhysicalModeling Part, and import a JavaFX canvas (FXCanvas) into the panel. Create a new browser engine object (WebEngine) in the canvas.

[0077] Open the Eclipse e4 model configuration file Application.e4xmi, add an event handler for the modeling menu, and implement the function to open the menu and display the modeling panel.

[0078] The key APIs are shown in Table 2, and the key dependencies are reflected in the UI as follows: Figure 8 As shown.

[0079] Table 2 Key APIs

[0080]

[0081] (3) Front-end modeling based on H5+Antv X6

[0082] The purpose of this step is to achieve front-end physical modeling and canvas dragging effects.

[0083] Outside of openGauss Data Studio, create a static HTML page and a corresponding index.js file. In the HTML file, add the x6.js dependency. In index.js, use X6's Graph, Node, and Edge objects to generate a canvas and draggable node elements. Add Nodes (referring to tables) and Edges (referring to foreign key relationships in tables) to the canvas as needed.

[0084] In addition to the draggable canvas, the right-hand operation panel (Add Table and Add Association) is generated using HTML. The panel contains "Add Table" and "Add Association" buttons, and click events are added to the buttons. Clicking the buttons will complete the action of adding nodes and node associations to the canvas.

[0085] The key code dependencies are shown in Table 3, and their UI representation is as follows: Figure 9 As shown.

[0086] Table 3 Key Code Dependencies

[0087]

[0088] (4) Loading HTML in the browser engine

[0089] The purpose of this step is to integrate the aforementioned front-end modeling UI into openGauss Data Studio.

[0090] Copy the front-end modeling UI code to the org.opengauss.mppdbide.view module, and use the JavaFX API method WebView.getEngine().load() to load the HTML, thus displaying the front-end modeling HTML in openGauss Data Studio.

[0091] The key code dependencies are shown in Table 4.

[0092] Table 4 Key Code Dependencies

[0093]

[0094]

[0095] (5) Submission of storage model

[0096] The purpose of this step is to describe how an Eclipse RCP project, as a client / server architecture project, can pass front-end UI modeling content to back-end JDBC for execution when JavaFXwebview and H5 are introduced.

[0097] This step involves converting the physical model in the HTML (referring to the table structure and table relationships) into the corresponding DDL and then executing the DDL with a single click using the JDBC module of openGauss Data Studio. The key to this step is establishing communication between the HTML and JavaFX. This is achieved using JavaFX's browser engine, WebEngine. WebEngine can retrieve the JS object (JSObject) of the loaded HTML and set member objects within it. The front-end can then use these member objects set by WebEngine to pass data to JavaFX. JavaFX then retrieves the data and passes it to JDBC for execution, thus completing the data transfer and submission process between the front-end DDL, JavaFX, and JDBC.

[0098] Key code dependencies and APIs are shown in Table 5.

[0099] Table 5 Key Code Dependencies and APIs

[0100]

[0101] The modeling method of this invention includes the following main steps:

[0102] (I) Preparing Materials

[0103] A running openGauss database or a database with openGauss as its kernel;

[0104] openGauss Data Studio - Database Desktop Client;

[0105] The personal operating system (Windows) is located on the same local area network as the system network and database.

[0106] (II) Operating Procedures

[0107] (1) Open openGauss Data Studio, create a database login connection, and enter the openGauss database login username and password to establish a login connection between openGauss Data Studio and the database; the interface after logging in is as follows. Figure 2 As shown.

[0108] (2) Expand the object browsing tree on the left, select the database with the mouse, and open the modeling design window by right-clicking the menu, such as... Figure 3 As shown.

[0109] (3) Add tables and foreign key constraints to the canvas, and display and edit canvas elements by clicking and dragging the canvas, such as... Figure 4 and Figure 5 As shown.

[0110] (4) After completing the design of the table model, click Submit. The background will automatically assemble the DDL modeling commands according to the openGauss syntax rules and execute the modeling commands automatically through JDBC. Then, the tool will automatically refresh the object browsing tree on the left and display the latest table objects and foreign key information in the object browsing tree on the left.

[0111] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, substitutions, etc., made within the spirit and principle of the present invention should be included within the scope of protection of the claims of the present invention.

Claims

1. A method for realizing database physical modeling based on openGauss Data Studio, characterized in that, The method comprises: S1. Prepare the development environment and dependencies; S2. Configure the menu and operation panel of physical modeling; S3. Implement front-end physical modeling based on H5 and Antv X6, including: (1) In addition to openGauss Data Studio, create html static pages and corresponding index.js, input x6.js dependencies in html, use X6 Graph, Node and Edge objects in index.js to generate canvas and draggable node elements, and add tables and table foreign key associations on the canvas as needed; (2) Use html to generate the operation panel on the right, generate "add table" and "add association" buttons on the panel, and add click events to the buttons, which can add nodes and node associations to the canvas when the buttons are clicked; S4. Integrate the front-end modeling UI into openGauss Data Studio, including: Copy the front-end modeling UI code to the org.opengauss.mppdbide.view module, use the JavaFX api method WebView.getEngine().load() to load html, and realize the display of the front-end modeling html in openGauss Data Studio; S5. Pass the front-end modeling content to the back-end JDBC for execution.

2. The method for realizing database physical modeling based on openGauss Data Studio according to claim 1, characterized in that, The preparation of the development environment and dependencies in step S1 includes: (1) Obtain openGauss Data Studio open source code; (2) Prepare Antv X6 front-end JavaScript dependency plug-in; (3) Prepare JavaFX webview dependency plug-in; (4) Use IDE to import and build openGauss Data Studio development environment, and introduce JavaFX webview dependency plug-in in the org.opengauss.mppdbide.view module.

3. The method for realizing database physical modeling based on openGauss Data Studio according to claim 2, characterized in that, The IDE is Eclipse RCP or Intellij IDEA.

4. The method for realizing database physical modeling based on openGauss Data Studio according to claim 2, characterized in that, The configuration of the menu and operation panel of physical modeling in step S2 includes: (1) Add modeling menu configuration in the plug-in configuration file Application.e4xmi of openGauss Data Studio, and modify plugin.xml to mount the menu to the database object, so that the "physical modeling" menu item is displayed in the mouse right-click of the database object; (2) Create a modeling panel in the org.opengauss.mppdbide.view module, and introduce a JavaFX canvas in the panel, and create a browser engine object in the canvas; (3) Open the Eclipse e4 model configuration file Application.e4xmi, add an event handler for the modeling menu, and implement the opening of the menu and the display of the modeling panel in the event.

5. The method for realizing database physical modeling based on openGauss Data Studio according to claim 1, characterized in that, The front-end modeling content is transmitted to the back-end JDBC for execution in step S5, including: The browser engine webengine of JavaFX is used to connect the communication between html and JavaFX, convert the physical model in html into corresponding DDL, and complete one-key DDL execution through the JDBC module of openGauss Data Studio.

6. The method for realizing database physical modeling based on openGauss Data Studio according to claim 1, characterized in that, The use process of the modeling method includes the following operation steps: (1) open openGauss Data Studio, create a database login connection, input the openGauss database login account and password, and establish the login connection between openGauss Data Studio and the database; (2) expand the left object browser tree, specify the database with the mouse, and open the modeling design window through the right menu; (3) add tables and foreign key constraints to the canvas, and display and edit the canvas elements by clicking and dragging the canvas; (4) after completing the design of the table model, click submit, and the background automatically assembles the DDL modeling command according to the openGauss syntax rules, and automatically completes the execution of the modeling command through JDBC, and then the tool automatically refreshes the left object browser tree, and displays the latest table objects and foreign key information in the left object browser tree.

7. An apparatus for implementing database physical modeling based on openGauss Data Studio, characterized in that, The device realizes the steps of the method for realizing database physical modeling based on openGauss Data Studio according to any one of claims 1-6, and the device includes: The middle layer includes a JDBC framework, the JDBC framework is an openGauss database driver, interacts with the database, and provides a communication method for querying data and completing modeling; The business layer includes an Eclipse RCP framework, the Eclipse RCP framework uses its own plug-in mechanism to build the main body and appearance of the desktop application, and is used for UI implementation of openGauss Data Studio; The display layer includes Antv X6 and JavaFX; the Antv X6 is a visual front-end modeling framework, is used for implementation of the front-end modeling canvas, and provides the ability of canvas dragging and appearance definition; the JavaFX uses javafxwebview to provide the browser engine Webkit, and further runs the front-end modeling framework Antv X6 by using Webkit.

8. A computer readable storage medium, the storage medium storing a computer program, the program being executed by a processor to realize the steps of the method for realizing database physical modeling based on openGauss Data Studio according to any one of claims 1-6.

Images