1 <?php rcs_id('$Id: mssql.php,v 1.1.2.3 2001-11-04 03:43:36 dairiki Exp $');
\r
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 global $WikiPageStore; // ugly hack
\r
114 if ($dbi['table'] == $WikiPageStore)
\r
116 $linklist = ExtractWikiPageLinks($pagehash['content']);
\r
117 SetWikiPageLinks($dbi, $pagename, $linklist);
\r
120 $pagehash = MakeDBHash($pagename, $pagehash);
\r
122 // record the time of modification
\r
123 $pagehash["lastmodified"] = time();
\r
125 if (IsWikiPage($dbi, $pagename)) {
\r
127 $PAIRS = "author='$pagehash[author]'," .
\r
128 "content='$pagehash[content]'," .
\r
129 "created=$pagehash[created]," .
\r
130 "flags=$pagehash[flags]," .
\r
131 "lastmodified=$pagehash[lastmodified]," .
\r
132 "pagename='$pagehash[pagename]'," .
\r
133 "refs='$pagehash[refs]'," .
\r
134 "version=$pagehash[version]";
\r
136 $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'";
\r
140 // build up the column names and values for the query
\r
142 $COLUMNS = "author, content, created, flags, lastmodified, " .
\r
143 "pagename, refs, version";
\r
145 $VALUES = "'$pagehash[author]', '$pagehash[content]', " .
\r
146 "$pagehash[created], $pagehash[flags], " .
\r
147 "$pagehash[lastmodified], '$pagehash[pagename]', " .
\r
148 "'$pagehash[refs]', $pagehash[version]";
\r
151 $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)";
\r
154 //echo "<p>Insert/Update Query: $query<p>\n";
\r
156 $retval = mssql_query($query);
\r
157 if ($retval == false) {
\r
158 printf(gettext ("Insert/Update failed: %s <br>\n"), mssql_get_last_message());
\r
163 // for archiving pages to a seperate dbm
\r
164 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
\r
165 global $ArchivePageStore;
\r
166 $adbi = OpenDataBase($ArchivePageStore);
\r
167 InsertPage($adbi, $pagename, $pagehash);
\r
171 function IsWikiPage($dbi, $pagename) {
\r
172 $pagename = addslashes($pagename);
\r
173 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
\r
174 return(mssql_result($res, 0, 0));
\r
179 function IsInArchive($dbi, $pagename) {
\r
180 global $ArchivePageStore;
\r
182 $pagename = addslashes($pagename);
\r
183 if ($res = mssql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) {
\r
184 return(mssql_result($res, 0, 0));
\r
190 function RemovePage($dbi, $pagename) {
\r
191 global $WikiPageStore, $ArchivePageStore;
\r
192 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
\r
194 $pagename = addslashes($pagename);
\r
195 $msg = gettext ("Cannot delete '%s' from table '%s'");
\r
197 $msg .= gettext ("MSSQL error: %s");
\r
199 if (!mssql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc']))
\r
200 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mssql_get_last_message()));
\r
202 if (!mssql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc']))
\r
203 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mssql_get_last_message()));
\r
205 if (!mssql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc']))
\r
206 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mssql_get_last_message()));
\r
208 if (!mssql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc']))
\r
209 ExitWiki(sprintf($msg, $pagename, $HitCountStore, mssql_get_last_message()));
\r
211 if (!mssql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc']))
\r
212 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mssql_get_last_message()));
\r
216 function IncreaseHitCount($dbi, $pagename)
\r
218 global $HitCountStore;
\r
221 if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) {
\r
222 $rowexists = (mssql_result($res, 0, 0));
\r
226 $res = mssql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']);
\r
228 $res = mssql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']);
\r
233 function GetHitCount($dbi, $pagename)
\r
235 global $HitCountStore;
\r
237 $res = mssql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']);
\r
238 if (mssql_num_rows($res))
\r
239 $hits = mssql_result($res, 0, 0);
\r
246 function MakeSQLSearchClause($search, $column)
\r
248 $search = addslashes(preg_replace("/\s+/", " ", $search));
\r
249 $term = strtok($search, ' ');
\r
253 if ($word[0] == '-') {
\r
254 $word = substr($word, 1);
\r
255 $clause .= "not ($column like '%$word%') ";
\r
257 $clause .= "($column like '%$word%') ";
\r
259 if ($term = strtok(' '))
\r
265 // setup for title-search
\r
266 function InitTitleSearch($dbi, $search) {
\r
267 $clause = MakeSQLSearchClause($search, 'pagename');
\r
268 $res = mssql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]);
\r
274 // iterating through database
\r
275 function TitleSearchNextMatch($dbi, $res) {
\r
276 if($o = mssql_fetch_object($res)) {
\r
277 return $o->pagename;
\r
285 // setup for full-text search
\r
286 function InitFullSearch($dbi, $search) {
\r
287 $clause = MakeSQLSearchClause($search, 'content');
\r
288 $res = mssql_query("select * from $dbi[table] where $clause", $dbi["dbc"]);
\r
293 // iterating through database
\r
294 function FullSearchNextMatch($dbi, $res) {
\r
295 if($hash = mssql_fetch_array($res)) {
\r
296 return MakePageHash($hash);
\r
303 function InitMostPopular($dbi, $limit) {
\r
304 global $HitCountStore;
\r
305 $res = mssql_query("select top $limit * from $HitCountStore order by hits desc, pagename", $dbi["dbc"]);
\r
310 function MostPopularNextMatch($dbi, $res) {
\r
311 if ($hits = mssql_fetch_array($res))
\r
317 function GetAllWikiPageNames($dbi) {
\r
318 global $WikiPageStore;
\r
319 $res = mssql_query("select pagename from $WikiPageStore", $dbi["dbc"]);
\r
320 $rows = mssql_num_rows($res);
\r
321 for ($i = 0; $i < $rows; $i++) {
\r
322 $pages[$i] = mssql_result($res, $i, 0);
\r
328 ////////////////////////////////////////
\r
329 // functionality for the wikilinks table
\r
331 // takes a page name, returns array of scored incoming and outgoing links
\r
332 function GetWikiPageLinks($dbi, $pagename) {
\r
333 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
\r
335 $pagename = addslashes($pagename);
\r
336 $res = mssql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage");
\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['out'][] = array($out['topage'], $out['score']);
\r
343 $res = mssql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage");
\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['in'][] = array($out['frompage'], $out['score']);
\r
350 $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
351 $rows = mssql_num_rows($res);
\r
352 for ($i = 0; $i < $rows; $i++) {
\r
353 $out = mssql_fetch_array($res);
\r
354 $links['popular'][] = array($out['pagename'], $out['hits']);
\r
361 // takes page name, list of links it contains
\r
362 // the $linklist is an array where the keys are the page names
\r
363 function SetWikiPageLinks($dbi, $pagename, $linklist) {
\r
364 global $WikiLinksStore, $WikiScoreStore;
\r
366 $frompage = addslashes($pagename);
\r
368 // first delete the old list of links
\r
369 mssql_query("delete from $WikiLinksStore where frompage='$frompage'",
\r
372 // the page may not have links, return if not
\r
373 if (! count($linklist))
\r
375 // now insert the new list of links
\r
376 while (list($topage, $count) = each($linklist)) {
\r
377 $topage = addslashes($topage);
\r
378 if($topage != $frompage) {
\r
379 mssql_query("insert into $WikiLinksStore (frompage, topage) " .
\r
380 "values ('$frompage', '$topage')", $dbi["dbc"]);
\r
384 // update pagescore
\r
385 mssql_query("delete from $WikiScoreStore", $dbi["dbc"]);
\r
386 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
389 /* more mssql queries:
\r
392 select pagename from wiki left join wikilinks on pagename=topage where topage is NULL;
\r