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