[pmwiki-users] About the columns used in the sqlite recipe.

[Petko Yotov]

Alex Eftimiades writes:
> I thought it would be wise to ask why it was coded this way in the  
> first place. Is there any particular reason you stored the pagename rather  
> than the group and name separately? I presume it could have something to do  
> with faster data retrieval, but I would not know why (especially considering  
> you are unserializing the addattrib column). 

IIRC there were some versions of the php+sqlite library at the time that  
didn't allow to create, or didn't enforce UNIQUE constraints on more than  
one field.

> especially considering you are unserializing the addattrib column

At the time I didn't find (and I still haven't found) a better way to store  
additional page attributes added by recipes which I may not know. It may be  
a better idea to have a table instead of a field, with the fields pagename,  
attrname, attrvalue. Like the existing PTVs table. When you retrieve a page,  
you make an additional query getting all attributes from the second table.

> I also thought about making the database add a column every time a new page  
> attribute appears rather than storing it in the serialized addattrib. I  
> assume that would not be practical for when you have lots of columns that are  
> only filled in a few rows, but it would speed up and expand on possible ORDER  
> BY queries. 

Adding a column and an index to a medium or large SQLite table may be very  
resource-intensive and lock the wiki for minutes. And you cannot remove  
columns if you made a mistake.

The SQLite PageStore class is primarily a PageStore class, something that  
stores and retrieves the content and attributes which are sent or requested  
by PmWiki.

In your case, because have a number of other requirements, it may be better  
to write some custom solution. You can base it on the existing cookbook  
recipe as long as you respect the GNU GPL license if you distribute it.

Here are a couple of thoughts:

1. Decide in advance about what columns you will have, if you can. You will  
save yourself a lot of nerves and hair.

2. If you use (:pagelist order=something:), PmWiki will try to order the  
list even if your database returns it ordered. This may cause it to request  
the full pages in order to extract the attributes for comparing, and may  
take the time and memory above the system limits. So, if your database can  
return an ordered list of pages, you may want to tell PmWiki not to try to  
order them, for example (:pagelist order=none sqlorder=whatever:)

3. Note also, that (:pagelist order=name:) for PmWiki is ordering by  
Group.Page, ie. a page GroupA.ZZZZ will appear before GroupB.AAAA. To order  
by {$Name} you need to write (:pagelist order=$Name:).

> I have not tried it, but there is documentation on an  
> experimental sqliteCreateAggregate function <URL:http://php.net/manual/en/pdo 
> .sqlitecreateaggregate.php>here. I was wondering whether there would be any  
> significant ramifications of using such custom functions while doing  
> pagelists so more work can be done while querying the database rather than in  
> php. I know that the custom aggregate function would be evaluated in php, but  
> I imagine it would be more efficient to do it that way while querying the  
> database rather than entirely with php.

I have heard that these "user defined functions" can be indeed very  
efficient. I haven't had yet the chance to work with them.

Petko