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 wiki.d/$FullName
 12: 00.01 PageStore::ls merge wiki.d/$FullName
 13: 00.01 PageStore::ls end wiki.d/$FullName
 14: 00.01 PageStore::ls begin $FarmD/shared.d/$FullName
 15: 00.01 PageStore::ls end $FarmD/shared.d/$FullName
 16: 00.01 PageStore::ls begin $FarmD/wikilib.d/$FullName
 17: 00.01 PageStore::ls merge $FarmD/wikilib.d/$FullName
 18: 00.01 PageStore::ls end $FarmD/wikilib.d/$FullName
 19: 00.01 PageListSources end count=50
 20: 00.01 PageListSort pre ret=6 order=-time
 21: 00.01 MakePageList items count=50, filters=PageListSort
 22: 00.08 MakePageList post count=50, readc=50
 23: 00.08 PageListSort begin
 24: 00.08 PageListSort sort
 25: 00.08 PageListSort end
 26: 00.08 MakePageList end
 27: 00.08 FPLTemplate: FPLTemplateSliceList
 28: 00.08 FPLTemplate: FPLTemplateFormat
 29: 00.08 MarkupToHTML begin
 30: 00.10 MarkupToHTML end
 31: 00.10 FPLTemplate: Chain end
 32: 00.10 MarkupToHTML end
 33: 00.10 MarkupToHTML begin
 34: 00.10 FPLTemplate: Chain begin
 35: 00.10 FPLTemplate: FPLTemplateLoad
 36: 00.11 FPLTemplate: FPLTemplateDefaults
 37: 00.11 FPLTemplate: FPLTemplatePageList
 38: 00.11 MakePageList pre
 39: 00.11 PageListSources begin
 40: 00.11 PageStore::ls begin SQLite
 41: 00.11 PageStore::ls merge SQLite
 42: 00.11 PageStore::ls end SQLite
 43: 00.11 PageStore::ls begin wiki.d/$FullName
 44: 00.11 PageStore::ls merge wiki.d/$FullName
 45: 00.11 PageStore::ls end wiki.d/$FullName
 46: 00.11 PageStore::ls begin $FarmD/shared.d/$FullName
 47: 00.11 PageStore::ls end $FarmD/shared.d/$FullName
 48: 00.11 PageStore::ls begin $FarmD/wikilib.d/$FullName
 49: 00.11 PageStore::ls merge $FarmD/wikilib.d/$FullName
 50: 00.11 PageStore::ls end $FarmD/wikilib.d/$FullName
 51: 00.11 PageListSources end count=76
 52: 00.11 PageListSort pre ret=4 order=name
 53: 00.11 MakePageList items count=76, filters=
 54: 00.11 MakePageList post count=76, readc=0
 55: 00.11 PageListSort begin
 56: 00.11 PageListSort sort
 57: 00.11 PageListSort end
 58: 00.11 MakePageList end
 59: 00.11 FPLTemplate: FPLTemplateSliceList
 60: 00.11 FPLTemplate: FPLTemplateFormat
 61: 00.11 MarkupToHTML begin
 62: 00.14 MarkupToHTML end
 63: 00.14 FPLTemplate: Chain end
 64: 00.14 MarkupToHTML end
 65: 00.14 MarkupToHTML begin
 66: 00.14 FPLTemplate: Chain begin
 67: 00.14 FPLTemplate: FPLTemplateLoad
 68: 00.14 FPLTemplate: FPLTemplateDefaults
 69: 00.14 FPLTemplate: FPLTemplatePageList
 70: 00.14 MakePageList pre
 71: 00.14 PageListSources begin
 72: 00.14 PageStore::ls begin SQLite
 73: 00.14 PageStore::ls merge SQLite
 74: 00.15 PageStore::ls end SQLite
 75: 00.15 PageStore::ls begin wiki.d/$FullName
 76: 00.15 PageStore::ls merge wiki.d/$FullName
 77: 00.15 PageStore::ls end wiki.d/$FullName
 78: 00.15 PageStore::ls begin $FarmD/shared.d/$FullName
 79: 00.15 PageStore::ls end $FarmD/shared.d/$FullName
 80: 00.15 PageStore::ls begin $FarmD/wikilib.d/$FullName
 81: 00.15 PageStore::ls merge $FarmD/wikilib.d/$FullName
 82: 00.15 PageStore::ls end $FarmD/wikilib.d/$FullName
 83: 00.15 PageListSources end count=53
 84: 00.15 PageListSort pre ret=6 order=-time
 85: 00.15 MakePageList items count=53, filters=PageListSort
 86: 00.23 MakePageList post count=53, readc=53
 87: 00.23 PageListSort begin
 88: 00.23 PageListSort sort
 89: 00.24 PageListSort end
 90: 00.24 MakePageList end
 91: 00.24 FPLTemplate: FPLTemplateSliceList
 92: 00.24 FPLTemplate: FPLTemplateFormat
 93: 00.24 MarkupToHTML begin
 94: 00.25 MarkupToHTML end
 95: 00.25 FPLTemplate: Chain end
 96: 00.25 MarkupToHTML end
 97: 00.25 MarkupToHTML begin
 98: 00.26 MarkupToHTML end
 99: 00.36 MarkupToHTML end
100: 00.36 MarkupToHTML begin
101: 00.39 MarkupToHTML end
102: 00.39 MarkupToHTML begin
103: 00.39 MarkupToHTML end
104: 00.39 now