Based on the quick retrieval of the task of SQL database tree and generate the method that showsTechnical field
The present invention relates to Structured Query Language (SQL) SQL (Structured Query Language) retrieval technique, be specially the quick retrieval of a kind of task tree based on SQL database and generate the method that shows.
Background technology
In sql like language, provide a large amount of inquiry, sort methods.Foundation for ordering in the sql like language is that certain requirement is arranged, as simple numerical ordering, according to numerical values recited; The character ordering is according to its ASCII character size ordering; And time-sequencing, according to the time order and function order.
The statement of same realization purpose, different combinations, it is carried out efficient and differs greatly.In the actual utilization, if the sort by that the needs default sort rule of accord with SQL not, but its ordering rule that can't discern is generally handled this problem by following several modes:
1) data is extracted from database, in internal memory, judge ordering, draw ranking results according to specific regular program;
2) key word with ordering carries out format conversion, is converted to the ordering form that sql like language can be discerned, and the ranking function of utilizing sql like language again is at the data sorting after changing.
System is in handling above-mentioned sequencer procedure, and the implementation method of ordering is an one critical function, realizes that the speed of ordering also is an important performance reference target.Because in interactively mode of operation, the far-end operation person has certain tolerance for the response time of system.If the long-time not response of operation, system value can't be approved.Should in the system performance design, take into full account this demand.
In the project management, set the distribution situation that can understand task by the task of browsing project, being distributed on the logical topology of task is the structure of a binary tree, as Fig. 1.Because assigning of task be unordered in time, thereby the position that is kept in the database also is unordered arrangement, as table 1:
Table 1
Sequence number node number task names task definition is assigned start time time plan concluding time physical end time state
1 1.1 task A 2008-1-1
2 1.3 task B 2008-1-4
3 2.1 task C 2008-1-4
4 3.1 task D 2008-1-5
5 2.2 task E 2008-1-9
6 1.1.1 task F 2008-1-10
Unique foundation that can sort to task node is the node serial number of task, as: 1.1.1,2.1 etc.Multistage task node numbering is a required attribute in the task tree, but because be in the SQL statement to numerical value, time conventional among the SQL, or simply sorting operation is carried out in character combination, and the character of task node numbering form is relatively, SQL does not support at present.Therefore conventional SQL sorting operation can't obtain desired data at short notice.
The data of how unordered database space being preserved are output as orderly data, are the problems that the retrieval tasks tree will be considered in the project management system.In result's while of exporting, owing in program, need judgement, calculating, arrangement, processing to need the plenty of time, so the response time of system also should be considered.Therefore how fast and effectively the task node in the task tree being sorted, is the problem that needs reality to face in the design.
Summary of the invention
The problem to be solved in the present invention is: sql like language can not be retrieved fast to its ordering rule that can't discern, especially at the tree of the task in the project management, needs the method for task node information in the quick searching database table of a kind of energy.
Technical scheme of the present invention is: based on the quick retrieval of the task of SQL database tree and generate the method that shows, the task node numbering of task tree is converted to the information that SQL can sort, be specially each group numeral of task node numbering is carried out the numerical value weighted, carry out code conversion again, form database and can differentiate the ordering numerical value of size, the ordering numerical value according to obtaining sorts to task node by sql like language, realization is to the retrieval of task tree, and further generation shows; Wherein the numerical value weighted is: determine weighting factor f according to the maximum son node number that task is set under each node, weighting factor f is 10nIn greater than the minimum value of son node number, each group numeral of task node numbering be multiply by weighting factor, finish weighted; Code conversion is: with each group numeral of the numbering of the task node after the weighted, carry out the connection of character string by its order in the task node serial number, obtain the Final Format conversion value of task node numbering, numerical value promptly sorts.
Quick retrieval of task of the present invention tree and generation are presented in the system based on browser and server B/S framework to be carried out, and is convenient to operated from a distance, and SQL database is arranged on server end, and concrete steps are:
Step 1, the maximum node number under the layering of node that sets the tasks is determined the size of weighting factor f;
Step 2, the maximum number of plies of the tree that sets the tasks is determined the conversion value zero padding number of node level less than the maximum layer of node;
Step 3, carry out from task node number the ordering numerical value format conversion, format conversion is carried out at server end, the ordering numerical value that obtains deposits SQL database in, by sql like language ordering numerical value is differentiated size, realize ordering, obtain arranging orderly data set task node;
Step 4, after the browser of client obtains to arrange orderly data set, existing sequence according to data set presents net result by the Javascript language by tree-like structure, can reflect the distribution situation of current task tree, and the generation of realization task tree shows.
For avoiding server end with the frequent data interaction between the SQL database, in SQL database, utilize view to realize format conversion, database manipulation function by Java, the task node numbering is read out one by one, carry out format conversion, be inserted into again in the temporary table space of interim generation, at last temporary table is carried out the SQL data retrieval, sort according to the data that generate,, be and arrange orderly data set result's output.
The non-ordered data that can't directly sort with sql like language that the present invention can be fast, effectively database space is preserved is output as orderly data, retrieval of task tree and generation at project management shows especially, be shown as example with a normal task tree, the task node number is 1000, original query time is about 1 minute, and after employing the inventive method, query time shortens to 1～2 second, thereby realizes fast worktodo tree search function.
Description of drawings
Fig. 1 is project task tree synoptic diagram.
Embodiment
The form of task node numbering can't be discerned its magnitude relationship for the SQL basic statement.The inquiry ordering of SQL statement is to have utilized the size of the key word of ordering to sort, thereby enumerates the SQL query result.What need solution is how the task node numbering to be converted to the information that SQL can sort.
The present invention is converted to the information that SQL can sort with the task node numbering of task tree, be specially each group numeral of task node numbering is carried out the numerical value weighted, carry out code conversion again, form database and can differentiate the ordering numerical value of size, according to the ordering numerical value that obtains, by sql like language task node is sorted, realize retrieval, and further generate demonstration the task tree; Wherein the numerical value weighted is: determine weighting factor f according to the maximum son node number that task is set under each node, weighting factor f is 10nIn greater than the minimum value of son node number, each group numeral of task node numbering be multiply by weighting factor, finish weighted; Code conversion is: the maximum number of plies of determining the current task tree, determine node zero padding disposal route, with each group numeral of the numbering of the task node after the weighted, carry out the connection of character string by its order in the task node serial number, obtain the Final Format conversion value of task node numbering, numerical value promptly sorts.
Weighting factor f is 10nIn a value, if maximum son node number is 400 under the individual node of task tree, so, 102＜400＜103, the value of f is 1000; Maximum son node number is 99 under the individual node of task tree, so, and 101＜99＜102, the value of f is 100.
Determine the maximum number of plies of current task tree, determine that node conversion zero padding disposal route is meant, as: node serial number is a.b.c, this node layer is 3, and the maximum layer of task tree is 4, and the form that can represent is a.b.c.d, this moment, we thought that consideration at node a.b.c is need be according to 4 layers node processing, the three first layers value is according to the normal handling method, and last demand is handled according to zero padding, and the figure place of zero padding is a weighting factor f figure place.
If node serial number is a.b.c, the maximum layer of task tree is 4, carries out numerical value weighted and code conversion:
Value＝a*f+b*f+c*f+f*0
Value is final format conversion value, the numerical value that promptly sorts, and what plus sige was represented in formula is the connection of character string.For example maximum son node number is 450 under the individual node of task tree, then the value of f is 1000, the maximum layer of task tree is 4, task node is numbered 1.1.1, then be divided into 1,1,1 three group of numeral, ordering numerical value Value=1*1000+1*1000+1*1000+1000*0=1000100010000000, in like manner task node is numbered 2.1, ordering numerical value Value=2*1000+1*1000+0*1000+0*1000=2000100000000000, SQL database is carried out retrieval ordering to ordering numerical value, realization is to the orderly output of project management task tree, as table 2:
Table 2
Sequence number node number task names task definition is assigned start time time plan concluding time physical end time state
1 1.1 task A 2008-1-1
6 1.1.1 task F 2008-1-10
2 1.3 task B 2008-1-4
3 2.1 task C 2008-1-4
5 2.2 task E 2008-1-9
4 3.1 task D 2008-1-5
Compare with table 1, effectively realized task node being sorted by the task node numbering.
During specific implementation of the present invention, quick retrieval of task tree and generation are presented in the system based on browser and server B/S framework to be carried out, and is convenient to operated from a distance, and SQL database is arranged on server end, and concrete steps are:
Step 1, maximum node number under layering of node sets the tasks, determine the size of weighting factor f, for example, according to daily task statistics, according to the expression of this task node form of a.b.c, a, b, the span of these three numerical value of c is more than or equal to 0, smaller or equal to 1000, then determine a task tree down the maximum node tree of one deck be 1000;
Step 2, the maximum number of plies of the tree that sets the tasks, by determining the clear and definite value of maximum layer space, for example the node form of a.b.c represents that the task node number of plies is 3 layers;
Step 3, carry out numbering the format conversion of ordering numerical value from task node, because system is the design architecture of B/S, format conversion is carried out at server end, the ordering numerical value that obtains deposits SQL database in, by sql like language ordering numerical value is differentiated size, realize ordering, obtain arranging orderly data set task node;
Step 4, after the browser of client obtains to arrange orderly data set, existing sequence according to data set presents net result by the Javascript language by tree-like structure, can reflect the distribution situation of current task tree, and the generation of realization task tree shows.
For avoiding server end with the frequent data interaction between the SQL database, in SQL database, utilize view to realize format conversion, database manipulation function by Java, the task node numbering is read out one by one, carry out format conversion, be inserted into again in the temporary table space of interim generation, at last temporary table is carried out the SQL data retrieval, sort according to the data that generate,, be and arrange orderly data set result's output.