I have been trying to redesign some of the common pagelist
functionality to take advantage of an sqlite installation. You just
include it after you set your WikiDir to the custom SQLite pagestore
class. Like the person who sent out the updated pmwiki skin, I was
looking for some feedback/comments on the implementation and whether
you think this might help speed up pagelists on an sqlite installation
with a lot of pages.
This actually has not helped speed up my own pagelists as much as I
thought it would. In fact, I do not really notice a difference at all!
Then again, I only have about 550 pages on my site. Perhaps I would
notice more of a difference if I had several hundred thousand. All the
more reason I wanted some feedback on the implementation. Right now,
it takes care of the name=, group=, link=, and order= statements using
SQLite queries. I hope to extend it to do the page (text) variable
specifications and if statements as well.
It uses a few custom functions to do FmtPageName for less standard
order=$Name type declarations and to check if the link= is in the
targets of the current page it is looking at. I would assume that
would make the sqlite query closer to just using php, but I thought it
would make a difference doing the testing on each page as they are
read from the database rather than reading them all from the database
into memory and sifting through the results from there. I could be
wrong, and this might not help at all, or perhaps only help in certain
situations.
Let me know if you have any insight on this.
Thanks,
Alex
<?php if (!defined('PmWiki')) exit();
/**
This text is written for PmWiki; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 3
of the License, or (at your option) any later version.
See pmwiki.php for full details and lack of warranty.
*/
$PageListFilters['SQListSources']=$PageListFilters['PageListSources'];
$PageListFilters['PageListSources']=-1;
$PageListFilters['PageListTermsTargets']=-1;
//$WikiDir->ex('CREATE INDEX IF NOT EXISTS ix_pages_ctime ON pages(ctime);CREATE INDEX IF NOT EXISTS ix_pages_title ON pages(title);');
$WikiDir->DB->sqlitecreateFunction('FmtPageName', 'FmtPageName', 2);
$WikiDir->DB->sqlitecreateFunction('MatchPageName', 'MatchPageName', 2);
//$WikiDir->ex('CREATE INDEX IF NOT EXISTS ix_pages_group ON pages(FmtPageName(\'$Group\',name));');
function SQListSources(&$list, &$opt, $pn, &$page) {
global $SearchPatterns, $WikiDir;
StopWatch('PageListSources begin');
## add the list= option to our list of pagename filter patterns
$db=&$WikiDir->DB;
if (@$opt['group']) $opt['=pnfilter'][] = FixGlob($opt['group'], '$1$2.*');
if (@$opt['name']) $opt['=pnfilter'][] = FixGlob($opt['name'], '$1*.$2');
//SDV($opt['order'],array('name'));
if (@$opt['trail']) {
$trail = ReadTrail($pn, $opt['trail']);
$tlist = array();
foreach($trail as $tstop) {
$n = $tstop['pagename'];
$tlist[] = $n;
$tstop['parentnames'] = array();
PCache($n, $tstop);
}
foreach($trail as $tstop)
$PCache[$tstop['pagename']]['parentnames'][] =
@$trail[$tstop['parent']]['pagename'];
if (!@$opt['=cached']) $list = MatchPageNames($tlist, $opt['=pnfilter']);
}
elseif (!@$opt['=cached']) {
foreach($opt['=pnfilter'] as $pat){
foreach(preg_split('/\s?,\s?/',$pat,-1,PREG_SPLIT_NO_EMPTY) as $p){
$p= str_replace('/', '.', $p);
if(in_array($p{0},array('-','!'))){
$p=$db->quote(substr($p,1));
$and[] = "NOT name GLOB $p";
continue;
}
$p=$db->quote($p);
$or[] = "name GLOB $p";
}
$and = count($and)? "(". implode(" AND ", $and).")" : '';
$or = count($or)? "(". implode(" OR ", $or).")" : '';
if("$and$or"==''){continue;}
if($or && $and) {$and .= ' AND ';}
if($or || $and) $q[]="($and$or)";
}
if (@$opt['link']) {
$link=$db->quote(MakePageName($pn, $opt['link']));
$db->sqlitecreateFunction('in_list', 'in_list', 2);
$q[]="in_list($link,targets)";
}
$o=$db->query("PRAGMA table_info(pages);")->fetchAll();
foreach($o as $x)$standard_orders[]=$x['name'];
array_pop($standard_orders);
foreach(explode(',',$opt['order']) as $order){
$order=trim($order);
foreach($standard_orders as $standard_order){
$regex="/(-)?$standard_order/";
if(preg_match($regex,$order,$matches)){
$sqlorder=preg_replace($regex,$standard_order.($matches[1]?" DESC":""),$order);
break;
}
}
$regex="/(-)?(\$?[^, -]+)/";
if(@!$sqlorder and preg_match($regex,$order,$matches)){
$sqlorder=preg_replace($regex,"FmtPageName({$matches[2]},name)".($matches[1]?" DESC":""),$order);
$db->sqlitecreateFunction('FmtPageName', 'FmtPageName', 2);
}
$regex="/(-)?group/";
if(@!$sqlorder and preg_match($regex,$order,$matches)){
$sqlorder=preg_replace($regex,'FmtPageName(\'\$Group\',name)'.($matches[1]?" DESC":""),$order);
}
if($sqlorder)$orders[]=$sqlorder;
else {unset($orders); break;}
}
if(@$orders){
$orders='ORDER BY '.implode(',',$orders);
$GLOBALS['$OLDPageListFilters']['PageListSort']=$PageListFilters['PageListSort'];
$PageListFilters['PageListSort']=-1;
}
$q=implode(' AND ',$q);
$q="SELECT name FROM pages WHERE (($q) AND MatchPageName(name,".$db->quote(implode(';',array_values($SearchPatterns[$opt['list']]))).")) $orders".(@$opt['count']?" LIMIT {$opt['count']}":'');
//$list = MatchPageNames(SQListPages($opt['=pnfilter'],$q,$opt['link']),$SearchPatterns[$opt['list']]);
$list=$WikiDir->q9($q);
}
StopWatch("PageListSources end count=".count($list));
return 0;
}
function in_list($x,$y){
return in_array($x,explode(',',$y));
}
function MatchPageName($page, $pat) {
$pat=explode(';',$pat);
global $Charset, $EnableRangeMatchUTF8;
# allow range matches in utf8; doesn't work on pmwiki.org and possibly elsewhere
$pcre8 = (IsEnabled($EnableRangeMatchUTF8,0) && $Charset=='UTF-8')? 'u' : '';
foreach((array)$pat as $p) {
$p=trim($p);
if (!$p) continue;
switch ($p{0}) {
case '/':
if(!preg_match($p, $page)) return 0;
continue;
case '!':
if(preg_match($p, $page)) return 0;
continue;
default:
list($inclp, $exclp) = GlobToPCRE(str_replace('/', '.', $p));
if ($exclp)
if(preg_match("/$exclp/i$pcre8", $page)) return 0;
if ($inclp)
if(!preg_match("/$inclp/i$pcre8", $page)) return 0;
}
}
return 1;
}
/*
function SQListPages($pat=NULL,$query=NULL,$link=NULL) {
global $WikiLibDirs,$PageListFilters;
foreach((array)$WikiLibDirs as $dir)
if (get_class($dir)=='PageStoreSQLite' and $query)
$out = array_unique(array_merge($dir->q9($query),(array)@$out));
else{
$preout = array_unique(array_merge($dir->ls($pat),(array)@$out));
if(@$link)
foreach($preout as $pn){
$page=ReadPage($pn, READPAGE_CURRENT);
if(in_array($link,$page['targets'])) $out[]=$pn;
}
else $out=$preout;
$PageListFilters['PageListSort']=$GLOBALS['$OLDPageListFilters']['PageListSort'];
}
return $out;
}
*/
_______________________________________________
pmwiki-users mailing list
[email protected]
http://www.pmichaud.com/mailman/listinfo/pmwiki-users
