1 <?php rcs_id('$Id: pgsql.php,v 1.4.2.6 2001-11-15 01:20:39 dairiki Exp $');
8 RetrievePage($dbi, $pagename, $pagestore)
9 InsertPage($dbi, $pagename, $pagehash)
10 SaveCopyToArchive($dbi, $pagename, $pagehash)
11 IsWikiPage($dbi, $pagename)
12 IsInArchive($dbi, $pagename)
13 InitTitleSearch($dbi, $search)
14 TitleSearchNextMatch($dbi, $res)
15 InitFullSearch($dbi, $search)
16 FullSearchNextMatch($dbi, $res)
17 InitBackLinkSearch($dbi, $pagename)
18 BackLinkSearchNextMatch($dbi, &$pos)
19 IncreaseHitCount($dbi, $pagename)
20 GetHitCount($dbi, $pagename)
21 InitMostPopular($dbi, $limit)
22 MostPopularNextMatch($dbi, $res)
23 GetAllWikiPageNames($dbi)
24 GetWikiPageLinks($dbi, $pagename)
25 SetWikiPageLinks($dbi, $pagename, $linklist)
29 // open a database and return a hash
31 function OpenDataBase($table) {
32 global $WikiDataBase, $pg_dbhost, $pg_dbport;
34 $connectstring = $pg_dbhost?"host=$pg_dbhost ":"";
35 $connectstring .= $pg_dbport?"port=$pg_dbport ":"";
36 $connectstring .= $WikiDataBase?"dbname=$WikiDataBase":"";
38 if (!($dbc = pg_pconnect($connectstring))) {
39 echo "Cannot establish connection to database, giving up.";
44 $dbi['table'] = $table;
45 // echo "<p>dbi after open: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>\n";
50 function CloseDataBase($dbi) {
51 // NOOP: we use persistent database connections
55 // Return hash of page + attributes or default
56 function RetrievePage($dbi, $pagename, $pagestore) {
57 $pagename = addslashes($pagename);
58 $query = "select * from $pagestore where pagename='$pagename'";
59 // echo "<p>$query<p>";
60 $res = pg_exec($dbi['dbc'], $query);
62 if (pg_numrows($res)) {
63 if ($array = pg_fetch_array($res, 0)) {
64 while (list($key, $val) = each($array)) {
65 // pg_fetch_array gives us all the values twice,
66 // so we have to manually edit out the indices
67 if (gettype($key) == "integer") {
70 $pagehash[$key] = $val;
73 // unserialize/explode content
74 $pagehash['refs'] = unserialize($pagehash['refs']);
75 $pagehash['content'] = explode("\n", $pagehash['content']);
81 // if we reach this the query failed
86 // Either insert or replace a key/value (a page)
87 function InsertPage($dbi, $pagename, $pagehash) {
88 // update the wikilinks table
89 $linklist = ExtractWikiPageLinks($pagehash['content']);
90 SetWikiPageLinks($dbi, $pagename, $linklist);
93 // prepare the content for storage
94 if (!isset($pagehash["pagename"]))
95 $pagehash["pagename"] = $pagename;
96 if (!isset($pagehash["flags"]))
97 $pagehash["flags"] = 0;
98 $pagehash["author"] = addslashes($pagehash["author"]);
99 $pagehash["content"] = implode("\n", $pagehash["content"]);
100 $pagehash["content"] = addslashes($pagehash["content"]);
101 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
102 $pagehash["refs"] = serialize($pagehash["refs"]);
104 // Check for empty variables which can cause a sql error
105 if(empty($pagehash["created"]))
106 $pagehash["created"] = time();
107 if(empty($pagehash["version"]))
108 $pagehash["version"] = 1;
110 // record the time of modification
111 $pagehash["lastmodified"] = time();
114 if (IsWikiPage($dbi, $pagename)) {
116 $PAIRS = "author='$pagehash[author]'," .
117 "content='$pagehash[content]'," .
118 "created=$pagehash[created]," .
119 "flags=$pagehash[flags]," .
120 "lastmodified=$pagehash[lastmodified]," .
121 "pagename='$pagehash[pagename]'," .
122 "refs='$pagehash[refs]'," .
123 "version=$pagehash[version]";
125 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
129 // build up the column names and values for the query
131 $COLUMNS = "author, content, created, flags, " .
132 "lastmodified, pagename, refs, version";
134 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
135 "$pagehash[created], $pagehash[flags], " .
136 "$pagehash[lastmodified], '$pagehash[pagename]', " .
137 "'$pagehash[refs]', $pagehash[version]";
140 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
143 // echo "<p>Query: $query<p>\n";
144 $retval = pg_exec($dbi['dbc'], $query);
145 if ($retval == false)
146 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
151 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
152 global $ArchivePageStore;
153 // echo "<p>save copy called<p>";
155 // echo "<p>dbi in SaveCopyToArchive: '$dbi' '$ArchivePageStore' '$dbi[dbc]'<p>";
157 // prepare the content for storage
158 if (!isset($pagehash["pagename"]))
159 $pagehash["pagename"] = $pagename;
160 if (!isset($pagehash["flags"]))
161 $pagehash["flags"] = 0;
162 $pagehash["author"] = addslashes($pagehash["author"]);
163 $pagehash["content"] = implode("\n", $pagehash["content"]);
164 $pagehash["content"] = addslashes($pagehash["content"]);
165 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
166 $pagehash["refs"] = serialize($pagehash["refs"]);
168 if (IsInArchive($dbi, $pagename)) {
170 $PAIRS = "author='$pagehash[author]'," .
171 "content='$pagehash[content]'," .
172 "created=$pagehash[created]," .
173 "flags=$pagehash[flags]," .
174 "lastmodified=$pagehash[lastmodified]," .
175 "pagename='$pagehash[pagename]'," .
176 "refs='$pagehash[refs]'," .
177 "version=$pagehash[version]";
179 $query = "UPDATE $ArchivePageStore SET $PAIRS WHERE pagename='$pagehash[pagename]'";
183 // build up the column names and values for the query
185 $COLUMNS = "author, content, created, flags, " .
186 "lastmodified, pagename, refs, version";
188 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
189 "$pagehash[created], $pagehash[flags], " .
190 "$pagehash[lastmodified], '$pagehash[pagename]', " .
191 "'$pagehash[refs]', $pagehash[version]";
194 $query = "INSERT INTO $ArchivePageStore ($COLUMNS) VALUES($VALUES)";
197 // echo "<p>Query: $query<p>\n";
198 $retval = pg_exec($dbi['dbc'], $query);
199 if ($retval == false)
200 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
206 function IsWikiPage($dbi, $pagename) {
207 global $WikiPageStore;
208 $pagename = addslashes($pagename);
209 $query = "select count(*) from $WikiPageStore " .
210 "where pagename='$pagename'";
211 $res = pg_exec($query);
212 $array = pg_fetch_array($res, 0);
217 function IsInArchive($dbi, $pagename) {
218 global $ArchivePageStore;
219 $pagename = addslashes($pagename);
220 $query = "select count(*) from $ArchivePageStore " .
221 "where pagename='$pagename'";
222 $res = pg_exec($query);
223 $array = pg_fetch_array($res, 0);
228 // setup for title-search
229 function InitTitleSearch($dbi, $search) {
231 global $search_counter;
234 $search = strtolower($search);
235 $search = preg_replace('/(?=[%_\\\\])/', "\\", $search);
236 $search = addslashes($search);
237 $query = "select pagename from $dbi[table] where lower(pagename) " .
238 "like '%$search%' order by pagename";
239 //echo "search query: $query<br>\n";
240 $res = pg_exec($dbi["dbc"], $query);
246 // iterating through database
247 function TitleSearchNextMatch($dbi, $res) {
248 global $search_counter;
249 if($o = @pg_fetch_object($res, $search_counter)) {
258 // setup for full-text search
259 function InitFullSearch($dbi, $search) {
260 global $search_counter;
262 $search = strtolower($search);
263 $search = preg_replace('/(?=[%_\\\\])/', "\\", $search);
264 $search = addslashes($search);
265 $query = "select pagename,content from $dbi[table] " .
266 "where lower(content) like '%$search%'";
268 $res = pg_exec($dbi["dbc"], $query);
273 // iterating through database
274 function FullSearchNextMatch($dbi, $res) {
275 global $search_counter;
276 if ($hash = @pg_fetch_array($res, $search_counter)) {
278 $page['pagename'] = $hash["pagename"];
279 $page['content'] = explode("\n", $hash["content"]);
288 ////////////////////////
289 // new database features
291 // setup for back-link search
292 function InitBackLinkSearch($dbi, $pagename) {
293 global $WikiLinksPageStore;
295 $topage = addslashes($pagename);
296 $query = "SELECT DISTINCT frompage FROM $WikiLinksPageStore"
297 . " WHERE topage='$topage'"
298 . " ORDER BY frompage";
299 $res['res'] = pg_exec( $dbi["dbc"], $query);
305 // iterating through database
306 function BackLinkSearchNextMatch($dbi, &$res) {
307 if($a = @pg_fetch_row($res['res'], $res['row'])) {
317 function IncreaseHitCount($dbi, $pagename) {
318 global $HitCountPageStore;
320 $qpagename = addslashes($pagename);
321 $query = "update $HitCountPageStore set hits=hits+1 where pagename='$qpagename'";
322 $res = pg_exec($dbi['dbc'], $query);
324 if (!pg_cmdtuples($res)) {
325 $query = "insert into $HitCountPageStore (pagename, hits) " .
326 "values ('$qpagename', 1)";
327 $res = pg_exec($dbi['dbc'], $query);
333 function GetHitCount($dbi, $pagename) {
334 global $HitCountPageStore;
335 $qpagename = addslashes($pagename);
336 $query = "select hits from $HitCountPageStore where pagename='$qpagename'";
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");