1 <?php rcs_id('$Id: mysql.php,v 1.12 2001-02-12 01:43:10 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 InitMostPopular($dbi, $limit)
23 MostPopularNextMatch($dbi, $res)
24 GetAllWikiPageNames($dbi)
25 GetWikiPageLinks($dbi, $pagename)
26 SetWikiPageLinks($dbi, $pagename, $linklist)
29 $WikiPageStore = $DBParams['prefix'] . "wiki";
30 $ArchivePageStore = $DBParams['prefix'] . "archive";
31 $WikiLinksStore = $DBParams['prefix'] . "wikilinks";
32 $WikiScoreStore = $DBParams['prefix'] . "wikiscore";
33 $HitCountStore = $DBParams['prefix'] . "hitcount";
35 // open a database and return the handle
36 // ignores MAX_DBM_ATTEMPTS
38 function OpenDataBase($dbname) {
39 extract($GLOBALS['DBParams']);
43 else if (!empty($port))
46 if (!($dbc = mysql_pconnect($server, $user, $password))) {
47 $msg = gettext ("Cannot establish connection to database, giving up.");
49 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
52 if (!mysql_select_db($database, $dbc)) {
53 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $database);
55 $msg .= sprintf(gettext ("MySQL error: %s"), mysql_error());
59 $dbi['table'] = $dbname;
64 function CloseDataBase($dbi) {
66 // mysql connections are established as persistant
67 // they cannot be closed through mysql_close()
71 // prepare $pagehash for storing in mysql
72 function MakeDBHash($pagename, $pagehash)
74 $pagehash["pagename"] = addslashes($pagename);
75 if (!isset($pagehash["flags"]))
76 $pagehash["flags"] = 0;
77 $pagehash["author"] = addslashes($pagehash["author"]);
78 $pagehash["content"] = implode("\n", $pagehash["content"]);
79 $pagehash["content"] = addslashes($pagehash["content"]);
80 if (!isset($pagehash["refs"]))
81 $pagehash["refs"] = array();
82 $pagehash["refs"] = serialize($pagehash["refs"]);
88 // convert mysql result $dbhash to $pagehash
89 function MakePageHash($dbhash)
91 // unserialize/explode content
92 $dbhash['refs'] = unserialize($dbhash['refs']);
93 $dbhash['content'] = explode("\n", $dbhash['content']);
98 // Return hash of page + attributes or default
99 function RetrievePage($dbi, $pagename, $pagestore) {
100 $pagename = addslashes($pagename);
101 if ($res = mysql_query("select * from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
102 if ($dbhash = mysql_fetch_array($res)) {
103 return MakePageHash($dbhash);
110 // Either insert or replace a key/value (a page)
111 function InsertPage($dbi, $pagename, $pagehash)
113 global $WikiPageStore; // ugly hack
115 if ($dbi['table'] == $WikiPageStore) { // HACK
116 $linklist = ExtractWikiPageLinks($pagehash['content']);
117 SetWikiPageLinks($dbi, $pagename, $linklist);
120 $pagehash = MakeDBHash($pagename, $pagehash);
122 $COLUMNS = "author, content, created, flags, " .
123 "lastmodified, pagename, refs, version";
125 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
126 "$pagehash[created], $pagehash[flags], " .
127 "$pagehash[lastmodified], '$pagehash[pagename]', " .
128 "'$pagehash[refs]', $pagehash[version]";
130 if (!mysql_query("replace into $dbi[table] ($COLUMNS) values ($VALUES)",
132 $msg = htmlspecialchars(sprintf(gettext ("Error writing page '%s'"), $pagename));
134 $msg .= htmlspecialchars(sprintf(gettext ("MySQL error: %s"), mysql_error()));
140 // for archiving pages to a seperate dbm
141 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
142 global $ArchivePageStore;
143 $adbi = OpenDataBase($ArchivePageStore);
144 InsertPage($adbi, $pagename, $pagehash);
148 function IsWikiPage($dbi, $pagename) {
149 $pagename = addslashes($pagename);
150 if ($res = mysql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
151 return(mysql_result($res, 0));
156 function IsInArchive($dbi, $pagename) {
157 global $ArchivePageStore;
159 $pagename = addslashes($pagename);
160 if ($res = mysql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
161 return(mysql_result($res, 0));
167 function RemovePage($dbi, $pagename) {
168 global $WikiPageStore, $ArchivePageStore;
169 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
171 $pagename = addslashes($pagename);
172 $msg = gettext ("Cannot delete '%s' from table '%s'");
174 $msg .= gettext ("MySQL error: %s");
176 if (!mysql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
177 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mysql_error()));
179 if (!mysql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
180 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mysql_error()));
182 if (!mysql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
183 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mysql_error()));
185 if (!mysql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
186 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mysql_error()));
188 if (!mysql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
189 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
193 function IncreaseHitCount($dbi, $pagename)
195 global $HitCountStore;
197 $res = mysql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']);
199 if (!mysql_affected_rows($dbi['dbc'])) {
200 $res = mysql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']);
206 function GetHitCount($dbi, $pagename)
208 global $HitCountStore;
210 $res = mysql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']);
211 if (mysql_num_rows($res))
212 $hits = mysql_result($res, 0);
219 function MakeSQLSearchClause($search, $column)
221 $search = addslashes(preg_replace("/\s+/", " ", $search));
222 $term = strtok($search, ' ');
226 if ($word[0] == '-') {
227 $word = substr($word, 1);
228 $clause .= "not ($column like '%$word%') ";
230 $clause .= "($column like '%$word%') ";
232 if ($term = strtok(' '))
238 // setup for title-search
239 function InitTitleSearch($dbi, $search) {
240 $clause = MakeSQLSearchClause($search, 'pagename');
241 $res = mysql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
247 // iterating through database
248 function TitleSearchNextMatch($dbi, $res) {
249 if($o = mysql_fetch_object($res)) {
258 // setup for full-text search
259 function InitFullSearch($dbi, $search) {
260 $clause = MakeSQLSearchClause($search, 'content');
261 $res = mysql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
266 // iterating through database
267 function FullSearchNextMatch($dbi, $res) {
268 if($hash = mysql_fetch_array($res)) {
269 return MakePageHash($hash);
276 function InitMostPopular($dbi, $limit) {
277 global $HitCountStore;
278 $res = mysql_query("select * from $HitCountStore order by hits desc, pagename limit $limit", $dbi["dbc"]);
283 function MostPopularNextMatch($dbi, $res) {
284 if ($hits = mysql_fetch_array($res))
290 function GetAllWikiPageNames($dbi) {
291 global $WikiPageStore;
292 $res = mysql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
293 $rows = mysql_num_rows($res);
294 for ($i = 0; $i < $rows; $i++) {
295 $pages[$i] = mysql_result($res, $i);
301 ////////////////////////////////////////
302 // functionality for the wikilinks table
304 // takes a page name, returns array of scored incoming and outgoing links
305 function GetWikiPageLinks($dbi, $pagename) {
306 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
309 $pagename = addslashes($pagename);
310 $res = mysql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
311 $rows = mysql_num_rows($res);
312 for ($i = 0; $i < $rows; $i++) {
313 $out = mysql_fetch_array($res);
314 $links['out'][] = array($out['topage'], $out['score']);
317 $res = mysql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
318 $rows = mysql_num_rows($res);
319 for ($i = 0; $i < $rows; $i++) {
320 $out = mysql_fetch_array($res);
321 $links['in'][] = array($out['frompage'], $out['score']);
324 $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");
325 $rows = mysql_num_rows($res);
326 for ($i = 0; $i < $rows; $i++) {
327 $out = mysql_fetch_array($res);
328 $links['popular'][] = array($out['pagename'], $out['hits']);
335 // takes page name, list of links it contains
336 // the $linklist is an array where the keys are the page names
337 function SetWikiPageLinks($dbi, $pagename, $linklist) {
338 global $WikiLinksStore, $WikiScoreStore;
340 $frompage = addslashes($pagename);
342 // first delete the old list of links
343 mysql_query("delete from $WikiLinksStore where frompage='$frompage'",
346 // the page may not have links, return if not
347 if (! count($linklist))
349 // now insert the new list of links
350 while (list($topage, $count) = each($linklist)) {
351 $topage = addslashes($topage);
352 if($topage != $frompage) {
353 mysql_query("insert into $WikiLinksStore (frompage, topage) " .
354 "values ('$frompage', '$topage')", $dbi["dbc"]);
359 mysql_query("delete from $WikiScoreStore", $dbi["dbc"]);
360 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"]);
363 /* more mysql queries:
366 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
372 // c-file-style: "ellemtel"