1 <!-- $Id: pgsql.php,v 1.4 2000-11-02 04:23:59 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 IncreaseHitCount($dbi, $pagename)
19 GetHitCount($dbi, $pagename)
20 InitMostPopular($dbi, $limit)
21 MostPopularNextMatch($dbi, $res)
22 GetAllWikiPageNames($dbi)
23 GetWikiPageLinks($dbi, $pagename)
24 SetWikiPageLinks($dbi, $pagename, $linklist)
28 // open a database and return a hash
30 function OpenDataBase($table) {
31 global $WikiDataBase, $pg_dbhost, $pg_dbport;
33 $connectstring = $pg_dbhost?"host=$pg_dbhost ":"";
34 $connectstring .= $pg_dbport?"port=$pg_dbport ":"";
35 $connectstring .= $WikiDataBase?"dbname=$WikiDataBase":"";
37 if (!($dbc = pg_pconnect($connectstring))) {
38 echo "Cannot establish connection to database, giving up.";
43 $dbi['table'] = $table;
44 // echo "<p>dbi after open: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>\n";
49 function CloseDataBase($dbi) {
50 // NOOP: we use persistent database connections
54 // Return hash of page + attributes or default
55 function RetrievePage($dbi, $pagename, $pagestore) {
56 $pagename = addslashes($pagename);
57 $query = "select * from $pagestore where pagename='$pagename'";
58 // echo "<p>$query<p>";
59 $res = pg_exec($dbi['dbc'], $query);
61 if (pg_numrows($res)) {
62 if ($array = pg_fetch_array($res, 0)) {
63 while (list($key, $val) = each($array)) {
64 // pg_fetch_array gives us all the values twice,
65 // so we have to manually edit out the indices
66 if (gettype($key) == "integer") {
69 $pagehash[$key] = $val;
72 // unserialize/explode content
73 $pagehash['refs'] = unserialize($pagehash['refs']);
74 $pagehash['content'] = explode("\n", $pagehash['content']);
80 // if we reach this the query failed
85 // Either insert or replace a key/value (a page)
86 function InsertPage($dbi, $pagename, $pagehash) {
87 $pagename = addslashes($pagename);
89 // update the wikilinks table
90 $linklist = ExtractWikiPageLinks($pagehash['content']);
91 SetWikiPageLinks($dbi, $pagename, $linklist);
94 // prepare the content for storage
95 if (!isset($pagehash["pagename"]))
96 $pagehash["pagename"] = $pagename;
97 if (!isset($pagehash["flags"]))
98 $pagehash["flags"] = 0;
99 $pagehash["author"] = addslashes($pagehash["author"]);
100 $pagehash["content"] = implode("\n", $pagehash["content"]);
101 $pagehash["content"] = addslashes($pagehash["content"]);
102 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
103 $pagehash["refs"] = serialize($pagehash["refs"]);
105 // Check for empty variables which can cause a sql error
106 if(empty($pagehash["created"]))
107 $pagehash["created"] = time();
108 if(empty($pagehash["version"]))
109 $pagehash["version"] = 1;
111 // record the time of modification
112 $pagehash["lastmodified"] = time();
115 if (IsWikiPage($dbi, $pagename)) {
117 $PAIRS = "author='$pagehash[author]'," .
118 "content='$pagehash[content]'," .
119 "created=$pagehash[created]," .
120 "flags=$pagehash[flags]," .
121 "lastmodified=$pagehash[lastmodified]," .
122 "pagename='$pagehash[pagename]'," .
123 "refs='$pagehash[refs]'," .
124 "version=$pagehash[version]";
126 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
130 // build up the column names and values for the query
132 $COLUMNS = "author, content, created, flags, " .
133 "lastmodified, pagename, refs, version";
135 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
136 "$pagehash[created], $pagehash[flags], " .
137 "$pagehash[lastmodified], '$pagehash[pagename]', " .
138 "'$pagehash[refs]', $pagehash[version]";
141 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
144 // echo "<p>Query: $query<p>\n";
145 $retval = pg_exec($dbi['dbc'], $query);
146 if ($retval == false)
147 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
152 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
153 global $ArchivePageStore;
154 // echo "<p>save copy called<p>";
156 $pagename = addslashes($pagename);
157 // echo "<p>dbi in SaveCopyToArchive: '$dbi' '$ArchivePageStore' '$dbi[dbc]'<p>";
159 // prepare the content for storage
160 if (!isset($pagehash["pagename"]))
161 $pagehash["pagename"] = $pagename;
162 if (!isset($pagehash["flags"]))
163 $pagehash["flags"] = 0;
164 $pagehash["author"] = addslashes($pagehash["author"]);
165 $pagehash["content"] = implode("\n", $pagehash["content"]);
166 $pagehash["content"] = addslashes($pagehash["content"]);
167 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
168 $pagehash["refs"] = serialize($pagehash["refs"]);
170 if (IsInArchive($dbi, $pagename)) {
172 $PAIRS = "author='$pagehash[author]'," .
173 "content='$pagehash[content]'," .
174 "created=$pagehash[created]," .
175 "flags=$pagehash[flags]," .
176 "lastmodified=$pagehash[lastmodified]," .
177 "pagename='$pagehash[pagename]'," .
178 "refs='$pagehash[refs]'," .
179 "version=$pagehash[version]";
181 $query = "UPDATE $ArchivePageStore SET $PAIRS WHERE pagename='$pagename'";
185 // build up the column names and values for the query
187 $COLUMNS = "author, content, created, flags, " .
188 "lastmodified, pagename, refs, version";
190 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
191 "$pagehash[created], $pagehash[flags], " .
192 "$pagehash[lastmodified], '$pagehash[pagename]', " .
193 "'$pagehash[refs]', $pagehash[version]";
196 $query = "INSERT INTO $ArchivePageStore ($COLUMNS) VALUES($VALUES)";
199 // echo "<p>Query: $query<p>\n";
200 $retval = pg_exec($dbi['dbc'], $query);
201 if ($retval == false)
202 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
208 function IsWikiPage($dbi, $pagename) {
209 global $WikiPageStore;
210 $pagename = addslashes($pagename);
211 $query = "select count(*) from $WikiPageStore " .
212 "where pagename='$pagename'";
213 $res = pg_exec($query);
214 $array = pg_fetch_array($res, 0);
219 function IsInArchive($dbi, $pagename) {
220 global $ArchivePageStore;
221 $pagename = addslashes($pagename);
222 $query = "select count(*) from $ArchivePageStore " .
223 "where pagename='$pagename'";
224 $res = pg_exec($query);
225 $array = pg_fetch_array($res, 0);
230 // setup for title-search
231 function InitTitleSearch($dbi, $search) {
233 global $search_counter;
236 $search = strtolower($search);
237 $search = addslashes($search);
238 $query = "select pagename from $dbi[table] where lower(pagename) " .
239 "like '%$search%' order by pagename";
240 //echo "search query: $query<br>\n";
241 $res = pg_exec($dbi["dbc"], $query);
247 // iterating through database
248 function TitleSearchNextMatch($dbi, $res) {
249 global $search_counter;
250 if($o = @pg_fetch_object($res, $search_counter)) {
259 // setup for full-text search
260 function InitFullSearch($dbi, $search) {
261 global $search_counter;
263 $search = strtolower($search);
264 $search = addslashes($search);
265 $search = addslashes($search);
266 $query = "select pagename,content from $dbi[table] " .
267 "where lower(content) like '%$search%'";
269 $res = pg_exec($dbi["dbc"], $query);
274 // iterating through database
275 function FullSearchNextMatch($dbi, $res) {
276 global $search_counter;
277 if ($hash = @pg_fetch_array($res, $search_counter)) {
279 $page['pagename'] = $hash["pagename"];
280 $page['content'] = explode("\n", $hash["content"]);
289 ////////////////////////
290 // new database features
293 function IncreaseHitCount($dbi, $pagename) {
294 global $HitCountPageStore;
295 $query = "update $HitCountPageStore set hits=hits+1 where pagename='$pagename'";
296 $res = pg_exec($dbi['dbc'], $query);
298 if (!pg_cmdtuples($res)) {
299 $query = "insert into $HitCountPageStore (pagename, hits) " .
300 "values ('$pagename', 1)";
301 $res = pg_exec($dbi['dbc'], $query);
307 function GetHitCount($dbi, $pagename) {
308 global $HitCountPageStore;
309 $query = "select hits from $HitCountPageStore where pagename='$pagename'";
310 $res = pg_exec($dbi['dbc'], $query);
311 if (pg_cmdtuples($res)) {
312 $hits = pg_result($res, 0, "hits");
322 function InitMostPopular($dbi, $limit) {
324 global $pg_most_pop_ctr, $HitCountPageStore;
325 $pg_most_pop_ctr = 0;
327 $query = "select * from $HitCountPageStore " .
328 "order by hits desc, pagename limit $limit";
329 $res = pg_exec($dbi['dbc'], $query);
333 function MostPopularNextMatch($dbi, $res) {
335 global $pg_most_pop_ctr;
336 if ($hits = @pg_fetch_array($res, $pg_most_pop_ctr)) {
344 function GetAllWikiPageNames($dbi) {
345 global $WikiPageStore;
346 $res = pg_exec($dbi['dbc'], "select pagename from $WikiPageStore");
347 $rows = pg_numrows($res);
348 for ($i = 0; $i < $rows; $i++) {
349 $pages[$i] = pg_result($res, $i, "pagename");
354 ////////////////////////////////////////
355 // functionality for the wikilinks table
357 // takes a page name, returns array of links
358 function GetWikiPageLinks($dbi, $pagename) {
359 global $WikiLinksPageStore;
360 $pagename = addslashes($pagename);
362 $res = pg_exec("select topage, score from wikilinks, wikiscore where topage=pagename and frompage='$pagename' order by score desc, topage");
363 $rows = pg_numrows($res);
364 for ($i = 0; $i < $rows; $i++) {
365 $out = pg_fetch_array($res, $i);
366 $links['out'][] = array($out['topage'], $out['score']);
369 $res = pg_exec("select frompage, score from wikilinks, wikiscore where frompage=pagename and topage='$pagename' order by score desc, frompage");
370 $rows = pg_numrows($res);
371 for ($i = 0; $i < $rows; $i++) {
372 $out = pg_fetch_array($res, $i);
373 $links['in'][] = array($out['frompage'], $out['score']);
376 $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");
377 $rows = pg_numrows($res);
378 for ($i = 0; $i < $rows; $i++) {
379 $out = pg_fetch_array($res, $i);
380 $links['popular'][] = array($out['pagename'], $out['hits']);
388 // takes page name, list of links it contains
389 // the $linklist is an array where the keys are the page names
391 function SetWikiPageLinks($dbi, $pagename, $linklist) {
392 global $WikiLinksPageStore;
393 $frompage = addslashes($pagename);
395 // first delete the old list of links
396 $query = "delete from $WikiLinksPageStore where frompage='$frompage'";
397 //echo "$query<br>\n";
398 $res = pg_exec($dbi['dbc'], $query);
400 // the page may not have links, return if not
401 if (! count($linklist))
404 // now insert the new list of links
406 while (list($topage, $count) = each($linklist)) {
407 $topage = addslashes($topage);
408 if ($topage != $frompage) {
409 $query = "insert into $WikiLinksPageStore (frompage, topage) " .
410 "values ('$frompage', '$topage')";
411 //echo "$query<br>\n";
412 $res = pg_exec($dbi['dbc'], $query);
416 pg_exec("delete from wikiscore");
417 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");