PmWiki | Cookbook / SQLite PageStore class (original) (raw)

Summary: Store wiki pages in an SQLite database file

Version: 20190903

Prerequisites: PmWiki 2.2.58, PHP 5.1

Status: Experimental

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:

There are some drawbacks:

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:

include_once("$FarmD/cookbook/sqlite.php"); WikiDir=newPageStoreSQLite(WikiDir = new PageStoreSQLite(WikiDir=newPageStoreSQLite(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:

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.

Other configurations

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

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

Notes

Release notes / Change log

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

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.