Extension talk:DocumentApproval

From MediaWiki.org
Jump to navigation Jump to search

Multiple approvals not working[edit]

I had to make some changes to get multiple approvals to work. Below is the diff to show what I changed.

214a215
>
303,304c304
<               $req_userid = 0;
<
---
>
306,307c306,307
<                 $sql =  "SELECT description FROM ". $dbr->tableName('approval_request');
<                 $sql .= " WHERE page_id = " . $pageid . " AND user_id = " . $userid;
---
>                 $sql =  "SELECT user_id, description FROM ". $dbr->tableName('approval_request');
>                 $sql .= " WHERE page_id = " . $pageid;
313,314c313,314
<                     $req_userid = $userid;
<                     $req_description = $row[0];
---
>                     $req_userid = $row[0];
>                     $req_description = $row[1];
563d562
< ?>

Hope that helps. It's a good extension. --Paul 01:27, 12 February 2008 (UTC)

Question about roles[edit]

How do I set the roles? And does it work that any user with that role can sign a document or does the user have to be defined as well as the role?

Issue with linefeeds (/n)[edit]

I modified the function fnDocumentApproval and moved the assignment of $function inside the if check for $login because when $requests was assigned from splitting $inputs on "\n" the first and last value in the array is blank and thus there is no value for $function = $request[1].

.. FROM ..

function fnDocumentApproval( $input, $argv, &$parser ) {

  daTableCheck();
  $requests=split("\n",$input);
..
..
   foreach($requests as $request)
    {
        $request=split(";",trim($request));
        $login = $request[0];
        $function = $request[1];
 
        if ($login != "")
        {
            $counter++;
 
            // Gets userid
            $userid = getUserIDFromUserText($login);
 

.. TO ..

function fnDocumentApproval( $input, $argv, &$parser ) {

  daTableCheck();
  $requests=split("\n",$input);
..
..
   foreach($requests as $request)
    {
        $request=split(";",trim($request));
        $login = $request[0];
 
        if ($login != "")
        {
            $function = $request[1];
            $counter++;
 
            // Gets userid
            $userid = getUserIDFromUserText($login);
 

--Chris Wolcott , May 2008 (UTC)

Sign Tab displayed incorrectly[edit]

If a user is not logged in the sign tab is being displayed. We are currently using Mediawiki 1.12.0 and php 5.2.0

Changed the daSkinTemplateTabs function in the file DocumentApproval.body.php to check if a user is current logged in.

.. FROM ..

function daSkinTemplateTabs( &$skin, &$content_actions ) {
        global $wgRequest, $wgTitle;
 
        $action = $wgRequest->getText( 'action' );
 
        // Check if the user are elegible for singing this page
        $revisionid = $wgTitle->getLatestRevID();
 
        $revision = Revision::newFromID($revisionid);
 
        if ($revision)
        {
        //    $revisionid = $revision->getId();
                $pageid = $revision->getPage();
                $user = User::newFromSession();
                $userid = $user->getID();
 
            $dbr = wfGetDB( DB_SLAVE );
 
            // Check if this user can sign the document
            $sql =  "SELECT user_id FROM ". $dbr->tableName('approval_request');
            $sql .= " WHERE page_id = " . $pageid;
            $sql .= " AND user_id = " . $userid;
 
            $reqUserId = "";
 
            $res = $dbr->query( $sql, __METHOD__ );
            if ($dbr->numRows($res))
            {
                $row = $dbr->fetchRow($res);
                        $reqUserID = $row[0];
                }
            $dbr->freeResult($res);
 
                // If user is elegible for singning, displays the sign tab
            if ($reqUserID == $userid)
                {
                        $content_actions['sign'] = array(
                                'class' => ($action == 'sign') ? 'selected' : false,
                                'text' => "sign",
                                'href' => $skin->mTitle->getLocalURL( 'action=sign' )
                        );
                }
        }
        return true;
}

.. TO ..

function daSkinTemplateTabs( &$skin, &$content_actions ) {
        global $wgRequest, $wgTitle;
 
        $action = $wgRequest->getText( 'action' );
 
        // Check if the user are elegible for singing this page
        $revisionid = $wgTitle->getLatestRevID();
 
        $revision = Revision::newFromID($revisionid);
 
        if ($revision)
        {
        // $revisionid = $revision->getId();
           $pageid = $revision->getPage();
           $user = User::newFromSession();
           $userid = $user->getID();
 
           // MOD - Check if there is a user logged-on
           if ($userid) {
              $dbr = wfGetDB( DB_SLAVE );
 
              // Check if this user can sign the document
              $sql =  "SELECT user_id FROM ". $dbr->tableName('approval_request');
              $sql .= " WHERE page_id = " . $pageid;
              $sql .= " AND user_id = " . $userid;
 
              $reqUserId = "";
 
              $res = $dbr->query( $sql, __METHOD__ );
              if ($dbr->numRows($res)) {
                 $row = $dbr->fetchRow($res);
                        $reqUserID = $row[0];
              }

              $dbr->freeResult($res);
 
              // If user is elegible for singning, displays the sign tab
              if ($reqUserID == $userid) {
                 $content_actions['sign'] = array(
                                'class' => ($action == 'sign') ? 'selected' : false,
                                'text' => "sign",
                                'href' => $skin->mTitle->getLocalURL( 'action=sign' ));
              }
           }
        }
        return true;
}

--Chris Wolcott 12:48 , 27 May 2008 (UTC)


No Sign tab displayed[edit]

As of MediaWiki 1.18, the SkinTemplateTabs Hook is deprecated.

In DocumentApproval.php I had to change $wgHooks['SkinTemplateTabs '][] = 'daSkinTemplateTabs'; to $wgHooks['SkinTemplateNavigation'][] = 'daSkinTemplateTabs';

And in DocumentApproval.body.php I had to change the Array name $content_actions['sign'] to $content_actions['views']['sign'] (just added the ['views'] hierarchy).
$skin->mTitle->getLocalURL() also doesn't seem to work anymore, so the whole block to add the "Sign"-tab is for me:

$href = $skin->makeArticleUrlDetails($skin->getTitle()->getFullText(), 'action=sign' );
$content_actions['views']['sign'] = array( // was $content_actions['sign'] before
          'class' => ($action == 'sign') ? 'selected' : false,
          'text' => wfMsg('sign-tab-text'),
          'href' => $href['href']); // was $skin->mTitle->getLocalURL( 'action=sign' ) before

First page save issue[edit]

If <approval> is added to a new document the I function fnDocumentApproval fails because there is no revision for this page yet (because it has not been saved). Thus I modified fnDocumentApproval to check for a null $revision and return false.

.. FROM ..

function fnDocumentApproval( $input, $argv, &$parser ) {

        daTableCheck();

        $requests=split("\n",$input);

    // get document full title
    $title = $parser->getTitle();

    $revision = Revision::newFromTitle($title);
    $revisionid = $revision->getId();
    $pageid = $revision->getPage();
..
..
 

.. TO ..

function fnDocumentApproval( $input, $argv, &$parser ) {

        daTableCheck();

        $requests=split("\n",$input);

    // get document full title
    $title = $parser->getTitle();

    $revision = Revision::newFromTitle($title);
    if (is_null($revision)) return false;

    $revisionid = $revision->getId();
    $pageid = $revision->getPage();
..
..

--Chris Wolcott 09:48 , 29 May 2008 (UTC)


An improvement but still not perfect[edit]

I have made the above modification in the DocumentApproval.body.php file which does allow me to at least save the newly created page with the approval details included (instead of it not saving as before). However the approval table and sign tab don't appear on the initial save. If I go back into the page and simply hit save page again they appear.

Is there any other fixes for this behaviour? Is this what other people have encountered?

Cameron.moody 23:01, 2 July 2008 (UTC)

I'm having the exact same problem. So far i've not been able to come up with a fix since I can't get the pageID from the uncreated page.

Logging of the approvals?[edit]

I can't seem to see anywhere that the actual signing is logged? I have checked under Special:Log but can't find any entries there. I also can't see anything under the page history to indicate that it had been signed. Is this sort of functionality available from somewhere, or can it be added at all?

Cameron.moody 04:38, 2 July 2008 (UTC)

How does an approver know that they have a request to approve a page?[edit]

Is there some sort of notification email that is sent? I have done some test pages listing email-confirmed users as approvers but they get no email. I also cannot see anywhere that they are notified of a pending request?

Cam

Return from fnSign hook incorrect[edit]

I changed the final else portion of the code to return true. The allows other UnknownHook calls to still execute.

Wolcott 20:36, 14 July 2008 (UTC)

Possible that a notification be sent to confirmed email address?[edit]

I am helping set up an internal wiki that only allows access to users with confirmed email addresses. With this extension there does not appear to be any notification.

Is it possible to add something to the code that whenever a page is saved that an email notification is sent to each of the users specified (ie their confirmed email address) to let them know? Ideally with a link to the page or the page title etc.

Enforce Approval as "required". Choose approver by clicking. Email notification[edit]

  1. Is there a feature that to set "approval" procedure as "required" and the user can't save the page without set approval assigner? no --Jostar 14:05, 9 February 2010 (UTC)
  2. Is there a way to let the users to click one of the user from one of predefined user groups instead of to type in the approval markup section? no --Jostar 14:05, 9 February 2010 (UTC)
  3. It seems that several users requested email notification before. Is it available yet? no but I like this option and I will think on this --Jostar 14:05, 9 February 2010 (UTC)
    • Every "sign"-action should be logged (easy to do)
    • A special page can show "pages waiting to sign" (easy to do) This list has to be checked from every user every day... better version 3:
    • Once someone added a user to the aproval list, the system sends him a message.

I did your second version because if you have very much pages to sign, getting a message for every single page is not fun. The code is listed below. --Daniel Gerber 09:28, 6 March 2010 (UTC)

Internationalisation[edit]

I removed the follwoing code and changed to the other part of the code for Internationalizing. --Jostar 16:19, 9 February 2010 (UTC)

class DocumentApproval {

	function loadMessages() {
		static $messagesLoaded = false;
	    global $wgMessageCache;
	    if ($messagesLoaded) return;
	        $messagesLoaded = true;
 
	    require(dirname(__FILE__) . '/DocumentApproval.i18n.php' );
	    foreach ( $allMessages as $lang => $langMessages ) {
	        $wgMessageCache->addMessages( $langMessages, $lang );
	    }
 
	    return true;
	}
 
}
 
function wfMsg($key) {
    DocumentApprovalLoadMessages();
    return wfMsg($key);
}

problems with "undefined Variable" due to extension:dpl[edit]

using Extension dpl an error accured only on pages usind dpl: 'undefined Variable:$reqUserID' in line 156

I could not find the real reason, but I changed this line (Number 156) to avoid the error message:

if ($reqUserID == $userid) {

to:

if (isset($reqUserID) && $reqUserID == $userid) { # isset avoids error: 'undefined Variable:$reqUserID' occuring with Extension:PDL for example

--Jostar 12:32, 10 February 2010 (UTC)

special page that lists all approval requests[edit]

I added a special page, that lists all pages where the user is requested for approval and all pages he already approved. Maybe we could integrate something like this in the next version of the extension? My Code:

File: DocumentApproval.php

<?php
 
/*
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation, version 2
of the License.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/
 
if (!defined('MEDIAWIKI')) die();
 
$wgDocumentApprovalExtensionVersion = '0.2';
 
$wgExtensionCredits['parserhook'][]    = array(
        'version'     => $wgDocumentApprovalExtensionVersion,
        'name'        => 'Document Approval',
        'author'      => 'João Artur Gramacho',
        'email'       => 'jgramacho at gmail dot com',
        'url'         => 'http://www.mediawiki.org/wiki/Extension:DocumentApproval',
        'description' => 'adds <nowiki><approval></nowiki> parser function document approval',
        'descriptionmsg' => 'da-desc',
);
$wgExtensionCredits['hook'][] = array(
        'name'        => 'Document Approval',
        'version'     => $wgDocumentApprovalExtensionVersion,
        'author'      => 'João Artur Gramacho',
        'email'       => 'jgramacho at gmail dot com',
        'url'         => 'http://www.mediawiki.org/wiki/Extension:DocumentApproval',
        'description' => 'Adds a hook for signing documents for approval'
);
 
$wgHooks['UnknownAction'][] = 'fnSign';
$wgHooks['SkinTemplateTabs'][] = 'daSkinTemplateTabs';
$wgHooks['ArticleDelete'][] = 'fnDocumentApprovalDelete';
$wgExtensionFunctions[] = "wfDocumentApprovalExtension";
 
$dir = dirname(__FILE__) . '/';
require_once($dir . 'DocumentApproval.body.php');
$wgExtensionMessagesFiles['DocumentApproval'] = $dir . 'DocumentApproval.i18n.php';
 
#$wgHooks['LoadAllMessages'][] = 'DocumentApproval::loadMessages';

//add Special Page
$wgSpecialPageGroups['DocumentApproval'] = 'pagetools';
# Add special page.
$wgAutoloadClasses['DocumentApproval'] = $dir . 'DocumentApproval.body.php';
$wgSpecialPages['DocumentApproval'] = 'DocumentApproval';


function wfDocumentApprovalExtension() {
   global $wgParser;
   wfLoadExtensionMessages('DocumentApproval' );
   # Defines the tag <approval> ... </approval>
   $wgParser->setHook( "approval", "fnDocumentApproval" );
}
?>

Class definition in file: DocumentApproval.body.php

class DocumentApproval extends SpecialPage {
#
# Specialpage-function: lists all pages with requests for approval and all approved pages for the user.
# @author: Daniel Gerber (daniel.gerber@bluewin.ch)
	function __construct() {
		parent::__construct( 'DocumentApproval' );
		wfLoadExtensionMessages('DocumentApproval');
	}
 
	function execute( $par ) {
		global $wgRequest, $wgOut, $wgUser;
 
		$this->setHeaders();
 
		
		$approved_tab = '';
		$approved_tab_empty = true;
		$not_approved_tab ='';
		$not_approved_tab_empty = true;
		$output = '<h2>'. wfMsg('daNotApprovedPages') .'</h2>';
		$userid = $wgUser->getId();
		$dbr = wfGetDB( DB_SLAVE );
 
        # check if the user has to sign any pages
        $sql =  "SELECT page_id, description FROM ". $dbr->tableName('approval_request')  . " WHERE user_id = " . $userid;
        $res = $dbr->query( $sql, __METHOD__ );
        if ($dbr->numRows($res)){
			$not_approved_tab .= '<table border=1 rules=all cellpadding=2><tr><th>'. wfMsg('daPage') .'</th><th>'. wfMsg('daFunction') .'</th></tr>';
            while ($row = $dbr->fetchRow($res)){		#for every page the user has to sign:
				# row[0] = page_id, row[1] = description
				$not_approved_tab .= '<tr>';
				
				# get Title of page from Page_id
				$sql = "SELECT page_title FROM ". $dbr->tableName('page') . " WHERE page_id = " .$row[0];
				$resp = $dbr->query($sql, __METHOD__);
				if ($dbr->numRows($resp)){
					$page = $dbr->fetchRow($resp);
				}
				else {
					$page[0] = wfMsg('daWrongId');
				}
				$dbr->freeResult($resp);
				$titel = $page[0];
				$titel2show = str_replace('_', ' ', $titel);
				
				# check if revision of page is already approved
				$sql = "SELECT rev_id, date FROM ". $dbr->tableName('approval') . "WHERE rev_page = " .$row[0];
				$res_a = $dbr->query($sql, __METHOD__);
				if($dbr->numRows($res_a)){
					# check last version approved by this user
					$max_id = '';
					while ($revision = $dbr->fetchRow($res_a)){
						$max_id[] = $revision[0];
						$date[] = $revision [1];
					}
					if (is_array($max_id)){
						rsort($max_id);
						$page_last_revision = $max_id[0];
					}
					else{
						$page_last_revision = $max_id;
					}
		
					$revcount = 0;
 
					# Gets the amount of changes since last approval
					$sql =  "SELECT count(*) as changes FROM ". $dbr->tableName('revision');
					$sql .= " WHERE rev_page = " . $row[0];
					$sql .= " AND rev_id > " . $page_last_revision;
					$res_r = $dbr->query( $sql, __METHOD__ );
					if ($dbr->numRows($res_r)){
						$row_r = $dbr->fetchRow($res_r);
						$revcount = $row_r[0];
					}
					$dbr->freeResult($res_r);
					
					if($revcount > 0){
						#if there are changes: print out actual revision for approval
						$not_approved_tab .='<tr>';	
						$not_approved_tab .= '<td><a href="./index.php?title=' .$titel . '">'.$titel2show. '</a></td>';
						$not_approved_tab .= '<td>' .$row[1] . '</td>';
						$not_approved_tab .= '</tr>';
						$not_approved_tab_empty = false;
					}
					else {
						# actual revision is approved, add it to "approved"-area
						$sql = "SELECT date FROM ". $dbr->tableName('approval') . "WHERE rev_page = " . $row[0] . " AND rev_id = " . $page_last_revision;
						$res_lad = $dbr->query( $sql, __METHOD__);
						$date_lad = $dbr->fetchRow($res_lad);
					
						# generate approved tab values
						$approved_tab .='<tr>';	
						$approved_tab .= '<td><a href="./index.php?title=' .$titel . '">'. str_replace('_', ' ',$titel). '</a></td>';
						$approved_tab .= '<td>' .$row[1] . '</td><td>' . $date_lad[0] . '</td>';
						$approved_tab .= '</tr>';
						$approved_tab_empty = false;
					}
				}
				else{
					#procedure if page is not approved
					$not_approved_tab .='<tr>';	
					$not_approved_tab .= '<td><a href="./index.php?title=' .$titel . '">'.$titel2show. '</a></td>';
					$not_approved_tab .= '<td>' .$row[1] . '</td>';
					$not_approved_tab .= '</tr>';
					$not_approved_tab_empty = false;
				}
				$dbr->freeResult($res_a);
				
			}
			$not_approved_tab .= "</table>";
			if (!$not_approved_tab_empty) {
				$output .= $not_approved_tab;
			}
			else{
				# = no pending requests for approval
				$output = wfMsg('daNotRequested');
			}
		}
		else{ 
			# = no requests for approval
			$output = wfMsg('daNotRequested');
        }
        $dbr->freeResult($res);
		$output .= '<br><h2>'. wfMsg('daApprovedPages') .'</h2>';
		
		if (!$approved_tab_empty) {
			# generate table with approved pages
			$output .= '<table border=1 rules=all cellpadding=2><tr><th>'. wfMsg('daPage') .'</th><th>'. wfMsg ('daFunction') .'</th><th>'. wfMsg('daApprovalDate') .'</th></tr>';
			$output .= $approved_tab . '</table>';
		}
		else {
			# procedure if there are no approved pages at all
			$output .= wfMsg('daNoPagesApproved');
		}
	
		$wgOut->addHTML( $output );
	}
}

Internationalization in file: DocumentApproval.i18n.php I did only the German and English internationalization.

/** Deutsch (German)
* @author Jork Leiterer
* @updates Daniel Gerber
*/
 
$messages['de'] = array(
  'documentapproval'	=> 'Unterzeichnung von Dokumenten',
  'da-desc'				=> 'F&#xFC;gt die <tt>&lt;approval&gt;</tt> Parserfunktion zur Dokumentengenehmigung ein',
  'daTableTitle'	 	=> '&#xDC;berarbeitungstabelle der Bewilligung',
  'daApprovalDate' 		=> 'Genehmigungsdatum',
  'daSign'		 		=> 'Signatur',
  'daFunction'			=> 'Funktion',
  'daPending'			=> 'offen',
  'daUserNotFound'		=> 'Benutzer nicht gefunden',
  'daNotNecessary'		=> 'Deine Unterschrift ist für dieses Dokument nicht erforderlich.',
  'daPreviouslySigned'	=> 'Das Dokument wurde schon am $1 von dir unterzeichnet und kann nicht noch ein mal unterzeichnet werden.',
  'sign-tab-text'	=> 'Unterzeichnen',
  'daNotApprovedPages'	=> 'Nicht unterzeichnete Seiten',
  'daApprovedPages'		=> 'Unterzeichnete Seiten',
  'daNotRequested'		=> 'Du bist f&uuml;r keine Unterzeichnung angefragt.',
  'daPage'				=> 'Seite',
  'daNoPagesApproved'	=> 'Du hast keine Seiten unterzeichnet.',
  'daWrongId'			=> 'Falsche Id',
);
 
$messages['de-formal'] = array(
  'daNotNecessary'		=> 'Ihre Unterschrift ist für dieses Dokument nicht erforderlich.',
  'daPreviouslySigned'	=> 'Das Dokument wurde schon am $1 von Ihnen unterzeichnet und kann nicht noch ein mal unterzeichnet werden.',
);
 
/** English
* @author João Artur Gramacho
* @updates Daniel Gerber
*/
 
$messages['en'] = array(
  'documentapproval'	=> 'DocumentApproval',
  'da-desc' 			=> 'adds <tt>&lt;approval&gt;</tt> parser function document approval',
  'daTableTitle'	 	=> 'Approval table of revision',
  'daApprovalDate' 		=> 'Approval date',
  'daSign'		 		=> 'Sign',
  'daFunction'			=> 'Role',
  'daPending'			=> 'pending',
  'daUserNotFound'		=> 'user not found',
  'daNotNecessary'		=> 'Your sign is not necessary to this document.',
  'daPreviouslySigned'	=> 'Document previously signed at $1',
  'sign-tab-text'	=> 'Sign',
  'daNotApprovedPages'	=> 'Not approved Pages',
  'daApprovedPages'		=> 'Approved Pages',
  'daNotRequested'		=> 'You are not requested for approval of a page.',
  'daPage'				=> 'Page',
  'daNoPagesApproved'	=> 'You have not approved any pages.',
  'daWrongId'			=> 'Wrong Id',
);

--Daniel Gerber 09:28, 6 March 2010 (UTC)

New Version 0.0.3[edit]

To adress logbook, special summary page, css format and order of signing.

My edits:

  1. 09.02.2010 internalisation changed to standard procedures
  2. 01.03.2010 Table in Wikiformat, use class = "wikitable"
  3. 02.03.2010 The order of signing must be correct, use order = "strict"
  4. 03.03.2010 Logbuch aktivated
  5. 03.03.2010 Table header is multilang

to-do:

  1. parameter reset="strict"...
  2. wenn in nowiki-tags, ignorieren!

--Jostar 18:53, 8 March 2010 (UTC)

Parameter[edit]

sorry, has to be translated

Innerhalb des tags <approval> ist die Angabe weiterer Parameter möglich.

Parameter 'class' im approval-tag
erlaubt die Tabellenformatierung, angegeben wird einfach die CSS-Klasse
  • Beispiel: class="wikitable sortable" erzeugt die Tabellenformatierung im wiki-Style und eine Sortiermöglichkeit der Tabelle
Parameter 'order' im approval-tag
legt die Reihenfolge der Abarbeitung für die Listeneinträge fest
  • Beispiel: order="strict" zeigt eine Nummerierungsspalte an mit der Spaltenüberschrift "!" und erlaubt "Unterschreiben" nur wenn man an der Reihe ist, also alle Anfragen vor dem eigenen Eintrag schon erfolgreich waren
Parameter 'reset' im approval-tag
legt das Zurücksetzten einmal abgegebener Unterschriften fest. Standardmäßig setzt jede Seitenänderung alle Unterschriften zurück, was zum Beispiel beim Ausfüllen von Formularen nicht der gewünschte Effekt ist.
  • Beispiel: reset="all" ist die Standardeinstellung
  • Beispiel: reset="form" es werden nur Unterschriften zurückgesetzt wenn eine Änderung außerhalb von Feldern (Parameter in Vorlage) erfolgt
  • Beispiel: reset="none" es werden nie Unterschriften zurückgesetzt (eigentlich sind sie dann auch sinnlos, anzuwenden vielleicht wenn es um Rechtschreibfehler etc. geht... es gibt im wiki die Möglichkeit Änderungen als "klein" zu markieren)

DocumentApproval.php[edit]

<?php
 
/*
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation, version 2
of the License.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/
 
if (!defined('MEDIAWIKI')) die();
 
$wgDocumentApprovalExtensionVersion = '0.3';
 
$wgExtensionCredits['parserhook'][]    = array(
        'version'     => $wgDocumentApprovalExtensionVersion,
        'name'        => 'Document Approval',
        'author'      => 'João Artur Gramacho',
        'email'       => 'jgramacho at gmail dot com',
        'url'         => 'http://www.mediawiki.org/wiki/Extension:DocumentApproval',
        'description' => 'adds <nowiki><approval></nowiki> parser function document approval',
        'descriptionmsg' => 'da-desc',
);
$wgExtensionCredits['hook'][] = array(
        'name'        => 'Document Approval',
        'version'     => $wgDocumentApprovalExtensionVersion,
        'author'      => 'João Artur Gramacho',
        'email'       => 'jgramacho at gmail dot com',
        'url'         => 'http://www.mediawiki.org/wiki/Extension:DocumentApproval',
        'description' => 'Adds a hook for signing documents for approval'
);
 
$wgExtensionCredits['specialpage'][] = array(
	'name' => 'DocumentApproval',
	'author' => 'Jork Leiterer',
	'description' => 'Page for extension DocumentApproval to see further request information',
	'descriptionmsg' => 'da-sdesc',
	'version' => $wgDocumentApprovalExtensionVersion,
);
 
#$dir = dirname(__FILE__) . '/';
#$wgAutoloadClasses['MyExtension'] = $dir . 'MyExtension_body.php'; # Tell MediaWiki to load the extension body.
#$wgExtensionMessagesFiles['MyExtension'] = $dir . 'MyExtension.i18n.php';
#$wgExtensionAliasesFiles['MyExtension'] = $dir . 'MyExtension.alias.php';
$wgSpecialPages['DocumentApproval'] = 'DocumentApproval'; # Let MediaWiki know about your new special page.

 
 
 
 
$wgHooks['UnknownAction'][] = 'fnSign';
$wgHooks['SkinTemplateTabs'][] = 'daSkinTemplateTabs';
$wgHooks['ArticleDelete'][] = 'fnDocumentApprovalDelete';
$wgExtensionFunctions[] = "wfDocumentApprovalExtension";
 
require_once(dirname(__FILE__) . '/DocumentApproval.body.php');
#require_once(dirname(__FILE__) . '/DocumentApproval.i18n.php' );
$wgExtensionMessagesFiles['DocumentApproval'] = dirname( __FILE__ ) . '/DocumentApproval.i18n.php';
 
#$wgHooks['LoadAllMessages'][] = 'DocumentApproval::loadMessages';

# Add a new log type
  $wgLogTypes[]                        = 'dalog';
  $wgLogNames  ['dalog']            = 'daloglogpage';
  $wgLogHeaders['dalog']            = 'daloglogpagetext';
  $wgLogActions['dalog/sentok']     = 'dalog-sentok-entry';
  $wgLogActions['dalog/sign']       = 'dalog-sign';
  $wgLogActions['dalog/komplett']   = 'dalog-komplett';
  $wgLogActions['dalog/email']      = 'dalog-email';
 
function wfDocumentApprovalExtension() {
   global $wgParser;
   wfLoadExtensionMessages( 'DocumentApproval' );
   # Defines the tag <approval> ... </approval>
   $wgParser->setHook( "approval", "fnDocumentApproval" );
}


DocumentApproval.body.php[edit]

<?php

# edits:
# 09.02.2010 internalisation changed to standard procedures
# 01.03.2010 Table in Wikiformat, use class = "wikitable"
# 02.03.2010 The order of Signing must be correct, use order = "strict"
# 03.03.2010 Logbuch aktivated
# 03.03.2010 Table header is multilang
# 02.08.2010 Prefix of the database is not longer hard coded as "mw_"
#
# to-do
# wenn in nowiki-tags, ignorieren!

class DocumentApproval extends SpecialPage {
	function __construct() {
		parent::__construct( 'DocumentApproval' );
		wfLoadExtensionMessages('DocumentApproval');
	}
 
	function execute( $par ) {
		global $wgRequest, $wgOut, $wgDBtype, $wgUser, $wgSitename, $wgTitle, $wgServerName;
 
		$this->setHeaders();
		$userlist = array('Admin', 'Leiterer');
		$username = ( in_array($wgUser->getname(), $userlist) && $par && getUserIDFromUserText($par) ? $par : $wgUser->getname());
		$userid = ( getUserIDFromUserText($username) ? getUserIDFromUserText($username) : $wgUser->getID());
		$wgOut->addwikitext(wfMsg('da-head', str_replace('_', ' ', $username)).($userid && in_array($wgUser->getname(), $userlist)? ' (ID: '.$userid.')' : ''));
    
    # Get request data from, e.g.
    # $param = wgRequest->getText('param');
    	
    $db = &wfGetDB(DB_SLAVE);

    //Get the pages where the users is listed
    $output3 = '';
    if (false == $result = $db->query( '
    SELECT a.description, m.user_real_name, a.page_id, c.date 
    FROM '.$db->tableName('user').' m 
    LEFT JOIN '.$db->tableName('approval_request').' a
    ON a.user_id = m.user_id
    LEFT JOIN '.$db->tableName('approval').' c
    ON a.page_id = c.rev_page
    
    WHERE (a.user_id = \''.$userid.'\') 
    GROUP BY a.page_id
    
    ', 'documentapproval' )) {
        $output3 .= '<p>Error accessing watchlist.</p>';
    } else if($db->numRows($result) == 0) {
        $output3 .= '<p>'.wfMsg('da-keineanfrage', $username).'.</p>';
    } else {
      $output3 .= "{| class=\"wikitable sortable\"  
      |- class=\"hintergrundfarbe5\"
      ! ".wfMsg('da-function')."
      ! ".wfMsg('article')." \n"
      .( in_array($wgUser->getname(), $userlist) ? "! ".wfMsg('da-pageID')." \n" : '')
      ."! ".wfMsg('da-createarticledate')." \n"
      ;
               
      while ($row = $db->fetchRow($result)) {
        $dbresults3[] = $row; # creates array with all entries for a user
        $output3 .= "|-\n| "
                  .$row["description"]."\n"
                  ."| [[".Title::newFromID($row["page_id"]).']]'."\n"
                  .( in_array($wgUser->getname(), $userlist) ? "|".$row["page_id"]."\n" : '')
                  ."|".$row["date"]."\n"
                  ;
        };
        $output3 .= "|}\n";
      if (count($dbresults3)>0) $output3 = '==' . wfMsg('da-signsum', $db->numRows($result) ) . "==\n" . $output3;
    }  		
  
  
    // Get the pages which the users already signed
    $output4 = '';
    if (false == $result = $db->query( '
    SELECT c.rev_page, c.rev_id, c.date, a.description
    FROM '.$db->tableName('user').' m '
    .'LEFT JOIN '.$db->tableName('approval').' c ON m.user_id = c.user_id AND (m.user_id = '.$userid.') '
    .'LEFT JOIN '.$db->tableName('approval_request').' a ON c.rev_page = a.page_id AND a.user_id = c.user_id '
    .'WHERE (c.user_id = '.$userid.') 
   
    ', 'documentapproval' )) {
        $output4 .= '<p>Error accessing watchlist.</p>';
    } else if($db->numRows($result) == 0) {
        $output4 .= '<p>'.wfMsg('da-keineunterschriften', $username).'.</p>';
    } else {
      unset($rowss);
      while ($row = $db->fetchRow($result)) { 
        $rows4[] = $row ; 
      };
      $output4 .= "{| class=\"wikitable sortable\"  
      |- class=\"hintergrundfarbe5\"\n"
      ."! ".wfMsg('da-function')."\n"
      ."! ".wfMsg('article')." \n"
      .( in_array($wgUser->getname(), $userlist) ? "! ".wfMsg('da-pageID')." \n" : '')
      ."! ".wfMsg('da-createarticledate')." \n"
      ;
       
      foreach ( $rows4 as $key => $row ) { 
        #if (in_array($wgUser->getname(), $userlist)) $wgOut->addwikitext ('de'.$key.':'.$row["rev_id"].'-'.$row[0].' ar2: '.$ar2[1]);         

        $ptit = Title::newFromID($row["rev_page"]);
        $revision = Revision::newFromTitle($ptit );
        $revisionid = $revision->getId();
       # if (in_array($wgUser->getname(), $userlist)) $wgOut->addwikitext ('rev: '.$revisionid.'  '.$row["rev_id"]);
        if ($revisionid == $row["rev_id"]) {
           $output4 .= "|-\n| "
           ."| ".$row["description"]."\n"
                    ."| [[".Title::newFromID($row["rev_page"]).']]'."\n"
                    .( in_array($wgUser->getname(), $userlist) ? "|".$row["rev_page"]."\n" : '')
                    ."|".$row["date"]."\n"
                    ;
           $dbresult4[]=$row["rev_page"];
          };
        };
        $output4 .= "|}\n";
        $output4 = ($dbresult4 > 0) ? '=='.wfMsg('da-signalready', count($dbresult4) ).'=='."\n" . $output4 : '<p>'.wfMsg('da-keineunterschriften', str_replace('_', ' ', $username)).'.</p>';
    } 
    
    
    //Get the pages where the users is invited to sign but did not sign yet
    $output2 = '';
    if (false) {
        $output2 .= '<p>Error accessing watchlist.</p>';
    } else {
      while ($row = $db->fetchRow($result)) { # Abfrage
        $rowss[] = $row ;         
      };   
      $output2 .= '=='.wfMsg('da-signwish', count($dbresults3) - count($dbresult4) ).'=='."\n";
      if ($dbresults3 && count($dbresults3) - count($dbresult4) > 0) {
        $output2 .= "{| class=\"wikitable sortable\"  
                  |- class=\"hintergrundfarbe5\"\n"
                  ."! ".wfMsg('da-function')."\n"
                  ."! ".wfMsg('article')." \n"
                  .( in_array($wgUser->getname(), $userlist) ? "! ".wfMsg('da-pageID')." \n" : '')
                  ."! ".wfMsg('da-createarticledate')." \n"
                  ;                    
        foreach ( $dbresults3 as $key => $row ) {
          if ( !$dbresult4 OR !in_array($row[2], $dbresult4 ) )
                      $output2 .= "|-\n| "
                                .$row["description"]."\n"
                                ."| [[".Title::newFromID($row["page_id"]).']]'."\n"
                                .( in_array($wgUser->getname(), $userlist) ? "|".$row["page_id"]."\n" : '')
                                ."|".$row["date"]."\n"
                                ;
        };
        $output2 .= '|}';
      };
    }; 
    
    $wgOut->addwikitext ( $output2 ); ## nur offene Anfragen
    $wgOut->addwikitext ( $output4 ); ## schon Unterschrieben
    $wgOut->addwikitext ( $output3 ); ## Beteiligung erwünscht
       
    // Lists all users where requests are waiting for
    if ( in_array($wgUser->getname(), $userlist) ) {
        $tables = array("approval_request", "user");
        $columns = array("user_name", "user_real_name", "COUNT(1) AS pages_watched_by_user");
        $conditions = array("user_id = user_id", "wl_namespace");
        $fname = "Database::select";
        $options = array(
            "GROUP BY" => "wl_user",
            "ORDER BY" => "COUNT(1) DESC",
        );
        if ($user_limit > 0) {
            $options["LIMIT"] = $user_limit;
        }

        if (false == $result = $db->query( 'SELECT user_name,user_real_name,COUNT(1) AS pages_watched_by_user FROM '.$db->tableName('approval_request').' a, '.$db->tableName('user').' m WHERE (a.user_id = m.user_id) GROUP BY a.user_id ORDER BY COUNT(1) DESC ', 'documentapproval' )) {
            $output .= '<p>Error accessing watchlist.</p>';
        } else if($db->numRows($result) == 0) {
            $output .= '<p>'.wfMsg('da-keineanfrage', $username).'.</p>';
        } else {
            $output .= '<table'.$tableStyle.'>
  <tr>
    <th style="text-align: left;">'.wfMsg('da-signer').'</th>
    <th>'.wfMsg('group-user-member').'</th>
    <th style="text-align: right;">'.wfMsg('da-requests').'</th>
  </tr>';
            while ($row = $db->fetchRow($result)) {
                $output .= '
  <tr>
    <td>'.$row["user_real_name"].'</td>
    <td><a href="'.($par ? str_replace('/'.$par, '', $_SERVER["PHP_SELF"]) : $_SERVER["PHP_SELF"]).'/'.$row["user_name"].'">'.$row["user_name"].'</a></td>'
    .'<td style="text-align: right;">'.$row["pages_watched_by_user"].'</td>
  </tr>';
            }
            $output .= '
  <tr>
    <td colspan="3" align="right">
      <form action="post" method="get" id="limit_form">'
        ;

            $output .= '
        </select>
        <input type="hidden" name="title" value="Special:WhoIsWatchingTabbed" />
        <input type="hidden" name="page_limit" value="'.$page_limit.'" />
      </form>
    </td>
  </tr>
</table>';
        }
        $wgOut->addwikitext ( '==' . wfMsg('da-signlist', $db->numRows($result)) . '==' );
        $wgOut->addHTML( $output );
    } # Ende alle Benutzer   
		return false;
	} # end function
} # end of special page --------------------------------------------------------------------------------------


 
function DocumentApprovalLoadMessages() {
	static $messagesLoaded = false;
    global $wgMessageCache;
    if ($messagesLoaded) return;
        $messagesLoaded = true;
 
    require(dirname(__FILE__) . '/DocumentApproval.i18n.php' );
    foreach ( $allMessages as $lang => $langMessages ) {
        $wgMessageCache->addMessages( $langMessages, $lang );
    }
 
    return true;
}
 
 
function daTableCheck()
{
	global $wgDBprefix;
    $dbr = wfGetDB( DB_SLAVE );
 
	// Check if 'approval_request' database tables exists
	if (!$dbr->tableExists('approval_request'))
	{
		$sql  = "CREATE TABLE `" . $wgDBprefix . "approval_request` (";
		$sql .= "`page_id` int(10) unsigned NOT NULL,";
		$sql .= "`user_id` int(10) unsigned NOT NULL,";
		$sql .= "`description` varchar(255) default NULL,";
	  $sql .= "`position` int(10) unsigned NOT NULL,";
	  $sql .= "`order` int(10) unsigned NOT NULL,";
		$sql .= "PRIMARY KEY  (`page_id`,`user_id`),";
		$sql .= "KEY `FK_" . $wgDBprefix . "_approval_request_user` (`user_id`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_request_page` FOREIGN KEY (`page_id`) REFERENCES `" . $wgDBprefix . "_page` (`page_id`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_request_user` FOREIGN KEY (`user_id`) REFERENCES `" . $wgDBprefix . "_user` (`user_id`)";
		$sql .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
		$res = $dbr->query( $sql, __METHOD__ );		
	}
 
	// Check if 'approval' database tables exists
	if (!$dbr->tableExists('approval'))
	{
		$sql  = "CREATE TABLE `" . $wgDBprefix . "approval` (";
		$sql .= "`rev_id` int(10) unsigned NOT NULL,";
		$sql .= "`rev_page` int(10) unsigned NOT NULL,";
		$sql .= "`user_id` int(10) unsigned NOT NULL,";
		$sql .= "`date` timestamp NOT NULL default CURRENT_TIMESTAMP,";
		$sql .= "PRIMARY KEY  (`rev_id`,`rev_page`,`user_id`),";
		$sql .= "KEY `FK_" . $wgDBprefix . "_approval_user` (`user_id`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_revision` FOREIGN KEY (`rev_id`, `rev_page`) REFERENCES `" . $wgDBprefix . "_revision` (`rev_id`, `rev_page`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_user` FOREIGN KEY (`user_id`) REFERENCES `" . $wgDBprefix . "_user` (`user_id`)";
		$sql .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
		$res = $dbr->query( $sql, __METHOD__ );		
	}
 
	// Check if 'approval_version' database tables exists
	if (!$dbr->tableExists('approval_version'))
	{
		$sql  = "CREATE TABLE `" . $wgDBprefix . "approval_version` (";
		$sql .= "`page_id` int(10) unsigned NOT NULL,";
		$sql .= "`last_approved_revision_id` int(10) unsigned default NULL,";
		$sql .= "`version` int(10) unsigned NOT NULL default '0',";
		$sql .= "`subversion` int(10) unsigned NOT NULL default '0',";
		$sql .= "PRIMARY KEY  (`page_id`),";
		$sql .= "KEY `FK_" . $wgDBprefix . "_approval_version_revision_id` (`last_approved_revision_id`,`page_id`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_version_page_id` FOREIGN KEY (`page_id`) REFERENCES `" . $wgDBprefix . "_page` (`page_id`),";
		$sql .= "CONSTRAINT `FK_" . $wgDBprefix . "_approval_version_revision_id` FOREIGN KEY (`last_approved_revision_id`, `page_id`) REFERENCES `" . $wgDBprefix . "_revision` (`rev_id`, `rev_page`)";		
		$sql .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
		$res = $dbr->query( $sql, __METHOD__ );		
	}
 
}
 
if (!function_exists('getUserIDFromUserText')) {
    function getUserIDFromUserText($user) {
        $dbr = wfGetDB( DB_SLAVE );
        $userid = 0;
 
        if (preg_match('/^\s*(.*?)\s*$/', $user, $matches))
            $user = $matches[1];
 
        $u = User::newFromName($user);
        if ($u) {
            $userid = $u->idForName();               // valid userName
        }
        if (!$userid) {                              // if not a valid userName, try as a userRealName
            $userid = $dbr->selectField( 'user', 'user_id', array( 'user_real_name' => $user ), 'renderTodo' );
            if (!$userid) {                          // if not valid userRealName, try case insensitive userRealName
                $sql = "SELECT user_id FROM ". $dbr->tableName('user') ." WHERE UPPER(user_real_name) LIKE '%" . strtoupper($user) . "%'";
                $res = $dbr->query( $sql, __METHOD__ );
                if ($dbr->numRows($res)) {
                    $row = $dbr->fetchRow($res);
                    $userid = $row[0];
                }
                $dbr->freeResult($res);
                if (!$userid) {                      // if not case insensitive userRealName, try case insensitive lastname
                    list ($first, $last) = preg_split('/\s+/', $user);
                    if ($last != '') {
                        $sql = "SELECT user_id FROM ". $dbr->tableName('user') ." WHERE UPPER(user_real_name) LIKE '%" . strtoupper($last) . "%'";
                        $res = $dbr->query( $sql, __METHOD__ );
                        if ($dbr->numRows($res)) {
                            $row = $dbr->fetchRow($res);
                            $userid = $row[0];
                        }
                        $dbr->freeResult($res);
                    }
                }
            }
        }
        return $userid;
    }
}
 
function daSkinTemplateTabs( &$skin, &$content_actions ) {
	global $wgRequest, $wgTitle;
    
	$action = $wgRequest->getText( 'action' );
 
	// Check if the user are elegible for singing this page
	$revisionid = $wgTitle->getLatestRevID();
 
	$revision = Revision::newFromID($revisionid);
 
	if ($revision)
	{
	//	$revisionid = $revision->getId();
		$pageid = $revision->getPage();
		$user = User::newFromSession();
		$userid = $user->getID();
    
    // MOD - Check if there is a user logged-on
    if ($userid) {
        $dbr = wfGetDB( DB_SLAVE );
   
        // Check if this user can sign the document
        $sql =  "SELECT user_id FROM ". $dbr->tableName('approval_request');
        $sql .= " WHERE page_id = " . $pageid;
        $sql .= " AND user_id = " . $userid;

        $reqUserId = "";
   
        $res = $dbr->query( $sql, __METHOD__ );
        if ($dbr->numRows($res))
          {
            $row = $dbr->fetchRow($res);
            $reqUserID = $row[0];
          }
        $dbr->freeResult($res);
           
        // If user is elegible for singning, displays the sign tab    
        if (isset($reqUserID) && $reqUserID == $userid) { # isset avoids error: 'undefined Variable:$reqUserID' occuring with Extension:PDL for example
        $content_actions['sign'] = array(
          'class' => ($action == 'sign') ? 'selected' : false,
          'text' => wfMsg('sign-tab-text'),
          'href' => $skin->mTitle->getLocalURL( 'action=sign' ),
          );
        }  
      }
	}
	return true;
}
 
function fnDocumentApprovalDelete( &$article, &$user, &$reason ) {
 
	daTableCheck();
 
    // get page_id
    $pageid = $article->getID();
 
    $dbr = wfGetDB( DB_SLAVE );
 
    // delete 
    $sql =  "DELETE ";
    $sql .= " FROM ". $dbr->tableName('approval_version');
    $sql .= " WHERE page_id = " . $pageid;
    $res = $dbr->query( $sql, __METHOD__ );
 
    // delete 
    $sql =  "DELETE ";
    $sql .= " FROM ". $dbr->tableName('approval_request');
    $sql .= " WHERE page_id = " . $pageid;
    $res = $dbr->query( $sql, __METHOD__ );
 
    // delete 
    $sql =  "DELETE ";
    $sql .= " FROM ". $dbr->tableName('approval');
    $sql .= " WHERE rev_page = " . $pageid;
    $res = $dbr->query( $sql, __METHOD__ );
 
    return true;
}
 
 
function fnDocumentApproval( $input, $argv, &$parser ) {
	daTableCheck();

	$requests=split("\n",$input);
 
    // get document full title
    $title = $parser->getTitle();
 
    $revision = Revision::newFromTitle($title);
    if (is_null($revision)) return false; # if there is no revision for a new page (because it has not been saved)
    
    $revisionid = $revision->getId();
    $pageid = $revision->getPage();
    $dbr = wfGetDB( DB_SLAVE );
 
    $output = "";
 
    $counter = 0;
 
    $listOfUsers = "";
 
    $page_version = 0;
    $page_subversion = 0;
    $page_last_revision = 0;
 
    // gets document last version and subversion
    $sql =  "SELECT version, subversion, last_approved_revision_id";
    $sql .= " FROM ". $dbr->tableName('approval_version');
    $sql .= " WHERE page_id = " . $pageid;
    $res = $dbr->query( $sql, __METHOD__ );
    if ($dbr->numRows($res))
    {
		$row = $dbr->fetchRow($res);
		$page_version = $row[0];
		$page_subversion = $row[1];
		$page_last_revision = $row[2];
    }
	$dbr->freeResult($res);
 
	$revcount = 0;
 
    // Gets the amount of changes since last approval
    $sql =  "SELECT count(*) as changes FROM ". $dbr->tableName('revision');
    $sql .= " WHERE rev_page = " . $pageid;
    $sql .= " AND rev_id > " . $page_last_revision;
    $res = $dbr->query( $sql, __METHOD__ );
    if ($dbr->numRows($res))
    {
		$row = $dbr->fetchRow($res);
		$revcount = $row[0];
    }
	$dbr->freeResult($res);
 
    // Gets the options inside the <approval>-tag
    foreach ($argv as $key => $value) {
            switch ($key) {
                    case 'class':
                            $tableclass = $value; // Gets the display option for a wiki format table, e.g. <approval class="wikitable sortable">
                            break;
                    case 'order':
                            $order = $value; // Gets the option for correct order signing (z.B. <approval order="strict">)
                            break;
                    case 'reset':
                            $reset = $value; // Gets the option for sign reset (z.B. <approval reset="all">)
                            break;
                    default :
                            wfDebug( __METHOD__.": Requested '$key ==> $value'\n" );
                            break;
            }
    }

    //creates table caption and header in html format and if option 'class' is found in wiki-like format
    $output .= "\n";
    $output .= '<table border=1 '.((isset($tableclass) && $tableclass != '') ? 'class="'.$tableclass.'" style="margin: 0.5em 1em 1em 0pt; empty-cells: show;" border="2" cellpadding="0" cellspacing="0"' : '" style="border: 1px solid rgb(170, 170, 170); margin: 1em 1em 1em 0pt; border-collapse: collapse; empty-cells: show;" border="2" cellpadding="3" cellspacing="4" rules="all"').'>'."\n";
    $output .= "<caption>\n";
    $output .= wfMsg("daTableTitle") . " #$page_version.$page_subversion.$revcount\n";
    $output .= "</caption>\n";
    $output .= "<tr ".((isset($tableclass) && $tableclass != '') ? 'class="hintergrundfarbe5"' : '').">\n";
    if ((isset($tableclass) && strpos('sortable', $tableclass) > 0 && $order != 'strict') || $order == 'strict')
    $output .= "<th>".($order == 'strict' ? '!' : '#')."</td>\n";
    $output .= "<th>" . wfMsg("daApprovalDate") . "</td>\n";
    $output .= "<th>" . wfMsg("daSign") . "</td>\n";
    $output .= "<th>" . wfMsg("daFunction") . "</td>\n";
    $output .= "</tr>\n";

    foreach($requests as $request)
    {
        $request=split(";",trim($request));
        $login = $request[0];
 
        if ($login != "")
        {
            $function = $request[1];
            $counter++;
 
            // Gets userid
            $userid = getUserIDFromUserText($login);
 
            if ($userid != 0)
            // if user exists
            {
 
                // Put user on the temporary approval list
                if ($listOfUsers == "")
                    $listOfUsers .= $userid;
                else
                    $listOfUsers .= ", " . $userid;
                
                $req_userid = 0;
                
                // Gets user information
                $u = User::newFromId($userid);
                $username = $u->getName();
                $fullname = $u->getRealName();
 
                // Check if this user is in the approval list on database 
                $sql =  "SELECT description, position, `order` FROM ". $dbr->tableName('approval_request');
                $sql .= " WHERE page_id = " . $pageid . " AND user_id = " . $userid;

 
                $res = $dbr->query( $sql, __METHOD__ );
                if ($dbr->numRows($res))
                {
                    $row = $dbr->fetchRow($res);
                    $req_userid = $userid;
                    $req_description = $row[0];
                    $req_position = $row['position'];
                    $req_order = $row['order'];

                }
                $dbr->freeResult($res);
 
                // If not, insert it
                if ($req_userid == 0)
                {
                    $sql =  "INSERT INTO ". $dbr->tableName('approval_request');
                    $sql .= " (page_id, user_id, description, position, `order`) VALUES";
                    $sql .= " ($pageid, $userid, '" . trim($function) . "', $counter, '" . $order . "')";
                    $res = $dbr->query( $sql, __METHOD__ );
                    $req_userid = $userid;
                    $req_description = $function;
                    
                    # log
                    $log = new LogPage( 'dalog' );
                    $log->addEntry( 'sentok', $title, $username.': '.trim($function));  
                    
                    #email
                }
 
                // If position changed?
                if ($req_position != $counter) {
                    $sql =  "UPDATE ". $dbr->tableName('approval_request');
                    $sql .= " SET position = '" . $counter . "'";
                    $sql .= " WHERE page_id = $pageid AND user_id = $userid";
                    $res = $dbr->query( $sql, __METHOD__ );
                    $req_position = $position;
                    }
                    
                // If order changed?
                if ($req_order != $order && $order != '') {
                    $sql =  "UPDATE ". $dbr->tableName('approval_request');
                    $sql .= " SET `order` = '" . $order . "'";
                    $sql .= " WHERE page_id = $pageid AND user_id = $userid";
                    $res = $dbr->query( $sql, __METHOD__ );
                    $req_order = $order;
                    }
 
                // If user role changed, update database
                if ($req_description != $function) {
                    $sql =  "UPDATE ". $dbr->tableName('approval_request');
                    $sql .= " SET description = '" . $function . "'";
                    $sql .= " WHERE page_id = $pageid AND user_id = $userid";
                    $res = $dbr->query( $sql, __METHOD__ );
                    $req_description = $function;
                    
                    # log
                    $log = new LogPage( 'dalog' );
                    $log->addEntry( 'sentok', $title, $username.': '.trim($function));  
                                        
                    #Add to watch
                    #$u->addWatch( $title ); fuegt das Logbuch hinzu!
                    
                    #send email
                    $u->sendMail ( wfMsg('da-emailtitle'), wfMsg('da-emailtext', ($fullname ? $fullname : $username), $title, trim($function), $title->GetFullURL() ). " (http://fywiki/wiki/Spezial:DocumentApproval)");
                    }
 
                // check if the user already signed this document
                $sql =  "SELECT date FROM ". $dbr->tableName('approval');
                $sql .= " WHERE rev_page = " . $pageid;
                $sql .= " AND user_id = " . $userid;
                $sql .= " AND rev_id = " . $revisionid;
 
                $signDate = "<strong>" . wfMsg("daPending") . "</strong>";
 
                $res = $dbr->query( $sql, __METHOD__ );
                if ($dbr->numRows($res))
                {
                    $row = $dbr->fetchRow($res);
                    $signDate = $row[0];
                }
                $dbr->freeResult($res);
 
                $output .= "<tr>\n";
                
                if ((isset($tableclass) && strpos('sortable', $tableclass) > 0 && $order != 'strict') || $order == 'strict')
                $output .= "<td>$counter</td>\n";
                
                $output .= "<td align=center>$signDate</td>\n";
                $output .= "<td>$fullname</td>\n";
                $output .= "<td>$function</td>\n";
                $output .= "</tr>\n";
            }
            else
            // If the user was not found
            {
                $output .= "<tr>\n";
                if ((isset($tableclass) && strpos('sortable', $tableclass) > 0 && $order != 'strict') || $order == 'strict')
                $output .= "<td>$counter</td>\n";
                $output .= "<td bgcolor=#ffc0c0 align=center><strong>" . wfMsg("daUserNotFound"). "</strong></td>\n";
                $output .= "<td>$login</td>\n";
                $output .= "<td>$function</td>\n";
                $output .= "</tr>\n";
            }
        }
    }
 
    if ($listOfUsers != "")
    {
	    // Remove users that do not have to sign the document from database
	    $sql =  "DELETE FROM ". $dbr->tableName('approval_request');
	    $sql .= " WHERE page_id = $pageid AND user_id NOT IN (" . $listOfUsers . ")";
	    $res = $dbr->query( $sql, __METHOD__ );
    }
 
    $output .= "</table>\n"; 
    return $output;
}
 
function fnSign($action, $article)
{
  global $wgOut, $wgLang, $wgContLang;
 
	daTableCheck();
 
	if ($action == 'sign')
    {
        $title = $article->getTitle();
 
        $revision = Revision::newFromTitle($title);
        $revisionid = $revision->getId();
        $pageid = $revision->getPage();
        $user = User::newFromSession();
        $userid = $user->getID();

        $dbr = wfGetDB( DB_SLAVE );
 
        // Check if this user can sign the document
        $sql =  "SELECT user_id, position, `order` FROM ". $dbr->tableName('approval_request');
        $sql .= " WHERE page_id = " . $pageid;
        $sql .= " AND user_id = " . $userid;
 
        $reqUserId = "";
 
        $res = $dbr->query( $sql, __METHOD__ );
        if ($dbr->numRows($res))
        {
            $row = $dbr->fetchRow($res);
            $reqUserID = $row[0];
            $position = $row['position'];
            $order = $row['order'];
        }
        $dbr->freeResult($res);
 
        if ( ($reqUserID != $userid) || ($userid == 0) ) # avoid signing for unknown user (only IP availible)
        {
            $wgOut->addWikiText(wfMsg("da-NotNecessary"));
            return false;
        }
  
        // Check if document is already signed
        $sql =  "SELECT date FROM ". $dbr->tableName('approval');
        $sql .= " WHERE rev_page = " . $pageid;
        $sql .= " AND user_id = " . $userid;
        $sql .= " AND rev_id = " . $revisionid;
 
        $signDate = "";
 
        $res = $dbr->query( $sql, __METHOD__ );
        if ($dbr->numRows($res))
        {
            $row = $dbr->fetchRow($res);
            $signDate = $row[0];
        }
        $dbr->freeResult($res);
 
        // If it is not signed yet
        if ($signDate == "")
        {
          
          // Check if this user is in the order of signing --------------------
          if ($order == 'strict') {
          
              // Check how many already signed it
              $sql =  "SELECT count(*) as approvals FROM ". $dbr->tableName('approval');
              $sql .= " WHERE rev_page = " . $pageid;
              $sql .= " AND rev_id = " . $revisionid;

              $approvals = 0;
     
              $res = $dbr->query( $sql, __METHOD__ );
              if ($dbr->numRows($res))
              {
                  $row = $dbr->fetchRow($res);
                  $approvals = $row[0];
              }
              $dbr->freeResult($res);           
	                       
              if ( $position != $approvals+1) {
                // nicht an der Reihe
                $wgOut->setPagetitle($title); # Add the actual page name to the message page
                $wgOut->addWikiText(wfMsgExt('da-NotYourTurn', 'parsemag', $position-$approvals-1).' ([['.$title.'?action=|'.str_replace('.','',wfMsg('returnto', $title)).']].)');
                return false;
              } 
          }
        
          // Sign it        
          $sql =  "INSERT INTO ". $dbr->tableName('approval');
          $sql .= " (rev_id, rev_page, user_id) VALUES";
          $sql .= " ($revisionid, $pageid, $userid)";
          $res = $dbr->query( $sql, __METHOD__ );

          // Log Signing
          $log = new LogPage( 'dalog' );
          #$log->addEntry( 'sign', $title, '[[{{localurl:'.$title.'{{!}}oldid='.$revisionid.'}}|'.$revisionid.']]'); # unklar wie Links gehen
          $log->addEntry( 'sign', $title, $revisionid);
          
          // Check how many already signed it
          $sql =  "SELECT count(*) as approvals FROM ". $dbr->tableName('approval');
          $sql .= " WHERE rev_page = " . $pageid;
          $sql .= " AND rev_id = " . $revisionid;

          $approvals = 0;
 
	        $res = $dbr->query( $sql, __METHOD__ );
	        if ($dbr->numRows($res))
	        {
	            $row = $dbr->fetchRow($res);
	            $approvals = $row[0];
	        }
	        $dbr->freeResult($res);
 
          // Check how many must sign it
	        $sql =  "SELECT count(*) as signers FROM ". $dbr->tableName('approval_request');
	        $sql .= " WHERE page_id = " . $pageid;
 
          $signers = 0;
 
	        $res = $dbr->query( $sql, __METHOD__ );
	        if ($dbr->numRows($res))
	        {
	            $row = $dbr->fetchRow($res);
	            $signers = $row[0];
	        }
	        $dbr->freeResult($res);
 
	        // If all signer already signed, generate a new version (or subversion)
	        if (($signers > 0) && ($approvals == $signers))
	        {
            // Check if page already have a version
	        	$sql =  "SELECT last_approved_revision_id, version, subversion";
	        	$sql .= " FROM ". $dbr->tableName('approval_version');
		        $sql .= " WHERE page_id = " . $pageid;
 
		        $page_version = 0;
		        $page_subversion = 0;
		        $page_last_revision = 0;
 
		        $res = $dbr->query( $sql, __METHOD__ );
		        if ($dbr->numRows($res))
		        {
		            $row = $dbr->fetchRow($res);
                $page_last_revision = $row[0];
		            $page_version = $row[1];
		            $page_subversion = $row[2];
		        }
		        else
		        {
		            $sql =  "INSERT INTO ". $dbr->tableName('approval_version');
		            $sql .= " (page_id) VALUES";
		            $sql .= " ($pageid)";
		            $resInsert = $dbr->query( $sql, __METHOD__ );		        	
		        }
		        $dbr->freeResult($res);
 
		        // Check the amount of changes	
	        	$sql =  "SELECT count(*) as edits";
	        	$sql .= " FROM ". $dbr->tableName('revision');
		        $sql .= " WHERE rev_page = " . $pageid;
		        $sql .= " AND rev_id > " . $page_last_revision;
            $sql .= " AND rev_minor_edit = 0";
 
		        $page_edits = 0;
 
		        $res = $dbr->query( $sql, __METHOD__ );
		        if ($dbr->numRows($res))
		        {
		          $row = $dbr->fetchRow($res);
		        	$page_edits = $row[0];
		        }
		        $dbr->freeResult($res);
 
		        // If not all of the changes are minor
		        if ($page_edits > 0)
		        {
		        	$page_version++;
		        	$page_subversion = 0;
		        }
		        else
		        {
		        	$page_subversion++;
		        }
 
            // Update version in database
		        $sql =  "UPDATE ". $dbr->tableName('approval_version') . " SET";
	            $sql .= " last_approved_revision_id = $revisionid,";
	            $sql .= " version = $page_version,";
	            $sql .= " subversion = $page_subversion";
	            $sql .= " WHERE page_id = " . $pageid;	            
	            $res = $dbr->query( $sql, __METHOD__ );
 
            // log kompleted request
            $log = new LogPage( 'dalog' );
            $log->addEntry( 'komplett', $title, 'v'.$page_version);
	        }
 
	        // Clear document cache
            $article->purge();
        }
        else
        {
            $wgOut->setPagetitle($title); # Add the actual page name to the message page
            $wgOut->addWikiText(wfMsg('da-PreviouslySigned', $wgLang->date( $signDate, true, true ), $wgLang->time( $signDate, true, true )).' ([['.$title.'?action=|'.str_replace('.','',wfMsg('returnto', $title)).']])');
        }
 
        return false;
    }
    else
        return true; # 'true' allows other UnknownHook calls to still execute
}

DocumentApproval.i18n.php[edit]

<?php
/**
 * Internationalisation file for Extension:DocumentApproval
 *
 */
 
$messages = array();

/** Deutsch (German)
* @author Jork Leiterer
*/

$messages['de'] = array(
  'documentapproval' => 'Document Approval',
  'da-desc' => "Fügt die <tt>&lt;approval&gt;</tt> Parserfunktion zur Dokumentengenehmigung ein",
  'da-sdesc' => 'Seite für die Extension DocumentApproval um weitere Unterschriftenanfragen zu sehen',
  
  'da-NotYourTurn' => "Du darfst diese Seite unterschreiben, bist aber noch nicht an der Reihe zu unterschreiben. Du musst noch auf {{PLURAL:$1|eine Unterschrift|$1 Unterschriften}} warten.",
  'da-keineanfrage' => "Derzeit liegen keine Anfragen zum Unterschreiben für Benutzer $1 vor.",
  'da-keineunterschriften' => "Es wurden keine von Benutzer $1 unterschriebenen Seiten gefunden.",
  
  'daloglogpage' => 'Document Approval Logbuch',
  'daloglogpagetext'		=> 'Dieses Logbuch protokolliert Änderungen von Unterschriften.',
  'dalogtext' => 'Document Approval',
	'dalog-sentok-entry'		=> 'hat auf der Seite $1 eine Unterschrift-Anfrage geändert',
	'dalog-komplett' => 'hat die Unterschrift-Anfrage der Seite $1 vervollständigt',
	'dalog-sign'		=> 'hat die Seite $1 unterschrieben',
	'da-emailtext' => "Lieber $1,\n\nDie Seite $2 wartet auf deine Unterschrift als $3 ($4).\n\nDein freundliches {{SITENAME}}\n\nPS:\nAlle Anfragen kannst du auf der Seite Spezial:DocumentApproval einsehen\nEine ausführliche Beschreibung enthält die Seite Hilfe:DocumentApproval",
	'da-emailtitle' => 'Neue Anfrage zur Unterschrift',
  'daTableTitle'	 		=> 'Überarbeitungstabelle der Bewilligung',
  'daApprovalDate' 		=> 'Genehmigungsdatum',
  'daSign'		 		=> 'Signatur',
  'da-function'			=> 'Funktion',
  'da-pageID' => 'Seiten-ID',
  'da-createarticledate'  => 'Erstelldatum',
  'da-signer' => 'Unterschreiber',
  'da-requests' => 'Anfragen',
  
  'daPending'				=> 'offen',
  'daUserNotFound'		=> 'Benutzer nicht gefunden',
  
  'da-head' => 'Liste aller Unterschriften für Benutzer $1',
  'da-signsum' =>  'Alle Anfragen: $1',
  'da-signalready' =>  'Schon unterschriebene Anfragen: $1',
  'da-signwish' => "Offene Anfragen zum Unterschreiben: $1",
  'da-signlist'   => 'Alle Anfragen nach Unterschriften: $1',
  
  'da-NotNecessary'		=> 'Deine Unterschrift ist für dieses Dokument nicht erforderlich.',
  'da-PreviouslySigned'	=> 'Das Dokument wurde schon am $1 um $2 von dir unterzeichnet und kann nicht noch ein mal unterzeichnet werden.',
  
  'sign-tab-text'	=> 'Unterzeichnen',
);
      
$messages['de-formal'] = array(
  'da-NotYourTurn' => "Sie dürfen die Seite unterschreiben, sind aber noch nicht an der Reihe zu unterschreiben",
  'da-NotNecessary'		=> 'Ihre Unterschrift ist für dieses Dokument nicht erforderlich.',
  'daPreviouslySigned'	=> 'Das Dokument wurde schon am $1 um $2 von Ihnen unterzeichnet und kann nicht noch ein mal unterzeichnet werden.',
  'da-NotYourTurn' => "Du darfst die Seite unterschreiben, bist aber noch nicht an der Reihe zu unterschreiben",
  'da-emailtext' => "Sehr geehrter $1,\n\nDie Seite $2 wartet auf Ihre Unterschrift als $3 ($4).\n\nIhr freundliches {{SITENAME}}\n\nPS:\nAlle Anfragen können Sie auf der Seite Spezial:DocumentApproval einsehen\nEine ausführliche Beschreibung enthält die Seite Hilfe:DocumentApproval",
);

/** English
* @author João Artur Gramacho
*/

$messages['en'] = array(
  'documentapproval' => 'Document Approval',
  'da-desc' => 'adds <tt>&lt;approval&gt;</tt> parser function document approval',
  'da-sdesc' => 'Page for extension DocumentApproval to see further request information',
 
  'da-NotYourTurn' => "You may sign this page, but you are not yet signed on the series. You still have to wait for {{PLURAL:$1|one signatures|$1 signatures}}.",
  'da-keineanfrage' => "Currently, no requests are available to sign for user $1.",
  'da-keineunterschriften' => "There are no user $1 signed pages.",
     
  'daloglogpage' => 'Document Approval Log',
 
  'daTableTitle'	 		=> 'Approval table of revision',
  'daApprovalDate' 		=> 'Approval date',
  'daSign'		 		=> 'Sign',
  'daFunction'			=> 'Role',
  'daPending'				=> 'pending',
  'daUserNotFound'		=> 'user not found',
  'da-NotNecessary'		=> 'Your sign is not necessary to this document.',
  'da-PreviouslySigned'	=> 'Document previously signed at $1',
  'sign-tab-text'	=> 'Sign',
  
  
  'da-function'			=> 'Function',
  'da-pageID' => 'Page-ID',
  'da-createarticledate'  => 'Create Date',
  'da-signer' => 'Signer',
  'da-requests' => 'Requests',
  
  
  'da-head' => 'List of signatures for user $1',
  'da-signwish' => 'Open requests to sign: $1',
  'da-signalready' => 'Already signed requests: $1',
  'da-signsum' => 'All requests: $1',
  'da-signlist' => 'All requests for signatures: $1',
);

/** French
* @author João Artur Gramacho
*/

$messages['fr'] = array(
  'documentapproval' => 'Document Approval',
  'da-desc' => "Ajoute <tt>&lt;approval&gt;</tt> fonction analyseur document d'approbation",

    
  'daloglogpage' => 'Document Approval carnet',
  
  'daTableTitle'	 		=> "Table d'approbation de la révision",
  'daApprovalDate' 		=> "Date d'approbation",
  'daSign'		 		=> "Signature",
  'daFunction'			=> "Rôle",
  'daPending'				=> "en attente",
  'daUserNotFound'		=> "utilisateur non trouvée",
  'da-NotNecessary'		=> "Votre enseigne n'est pas nécessaire à ce document.",
  'da-PreviouslySigned'	=> "Le document a été signé le $1 par vous et ne peut être une autre fois signé.",
  'sign-tab-text'	=> 'Signe',
  
  'da-signsum' => 'All requests: $1',
  'da-signalready' => "Déjà signé des demandes: $1", 
  'da-signwish' => 'Open requests to sign: $1',
  'da-signlist' => 'All requests for signatures: $1',
);
  
/** Português (Portuguese)
* @author João Artur Gramacho
*/

$messages['pt-br'] = array(
  'da-desc' => 'acrescenta <tt>&lt;approval&gt;</tt> função do analisador de aprovação do documento',
  'daTableTitle'	 		=> 'Tabela de aprovações da revisão',
  'daApprovalDate' 		=> 'Data da aprovação',
  'daSign' 				=> 'Assinatura eletrônica',
  'daFunction'			=> 'Função',
  'daPending'				=> 'pendente',
  'daUserNotFound'		=> 'usuário não encontrado',
  'da-NotNecessary'		=> 'Sua assinatura não é necessária para esse documento.',
  'da-PreviouslySigned'	=> 'Documento assinado previamente em $1',
  'sign-tab-text'	=> 'Signo',
);

Error message[edit]

Hello,

With nex version 0.3, I have a problem with SQL :

from function « fnDocumentApproval ». Databe send error message :« 1054 : Unknown column 'position' in 'field list' (localhost) ».
Hello! Probably this column does not exist in your table 'approval_request'. You can add this with phpMyAdmin. The extension should create a new table correctly if it is not there but can not check if there is missing a column (function daTableCheck). This should be improved. Anyone can help? --Jostar 12:04, 16 April 2010 (UTC)


Hello, I think there is some namespace problem. I get

 1146: Table 'db.mw_user' doesn't exist (localhost).
 

when I call

 Spezial:DocumentApproval

You have Hardcoded the namespace.

True. The prefix is should be adapted. I will look for this ... --Jostar 13:29, 8 June 2010 (UTC) Done today, should work now. --Jostar 21:48, 2 August 2010 (UTC)


Hello, I have a problem when activation this 0.3 version on a MW 1.16 with Apache/Windows. The error is: "MediaWiki internal error. Exception caught inside exception handler". When I remove the "require once"-line from my LocalSettings.php (not activate the Document Approval), everything is working... --DaJohnB 13:17, 30 March 2011 (UTC)

It was not written for Version 1.16 and need to be updated. Sorry. --Jostar 15:17, 30 March 2011 (UTC)
Thanks for this info. Now I know it's not me...--DaJohnB 06:36, 6 April 2011 (UTC)
It might as well be a problem of your database engine. I encountered the same problem, after deleting
  ENGINE=innodb
in function daTableCheck() and adding daTableCheck() in the daSkinTemplateTabs-Routine, everything worked fine for me. You then just have to make shure, that the columns position and order are created in approval_request.--Daniel Gerber 16:20, 29 April 2011 (UTC)

Doesn't seem to call fnDocumentApproval[edit]

Great extension. I really want to get it working.

MW 1.15.4 php 5.3.2 MySQL 5.1.48

I'm having difficulty though- fnDocumentApproval doesn't appear to be called. I have an existing page. I added the <approval/> tag with a valid user name inside. If I comment out everything and tell fnDocumentApproval to return "output = 'test'" I get nothing.

The extension shows up on Version. I've manually input data into the approval_request table, and it returns results on the Special page. Am I missing something? Is there anything that might be interfering? Is there a way to step through and see what's going on? Thanks for your help, Phil. --12.234.65.231 23:05, 30 July 2010 (UTC)

Do you use version 0.0.3? What is your database prefix? Do you tried only <approval....\> or also <approval>....<approval/>?
I use 0.3. Actually I got it to work. I changed the function definition in DocumentApproval.body.php from "function fnDocumentApproval( $input, $argv, &$parser )" to "function fnDocumentApproval( $input, $argv, $parser )" (removed the ampersand). Not sure if this is a good idea, but it seems to work, and I've noticed other extension functions with the same pattern. Phil--12.234.65.231 22:08, 2 August 2010 (UTC)
I had the same problem to fix after moving from windows to linux based mediawiki installation. Linux do not like the "&"s... functions are still working. --Jostar 12:00, 3 August 2010 (UTC)

Failed to use the CONSTRAINTS[edit]

I tried to use the SQL statements on the article page to create the database tables required for this extension. (Adding the "position" and "order" needed for version 0.3 of DocumentApproval.) But I failed miserably getting only the somewhat obscure failure message:

ERROR 1005 (HY000) at line 35: Can't create table 'wikidb.approval_version' (errno: 150)

By trial and error, I figured out that if I commented out two of the CONSTRAINT (as below) the table creation works just fine.

Do I risk something bad by excluding the CONTSTRAINTs when creating my tables?

I'm using:

# mysql --version
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
USE wikidb;
SET FOREIGN_KEY_CHECKS = 0;
 
--
-- Table structure for table `approval_request`
--
DROP TABLE IF EXISTS `approval_request`;
CREATE TABLE `approval_request` (
  `page_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `description` varchar(255) default NULL,
  `position` int(10) unsigned NOT NULL,
  `order` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`page_id`,`user_id`),
  KEY `FK_approval_request_user` (`user_id`),
  CONSTRAINT `FK_approval_request_page` FOREIGN KEY (`page_id`) REFERENCES `page` (`page_id`),
  CONSTRAINT `FK_approval_request_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `approval`
--
DROP TABLE IF EXISTS `approval`;
CREATE TABLE `approval` (
  `rev_id` int(10) unsigned NOT NULL,
  `rev_page` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`rev_id`,`rev_page`,`user_id`),
  KEY `FK_approval_user` (`user_id`),
--  CONSTRAINT `FK_approval_revision` FOREIGN KEY (`rev_id`, `rev_page`) REFERENCES `revision` (`rev_id`, `rev_page`),
  CONSTRAINT `FK_approval_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `approval_version`
--
DROP TABLE IF EXISTS `approval_version`;
CREATE TABLE `approval_version` (
  `page_id` int(10) unsigned NOT NULL,
  `last_approved_revision_id` int(10) unsigned default NULL,
  `version` int(10) unsigned NOT NULL default '0',
  `subversion` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`page_id`),
  KEY `FK_approval_version_revision_id` (`last_approved_revision_id`,`page_id`),
  CONSTRAINT `FK_approval_version_page_id` FOREIGN KEY (`page_id`) REFERENCES `page` (`page_id`)
--  CONSTRAINT `FK_approval_version_revision_id` FOREIGN KEY (`last_approved_revision_id`, `page_id`) REFERENCES `revision` (`rev_id`, `rev_page`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Above is the is the modified table creation statements (as applicable to my database) from the article page. Note that I don't have a <PREFIX> part, so if you want to use this to create your own tables, you'll have to put that back in.


/Zrajm 12:39, 15 April 2011 (UTC)


As provided by a1ex07 at Error No 150 mySQL - Stack Overflow
it will work if you change the order of columns in constraint definition:
CONSTRAINT `FK_approval_revision` FOREIGN KEY (`rev_page`,`rev_id`) REFERENCES `revision` (`rev_page`,`rev_id`),


KEY `FK_approval_version_revision_id` (`page_id`, `last_approved_revision_id`),
...
CONSTRAINT `FK_approval_version_revision_id` FOREIGN KEY (`page_id`, `last_approved_revision_id`) REFERENCES `revision` (`rev_page`, `rev_id`)
Original query doesn't work because the order of fields must be the same as in unique index. However, adding rev_page column to constraint is superfluous (revision.rev_id + revision.rev_page is 100% unique without a unique constraint, since revision.rev_id unique by itself). So you don't need unique key on (revision.rev_id + revision.rev_page), and it would be much better if you change your constraint to
CONSTRAINT `FK_approval_revision` FOREIGN KEY (`rev_id`) REFERENCES `revision` (`rev_id`)