1 <?php rcs_id('$Id: mysql.php,v 1.15 2001-07-18 01:06:29 uckelman Exp $');
7 MakeDBHash($pagename, $pagehash)
9 RetrievePage($dbi, $pagename, $pagestore, $version)
10 RetrievePageVersions($dbi, $pagename, $curstore, $archstore)
11 GetMaxVersionNumber($dbi, $pagename, $pagestore)
12 InsertPage($dbi, $pagename, $pagehash, $clobber)
13 ReplaceCurrentPage($pagename, $pagehash)
14 SavePageToArchive($pagename, $pagehash)
15 SelectStore($dbi, $pagename, $version, $curstore, $archstore)
16 IsVersionInWiki($dbi, $pagename, $version)
17 IsVersionInArchive($dbi, $pagename, $version)
18 IsWikiPage($dbi, $pagename)
19 IsInArchive($dbi, $pagename)
20 RemovePage($dbi, $pagename)
21 IncreaseHitCount($dbi, $pagename)
22 GetHitCount($dbi, $pagename)
23 MakeSQLSearchClause($search, $column)
24 InitTitleSearch($dbi, $search)
25 TitleSearchNextMatch($dbi, $res)
26 InitFullSearch($dbi, $search)
27 FullSearchNextMatch($dbi, $res)
28 InitMostPopular($dbi, $limit)
29 MostPopularNextMatch($dbi, $res)
30 GetAllWikiPageNames($dbi)
31 GetWikiPageLinks($dbi, $pagename)
32 SetWikiPageLinks($dbi, $pagename, $linklist)
35 $WikiPageStore = $DBParams['prefix'] . "pages";
36 $ArchivePageStore = $DBParams['prefix'] . "archive";
37 $WikiLinksStore = $DBParams['prefix'] . "links";
38 $WikiScoreStore = $DBParams['prefix'] . "score";
39 $HitCountStore = $DBParams['prefix'] . "hitcount";
41 // open a database and return the handle
42 // ignores MAX_DBM_ATTEMPTS
44 function OpenDataBase($dbname) {
45 extract($GLOBALS['DBParams']);
49 else if (!empty($port))
52 if (!($dbc = mysql_pconnect($server, $user, $password))) {
53 $msg = gettext ("Cannot establish connection to database, giving up.");
55 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
58 if (!mysql_select_db($database, $dbc)) {
59 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $database);
61 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
65 $dbi['table'] = $dbname;
70 function CloseDataBase($dbi) {
72 // mysql connections are established as persistant
73 // they cannot be closed through mysql_close()
77 // prepare $pagehash for storing in mysql
78 function MakeDBHash($pagename, $pagehash)
80 $pagehash["pagename"] = addslashes($pagename);
81 if (!isset($pagehash["flags"]))
82 $pagehash["flags"] = 0;
83 $pagehash["author"] = addslashes($pagehash["author"]);
84 $pagehash["content"] = implode("\n", $pagehash["content"]);
85 $pagehash["content"] = addslashes($pagehash["content"]);
86 if (!isset($pagehash["refs"]))
87 $pagehash["refs"] = array();
88 $pagehash["refs"] = serialize($pagehash["refs"]);
94 // convert mysql result $dbhash to $pagehash
95 function MakePageHash($dbhash)
97 // unserialize/explode content
98 $dbhash['refs'] = unserialize($dbhash['refs']);
99 $dbhash['content'] = explode("\n", $dbhash['content']);
104 // Return hash of page + attributes or default
105 function RetrievePage($dbi, $pagename, $pagestore, $version) {
106 $pagename = addslashes($pagename);
108 $version = $version ? " and version=$version" : '';
110 if ($res = mysql_query("select * from $pagestore where pagename='$pagename'$version", $dbi['dbc'])) {
111 if ($dbhash = mysql_fetch_array($res)) {
112 return MakePageHash($dbhash);
119 // Return all versions of a page as an array of page hashes
120 function RetrievePageVersions($dbi, $pagename, $curstore, $archstore) {
121 $pagename = addslashes($pagename);
122 if (($page[0] = RetrievePage($dbi, $pagename, $curstore, 0)) != -1) {
123 if ($res = mysql_query("select * from $archstore where pagename='$pagename' order by version desc", $dbi['dbc'])) {
124 while ($dbhash = mysql_fetch_array($res)) {
125 array_push($page, MakePageHash($dbhash));
134 // Get maximum version number of a page in pagestore
135 function GetMaxVersionNumber($dbi, $pagename, $pagestore) {
136 $pagename = addslashes($pagename);
137 if ($res = mysql_query("select max(version) from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
138 return mysql_result($res, 0);
144 // Either insert or replace a key/value (a page)
145 function InsertPage($dbi, $pagename, $pagehash, $clobber)
147 $pagehash = MakeDBHash($pagename, $pagehash);
149 $COLUMNS = "author, content, created, flags, " .
150 "lastmodified, pagename, refs, version";
152 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
153 "$pagehash[created], $pagehash[flags], " .
154 "$pagehash[lastmodified], '$pagehash[pagename]', " .
155 "'$pagehash[refs]', $pagehash[version]";
157 // Clobber existing page?
158 $clobber = $clobber ? 'replace' : 'insert';
160 if (!mysql_query("$clobber into $dbi[table] ($COLUMNS) values ($VALUES)",
162 $msg = htmlspecialchars(sprintf(gettext ("Error writing page '%s'"), $pagename));
164 $msg .= htmlspecialchars(sprintf(gettext ("MySQL error: %s"), mysql_error()));
170 // Adds to or replaces a page in the current pagestore
171 function ReplaceCurrentPage($pagename, $pagehash) {
172 global $WikiPageStore;
173 $dbi = OpenDataBase($WikiPageStore);
174 $linklist = ExtractWikiPageLinks($pagehash['content']);
175 SetWikiPageLinks($dbi, $pagename, $linklist);
176 InsertPage($dbi, $pagename, $pagehash, true);
180 // Adds a page to the archive pagestore
181 function SavePageToArchive($pagename, $pagehash) {
182 global $ArchivePageStore;
183 $dbi = OpenDataBase($ArchivePageStore);
184 InsertPage($dbi, $pagename, $pagehash, false);
188 // Returns store where version of page resides
189 function SelectStore($dbi, $pagename, $version, $curstore, $archstore) {
191 if (IsVersionInWiki($dbi, $pagename, $version)) return $curstore;
192 elseif (IsVersionInArchive($dbi, $pagename, $version)) return $archstore;
195 elseif (IsWikiPage($dbi, $pagename)) return $curstore;
200 function IsVersionInWiki($dbi, $pagename, $version) {
201 $pagename = addslashes($pagename);
202 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename' and version='$version'", $dbi['dbc'])) {
203 return mysql_result($res, 0);
208 function IsVersionInArchive($dbi, $pagename, $version) {
209 global $ArchivePageStore;
211 $pagename = addslashes($pagename);
212 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename' and version='$version'", $dbi['dbc'])) {
213 return mysql_result($res, 0);
219 function IsWikiPage($dbi, $pagename) {
220 $pagename = addslashes($pagename);
221 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
222 return(mysql_result($res, 0));
227 function IsInArchive($dbi, $pagename) {
228 global $ArchivePageStore;
230 $pagename = addslashes($pagename);
231 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
232 return(mysql_result($res, 0));
238 function RemovePage($dbi, $pagename) {
239 global $WikiPageStore, $ArchivePageStore;
240 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
242 $pagename = addslashes($pagename);
243 $msg = gettext ("Cannot delete '%s' from table '%s'");
245 $msg .= gettext ("MySQL error: %s");
247 if (!mysql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
248 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysql_error()));
250 if (!mysql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
251 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysql_error()));
253 if (!mysql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
254 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysql_error()));
256 if (!mysql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
257 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysql_error()));
259 if (!mysql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
260 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
264 function IncreaseHitCount($dbi, $pagename)
266 global $HitCountStore;
268 $res = mysql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']);
270 if (!mysql_affected_rows($dbi['dbc'])) {
271 $res = mysql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']);
277 function GetHitCount($dbi, $pagename)
279 global $HitCountStore;
281 $res = mysql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']);
282 if (mysql_num_rows($res))
283 $hits = mysql_result($res, 0);
290 function MakeSQLSearchClause($search, $column)
292 $search = addslashes(preg_replace("/\s+/", " ", $search));
293 $term = strtok($search, ' ');
297 if ($word[0] == '-') {
298 $word = substr($word, 1);
299 $clause .= "not ($column like '%$word%') ";
301 $clause .= "($column like '%$word%') ";
303 if ($term = strtok(' '))
309 // setup for title-search
310 function InitTitleSearch($dbi, $search) {
311 $clause = MakeSQLSearchClause($search, 'pagename');
312 $res = mysql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
318 // iterating through database
319 function TitleSearchNextMatch($dbi, $res) {
320 if($o = mysql_fetch_object($res)) {
329 // setup for full-text search
330 function InitFullSearch($dbi, $search) {
331 $clause = MakeSQLSearchClause($search, 'content');
332 $res = mysql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
337 // iterating through database
338 function FullSearchNextMatch($dbi, $res) {
339 if($hash = mysql_fetch_array($res)) {
340 return MakePageHash($hash);
347 function InitMostPopular($dbi, $limit) {
348 global $HitCountStore;
349 $res = mysql_query("select * from $HitCountStore order by hits desc, pagename limit $limit", $dbi["dbc"]);
354 function MostPopularNextMatch($dbi, $res) {
355 if ($hits = mysql_fetch_array($res))
361 function GetAllWikiPageNames($dbi) {
362 global $WikiPageStore;
363 $res = mysql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
364 $rows = mysql_num_rows($res);
365 for ($i = 0; $i < $rows; $i++) {
366 $pages[$i] = mysql_result($res, $i);
372 ////////////////////////////////////////
373 // functionality for the wikilinks table
375 // takes a page name, returns array of scored incoming and outgoing links
376 function GetWikiPageLinks($dbi, $pagename) {
377 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
380 $pagename = addslashes($pagename);
381 $res = mysql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
382 $rows = mysql_num_rows($res);
383 for ($i = 0; $i < $rows; $i++) {
384 $out = mysql_fetch_array($res);
385 $links['out'][] = array($out['topage'], $out['score']);
388 $res = mysql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
389 $rows = mysql_num_rows($res);
390 for ($i = 0; $i < $rows; $i++) {
391 $out = mysql_fetch_array($res);
392 $links['in'][] = array($out['frompage'], $out['score']);
395 $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");
396 $rows = mysql_num_rows($res);
397 for ($i = 0; $i < $rows; $i++) {
398 $out = mysql_fetch_array($res);
399 $links['popular'][] = array($out['pagename'], $out['hits']);
406 // takes page name, list of links it contains
407 // the $linklist is an array where the keys are the page names
408 function SetWikiPageLinks($dbi, $pagename, $linklist) {
409 global $WikiLinksStore, $WikiScoreStore;
411 $frompage = addslashes($pagename);
413 // first delete the old list of links
414 mysql_query("delete from $WikiLinksStore where frompage='$frompage'",
417 // the page may not have links, return if not
418 if (! count($linklist))
420 // now insert the new list of links
421 while (list($topage, $count) = each($linklist)) {
422 $topage = addslashes($topage);
423 if($topage != $frompage) {
424 mysql_query("insert into $WikiLinksStore (frompage, topage) " .
425 "values ('$frompage', '$topage')", $dbi["dbc"]);
430 mysql_query("delete from $WikiScoreStore", $dbi["dbc"]);
431 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"]);
434 /* more mysql queries:
437 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
443 // c-file-style: "ellemtel"