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 <dp...@pgadmin.org> wrote: > Hi > > On Mon, Apr 1, 2019 at 3:12 AM Haoran Yu <haley...@gmail.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-hack...@postgresql.org. > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
