Properties/Templates of Categories Plugin not working

[ILF]

Hello.

I am fairly recent user of s9y but I already love it. I installed the properties/templates of categories plugin on my blog couple of times and then removed it, because I didn't really need it and because it messed up with the rss feeds of the blog - after installing it, all the rss feeds stopped working. Last night I installed the plugin again, this time because I needed it. The effect on the feeds was the same. However I fiddled some more with the plugin and noticed that after setting the properties of some categories and then clicking save, nothing really happens and the settings are not saved.

This lead me to the conclusion that something is not ok with either the source or the DB. I checked the php source of the plugin and found out that the table it is trying to write its properties in the DB, does not exist. After some more reading of the source I stumbled upon the probable reason, the table properties are set for mysql. I'm using postgres, and I recreated the table in postgres, however I haven't tested yet if it is working mainly because I'm not a developer, my php skills are extremely poor, I just happen to know SQL and more specific I'm kinda fluent with postgres stored procedures, but again I'm not a developer, I work as a sys admin.

So please, can someone help me with this? I can and will test the plugin after creating the table in the database by hand, however I'm not sure I'll be able to fix the rest of the code that is mysql related.

[ILF]

OK, a little strange to reply to my own post, but here are the results:

after creating the table on my test servers with the following syntax in postgre:

Code: Select all

CREATE TABLE serendipity_categorytemplates
(
  categoryid integer,
  "template" character varying,
  fetchlimit integer,
  futureentries integer,
  lang character varying,
  pass character varying,
  sort_order character varying,
  hide_rss boolean DEFAULT false
)
WITH (OIDS=TRUE);
ALTER TABLE serendipity_categorytemplates OWNER TO some_user;

CREATE INDEX ctcid
  ON serendipity_categorytemplates
  USING btree
  (categoryid);

the plugin started working. However I still experience the lack of rss feeds of my blog. Also suddenly the bulletproof template broke, I see a blank line in between my header and the top of the page, the calendar is all messed up, and there are other parts of the blog that do not look like they should. If I remove the plugin everything works. I again suspect the php code, as I am looking the sql statements and it seems like it is selecting all categories from the categories table and inserting them to the categorytemplates table so that they all have the ability to be modified. Am I right? I think I can go through my blog and just set the properties of every single category, but I don't think this is the best possible solution.

Is there anyone at least remotely interested in fixing this, so that it can work with postgres, or at least helping me, because I really do not feel confident enough to fiddle with the code by myself.

[garvinhicking]

Hi!

Thanks for letting us know that here seems to be a postgresql issue.

Can you tell me what about this SQL statement:

Code: Select all

CREATE TABLE {$serendipity['dbPrefix']}categorytemplates (
                        categoryid int(11) default null,
                        template varchar(255) default null,
                        fetchlimit int(4) default null,
                        futureentries int(4) default null,
                        lang varchar(255) default null,
                        pass varchar(255) default null,
                        sort_order varchar(255),
                        hide_rss tinyint(1) default false
                    )

does not work on PostgreSQL? I thought this would be compatible SQL for all engines we support. Is it the tinyint() that misbehaves?

I believe the RSS feed breaks because of some code that was committed to issue a SUB-Select which does not seem to work in Postgresql. It's this part of the code:

Code: Select all

 $conds[] = ("(e.id NOT IN (SELECT e.id FROM {$serendipity['dbPrefix']}entries AS e
                            LEFT JOIN {$serendipity['dbPrefix']}entrycat AS ec ON ec.entryid = e.id
                            JOIN {$serendipity['dbPrefix']}categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))");

Do you maybe spot a problem with postgresql in this Syntax?

The same question applies to this SQL query:

Code: Select all

        $query = "SELECT
                    c.categoryid,
                    c.category_name,
                    c.category_icon
                  FROM {$serendipity['dbPrefix']}category AS c
            INNER JOIN {$serendipity['dbPrefix']}categorytemplates AS t
                    ON t.categoryid = c.categoryid
                 WHERE t.template != ''
              ORDER BY c.category_name ASC";

Actually, the plugin does not require you to have entries to the categorytemplates DB table. Only for those categories where you have a distinct template set to a category, you need an entry.

HTH,
Garvin

[ILF]

Hi Garvin, nice to finally meet (well kind of ) the man!

You are indeed right. The tinyint is what causes all the problems. Postgres sees the value of 1 and decides that the type should be boolean, which seems quite logical in my opinion. I do not remember what types does mysql support but I think this field can be set to boolean?

As far as it comes to the second snip of code, the reason is again the tinyint. Because we are trying to select hide_rss as 1, where in postgres it is TRUE/FALSE not 0/1 because the field is no longer tinyint but boolean.

The last one seems OK, even though I still have to run it in the DB to see if I'm right.

I'll fix the source for the second query so I can check what will happen to the rss feeds and especially if this will fix the outlook of the blog, because as I mentioned after the plugin was somewhat activated, the bulletproof was not working like it should anymore.

Edit:

OK, replaced 1 with TRUE, and the rss is working again. I have another question, if the hide_rss is set to true for a specific category, it hides it from the rss of the blog, but does it also turn off the rss for this specific category, because now the rss of the category is empty, although I have posts in it.

[garvinhicking]

Hi!

Hi Garvin, nice to finally meet (well kind of ) the man!

I feel flattered.

I think the best way would be to simply make it a varchar(4) - I believe a varchar(1) makes trouble on at least mysql. Using a varchar would then allow us to easily continue using '0' and '1' without needing to think about boolean typecasting. I've just committed such a variant to CVS, the update should be on spartacus within the next 24-48 hours.

I'll fix the source for the second query so I can check what will happen to the rss feeds and especially if this will fix the outlook of the blog, because as I mentioned after the plugin was somewhat activated, the bulletproof was not working like it should anymore.

I belive this happened due to a SQL error message that could be emitted?

OK, replaced 1 with TRUE, and the rss is working again. I have another question, if the hide_rss is set to true for a specific category, it hides it from the rss of the blog, but does it also turn off the rss for this specific category, because now the rss of the category is empty, although I have posts in it.

Actually, I believe so. Using 'hide_rss' disables every RSS feed for this category's entries. It's technically though harder to exclude those categories from the sidebar categories plugin, because this one does not know about a possible categorytemplates plugin...

Regards,
Garvin

[judebert]

Hi! Just popping in for a look because I programmed part of the plugin, and Garvin asked me to check.

Everything Garvin has said looks right on target to me. I have even less experience with SQL than you guys, but I remember looking for BOOLEAN in MySQL and not finding it. The documentation recommended using tinyint, so I went with that. I just assumed that, since MySQL recommended it, it would work with other databases. Naive of me, I suppose.

I checked out Garvin's commit, and it looks good to me. Will you have to change your BOOLEAN fields and values to interoperate with the new version, or will Postgres automatically cast the boolean to a string?

Don was once having a problem like this with feeds, and I'm glad you guys found the source. Thanks!

[ILF]

Hi again,

so here is what is going on, after Garvin commit the changes. I didn't want to wait for spartacus and checked out the latest source, so my plugin is now bumped to 0.31. I removed my table from the database, and now the proper table is created in postgres. It seems that everything works as far as code and sql is concerned, however I still experience the nasty side effect of totally messed up outlook of the blog. Garvin suggested that the problem is because of an error message most probably from the sql, so I checked the generated source of the page, however I don't see any error or warning message that could mess up with the way the blog looks. I will make couple of screenshots with the way the blog looks after installing the plugin, so I hope someone will recognize the problem just by the looks of it.

Judebert, postgres will not automatically cast the boolean to a string, but there is no need anyway, because so far the the table was not created anyway for anyone, and I converted my already .

Edit:

Before installing the plugin:

After installing the plugin:

[judebert]

If I had access to the web page, I could check it out with FireBug. You can do that, and tell us where the extra element comes from. Probably some javascript output I'm messing up.

I'm looking at the plugin now, just to see what I can see. Weird question: does this still happen after you log out, or is it restricted to the admin user?

Additionally, try turning off the 'fixcat' option in the plugin (although I'm pretty sure this will have no effect. If it does, I've just learned something about PHP!).

[ILF]

Hello Judebert,

I tried doing what you suggested, however, it seems the learning curve is too steep for me, and besides, even if I was able to find my way around FireBug, I wouldn't know what I'm looking for either. So you should have access now , check your PMs.

As far as it comes to loging out, yes it still happens. By 'fixcat' I suppose you mean "Globally set entry's category", or at least I hope it is that, if it is so, then my php skills are getting better, now I know what part of an array is what . If it is indeed that setting you are talking about, it was never set to Yes in the first place, so I decided to test it both ways to no avail though .

[judebert]

Yup, that's the one. Sorry it didn't work. I'll check out the test site and see what I can come up with.

[judebert]

Okay, this is weird. We need to get a real web designer involved.

As soon as I add width to the border of #serendipity_banner -- even 1px, regardless of color -- the space at the top of the banner disappears.

According to FireBug, it's margin or padding in the H1. But modifying those values does nothing.

Don? Any idea what's going on?

[garvinhicking]

Hi!

But in which regard is the categorytemplates causing such a CSS modification? I think it doesn't output anything like that? I'm stumped.

Regards,
Garvin

[judebert]

I know! I don't see it anywhere in the code; I'm assuming it interacts with BP in some way, possibly at the serendipity_smarty_init() call. The plugin calls it without any variables to assign.

I was hoping to see some additional element, or some additional style class, that I could search for in the plugin code. But it all looks plain-vanilla to me. Instead, I found how to remove the white space. It's a valid workaround: just add a 1px border to the banner.

But I still don't know why it happens. I'm hoping a CSS designer can tell me why it collapses when I add the border, and then I'll be closer to figuring it out.

[ILF]

Hi Judebert,

if adding a 1px border fixes the issue with the white line in the header, does it also fix the issue with the links that get purple (in my case, because of the purple color theme) border, and the fact that basically everything gets aligned to the left. Also the grey box under the pics disappears, which I suspect is probably a javascript(???) problem, but again I'm not sure at all about that .

Btw, could it be some bizarre combination of plugins that causes all this misbehavior of the css?

[judebert]

No, I missed those changes. Now I'm starting to suspect a CSS file is not included, probably because of the bare init in the template plugin.

Let me fool around a little more and see what else I can come up with.