CN1201950A - Method for deleting and restoring lines of table in database - Google Patents
Method for deleting and restoring lines of table in database Download PDFInfo
- Publication number
- CN1201950A CN1201950A CN 98107986 CN98107986A CN1201950A CN 1201950 A CN1201950 A CN 1201950A CN 98107986 CN98107986 CN 98107986 CN 98107986 A CN98107986 A CN 98107986A CN 1201950 A CN1201950 A CN 1201950A
- Authority
- CN
- China
- Prior art keywords
- row
- physics
- tree
- blob
- recovery
- 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
A method for deleting row in physical table in database includes such steps as saving the identification information of the physial tables where each rows in the row tree whose root is the row to be deleted axist and the contents of each rows into a new row of recovery table, and deleting the row from the physical table. A method for restoring the row includes such steps as reading the identification information of physical tablet where each rows in the row tree whose root is the row to be restored exist and the contents of each rows from the recovery table and inserting correspondent rows into releative physical tables.
Description
The present invention relates to the database operation method in the computer application field, relate in particular to the method that the row that is stored in the database physics table in the computer memory is deleted and recovered.
In current society, information management system (MIS) has been applied to all trades and professions, and database technology plays a part very important in the mankind's Activities.Present popular large data base management system such as Oracle, Sybase etc.As known in this area, a database is made up of a plurality of physics tables (abbreviating table as), and each physics table is divided into a plurality of row and a plurality of row again.Usually, the relation that row in physics table and the row in other physics tables exist a kind of " coexistence ", with this " coexistence " contextual definition for there being dependence.
When the delegation of deletion in certain physics table, also should be from other physics tables in the lump deletion have other each row that depend on this row.So,, in fact often deleted a huge data group though deleted data line on the surface.Sometimes delete huge data because of the mistake deletion, particularly just find mistake deletion in the past after data maintenance after a while, loss will be heavy.Though the backup of making before utilizing can not keep the modification of being made in the recent period with database recovery to previous level.For the protected data database data, generally provide two kinds of safeguard measures in the database application system now.The firstth, limit user right, domestic consumer can not delete the row in the physics table.The secondth, going before the deletion, whether the prompting user goes deletion action.Though having reduced mistake to a certain extent, these two kinds of measures delete the loss that causes,, the incident of deletion and so on still happens occasionally by mistake, and when utilizing backup to recover, still has the shortcoming that can not keep recent modification.
First purpose of the present invention provides a kind of delet method to row in the database physics table, utilizes this method automatically to delete the row that all existence depend on the row of deleting from each physics table of database, avoids junk data.
Second purpose of the present invention provides a kind of method that the row that utilizes the said method deletion is recovered, utilize this method can with to some extent the row of deletion automatically restore in the database in the corresponding physics table, guarantee the modification of database being made after the deletion simultaneously.
For realizing above-mentioned first purpose, the invention provides a kind of method that deletion is gone from the physics table of database, it is characterized in that may further comprise the steps:
Preserve step, will be saved in the content of the identification information of the physics table at the every capable place in the row tree of waiting to delete the behavior root and every row a new recovery table of recovery table capable in;
The deletion step, in preserving step, after the content of the delegation in having preserved described row tree, the described delegation of deletion from the physics table at described delegation place.
For realizing above-mentioned second purpose, the invention provides the method in a kind of physics table that will return to database according to the row of method of the present invention deletion, it is characterized in that may further comprise the steps:
Recovering step, during the pairing recovery table of row tree to wait to recover the behavior root from the recovery table is capable, take out content corresponding as the identification information of the physics table at the every capable place in the row tree and the content of every row, and will go accordingly and be increased in the physics table that identification information identifies;
The deletion step in recovering step, after all the elements of taking out recovery table row, is deleted this and is reclaimed the table row from reclaim table.
According to row deletion of the present invention and restoration methods, can be widely used in development, the exploitation of information management system, data base management system (DBMS) and database foreground developing instrument, the security that improves database data in the storer.
To the detailed description of embodiment of the present invention, the other objects and features of the invention will be more obvious in conjunction with the drawings.
Fig. 1 is to be that example illustrates the synoptic diagram that concerns between each physics table with personnel's archive database;
Fig. 2 is the process flow diagram of capable delet method of the present invention;
Fig. 3 is the process flow diagram of capable restoration methods of the present invention.
Preferred forms of the present invention is described with reference to the accompanying drawings.
Fig. 1 is to be that example illustrates the synoptic diagram that concerns between each physics table with personnel's archive database.In this database, six physics tables are arranged, wherein
1 is public organization's table, and name is called Organ, and every row comprises row such as record the code Id of group, community name Nm.2 is the unit table, and name is called Dpt, and every row comprises row such as unit encoding Id, organization Nm.3 is the resume table, and name is called Resume, and every row comprises row such as unit encoding RDptID, identification card number RPerId, zero-time.4 for participating in public organization's table, and name is called JoinOrgan, and every row comprises row such as unit encoding JDptID, identification card number JPerId, the coding OrgId of group.5 is personnel's basic condition table, and name is called Person, and every row comprises row such as unit encoding DptID, identification card number PerId, name Nm, sex Sex, photo Photo.6 are spouse's table, and name is called Spouse.Every row comprises row such as unit encoding SDptID, identification card number SPerId, spouse's name.
With personnel's basic condition table 5 and resume table 3 is that the example instruction card exists dependence between capable.For everyone, in personnel's basic condition table 5, has only a basic condition row; And in resume table 3, can have a plurality of resumes capable.Delegation all should be corresponding to the delegation in personnel's basic condition table 5 arbitrarily in the resume table 3; Otherwise this row in the resume table 3 is a junk data, shows that the data association integrity is destroyed.In this embodiment, to depend on personnel's basic condition table capable for the capable existence of resume table.
Between the database physics table, exist in theory one to one, one-to-many and three kinds of relations of multi-to-multi.Because three kinds of present database models are not all supported many-to-many relationship, many-to-many relationship wherein all is broken down into many-one relationship, so have only one-one relationship and many-one relationship between the physics table at present.Among Fig. 1, between unit table 2 and the personnel's basic condition table 5, between personnel's basic condition table 5 and the resume table 3, between personnel's basic condition table 5 and the participation public organization table 4, and all be many-one relationship between public organization's table 1 and the participation public organization table 4, and be one-one relationship between personnel's basic condition table 5 and the spouse's table 6.
In the entity relationship modeling technique, be widely used in database design already.When utilizing this technical design physics table, two tables of one-one relationship are mutually the external key of the other side's major key as oneself; Two tables of many-one relationship, " multiterminal " table is the external key of the major key of " an end table " as oneself.For example, the major key of unit table 2 is unit encoding, so in personnel's basic condition table 5, have the unit encoding row.In addition, the major key of personnel's basic condition table 5 is " unit encoding " and " identification card number ", all has unit encoding row and identification card number row in public organization's table 4, resume table 3 and the spouse's table 6 so participate in.Among Fig. 1, indicate the major key of the row of underscore in each physics table as this physics table.
The notion of row tree and table tree is described below.
The delegation of certain physics table, be distributed in that existence in other tables depends on that all row of this row form be tree-shaped structure usually, be called the row tree.This row is called the root of this row tree.In personnel's archive database shown in Figure 1, the row tree of personnel's basic condition table 5 be by the delegation in personnel's basic condition table 5 (for the root of row tree) and resume table 3, participate in public organization's table 4 and the spouse's table 6 all with this individual accordingly the two-stage that constitutes of row (child node of row tree) set.The row tree of unit table 2 is by the delegation in the unit table 2 (be the root of row tree, write down the unit encoding, organization of certain unit etc.) and personnel's basic condition table 5, resume table 3, participates in public organization's table 4 and the spouse's table 6 all and this unit three grades of trees of row (child node of row tree) formation accordingly.Resume table 3 does not have to exist the table that depends on it, so include only a root in its row tree.
Therefore, the row tree is the logical data unit with information integrity, and its existence is the instantiation that concerns between the physics table.
For in a row tree, also exist the relation that is tree-shaped usually between the physics, this tree is called the table tree.
As above described in the face of the entity relationship modeling technique, in the table tree, the parent table of one-one relationship and sublist, sublist have the major key of parent table with the external key form; The parent table of many-one relationship and sublist, parent table are always at " end ", and the sublist of multiterminal also has the major key of parent table with the form of external key.The rest may be inferred, and other tables in the table tree (successor node of root table is also referred to as the successor list of root table) can have the major key of root table.So, utilize the major key of root table can discern whole row tree easily, root table major key has also become the unique identification of the capable tree of root table, is called row tree major key.
Therefore, in database shown in Figure 1, the capable tree of personnel's basic condition table 5 can be determined uniquely with " unit encoding " and " identification card number ".The capable tree of unit table 2 can be determined uniquely with " unit encoding ".
After the notion of having described capable tree as mentioned above, capable delet method of the present invention and row restoration methods have just been understood easily.
Fig. 2 is the process flow diagram of capable delet method of the present invention.In step 21, determine row tree with the behavior root that will delete.Step 22 finds the table that stores these row tree data but do not compile as yet.Step 23 is pooled to the identification information of this table and the data that wherein belong to this row tree in the BLOB type variable.Here, BLOB represents binary large object, is a kind of standard data type.This data type is used to store very large data, as image, and big text etc.Among the different DBMS, BLOB type data there is different disposal routes.In step 24, judge whether to store in addition these row tree data but the table that do not compiled.If the judged result of step 24 is a "Yes", then process forwards step 22 to; Otherwise proceed to step 25.Step 25, with the value of above-mentioned BLOB type variable as the row content, newline of increase in reclaiming table (will be explained below).Step 26 is deleted all row in the row tree from corresponding table.Need to prove at this,, also can after compiling, carry out immediately for the deletion of delegation.
Fig. 3 is the process flow diagram of capable restoration methods of the present invention.In step 31, determine in the recovery table and wait to recover the corresponding delegation of row, will receive in this row in the BLOB variable.Step 32 intercepts out one and does not recover the content of row and the identification information of respective table as yet from this BLOB variable.Step 33 is determined a table according to the identification information of table, takes out the data of this table again from the BLOB variable, is increased in this table.In step 34, judge whether the content that is not resumed is as yet arranged in the BLOB variable.If the judged result of step 34 is a "Yes", then process forwards step 32 to; Otherwise proceed to step 35.The corresponding delegation of row is deleted and waited to recover to step 35 from the recovery table.Here need to prove that step 35 also can be carried out immediately after step 31.
The recovery table of above mentioning is a physics table that is increased in order to preserve deleted row tree in the former database, and each row wherein is used for preserving the content of all row of row tree and the identification information of respective table.When recovering, can from reclaim table, taking out corresponding delegation,, will go accordingly and return in the corresponding table according to the identification information of the table of being preserved to row.
The recovery table can have structure as shown in table 1.
Table 1
| The deletion date | Row tree summary | Text line | Binary column |
That is, the every row in the recovery table can have four row: deletion date, row tree summary, text line and binary column.
The deletion date is the date type, the date of the capable tree of record deletion.
Row tree summary is a character type, briefly describes the content of deleted row tree, and it is for the ease of understanding row tree content that these row are set.
Text line is the BLOB type, is used for preserving the value of type column such as deleted each decimal of going of row tree, integer, lint-long integer, character, date, time and the identification information of respective table.Though the value different in kind of these row can be converted to character string with them of equal valuely.Above-mentioned train value character string is separated and connected into a BLOB value with predetermined train value separator (such as tab Tab).Can use the identification information of table name as respective table.So the structure of text line can be expressed as:
{ table name { train value separator [train value character string] } table end-data symbol }
Wherein { } expression can repeat, and [] expression can be omitted, and is sky.
Binary column is the BLOB type, is used for preserving the value of large data row such as deleted each sound of going of row tree, image.For these data, can not separate with separator simply.Can separate and connect into a BLOB value with " { size [data] } " structure, wherein, " size " be lint-long integer, and " data " are connected binary data, and { } represented and can be repeated, and [] represented and can be omitted, be sky.During data in recovering binary column, can discern corresponding table by means of the table identification information of being preserved in the text line.
If adopt the recovery list structure shown in the table 1, but delet method slightly modified then shown in Figure 2.Step 23 is pooled to the identification information of table and the data of the text line of current line in the one BLOB type variable, and the data of the binary column of current line are pooled in the 2nd BLOB type variable.Step 25 utilizes the value of a BLOB type variable that the capable text line of recovery table is carried out assignment, utilizes the value of the 2nd BLOB type variable that the capable binary column of recovery table is carried out assignment, and other each row are carried out correspondingly assignment.Correspondingly, also slightly modified of restoration methods shown in Figure 3.Step 31 receives into a BLOB type variable in the text line that determined recovery table is capable, will receive into the 2nd BLOB type variable in the binary column.Step 32, the identification information of intercepting table from a BLOB type variable.Step 33, the data of getting table from a BLOB type variable are carried out assignment to corresponding text line, and the data of getting table from the 2nd BLOB type variable are carried out assignment to corresponding binary column.Step 34 judges in the BLOB type variable whether the content that is not resumed is arranged.
According to the above, those skilled in the art in this area can realize easily according to capable delet method of the present invention and restoration methods.
In addition, in realizing capable delet method of the present invention and restoration methods, for the ease of determining the dependence (be in the different table having dependence between the row) between the physics table, obtain identification information, the text line in the differentiation delegation and the binary column etc. of table, can adopt the row tree description list of structure as shown in table 2.
Table 2
| Table name | The major key name | Prompting | The text line name | The binary column name | Follow-up table name |
In the row tree description list, every row is used to describe a physics table.Every row has six row: table name, major key name, prompting, text line, binary column, successor list.They are character type.
Table name: the table name that indicates described physics table.
Major key name: the major key name that indicates described physics table.
Prompting: to the brief description of institute's description list, the value of these row will be used to form the row tree summary in the recovery table.
The text line name: indicate that type is the row name of type column such as decimal, integer, lint-long integer, character, date, time in institute's description list, the value of respective column will be used to form the text line in the recovery table.
The binary column name: indicate the row name of large data row such as sound in institute's description list, image, the value of respective column will be used to form the binary column in the recovery table.
Follow-up table name: indicate institute's description list successor list title and corresponding to the title of the row of root table major key.For example, can adopt such form: follow-up table name: [successor list major key], the successor list major key }; .Last "; " can omit.
With personnel's basic condition table 5 shown in Figure 1 is example, and the values that then are used to describe six capable row of the row tree description list of this table are respectively:
Table name: Person;
Major key name: DptID, PerId;
Prompting: DptID, Nm (being unit encoding and name);
Text line name: DptID, PerId, Nm, Sex;
Binary column name: Photo;
Follow-up table name: Resume:RDptID, RPerId; Spouse:SDptID, SPerId; JoinOrgan:JDptID, JPerId
Certainly the row tree description list of structure is not essential as mentioned above, can adopt any other mode so that determine in the row tree the dependence that exists between each row yet.
Though describe preferred forms of the present invention with reference to the accompanying drawings, should be understood that, for those skilled in the art in this area, under the situation that does not deviate from the spirit and scope of the invention, can carry out various modifications and changes to above-mentioned embodiment.Therefore, scope of the present invention only is defined by the claims.
Claims (6)
1. from the physics table of database, delete the method for row, it is characterized in that may further comprise the steps:
Preserve step, will be saved in the content of the identification information of the physics table at the every capable place in the row tree of waiting to delete the behavior root and described every row a new recovery table of recovery table capable in;
The deletion step, in described preservation step, after the content of the delegation in having preserved described row tree, the described delegation of deletion from the physics table at described delegation place.
2. according to the method for claim 1, it is characterized in that described preservation step may further comprise the steps:
For with each row in the row tree of waiting to delete the behavior root, the content of the text line of the identification information of the physics table at every capable place and every row is appended in the BLOB type variable, the content of the binary column of every row is appended in the 2nd BLOB type variable,
It is capable to increase a new recovery table in described recovery table, the value that the described new capable text line of recovery table is used to store a described BLOB type variable, the value that the described new capable binary column of recovery table is used to store described the 2nd BLOB type variable.
3. according to the method for claim 1, it is characterized in that described deletion step is to carry out after having preserved described all contents of going of going in setting.
4. will return to the method in the physics table of database according to the row of the method for claim 1 deletion, and it is characterized in that may further comprise the steps:
Recovering step, during the pairing recovery table of row tree to wait to recover the behavior root from the recovery table is capable, take out content corresponding as the identification information of the physics table at the every capable place in the row tree and the content of described every row, and will go accordingly and be increased in the physics table that described identification information identifies;
The deletion step in described recovering step, after all the elements of taking out a described recovery table row, is deleted described one and is reclaimed the table row from reclaim table.
5. according to the method for claim 4, it is characterized in that described recovering step may further comprise the steps:
The pairing recovery table of row tree that takes out from the recovery table to wait to recover the behavior root is capable;
The value of utilizing the capable text line of a described recovery table is to a BLOB variable assignments;
The value of utilizing the capable binary column of a described recovery table is to the 2nd BLOB variable assignments;
Repeating following steps, is empty up to a described BLOB variable:
The content that from a described BLOB variable, intercepts the identification information of a physics table and will recover the text line of row,
Intercepting will recover the content of the binary column of row from described the 2nd BLOB variable,
In the physics table that described identification information identified, increase the described row that will recover.
6. according to the method for claim 4, it is characterized in that described deletion step in the end carries out.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN 98107986 CN1201950A (en) | 1998-05-08 | 1998-05-08 | Method for deleting and restoring lines of table in database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN 98107986 CN1201950A (en) | 1998-05-08 | 1998-05-08 | Method for deleting and restoring lines of table in database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN1201950A true CN1201950A (en) | 1998-12-16 |
Family
ID=5219484
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN 98107986 Pending CN1201950A (en) | 1998-05-08 | 1998-05-08 | Method for deleting and restoring lines of table in database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN1201950A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6684225B1 (en) | 1999-07-29 | 2004-01-27 | International Business Machines Corporation | Dropped database table recovery |
| CN1332343C (en) * | 2004-10-09 | 2007-08-15 | 武汉大学 | Method for recovering and reutilizing object marking symbol in data base |
| CN100499746C (en) * | 2003-12-30 | 2009-06-10 | 三星Techwin株式会社 | Digital photographing apparatus and file managing method thereof |
| US8521789B2 (en) | 2003-05-27 | 2013-08-27 | Oracle International Corporation | Undrop objects and dependent objects in a database system |
-
1998
- 1998-05-08 CN CN 98107986 patent/CN1201950A/en active Pending
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6684225B1 (en) | 1999-07-29 | 2004-01-27 | International Business Machines Corporation | Dropped database table recovery |
| US7188124B2 (en) | 1999-07-29 | 2007-03-06 | International Business Machines Corporation | Method, system, computer product for recovering dropped database table specifying one or more table spaces, recovering the table space being restored using the dropped table history data structure |
| US8521789B2 (en) | 2003-05-27 | 2013-08-27 | Oracle International Corporation | Undrop objects and dependent objects in a database system |
| CN1791873B (en) * | 2003-05-27 | 2014-01-15 | 甲骨文国际公司 | Undrop objects and dependent objects in a database system |
| CN100499746C (en) * | 2003-12-30 | 2009-06-10 | 三星Techwin株式会社 | Digital photographing apparatus and file managing method thereof |
| CN1332343C (en) * | 2004-10-09 | 2007-08-15 | 武汉大学 | Method for recovering and reutilizing object marking symbol in data base |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US7464247B2 (en) | System and method for updating data in a distributed column chunk data store | |
| US7546321B2 (en) | System and method for recovery from failure of a storage server in a distributed column chunk data store | |
| US7587569B2 (en) | System and method for removing a storage server in a distributed column chunk data store | |
| US7447839B2 (en) | System for a distributed column chunk data store | |
| US8843454B2 (en) | Elimination of duplicate objects in storage clusters | |
| US8214388B2 (en) | System and method for adding a storage server in a distributed column chunk data store | |
| US7457935B2 (en) | Method for a distributed column chunk data store | |
| CN1622087A (en) | Managing file system versions | |
| Thurachon et al. | Incremental association rule mining with a fast incremental updating frequent pattern growth algorithm | |
| CN109299086B (en) | Optimal sort key compression and index reconstruction | |
| KR101085735B1 (en) | System and method for identifying and storing changes made to a table | |
| US10496612B2 (en) | Method for reliable and efficient filesystem metadata conversion | |
| CN103279532A (en) | Filtering system and filtering method for removing duplication of elements of multiple sets and identifying belonged sets | |
| US20180150536A1 (en) | Instance-based distributed data recovery method and apparatus | |
| CN1201950A (en) | Method for deleting and restoring lines of table in database | |
| US7756818B1 (en) | Database table recovery system | |
| CN107291574B (en) | Backup data recovery primary key generation method based on interpretation system | |
| CN105677514A (en) | Method for reconstructing disaster data file directory structure | |
| CN116910051B (en) | Data processing method, device, electronic equipment and computer readable storage medium | |
| CN107145601A (en) | A kind of efficient adduction relationship finds algorithm | |
| US8990173B2 (en) | Method and apparatus for selecting an optimal delete-safe compression method on list of delta encoded integers | |
| CN116266173A (en) | Key value storage method and system based on log structure merging tree | |
| CN116627716A (en) | Data persistence processing method and device, electronic equipment and storage medium | |
| CN112506708A (en) | Memory image file analysis method for Android intelligent terminal | |
| Chan | Fast Text Access Methods for Optical and Large Magnetic Disks: Designs and Performance Comparison. |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C06 | Publication | ||
| PB01 | Publication | ||
| C02 | Deemed withdrawal of patent application after publication (patent law 2001) | ||
| WD01 | Invention patent application deemed withdrawn after publication |