SQLite

Get it at Path:/cookbook/sqlite.txt

This is a new PageStore class for PmWiki. It stores the wiki pages in a SQLite database. Requires PHP 5.1 or newer with PDO_SQLITE (either compiled in, or shared).

This wiki here is using the recipe for some weeks now.

See full documentation at pmwiki.org: Cookbook:SQLite.

Additional features

  • Done. Possibility to search through all the revision history, by Author or IP address (params user=, days=, ip= in the searchbox or in a pagelist). Maybe: This could possibly change how RecentChanges pages currently work.
  • Done. Optimized handling of the page history: it is now possible to have thousands of page revisions per page without hitting the PHP memory limit. The page history is paginated. See SQLite?action=diff.
  • Done. Even shorter urls than the Short urls, like in tinyurl: http://galleries.accent.bg/R/I (this will be a recipe based on a unique page-id in the database).
  • Done. Different approach to page deletions and restauration - instead of deleting the page, it is renamed and then locked: an admin can still review it, edit it or restore it from within the wiki (no need for FTP).
  • Done Ability to call user defined functions at several points of the processing (while writing a page). In the $SQLiteWriteFunctions array you add functions to be called at some point of the processing. (Docs to be written.)

Todo (ASAP)

  • Ability to export a page/the whole wiki in pmWiki's format, possibly into "$WorkD/SQLite-export/" directory.
  • Check the PmWiki import recipes to see if they work.

Other feasable possibilities (MAYBE)

  • It is very easy to extend the database to store and index PageTextVariables and Links and make PageList searches times faster than the built-in ones. Currently, the recipe creates these two additionnal tables but is not filling them or browsing them. (This will be user defined recipes wih the SQLiteWriteFunctions array)
  • It will be possible to further extend the database by creating custom tables gathering specified wiki-page data, and to search and retrieve this data even faster. (This will be user defined recipes wih the SQLiteWriteFunctions array)

Sandbox

* {*$page_id} {*$ShortName}
* [[{*$ShortPage}]] [[r.a]] [[r.2]] [[r.z]] [[r.zzz]]
* {*$ShortURL}

See also Site.LocalTemplates for the new fmt=# templates.

List of deleted pages with links to view/edit and see their history, and to undelete them.

(:pagelist list=deleted fmt=#deletedpages order=-time count=-2:)

List of all pages, and number of their revisions (only pages inside the database; we have days=99999 which is more than the oldest edit, so all get shown).

(:pagelist days=99999 fmt=#diffsearch count=10 group=Cookbook,Thumblist2:)

List of pages edited by others (not me) in groups other than Thumblist*. (:zmarkup:)(:pagelist user=-Petko group=-Thumblist* fmt=#diffsearch count=6:)

Pages (at least once) edited by "simon". (:zmarkup:)(:pagelist user=simon fmt=#diffsearch:)

Pages (at least once) edited by any of these IP ranges. (:zmarkup:)(:pagelist ip=202.37.*,203.97* fmt=#diffsearch:)

{=$LastModifiedMajor} and {=$LastModified} are sometimes not the same. You could need to use a {=$LastModifiedMajor} timestamp while publishing an article or a blog entry; subsequent "minor" edits will not change the "major" timestamp.

(:pagelist group=Cookbook fmt=#timemajor order=-time list=normal count=-5:)
 0: 00.00 MarkupToHTML begin
 1: 00.00 MarkupToHTML begin
 2: 00.00 FPLTemplate: Chain begin
 3: 00.00 FPLTemplate: FPLTemplateLoad
 4: 00.01 FPLTemplate: FPLTemplateDefaults
 5: 00.01 FPLTemplate: FPLTemplatePageList
 6: 00.01 MakePageList pre
 7: 00.01 PageListSources begin
 8: 00.01 PageStore::ls begin SQLite
 9: 00.01 PageStore::ls merge SQLite
10: 00.01 PageStore::ls end SQLite
11: 00.01 PageStore::ls begin $FarmD/shared.d/$FullName
12: 00.01 PageStore::ls end $FarmD/shared.d/$FullName
13: 00.01 PageStore::ls begin $FarmD/wikilib.d/$FullName
14: 00.01 PageStore::ls merge $FarmD/wikilib.d/$FullName
15: 00.01 PageStore::ls end $FarmD/wikilib.d/$FullName
16: 00.01 PageListSources end count=50
17: 00.01 PageListSort pre ret=6 order=-time
18: 00.01 MakePageList items count=50, filters=PageListSort
19: 00.09 MakePageList post count=50, readc=50
20: 00.09 PageListSort begin
21: 00.09 PageListSort sort
22: 00.09 PageListSort end
23: 00.09 MakePageList end
24: 00.09 FPLTemplate: FPLTemplateSliceList
25: 00.09 FPLTemplate: FPLTemplateFormat
26: 00.09 MarkupToHTML begin
27: 00.11 MarkupToHTML end
28: 00.11 FPLTemplate: Chain end
29: 00.11 MarkupToHTML end
30: 00.11 MarkupToHTML begin
31: 00.11 FPLTemplate: Chain begin
32: 00.11 FPLTemplate: FPLTemplateLoad
33: 00.11 FPLTemplate: FPLTemplateDefaults
34: 00.11 FPLTemplate: FPLTemplatePageList
35: 00.11 MakePageList pre
36: 00.11 PageListSources begin
37: 00.11 PageStore::ls begin SQLite
38: 00.11 PageStore::ls merge SQLite
39: 00.11 PageStore::ls end SQLite
40: 00.11 PageStore::ls begin $FarmD/shared.d/$FullName
41: 00.11 PageStore::ls end $FarmD/shared.d/$FullName
42: 00.11 PageStore::ls begin $FarmD/wikilib.d/$FullName
43: 00.11 PageStore::ls merge $FarmD/wikilib.d/$FullName
44: 00.11 PageStore::ls end $FarmD/wikilib.d/$FullName
45: 00.11 PageListSources end count=76
46: 00.11 PageListSort pre ret=4 order=name
47: 00.11 MakePageList items count=76, filters=
48: 00.11 MakePageList post count=76, readc=0
49: 00.11 PageListSort begin
50: 00.11 PageListSort sort
51: 00.11 PageListSort end
52: 00.11 MakePageList end
53: 00.11 FPLTemplate: FPLTemplateSliceList
54: 00.11 FPLTemplate: FPLTemplateFormat
55: 00.11 MarkupToHTML begin
56: 00.15 MarkupToHTML end
57: 00.15 FPLTemplate: Chain end
58: 00.15 MarkupToHTML end
59: 00.15 MarkupToHTML begin
60: 00.15 FPLTemplate: Chain begin
61: 00.15 FPLTemplate: FPLTemplateLoad
62: 00.15 FPLTemplate: FPLTemplateDefaults
63: 00.15 FPLTemplate: FPLTemplatePageList
64: 00.15 MakePageList pre
65: 00.15 PageListSources begin
66: 00.15 PageStore::ls begin SQLite
67: 00.15 PageStore::ls merge SQLite
68: 00.16 PageStore::ls end SQLite
69: 00.16 PageStore::ls begin $FarmD/shared.d/$FullName
70: 00.16 PageStore::ls end $FarmD/shared.d/$FullName
71: 00.16 PageStore::ls begin $FarmD/wikilib.d/$FullName
72: 00.16 PageStore::ls merge $FarmD/wikilib.d/$FullName
73: 00.16 PageStore::ls end $FarmD/wikilib.d/$FullName
74: 00.16 PageListSources end count=53
75: 00.16 PageListSort pre ret=6 order=-time
76: 00.16 MakePageList items count=53, filters=PageListSort
77: 00.22 MakePageList post count=53, readc=53
78: 00.22 PageListSort begin
79: 00.22 PageListSort sort
80: 00.22 PageListSort end
81: 00.22 MakePageList end
82: 00.22 FPLTemplate: FPLTemplateSliceList
83: 00.22 FPLTemplate: FPLTemplateFormat
84: 00.22 MarkupToHTML begin
85: 00.23 MarkupToHTML end
86: 00.23 FPLTemplate: Chain end
87: 00.23 MarkupToHTML end
88: 00.23 MarkupToHTML begin
89: 00.24 MarkupToHTML end
90: 00.33 MarkupToHTML end
91: 00.34 MarkupToHTML begin
92: 00.36 MarkupToHTML end
93: 00.36 MarkupToHTML begin
94: 00.37 MarkupToHTML end
95: 00.37 now