3 /* Microsoft SQL-Server library for PHPWiki
\r
4 Author: Andrew K. Pearson
\r
10 OpenDataBase($dbname)
\r
12 MakeDBHash($pagename, $pagehash)
\r
13 MakePageHash($dbhash)
\r
14 RetrievePage($dbi, $pagename, $pagestore)
\r
15 InsertPage($dbi, $pagename, $pagehash)
\r
16 SaveCopyToArchive($dbi, $pagename, $pagehash)
\r
17 IsWikiPage($dbi, $pagename)
\r
18 IsInArchive($dbi, $pagename)
\r
19 RemovePage($dbi, $pagename)
\r
20 IncreaseHitCount($dbi, $pagename)
\r
21 GetHitCount($dbi, $pagename)
\r
22 MakeSQLSearchClause($search, $column)
\r
23 InitTitleSearch($dbi, $search)
\r
24 TitleSearchNextMatch($dbi, $res)
\r
25 InitFullSearch($dbi, $search)
\r
26 FullSearchNextMatch($dbi, $res)
\r
27 InitMostPopular($dbi, $limit)
\r
28 MostPopularNextMatch($dbi, $res)
\r
29 GetAllWikiPageNames($dbi)
\r
30 GetWikiPageLinks($dbi, $pagename)
\r
31 SetWikiPageLinks($dbi, $pagename, $linklist)
\r
34 // open a database and return the handle
\r
35 // ignores MAX_DBM_ATTEMPTS
\r
37 function OpenDataBase($dbname) {
\r
38 global $mssql_server, $mssql_user, $mssql_pwd, $mssql_db;
\r
40 if (!($dbc = mssql_pconnect($mssql_server, $mssql_user, $mssql_pwd))) {
\r
41 $msg = gettext ("Cannot establish connection to database, giving up.");
\r
43 $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message());
\r
47 mssql_get_last_message();
\r
49 if (!mssql_select_db($mssql_db, $dbc)) {
\r
50 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $mssql_db);
\r
52 $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message());
\r
56 mssql_get_last_message();
\r
59 $dbi['table'] = $dbname;
\r
64 function CloseDataBase($dbi) {
\r
66 // mssql connections are established as persistant
\r
67 // they cannot be closed through mssql_close()
\r
71 // prepare $pagehash for storing in mssql
\r
72 function MakeDBHash($pagename, $pagehash)
\r
74 $pagehash["pagename"] = addslashes($pagename);
\r
75 if (!isset($pagehash["flags"]))
\r
76 $pagehash["flags"] = 0;
\r
77 $pagehash["author"] = addslashes($pagehash["author"]);
\r
78 $pagehash["content"] = implode("\n", $pagehash["content"]);
\r
79 $pagehash["content"] = addslashes($pagehash["content"]);
\r
80 if (!isset($pagehash["refs"]))
\r
81 $pagehash["refs"] = array();
\r
82 $pagehash["refs"] = serialize($pagehash["refs"]);
\r
88 // convert mssql result $dbhash to $pagehash
\r
89 function MakePageHash($dbhash)
\r
91 // unserialize/explode content
\r
92 $dbhash['refs'] = unserialize($dbhash['refs']);
\r
93 $dbhash['content'] = explode("\n", $dbhash['content']);
\r
98 // Return hash of page + attributes or default
\r
99 function RetrievePage($dbi, $pagename, $pagestore) {
\r
100 $pagename = addslashes($pagename);
\r
101 if ($res = mssql_query("select * from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
\r
102 if ($dbhash = mssql_fetch_array($res)) {
\r
103 return MakePageHash($dbhash);
\r
110 // Either insert or replace a key/value (a page)
\r
111 function InsertPage($dbi, $pagename, $pagehash) {
\r
113 $pagehash = MakeDBHash($pagename, $pagehash);
\r
115 // record the time of modification
\r
116 $pagehash["lastmodified"] = time();
\r
118 if (IsWikiPage($dbi, $pagename)) {
\r
120 $PAIRS = "author='$pagehash[author]'," .
\r
121 "content='$pagehash[content]'," .
\r
122 "created=$pagehash[created]," .
\r
123 "flags=$pagehash[flags]," .
\r
124 "lastmodified=$pagehash[lastmodified]," .
\r
125 "pagename='$pagehash[pagename]'," .
\r
126 "refs='$pagehash[refs]'," .
\r
127 "version=$pagehash[version]";
\r
129 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
\r
133 // build up the column names and values for the query
\r
135 $COLUMNS = "author, content, created, flags, lastmodified, " .
\r
136 "pagename, refs, version";
\r
138 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
\r
139 "$pagehash[created], $pagehash[flags], " .
\r
140 "$pagehash[lastmodified], '$pagehash[pagename]', " .
\r
141 "'$pagehash[refs]', $pagehash[version]";
\r
144 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
\r
147 //echo "<p>Insert/Update Query: $query<p>\n";
\r
149 $retval = mssql_query($query);
\r
150 if ($retval == false) {
\r
151 printf(gettext ("Insert/Update failed: %s <br>\n"), mssql_get_last_message());
\r
156 // for archiving pages to a seperate dbm
\r
157 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
\r
158 global $ArchivePageStore;
\r
159 $adbi = OpenDataBase($ArchivePageStore);
\r
160 InsertPage($adbi, $pagename, $pagehash);
\r
164 function IsWikiPage($dbi, $pagename) {
\r
165 $pagename = addslashes($pagename);
\r
166 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
\r
167 return(mssql_result($res, 0, 0));
\r
172 function IsInArchive($dbi, $pagename) {
\r
173 global $ArchivePageStore;
\r
175 $pagename = addslashes($pagename);
\r
176 if ($res = mssql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
\r
177 return(mssql_result($res, 0, 0));
\r
183 function RemovePage($dbi, $pagename) {
\r
184 global $WikiPageStore, $ArchivePageStore;
\r
185 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
\r
187 $pagename = addslashes($pagename);
\r
188 $msg = gettext ("Cannot delete '%s' from table '%s'");
\r
190 $msg .= gettext ("MSSQL error: %s");
\r
192 if (!mssql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
\r
193 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mssql_get_last_message()));
\r
195 if (!mssql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
\r
196 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mssql_get_last_message()));
\r
198 if (!mssql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
\r
199 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mssql_get_last_message()));
\r
201 if (!mssql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
\r
202 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mssql_get_last_message()));
\r
204 if (!mssql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
\r
205 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mssql_get_last_message()));
\r
209 function IncreaseHitCount($dbi, $pagename)
\r
211 global $HitCountStore;
\r
214 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
\r
215 $rowexists = (mssql_result($res, 0, 0));
\r
219 $res = mssql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']);
\r
221 $res = mssql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']);
\r
226 function GetHitCount($dbi, $pagename)
\r
228 global $HitCountStore;
\r
230 $res = mssql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']);
\r
231 if (mssql_num_rows($res))
\r
232 $hits = mssql_result($res, 0, 0);
\r
239 function MakeSQLSearchClause($search, $column)
\r
241 $search = addslashes(preg_replace("/\s+/", " ", $search));
\r
242 $term = strtok($search, ' ');
\r
246 if ($word[0] == '-') {
\r
247 $word = substr($word, 1);
\r
248 $clause .= "not ($column like '%$word%') ";
\r
250 $clause .= "($column like '%$word%') ";
\r
252 if ($term = strtok(' '))
\r
258 // setup for title-search
\r
259 function InitTitleSearch($dbi, $search) {
\r
260 $clause = MakeSQLSearchClause($search, 'pagename');
\r
261 $res = mssql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
\r
267 // iterating through database
\r
268 function TitleSearchNextMatch($dbi, $res) {
\r
269 if($o = mssql_fetch_object($res)) {
\r
270 return $o->pagename;
\r
278 // setup for full-text search
\r
279 function InitFullSearch($dbi, $search) {
\r
280 $clause = MakeSQLSearchClause($search, 'content');
\r
281 $res = mssql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
\r
286 // iterating through database
\r
287 function FullSearchNextMatch($dbi, $res) {
\r
288 if($hash = mssql_fetch_array($res)) {
\r
289 return MakePageHash($hash);
\r
296 function InitMostPopular($dbi, $limit) {
\r
297 global $HitCountStore;
\r
298 $res = mssql_query("select top $limit * from $HitCountStore order by hits desc, pagename", $dbi["dbc"]);
\r
303 function MostPopularNextMatch($dbi, $res) {
\r
304 if ($hits = mssql_fetch_array($res))
\r
310 function GetAllWikiPageNames($dbi) {
\r
311 global $WikiPageStore;
\r
312 $res = mssql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
\r
313 $rows = mssql_num_rows($res);
\r
314 for ($i = 0; $i < $rows; $i++) {
\r
315 $pages[$i] = mssql_result($res, $i, 0);
\r
321 ////////////////////////////////////////
\r
322 // functionality for the wikilinks table
\r
324 // takes a page name, returns array of scored incoming and outgoing links
\r
325 function GetWikiPageLinks($dbi, $pagename) {
\r
326 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
\r
328 $pagename = addslashes($pagename);
\r
329 $res = mssql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
\r
330 $rows = mssql_num_rows($res);
\r
331 for ($i = 0; $i < $rows; $i++) {
\r
332 $out = mssql_fetch_array($res);
\r
333 $links['out'][] = array($out['topage'], $out['score']);
\r
336 $res = mssql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
\r
337 $rows = mssql_num_rows($res);
\r
338 for ($i = 0; $i < $rows; $i++) {
\r
339 $out = mssql_fetch_array($res);
\r
340 $links['in'][] = array($out['frompage'], $out['score']);
\r
343 $res = mssql_query("select distinct pagename, hits from $WikiLinksStore, $HitCountStore where (frompage=pagename and topage='$pagename') or (topage=pagename and frompage='$pagename') order by hits desc, pagename");
\r
344 $rows = mssql_num_rows($res);
\r
345 for ($i = 0; $i < $rows; $i++) {
\r
346 $out = mssql_fetch_array($res);
\r
347 $links['popular'][] = array($out['pagename'], $out['hits']);
\r
354 // takes page name, list of links it contains
\r
355 // the $linklist is an array where the keys are the page names
\r
356 function SetWikiPageLinks($dbi, $pagename, $linklist) {
\r
357 global $WikiLinksStore, $WikiScoreStore;
\r
359 $frompage = addslashes($pagename);
\r
361 // first delete the old list of links
\r
362 mssql_query("delete from $WikiLinksStore where frompage='$frompage'",
\r
365 // the page may not have links, return if not
\r
366 if (! count($linklist))
\r
368 // now insert the new list of links
\r
369 while (list($topage, $count) = each($linklist)) {
\r
370 $topage = addslashes($topage);
\r
371 if($topage != $frompage) {
\r
372 mssql_query("insert into $WikiLinksStore (frompage, topage) " .
\r
373 "values ('$frompage', '$topage')", $dbi["dbc"]);
\r
377 // update pagescore
\r
378 mssql_query("delete from $WikiScoreStore", $dbi["dbc"]);
\r
379 mssql_query("insert into $WikiScoreStore select w1.topage, count(*) from $WikiLinksStore as w1, $WikiLinksStore as w2 where w2.topage=w1.frompage group by w1.topage", $dbi["dbc"]);
\r
382 /* more mssql queries:
\r
385 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
\r