1 <?php rcs_id('$Id: mysql.php,v 1.10.2.8 2005-07-23 11:15:48 rurban Exp $');
7 MakeDBHash($pagename, $pagehash)
9 RetrievePage($dbi, $pagename, $pagestore)
10 InsertPage($dbi, $pagename, $pagehash)
11 SaveCopyToArchive($dbi, $pagename, $pagehash)
12 IsWikiPage($dbi, $pagename)
13 IsInArchive($dbi, $pagename)
14 RemovePage($dbi, $pagename)
15 IncreaseHitCount($dbi, $pagename)
16 GetHitCount($dbi, $pagename)
17 MakeSQLSearchClause($search, $column)
18 InitTitleSearch($dbi, $search)
19 TitleSearchNextMatch($dbi, $res)
20 InitFullSearch($dbi, $search)
21 FullSearchNextMatch($dbi, $res)
22 InitBackLinkSearch($dbi, $pagename)
23 BackLinkSearchNextMatch($dbi, &$pos)
24 InitMostPopular($dbi, $limit)
25 MostPopularNextMatch($dbi, $res)
26 GetAllWikiPageNames($dbi)
27 GetWikiPageLinks($dbi, $pagename)
28 SetWikiPageLinks($dbi, $pagename, $linklist)
31 // open a database and return the handle
32 // ignores MAX_DBM_ATTEMPTS
34 function OpenDataBase($dbname) {
35 global $mysql_server, $mysql_user, $mysql_pwd, $mysql_db;
37 // smaller servers might benefit from mysql_pconnect, but larger ones
38 // may run out of connections
39 if (!($dbc = mysql_connect($mysql_server, $mysql_user, $mysql_pwd))) {
40 $msg = gettext ("Cannot establish connection to database, giving up.");
42 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
45 if (!mysql_select_db($mysql_db, $dbc)) {
46 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $mysql_db);
48 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
52 $dbi['table'] = $dbname;
57 function CloseDataBase($dbi) {
59 // mysql connections are established as persistant
60 // they cannot be closed through mysql_close()
64 // prepare $pagehash for storing in mysql
65 function MakeDBHash($pagename, $pagehash)
67 $pagehash["pagename"] = addslashes($pagename);
68 if (!isset($pagehash["flags"]))
69 $pagehash["flags"] = 0;
70 $pagehash["author"] = addslashes($pagehash["author"]);
71 $pagehash["content"] = implode("\n", $pagehash["content"]);
72 $pagehash["content"] = addslashes($pagehash["content"]);
73 if (!isset($pagehash["refs"]))
74 $pagehash["refs"] = array();
75 $pagehash["refs"] = serialize($pagehash["refs"]);
81 // convert mysql result $dbhash to $pagehash
82 function MakePageHash($dbhash)
84 // unserialize/explode content
85 $dbhash['refs'] = unserialize($dbhash['refs']);
86 $dbhash['content'] = explode("\n", $dbhash['content']);
91 // Return hash of page + attributes or default
92 function RetrievePage($dbi, $pagename, $pagestore) {
93 $pagename = addslashes($pagename);
94 if ($res = mysql_query("SELECT * FROM $pagestore WHERE pagename='$pagename'", $dbi['dbc'])) {
95 if ($dbhash = mysql_fetch_array($res)) {
96 return MakePageHash($dbhash);
103 // Either insert or replace a key/value (a page)
104 function InsertPage($dbi, $pagename, $pagehash)
106 global $WikiPageStore; // ugly hack
108 if ($dbi['table'] == $WikiPageStore) { // HACK
109 $linklist = ExtractWikiPageLinks($pagehash['content']);
110 SetWikiPageLinks($dbi, $pagename, $linklist);
113 $pagehash = MakeDBHash($pagename, $pagehash);
115 $COLUMNS = "author, content, created, flags, " .
116 "lastmodified, pagename, refs, version";
118 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
119 "$pagehash[created], $pagehash[flags], " .
120 "$pagehash[lastmodified], '$pagehash[pagename]', " .
121 "'$pagehash[refs]', $pagehash[version]";
123 if (!mysql_query("REPLACE INTO ".$dbi['table']." ($COLUMNS) VALUES ($VALUES)",
125 $msg = sprintf(gettext ("Error writing page '%s'"), $pagename);
127 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
133 // for archiving pages to a seperate dbm
134 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
135 global $ArchivePageStore;
136 $adbi = OpenDataBase($ArchivePageStore);
137 InsertPage($adbi, $pagename, $pagehash);
141 function IsWikiPage($dbi, $pagename) {
142 $pagename = addslashes($pagename);
143 if ($res = mysql_query("SELECT COUNT(*) FROM ".$dbi['table']." WHERE pagename='$pagename'",
145 return(mysql_result($res, 0));
150 function IsInArchive($dbi, $pagename) {
151 global $ArchivePageStore;
153 $pagename = addslashes($pagename);
154 if ($res = mysql_query("SELECT COUNT(*) FROM $ArchivePageStore WHERE pagename='$pagename'",
156 return(mysql_result($res, 0));
162 function RemovePage($dbi, $pagename) {
163 global $WikiPageStore, $ArchivePageStore;
164 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
166 $pagename = addslashes($pagename);
167 $msg = gettext ("Cannot delete '%s' from table '%s'");
169 $msg .= gettext ("MySQL error: %s");
171 if (!mysql_query("DELETE FROM $WikiPageStore WHERE pagename='$pagename'", $dbi['dbc']))
172 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysql_error()));
174 if (!mysql_query("DELETE FROM $ArchivePageStore WHERE pagename='$pagename'", $dbi['dbc']))
175 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysql_error()));
177 if (!mysql_query("DELETE FROM $WikiLinksStore WHERE frompage='$pagename'", $dbi['dbc']))
178 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysql_error()));
180 if (!mysql_query("DELETE FROM $HitCountStore WHERE pagename='$pagename'", $dbi['dbc']))
181 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysql_error()));
183 if (!mysql_query("DELETE FROM $WikiScoreStore WHERE pagename='$pagename'", $dbi['dbc']))
184 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
188 function IncreaseHitCount($dbi, $pagename)
190 global $HitCountStore;
192 $qpagename = addslashes($pagename);
193 $res = mysql_query("UPDATE $HitCountStore SET hits=hits+1"
194 . " WHERE pagename='$qpagename'",
197 if (!mysql_affected_rows($dbi['dbc'])) {
198 $res = mysql_query("INSERT INTO $HitCountStore (pagename, hits)"
199 . " VALUES ('$qpagename', 1)",
206 function GetHitCount($dbi, $pagename)
208 global $HitCountStore;
210 $qpagename = addslashes($pagename);
211 $res = mysql_query("SELECT hits FROM $HitCountStore"
212 . " WHERE pagename='$qpagename'",
214 if (mysql_num_rows($res))
215 $hits = mysql_result($res, 0);
222 function MakeSQLSearchClause($search, $column)
224 $search = preg_replace("/\s+/", " ", trim($search));
225 $search = preg_replace('/(?=[%_\\\\])/', "\\", $search);
226 $search = addslashes($search);
228 $term = strtok($search, ' ');
231 $word = strtolower("$term");
232 if ($word[0] == '-') {
233 $word = substr($word, 1);
234 $clause .= "NOT (LCASE($column) LIKE '%$word%') ";
236 $clause .= "(LCASE($column) LIKE '%$word%') ";
238 if ($term = strtok(' '))
245 // setup for title-search
246 function InitTitleSearch($dbi, $search) {
247 $clause = MakeSQLSearchClause($search, 'pagename');
248 $res = mysql_query("SELECT pagename FROM ".$dbi['table']." WHERE $clause ORDER BY pagename",
254 // iterating through database
255 function TitleSearchNextMatch($dbi, $res) {
256 if($o = mysql_fetch_object($res)) {
265 // setup for full-text search
266 function InitFullSearch($dbi, $search) {
267 $clause = MakeSQLSearchClause($search, 'content');
268 $res = mysql_query("SELECT * FROM ".$dbi['table']." WHERE $clause", $dbi["dbc"]);
273 // iterating through database
274 function FullSearchNextMatch($dbi, $res) {
275 if($hash = mysql_fetch_array($res)) {
276 return MakePageHash($hash);
283 // setup for back-link search
284 function InitBackLinkSearch($dbi, $pagename) {
285 global $WikiLinksStore;
287 $topage = addslashes($pagename);
288 $res = mysql_query( "SELECT DISTINCT frompage FROM $WikiLinksStore"
289 . " WHERE topage='$topage'"
290 . " ORDER BY frompage",
296 // iterating through database
297 function BackLinkSearchNextMatch($dbi, $res) {
298 if($a = mysql_fetch_row($res)) {
307 function InitMostPopular($dbi, $limit) {
308 global $HitCountStore;
309 $res = mysql_query("SELECT * FROM $HitCountStore ORDER BY hits desc, pagename LIMIT $limit",
315 function MostPopularNextMatch($dbi, $res) {
316 if ($hits = mysql_fetch_array($res))
322 function GetAllWikiPageNames($dbi) {
323 global $WikiPageStore;
324 $res = mysql_query("SELECT pagename FROM $WikiPageStore", $dbi["dbc"]);
325 $rows = mysql_num_rows($res);
326 for ($i = 0; $i < $rows; $i++) {
327 $pages[$i] = mysql_result($res, $i);
333 ////////////////////////////////////////
334 // functionality for the wikilinks table
336 // takes a page name, returns array of scored incoming and outgoing links
337 function GetWikiPageLinks($dbi, $pagename) {
338 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
340 $pagename = addslashes($pagename);
341 $res = mysql_query("SELECT topage, score FROM $WikiLinksStore, $WikiScoreStore"
342 ." WHERE topage=pagename AND frompage='$pagename'"
343 ." ORDER BY score DESC, topage");
344 $rows = mysql_num_rows($res);
345 for ($i = 0; $i < $rows; $i++) {
346 $out = mysql_fetch_array($res);
347 $links['out'][] = array($out['topage'], $out['score']);
350 $res = mysql_query("SELECT frompage, score FROM $WikiLinksStore, $WikiScoreStore"
351 ." WHERE frompage=pagename AND topage='$pagename'"
352 ." ORDER BY score DESC, frompage");
353 $rows = mysql_num_rows($res);
354 for ($i = 0; $i < $rows; $i++) {
355 $out = mysql_fetch_array($res);
356 $links['in'][] = array($out['frompage'], $out['score']);
359 $res = mysql_query("SELECT DISTINCT pagename, hits FROM $WikiLinksStore, $HitCountStore"
360 ." WHERE (frompage=pagename AND topage='$pagename')"
361 ." OR (topage=pagename and frompage='$pagename')"
362 ." ORDER BY hits DESC, pagename");
363 $rows = mysql_num_rows($res);
364 for ($i = 0; $i < $rows; $i++) {
365 $out = mysql_fetch_array($res);
366 $links['popular'][] = array($out['pagename'], $out['hits']);
373 // takes page name, list of links it contains
374 // the $linklist is an array where the keys are the page names
375 function SetWikiPageLinks($dbi, $pagename, $linklist) {
376 global $WikiLinksStore, $WikiScoreStore;
378 $frompage = addslashes($pagename);
380 // first delete the old list of links
381 mysql_query("DELETE FROM $WikiLinksStore WHERE frompage='$frompage'",
384 // the page may not have links, return if not
385 if (! count($linklist))
387 // now insert the new list of links
388 while (list($topage, $count) = each($linklist)) {
389 $topage = addslashes($topage);
390 if($topage != $frompage) {
391 mysql_query("INSERT INTO $WikiLinksStore (frompage, topage)"
392 ." VALUES ('$frompage', '$topage')", $dbi["dbc"]);
397 mysql_query("DELETE FROM $WikiScoreStore", $dbi["dbc"]);
398 mysql_query("INSERT INTO $WikiScoreStore"
399 ." SELECT w1.topage, COUNT(*) FROM $WikiLinksStore AS w1, $WikiLinksStore AS w2"
400 ." WHERE w2.topage=w1.frompage GROUP BY w1.topage", $dbi["dbc"]);
403 /* more mysql queries:
406 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;