Currently my PrintJobHistory plugin use a local sqlite3 database for storing the print job attributes, except the thumbnail and camera snapshot images. These images were located in the plugin data directory as single jpg files
I want to add an option to use an external database located on a different server. Now I am thinking of moving also the image data to the database. But what are the drawbacks:
Browser-Performance: Currently you can list more then 200 Table-Entries with images and scroll thru that list "quickly". Because the image data is directly streamed from the filesystem.
Do you have experience or an opinion how I should handle storing the images and also providing fast responses.
Maybe setup a cache during startup. Loading images from the database into the Octoprint-Server, instead of "on demand".
I've dealt with images stored externally in a database/web application and it wasn't too bad. You definitely see a performance hit compared to loading directly from the server because there is the extra hop of communication. The pre-fetch approach would probably be a good idea. Another option that might actually work is re-encoding the images to base64 png strings and store that instead? I'm not sure of the performance difference between pulling string data versus binary data, but I guess that would depend on the database engine and it's capabilities.
What is the base64 encoding for? It enlarges the data to 4/3 (+1/3), this additional data has to be transferred to/from the database and also stored.
If binary data isn't feasible, better use an encoding using 7 of 8 bits instead of only 6 of 8 if there is no mandatory check of valid uft-8 in the data-path. This enlarges the data only to 8/7 (+1/7).
It could save you processing speed at the cost of data size. Consider this... Persist base64 data in the database, use those strings directly as data URL in the image tags, which all current browsers should support just fine. Saves you one request roundtrip and/or on the fly base64 encoding on reading from the database per image. Considering resource limitations on stuff like a raspberry pi that might actually be not that bad of an idea.
I thought it was about a plugin storing and retrieving images to/from a database and the performance differences there may be between binary data and strings.
Persist base64 data in the database, use those strings directly as data URL in the image tags
I don't know if I understand you correctly. Use the base64 encoded image as part of an URL? The complete 50+KB?
I've written a short encode and decode in C for 7/8 bits and it uses half a second for the 13 mb /boot/kernel8.img. I guessed before it would be quicker. And the {en,de}coding dwarfed the loading time from the filesystem.
The experiment shows you are right, if one can leave the decoding of base64 to the browser it is way better.
I'm not experienced with web-programming. If you send a picture to the browser, do you have to encode it in base64?
No you don't have to, but this is how you do it. The below image is not an image...it's a base64 encoded string. I don't know if any other base encodings are supported by browsers. Right-click and select inspect. I tried to do the OctoPrint logo, but it exceeded the character limit of a post.
I would suspect it would depend on the database engine how well it performs storing large amounts of simple text versus binary data, and then dealing with how to access that binary data.
No problem, it's actually how Prusa is embedding images in gcode for the Prusa Mini (and any other printer you manually add the thumbnail setting for) and I use to extract the image on upload into my plugin's data folder.
So, I will store the images as a blob in the database and maybe (after performance test) I will create a local file storage as a cache.
...and unfortunately I need to shrink all images before storing. Currently the user can upload e.g. 20MB picture.
