Is it me or the database that's not normal?

So I’m an Etomite native. In Eto, there is no privateweb or privatemgr field in the prefix_site_content table of the database. So when working with a popular MODx’er on getting the newsPublisher snippet to respect its parents permissions - I ran into a snafu.

I got the new news/blog item to successfully acquire the same permissions as its parent (folder). Then I checked in the manager to see that it was reflected there. Sure enough, when you hit "edit," the correct document group checkboxes are checked. However, a glance at the doc tree shows no padlock. A quick "cancel" to take me back to the doc info ... scan ... yep. Shows web and manager access as public.

Upon further investigation, it is because of these new (to me) fields in the content table. So I began looking into them. It appears to me that they do NOT contain any unique information, but are merely shortcuts for other SQL statements aimed at determining permissions.

As a trial and error thing, I went back to edit the doc that had the right groups associated. I saved without making ANY changes. Suddenly, all was well. So I think I’m understanding this whole thing. But it begs the question: why create a new field that is set by reading other information in the database. What might make more sense is an API function to do that. That way, snippet developers won’t have to recreate the wheel. In this case, if the privateweb/privatemgr status (not field) were checked dynamically, then then the manager would have instantly reflected the permissions of the page accurately, without having to go back in and hit "save" first.

Of course, as my footer says, I could be totally wrong. But it seems to me that these fields were added as a convenience. But in the long haul, having to change permissions once with document groups, then with the content, might be more cumbersome.

Did that make any sense at all

Makes total sense Jared (and I do love your disclaimer... mind if I borrow it for a blog that’s brewing in my head?).

I’m not sure if they’re used elsewhere (other than visually for the tree) but hopefully Raymond can comment soon.

As far back as I can remember I think I added these two fields for speeding up the query process. Rather than going throught the system in trying to find out which permissions were set for the document these two fields will rasily determine whether or not the document is private to manager or private to web users.

These designs was primarily to maintain support of the then module (from the eto) without breaking the Etomite core

Indeed, the check is preventing the parser from having to load and check the groups a user is in, the document groups a user has access to, and other data, unless it is marked as privateweb/privatemgr. And this definitely increases performance.

I guess my question is- is the increase in performance gained from not running 2 queries (could be one if webusers/manager users merged) enough of a benefit to compensate the extra hassle to develop snippet code etc. to handle setting those fields?

I suppose in this particular instance, the frequency of having to do this is pretty low, but it does raise issues about database normalization. I tend to lean toward a trim database and great API tools. Over the long haul it would seem like this would make upgrading and module/snippet/whatever development easier.

Just my two cents.

Quote from: jaredc at Dec 14, 2005, 03:03 PM

I guess my question is- is the increase in performance gained from not running 2 queries (could be one if webusers/manager users merged) enough of a benefit to compensate the extra hassle to develop snippet code etc. to handle setting those fields?

That’s correct, and in the current MODx, calling snippets to return all children called these same queries again, to determine which documents could be returned. I have refactored these to query the data once and cache it, so additional requests for the data via API calls will utilize the cached data if it already exists for this purpose. This may prove to be enough of a performance increase to drop the private flag(s), but I still can’t help wanting to preserve resources and processing time on all-to-frequent requests to public pages that do not need to query this particular data.

Quote from: jaredc at Dec 14, 2005, 03:03 PM

I suppose in this particular instance, the frequency of having to do this is pretty low, but it does raise issues about database normalization. I tend to lean toward a trim database and great API tools. Over the long haul it would seem like this would make upgrading and module/snippet/whatever development easier.

In my experience, database normalization and performance are often at odds, and in this case, I don’t think a single BINARY/BIT field to indicate if a document is private is bloating the data structures. And if the object-relational API can completely hide this flag, automatically saving changes to it as appropriate when the document object is modified, well, I don’t see it as a problem to MODx resource developers.

Quote from: OpenGeek at Dec 14, 2005, 07:52 PM

...I don’t think a single BINARY/BIT field to indicate if a document is private is bloating the data structures. And if the object-relational API can completely hide this flag, automatically saving changes to it as appropriate when the document object is modified, well, I don’t see it as a problem to MODx resource developers.

Agreed. (No- I’m not a freak on 107th degree normalization ) Does that mean there will be API tools to handle this?

There will be a significantly better API to make it all work the way it should. You should not have to directly access the DB for anything.