CN105975489A - Metadata-based online SQL code completion method - Google Patents
Metadata-based online SQL code completion method Download PDFInfo
- Publication number
- CN105975489A CN105975489A CN201610265319.6A CN201610265319A CN105975489A CN 105975489 A CN105975489 A CN 105975489A CN 201610265319 A CN201610265319 A CN 201610265319A CN 105975489 A CN105975489 A CN 105975489A
- Authority
- CN
- China
- Prior art keywords
- name
- database
- user
- completion
- metadata
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
Abstract
The invention discloses a metadata-based online SQL code completion method. The method comprises the following steps: carrying out configuration management on different types of database connection information; acquiring interfaces of various types of database metadata ; realizing a web end page function by adopting Jsp jQuery and Java Scripts; and improving an SQL code completion function of a Codemirror plugin, modifying a completion rule, dynamically carrying out automatic completion on different types of SQL sentences in real time, and dynamically displaying an interaction result. Compared with the prior art, the method disclosed in the invention has the positive effects of online realizing different types of database SQL code completion functions through improving the SQL code completion function of the Codemirror plugin by utilizing metadata information of databases, replacing database commercial client software used by data developers, reducing the enterprise cost and improving the work efficiency.
Description
Technical field
The present invention relates to a kind of online SQL code complementing method based on metadata.
Background technology
Flourish along with big data technique, enterprise to the data pick-up between disparate databases, conversion,
The demand loaded grows with each passing day, such as, in ETL dispatching patcher, need to run substantial amounts of disparate databases
The SQL code of type, including MySQL, Hive, Oracle and Cassandra.Therefore, online S
QL code completion is particularly important for data mining personnel.
Current different types of data base all has corresponding commercial accounts ending tool, and these instruments have powerful
Auto-complete function, but ignore the factor of Financial cost, when developer writes SQL code online,
Generally require and first write in commercial accounts' ending tool of its correspondence, be then copied to the place needing to run,
Such as ETL dispatching patcher.The workflow of this off-line greatly reduces the work effect of data mining personnel
Rate.
Summary of the invention
In order to overcome the shortcoming of prior art, the invention provides a kind of online SQL generation based on metadata
Code complementing method, for data mining personnel provide a kind of online, that be similar to data base commercial accounts end,
Support the SQL code completion instrument of distinct type data-base simultaneously.
The technical solution adopted in the present invention is: a kind of online SQL code complementing method based on metadata,
Including following content:
One, distinct type data-base link information is managed for configuration, including increasing newly, delete, revise and
Query function；
Two, realize obtaining the interface of all kinds database metadata, including:
1) according to database linkage information, all data in the database software corresponding with link information are obtained
The interface of library name；
2) according to database linkage information and database name, the database software corresponding with link information is obtained
The interface of all table names under middle finger given data library name；
3) according to database linkage information, database name and table name, the number corresponding with link information is obtained
Interface according to all row names specified in specified database in library software under table name；
Three, Jsp, jQuery and JavaScripts is used to realize web terminal page function；
Four, improving the SQL code completion function of Codemirror plug-in unit, amendment completion is regular, in real time,
Dynamically dissimilar SQL statement is carried out auto-complete, and interaction results is carried out Dynamic Announce.
Compared with prior art, the positive effect of the present invention is: utilizes the metadata information of data base, passes through
The SQL code of Codemirror plug-in unit is mended Full Featured improvement, it is possible to canbe used on line different types of data
Storehouse SQL code completion function, the data base commercial accounts end software that alternate data developer uses, energy
Reduce entreprise cost, improve work efficiency.
Accompanying drawing explanation
Examples of the present invention will be described by way of reference to the accompanying drawings, wherein:
Fig. 1 is the flowchart of the inventive method；
Fig. 2 is completion keyword and database name schematic diagram；
Fig. 3 is completion table name and row name schematic diagram；
Detailed description of the invention
A kind of online SQL code complementing method based on metadata, as it is shown in figure 1, include following content:
One, distinct type data-base link information is managed for configuration, including increasing newly, delete, revise and
Query function:
The contents of the section is as subsequent step input content, what type of for data mining personnel selection operation
Data base, it is proposed that the mode using Spring, MVC and mybatis to combine realizes, wherein data base is even
The information of connecing should comprise following field: data base ID, type of database, data base's configuration name, data base
URL, database user name, database user password.For guaranteeing data security, data base do be used
Family password is encrypted.
Two, realize obtaining the interface of all kinds database metadata, including:
1) according to database linkage information, all data in the database software corresponding with link information are obtained
The interface of library name；
2) according to database linkage information and database name, the database software corresponding with link information is obtained
The interface of all table names under middle finger given data library name；
3) according to database linkage information and database name and table name, the number corresponding with link information is obtained
Interface according to all row names specified in specified database in library software under table name.
Wherein, MySQL uses JDBC to perform the form of SQL statement, obtains its database name, number
According to the table under library name and the row under table name；Oracle also uses JDBC to perform the mode of SQL statement, obtains
Take the row under database instance name under active user, table and table name；Hive then uses and calls metadata client
The mode of HiveMetaStoreClient interface, the table obtained in Hive under database name, database name and
Row under table name.
Three, web terminal page function is realized.
Use Jsp, jQuery and JavaScripts to realize web terminal page function, configure including data base
The page basic function such as mutual of title choice box, SQL input frame, web terminal and server end.
The data base's configuration name selected according to user, it is judged that active user will operate certain categorical data
Storehouse.
Four, improving the SQL code completion function of Codemirror plug-in unit, amendment completion is regular, in real time,
Dynamically dissimilar SQL statement is carried out auto-complete, and interaction results is carried out Dynamic Announce.
At present, the SQL code completion function of Codemirror plug-in unit, the need of actual application can not be met
Ask.Owing to it needs using complete list structure information as input, therefore, use primary version Codemirror
The SQL code completion function of plug-in unit, needs, according to the database linkage information chosen, once to obtain this number
According to all list structure information in storehouse, in actual applications, can exist and there is the table of a lot of row name and comprise
The data base of a large amount of table names, to such an extent as to web page collapses because of the mutual of single large-scale data.
Therefore, the SQL code completion function of Codemirror plug-in unit is improved by the present invention, first,
When user chooses a data storehouse link information, and Background scheduling obtains the interface of database name, obtains current number
According to all database names in library software.Then according to the input information of user, it is judged that being currently entered content is
It is no for keyword with database name, as user's input ". ", then according to the content before ". ", it is judged that
The content that user will input is table name or row name, and calls in real time according to judged result and connect accordingly
Mouthful, obtain table name and row name that user may input, then carry out mating completion.By above-mentioned improvement,
Only as user's input ". ", just need to obtain accurately relevant table name or row name, the method from backstage
The data volume that web page single is mutual can be substantially reduced.
As shown in Figures 2 and 3, it is as follows that concrete completion realizes details to the present invention concrete completion rule:
The rule of completion keyword and database name is as in figure 2 it is shown, work as user and enter at SQL code input frame
Writing of row SQL code, when input character length is the content of 3, the content that user is inputted and SQL
Keyword carries out fuzzy matching: if the match is successful, then judge that user currently may need to input keyword,
And point out user may need the keyword of input, the content otherwise user inputted and the number obtained from backstage
Carry out fuzzy matching according to library name: if the match is successful, then judge that user currently may need input database name,
And point out user may need the database name of input, otherwise currently without completion information alert.
The rule of completion table name and row name is as it is shown on figure 3, as user inputs ". ", then according to ". " front defeated
Enter content and judge that active user may need to input table name and still arrange name: if ". " front input content is for several
According to library name, then call and obtain the interface of table name in data base, obtain the table name under ". " front database name,
And by next step input content of user with table name fuzzy matching, if matched, then prompting user may need
The table name of input, otherwise currently without completion information alert；If ". " front input content is table name, then adjust
With obtaining in data base, the interface of the row name of specific table, and next step input content same column name of user is obscured
Coupling, if matched, then prompting user may need the row name of input, otherwise carries currently without completion information
Show.
Present invention B/S based on web page service framework equivalence can replace with C/S based on client service
Framework；Above-mentioned Codemirror plug-in unit can equivalence replace with other have SQL code mend Full Featured insert
Part；And on the basis of above-mentioned full implementation, other kinds of data base can be added, as
Cassandra。
Claims (7)
1. an online SQL code complementing method based on metadata, it is characterised in that: include following interior
Hold:
One, distinct type data-base link information is managed for configuration, including increasing newly, delete, revise and
Query function；
Two, realize obtaining the interface of all kinds database metadata, including:
1) according to database linkage information, all data in the database software corresponding with link information are obtained
The interface of library name；
2) according to database linkage information and database name, the database software corresponding with link information is obtained
The interface of all table names under middle finger given data library name；
3) according to database linkage information, database name and table name, the number corresponding with link information is obtained
Interface according to all row names specified in specified database in library software under table name；
Three, Jsp, jQuery and JavaScripts is used to realize web terminal page function；
Four, improving the SQL code completion function of Codemirror plug-in unit, amendment completion is regular, in real time,
Dynamically dissimilar SQL statement is carried out auto-complete, and interaction results is carried out Dynamic Announce.
A kind of online SQL code complementing method based on metadata the most according to claim 1, its
It is characterised by: described database linkage information comprises following field: data base ID, type of database, number
According to storehouse configuration name, data base URL, database user name, database user password.
A kind of online SQL code complementing method based on metadata the most according to claim 2, its
It is characterised by: described database user password need to be encrypted.
A kind of online SQL code complementing method based on metadata the most according to claim 1, its
It is characterised by: MySQL uses JDBC to perform the form of SQL statement, obtains its database name, number
According to the table under library name and the row under table name；Oracle also uses JDBC to perform the mode of SQL statement, obtains
Take the row under database instance name under active user, table and table name；Hive uses and calls metadata client
The mode of HiveMetaStoreClient interface, the table obtained in Hive under database name, database name and
Row under table name.
A kind of online SQL code complementing method based on metadata the most according to claim 1, its
It is characterised by: described web terminal page function includes that data base's configuration name choice box, SQL code input
Frame, web terminal and server end mutual.
A kind of online SQL code complementing method based on metadata the most according to claim 1, its
It is characterised by: in described completion rule, the implementation method of completion keyword and database name is: user is inputted
Content and SQL keyword carry out fuzzy matching: if the match is successful, then judge user currently may need
Keyword to be inputted, and point out user may need input keyword, otherwise by user input content and
The database name obtained from backstage carries out fuzzy matching: if the match is successful, then judge that user currently may need
Want input database name, and point out user may need the database name of input, otherwise currently without completion information
Prompting.
A kind of online SQL code complementing method based on metadata the most according to claim 1, its
It is characterised by: in described completion rule, the implementation method of completion table name and row name comprises the steps:
Step one, user input ". "；
Whether the front input content of step 2, judgement ". " is database name or table name: database name in this way,
Then enter step 3；Table name in this way, then enter step 6；If neither, then currently without completion information
Prompting.
Step 3, call and obtain the interface of table name in data base, obtain all tables under ". " front database name
Name；
Step 4, by next step input content of user with table name mate: if the match is successful, then enter step
Five；Otherwise currently without completion information alert；
Step 5, the possible table name needing input of prompting user, select for user；If after user input name,
Input ". ", then enter step 6 again；Otherwise show that user need not completion row name；
Step 6, according to input acquisition of information ". " front table name and database name, call acquisition and specify data
In storehouse, the interface of all row names under appointment table name, obtains the row name under the table name that ". " is front；
Step 7, by next step input content same column name of user mate, if the match is successful, then enter step
Eight；Otherwise currently without completion information alert；
Step 8, the possible row name needing input of prompting user, select for user.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610265319.6A CN105975489A (en) | 2016-04-26 | 2016-04-26 | Metadata-based online SQL code completion method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610265319.6A CN105975489A (en) | 2016-04-26 | 2016-04-26 | Metadata-based online SQL code completion method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN105975489A true CN105975489A (en) | 2016-09-28 |
Family
ID=56993066
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610265319.6A Pending CN105975489A (en) | 2016-04-26 | 2016-04-26 | Metadata-based online SQL code completion method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105975489A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106802799A (en) * | 2016-12-29 | 2017-06-06 | 金蝶软件（中国）有限公司 | JS codes automatic prompt method and device based on the frameworks of WAF II |
| CN108733720A (en) * | 2017-04-24 | 2018-11-02 | 北京京东尚科信息技术有限公司 | The on-line testing method and check system of the SQL query of multiple database type |
| CN109726213A (en) * | 2018-12-10 | 2019-05-07 | 网易无尾熊（杭州）科技有限公司 | A kind of program code conversion method, device, medium and calculate equipment |
| CN109947828A (en) * | 2019-03-15 | 2019-06-28 | 优信拍（北京）信息科技有限公司 | A kind of method and apparatus handling report data |
| CN111444708A (en) * | 2020-04-01 | 2020-07-24 | 山东汇贸电子口岸有限公司 | SQ L statement intelligent completion method based on use scene |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050216503A1 (en) * | 2001-11-30 | 2005-09-29 | Regis Charlot | Method for adaptive data management |
| CN101876893A (en) * | 2009-04-28 | 2010-11-03 | 上海亿客网络科技有限公司 | Automatic generation method of WEB project code |
| CN103473105A (en) * | 2013-09-25 | 2013-12-25 | 北京大学 | Realizing method of online code completion function |
-
2016
- 2016-04-26 CN CN201610265319.6A patent/CN105975489A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050216503A1 (en) * | 2001-11-30 | 2005-09-29 | Regis Charlot | Method for adaptive data management |
| CN101876893A (en) * | 2009-04-28 | 2010-11-03 | 上海亿客网络科技有限公司 | Automatic generation method of WEB project code |
| CN103473105A (en) * | 2013-09-25 | 2013-12-25 | 北京大学 | Realizing method of online code completion function |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106802799A (en) * | 2016-12-29 | 2017-06-06 | 金蝶软件（中国）有限公司 | JS codes automatic prompt method and device based on the frameworks of WAF II |
| CN106802799B (en) * | 2016-12-29 | 2020-06-12 | 金蝶软件（中国）有限公司 | JS code automatic prompting method and device based on WAF II framework |
| CN108733720A (en) * | 2017-04-24 | 2018-11-02 | 北京京东尚科信息技术有限公司 | The on-line testing method and check system of the SQL query of multiple database type |
| CN109726213A (en) * | 2018-12-10 | 2019-05-07 | 网易无尾熊（杭州）科技有限公司 | A kind of program code conversion method, device, medium and calculate equipment |
| CN109947828A (en) * | 2019-03-15 | 2019-06-28 | 优信拍（北京）信息科技有限公司 | A kind of method and apparatus handling report data |
| CN111444708A (en) * | 2020-04-01 | 2020-07-24 | 山东汇贸电子口岸有限公司 | SQ L statement intelligent completion method based on use scene |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11334594B2 (en) | Data model transformation | |
| JP7273045B2 (en) | Dimensional Context Propagation Techniques for Optimizing SQL Query Plans | |
| US11003645B1 (en) | Column lineage for resource dependency system and graphical user interface | |
| US9747360B2 (en) | Mapping non-relational database objects into a relational database model | |
| US9298829B2 (en) | Performing a function on rows of data determined from transitive relationships between columns | |
| US20160224594A1 (en) | Schema Definition Tool | |
| US20130311454A1 (en) | Data source analytics | |
| CN105975489A (en) | Metadata-based online SQL code completion method | |
| WO2017048303A1 (en) | Graph-based queries | |
| US10296505B2 (en) | Framework for joining datasets | |
| CN102426582B (en) | Data manipulation management devices and data manipulation management method | |
| US11768854B2 (en) | Data permissioning through data replication | |
| CN103744891A (en) | Method and system for data query | |
| CN102917009B (en) | A kind of stock certificate data collection based on cloud computing technology and storage means and system | |
| US9805112B2 (en) | Method and structure for managing multiple electronic forms and their records using a static database | |
| US20140136472A1 (en) | Methodology supported business intelligence (BI) software and system | |
| EP3018595A1 (en) | System and method for reporting multiple objects in enterprise content management | |
| CN105302842A (en) | Data processing method and device | |
| EP3486798A1 (en) | Reporting and data governance management | |
| CN108228762B (en) | Method and system for configuring master database universal templates | |
| Ben-Gan | T-Sql Fundamentals | |
| Jennings | Professional ADO. NET 3.5 with LINQ and the Entity Framework | |
| US10318524B2 (en) | Reporting and data governance management | |
| CN111125045B (en) | Lightweight ETL processing platform | |
| US11893026B2 (en) | Advanced multiprovider optimization |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20160928 |