1 <?php rcs_id('$Id: mysql.php,v 1.16 2001-07-18 04:56:14 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);
116 // if we reach this the query failed
121 // Return all versions of a page as an array of page hashes
122 function RetrievePageVersions($dbi, $pagename, $curstore, $archstore) {
123 $pagename = addslashes($pagename);
124 if (($page[0] = RetrievePage($dbi, $pagename, $curstore, 0)) != -1) {
125 if ($res = mysql_query("select * from $archstore where pagename='$pagename' order by version desc", $dbi['dbc'])) {
126 while ($dbhash = mysql_fetch_array($res)) {
127 array_push($page, MakePageHash($dbhash));
136 // Get maximum version number of a page in pagestore
137 function GetMaxVersionNumber($dbi, $pagename, $pagestore) {
138 $pagename = addslashes($pagename);
139 if ($res = mysql_query("select max(version) from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
140 return mysql_result($res, 0);
146 // Either insert or replace a key/value (a page)
147 function InsertPage($dbi, $pagename, $pagehash, $clobber)
149 $pagehash = MakeDBHash($pagename, $pagehash);
151 $COLUMNS = "author, content, created, flags, " .
152 "lastmodified, pagename, refs, version";
154 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
155 "$pagehash[created], $pagehash[flags], " .
156 "$pagehash[lastmodified], '$pagehash[pagename]', " .
157 "'$pagehash[refs]', $pagehash[version]";
159 // Clobber existing page?
160 $clobber = $clobber ? 'replace' : 'insert';
162 if (!mysql_query("$clobber into $dbi[table] ($COLUMNS) values ($VALUES)",
164 $msg = htmlspecialchars(sprintf(gettext ("Error writing page '%s'"), $pagename));
166 $msg .= htmlspecialchars(sprintf(gettext ("MySQL error: %s"), mysql_error()));
172 // Adds to or replaces a page in the current pagestore
173 function ReplaceCurrentPage($pagename, $pagehash) {
174 global $WikiPageStore;
175 $dbi = OpenDataBase($WikiPageStore);
176 $linklist = ExtractWikiPageLinks($pagehash['content']);
177 SetWikiPageLinks($dbi, $pagename, $linklist);
178 InsertPage($dbi, $pagename, $pagehash, true);
182 // Adds a page to the archive pagestore
183 function SavePageToArchive($pagename, $pagehash) {
184 global $ArchivePageStore;
185 $dbi = OpenDataBase($ArchivePageStore);
186 InsertPage($dbi, $pagename, $pagehash, false);
190 // Returns store where version of page resides
191 function SelectStore($dbi, $pagename, $version, $curstore, $archstore) {
193 if (IsVersionInWiki($dbi, $pagename, $version)) return $curstore;
194 elseif (IsVersionInArchive($dbi, $pagename, $version)) return $archstore;
197 elseif (IsWikiPage($dbi, $pagename)) return $curstore;
202 function IsVersionInWiki($dbi, $pagename, $version) {
203 $pagename = addslashes($pagename);
204 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename' and version='$version'", $dbi['dbc'])) {
205 return mysql_result($res, 0);
210 function IsVersionInArchive($dbi, $pagename, $version) {
211 global $ArchivePageStore;
213 $pagename = addslashes($pagename);
214 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename' and version='$version'", $dbi['dbc'])) {
215 return mysql_result($res, 0);
221 function IsWikiPage($dbi, $pagename) {
222 $pagename = addslashes($pagename);
223 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
224 return mysql_result($res, 0);
229 function IsInArchive($dbi, $pagename) {
230 global $ArchivePageStore;
232 $pagename = addslashes($pagename);
233 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
234 return mysql_result($res, 0);
240 function RemovePage($dbi, $pagename) {
241 global $WikiPageStore, $ArchivePageStore;
242 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
244 $pagename = addslashes($pagename);
245 $msg = gettext ("Cannot delete '%s' from table '%s'");
247 $msg .= gettext ("MySQL error: %s");
249 if (!mysql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
250 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysql_error()));
252 if (!mysql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
253 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysql_error()));
255 if (!mysql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
256 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysql_error()));
258 if (!mysql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
259 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysql_error()));
261 if (!mysql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
262 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
266 function IncreaseHitCount($dbi, $pagename)
268 global $HitCountStore;
270 $res = mysql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']);
272 if (!mysql_affected_rows($dbi['dbc'])) {
273 $res = mysql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']);
279 function GetHitCount($dbi, $pagename)
281 global $HitCountStore;
283 $res = mysql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']);
284 if (mysql_num_rows($res))
285 $hits = mysql_result($res, 0);
292 function MakeSQLSearchClause($search, $column)
294 $search = addslashes(preg_replace("/\s+/", " ", $search));
295 $term = strtok($search, ' ');
299 if ($word[0] == '-') {
300 $word = substr($word, 1);
301 $clause .= "not ($column like '%$word%') ";
303 $clause .= "($column like '%$word%') ";
305 if ($term = strtok(' '))
311 // setup for title-search
312 function InitTitleSearch($dbi, $search) {
313 $clause = MakeSQLSearchClause($search, 'pagename');
314 $res = mysql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
320 // iterating through database
321 function TitleSearchNextMatch($dbi, $res) {
322 if($o = mysql_fetch_object($res)) {
331 // setup for full-text search
332 function InitFullSearch($dbi, $search) {
333 $clause = MakeSQLSearchClause($search, 'content');
334 $res = mysql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
339 // iterating through database
340 function FullSearchNextMatch($dbi, $res) {
341 if($hash = mysql_fetch_array($res)) {
342 return MakePageHash($hash);
349 function InitMostPopular($dbi, $limit) {
350 global $HitCountStore;
351 $res = mysql_query("select * from $HitCountStore order by hits desc, pagename limit $limit", $dbi["dbc"]);
356 function MostPopularNextMatch($dbi, $res) {
357 if ($hits = mysql_fetch_array($res))
363 function GetAllWikiPageNames($dbi) {
364 global $WikiPageStore;
365 $res = mysql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
366 $rows = mysql_num_rows($res);
367 for ($i = 0; $i < $rows; $i++) {
368 $pages[$i] = mysql_result($res, $i);
374 ////////////////////////////////////////
375 // functionality for the wikilinks table
377 // takes a page name, returns array of scored incoming and outgoing links
378 function GetWikiPageLinks($dbi, $pagename) {
379 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
382 $pagename = addslashes($pagename);
383 $res = mysql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
384 $rows = mysql_num_rows($res);
385 for ($i = 0; $i < $rows; $i++) {
386 $out = mysql_fetch_array($res);
387 $links['out'][] = array($out['topage'], $out['score']);
390 $res = mysql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
391 $rows = mysql_num_rows($res);
392 for ($i = 0; $i < $rows; $i++) {
393 $out = mysql_fetch_array($res);
394 $links['in'][] = array($out['frompage'], $out['score']);
397 $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");
398 $rows = mysql_num_rows($res);
399 for ($i = 0; $i < $rows; $i++) {
400 $out = mysql_fetch_array($res);
401 $links['popular'][] = array($out['pagename'], $out['hits']);
408 // takes page name, list of links it contains
409 // the $linklist is an array where the keys are the page names
410 function SetWikiPageLinks($dbi, $pagename, $linklist) {
411 global $WikiLinksStore, $WikiScoreStore;
413 $frompage = addslashes($pagename);
415 // first delete the old list of links
416 mysql_query("delete from $WikiLinksStore where frompage='$frompage'",
419 // the page may not have links, return if not
420 if (! count($linklist))
422 // now insert the new list of links
423 while (list($topage, $count) = each($linklist)) {
424 $topage = addslashes($topage);
425 if($topage != $frompage) {
426 mysql_query("insert into $WikiLinksStore (frompage, topage) " .
427 "values ('$frompage', '$topage')", $dbi["dbc"]);
432 mysql_query("delete from $WikiScoreStore", $dbi["dbc"]);
433 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"]);
436 /* more mysql queries:
439 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
445 // c-file-style: "ellemtel"