1 From swain@panix.com Tue May 1 11:28:09 2001 -0400
2 Return-Path: <Andrew.Pearson@barclayscapital.com>
3 Received: from avon0.barclayscapital.com (hermes.barclayscapital.com [141.228.4.66])
4 by mail3.panix.com (Postfix) with SMTP id B1954983BC
5 for <swain@panix.com>; Tue, 1 May 2001 11:28:05 -0400 (EDT)
6 Received: from hermes.barclayscapital.com ([141.228.4.66]) by avon0.barclayscapital.com
7 via smtpd (for mail3.panix.com [166.84.0.167]) with SMTP; 1 May 2001 15:28:06 UT
8 Received: from avon1.barclayscapital.com (localhost [127.0.0.1])
9 by hermes.barclayscapital.com with SMTP id QAA23136
10 for <swain@panix.com>; Tue, 1 May 2001 16:31:29 +0100 (BST)
11 From: Andrew.Pearson@barclayscapital.com
12 Received: from exlpseg011.ldn.bzwint.com by avon1.barclayscapital.com
13 via smtpd (for hermesint.barclayscapital.com [141.228.4.34]) with SMTP; 1 May 2001 15:26:56 UT
14 Received: (private information removed)
15 Received: (private information removed)
16 Message-ID: <07F26630F244D311B6A300805F57215301B6825F@exlpseu011.ldn.bzwint.com>
18 Cc: phpwiki-talk@lists.sourceforge.net
19 Subject: PHPWiki with Microsoft SQL-Server
20 Date: Tue, 1 May 2001 16:26:50 +0100
22 X-Mailer: Internet Mail Service (5.5.2448.0)
23 Content-Type: multipart/mixed ; boundary="----_=_NextPart_000_01C0D253.2568B8F4"
24 X-Eagle-Notice: Sender not 8-bit clean in '\tby hermes.barclayscapital.com with SMTP\234 id QAA23136'
28 This message is in MIME format. Since your mail reader does not understand
29 this format, some or all of this message may not be legible.
31 ------_=_NextPart_000_01C0D253.2568B8F4
32 Content-Type: text/plain;
36 My colleague John Clayton set up PHPWiki for our development team using
37 Apache and MySQL on Windows NT 4. He then left, and I was asked to port
38 this to IIS and SQLServer. Please note this is not a reflection of the
39 Apache and MySQL products, which were performing the task admirably, but had
40 more to do with consistency of our environment. Since PHP does work with
41 SQL-Server, the whole migration took about a day. Here are the steps I
44 1. Wrote a sql-server library called mssql.php to reside in wiki\lib.
46 2. Added the following clause to wiki\lib\config.php
47 // MS SQLServer settings
48 } elseif ($WhichDatabase == 'mssql') {
49 $WikiPageStore = "wiki";
50 $ArchivePageStore = "archive";
51 $WikiLinksStore = "wikilinks";
52 $WikiScoreStore = "wikiscore";
53 $HitCountStore = "hitcount";
54 $mssql_server = 'servername';
55 $mssql_user = 'wikiweb';
56 $mssql_pwd = 'wikiweb';
58 include "lib/mssql.php";
61 3. Set $WhichDatabase='mssql' in config.php
63 4. Dumped out the mysql wiki database (mysqldump --user=john
64 --host=localhost wiki) and wrote the following perl script to convert to
65 sql-server compatible sql
66 <<translate_mysql.pl>>
68 5. Loaded the translated db script into SQL-Server and granted relevant
69 permissions/logins etc.
71 6. Set "magic_quotes_sybase=On" in php.ini to handle embedded quote
72 characters in strings. This is because SQL-Server, like Sybase, uses ''
73 instead of \' within strings to cope with embedded quotes.
75 We had some problems initially with the PHP extension dll for sql-server,
76 but I installed a newer version from http://www.mm4.de. In fact I unpacked
77 their whole php4.0.5-rc1 distribution.
79 I make no claims about all this working 100%, but our existing site seems to
80 work okay in its new IIS/SQL-Server home :-)
88 --------------------------------------------------------------------------------------
89 For more information about Barclays Capital, please
90 visit our web site at http://www.barcap.com.
93 Internet communications are not secure and therefore the Barclays Group
94 does not accept legal responsibility for the contents of this message.
95 Any views or opinions presented are solely those of the author and do
96 not necessarily represent those of the Barclays Group unless otherwise
99 --------------------------------------------------------------------------------------
101 ------_=_NextPart_000_01C0D253.2568B8F4
102 Content-Type: application/octet-stream;
104 Content-Transfer-Encoding: quoted-printable
105 Content-Disposition: attachment;
110 /* Microsoft SQL-Server library for PHPWiki
111 Author: Andrew K. Pearson
117 OpenDataBase($dbname)
119 MakeDBHash($pagename, $pagehash)
120 MakePageHash($dbhash)
121 RetrievePage($dbi, $pagename, $pagestore)
122 InsertPage($dbi, $pagename, $pagehash)
123 SaveCopyToArchive($dbi, $pagename, $pagehash)
124 IsWikiPage($dbi, $pagename)
125 IsInArchive($dbi, $pagename)
126 RemovePage($dbi, $pagename)
127 IncreaseHitCount($dbi, $pagename)
128 GetHitCount($dbi, $pagename)
129 MakeSQLSearchClause($search, $column)
130 InitTitleSearch($dbi, $search)
131 TitleSearchNextMatch($dbi, $res)
132 InitFullSearch($dbi, $search)
133 FullSearchNextMatch($dbi, $res)
134 InitMostPopular($dbi, $limit)
135 MostPopularNextMatch($dbi, $res)
136 GetAllWikiPageNames($dbi)
137 GetWikiPageLinks($dbi, $pagename)
138 SetWikiPageLinks($dbi, $pagename, $linklist)
141 // open a database and return the handle
142 // ignores MAX_DBM_ATTEMPTS
144 function OpenDataBase($dbname) {
145 global $mssql_server, $mssql_user, $mssql_pwd, $mssql_db;
147 if (!($dbc =3D mssql_pconnect($mssql_server, $mssql_user, =
149 $msg =3D gettext ("Cannot establish connection to database, =
152 $msg .=3D sprintf(gettext ("MSSQL error: %s"), =
153 mssql_get_last_message());
157 mssql_get_last_message();
159 if (!mssql_select_db($mssql_db, $dbc)) {
160 $msg =3D sprintf(gettext ("Cannot open database %s, giving =
163 $msg .=3D sprintf(gettext ("MSSQL error: %s"), =
164 mssql_get_last_message());
168 mssql_get_last_message();
170 $dbi['dbc'] =3D $dbc;
171 $dbi['table'] =3D $dbname;
176 function CloseDataBase($dbi) {
178 // mssql connections are established as persistant
179 // they cannot be closed through mssql_close()
183 // prepare $pagehash for storing in mssql
184 function MakeDBHash($pagename, $pagehash)
186 $pagehash["pagename"] =3D addslashes($pagename);
187 if (!isset($pagehash["flags"]))
188 $pagehash["flags"] =3D 0;
189 $pagehash["author"] =3D addslashes($pagehash["author"]);
190 $pagehash["content"] =3D implode("\n", $pagehash["content"]);
191 $pagehash["content"] =3D addslashes($pagehash["content"]);
192 if (!isset($pagehash["refs"]))
193 $pagehash["refs"] =3D array();
194 $pagehash["refs"] =3D serialize($pagehash["refs"]);
200 // convert mssql result $dbhash to $pagehash
201 function MakePageHash($dbhash)
203 // unserialize/explode content
204 $dbhash['refs'] =3D unserialize($dbhash['refs']);
205 $dbhash['content'] =3D explode("\n", $dbhash['content']);
210 // Return hash of page + attributes or default
211 function RetrievePage($dbi, $pagename, $pagestore) {
212 $pagename =3D addslashes($pagename);
213 if ($res =3D mssql_query("select * from $pagestore where =
214 pagename=3D'$pagename'", $dbi['dbc'])) {
215 if ($dbhash =3D mssql_fetch_array($res)) {
216 return MakePageHash($dbhash);
223 // Either insert or replace a key/value (a page)
224 function InsertPage($dbi, $pagename, $pagehash) {
226 $pagehash =3D MakeDBHash($pagename, $pagehash);
228 // record the time of modification
229 $pagehash["lastmodified"] =3D time();
231 if (IsWikiPage($dbi, $pagename)) {
233 $PAIRS =3D "author=3D'$pagehash[author]'," .
234 "content=3D'$pagehash[content]'," .
235 "created=3D$pagehash[created]," .
236 "flags=3D$pagehash[flags]," .
237 "lastmodified=3D$pagehash[lastmodified]," .
238 "pagename=3D'$pagehash[pagename]'," .
239 "refs=3D'$pagehash[refs]'," .
240 "version=3D$pagehash[version]";
242 $query =3D "UPDATE $dbi[table] SET $PAIRS WHERE =
243 pagename=3D'$pagename'";
247 // build up the column names and values for the query
249 $COLUMNS =3D "author, content, created, flags, lastmodified, " =
251 "pagename, refs, version";
253 $VALUES =3D "'$pagehash[author]', '$pagehash[content]', " .
254 "$pagehash[created], $pagehash[flags], " .
255 "$pagehash[lastmodified], '$pagehash[pagename]', " =
257 "'$pagehash[refs]', $pagehash[version]";
260 $query =3D "INSERT INTO $dbi[table] ($COLUMNS) =
264 //echo "<p>Insert/Update Query: $query<p>\n";
266 $retval =3D mssql_query($query);
267 if ($retval =3D=3D false) {
268 printf(gettext ("Insert/Update failed: %s <br>\n"), =
269 mssql_get_last_message());
274 // for archiving pages to a seperate dbm
275 function SaveCopyToArchive($dbi, $pagename, $pagehash) {
276 global $ArchivePageStore;
277 $adbi =3D OpenDataBase($ArchivePageStore);
278 InsertPage($adbi, $pagename, $pagehash);
282 function IsWikiPage($dbi, $pagename) {
283 $pagename =3D addslashes($pagename);
284 if ($res =3D mssql_query("select count(*) from $dbi[table] where =
285 pagename=3D'$pagename'", $dbi['dbc'])) {
286 return(mssql_result($res, 0, 0));
291 function IsInArchive($dbi, $pagename) {
292 global $ArchivePageStore;
294 $pagename =3D addslashes($pagename);
295 if ($res =3D mssql_query("select count(*) from $ArchivePageStore =
296 where pagename=3D'$pagename'", $dbi['dbc'])) {
297 return(mssql_result($res, 0, 0));
303 function RemovePage($dbi, $pagename) {
304 global $WikiPageStore, $ArchivePageStore;
305 global $WikiLinksStore, $HitCountStore, $WikiScoreStore;
307 $pagename =3D addslashes($pagename);
308 $msg =3D gettext ("Cannot delete '%s' from table '%s'");
310 $msg .=3D gettext ("MSSQL error: %s");
312 if (!mssql_query("delete from $WikiPageStore where =
313 pagename=3D'$pagename'", $dbi['dbc']))
314 ExitWiki(sprintf($msg, $pagename, $WikiPageStore, =
315 mssql_get_last_message()));
317 if (!mssql_query("delete from $ArchivePageStore where =
318 pagename=3D'$pagename'", $dbi['dbc']))
319 ExitWiki(sprintf($msg, $pagename, $ArchivePageStore, =
320 mssql_get_last_message()));
322 if (!mssql_query("delete from $WikiLinksStore where =
323 frompage=3D'$pagename'", $dbi['dbc']))
324 ExitWiki(sprintf($msg, $pagename, $WikiLinksStore, =
325 mssql_get_last_message()));
327 if (!mssql_query("delete from $HitCountStore where =
328 pagename=3D'$pagename'", $dbi['dbc']))
329 ExitWiki(sprintf($msg, $pagename, $HitCountStore, =
330 mssql_get_last_message()));
332 if (!mssql_query("delete from $WikiScoreStore where =
333 pagename=3D'$pagename'", $dbi['dbc']))
334 ExitWiki(sprintf($msg, $pagename, $WikiScoreStore, =
335 mssql_get_last_message()));
339 function IncreaseHitCount($dbi, $pagename)
341 global $HitCountStore;
344 if ($res =3D mssql_query("select count(*) from $dbi[table] where =
345 pagename=3D'$pagename'", $dbi['dbc'])) {
346 $rowexists =3D (mssql_result($res, 0, 0));
350 $res =3D mssql_query("update $HitCountStore set hits=3Dhits+1 =
351 where pagename=3D'$pagename'", $dbi['dbc']);
353 $res =3D mssql_query("insert into $HitCountStore (pagename, hits) =
354 values ('$pagename', 1)", $dbi['dbc']);
359 function GetHitCount($dbi, $pagename)
361 global $HitCountStore;
363 $res =3D mssql_query("select hits from $HitCountStore where =
364 pagename=3D'$pagename'", $dbi['dbc']);
365 if (mssql_num_rows($res))
366 $hits =3D mssql_result($res, 0, 0);
373 function MakeSQLSearchClause($search, $column)
375 $search =3D addslashes(preg_replace("/\s+/", " ", $search));
376 $term =3D strtok($search, ' ');
380 if ($word[0] =3D=3D '-') {
381 $word =3D substr($word, 1);
382 $clause .=3D "not ($column like '%$word%') ";
384 $clause .=3D "($column like '%$word%') ";
386 if ($term =3D strtok(' '))
392 // setup for title-search
393 function InitTitleSearch($dbi, $search) {
394 $clause =3D MakeSQLSearchClause($search, 'pagename');
395 $res =3D mssql_query("select pagename from $dbi[table] where =
396 $clause order by pagename", $dbi["dbc"]);
402 // iterating through database
403 function TitleSearchNextMatch($dbi, $res) {
404 if($o =3D mssql_fetch_object($res)) {
413 // setup for full-text search
414 function InitFullSearch($dbi, $search) {
415 $clause =3D MakeSQLSearchClause($search, 'content');
416 $res =3D mssql_query("select * from $dbi[table] where $clause", =
422 // iterating through database
423 function FullSearchNextMatch($dbi, $res) {
424 if($hash =3D mssql_fetch_array($res)) {
425 return MakePageHash($hash);
432 function InitMostPopular($dbi, $limit) {
433 global $HitCountStore;
434 $res =3D mssql_query("select top $limit * from $HitCountStore =
435 order by hits desc, pagename", $dbi["dbc"]);
440 function MostPopularNextMatch($dbi, $res) {
441 if ($hits =3D mssql_fetch_array($res))
447 function GetAllWikiPageNames($dbi) {
448 global $WikiPageStore;
449 $res =3D mssql_query("select pagename from $WikiPageStore", =
451 $rows =3D mssql_num_rows($res);
452 for ($i =3D 0; $i < $rows; $i++) {
453 $pages[$i] =3D mssql_result($res, $i, 0);
459 ////////////////////////////////////////
460 // functionality for the wikilinks table
462 // takes a page name, returns array of scored incoming and outgoing =
464 function GetWikiPageLinks($dbi, $pagename) {
465 global $WikiLinksStore, $WikiScoreStore, $HitCountStore;
467 $pagename =3D addslashes($pagename);
468 $res =3D mssql_query("select topage, score from $WikiLinksStore, =
469 $WikiScoreStore where topage=3Dpagename and frompage=3D'$pagename' =
470 order by score desc, topage");
471 $rows =3D mssql_num_rows($res);
472 for ($i =3D 0; $i < $rows; $i++) {
473 $out =3D mssql_fetch_array($res);
474 $links['out'][] =3D array($out['topage'], $out['score']);
477 $res =3D mssql_query("select frompage, score from =
478 $WikiLinksStore, $WikiScoreStore where frompage=3Dpagename and =
479 topage=3D'$pagename' order by score desc, frompage");
480 $rows =3D mssql_num_rows($res);
481 for ($i =3D 0; $i < $rows; $i++) {
482 $out =3D mssql_fetch_array($res);
483 $links['in'][] =3D array($out['frompage'], $out['score']);
486 $res =3D mssql_query("select distinct pagename, hits from =
487 $WikiLinksStore, $HitCountStore where (frompage=3Dpagename and =
488 topage=3D'$pagename') or (topage=3Dpagename and frompage=3D'$pagename') =
489 order by hits desc, pagename");
490 $rows =3D mssql_num_rows($res);
491 for ($i =3D 0; $i < $rows; $i++) {
492 $out =3D mssql_fetch_array($res);
493 $links['popular'][] =3D array($out['pagename'], $out['hits']);
500 // takes page name, list of links it contains
501 // the $linklist is an array where the keys are the page names
502 function SetWikiPageLinks($dbi, $pagename, $linklist) {
503 global $WikiLinksStore, $WikiScoreStore;
505 $frompage =3D addslashes($pagename);
507 // first delete the old list of links
508 mssql_query("delete from $WikiLinksStore where =
509 frompage=3D'$frompage'",
512 // the page may not have links, return if not
513 if (! count($linklist))
515 // now insert the new list of links
516 while (list($topage, $count) =3D each($linklist)) {
517 $topage =3D addslashes($topage);
518 if($topage !=3D $frompage) {
519 mssql_query("insert into $WikiLinksStore (frompage, topage) =
521 "values ('$frompage', '$topage')", $dbi["dbc"]);
526 mssql_query("delete from $WikiScoreStore", $dbi["dbc"]);
527 mssql_query("insert into $WikiScoreStore select w1.topage, =
528 count(*) from $WikiLinksStore as w1, $WikiLinksStore as w2 where =
529 w2.topage=3Dw1.frompage group by w1.topage", $dbi["dbc"]);
532 /* more mssql queries:
535 select pagename from wiki left join wikilinks on pagename=3Dtopage =
536 where topage is NULL;
540 ------_=_NextPart_000_01C0D253.2568B8F4
541 Content-Type: application/octet-stream;
542 name="translate_mysql.pl"
543 Content-Disposition: attachment;
544 filename="translate_mysql.pl"
547 # Convert MySQL wiki database dump to a Microsoft SQL-Server compatible SQL script
548 # NB This is not a general-purpose MySQL->SQL-Server conversion script
550 # Author: Andrew K. Pearson
553 # Example usage: perl translate_mysql.pl dump.sql > dump2.sql
555 # NB I did not use sed because the version I have is limited to input lines of <1K in size
561 $newvalue =~ s/\\\"/\'\'/g;
562 $newvalue =~ s/\\\'/\'\'/g;
563 $newvalue =~ s/\\n/\'+char(10)+\'/g;
564 $newvalue =~ s/TYPE=MyISAM;//g;
565 $newvalue =~ s/int\(.+\)/int/g;
566 $newvalue =~ s/mediumtext/text/g;
567 $newvalue =~ s/^#/--/g;
573 ------_=_NextPart_000_01C0D253.2568B8F4--