CN115544006A - General database data operation model and construction method thereof - Google Patents

Abstract

The invention discloses a general database data operation model and a construction method thereof, which relate to the technical field of database data operation, integrate nine core operations of data statistics, reports, lists, addition, modification, deletion, emptying, import and export into one functional page, realize the query and data maintenance of any structure table of a database through a front-end page, have the powerful functions of data item rule maintenance and empowerment management, greatly enhance the universality and convenience of the model, basically meet the requirements of most data query and maintenance in various industries, and provide solid guarantee for wide popularization and application.

Description

General database data operation model and construction method thereof

Technical Field

The invention relates to the technical field of database data operation, in particular to a general database data operation model and a construction method thereof.

Background

With the gradual development of networks, users have more and more demands for network services and show a diversified trend. And most of the business processing will eventually be reflected in the manipulation of the data. A plurality of platforms of the current system are complex, most of the existing systems are in a cast-in-place mode, one page is developed or one function is developed according to requirements, the logic is relatively simple under the operation, the development and delivery time cost is low, but as the systems are more and more pages are more and more, a large amount of repeated development and maintenance cost is consumed, the existing software development process is developed according to business needs for a long time, and even if a plurality of similar data operations are frequently encountered, the existing codes are mostly carried, deleted and changed. And many functions of many systems even exceed 9 to more than one, and finally, the user realizes the operation on the database data through front-end selection or input. The operations are mainly statistics, reports, lists, additions, modifications, deletions, emptying, importation, exportation, etc. Although the core SQL form of each data operation is substantially the same, due to various differences between the business requirements and the data structure, much time is wasted in development and maintenance for the user requirements, and the data operation is continuously modified or redesigned to adapt to the new business development. In order to develop the result with half the effort for a long time, the constant variable is found in the variation and a proper dynamic variable is adopted, and a general data operation model is repeatedly trained and constructed.

Disclosure of Invention

In order to overcome the defects, the invention provides a general database data operation model and a construction method thereof, integrates the statistics, report forms, lists, addition, modification, deletion, emptying, import and export of nine core operations of data into a functional page, realizes the query and data maintenance of any structure table of the database through a front-end page, and has the functions of creation/maintenance/empowerment management of data operation items and use rules thereof.

A general database data manipulation model, comprising:

a user module: the user module is used for acquiring the project and the use rule thereof, and dynamically generating or loading the page assembly for the user to perform custom operation.

Preferably, the management module is used for creating, maintaining and assigning right data operation items and usage rules thereof, and the management module further comprises item management and rule management, wherein the item management is used for previewing/creating a table or a view, creating data operation items and initializing usage rules and maintenance/assigning right operation items thereof; the rule management is used to maintain/entitle usage rules including field rules, operation types, association extensions.

Preferably, the user module includes a project permission area and a custom input area, the project permission area is used for acquiring a user permission project, an adaptive rule and an adaptive operation component thereof, and the custom input area is used for user to perform custom operation setting or input.

Preferably, the operation mode of the user in the item license area includes: statistics, reporting, listing, adding, modifying, importing, deleting and emptying.

Preferably, the modification at most comprises single-line modification, single-line modification line feed, multi-line same-value modification, screening same-value modification, file batch modification and attachment uploading maintenance.

Preferably, the user module further comprises a memory collection area, and the memory collection area is used for automatically memorizing and collecting user-defined operations.

Preferably, the user module further comprises an attachment area, and the attachment area is used for a user to view, upload and maintain the attachment.

Preferably, the user module further includes a list setting area, where the list setting area is used for the user to perform display hiding setting and field arrangement order customization on fields according to needs of the service, and to set the number of display rows and columns and the width and height according to the resolution of the display.

A method for constructing a general database data operation model comprises the following steps:

step 1: the management module is used for creating, maintaining and empowering data items by an administrator, and performing maintenance empowering on the use rules of the data items according to the basic characteristics of a database table or a view structure, wherein the use rules comprise but are not limited to field use rules, data operation types and item association extension;

step 2: when a user opens a page for the first time, a program automatically acquires items and usage rules thereof according to authorization and operates a memory collection, the program automatically generates field list components and operation type options according to the rules, automatically initializes selection input according to the memory, and automatically configures collection components according to the collection;

and 3, step 3: when the user switches the data operation type, the system reloads the applicable field list according to the type and confirms the availability, the user can carry out self-defined setting row-column display on the field list, and the user selects or inputs components such as the field list and the like; when the user selects to operate the collection, the program automatically matches the operation type according to the collection, reloads the field list component and initializes the selection input.

And 4, step 4: and after the user successfully executes the corresponding type of data operation, the user module outputs a data table or operation information, automatically memorizes the operation input at the same time, generates an operation log at the same time, and allows the user to perform custom naming collection on the operation input at the same time.

Preferably, thestep 1 further comprises the steps of:

step 11: previewing the database table or view associated with the created project to confirm whether the database table or view exists, if so, enteringstep 12, and if not, creating a new table or view by using the data in the database, or creating a new table to the database by using an external CSV file;

step 12: if the database table or the view associated with the data item changes the structure, if so, enteringstep 14, if not, enteringstep 13;

step 13: creating a data operation item and initializing a field rule parameter;

step 14: maintenance/entitlement data handling items and their usage rules.

The invention has the beneficial effects that:

the invention integrates nine core operations of data statistics, report forms, lists, addition, modification, deletion, emptying, import and export into one functional page, realizes the query and data maintenance of any structure table of the database through a front-end page, has the powerful functions of data item rule maintenance and empowerment management, greatly enhances the universality and the convenience of the model, basically meets the requirements of most data query and maintenance in various industries, and provides solid guarantee for wide popularization and application.

Drawings

In order to more clearly illustrate the detailed description of the invention or the technical solutions in the prior art, the drawings used in the detailed description or the prior art description will be briefly described below. Throughout the drawings, like elements or portions are generally identified by like reference numerals. In the drawings, elements or portions are not necessarily drawn to scale.

FIG. 1 is a schematic diagram of a general database data manipulation model provided by the present invention;

FIG. 2 is a step diagram of a method for constructing a general database data operation model according to the present invention;

FIG. 3 is a diagram of management module project management/rule management steps of a method for constructing a general database data operation model according to the present invention;

FIG. 4 is a diagram of the core operation steps of the user module of the method for constructing the general database data operation model according to the present invention;

fig. 5 is a schematic diagram of a core function of a user module of a method for constructing a general database data operation model according to the present invention.

Fig. 6 is a schematic diagram of a user module front-end interface of a method for constructing a general database data operation model according to the present invention.

Fig. 7 is a schematic diagram of a user module front-end interface field list setting area of a method for constructing a general database data operation model according to the present invention.

Detailed Description

Embodiments of the present invention will be described in detail below with reference to the accompanying drawings. The following examples are only for illustrating the technical solutions of the present invention more clearly, and therefore are only used as examples, and the protection scope of the present invention is not limited thereby.

It is to be noted that, unless otherwise specified, technical or scientific terms used herein shall have the ordinary meaning as understood by those skilled in the art to which the invention pertains.

As shown in fig. 1, a general database data manipulation model includes:

a user module: the user module is used for acquiring the project and the use rule thereof, and dynamically generating or loading the page assembly for the user to perform custom operation.

The data operation mode supported by the user module is shown in fig. 5, and includes statistics, reporting, list query (desensitization controllable), data addition, data modification (6 methods adaptive), deletion, emptying, import (no limited sequence), and export. The system has more than twenty convenient assistance of automatically memorizing, customizing and collecting the individuality and the like, and can quickly realize most of individuality data operation.

More specifically, the system also comprises a management module, wherein the management module is used for creating, maintaining and assigning right data operation items and usage rules thereof, and the management module also comprises item management and rule management, wherein the item management is used for previewing/creating a table or a view, creating data operation items and initializing the usage rules and the maintenance/assigning right operation items; the rule management is used to maintain/entitle usage rules including field rules, operation types, association extensions.

More specifically, the user module includes a project permission area and a custom input area, the project permission area is used for acquiring a user permission project, a rule adapted to the project permission project and an operation component adapted to the project permission project, and the custom input area is used for setting or inputting a custom operation by a user.

More specifically, the user module further comprises a memory collection area, and the memory collection area is used for a user to collect and maintain the user-defined operation or select to use and collect the user-defined operation.

The user module includes a-G zones as shown in fig. 6 and 7. The user can realize 9 data operations at most through the user module, and the user module is simultaneously provided with a plurality of convenient aids, so that the operation of the user is ensured to be more free, personalized and quick, wherein the memory collection area is an E area shown in figure 6.

As shown in fig. 4 and fig. 6, automatically acquiring user role permission items and their adaptation rules (rule role adaptation priority principle is from small to large) and user collection settings, generating operation mode options (at most 8 kinds) in area a, automatically generating adapted field list components (checkbox, text box, drop-down box) in area B and displaying columns and rows according to user-defined settings, loading collection options in area E and accessories in area F, where area C is an execution area: executing collection, executing inquiry, executing maintenance (including accessories), executing expansion and executing export, automatically confirming availability of a desensitization switch and an accessory button in a C area, wherein an E area and an F area are components to which the C area belongs and are switched by a five-star icon button and an accessory button in the C area respectively; and (4) selecting the E area collection item or performing custom selection input by the user to complete the required data query or maintenance operation.

More specifically, the operation mode of the user in the item license area includes: statistics, reporting, listing, exporting, adding, modifying, importing, deleting and emptying.

The item permission area is area a shown in fig. 6, and the operation mode of area a includes: permission items, item permission usage rules, rule permission modes of operation and field lists, field list permission ordering and line display settings.

The B area is a user-defined selection input area: the list field component availability corresponds to the operational mode, the field checkings can be grouped or aggregated by the column in the statistics and reporting mode, the query result includes the column in the listing mode, the column can be modified in the modification mode, the textbox entry and drop-down box selection can be filtered in any mode, and the value of the column can be updated by the entry in the modification mode. In performing the statistical operations: b, the list in the area is subjected to collusion grouping or aggregation, and the total number of rows of the list is counted without collusion; report operation: the B-zone list only shows the grouping aggregation and screening fields, and the other aggregation value type fields are hidden but participate in the aggregation operation. Preview data is not checked. Is simpler and more convenient than a statistical mode; and (3) listing operation: and (4) checking a field query list in the area B, and previewing 2 thousand rows of full field data without checking. If the desensitization field exists in the field list, the desensitization switch of the C area is visible, and the on or off is controlled by the work number desensitization authority. And the D area is an output area: outputting a table or information or exception of the execution result; if the field supports the attachment, the attachment button in the C area can be used for clicking to open the F area of the component, then the leftmost button in the row in the D area table is clicked to select, that is, whether the attachment exists in all attachment columns in the E area can be seen, and if the attachment exists, the attachment can be downloaded by clicking; adding operation: the checking B area needs to add data fields (with light yellow or green background colors) and input values, if the primary key value is null, the data fields are acquiescent by a rear end sequence, the input values of the light green background fields cannot be null, and operation marks (operator numbers and time) are automatically processed by a model; and (3) importing operation: the "file" button in the area a selects a CSV file having the same name as the item, and executes additional import, and the area D does not show the table but indicates how many lines to import. The CSV file has unlimited field sequence, can have many but not necessarily required imported fields, can use the field names of database tables and also can use the alias names specified by the items, even can be used together, and the model can strictly verify and automatically process the operation marks; emptying operation: directly clearing all data in the table; and (3) deleting operation: and selecting or checking the row in the table in the area D or screening the input # #inthe area B, and executing deletion.

More specifically, the modification includes, but is not limited to, single-line modification linefeed, multi-line same-value modification, filter same-value modification, file batch modification, and attachment uploading maintenance.

Single line modification: the data row (red) in the D area is selected, the column to be modified in the B area is selected and input, the D area data table displays the row after the execution is successful, and the previous table is covered, so that the method is suitable for modifying data at low frequency.

Single line modification linefeed: and after the execution is successful, the corresponding column value of the data table in the D area is rewritten, and meanwhile, the data table is automatically switched to the next row (red, prompt is given to the end) and the input frame data in the B area is refreshed upwards. The D area is maintained in the current table and automatically feeds lines, so that the method is suitable for modifying data with different values of line-by-line fields at high frequency.

Multi-row same value modification: and checking a plurality of rows in the D area table, checking a modified column in the B area, inputting values, displaying the modified rows in the D area data table after the execution is successful, and covering the front table. So that the method is suitable for multiple lines of same-value low-frequency secondary modified data.

Screening for equivalence modification: and the input box in the area B is well provided with screening conditions, a modification field is selected and checked, and the value of = = is input, the column value which meets the screening is modified, and the area D is not displayed back but is prompted. The method is suitable for large-data-volume equivalent modification.

Modifying files in batches: the 'file' button in area A selects CSV file with the same name as the project, checks the modification field in area B, executes the column value of the equivalent record of the modified primary key value, and area D does not display the list but prompts the number of columns and rows of the file and the number of rows and rows of the modified data. The CSV file has unlimited field sequence, and can have more but not less main keys and modified fields.

Maintenance of accessories: if the item table has field permission maintenance attachments (picture document compressed packets and the like), an attachment button in the area C is visible and available, and the area F of the attachment component is opened by clicking, so that the field branches of the permitted attachments can be seen, and the check list group is submitted, added and deleted. And when the row in the D area table is selected by clicking, whether the accessory exists or not can be seen on the column branches in the F area, and the single option is selected to complete the addition, deletion, modification and check of the accessory.

More specifically, the user module further comprises an attachment area, and the attachment area is used for allowing a user to view, upload and maintain the attachment.

The attachment zone is zone F shown in figure 6.

More specifically, the user module further includes a list setting area, where the list setting area is used for a user to perform display hiding setting and field arrangement order customization on fields according to service needs, and to set the number of display rows and columns and the width and height according to the resolution of the display. The list setting area is located in the G area shown in fig. 7, and the G area is switched by a double-arrow icon button on the right side of the a area.

Outputting a query data table or operation information to a D area for display, and clicking a table head of the D area to support sorting; after the operation is successful, the model automatically memorizes the operation setting and input and generates an operation log; the user can maintain, operate and collect in the E area as required, at most 60 items are supported, and sharing is supported so that the user can conveniently set the E area without people. The operation collection is to store the user setting and input, and the operation collection is convenient and quick to use again without setting every time.

As shown in fig. 2, a general database data manipulation model includes:

step 1: the management module is used for creating, maintaining and empowering data items by an administrator, and performing maintenance empowering on the use rules of the data items according to the basic characteristics of a database table or a view structure, wherein the use rules comprise but are not limited to field use rules, data operation types and item association extension;

step 2: when a user opens a page for the first time, a program automatically acquires items and usage rules thereof according to authorization and operates a memory collection, the program automatically generates field list components and operation type options according to the rules, automatically initializes selection input according to the memory, and automatically configures collection components according to the collection;

and step 3: when the user switches the data operation types, the system reloads the applicable field list according to the types and confirms the availability, the user can perform custom setting row and column display on the field list, and the user selects or inputs components such as the field list and the like; when the user selects to operate the collection, the program automatically matches the operation type according to the collection, reloads the field list component and initializes the selection input.

And 4, step 4: and after the user successfully executes the corresponding type of data operation, the user module outputs a data table or operation information, automatically memorizes the operation input at the same time, generates an operation log at the same time, and allows the user to perform custom naming collection on the operation input at the same time.

As shown in fig. 3, more specifically, thestep 1 further includes the following steps:

step 11: previewing the database table or view associated with the created project to confirm whether the database table or view exists, if so, enteringstep 12, and if not, creating a new table or view by using the data in the database, or creating a new table to the database by using an external CSV file;

step 12: if the database table or the view associated with the data item changes the structure, if so, enteringstep 14, if not, enteringstep 13;

step 13: creating a data operation item and initializing a field rule parameter;

step 14: maintenance/entitlement data handling items and their usage rules.

The logic method for dynamically realizing various operations by the model user module is mainly characterized by maintaining the empowerment by using rules, wherein the using rules comprise field rule convention and table operation rights (operation mode and association expansion).

Field rule convention: the model aims to realize operations such as adding, deleting, modifying, checking and the like on any table at the back end through the front end, so that the model needs to use any structural table at the back end, the front end dynamically generates a matching component, and the key of the coordination of the back end and the front end is the table field parameter rule formulation. By analyzing and integrating the similarities and differences of various data operations, the parameter table structure is determined and the identification rule is agreed. If the type column C represents that the character type front end field is selected as a black character, and the N represents that the numerical type front end field is selected as a blue character; d represents that the date type front end field is selected as a red character; the rightmost symbol of the alias column indicates that the input value cannot be null when the column is modified; themodification column 1 indicates that the field is a primary key, 2 indicates that the field is modifiable, 4 indicates that the field is an operation work number, and 5 indicates that the field is an update time stamp; the import column check indicates that the field allows import; valid column check indicates that the field permits use; desensitization treatment is required when the desensitization check representation field is used for inquiring a sensitive data list; fuzzy column check representation fields support fuzzy screening; grouping is allowed in the statistic column check field; the SUM column tick field allows SUM; if the option column has option input, the front-end input control is a drop-down box, otherwise, the front-end input control is a text box;

table operation right: the model corresponds the operation rights of the table, namely the operation mode and the association expansion, with the use rules, and then configures different use rules to different roles or individuals, and because the rules establish the table field use method, the operation mode and the association expansion, different users can perform different operations (increase, delete, change and check) on different fields of the same table and even different data screening;

the model user module not only realizes the core function of data operation, but also comprises a plurality of auxiliary methods so as to meet the requirements of users on freedom, conciseness and quick individuality;

and (3) separate right adaptation: data operation items and rules are maintained quickly, on-line is needed, right is given according to needs, right is given, and users are basically accurate to adapt to small redundancy. Because the add/drop modify lookup operations of the table may be weighted, the field and data usage of the table may be weighted.

Operation memory and collection: the model can automatically memorize the operations of selection, input and the like of the user, and even the user can collect the operations and use ok next time without repeated setting. This convenience is far beyond the design of any dedicated function page.

Custom field list: the user can select the own sorting mode and can also customize list field display, hiding, sequence adjustment and row and column, wherein the hidden can shield fields which are not needed by the user, the sequence adjustment supports multiple movements and can quickly complete field battles which are wanted by the user, and the row and column setting can allocate the number of field columns and column width of a single row and allocate the number of display lines and row height according to the needs of the user and the resolution of the display.

Line-by-line automatic line feed modification: and selecting a row in the table in the modification operation, checking the row to be modified and the input data value, and after the successful submission is executed, automatically switching the downlink of the model and refreshing the control data of the field list, thereby greatly improving the operation efficiency.

Screening for modifications and deletions: and inputting and confirming the screening condition preview data. In the modification mode, the field value of the screened data can be modified by checking the field to be modified and inputting "= = modification value"; and the screened data line can be deleted by inputting the "##" into the text box in the deletion mode.

Broad of bulk modify or import CSV file fields: the model supports batch modification data or import of the CSV files, wherein the sequence of fields of the CSV files is not limited, the number of the fields can be more than or not less than required fields, and the field names can be database table field names, alias names specified by the model or even mixed use of the database table field names and the alias names.

Five data screening methods: screening is supported in any mode of operation. Single or multiple equivalence methods of up to 300, comma-separated interval method, matching or non-fuzzy method, null-non-null method (= 0 or = 1), and duplication method (= 2).

Very simple date screening: the date type red field input box inputs 0 to indicate the current date, -1 to indicate the previous 1 date and 1 to indicate the next 1 date, and then corresponding to the date format list selection, for example, the inquiry of the last days or months or years is very convenient and fast to screen and count, and the time is not needed to be moved after collection.

Text fill and numeric self-add: the rule management supports direct input or selection of input boxes. Where the grouping text box supports a "= N" pattern to fill N rows down, "= N + M" fills N rows down and accumulates from the left first value + M, "= N + + M" fills N rows down and accumulates from the left first value arithmetic M. If no value is contained in the text, the left-first value is 0 by default. The inventive method can operate on the leftmost value at any position in the text string.

Sorting table fields: and displaying a paging table at the front end of the query data result, wherein the table supports background switching of a moving line of a mouse, supports clicking a table head to switch sequencing and synchronize data sequence numbers for the first time in a reverse order, and switching paging sequence numbers are consistent.

The model is developed and put into use in 5 months in 2020, and is improved for more than one hundred times in 9 months in 2022, only the logic layer codes reach 28.5 ten thousand, and 238 method events and 962 method events are judged, so that the complex problems which possibly occur are greatly predicted, coordinated, controlled and solved. The model can be selected to be entitled to realize 9 data operations, supports the uploading and downloading of accessories, greatly strengthens the universality and the convenience of the model, basically meets the requirements of inquiring and maintaining most data in various industries, and provides a solid guarantee for wide popularization and application.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; such modifications and substitutions do not depart from the spirit and scope of the present invention, and they should be construed as being included in the following claims and description.

Claims (10)

1. A general database data manipulation model, comprising:

a user module: the user module is used for acquiring the project and the use rule thereof, and dynamically generating or loading the page assembly for the user to perform custom operation.

2. The universal database data manipulation model of claim 1 further comprising, but not limited to, a management module for creating, maintaining, and granting data manipulation items and their usage rules, and further comprising item management, rule management, for previewing/creating tables or views, creating data manipulation items and initializing their usage rules, maintaining, and granting manipulation items; the rule management is used to maintain/entitle usage rules including field rules, operation types, association extensions.

3. The general database data operation model of claim 1, wherein the user module comprises a project permission area and a custom input area, the project permission area is used for obtaining user permission projects, adaptive rules and adaptive operation components thereof, and the custom input area is used for user to perform custom operation setting or input.

4. The universal database data manipulation model of claim 3 wherein the user's manipulation modes in the project license area include: statistics, reporting, listing, adding, modifying, importing, deleting and emptying.

5. The universal database data manipulation model of claim 4 wherein said modification operations include, but are not limited to, single line modification linefeed, multiple line same value modification, filter same value modification, file batch modification, attachment upload maintenance.

6. The universal database data manipulation model of claim 3 wherein the user module further comprises a memory repository for user collection maintenance or selective use of a collection for custom manipulation.

7. The universal database data manipulation model of claim 3 wherein the user module further comprises an attachment area for viewing and uploading maintenance of attachments by a user.

8. The model of claim 3, wherein the user module further comprises a list setting area, and the list setting area is used for the user to set the display hiding setting and the field arrangement order according to the service requirement, and set the number of the display rows and columns and the width and height according to the resolution of the display.

9. A method for constructing a general database data operation model, which comprises the general database data operation model of any one of claims 1 to 8, and comprises the following steps:

step 1: the management module is used for creating, maintaining and empowering data items by an administrator, and performing maintenance empowering on the use rules of the data items according to the basic characteristics of a database table or a view structure, wherein the use rules comprise but are not limited to field use rules, data operation types and item association extension;

step 2: when a user opens a page for the first time, a program automatically acquires items and usage rules thereof according to authorization and operates a memory collection, the program automatically generates field list components and operation type options according to the rules, automatically initializes selection input according to the memory, and automatically configures collection components according to the collection;

and step 3: when the user switches the data operation type, the system reloads the applicable field list according to the type and confirms the availability, the user can carry out self-defined setting row-column display on the field list, and the user selects or inputs components such as the field list and the like; when the user selects to operate the collection, the program automatically matches the operation type, reloads the field list component and initializes the selection input according to the collection;

and 4, step 4: and after the user successfully executes the corresponding type of data operation, the user module outputs a data table or operation information, automatically memorizes the operation input at the same time, generates an operation log at the same time, and allows the user to perform custom naming collection on the operation input at the same time.

10. The method for constructing the general database data operation model according to claim 9, wherein the step 1 further comprises the steps of:

step 11: previewing the database table or view associated with the created project to confirm whether the database table or view exists, if so, entering step 12, and if not, creating a new table or view by using the data in the database, or creating a new table to the database by using an external CSV file;

step 12: whether a database table or a view associated with the data item changes the structure or not is judged, if yes, the step 14 is carried out if the item is created, and if not, the step 13 is carried out;

step 13: creating a data operation item and initializing a field rule parameter;

step 14: maintenance/entitlement data handling items and their usage rules.

Images