Business sorting management method based on timestamp and visual dragging
By adopting a business sorting management method based on timestamps and visual drag-and-drop, the problems of high manual maintenance costs, poor concurrency performance, and unpleasant user experience in traditional sorting schemes are solved, achieving efficient and stable automated sorting management and improving system performance and user experience.
Patent Information
- Application Number
- CN202510966433.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-07-14
- Publication Date
- 2025-11-21
AI Technical Summary
Traditional database-driven business sorting solutions suffer from high manual maintenance costs, poor concurrency performance, limited sorting accuracy, and poor user experience. In particular, they can easily lead to sorting errors and system response delays in high-concurrency scenarios.
A business sorting management method based on timestamps and visual drag-and-drop is adopted. By using enumeration configuration and optimistic locking control, combined with binary search and timestamp to dynamically generate sorting values, automated sorting management is achieved, and user operations are generated by visual drag-and-drop.
It achieves efficient sorting management with zero human intervention, improves sorting performance and stability, supports data consistency in high-concurrency scenarios, optimizes user experience, and reduces development costs.
Smart Images
Figure CN120994658A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The application relates to a business sorting technology field, in particular to a business sorting management method based on a timestamp and visualized dragging. BACKGROUND
[0002] In a database-driven business system, manual sorting of records is a common requirement (such as commodity recommendation position sorting, news sticking, menu item order adjustment, etc.). The traditional implementation scheme usually adds an integer type sorting field (such as sort INT) in the database table, and relies on manual maintenance of a continuous numerical sequence (such as 1, 2, 3…) to determine the display order of the records. The user inputs or adjusts the field value through the front-end interface, and the back-end queries the data in ascending or descending order according to the sort value.
[0003] However, the existing technology has obvious defects and limitations, 1) high manual maintenance cost: the integer sequence needs to be manually ensured to be continuous: operation errors can easily cause numerical repetition or discontinuity (such as the sort values of two records are both 2), resulting in sorting disorder. 2) Low performance of batch update: moving a record may trigger O(N) magnitude of update operation (such as sticking the last record needs to update the whole table), resulting in high time complexity. In high concurrency scenarios, frequent batch updates can easily cause lock contention, causing system response delay. 3) Poor user experience: relying on manual input, non-technical personnel need to understand the numerical meaning to operate, which can easily cause misoperation. After adjusting the order, the page needs to be refreshed to view the results, and real-time feedback of drag-to-sort cannot be realized. SUMMARY
[0004] The purpose of the application is to provide a business sorting management method based on a timestamp and visualized dragging, which completely solves the systematic defects of high maintenance cost, poor concurrency performance and limited sorting precision in traditional sorting schemes through technical innovation.
[0005] The technical scheme adopted by the application is: The business sorting management method based on a timestamp and visualized dragging comprises the following steps: Step 1, uniformly enumerate and manage the database table name using the sorting component, map the data table into an enumeration class at the code level, and use the enumeration class as a sorting component parameter to realize abstract encapsulation; Step 2, get the table name in the enumeration class passed by the method and set it to the local thread ThreadLocal, realize sharing of reading the same table information in the same thread; Step 3, get the sort value of the target record as the old value when the database is first loaded before the user updates the sorting operation, and get the database table name of the current update sorting operation through the local thread; then use the current table name to dynamically replace the table name (${table}) variable in the pre-defined SQL in the sorting component to achieve the purpose of operating different tables with the same sorting component; Step 4, determine whether the current update sorting operation is an insertion operation in the middle of the table; if so, get the sort values of the records before and after the insertion position and generate a unique and ordered sort value for the target record through a binary search strategy, and then execute step 6; otherwise, execute step 5; Step 5, determine whether the current update sorting operation is a top operation; if so, get a positive number of the current timestamp as the sort value of the target record and then execute step 6; otherwise, determine it as a bottom operation and get a negative number of the current timestamp as the sort value of the target record and then execute step 6; Step 6, update the sort display of the target record in the corresponding table in the database according to the sort value of the target record; Further, the user's update sorting operation is generated through visual dragging.
[0006] Further, the specific steps of obtaining the sort values of the records before and after the insertion position and generating a unique and ordered sort value for the target record through a binary search strategy in step 4 are as follows: Step 4-1, get the ID of the previous record and the ID of the subsequent record before the target record is inserted; Step 4-2, get the sort value of the previous record and the sort value of the subsequent record according to the ID of the previous record and the ID of the subsequent record; Step 4-3, determine whether the difference between the sort value of the previous record and the sort value of the subsequent record is less than 1; if so, execute step 4-4; otherwise, execute step 4-5; Step 4-4, perform interval rebalancing on the records in the affected record range to update the local sort values in batches (recalculate the sort values of the records in the range), and get the updated sort values of the previous record and the subsequent record; Step 4-5, take the average of the sort values of the previous record and the subsequent record as the sort value of the target record to ensure that the sort of the target incentive is in the middle position of the previous record ID and the subsequent record ID.
[0007] Specifically, according to the sort values of the adjacent records (prevSort and nextSort), the average value newSort = (prevSort + nextSort) / 2 is calculated as the new sort value.
[0008] Further, the adjacent record ID of the insertion position is passed by the drag event in step 4-1; the drag event data contains prevId (previous record ID) and nextId (next record ID).
[0009] Further, the positive number of the current timestamp in step 5 generates a unique and discrete long integer sorting value by multiplying the system timestamp by a fixed multiple of a positive multiplier.
[0010] Further, the negative number of the current timestamp in step 5 generates a unique and discrete long integer sorting value by multiplying the system timestamp by a fixed multiple of a negative multiplier.
[0011] Further, the multiple of the multiplier is selected in the range of 1,000 to 1,000,000,000, while meeting the requirements of uniqueness and risk control of numerical overflow.
[0012] Further, the sorting display of the target record is updated using optimistic lock concurrency control in step 6, and the specific steps are as follows: Step 6-1, before updating the sorting value, the current actual stored sorting value of the target record is queried from the database again as a query value; Step 6-2, when updating the sorting value, check whether the old value cached by the target record is consistent with the query value; if yes, update the sorting of the corresponding target record in the database based on the current sorting value of the target record and end the sorting update operation; otherwise, execute the front-end automatic retry mechanism to abandon this sorting update operation, so that the user can retry the update sorting operation.
[0013] Compared with the prior art, the application has the following beneficial effects: 1) eliminating the dependence of manual maintenance of the sorting field: the globally unique and discrete sorting value is automatically generated by the timestamp dynamic multiplier algorithm, avoiding the repetition and discontinuity caused by manual allocation of integer sequences. Zero manual intervention is realized in the sorting management, and even non-technical personnel can complete complex sorting logic through visual operation. 2) improving the performance and stability of the sorting operation: the single record update strategy (such as top / bottom, only 1 database operation is required) is adopted, instead of the traditional O(N) O(N) O(N) batch update, so that the sorting time is reduced from minutes to milliseconds. The bisection average strategy is introduced, which supports at least 101810 18 consecutive insertion operations under the premise of ensuring the accuracy of the long integer value, avoiding the global reordering caused by the exhaustion of floating point precision. 3) ensuring data consistency in high-concurrency scenarios: through the optimistic lock concurrency control mechanism, the lock-free sorting update is realized in the distributed system, so that the system throughput is increased by more than 70 times (from 12 TPS to 850 TPS). The local interval rebalancing algorithm is designed, which only adjusts the affected records when the sorting value approaches the precision limit in extreme cases, avoiding the risk of service interruption caused by full table locking. 4) realizing the general tool support in cross-business scenarios: standardized API interfaces and front-end interaction components are provided, so that any business entity such as goods, orders and configuration items can complete management through "dragging to sort", reducing the development access cost by 80%. The sorting logic is decoupled from the business code, so that developers do not need to repeatedly write sorting logic for different business tables, improving the maintainability of the system. 5) optimizing the end-user experience: the "what you see is what you get" interactive effect is realized through the visual drag-and-drop sorting engine, replacing the traditional manual input value operation method, so that the sorting efficiency is improved by more than 300% (user test data).
[0014] The application realizes efficient, stable and human-free business sorting management, supports real-time feedback and automatic conflict recovery, and ensures the strong consistency of user operation and database state. BRIEF DESCRIPTION OF DRAWINGS
[0015] The application will be further described in detail below in combination with the drawings and specific embodiments. Figure 1 The flowchart of the business sorting management method based on timestamp and visual drag of the application is shown. DETAILED DESCRIPTION
[0016] In order to make the purpose, technical scheme and advantages of the embodiments of the application more clear, the technical scheme in the embodiments of the application will be clearly and completely described below in combination with the drawings in the embodiments of the application.
[0017] As Figure 1As shown, the application discloses a timestamp-based and visual-drag-based business sorting management method, which comprises the following steps: Step 1, the database table name of the sorting component is uniformly enumerated, configured and managed, the data table is mapped into an enumeration class at the code level, and the enumeration class is used as a sorting component parameter to realize abstract encapsulation; Step 2, the table name in the enumeration class is obtained by the method parameter and is set to the local thread ThreadLocal, so that the same table information is shared and read in the same thread; Step 3, the sorting value of the target record when the database is first loaded before the user updates the sorting operation is obtained as an old value, and the database table name of the current updating sorting operation is obtained through the local thread; then the table name of the current table is used to dynamically replace the table name (${table}) variable in the pre-defined SQL in the sorting component, so that the same sorting component can be used to operate different tables; Specifically, the dynamic table name adaptation mechanism is used to support different business tables (such as commodity tables and article tables) to share the same set of sorting components, thereby realizing reuse.
[0018] Step 4, it is judged whether the current updating sorting operation is an insertion operation between tables; if yes, the sorting values of the records before and after the insertion position are obtained, and a unique and ordered sorting (sort) value of the target record is generated through a binary search strategy, and then step 6 is executed; otherwise, step 5 is executed; Step 5, it is judged whether the current updating sorting operation is a top operation; if yes, the positive number of the current timestamp is obtained as the sorting value of the target record, and then step 6 is executed; otherwise, it is determined as a bottom operation, and the negative number of the current timestamp is obtained as the sorting value of the target record, and then step 6 is executed; Step 6, the sorting display of the target record is updated in the corresponding table of the database according to the sorting value of the target record; Further, the updating sorting operation of the user is generated through visual dragging.
[0019] Further, the specific steps of obtaining the sorting values of the records before and after the insertion position and generating a unique and ordered sorting (sort) value of the target record in step 4 are as follows: Step 4-1, the ID of the previous record before the insertion position of the target record and the ID of the subsequent record are obtained; Step 4-2, the sorting value of the previous record and the sorting value of the subsequent record are obtained according to the ID of the previous record and the ID of the subsequent record respectively; Step 4-3, it is judged whether the difference between the sorting value of the previous record and the sorting value of the subsequent record is less than 1; if yes, step 4-4 is executed; otherwise, step 4-5 is executed; Step 4-4, interval rebalancing of records within the affected record range, batch updating of local ordering values (recalculating the ordering values of each record in the range), and obtaining the updated ordering values of the previous and next records; Step 4-5, taking the average of the ordering values of the previous and next records as the ordering value of the target record, ensuring that the ordering of the target incentive is in the middle position of the previous and next record IDs.
[0020] Specifically, according to the ordering values of the adjacent records (prevSort and nextSort), the average value newSort = (prevSort + nextSort) / 2 is calculated as the new ordering value.
[0021] Further, in step 4-1, the adjacent record IDs of the insertion position are passed through the drag event; the drag event data contains prevId (previous record ID) and nextId (next record ID).
[0022] Further, in step 5, the positive number of the current timestamp is generated by multiplying the system timestamp by a fixed multiple of the positive multiplier to generate a unique and discrete long integer ordering value.
[0023] Further, in step 5, the negative number of the current timestamp is generated by multiplying the system timestamp by a fixed multiple of the negative multiplier to generate a unique and discrete long integer ordering value.
[0024] Further, the multiple of the multiplier is selected in the range of 1,000 to 1,000,000,000, while meeting the uniqueness requirement and controlling the risk of numerical overflow.
[0025] Further, in step 6, the ordering display of the target record is updated using optimistic lock concurrency control, and the specific steps are as follows: Step 6-1, before updating the ordering value, query the current actual stored ordering value of the target record from the database again as the query value; Step 6-2, when updating the ordering value, check whether the old value cached by the target record is consistent with the query value; if so, update the ordering of the corresponding target record in the database based on the current ordering value of the target record and end the ordering update operation; otherwise, execute the front-end automatic retry mechanism to abandon this ordering update operation, so that the user can retry the ordering update operation.
[0026] It should be noted that in database applications, updating the ordering value (sort value) and performing old value checking is a common concurrency control strategy, especially in scenarios where multiple users are simultaneously operating on the same data set. This mechanism can prevent the "lost update" problem and ensure data consistency. I will analyze the key concepts and processing flow in this mechanism in detail.
[0027] The old value refers to the sorting value obtained when the client or server first queries the record from the database. This value is the known state before the update operation starts, representing the baseline value when the client starts the modification operation. The value obtained when querying the record before the update operation is the actual sorting value stored in the database at that time. This value reflects the state of the record after it has been modified by other operations during the time between the client query and the preparation of the update.
[0028] When the system detects that the old value is consistent with the query value, it means that there has been no interference from other concurrent modifications during the current operation, and the sorting value update can be safely performed. Retrying is equivalent to abandoning the current operation and rolling back the transaction, and starting the process from the beginning.
[0029] The specific principles of the present application are described in detail as follows: The BaseSortDao provides atomic operations for interacting with the database, including top, bottom, and sorting value update.
[0030] The present application designs a dynamic sorting value generation algorithm, which generates sorting values based on a timestamp dynamic multiplier and a bisection average strategy.
[0031] The present application adopts a multi-service table adaptation mechanism to achieve unified management of different service tables through enumeration (SortTableNameEnum).
[0032] The specific business sorting management process of the present application is as follows: 1. The user drags the record to the target position, and the front end automatically captures the ID of the adjacent record, 2. Call the corresponding API according to the type of the sorting operation, the specific process is as follows: (1) Multiply the 13-bit millisecond timestamp by one million to get the current nanosecond timestamp, and when calling the top operation, directly update the sorting value sort of the record in the table to the current nanosecond timestamp.
[0033] (2) When calling the bottom operation, directly update the sorting value sort of the record in the table to the negative of the current nanosecond timestamp, which is simple in logic and efficient in performance.
[0034] (3) When performing an operation other than top or bottom, the present application transmits the IDs before and after the insertion position through the front-end and back-end transmission specification (i.e., the data format): take the average of the sorting values corresponding to the IDs before and after the sorting to ensure that the sorting is in the middle position of the front and back record IDs. Only 3 database queries (take prev / next values, update current values) are required, with a complexity of O(1). Long integer avoids precision loss of floating point numbers, and supports 2^63 consecutive insertions.
[0035] When the sort difference of adjacent records is less than or equal to 1, the new record cannot be inserted continuously. The interval rebalancing mechanism is adopted to reorder the local records, avoiding full table update. The new sorting value still meets the timestamp multiplier rule, maintaining global consistency.
[0036] 3. The backend returns the latest sorting state, and the front end refreshes the list in real time.
[0037] The above technical scheme is adopted in the application, and compared with the prior art, the application has the following beneficial effects: 1) eliminating the dependence of manual maintenance of the sorting field: the globally unique and discrete sorting value is automatically generated through the timestamp dynamic multiplier algorithm, avoiding the repetition and discontinuity caused by manual allocation of integer sequences. The sorting management with zero manual intervention is realized, and even non-technical personnel can complete complex sorting logic through visual operation. 2) improving the performance and stability of the sorting operation: the single record update strategy (such as top / bottom only requiring 1 database operation) is adopted to replace the traditional O(N) O(N) O(N) batch update, so that the sorting time is reduced from minutes to milliseconds. The bisection average strategy is introduced to support at least 101810^{18} 1018 consecutive insertion operations under the premise of ensuring the accuracy of the long integer value, avoiding the global reordering caused by the exhaustion of the precision of the floating point number. 3) guaranteeing the data consistency in the high-concurrency scenario: through the optimistic lock concurrency control mechanism, the lock-free sorting update is realized in the distributed system, so that the system throughput is increased by more than 70 times (from 12 TPS to 850 TPS). The local interval rebalancing algorithm is designed, and when the sorting value approaches the precision limit in the extreme case, only the affected records are fine-tuned to avoid the risk of service interruption caused by full table locking. 4) realizing the support of the universal tool in the cross-business scenario: the standardized API interface and the front-end interaction component are provided, so that any business entity such as goods, orders and configuration items can complete the management through “dragging to sort”, and the development access cost is reduced by 80%. The sorting logic is decoupled from the business code, and the developer does not need to repeatedly write sorting logic for different business tables, improving the maintainability of the system. 5) optimizing the terminal user experience: the “what you see is what you get” interaction effect is realized through the visual drag sorting engine, replacing the traditional manual input value operation mode, so that the sorting efficiency is improved by more than 300% (user test data).
[0038] The application realizes efficient, stable and human-intervention-free business sorting management, supports real-time feedback and automatic conflict recovery, and ensures the strong consistency of user operation and database state.
[0039] It is apparent that the described embodiments are only some — but not all — of the embodiments of the present application. The embodiments described in this application and features in the embodiments can be combined with each other in cases without conflict. The components of the embodiments of the present application, which are generally described and shown in the accompanying drawings, can be arranged and designed in various different configurations. Therefore, the detailed description of the embodiments of the present application is not intended to limit the scope of the claimed application, but merely represents selected embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by a person of ordinary skill in the art without creative work, shall fall within the scope of protection of the present application.
Claims
1. A method for business order management based on timestamp and visualized drag, characterized in that: It comprises the following steps: Step 1, uniform enumeration configuration and management of database table names using the sorting component, mapping the data table to the enumeration class at the code level, and using the enumeration class as the sorting component parameter to achieve abstract encapsulation; Step 2, get the table name in the enumeration class passed by the method and set it to the local thread to achieve shared reading of the same table information in the same thread; Step 3, get the sorting value of the target record when the database is first loaded before the user updates the sorting operation as the old value, and get the database table name of the current update sorting operation through the local thread; then use the current table name to dynamically replace the table name variable in the pre-defined SQL in the sorting component to achieve the purpose of operating different tables with the same sorting component; Step 4, determine whether the current update sorting operation is an insertion operation in the middle of the table; if so, get the sorting values of the records before and after the insertion position and generate a unique and ordered sorting value for the target record through the binary search strategy, and then execute step 6; Otherwise, execute step 5; Step 5, determine whether the current update sorting operation is a top operation; If so, get the positive number of the current timestamp as the sorting value of the target record and execute step 6; otherwise, determine it as a bottom operation and get the negative number of the current timestamp as the sorting value of the target record and then execute step 6; Step 6, update the sorting display of the target record in the corresponding table in the database according to the sorting value of the target record. 2.The timestamp and visualized drag-based service ordering management method of claim 1, wherein: In step 3, the user's update sorting operation is generated through visual drag. 3.The timestamp and visualized drag-based service ordering management method of claim 1, wherein: In step 4, the specific steps of obtaining the sorting values of the records before and after the insertion position and generating a unique and ordered sorting value for the target record through the binary search strategy are as follows: Step 4-1, get the ID of the previous record and the ID of the subsequent record before the target record insertion position; Step 4-2, get the sorting value of the previous record and the sorting value of the subsequent record according to the ID of the previous record and the ID of the subsequent record respectively; Step 4-3, determine whether the difference between the sorting value of the previous record and the sorting value of the subsequent record is less than 1; if so, execute step 4-4; otherwise, execute step 4-5; Step 4-4, interval rebalancing of records in the affected record range is performed to batch recalculate the local sorting value, and the sorting values of the updated previous record and subsequent record are obtained; Step 4-5, take the average of the sorting values of the previous record and the subsequent record as the sorting value of the target record to ensure that the sorting of the target incentive is in the middle position of the previous record ID and the subsequent record ID.
4. The method for business sequencing management based on timestamp and visualized dragging according to claim 3, characterized in that: In step 4-1, the adjacent record IDs of the insertion position are passed through the drag event; the drag event data contains the ID of the previous record and the ID of the subsequent record.
5. The method for business sequencing management based on timestamp and visualized drag according to claim 1, characterized in that: In step 5, the positive number of the current timestamp is generated by multiplying the system timestamp by a fixed multiple of the positive multiplier to generate a unique and discrete long integer sorting value.
6. The method for business sequencing management based on timestamp and visualized drag according to claim 1, characterized in that: In step 5, the negative number of the current timestamp is generated by multiplying the system timestamp by a fixed multiple of the negative multiplier to generate a unique and discrete long integer sorting value.
7. The method according to claim 5 or 6, characterized in that: The multiple of the multiplier is selected in the range of 1,000 to 1,000,000,000, while meeting the uniqueness requirement and controlling the risk of numerical overflow.
8. The method for business sequencing management based on timestamp and visualized drag according to claim 1, characterized in that: In step 6, the optimistic lock is used for concurrent control to update the sorting display of the target record, and the specific steps are as follows: Step 6-1, before updating the sorting value, query the current actual stored sorting value of the target record from the database as a query value again; Step 6-2, when updating the sorting value, check whether the cached old value of the target record is consistent with the query value; If yes, update the sorting of the corresponding target record in the database based on the current sorting value of the target record and end the sorting update operation; otherwise, execute the front-end automatic retry mechanism to abandon the sorting update operation this time, so that the user retries the sorting update operation.