CN107818178A - A kind of method of automatically generated data storehouse change SQL scripts - Google Patents
A kind of method of automatically generated data storehouse change SQL scripts Download PDFInfo
- Publication number
- CN107818178A CN107818178A CN201711182779.3A CN201711182779A CN107818178A CN 107818178 A CN107818178 A CN 107818178A CN 201711182779 A CN201711182779 A CN 201711182779A CN 107818178 A CN107818178 A CN 107818178A
- Authority
- CN
- China
- Prior art keywords
- sql
- automatically generated
- reversely
- change
- data storehouse
- 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/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Abstract
A kind of method of automatically generated data storehouse of the present application change SQL scripts, tables of data addition timestamp field on the basis of this method；When reference data changes, synchronized update correlation time stamp information；SQL wscript.exes are reversely generated based on preset time section, therefore can be when development environment be related to the change of master meter data, no longer need synchronous arrangement SQL scripts, and it is automatically generated the change SQL scripts in preset time section, upgrade job efficiency is greatly improved, while avoids maloperation caused by manual operation possibility.
Description
Technical field
The present invention relates to computer software fields, and in particular to a kind of method of automatically generated data storehouse change SQL scripts.
Background technology
SQL (Structured Query Language), abbreviation SQL, be a kind of data base querying and
Programming language, for accessing data and inquiry, renewal and administrative relationships Database Systems.Benchmark database, also it is base
Quasi- storehouse, all data changes of development environment are all acted on benchmark database, such as newly-built menu, change the authority information of role
Deng, project field upgrade when, the SQL scripts of change data can be exported from pattern library.Master meter is the tables of data inside pattern library.
In software field, software product after the issue of new version, rises if desired often in continuous renewal iteration
Level includes increment upgrading, the database data table of program code to the production environment in project, it is necessary to do substantial amounts of upgrade job
Increment upgrading etc., while be also contemplated that compatibility issue.
Upgrade this part in data table data, then current realization rate goes to scene generally by SQL scripts are arranged
The mode that production environment performs upgrades, but this upgrading mode is required under benchmark development environment, be related to master meter number every time
According to change, will synchronously arrange SQL scripts, waste time and energy, manually carry out it is this operation be also easy to malfunction.
Based on above mentioned problem, the present invention realizes a kind of method of automatically generated data storehouse change SQL scripts, and this method can
With when development environment is related to the change of master meter data, it is no longer necessary to synchronous to arrange SQL scripts；When in-situ production environment upgrades,
The change SQL scripts in preset time section can be automatically generated using method of the present invention, greatly improve upgrade job
Efficiency, while avoid maloperation caused by manual operation possibility.
The content of the invention
The present invention is divided into three steps, first step, on the basis of tables of data addition timestamp field；Second step, benchmark
When data change, synchronized update correlation time stamp information；Third step, SQL wscript.exes are reversely generated based on preset time section.
Specifically, a kind of method of automatically generated data storehouse change SQL scripts is claimed in the application, it is characterised in that
This method specifically comprises the following steps：
First step, on the basis of tables of data addition timestamp field；
Second step, when reference data changes, synchronized update correlation time stamp information；
Third step, SQL wscript.exes are reversely generated based on preset time section.
The method of change SQL scripts in automatically generated data storehouse as described above, is further characterized in that, the addition of reference data table
Timestamp field include newly-built timestamp, renewal time stamp, erasing time stamp, whether deleted status.
The method of change SQL scripts in automatically generated data storehouse as described above, is further characterized in that, based on preset time section
Reversely generation SQL wscript.exes further comprise：All master meters are traveled through, each master meter is searched and is increased newly in preset time section
Data record, further search for the metadata information of each master meter, reversely generate the insert SQL statements of newly-built record.
The method of change SQL scripts in automatically generated data storehouse as described above, is further characterized in that, based on preset time section
Reversely generation SQL wscript.exes further comprise：All master meters are traveled through, each master meter is searched and is updated in preset time section
Data record, further search for the metadata information of each master meter, reversely generate the update SQL statements of more new record.
The method of change SQL scripts in automatically generated data storehouse as described above, is further characterized in that, based on preset time section
Reversely generation SQL wscript.exes further comprise：All master meters are traveled through, each master meter is searched and is deleted in preset time section
Data record, further search for the metadata information of each master meter, reversely generate the delete SQL statements of deletion record.
The method of change SQL scripts in automatically generated data storehouse as described above, is further characterized in that, the member of each master meter
Data message includes：Field name, field type.
Embodiment
The specific implementation step of the method for the invention：
(1) tables of data adds timestamp field on the basis of
Specific instruction is as follows：
Whether is_deleted char (1) have deleted, and 0 does not delete；1 has deleted
Deleted_at datet ime erasing times
Created_at datet ime creation times
Updated_at datet ime modification times
(2) when reference data changes, synchronized update correlation time stamp information
The data change of related to database master meter, is required for the corresponding timestamp of synchronized update.Such as：Newdata
Record, creation time is set；Certain data record is changed, updates modification time；Certain data is deleted, erasing time is set, and
Is_deleted values are revised as 1.
(3), based on the given beginning and ending time, SQL wscript.exes are reversely generated.
1st, all master meters are traveled through, search the data record that each master meter increases newly in preset time section.
Specific instruction is as follows：
Select*from master meter where created_at>=time started stamp and created_at<=at the end of
Between stab；
2nd, the metadata information (field name, field type) of each master meter is further searched for, reversely generates insert
SQL statement.
Such as：Insert into master meters (field 1, field 2, field 3 ...) values (value 1, value 2, value 3 ...)
3rd, all master meters are traveled through, search each master meter data record updated in preset time section.
Specific instruction is as follows：
Select*from master meter where updated_at>=time started stamp and updated_at<=at the end of
Between stab；
4th, the metadata information (field name, field type, major key information) of each master meter is further searched for, it is reversely raw
Into update SQL statements.
Such as：Update master meter set field 1=values 1, field 2=values 2, field 3=values 3 ... where major keys=master
Key assignments；
5th, all master meters are traveled through, search the data record that each master meter is deleted in preset time section.
Specific instruction is as follows：
Select*from master meter where is_deleted='1'and deleted_at>=the time started stabs and
deleted_at<=ending time stamp；
6th, the metadata information (field name, field type, major key information) of each master meter is further searched for, it is reversely raw
Into delete SQL statements.
Such as：Delete master meter set field 1=values 1, field 2=values 2, field 3=values 3 ... where major keys=major key
Value；
It should be evident that illustrated above is only the specific embodiment of the present invention, for the common skill in this area
For art personnel, on the premise of not paying creative work, other technical schemes can also be obtained according to above-described embodiment,
And the equivalent variations made in the scope of protection of the invention all should be fallen within the scope of protection of the present invention, and belong to the present invention
The scope of protection.
In summary, the present invention only needs the given beginning and ending time, you can generates SQL change scripts with a key, improves work
Make efficiency, reduce the chance of manual operations error, drastically increase research and development test, production efficiency.
Claims (6)
- A kind of 1. method of automatically generated data storehouse change SQL scripts, it is characterised in that this method specifically comprises the following steps：First step, on the basis of tables of data addition timestamp field；Second step, when reference data changes, synchronized update correlation time stamp information；Third step, SQL wscript.exes are reversely generated based on preset time section.
- 2. the method for change SQL scripts in automatically generated data storehouse as claimed in claim 1, is further characterized in that, reference data Table addition timestamp field include newly-built timestamp, renewal time stamp, erasing time stamp, whether deleted status.
- 3. the method for change SQL scripts in automatically generated data storehouse as claimed in claim 2, is further characterized in that, based on given Period reversely generates SQL wscript.exes and further comprised：All master meters are traveled through, search each master meter in preset time section Interior newly-increased data record, the metadata information of each master meter is further searched for, reversely generate the insert of newly-built record SQL statement.
- 4. the method for change SQL scripts in automatically generated data storehouse as claimed in claim 3, is further characterized in that, based on given Period reversely generates SQL wscript.exes and further comprised：All master meters are traveled through, search each master meter in preset time section The data record of interior renewal, the metadata information of each master meter is further searched for, reversely generate the update of more new record SQL statement.
- 5. the method for change SQL scripts in automatically generated data storehouse as claimed in claim 4, is further characterized in that, based on given Period reversely generates SQL wscript.exes and further comprised：All master meters are traveled through, search each master meter in preset time section The data record of interior deletion, the metadata information of each master meter is further searched for, reversely generate the delete of deletion record SQL statement.
- 6. the method for change SQL scripts in automatically generated data storehouse as claimed in claim 5, is further characterized in that, each benchmark The metadata information of table includes：Field name, field type.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711182779.3A CN107818178A (en) | 2017-11-23 | 2017-11-23 | A kind of method of automatically generated data storehouse change SQL scripts |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711182779.3A CN107818178A (en) | 2017-11-23 | 2017-11-23 | A kind of method of automatically generated data storehouse change SQL scripts |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107818178A true CN107818178A (en) | 2018-03-20 |
Family
ID=61609953
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201711182779.3A Pending CN107818178A (en) | 2017-11-23 | 2017-11-23 | A kind of method of automatically generated data storehouse change SQL scripts |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107818178A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109522364A (en) * | 2018-10-18 | 2019-03-26 | 恒峰信息技术有限公司 | A kind of strange land data double-way synchronous method and system |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101552701A (en) * | 2008-04-03 | 2009-10-07 | 华为技术有限公司 | Method, system and device for recording parameter maintenance variation |
| CN102662946A (en) * | 2012-02-20 | 2012-09-12 | 北京地拓科技发展有限公司 | Method and system for automatically monitoring record changes in database |
| CN104298769A (en) * | 2014-10-29 | 2015-01-21 | 北京思特奇信息技术股份有限公司 | System and method for synchronizing discrepant data of common domain between databases |
-
2017
- 2017-11-23 CN CN201711182779.3A patent/CN107818178A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101552701A (en) * | 2008-04-03 | 2009-10-07 | 华为技术有限公司 | Method, system and device for recording parameter maintenance variation |
| CN102662946A (en) * | 2012-02-20 | 2012-09-12 | 北京地拓科技发展有限公司 | Method and system for automatically monitoring record changes in database |
| CN104298769A (en) * | 2014-10-29 | 2015-01-21 | 北京思特奇信息技术股份有限公司 | System and method for synchronizing discrepant data of common domain between databases |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109522364A (en) * | 2018-10-18 | 2019-03-26 | 恒峰信息技术有限公司 | A kind of strange land data double-way synchronous method and system |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| EP3258393A1 (en) | Data revision control in large-scale data analytic systems | |
| US9646030B2 (en) | Computer-readable medium storing program and version control method | |
| CN104252452A (en) | Data management method and device | |
| CN103049251B (en) | A kind of data base persistence layer device and database operation method | |
| CN108153784A (en) | Synchrodata treating method and apparatus | |
| CN104331285A (en) | Automatic code generation method and system | |
| CN102402559A (en) | Database upgrade script generating method and device | |
| CN105224377A (en) | A kind of method by metadata automatic generating software project code file and device | |
| CN102646040B (en) | The method for producing software set based on merit part and merit part and device | |
| CN109508355A (en) | A kind of data pick-up method, system and terminal device | |
| CN105354036A (en) | Database operation code generating method and apparatus | |
| CN104657387A (en) | Data query method and device | |
| CN106802905B (en) | Collaborative data exchange method of isomorphic PLM system | |
| CN102393860A (en) | Database data migration system | |
| CN108595324A (en) | Test cases reuse management method and system | |
| CN105183645A (en) | Reuse based design and implementation method for radar software testing | |
| CN104252519A (en) | Increment updating method of topographic database | |
| CN105224527A (en) | Be applicable to the general ETL method of multiple object table update mode | |
| CN101923464A (en) | DO-178B standard document generator | |
| CN102857949A (en) | Method and device for planning data consistency guarantees | |
| CN107818178A (en) | A kind of method of automatically generated data storehouse change SQL scripts | |
| CN103107919A (en) | Method and system for network resource modeling | |
| CN104748757A (en) | Data updating method and device for navigation electronic map | |
| CN103678396A (en) | Data backup method and device based on data models | |
| CN106325833A (en) | Code generation method for enterprise information management |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| TA01 | Transfer of patent application right |
Effective date of registration: 20200522 Address after: Building S01, Inspur Science Park, No. 1036, Inspur Road, high tech Zone, Jinan City, Shandong Province, 250000 Applicant after: Tidal Cloud Information Technology Co.,Ltd. Address before: 450000 Henan province Zheng Dong New District of Zhengzhou City Xinyi Road No. 278 16 floor room 1601 Applicant before: ZHENGZHOU YUNHAI INFORMATION TECHNOLOGY Co.,Ltd. |
|
| TA01 | Transfer of patent application right | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20180320 |
|
| RJ01 | Rejection of invention patent application after publication |