Photo Organizer

  • Status New
  • Percent Complete
  • Task Type Feature Request
  • Category Backend / Core → Database
  • Assigned To No-one
  • Operating System All
  • Severity Low
  • Priority Very Low
  • Reported Version 2.33
  • Due in Version Undecided
  • Due Date Undecided
  • Votes
  • Private
Attached to Project: Photo Organizer
Opened by pizza - 2007-03-06
Last edited by pizza - 2008-01-06

FS#180 - Break out the statistics data

right now "view counts" are part of the "photo" table.

It might be beneficial to split stats to a separate table.. we can track additional fields (views vs downloads vs ??) and also track it for each photo version etc.

pizza commented on 2007-04-26 02:47

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

pizza commented on 2007-07-24 12:55

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

pizza commented on 2007-10-19 16:24

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 = 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?

pizza commented on 2008-02-18 16:23

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.


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)

pizza commented on 2008-02-18 16:33

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


Available keyboard shortcuts


Task Details

Task Editing