Forum OpenACS Q&A: Augmenting acs_objects - Add package_id, name, overview, etc. to it

Code:

        shortname               varchar2(200),
        overview                varchar2(4000),
        overview_html_p         char(1) default 'f'
                                constraint acs_objects_h_ck
                                check (overview_html_p in ('t','f')),

Helps to get rid off the *ugly* and *performance-killing* acs_objects.name pl/sql function - an absolute no-no if you want to present large lists of "objects".

Code:

	package_id		integer,

Something like this meta code becomes possible:

set query "
select object_id, shortname, overview, overview_html_p, ...
  from acs_objects, ...
  where ..."

perform_query

loop_thru_results
  set link_string "<a href='show-me-this-object?object_id'>$shortname</a><br>$overview"

We added a few more fields where I'm not always sure anymore why - like:

	state			char(1) default 'l' not null
				constraint acs_objects_state_ck
				check (state in ('l', 'd', 'a')),

        datastore               char(1) default 'a'
                                constraint acs_obj_datstr_nn not null,

        publish_date            date default sysdate,
        toplevel_package_id     integer

Later on I suggested that a site-wide package would *only* access acs_objects, no package-specific table - a query would just crawl through that very table, no need for expensive joins between large tables. And making a table "wider" is usuallyno big performance issue in Oracle.

How to keep acs_objects in sync with the package-specific tables - via triggers. Ah, and package queries would never access acs_objects, cos acs_objects' fields are - kept in sync with triggers - also part of the package-specific tables. Package-specific queries don't have to perform expensive joins with the large acs_objects table then.

(Unfortunately I never came to implementing this - I was moved to another project.)

Dirk

Notifications and search use service contracts to get the URL of an object and it seems quick enough. Notificatiosn does use acs_object__name to get the name of objects on the manage page, you can see it at https://openacs.org/notifications/manage

Of course, the length of this page depends on how many items you are subscribed to.

The get_url service contract that Dave is talking about is part of the notifications service contract. I would like to move that out and make it a stand alone service contract, so that every package can publish the url's of it's objects without having to implement notifications. The only client of this service contract would then be a yet to write /object-by-id/ page, while listings, notifications etc. can just link to that page. It would make most sense to do this when tcl service contracts are available I think.

Dirk, can you expand a bit more on which columns of acs_objects should be duplicated in your opinion in the package data tables and how the automatic synchronization in both directions would work? E.g. do you propose to have a package_id column both in acs_objects _and_ the package's table? Will there be a trigger on both - if yes wouldn't they fire each other infinitely?

This doesn't solve the name display issue.

THe object-goto page only needs to compute the URL for one page, so doing a query via an acs-service-contract shouldn't be a problem. Now, if the package_id is needed for other types of operations, they can be evaluated on their own. I just wanted to show that for redirection to an object specific page, using acs-serivce-contract works pretty well. It works by calling the acs-service-contract assigned to the object_type. So far for search and notifications it works fine. Maybe it could be found to not work in all cases.

If we stuff too much into objects, then those who keep complaining that objects are too expensive to use in general will have been proven right.  And I'd like to avoid that :)

What we are trying to do is to convince people to store content in the content repository rather than roll their own ad hoc solutions.  Though I realize people continue to do so, that doesn't mean I want to encourage them.  Nor do I want to encourage people to look at acs_objects themselves as being in some sense biased to the building of content types.

And the CR already knows how to expose URLs, how to customize rendering of content types and objects, etc.

Having package_id in cr_items would be very useful.  This is the one item on the list that may make sense to include in acs_objects, too.  Though finding the package owner via context id - if we follow through and decide that context id does indeed want to be a true parent linke - is fairly simple using the context index map and apm datamodel.  The question becomes "how often do you need to do this" ... a time vs. space tradeoff.

Is the PL/SQL call for the name information really that burdensome in practice?  Is it certain that this isn't just a result of poor query or page design?  Shouldn't we be paginating output rather than listing thousands of objects on a page?

Branimir - surely no one wants to do 50 queries per page, one wants to execute a single query that returns 50 rows ...

So we're balancing code speed vs. space.  And there are folks who already argue that objects are too heavyweight, if anything.  Those arguments will simply increase if we add a name field, much less a possibly HTML-formatted description.

BTW "real" content - stuff that should be in the content repository anyway - already has name and description fields.

Do all objects in the system really require this much information?

Another way to present an abstract view of objects so that generic pages can find a name, etc would be to use standard views to collect the information.  This would still require a join with acs_objects and the type-specific table but would be  less expensive than the name_method approach.

It is not.  The claim is that functions that perform SQL queries are
unusable (most of the time) within SQL queries.  They are also
dangerous because people using it often don't realize what
they are doing when they use it.  It looks like one neat SQL query
whereas in fact it may easily be 1000.  An especially nasty example
is trying to use it in the WHERE clause, say:

select count(*) from acs_objects
where acs_object.name(object_id) = 'Foo'

Don't try this on a site with any substantial number of objects.  You
*will* have to kill the database process!

<blockquote> why Ars Digita chose to architect ACS 4.x around the principle that
object system details should be hidden as much as possible within PL/
SQL functions and procedures?
</blockquote>

Well, abstractions always sound like a good idea.  And I do think there
is some merit to the hiding in PL/SQL functions for things like constructors and destructors.  Accessors on the other hand simply don't work. I think that people
who designed all that weren't aware of the performance issues back
then.

Actually I think we even abandoned constructors in some parts of
scripts to migrate from an ACS3-based system to an ACS4-based
one.  But even in that case constructors were useful because you could
treat them as documentation.

Okay, so assume you add a name column to acs_objects. Have you calculated the time required to update the name, or any other field in that table? it is very very slow. Probably that won't change too much, but how about the description field? I would guess that acs_object.name scales fairly well with the number of rows in acs_objects, whereas putting changeable data into that table doesn't scale at all.

Poorly designed interfaces that select hundreds, or thousands of rows of data, of any possible object type, should be changed to work nicely.

I also find it difficult to imagine an application where you would search for an object by name by using acs_object.name. The data exists in a separate table that the application should know about.

As to changing data: if you expect heavy updating/deleting/inserting then you can mumble around with PCTFREE and PCTUSED. Given that the name field is usually quite small there should row migration should almost never happen.

Updating/inserting/deleting into a second table certainly cost, but accesses happen far more frequently.

Following up Branimir's comment: I always found that we had way too many useless joins between acs_objects and acs_objects_description. And I don't mind NULLed columns, they don't take space and don't cost when being accessed.

So here's a step-by-step approach: introduce acs_objects_description with above mentioned fields and deprecate the name and get_url function. If things work out well, then go one step further and try to deprecate acs_objects_description or at least move around some fields (modifying_ip in acs_objects for example looks really useless).

What is stored in the content repository? Would you store metadata information about a subsite there? Or information about sent email alerts? If so, then replace acs_objects_description with CR.

I'd say that it seems fundamentally obvious to me that there are relatively few things one wants to do via a web interface to objects en masse.  Let's face it, pages that list every object in the system are by nature not very useful except to the programmer, and as a programmer I don't remember this approach every being useful to me.

On the other hand, pages that display information about broad classes of objects - content, for instance - do make sense.

That's where I'm coming from and I'd guess Dan's thinking something similar.

"Here's the content contributed by User Resu grouped by content package" ... something like this makes sense.  Abd the CR provides metadata common to content ... name and description being two examples.

"Here's every object contributed by User Resu - even though she was often unaware that something she did made a bunch of objects" ... not very useful.  How many site admins want to see a list of all of the relation objects this user has created by virtue of joining a group, etc?  How many casual users?

content_item.get_live_revision()

is more "abstract" than one that reads

cr_items.live_revision

an argument I don't make :)

package_id ... not sure one way or another.  I would certainly agree it can be useful for content items.  I know so because I added it to content items when working on the Greenpeace site, and it was very very useful!  We used it when grabbing all content generated by a particular package, i.e. "news for the dutch greenpeace site".

Package instances should create their own root folder and in theory this can be used to find all content generated by the package.  But in our case at Greenpeace we inherited some bad code that didn't bother to correctly generate root folders for each instance, just one shared by all instances...

Can you provide some specific examples where it has been useful?  There's quite a bit of sentiment to make "context_id" be a true "parent_id" (I know this goes against the original design but in practice aD itself released a bunch of code that depends on it, bboard for instance).  If we do that finding all objects generated by a package becomes a matter of casting the right CONNECT BY or tree sortkey query (depending on which RDBMS you're using) and these aren't terribly expensive.  More expensive than querying for the package_id but then again more general because you can query for the subtree underneath any object, not just a package ...

I think the difference between "live revision" and "latest revision" is clear enough that one rarely needs the PL/SQL fallback.  It's been a bit blurry in the original implementation of the CR in that some code operates on the "latest revision" if no "live revision" is available, but I'd rather tighten up our definition of what these concepts mean and avoid the PL/SQL sugar - because mostly it is just sugar and hiding the fact that either "live" or "latest" may be returned seems a bit confusing.  If you want it use COALESCE in the query where it will be immediately obvious to the next person who has to figure out what your query does.

Just my thoughts.

I did not make those Tcl procs to fix a broken data model.  The design is to make it easier to use the CR.  I wanted to have a design wherein the UI package is separate from the data operations.  As I have experienced most CMS has the same functions and operations, but their UI varies a lot.  Of course the theory must be more tested.  And based from my initial first stint, it seems to be ok.  But I need to change it, most are naming convention, merge or split some procs.

CR seems to work for me ok.  Since the name (cr_items.name, normally used for url) and (cr_revisions.description) description are already there.  And you can look it up by package_id in cr_folders.package_id.  The CR maybe a heavy weight, especially if you do not need versioning.  Although I have used CR even if I did not need versioning.  The UI does not expose versioning and I only have one version in the CR.

Hope this is still related with the thread.

Harrison barely even touches on the subject of object modeling in an RDBMS. His whole section on "mapping entities or classes to tables" is barely 3 pages long, he never mentions any real systems, and the table you are referring to on page 489 is at best a simple back of the envelope take on the performance ramifications. It looks at three very generic and high-level options: "Single table", "separate subtype and supertype stables" (which OpenACS uses), and "separate subtype tables only - no supertype table". At best, the whole section is useful soley as a starting point for further thinking about performance implications - it offeres no actual answers, and it certainly doesn't even attempt to discuss any other software design considerations whatsoever.

And Harrison at least, doesn't seem to pretend that those 3 pages are anything more.

openacs.org=# select object_type, count(*) from acs_objects group by object_type having count(*) > 25;
       object_type      | count
  ----------------------+-------
*+ acs_message          |    38
 + acs_message_revision |    44
   acs_sc_msg_type      |    46
*  apm_package          |   140
*  apm_package_version  |    44
*  apm_parameter        |   128
*  apm_parameter_value  |   658
*+ bookmark             |   378
*+ bt_bug               |   254
*+ bt_patch             |    61
*  content_extlink      |   125
*  content_folder       |   117
*  content_item         |   476
   content_revision     |   168
   cr_item_child_rel    |    64
*+ etp_page_revision    |   341
*+ faq_q_and_a          |    50
*+ forums_message       | 32307
   membership_rel       |  6186
   notification         |  1486
   notification_request |  9832
*  site_node            |    97
*  static_page          |   188
*+ url                  |   504
*  user                 |  6186
   user_portrait_rel    |    31

I also put a + by those things I think would be better off in the content repository (etp_page_revision and acs_message_revision are in fact in the CR already iirc).

In looking at the results I am left thinking that most things you actually would want to display to people with an attached name actually should really be in the content repository anyway and would have names there. I do want to see the "all new content sitewide", "all content by user X", and "all new content in subsite Y" be fast enough to use (which includes making permissions fast enough to do this -- something Don is working hard on as we type).

It's not.  Only certain classes of objects should ever be exposed directly to users, even admins.  Those that are internal should only be manipulated via web pages with a UI that makes sense to the user.

Having an object name in acs_objects wouldn't bother me particularly, especially if we didn't have to carry the cost of an index on it (i.e. discourage lookup by name).  But I think the notion that most named things belong in the CR is reasonable.  I also think that most of the named things that don't belong there - site nodes, for instance - ought to be manipulated by intellegently designed pages for those types, which will "know" where to find the name without calling acs_object.name (this is already true for site nodes).

So I'm still in serious need of convincing by more than one voice.

But dragging around columns for a description and html flag is something I can't see any justification for (again, keeping in mind that in PG there's a per-row cost for NULL columns)...

Lo and behold - I found this query in the logfile of a HEAD-based system while browsing around:

    select site_node.url(node_id) as url, object_id,                                                                                                          
           acs_object.name(object_id) as object_name,                                                                                                         
           level                                                                                                                                              
    from site_nodes                                                                                                                                           
    start with node_id = :node_id                                                                                                                             
    connect by prior parent_id = node_id                                                                                                                      
    order by level desc                                                                                                                                       

It's executed on every click (and the site_node.url function gets more expensive the more subsites you add to the system).

site_nodes does have a name field though which looks sensible. Is it safe to rephrase the query?