SOAP Create SQL Error

Folks,

I'm warming up for The Big Transition to Bricolage(tm),
going through  
the paces with a few dozen articles to prepare for importing
~10,000  
of them. It didn't take me long to hit a snag: I can't
create any new  
records via the SOAP interface.

After trying repeatedly with my own externally-created XML
data, I ran  
a simple test. I created a new story in Bricolage. I
exported it. I  
deleted it from Bricolage. Then I attempted to import it
again with  
bric_soap. It failed with "Unable to execute SQL
statement." In my  
logs, I found:

----------cut----------
Use of uninitialized value in exists at
/usr/local/bricolage/lib/Bric/ 
SOAP/Story.pm line
  775, <GEN375> line 19.
[Tue Apr 15 18:42:44 2008] [error]
SOAP::Serializer::envelope: Server  
Application error
Unable to execute SQL statement: DBD::Pg::st execute failed:
ERROR:   
new row for relatio
n "story" violates check constraint
"ck_story__publish_status"n [for  
Statement "INSERT
INTO story (id, uuid, priority, source__id, usr__id,
element_type__id,  
first_publish_dat
e, publish_date, expire_date, current_version,
published_version,  
workflow__id, publish_
status, primary_uri, active, desk__id, site__id, alias_id)
VALUES  
(NEXTVAL('seq_story'),
  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
with  
ParamValues: 11='0', 7='2008-0
4-15 00:00:00.000000', 17=undef, 2='3',
1='BC91CEA6-0B1B-11DD- 
B034-02A59314BD3F', 16='10
0', 13='/articles/2008/spring/test/', 6='2008-02-15
16:03:00.000000',  
3='1', 9='
0', 12='0', 14='0', 15='0', 8=undef, 4='0', 10=undef, 5='1']
at /usr/ 
local/bricolage/lib
/Bric/Util/DBI.pm line 1083, <GEN375> line
19.nn[/usr/local/ 
bricolage/lib/Bric/Util/DB
I.pm:1084]n[/usr/local/bricolage/lib/Bric/Biz/Asset/Busines
s/Story.pm: 
2332]n[/usr/loca
l/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:1904]n[/us
r/local/ 
bricolage/lib/Bric/S
OAP/Story.pm:1021]n[/usr/local/bricolage/lib/Bric/SOAP/Asse
t.pm: 
243]n[/usr/lib/perl5/s
ite_perl/5.8.5/SOAP/Lite.pm:2569]n[/usr/lib/perl5/site_perl
/5.8.5/ 
SOAP/Transport/HTTP.p
m:327]n[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.
pm:621]n[/ 
usr/local/bricola
ge/lib/Bric/SOAP/Handler.pm:169]
[/usr/local/bricolage/lib/Bric/SOAP/Handler.pm:249]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2649]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:1505]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:761]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2624]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:338]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2617]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:327]
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:621]
[/usr/local/bricolage/lib/Bric/SOAP/Handler.pm:169]
----------cut----------

To my eyes, the crux of this appears to be "new row for
relation  
'story' violates check constraint
'ck_story__publish_status'". Being a  
MySQL guy, not a Postgres guy, I'm really not sure of what
to do with  
this. I'm running Bricolage 1.10.3 and PostgreSQL 7.4.17-1.
I've  
checked the bug tracker and the 1.10.4 changelog, and can
find no  
record of this.

Any suggestions? Am I -- as I hope -- doing something
foolish?

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223

On Tue, Apr 15, 2008 at 03:13:37PM -0400, Waldo Jaquith
wrote:
> Folks,
> 
> I'm warming up for The Big Transition to Bricolage(tm),
going through  

Welcome 

> the paces with a few dozen articles to prepare for
importing ~10,000  
> of them. It didn't take me long to hit a snag: I can't
create any new  
> records via the SOAP interface.
> 
> After trying repeatedly with my own externally-created
XML data, I ran  
> a simple test. I created a new story in Bricolage. I
exported it. I  
> deleted it from Bricolage. Then I attempted to import
it again with  
> bric_soap. It failed with "Unable to execute SQL
statement." In my  
> logs, I found:
> 
> ----------cut----------
> Use of uninitialized value in exists at
/usr/local/bricolage/lib/Bric/ 
> SOAP/Story.pm line
>  775, <GEN375> line 19.
> [Tue Apr 15 18:42:44 2008] [error]
SOAP::Serializer::envelope: Server  
> Application error
> Unable to execute SQL statement: DBD::Pg::st execute
failed: ERROR:   
> new row for relatio
> n "story" violates check constraint
"ck_story__publish_status"n [for  
> Statement "INSERT
> INTO story (id, uuid, priority, source__id, usr__id,
element_type__id,  
> first_publish_dat
> e, publish_date, expire_date, current_version,
published_version,  
> workflow__id, publish_
> status, primary_uri, active, desk__id, site__id,
alias_id) VALUES  
> (NEXTVAL('seq_story'),
>  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)" with  
> ParamValues: 11='0', 7='2008-0
> 4-15 00:00:00.000000', 17=undef, 2='3',
1='BC91CEA6-0B1B-11DD- 
> B034-02A59314BD3F', 16='10
> 0', 13='/articles/2008/spring/test/', 6='2008-02-15
16:03:00.000000',  
> 3='1', 9='
> 0', 12='0', 14='0', 15='0', 8=undef, 4='0', 10=undef,
5='1'] at /usr/ 
> local/bricolage/lib
> /Bric/Util/DBI.pm line 1083, <GEN375> line
19.nn[/usr/local/ 
> bricolage/lib/Bric/Util/DB
>
I.pm:1084]n[/usr/local/bricolage/lib/Bric/Biz/Asset/Busines
s/Story.pm: 
> 2332]n[/usr/loca
>
l/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:1904]n[/us
r/local/ 
> bricolage/lib/Bric/S
>
OAP/Story.pm:1021]n[/usr/local/bricolage/lib/Bric/SOAP/Asse
t.pm: 
> 243]n[/usr/lib/perl5/s
>
ite_perl/5.8.5/SOAP/Lite.pm:2569]n[/usr/lib/perl5/site_perl
/5.8.5/ 
> SOAP/Transport/HTTP.p
>
m:327]n[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.
pm:621]n[/ 
> usr/local/bricola
> ge/lib/Bric/SOAP/Handler.pm:169]
> [/usr/local/bricolage/lib/Bric/SOAP/Handler.pm:249]
> [/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2649]
> [/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:1505]
> [/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:761]
> [/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2624]
>
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:338]
> [/usr/lib/perl5/site_perl/5.8.5/SOAP/Lite.pm:2617]
>
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:327]
>
[/usr/lib/perl5/site_perl/5.8.5/SOAP/Transport/HTTP.pm:621]
> [/usr/local/bricolage/lib/Bric/SOAP/Handler.pm:169]
> ----------cut----------
> 
> To my eyes, the crux of this appears to be "new
row for relation  
> 'story' violates check constraint
'ck_story__publish_status'". Being a  
> MySQL guy, not a Postgres guy, I'm really not sure of
what to do with  
> this. I'm running Bricolage 1.10.3 and PostgreSQL
7.4.17-1. I've  
> checked the bug tracker and the 1.10.4 changelog, and
can find no  
> record of this.

I think your eyes are right, regarding the crux of the
problem... 

Your publish_status is 0, yet there are two publish-dates
provided (publish_date, first_publish_date)... the
constraint is that:

If there's a "true" status for publish_status, we
need dates.
If there's a "false" status for publish_status,
dates must be NULL.


Not sure how this record came to be attempted in your DB,
but that's what appears to be happening.

Hope this helps,

> Any suggestions? Am I -- as I hope -- doing something
foolish?

-- 

Brad Harder,
Method Digital Logic
http://www.methodlogic.net

On Apr 15, 2008, at 14:02, bharder wrote:

> I think your eyes are right, regarding the crux of the
problem...
>
> Your publish_status is 0, yet there are two
publish-dates provided  
> (publish_date, first_publish_date)... the constraint is
that:
>
> If there's a "true" status for
publish_status, we need dates.
> If there's a "false" status for
publish_status, dates must be NULL.
>
>
> Not sure how this record came to be attempted in your
DB, but that's  
> what appears to be happening.

Waldo, make sure that when you create the new story via XML
that you  
first delete or empty out the publish date fields and set  
publish_status to 0.

I don't usually run into this myself because I don't export
published  
stories to use for models for the XML I'll need to generate
for a  
migration. But I can see how it could happen. 

HTH,

David

On Tue, Apr 15, 2008 at 02:05:42PM -0700, David E. Wheeler
wrote:
> On Apr 15, 2008, at 14:02, bharder wrote:
> 
> >I think your eyes are right, regarding the crux of
the problem...
> >
> >Your publish_status is 0, yet there are two
publish-dates provided  
> >(publish_date, first_publish_date)... the
constraint is that:
> >
> >If there's a "true" status for
publish_status, we need dates.
> >If there's a "false" status for
publish_status, dates must be NULL.
> >
> >
> >Not sure how this record came to be attempted in
your DB, but that's  
> >what appears to be happening.
> 
> Waldo, make sure that when you create the new story via
XML that you  
> first delete or empty out the publish date fields and
set  
> publish_status to 0.
> 
> I don't usually run into this myself because I don't
export published  
> stories to use for models for the XML I'll need to
generate for a  
> migration. But I can see how it could happen. 

One would have to be adjusting the fields w/o concern for
the constraints/relationships one way or another, though,
because if this was pulled out of the DB and those fields
weren't adjusted, and resubmitted, there's an existing
problems in the DB too...

Either way, resovling this (no matter how the XML is
generated) should fix the submission issue. If you pulled a
record out that has this error though, you've got other
problems, too.


-- 

Brad Harder,
Method Digital Logic
http://www.methodlogic.net

On Tue, 15 Apr 2008, bharder wrote:
> On Tue, Apr 15, 2008 at 02:05:42PM -0700, David E.
Wheeler wrote:
>> On Apr 15, 2008, at 14:02, bharder wrote:
>>> Your publish_status is 0, yet there are two
publish-dates provided
>>> (publish_date, first_publish_date)... the
constraint is that:
>>>
>>> If there's a "true" status for
publish_status, we need dates.
>>> If there's a "false" status for
publish_status, dates must be NULL.
[...]
>> Waldo, make sure that when you create the new story
via XML that you
>> first delete or empty out the publish date fields
and set
>> publish_status to 0.

It's clearly some kind of bug, so, in the hopes that
it will get fixed, I filed a bug report:
http://
bugs.bricolage.cc/show_bug.cgi?id=1326
including a procedure you can follow to fix the bug.
(If you're in college, think of it as a Bricolage hacking
homework assignment. There will likely be a quiz.)

David & Co.,

On Apr 15, 2008, at 5:05 PM, David E. Wheeler wrote:
> On Apr 15, 2008, at 14:02, bharder wrote:
>> I think your eyes are right, regarding the crux of
the problem...
>>
>> Your publish_status is 0, yet there are two
publish-dates provided  
>> (publish_date, first_publish_date)... the
constraint is that:
>>
>> If there's a "true" status for
publish_status, we need dates.
>> If there's a "false" status for
publish_status, dates must be NULL.
>>
>>
>> Not sure how this record came to be attempted in
your DB, but  
>> that's what appears to be happening.
>
> Waldo, make sure that when you create the new story via
XML that you  
> first delete or empty out the publish date fields and
set  
> publish_status to 0.
>
> I don't usually run into this myself because I don't
export  
> published stories to use for models for the XML I'll
need to  
> generate for a migration. But I can see how it could
happen. 

Well, that worked very nicely, indeed. It hadn't occurred to
me that  
Bricolage would naturally want to use its own timespan to
record  
things like date published, rather than allowing me to
import that  
data from my existing CMS. Presumably cover_date will do the
trick,  
though, for recording the dates in which these articles have
been  
published over the past 85 years. (He said, hopefully.)
Thanks for  
your help with that, Brad, David and Scott.

On an unrelated note, the use of the term
"stories" within Bricolage  
is throwing some folks for a loop around here. In the world
of  
literature, the term "story" denotes works of
fiction. "That's where  
the fiction is," they figure, "but where do I find
the poetry and non- 
fiction?" It's a minor point, but one worth calling
up.

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223

On Wed, Apr 16, 2008 at 03:52:25PM -0400, Waldo Jaquith
wrote:
> David & Co.,
> 
> On Apr 15, 2008, at 5:05 PM, David E. Wheeler wrote:
> >On Apr 15, 2008, at 14:02, bharder wrote:
> >>I think your eyes are right, regarding the crux
of the problem...
> >>
> >>Your publish_status is 0, yet there are two
publish-dates provided  
> >>(publish_date, first_publish_date)... the
constraint is that:
> >>
> >>If there's a "true" status for
publish_status, we need dates.
> >>If there's a "false" status for
publish_status, dates must be NULL.
> >>
> >>
> >>Not sure how this record came to be attempted
in your DB, but  
> >>that's what appears to be happening.
> >
> >Waldo, make sure that when you create the new story
via XML that you  
> >first delete or empty out the publish date fields
and set  
> >publish_status to 0.
> >
> >I don't usually run into this myself because I
don't export  
> >published stories to use for models for the XML
I'll need to  
> >generate for a migration. But I can see how it
could happen. 
> 
> Well, that worked very nicely, indeed. It hadn't
occurred to me that  
> Bricolage would naturally want to use its own timespan
to record  
> things like date published, rather than allowing me to
import that  
> data from my existing CMS. Presumably cover_date will
do the trick,  
> though, for recording the dates in which these articles
have been  
> published over the past 85 years. (He said, hopefully.)
Thanks for  
> your help with that, Brad, David and Scott.

You should be fine w/ cover date for your stories -- the
publish/last-publish are describing the internal Bricolage
publishing processing (ie: writing out to web, or whatever
output channel you choose).

> On an unrelated note, the use of the term
"stories" within Bricolage  
> is throwing some folks for a loop around here. In the
world of  
> literature, the term "story" denotes works of
fiction. "That's where  
> the fiction is," they figure, "but where do I
find the poetry and non- 
> fiction?" It's a minor point, but one worth
calling up.

_That_ is interesting. Is there some term that you would
generically use to describe all of story, poetry, and
non-fiction pieces?

-- 

Brad Harder,
Method Digital Logic
http://www.methodlogic.net

On Apr 16, 2008, at 4:15 PM, bharder wrote:
>> On an unrelated note, the use of the term
"stories" within Bricolage
>> is throwing some folks for a loop around here. In
the world of
>> literature, the term "story" denotes
works of fiction. "That's where
>> the fiction is," they figure, "but where
do I find the poetry and  
>> non-
>> fiction?" It's a minor point, but one worth
calling up.
>
> _That_ is interesting. Is there some term that you
would generically  
> use to describe all of story, poetry, and non-fiction
pieces?

There are three terms that I've found used widely: article,
work, and  
piece. The latter two are used with particular frequency,
and seem an  
awkward overreach to me in the mold of
"fireperson" or "chairperson."  
"Article" isn't quite fitting for poetry but, hey,
they're poets --  
they knew they were doomed to a life of exclusion and
poverty when  
they started down that path. But for prose, certainly, it's
the most  
fitting term, and I can't see spending much time worrying
about  
anything other than prose w/r/t Bricolage.

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

On Apr 16, 2008, at 12:52, Waldo Jaquith wrote:

> Well, that worked very nicely, indeed. It hadn't
occurred to me that  
> Bricolage would naturally want to use its own timespan
to record  
> things like date published, rather than allowing me to
import that  
> data from my existing CMS. Presumably cover_date will
do the trick,  
> though, for recording the dates in which these articles
have been  
> published over the past 85 years. (He said, hopefully.)
Thanks for  
> your help with that, Brad, David and Scott.

Yes, that's exactly what cover date is for, in fact.

> On an unrelated note, the use of the term
"stories" within Bricolage  
> is throwing some folks for a loop around here. In the
world of  
> literature, the term "story" denotes works of
fiction. "That's where  
> the fiction is," they figure, "but where do I
find the poetry and  
> non-fiction?" It's a minor point, but one worth
calling up.

Edit en_us.pm and add these lines to %Lexicon:

   'Story' => 'Article',
   'Stories' => 'Articles',

Then reboot. That should do it. You'll need to make this
same change  
every time you upgrade, but I've seen far worse patches.

Best,

David

On Wed, 16 Apr 2008, David E. Wheeler wrote:
> On Apr 16, 2008, at 12:52, Waldo Jaquith wrote:
>> On an unrelated note, the use of the term
"stories" within Bricolage is
>> throwing some folks for a loop around here. [...]
>
> Edit en_us.pm and add these lines to %Lexicon:
>
> 'Story' => 'Article',
> 'Stories' => 'Articles',

Mark and/or Rachel did a similar thing here.
FWIW, we call them "Documents".
(And also have a "Story" element,
  which can make things confusing to me...)

Here's relevant parts of what I see
in our "en_hq" (headquarters) localization:

    '"Story"' =>
'"Document"',
    'Currently Related Story' => 'Currently Related
Document',
    'Related Story' => 'Related Document',
    'Story Type' => 'Document Type',
    'Create a New Story' => 'Create a New Document',
    'Find Stories' => 'Find Documents',
    'New Story' => 'New Document',
    'Active Stories' => 'Active Documents',
    'No related Stories' => 'No related Documents',
    'Stories in this category' => 'Documents in this
category',
    'Story [_1] check out canceled.' => 'Document [_1]
check out canceled.',
    'Story [_1] deleted.' => 'Document [_1] deleted.',
    'Story [_1] published.' => 'Document [_1]
published.',
    'Story [_1] reverted to V.[_2].' => 'Document [_1]
reverted to V.[_2].',
    'Story [_1] saved and moved to [_2].' => 'Document
[_1] saved and moved to 
[_2].',
    'Story [_1] saved, checked in to [_2].' => 'Document
[_1] saved, checked in 
to [_2].',
    'Story [_1] saved.' => 'Document [_1] saved.',

    'The category was not added, as it would have caused a
URI clash with story 
[_1].' =>
      'The category was not added, as it would have caused a
URI clash with 
document [_1].',

    'The slug has been reverted to [_1], as the slug [_2]
caused this story to 
have a URI conflicting with that of story [_3].' =>
      'The slug has been reverted to [_1], as the slug [_2]
caused this document 
to have a URI conflicting with that of document [_3].',

    'The slug, category and cover date you selected would
have caused this story 
to have a URI conflicting with that of story [_1].' =>
      'The slug, category and cover date you selected would
have caused this 
document to have a URI conflicting with that of document
[_1].',

   # System Requests Messages
    'Choose a Related Story' => 'Choose a Related
Document',
    'Please select a story type.' => 'Please select a
document type.',

   # Error Messages

    'The cover date has been reverted to [_1], as it caused
this story to have a 
URI conflicting with that of story '[_2].' =>
      'The cover date has been reverted to [_1], as it
caused this document to 
have a URI conflicting with that of document '[_2].',

    'This story has not been assigned to a category.' =>
      'This document has not been assigned to a category.',

    'Story [_1] saved and shelved.' => 'Document [_1]
saved and shelved.',
    'Story [_1] saved and checked in to [_2].' =>
      'Document [_1] saved and checked in to [_2].',
    'Story [_1] created and saved.' => 'Document [_1]
created and saved.',


Some of these might not be applicable to
Bricolage versions higher than 1.8,
or to installations without our patches,
but it probably doesn't matter.
Your mileage may vary.