CN108319687B - SQL rule generator based on JS dragging technology - Google Patents

Abstract

Compared with the prior art, the SQL rule generator based on the JS dragging technology has the following advantages that: the SQL rule configuration module is independently innovated and researched, and the usability of the system is effectively improved. For example, the metadata dragging tree can display tables and fields in the database in a full-Chinese and full-paraphrase tree structure, and a user can complete rule configuration only by dragging nodes in the tree into a left target control one by one in a dragging mode without requiring the user to have professional SQL language basic knowledge or knowing the database data structure.

Description

SQL rule generator based on JS dragging technology

Technical Field

The invention belongs to the technical field of structured query languages, and particularly relates to a SQL rule generator based on a JS dragging technology.

Background

In a system represented by data quality inspection, risk early warning, and the like, it is often necessary to define some data inspection rules, and describe the data inspection rules by using SQL (Structured Query Language) statements, so as to discover data quality problem points or risk early warning signals by executing the SQL statements.

However, currently, the SQL rule generator employs a C/S (Client/Server) architecture, directly operates on the database to assist a technician to generate an SQL statement executable in the database, which requires the user to have professional SQL knowledge to generate the SQL statement by using the SQL rule generator and requires the user to be familiar with the metadata structure of the database, and to implement the rules according to the development mode, which is implemented by first providing a requirement by a service person and then developing by a technician, but is limited by the production period and flow of the production system, and the development mode has the disadvantages of slow development response, long parameter adjustment period, fatigue of the technician in dealing with system changes, and the rule development and modification efficiency becomes the main bottleneck of the wide application of the system.

Disclosure of Invention

In view of the above, the present invention provides an SQL rule generator based on JS dragging technology, so that a business user can use a browser to online, customize data quality and risk early warning check rules in a full-chinese paraphrase and graphical interface dragging manner, and automatically generate rules of SQL statements without mastering basic knowledge of SQL language and knowing a database data structure.

The technical scheme is as follows:

a SQL rule generator based on JS drag technology, the rule generator comprising: the SQL rule configuration module is used for configuring the SQL rule;

wherein, SQL rule configuration module includes:

the metadata tree is used for displaying data tables and data table fields in a database in a Chinese form, wherein the metadata tree supports JS drag operation;

the dragging object identification unit is used for judging whether a focus control corresponding to the current position of the dragging object is matched with the dragging object or not so as to determine whether the dragging event is responded, and the dragging object is a data table or a data table field in the metadata tree;

the dragging object previewing unit is used for displaying the Chinese name of the dragging object when the dragging operation is executed on the dragging object;

and the cursor control unit is used for realizing the movement of a text box cursor along with a mouse in a text field set by the SQL WHERE condition so as to determine the insertion position of the dragged object.

Preferably, the metadata tree includes attribute information of the dragged object.

Preferably, the drag object identification unit is specifically configured to:

acquiring preset identification information of the focus control, and determining the type of the focus control according to the preset identification information;

acquiring the type of the dragged object from the attribute information of the dragged object;

and when the type of the focus control is matched with the type of the dragged object, indicating that the focus control is matched with the dragged object, and adding the Chinese name of the dragged object to the position of the focus space for displaying.

Preferably, the rule generator further comprises: the SQL rule generating module generates SQL rules;

wherein, the SQL rule generating module comprises:

the SQL sentence splicing unit is used for automatically splicing the SELECT clause, the FROM clause, the WHERE clause and the GROUP BY clause in the SQL sentence according to the English name of the dragging object which is always stored BY the focus control to generate the SQL sentence;

the SQL sentence automatic aggregation unit is used for replacing the SELECT clause in the spliced SQL sentence with a SELECT COUNT clause, submitting an SQL sentence with a GROUP BY primary key and an SQL sentence without the GROUP BY primary key at the same time, executing the SQL sentence with the GROUP BY primary key to return a first record number, executing the SQL sentence without the GROUP BY primary key to return a second record number, using the SQL sentence without the GROUP BY primary key when the first record number is equal to the second record number, and using the SQL sentence with the GROUP BY primary key when the first record number is not equal to the second record number;

the SQL statement verification unit is used for adding frequent and false conditions in the SQL WHERE condition setting, submitting the database for execution and determining whether the semantics of the SQL statement is correct according to the execution result of the database;

and the SQL statement loading unit is used for generating a rule configuration interface of the SQL statement when the existing SQL statement is loaded so as to modify the SQL statement.

Preferably, the SQL rule generator adopts a B/S architecture.

According to the technical scheme, the application discloses the SQL rule generator based on the JS dragging technology, and compared with the prior art, the technical scheme provided by the invention has the following advantages: the SQL rule configuration module is independently innovated and researched, and the usability of the system is effectively improved. For example, the metadata dragging tree can display tables and fields in the database in a full-Chinese and full-paraphrase tree structure, and a user can complete rule configuration only by dragging nodes in the tree into a left target control one by one in a dragging mode without requiring the user to have professional SQL language basic knowledge or knowing the database data structure.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to these drawings without creative efforts.

Fig. 1 is a schematic structural diagram of an SQL rule configuration module of an SQL rule generator according to an embodiment of the present invention;

FIG. 2 is a schematic diagram of a display interface provided by an embodiment of the invention;

FIG. 3 is a diagram of a metadata tree provided by an embodiment of the present invention;

FIG. 4 is a diagram of an information association text box provided by an embodiment of the invention;

FIG. 5 is a preview display of a dragged object provided by an embodiment of the present invention;

FIG. 6 is a diagram of a set rule condition textbox provided by an embodiment of the invention;

fig. 7 is a schematic structural diagram of an SQL rule generating module of an SQL rule generator according to an embodiment of the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

Fig. 1 is a schematic structural diagram of an SQL rule configuration module of an SQL rule generator according to an embodiment of the present invention.

The SQL rule configuration module is used for realizing the rule configuration of the SQL statement completed by the user in the online state of the browser.

Specifically, the SQL rule configuration module includes:

a metadata tree for exposing data tables and data table fields in a database in Chinese form. The metadata tree can support a JS dragging technology, and a user can drag a data table and a data table field in the metadata tree through a mouse.

Generally, the main operation objects of SQL are tables in a database and fields thereof. The invention displays the database table and the field in a tree structure, translates the name by using a metadata technology, displays the name in a Chinese mode which can be read and understood by business personnel, and simultaneously saves the types of the dragging objects (table, field, dictionary and operator), the data types (integer, real and character), and the like. Such as the right-hand area of the display interface shown in fig. 2 and the metadata tree shown in fig. 3, the data tables and data table fields in the database are displayed in the form of a metadata tree.

It should be noted that, in this embodiment, the metadata tree can capture a mouse click operation. When a user presses a mouse to drag the dragged object, attribute information of the dragged object can be obtained and stored, for example, when the dragged object is a data table, a Chinese name and an English name of the data table are obtained, and when the dragged object is a field, a Chinese name, an English name and a field type of the field are obtained.

And the dragged object identification unit is used for judging whether the focus control corresponding to the current position of the dragged object is matched with the dragged object or not so as to determine whether the current dragging event is responded.

For example, in the text box associated with the information shown in fig. 4, when the dragged object is the "credit voucher balance change table", the dragged object is dragged and moved from the metadata tree to the text box associated with the data table information by using the mouse, and when the mouse is released (that is, the mouse pressing operation is finished), the focus control corresponding to the text box identifies the dragged object, so as to determine whether the dragged object is matched with the focus space. For example, if id of the focus space of a certain text box (input) starts with dragdrop _ then the text box can respond that the drag object is a data table, and similarly, dragdrop _ represents that the focus control response type is a field and dragdrop _ represents that the control response type is an operator. When the drag object is matched with the focus space, writing the attribute information of the drag object into the focus space, and simultaneously displaying the Chinese name (credit voucher balance change table) of the drag object in the text box.

And the dragging object previewing unit is used for displaying the Chinese name of the dragging object when the dragging operation is executed on the dragging object.

According to the invention, by capturing the mouse movement event, the function that the dragged object moves along with the mouse is realized, and the user can preview the currently dragged object at any time. Such as the drag object preview display shown in fig. 5. When the dragging object is the contract state table, the dragging object is dragged and moved to the text box of the information association from the metadata tree through the mouse, and the Chinese name (the contract state table) of the dragging object moves along with the dragging and moving process of the mouse to the dragging object, so that a user can preview the currently acquired first object at any time.

And the cursor control unit is used for realizing the movement of a text box cursor along with a mouse in a text field set by the SQL WHERE condition so as to determine the insertion position of the dragged object.

Such as the set rule conditions text box shown in fig. 6. The functional interface is a dragging control represented by SQL WHERE condition setting in SQL, and in order to support flexible setting between each field, the dragging control needs to be in the form of an editable text box, and a text box cursor of the editable text box can move along with a mouse, so that a user determines the position of a dragging object in the text box with the setting rule condition by moving the mouse, and re-edits each Chinese name and each operator in the editable text box.

Compared with the prior art, the SQL rule generator based on the JS dragging technology has the following advantages that: the SQL rule configuration module is independently innovated and researched, and the usability of the system is effectively improved. For example, the metadata dragging tree can display tables and fields in the database in a full-Chinese and full-paraphrase tree structure, and a user can complete rule configuration only by dragging nodes in the tree into a left target control one by one in a dragging mode without requiring the user to have professional SQL language basic knowledge or knowing the database data structure.

It should be noted that, in the present application, the data table and the field are displayed by using a metadata technology, and the data table control can only respond to a drag operation in which a drag object is the data table, and the field control can only respond to a drag operation in which a drag object is the field, so that the usability of the subsequently generated SQL statement is ensured by the above manner.

Fig. 7 is a structural diagram of an SQL rule generating module of the SQL rule generator according to the present invention.

Specifically, the SQL rule generating module includes: the SQL sentence splicing unit 5, the SQL sentence gathering unit 6, the SQL sentence verifying unit 7 and the SQL sentence loading unit 8.

And the SQL sentence splicing unit is used for automatically splicing the SELECT clause, the FROM clause, the WHERE clause and the GROUP BY clause in the SQL sentence according to the English name of the dragging object which is always stored BY the focus control to generate the SQL sentence.

In the embodiment of the present invention, one feasible way of automatic splicing is: and automatically splicing the SELECT clause, the FROM clause, the WHERE clause and the GROUP BY clause of the SQL sentence according to the English name corresponding to the dragged object BY the javascript program, and finishing the assembly of the SQL sentence to obtain the SQL sentence corresponding to the check rule. The SELECT clause, the FROM clause, the WHERE clause, and the GROUP BY clause of the JavaScript interpreted script language are clauses used BY the SQL statement to check a detection object, and if the detection object is each database, the SELECT clause, the FROM clause, the WHERE clause, and the GROUP BY clause are clauses related to the database in the SQL statement, and for the clauses, the functions are respectively:

the FROM clauses are used for indicating data tables FROM which information is acquired, and the data tables corresponding to the FROM clauses can be determined through English names corresponding to the dragging objects and types corresponding to the dragging objects; the SELECT clause is used for indicating which information is acquired; the WHERE clause is used for indicating the selection condition set for obtaining the information; the GROUP BY clause is a clause with an aggregation function.

A user can set a main key field in an SQL rule generator autonomously, but the field is not necessarily the only main key in a database table, and in order to ensure that the inquired records are not repeated, an aggregation mode, namely a GROUP BY clause, is sometimes required. Therefore, the invention discloses an SQL statement automatic aggregation unit which is used for judging whether the SQL statement uses a party mode or not.

Specifically, the spliced SELECT clause in the SQL statement is replaced BY a SELECT COUNT clause, and a SQL statement with a GROUP BY primary key and a SQL statement without a GROUP BY primary key are submitted simultaneously, the SQL statement with the GROUP BY primary key is executed to return a first record number, the SQL statement without the GROUP BY primary key is executed to return a second record number, when the first record number is equal to the second record number, the SQL statement without the GROUP BY primary key is used, and when the first record number is equal to the second record number, the SQL statement with the GROUP BY primary key is used.

And the SQL statement verification unit is used for adding a frequently false condition in the SQLWHERE condition setting, submitting the database to test execution and determining whether the semantics of the SQL statement is correct according to the execution result of the database.

And the SQL statement loading unit is used for generating a rule configuration interface of the SQL statement when the existing SQL statement is loaded so as to modify the SQL statement.

Because the JSP page of the SQL rule generator is very complex, it is very difficult to accurately restore the values stored in the database to each control on the page when loading the existing SQL statement. The SQL rule generator uses a mode that a rule configuration interface of an SQL statement is stored in a database, when a user edits the existing SQL statement, a page stored in the database before is directly loaded back, and after the user modifies the existing SQL statement, the SQL is generated in real time and then the page is stored in the database for the next editing.

In this embodiment, the rule generator can automatically determine whether to use aggregation for the generated SQL statement, and two-level verification of syntax and semantics is adopted to ensure the usability of the SQL rule.

It should be noted that the rule generator disclosed by the invention adopts a B/S architecture, a user can use a browser to define the data quality and risk early warning check rule online, and compared with a C/S architecture, the rule generator can be used in a cross-system and cross-platform manner.

While, for purposes of simplicity of explanation, the foregoing method embodiments have been described as a series of acts or combination of acts, it will be appreciated by those skilled in the art that the present invention is not limited by the illustrated ordering of acts, as some steps may occur in other orders or concurrently with other steps in accordance with the invention. Further, those skilled in the art should also appreciate that the embodiments described in the specification are preferred embodiments and that the acts and modules referred to are not necessarily required by the invention.

It should be noted that, in the present specification, the embodiments are all described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments may be referred to each other. For the device-like embodiment, since it is basically similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

Finally, it should also be noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the invention. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

The foregoing is only a preferred embodiment of the present invention, and it should be noted that, for those skilled in the art, various modifications and decorations can be made without departing from the principle of the present invention, and these modifications and decorations should also be regarded as the protection scope of the present invention.

Claims (4)

1. A SQL rule generator based on JS drag technology is characterized in that the rule generator comprises: the SQL rule configuration module is used for configuring the SQL rule and the SQL rule generation module is used for generating the SQL rule;

wherein, SQL rule configuration module includes:

the metadata tree is used for displaying data tables and data table fields in a database in a Chinese form, wherein the metadata tree supports JS drag operation;

the dragging object identification unit is used for judging whether a focus control corresponding to the current position of the dragging object is matched with the dragging object or not so as to determine whether the dragging event is responded, and the dragging object is a data table or a data table field in the metadata tree;

the dragging object previewing unit is used for displaying the Chinese name of the dragging object when the dragging operation is executed on the dragging object;

the cursor control unit is used for realizing that a text box cursor moves along with a mouse in a text field set by an SQLWHERE condition so as to determine the insertion position of a dragged object;

wherein, the SQL rule generating module comprises:

the SQL sentence splicing unit is used for automatically splicing the SELECT clause, the FROM clause, the WHERE clause and the GROUP BY clause in the SQL sentence according to the English name of the dragging object which is always stored BY the focus control to generate the SQL sentence;

the SQL sentence automatic aggregation unit is used for replacing the SELECT clause in the spliced SQL sentence with a SELECT COUNT clause, submitting an SQL sentence with a GROUP BY primary key and an SQL sentence without the GROUP BY primary key at the same time, executing the SQL sentence with the GROUP BY primary key to return a first record number, executing the SQL sentence without the GROUP BY primary key to return a second record number, using the SQL sentence without the GROUP BY primary key when the first record number is equal to the second record number, and using the SQL sentence with the GROUP BY primary key when the first record number is not equal to the second record number;

the SQL statement verification unit is used for adding frequent and false conditions in the SQL WHERE condition setting, submitting the database for execution and determining whether the semantics of the SQL statement is correct according to the execution result of the database;

and the SQL statement loading unit is used for generating a rule configuration interface of the SQL statement when the existing SQL statement is loaded so as to modify the SQL statement.

2. The SQL rule generator of claim 1, wherein the metadata tree contains attribute information of the dragged object.

3. The SQL rule generator according to claim 2, wherein the dragged object identifying unit is specifically configured to:

acquiring preset identification information of the focus control, and determining the type of the focus control according to the preset identification information;

acquiring the type of the dragged object from the attribute information of the dragged object;

and when the type of the focus control is matched with the type of the dragged object, indicating that the focus control is matched with the dragged object, and adding the Chinese name of the dragged object to the position of the focus space for displaying.

4. The SQL rule generator of claim 1, wherein the SQL rule generator employs a B/S architecture.

Images