Re: GSoC proposal for pgAdmin 4 bytea support

Thank you Dave, I have modified my proposal according to your feedback on
detecting media types.

Haoran

On Mon, Apr 1, 2019 at 2:09 AM Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Mon, Apr 1, 2019 at 3:12 AM Haoran Yu <haleyyew(at)gmail(dot)com> wrote:
>
>> Dear PostgreSQL community,
>>
>> I have submitted a proposal for the project pgAdmin 4 bytea support. The
>> project discusses storing media content (images, audio, video) as bytea.
>> However, I have a quick question. What does bytea data look like typically
>> when storing media content? What I had in mind is, media contents that uses
>> MIME type, which are rendered as part of HTML. For example, the following
>> is rendered as a red dot:
>>
>> 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAUA
>> AAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO
>> 9TXL0Y4OHwAAAABJRU5ErkJggg==’
>>
>> This string is decoded to bytea, and I stored it in a bytea column.
>>
>> What are some other examples of using bytea to store media content, not
>> necessarily using the MIME type? Is there a way to detect the type of these
>> media (audio, image) stored in bytea?
>>
>
> When I have stored small media items in bytea columns in the past, I just
> stored the data. I vaguely recall I did store the mime type in another
> column, but that may not be the case in all scenarios (e.g. when a system
> is designed to store only PNGs). I think you should assume it's raw data
> only, and try to determine the file type by examining the data;
>
> e.g
>
> PNG files have an 8 byte signature:
> http://www.libpng.org/pub/png/spec/1.2/PNG-Structure.html
> MPEG files have identifying information in the frame header that you may
> be able to use: http://mpgedit.org/mpgedit/mpeg_format/MP3Format.html
> JPEG images have identifying markers:
> https://en.wikipedia.org/wiki/JPEG_File_Interchange_Format
>
> etc.
>
>
>> Another question I had is, I read that there are performance-related
>> issues for storing media in bytea. Are there practical ways to store bytea
>> data that does not face performance-related issues? For example, storing
>> large media content using multiple bytea parts, and reassembling them
>> together once retrieved from the database?
>>
>
> Not that I'm aware of. For larger objects, most people store them
> externally (which of course loses ACID properties). There are certainly
> applications for storing smaller objects directly in the database though -
> and some folks have done work in the past with index types and
> operators/functions for finding and comparing images for example, so there
> are also benefits other than ACID to storing data in this way.
>
> BTW; for pgAdmin related GSoC questions, you'd do better to ask on
> pgadmin-hackers(at)postgresql(dot)org(dot)
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>