• View
• Edit
• History
• Attach
• Print
• Backlinks

SQLite PageStore class

Questions answered by this recipe

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

Table of contents

• Description | Installation
• Configuration | Short URLs | Search history | Optimized history | Deleting pages |
  Optimizing the database | User functions | Other configurations
• To do and maybe | Notes | Changelog | See also | Author | Comments

Description

An extended 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 a number of benefits when using a SQLite database:

• Easier back-up (just one file per wiki field).
• 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).
• It is possible to index PageTextVariables, links and possibly other attributes/values while saving a page, and in pagelists/searches to use an extremely fast database query to retrieve a list of matching pages, instead of opening and parsing every file like PmWiki currently does.

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

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 20110718Δ. (Alt. download: sqlite.txt.)

See Also

• DataPlates, DataQuery, DQReport, DatabaseStandard

Author

• Petko

Comments

Feedback/comments are welcome at SQLite-Talk.