What is the best way to store videos, audios, images, documents in a bank?

Question

I want to create a repository system where people can store videos, audios, pdfs, images etc. I will probably use PGSQL and want to know what is the best strategy for storing this data that can be different in size and type.

From now on, thank you.

Answer 1

The simplest solution is to store the file in a directory (folder) and the location (or other reference to this file) in the database. For example, let’s say you go store PDF files. You can save the documents in a directory called "Pdfs" and create a table with id and location of these documents. As in id:0001, Uri:Pdfs/document_0001.pdf

You could use a software like Dspace, for example.

Answer 2

You can store the contents of a file in a column of type BYTEA let’s see:

CREATE TABLE tb_arquivo
(
    id BIGSERIAL PRIMARY KEY, -- Identificador unico do arquivo
    nome TEXT NOT NULL,       -- Nome/Path original do arquivo
    conteudo BYTEA NOT NULL,  -- Conteudo do arquivo
    tamanho BIGINT NOT NULL,  -- Tamanho total do arquivo
    md5 TEXT NOT NULL         -- Assinatura MD5 do arquivo
);

The contents of the file can be read through the function pg_read_binary_file() and its attributes can be obtained through the function pg_stat_file().

Assuming the file is written to the disk of the database server in its directory PG_DATA:

Recording a file in this table would be something like:

INSERT INTO
    tb_arquivo ( nome, conteudo, tamanho, md5 )
VALUES
    ( 'imagem.png',
    pg_read_binary_file('imagem.png'),
    (pg_stat_file('imagem.png')).size,
    md5(pg_read_binary_file('imagem.png')) );

Answer 3

1. Stores files on the server and catalogs in the database.
2. To ensure the integrity of the files stored in the directories can be generated a hash, save in the bank, and compare at the time of the query.
3. To view upload files via code.