1 <?php rcs_id('$Id: mssql.php,v 1.1.2.8 2005-01-07 14:23:04 rurban Exp $');
3 /* Microsoft SQL-Server library for PHPWiki
4 Author: Andrew K. Pearson
12 MakeDBHash($pagename, $pagehash)
14 RetrievePage($dbi, $pagename, $pagestore)
15 InsertPage($dbi, $pagename, $pagehash)
16 SaveCopyToArchive($dbi, $pagename, $pagehash)
17 IsWikiPage($dbi, $pagename)
18 IsInArchive($dbi, $pagename)
19 RemovePage($dbi, $pagename)
20 IncreaseHitCount($dbi, $pagename)
21 GetHitCount($dbi, $pagename)
22 MakeSQLSearchClause($search, $column)
23 InitTitleSearch($dbi, $search)
24 TitleSearchNextMatch($dbi, $res)
25 InitFullSearch($dbi, $search)
26 FullSearchNextMatch($dbi, $res)
27 InitMostPopular($dbi, $limit)
28 MostPopularNextMatch($dbi, $res)
29 GetAllWikiPageNames($dbi)
30 GetWikiPageLinks($dbi, $pagename)
31 SetWikiPageLinks($dbi, $pagename, $linklist)
34 // open a database and return the handle
35 // ignores MAX_DBM_ATTEMPTS
37 function OpenDataBase($dbname) {
38 global $mssql_server, $mssql_user, $mssql_pwd, $mssql_db;
40 if (!($dbc = mssql_pconnect($mssql_server, $mssql_user, $mssql_pwd))) {
41 $msg = gettext ("Cannot establish connection to database, giving up.");
43 $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message());
47 mssql_get_last_message();
49 if (!mssql_select_db($mssql_db, $dbc)) {
50 $msg = sprintf(gettext ("Cannot open database %s, giving up."), $mssql_db);
52 $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message());
56 mssql_get_last_message();
59 $dbi['table'] = $dbname;
64 function CloseDataBase($dbi) {
66 // mssql connections are established as persistant
67 // they cannot be closed through mssql_close()
71 // prepare $pagehash for storing in mssql
72 function MakeDBHash($pagename, $pagehash)
74 $pagehash["pagename"] = addslashes($pagename);
75 if (!isset($pagehash["flags"]))
76 $pagehash["flags"] = 0;
77 $pagehash["author"] = addslashes($pagehash["author"]);
78 $pagehash["content"] = implode("\n", $pagehash["content"]);
79 $pagehash["content"] = addslashes($pagehash["content"]);
80 if (!isset($pagehash["refs"]))
81 $pagehash["refs"] = array();
82 $pagehash["refs"] = serialize($pagehash["refs"]);
88 // convert mssql result $dbhash to $pagehash
89 function MakePageHash($dbhash)
91 // unserialize/explode content
92 $dbhash['refs'] = unserialize($dbhash['refs']);
93 $dbhash['content'] = explode("\n", $dbhash['content']);
98 // Return hash of page + attributes or default
99 function RetrievePage($dbi, $pagename, $pagestore) {
100 $pagename = addslashes($pagename);
101 if ($res = mssql_query("select * from $pagestore where pagename='$pagename'", $dbi['dbc'])) {
102 if ($dbhash = mssql_fetch_array($res)) {
103 return MakePageHash($dbhash);
110 // Either insert or replace a key/value (a page)
111 function InsertPage($dbi, $pagename, $pagehash) {
113 global $WikiPageStore; // ugly hack
114 if ($dbi['table'] == $WikiPageStore)
116 $linklist = ExtractWikiPageLinks($pagehash['content']);
117 SetWikiPageLinks($dbi, $pagename, $linklist);
120 $pagehash = MakeDBHash($pagename, $pagehash);
122 // record the time of modification
123 $pagehash["lastmodified"] = time();
125 if (IsWikiPage($dbi, $pagename)) {
127 $PAIRS = "author='$pagehash[author]'," .
128 "content='$pagehash[content]'," .
129 "created=$pagehash[created]," .
130 "flags=$pagehash[flags]," .
131 "lastmodified=$pagehash[lastmodified]," .
132 "pagename='$pagehash[pagename]'," .
133 "refs='$pagehash[refs]'," .
134 "version=$pagehash[version]";
136 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
140 // build up the column names and values for the query
142 $COLUMNS = "author, content, created, flags, lastmodified, " .
143 "pagename, refs, version";
145 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
146 "$pagehash[created], $pagehash[flags], " .
147 "$pagehash[lastmodified], '$pagehash[pagename]', " .
148 "'$pagehash[refs]', $pagehash[version]";
151 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
154 //echo "<p>Insert/Update Query: $query<p>\n";
156 $retval = mssql_query($query);
157 if ($retval == false) {
158 printf(gettext ("Insert/Update failed: %s"), mssql_get_last_message() . "<br>\n");
163 // for archiving pages to a seperate dbm
164 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
165 global $ArchivePageStore;
166 $adbi = OpenDataBase($ArchivePageStore);
167 InsertPage($adbi, $pagename, $pagehash);
171 function IsWikiPage($dbi, $pagename) {
172 $pagename = addslashes($pagename);
173 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
174 return(mssql_result($res, 0, 0));
179 function IsInArchive($dbi, $pagename) {
180 global $ArchivePageStore;
182 $pagename = addslashes($pagename);
183 if ($res = mssql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
184 return(mssql_result($res, 0, 0));
190 function RemovePage($dbi, $pagename) {
191 global $WikiPageStore, $ArchivePageStore;
192 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
194 $pagename = addslashes($pagename);
195 $msg = gettext ("Cannot delete '%s' from table '%s'");
197 $msg .= gettext ("MSSQL error: %s");
199 if (!mssql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
200 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mssql_get_last_message()));
202 if (!mssql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
203 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mssql_get_last_message()));
205 if (!mssql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
206 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mssql_get_last_message()));
208 if (!mssql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
209 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mssql_get_last_message()));
211 if (!mssql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
212 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mssql_get_last_message()));
216 function IncreaseHitCount($dbi, $pagename)
218 global $HitCountStore;
220 $qpagename = addslashes($pagename);
222 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$qpagename'", $dbi['dbc'])) {
223 $rowexists = (mssql_result($res, 0, 0));
227 $res = mssql_query("update $HitCountStore set hits=hits+1 where pagename='$qpagename'", $dbi['dbc']);
229 $res = mssql_query("insert into $HitCountStore (pagename, hits) values ('$qpagename', 1)", $dbi['dbc']);
234 function GetHitCount($dbi, $pagename)
236 global $HitCountStore;
238 $qpagename = addslashes($pagename);
239 $res = mssql_query("select hits from $HitCountStore where pagename='$qpagename'", $dbi['dbc']);
240 if (mssql_num_rows($res))
241 $hits = mssql_result($res, 0, 0);
248 function MakeSQLSearchClause($search, $column)
250 $search = preg_replace("/\s+/", " ", trim($search));
251 $search = preg_replace('/(?=[%_\\\\])/', "\\", $search);
252 $search = addslashes($search);
254 $term = strtok($search, ' ');
258 if ($word[0] == '-') {
259 $word = substr($word, 1);
260 $clause .= "not ($column like '%$word%') ";
262 $clause .= "($column like '%$word%') ";
264 if ($term = strtok(' '))
270 // setup for title-search
271 function InitTitleSearch($dbi, $search) {
272 $clause = MakeSQLSearchClause($search, 'pagename');
273 $res = mssql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
279 // iterating through database
280 function TitleSearchNextMatch($dbi, $res) {
281 if($o = mssql_fetch_object($res)) {
290 // setup for full-text search
291 function InitFullSearch($dbi, $search) {
292 $clause = MakeSQLSearchClause($search, 'content');
293 $res = mssql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
298 // iterating through database
299 function FullSearchNextMatch($dbi, $res) {
300 if($hash = mssql_fetch_array($res)) {
301 return MakePageHash($hash);
308 function InitMostPopular($dbi, $limit) {
309 global $HitCountStore;
310 $res = mssql_query("select top $limit * from $HitCountStore order by hits desc, pagename", $dbi["dbc"]);
315 function MostPopularNextMatch($dbi, $res) {
316 if ($hits = mssql_fetch_array($res))
322 function GetAllWikiPageNames($dbi) {
323 global $WikiPageStore;
324 $res = mssql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
325 $rows = mssql_num_rows($res);
326 for ($i = 0; $i < $rows; $i++) {
327 $pages[$i] = mssql_result($res, $i, 0);
333 ////////////////////////////////////////
334 // functionality for the wikilinks table
336 // takes a page name, returns array of scored incoming and outgoing links
337 function GetWikiPageLinks($dbi, $pagename) {
338 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
340 $pagename = addslashes($pagename);
341 $res = mssql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
342 $rows = mssql_num_rows($res);
343 for ($i = 0; $i < $rows; $i++) {
344 $out = mssql_fetch_array($res);
345 $links['out'][] = array($out['topage'], $out['score']);
348 $res = mssql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
349 $rows = mssql_num_rows($res);
350 for ($i = 0; $i < $rows; $i++) {
351 $out = mssql_fetch_array($res);
352 $links['in'][] = array($out['frompage'], $out['score']);
355 $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");
356 $rows = mssql_num_rows($res);
357 for ($i = 0; $i < $rows; $i++) {
358 $out = mssql_fetch_array($res);
359 $links['popular'][] = array($out['pagename'], $out['hits']);
366 // takes page name, list of links it contains
367 // the $linklist is an array where the keys are the page names
368 function SetWikiPageLinks($dbi, $pagename, $linklist) {
369 global $WikiLinksStore, $WikiScoreStore;
371 $frompage = addslashes($pagename);
373 // first delete the old list of links
374 mssql_query("delete from $WikiLinksStore where frompage='$frompage'",
377 // the page may not have links, return if not
378 if (! count($linklist))
380 // now insert the new list of links
381 while (list($topage, $count) = each($linklist)) {
382 $topage = addslashes($topage);
383 if($topage != $frompage) {
384 mssql_query("insert into $WikiLinksStore (frompage, topage) " .
385 "values ('$frompage', '$topage')", $dbi["dbc"]);
390 mssql_query("delete from $WikiScoreStore", $dbi["dbc"]);
391 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"]);
394 /* more mssql queries:
397 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;