SQLite PageStore class

Summary: Store wiki pages in an SQLite database file
Version: 20190903
Prerequisites: PmWiki 2.2.58, PHP 5.1
Status: Experimental
Maintainer: Petko
Discussion: SQLite-Talk
License: GPL3+

PmWiki stores pages in normal text files. Can it store pages and metadata in an SQLite database file?

Description

An experimental PageStore class to save wiki pages in an SQLite database file.

This is a new PageStore class for PmWiki. It stores the wiki pages in a SQLite database file. Requires PHP 5.1 or newer with PDO_SQLITE.

There are some benefits when using a SQLite database:

  • No more problems with filenames in different alphabets, directories and permissions. A website can be transferred to a new server/operating system without the risk of losing the files.
  • Probably faster than using many files on the harddisk, especially in searching/pagelists (new generations of filesystems like Reiser4 may be very fast too, however opening/reading/closing two dozens of files is slower than reading them with SQLite).

There are some drawbacks:

  • impossible to do incremental backups of the content: the full database must be downloaded every time
  • while the database is very fast for hundreds of thousands of pages, it has not been well tested with many users reading and writing pages at the same time: thus performance may be not as good as with the standard PmWiki wiki.d flatfiles on high traffic websites
  • currently there is no extensively tested way to import existing wiki pages into a database file
  • a function for exporting the pages from the database to the standard wiki.d flatfile format is not yet written.

SQLite is not a large-scale server-client database. SQLite is very small and fast with no network latency (the data does not pass in a network stream or a socket, just between function calls). SQLite is also ACID-compliant which makes data corruption or loss highly improbable, even in a power crash. The SQLite database system is tested very seriously by the authors and is very widely used in free and commercial software projects.

See also this comment and this one at the mailing list.

Installation

This recipe requires PHP 5.1 or newer with PDO_SQLITE. It does not depend on other pmwiki recipes or database layers such as AdoDB.

This is still a preview release, intended to be tested by experienced administrators.

To install the recipe, please:

  • Download the scriptΔ and place it in your /cookbook/ directory. (Alt. download: sqlite.txt.)
  • Place such a code near the beginning of your local/config.php file:
include_once("$FarmD/cookbook/sqlite.php");
$WikiDir = new PageStoreSQLite($WorkDir.'/pmwiki.sqlite.db', 1);
$WikiLibDirs = array(
  &$WikiDir,
  new PageStore('wiki.d/{$FullName}'),
  new PageStore('$FarmD/wikilib.d/{$FullName}')
);

The code above should be placed before including other recipes and scripts, notably before AuthUser, Internationalizations (XLPage) and UTF-8.

When you create a new page, or when you edit and save an existing page, it will be stored, including its full history, in the 'pmwiki.sqlite.db' database file inside your work directory.

Configuration and additional features

Very Short URLs

Every page in the database has a unique numeric ID. Every page can be accessed via this unique ID, and have a very short, case-insensitive URL. This feature can be very usefull for international wikis which sometimes have extremely long encoded urls.

This feature is now integrated in the recipe, but disabled by default. To enable it, you need to select a "Redirect Group" in config.php:

 $RedirectGroup = "R";

Then, all your short urls will be in the wiki group R/, like: site.com/R/1, site.com/R/12, etc. It also becomes impossible to manually save any pages in the R/ wiki group.

There are three new page variables that contain the "short url" and can be used to display it:

  • {*$ShortName} : a page name like "7"
  • {*$ShortPage} : a full group.page name like "R.7"
  • {*$ShortURL} : a full page URL like "site.com/R/7"

If the page is not (yet) in the database, these variables contain the ordinary FullName and PageUrl variables.

The short URLs are compacted to Base_36 integers (digits and letters), so 46655 pages fit in 3-digit case-insensitive page IDs.

Search full revision history by author or ip

PHP has removed the function sqlite_escape_string in recent versions, so these may not work for you, until I find the time for a major rewrite.

With this recipe, PmWiki accepts additional parameters user=, ip=, days= in searchboxes or in pagelists to find pages that match a username, an IP address for the fill revision history or for a number of days back.

This search is fully compatible with pagelist templates. The new page variable {=$RevMatches} contains the number of revisions that match the requested criteria.

The search containing user=, ip=, days= is only performed on pages inside the database; if you have older pages in flat files, they will not be searched.

Optimized 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. Demo.

Ten revisions per page are displayed by default. To change them, for example to 20, add in config.php:

 $DiffCountPerPage = 20;

This feature is similar to the recipe LimitDiffsPerPage but it actually sends the sliced page history. (LimitDiffsPerPage with the original PageStore gets and sends the full page history in a variable that later gets sliced. If the page history is extremely huge, it may hit the PHP memory limits and editing the page or viewing the history may cause pmwiki to crash. With the SQLite recipe this problem cannot happen.)

Deleting pages

The SQLite recipe offers a different approach to page deletions and restorations. 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).

The deleted page is renamed to add a suffix "-deleted-" and a unique timestamp. The new page is locked so that only the wiki administrator can read/edit/restore them.

These deleted pages are not included in pagelists and searches, unless the keyword list=deleted is used.

The new page variable {$oFileName} contains the original file name of the deleted page, and can be used in the page or in pagelists.

There is a new action=undelete which can rename back the page with the whole history (the admin needs to manually unlock the page attributes).

If a "deleted" page is deleted, it is fully removed from the database and cannot be recovered.

Optimizing the database

When an SQLite database is heavily edited with many records (wiki pages) modified or deleted, it becomes fragmented and slower. To optimize it, an administrator can call from time to time a page with the action "vacuum": site.org/pmwiki.php?action=vacuum.

From version 20081216 on, the recipe performs automatic optimizations of the database every 50 page writes. Upon editing, PmWiki writes not only the edited page, but also a couple of RecentChanges pages, so an optimization happens approximately every 15-20 page edits. The config.php variable that controls this is $SQLiteAutoVacuum:

 $SQLiteAutoVacuum = 50;  # every 50 page writes, default
 $SQLiteAutoVacuum = 300; # every 300 writes = about 100 edits,
 $SQLiteAutoVacuum = 0;   # disable auto optimizations

If you disable automatic optimizations, you can still use the ?action=vacuum to manually optimize the database.

User defined functions

(Developpers only.) It is possible to call user defined functions at several points of the processing (while writing a page).

The $SQLiteWriteFunctions array contains the names of the functions to be called while writing a page. It should be defined like:

 $SQLiteWriteFunctions[10] = 'MyFirstFunction';
 $SQLiteWriteFunctions[20] = 'MySecondFunction';
 $SQLiteWriteFunctions[200] = 'MyLastFunction';

MyFirstFunction(&$page) returns a string (query) to be executed; it can also modify the $page array with the page text and metadata.

These functions will be sorted and executed by key.

  • If the key is <100, the function will be called before adding the page content and history, and it could change the $page array. The string it returns will be placed in the beginning of the SQL transaction.
  • If the key is >100, the function will be called after adding the page content and history. The string it returns will be placed in the end of the SQL transaction (the string can be several queries, each terminated by ";"). It can be used for indexing data/links in external tables.

Other configurations

(All added to config.php before including the sqlite.php file.)

  • $EnableSQLiteEmptyDiffs = 0; -- if a page diff is empty (no changes were made to the page text) it is not saved in the page history; default is 1 and the diff is saved.
  • $SQLiteDeletedPagesAttr = ""; -- the attributes of a deleted page; default is '@lock', and the page can only be viewed/edited by the administrator; set to "" to not change the page attributes.
  • $SQLiteCreateQuery = 'BEGIN TRANSACTION; ... COMMIT;'; -- change the initial database query which creates the database tables; you should know what you're doing.

There is a new page variable {$LastModifiedMajor} that contains the time of the last "major" edit of the page (when the "minor edit" checkbox is not checked).

If your wiki uses Internationalizations, you can translate these strings in your XLPage, for example in French:

  'Page deleted' => 'Page supprimée',
  'Page undeleted' => 'Page restaurée',
  'Show all' => 'Afficher tout',

See Internationalizations for details.

Todo and maybe

  • Ability to export a page/the whole wiki in pmWiki's format, possibly into "$WorkD/SQLite-export/" directory.
  • An experimental recipe for quick import of all pages from wiki.d directories into the sqlite database was written. MergeWikiLibDirs.

Notes

  • Other pmwiki recipes which use the standard functions to retrieve and store pages and data should work fine with SQLite.
  • The recipe is developped and only tested with the PmWiki 2.2 beta series. It is tested and works with UTF-8.
  • Uploaded files are not stored in the database but on the filesystem as usual.
  • If you are using a different PageStore object like PerGroup sub directories or Compressed PageStore, you may need to adapt the code that installs the SQLite recipe.

Release notes / Change log

Current (recommended) version is 20190903Δ. (Alt. download: sqlite.txt.)

  • 20190903 Update for PHP 7.4.
  • 20180106 : On installations that lack the sqlite_escape_string function, the recipe will no longer cause fatal errors, even if the search enhancements will not work.
  • 20171108 : Update for PHP 7.0-7.2.
  • 20141207 : Update for PHP 5.5 compatibility.
  • 20110718 : Fixed problem with unfiltered $pagename that could allow an SQL injection (PITS:01261, reported by Michael Engelke). All wikis using the script should upgrade.
  • 20100219 : Fixed history pagination both for PmWiki 2.2.13 (default to source view) and earlier (default to output view).
  • 20090422 : fixed bug with Short URLs introduced with 20090414.
  • 20090414 : improvement to allow multiple $WikiLibDirs SQLite files
  • 20090206 : a save optimization (if an existing page is requested to be written with history, most likely by a recipe)
  • 20090130 : fixed duplicating history records for edits by custom ?action= calls from recipes.
  • 20090102 : a bug was fixed for Drafts mode.
  • 20081216 : Added $SQLiteAutoVacuum functionnality; minor optimizations.
  • 84M (2008-04-22) : Changed the way ShortUrls are processed, to avoid unexpected redirects from pagelists/includes.
  • 84Ea (2008-04-14) : $SQLiteSkipEmptyDiffs changed to $EnableSQLiteEmptyDiffs (to follow a PmWiki standard on such variables)
  • 84E (2008-04-14) : Fixed a bug that removed all edit history on changing the page attributes (caught by Smc); fixed a bug that logged a diff even for service pages like RecentChanges; international strings (Page deleted/undeleted) are now translated.
  • 835 (2008-03-05) : Added {$ShortName} variable, fixed {$LastModifiedMajor}
  • 834 (2008-03-04) : Release on pmwiki.org after 4 months of testing and tweaking.
  • The recipe was first written in November 2007 and since then is available and used on a few low-traffic wikis.

See Also

Author

Comments

Feedback/comments are welcome at SQLite-Talk.

User notes? : If you use, used or reviewed this recipe, you can add your name. These statistics appear in the Cookbook listings and will help newcomers browsing through the wiki.