CN108563694A - Method, apparatus, computer device and storage medium for performing SQ L for logical deletion - Google Patents
Method, apparatus, computer device and storage medium for performing SQ L for logical deletion Download PDFInfo
- Publication number
- CN108563694A CN108563694A CN201810225650.4A CN201810225650A CN108563694A CN 108563694 A CN108563694 A CN 108563694A CN 201810225650 A CN201810225650 A CN 201810225650A CN 108563694 A CN108563694 A CN 108563694A
- Authority
- CN
- China
- Prior art keywords
- logic
- sql
- deleted
- sql statement
- data
- 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.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 48
- 238000012217 deletion Methods 0.000 title abstract description 15
- 230000037430 deletion Effects 0.000 title abstract description 15
- 239000003550 marker Substances 0.000 claims description 41
- 230000009471 action Effects 0.000 claims description 37
- 238000013507 mapping Methods 0.000 claims description 16
- 230000004048 modification Effects 0.000 claims description 16
- 238000012986 modification Methods 0.000 claims description 16
- 238000004590 computer program Methods 0.000 claims description 15
- 238000004364 calculation method Methods 0.000 claims 1
- 238000010586 diagram Methods 0.000 description 5
- 230000006870 function Effects 0.000 description 4
- 238000012360 testing method Methods 0.000 description 4
- 230000006399 behavior Effects 0.000 description 3
- 238000013506 data mapping Methods 0.000 description 3
- 230000008859 change Effects 0.000 description 2
- 238000004458 analytical method Methods 0.000 description 1
- 230000008901 benefit Effects 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 235000013399 edible fruits Nutrition 0.000 description 1
- 238000005538 encapsulation Methods 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000002708 enhancing effect Effects 0.000 description 1
- 230000007717 exclusion Effects 0.000 description 1
- 239000004744 fabric Substances 0.000 description 1
- 238000007689 inspection Methods 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 230000002085 persistent effect Effects 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 238000004088 simulation Methods 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 230000001360 synchronised effect Effects 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Stored Programmes (AREA)
Abstract
The invention relates to a SQ L execution method, a device, computer equipment and a storage medium for logic deletion, which are applied to the technical field of databases.
Description
Technical field
The present invention relates to database technical field, SQL (the Structured Query deleted more particularly to logic-based
Language, structured query language, abbreviation SQL) execute method, apparatus, computer equipment and storage medium.
Background technology
Logic deletion refers to that not executing delete operations on the database really deletes data, but data
Certain field is set to a label.Meanwhile being arranged one for indicating what logic was deleted for the data inside database table
Field (such as is_delete) indicates that data are deleted by logic if the field is 1, and data are indicated if the field is 0
It is not deleted by logic.
In the implementation of the present invention, following problem exists in the prior art in inventor：It is patrolled by user in data
It collects after deleting, uses traditional SQL statement, it may be difficult to exclude the data, SQL implementing results is caused to cannot be satisfied requirement.
Invention content
Based on this, it is necessary to for the problem for the SQL implementing result inaccuracy that existing way deletes logic, provide one kind
Method, apparatus, computer equipment and storage medium are executed to the SQL that logic is deleted.
Scheme provided in an embodiment of the present invention includes：
A kind of SQL execution methods that logic is deleted, including：
Pending SQL statement is obtained, identifies the action type of the pending SQL statement；
When the action type is to delete, update or inquire, according to preset logic deleted marker in entity class, know
Logic in the tables of data that not pending SQL statement is directed to deletes field；There is mapping between the entity class and the tables of data
Relationship；
Field is deleted according to the logic and generates corresponding SQL operating conditions, according to SQL operating conditions update
Pending SQL statement executes the SQL statement that update obtains.
In one of the embodiments, according to preset logic deleted marker in entity class, pending SQL statement needle is identified
To tables of data in logic delete field the step of include：
Determine the corresponding entity class of the pending SQL statement；
Preset logic deleted marker in the entity class is detected, the logic in tables of data is identified according to logic deleted marker
Delete field.
Further include in one of the embodiments,：
Do not include preset logic deleted marker in the entity class if detecting, executes the pending SQL statement；
And/or
After the step of determining the action type of the pending SQL statement, further include：
When the action type of pending SQL statement is newly-increased action type, the pending SQL statement is executed.
The step of determining the corresponding entity class of the pending SQL statement in one of the embodiments, include：
The pending SQL statement is parsed according to preset SQL resolvers, obtains the pending SQL statement
Corresponding syntax tree；The FROM nodes of the syntax tree and/or the leaf node of FROM nodes are traversed, determines pending SQL languages
The tables of data that sentence is directed to；
According to the mapping relations of entity class and tables of data, the corresponding entity class of the tables of data is obtained, is determined as described wait for
Execute the corresponding entity class of SQL statement.
Identify that the logic in the tables of data deletes the step of field according to logic deleted marker in one of the embodiments,
Suddenly include：
The attribute pointed by logic deleted marker in entity class is determined, according to field in attribute in entity class and tables of data
Mapping relations obtain the corresponding field of the attribute, and field is deleted as logic.
Field is deleted according to the logic in one of the embodiments, and generates corresponding SQL operating conditions, according to described
SQL operating conditions update the pending SQL statement the step of include：
If the action type is update or inquiry, the first operating condition of X.is_delete=K1 is generated, in institute
It states and increases by first operating condition in the where attributes of pending SQL statement；
If the action type is to delete, the second operating condition of X.is_delete=K2 is generated, waits holding according to described
Row SQL statement creates the SQL statement of a updating type, in the set attributes of the SQL statement of the updating type described in increase
Second operating condition；
Wherein, X indicates that the data item in the tables of data that pending SQL statement is directed toward, is_delete indicate logic cancel (CANCL)
Section, K1, K2 indicate preset logical value, as X.is_delete=K1, indicate that data X is not deleted by logic, work as X.is_
When delete=K2, indicate that data X is deleted by logic.
Further include in one of the embodiments,：
The corresponding entity class of tables of data is created, the attribute that setting is deleted for indicating logic in the entity class, for this
Corresponding logic deleted marker is arranged in attribute.
The attribute that setting is deleted for indicating logic in the entity class in one of the embodiments, for the attribute
The step of corresponding logic deleted marker is arranged include：
Attribute that Boolean types are arranged in the entity class, being deleted for indicating logic, is arranged logic for the category
Deleted marker@LogicDelete.
A kind of SQL executive devices that logic is deleted, including：
Type identification module identifies the action type of the pending SQL statement for obtaining pending SQL statement；
Field identification module, for according to preset logic deleted marker in entity class, identifying that pending SQL statement is directed to
Tables of data in logic delete field；There are mapping relations between the entity class and the tables of data；
And modification execution module, corresponding SQL operating conditions are generated for deleting field according to the logic, according to
The SQL operating conditions update the pending SQL statement, execute the SQL statement that update obtains.
The field identification module includes in one of the embodiments,：
Class recognition unit, for when the action type is to delete, update or inquire, determining the pending SQL
The corresponding entity class of sentence；
Field recognition unit, for detecting preset logic deleted marker in the entity class, according to logic deleted marker
Identify that the logic in tables of data deletes field.
Further include in one of the embodiments,：
First execution module executes institute if not including preset logic deleted marker in the entity class for detecting
State pending SQL statement；
And/or
Second execution module, for when it is newly-increased action type to determine the action type of the pending SQL statement,
Execute the pending SQL statement.
The class recognition unit includes in one of the embodiments,：
Tables of data identifies subelement, for being parsed to the pending SQL statement according to preset SQL resolvers,
Obtain the corresponding syntax tree of the pending SQL statement；Traverse the FROM nodes of the syntax tree and/or the leaf of FROM nodes
Node determines the tables of data that pending SQL statement is directed to；
Class identifies subelement, for the mapping relations according to entity class and tables of data, obtains the corresponding reality of the tables of data
Body class is determined as the corresponding entity class of the pending SQL statement.
The field recognition unit includes in one of the embodiments,：
Field determination subelement, for determining the attribute in entity class pointed by logic deleted marker, according in entity class
The mapping relations of attribute and field in tables of data, obtain the corresponding field of the attribute, and field is deleted as logic.
The modification execution module includes in one of the embodiments,：
First modification unit generates X.is_delete=K1's if being update or inquiry for the action type
First operating condition increases by first operating condition in the where attributes of the pending SQL statement；
Second modification unit generates the second operation of X.is_delete=K2 if being to delete for the action type
Condition creates the SQL statement of a updating type according to the pending SQL statement, in the SQL statement of the updating type
Increase by second operating condition in set attributes；
Wherein, X indicates that the data item in the tables of data that pending SQL statement is directed toward, is_delete indicate logic cancel (CANCL)
Section, K1, K2 indicate preset logical value, as X.is_delete=K1, indicate that data X is not deleted by logic, work as X.is_
When delete=K2, indicate that data X is deleted by logic.
The above-mentioned SQL deleted logic executes method and device, after getting pending SQL statement, described in identification
The action type of pending SQL statement；When the action type is to delete, update or inquire, preset according in entity class
Logic deleted marker identification tables of data in logic delete field；Field, which is deleted, according to the logic generates corresponding SQL behaviour
Make condition, pending SQL statement is updated according to the SQL operating conditions of generation, and then execute the SQL statement that update obtains.Thus
It realizes and pending SQL is converted automatically, adapt to all SQL statements.User is when editing SQL statement, nothing
It needs whether the data in focused data library are deleted by logic, by conventional SQL statement, can realize the data deleted logic
SQL operations, the accuracy for the SQL operating results for not only simplifying the SQL operation difficulties of user, and improving.
A kind of computer equipment, including memory, processor and be stored on the memory and can be in the processing
The computer program run on device, the processor realize such as the above-mentioned SQL deleted logic when executing the computer program
Execution method.
Above computer equipment is realized by the computer program run on the processor through conventional SQL languages
The SQL behaviour that sentence can realize the SQL operations for the data deleted logic, not only simplify the SQL operation difficulties of user, and improve
Make the accuracy of result.
A kind of computer storage media, is stored thereon with computer program, is realized when which is executed by processor as above
It states and method is executed to the SQL that logic is deleted.
Above computer storage medium is realized by conventional SQL statement, just by the computer program of its storage
The SQL that the data deleted logic can be achieved is operated, and not only simplifies the SQL operation difficulties of user, and the SQL operating results improved
Accuracy.
Description of the drawings
Fig. 1 is the applied environment figure for executing method in one embodiment to the SQL that logic is deleted；
Fig. 2 is that the SQL of an embodiment deleted logic executes the schematic flow chart of method；
Fig. 3 is the schematic diagram of the corresponding syntax tree of query SQL sentence of an embodiment；
Fig. 4 is the schematic diagram for deleting the corresponding syntax tree of SQL statement of an embodiment；
Fig. 5 is that the SQL of another embodiment deleted logic executes the schematic flow chart of method
Fig. 6 is the schematic diagram for the SQL executive devices of an embodiment deleted logic.
Specific implementation mode
In order to make the purpose , technical scheme and advantage of the present invention be clearer, with reference to the accompanying drawings and embodiments, right
The present invention is further elaborated.It should be appreciated that the specific embodiments described herein are merely illustrative of the present invention, and
It is not used in the restriction present invention.
Referenced herein " embodiment " is it is meant that a particular feature, structure, or characteristic described can wrap in conjunction with the embodiments
It is contained at least one embodiment of the application.Each position in the description occur the phrase might not each mean it is identical
Embodiment, nor the independent or alternative embodiment with other embodiments mutual exclusion.Those skilled in the art explicitly and
Implicitly understand, embodiment described herein can be combined with other embodiments.
Structured query language (abbreviation SQL) is a kind of important relational database operation language, for access data with
And inquiry, update and administrative relationships database；It allows user to work in high level data structure.It does not require specified couple of user
The deposit method of data does not need user yet and understands specific data location mode, so with entirely different fabric
Disparate databases system can use identical structured query language to input the interface with management as data.Structuring is looked into
Asking language statement can be nested, this makes it have the function of great flexibility and powerful.
The SQL provided by the present application deleted logic executes method, can be applied to computer equipment as shown in Figure 1
In.The computer equipment can be terminal, include but not limited to be various personal computers, it is laptop, smart mobile phone, flat
Plate computer and portable wearable device.
In one embodiment, the internal structure chart of the computer equipment can be as shown in Figure 1.The computer equipment includes logical
Cross processor, memory, display screen and the input unit of system bus connection.Wherein, the processor of the computer equipment is used for
Calculating and control ability are provided.The memory of the computer equipment includes non-volatile memory medium, built-in storage.This is non-volatile
Property storage medium is stored with operating system and computer program.The built-in storage is the operating system in non-volatile memory medium
Operation with computer program provides environment.A kind of logic deleted when the computer program is executed by processor with realizing
SQL executes method.The display screen of the computer equipment can be liquid crystal display or electric ink display screen, which sets
Standby input unit can be the touch layer covered on display screen, can also be the button being arranged on computer equipment shell, rail
Mark ball or Trackpad can also be external keyboard, Trackpad or mouse etc..
It will be understood by those skilled in the art that structure shown in Fig. 1, is only tied with the relevant part of application scheme
The block diagram of structure does not constitute the restriction for the computer equipment being applied thereon to application scheme, specific computer equipment
May include either combining certain components than more or fewer components as shown in the figure or being arranged with different components.
In one embodiment, as shown in Fig. 2, providing a kind of SQL execution methods deleted logic, including following step
Suddenly：
S11 obtains pending SQL statement, identifies the action type of the pending SQL statement.
The type of common SQL statement includes：Query type, addition type, modification type and deletion type, wherein inquiry
The corresponding sentence of type is also referred to as data query language (DQL:Data Query Language), addition type, modification type and
It deletes the corresponding sentence of type and is also referred to as data manipulation language (DML：Data Manipulation Language).
For query type, sentence is also referred to as " data retrieval sentence ", to obtain data from tables of data, determines number
According to how providing in application program.Reserved word SELECT is verb with the most use, other common reserved words include WHERE,
ORDER BY, GROUP BY and HAVING etc..These reserved words are often used together with other kinds of SQL statement.Add class
It is respectively INSERT, UPDATE and DELETE that type, modification type sentence corresponding with type is deleted, which include verb, they are used respectively
In addition, modification and delete the record in tables of data.
S12 deletes mark when the action type is to delete, update or inquire according to preset logic in entity class
Will identifies that the logic in the tables of data that pending SQL statement is directed to deletes field；Have between the entity class and the tables of data
Mapping relations.
The entity class is corresponding with the tables of data in database, persistent layer (the namely data access based on current mainstream
Layer) frame, built a tables of data in database, then needed to create a corresponding entity inside code according to specification
Class, and the attribute inside entity class has mapped the field of tables of data.
The usual targeted object of SQL statement is the data item in tables of data, such as：SELECT name,country
FROM Websites, the SQL statement refer to choosing " name " and " country " data item from " Websites " table, i.e., should
SQL statement is directed to tables of data " Websites ".Since there are mapping relations between tables of data and entity class, as a result, each
SQL statement has its corresponding entity class.
In one embodiment, the realization of the step includes：When the action type is to delete, update or inquire, really
Determine the corresponding entity class of the pending SQL statement.Preset logic deleted marker in the entity class is detected, is deleted according to logic
Except the logic in Logo identification data table deletes field.
Wherein, the logic deleted marker is the procedure identification that can be performed SQL, is equivalent to being used for table in entity class
Show that logic deletes the note of the attribute of state.Since the attribute of entity class and the field of tables of data are there are mapping relations, by
This may recognize that the field for deleting information in tables of data for recording logic.
S13 deletes field according to the logic and generates corresponding SQL operating conditions, updated according to the SQL operating conditions
The pending SQL statement executes the SQL statement that update obtains.
In one embodiment, it is logical value that logic, which deletes the corresponding value of field, deletes the SQL that field generates according to logic and grasps
It includes two classes to make condition, and one kind is the condition that corresponding data item is deleted by logic, and one kind is that corresponding data Xiang Wei is deleted by logic
Condition.Pending SQL statement is changed according to the SQL operating conditions of generation, is the increase pair in original pending SQL statement
The logic of contiguous items deletes the considerations of information, by executing updated SQL statement, can obtain more accurate data knot
Fruit.
It is understood that it can also be other data types that logic, which deletes the corresponding value of field,.
Method is executed by the SQL of above-described embodiment, user is when editing SQL statement, without in focused data library
Data whether deleted by logic, by conventional SQL statement, can realize the SQL operations for the data deleted logic, both letter
The SQL operation difficulties of user, and the accuracy of the SQL operating results improved are changed.
The SQL execution method of the application further includes in one of the embodiments,：The corresponding entity class of tables of data is created,
The attribute that setting is deleted for indicating logic in the entity class, and corresponding logic deleted marker is set for the attribute.
Pass through logic deleted marker so that program can effectively identify that the logic that information is deleted for recording mathematical logic is deleted
Except field, pending SQL statement is changed based on this.It should be understood that refering to what is shown in Fig. 5, in one embodiment, if inspection
It measures and does not include preset logic deleted marker in the entity class, execute the pending SQL statement.In such cases, no
Pending SQL statement is converted again, and directly executes the pending SQL statement got, do not execute above-mentioned steps S12 with
And step S13.
Because if not including preset logic deleted marker in entity class, program will be unable to effectively identify for recording number
The logic deletion field that information is deleted according to logic executes original pending SQL statement, can prevent logic from deleting in the case
Except field identification error leads to the deviation of SQL implementing results.
In one embodiment, the SQL execution method of the application further includes：In the behaviour for determining the pending SQL statement
After the step of making type, if the action type of pending SQL statement is newly-increased action type, execute described pending
SQL statement.I.e. in this case, no longer pending SQL statement is converted, and directly execute get it is pending
SQL statement does not execute above-mentioned steps S12 and step S13.
Since to delete state unrelated for the logic of newly-increased operation and data, pending SQL need not be changed, also not shadow
Ring the accuracy of SQL implementing results.
The attribute isDeleted of Boolean types is set in the entity class in one of the embodiments, is used for
Indicate the attribute that logic is deleted；Before attribute isDeleted, logic deleted marker is set.Such as：
Database has a tables of data " t_user_info " to be：
Accordingly, it needs to be created that a corresponding entity class inside program, the attribute corresponding data table of entity class
Field, the entity class can be：
Logic deleted marker in the entity class is@LogicDelete, and the attribute executed is isDeleted, and attribute
IsDeleted has mapped the is_deleted fields in tables of data, realizes that logic deletion needs first to know that logic deletes field,
And LogicDelete is exactly to delete field for recognition logic.
Based on above-mentioned example, patrolling in the tables of data is identified according to logic deleted marker in one of the embodiments,
Collecting the step of deleting field includes：Determine the attribute pointed by logic deleted marker in entity class, according to attribute in entity class with
The mapping relations of field in tables of data obtain the corresponding field of the attribute, and field is deleted as logic.Thus, it is possible to quickly accurate
True determines that logic deletes field.
The step of determining the corresponding entity class of the pending SQL statement in one of the embodiments, include：According to pre-
If SQL resolvers the pending SQL statement is parsed, obtain the corresponding syntax tree of the pending SQL statement；Time
The FROM nodes of the syntax tree and/or the leaf node of FROM nodes are gone through, determines the tables of data that pending SQL statement is directed to；
According to the mapping relations of entity class and tables of data, the corresponding entity class of the tables of data is obtained, is determined as the pending SQL languages
The corresponding entity class of sentence.
Such as：If pending SQL statement is query statement, cancel statement or update sentence, need to pending SQL languages
Sentence is analyzed, and the SQL resolvers inside Open-Source Tools druid can be used to parse pending SQL statement, be parsed work
Tool can be parsed into SQL statement one syntax tree.It is that query SQL Sentence analysis goes out the result is that SelectStatement
Object, the inside include mainly SelectList, From, Where attribute, and the structure that update SQL statement analyzes is one
The object of UpdateStatement, the inside mainly contain SetItem, From, Where attribute；SQL statement is deleted to analyze
Structure be a DeleteStatement object, the inside mainly contains From, Where attribute.Query SQL sentence pair
The syntax tree answered is shown in Figure 3, and the corresponding syntax tree of update SQL statement is shown in Figure 4.
By the corresponding syntax tree of SQL statement, the corresponding entity class of pending SQL statement can be quickly determined.
In one embodiment, the data that syntax tree corresponding node can be changed during traversing syntax tree, to reach
To the purpose for changing pending SQL.
Field is deleted according to the logic in one of the embodiments, and generates corresponding SQL operating conditions, according to described
SQL operating conditions update the pending SQL statement the step of include：
If the action type is update or inquiry, the first operating condition of X.is_delete=K1 is generated, in institute
It states and increases by first operating condition in the where attributes of pending SQL statement；If the action type is to delete, generate
The second operating condition of X.is_delete=K2 creates the SQL statement of a updating type according to the pending SQL statement,
Increase by second operating condition in the set attributes of the SQL statement of the updating type.
Wherein, X indicates that the data item in the tables of data that pending SQL statement is directed toward, is_delete indicate logic cancel (CANCL)
Section, K1, K2 indicate preset logical value, as X.is_delete=K1, indicate that data X is not deleted by logic, work as X.is_
When delete=K2, indicate that data X is deleted by logic.
Below by query SQL sentence and for deleting SQL statement, the update realization of pending SQL statement is illustrated:
Assuming that pending query SQL sentence is：
SELECT u.*, th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id=
Th.user_id WHERE u.name=' test '；
It parses the SQL statement and obtains syntax tree as shown in figure 3, when traversing SQLExprTableSource nodes, obtain
Corresponding table name is got, because entity class and tables of data there are mapping relations, therefore can further find corresponding entity
Class；Judge to indicate either with or without@LogicDelete in entity class, if so, then indicating targeted category according to@LogicDelete
Property map out corresponding field name in tables of data, add corresponding logic in the Where nodes of pending query SQL sentence later
Deletion condition, such as plus is_delete=0 as querying condition.Based on this, above-mentioned pending query SQL sentence is obtained
Corresponding, updated SQL statement is：
SELECT u.*, th.*ROM t_user_info u LEFT JOIN t_user_health th ON u.id=
Th.user_id WHERE u.name=' test ' AND u.is_delete=0AND th.is_delete=0.
It should be understood that if pending SQL statement is update SQL statement, more above-mentioned same principle modification is pending
SQL statement so that cannot be changed by the data that logic is deleted, improve the accuracy of SQL update results.
Assuming that pending deletion SQL statement is:
Delete from t_user_info u where u.name=test '；
The pending deletion SQL statement is parsed, it is as shown in Figure 4 to obtain syntax tree；Delete is operated, it can not basis
Traversal SQL syntax tree goes directly to change node content therein, to update the deletion SQL statement, because root node has been not
With.In this regard, the mode of reconfigurable UpdateStatement generates the corresponding parsing tree of update SQL statement, because if
Make into update SQL statement, From and Where are identical, therefore when constructing UpdateStatement nodes, can be with
Assignment, and UpdateStatement ratios are carried out according to the From and Where of the DeleteStatement for deleting SQL statement
The more SetItem nodes of DeleteStatement, this SetItem node are exactly to update is_deleted inside SQL statement
The grammer tree node of=1 this sentence.Former cancel statement for deleting SQL statement can be converted into logic deletion based on this
Cancel statement.It generates and replaces original SQL statement after new SQL statement and go to execute, that is, can reach the purpose of logic deletion.It is based on
This, obtaining the corresponding update SQL statement of above-mentioned pending deletion SQL statement is：
Update t_user_info u set u.is_deleted=1where u.name=test '.
In one embodiment, the realization means of the application SQL execution method can be：In advance according to the rule of mybatis
Model writes a mybaits plug-in unit, and the function of the plug-in unit is modified to pending SQL statement, reaches the pending SQL of transformation
The purpose of sentence, principle be prepare methods in StatementHandler in Mybatis are intercepted, that is,
Execute the method that can first call plug-in unit when prepare methods.Mybatis is a comparison specification and flexible frame,
Entity class is provided, interface itself provides by the mybaits plug-in units can realize and change the purpose of pending SQL statement, therefore
It can very easily realize that the SQL of the application deleted logic executes method.
It should be understood that can also realize that the above-mentioned SQL deleted logic is held by other frames of data access layer
Row method, such as realized based on Hibernate or JDBC.Hibernate encapsulates relatively more, itself goes without providing interface
SQL statement is rewritten, therefore it is larger to implement difficulty.JDBC is oneself to write SQL to go to execute, and can pass through dynamic before executing SQL
Agency's (simulation mybatis plug-in units) realizes the rewriting to SQL statement, but what encapsulation JDBC does not do, so to realize this Shen
SQL please executes method, needs user separately to increase entity class and adds logic deleted marker, but dynamic proxy is JDBC sheets
Body does not have offer, and user is needed separately to go to realize.
By above-described embodiment, pending SQL can be converted automatically, configuration is simple, theoretically adapts to all
SQL statement, user are that delete data or logic deletion data also need not be certainly without being concerned about bottom when writing SQL
Oneself is in the operation SQL for writing logic deletion.
It should be understood that for each method embodiment above-mentioned, although each step in flow chart is according to arrow
Instruction is shown successively, but these steps are not the inevitable sequence indicated according to arrow to be executed successively.Unless having herein bright
True explanation, there is no stringent sequences to limit for the execution of these steps, these steps can execute in other order.And
And at least part step in the flow chart of embodiment of the method may include multiple sub-steps or multiple stages, this is a little
Step or stage are not necessarily to execute completion in synchronization, but can execute at different times, these sub-steps
Either the execution sequence in stage be also not necessarily carry out successively but can with the sub-step of other steps or other steps or
At least part in person's stage executes in turn or alternately.
Based on thought identical with the SQL execution method deleted logic in above-described embodiment, it is also provided herein to patrolling
Collect the SQL executive devices deleted.
In one embodiment, as shown in fig. 6, the SQL executive devices deleted logic of the present embodiment include：
Type identification module 501 identifies the operation class of the pending SQL statement for obtaining pending SQL statement
Type；
Field identification module 502 is used for when the action type is to delete, update or inquire, according in entity class
Preset logic deleted marker identifies that the logic in the tables of data that pending SQL statement is directed to deletes field；The entity class with
There are mapping relations between the tables of data；
And modification execution module 503, generate corresponding SQL operating conditions, root for deleting field according to the logic
The pending SQL statement is updated according to the SQL operating conditions, executes the SQL statement that update obtains.
In one embodiment, the field identification module 502 includes：
Class recognition unit, for when the action type is to delete, update or inquire, determining the pending SQL
The corresponding entity class of sentence；And field recognition unit, for detecting preset logic deleted marker, root in the entity class
Identify that the logic in tables of data deletes field according to logic deleted marker.
In one embodiment, the SQL executive devices further include：First execution module, if for detecting the entity
Do not include preset logic deleted marker in class, executes the pending SQL statement.
In one embodiment, the SQL executive devices further include：Second execution module, for when determining described to wait holding
When the action type of row SQL statement is newly-increased action type, the pending SQL statement is executed.
In one embodiment, the class recognition unit includes：Tables of data identifies that subelement and class identify subelement.
Wherein, tables of data identifies subelement, for being carried out to the pending SQL statement according to preset SQL resolvers
Parsing, obtains the corresponding syntax tree of the pending SQL statement；Traverse the FROM nodes and/or FROM nodes of the syntax tree
Leaf node, determine the tables of data that pending SQL statement is directed to；Class identifies subelement, for according to entity class and tables of data
Mapping relations, obtain the corresponding entity class of the tables of data, be determined as the corresponding entity class of the pending SQL statement.
In one embodiment, the field recognition unit includes：Field determination subelement, for determining logic in entity class
Attribute pointed by deleted marker obtains the attribute pair according to the mapping relations of field in attribute in entity class and tables of data
The field answered deletes field as logic.
In one embodiment, the modification execution module 503 includes：First modification unit and the second modification unit.
Wherein, the first modification unit generates X.is_delete if being update or inquiry for the action type
The first operating condition of=K1 increases by first operating condition in the where attributes of the pending SQL statement；Second
Unit is changed, if being to delete for the action type, the second operating condition of X.is_delete=K2 is generated, according to institute
The SQL statement that pending SQL statement creates a updating type is stated, is increased in the set attributes of the SQL statement of the updating type
Add second operating condition.
Wherein, X indicates that the data item in the tables of data that pending SQL statement is directed toward, is_delete indicate logic cancel (CANCL)
Section, K1, K2 indicate preset logical value, as X.is_delete=K1, indicate that data X is not deleted by logic, work as X.is_
When delete=K2, indicate that data X is deleted by logic.
It by the SQL executive devices of above-described embodiment, realizes and pending SQL is converted automatically, adapt to own
SQL statement.Whether user is deleted by logic, by normal when editing SQL statement without the data in focused data library
The SQL statement of rule can realize the SQL operations for the data deleted logic, not only simplify the SQL operation difficulties of user, and carry
The accuracy of high SQL operating results.
Specific limit about the SQL executive devices deleted logic may refer to above for deleting logic
SQL executes the restriction of method, and details are not described herein.Modules in the above-mentioned SQL executive devices deleted logic can be whole
Or part is realized by software, hardware and combinations thereof.Above-mentioned each module can be in the form of hardware embedded in or independently of computer
In processor in equipment, can also in a software form it be stored in the memory in computer equipment, in order to processor tune
With the corresponding operation of the above modules of execution.
In addition, in the embodiment for the SQL executive devices of above-mentioned example deleted logic, the logic of each program module is drawn
Divide and is merely illustrative of, can be as needed in practical application, such as the configuration requirement of corresponding hardware or the reality of software
Above-mentioned function distribution is completed by different program modules, i.e., the SQL deleted logic is executed dress by existing convenient consideration
The internal structure set is divided into different program modules, to complete all or part of the functions described above.
In one embodiment, a kind of computer equipment, including memory and processor are provided, is stored in memory
Computer program, the processor realize the sides of execution SQL of any of the above-described embodiment deleted logic when executing computer program
The step of method.
In one embodiment, a kind of computer readable storage medium is provided, computer program is stored thereon with, is calculated
Machine program realizes the step of SQL of any of the above-described embodiment deleted logic executes method when being executed by processor.
One of ordinary skill in the art will appreciate that realizing all or part of flow in above-described embodiment method, being can be with
Relevant hardware is instructed to complete by computer program, the computer program can be stored in a non-volatile computer
In read/write memory medium, the computer program is when being executed, it may include such as the flow of the embodiment of above-mentioned each method.Wherein,
Any reference to memory, storage, database or other media used in each embodiment provided herein,
Including non-volatile and/or volatile memory.Nonvolatile memory may include read-only memory (ROM), programming ROM
(PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM) or flash memory.Volatile memory may include
Random access memory (RAM) or external cache.By way of illustration and not limitation, RAM is available in many forms,
Such as static state RAM (SRAM), dynamic ram (DRAM), synchronous dram (SDRAM), double data rate sdram (DDRSDRAM), enhancing
Type SDRAM (ESDRAM), synchronization link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM
(RDRAM), direct memory bus dynamic ram (DRDRAM) and memory bus dynamic ram (RDRAM) etc..
Each technical characteristic of above example can be combined arbitrarily, to keep description succinct, not to above-described embodiment
In each technical characteristic it is all possible combination be all described, as long as however, the combination of these technical characteristics be not present lance
Shield is all considered to be the range of this specification record.
In the above-described embodiments, it all emphasizes particularly on different fields to the description of each embodiment, there is no the portion being described in detail in some embodiment
Point, it may refer to the associated description of other embodiments.
The term " comprising " and " having " of embodiment hereof and their any deformations, it is intended that cover non-exclusive packet
Contain.Such as contain series of steps or the process, method, system, product or equipment of (module) unit are not limited to arrange
The step of going out or unit, but further include the steps that optionally not listing or unit, or further include optionally for these mistakes
The intrinsic other steps of journey, method, product or equipment or unit.
Referenced herein " multiple " refer to two or more."and/or", the association for describing affiliated partner are closed
System indicates may exist three kinds of relationships, for example, A and/or B, can indicate：Individualism A exists simultaneously A and B, individualism
These three situations of B.It is a kind of relationship of "or" that character "/", which typicallys represent forward-backward correlation object,.
Referenced herein " first second " be only be the similar object of difference, do not represent for the specific of object
Sequence, it is possible to understand that specific sequence or precedence can be interchanged in ground, " first second " in the case of permission.It should manage
The object that solution " first second " is distinguished can be interchanged in the appropriate case so that the embodiments described herein can in addition to
Here the sequence other than those of diagram or description is implemented.
The several embodiments of the application above described embodiment only expresses, the description thereof is more specific and detailed, but simultaneously
It cannot therefore be construed as limiting the scope of the patent.It should be pointed out that coming for those of ordinary skill in the art
It says, under the premise of not departing from the application design, various modifications and improvements can be made, these belong to the protection of the application
Range.Therefore, the protection domain of the application patent should be determined by the appended claims.
Claims (11)
1. a kind of SQL deleted logic executes method, which is characterized in that including：
Pending SQL statement is obtained, identifies the action type of the pending SQL statement；
When the action type is to delete, update or inquire, waited for according to preset logic deleted marker, identification in entity class
The logic executed in the tables of data that SQL statement is directed to deletes field；There are mapping relations between the entity class and the tables of data；
Field is deleted according to the logic and generates corresponding SQL operating conditions, waits holding according to described in SQL operating conditions update
Row SQL statement executes the SQL statement that update obtains.
2. the SQL according to claim 1 deleted logic executes method, which is characterized in that preset according in entity class
Logic deleted marker, identify that the step of logic in the tables of data that pending SQL statement is directed to deletes field includes：
Determine the corresponding entity class of the pending SQL statement；
Preset logic deleted marker in the entity class is detected, identifies that the logic in tables of data is deleted according to logic deleted marker
Field.
3. the SQL according to claim 2 deleted logic executes method, which is characterized in that further include：
Do not include preset logic deleted marker in the entity class if detecting, executes the pending SQL statement；
And/or
After the step of determining the action type of the pending SQL statement, further include：
When the action type of pending SQL statement is newly-increased action type, the pending SQL statement is executed.
4. the SQL according to claim 2 deleted logic executes method, which is characterized in that determine the pending SQL
The step of sentence corresponding entity class includes：
The pending SQL statement is parsed according to preset SQL resolvers, the pending SQL statement is obtained and corresponds to
Syntax tree；The FROM nodes of the syntax tree and/or the leaf node of FROM nodes are traversed, determines pending SQL statement needle
To tables of data；
According to the mapping relations of entity class and tables of data, the corresponding entity class of the tables of data is obtained, is determined as described pending
The corresponding entity class of SQL statement.
5. the SQL according to claim 2 deleted logic executes method, which is characterized in that according to logic deleted marker
Identify that the step of logic in the tables of data deletes field includes：
The attribute pointed by logic deleted marker in entity class is determined, according to the mapping of field in attribute in entity class and tables of data
Relationship obtains the corresponding field of the attribute, and field is deleted as logic.
6. the SQL according to any one of claims 1 to 5 deleted logic executes method, which is characterized in that patrolled according to described
It collects and deletes the corresponding SQL operating conditions of field generation, the step of the pending SQL statement is updated according to the SQL operating conditions
Suddenly include：
If the action type is update or inquiry, the first operating condition of X.is_delete=K1 is generated, is waited for described
It executes in the where attributes of SQL statement and increases by first operating condition；
If the action type is to delete, the second operating condition of X.is_delete=K2 is generated, according to described pending
SQL statement creates the SQL statement of a updating type, increases described the in the set attributes of the SQL statement of the updating type
Two operating conditions；
Wherein, X indicates that the data item in the tables of data that pending SQL statement is directed toward, is_delete indicate that logic deletes field,
K1, K2 indicate preset logical value, as X.is_delete=K1, indicate that data X is not deleted by logic, work as X.is_delete
When=K2, indicate that data X is deleted by logic.
7. the SQL according to claim 4 or 5 deleted logic executes method, which is characterized in that further include：
The corresponding entity class of tables of data is created, the attribute that setting is deleted for indicating logic in the entity class, for the attribute
Corresponding logic deleted marker is set.
8. the SQL according to claim 7 deleted logic executes method, which is characterized in that set in the entity class
Set for indicate logic delete attribute the step of include：
Attribute that Boolean types are arranged in the entity class, being deleted for indicating logic, is arranged logic for the category and deletes
Indicate@LogicDelete.
9. a kind of SQL executive devices deleted logic, which is characterized in that including：
Type identification module identifies the action type of the pending SQL statement for obtaining pending SQL statement；
Field identification module, for when the action type is to delete, update or inquire, being patrolled according to preset in entity class
Deleted marker is collected, identifies that the logic in the tables of data that pending SQL statement is directed to deletes field；The entity class and the data
There are mapping relations between table；
And modification execution module, corresponding SQL operating conditions are generated for deleting field according to the logic, according to described
SQL operating conditions update the pending SQL statement, execute the SQL statement that update obtains.
10. a kind of computer equipment, including memory, processor and storage are on a memory and the meter that can run on a processor
Calculation machine program, which is characterized in that the processor realizes the step of claim 1 to 8 any the method when executing described program
Suddenly.
11. a kind of computer readable storage medium, is stored thereon with computer program, which is characterized in that the program is by processor
The step of claim 1 to 8 any the method is realized when execution.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4A CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810225650.4A CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108563694A true CN108563694A (en) | 2018-09-21 |
| CN108563694B CN108563694B (en) | 2021-04-13 |
Family
ID=63531750
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810225650.4A Active CN108563694B (en) | 2018-03-19 | 2018-03-19 | SQL execution method and device for logic deletion, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108563694B (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109271404A (en) * | 2018-10-25 | 2019-01-25 | 北京华宇信息技术有限公司 | Database is anti-error to delete method, apparatus, electronic equipment and computer-readable medium |
| CN110309196A (en) * | 2019-05-22 | 2019-10-08 | 深圳壹账通智能科技有限公司 | Block chain data storage and query method, apparatus, equipment and storage medium |
| CN110515973A (en) * | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN116010419A (en) * | 2023-02-07 | 2023-04-25 | 江西数字网联信息安全技术有限公司 | Method and device for creating unique index and optimizing logic deletion |
| CN116975032A (en) * | 2023-07-14 | 2023-10-31 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en) * | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| CN103473296A (en) * | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | Recycle bin mechanism and recycle bin system applicable to cloud computing |
| CN103678494A (en) * | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| US20140279850A1 (en) * | 2013-03-14 | 2014-09-18 | Cavium, Inc. | Batch incremental update |
| CN105989195A (en) * | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en) * | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107111628A (en) * | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Effective maintenance of row repository index on memory optimization table |
| CN107168700A (en) * | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en) * | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
-
2018
- 2018-03-19 CN CN201810225650.4A patent/CN108563694B/en active Active
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP0120185B1 (en) * | 1983-02-28 | 1992-01-02 | International Business Machines Corporation | Computing apparatus and method for translating into a linear query a graphic language query |
| US20140279850A1 (en) * | 2013-03-14 | 2014-09-18 | Cavium, Inc. | Batch incremental update |
| CN103473296A (en) * | 2013-09-04 | 2013-12-25 | 国云科技股份有限公司 | Recycle bin mechanism and recycle bin system applicable to cloud computing |
| CN103678494A (en) * | 2013-11-15 | 2014-03-26 | 北京奇虎科技有限公司 | Method and device for client side and server side data synchronization |
| CN107111628A (en) * | 2014-10-31 | 2017-08-29 | 微软技术许可有限责任公司 | Effective maintenance of row repository index on memory optimization table |
| CN105989195A (en) * | 2015-03-23 | 2016-10-05 | 国际商业机器公司 | Approach and system for processing data in database |
| CN107092685A (en) * | 2017-04-24 | 2017-08-25 | 广州新盛通科技有限公司 | A kind of method that file system and RDBMS store transaction data are used in combination |
| CN107168700A (en) * | 2017-04-30 | 2017-09-15 | 王栋 | Foundational development framework based on the SQL SELECT full-automatic list WEB background management systems driven |
| CN107193910A (en) * | 2017-05-14 | 2017-09-22 | 四川盛世天成信息技术有限公司 | A kind of database tamper resistant method and system applied to data safety class product |
Non-Patent Citations (1)
| Title |
|---|
| 郑汉垣: "Delphi中如何恢复与物理删除Dbase数据库的记录", 《龙岩师专学报》 * |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109271404A (en) * | 2018-10-25 | 2019-01-25 | 北京华宇信息技术有限公司 | Database is anti-error to delete method, apparatus, electronic equipment and computer-readable medium |
| CN110309196A (en) * | 2019-05-22 | 2019-10-08 | 深圳壹账通智能科技有限公司 | Block chain data storage and query method, apparatus, equipment and storage medium |
| CN110515973A (en) * | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN110515973B (en) * | 2019-08-30 | 2022-02-18 | 上海达梦数据库有限公司 | Data query optimization method, device, equipment and storage medium |
| CN116010419A (en) * | 2023-02-07 | 2023-04-25 | 江西数字网联信息安全技术有限公司 | Method and device for creating unique index and optimizing logic deletion |
| CN116975032A (en) * | 2023-07-14 | 2023-10-31 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
| CN116975032B (en) * | 2023-07-14 | 2024-04-12 | 南京领行科技股份有限公司 | Data alignment method, system, electronic device and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108563694B (en) | 2021-04-13 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108563694A (en) | Method, apparatus, computer device and storage medium for performing SQ L for logical deletion | |
| US11341155B2 (en) | Mapping instances of a dataset within a data management system | |
| US20100185693A1 (en) | Methods and apparatus for creating an audit trail | |
| US10936478B2 (en) | Fast change impact analysis tool for large-scale software systems | |
| CN110209568A (en) | Coverage rate test method, apparatus and storage equipment | |
| US20090150420A1 (en) | Generating debug information | |
| CN102426582B (en) | Data manipulation management devices and data manipulation management method | |
| US20180144061A1 (en) | Edge store designs for graph databases | |
| US20120297364A1 (en) | Augmented design structure matrix visualizations for software system analysis | |
| US20220269702A1 (en) | Intelligent annotation of entity-relationship data models | |
| CN107832448A (en) | Database operation method, device and equipment | |
| CN113254470A (en) | Data change method and device, computer equipment and storage medium | |
| EP2951680B1 (en) | Acquiring identification of an application lifecycle management entity associated with similar code | |
| CN112970011B (en) | Pedigree in record query optimization | |
| CN116302930A (en) | Application testing method and device | |
| US11036779B2 (en) | Data analytics systems and methods | |
| CN115357590A (en) | Recording method and device for data change, electronic device and storage medium | |
| CN111949845A (en) | Method, apparatus, computer device and storage medium for processing mapping information | |
| EP3260997A1 (en) | Method and system for enforcing user policy on database records | |
| CN106991116A (en) | The optimization method and device of database executive plan | |
| CN114861229B (en) | Hive dynamic desensitization method and system | |
| CN112667691B (en) | Patent indexing method, device, equipment and storage medium based on database | |
| CN112416966B (en) | Impromptu query method, impromptu query device, computer device and storage medium | |
| US7051333B1 (en) | System and method for extending a programming language to include multiple dissimilar object systems | |
| CN108845857A (en) | A kind of icon management method and device based on cloud platform |
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 |