1 <?php rcs_id('$Id: mysql.php,v 1.10.2.4 2001-11-07 20:30:47 dairiki 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 if (!($dbc = mysql_pconnect($mysql_server, $mysql_user, $mysql_pwd))) {
38 $msg = gettext ("Cannot establish connection to database, giving up.");
40 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
43 if (!mysql_select_db($mysql_db, $dbc)) {
44 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $mysql_db);
46 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
50 $dbi['table'] = $dbname;
55 function CloseDataBase($dbi) {
57 // mysql connections are established as persistant
58 // they cannot be closed through mysql_close()
62 // prepare $pagehash for storing in mysql
63 function MakeDBHash($pagename, $pagehash)
65 $pagehash["pagename"] = addslashes($pagename);
66 if (!isset($pagehash["flags"]))
67 $pagehash["flags"] = 0;
68 $pagehash["author"] = addslashes($pagehash["author"]);
69 $pagehash["content"] = implode("\n", $pagehash["content"]);
70 $pagehash["content"] = addslashes($pagehash["content"]);
71 if (!isset($pagehash["refs"]))
72 $pagehash["refs"] = array();
73 $pagehash["refs"] = serialize($pagehash["refs"]);
79 // convert mysql result $dbhash to $pagehash
80 function MakePageHash($dbhash)
82 // unserialize/explode content
83 $dbhash['refs'] = unserialize($dbhash['refs']);
84 $dbhash['content'] = explode("\n", $dbhash['content']);
89 // Return hash of page + attributes or default
90 function RetrievePage($dbi, $pagename, $pagestore) {
91 $pagename = addslashes($pagename);
92 if ($res = mysql_query("select * from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
93 if ($dbhash = mysql_fetch_array($res)) {
94 return MakePageHash($dbhash);
101 // Either insert or replace a key/value (a page)
102 function InsertPage($dbi, $pagename, $pagehash)
104 global $WikiPageStore; // ugly hack
106 if ($dbi['table'] == $WikiPageStore) { // HACK
107 $linklist = ExtractWikiPageLinks($pagehash['content']);
108 SetWikiPageLinks($dbi, $pagename, $linklist);
111 $pagehash = MakeDBHash($pagename, $pagehash);
113 $COLUMNS = "author, content, created, flags, " .
114 "lastmodified, pagename, refs, version";
116 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
117 "$pagehash[created], $pagehash[flags], " .
118 "$pagehash[lastmodified], '$pagehash[pagename]', " .
119 "'$pagehash[refs]', $pagehash[version]";
121 if (!mysql_query("replace into $dbi[table] ($COLUMNS) values ($VALUES)",
123 $msg = sprintf(gettext ("Error writing page '%s'"), $pagename);
125 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
131 // for archiving pages to a seperate dbm
132 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
133 global $ArchivePageStore;
134 $adbi = OpenDataBase($ArchivePageStore);
135 InsertPage($adbi, $pagename, $pagehash);
139 function IsWikiPage($dbi, $pagename) {
140 $pagename = addslashes($pagename);
141 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
142 return(mysql_result($res, 0));
147 function IsInArchive($dbi, $pagename) {
148 global $ArchivePageStore;
150 $pagename = addslashes($pagename);
151 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
152 return(mysql_result($res, 0));
158 function RemovePage($dbi, $pagename) {
159 global $WikiPageStore, $ArchivePageStore;
160 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
162 $pagename = addslashes($pagename);
163 $msg = gettext ("Cannot delete '%s' from table '%s'");
165 $msg .= gettext ("MySQL error: %s");
167 if (!mysql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
168 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysql_error()));
170 if (!mysql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
171 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysql_error()));
173 if (!mysql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
174 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysql_error()));
176 if (!mysql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
177 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysql_error()));
179 if (!mysql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
180 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
184 function IncreaseHitCount($dbi, $pagename)
186 global $HitCountStore;
188 $qpagename = addslashes($pagename);
189 $res = mysql_query("update $HitCountStore set hits=hits+1"
190 . " where pagename='$qpagename'",
193 if (!mysql_affected_rows($dbi['dbc'])) {
194 $res = mysql_query("insert into $HitCountStore (pagename, hits)"
195 . " values ('$qpagename', 1)",
202 function GetHitCount($dbi, $pagename)
204 global $HitCountStore;
206 $qpagename = addslashes($pagename);
207 $res = mysql_query("select hits from $HitCountStore"
208 . " where pagename='$qpagename'",
210 if (mysql_num_rows($res))
211 $hits = mysql_result($res, 0);
218 function MakeSQLSearchClause($search, $column)
220 $search = preg_replace("/\s+/", " ", trim($search));
221 $search = preg_replace('/(?=[%_\\\\])/', "\\", $search);
222 $search = addslashes($search);
224 $term = strtok($search, ' ');
227 $word = strtolower("$term");
228 if ($word[0] == '-') {
229 $word = substr($word, 1);
230 $clause .= "not (LCASE($column) like '%$word%') ";
232 $clause .= "(LCASE($column) like '%$word%') ";
234 if ($term = strtok(' '))
241 // setup for title-search
242 function InitTitleSearch($dbi, $search) {
243 $clause = MakeSQLSearchClause($search, 'pagename');
244 $res = mysql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
250 // iterating through database
251 function TitleSearchNextMatch($dbi, $res) {
252 if($o = mysql_fetch_object($res)) {
261 // setup for full-text search
262 function InitFullSearch($dbi, $search) {
263 $clause = MakeSQLSearchClause($search, 'content');
264 $res = mysql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
269 // iterating through database
270 function FullSearchNextMatch($dbi, $res) {
271 if($hash = mysql_fetch_array($res)) {
272 return MakePageHash($hash);
279 // setup for back-link search
280 function InitBackLinkSearch($dbi, $pagename) {
281 global $WikiLinksStore;
283 $topage = addslashes($pagename);
284 $res = mysql_query( "SELECT DISTINCT frompage FROM $WikiLinksStore"
285 . " WHERE topage='$topage'"
286 . " ORDER BY frompage",
292 // iterating through database
293 function BackLinkSearchNextMatch($dbi, $res) {
294 if($a = mysql_fetch_row($res)) {
303 function InitMostPopular($dbi, $limit) {
304 global $HitCountStore;
305 $res = mysql_query("select * from $HitCountStore order by hits desc, pagename limit $limit", $dbi["dbc"]);
310 function MostPopularNextMatch($dbi, $res) {
311 if ($hits = mysql_fetch_array($res))
317 function GetAllWikiPageNames($dbi) {
318 global $WikiPageStore;
319 $res = mysql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
320 $rows = mysql_num_rows($res);
321 for ($i = 0; $i < $rows; $i++) {
322 $pages[$i] = mysql_result($res, $i);
328 ////////////////////////////////////////
329 // functionality for the wikilinks table
331 // takes a page name, returns array of scored incoming and outgoing links
332 function GetWikiPageLinks($dbi, $pagename) {
333 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
335 $pagename = addslashes($pagename);
336 $res = mysql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
337 $rows = mysql_num_rows($res);
338 for ($i = 0; $i < $rows; $i++) {
339 $out = mysql_fetch_array($res);
340 $links['out'][] = array($out['topage'], $out['score']);
343 $res = mysql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
344 $rows = mysql_num_rows($res);
345 for ($i = 0; $i < $rows; $i++) {
346 $out = mysql_fetch_array($res);
347 $links['in'][] = array($out['frompage'], $out['score']);
350 $res = mysql_query("select distinct pagename, hits from $WikiLinksStore, $HitCountStore where (frompage=pagename and topage='$pagename') or (topage=pagename and frompage='$pagename') order by hits desc, pagename");
351 $rows = mysql_num_rows($res);
352 for ($i = 0; $i < $rows; $i++) {
353 $out = mysql_fetch_array($res);
354 $links['popular'][] = array($out['pagename'], $out['hits']);
361 // takes page name, list of links it contains
362 // the $linklist is an array where the keys are the page names
363 function SetWikiPageLinks($dbi, $pagename, $linklist) {
364 global $WikiLinksStore, $WikiScoreStore;
366 $frompage = addslashes($pagename);
368 // first delete the old list of links
369 mysql_query("delete from $WikiLinksStore where frompage='$frompage'",
372 // the page may not have links, return if not
373 if (! count($linklist))
375 // now insert the new list of links
376 while (list($topage, $count) = each($linklist)) {
377 $topage = addslashes($topage);
378 if($topage != $frompage) {
379 mysql_query("insert into $WikiLinksStore (frompage, topage) " .
380 "values ('$frompage', '$topage')", $dbi["dbc"]);
385 mysql_query("delete from $WikiScoreStore", $dbi["dbc"]);
386 mysql_query("insert into $WikiScoreStore select w1.topage, count(*) from $WikiLinksStore as w1, $WikiLinksStore as w2 where w2.topage=w1.frompage group by w1.topage", $dbi["dbc"]);
389 /* more mysql queries:
392 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;