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

[Alex Eftimiades]

Thanks for the quick reply.

As for writing a custom script, I would strongly prefer not to because  
I would be the only one to benefit from it. That being said, and given  
what you told me, I may decide to fork the sqlite recipe to better  
support a pagelist recipe to take advantage of it. That will be a last  
resort, unless of course there are enough things to be improved upon  
on the existing recipe that it would be beneficial to myself and the  
community to provide an alternative solution.

That being said, I thought of two more questions that would be useful  
to know before I decide on what route to take:

*Page Ids: What exactly was the point of making separate page IDs when  
you already have an index? I have seen this in a few example SQL  
tables before, and I get the feeling that adding an a separate index  
is common practice.
*The way pages are deleted: Why not just have a boolean column that  
indicates whether a page is deleted or not? That seems more efficient  
better for querying than renaming the pagename.

Also, I have every intention of using the GNU general public license  
if/when I distribute this.

Thanks,
Alex

On Aug 24, 2012, at 6:16 PM, Petko Yotov wrote:

> 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
>
>
> _______________________________________________
> pmwiki-users mailing list
> pmwiki-users at pmichaud.com
> http://www.pmichaud.com/mailman/listinfo/pmwiki-users