A client-based database SQL training and testing system and its implementation method
By identifying the topological relationship of tables in the database client system and generating SQL test questions, the problem of lack of SQL practice functions in the existing technology is solved, and the convenience and efficiency of users to improve SQL capabilities in the same software are achieved.
Patent Information
- Application Number
- CN202310485668.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2023-04-28
- Publication Date
- 2024-11-12
- Estimated Expiration
- 2043-04-28
AI Technical Summary
The existing database client software lacks the functions used to practice and improve SQL capabilities, which leads to users who need to practice on other platforms, making the user experience inconvenient.
A client-based database SQL test system was designed. Through the client, the topological relationship of the table is identified, and many-to-many, one-to-many and single table test SQL test questions are generated. Database information is used to generate based on users. Familiar practice and test questions, provide multiple sets of test templates to meet the test volume needs.
Users can conduct SQL training and tests directly on the client side to improve SQL capabilities. The generated test questions are based on the user's database information, and the training ability is more targeted and improve the user experience.
Smart Images

Figure CN116737779B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to a client-based database SQL training and testing system and an implementation method thereof. Background Art
[0002] In the field of database technology, database client is one of the important software that users must have. Although there is software, if users want to operate databases or data efficiently, it is more intuitive and efficient to operate through SQL. Therefore, mastering SQL skills can improve the efficiency of users using software and operating databases.
[0003] At present, the mainstream database client software mainly includes Navicat, DataGrip, and Dbeaver. By analyzing the function lists they provide, it is found that these software provide a large number of operation functions, but lack the functions for practicing and testing the user's ability to obtain data using the SQL language in the database client to help them improve their SQL ability. Therefore, if users want to improve their SQL ability, they can only go to other question-answering platforms. Although this can ultimately achieve the purpose of improving SQL ability, using the software and improving SQL ability are not in the same software or platform, which is very inconvenient and the user experience is poor. Summary of the invention
[0004] The present invention aims to at least solve the technical problems existing in the prior art, and in particular innovatively proposes a client-based database SQL training and testing system and an implementation method thereof.
[0005] In order to achieve the above-mentioned object of the present invention, the present invention provides a database SQL training and testing system based on a client, comprising:
[0006] Database server: used to store database data;
[0007] Client: installed in the computer operating system to access the database server;
[0008] Server: used to collect the SQL test questions and answer data generated by the client, generate test reports, and feed them back to the client;
[0009] The client is connected to the server and the database server respectively.
[0010] The present invention also proposes a method for implementing a database SQL training and testing system based on a client, the key of which is how to make good use of the user's database information and then generate SQL training and testing questions and answers familiar to the user from this information. Specifically, the following steps are included:
[0011] 1) Connect to the database server through the link information configured by the user on the client;
[0012] 2) The client program reads the table information and table structure information in the database server;
[0013] 3) Identify the topological relationship between database tables; this is achieved through the following two steps;
[0014] a) Identify the primary key and logical foreign key in the table through naming conventions; the identification rules are as follows:
[0015] i. If the field name is _id, it is the primary key of the table;
[0016] ii. If the field name ends with _id, it is a logical foreign key;
[0017] b) Generate a topological relationship diagram between tables based on the foreign key relationship identified in the previous step, and store the out-degree, in-degree, foreign key and other information of each table;
[0018] 4) Generate SQL test questions for many-to-many relationship tables; implementation steps:
[0019] a) Filter out the many-to-many relationship table through out-degree and in-degree data;
[0020] i. If a table has no out-degree and the number of in-degrees is greater than 1, it indicates a many-to-many relationship.
[0021] ii. If a table has no out-degree and the number of in-degrees is equal to 1, it means there is a 1-to-1 relationship.
[0022] iii. If the number of in-degree and out-degree of a table is 0, it means that it is an independent table.
[0023] b) Use the above many-to-many related table names, fields and other information to replace the test question template and generate SQL test questions;
[0024] c) Execute the data check SQL to obtain the data result. If the result is empty, the generation of this question is terminated. If the result has data, the information of this question is generated through the question template, including title, thought analysis, output fields, included fields, answer and other information;
[0025] 5) Generate a one-to-many relationship table SQL test question; implementation steps:
[0026] a) Filter out the one-to-many relationship table through out-degree and in-degree data;
[0027] b) Use the one-to-many related table name above to replace the data check template SQL for this question;
[0028] c) Execute the data check SQL to obtain the data result. If the result is empty, the generation of this question is terminated. If the result has data, the information of this question is generated through the question template, including title, thought analysis, output fields, included fields, answer and other information;
[0029] 6) Generate SQL test questions for a single table; implementation steps:
[0030] a) Determine whether to generate this test question through the table annotation, field annotation, index and other information of the table. If the table annotation is empty or the field annotation is empty or the index name does not start with idx_, the information of this test question is generated through the test question template, including the title, thought analysis, output field, included field, answer and other information. Otherwise, the generation of this test question is terminated. A test question can be generated through the above process, but this cannot meet the demand for the number of test questions for practice and test. Therefore, the present invention provides multiple sets of test question templates with different question types for many-to-many relationships, one-to-many, and single tables. When generating test questions, each table information of the current database is used to match each set of test question templates. If the table information meets the generation conditions of the module, the current table will generate a test question in combination with the current set of test question templates. However, a table will try multiple sets of templates, and it is possible that a table will generate multiple test questions. This solution can generate the number of test questions that meet the practice test.
[0031] In summary, due to the adoption of the above technical solution, the present invention enables users to call database data through the client and practice SQL, thereby improving SQL ability, which greatly facilitates users. In particular, the generated SQL practice questions are generated based on the user's own database information, which is more conducive to training the user's SQL ability.
[0032] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS
[0033] The above and / or additional aspects and advantages of the present invention will become apparent and easily understood from the description of the embodiments in conjunction with the following drawings, in which:
[0034] Figure 1 It is a schematic diagram of the system deployment structure of the present invention.
[0035] Figure 2 This is a diagram of the steps for practicing SQL test questions.
[0036] Figure 3 This is a schematic diagram of the steps for generating a table topology diagram.
[0037] Figure 4 It is a schematic diagram for generating SQL practice test questions. DETAILED DESCRIPTION
[0038] Embodiments of the present invention are described in detail below, examples of which are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and cannot be understood as limiting the present invention.
[0039] See Figure 1 As shown, a client-based database SQL training and testing system includes a database server, a client terminal, and a service terminal.
[0040] The database server can be different mainstream database products, running in any supported operating system. The client is the system of the present invention, which supports installation in Windows and Mac operating systems and has the ability to access mainstream databases.
[0041] The client communicates with the database server through the network, including normal database operation communication and basic information acquisition communication generated by SQL test questions.
[0042] The server is mainly used to collect the SQL test questions and answer data generated by the client, to generate test reports, and to feed back to the client.
[0043] See Figure 2 As shown, a method for implementing database SQL training and testing based on a client is provided. The database server obtains the access address and database account information provided to the client, and then the SQL training and testing function can be used through the system of the present invention. The detailed steps of use are as follows:
[0044] 1) Obtain the address and account information of the database server;
[0045] 2) Configure the database server address and account information in the client;
[0046] 3) After connecting to the database server, open the SQL test function; the program will automatically generate test questions based on the database information. The generation steps are as follows:
[0047] a) Read the database tables, table fields and other information;
[0048] b) According to the naming convention, analyze the foreign key relationship of the table fields to generate a topological relationship diagram between the tables;
[0049] c) Generate many-to-many, one-to-many, single-table and other related exercises based on the topological relationship diagram;
[0050] The SQL test questions for generating many-to-many relationship tables are as follows:
[0051] A. Filter out the many-to-many relationship table through out-degree and in-degree data;
[0052] i. If a table has no out-degree and the number of in-degrees is greater than 1, it indicates a many-to-many relationship.
[0053] ii. If a table has no out-degree and the number of in-degrees is equal to 1, it means there is a 1-to-1 relationship.
[0054] iii. If the number of in-degree and out-degree of a table is 0, it means that it is an independent table.
[0055] B. Use the above many-to-many related table names, fields and other information to replace the test question template and generate SQL test questions;
[0056] C. Execute the data check SQL to obtain the data result. If the result is empty, the generation of this question is terminated. If the result has data, the information of this question is generated through the question template, including title, thought analysis, output fields, included fields, answers and other information;
[0057] The SQL test questions that generate a one-to-many relationship table; implementation steps:
[0058] A. Filter out the one-to-many relationship table through out-degree and in-degree data;
[0059] B. Use the one-to-many related table name above to replace the data check template SQL for this question;
[0060] C. Execute the data check SQL to obtain the data result. If the result is empty, the generation of this question is terminated. If the result has data, the information of this question is generated through the question template, including title, thought analysis, output fields, included fields, answers and other information;
[0061] The SQL test questions for a single table are generated; implementation steps:
[0062] A. Determine whether to generate this test question through the table annotation, field annotation, index and other information of the table. If the table annotation is empty or the field annotation is empty or the index name does not start with idx_, the information of this test question is generated through the test question template, including title, thought analysis, output field, included field, answer and other information. Otherwise, the generation of this test question is terminated. A test question can be generated through the above process, but this cannot meet the demand for the number of test questions for practice and test. Therefore, the system of the present invention provides multiple sets of test question templates with different question types for many-to-many relationships, one-to-many, and single tables. When generating test questions, these templates will check whether test questions need to be generated. This solution can generate the number of test questions that meet the practice and test requirements.
[0063] d) Feedback the generated exercises to the interface display
[0064] 4) In the interface opened in the previous step, complete the viewing and answering of the SQL practice test questions; at this time, the program will automatically collect the practice test data and send it to the server for storage. The feedback steps are as follows:
[0065] a) Collect data on SQL answers, number of answers, correct answers, etc. for generating analysis charts for SQL test reports;
[0066] b) Submit the training and testing data to the server;
[0067] c) The server saves the training and testing data to the database server;
[0068] The steps for generating the table topology diagram are shown in Figure 3 As shown, to generate SQL questions based on the information of all tables in the selected database, one of the key points is to identify the many-to-many, one-to-many and other relationships between tables. Based on this relationship, accurate and more complex multi-table association query exercises can be generated. In actual work, physical foreign keys are generally not set between tables for constraints, which brings great obstacles to identifying table relationships. The present invention solves this obstacle by first identifying logical foreign keys and then generating table relationship topology. The specific implementation steps are as follows:
[0069] 1) Read the table information in the database, including table name and comment information;
[0070] 2) Read the field information of each table in the database, including field name, field length, field type, field comment and other information;
[0071] 3) Generate the table logical foreign key set. The generation steps are as follows:
[0072] a) Determine whether the table includes a field named id;
[0073] b) If the field with the id name is not included, the logical foreign key of this table is not generated;
[0074] c) If the field of id name is included, a logical foreign key of this table is generated; the logical foreign key is generated according to the table name, the steps are as follows:
[0075] i. The table name is divided into multiple parts according to the underscore (_). For example, the sys_user table name is divided into two parts: sys and user.
[0076] ii. The cut parts are combined into logical foreign key names one by one in ascending order from the back to the front. For example, for the two parts sys and user, the last part user is combined first, and the suffix _id is added to it, so the logical foreign key of user_id is generated; then the last two parts sys and user are combined, and multiple parts are concatenated in sequence with underscores to form sys_user, and then the suffix _id is added to it, so the logical foreign key of sys_user_id is generated; in addition, in actual work, the first underscore part of the table name is often the table prefix, so this prefix can be ignored when combining the logical primary key. In the above example, sys_user includes sys, which is the first part of the table name, so the sys_user table finally generates only one logical foreign key user_id;
[0077] iii. Store the generated logical foreign keys into a collection;
[0078] d) Traverse the logical foreign key set and compare it with each field of each table. If the logical foreign key and the field name are the same, associate this table with the table corresponding to this logical foreign key (that is, record the out-degree and in-degree information in the two table information here respectively), and store the associated information in a set.
[0079] e) Traverse the logical foreign key set and all table field information in the previous step to generate the topological relationship data between tables, that is, the out-degree, in-degree and other information of the table. The generation steps are as follows:
[0080] i. Traverse the fields of each table (this table is called table A) and determine whether the field ends with _id;
[0081] ii. If the field does not end with _id, the foreign key association of this field is terminated;
[0082] iii. If the field ends with _id, obtain the table information of the logical foreign key from the logical foreign key set (this table is called table B). If obtained, record the table lists of in-degree and out-degree in tables A and B respectively; if the associated table information is not obtained, end the foreign key association of this field;
[0083] See Figure 4 As shown, a client-based database SQL practice test system should generate enough practice questions to meet the user's practice test needs. The software has built-in rich test question templates. When generating test questions, it automatically detects whether the corresponding test question template is generated based on the selected database table and table data information. This method solves the problem of a small number of generated SQL practice questions. The detection steps are as follows:
[0084] 1) Traverse each table in the selected database;
[0085] 2) Traverse each built-in test template. A test template contains the following main parts:
[0086] i. The minimum number of out-degree or in-degree required by the template
[0087] ii. Data checking method for test questions generated by template
[0088] iii. Generate a string template for the test title
[0089] iv. Generate a string template for solving test questions
[0090] v. Generate a string template for answering the test questions
[0091] vi. Generate a string template containing keywords for the test answer
[0092] In each template, the table in-degree, table out-degree, table name, table field, table index, and table data in the first step are used to detect whether to generate test questions. If the template requirements are met, the test questions are generated, otherwise they are skipped. The key judgments are as follows:
[0093] a) Get the in-degree or out-degree information of the current table, and compare it with the in-degree and out-degree requirements of the built-in test template; if the minimum in-degree or out-degree requirements of the test template are not met, the process ends; if the minimum in-degree or out-degree requirements are met, the process continues to the next step;
[0094] b) Determine whether there is data in the table that meets the requirements of the template. If not, end the process. If it does, proceed to the next step. The test template has the following requirements for data:
[0095] i. Whether the SQL answer to the current test template can find data in the table; if not, skip generating it;
[0096] ii. Is there any data of the type that meets the requirements of the template in the current table? If so, the test questions can be generated.
[0097] c) Generate relevant component information such as test questions, ideas, answers, keywords, etc.
[0098] i. Generate test questions by replacing the question string template in the test question template with the current table, field and other information;
[0099] ii. Generate solution ideas by replacing the idea string template in the test question template with the current table, field and other information;
[0100] iii. Generate the answer to the question by replacing the answer string template in the question template with the current table, field and other information;
[0101] iv. Use the key string template in the test question template to replace the information of the current table, field, etc. to generate the keywords that should be included in the answer.
[0102] Although the embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.
Claims
1. A method for implementing a database SQL training and testing system based on a client, characterized in that: The following steps are involved: S1, obtain the link information configured by the client and connect to the database server; S2, the client reads the table information and table structure information in the database server; S3, determine whether the client has received the SQL test instruction, if yes, execute the next step; If not, call the database data according to the input SQL; S4, identifying the topological relationship between database tables and automatically generating practice test questions based on database information; S4-1, reading table information in a database, wherein the table information includes a table name and / or comment information; S4-2, reading the field information of each table in the database, wherein the field information includes one or any combination of field name, field length, field type, and field comment; S4-3, generating a table logical foreign key set, and then analyzing the foreign key relationship of the table fields to generate a topological relationship diagram between the tables; S4-4, exercises on generating one or any combination of many-to-many relationships, one-to-many relationships, and single-table relationships based on the topological relationship diagram; The generating table logical foreign key set is to store the generated logical foreign keys into a set to obtain a logical foreign key set. The generating of the logical foreign keys includes the following steps: (1) Determine whether the table includes a field named id. If the table does not include a field named id, the logical foreign key of the table is not generated. If the table includes a field named id, the logical foreign key of the table is generated. (2) Traverse the logical foreign key set and compare it with each field of each table. If the logical foreign key and the field name are the same, associate this table with the table corresponding to this logical foreign key and store the association information in a set; (3) Traverse the logical foreign key set and all table field information in the previous step, generate the topological graph relationship data between tables, and store the out-degree, in-degree and foreign key information of each table; i. Traverse the fields of each table, which is called table A, and determine whether the field ends with _id; ii. If the field does not end with _id, the foreign key association of this field is terminated; iii. If the field ends with _id, the table information of the logical foreign key is obtained from the logical foreign key set. The table is called table B. If the information of table B is successfully obtained, the in-degree and out-degree information are recorded in table A and table B respectively; if the associated table information is not obtained, the foreign key association of this field is terminated; When generating test questions, S5 automatically detects whether the questions corresponding to the test question template are generated according to the selected database table and table data information. The detection steps are as follows: (1) Traverse each table in the selected database; (2) Traverse each built-in test question template. A test question template includes: the minimum number of out-degree or in-degree required by the template, the data checking method for generating test questions, the string template for generating the test question title, the string template for generating the test question solution idea, the string template for generating the test question solution answer, and the string template for generating the test question answer containing keywords; Determine whether the table meets the template requirements based on the table's in-degree, out-degree, table name, table field, table index, and table data. If the table meets the template requirements, generate the test template questions, otherwise skip; Determining whether a table meets the template requirements includes the following steps: 1) Get the in-degree or out-degree number information of the current table, and compare it with the in-degree and out-degree number requirements of the built-in test template; if the minimum in-degree or out-degree number of the test template is not met, the process ends; if it is met, continue to the next step; 2) Determine whether there is data in the table that meets the requirements of the template. If not, end. If yes, proceed to the next step. The test template has the following data requirements: Whether the SQL answer to the current test template can find data in the current table; if not, skip generating it; Whether there is data of the type that meets the requirements of the template in the current table; if so, the test questions can be generated; 3) Generate information related to test questions, ideas, answers, and keywords; i. Generate test questions by replacing the question string template in the test question template with the current table, field and other information; ii. Generate solution ideas by replacing the idea string template in the test question template with the current table, field and other information; iii. Generate the answer to the question by replacing the answer string template in the question template with the current table, field and other information; iv. Use the key string template in the test question template to replace the current table, field and other information to generate the keywords that should be included in the answer.
2. According to a client-based database SQL training and testing system implementation method according to claim 1, it is characterized in that: The logical foreign key of this table is generated according to the table name, including the following steps: i. The table name is divided into multiple parts according to the underscore; ii. The cut parts are combined into logical foreign key names one by one in an incremental manner from the back to the front, and then the table prefix is removed, and the table prefix is the first part of the underscore; the final logical foreign key is obtained.
3. According to a client-based database SQL training and testing system implementation method according to claim 1, it is characterized in that: The S4-4 comprises the following steps: (1) Filter the table by out-degree data and in-degree data: If a table has no out-degree and the number of in-degrees is greater than 1, it indicates a many-to-many relationship; If a table has no out-degree and the number of in-degrees is equal to 1, it means there is a 1-to-1 relationship; If the number of in-degree and out-degree of a table is 0, it means that this is an independent table; Then filter out the one-to-many relationships from the many-to-many relationships; (2) If it is a many-to-many relationship, extract the table name and field information of the many-to-many relationship table based on the many-to-many relationship table obtained by screening in the previous step, replace the many-to-many relationship question template and generate SQL questions; If it is a one-to-many relationship, then according to the one-to-many relationship table obtained by screening in the previous step, extract the relevant table name and field information of the many-to-many relationship table, replace the one-to-many relationship question template and generate SQL questions; If it is an independent table, the table comment, field comment, and index information of the table are used to determine whether to generate this test question. If any of the following conditions are met: the table comment is empty, the field comment is empty, or the index name does not start with idx_, then the SQL test question can be generated by replacing the relevant content in the test question template; otherwise, the generation of this test question is terminated. (3) Execute the data check SQL to obtain the data result. If the result is empty, the generation of this test question is terminated. If the result contains data, the information of this test question is generated through the test question template. The information includes the title, idea analysis, output fields, included fields, and answer information.
4. According to a client-based database SQL training and testing system implementation method according to claim 1, it is characterized in that: It also includes S6, which feeds back the generated practice questions to the interface display. When the SQL practice test questions are answered, the client collects the practice test SQL answers, the number of answers, and the correct answer data and sends them to the server. The server saves the practice test data to the database server and generates a practice test report analysis chart.
Citation Information
Patent Citations
Question setting method and device and server
CN110704503A
Test question score analysis and exercise improvement system
CN114495124A
Method and system for automatically generating association relationship between database tables, electronic equipment and program product
CN115757660A
Method and system for learning language and literature
CN1353375A
Method for testing a schoolwork capability
KR1020040069456A
