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

[Alex Eftimiades]

I have a few questions concerning the way the database is structured  
in the sqlite recipe:

	I was writing some custom pagelist functions that will take advantage  
of data stored in an sqlite database. I got it to retrieve pages based  
on the name=, group=, and link= statements, and I was just starting on  
getting them retrieved in orders determined by order= assuming the  
order is one of the columns in the database when I realized I would  
not be able to do an ORDER BY name for order=name because the pagename  
is stored and not the group and name separately.

	Before I go recoding the sqlite database and start worrying about  
transferring my wiki into the new database (backing up the old one of  
course), 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).

	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.

	I have not tried it, but there is documentation on an experimental  
sqliteCreateAggregate function 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 might, for example, make a function for unserializing the data  
in addattrib (assuming I do not have a new column created for each new  
attribute). This seems to be a new and experimental function, so I am  
not even sure if my php installation is updated enough for it, but it  
is worth considering.

The pagelist recipe is otherwise coming along very nicely. It has  
already sped up my wiki tremendously and I hope to finish it over the  
next couple of days so others can make use of it.

Thanks,
Alex
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pmichaud.com/pipermail/pmwiki-users/attachments/20120824/5ea31c50/attachment.html>