1 <!-- $Id: pgsql.php,v 1.3 2000-11-02 03:05:20 wainstead Exp $ -->
9 RetrievePage($dbi, $pagename, $pagestore)
10 InsertPage($dbi, $pagename, $pagehash)
11 SaveCopyToArchive($dbi, $pagename, $pagehash)
12 IsWikiPage($dbi, $pagename)
13 InitTitleSearch($dbi, $search)
14 TitleSearchNextMatch($dbi, $res)
15 InitFullSearch($dbi, $search)
16 FullSearchNextMatch($dbi, $res)
17 IncreaseHitCount($dbi, $pagename)
18 GetHitCount($dbi, $pagename)
19 InitMostPopular($dbi, $limit)
20 MostPopularNextMatch($dbi, $res)
21 GetWikiPageLinks($dbi, $pagename)
22 SetWikiPageLinks($dbi, $pagename, $linklist)
26 // open a database and return a hash
28 function OpenDataBase($table) {
29 global $WikiDataBase, $pg_dbhost, $pg_dbport;
31 $connectstring = $pg_dbhost?"host=$pg_dbhost ":"";
32 $connectstring .= $pg_dbport?"port=$pg_dbport ":"";
33 $connectstring .= $WikiDataBase?"dbname=$WikiDataBase":"";
35 if (!($dbc = pg_pconnect($connectstring))) {
36 echo "Cannot establish connection to database, giving up.";
41 $dbi['table'] = $table;
42 // echo "<p>dbi after open: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>\n";
47 function CloseDataBase($dbi) {
48 // NOOP: we use persistent database connections
52 // Return hash of page + attributes or default
53 function RetrievePage($dbi, $pagename, $pagestore) {
54 $pagename = addslashes($pagename);
55 $query = "select * from $pagestore where pagename='$pagename'";
56 // echo "<p>$query<p>";
57 $res = pg_exec($dbi['dbc'], $query);
59 if (pg_numrows($res)) {
60 if ($array = pg_fetch_array($res, 0)) {
61 while (list($key, $val) = each($array)) {
62 // pg_fetch_array gives us all the values twice,
63 // so we have to manually edit out the indices
64 if (gettype($key) == "integer") {
67 $pagehash[$key] = $val;
70 // unserialize/explode content
71 $pagehash['refs'] = unserialize($pagehash['refs']);
72 $pagehash['content'] = explode("\n", $pagehash['content']);
78 // if we reach this the query failed
83 // Either insert or replace a key/value (a page)
84 function InsertPage($dbi, $pagename, $pagehash) {
85 $pagename = addslashes($pagename);
86 // echo "<p>dbi in InsertPage: '$dbi' '$dbi[table]' '$dbi[dbc]'<p>";
88 // update the wikilinks table
89 // UpdateWikiLinks($dbi, $pagename, implode(" ",$pagehash['content']));
91 // prepare the content for storage
92 if (!isset($pagehash["pagename"]))
93 $pagehash["pagename"] = $pagename;
94 if (!isset($pagehash["flags"]))
95 $pagehash["flags"] = 0;
96 $pagehash["author"] = addslashes($pagehash["author"]);
97 $pagehash["content"] = implode("\n", $pagehash["content"]);
98 $pagehash["content"] = addslashes($pagehash["content"]);
99 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
100 $pagehash["refs"] = serialize($pagehash["refs"]);
102 // Check for empty variables which can cause a sql error
103 if(empty($pagehash["created"]))
104 $pagehash["created"] = time();
105 if(empty($pagehash["version"]))
106 $pagehash["version"] = 1;
108 // record the time of modification
109 $pagehash["lastmodified"] = time();
112 if (IsWikiPage($dbi, $pagename)) {
114 $PAIRS = "author='$pagehash[author]'," .
115 "content='$pagehash[content]'," .
116 "created=$pagehash[created]," .
117 "flags=$pagehash[flags]," .
118 "lastmodified=$pagehash[lastmodified]," .
119 "pagename='$pagehash[pagename]'," .
120 "refs='$pagehash[refs]'," .
121 "version=$pagehash[version]";
123 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
127 // build up the column names and values for the query
129 $COLUMNS = "author, content, created, flags, " .
130 "lastmodified, pagename, refs, version";
132 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
133 "$pagehash[created], $pagehash[flags], " .
134 "$pagehash[lastmodified], '$pagehash[pagename]', " .
135 "'$pagehash[refs]', $pagehash[version]";
138 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
141 // echo "<p>Query: $query<p>\n";
142 $retval = pg_exec($dbi['dbc'], $query);
143 if ($retval == false)
144 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
149 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
150 global $ArchivePageStore;
151 // echo "<p>save copy called<p>";
153 $pagename = addslashes($pagename);
154 // echo "<p>dbi in SaveCopyToArchive: '$dbi' '$ArchivePageStore' '$dbi[dbc]'<p>";
156 // prepare the content for storage
157 if (!isset($pagehash["pagename"]))
158 $pagehash["pagename"] = $pagename;
159 if (!isset($pagehash["flags"]))
160 $pagehash["flags"] = 0;
161 $pagehash["author"] = addslashes($pagehash["author"]);
162 $pagehash["content"] = implode("\n", $pagehash["content"]);
163 $pagehash["content"] = addslashes($pagehash["content"]);
164 $pagehash["pagename"] = addslashes($pagehash["pagename"]);
165 $pagehash["refs"] = serialize($pagehash["refs"]);
167 if (IsInArchive($dbi, $pagename)) {
169 $PAIRS = "author='$pagehash[author]'," .
170 "content='$pagehash[content]'," .
171 "created=$pagehash[created]," .
172 "flags=$pagehash[flags]," .
173 "lastmodified=$pagehash[lastmodified]," .
174 "pagename='$pagehash[pagename]'," .
175 "refs='$pagehash[refs]'," .
176 "version=$pagehash[version]";
178 $query = "UPDATE $ArchivePageStore SET $PAIRS WHERE pagename='$pagename'";
182 // build up the column names and values for the query
184 $COLUMNS = "author, content, created, flags, " .
185 "lastmodified, pagename, refs, version";
187 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
188 "$pagehash[created], $pagehash[flags], " .
189 "$pagehash[lastmodified], '$pagehash[pagename]', " .
190 "'$pagehash[refs]', $pagehash[version]";
193 $query = "INSERT INTO $ArchivePageStore ($COLUMNS) VALUES($VALUES)";
196 // echo "<p>Query: $query<p>\n";
197 $retval = pg_exec($dbi['dbc'], $query);
198 if ($retval == false)
199 echo "Insert/update failed: " . pg_errormessage($dbi['dbc']);
205 function UpdateWikiLinks($dbi, $pagename, $pagetext) {
207 global $AllowedProtocols;
208 // extract all links from the page, both [] and OldStyle
210 // this is [bracketlinks]
211 $numBracketLinks = preg_match_all("/\[.+?\]/s", $pagetext, $brktlinks);
213 // this is OldSchoolLinking
214 $numWikiLinks = preg_match_all("#!?\b(([A-Z][a-z]+){2,})\b#",
215 $pagetext, $wikilinks);
217 for ($x = 0; $x < $numWikiLinks; $x++) {
218 if (preg_match("/^!/", $wikilinks[0][$x]))
220 $alllinks[$wikilinks[0][$x]]++;
221 //echo "MATCH: ", $wikilinks[0][$x], "<P>\n";
222 //echo "assigned ", $alllinks[$wikilinks[0][$x]], " ", $wikilinks[0][$x], " <br>\n";
225 for ($x = 0; $x < $numBracketLinks; $x++) {
226 // skip escaped bracket sets [[like this]
227 if (preg_match("/^\[\[/", $brktlinks[0][$x]))
229 // skip anything with an allowed protocol
230 if (preg_match("/$AllowedProtocols/", $brktlinks[0][$x]))
234 $alllinks[$brktlinks[0][$x]]++;
236 //echo "MATCH: ", $brktlinks[0][$x], "<P>\n";
237 //echo "assigned ", $alllinks[$brktlinks[0][$x]], " ", $brktlinks[0][$x], " <br>\n";
240 // call the right function to update the table
241 SetWikiPageLinks($dbi, $pagename, $alllinks);
246 function IsWikiPage($dbi, $pagename) {
247 global $WikiPageStore;
248 $pagename = addslashes($pagename);
249 $query = "select count(*) from $WikiPageStore where pagename='$pagename'";
250 $res = pg_exec($query);
251 $array = pg_fetch_array($res, 0);
256 function IsInArchive($dbi, $pagename) {
257 global $ArchivePageStore;
258 $pagename = addslashes($pagename);
259 $query = "select count(*) from $ArchivePageStore where pagename='$pagename'";
260 $res = pg_exec($query);
261 $array = pg_fetch_array($res, 0);
266 // setup for title-search
267 function InitTitleSearch($dbi, $search) {
269 global $search_counter;
272 $search = strtolower($search);
273 $search = addslashes($search);
274 $query = "select pagename from $dbi[table] where lower(pagename) " .
275 "like '%$search%' order by pagename";
276 // echo "search query: $query<br>\n";
277 $res = pg_exec($dbi["dbc"], $query);
283 // iterating through database
284 function TitleSearchNextMatch($dbi, $res) {
285 global $search_counter;
286 if($o = @pg_fetch_object($res, $search_counter)) {
295 // setup for full-text search
296 function InitFullSearch($dbi, $search) {
297 global $search_counter;
299 $search = strtolower($search);
300 $search = addslashes($search);
301 $search = addslashes($search);
302 $query = "select pagename,content from $dbi[table] " .
303 "where lower(content) like '%$search%'";
305 $res = pg_exec($dbi["dbc"], $query);
310 // iterating through database
311 function FullSearchNextMatch($dbi, $res) {
312 global $search_counter;
313 if ($hash = @pg_fetch_array($res, $search_counter)) {
315 $page['pagename'] = $hash["pagename"];
316 $page['content'] = explode("\n", $hash["content"]);
325 ////////////////////////
326 // new database features
329 function IncreaseHitCount($dbi, $pagename) {
330 global $HitCountPageStore;
331 $query = "update $HitCountPageStore set hits=hits+1 where pagename='$pagename'";
332 $res = pg_exec($dbi['dbc'], $query);
334 if (!pg_cmdtuples($res)) {
335 $query = "insert into $HitCountPageStore (pagename, hits) " .
336 "values ('$pagename', 1)";
337 $res = pg_exec($dbi['dbc'], $query);
343 function GetHitCount($dbi, $pagename) {
344 global $HitCountPageStore;
345 $query = "select hits from $HitCountPageStore where pagename='$pagename'";
346 $res = pg_exec($dbi['dbc'], $query);
347 if (pg_cmdtuples($res)) {
348 $hits = pg_result($res, 0, "hits");
358 function InitMostPopular($dbi, $limit) {
360 global $pg_most_pop_ctr, $HitCountPageStore;
361 $pg_most_pop_ctr = 0;
363 $query = "select * from $HitCountPageStore " .
364 "order by hits desc, pagename limit $limit";
365 $res = pg_exec($dbi['dbc'], $query);
369 function MostPopularNextMatch($dbi, $res) {
371 global $pg_most_pop_ctr;
372 if ($hits = @pg_fetch_array($res, $pg_most_pop_ctr)) {
380 function GetAllWikiPageNames($dbi) {
381 global $WikiPageStore;
382 $res = pg_exec($dbi['dbc'], "select pagename from $WikiPageStore");
383 $rows = pg_numrows($res);
384 for ($i = 0; $i < $rows; $i++) {
385 $pages[$i] = pg_result($res, $i, "pagename");
390 ////////////////////////////////////////
391 // functionality for the wikilinks table
393 // takes a page name, returns array of links
394 function GetWikiPageLinks($dbi, $pagename) {
395 global $WikiLinksPageStore;
396 $query = "select frompage from $WikiLinksPageStore where topage='$pagename'";
397 $res = pg_exec($dbi['dbc'], $query);
398 $rows = pg_numrows($res);
399 for ($i = 0; $i < $rows; $i++) {
400 $pages[$i] = pg_result($res, $i, "frompage");
407 // takes page name, list of links it contains
408 // the $linklist is an array where the keys are the page names
410 function SetWikiPageLinks($dbi, $pagename, $linklist) {
411 global $WikiLinksPageStore;
412 $frompage = addslashes($pagename);
414 // first delete the old list of links
415 $query = "delete from $WikiLinksPageStore where frompage='$frompage'";
416 // echo "$query<br>\n";
417 $res = pg_exec($dbi['dbc'], $query);
419 // the page may not have links, return if not
420 if (! count($linklist))
423 // now insert the new list of links
425 while (list($topage, $count) = each($linklist)) {
426 $topage = addslashes($topage);
427 $query = "insert into $WikiLinksPageStore (frompage, topage) " .
428 "values ('$frompage', '$topage')";
429 // echo "$query<br>\n";
430 $res = pg_exec($dbi['dbc'], $query);