1 <!-- $Id: pgsql.php,v 1.4.2.2 2001-09-21 21:57:07 wainstead Exp $ -->
9 RetrievePage($dbi, $pagename, $pagestore)
10 InsertPage($dbi, $pagename, $pagehash)
11 SaveCopyToArchive($dbi, $pagename, $pagehash)
12 IsWikiPage($dbi, $pagename)
13 IsInArchive($dbi, $pagename)
14 InitTitleSearch($dbi, $search)
15 TitleSearchNextMatch($dbi, $res)
16 InitFullSearch($dbi, $search)
17 FullSearchNextMatch($dbi, $res)
18 InitBackLinkSearch($dbi, $pagename)
19 BackLinkSearchNextMatch($dbi, &$pos)
20 IncreaseHitCount($dbi, $pagename)
21 GetHitCount($dbi, $pagename)
22 InitMostPopular($dbi, $limit)
23 MostPopularNextMatch($dbi, $res)
24 GetAllWikiPageNames($dbi)
25 GetWikiPageLinks($dbi, $pagename)
26 SetWikiPageLinks($dbi, $pagename, $linklist)
30 // open a database and return a hash
32 function OpenDataBase($table) {
33 global $WikiDataBase, $pg_dbhost, $pg_dbport;
35 $connectstring = $pg_dbhost?"host=$pg_dbhost ":"";
36 $connectstring .= $pg_dbport?"port=$pg_dbport ":"";
37 $connectstring .= $WikiDataBase?"dbname=$WikiDataBase":"";
39 if (!($dbc = pg_pconnect($connectstring))) {
40 echo "Cannot establish connection to database, giving up.";
45 $dbi['table'] = $table;
46 // echo "<p>dbi after open: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>\n";
51 function CloseDataBase($dbi) {
52 // NOOP: we use persistent database connections
56 // Return hash of page + attributes or default
57 function RetrievePage($dbi, $pagename, $pagestore) {
58 $pagename = addslashes($pagename);
59 $query = "select * from $pagestore where pagename='$pagename'";
60 // echo "<p>$query<p>";
61 $res = pg_exec($dbi['dbc'], $query);
63 if (pg_numrows($res)) {
64 if ($array = pg_fetch_array($res, 0)) {
65 while (list($key, $val) = each($array)) {
66 // pg_fetch_array gives us all the values twice,
67 // so we have to manually edit out the indices
68 if (gettype($key) == "integer") {
71 $pagehash[$key] = $val;
74 // unserialize/explode content
75 $pagehash['refs'] = unserialize($pagehash['refs']);
76 $pagehash['content'] = explode("\n", $pagehash['content']);
82 // if we reach this the query failed
87 // Either insert or replace a key/value (a page)
88 function InsertPage($dbi, $pagename, $pagehash) {
89 $pagename = addslashes($pagename);
91 // update the wikilinks table
92 $linklist = ExtractWikiPageLinks($pagehash['content']);
93 SetWikiPageLinks($dbi, $pagename, $linklist);
96 // prepare the content for storage
97 if (!isset($pagehash["pagename"]))
98 $pagehash["pagename"] = $pagename;
99 if (!isset($pagehash["flags"]))
100 $pagehash["flags"] = 0;
101 $pagehash["author"] = addslashes($pagehash["author"]);
102 $pagehash["content"] = implode("\n", $pagehash["content"]);
103 $pagehash["content"] = addslashes($pagehash["content"]);
104 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
105 $pagehash["refs"] = serialize($pagehash["refs"]);
107 // Check for empty variables which can cause a sql error
108 if(empty($pagehash["created"]))
109 $pagehash["created"] = time();
110 if(empty($pagehash["version"]))
111 $pagehash["version"] = 1;
113 // record the time of modification
114 $pagehash["lastmodified"] = time();
117 if (IsWikiPage($dbi, $pagename)) {
119 $PAIRS = "author='$pagehash[author]'," .
120 "content='$pagehash[content]'," .
121 "created=$pagehash[created]," .
122 "flags=$pagehash[flags]," .
123 "lastmodified=$pagehash[lastmodified]," .
124 "pagename='$pagehash[pagename]'," .
125 "refs='$pagehash[refs]'," .
126 "version=$pagehash[version]";
128 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
132 // build up the column names and values for the query
134 $COLUMNS = "author, content, created, flags, " .
135 "lastmodified, pagename, refs, version";
137 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
138 "$pagehash[created], $pagehash[flags], " .
139 "$pagehash[lastmodified], '$pagehash[pagename]', " .
140 "'$pagehash[refs]', $pagehash[version]";
143 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
146 // echo "<p>Query: $query<p>\n";
147 $retval = pg_exec($dbi['dbc'], $query);
148 if ($retval == false)
149 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
154 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
155 global $ArchivePageStore;
156 // echo "<p>save copy called<p>";
158 $pagename = addslashes($pagename);
159 // echo "<p>dbi in SaveCopyToArchive: '$dbi' '$ArchivePageStore' '$dbi[dbc]'<p>";
161 // prepare the content for storage
162 if (!isset($pagehash["pagename"]))
163 $pagehash["pagename"] = $pagename;
164 if (!isset($pagehash["flags"]))
165 $pagehash["flags"] = 0;
166 $pagehash["author"] = addslashes($pagehash["author"]);
167 $pagehash["content"] = implode("\n", $pagehash["content"]);
168 $pagehash["content"] = addslashes($pagehash["content"]);
169 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
170 $pagehash["refs"] = serialize($pagehash["refs"]);
172 if (IsInArchive($dbi, $pagename)) {
174 $PAIRS = "author='$pagehash[author]'," .
175 "content='$pagehash[content]'," .
176 "created=$pagehash[created]," .
177 "flags=$pagehash[flags]," .
178 "lastmodified=$pagehash[lastmodified]," .
179 "pagename='$pagehash[pagename]'," .
180 "refs='$pagehash[refs]'," .
181 "version=$pagehash[version]";
183 $query = "UPDATE $ArchivePageStore SET $PAIRS WHERE pagename='$pagename'";
187 // build up the column names and values for the query
189 $COLUMNS = "author, content, created, flags, " .
190 "lastmodified, pagename, refs, version";
192 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
193 "$pagehash[created], $pagehash[flags], " .
194 "$pagehash[lastmodified], '$pagehash[pagename]', " .
195 "'$pagehash[refs]', $pagehash[version]";
198 $query = "INSERT INTO $ArchivePageStore ($COLUMNS) VALUES($VALUES)";
201 // echo "<p>Query: $query<p>\n";
202 $retval = pg_exec($dbi['dbc'], $query);
203 if ($retval == false)
204 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
210 function IsWikiPage($dbi, $pagename) {
211 global $WikiPageStore;
212 $pagename = addslashes($pagename);
213 $query = "select count(*) from $WikiPageStore " .
214 "where pagename='$pagename'";
215 $res = pg_exec($query);
216 $array = pg_fetch_array($res, 0);
221 function IsInArchive($dbi, $pagename) {
222 global $ArchivePageStore;
223 $pagename = addslashes($pagename);
224 $query = "select count(*) from $ArchivePageStore " .
225 "where pagename='$pagename'";
226 $res = pg_exec($query);
227 $array = pg_fetch_array($res, 0);
232 // setup for title-search
233 function InitTitleSearch($dbi, $search) {
235 global $search_counter;
238 $search = strtolower($search);
239 $search = addslashes($search);
240 $query = "select pagename from $dbi[table] where lower(pagename) " .
241 "like '%$search%' order by pagename";
242 //echo "search query: $query<br>\n";
243 $res = pg_exec($dbi["dbc"], $query);
249 // iterating through database
250 function TitleSearchNextMatch($dbi, $res) {
251 global $search_counter;
252 if($o = @pg_fetch_object($res, $search_counter)) {
261 // setup for full-text search
262 function InitFullSearch($dbi, $search) {
263 global $search_counter;
265 $search = strtolower($search);
266 $search = addslashes($search);
267 $search = addslashes($search);
268 $query = "select pagename,content from $dbi[table] " .
269 "where lower(content) like '%$search%'";
271 $res = pg_exec($dbi["dbc"], $query);
276 // iterating through database
277 function FullSearchNextMatch($dbi, $res) {
278 global $search_counter;
279 if ($hash = @pg_fetch_array($res, $search_counter)) {
281 $page['pagename'] = $hash["pagename"];
282 $page['content'] = explode("\n", $hash["content"]);
291 ////////////////////////
292 // new database features
294 // setup for back-link search
295 function InitBackLinkSearch($dbi, $pagename) {
296 global $WikiLinksPageStore;
298 $topage = addslashes($pagename);
299 $query = "SELECT DISTINCT frompage FROM $WikiLinksPageStore"
300 . " WHERE topage='$topage'"
301 . " ORDER BY frompage";
302 $res['res'] = pg_exec( $dbi["dbc"], $query);
308 // iterating through database
309 function BackLinkSearchNextMatch($dbi, &$res) {
310 if($a = @pg_fetch_row($res['res'], $res['row'])) {
320 function IncreaseHitCount($dbi, $pagename) {
321 global $HitCountPageStore;
322 $query = "update $HitCountPageStore set hits=hits+1 where pagename='$pagename'";
323 $res = pg_exec($dbi['dbc'], $query);
325 if (!pg_cmdtuples($res)) {
326 $query = "insert into $HitCountPageStore (pagename, hits) " .
327 "values ('$pagename', 1)";
328 $res = pg_exec($dbi['dbc'], $query);
334 function GetHitCount($dbi, $pagename) {
335 global $HitCountPageStore;
336 $query = "select hits from $HitCountPageStore where pagename='$pagename'";
337 $res = pg_exec($dbi['dbc'], $query);
338 if (pg_cmdtuples($res)) {
339 $hits = pg_result($res, 0, "hits");
349 function InitMostPopular($dbi, $limit) {
351 global $pg_most_pop_ctr, $HitCountPageStore;
352 $pg_most_pop_ctr = 0;
354 $query = "select * from $HitCountPageStore " .
355 "order by hits desc, pagename limit $limit";
356 $res = pg_exec($dbi['dbc'], $query);
360 function MostPopularNextMatch($dbi, $res) {
362 global $pg_most_pop_ctr;
363 if ($hits = @pg_fetch_array($res, $pg_most_pop_ctr)) {
371 function GetAllWikiPageNames($dbi) {
372 global $WikiPageStore;
373 $res = pg_exec($dbi['dbc'], "select pagename from $WikiPageStore");
374 $rows = pg_numrows($res);
375 for ($i = 0; $i < $rows; $i++) {
376 $pages[$i] = pg_result($res, $i, "pagename");
381 ////////////////////////////////////////
382 // functionality for the wikilinks table
384 // takes a page name, returns array of links
385 function GetWikiPageLinks($dbi, $pagename) {
386 global $WikiLinksPageStore;
387 $pagename = addslashes($pagename);
389 $res = pg_exec("select topage, score from wikilinks, wikiscore where topage=pagename and frompage='$pagename' order by score desc, topage");
390 $rows = pg_numrows($res);
391 for ($i = 0; $i < $rows; $i++) {
392 $out = pg_fetch_array($res, $i);
393 $links['out'][] = array($out['topage'], $out['score']);
396 $res = pg_exec("select frompage, score from wikilinks, wikiscore where frompage=pagename and topage='$pagename' order by score desc, frompage");
397 $rows = pg_numrows($res);
398 for ($i = 0; $i < $rows; $i++) {
399 $out = pg_fetch_array($res, $i);
400 $links['in'][] = array($out['frompage'], $out['score']);
403 $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");
404 $rows = pg_numrows($res);
405 for ($i = 0; $i < $rows; $i++) {
406 $out = pg_fetch_array($res, $i);
407 $links['popular'][] = array($out['pagename'], $out['hits']);
415 // takes page name, list of links it contains
416 // the $linklist is an array where the keys are the page names
418 function SetWikiPageLinks($dbi, $pagename, $linklist) {
419 global $WikiLinksPageStore;
420 $frompage = addslashes($pagename);
422 // first delete the old list of links
423 $query = "delete from $WikiLinksPageStore where frompage='$frompage'";
424 //echo "$query<br>\n";
425 $res = pg_exec($dbi['dbc'], $query);
427 // the page may not have links, return if not
428 if (! count($linklist))
431 // now insert the new list of links
433 while (list($topage, $count) = each($linklist)) {
434 $topage = addslashes($topage);
435 if ($topage != $frompage) {
436 $query = "insert into $WikiLinksPageStore (frompage, topage) " .
437 "values ('$frompage', '$topage')";
438 //echo "$query<br>\n";
439 $res = pg_exec($dbi['dbc'], $query);
443 pg_exec("delete from wikiscore");
444 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");