Photo Organizer

Perhaps it should be moved into the 'files' table; this way we can keep track of all image sizes independently.

Or... eliminate it entirely, and use the logs to infer it.

Views needs to be associated with the file records. We can create functions to obtain the views_per_photo, views_per_version figures. They'll include everything but the thumbnail.

views_per_photo(photo_id) -- SELECT sum(f.views) FROM files f, photo_version v WHERE v.identifier = f.version AND f.size != 1;
views_per_version(photo_id, version_id) -- SELECT sum(f.views) FROM files f WHERE f.version = version_id AND f.size != 1;

The migration will assign all existing views to the 'preview' image of the primary version, and will probably need to be an PHP function.

UPDATE files SET views = (select p.views from photo p, photo_version v WHERE p.identifier = v.photo AND files.version = v.identifier) WHERE size = 2;

Question -- what do we do when we delete a version or file with X views? (Does this mean we need to maintain a global per-photo 'other views' counter that factors in?)

> what do we do when we delete a version or file with X views

I'm probably missing something obvious here, but what's wrong with subtracting the version/file's views_per_version from the views_per_photo before deleting?
That is, if it needs subtracting at all... Since those views all count towards a photo, regardless of the current status of any additional versions, shouldn't they remain?

The original idea was that we'd eliminate the top-level views altogether.. but instead, it would seem we need to retain it, and update the top-level count on-the-fly or when things are deleted.

So, this would logically mean:

1) per-file counter incremented on each 'hit'
2) per-version counter incremented on each 'hit'
3) per-photo counter incremented on each 'hit'
4) querying views_per_X is trivial; just look up the value in the right table.

or:

1) per-file counter incremented on each hit
2) when file is deleted, per-version counter += file counter
3) when version is deleted, per-photo counter += file counter
4) to query views_per_X, one has to take the value in table X and add the values for its "children" too.

Normally there are more hits than views_per_X, so the first model is slower on a busy site -- but it's also much simpler to code. The second model scales far better, but is more complex programically and views_per_X is ovbiously more complex to compute.

(Note that all of the lifting can be hidden inside triggers & stored procedures, so from the UI's perspective, there's no difference)

oh -- and we don't want the per-photo/version statiscs updated for thumbnails, just to complicate things.