CN108536758B - Data table reconstruction method, device and system for database mode - Google Patents
Data table reconstruction method, device and system for database mode Download PDFInfo
- Publication number
- CN108536758B CN108536758B CN201810229982.XA CN201810229982A CN108536758B CN 108536758 B CN108536758 B CN 108536758B CN 201810229982 A CN201810229982 A CN 201810229982A CN 108536758 B CN108536758 B CN 108536758B
- Authority
- CN
- China
- Prior art keywords
- data
- paths
- tenant
- directed graph
- data table
- 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.)
- Expired - Fee Related
Links
- 238000000034 method Methods 0.000 title claims abstract description 24
- 238000012163 sequencing technique Methods 0.000 claims description 3
- 238000010586 diagram Methods 0.000 description 4
- 238000013499 data model Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000007726 management method Methods 0.000 description 2
- 241000219546 Damnacanthus major Species 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000008094 contradictory effect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 230000007613 environmental effect Effects 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000013507 mapping Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
Images
Abstract
The invention discloses a data Table reconstruction method, a device and a system of a database mode, wherein the method comprises the steps that a tenant inputs data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records are matched with a tenant ID and a Table ID, the tenant ID is used for identifying the identity of the tenant, and the Table ID is used for identifying the name of the data Table of the current tenant; dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one; dividing the data table of each tenant according to the table ID, wherein external key constraints exist among partial tables in the divided data table; generating a directed graph among the data tables according to the existing foreign key constraints; removing included paths from all paths in the directed graph, thereby obtaining the directed graph with pure paths; and dividing the data on each clean path in the directed graph into a data table, and reconstructing the database. The invention avoids the cross-database connection operation of the data to the maximum extent.
Description
Technical Field
The invention relates to the field of power grid dispatching communication, in particular to a method, a device and a system for reconstructing a data table of a database mode.
Background
SaaS is short for Software-as-a-Service, and with the development of Internet technology and the maturity of application Software, a completely innovative Software application mode is developed in the beginning of the 21 st century. The method is a mode for providing services through the Internet, a manufacturer uniformly deploys application software on a server of the manufacturer, a customer can order the required application software services from the manufacturer through the Internet according to actual requirements of the customer, and the customer pays fees to the manufacturer according to the amount and time of the ordered services. The user does not need to buy complete software, but rents Web-based software to the provider to manage the enterprise operation activity, and the service provider does not need to maintain the software, so that the service provider can manage and maintain the software in full authority, thereby greatly reducing the operation cost of the enterprise. Since all tenants share one operation instance in the SaaS system, how to manage tenant data is a very difficult problem.
In the SaaS system, data models generally used include an independent database, a shared database independent mode (Schema), and a shared database shared mode. For cost reasons, a data model of a shared database sharing mode is generally adopted. Under the model, various database modes are proposed, such as an extension Table Layout database mode, which is derived from Copeland GP, Khoshaffin S.A. composition storage model [ C ]// ACM SIGMOD International Conference on Management of data. ACM,1985: 268. sub.279; the Universal Table Layout Database schema is derived from Maier D, Ullman J D. major objects and the semantic of environmental relationships databases [ J ]. Acm Transactions on Database Systems,1983,8(1): 1-14; the Chunk Table Layout database schema is a block database schema proposed by T group et al (Aulbach S, group T, Jacobs D, et al. Multi-content databases for software as a service: schema-mapping technologies [ C ]// ACM SIGMOD International Conference on Management of Data, SIGMOD 2008, Vancouver, Bc, Canada, June.DBLP,2008: 1195-.
In which, we focus on the Universal Table Layout database schema, which is most commonly used in actual production, such as the one used by the currently most known SaaS system salesforce. However, in the database model, there are some obvious disadvantages that data of all tenants are put into one data table, so the operation speed of the user is a serious problem, and therefore, some ways need to be taken to reconstruct the data table therein, which can ensure the operation speed of the user and does not cause a large increase in database maintenance cost.
Disclosure of Invention
The invention mainly aims to provide a data Table reconstruction method based on a Universal Table Layout database mode, and aims to overcome the problems.
In order to achieve the above object, the present invention provides a data Table reconstruction method based on a Universal Table Layout database schema, which is characterized by comprising the following steps:
s10; the method comprises the steps that a tenant inputs data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records is matched with a tenant ID and a Table ID, the tenant ID is used for identifying the identity of the tenant, and the Table ID is used for identifying the name of the data Table of the current tenant;
s20, dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
s30, dividing the data table of each tenant according to the table ID, wherein external key constraints exist among partial tables in the divided data table;
s40, generating a directed graph between data tables according to the existing foreign key constraint;
s50, removing included paths from all paths in the directed graph, thereby obtaining the directed graph with pure paths;
s60, dividing the data on each pure path in the directed graph into a data table, and reconstructing the database.
Preferably, the method for generating the directed graph in S30 is:
if the data table A refers to the primary key of the data table B as a foreign key, a directional arrow pointing from the table A to the table B is drawn, and directional arrows are added among all the data tables to generate a directed graph among the data tables.
Preferably, the S40 includes:
s401, finding out data tables with out degrees being not 0 of all paths in the generated directed graph, and finding out all paths by taking the data tables with out degrees being not 0 as starting points in sequence;
s402, sequencing all paths in the directed graph in sequence according to the path sizes of the paths;
s403, eliminating included paths with smaller paths from all paths to obtain a directed graph with pure paths.
The invention also discloses a data Table reconstruction device based on the Universal Table Layout database mode, which comprises the following steps:
the data input module is used for a tenant to input data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records is matched with a tenant ID, and the tenant ID is used as an identification of a tenant identity;
the first dividing module is used for dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
the second division module is used for dividing the data table of each tenant according to the table ID, and external key constraints exist among partial tables in the divided data tables;
the generation module is used for generating a directed graph among the data tables according to the existing foreign key constraints;
the removing module is used for removing included paths from all paths in the directed graph so as to obtain the directed graph with pure paths;
and the reconstruction module is used for dividing the data on each pure path in the directed graph into a data table and reconstructing the database.
Preferably, the culling module comprises:
the searching unit is used for finding out data tables with out degrees different from 0 of all paths in the generated directed graph, and finding out all paths by taking the data tables with out degrees different from 0 as starting points in sequence;
the sorting unit is used for sequentially sorting all the paths in the directed graph according to the path sizes of the paths;
and the eliminating unit is used for eliminating included paths with smaller paths from all paths to obtain a directed graph with pure paths.
The invention also discloses a data Table reconstruction system based on the Universal Table Layout database mode, which comprises a server, the Universal Table Layout database and the data Table reconstruction device based on the Universal Table Layout database mode, and is used for realizing the data Table reconstruction method based on the Universal Table Layout database mode.
Compared with the prior art, the invention has the following advantages:
(1) the invention provides a method for dividing a data Table in a Universal Table Layout database mode, through the method, an administrator of a software as a service (SaaS) system can realize the division of the data Table, mass data in one data Table are avoided, and meanwhile, cross-database connection operation is reduced to the greatest extent.
(2) When the invention divides the data table, the relation among the logic tables is fully considered, and the data table with the external key constraint is divided into a database as much as possible.
(3) The method is suitable for the conditions that the data volume in the SaaS system is large and certain requirements are met for high concurrency.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the structures shown in the drawings without creative efforts.
FIG. 1 is a schematic diagram of a data table divided into a plurality of data tables according to tenant IDs;
FIG. 2 is a schematic diagram of a tenant data table relationship diagram;
fig. 3 is a diagram illustrating two paths starting from a.
The implementation, functional features and advantages of the objects of the present invention will be further explained with reference to the accompanying drawings.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
It should be noted that, if directional indications (such as up, down, left, right, front, and back … …) are involved in the embodiment of the present invention, the directional indications are only used to explain the relative positional relationship between the components, the movement situation, and the like in a specific posture (as shown in the drawing), and if the specific posture is changed, the directional indications are changed accordingly.
In addition, if there is a description of "first", "second", etc. in an embodiment of the present invention, the description of "first", "second", etc. is for descriptive purposes only and is not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one such feature. In addition, technical solutions between various embodiments may be combined with each other, but must be realized by a person skilled in the art, and when the technical solutions are contradictory or cannot be realized, such a combination should not be considered to exist, and is not within the protection scope of the present invention.
The invention provides a data Table reconstruction method based on a Universal Table Layout database mode, which is characterized by comprising the following steps of:
s10; the method comprises the steps that a tenant inputs data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records is matched with a tenant ID and a Table ID, the tenant ID is used for identifying the identity of the tenant, and the Table ID is used for identifying the name of the data Table of the current tenant;
s20, dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
s30, dividing the data table of each tenant according to the table ID, wherein external key constraints exist among partial tables in the divided data table;
s40, generating a directed graph between data tables according to the existing foreign key constraint;
s50, removing included paths from all paths in the directed graph, thereby obtaining the directed graph with pure paths;
s60, dividing the data on each pure path in the directed graph into a data table, and reconstructing the database.
Preferably, the method for generating the directed graph in S30 is:
if the data table A refers to the primary key of the data table B as a foreign key, a directional arrow pointing from the table A to the table B is drawn, and directional arrows are added among all the data tables to generate a directed graph among the data tables.
Preferably, the S40 includes:
s401, finding data tables with out degrees of all paths being not 0 in the generated directed graph, and sequentially obtaining the data tables
Taking a data table with the degree of not 0 as a starting point, and finding out all paths;
s402, sequencing all paths in the directed graph in sequence according to the path sizes of the paths;
s403, eliminating included paths with smaller paths from all paths to obtain a directed graph with pure paths.
The invention also discloses a data Table reconstruction device based on the Universal Table Layout database mode, which comprises the following steps:
the data input module is used for a tenant to input data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records is matched with a tenant ID, and the tenant ID is used as an identification of a tenant identity;
the first dividing module is used for dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
the second division module is used for dividing the data table of each tenant according to the table ID, and external key constraints exist among partial tables in the divided data tables;
the generation module is used for generating a directed graph among the data tables according to the existing foreign key constraints;
the removing module is used for removing included paths from all paths in the directed graph so as to obtain the directed graph with pure paths;
and the reconstruction module is used for dividing the data on each pure path in the directed graph into a data table and reconstructing the database.
Preferably, the culling module comprises:
the searching unit is used for finding out data tables with out degrees different from 0 of all paths in the generated directed graph, and finding out all paths by taking the data tables with out degrees different from 0 as starting points in sequence;
the sorting unit is used for sequentially sorting all the paths in the directed graph according to the path sizes of the paths;
and the eliminating unit is used for eliminating included paths with smaller paths from all paths to obtain a directed graph with pure paths.
The invention also discloses a data Table reconstruction system based on the Universal Table Layout database mode, which comprises a server, the Universal Table Layout database and the data Table reconstruction device based on the Universal Table Layout database mode, and is used for realizing the data Table reconstruction method based on the Universal Table Layout database mode.
Since the device for reconstructing a data Table based on the Universal Table Layout database schema of the present invention adopts all technical solutions of all embodiments of the method for reconstructing a data Table based on the Universal Table Layout database schema of the present invention, all beneficial effects brought by the technical solutions of the above embodiments are at least achieved, and are not described herein any more.
The concrete practical operation example is as follows:
(1) according to the tenant ID value of each row of data records of the data Table mt _ data in the Universal Table Layout database mode, the data Table mt _ data is divided into a plurality of data tables, namely, one data Table for each tenant, as shown in FIG. 1.
(2) The data table of each tenant is continuously divided according to the table ID, so that the data table of each tenant is divided into a plurality of tables, and here, we take any tenant as an example to specifically introduce.
(3) Assume that a tenant is divided into 12 data tables, each A, B, C, D, E, F, G, H, I, J, K, L, by table ID. Wherein, there is a foreign key constraint between table A and tables B and E, a foreign key constraint between table C and table B, a foreign key constraint between table D and table C, a foreign key constraint between table F and tables A and G, a foreign key constraint between table G and table H, a foreign key constraint between table H and tables C and D, and a foreign key constraint between table I and table G. The resulting graph is shown in FIG. 2, where each node represents a table of data.
(4) Using all points with out degree not 0 as starting point to search all paths
Taking A as a starting point, the two paths are A, E and A, B respectively, the former path is called I path, and the latter path is called II path.
C is the starting point and one path is C, B, here called path iii.
D is the starting point, and the two paths are D, H, C, B and D, C, B, which are referred to as the former iv path and the latter v path.
F as a starting point, F, A, B and F, G, H, C, B are two paths, and F, A, E are sequentially called a vi path, a vii path, and a viii path.
Fifthly, G is a starting point, one path is G, H, C, B, and the path is called IX.
Sixthly, taking H as a starting point, and taking a path H, C, B as the path X.
And where I is the start point and one path is I, G, H, C, B, here called the path xi.
(5) Then the path sequences are sequenced from long to short according to the path lengths, and then VII, XI, IV, IX, V, VI, VIII, X, I, II and III are carried out in sequence.
(6) Here we assume that there is a result set R to which we want to add the above partial path sequence. The manner of addition is as follows:
firstly, selecting a first path sequence to be added into a result set
Secondly, selecting the following path sequences in sequence and adding the path sequences into the result set
Third, when the following path sequence is added into the result set, it is ensured that the path sequence is not included by the path sequence in the result set
Fourthly, until all the path sequences are checked
(7) Therefore, the final result set R ═ vii, xi, iv, vi, viii.
(8) Therefore, 5 data tables are finally established in a Universal Table Layout database mode and sequentially comprise corresponding data tables VII, XI, IV, VI and VIII.
(9) The data tables involved in each path are stored in a database. Therefore, the pressure of a single database can be reduced, and the condition of cross-database connection operation can be effectively avoided.
The above description is only a preferred embodiment of the present invention, and is not intended to limit the scope of the present invention, and all modifications and equivalents of the present invention, which are made by the contents of the present specification and the accompanying drawings, or directly/indirectly applied to other related technical fields, are included in the scope of the present invention.
Claims (3)
1. A data table reconstruction method of a database mode is characterized by comprising the following steps:
s10; the method comprises the steps that a tenant inputs data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records are matched with a tenant ID and a Table ID, the tenant ID is used for identifying the identity of the tenant, and the Table ID is used for identifying the name of the data Table of the current tenant;
s20, dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
s30, dividing the data table of each tenant according to the table ID, wherein external key constraints exist among partial tables in the divided data table;
s40, generating a directed graph between data tables according to the existing foreign key constraint;
s50, removing included paths from all paths in the directed graph, thereby obtaining the directed graph with pure paths;
s60, dividing data on each clean path in the directed graph into a data table, and reconstructing a database;
the method for generating the directed graph in the step S40 includes:
if the data table A refers to a main key of the data table B as a foreign key, drawing a directed arrow pointing to the table B from the table A, and sequentially adding directed arrows among all the data tables to generate directed graphs among the data tables;
the S50 includes:
s501, finding all data tables with out degrees different from 0 in the generated directed graph, and finding out all paths by taking the data tables as starting points in sequence;
s502, sequencing all paths in the directed graph in sequence according to the path size of the paths;
s503, paths with small included paths are eliminated from all paths, and a directed graph with pure paths is obtained.
2. An apparatus for reconstructing a data table of a database schema, comprising:
the input module is used for a tenant to input data into a data Table mt _ data in a Universal Table Layout database mode, each row of data records is matched with a tenant ID, and the tenant ID is used as an identification of a tenant identity;
the first division module is used for dividing the data table mt _ data into data tables of each tenant corresponding to the tenant ID values one by one;
the second division module is used for dividing the data table of each tenant according to the table ID, and external key constraints exist among partial tables in the divided data tables;
the generation module is used for generating a directed graph among the data tables according to the existing foreign key constraints; the method specifically comprises the following steps: if the data table A refers to a main key of the data table B as a foreign key, drawing a directed arrow pointing to the table B from the table A, and sequentially adding directed arrows among all the data tables to generate directed graphs among the data tables;
the removing module is used for removing included paths from all paths in the directed graph so as to obtain the directed graph with pure paths;
the reconstruction module is used for dividing data on each pure path in the directed graph into a data table and reconstructing a database;
the rejection module comprises:
the searching unit is used for finding data tables with out degrees different from 0 of all paths in the generated directed graph, and finding out all paths by taking the data tables as starting points in sequence;
the sorting unit is used for sequentially sorting all the paths in the directed graph according to the path sizes of the paths;
and the eliminating unit is used for eliminating included paths with smaller paths from all paths to obtain a directed graph with pure paths.
3. A data Table reconstruction system of a database schema, comprising a server and a Universal Table Layout database, characterized by further comprising the data Table reconstruction apparatus of the database schema according to claim 2, for implementing the data Table reconstruction method of the database schema according to claim 1.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810229982.XA CN108536758B (en) | 2018-03-20 | 2018-03-20 | Data table reconstruction method, device and system for database mode |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810229982.XA CN108536758B (en) | 2018-03-20 | 2018-03-20 | Data table reconstruction method, device and system for database mode |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108536758A CN108536758A (en) | 2018-09-14 |
| CN108536758B true CN108536758B (en) | 2021-09-10 |
Family
ID=63484231
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810229982.XA Expired - Fee Related CN108536758B (en) | 2018-03-20 | 2018-03-20 | Data table reconstruction method, device and system for database mode |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108536758B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109635037B (en) * | 2018-11-19 | 2022-12-09 | 中山大学 | Fragmentation storage method and device for relational distributed database |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102655526A (en) * | 2011-11-02 | 2012-09-05 | 李宗诚 | Computing technique foundation of intelligent integrated system for coordinative configuration of Internet user terminals |
| CN103490938A (en) * | 2013-10-15 | 2014-01-01 | 河海大学 | Layering-based cloud service combination failure recovery system and method |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6946715B2 (en) * | 2003-02-19 | 2005-09-20 | Micron Technology, Inc. | CMOS image sensor and method of fabrication |
-
2018
- 2018-03-20 CN CN201810229982.XA patent/CN108536758B/en not_active Expired - Fee Related
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102655526A (en) * | 2011-11-02 | 2012-09-05 | 李宗诚 | Computing technique foundation of intelligent integrated system for coordinative configuration of Internet user terminals |
| CN103490938A (en) * | 2013-10-15 | 2014-01-01 | 河海大学 | Layering-based cloud service combination failure recovery system and method |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108536758A (en) | 2018-09-14 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110032604B (en) | Data storage device, translation device and database access method | |
| US9348641B2 (en) | System and method for performing a transaction in a massively parallel processing database | |
| CN105824957A (en) | Query engine system and query method of distributive memory column-oriented database | |
| CN107113341B (en) | System for high throughput processing of transactions in a distributed relational database management system for data partitioning | |
| JPH07319923A (en) | Method and equipment for processing of parallel database of multiprocessor computer system | |
| CN105405070A (en) | Distributed memory power grid system construction method | |
| US11321394B2 (en) | Graph processing system | |
| CN114218218A (en) | Data processing method, device and equipment based on data warehouse and storage medium | |
| CN108388623A (en) | ER relationships generation method, device, computer equipment and storage medium | |
| CN114090695A (en) | Query optimization method and device for distributed database | |
| CN103678591A (en) | Device and method for automatically executing multi-service receipt statistical treatment | |
| CN108536758B (en) | Data table reconstruction method, device and system for database mode | |
| CN110175202A (en) | The method and system of the outer connection of table for database | |
| Pankowski | Consistency and availability of Data in replicated NoSQL databases | |
| CN110471968A (en) | Dissemination method, device, equipment and the storage medium of ETL task | |
| CN112860680B (en) | Data processing method and system, and data query method and system | |
| CN111259062B (en) | Method and device capable of guaranteeing sequence of statement result set of full-table query of distributed database | |
| CN114817226A (en) | Government data processing method and device | |
| CN108595552A (en) | Data cube dissemination method, device, electronic equipment and storage medium | |
| CN107526573B (en) | Method for processing remote sensing image by adopting parallel pipeline | |
| CN107515867A (en) | The generation method and device that data storage, querying method and the device and a kind of rowKey of a kind of NoSQL databases combine entirely | |
| CN114218114B (en) | Full-automatic test data generation method based on interface flow arrangement | |
| CN115794870A (en) | Query template parameter instantiation method aiming at unitary radix constraint | |
| CN116501735A (en) | Multi-entity report generation method, device, equipment and medium | |
| CN117149866A (en) | Method and device for exporting multi-source heterogeneous data, readable storage medium and terminal |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20210910 |
|
| CF01 | Termination of patent right due to non-payment of annual fee |