From 9e736e039d32bb05308d773537aff925328eaddd Mon Sep 17 00:00:00 2001 From: wainstead Date: Fri, 2 Nov 2001 05:15:05 +0000 Subject: [PATCH] Cleaned it up and added some hints. git-svn-id: svn://svn.code.sf.net/p/phpwiki/code/branches/release-1_2-branch@596 96ab9672-09ca-45d6-a79d-3d69d39ca109 --- INSTALL.mssql | 513 ++------------------------------------------------ 1 file changed, 12 insertions(+), 501 deletions(-) diff --git a/INSTALL.mssql b/INSTALL.mssql index 6f97aed54..b9dac16c4 100644 --- a/INSTALL.mssql +++ b/INSTALL.mssql @@ -1,36 +1,21 @@ -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) +Note: this is the email I got when the files were contributed. I cannot test +the code, since I don't have access to mssql, so ymmv. The files referenced +here are at: + +lib/mssql.php +admin/translate_mysql.pl (you probably won't need this) + +The code for lib/config.php has already been added. + +~swain + + 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 @@ -98,477 +83,3 @@ 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-- - -- 2.45.2