Forum OpenACS Development: input view on postgres

Collapse
Posted by Jun Yamog on
Hi,

I am experimenting with input views in particular in CR.  By creating
a content type foo you will get cr_foo.  Which also comes with 2 nice
views cr_foox and cr_fooi.  I am more interested with cr_fooi, which
is an input view.

Basically inserting into cr_fooi will automagically create an
acs_object, cr_revision and cr_foo row.  Rather than inserting to them
manually (highly discouraged) or calling content_revision__new and
inserting to cr_foo.  There is a snag though, I am still unsure if its
a bug or what.

Since cr_foo has foo_id which references revision_id AND is a primary
key.  foo_id has a constraint of "not null".  This is logical but you
will not be able to insert to cr_fooi because I think the not null
trigger will fire first before you have the ability to insert
new.revision_id.  This rule is made at content_type__refresh_trigger.

I have a dumb work around by recreating cr_foo and just making foo_id
to just a foreign key,  recreate the view and refresh the view rule by
content_type__refresh_view (foo).  It now works.

Any suggestions to properly do this?  DanW *wink* *wink* guidance to
the proper CR path again.

Collapse
Posted by Dan Wickstrom on
I have tested inserting into the content views before, so I know that it used to work.

Did you actually try it?  From the way you phrased your post, I had the impression that you thought it might fail, and I also had the impression that you never actually tried inserting into cr_fooi?  Maybe I'm mis-reading.  If you have a test case that rereates the problem, post it, and I will take a look.  I will be traveling tomorrow, so I won't look at it until Sunday or Monday.

Collapse
Posted by Jun Yamog on
Hi Dan,

Yes I have tried it after our chat.  It seems cr_revisionsi works.  But cr_fooi does not.  After I manually recreated the table and removed the not null constraint it does go in.  Put back the constraint it does not work, so it seem to boil down to the not null constraint.

I am using pg 7.2.1 in case pg changed the way they deal with constraints from 7.1.3

Collapse
Posted by Dan Wickstrom on
I tried the following:

openacs4=# select version();
-[ RECORD 1 ]-------------------------------------------------------------
version | PostgreSQL 7.2.1 on sparc-sun-solaris2.8, compiled by GCC 2.95.3
openacs4=# select content_folder__register_content_type(-100,'cr_foo','f');
-[ RECORD 1 ]-------------------+--
content_folder__register_conten | 0

openacs4=#         select content_item__new (
                'foo_item2',
                NULL,
                NULL,
                NULL,
                current_timestamp,
                null,
                null,
                null,
                'content_item',
                'cr_foo',
                NULL,
                NULL,
                NULL,
                NULL,
                NULL
        );
-[ RECORD 1 ]-
content_item__new | 2585

openacs4=# select acs_object_id_seq.nextval from dual;
N-[ RECORD 1 ]-
nextval | 2586

openacs4=# insert into cr_fooi (item_id, 
                     revision_id, 
                     title, 
                     description, 
                     context_id, 
                     creation_user, 
                     creation_ip) 
values (2585,2586,'foo test','this is a test of foo',null,null,null);
INSERT 0 0
openacs4=# select * from cr_foo; 
-[ RECORD 1 ]
foo_id | 2586

The key was that I needed to register the content type to the root folder.

Collapse
Posted by Dan Wickstrom on
Actually never mind out the key being to register the content type in the root folder.  That was causing me problems with creating the content item.  I don't really understand why you're having a problem, because it's working fine for me.  A standalone test script that reproduces the problem would be quite helpful.
Collapse
Posted by Jun Yamog on
Hi Dan,

Maybe the difference with us is that I only register the custom content type on a subfolder under the root.  Not on the root itself.  I believe you won't be able to put a content folder unless that content type is registered on that folder or its parents.

Anyway I will try to register my custom content type on the root (-100).  If that fails I will post a script here.  My pg db is kinda screwed now with all the experimentation done on it.  I think it would best to start from a fresh install, also that way we eliminate some of the variables.

I will post as soon as I can.

Collapse
Posted by Jun Yamog on
Hi Dan,

I was able to pinpoint the problem. It seems that

insert into cr_revisionsi (item_id, title, text) ... blah 

works.  But

insert into cr_fooi (item_id, title, text) ... blah will fail

you will need

insert into cr_fooi (item_id, revision_id, title, text)
Do you think we modify content_type__trigger_insert_statement to get the revision_id from content_type__refresh_trigger? That way we can do away with getting the "select acs_object_id_seq.nextval from dual;". Is this thought correct way to do it?

OT: When doing a content_item__new we are supposed to inherit from content_revision or content_item? Content_revision right?

Collapse
Posted by Jun Yamog on
Trash that idea.  How can you see a rule?  How can I view cr_foo_r rule?
Collapse
Posted by Jun Yamog on
Hi Dan,

This is the rule that is created by content_type__trigger_insert_statement

 CREATE RULE cr_foo_r AS ON INSERT TO cr_fooi 
DO INSTEAD (UPDATE cr_dummy SET val = (SELECT 
content_revision__new(new.title, (new.description)::"varchar", now(), new.mime_type, new.nls_language, 
CASE WHEN (new.text IS NULL) THEN new.data ELSE new.text END, content_symlink__resolve(new.item_id), 
new.revision_id, now(), new.creation_user, new.creation_ip) 
AS content_revision__new); INSERT INTO cr_foo (foo_id) VALUES (new.revision_id); );
By changing the rule and using cr_dummy.val instead of new.revision_id
 CREATE RULE cr_foo_r AS ON INSERT TO cr_fooi 
DO INSTEAD (UPDATE cr_dummy SET val = (SELECT 
content_revision__new(new.title, (new.description)::"varchar", now(), new.mime_type, new.nls_language, 
CASE WHEN (new.text IS NULL) THEN new.data ELSE new.text END, content_symlink__resolve(new.item_id), 
new.revision_id, now(), new.creation_user, new.creation_ip) AS 
content_revision__new); INSERT INTO cr_foo (foo_id) VALUES (cr_dummy.val); );

You are now able to insert without specifying revision_id. So we basically need to change content_type__trigger_insert_statement proc. Is this a valid solution? This is the first time I that I ventured this deep into pg so I am not sure if this thoughts are correct.

Collapse
Posted by Dan Wickstrom on
Yes, I think that is the right way to do it.  I looked at the oracle version, and it is analogous to what you are proposing.

When debugging this, I must have caught up in making it work, and I forgot to go back and check that it still matched the oracle version in functionality.

Thanks for finding this Jun!

Collapse
Posted by Jun Yamog on
Hi Dan,

Thank you too for the help and explanation, its been a learning weekend for me both on CR and Postgres.  Do I still put this in on the SDM?  Or you just go and commit the changes to the CVS?

Collapse
Posted by Dan Wickstrom on
No, I'll commit the changes.  I haven't done it yet, because I want to look at it in some more detail.  I should have some time to look at it today, and if everything looks good, I'll commit the changes.
Collapse
Posted by Dan Wickstrom on
I've updated the content type rule generation so that it works the same as oracle.  I'm not planning on providing upgrade scripts unless somebody screams.  Currently, I can only find one place in the code where this feature is used and this change shouldn't affect it.