From 2af368e58b497656df4a52c2e0c825275c70cafd Mon Sep 17 00:00:00 2001 From: wainstead Date: Fri, 2 Nov 2001 05:07:52 +0000 Subject: [PATCH] Missed these but they will be in the 1.2.2 release. Code for Microsoft SQL Server. git-svn-id: svn://svn.code.sf.net/p/phpwiki/code/branches/release-1_2-branch@594 96ab9672-09ca-45d6-a79d-3d69d39ca109 --- INSTALL.mssql | 574 ++++++++++++++++++++++++++++++++++++++++++++++++++ lib/mssql.php | 387 ++++++++++++++++++++++++++++++++++ 2 files changed, 961 insertions(+) create mode 100644 INSTALL.mssql create mode 100644 lib/mssql.php diff --git a/INSTALL.mssql b/INSTALL.mssql new file mode 100644 index 000000000..6f97aed54 --- /dev/null +++ b/INSTALL.mssql @@ -0,0 +1,574 @@ +From swain@panix.com Tue May 1 11:28:09 2001 -0400 +Return-Path: +Received: from avon0.barclayscapital.com (hermes.barclayscapital.com [141.228.4.66]) + by mail3.panix.com (Postfix) with SMTP id B1954983BC + for ; Tue, 1 May 2001 11:28:05 -0400 (EDT) +Received: from hermes.barclayscapital.com ([141.228.4.66]) by avon0.barclayscapital.com + via smtpd (for mail3.panix.com [166.84.0.167]) with SMTP; 1 May 2001 15:28:06 UT +Received: from avon1.barclayscapital.com (localhost [127.0.0.1]) + by hermes.barclayscapital.com with SMTP id QAA23136 + for ; Tue, 1 May 2001 16:31:29 +0100 (BST) +From: Andrew.Pearson@barclayscapital.com +Received: from exlpseg011.ldn.bzwint.com by avon1.barclayscapital.com + via smtpd (for hermesint.barclayscapital.com [141.228.4.34]) with SMTP; 1 May 2001 15:26:56 UT +Received: (private information removed) +Received: (private information removed) +Message-ID: <07F26630F244D311B6A300805F57215301B6825F@exlpseu011.ldn.bzwint.com> +To: swain@panix.com +Cc: phpwiki-talk@lists.sourceforge.net +Subject: PHPWiki with Microsoft SQL-Server +Date: Tue, 1 May 2001 16:26:50 +0100 +MIME-Version: 1.0 +X-Mailer: Internet Mail Service (5.5.2448.0) +Content-Type: multipart/mixed ; boundary="----_=_NextPart_000_01C0D253.2568B8F4" +X-Eagle-Notice: Sender not 8-bit clean in '\tby hermes.barclayscapital.com with SMTP\234 id QAA23136' +Status: RO +X-Status: A + +This message is in MIME format. Since your mail reader does not understand +this format, some or all of this message may not be legible. + +------_=_NextPart_000_01C0D253.2568B8F4 +Content-Type: text/plain; + charset="iso-8859-1" + + +My colleague John Clayton set up PHPWiki for our development team using +Apache and MySQL on Windows NT 4. He then left, and I was asked to port +this to IIS and SQLServer. Please note this is not a reflection of the +Apache and MySQL products, which were performing the task admirably, but had +more to do with consistency of our environment. Since PHP does work with +SQL-Server, the whole migration took about a day. Here are the steps I +carried out: + +1. Wrote a sql-server library called mssql.php to reside in wiki\lib. + <> +2. Added the following clause to wiki\lib\config.php + // MS SQLServer settings + } elseif ($WhichDatabase == 'mssql') { + $WikiPageStore = "wiki"; + $ArchivePageStore = "archive"; + $WikiLinksStore = "wikilinks"; + $WikiScoreStore = "wikiscore"; + $HitCountStore = "hitcount"; + $mssql_server = 'servername'; + $mssql_user = 'wikiweb'; + $mssql_pwd = 'wikiweb'; + $mssql_db = 'wiki'; + include "lib/mssql.php"; +} + +3. Set $WhichDatabase='mssql' in config.php + +4. Dumped out the mysql wiki database (mysqldump --user=john +--host=localhost wiki) and wrote the following perl script to convert to +sql-server compatible sql + <> + +5. Loaded the translated db script into SQL-Server and granted relevant +permissions/logins etc. + +6. Set "magic_quotes_sybase=On" in php.ini to handle embedded quote +characters in strings. This is because SQL-Server, like Sybase, uses '' +instead of \' within strings to cope with embedded quotes. + +We had some problems initially with the PHP extension dll for sql-server, +but I installed a newer version from http://www.mm4.de. In fact I unpacked +their whole php4.0.5-rc1 distribution. + +I make no claims about all this working 100%, but our existing site seems to +work okay in its new IIS/SQL-Server home :-) + +Andrew Pearson +Barclays Capital, UK + + + + +-------------------------------------------------------------------------------------- +For more information about Barclays Capital, please +visit our web site at http://www.barcap.com. + + +Internet communications are not secure and therefore the Barclays Group +does not accept legal responsibility for the contents of this message. +Any views or opinions presented are solely those of the author and do +not necessarily represent those of the Barclays Group unless otherwise +specifically stated. + +-------------------------------------------------------------------------------------- + +------_=_NextPart_000_01C0D253.2568B8F4 +Content-Type: application/octet-stream; + name="mssql.php" +Content-Transfer-Encoding: quoted-printable +Content-Disposition: attachment; + filename="mssql.php" + +"; + $msg .=3D sprintf(gettext ("MSSQL error: %s"), = +mssql_get_last_message()); + ExitWiki($msg); + } + // flush message + mssql_get_last_message(); + + if (!mssql_select_db($mssql_db, $dbc)) { + $msg =3D sprintf(gettext ("Cannot open database %s, giving = +up."), $mssql_db); + $msg .=3D "
"; + $msg .=3D sprintf(gettext ("MSSQL error: %s"), = +mssql_get_last_message()); + ExitWiki($msg); + } + // flush message + mssql_get_last_message(); + + $dbi['dbc'] =3D $dbc; + $dbi['table'] =3D $dbname; + return $dbi; + } + + + function CloseDataBase($dbi) { + // NOP function + // mssql connections are established as persistant + // they cannot be closed through mssql_close() + } + + + // prepare $pagehash for storing in mssql + function MakeDBHash($pagename, $pagehash) + { + $pagehash["pagename"] =3D addslashes($pagename); + if (!isset($pagehash["flags"])) + $pagehash["flags"] =3D 0; + $pagehash["author"] =3D addslashes($pagehash["author"]); + $pagehash["content"] =3D implode("\n", $pagehash["content"]); + $pagehash["content"] =3D addslashes($pagehash["content"]); + if (!isset($pagehash["refs"])) + $pagehash["refs"] =3D array(); + $pagehash["refs"] =3D serialize($pagehash["refs"]); +=20 + return $pagehash; + } + + + // convert mssql result $dbhash to $pagehash + function MakePageHash($dbhash) + { + // unserialize/explode content + $dbhash['refs'] =3D unserialize($dbhash['refs']); + $dbhash['content'] =3D explode("\n", $dbhash['content']); + return $dbhash; + } + + + // Return hash of page + attributes or default + function RetrievePage($dbi, $pagename, $pagestore) { + $pagename =3D addslashes($pagename); + if ($res =3D mssql_query("select * from $pagestore where = +pagename=3D'$pagename'", $dbi['dbc'])) { + if ($dbhash =3D mssql_fetch_array($res)) { + return MakePageHash($dbhash); + } + } + return -1; + } + + + // Either insert or replace a key/value (a page) + function InsertPage($dbi, $pagename, $pagehash) { + + $pagehash =3D MakeDBHash($pagename, $pagehash); + + // record the time of modification + $pagehash["lastmodified"] =3D time(); + + if (IsWikiPage($dbi, $pagename)) { + + $PAIRS =3D "author=3D'$pagehash[author]'," . + "content=3D'$pagehash[content]'," . + "created=3D$pagehash[created]," . + "flags=3D$pagehash[flags]," . + "lastmodified=3D$pagehash[lastmodified]," . + "pagename=3D'$pagehash[pagename]'," . + "refs=3D'$pagehash[refs]'," . + "version=3D$pagehash[version]"; + + $query =3D "UPDATE $dbi[table] SET $PAIRS WHERE = +pagename=3D'$pagename'"; + + } else { + // do an insert + // build up the column names and values for the query + + $COLUMNS =3D "author, content, created, flags, lastmodified, " = +. + "pagename, refs, version"; + + $VALUES =3D "'$pagehash[author]', '$pagehash[content]', " . + "$pagehash[created], $pagehash[flags], " . + "$pagehash[lastmodified], '$pagehash[pagename]', " = +. + "'$pagehash[refs]', $pagehash[version]"; + + + $query =3D "INSERT INTO $dbi[table] ($COLUMNS) = +VALUES($VALUES)"; + } + + //echo "

Insert/Update Query: $query

\n"; + + $retval =3D mssql_query($query); + if ($retval =3D=3D false) { + printf(gettext ("Insert/Update failed: %s
\n"), = +mssql_get_last_message()); + } + } + + + // for archiving pages to a seperate dbm + function SaveCopyToArchive($dbi, $pagename, $pagehash) { + global $ArchivePageStore; + $adbi =3D OpenDataBase($ArchivePageStore); + InsertPage($adbi, $pagename, $pagehash); + } + + + function IsWikiPage($dbi, $pagename) { + $pagename =3D addslashes($pagename); + if ($res =3D mssql_query("select count(*) from $dbi[table] where = +pagename=3D'$pagename'", $dbi['dbc'])) { + return(mssql_result($res, 0, 0)); + } + return 0; + } + + function IsInArchive($dbi, $pagename) { + global $ArchivePageStore; + + $pagename =3D addslashes($pagename); + if ($res =3D mssql_query("select count(*) from $ArchivePageStore = +where pagename=3D'$pagename'", $dbi['dbc'])) { + return(mssql_result($res, 0, 0)); + } + return 0; + } + + + function RemovePage($dbi, $pagename) { + global $WikiPageStore, $ArchivePageStore; + global $WikiLinksStore, $HitCountStore, $WikiScoreStore; + + $pagename =3D addslashes($pagename); + $msg =3D gettext ("Cannot delete '%s' from table '%s'"); + $msg .=3D "
\n"; + $msg .=3D gettext ("MSSQL error: %s"); + + if (!mssql_query("delete from $WikiPageStore where = +pagename=3D'$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiPageStore, = +mssql_get_last_message())); + + if (!mssql_query("delete from $ArchivePageStore where = +pagename=3D'$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, = +mssql_get_last_message())); + + if (!mssql_query("delete from $WikiLinksStore where = +frompage=3D'$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, = +mssql_get_last_message())); + + if (!mssql_query("delete from $HitCountStore where = +pagename=3D'$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $HitCountStore, = +mssql_get_last_message())); + + if (!mssql_query("delete from $WikiScoreStore where = +pagename=3D'$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, = +mssql_get_last_message())); + } + + + function IncreaseHitCount($dbi, $pagename) + { + global $HitCountStore; + + $rowexists =3D 0; + if ($res =3D mssql_query("select count(*) from $dbi[table] where = +pagename=3D'$pagename'", $dbi['dbc'])) { + $rowexists =3D (mssql_result($res, 0, 0)); + } + + if ($rowexists) + $res =3D mssql_query("update $HitCountStore set hits=3Dhits+1 = +where pagename=3D'$pagename'", $dbi['dbc']); + else + $res =3D mssql_query("insert into $HitCountStore (pagename, hits) = +values ('$pagename', 1)", $dbi['dbc']); + + return $res; + } + + function GetHitCount($dbi, $pagename) + { + global $HitCountStore; + + $res =3D mssql_query("select hits from $HitCountStore where = +pagename=3D'$pagename'", $dbi['dbc']); + if (mssql_num_rows($res)) + $hits =3D mssql_result($res, 0, 0); + else + $hits =3D "0"; + + return $hits; + } + + function MakeSQLSearchClause($search, $column) + { + $search =3D addslashes(preg_replace("/\s+/", " ", $search)); + $term =3D strtok($search, ' '); + $clause =3D ''; + while($term) { + $word =3D "$term"; + if ($word[0] =3D=3D '-') { + $word =3D substr($word, 1); + $clause .=3D "not ($column like '%$word%') "; + } else { + $clause .=3D "($column like '%$word%') "; + } + if ($term =3D strtok(' ')) + $clause .=3D 'and '; + } + return $clause; + } + + // setup for title-search + function InitTitleSearch($dbi, $search) { + $clause =3D MakeSQLSearchClause($search, 'pagename'); + $res =3D mssql_query("select pagename from $dbi[table] where = +$clause order by pagename", $dbi["dbc"]); + + return $res; + } + + + // iterating through database + function TitleSearchNextMatch($dbi, $res) { + if($o =3D mssql_fetch_object($res)) { + return $o->pagename; + } + else { + return 0; + } + } + + + // setup for full-text search + function InitFullSearch($dbi, $search) { + $clause =3D MakeSQLSearchClause($search, 'content'); + $res =3D mssql_query("select * from $dbi[table] where $clause", = +$dbi["dbc"]); + + return $res; + } + + // iterating through database + function FullSearchNextMatch($dbi, $res) { + if($hash =3D mssql_fetch_array($res)) { + return MakePageHash($hash); + } + else { + return 0; + } + } + + function InitMostPopular($dbi, $limit) { + global $HitCountStore; + $res =3D mssql_query("select top $limit * from $HitCountStore = +order by hits desc, pagename", $dbi["dbc"]); + =20 + return $res; + } + + function MostPopularNextMatch($dbi, $res) { + if ($hits =3D mssql_fetch_array($res)) + return $hits; + else + return 0; + } + + function GetAllWikiPageNames($dbi) { + global $WikiPageStore; + $res =3D mssql_query("select pagename from $WikiPageStore", = +$dbi["dbc"]); + $rows =3D mssql_num_rows($res); + for ($i =3D 0; $i < $rows; $i++) { + $pages[$i] =3D mssql_result($res, $i, 0); + } + return $pages; + } + =20 + =20 + //////////////////////////////////////// + // functionality for the wikilinks table + + // takes a page name, returns array of scored incoming and outgoing = +links + function GetWikiPageLinks($dbi, $pagename) { + global $WikiLinksStore, $WikiScoreStore, $HitCountStore; + + $pagename =3D addslashes($pagename); + $res =3D mssql_query("select topage, score from $WikiLinksStore, = +$WikiScoreStore where topage=3Dpagename and frompage=3D'$pagename' = +order by score desc, topage"); + $rows =3D mssql_num_rows($res); + for ($i =3D 0; $i < $rows; $i++) { + $out =3D mssql_fetch_array($res); + $links['out'][] =3D array($out['topage'], $out['score']); + } + + $res =3D mssql_query("select frompage, score from = +$WikiLinksStore, $WikiScoreStore where frompage=3Dpagename and = +topage=3D'$pagename' order by score desc, frompage"); + $rows =3D mssql_num_rows($res); + for ($i =3D 0; $i < $rows; $i++) { + $out =3D mssql_fetch_array($res); + $links['in'][] =3D array($out['frompage'], $out['score']); + } + + $res =3D mssql_query("select distinct pagename, hits from = +$WikiLinksStore, $HitCountStore where (frompage=3Dpagename and = +topage=3D'$pagename') or (topage=3Dpagename and frompage=3D'$pagename') = +order by hits desc, pagename"); + $rows =3D mssql_num_rows($res); + for ($i =3D 0; $i < $rows; $i++) { + $out =3D mssql_fetch_array($res); + $links['popular'][] =3D array($out['pagename'], $out['hits']); + } + + return $links; + } + + + // takes page name, list of links it contains + // the $linklist is an array where the keys are the page names + function SetWikiPageLinks($dbi, $pagename, $linklist) { + global $WikiLinksStore, $WikiScoreStore; + + $frompage =3D addslashes($pagename); + + // first delete the old list of links + mssql_query("delete from $WikiLinksStore where = +frompage=3D'$frompage'", + $dbi["dbc"]); + + // the page may not have links, return if not + if (! count($linklist)) + return; + // now insert the new list of links + while (list($topage, $count) =3D each($linklist)) { + $topage =3D addslashes($topage); + if($topage !=3D $frompage) { + mssql_query("insert into $WikiLinksStore (frompage, topage) = +" . + "values ('$frompage', '$topage')", $dbi["dbc"]); + } + } + + // update pagescore + mssql_query("delete from $WikiScoreStore", $dbi["dbc"]); + mssql_query("insert into $WikiScoreStore select w1.topage, = +count(*) from $WikiLinksStore as w1, $WikiLinksStore as w2 where = +w2.topage=3Dw1.frompage group by w1.topage", $dbi["dbc"]); + } + +/* more mssql queries: + +orphans: +select pagename from wiki left join wikilinks on pagename=3Dtopage = +where topage is NULL; +*/ +?> + +------_=_NextPart_000_01C0D253.2568B8F4 +Content-Type: application/octet-stream; + name="translate_mysql.pl" +Content-Disposition: attachment; + filename="translate_mysql.pl" + + +# Convert MySQL wiki database dump to a Microsoft SQL-Server compatible SQL script +# NB This is not a general-purpose MySQL->SQL-Server conversion script + +# Author: Andrew K. Pearson +# Date: 01 May 2001 + +# Example usage: perl translate_mysql.pl dump.sql > dump2.sql + +# NB I did not use sed because the version I have is limited to input lines of <1K in size + +while (<>) +{ + $newvalue = $_; + + $newvalue =~ s/\\\"/\'\'/g; + $newvalue =~ s/\\\'/\'\'/g; + $newvalue =~ s/\\n/\'+char(10)+\'/g; + $newvalue =~ s/TYPE=MyISAM;//g; + $newvalue =~ s/int\(.+\)/int/g; + $newvalue =~ s/mediumtext/text/g; + $newvalue =~ s/^#/--/g; + + print $newvalue; +} + + +------_=_NextPart_000_01C0D253.2568B8F4-- + diff --git a/lib/mssql.php b/lib/mssql.php new file mode 100644 index 000000000..9fc57f67a --- /dev/null +++ b/lib/mssql.php @@ -0,0 +1,387 @@ +"; + $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message()); + ExitWiki($msg); + } + // flush message + mssql_get_last_message(); + + if (!mssql_select_db($mssql_db, $dbc)) { + $msg = sprintf(gettext ("Cannot open database %s, giving up."), $mssql_db); + $msg .= "
"; + $msg .= sprintf(gettext ("MSSQL error: %s"), mssql_get_last_message()); + ExitWiki($msg); + } + // flush message + mssql_get_last_message(); + + $dbi['dbc'] = $dbc; + $dbi['table'] = $dbname; + return $dbi; + } + + + function CloseDataBase($dbi) { + // NOP function + // mssql connections are established as persistant + // they cannot be closed through mssql_close() + } + + + // prepare $pagehash for storing in mssql + function MakeDBHash($pagename, $pagehash) + { + $pagehash["pagename"] = addslashes($pagename); + if (!isset($pagehash["flags"])) + $pagehash["flags"] = 0; + $pagehash["author"] = addslashes($pagehash["author"]); + $pagehash["content"] = implode("\n", $pagehash["content"]); + $pagehash["content"] = addslashes($pagehash["content"]); + if (!isset($pagehash["refs"])) + $pagehash["refs"] = array(); + $pagehash["refs"] = serialize($pagehash["refs"]); + + return $pagehash; + } + + + // convert mssql result $dbhash to $pagehash + function MakePageHash($dbhash) + { + // unserialize/explode content + $dbhash['refs'] = unserialize($dbhash['refs']); + $dbhash['content'] = explode("\n", $dbhash['content']); + return $dbhash; + } + + + // Return hash of page + attributes or default + function RetrievePage($dbi, $pagename, $pagestore) { + $pagename = addslashes($pagename); + if ($res = mssql_query("select * from $pagestore where pagename='$pagename'", $dbi['dbc'])) { + if ($dbhash = mssql_fetch_array($res)) { + return MakePageHash($dbhash); + } + } + return -1; + } + + + // Either insert or replace a key/value (a page) + function InsertPage($dbi, $pagename, $pagehash) { + + $pagehash = MakeDBHash($pagename, $pagehash); + + // record the time of modification + $pagehash["lastmodified"] = time(); + + if (IsWikiPage($dbi, $pagename)) { + + $PAIRS = "author='$pagehash[author]'," . + "content='$pagehash[content]'," . + "created=$pagehash[created]," . + "flags=$pagehash[flags]," . + "lastmodified=$pagehash[lastmodified]," . + "pagename='$pagehash[pagename]'," . + "refs='$pagehash[refs]'," . + "version=$pagehash[version]"; + + $query = "UPDATE $dbi[table] SET $PAIRS WHERE pagename='$pagename'"; + + } else { + // do an insert + // build up the column names and values for the query + + $COLUMNS = "author, content, created, flags, lastmodified, " . + "pagename, refs, version"; + + $VALUES = "'$pagehash[author]', '$pagehash[content]', " . + "$pagehash[created], $pagehash[flags], " . + "$pagehash[lastmodified], '$pagehash[pagename]', " . + "'$pagehash[refs]', $pagehash[version]"; + + + $query = "INSERT INTO $dbi[table] ($COLUMNS) VALUES($VALUES)"; + } + + //echo "

Insert/Update Query: $query

\n"; + + $retval = mssql_query($query); + if ($retval == false) { + printf(gettext ("Insert/Update failed: %s
\n"), mssql_get_last_message()); + } + } + + + // for archiving pages to a seperate dbm + function SaveCopyToArchive($dbi, $pagename, $pagehash) { + global $ArchivePageStore; + $adbi = OpenDataBase($ArchivePageStore); + InsertPage($adbi, $pagename, $pagehash); + } + + + function IsWikiPage($dbi, $pagename) { + $pagename = addslashes($pagename); + if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) { + return(mssql_result($res, 0, 0)); + } + return 0; + } + + function IsInArchive($dbi, $pagename) { + global $ArchivePageStore; + + $pagename = addslashes($pagename); + if ($res = mssql_query("select count(*) from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) { + return(mssql_result($res, 0, 0)); + } + return 0; + } + + + function RemovePage($dbi, $pagename) { + global $WikiPageStore, $ArchivePageStore; + global $WikiLinksStore, $HitCountStore, $WikiScoreStore; + + $pagename = addslashes($pagename); + $msg = gettext ("Cannot delete '%s' from table '%s'"); + $msg .= "
\n"; + $msg .= gettext ("MSSQL error: %s"); + + if (!mssql_query("delete from $WikiPageStore where pagename='$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiPageStore, mssql_get_last_message())); + + if (!mssql_query("delete from $ArchivePageStore where pagename='$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, mssql_get_last_message())); + + if (!mssql_query("delete from $WikiLinksStore where frompage='$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, mssql_get_last_message())); + + if (!mssql_query("delete from $HitCountStore where pagename='$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $HitCountStore, mssql_get_last_message())); + + if (!mssql_query("delete from $WikiScoreStore where pagename='$pagename'", $dbi['dbc'])) + ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, mssql_get_last_message())); + } + + + function IncreaseHitCount($dbi, $pagename) + { + global $HitCountStore; + + $rowexists = 0; + if ($res = mssql_query("select count(*) from $dbi[table] where pagename='$pagename'", $dbi['dbc'])) { + $rowexists = (mssql_result($res, 0, 0)); + } + + if ($rowexists) + $res = mssql_query("update $HitCountStore set hits=hits+1 where pagename='$pagename'", $dbi['dbc']); + else + $res = mssql_query("insert into $HitCountStore (pagename, hits) values ('$pagename', 1)", $dbi['dbc']); + + return $res; + } + + function GetHitCount($dbi, $pagename) + { + global $HitCountStore; + + $res = mssql_query("select hits from $HitCountStore where pagename='$pagename'", $dbi['dbc']); + if (mssql_num_rows($res)) + $hits = mssql_result($res, 0, 0); + else + $hits = "0"; + + return $hits; + } + + function MakeSQLSearchClause($search, $column) + { + $search = addslashes(preg_replace("/\s+/", " ", $search)); + $term = strtok($search, ' '); + $clause = ''; + while($term) { + $word = "$term"; + if ($word[0] == '-') { + $word = substr($word, 1); + $clause .= "not ($column like '%$word%') "; + } else { + $clause .= "($column like '%$word%') "; + } + if ($term = strtok(' ')) + $clause .= 'and '; + } + return $clause; + } + + // setup for title-search + function InitTitleSearch($dbi, $search) { + $clause = MakeSQLSearchClause($search, 'pagename'); + $res = mssql_query("select pagename from $dbi[table] where $clause order by pagename", $dbi["dbc"]); + + return $res; + } + + + // iterating through database + function TitleSearchNextMatch($dbi, $res) { + if($o = mssql_fetch_object($res)) { + return $o->pagename; + } + else { + return 0; + } + } + + + // setup for full-text search + function InitFullSearch($dbi, $search) { + $clause = MakeSQLSearchClause($search, 'content'); + $res = mssql_query("select * from $dbi[table] where $clause", $dbi["dbc"]); + + return $res; + } + + // iterating through database + function FullSearchNextMatch($dbi, $res) { + if($hash = mssql_fetch_array($res)) { + return MakePageHash($hash); + } + else { + return 0; + } + } + + function InitMostPopular($dbi, $limit) { + global $HitCountStore; + $res = mssql_query("select top $limit * from $HitCountStore order by hits desc, pagename", $dbi["dbc"]); + + return $res; + } + + function MostPopularNextMatch($dbi, $res) { + if ($hits = mssql_fetch_array($res)) + return $hits; + else + return 0; + } + + function GetAllWikiPageNames($dbi) { + global $WikiPageStore; + $res = mssql_query("select pagename from $WikiPageStore", $dbi["dbc"]); + $rows = mssql_num_rows($res); + for ($i = 0; $i < $rows; $i++) { + $pages[$i] = mssql_result($res, $i, 0); + } + return $pages; + } + + + //////////////////////////////////////// + // functionality for the wikilinks table + + // takes a page name, returns array of scored incoming and outgoing links + function GetWikiPageLinks($dbi, $pagename) { + global $WikiLinksStore, $WikiScoreStore, $HitCountStore; + + $pagename = addslashes($pagename); + $res = mssql_query("select topage, score from $WikiLinksStore, $WikiScoreStore where topage=pagename and frompage='$pagename' order by score desc, topage"); + $rows = mssql_num_rows($res); + for ($i = 0; $i < $rows; $i++) { + $out = mssql_fetch_array($res); + $links['out'][] = array($out['topage'], $out['score']); + } + + $res = mssql_query("select frompage, score from $WikiLinksStore, $WikiScoreStore where frompage=pagename and topage='$pagename' order by score desc, frompage"); + $rows = mssql_num_rows($res); + for ($i = 0; $i < $rows; $i++) { + $out = mssql_fetch_array($res); + $links['in'][] = array($out['frompage'], $out['score']); + } + + $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"); + $rows = mssql_num_rows($res); + for ($i = 0; $i < $rows; $i++) { + $out = mssql_fetch_array($res); + $links['popular'][] = array($out['pagename'], $out['hits']); + } + + return $links; + } + + + // takes page name, list of links it contains + // the $linklist is an array where the keys are the page names + function SetWikiPageLinks($dbi, $pagename, $linklist) { + global $WikiLinksStore, $WikiScoreStore; + + $frompage = addslashes($pagename); + + // first delete the old list of links + mssql_query("delete from $WikiLinksStore where frompage='$frompage'", + $dbi["dbc"]); + + // the page may not have links, return if not + if (! count($linklist)) + return; + // now insert the new list of links + while (list($topage, $count) = each($linklist)) { + $topage = addslashes($topage); + if($topage != $frompage) { + mssql_query("insert into $WikiLinksStore (frompage, topage) " . + "values ('$frompage', '$topage')", $dbi["dbc"]); + } + } + + // update pagescore + mssql_query("delete from $WikiScoreStore", $dbi["dbc"]); + 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"]); + } + +/* more mssql queries: + +orphans: +select pagename from wiki left join wikilinks on pagename=topage where topage is NULL; +*/ +?> -- 2.45.0