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
[email protected]
http://www.pmichaud.com/mailman/listinfo/pmwiki-users
