Forum OpenACS Development: OpenACS 5.9.0: journal_article and Edit This Page

Request notifications

Hi!

We've got a considerable mess here in our database concerning the content repository and journal_article:

- Our current ]po[ database contains a "journal_article" content type, but does not contain a journal_articles table. So the upgrade fails to recreate Content Reporitory views:

QUERY:  create view journal_articlei as select  acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
cr_text.text_data as text,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language, journal_article.*, etp_page_revisions.* from acs_objects, cr_revisions cr, cr_text, journal_article, etp_page_revisions where
acs_objects.object_id = cr.revision_id and acs_objects.object_id = journal_article.journal_article and acs_objects.object_id = etp_page_revisions.etp_page_revision_id

- We tried to remove any traces from journal_article from our DB, but we found that "journal_article" was referenced by some other object type (don't remember anymore).

- We now tried to install edit-this-page because this package apparently contains the creation of the journal_entry content type etc. but the generation of the content type fails with errors, that service contracts already exist. Also, I'm not sure if the journal_entries table would be created during this process.

- We tried to uninstall edit-this-page using the package manager, but this also fails, and then there is still a table "journal_entries" and the three journal_* content types. Even executing SQL statements individually, or combining with upgrade scripts doesn't change the result.

I would prefer to eliminate the tables, SCs and content types journal_* from our DB. Is there a recommended path?

Cheers,
Frank

It's the ACS-Workflow table wf_attribute_value_audit.journal_id and some other tables that depend on journal_issues. So we can't delete the table.

However, the journal_issue__new PL/PgSQL call performs a direct insert into journal_entries after creating the object, so it's completely content-repository ignorant. I guess there have never been upgrade scripts for this ACS 3.4 functionality? :-)

Still, is there a way to cleanly install/uninstall ETP? The acs-workflow is an important part of ]po[ and the goal is to install all of this on top of a clean OpenACS 5.9 installation.

Cheers,
Frank

Collapse
Posted by Gustaf Neumann on
Frank,

The problem does not only exist with 'journal_article', but as well with at least 'journal_issue', and 'news_item'. It is strange that your installation contains the cr-content type definitions of etp, but no etp installed. ... so maybe these are traces of a non-clean de-install attempt some time ago....

What happens when you run the one-liner of the update script [1]? It should help you over the issue with content_type__refresh_view.

OpenACS.org runs 5.9.0 fine with edit this page after updating to the newest version of etp.

-g

[1] http://fisheye.openacs.org/browse/OpenACS/openacs-4/packages/edit-this-page/sql/postgresql/upgrade/upgrade-1.9d2-1.9d3.sql?r=1.1

Collapse
Posted by Frank Bergmann on
Hi!

Thanks for helping me with the issue!
Yes, the default ]po[ database may still contain stuff from ACS 3.4. That's because we had to update our users so many times already.

However, I believe I've found the source of the recent issues: In
upgrade-5.9.0d4-5.9.0d5.sql, the very "drop tree_sortkey" cascades to 47 objects that are all deleted. I'm quite sure that's not intended like this, because it deletes for example "file_storage_objectx", which is needed to re-generated the content-repository views later in the script.

Does this happen because I try to upgrade directly from OpenACS 5.7 to OpenACS 5.9?

ALTER TABLE acs_objects drop column tree_sortkey cascade;
NOTICE: drop cascades to 47 other objects
DETAIL: drop cascades to view cr_revisionsi
drop cascades to function cr_revisions_f(cr_revisionsi)
drop cascades to view etp_page_revisionsi
drop cascades to function etp_page_revisions_f(etp_page_revisionsi)
drop cascades to view file_storage_objecti
drop cascades to function file_storage_object_f(file_storage_objecti)
drop cascades to view imagesi
drop cascades to function images_f(imagesi)
drop cascades to view users_email_imagei
drop cascades to function users_email_image_f(users_email_imagei)
drop cascades to view workflow_case_log_revi
drop cascades to function workflow_case_log_rev_f(workflow_case_log_revi)
drop cascades to view xowiki_filei
drop cascades to function xowiki_file_f(xowiki_filei)
drop cascades to view xowiki_formi
drop cascades to function xowiki_form_f(xowiki_formi)
drop cascades to view xowiki_form_pagei
drop cascades to function xowiki_form_page_f(xowiki_form_pagei)
drop cascades to view xowiki_objecti
drop cascades to function xowiki_object_f(xowiki_objecti)
drop cascades to view xowiki_pagei
drop cascades to function xowiki_page_f(xowiki_pagei)
drop cascades to view xowiki_page_instancei
drop cascades to function xowiki_page_instance_f(xowiki_page_instancei)
drop cascades to view xowiki_page_templatei
drop cascades to function xowiki_page_template_f(xowiki_page_templatei)
drop cascades to view xowiki_plain_pagei
drop cascades to function xowiki_plain_page_f(xowiki_plain_pagei)
drop cascades to view xowiki_podcast_itemi
drop cascades to function xowiki_podcast_item_f(xowiki_podcast_itemi)
drop cascades to view cc_users
drop cascades to view cr_revisionsx
drop cascades to view etp_page_revisionsx
drop cascades to view file_storage_objectx
drop cascades to view fs_urls_full
drop cascades to view imagesx
drop cascades to view users_email_imagex
drop cascades to view workflow_case_log_revx
drop cascades to view xowiki_filex
drop cascades to view xowiki_form_pagex
drop cascades to view xowiki_formx
drop cascades to view xowiki_objectx
drop cascades to view xowiki_page_instancex
drop cascades to view xowiki_page_templatex
drop cascades to view xowiki_pagex
drop cascades to view xowiki_plain_pagex
drop cascades to view xowiki_podcast_itemx
Collapse
Posted by Frank Bergmann on
Hi!

We finally made it. There was a misunderstanding due to the journal_* prefix:

- journal_entry is defined in acs-kernel while
- journal_issue and journal_article are defined by ETP

So we'll now include a hand-written SQL cleanup-script for ETP.
This script also removes an entry of "journal_entry" (the acs-kernel object type) in content_types. No idea how this ever has gotten there, but journal_entry definitely doesn't belong there. Here is the cleanup script:

drop rule if exists journal_article_r on journal_articlei;
drop function if exists journal_article_f(journal_articlei);
select content_type__drop_type('journal_article', 'f', 'f');
drop view if exists journal_articlei;
drop view if exists journal_articlex;

drop rule if exists journal_issue_r on journal_issuei;
drop function if exists journal_issue_f(journal_issuei);
select content_type__drop_type('journal_issue', 'f', 'f');
drop view if exists journal_issuei;
drop view if exists journal_issuex;

-- Somebody also made journal_entry a content-repository data type...
select content_type__drop_type('journal_entry', 'f', 'f');

So this specific problem seems to be fixed now.

However, we've not got similar problems re-generating the content-repository views for file_storage_object. However, this time we can't just cleanup stuff.

=> Can somebody tell me how to generate this file_storage_objectx table or view?

Thanks!
Frank

psql:file-storage-create.sql:67: ERROR:  relation "file_storage_objectx" does not exist
LINE 16: ...ctx.* from acs_objects, cr_revisions cr, cr_text, file_stora...
^
QUERY: create view file_storage_objectxi as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
cr_text.text_data as text,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language, file_storage_objectx.* from acs_objects, cr_revisions cr, cr_text, file_storage_objectx where
acs_objects.object_id = cr.revision_id and acs_objects.object_id = file_storage_objectx.object_id

Collapse
Posted by Gustaf Neumann on
Hi Frank,

where is the query above coming from? The view for the file_storage_object is supposed to be created via select content_type__refresh_view('file_storage_object'), which should create the two views

  • file_storage_object_ti, and
  • file_storage_object_tx,
but no file_storage_objectxi (what you are trying to create).

The last changes in this area were done more than six years ago [1] (in OpenACS 5.6). Can it be that you have missed some update scripts on file storage around that time? For your reference, on OpenACS 5.6 or newer, the type definition should look like

oacs-5-9=# select object_type, supertype, table_name, id_column, package_name  from acs_object_types where object_type like 'file%';
     object_type     |    supertype     |      table_name       |       id_column        |    package_name     
---------------------+------------------+-----------------------+------------------------+---------------------
 file_storage_object | content_revision | file_storage_object_t | file_storage_object_id | file_storage_object
(1 row)

-g
[1] https://github.com/openacs/file-storage/commit/6e10b3b140b2cb65bdf86fd791f2b070964b25c5

Collapse
Posted by Frank Bergmann on
Hi!

I have no idea how the file-storage installation in ]po[ had the view "file_storage_objectx" as "table_name". It's perfectly possible that we have missed some upgrade scripts. However, the package actually works.

Anyway, this seems to be the last data-type with content-repository upgrade issues. We will now uninstall file-storage completely and reinstall. This process is a bit ugly, because file-storage is used by the packages xowiki and attachments, but I hope it will work out...

Thanks for the help!
Frank

Collapse
Posted by Gustaf Neumann on
If someone else sees the problem above when upgrading old sites via acs-kernel/upgrade-5.9.0d4-5.9.0d5.sql, please run first the following SQL command in psql:
update acs_object_types set table_name = NULL, id_column = NULL where object_type = 'file_storage_object';