Best way to store a PNG to show in a table form column?

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Best way to store a PNG to show in a table form column?

Postby Bernd.N » Thu Feb 23, 2017 5:09 pm

On our contacts table form, we have a calculated field that will show the customer's logo (a PNG) when a contact is his own employee, see code below.
Currently we store the customers logos in the Servoy media, which has the disadvantage that we need to add the logos of new customers to the solution media and then create a new version of the solution.

Now two other options we see are
a) store the needed PNGs in a folder below the Servoy application_server folder, like D:\Servoy7\application_server\server\webapps\ROOT\images
b) store the PNGs in an own database table like images

Which storage option of those three solutions is the best, when you want that calculation to run as fast as possible?
Or said in another way, what PNG will Servoy retrieve fastest?

Code: Select all
function clcs_ps_is_our_employee()
{
         
     if (ps_is_our_employee) {
         
          if (ps_was_our_employee) {

               return '<html> <img src="media:///logos/' + _to_tenants$currenttenantid.tn_icon_small_gray + '"> </html>';
          }
          else {

               return '<html> <img src="media:///logos/' + _to_tenants$currenttenantid.tn_icon_small + '"> </html>';
          }
     }
     else if (ps_is_external_time_booker) {
     
          return '<html> <img src="media:///clock-o_808080_16.png"> </html>';
     }
     else {

          return null;
     }
}
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Best way to store a PNG to show in a table form column?

Postby patrick » Thu Feb 23, 2017 5:16 pm

Media doesn't sound right for this purpose, yes. It's meant for static binaries.

Personally, I don't like file systems. They have access restrictions, you need to make sure it's all there, you have to deal with unique naming etc. So I'd go for the DB. Retrieving these is as fast as it takes the DB to query and transfer the data. I'd probably make sure that I have a thumbnail version of every image in the DB as well, so you don't have to fetch a 5MB image when you only want to shows something in a size as 48x48 pixels.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Best way to store a PNG to show in a table form column?

Postby ROCLASI » Thu Feb 23, 2017 5:36 pm

I agree with Patrick on the Media part.

However I would not store images in the database.
Believe me, I've done it (25.000+ images, thumbnail + low-res + high-res, inside a DB) and it ain't pretty.

Instead store them on the filesystem.
Use the database to keep track of the location, real name, mime-type, etc. of the file and use, for example, a UUID value for the physical filename and store this value in the record. This way all the meta-data of that image is in your database but the binary is on the filesystem. This keeps your database light and fast and your (daily) backup-size sane.
And loading of images will be done via http/https and will be lazy-loaded so your solution still stays fast.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Best way to store a PNG to show in a table form column?

Postby Bernd.N » Thu Feb 23, 2017 5:40 pm

Thanks for your advice. I see both points.
As we need to just store four tiny PNG images (48x48) for a new customer, I would opt for the table storage in this case, as then we do not need to think about those images when transfering the DB one day to another system.

In case that solution will be slower than our current retrieval from Servoy media, we could still switch to the file system storage.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Best way to store a PNG to show in a table form column?

Postby sbutler » Thu Feb 23, 2017 9:04 pm

I use a combination, when deciding I look at a few things. If they are big images, then definitely on the file system, or some CDN/API based storage. If they are acceptable size, then I look to see if they match up with my existing schema (I prefer not to make separate schema to store images).

This ends up with things like company logo's going in the company database table, and other misc stuff on the file system.


PS. Another side note, instead of referencing static images from media, like media:///clock-o_808080_16.png, you might consider using more CSS convention, with the HTML referencing a DIV or something with a CSS class, and then in your CSS, reference the media:///clock-o_808080_16.png as the background image. Makes switching images a bit easier, and can let you do different themes with different icon/image sets by switching to different CSS styles.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Access restriction on file level possible ?

Postby Bernd.N » Thu May 18, 2017 2:24 pm

When storing images in the file system, there is the topic of security and authorisation.
For example think about scans regarding personnel files, or even supplier invoices that should not be accessible to every user.

Is there a technical way to restrict reading access on the level of each file?
As when storing images in a database, access restriction is easy.

What would be needed is kind of a general hiding of all image files, and then showing specific images only to those who are allowed to see them.

For example, a project leader should see only the scans of the supplier invoices that belong to his projects.
Or a department manager should see the personnel files only from those employees in her own department.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 5 guests