Method for importing and exporting file data based on postgresql

Abstract

The application provides a file data import and export method based on PostgreSQL, and the import method comprises the following steps: S10, creating a PostgreSQL data table, wherein the PostgreSQL data table comprises a to-be-imported file column in a preset data type; S20, importing the to-be-imported file into the to-be-imported file column in the preset data type through a pre-created import function; wherein the preset data type displays information of the to-be-imported file column, and the information at least comprises text description information of the to-be-imported file. The application solves the problem that there is no data type specially used for storing files in the PostgreSQL database, and the data query result displays the creation time, size, check value and description of the file, which is easier to understand than the existing bytea binary content. In the case that the disk space is allowed, the file data type has no size limit, thereby solving the problem that the bytea binary type has a size limit of 1 GB. The file corresponding to the file data type is directly stored on the disk, and if backup is needed, direct copying can be performed.

Description

Technical Field

[0001] This invention relates to the field of PostgreSQL technology, and in particular to a method for importing and exporting file data based on PostgreSQL. Background Technology

[0002] As an open-source database product, PostgreSQL is most commonly used for storing readable information, such as a students table that stores basic student information.

[0003] 1 Xiaoming male 12 2 Xiao Li male 13 3 Xiao Hong female 11

[0004] The information in the table above is all directly readable text. If now the requirement is for each student to record a self-introduction video and save it to the table, it's clear that videos are not text information and cannot be directly added to the table.

[0005] PostgreSQL does not have a dedicated data type for storing files. Under current technology, if you want to store files, you can only use PostgreSQL's built-in binary data type, `bytea`. Using `bytea` for file storage will result in the following example:

[0006] 1 Xiaoming male 12 01010111011001010110 2 Xiao Li male 13 11000110001101101111 3 Xiao Hong female 11 10111101101110011010

[0007] As can be seen from the table, although the video is stored using the byta type, because the video file is a binary file, it can only be displayed as a string composed of 0s and 1s. It should be noted that the string length is not fixed; the larger the file, the longer the string.

[0008] It is evident that PostgreSQL, in its current technology, suffers from the following technical problems:

[0009] 1. Currently, there is no data type specifically designed for storing files in PostgreSQL databases.

[0010] 2. The alternative storage method, byeta, has the following disadvantages:

[0011] (1) The binary content of bytea can only be displayed as a combination of 0 and 1, which is not easy to understand;

[0012] (2) Binary data is generally quite large, so direct query may result in a screen full of 0s and 1s, which is extremely unfriendly to querying and reading.

[0013] (3) The binary type of bytea has a size limit of 1GB;

[0014] (4) The byta binary type is not friendly to database backup and recovery. Summary of the Invention

[0015] The technical problem to be solved by this invention is to solve the problem of importing and exporting file data in PostgreSQL. This invention proposes a method for importing and exporting file data based on PostgreSQL.

[0016] The file data import method based on PostgreSQL according to an embodiment of the present invention includes:

[0017] S10, Create a PostgreSQL data table, which includes a column for files to be imported using a preset data type;

[0018] S20, using a pre-created import function, the file to be imported is imported into the file to be imported field with the preset data type;

[0019] The preset data type displayed in the file to be imported section includes at least the textual description of the file to be imported.

[0020] According to some embodiments of the present invention, before creating the PostgreSQL data table, the method further includes:

[0021] S08. Based on the file to be imported, create the preset data type. The content of the preset data type includes: file ID, file creation time, file size, file MD5 checksum, and file description. The file description uses text information to describe the file to be imported.

[0022] In some embodiments of the present invention, before creating the PostgreSQL data table, the method further includes:

[0023] S06, Receive the file to be imported from the client.

[0024] According to some embodiments of the present invention, step S06 specifically includes:

[0025] S061, Receive the data import request sent by the client and perform verification;

[0026] S062, After successful verification, receive the file to be imported sent by the client.

[0027] In some embodiments of the present invention, in step S062, during the file to be imported transfer process, the file is divided into multiple network data packets of a preset size and sent multiple times. When the network data packets received by the memory cache meet the preset conditions, the received network data packets are stored on the disk.

[0028] According to some embodiments of the present invention, the method further includes:

[0029] S30, Create a system table to store all file information saved according to the preset data type.

[0030] In some embodiments of the present invention, the pre-created import function is: pg_import_file(file_path TEXT, description TEXT), where file_path TEXT is the path to the file to be imported, and description TEXT is the text description information of the file to be imported.

[0031] According to some embodiments of the present invention, the files to be imported include: video files, audio files, and text files.

[0032] According to an embodiment of the present invention, a file data export method based on PostgreSQL is provided. The method exports files from a PostgreSQL database, wherein the data tables in the PostgreSQL database are imported using the file data import method based on PostgreSQL as described above. The export method includes:

[0033] A10 exports files from the PostgreSQL database using a pre-created export function.

[0034] According to some embodiments of the present invention, the pre-created export function is: pg_export_file(foidOID,file_path TEXT), where the parameter foidOID is the file ID and file_path TEXT is the path where the file is located.

[0035] The present invention has the following beneficial effects:

[0036] This invention solves the problem of PostgreSQL databases lacking a dedicated data type for storing files. Furthermore, the data query results display the file's creation time, size, checksum, and description, making it easier to understand compared to the existing Byta binary data. With sufficient disk space, the `file` data type has no size limit, resolving the 1GB size limit issue of the Byta binary type. Files corresponding to the `file` data type are stored directly on the disk; backups can be easily achieved by copying them. Attached Figure Description

[0037] Figure 1This is a flowchart of a file data import method based on PostgreSQL according to an embodiment of the present invention;

[0038] Figure 2 This is a schematic diagram illustrating the composition of a preset data type according to an embodiment of the present invention;

[0039] Figure 3 This is an example diagram of a file data import method based on PostgreSQL according to an embodiment of the present invention;

[0040] Figure 4 This is an example diagram of a file data query method based on PostgreSQL according to an embodiment of the present invention;

[0041] Figure 5 This is an example diagram of a file data export method based on PostgreSQL according to an embodiment of the present invention;

[0042] Figure 6 This is a schematic diagram of file data transmission according to an embodiment of the present invention;

[0043] Figure 7 This is a schematic diagram of network data packet composition according to an embodiment of the present invention;

[0044] Figure 8 This is a schematic diagram of the file data import interaction protocol based on PostgreSQL according to an embodiment of the present invention;

[0045] Figure 9 This is a schematic diagram of the file data export interaction protocol based on PostgreSQL according to an embodiment of the present invention;

[0046] Figures 10-15 This is a schematic diagram illustrating the process of importing a file from a client to a server according to an embodiment of the present invention;

[0047] Figures 16-21 This is a schematic diagram illustrating the process of importing a file from a client to a server according to an embodiment of the present invention. Detailed Implementation

[0048] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments.

[0049] The steps described in the specification and the flowcharts in the accompanying drawings of this invention are not necessarily to be strictly followed according to the step numbers; the execution order of the steps can be changed. Furthermore, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be broken down into multiple steps.

[0050] like Figure 1As shown, the file data import method based on PostgreSQL according to an embodiment of the present invention includes:

[0051] S10, Create a PostgreSQL data table, which includes a column for files to be imported using a preset data type;

[0052] For example, combining Figure 3 As shown, the created PostgreSQL table includes a "video file" column, used to import students' self-introduction videos. In this example, the student's self-introduction video is the file to be imported, and "file" is a preset data type. It is understood that the above example is only for illustrative purposes and the invention is not limited to the embodiments shown in the above example.

[0053] S20: Import the file to be imported into the import file field using a pre-created import function with a preset data type;

[0054] Among them, the information displayed in the "File to be Imported" field for the preset data type includes at least the text description information of the file to be imported.

[0055] The file data import method based on PostgreSQL according to embodiments of the present invention solves the problem that there is no dedicated data type for storing files in the PostgreSQL database. Moreover, relevant file information can be displayed in text description in the PostgreSQL data table, making it easier for users to understand.

[0056] According to some embodiments of the present invention, before creating the PostgreSQL data table, the method further includes:

[0057] S08, based on the file to be imported, creates a preset data type, such as... Figure 2 As shown, the preset data types include: file ID, file creation time, file size, file MD5 checksum, and file description. The file description uses text information to describe the file to be imported.

[0058] Therefore, the creation time, size, checksum, and description of a file can be displayed in the PostgreSQL query results, which is easier to understand than the binary content of the byta used in the existing technology.

[0059] In some embodiments of the present invention, the method further includes, before creating the PostgreSQL data table:

[0060] S06, Receive the file to be imported from the client.

[0061] According to some embodiments of the present invention, step S06 specifically includes:

[0062] S061, Receive the data import request sent by the client and perform verification;

[0063] S062, after successful verification, receive the file to be imported sent by the client.

[0064] In some embodiments of the present invention, such as Figures 10-15 As shown, in step S062, during the file transfer process, the data packets are divided into multiple network data packets according to a preset size and sent in multiple batches. When the network data packets received by the memory cache meet the preset conditions, the received network data packets are stored on the disk.

[0065] According to some embodiments of the present invention, the method further includes:

[0066] S30, Create a system table to store information about all files saved according to a preset data type.

[0067] In some embodiments of the present invention, the pre-created import function is: pg_import_file(file_pathTEXT, description TEXT), where the parameter file_pathTEXT is the path to the file to be imported, and descriptionTEXT is the text description information of the file to be imported.

[0068] According to some embodiments of the present invention, the files to be imported include, but are not limited to, video files, audio files, and text files. That is, the files imported into the PostgreSQL data table in this invention can be video files, audio files, and text files, etc.

[0069] According to an embodiment of the present invention, a file data export method based on PostgreSQL is used to export files from a PostgreSQL database. The data tables in the PostgreSQL database are imported using the file data import method based on PostgreSQL described above. The export method includes:

[0070] A10 exports files from a PostgreSQL database using a pre-created export function.

[0071] According to some embodiments of the present invention, the pre-created export function is: pg_export_file(foid OID, file_path TEXT), where the parameter foid OID is the file ID and file_path TEXT is the path where the file is located.

[0072] The method for importing and exporting file data based on PostgreSQL according to the present invention will be described in detail below with reference to the accompanying drawings. It should be understood that the following description is merely exemplary and should not be construed as a specific limitation of the present invention.

[0073] This invention proposes a new data type, named file, specifically for storing files in PostgreSQL databases, and addresses the shortcomings of the byta storage method.

[0074] This invention designs a file data type specifically for storing file data, which includes, for example, Figure 2 The information content shown.

[0075] This invention designs two dedicated functions for importing and exporting file data types.

[0076] Import function: pg_import_file(file_path TEXT, description TEXT);

[0077] Export function: pg_export_file(foid OID, file_path TEXT);

[0078] This invention designs a dedicated system table pg_files, which stores file information for all file data types stored in the database.

[0079] The method of using this solution is illustrated in the following example. Figures 3-5 As shown:

[0080] In the example above, when using `pg_import_file` to import a file, the file is segmented and converted into network data packets by the client (in this case, psql). Figure 6 As shown.

[0081] This solution designs five types of network data packets for network data transmission, such as... Figure 7 As shown.

[0082] The file import and export process using the network data packets of this invention is as follows: Figure 8 and Figure 9 As shown.

[0083] Figure 8 The interaction protocol for importing data packets specifically includes:

[0084] d1: This indicates that the client sent a data import request to the database, carrying file data.

[0085] e2: This indicates that the server received the data from d1, but after verification, the data was found to be incorrect.

[0086] d3: This indicates that the client received the message from e2 and knew that the data was incorrect, so the data sent by d3 is the same as that sent by d1.

[0087] d4: This indicates that the server received the data from d3 and verified that the data is correct.

[0088] d5: Indicates that the client is sending new file data.

[0089] d6: This indicates that the server received the data from d5 and verified that the data is correct.

[0090] c7: This indicates that the client sends a message to the server indicating that the file sending is complete.

[0091] Figure 9 The interaction protocol for exporting file data specifically includes:

[0092] g1: This indicates that the client has sent a data export request to the database, carrying the file ID to be exported.

[0093] d2: This indicates that the server has received the data from g1 and has started transmitting file data.

[0094] e3: This indicates that the client received the message from d2, but after verification, the data was found to be incorrect.

[0095] d4: This indicates that the server has received the data from e3 and is resending the data.

[0096] d5: This indicates that the client received the data from d4, and after verification, the data is correct.

[0097] d6: Indicates that the server is sending new file data.

[0098] d7: This indicates that the client received the data from d6, and after verification, the data is correct.

[0099] c8: This indicates that the server sends a message to the client indicating that the file sending is complete.

[0100] This invention designs a process for importing data from the client's disk to the server's disk via the network, such as... Figure 10 As shown, the client reads 8kb of file data from the disk into memory, and then sends the data in memory to the server over the network. The server reads the network data and first saves it to its memory cache.

[0101] like Figure 11As shown, the client reads file data from the disk in sequence, with 8 kb of data each time into the memory, and the client discards the data that has been successfully sent before. The new data read from the memory is sent to the server through the network. The server reads the network data and first saves it in the memory cache.

[0102] As Figure 12 shown, the client reads and sends data again according to the previous steps.

[0103] As Figure 13 shown, the client reads and sends several data again according to the previous steps. At the same time, since a part of the data has been cached in the server's memory, in order to avoid insufficient memory space, the server will first write a part of the data to the server's disk.

[0104] As Figure 14 , the last data of the file may be less than 8 kb in size. The client will read the remaining data (assumed to be 5 kb in the figure), and then still read and send the data according to the previous steps.

[0105] As Figure 15 shown, when all the data of the file has been read and sent, the client will notify the server that it has been completed. At the same time, the server will also write the data in the memory to the disk. The process of importing the client's data to the server is completed according to the above steps. The name of the server file is the unique ID assigned by PostgreSQL. The above process corresponds to Figure 3 the execution process of the following SQL statement in

[0106] For the corresponding import, the present invention also designs the processing process from the server disk through the network to the client disk during export, corresponding to Figure 5 the following SQL statement in

[0107] "SELECT pg_export_file(100,' / mnt / 小明的自我介绍视频.mp4')"

[0108] This process is opposite to the import process and will not be described in words here. The process is as Figures 16-21 shown.

[0109] The present invention has the following beneficial effects:

[0110] (1) It solves the problem that there is no data type dedicated to storing files in the postgresql database.

[0111] (2) The query results show the file’s creation time, size, checksum, and description, which are easier to understand than the binary version of the byta.

[0112] (3) When disk space allows, the file data type has no size limit, which solves the problem of the 1GB size limit of the byta binary type.

[0113] (4) Files corresponding to the file data type are stored directly on the disk. If backup is needed, simply copy them.

[0114] Through the description of specific embodiments, a more in-depth and specific understanding should be gained of the technical means and effects adopted by the present invention to achieve the intended purpose. However, the accompanying drawings are only for reference and illustration and are not intended to limit the present invention.

Claims

1. A method for importing file data based on PostgreSQL, characterized in that, include: S10, Create a PostgreSQL data table, which includes a column for files to be imported using a preset data type; S20, using a pre-created import function, the file to be imported is imported into the file to be imported field with the preset data type; The preset data type displayed in the file to be imported section includes at least the text description information of the file to be imported. Before creating the PostgreSQL data table, the method further includes: S08. Based on the file to be imported, create the preset data type. The content of the preset data type includes: file ID, file creation time, file size, file MD5 checksum, and file description. The file description uses text information to describe the file to be imported. The file to be imported involves five types of network data packets during the data import process, including: The first data packet includes: a flag indicating file data, the data packet size, a data checksum, and the file data; The second data packet includes: a flag indicating the file and the completion of the transfer, and the data packet size; The third data packet includes: a flag indicating the file to be exported, the data packet size, and the ID of the file to be exported; The fourth data packet includes: a flag indicating a verification error, and the data packet size; The fifth data packet includes: a flag indicating that the verification has passed, and the data packet size; Before creating the PostgreSQL data table, the method further includes: S06, Receive the file to be imported from the client; Step S06 specifically includes: S061, Receive the data import request sent by the client and perform verification; S062, After successful verification, receive the file to be imported sent by the client; In step S062, during the file transfer process, the file to be imported is divided into multiple network data packets of a preset size and sent in multiple batches. When the network data packets received by the memory cache meet the preset conditions, the received network data packets are stored on the disk.

2. The file data import method based on PostgreSQL according to claim 1, characterized in that, The method further includes: S30, Create a system table to store all file information saved according to the preset data type.

3. The file data import method based on PostgreSQL according to claim 1, characterized in that, The pre-created import function is: pg_import_file(file_path TEXT, description TEXT), where file_path TEXT is the path to the file to be imported, and description TEXT is the text description information of the file to be imported.

4. The file data import method based on PostgreSQL according to claim 1, characterized in that, The files to be imported include: video files, audio files, and text files.

5. A method for exporting file data based on PostgreSQL, characterized in that, The export method exports files from a PostgreSQL database, wherein the data tables in the PostgreSQL database are imported using the file data import method based on PostgreSQL as described in any one of claims 1-4, and the export method includes: A10, uses a pre-created export function to export files from the PostgreSQL database; The pre-created export function is: pg_export_file(foid OID, file_path TEXT), where foid OID is the file ID and file_path TEXT is the path to the file.

Images