[ php-blog-Bugs-3254006 ] categorytemplates & postgresql

Bugs item #3254006, was opened at 2011-03-28 22:35
Message generated for change (Comment added) made by eazrael
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=542822&aid=3254006&group_id=75065

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Plugins
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Christoph Nelles (eazrael)
Assigned to: Nobody/Anonymous (nobody)
Summary: categorytemplates & postgresql

Initial Comment:
A friend told me that my s9y rss does not work anymore. The problem is a failing query: 
2011-03-28 22:17:36 CEST ERROR:  column "hide_rss" does not exist at character 1293
2011-03-28 22:17:36 CEST STATEMENT:  SELECT DISTINCT
                                    
                
                                    e.id,
                                    e.title,
                                    e.timestamp,
                                    e.comments,
                                    e.exflag,
                                    e.authorid,
                                    e.trackbacks,
                                    e.isdraft,
                                    e.allow_comments,
                                    e.last_modified,
                
                                    a.realname AS author,
                                    a.username AS loginname,
                                    a.email
                                     , e.body, e.extended
                                     
                                FROM
                                    serendipity_entries AS e
                                    LEFT JOIN serendipity_authors a
                                        ON e.authorid = a.authorid
                                    LEFT JOIN serendipity_entrycat ec
                                        ON e.id = ec.entryid
                                    LEFT JOIN serendipity_category c
                                        ON ec.categoryid = c.categoryid
                                    
                                    WHERE isdraft = 'false' AND (e.id NOT IN (SELECT e.id FROM serendipity_entries AS e
                                            LEFT JOIN serendipity_entrycat AS ec ON ec.entryid = e.id
                                            JOIN serendipity_categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1)) 
                                     
                                     
                                     ORDER BY timestamp DESC
                                      LIMIT 15

This in turn is caused by wrong database upgrades in plugins/serendipity_event_categorytemplates/serendipity_event_categorytemplates.php: checkScheme() as all SQL statements won't work on PostgreSQL as all the datatypes are MySQL ones. For example: 
"ALTER TABLE {$serendipity['dbPrefix']}categorytemplates ADD COLUMN hide_rss tinyint(1) default false";


----------------------------------------------------------------------

>Comment By: Christoph Nelles (eazrael)
Date: 2011-03-29 20:01

Message:
In the source code, it's a tinyint, and PostgreSQL does not allow size
limitations on ints like MySQL. 
Are you sure you want a 4 character field for a 5 character text like
false. BTW. then the false has to be a text. I propose using  0/1 and a
simple int field.

----------------------------------------------------------------------

Comment By: Garvin Hicking (garvinhicking)
Date: 2011-03-29 08:10

Message:
HI!

So you mean this fails in postgresql:

ALTER TABLE {$serendipity['dbPrefix']}categorytemplates ADD COLUMN
hide_rss varchar(4) default false

?

Can you tell me if this instead would work

ALTER TABLE {$serendipity['dbPrefix']}categorytemplates ADD COLUMN
hide_rss varchar(4) default 'false'

?

Regards,
Garvin

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=542822&aid=3254006&group_id=75065

------------------------------------------------------------------------------
Enable your software for Intel(R) Active Management Technology to meet the
growing manageability and security demands of your customers. Businesses
are taking advantage of Intel(R) vPro (TM) technology - will your software 
be a part of the solution? Download the Intel(R) Manageability Checker 
today! http://p.sf.net/sfu/intel-dev2devmar