CN103473306A - Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction - Google Patents
Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction Download PDFInfo
- Publication number
- CN103473306A CN103473306A CN2013104104378A CN201310410437A CN103473306A CN 103473306 A CN103473306 A CN 103473306A CN 2013104104378 A CN2013104104378 A CN 2013104104378A CN 201310410437 A CN201310410437 A CN 201310410437A CN 103473306 A CN103473306 A CN 103473306A
- Authority
- CN
- China
- Prior art keywords
- sql
- condition
- field
- module
- metadata information
- 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
Images
Abstract
The invention relates to a method for adopting a structured query language (SQL) mark substitution method to achieve data self-extraction. The method comprises a first step of marking fields and conditions needing displaying in an SQL when the SQL with a random complexity needs to perform visualized data extraction, a second step of parsing selectable derived fields and selectable derived conditions in the SQL after marking, and obtaining metadata information corresponding to the fields and the conditions from an appointed database table according to the parsed data, a third step of generating a data extraction template aiming at all the marked SQLs on the basis of the obtained metadata information, and a fourth step of inputting marks of SQL fields and conditions needing extracting when the data are extracted, adopting the generated data extraction template to obtain the metadata information matched with the corresponding marks, utilizing the metadata information to substitute the fields and conditions needing extracting, and deriving the SQL after substitution. By establishing the data extraction template, the SQL is solidified to be in a graphic interface mode, and system supporting capacity and usability are improved.
Description
Technical field
The present invention relates to computing machine business applied technical field, particularly relate to a kind of employing SQL(Structured Query Language, Structured Query Language (SQL)) the Marker exchange method realizes the method and system that data are asked for.
Background technology
SQL is Structured Query Language (SQL), and it is a kind of data base querying and programming language, for access data with inquiry, renewal and management database system.
Conventional background data base data acquisition mode is that the business personnel submits demand to, entrusts the technician to carry out the data derivation by database client, and this mode comparatively bothers for the related personnel who is unfamiliar with SQL.Therefore, the existing database system is guaranteeing under the prerequisite of meeting consumers' demand to pursue higher ease for use, needs the operation of more complexity is placed in background program and carrys out analyzing and processing, and it is more intelligent that this will solve the program of analysing.At present a lot of SQL program curings are only supported simple single table SQL query statement, do not support or few support sector divided data built-in function, and the complicated SQL statement of multilayer nest can't resolve, to the function of a lot of disparate databases also None-identifieds all.Therefore, the method that existing SQL obtains back-end data can't meet needs in client's actual production and work to a great extent, its a large amount of complicated SQL statement of using, and SQL statement that need be complicated to these is carried out loaded down with trivial details parsing, easily cause the function short slab, thereby the background data base system is performed practically no function.A kind of method and system that adopt SQL Marker exchange method to generate the peek template of the present invention, propose for addressing the above problem.
Summary of the invention
Technical matters to be solved by this invention is to provide a kind of SQL of employing Marker exchange method and realizes the method and system that data are asked for, and for solving available technology adopting SQL, obtains back-end data and has circumscribed problem.
The technical scheme that the present invention solves the problems of the technologies described above is as follows: a kind of SQL of employing Marker exchange method realizes the method that data are asked for, and comprising:
Step 1: for the SQL of any complexity, when it need carry out visual peek, need field and the condition of being showed in this SQL of mark;
Step 2: optional derived field and optional derivation condition in the SQL after the parsing mark, and obtain the metadata information corresponding with field and condition from the Database System Tables of appointment according to the data that parse;
Step 3: the metadata information based on obtaining generates the peek template for the SQL of institute's mark;
Step 4: while extracting data, SQL field and condition that the input peek is required, and adopt the peek template generated to obtain and the SQL field of peeking required and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
On the basis of technique scheme, the present invention can also do following improvement.
Further, in described step 2, all optional derivation condition in the SQL after the parsing mark specifically refers to that the employing regular expression parses optional derivation condition substatement.
Further, metadata information is saved in corresponding application system database after obtaining metadata information in described step 2.
Further, while in the SQL in described step 2 after the parsing mark, described step 4, in input SQL field and condition and described step 4, deriving SQL, all need verification SQL whether legal.
Further, in described step 4, when metadata information is replaced peek required field and condition, reject in SQL and do not need the field information and the conditional information that extract, for guaranteeing the user, finally only receive the data message needed.
Further, also comprise after carrying out described step 4: carry out the SQL derived in target database, obtain desired data, and export desired data to the user.
Based on said method, technical scheme of the present invention also comprises that a kind of SQL of employing Structured Query Language (SQL) Marker exchange method realizes the system that data are asked for, and it comprises mark module, parsing module, template generation module and replacement module:
Described mark module, it,, for when the SQL of complexity need carry out visual peek arbitrarily, needs field and the condition of being showed in this SQL of mark;
Described parsing module, it connects described mark module, for resolving all optional derived field and the optional derivation condition of the SQL after mark, and obtain the metadata information corresponding with field and condition according to the data that parse from the Database System Tables of appointment;
Described template generation module, it connects described parsing module, for the metadata information based on obtaining, generates the peek template for the SQL of institute's mark;
Described replacement module, it connects described template generation module, for adopting the peek template of generation to obtain the SQL field required with the peek of inputting and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
Further, described parsing module also is connected with an application data library module, the metadata information obtained for storage.
Further, described mark module and described replacement module respectively are connected with a SQL correction verification module, for when resolving SQL, input SQL field condition and deriving SQL, SQL are carried out to verification, legal to guarantee SQL.
Further, described system also comprises that is rejected a module, and it connects described template generation module and described replacement module, when at metadata information, replacing peek required field and condition, rejects in SQL and does not need the field information and the conditional information that extract.
The invention has the beneficial effects as follows: usage flag Shift Method of the present invention can be resolved the SQL statement of any difficulty, field and the condition of output SQL, and the corresponding peek of foundation template, advance to realize that SQL is cured as to the graphic interface form extracts data or output analysis diagram for other users, it has significantly promoted the system support ability, and non-data professional person operation also can skilled operation, promoted the ease for use of the background data base system based on SQL.
The accompanying drawing explanation
Fig. 1 is that the present invention adopts SQL Marker exchange method to realize the schematic flow sheet of the method that data are asked for;
Fig. 2 carries out the schematic flow sheet of SQL replacement in the embodiment of the present invention;
Fig. 3 is that the present invention adopts SQL Marker exchange method to realize the structural representation of the system that data are asked for.
In accompanying drawing, the list of parts of each label representative is as follows:
1, mark module, 2, parsing module, 3, the template generation module, 4, replacement module, 5, the application data library module, 6, the SQL correction verification module, 7, reject module.
Embodiment
Below in conjunction with accompanying drawing, principle of the present invention and feature are described, example, only for explaining the present invention, is not intended to limit scope of the present invention.
As shown in Figure 1, the present embodiment has provided a kind of SQL of employing Marker exchange method and has realized the method that data are asked for, and comprising:
Step 1: for the SQL of any complexity, when it need carry out visual peek, need field and the condition of being showed in this SQL of mark;
Step 2: optional derived field and optional derivation condition in the SQL after the parsing mark, and obtain the metadata information corresponding with field and condition from the Database System Tables of appointment according to the data that parse;
Step 3: the metadata information based on obtaining generates the peek template for the SQL of institute's mark;
Step 4: while extracting data, SQL field and condition that the input peek is required, and adopt the peek template generated to obtain and the SQL field of peeking required and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
In the present embodiment program implement, can with～and # come respectively the optional derived field of mark and optional derivation condition, and optional derivation condition entry value type is used # (Database data type) # to carry out mark.In program implement, use legal condition substatement block message in the SQL after field substatement piece regular expression is resolved mark, then parse field information with field regular expression mark; Service condition substatement piece regular expression parses the condition substatement, in like manner the service condition regular expression parses conditional information, as adopt regular expression to parse optional derivation condition substatement (meaning with Condition), use therein regular expression be (～s* (and AND|and) s* (w* .) w* s*=s*# (w+) # s*～).In addition, in the database table that parsing SQL relates to, comprise the relevant informations such as user name, another name, subregion and DBLink, the metadata set information that the database table of the condition substatement of extraction by linked database parses field in SQL, condition is saved in application system database.
In the present embodiment, during SQL in described step 2 after resolving mark, need first verification SQL whether legal, and only legal SQL is resolved.And when the SQL field that input will be extracted in described step 4 and the mark of condition and derivation SQL, also need SQL is carried out to verification, legal for guaranteeing SQL.
Need pay special attention to, in described step 4, when metadata information is replaced peek required field and condition, reject in SQL and do not need the field information and the conditional information that extract, for guaranteeing the user, finally only receive the data message needed.
Therefore, as shown in Figure 2, the concrete implementing procedure of described step 4 is: at first whether the SQL of certification label is legal, if do not conform to the rule process ends, otherwise continues to carry out; According to parameter replacement demand derived field, and reject not derived field; The derivation condition of selecting according to parameter replacement demand user, reject non-selected condition; SQL after replacing is carried out to verification, determine that whether SQL is legal.
Also need in target database, carry out the SQL derived after carrying out described step 4, obtain desired data, and export desired data to the user.
As shown in Figure 3, the present embodiment gives a kind of SQL of the employing Structured Query Language (SQL) Marker exchange method that realizes said method and realizes the system that data are asked for, and comprises mark module 1, parsing module 2, template generation module 3 and replacement module 4:
Described mark module 1, it,, for when the SQL of complexity need carry out visual peek arbitrarily, needs field and the condition of being showed in this SQL of mark;
Described parsing module 2, it connects described mark module 1, for resolving all optional derived field and the optional derivation condition of the SQL after mark, and obtain the metadata information corresponding with field and condition according to the data that parse from the Database System Tables of appointment;
Described template generation module 3, it connects described parsing module 2, for the metadata information based on obtaining, generates the peek template for the SQL of institute's mark;
Described replacement module 4, it connects described template generation module 3, for adopting the peek template of generation to obtain the SQL field required with the peek of inputting and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
Described parsing module 2 also is connected with an application data library module 5, the metadata information obtained for storage; Described mark module 1 and described replacement module 4 respectively are connected with a SQL correction verification module 6, for when resolving SQL, input SQL mark and deriving SQL, SQL are carried out to verification, legal to guarantee SQL; Described system also comprises that is rejected a module 7, and it connects described template generation module 3 and described replacement module 4, when at metadata information, replacing peek required field and condition, rejects in SQL and does not need the field information and the conditional information that extract.
In concrete application, after backstage creates the peek template, need design on foreground corresponding graphic interface based on said method and system, whole implementation comprises following a few step:
One, determine template name, template usage data source, Authority Verification mode (note, examine), whether allow to issue etc.;
Two, control exhibition method, display data, the displaying authority of configuration all conditions;
Three, system analysis preserve SQL analytical element data message;
Four, the user selects to solidify template and is used for peek or present graphical etc.;
Five, the user selects to need displaying or derived field and fills in necessary condition and optional condition;
Six, fill in task names and derived data form and whether cycle peek etc., creation task;
Seven, check data, report pattern according to formulating before of task, download to analyze or pass to the peripheral interface analysis by interface and use.
The foregoing is only preferred embodiment of the present invention, in order to limit the present invention, within the spirit and principles in the present invention not all, any modification of doing, be equal to replacement, improvement etc., within all should being included in protection scope of the present invention.
Claims (10)
1. one kind adopts SQL Structured Query Language (SQL) Marker exchange method to realize the method that data are asked for, and it is characterized in that, comprising:
Step 1: for the SQL of any complexity, when it need carry out visual peek, need field and the condition of being showed in this SQL of mark;
Step 2: optional derived field and optional derivation condition in the SQL after the parsing mark, and obtain the metadata information corresponding with field and condition from the Database System Tables of appointment according to the data that parse;
Step 3: the metadata information based on obtaining generates the peek template for the SQL of institute's mark;
Step 4: while extracting data, SQL field and condition that the input peek is required, and adopt the peek template generated to obtain and the SQL field of peeking required and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
2. method according to claim 1, is characterized in that, the optional derivation condition in the SQL in described step 2 after the parsing mark specifically refers to and adopts regular expression to parse optional derivation condition substatement.
3. method according to claim 1, is characterized in that, metadata information is saved in corresponding application system database after obtaining metadata information in described step 2.
4. whether method according to claim 1, is characterized in that, while in the SQL in described step 2 after the parsing mark, described step 4, in input SQL field and condition and described step 4, deriving SQL, all need verification SQL legal.
5. method according to claim 1, it is characterized in that, when metadata information is replaced peek required field and condition, reject in SQL and do not need the field information and the conditional information that extract in described step 4, for guaranteeing the user, finally only receive the data message needed.
6. according to arbitrary described method in right 1 to 5, it is characterized in that, also comprise after carrying out described step 4: carry out the SQL derived in target database, obtain desired data, and export desired data to the user.
7. one kind adopts SQL Structured Query Language (SQL) Marker exchange method to realize the system that data are asked for, and it is characterized in that, comprises mark module, parsing module, template generation module and replacement module:
Described mark module, it,, for when the SQL of complexity need carry out visual peek arbitrarily, needs field and the condition of being showed in this SQL of mark;
Described parsing module, it connects described mark module, for resolving all optional derived field and the optional derivation condition of the SQL after mark, and obtain the metadata information corresponding with field and condition according to the data that parse from the Database System Tables of appointment;
Described template generation module, it connects described parsing module, for the metadata information based on obtaining, generates the peek template for the SQL of institute's mark;
Described replacement module, it connects described template generation module, for adopting the peek template of generation to obtain the SQL field required with the peek of inputting and the metadata information of Condition Matching, and replace peek required field and condition with the metadata information obtained, then field and condition based on after replacing derive executable SQL.
8. system according to claim 7, is characterized in that, described parsing module also is connected with an application data library module, the metadata information obtained for storage.
9. system according to claim 7, is characterized in that, described mark module and described replacement module respectively are connected with a SQL correction verification module, for when resolving SQL, input SQL field condition and deriving SQL, SQL carried out to verification, legal to guarantee SQL.
10. according to arbitrary described system in claim 7 to 9, it is characterized in that, described system also comprises that is rejected a module, it connects described template generation module and described replacement module, when at metadata information, replacing peek required field and condition, reject in SQL and do not need the field information and the conditional information that extract.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013104104378A CN103473306A (en) | 2013-09-10 | 2013-09-10 | Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013104104378A CN103473306A (en) | 2013-09-10 | 2013-09-10 | Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103473306A true CN103473306A (en) | 2013-12-25 |
Family
ID=49798154
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2013104104378A Pending CN103473306A (en) | 2013-09-10 | 2013-09-10 | Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103473306A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107025233A (en) * | 2016-01-29 | 2017-08-08 | 苏宁云商集团股份有限公司 | A kind of processing method and processing device of data characteristics |
| CN107908725A (en) * | 2017-11-14 | 2018-04-13 | 中国银行股份有限公司 | A kind of batch data method of calibration, device and system |
| CN108334321A (en) * | 2017-01-18 | 2018-07-27 | 阿里巴巴集团控股有限公司 | The presentation device of program code, method and device |
| CN105205123B (en) * | 2015-09-11 | 2018-12-04 | 北京恒华伟业科技股份有限公司 | Data interactive method and device between a kind of database |
| CN109753513A (en) * | 2018-12-19 | 2019-05-14 | 国网新疆电力有限公司电力科学研究院 | Account check method and system are matched in a kind of defeated change based on regular expression matching |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1892945A2 (en) * | 2006-08-21 | 2008-02-27 | Ricoh Company, Ltd. | Meta data customizing method |
| CN102193922A (en) * | 2010-03-04 | 2011-09-21 | 杭州华三通信技术有限公司 | Method and device for accessing database |
| CN102708142A (en) * | 2012-03-14 | 2012-10-03 | 北京神州数码思特奇信息技术股份有限公司 | Method and system for acquiring background data through structured query language (SQL) custom-made template |
-
2013
- 2013-09-10 CN CN2013104104378A patent/CN103473306A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1892945A2 (en) * | 2006-08-21 | 2008-02-27 | Ricoh Company, Ltd. | Meta data customizing method |
| CN102193922A (en) * | 2010-03-04 | 2011-09-21 | 杭州华三通信技术有限公司 | Method and device for accessing database |
| CN102708142A (en) * | 2012-03-14 | 2012-10-03 | 北京神州数码思特奇信息技术股份有限公司 | Method and system for acquiring background data through structured query language (SQL) custom-made template |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105205123B (en) * | 2015-09-11 | 2018-12-04 | 北京恒华伟业科技股份有限公司 | Data interactive method and device between a kind of database |
| CN107025233A (en) * | 2016-01-29 | 2017-08-08 | 苏宁云商集团股份有限公司 | A kind of processing method and processing device of data characteristics |
| CN107025233B (en) * | 2016-01-29 | 2020-04-28 | 苏宁云计算有限公司 | Data feature processing method and device |
| CN108334321A (en) * | 2017-01-18 | 2018-07-27 | 阿里巴巴集团控股有限公司 | The presentation device of program code, method and device |
| CN107908725A (en) * | 2017-11-14 | 2018-04-13 | 中国银行股份有限公司 | A kind of batch data method of calibration, device and system |
| CN109753513A (en) * | 2018-12-19 | 2019-05-14 | 国网新疆电力有限公司电力科学研究院 | Account check method and system are matched in a kind of defeated change based on regular expression matching |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| He et al. | A database linking Chinese patents to China’s census firms | |
| CN104866426B (en) | Software test integrated control method and system | |
| CN103473306A (en) | Method and system for adopting structured query language (SQL) mark substitution method to achieve data self-extraction | |
| CN101493834A (en) | Method for generating evidence for keeping account by random service system document | |
| CN101539855A (en) | Service basic software platform | |
| CN103927314B (en) | A kind of method and apparatus of batch data processing | |
| CN108268525A (en) | A kind of report implementation method and device based on Excel template | |
| CN103605747A (en) | Method and device for processing file form | |
| CN103412868A (en) | Method and device for generating document | |
| CN106886535A (en) | A kind of data pick-up method and apparatus for being adapted to multiple data sources | |
| CN107748752A (en) | A kind of data processing method and device | |
| CN101751400A (en) | System and method of technical data analysis and system of patent analysis | |
| CN105574660A (en) | Supplier evaluation and analysis system | |
| CN109408502A (en) | A kind of data standard processing method, device and its storage medium | |
| CN101877109A (en) | Application system tool set based on online banking service platform | |
| CN112541692B (en) | Scientific data management plan generation method and device | |
| CN103473305A (en) | Method and system for performing decision-making process show in statistic analysis | |
| CN102866985B (en) | For data formatter and the method for on-line analysing processing system | |
| CN104572055B (en) | Layout script generation device and layout scenario generation method | |
| CN112131855B (en) | Bank certificate template generation method and device | |
| CN101520864A (en) | Method for realizing subject support items in accounting management process | |
| Siqueira | Sucupira-A Platform for the Evaluation of Graduate Education in Brazil | |
| CN115080594A (en) | Method and system for carrying out multi-dimensional analysis on data and electronic equipment | |
| CN109190000A (en) | A kind of Engineering documents Management method | |
| CN112286879B (en) | Metadata-based data asset construction method and device |
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 |
Application publication date: 20131225 |
|
| RJ01 | Rejection of invention patent application after publication |