2 rcs_id('$Id: pgsql.php,v 1.8 2001-07-18 04:59:47 uckelman Exp $');
9 RetrievePage($dbi, $pagename, $pagestore, $version)
10 RetrievePageVersions($dbi, $pagename, $curstore, $archstore)
11 GetMaxVersionNumber($dbi, $pagename, $pagestore)
12 InsertPage($dbi, $pagename, $pagehash, $clobber)
13 SelectStore($dbi, $pagename, $version, $curstore, $archstore)
14 IsVersionInWiki($dbi, $pagename, $version)
15 IsVersionInArchive($dbi, $pagename, $version)
16 IsWikiPage($dbi, $pagename)
17 IsInArchive($dbi, $pagename)
18 RemovePage($dbi, $pagename)
19 InitTitleSearch($dbi, $search)
20 TitleSearchNextMatch($dbi, $res)
21 InitFullSearch($dbi, $search)
22 FullSearchNextMatch($dbi, $res)
23 IncreaseHitCount($dbi, $pagename)
24 GetHitCount($dbi, $pagename)
25 InitMostPopular($dbi, $limit)
26 MostPopularNextMatch($dbi, $res)
27 GetAllWikiPageNames($dbi)
28 GetWikiPageLinks($dbi, $pagename)
29 SetWikiPageLinks($dbi, $pagename, $linklist)
32 $WikiPageStore = $DBParams['prefix'] . "pages";
33 $ArchivePageStore = $DBParams['prefix'] . "archive";
34 $WikiLinksPageStore = $DBParams['prefix'] . "links";
35 $HotTopicsPageStore = $DBParams['prefix'] . "hottopics";
36 $HitCountPageStore = $DBParams['prefix'] . "hitcount";
38 // open a database and return a hash
40 function OpenDataBase($table) {
41 extract($GLOBALS['DBParams']);
45 $args[] = "host=$server";
47 $args[] = "port=$port";
48 if (!empty($database))
49 $args[] = "dbname=$database";
51 $args[] = "user=$user";
52 if (!empty($password))
53 $args[] = "password=$password";
55 if (!($dbc = pg_pconnect(join(' ', $args)))) {
56 ExitWiki("Cannot establish connection to database, giving up.");
60 $dbi['table'] = $table;
61 // echo "<p>dbi after open: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>\n";
66 function CloseDataBase($dbi) {
67 // NOOP: we use persistent database connections
71 // Return hash of page + attributes or default
72 function RetrievePage($dbi, $pagename, $pagestore, $version) {
73 $pagename = addslashes($pagename);
74 $version = $version ? " and version=$version" : '';
75 $query = "select * from $pagestore where pagename='$pagename'$version";
76 // echo "<p>$query<p>";
77 $res = pg_exec($dbi['dbc'], $query);
79 if (pg_numrows($res)) {
80 if ($array = pg_fetch_array($res, 0)) {
81 while (list($key, $val) = each($array)) {
82 // pg_fetch_array gives us all the values twice,
83 // so we have to manually edit out the indices
84 if (gettype($key) == "integer") {
87 $pagehash[$key] = $val;
90 // unserialize/explode content
91 $pagehash['refs'] = unserialize($pagehash['refs']);
92 $pagehash['content'] = explode("\n", $pagehash['content']);
98 // if we reach this the query failed
103 // Return all versions of a page as an array of page hashes
104 function RetrievePageVersions($dbi, $pagename, $curstore, $archstore) {
105 $pagename = addslashes($pagename);
106 if (($page[0] = RetrievePage($dbi, $pagename, $curstore, 0)) != -1) {
107 $res = pg_exec($dbi['dbc'], "select * from $archstore where pagename='$pagename' order by version desc");
108 if (pg_numrows($res)) {
109 while ($array = pg_fetch_array($res, 0)) {
110 while (list($key, $val) = each($array)) {
111 if (gettype($key) == "integer") {
114 $dbhash[$key] = $val;
117 $dbhash['refs'] = unserialize($dbhash['refs']);
118 $dbhash['content'] = explode("\n", $dbhash['content']);
120 array_push($page, $dbhash);
127 // if we reach this the query failed
132 // Get maximum version number of a page in pagestore
133 function GetMaxVersionNumber($dbi, $pagename, $pagestore) {
134 $pagename = addslashes($pagename);
135 if ($res = pg_exec($dbi['dbc'], "select max(version) from $pagestore where pagename='$pagename'")) {
136 return pg_result($res, 0, "version");
142 // Either insert or replace a key/value (a page)
143 function InsertPage($dbi, $pagename, $pagehash, $clobber) {
144 $pagename = addslashes($pagename);
146 // update the wikilinks table
147 $linklist = ExtractWikiPageLinks($pagehash['content']);
148 SetWikiPageLinks($dbi, $pagename, $linklist);
151 // prepare the content for storage
152 if (!isset($pagehash["pagename"]))
153 $pagehash["pagename"] = $pagename;
154 if (!isset($pagehash["flags"]))
155 $pagehash["flags"] = 0;
156 $pagehash["author"] = addslashes($pagehash["author"]);
157 $pagehash["content"] = implode("\n", $pagehash["content"]);
158 $pagehash["content"] = addslashes($pagehash["content"]);
159 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
160 $pagehash["refs"] = serialize($pagehash["refs"]);
162 // Check for empty variables which can cause a sql error
163 if(empty($pagehash["created"]))
164 $pagehash["created"] = time();
165 if(empty($pagehash["version"]))
166 $pagehash["version"] = 1;
168 // record the time of modification
169 $pagehash["lastmodified"] = time();
171 // Clobber existing page?
172 $clobber = $clobber ? 'replace' : 'insert';
174 $COLUMNS = "author, content, created, flags, " .
175 "lastmodified, pagename, refs, version";
177 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
178 "$pagehash[created], $pagehash[flags], " .
179 "$pagehash[lastmodified], '$pagehash[pagename]', " .
180 "'$pagehash[refs]', $pagehash[version]";
182 if (!pg_exec($dbi['dbc'], "$clobber into $dbi[table] ($COLUMNS) values ($VALUES)")) {
183 $msg = htmlspecialchars(sprintf(gettext("Error writing page '%s'"), $pagename));
185 $msg .= htmlspecialchars(sprintf(gettext("PostgreSQL error: %s"), pg_errormessage($dbi['dbc'])));
191 // Adds a page to the archive pagestore
192 function SavePageToArchive($pagename, $pagehash) {
193 global $ArchivePageStore;
194 $dbi = OpenDataBase($ArchivePageStore);
195 InsertPage($dbi, $pagename, $pagehash, false);
199 // Returns store where version of page resides
200 function SelectStore($dbi, $pagename, $version, $curstore, $archstore) {
202 if (IsVersionInWiki($dbi, $pagename, $version)) return $curstore;
203 elseif (IsVersionInArchive($dbi, $pagename, $version)) return $archstore;
206 elseif (IsWikiPage($dbi, $pagename)) return $curstore;
211 function IsVersionInWiki($dbi, $pagename, $version) {
212 $pagename = addslashes($pagename);
213 if ($res = pg_exec($dbi['dbc'], "select count(*) from $dbi[table] where pagename='$pagename' and version='$version'")) {
214 return pg_result($res, 0, "count");
220 function IsVersionInArchive($dbi, $pagename, $version) {
221 global $ArchivePageStore;
223 $pagename = addslashes($pagename);
224 if ($res = pg_exec($dbi['dbc'], "select count(*) from $ArchivePageStore where pagename='$pagename' and version='$version'")) {
225 return pg_result($res, 0, "count");
231 function IsWikiPage($dbi, $pagename) {
232 $pagename = addslashes($pagename);
233 if ($res = pg_exec($dbi['dbc'], "select count(*) from $dbi[table] where pagename='$pagename'")) {
234 return pg_result($res, 0, "count");
240 function IsInArchive($dbi, $pagename) {
241 global $ArchivePageStore;
243 $pagename = addslashes($pagename);
244 if ($res = pg_exec($dbi['dbc'], "select count(*) from $ArchivePageStore where pagename='$pagename'")) {
245 return pg_result($res, 0, "count");
251 function RemovePage($dbi, $pagename) {
252 global $WikiPageStore, $ArchivePageStore;
253 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
255 $pagename = addslashes($pagename);
256 $msg = gettext ("Cannot delete '%s' from table '%s'");
258 $msg .= gettext ("PostgreSQL error: %s");
260 if (!pg_exec($dbi['dbc'], "delete from $WikiPageStore where pagename='$pagename'"))
261 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, pg_errormessage()));
263 if (!pg_exec($dbi['dbc'], "delete from $ArchivePageStore where pagename='$pagename'"))
264 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, pg_errormessage()));
266 if (!pg_exec($dbi['dbc'], "delete from $WikiLinksStore where frompage='$pagename'"))
267 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, pg_errormessage()));
269 if (!pg_exec($dbi['dbc'], "delete from $HitCountStore where pagename='$pagename'"))
270 ExitWiki(sprintf($msg, $pagename, $HitCountStore, pg_errormessage()));
272 if (!pg_exec($dbi['dbc'], "delete from $WikiScoreStore where pagename='$pagename'"))
273 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mysql_error()));
277 // setup for title-search
278 function InitTitleSearch($dbi, $search) {
280 global $search_counter;
283 $search = strtolower($search);
284 $search = addslashes($search);
285 $query = "select pagename from $dbi[table] where lower(pagename) " .
286 "like '%$search%' order by pagename";
287 //echo "search query: $query<br>\n";
288 $res = pg_exec($dbi["dbc"], $query);
294 // iterating through database
295 function TitleSearchNextMatch($dbi, $res) {
296 global $search_counter;
297 if($o = @pg_fetch_object($res, $search_counter)) {
306 // setup for full-text search
307 function InitFullSearch($dbi, $search) {
308 global $search_counter;
310 $search = strtolower($search);
311 $search = addslashes($search);
312 $search = addslashes($search);
313 $query = "select pagename,content from $dbi[table] " .
314 "where lower(content) like '%$search%'";
316 $res = pg_exec($dbi["dbc"], $query);
321 // iterating through database
322 function FullSearchNextMatch($dbi, $res) {
323 global $search_counter;
324 if ($hash = @pg_fetch_array($res, $search_counter)) {
326 $page['pagename'] = $hash["pagename"];
327 $page['content'] = explode("\n", $hash["content"]);
336 ////////////////////////
337 // new database features
340 function IncreaseHitCount($dbi, $pagename) {
341 global $HitCountPageStore;
342 $query = "update $HitCountPageStore set hits=hits+1 where pagename='$pagename'";
343 $res = pg_exec($dbi['dbc'], $query);
345 if (!pg_cmdtuples($res)) {
346 $query = "insert into $HitCountPageStore (pagename, hits) " .
347 "values ('$pagename', 1)";
348 $res = pg_exec($dbi['dbc'], $query);
354 function GetHitCount($dbi, $pagename) {
355 global $HitCountPageStore;
356 $query = "select hits from $HitCountPageStore where pagename='$pagename'";
357 $res = pg_exec($dbi['dbc'], $query);
358 if (pg_cmdtuples($res)) {
359 $hits = pg_result($res, 0, "hits");
369 function InitMostPopular($dbi, $limit) {
371 global $pg_most_pop_ctr, $HitCountPageStore;
372 $pg_most_pop_ctr = 0;
374 $query = "select * from $HitCountPageStore " .
375 "order by hits desc, pagename limit $limit";
376 $res = pg_exec($dbi['dbc'], $query);
380 function MostPopularNextMatch($dbi, $res) {
382 global $pg_most_pop_ctr;
383 if ($hits = @pg_fetch_array($res, $pg_most_pop_ctr)) {
391 function GetAllWikiPageNames($dbi) {
392 global $WikiPageStore;
393 $res = pg_exec($dbi['dbc'], "select pagename from $WikiPageStore");
394 $rows = pg_numrows($res);
395 for ($i = 0; $i < $rows; $i++) {
396 $pages[$i] = pg_result($res, $i, "pagename");
401 ////////////////////////////////////////
402 // functionality for the wikilinks table
404 // takes a page name, returns array of links
405 function GetWikiPageLinks($dbi, $pagename) {
406 global $WikiLinksPageStore;
407 $pagename = addslashes($pagename);
409 $res = pg_exec("select topage, score from wikilinks, wikiscore where topage=pagename and frompage='$pagename' order by score desc, topage");
410 $rows = pg_numrows($res);
411 for ($i = 0; $i < $rows; $i++) {
412 $out = pg_fetch_array($res, $i);
413 $links['out'][] = array($out['topage'], $out['score']);
416 $res = pg_exec("select frompage, score from wikilinks, wikiscore where frompage=pagename and topage='$pagename' order by score desc, frompage");
417 $rows = pg_numrows($res);
418 for ($i = 0; $i < $rows; $i++) {
419 $out = pg_fetch_array($res, $i);
420 $links['in'][] = array($out['frompage'], $out['score']);
423 $res = pg_exec("select distinct pagename, hits from wikilinks, hitcount where (frompage=pagename and topage='$pagename') or (topage=pagename and frompage='$pagename') order by hits desc, pagename");
424 $rows = pg_numrows($res);
425 for ($i = 0; $i < $rows; $i++) {
426 $out = pg_fetch_array($res, $i);
427 $links['popular'][] = array($out['pagename'], $out['hits']);
435 // takes page name, list of links it contains
436 // the $linklist is an array where the keys are the page names
438 function SetWikiPageLinks($dbi, $pagename, $linklist) {
439 global $WikiLinksPageStore;
440 $frompage = addslashes($pagename);
442 // first delete the old list of links
443 $query = "delete from $WikiLinksPageStore where frompage='$frompage'";
444 //echo "$query<br>\n";
445 $res = pg_exec($dbi['dbc'], $query);
447 // the page may not have links, return if not
448 if (! count($linklist))
451 // now insert the new list of links
453 while (list($topage, $count) = each($linklist)) {
454 $topage = addslashes($topage);
455 if ($topage != $frompage) {
456 $query = "insert into $WikiLinksPageStore (frompage, topage) " .
457 "values ('$frompage', '$topage')";
458 //echo "$query<br>\n";
459 $res = pg_exec($dbi['dbc'], $query);
463 pg_exec("delete from wikiscore");
464 pg_exec("insert into wikiscore select w1.topage, count(*) from wikilinks as w1, wikilinks as w2 where w2.topage=w1.frompage group by w1.topage");
471 // c-file-style: "ellemtel"
473 // indent-tabs-mode: nil