KR101900811B1 - Method and computer program for designing a query - Google Patents

Abstract

A query design method, a computer program, and a system in which a query sentence is automatically generated when a user moves a desired column in a WYSIWYG way through a graphical user interface and creates a query condition . A query design method according to the present invention includes a column list area for selecting columns of a table to be queried by a query, a filter editing area for editing a filter indicating a query condition, and a result viewing area for displaying a query being generated Providing a user interface screen including the user interface screen; Displaying a list of columns of the selected table in a column list area; Generating a query sentence corresponding to one or more columns selected from the column list by the user and displaying the generated query sentence in the result viewing area; Adding a new conditional expression and a new logical operator when a user selects a new logical operator and adding parentheses defining a relationship between a current conditional expression and a new conditional expression; ; And changing the query condition included in the query sentence in the result viewing area according to the change of the filter edited in the filter editing area.

Description

Method and computer program for designing a query

More particularly, the present invention relates to a query design method and system for supporting a user interface for query design, and a computer program therefor, and more particularly, to a query designer for assisting generation of a query for querying desired data from a database.

To build and use a database, you need a language that can access the database, which is a query language such as SQL (Structured Query Language).

SQL is a kind of programming language. It is common for a technician who understands the structure of database, DBMS (Database Management System) and computer programming to write SQL and retrieve desired data.

However, recently, as the Internet has developed, there have been a lot of people using a database to provide services. However, since it is difficult for a person who operates a small database and uses the data to hire a professional developer, There is a growing need for a tool that allows a non-technical user to manipulate the database to create the desired table and query the data.

SUMMARY OF THE INVENTION The present invention has been made to solve the above problems of the prior art, and it is an object of the present invention to provide a query designer that allows a general user who does not have a specialized knowledge of a database or a programming language to easily design a query.

In addition, the present invention allows a user to move a desired column by WYSIWYG (WYSIWYG) method and move and select objects included in a query condition through a graphical user interface, And to provide a user interface method and system.

According to an aspect of the present invention, there is provided a query design method including: a column list area in which columns of a table to be searched by a query can be selected; a filter list Providing a user interface screen including a region and a result viewing area for displaying a query being generated; Displaying a list of columns of the selected table in the column list area; Generating a query sentence corresponding to one or more columns selected from the column list by the user and displaying the generated query sentence in the result viewing area; A filter editing unit for editing a filter in the filter editing area according to an input of the user, wherein when the user selects a new logical operator, a new conditional expression and a new logical operator are added, and a parenthesis ; ≪ / RTI > And changing a query condition included in a query sentence of the result viewing area according to a change of a filter edited in the filter editing area.

The column list area includes a column filter for controlling the display of columns according to the data type, and the column corresponding to the data type released by the user from the column filter may be deleted from the column list area.

The column list area may include a user interface for defining and adding a reconstructed virtual column to have new data using a table column.

The virtual column includes at least one of a computation column defined by a computation formula using a column value of a table, a format column defined by a string including a column value of the table, and a condition column defined by an output condition based on a column value of the table can do.

Wherein the filter edited in the filter editing area includes at least one conditional rule arranged for each line, and the relationship between the conditional expressions is defined by parentheses and logical operators that bind the conditional expressions and the columns required for each conditional rule are determined by drag- Can be moved from the column list area.

The method includes converting the contents of the edited filter in the filter editing area into an XML file and storing the converted XML file; If the XML file is called by the user, interpreting the XML file into a form of a filter, and displaying the converted XML file in the filter editing area.

According to the present invention, it is possible to easily design a query even for a general user who does not have a specialized knowledge of a database or a programming language, and a user can easily design a query through a graphic user interface (WYSIWYG) If you move the desired column and create a query condition, the query statement is automatically generated.

FIG. 1 illustrates a network configuration of a query design system according to an embodiment of the present invention.
FIG. 2 illustrates a user interface screen of a query designer according to an exemplary embodiment of the present invention. Referring to FIG.
3 is a flowchart illustrating a query design method according to an embodiment of the present invention.
FIG. 4 illustrates a screen for erasing an unwanted data type using a column filter in a column list area according to an exemplary embodiment of the present invention. Referring to FIG.
FIG. 5 is a diagram illustrating how a query is automatically generated in a result viewing area by selecting a column in the column list area according to an embodiment of the present invention.
FIG. 6 shows a screen for editing a filter according to an embodiment of the present invention.
Figs. 7 to 11 illustrate how the conditional expression is changed in the filter edit region.
12 to 14 are views for explaining a hypothetical column according to an embodiment of the present invention.
FIG. 15 illustrates a screen showing XML created by the filter editing area.

The terms used in this specification will be briefly described and the present invention will be described in detail.

While the present invention has been described in connection with what is presently considered to be the most practical and preferred embodiment, it is to be understood that the invention is not limited to the disclosed embodiments. Also, in certain cases, there may be a term selected arbitrarily by the applicant, in which case the meaning thereof will be described in detail in the description of the corresponding invention. Therefore, the term used in the present invention should be defined based on the meaning of the term, not on the name of a simple term, but on the entire contents of the present invention.

When an element is referred to as " including " an element throughout the specification, it is to be understood that the element may include other elements as well, without departing from the spirit or scope of the present invention. The term " means ", " part ", " module ", etc. in the specification means units for processing at least one function or operation, Lt; / RTI >

Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings so that those skilled in the art can easily carry out the present invention. The present invention may, however, be embodied in many different forms and should not be construed as limited to the embodiments set forth herein. In order to clearly illustrate the present invention, parts not related to the description are omitted, and similar parts are denoted by like reference characters throughout the specification.

A computer program and a system for performing a query design method and a query design method by a query designer disclosed below provide a completely new and advanced user interface in comparison with a known user interface. Various embodiments having one or more sensors for interacting with a user may be considered in combination with or independently of known input tools.

FIG. 1 illustrates a network configuration of a query design system according to an embodiment of the present invention.

The query design system according to the present invention includes a server 10 and one or more user terminals 11 that communicate with each other via a network. The server 10 is a computing device that performs functions of a database management system (DBMS) and a query generation server for driving a database (DB) 14, and may be implemented in one physical device, And a query generation server on a separate device.

The user terminal 11 may be provided with a query designer, which is a client program that provides a user interface for generating a query. The query designer designs a query sentence according to a user's operation and sends the generated query to the server 10 And performs table information or data inquiry by transmitting. The server 10 directly inquires data of the database 14 by querying the table information according to the request of the query designer, executing the query, and returns the result to the user terminal 11.

According to another embodiment, the query designer can be provided on a web-based basis. In this case, the server 10 performs a role of a web server, and a user interface screen for performing a query design method will be provided through a web page. When the user operates to create a column and a query condition of a table to be inquired through the query design screen of the user terminal 11, the server 10 receives a request from the terminal 11 to design and generate a corresponding query, And transmits the result through a web page.

FIG. 2 is a view showing a user interface screen of a query designer according to an embodiment of the present invention, and FIG. 3 is a flowchart illustrating a query design method according to an embodiment of the present invention.

When a query designer is executed or a web site providing a query design service is accessed, a user interface screen is provided (S32).

The user interface screen includes a column list area 30 for selecting columns of a table to be queried by a query, a filter editing area 40 for easily editing a filter indicating a query's query condition in a WYSIWYG manner, And a result viewing area 50 in which data to be inquired by the query can be previewed. It may also include a table selection area 20 for selecting a table and creating an associated view.

According to the query design method of the present invention, the user can automatically generate a query that can retrieve data of a desired condition by simply selecting and arranging objects in each region through a graphical user interface instead of directly creating a query have.

In the table selection area 20, the user can select one of the tables created in the database. In Figure 2, a table named " Wall " When a table to be operated is selected, the column list of the corresponding table is displayed in the column list area 20 (S34).

On the other hand, under the table selection field, a list 21 of generated views for the selected table is displayed. At the beginning, each table can have a " default view " You may.

Each view has a corresponding query, and the view is a window in which you can see the data that is queried by this query. That is, the user can view desired data among the entire data of the table through the view or process the data in a different shape, and can generate and use a plurality of views for one table.

The query designer can inquire a list of tables stored in the database, a list of views, data stored in each table, an XML (extensible markup language) file generated in correspondence with the edited filter, A query and a corresponding view are generated according to the operation of the user, and the data corresponding to the generated view is inquired and displayed to the user.

If one of the views is selected in the related view list (21), the screen is changed to a workspace in which the query design can be performed for the corresponding view. That is, the columns of the table associated with the selected view are listed in the column list area 30. In the filter edit area 40, if a filter that is being edited or already created for the view is displayed, or if the filter is not edited, In the result viewing area 50, a query is automatically generated and displayed.

Figure 2 shows an initial state in which a " default view " of a table called " WALL " is displayed, and a query for this view is not yet designed.

The column list area 30 may include a column filter 33. The column filter 33 is a filter for causing only the columns of the desired data type to be listed in the column list area. The column filter 33 can be useful when the number of columns included in the table is large.

By default, all column filters are enabled and in this case columns of all data types are displayed. When a user deselects (disables) a data type filter that is not desired by the user, the column of the corresponding type disappears from the column list area 20.

FIG. 4 illustrates a screen for erasing columns having an undesired data type using a column filter in the column list area.

4, when the user presses the format type and the calculation type 45 of the column filter 33 in the column list area 30, it is found that the columns of the corresponding data type disappear from the column list . In addition, a format column addition button 31 and a calculated column addition button 32 are provided in the column list area 30, thereby making a virtual column new.

 In addition, a color filter is applied to each column name in the column list and can be displayed in a different color depending on the data type. For example, a Text type column is colored blue and a Number type column is colored red. This is to make it easier for users to identify when the number of columns is large. In addition, since the data type, which is one of the information on the column, is a result of color, summary information on the column can be included without additional text.

After selecting a table and selecting or creating a view associated with it, the user selects the columns to be queried in the column list area. As the user selects one or more columns in the column list area 30, the query designer generates corresponding query statements and displays them in the result viewing area 50 (S36). Just check the desired column in the column list and SQL query that can check the checked column is automatically generated and displayed.

FIG. 5 shows that a query is automatically generated in the result viewing area by selecting a column in the column list area.

5, when the user sequentially selects "contact_id", "contents", "date and time", "email", and "number 2" in the column list area 30, "Select contact_id, contents, Quot; Email, Number 2 from PostWall " is generated in real time.

In the column list area 30 where the user performs a selection operation, labels for each column can be displayed for the convenience of the user. However, since the query is a sentence that actually inquires the database, the system name corresponding to each column is used in the query . Similarly, the table name chosen by the user is labeled "Wall", but the table name used in the actual query is the system name "PostWall".

Meanwhile, the query designer supports editing of the filter in the filter edit region 40 according to the input of the user (S38).

If the choice of the column is to define the column to be queried by the current view, editing the filter will delimit the records to be queried by the view among all the data records stored in the table. According to the query designer according to the present invention, instead of directly inputting the query conditional expression of the query below the where, the column to be inserted into the condition is fetched from the filter edit area 40, Just select the operator, comparison operator, and SQL operator, and the query designer will automatically generate the conditional expression in the where clause.

The filter created in the filter editing area 40 includes at least one conditional form having the form of " A operator B ", and one conditional is arranged for each line. Each conditional is connected by an AND operator or an OR operator, which is completely different depending on where the parentheses are inserted in the conditional expression.

When the user presses the conditional add button 44 in the filter editing area 40, a conditional expression is added. When the AND operator add button 41 or the OR operator add button 42 is pressed, an AND relationship or an OR relationship And the position of the parenthesis corresponding to the condition is automatically determined and inserted.

FIG. 6 shows a screen for editing a filter according to an embodiment of the present invention.

6, the function buttons of the filter edit region may include a conditional add button 44, an AND operator add button 41, an OR operator add button 42, a conditional delete button, an XML lookup button 43, have.

When the user presses the conditional statement add button 44 for input of the conditional statement, three blank spaces (□□□) corresponding to the conditional clause 60 appear in the filter edit region 40. In the example of Fig. 6, the user brings the column " phone " to be operated on to the first column 61, the comparison column 62 selects the comparison operator & 010-8934-4372 "to complete one conditional statement.

The column used in the conditional expression can be easily fetched from the column list area 30 by drag & drop. Also, when the second column 62 is clicked, the available operators are listed, and the desired comparison operator or SQL operator can be selected. In the third column (63), one condition can be made by directly inputting a constant or taking another column.

The condition expression where Phone = "010-8934-4372" (65) is automatically generated in the SQL query sentence displayed in the result viewing area 50 in the lower part of the screen according to the filter created in the filter editing area 40. As a result, The query sentence 55 of the query sentence 55-1 is changed to the corrected query sentence 55-1.

In this way, if the query target column selected in the column list area 30 is changed, deleted or added or the filter is changed in the filter edit area 40, the query sentence in the result view area 50 is changed in real time accordingly (S40 ).

In the result viewing area 50, data can also be previewed. When the preview 51 is clicked, the generated query is transmitted to the server 10 and executed. As a result of execution of the query, data to be inquired from the table is received and output . For example, if the preview 51 is selected on the screen of Fig. 6, the columns of "select" or less that satisfy the condition "where" in the table "PostWall" will be displayed in tabular form. Click SQL (52) to return to the query preview screen.

Figs. 7 to 11 illustrate how the conditional expression is changed in the filter edit region.

When one conditional statement 60 is created as shown in FIG. 6, if the AND operator addition button 41 is pressed, a second conditional statement 70, which is an AND relation to the first conditional statement 60, is automatically displayed as shown in FIG. As in Condition 1 (60), Condition 2 (70) can be completed by bringing the table column into three empty columns, selecting the operator, and entering a constant. The query designer automatically generates and inserts logical operators and parentheses that indicate the relationship between each conditional statement. In the filter edit region 40, only '(' is displayed among '(' and ')' constituting the parentheses. Since the conditional terms related to each other are located at the zero end of '(' The conditional expression of the filter in the screen of FIG. 7 is (conditional expression 1 AND conditional expression " 2).

If you want to separate conditional statement 1 (60) into OR condition again, click conditional statement 1 (60) and press the Add OR operator add button (42). Then, as shown in FIG. 8, the query designer adds conditional statement 3 (80), which is an OR relationship with the conditional statement 1 (60), and recognizes and inserts the position of the logical operator and the parenthesis automatically. In this state, the conditional expression of the filter is ( ( condition 1 OR condition 3) AND condition condition 2). In the state of FIG. 7, the underlined portion is further added.

When it is desired to separate conditional statement 1 (60) again by the AND condition in the state of Fig. 8, click on the conditional statement 1 (60) and press the AND operator addition button 41 with the selected condition. Then, as shown in FIG. 9, the query designer inserts the conditional expression 4 (90) which is an AND relation with the conditional expression 1 (60) and automatically recognizes and inserts the position of the logical operator and the parenthesis. In the state of FIG. 9, the conditional expression of the filter is (( ( conditional 1 AND conditional condition 4) OR conditional condition 3) AND conditional condition 2). In the state shown in FIG. 8, an underlined portion is further added.

Fig. 10 shows the process of completing the added conditional expression. When a table column called "title" is brought into the first blank 91 of the conditional statement 4 (90), the conditional statement 4 (90) is changed as shown in FIG.

When the user clicks the second blank, the available operators 92 are listed as shown in FIG. 11, and if one of them is selected, the second blank is filled. In FIG. 11, only the comparison operator is illustrated for the sake of illustration, but the query designer of the present invention can provide both the comparison operator and the SQL operator usable in the corresponding table column.

Every time a conditional is completed, the completed conditional statement is applied to the SQL of the result viewing area 50, and a condition corresponding to the conditional statement will be additionally displayed under the where.

In the example of FIG. 12, since only the first condition 60 is completed, it can be seen that the conditional expression of the where clause stays in Phone = '010-8934-4372'.

12 to 14 are diagrams for explaining virtual columns supported by the present invention.

According to one embodiment of the present invention, the query designer supports format columns (Formatted type), computed columns (Compute type), and condition columns (CaseWhenThen type) which are virtual columns other than the data types provided by the database. A virtual column is not a column stored in an actual table, but a column that has been reconfigured to have new data using table columns for convenience.

To create a virtual column, a virtual column addition button provided in the column list area 30 is pressed. In the example of FIG. 12, only the format column addition button 31 and the calculation column addition button 32 are included, but the condition column addition button can also be implemented in a similar manner.

When you click the Add Virtual Column button, the virtual column of that type is added to the column list. The name and the attribute of the virtual column can be edited in the virtual column editing area 34 on the right side.

Format columns are used to transform strings using existing table columns. For example, if you want to insert the recipient's name in the promotional text to create a promotional mail or social network service (SNS) ad text, insert the recipient's name column in the table within the promotional text, You can get this other promotional text.

12 shows an example of generating a format column " format column 1 ". The user inputs a character string to be inserted into the virtual column in the virtual column editing area 34 and imports the "email" column of the table and inserts the character string into the middle of the character string to define "format column 1" Column 1 "column in the query, the query designer creates the corresponding format column definition and reflects it in the SQL statement.

Referring to FIG. 12, a column having a system name of " formatted_20 " is added corresponding to " format column 1 " At this time, as the definition of the " formatted_20 " column according to the editing in the virtual column editing area 40,

(Email || 'Hello, Hello!') As formatted_20

Is automatically generated and inserted.

When the generated query is executed, the following result is returned from the query of the "formatted_20" column.

Hi kim_sara@naver.com!

Hi Lee_michael@hanmail.net Hello!

...

 That is, the value of the "Email" column of the corresponding record is inserted and output in each string, and the result is that a string having a different mail address is automatically generated for each record.

As you can see, you can get the query result as it is displayed in the virtual column editing area by simply inputting the text in the form desired by the user and fetching the column at the desired position. Therefore, the concatenation operator "||" It is possible to create a virtual column without any technical knowledge.

13 shows an example of generating a " computed column 1 "

The computed column is used to calculate an existing column value and convert it to a new value. The column definition of " computed column 1 "

100 + Number 2

, A column having a system name of math_93 is added in correspondence with " computed column 1 "

According to the editing in the virtual column editing area,

100 + Number2 as math_93

The definition of the column is inserted.

On the other hand, the condition column of the CaseWhenThen type,

case when condition Then the contents of the output

Can be defined as follows.

When creating conditional columns, by default,

case when ___ then ___

Will be displayed and the user will have to complete the conditional expression and input the contents of the output by bringing in the column to be included in the condition. For example, if you enter " title like 'sky" as a condition and "ocean" as the content of the output, you would return "sea" if the title column's value is similar to' sky '.

14 shows an example of generating a condition column " customer grade ".

When the condition column adding button is pressed, the user interface of the virtual column editing area 34 will be changed as shown in FIG. If you enter the column name and click the Add Conditional Statement button (140), one conditional is added line by line.

If you add the condition and output contents to the added space, the definition of the condition column is completed. The condition of the output is set by fetching the column necessary for the condition from the column list area 30 to the first blank 142 and inputting the desired data into the second blank 143 and inputting the contents of the output to the third blank 144 do.

In order for the completed condition column "customer rating" to be queried by the query, "customer rating" is selected in the column list area 30. The definition corresponding to the "customer rating" column will be automatically added to the SQL statement format below the select in the query statement.

"Royal User" is output as the value of the "customer rating" column for the record having the table column "Rank" of "A" in the result of the inquiry executing the generated query sentence, and the record having the rank " "Silver User" will be displayed as the value in the "Customer Rating" column.

Thus, conditional columns can be useful when you want to convert existing data values into different values collectively according to the condition.

Meanwhile, the query designer will transmit an instruction to the database server 10 to convert the contents of the filter created in the filter edit region 40 into XML-format data and store the converted data in the database when the user requests it.

When the XML file is called by the user, the called XML file is analyzed and converted into a filter form and displayed in the filter edit area 40, thereby allowing the existing filter to be used or changed for another operation .

FIG. 14 illustrates a screen showing XML created by a filter created in the filter editing area.

If the XML view button 43 of the filter editing area 40 is clicked, the file 141 describing the filter being edited in XML can be viewed. The query designer can save this XML file in the database and recall it later when creating other views or creating views on other tables.

In the example of FIG. 14, the filter condition is described according to the XML standard. However, the filter condition is not limited to the XML standard.

The method according to an embodiment of the present invention can be implemented in the form of a program command which can be executed through various computer means and recorded in a computer-readable medium. The computer-readable medium may include program instructions, data files, data structures, and the like, alone or in combination. The program instructions recorded on the medium may be those specially designed and constructed for the present invention or may be available to those skilled in the art of computer software. Examples of computer-readable media include magnetic media such as hard disks, floppy disks and magnetic tape; optical media such as CD-ROMs and DVDs; magnetic media such as floppy disks; Magneto-optical media, and hardware devices specifically configured to store and execute program instructions such as ROM, RAM, flash memory, and the like. Examples of program instructions include machine language code such as those produced by a compiler, as well as high-level language code that can be executed by a computer using an interpreter or the like.

While the present invention has been particularly shown and described with reference to exemplary embodiments thereof, it is to be understood that the invention is not limited to the disclosed exemplary embodiments, It belongs to the scope of right.

10: Database server
11: User terminal
14: Database

Claims (8)

Computer,
Providing a user interface screen including a column list area for selecting columns of a table to be queried by a query, a filter editing area for editing a filter indicating a query condition, and a result viewing area for displaying a query being generated Wow;
Displaying a list of columns of the selected table in the column list area;
Generating a query sentence corresponding to one or more columns selected from the column list by the user and displaying the generated query sentence in the result viewing area,
A user moves a desired column on the basis of WYSIWYG (WYSIWYG, what you see is what you get), designs a query corresponding to moving and selecting objects included in a query condition,
The design of the query is made in the column list area and the filter editing area,
Wherein a query condition itself is changed by designing the query. The method according to claim 1,
Wherein the column list area includes a column filter for controlling whether a column is displayed for each data type,
Wherein a column corresponding to a data type released by the user from the column filter is deleted from the column list area. The method according to claim 1,
Wherein the column list area includes a user interface that can define and add reconstructed virtual columns to have new data using the columns of the table. The method of claim 3,
The virtual column includes at least one of a computation column defined by a computation formula using a column value of a table, a format column defined by a string including a column value of the table, and a condition column defined by an output condition based on a column value of the table A query design method. The method according to claim 1,
Wherein the filter edited in the filter editing area includes at least one conditional rule arranged for each line, and the relationship between the conditional expressions is defined by parentheses and logical operators that bind the conditional expressions and the columns required for each conditional rule are determined by drag- Is moved from the column list area. The method according to claim 1,
Converting the contents of the edited filter in the filter editing area into an XML file and storing the converted XML file;
And when the XML file is called by the user, interpreting the XML file, converting the XML file into a filter format, and displaying the XML file in the filter edit area. A computer program recorded on a recording medium for performing a query design method, the method comprising:
Providing a user interface screen including a column list area for selecting columns of a table to be queried by a query, a filter editing area for editing a filter indicating a query condition, and a result viewing area for displaying a query being generated Wow;
Displaying a list of columns of the selected table in the column list area;
Generating a query sentence corresponding to one or more columns selected from the column list by the user and displaying the generated query sentence in the result viewing area,
A user moves a desired column on the basis of WYSIWYG (WYSIWYG, what you see is what you get), designs a query corresponding to moving and selecting objects included in a query condition,
The design of the query is made in the column list area and the filter editing area,
Characterized in that the query condition itself is altered by designing the query. The method according to claim 1,
A filter editing unit for editing a filter in the filter editing area according to an input of the user, wherein when the user selects a new logical operator, a new conditional expression and a new logical operator are added, and a parenthesis ; ≪ / RTI >
And changing a query condition included in a query sentence of the result viewing area according to a change of a filter edited in the filter editing area.

Images