mysqli

[Marc Lefkon]

If someone would like, I've upgraded the mysql.php file in /lib for use with the mysqli extension. Replace all the text in the file with:

<?php rcs_id('$Id: mysql.php,v 1.10.2.4 2001/11/07 20:30:47 dairiki Exp $');

/
Database functions:
OpenDataBase($dbname)
CloseDataBase($dbi)
MakeDBHash($pagename, $pagehash)
MakePageHash($dbhash)
RetrievePage($dbi, $pagename, $pagestore)
InsertPage($dbi, $pagename, $pagehash)
SaveCopyToArchive($dbi, $pagename, $pagehash)
IsWikiPage($dbi, $pagename)
IsInArchive($dbi, $pagename)
RemovePage($dbi, $pagename)
IncreaseHitCount($dbi, $pagename)
GetHitCount($dbi, $pagename)
MakeSQLSearchClause($search, $column)
InitTitleSearch($dbi, $search)
TitleSearchNextMatch($dbi, $res)
InitFullSearch($dbi, $search)
FullSearchNextMatch($dbi, $res)
InitBackLinkSearch($dbi, $pagename)
BackLinkSearchNextMatch($dbi, &$pos)
InitMostPopular($dbi, $limit)
MostPopularNextMatch($dbi, $res)
GetAllWikiPageNames($dbi)
GetWikiPageLinks($dbi, $pagename)
SetWikiPageLinks($dbi, $pagename, $linklist)
/

// open a database and return the handle
// ignores MAX_DBM_ATTEMPTS

function OpenDataBase($dbname) {
global $mysql_server, $mysql_user, $mysql_pwd, $mysql_db;

  // smaller servers might benefit from mysql_pconnect, but larger ones 
  // may run out of connections
  if (!($dbc = mysqli_connect($mysql_server, $mysql_user, $mysql_pwd))) { 
     $msg = gettext ("Cannot establish connection to database, giving up.");
 $msg .= "<br />";
 $msg .= sprintf(gettext ("MySQL error: %s"), mysqli_error($dbc));
 ExitWiki($msg);
  }
  if (!mysqli_select_db($dbc, $mysql_db)) { 
     $msg =  sprintf(gettext ("Cannot open database %s, giving up."), $mysql_db);
 $msg .= "<br />";
 $msg .= sprintf(gettext ("MySQL error: %s"), mysqli_error($dbc)); 
 ExitWiki($msg);
  }
  $dbi['dbc'] = $dbc;
  $dbi['table'] = $dbname;
  return $dbi;

}

function CloseDataBase($dbi) {
// NOP function
// mysql connections are established as persistant
// they cannot be closed through mysql_close()
}

// prepare $pagehash for storing in mysql
function MakeDBHash($pagename, $pagehash)
{
$pagehash["pagename"] = addslashes($pagename);
if (!isset($pagehash["flags"]))
$pagehash["flags"] = 0;
$pagehash["author"] = addslashes($pagehash["author"]);
$pagehash["content"] = implode("\n", $pagehash["content"]);
$pagehash["content"] = addslashes($pagehash["content"]);
if (!isset($pagehash["refs"]))
$pagehash["refs"] = array();
$pagehash["refs"] = serialize($pagehash["refs"]);

  return $pagehash;

}

// convert mysql result $dbhash to $pagehash
function MakePageHash($dbhash)
{
// unserialize/explode content
$dbhash['refs'] = unserialize($dbhash['refs']);
$dbhash['content'] = explode("\n", $dbhash['content']);
return $dbhash;
}

// Return hash of page + attributes or default
function RetrievePage($dbi, $pagename, $pagestore) {
$pagename = addslashes($pagename);
if ($res = mysqli_query($dbi['dbc'],"SELECT * FROM $pagestore WHERE pagename='$pagename'")) {
if ($dbhash = mysqli_fetch_array($res)) {
return MakePageHash($dbhash);
}
}
return -1;
}

// Either insert or replace a key/value (a page)
function InsertPage($dbi, $pagename, $pagehash)
{
global $WikiPageStore; // ugly hack

  if ($dbi['table'] == $WikiPageStore) { // HACK
     $linklist = ExtractWikiPageLinks($pagehash['content']);
 SetWikiPageLinks($dbi, $pagename, $linklist);
  }

  $pagehash = MakeDBHash($pagename, $pagehash);

  $COLUMNS = "author, content, created, flags, " .
             "lastmodified, pagename, refs, version";

  $VALUES =  "'$pagehash[author]', '$pagehash[content]', " .
             "$pagehash[created], $pagehash[flags], " .
             "$pagehash[lastmodified], '$pagehash[pagename]', " .
             "'$pagehash[refs]', $pagehash[version]";

  if (!mysqli_query($dbi['dbc'], "REPLACE INTO ".$dbi['table']." ($COLUMNS) VALUES ($VALUES)")) { 
        $msg = sprintf(gettext ("Error writing page '%s'"), $pagename);
    $msg .= "<br />";
    $msg .= sprintf(gettext ("MySQL error: %s"), mysqli_error($dbi['dbc']));
        ExitWiki($msg);
  }

}

// for archiving pages to a seperate dbm
function SaveCopyToArchive($dbi, $pagename, $pagehash) {
global $ArchivePageStore;
$adbi = OpenDataBase($ArchivePageStore);
InsertPage($adbi, $pagename, $pagehash);
}

function IsWikiPage($dbi, $pagename) {
$pagename = addslashes($pagename);
if ($res = mysqli_query($dbi['dbc'], "SELECT COUNT(*) FROM ".$dbi['table']." WHERE pagename='$pagename'")) {
$row = mysqli_fetch_row($res);
return( $row[0] );
}
return 0;
}

function IsInArchive($dbi, $pagename) {
global $ArchivePageStore;

  $pagename = addslashes($pagename);
  if ($res = mysqli_query($dbi['dbc'], "SELECT COUNT(*) FROM $ArchivePageStore WHERE pagename='$pagename'")) { 
     $row = mysqli_fetch_row($res);
     return( $row[0] ); 
  }
  return 0;

}

function RemovePage($dbi, $pagename) {
global $WikiPageStore, $ArchivePageStore;
global $WikiLinksStore, $HitCountStore, $WikiScoreStore;

  $pagename = addslashes($pagename);
  $msg = gettext ("Cannot delete '%s' from table '%s'");
  $msg .= "<br>\n";
  $msg .= gettext ("MySQL error: %s");

  if (!mysqli_query($dbi['dbc'], "DELETE FROM $WikiPageStore WHERE pagename='$pagename'"))
     ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysqli_error($dbi['dbc'])));

  if (!mysqli_query($dbi['dbc'], "DELETE FROM $ArchivePageStore WHERE pagename='$pagename'"))
     ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysqli_error($dbi['dbc'])));

  if (!mysqli_query($dbi['dbc'], "DELETE FROM $WikiLinksStore WHERE frompage='$pagename'"))
     ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysqli_error($dbi['dbc'])));

  if (!mysqli_query($dbi['dbc'], "DELETE FROM $HitCountStore WHERE pagename='$pagename'"))
     ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysqli_error($dbi['dbc'])));

  if (!mysqli_query($dbi['dbc'], "DELETE FROM $WikiScoreStore WHERE pagename='$pagename'"))
     ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysqli_error($dbi['dbc'])));

}

function IncreaseHitCount($dbi, $pagename)
{
global $HitCountStore;

  $qpagename = addslashes($pagename);
  $res = mysqli_query($dbi['dbc'], "UPDATE $HitCountStore SET hits=hits+1 WHERE pagename='$qpagename'");

  if (!mysqli_affected_rows($dbi['dbc'])) { 
        $res = mysqli_query($dbi['dbc'], "INSERT INTO $HitCountStore (pagename, hits) VALUES ('$qpagename', 1)");
  }

  return $res;

}

function GetHitCount($dbi, $pagename)
{
global $HitCountStore;

  $qpagename = addslashes($pagename);
  $res = mysqli_query($dbi['dbc'], "SELECT hits FROM $HitCountStore WHERE pagename='$qpagename'");
  if (mysqli_num_rows($res)) { 
     $row = mysqli_fetch_row($res);
     $hits = $row[0]; 
  } else {
     $hits = "0";
  }
  return $hits;

}

function MakeSQLSearchClause($search, $column)
{
$search = preg_replace("/\s+/", " ", trim($search));
$search = preg_replace('/(?=[%_\\])/', "\", $search);
$search = addslashes($search);

  $term = strtok($search, ' ');
  $clause = '';
  while($term) {
     $word = strtolower("$term");
 if ($word[0] == '-') {
    $word = substr($word, 1);
    $clause .= "NOT (LCASE($column) LIKE '%$word%') ";
 } else {
    $clause .= "(LCASE($column) LIKE '%$word%') ";
 }
 if ($term = strtok(' '))
    $clause .= 'AND ';
  }

  return $clause;

}

// setup for title-search
function InitTitleSearch($dbi, $search) {
$clause = MakeSQLSearchClause($search, 'pagename');
$res = mysqli_query($dbi["dbc"], "SELECT pagename FROM ".$dbi['table']." WHERE $clause ORDER BY pagename");
return $res;
}

// iterating through database
function TitleSearchNextMatch($dbi, $res) {
if($o = mysqli_fetch_object($res)) {
return $o->pagename;
}
else {
return 0;
}
}

// setup for full-text search
function InitFullSearch($dbi, $search) {
$clause = MakeSQLSearchClause($search, 'content');
$res = mysqli_query($dbi["dbc"], "SELECT * FROM ".$dbi['table']." WHERE $clause");

  return $res;

}

// iterating through database
function FullSearchNextMatch($dbi, $res) {
if($hash = mysqli_fetch_array($res)) {
return MakePageHash($hash);
}
else {
return 0;
}
}

// setup for back-link search
function InitBackLinkSearch($dbi, $pagename) {
global $WikiLinksStore;

  $topage = addslashes($pagename);
  $res = mysqli_query($dbi["dbc"], "SELECT DISTINCT frompage FROM $WikiLinksStore WHERE topage='$topage' ORDER BY frompage");
  return $res;

}

// iterating through database
function BackLinkSearchNextMatch($dbi, $res) {
if($a = mysqli_fetch_row($res)) {
return $a[0];
}
else {
return 0;
}
}

function InitMostPopular($dbi, $limit) {
global $HitCountStore;
$res = mysqli_query($dbi["dbc"], "SELECT * FROM $HitCountStore ORDER BY hits desc, pagename LIMIT $limit");

  return $res;

}

function MostPopularNextMatch($dbi, $res) {
if ($hits = mysqli_fetch_array($res))
return $hits;
else
return 0;
}

function GetAllWikiPageNames($dbi) {
global $WikiPageStore;
$res = mysqli_query($dbi["dbc"], "SELECT pagename FROM $WikiPageStore");
$rows = mysqli_num_rows($res);
for ($i = 0; $i < $rows; $i++) {
$row = mysqli_fetch_row($res);
$hits = $row[0];
}
return $pages;
}

////////////////////////////////////////
// functionality for the wikilinks table

// takes a page name, returns array of scored incoming and outgoing links
function GetWikiPageLinks($dbi, $pagename) {
global $WikiLinksStore, $WikiScoreStore, $HitCountStore;

  $pagename = addslashes($pagename);
  $res = mysqli_query($dbi["dbc"], "SELECT topage, score FROM $WikiLinksStore, $WikiScoreStore WHERE topage=pagename AND frompage='$pagename' ORDER BY score DESC, topage"); 
  $rows = mysqli_num_rows($res);
  for ($i = 0; $i < $rows; $i++) {
       $out = mysqli_fetch_array($res);
       $links['out'][] = array($out['topage'], $out['score']);
  }

  $res = mysqli_query($dbi["dbc"], "SELECT frompage, score FROM $WikiLinksStore, $WikiScoreStore WHERE frompage=pagename AND topage='$pagename' ORDER BY score DESC, frompage"); 
  $rows = mysqli_num_rows($res);
  for ($i = 0; $i < $rows; $i++) {
     $out = mysqli_fetch_array($res);
     $links['in'][] = array($out['frompage'], $out['score']);
  }

  $res = mysqli_query($dbi["dbc"], "SELECT DISTINCT pagename, hits FROM $WikiLinksStore, $HitCountStore WHERE (frompage=pagename AND topage='$pagename') OR (topage=pagename and frompage='$pagename') ORDER BY hits DESC, pagename"); 
  $rows = mysqli_num_rows($res);
  for ($i = 0; $i < $rows; $i++) {
     $out = mysqli_fetch_array($res);
     $links['popular'][] = array($out['pagename'], $out['hits']);
  }

  return $links;

}

// takes page name, list of links it contains
// the $linklist is an array where the keys are the page names
function SetWikiPageLinks($dbi, $pagename, $linklist) {
global $WikiLinksStore, $WikiScoreStore;

  $frompage = addslashes($pagename);

  // first delete the old list of links
  mysqli_query($dbi["dbc"], "DELETE FROM $WikiLinksStore WHERE frompage='$frompage'");

  // the page may not have links, return if not
  if (! count($linklist))
     return;
  // now insert the new list of links
  while (list($topage, $count) = each($linklist)) {
     $topage = addslashes($topage);
 if($topage != $frompage) {
  mysqli_query($dbi["dbc"], "INSERT INTO $WikiLinksStore (frompage, topage) VALUES ('$frompage', '$topage')");
 }
  }

  // update pagescore
  mysqli_query($dbi["dbc"], "DELETE FROM $WikiScoreStore");
  mysqli_query($dbi["dbc"], "INSERT INTO $WikiScoreStore" 
              ." SELECT w1.topage, COUNT(*) FROM $WikiLinksStore AS w1, $WikiLinksStore AS w2"
              ." WHERE w2.topage=w1.frompage GROUP BY w1.topage");

}

/* more mysql queries:

orphans:
select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
*/
?>