Mailing List Archive: Bricolage: users

SOAP Create SQL Error

Index | Next | Previous | View Threaded

waldo at vqronline

Apr 15, 2008, 12:13 PM

Post #1 of 13 (547 views)
Permalink

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.\n\n[/usr/local/
bricolage/lib/Bric/Util/DB
I.pm:1084]\n[/usr/local/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:
2332]\n[/usr/loca
l/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:1904]\n[/usr/local/
bricolage/lib/Bric/S
OAP/Story.pm:1021]\n[/usr/local/bricolage/lib/Bric/SOAP/Asset.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

bharder at methodlogic

Apr 15, 2008, 2:02 PM

Post #2 of 13 (522 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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.\n\n[/usr/local/
> bricolage/lib/Bric/Util/DB
> I.pm:1084]\n[/usr/local/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:
> 2332]\n[/usr/loca
> l/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:1904]\n[/usr/local/
> bricolage/lib/Bric/S
> OAP/Story.pm:1021]\n[/usr/local/bricolage/lib/Bric/SOAP/Asset.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

david at kineticode

Apr 15, 2008, 2:05 PM

Post #3 of 13 (520 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

bharder at methodlogic

Apr 15, 2008, 3:12 PM

Post #4 of 13 (521 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

lannings at who

Apr 16, 2008, 4:39 AM

Post #5 of 13 (517 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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.)

waldo at vqronline

Apr 16, 2008, 12:52 PM

Post #6 of 13 (515 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

bharder at methodlogic

Apr 16, 2008, 1:15 PM

Post #7 of 13 (516 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

waldo at vqronline

Apr 16, 2008, 1:26 PM

Post #8 of 13 (517 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

david at kineticode

Apr 16, 2008, 2:29 PM

Post #9 of 13 (513 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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

lannings at who

Apr 17, 2008, 1:01 AM

Post #10 of 13 (508 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

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.

david at kineticode

Apr 17, 2008, 11:06 AM

Post #11 of 13 (506 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

On Apr 17, 2008, at 01:01, Scott Lanning wrote:

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

Scott, would you add something to the wiki for how to create custom
localizations? I think that the basic steps are:

* Create a module inheriting from Bric::Util::Language and with
%Lexicon.
* Add it to the database.
* Add it to bricolage.conf (preload the l10n).

Is that it? Nice thing about this is that you don't have to maintain a
patch.

Best,

David

lannings at who

Apr 18, 2008, 6:50 AM

Post #12 of 13 (498 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

On Thu, 17 Apr 2008, David E. Wheeler wrote:
> Scott, would you add something to the wiki for how to create
> custom localizations?

http://wiki.bricolage.cc/bin/view/Bric/CustomLocalization

david at kineticode

Apr 18, 2008, 8:35 AM

Post #13 of 13 (505 views)
Permalink

Re: SOAP Create SQL Error [In reply to]

On Apr 18, 2008, at 06:50, Scott Lanning wrote:

> On Thu, 17 Apr 2008, David E. Wheeler wrote:
>> Scott, would you add something to the wiki for how to create
>> custom localizations?
>
> http://wiki.bricolage.cc/bin/view/Bric/CustomLocalization

Awesome, thanks! Um, "pu_sy"? Got something on your mind? ;-)

David

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com