storing images in the database

[FBachofner]

I developed a website which has a ton of images.

It is based on a homegrown CMS which uses a PHPMaker generated CRUD system on the backend to manage data within a relatively complex enterprise datamodel, the majority of which is not yet (and may never be) exposed on the web.

The images are stored in the database as BLOBs (not as files in the filesystem with the filename and path stored in the DB).  I know some developers don't like doing this, but we have a number of business reasons for doing so, not the least of which is ease of synchronizing data between the enterprise DB (full of even more images) and the web DB.

Compared with other CMSes, ProcessWire seems to have a very elegant way of dealing with images, but the default way is for the images to be stored in the filesystem in a subdirectory of the page where the images are used.  [Here is just another reason we want those images in the DB -- our images can, in theory appear in a number of pages, so we don't want them conceptually related to a page (or duplicated into other directories)].

Anyway, my questions are:

1. Is it relatively easy for ProccessWire to deal with images stored in the DB?
2. Is there a BLOB fieldtype (sorry, haven't installed PW yet, but the fieldtypes page seems to indicate BLOB is not available "out of the box")?  I can't seem to find it the additional fieldtype modules either.
3. Does the API have built in methods for inserting into and displaying images from BLOBs (a look at the cheatsheet seems to indicate no)?
4. If any of the foregoing are readily possible, does ProcessWire deal "easily" with a second DB (assuming I do not want to commingle enterprise data with website data -- of course suppose I could always synchronize relevant data to the PW DB . . .)

I don't think I'll be migrating the existing site, but I can envision PW being a great tool for creating similar sites without having to reinvent the wheel and making a custom CMS each time.

I just don't want to be constantly "fighting" the default behavior ProcessWire has for images.  Given PW's flexibility, I suppose I could hand code the PHP to deal with this but am hoping for a simpler way.

Your insights would be much appreciated! 

[BitPoet]

1. Relatively easy, yes, as in no harder than dealing with them if you build a full solution from scratch.

2. No, there isn't. A fieldtype for that would need to be tailored to a certain implementation logic.

3. There's nothing specific in the api, no, but if you build a field type for it, you can use all the field selectors together with a few lines of PHP to set headers, etag etc. based on the field value to output an image and hook into the existing logic of pagefile/pageimage to add your db inserts on top of PW's builtin upload logic.

4. You can either use PW's database interface (class Database, which is just a small wrapper around mysqli), use plain PDO or include your own database access class.

PW has a few reasons for adding images to pages, one of the being security - if you have $config->pagefileSecure enabled and define access restrictions to a page/template, you implicitly limit access to its files too. Any option to store files completely outside PW's page structure will break that relationship and require you to limit access rights on a per-image basis (or to implement your own permissions logic that checks access rights with the pages the images are used in, meaning you still need to "assign" images to pages somehow).

I do understand the wish to provide some kind of "image pool" though - it's, in fact, one of the next things I have to tackle for our company's intranet, as we have a lot of repeating images and documents in the technical manuals section there that need to be kept in sync. I'll probably introduce some kind of "files library" template, add library pages in places in the tree wherever security settings change and extend the pwlink and pwimage plugins for CKEditor to allow easy picking and inline uploading. You'd need to do something similar to be able to pick your external images.

As for outputting files/images not stored in the usual location under the page's directory in assets/files, the code for FieldtypeSecureFile (which stores page files outside of the web root) is worth looking at.

[LostKobrakai]

Just a small addition. ProcessWire does provide a pdo instance as well: $database vs $db (mysqli). 

@FBachofner

You can probably take most of the code from FieldtypeSecureFile and you'll see how FieldtypeFile can be extended. You'd want to do mostly the same, but instead of rendering files from a non-webroot folder you'd render files from the db. 

Edited February 28, 2016 by LostKobrakai
Fixed variables

[kongondo]

Just a small addition. ProcessWire does provide a pdo instance as well ($db, instead of $database). 

Shouldn't this be the other way round?

$db; // refers to mysqli

$database; // refers to PDO

[LostKobrakai]

Yeah. I corrected my post.

[FBachofner]

Hi BitPoet:
 
 
Thanks for your very detailed answer!
 

PW has a few reasons for adding images to pages, one of the being security - if you have $config->pagefileSecure enabled and define access restrictions to a page/template, you implicitly limit access to its files too. Any option to store files completely outside PW's page structure will break that relationship and require you to limit access rights on a per-image basis (or to implement your own permissions logic that checks access rights with the pages the images are used in, meaning you still need to "assign" images to pages somehow).

In the case of the site I described, site editors have little ability to "choose" an image.

Where images are related to artists (it is a gallery website), all images for the artist are shown on that artist's page (site visitor can page, sort, etc. -- although that functionality is currently turned off and under review for upgrade).

Where a press release is published by a site editor, the editor can select one (and currently only one, to match with the physical, single page press releases) image for display with that press release.

So too with "teaser images" for current and upcoming exhibits . . .

In the full exhibitions module (also currently turned off and under review for upgrade), all images associated with a particular exhibition were displayed -- the site editor's purview extends to not associate an image with an exhibition, that's about it.

Pretty much all of the image display logic is controlled by record relationships in the DB datamodel and it should match the enterprise data, which makes publishing the website childs-play (at least in terms of not having to think much about which content needs to be published, etc.).

Your comments about security are interesting and well taken.

In our situation there is no "security" for site viewers -- anything published is viewable by anyone.

For editors, the DB restricts record(s) deletion to its owner (and admins) but we are significantly more permissive with edits.  Pretty much any editor can edit content -- but it is tracked so there is accountability.  I am considering changing this and expect (as first order of business) that I will soon I will enable "deletions," but it is going to be by setting a field ("MRAD" - mark record as deleted) to true and then no longer allowing that content to be displayed (or even edited) by anyone except the record owner and admins (of course).  The further expectation is that records flagged MRAD will be deleted by cron job every so often (maybe MRAD >= 2 months?) -- giving a fair chance at "undo" if required.

You'd need to do something similar to be able to pick your external images.

The above gives an idea of how that particular site does it.  As above, in any other site, I would imagine associating a page with an image (or images) by setting up a relationship and running a query.
 

As for outputting files/images not stored in the usual location under the page's directory in assets/files, the code for FieldtypeSecureFile (which stores page files outside of the web root) is worth looking at.

Thanks, I'll take a look at that.

[FBachofner]

Hi LostKobrakai:

You can probably take most of the code from FieldtypeSecureFile and you'll see how FieldtypeFile can be extended. You'd want to do mostly the same, but instead of rendering files from a non-webroot folder you'd render files from the db. 

Thanks, I'll take a look at that.  It seems PW can accomplish just about anything you want.

My main concern with CMSes (and many frameworks) is that you "buy in" to a datamodel (and implementation schema) which may not suit oneself conceptually or practically.  For example, I am generally not a fan of storing images in the filesystem (except, perhaps, for images used site-wide -- i.e. logos, etc.).  [That said, I probably understand many (if not all) the reasons Ryan had for making that choice. ]

Furthermore, adding tables to the schema associated with the datamodel may create significant problems when upgrading the CMS in the future.  [ This is why I asked how "easy" it is to adapt PW to the use of multiple databases. ]

On a personal level, I am trying to wrap my head around the fact that the vast majority of sites I work on do not need registered users and are thus conceptually simple to create using static HTML and/or homegrown PHP (pulling some -- generally iterative -- content from DB) along with a nice front end framework like Foundation for layout and certain dynamic aspects.

The known/experienced exceptions to this (where a "real" CMS may be a significant benefit for me) is where:

1. "basic" pages (home, about, that sort of thing) need frequent editing [this sort of page is usually not dealt with in our datamodels (and corresponding CRUD tool), thus requiring a site admin to make edits to HTML and/or PHP code (this can get old fast when a client likes to change things around a lot -- then again, if it is more than just text changes (i.e. images, layout), they usually wouldn't be able deal with it themselves anyway!)
2. sites where
   1. pages often require multiple layouts, not just "selectable" but easily changeable by editors (who have some HTML / framework knowledge (contradicted by point #1 above, but I myself fall under the category "editor," so . . . it would be nice to be able to edit pages right in the browser)
   2. pages have a variety of content structures
3. "pretty" URLs are required for all pages [ I probably just need to rethink usage of the datamodel for this and read up on how CMSes generally deal with this ("slugs"?) ]
4. a site which might in the near future need to become (sticky) customizeable and/or go "transactional" (i.e. shopping)
   1. the first can be dealt with to a degree without a registration system (but not across browsers / hosts)
   2. the second certainly requires a registration system and/or a 3rd party vendor for a shopping cart
5. there are undoubtedly other reasons, but those would be edge cases for me right now

The gallery site I described in this thread sells fine art.  The owner (my mom ) does not yet want to sell online through her own site as most of the artwork is "expensive" (that's relative, I suppose) and deserves in-person viewing before purchase.  Even so, I think that day is (certainly should be!) coming.  She is at the very least potentially losing sales from people who live at a distance and previously saw an artwork in person, right?!

Anyway, at such point I think I will want to avoid coding a (complex, secure, reliable) user authentication and shopping cart system (or interaction with one).  It seems PW may be the way to go to avoid reinventing the wheel.  I suppose a framework like Cake, Yii, Laravel or similar would fit the bill too -- and might allow easier customization?  [ I hear Laravel is a resource pig though -- so that is a concern (and a real issue I have with PW competitor OctoberCMS which seems near perfect in other regards). ]

Comments / insights appreciated.

[szabesz]

@FBachofner: Have you already checked out padloper.pw? Should you chooses ProcessWire, you might be interested in it when the time comes:

https://www.padloper.pw

[FBachofner]

@FBachofner: Have you already checked out padloper.pw?

Thanks.  I'll keep it in mind.