A method for storing video files in a relational database based on version sequence

Abstract

The invention discloses a method for storing video files in a relational database based on version sequence, which relates to the technical field of storage optimization, including: constructing an application server plus a database read-write separation cluster system; obtaining video file information to be stored, and calculating the MD5 value of the video file to be stored, storing the MD5 value of the video file to be stored in a relational database table according to a set format, and storing the video file to be stored in a video storage area; obtaining a video access request; if the video access request is a write request, accessing a database master server; if the video access request is a read request, based on a load balancing algorithm, allocating a database read-only server for the read request, and accessing the corresponding database read-only server. The invention has the advantages of: solving the problems of version management, security management, multi-user concurrent access conflict resolution, high performance, high reliability, etc. of massive video files.

Description

Technical Field

[0001] The present invention relates to the technical field of storage optimization, and in particular to a method for storing video files in a relational database based on version sequences. Background Art

[0002] At present, the main technical means for the storage and management of massive video files is to use a file management system. In order to meet the needs of business scenarios, this file management system needs to meet multiple requirements such as version management, security management, multi-user concurrent access conflict resolution, high performance, and high reliability. It has great technical difficulty and development workload, and users will face high purchase or development costs, as well as subsequent maintenance costs. Summary of the invention

[0003] In order to solve the above technical problems, a method for storing video files in a relational database based on version sequences is provided. This technical solution utilizes the user's existing relational database management system and database read-write separation cluster to solve the problems of version management, security management, multi-user concurrent access conflict resolution, high performance, high reliability, etc. for massive video files.

[0004] In order to achieve the above purpose, the technical solution adopted by the present invention is:

[0005] A method for storing video files in a relational database based on version sequences, comprising:

[0006] Constructing an application server plus a database read-write separation cluster system, wherein the application server plus a database read-write separation cluster system comprises at least one database master server and at least two database read-only servers, wherein the database master server executes video file read requests and video file write requests, and the database read-only server executes video file read requests;

[0007] Obtaining information of the video file to be stored, calculating the MD5 value of the video file to be stored, storing the MD5 value of the video file to be stored in a relational database table according to a set format, and storing the video file to be stored in a video storage area;

[0008] Get video access request;

[0009] If the video access request is a write request, the database master server is accessed, and the video file stored in the database master server after the write request is executed is synchronized with all database read-only servers;

[0010] If the video access request is a read request, a database read-only server is allocated to the read request based on the load balancing algorithm, and the corresponding database read-only server is accessed.

[0011] Preferably, the application server plus database read-write separation cluster system includes a safe backup mode, wherein when a database master server fails, one of the database read-only servers is selected from multiple database read-only servers based on an election algorithm as the database master server;

[0012] The election algorithm is:

[0013] Get the communication delay between multiple database read-only servers;

[0014] Based on the communication delays between multiple database read-only servers, the sum of the communication delays between each database read-only server and other database read-only servers is calculated as the central service indicator of the database read-only server;

[0015] Get the service life of each database read-only server;

[0016] Obtain several database read-only servers of the same model as sample servers;

[0017] Obtain the ratio of the number of sample servers that have experienced failures during their service life to the total number of sample servers as the failure probability of the database read-only server;

[0018] Calculate the election index of the database read-only server based on the election formula;

[0019] Select the database read-only server with the largest election index as the database master server;

[0020] The election formula is specifically:

[0021]

[0022] In the formula, is the election index of the read-only server of the i-th database, is the central service indicator of the i-th database read-only server, is the failure probability of the central service indicator of the i-th database read-only server, is the total number of database read-only servers. It is the minimum value of the central service index of multiple database read-only servers. It is the maximum value among the central service indicators of multiple database read-only servers. is the minimum value of the failure probability of the central service indicator of multiple database read-only servers. The maximum value of the failure probability of the central service indicator for multiple database read-only servers.

[0023] Preferably, the obtaining of the video access request specifically includes:

[0024] If the number of video access requests is equal to 1, no response will be made;

[0025] If the number of video access requests is greater than 1, determine whether the number of write requests is greater than 1. If so, add a label to each write request in chronological order. If not, do not respond.

[0026] Preferably, if the video access request is a write request, accessing the database host server specifically includes:

[0027] If the number of write requests is equal to 1, the database master server is accessed for the write request. If the number of write requests is greater than 1, the database master server is accessed for each write request in sequence according to the label order of the multiple write requests.

[0028] Perform video file verification based on write requests;

[0029] If the verification passes, the video file is loaded based on the write request, and the new version of the video file generated by the write request is updated for subsequent write requests;

[0030] If the verification fails, an error is reported.

[0031] Preferably, the load balancing algorithm is specifically:

[0032] Obtain the used loads of all database read-only servers, and take the sum of the used loads of all database read-only servers as the total used load;

[0033] Based on all read requests, determine the expected load of each read request, and take the sum of the expected loads of all read requests as the total read request load;

[0034] The sum of used load and read request load is added together to obtain the expected total load. The expected total load is divided by the total number of database read-only servers to obtain the nominal load of the database read-only servers.

[0035] Generate at least one read request allocation plan under the conditions that all read requests are allocated with database read-only servers and each database read-only server has not reached its load upper limit;

[0036] Based on the load evaluation formula, evaluate the load balancing indicators of all read request allocation schemes;

[0037] Filter out the read request allocation scheme with the smallest load balancing index and record it as the optimal read request allocation scheme;

[0038] Allocate a database read-only server for each read request based on the optimal read request allocation scheme;

[0039] The load evaluation formula is specifically:

[0040]

[0041] In the formula, The load balancing indicator for the read request allocation scheme, is the load of the i-th database read-only server after accessing the database read-only server according to the read request allocation scheme, The nominal load of a database read-only server.

[0042] Preferably, the accessing the corresponding database read-only server specifically includes:

[0043] Perform video file verification based on read requests;

[0044] If the verification passes, the video file is loaded based on the read request.

[0045] Preferably, the video file verification specifically includes:

[0046] Determine the video file targeted by the write request or the read request, and record it as the video file to be called;

[0047] Extract the MD5 value of the video file to be called from the relational database table and record it as the base MD5 value;

[0048] Calculate the MD5 value of the video file to be called stored in the database master server or the database read-only server, and record it as the real-time MD5 value;

[0049] Determine whether the reference MD5 value is equal to the real-time MD5 value. If so, the verification passes; if not, the verification fails.

[0050] Compared with the prior art, the present invention has the following beneficial effects:

[0051] The present invention proposes a method for storing video files in a relational database based on version sequence, which utilizes the existing relational database management system and database read-write separation cluster, and designs a read request load balancing algorithm and write request concurrent access conflict resolution based on the requirements of multi-user concurrent access, so as to ensure the request response efficiency and data consistency under multi-user concurrent access. It solves the problems of version management, security management, multi-user concurrent access conflict resolution, high performance, high reliability, etc. of massive video files. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 Flowchart of the method for storing video files in a relational database based on version sequence proposed in this solution;

[0053] Figure 2 This is a flowchart of the method of the election algorithm in this scheme;

[0054] Figure 3 This is a flow chart of the method for obtaining a video access request in this solution;

[0055] Figure 4 This is a flow chart of the method for accessing the database master server in this solution;

[0056] Figure 5 This is a method flow chart of the load balancing algorithm in this solution;

[0057] Figure 6 This is a flow chart of the method for performing video file verification in this solution;

[0058] Figure 7 It is a schematic diagram of the structure of the electronic device in the present invention;

[0059] Figure 8 It is a schematic diagram of the computer-readable storage medium structure in the present invention. DETAILED DESCRIPTION

[0060] The following description is used to disclose the present invention so that those skilled in the art can implement the present invention. The preferred embodiments described below are only examples, and those skilled in the art may think of other obvious variations.

[0061] Reference Figure 1 As shown, a method for storing video files in a relational database based on version sequence includes:

[0062] Constructing an application server plus a database read-write separation cluster system, wherein the application server plus a database read-write separation cluster system includes at least one database master server and at least two database read-only servers, wherein the database master server executes video file read requests and video file write requests, and the database read-only server executes video file read requests;

[0063] Obtaining information of the video file to be stored, calculating the MD5 value of the video file to be stored, storing the MD5 value of the video file to be stored in a relational database table according to a set format, and storing the video file to be stored in a video storage area;

[0064] Get video access request;

[0065] If the video access request is a write request, the database master server is accessed, and the video file stored in the database master server after the write request is executed is synchronized with all database read-only servers;

[0066] If the video access request is a read request, a database read-only server is allocated to the read request based on the load balancing algorithm, and the corresponding database read-only server is accessed.

[0067] The following is a specific example to illustrate the storage and update of a relational database table;

[0068] Table format definition of relational database

[0069] The table fields are defined as follows:

[0070] ID: ID number

[0071] NAME: Video file name CHAR (100)

[0072] NOV: version number

[0073] DATA: data VARCHAR

[0074] The table looks like this:

[0075]

[0076] The format of the DATA field is as follows:

[0077] Video file path / video file name#Version 1 digital signature, version 1 summary, version 1 storage time, version 1 operator#Version 2 digital signature, version 2 summary, version 2 storage time, version 2 operator#Version 3 digital signature, version 3 summary, version 3 storage time, version 3 operator...

[0078] Take the video file name a1.mp4 and the digital signature using the commonly used MD5 as an example. The data of the first version of the video file stored in the table is:

[0079] opt / video / a1.mp4#289b358d323b4f644bada1bb1355d7c2, activity video, 20240318140303, Zhang San

[0080] Among them, 289b358d323b4f644bada1bb1355d7c2 is the MD5 value of a1.mp4.

[0081] The specific methods for storing and updating video files are as follows:

[0082] When storing the first version of a video file, the application works as follows:

[0083] Calculate the MD5 value for a1.mp4;

[0084] Store in the database table according to the above format;

[0085] The a1.mp4 file is stored in the opt / video directory.

[0086] When the application accesses the first version of the video file, the application works as follows:

[0087] According to the user's access request, the fields in the database table are accessed and the video file name and its first version are displayed.

[0088] When the user clicks on the first version of the video file, the MD5 value of the video file stored in the file system is first calculated. If it is inconsistent with the MD5 value extracted from the database, an error is reported; if they are consistent, the video file is loaded for the user.

[0089] When the database stores subsequent versions of the video file, the application works as follows:

[0090] Rename the modified a1.mp4 file to a1.mp4.n, where n is the version number, and store it in the opt / video directory

[0091] Calculate the MD5 value of a1.mp4.n;

[0092] Store in the database table according to the above format;

[0093] When the application accesses subsequent versions of the video file, the application works as follows:

[0094] Access the fields in the database table and display the video file name and its versions.

[0095] When a user clicks on a certain version of a video file, the MD5 value of the version of the video file stored in the file system is first calculated. If it is inconsistent with the MD5 value extracted from the database, an error is reported; if they are consistent, the video file is loaded for the user.

[0096] This solution utilizes the existing relational database management system and database read-write separation cluster, and designs a read request load balancing algorithm and write request concurrent access conflict resolution based on the needs of multi-user concurrent access, to ensure request response efficiency and data consistency under multi-user concurrent access.

[0097] The application server plus database read-write separation cluster system includes a safe backup mode. In the safe backup mode, when the database master server fails, one of the database read-only servers is selected from multiple database read-only servers based on the election algorithm as the database master server;

[0098] Reference Figure 2 As shown, the election algorithm is:

[0099] Get the communication delay between multiple database read-only servers;

[0100] Based on the communication delays between multiple database read-only servers, the sum of the communication delays between each database read-only server and other database read-only servers is calculated as the central service indicator of the database read-only server;

[0101] Get the service life of each database read-only server;

[0102] Obtain several database read-only servers of the same model as sample servers;

[0103] Obtain the ratio of the number of sample servers that have experienced failures during their service life to the total number of sample servers as the failure probability of the database read-only server;

[0104] Calculate the election index of the database read-only server based on the election formula;

[0105] Select the database read-only server with the largest election index as the database master server;

[0106] The specific election formula is:

[0107]

[0108] In the formula, is the election index of the read-only server of the i-th database, is the central service indicator of the i-th database read-only server, is the failure probability of the central service indicator of the i-th database read-only server, is the total number of database read-only servers. It is the minimum value of the central service index of multiple database read-only servers. It is the maximum value among the central service indicators of multiple database read-only servers. is the minimum value of the failure probability of the central service indicator of multiple database read-only servers. The maximum value of the failure probability of the central service indicator for multiple database read-only servers.

[0109] Since multiple database read-only servers can be configured, access requests from multiple users can be distributed to multiple servers for processing, achieving high system performance; since each database server has its own storage system that stores copies of video files, data recovery can be performed in the event of a storage failure, thereby achieving high system reliability; since there are multiple database read-only servers, when a database read-only server fails, it will not affect user use. When the database master server fails, multiple database read-only servers will "elect" one of the database read-only servers as the database master server, and the system can still operate normally, thereby achieving high system availability.

[0110] When electing the database master server, full consideration is given to the performance of the database read-only server and the communication performance with other database read-only servers, ensuring the optimal database master server determination under a two-tier evaluation of ensuring system operation stability and data communication consistency between systems.

[0111] Reference Figure 3 As shown, the video access request specifically includes:

[0112] If the number of video access requests is equal to 1, no response will be made;

[0113] If the number of video access requests is greater than 1, determine whether the number of write requests is greater than 1. If so, add a label to each write request in chronological order. If not, do not respond.

[0114] Reference Figure 4 As shown, if the video access request is a write request, accessing the database master server specifically includes:

[0115] If the number of write requests is equal to 1, the database master server is accessed for the write request. If the number of write requests is greater than 1, the database master server is accessed for each write request in sequence according to the label order of the multiple write requests.

[0116] Perform video file verification based on write requests;

[0117] If the verification passes, the video file is loaded based on the write request, and the new version of the video file generated by the write request is updated for subsequent write requests;

[0118] If the verification fails, an error is reported.

[0119] Since relational databases have the basic characteristics of ACID (atomicity, consistency, isolation, and persistence of transactions), this characteristic can be used to solve the data consistency problem when multiple users access the database.

[0120] When multiple users access a version of the same video file at the same time, the database will first determine whether it is a read operation or a write operation. If it is a read operation, the database will assign these users' access requests to the read-only server, easily solving the concurrent reading of video files by multiple users;

[0121] If multiple users write at the same time, the database will respond to the first user's write operation, and other users will be in a waiting state. The system will give a prompt "X file X version is being saved as a new version by X user. Please choose whether to edit on the new version according to the situation." These users will soon see a summary of the new version and decide whether to edit on the new version based on the summary. If they do not edit on the new version, they can save it as a new version (the first user's write operation has been completed); if they choose to edit on the new version, they need to call the new version video file, edit it, and save it as a new version.

[0122] Reference Figure 5 As shown in the figure, the load balancing algorithm is as follows:

[0123] Obtain the used loads of all database read-only servers, and take the sum of the used loads of all database read-only servers as the total used load;

[0124] Based on all read requests, determine the expected load of each read request, and take the sum of the expected loads of all read requests as the total read request load;

[0125] The sum of used load and read request load is added together to obtain the expected total load. The expected total load is divided by the total number of database read-only servers to obtain the nominal load of the database read-only servers.

[0126] Generate at least one read request allocation plan under the conditions that all read requests are allocated with database read-only servers and each database read-only server has not reached its load upper limit;

[0127] Based on the load evaluation formula, evaluate the load balancing indicators of all read request allocation schemes;

[0128] Filter out the read request allocation scheme with the smallest load balancing index and record it as the optimal read request allocation scheme;

[0129] Allocate a database read-only server for each read request based on the optimal read request allocation scheme;

[0130] The load assessment formula is as follows:

[0131]

[0132] In the formula, The load balancing indicator for the read request allocation scheme, is the load of the i-th database read-only server after accessing the database read-only server according to the read request allocation scheme, The nominal load of a database read-only server.

[0133] Through the load balancing algorithm, the load of each database read-only server in the perfect balance state is comprehensively evaluated with multiple read request access requirements and the inherent load of the database read-only server itself as the nominal load, and based on the standard deviation evaluation principle, the deviation value between the load of the database read-only server and the nominal load under multiple read request allocation schemes is calculated as the load balancing index of the read request allocation scheme. The smaller the load balancing index is, the smaller the deviation between the load of each database read-only server under the read request allocation scheme and the nominal load is, and the more balanced the load distribution of each read request allocation scheme is.

[0134] Accessing the corresponding database read-only server specifically includes:

[0135] Perform video file verification based on read requests;

[0136] If the verification passes, the video file is loaded based on the read request.

[0137] Reference Figure 6 As shown, the video file verification specifically includes:

[0138] Determine the video file targeted by the write request or the read request, and record it as the video file to be called;

[0139] Extract the MD5 value of the video file to be called from the relational database table and record it as the base MD5 value;

[0140] Calculate the MD5 value of the video file to be called stored in the database master server or the database read-only server, and record it as the real-time MD5 value;

[0141] Determine whether the reference MD5 value is equal to the real-time MD5 value. If so, the verification passes; if not, the verification fails.

[0142] When using a relational database to store video files, since video files are often large, they are rarely stored directly as BLOB objects in relational database tables. Instead, the storage address of the video file is stored in the table, and the actual video file is stored in the operating system. This brings a problem, that is, when the video file changes for some reason, the database is not aware of it, and accessing the video file through the database may result in incorrect results.

[0143] To solve this problem, the present invention adopts the method of storing the numerical signature of the video file (such as the more common MD5 value) in the relational database table. When the user accesses the video file, the MD5 value of the video file is calculated in real time and compared with the MD5 value in the relational database table. If they are consistent, it means that the video file stored in the operating system has not been damaged or tampered with; if they are inconsistent, it means that the video file stored in the operating system has been damaged or tampered with.

[0144] Furthermore, the method according to the embodiment of the present application can also be performed by Figure 7 The electronic device architecture shown in FIG. Figure 7 As shown, the electronic device 500 may include a bus 501, one or more CPUs 502, a read-only memory (ROM) 503, a random access memory (RAM) 504, a communication port 505 connected to a network, an input / output component 506, a hard disk 507, etc. The storage device in the electronic device 500, such as the ROM 503 or the hard disk 507, may store a method for storing video files in a relational database based on version sequence provided by the present application. The electronic device 500 may also include a user interface 508. Of course, Figure 7 The architecture shown is only exemplary and can be omitted according to actual needs when implementing different devices. Figure 7 One or more components of an electronic device are shown.

[0145] Figure 8 Schematic diagram of the computer-readable storage medium structure provided by an embodiment of the present application. Figure 8 As shown, a computer-readable storage medium 600 according to an embodiment of the present application is shown. A computer-readable program is stored on the computer-readable storage medium 600. When the computer-readable program is executed by the processor, a method for storing video files in a relational database based on a version sequence according to an embodiment of the present application described with reference to the above figures can be executed. The storage medium 600 includes, but is not limited to, for example, volatile memory and / or non-volatile memory. Volatile memory may include, for example, random access memory (RAM) and cache memory (cache), etc. Non-volatile memory may include, for example, read-only memory (ROM), hard disk, flash memory, etc.

[0146] In summary, the advantages of the present invention are: solving the problems of version management, security management, multi-user concurrent access conflict resolution, high performance, high reliability, etc. for massive video files.

[0147] The above shows and describes the basic principles, main features and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The above embodiments and descriptions only describe the principles of the present invention. The present invention may be subject to various changes and improvements without departing from the spirit and scope of the present invention. These changes and improvements fall within the scope of the present invention. The scope of protection claimed by the present invention is defined by the attached claims and their equivalents.

Claims

1. A method for storing video files in a relational database based on version sequence, characterized in that: include: Constructing an application server plus a database read-write separation cluster system, wherein the application server plus a database read-write separation cluster system comprises at least one database master server and at least two database read-only servers, wherein the database master server executes video file read requests and video file write requests, and the database read-only server executes video file read requests; Obtaining information of the video file to be stored, calculating the MD5 value of the video file to be stored, storing the MD5 value of the video file to be stored in a relational database table according to a set format, and storing the video file to be stored in a video storage area; Get video access request; If the video access request is a write request, the database master server is accessed, and the video file stored in the database master server after the write request is executed is synchronized with all database read-only servers; If the video access request is a read request, a database read-only server is allocated to the read request based on a load balancing algorithm, and the corresponding database read-only server is accessed; the application server plus database read-write separation cluster system includes a safe backup mode, and the safe backup mode is that when a database master server fails, one of the database read-only servers is selected from multiple database read-only servers based on an election algorithm as the database master server; The election algorithm is: Get the communication delay between multiple database read-only servers; Based on the communication delays between multiple database read-only servers, the sum of the communication delays between each database read-only server and other database read-only servers is calculated as the central service indicator of the database read-only server; Get the service life of each database read-only server; Obtain several database read-only servers of the same model as sample servers; Obtain the ratio of the number of sample servers that have experienced failures during their service life to the total number of sample servers as the failure probability of the database read-only server; Calculate the election index of the database read-only server based on the election formula; Select the database read-only server with the largest election index as the database master server; The election formula is specifically: In the formula, is the election index of the read-only server of the i-th database, is the central service indicator of the i-th database read-only server, is the failure probability of the central service indicator of the i-th database read-only server, is the total number of database read-only servers. It is the minimum value of the central service index of multiple database read-only servers. It is the maximum value among the central service indicators of multiple database read-only servers. is the minimum value of the failure probability of the central service indicator of multiple database read-only servers. is the maximum value of the failure probability of the central service indicator of multiple database read-only servers; the obtaining of the video access request specifically includes: If the number of video access requests is equal to 1, no response will be made; If the number of video access requests is greater than 1, determine whether the number of write requests is greater than 1. If so, add a label to each write request in chronological order. If not, do not respond. If the video access request is a write request, accessing the database master server specifically includes: If the number of write requests is equal to 1, the database master server is accessed for the write request. If the number of write requests is greater than 1, the database master server is accessed for each write request in sequence according to the label order of the multiple write requests. Perform video file verification based on write requests; If the verification passes, the video file is loaded based on the write request, and the new version of the video file generated by the write request is updated for subsequent write requests; If the verification fails, an error is reported.

2. The method for storing video files in a relational database based on version sequence according to claim 1, characterized in that: The load balancing algorithm is specifically as follows: Obtain the used loads of all database read-only servers, and take the sum of the used loads of all database read-only servers as the total used load; Based on all read requests, determine the expected load of each read request, and take the sum of the expected loads of all read requests as the total read request load; The sum of used load and read request load is added together to obtain the expected total load. The expected total load is divided by the total number of database read-only servers to obtain the nominal load of the database read-only servers. Generate at least one read request allocation plan under the conditions that all read requests are allocated with database read-only servers and each database read-only server has not reached its load upper limit; Based on the load evaluation formula, evaluate the load balancing indicators of all read request allocation schemes; Filter out the read request allocation scheme with the smallest load balancing index and record it as the optimal read request allocation scheme; Allocate a database read-only server for each read request based on the optimal read request allocation scheme; The load evaluation formula is specifically: In the formula, The load balancing indicator for the read request allocation scheme, is the load of the i-th database read-only server after accessing the database read-only server according to the read request allocation scheme, The nominal load of a database read-only server.

3. The method for storing video files in a relational database based on version sequence according to claim 2, characterized in that: The access to the corresponding database read-only server specifically includes: Perform video file verification based on read requests; If the verification passes, the video file is loaded based on the read request.

4. The method for storing video files in a relational database based on version sequence according to claim 3, characterized in that: The video file verification specifically includes: Determine the video file targeted by the write request or the read request, and record it as the video file to be called; Extract the MD5 value of the video file to be called from the relational database table and record it as the base MD5 value; Calculate the MD5 value of the video file to be called stored in the database master server or the database read-only server, and record it as the real-time MD5 value; Determine whether the reference MD5 value is equal to the real-time MD5 value. If so, the verification passes; if not, the verification fails.

5. An electronic device, characterized in that: include: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the method for storing video files in a relational database based on version sequences as described in any one of claims 1-4.

6. A computer-readable storage medium storing a computer-readable program, characterized in that: When the computer-readable program is executed by a processor, the method for storing video files in a relational database based on version sequences according to any one of claims 1 to 4 is implemented.

Images