Last modified: 2014-11-14 11:06:38 UTC
MediaWiki does not allow support for Microsoft SQL Server 2000. Since I have developed such support, I am introducing this bug report as an entry I can associate with my anticipated upload of the PHP code to enable this support.
Created attachment 3563 [details] proposed file for maintenance/sqlserver/tables.sql This attachment contains the SQL to create the MediaWiki tables in Microsoft SQL Server 2000.
Created attachment 3564 [details] includes/DatabaseMssql.php new PHP file This extends the Database class. Requires the ADODB extension to PHP.
Offhand, I notice a couple things: * The tables.sql contains a lot of comments about MySQL-specific features * The tables.sql seems to be using the MySQL-TIMESTAMP-style char or varchar columns for timestamps. I recommend against this if standard date/time columns are available; compare with the PostgreSQL support. * There's a bunch of weird trigger stuff. The heck is that about? * The class file duplicates a huge amount of code from the parent Database class. Cut that stuff out -- you should never duplicate code like that.
(In reply to comment #3) > Offhand, I notice a couple things:* The tables.sql contains a lot of comments about MySQL-specific features* The tables.sql seems to be using the MySQL- TIMESTAMP-style char or varcharcolumns for timestamps. I recommend against this if standard date/time columnsare available; compare with the PostgreSQL support.* There's a bunch of weird trigger stuff. The heck is that about?* The class file duplicates a huge amount of code from the parent Databaseclass. Cut that stuff out -- you should never duplicate code like that. Brion, Thanks for your quick feedback. I'll clean up the tables.sql and look to remove duplicates of the Database class (although I thought I'd caught most of that). The "weird trigger stuff" relates primarily to how I had to deal with the issue you identified in the second bullet (the MySQL TIMESTAMP style columns). Initially I tried to use bona fide TIMESTAMP or DATETIME fields, but these don't tend to work well with the PHP code, so I found myself having to make extensive changes throughout the PHP. That seemed like a bad idea, so I instead created base tables with DATETIME columns and views with varchar columns. The trigger stuff is to make the changes to the views do the corresponding updates to the base tables. There's also a little bit of finessing for the tables that have IDENTIFIER columns, since the rules for adding new rows to databases that have those are a bit different between MySQL and SQL Server. Again my solution reflects a desire to have minimum impact on existing PHP. I have not been able to completely achieve that. In particular, any queries that involve "GROUP BY" clauses I have had to modify, albeit in a way that still seems compatible with MySQL.
Created attachment 3566 [details] includes\DatabaseADODB.php provides support for MS SQL Server This is class extends Database and provides support for Microsoft SQL Server 2000.
You really should not be doing that kind of funky timestamp stuff; please use DATETIME columns as in the Postgres support. If there are remaining issues in the general code not already resolved for Postgres, bring those to our attention. Class file still appears to contain a lot of duplicated code from the base class; try to prune this out. There are also many lines with strange indentation, making it hard to review the code. Be sure that you're using only tabs for indentation, not mixing with spaces. A quick peek at the insert() function shows what appears to be a logic error; when setting up for single- or multi-row inserts a $value array is built, but then the originally-passed $a array is always passed downstream instead.
Created attachment 3576 [details] includes\DatabaseADODB.php with tabs -- provides support for MS SQL Server Per Brion's advice, start each line with tabs and eliminate the spaces I was sometimes using.
Can I inquire as to the status of this bug as I am in need of MSSQL support
This is experimental third-party code which may or may not ever be integrated or maintained. It almost certainly does not work completely, and would be AT YOUR OWN RISK. If you want to run MediaWiki reliably, just install MySQL. It's free and widely supported. Yes, MySQL runs on Microsoft Windows. You do not have to pay for a license. There is no actual requirement ever to _need_ MSSQL. Some people might find it _convenient_ to use the same database that they already have set up, or to more easily integrate with another application, but there is nothing stopping you from installing MySQL if you can install MediaWiki. If you have organizational issues preventing you from installing the software needed for the software you want to use, that is outside the scope of the support we can provide you.
Just wanted to add - thanks for your work on this issue. It's going to make a big difference to the adoption of MediaWiki in environments where MS SQL is dominant. It looks like you're well on the way to making this work reliably.
Created attachment 4356 [details] This updated SQL script enables case sensitivy on MS SQL Server. Microsoft SQL server is case-insensitive by default. This has caused some minor problems with my MediaWiki installation. This updated database creation script causes columns to be case-sensitive where appropriate -- particularly with article titles.
I just want to subscribe to this bug. Is adodb only available in Windows?
ADOdb is available for PHP, so it doesn't really matter what the underlying OS platform is. BTW here at the Microsoft Open Source Software Lab we're interested in helping this project out. Is there any way I can get access to any changes you've made besides the attached files? You said you've had to change a few queries in the code itself. Also, without diving into the system, did you just add ADOdb support for this patch or is it previously part of the project?
(In reply to comment #13) > ADOdb is available for PHP, so it doesn't really matter what the underlying OS > platform is. > > BTW here at the Microsoft Open Source Software Lab we're interested in helping > this project out. Is there any way I can get access to any changes you've made > besides the attached files? You said you've had to change a few queries in the > code itself. Also, without diving into the system, did you just add ADOdb > support for this patch or is it previously part of the project? > Yes, I can provide the changes. What I'd like to do is to submit patches to the files that need to change, for example, 8 of the "Special" pages need changes made to the "GROUP BY" clauses in the SQL, since SQL Server doesn't support "GROUP BY 1, 2, 3...". The files in the includes subdirectory that I've had to change include AutoLoader.php, BagOStuff.php, Defines.php, FileDeleteForm.php, FileRevertForm.php, GlobalFunctions.php, HTMLCacheUpdate.php, ImagePage.php, LinkBatch.php, Math.php, OutputPage.php, Pager.php, Revision.php, SearchEngine.php, WatchlistEditor.php, and Wiki.php. The rest of the include files are unchanged. The text search capability is now working somewhat on my wiki, but it needs a little more work (It's using Lucene.Net in a manner similar to the way Wikipedia is now using it rather than the SQL Server full-text capability). Also, I was trying to get the math rendering working by building it with F# based on the MediaWiki OCaml implementation -- but that wasn't working very well, so the math now works with a direct interface to MikTex and GhostScript from the PHP code. Since my patch submissions are lagging, let me know if you'd like me to send you a zip of the codebase.
Just noticed this bug. There's limited MSSQL support in 1.13, added by nad in r34602, r34608, r34610, but it's evidently very buggy. If anyone would like to do some work to add proper MSSQL support along the lines of our current pgsql support, please e-mail Tim Starling or Brion Vibber for commit access (contact info readily available on Google). There should be no problem as long as you're careful when touching non-MSSQL-specific code -- MySQL *must* work flawlessly so that Wikipedia doesn't die, and it can be very finicky about some things, performance-wise. We'd be happy to have another DB engine properly supported, as long as it doesn't interfere with MySQL.
We'll (Microsoft) be contacting Brion and Tim soon. Yeah, read that again, Microsoft is going to commit open source code!
(In reply to comment #14) > Yes, I can provide the changes. What I'd like to do is to submit patches to the > files that need to change, for example, 8 of the "Special" pages need changes > made to the "GROUP BY" clauses in the SQL, since SQL Server doesn't support > "GROUP BY 1, 2, 3...". The files in the includes subdirectory that I've had to > change include AutoLoader.php, BagOStuff.php, Defines.php, FileDeleteForm.php, > FileRevertForm.php, GlobalFunctions.php, HTMLCacheUpdate.php, ImagePage.php, > LinkBatch.php, Math.php, OutputPage.php, Pager.php, Revision.php, > SearchEngine.php, WatchlistEditor.php, and Wiki.php. Changing queries all over the place just to make MSSQL happy sounds a little evil to me. Unless you can rewrite those queries to functionally identical ones with acceptable performance which will still work on MySQL, you shouldn't do it. If there's a simple rewrite algorithm, try to rewrite problematic queries inside the DatabaseMssql class. As to the timestamp issue: I believe the PostGres code uses the native DATETIME type internally and rewrites timestamps back and forth using the wfTimestamp() function (in GlobalFunctions.php). It has a TS_POSTGRES type for PostGres DATETIMEs. > The rest of the include > files are unchanged. The text search capability is now working somewhat on my > wiki, but it needs a little more work (It's using Lucene.Net in a manner > similar to the way Wikipedia is now using it rather than the SQL Server > full-text capability). That's not really related to MSSQL, but if you have a clean implementation of this (involving subclassing the SearchEngine class, like [[mw:Extension:MWSearch]] does) that works, releasing it as an extension should be fine. > Since my patch submissions are lagging, let me know if you'd like me to send > you a zip of the codebase. Just attach patches to this bug. Please don't zip anything.
(In reply to comment #17) > Changing queries all over the place just to make MSSQL happy sounds a little > evil to me. Unless you can rewrite those queries to functionally identical ones > with acceptable performance which will still work on MySQL, you shouldn't do > it. If there's a simple rewrite algorithm, try to rewrite problematic queries > inside the DatabaseMssql class. There's absolutely no problem in rewriting things to be more portable, at the query call time if appropriate. Where possible, this should be done at the level of the database abstraction layer. You could add code in the abstraction layer to have it try to figure out what 1, 2, 3, etc. represent, but it seems simpler to just stop using that alternative notation, in the places we currently do. > As to the timestamp issue: I believe the PostGres code uses the native DATETIME > type internally and rewrites timestamps back and forth using the wfTimestamp() > function (in GlobalFunctions.php). It has a TS_POSTGRES type for PostGres > DATETIMEs. We'd use MySQL's date/time formats too, honestly, except that it would be more effort than it's worth to convert them on Wikipedia. > That's not really related to MSSQL, but if you have a clean implementation of > this (involving subclassing the SearchEngine class, like > [[mw:Extension:MWSearch]] does) that works, releasing it as an extension should > be fine. We have search in core for MySQL and (I believe) PostgreSQL. I see no reason why we shouldn't have it for MSSQL too, if it can be made to work out of the box. > Just attach patches to this bug. Please don't zip anything. It's best to have someone with commit access for this, like we have for PostgreSQL. I doubt many current committers have installs of MSSQL handy, and it's obviously not a great idea for random developers to commit patches they haven't tested. Anyone who wants to maintain MSSQL support (or Oracle or SQLite or ...) should ask for commit access, not try to submit patches on Bugzilla at all. Happily, it seems someone is doing just this.
(In reply to comment #18) > There's absolutely no problem in rewriting things to be more portable, at the > query call time if appropriate. Where possible, this should be done at the > level of the database abstraction layer. You could add code in the abstraction > layer to have it try to figure out what 1, 2, 3, etc. represent, but it seems > simpler to just stop using that alternative notation, in the places we > currently do. That's what I meant with my "Unless you can..." sentence: we should do it at query call time *if* the rewritten query if functionally identical and performs similarly. > We have search in core for MySQL and (I believe) PostgreSQL. I see no reason > why we shouldn't have it for MSSQL too, if it can be made to work out of the > box. Of course. What I was referring to, however, was the Lucene.Net thing. We also don't have normal Lucene support in core (although I have no idea why that is). > > > Just attach patches to this bug. Please don't zip anything. > > It's best to have someone with commit access for this, like we have for > PostgreSQL. I doubt many current committers have installs of MSSQL handy, and > it's obviously not a great idea for random developers to commit patches they > haven't tested. Anyone who wants to maintain MSSQL support (or Oracle or > SQLite or ...) should ask for commit access, not try to submit patches on > Bugzilla at all. Happily, it seems someone is doing just this. I didn't suggest MSSQL support development happen through patches and BugZilla, of course not. I was just trying to say that we don't like patches to be e-mailed to people (attach them to a bug instead, so everyone can read them) and that we certainly don't like anything in some kind of zipped format.
FYI, we have a class that extends SearchEngine, we created a FixGroupBy123 function, and we use TS_ISO_8601 for dates in our version
(In reply to comment #20) > FYI, we have a class that extends SearchEngine, we created a FixGroupBy123 > function, and we use TS_ISO_8601 for dates in our version > My Microsoft SQL Server code is also using DATETIME columns internally where needed, but is exposing those tables with DATETIME columns only through views that convert to VARCHAR(14). Each of those views also has INSERT and UPDATE triggers to do the reverse conversion from VARCHAR(14) back to DATETIME. Unfortunately, the character format used for dates within MySQL and MediaWiki is not one of the many that SQL Server 2000 would have understood (although sticking a 'T' in the middle of it would have made it one). I am uploading my latest version of the SQL code, which now also replaces the LOGGING table with a view. This lets me avoid having to modify the PHP code that adds logging records so that it can continue to believe that it's updating the log_id IDENTITY column. I also changed the log_params column from TEXT to VARCHAR(8000) so that I can use the column in the UPDATE trigger. I resisted changing it to VARCHAR(MAX) so that I would maintain backward compatibility with SQL Server 2000. I can't imagine that a log entry would actually ever need more than 8000 characters of parameters.
Created attachment 5081 [details] MS SQL Server script This replaces the previous version of the SQL script.
Created attachment 5082 [details] includes\db\DatabaseADODB.php Updated PHP code for the MS SQL Server MediaWiki version.
(In reply to comment #21) > My Microsoft SQL Server code is also using DATETIME columns internally where > needed, but is exposing those tables with DATETIME columns only through views > that convert to VARCHAR(14). Each of those views also has INSERT and UPDATE > triggers to do the reverse conversion from VARCHAR(14) back to DATETIME. This seems unnecessarily elaborate. You can just override Database::timestamp. > I am uploading my > latest version of the SQL code, which now also replaces the LOGGING table with > a view. This lets me avoid having to modify the PHP code that adds logging > records so that it can continue to believe that it's updating the log_id > IDENTITY column. Um, why do you need to do that?
Created attachment 5083 [details] Patch for includes/specials/SpecialMostCategories.php This patch is a trial balloon. It illustrates a fix to the GROUP BY SQL clause. This is what I get from Beyond Compare when I export a difference report in "Unified Diff" format. This most closely resembles in format what I've seen in another MediaWiki bugzilla page. Is this the format expected for a patch? Do I want to remove those first couple of lines, or are they OK as-is?
Generally the output of the command "svn diff" is preferred. If you do use an ordinary diff -u command, or something similar, it should be relative to the installation directory of MediaWiki or some subdirectory thereof, not to the filesystem root; otherwise it won't apply cleanly. I'm also not sure if it's a great idea to use backslashes, since I don't know if GNU patch will accept them. Anyway, I've switched over all the uses of GROUP BY 1,2,3 and similar that grep found, in r37805. Should make it into the 1.13 release if it doesn't cause problems.
(In reply to comment #24) > (In reply to comment #21) > > My Microsoft SQL Server code is also using DATETIME columns internally where > > needed, but is exposing those tables with DATETIME columns only through views > > that convert to VARCHAR(14). Each of those views also has INSERT and UPDATE > > triggers to do the reverse conversion from VARCHAR(14) back to DATETIME. > > This seems unnecessarily elaborate. You can just override Database::timestamp. > I sort of took the approach you recommend at first (actually fiddling with wfTimestamp in GlobalFunctions.php) but I couldn't get everything to work that way. In my mind, I wasted quite a bit of time on that approach. The SQL in the views actually seems much less elaborate than the regexes and so forth in the wfTimestamp function. It also has the benefit of actually working for me. > > I am uploading my > > latest version of the SQL code, which now also replaces the LOGGING table with > > a view. This lets me avoid having to modify the PHP code that adds logging > > records so that it can continue to believe that it's updating the log_id > > IDENTITY column. > > Um, why do you need to do that? > I only made that change today. The only motivation is that I then have to modify one less PHP file. SQL Server, unlike MySQL, will not let you INSERT a value for an IDENTITY (AUTO_INCREMENT) column. By vectoring through the view, the PHP thinks it's inserting that field, but it's stripped out by the INSERT trigger.
(In reply to comment #26) > Generally the output of the command "svn diff" is preferred. If you do use an > ordinary diff -u command, or something similar, it should be relative to the > installation directory of MediaWiki or some subdirectory thereof, not to the > filesystem root; otherwise it won't apply cleanly. I'm also not sure if it's a > great idea to use backslashes, since I don't know if GNU patch will accept > them. > > Anyway, I've switched over all the uses of GROUP BY 1,2,3 and similar that grep > found, in r37805. Should make it into the 1.13 release if it doesn't cause > problems. > Thanks, they (SpecialMostcategories.php, SpecialMostimages.php, SpecialMostlinked.php, SpecialMostlinkedCategories.php, SpecialMostlinkedtemplates.php, SpecialMostrevisions.php, SpecialWantedcategories.php, and SpecialWantedpages.php) look the same in r37805 as in my working copy. I had not had a chance to confirm that the semantics is the same between MySQL and SQL Server for those changes, but they look right to me. I'll have to work on the diff problem. I'm actually using TortoiseSVN with Beyond Compare as my "diff". Probably means nothing to a Linux person -- but the bottom line is that I don't currently have the "svn diff" command. My trial balloon was just that, to see how close I was with what I'm already using. I'll look for some better exemplars of what the diffs should look like on the bugzilla and mimic those.
(In reply to comment #27) > I sort of took the approach you recommend at first (actually fiddling with > wfTimestamp in GlobalFunctions.php) but I couldn't get everything to work that > way. In my mind, I wasted quite a bit of time on that approach. The SQL in the > views actually seems much less elaborate than the regexes and so forth in the > wfTimestamp function. It also has the benefit of actually working for me. You really should not need to do any kind of elaborate regex. You should only need to do something like add two lines to wfTimestamp in GlobalFunctions.php, like case TS_MSSQL: return gmdate( 'Ymd H:i:s', $uts ) . ' UTC'; or whatever format is required, and then have the timestamp function consist of return wfTimestamp(TS_MSSQL,$ts); > I only made that change today. The only motivation is that I then have to > modify one less PHP file. SQL Server, unlike MySQL, will not let you INSERT a > value for an IDENTITY (AUTO_INCREMENT) column. By vectoring through the view, > the PHP thinks it's inserting that field, but it's stripped out by the INSERT > trigger. This is what the nextSequenceValue() function is for. Add an empty class named something like MsSqlSequenceValue, have nextSequenceValue return new MsSqlSequenceValue;, and silently drop any value of that class that's provided in select() or wherever. Patches like this should not go into the software. You should not have to hack on views and stuff to reinterpret MySQL input on the database side. Any incompatibility should be handled by a) improving the abstraction layer and b) fixing queries in the software (but carefully!). The two cases you have a problem with are already dealt with by PostgreSQL, which uses its own timestamp format and has its own unique way of doing auto-increment, and whoever does the MSSQL support should emulate that approach for these cases and others. (But in practice I imagine none of the core developers is going to be reviewing that code much, so if anyone gets commit access it's their lookout.) (In reply to comment #28) > I'll have to work on the diff problem. I'm actually using TortoiseSVN with > Beyond Compare as my "diff". Probably means nothing to a Linux person -- but > the bottom line is that I don't currently have the "svn diff" command. I used to use TortoiseSVN when I still used Windows, actually. You want to select Create Patch from the appropriate context menu; that basically runs svn diff.
Created attachment 5084 [details] Trial balloon number 2 Patch for includes/specials/SpecialNewimages created with TortoiseSVN Create Patch command. Is this the desired format?
Yes, but that patch is completely unacceptable. Database logic must not be mixed into the code. That's what the abstraction layer is for. You need to override makeSelectOptions to output TOP in the right place when the LIMIT option is provided. Queries like these that use $db->query() instead of the wrappers like select() should be converted to use the wrappers if it's a problem. (You could also try to work around raw queries by trying to parse them, but that's really not ideal.)
(In reply to comment #31) > Yes, but that patch is completely unacceptable. Database logic must not be > mixed into the code. That's what the abstraction layer is for. You need to > override makeSelectOptions to output TOP in the right place when the LIMIT > option is provided. Queries like these that use $db->query() instead of the > wrappers like select() should be converted to use the wrappers if it's a > problem. (You could also try to work around raw queries by trying to parse > them, but that's really not ideal.) > Thanks, I was really more concerned about the format of the patch than the patch itself, although it does reflect the way my code looks right now. When that file is "fixed" to use the select wrapper rather than the query method I'll be among the first to breathe a sigh of relief. It seems like less and less of the database logic is embedded in the MediaWiki code since 1.9.3, when I first started my port, and with each release I need to make fewer and fewer changes to make it work with SQL Server. DatabaseADODB.php does override makeSelectOptions, but it currently isn't doing anything useful with the LIMIT option. I'll fix that tomorrow, because I'm done for tonight.
Are you sure you want to continue to work on this? We do have some MSSQL support in 1.13, and apparently Microsoft is interested in making it good for later versions. If you do continue work, I suggest you a) base your work on the existing DatabaseMssql.php, and b) submit individual small patches for individual things that are broken about it. I'm repurposing this to be a tracking bug, since we already have basic support for MSSQL. New bugs should be filed, blocking this, for any specific issues that arise. I don't suggest you expect to get patches accepted until we have someone with commit access working on MSSQL, since as I said, not many of us have an MSSQL install to test with. You could ask for commit access yourself, of course, as Joel is.
(In reply to comment #33) > Are you sure you want to continue to work on this? We do have some MSSQL > support in 1.13, and apparently Microsoft is interested in making it good for > later versions. If you do continue work, I suggest you a) base your work on > the existing DatabaseMssql.php, and b) submit individual small patches for > individual things that are broken about it. I do. Perhaps I have not been clear, but I have everything working already, including quite a few extensions and have a wiki that's in use with about 200,000 articles in it. The main thing I don't have working to my satisfaction, but which does not seem to be related to SQL Server, is the rendering of images that have non-ASCII characters in their file names. > I'm repurposing this to be a tracking bug, since we already have basic support > for MSSQL. New bugs should be filed, blocking this, for any specific issues > that arise. I don't suggest you expect to get patches accepted until we have > someone with commit access working on MSSQL, since as I said, not many of us > have an MSSQL install to test with. You could ask for commit access yourself, > of course, as Joel is. I suppose if I could get full functionality out of the MSSQL interface, I'd be willing to switch (back) to it. I adopted the ADODB interface, however, because I got stuck when I tried to use MSSQL. It was a dead end back then -- but that was a slightly earlier release of PHP. Perhaps the problems I experienced with it have been corrected since then.
(In reply to comment #34) > (In reply to comment #33) > > Are you sure you want to continue to work on this? We do have some MSSQL > > support in 1.13, and apparently Microsoft is interested in making it good for > > later versions. If you do continue work, I suggest you a) base your work on > > the existing DatabaseMssql.php, and b) submit individual small patches for > > individual things that are broken about it. > > I do. Perhaps I have not been clear, but I have everything working already, > including quite a few extensions and have a wiki that's in use with about > 200,000 articles in it. The main thing I don't have working to my satisfaction, > but which does not seem to be related to SQL Server, is the rendering of images > that have non-ASCII characters in their file names. Our biggest concern is unicode support since it requires the database to use nvarchar for everything which cuts the storable character size in half and causes indexing problems. We're not sure what the best balance is for that yet. > > > I'm repurposing this to be a tracking bug, since we already have basic support > > for MSSQL. New bugs should be filed, blocking this, for any specific issues > > that arise. I don't suggest you expect to get patches accepted until we have > > someone with commit access working on MSSQL, since as I said, not many of us > > have an MSSQL install to test with. You could ask for commit access yourself, > > of course, as Joel is. > > I suppose if I could get full functionality out of the MSSQL interface, I'd be > willing to switch (back) to it. I adopted the ADODB interface, however, because > I got stuck when I tried to use MSSQL. It was a dead end back then -- but that > was a slightly earlier release of PHP. Perhaps the problems I experienced with > it have been corrected since then. > Our patch uses the new sqlsrv driver recently released at: http://www.microsoft.com/sql/technologies/php/default.mspx
(In reply to comment #34) As I say, I suggest you e-mail Tim Starling or Brion Vibber and ask for commit access if you want to get this committed to the software. Almost no one who currently has commit access is likely to be able to review your patches. When someone does get commit access, or multiple someones, it's up to them to decide how they want it to work, I guess, since we appear to have three separate implementations at this point. (But it can't stick $wgDBType in the core code or anything like that.) (In reply to comment #35) > Our biggest concern is unicode support since it requires the database to use > nvarchar for everything which cuts the storable character size in half and > causes indexing problems. We're not sure what the best balance is for that yet. The default MySQL schema just has all of the text columns set to binary, and stores raw UTF-8 bytes in them. This works very nicely: there's no wasted space, you can deal with all code points (even outside the BMP, which MySQL utf8 can't, at least historically), and it's not like any string operations are done on the database side that really need to use characters instead of bytes.
(In reply to comment #36) > (In reply to comment #34) > > As I say, I suggest you e-mail Tim Starling or Brion Vibber and ask for commit > access if you want to get this committed to the software. Almost no one who > currently has commit access is likely to be able to review your patches. When > someone does get commit access, or multiple someones, it's up to them to decide > how they want it to work, I guess, since we appear to have three separate > implementations at this point. (But it can't stick $wgDBType in the core code > or anything like that.) We're using a separate namespace (mssqlnative) so these two can be in the code base concurrently since they use different drivers anyway. I don't know if you want to add the ADOdb dependency just for one driver though, but they can exist separately. > > (In reply to comment #35) > > Our biggest concern is unicode support since it requires the database to use > > nvarchar for everything which cuts the storable character size in half and > > causes indexing problems. We're not sure what the best balance is for that yet. > > The default MySQL schema just has all of the text columns set to binary, and > stores raw UTF-8 bytes in them. This works very nicely: there's no wasted > space, you can deal with all code points (even outside the BMP, which MySQL > utf8 can't, at least historically), and it's not like any string operations are > done on the database side that really need to use characters instead of bytes. > There is a problem with making the fields binary, you can't index binary fields, so that idea was one of the first to go sadly.
Created attachment 5093 [details] Adds DatabaseADODB.php to AutoLoader One-line addition to AutoLoader adds recognition of DatabaseADODB. I am continuing with ADODB for now because the new Microsoft connector for PHP is a Community Technology Preview (CTP) and not authorized for production use and the ADODB connector is working fine.
(In reply to comment #38) > Created an attachment (id=5093) [details] > Adds DatabaseADODB.php to AutoLoader > > One-line addition to AutoLoader adds recognition of DatabaseADODB. I am > continuing with ADODB for now because the new Microsoft connector for PHP is a > Community Technology Preview (CTP) and not authorized for production use and > the ADODB connector is working fine. > RTM is closer than you think =] I'm working on final bugs as we speak as well in my driver using the new sqlsrv driver
We're proud to announce the release of the sqlsrv PHP driver, you can learn more about the release here: http://blogs.msdn.com/sqlphp/archive/2008/07/28/microsoft-sql-server-2005-driver-for-php-v1-0-has-released.aspx Who should be our contact for submitting our patch? You may have heard about our contribution to ADOdb: http://www.theregister.co.uk/2008/07/25/microsoft_gpl/ You may not have known that our submission is through email. Who can I talk to about our patch?
(In reply to comment #40) > Who should be our contact for submitting our patch? You may have heard about > our contribution to ADOdb: > http://www.theregister.co.uk/2008/07/25/microsoft_gpl/ > > You may not have known that our submission is through email. Who can I talk to > about our patch? As I said in comment #15, you should talk to Tim Starling (tstarling@wikimedia.org) or Brion Vibber (brion@wikimedia.org). I would suggest again that you ask for commit access instead of just asking that the patch be submitted. That way you can maintain the patch yourself when it inevitably breaks due to some MySQL-centrism that someone adds. If you don't have commit access, you'll have to repeatedly bug people to commit your patches if you want to maintain the support. This is not only slow and annoying, it's also pointless, since you probably aren't going to be able to find anyone who has an MSSQL installation to test the patch on. If everyone is just going to blindly submit your patches without providing independent testing and scrutiny, you may as well be able to do so without the middleman. As far as submitting patches without commit access, they're generally submitted on Bugzilla, e.g., here.
> As I said in comment #15, you should talk to Tim Starling > (tstarling@wikimedia.org) or Brion Vibber (brion@wikimedia.org). I would > suggest again that you ask for commit access instead of just asking that the > patch be submitted. That way you can maintain the patch yourself when it > inevitably breaks due to some MySQL-centrism that someone adds. > > If you don't have commit access, you'll have to repeatedly bug people to commit > your patches if you want to maintain the support. This is not only slow and > annoying, it's also pointless, since you probably aren't going to be able to > find anyone who has an MSSQL installation to test the patch on. If everyone is > just going to blindly submit your patches without providing independent testing > and scrutiny, you may as well be able to do so without the middleman. > > As far as submitting patches without commit access, they're generally submitted > on Bugzilla, e.g., here. > Howdy Simetrical, I'm Garrett Serack, I'm the Open Source Community Developer here at Microsoft. (Joel works for me) We've been talking this thru a lot, and I completely agree with you, I think we're better off submitting the patches via subversion, rather than email, posting or ... whatever. The 'email' patches has up until now, been what was 'convenient', but by no means a requirement :D I'll be dropping Tim and/or Brion a mail today on this, and requesting access. thanks, Garrett Serack
(In reply to comment #42) I've also requested commit access from Brion, so I expect to hear something real soon now. I've downloaded the SQL Server 2005 driver from Microsoft and started trying to get the AdventureWorks sample from the documentation going. So far, it hasn't worked for me -- but the error messages I'm seeing look like: SQLSTATE: IM002 Code: 0 Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified The SQL Server driver isn't sitting on top of ODBC is it?
I think I'd prefer it if we have *one* MSSQL driver rather than two. :) Would it be possible to compare codebases and combine if necessary to a single version?
I think for the time being that keeping them separate would be wise. The new driver isn't part of the default php installation yet and requires PHP 5+ as well as being hosted on windows. As time passes and adoption grows we can merge them, but for now we should keep things separated
(In reply to comment #44) > I think I'd prefer it if we have *one* MSSQL driver rather than two. :) > > Would it be possible to compare codebases and combine if necessary to a single > version? > I'd be willing to work on keeping things as simple as possible. There are a few things that I would imagine would have to be fairly common to both versions anyway, like the database schema. I'm anxious to share my code, and I'm in the process of looking at the new ADODB library that was released on 11 July 2008. I'm also looking at the new SQL Server driver from Microsoft. I would also like, at some point, to contribute back my Lucene.Net version of the full text search service.
(In reply to comment #43) > The SQL Server driver isn't sitting on top of ODBC is it? It does sit on top of the ODBC driver; this was the most efficient and flexible way to build the driver. The SQL Server team assures me it's not a performance problem. :D We can certainly work together; Joel is about to disappear for 10 days on Vacation, when he gets back, we can see where we can sync our efforts. Garrett
This week I downloaded the new ADODB driver from SourceForge (http://adodb.sourceforge.net/). Some minor tweaks to the library are required in order to make it work with MediaWiki. Only a few changes are required, as discussed on my blog at http://mediawikiworker.blogspot.com/2008/07/mediawiki-113-and-adodb-505.html While I was working on that today, I see that the mssqlnative driver showed up on sourceforge. I'll see how that one goes...
Hello, I tried to connect my instance to mssql using ADODB. As I can see mediawiki 1.13.0rc2 contains the DatabaseMssql.php layer. However I wasn’t able to find DatabaseADODB.php? I installed the adodb layer in my root Mediawiki/adodb but it doesn’t seem to be working properly. Are there any instructions of how to integrate ADODB with mediawiki? Many Thanks Rocco
(In reply to comment #49) > Hello, > I tried to connect my instance to mssql using ADODB. As I can see mediawiki > 1.13.0rc2 contains the DatabaseMssql.php layer. However I wasn’t able to find > DatabaseADODB.php? I installed the adodb layer in my root Mediawiki/adodb but > it doesn’t seem to be working properly. Are there any instructions of how to > integrate ADODB with mediawiki? MSSQL support is experimental, and will probably be broken in some situations. ADODB doesn't seem to be supported at all at the moment, and DatabaseADODB.php was abandoned.
ADODB isn't supported, but it works fine. It isn't broken, and DatabaseADODB.php has not been abandoned by me, I just don't have any permission to get it included.
Created attachment 5200 [details] MS SQL Server script Updated the MS SQL server script (tables.sql) to fix problems uncovered when testing with a new MediaWiki installation. Supports the tables needed for MediaWiki 1.13.
Created attachment 5348 [details] MS SQL Server Script This update to tables.sql fixes a problem that sometimes occurs when deleting a page from the wiki. The logging table view now trims the comment to 255 characters. Sometimes the comment was longer than 255 due to escaped quotes (counted by PHP as single characters, I suppose, but by SQL Server as two characters).
Anybody progressing on this bug? My understanding is that for any of the 2 alternatives described changes in config/index.php and/or includes/db/DatabaseMssql.php are required. Any ideas how this is progressing? Thanks!
(In reply to comment #54) > Anybody progressing on this bug? My understanding is that for any of the 2 > alternatives described changes in config/index.php and/or > includes/db/DatabaseMssql.php are required. Any ideas how this is progressing? > Thanks! > Yes, we're nearing the end of our dev cycle for releasing support for the new sqlsrv driver
Yes. Mediawiki is fully functional on Microsoft Windows with SQL Server and IIS. I do not have the ability to check in any of my changes to the Subversion repository or I'd have done so by now. There are not enormous changes to be made, but I expect that most of what I've done is applicable whether using the new Microsoft sqlsrv driver or (as I've done) the AdoDB library.
Created attachment 5825 [details] MS SQL Server Script Includes a change for the category table. cat_title must use COLLATE SQL_Latin1_General
Created attachment 6005 [details] Patch for MediaWiki 1.1.4.0 to adapt to MS SQL support This patch contains the changes to be applied to the 1.14.0 release to adapt the release to run on Windows with Microsoft SQL Server 2000 or SQL Server 2005. I've made it to simplify the installation for other users based on the experiences I gained going through the installation with another user. Yes, there is at least one other user that I'm aware of. I had been tracking changes against the trunk rather than against the releases -- but since the trunk is always changing it was easier to have him install the 1.14.0 release and then patch in the changes.
Created attachment 6006 [details] MS SQL Server Script Updated MS SQL script (./maintenance/sqlserver/tables.sql) to be used in conjunction with 1.14.0 patch. This script also creates the new tables (change_tag, tag_summary, valid_tag) introduced since 1.14.0.
The patch (id 6005) seems to contains some strange modifications: * AutoLoader.php: where's the file? * Defines.php: this has nothing to do in core * Revision.php: don't comment that out, it's needed for PostgreSQL * SpecialShortpages.php, SpecialWantedcategories.php, SpecialWantedpages.php: why commenting out the link existence batch? * SpecialWantedtemplates.php: already fixed in r47618 * SpecialListfiles.php: already fixed in r48350
(In reply to comment #60) > The patch (id 6005) seems to contains some strange modifications: > * AutoLoader.php: where's the file? > * Defines.php: this has nothing to do in core > * Revision.php: don't comment that out, it's needed for PostgreSQL > * SpecialShortpages.php, SpecialWantedcategories.php, SpecialWantedpages.php: > why commenting out the link existence batch? > * SpecialWantedtemplates.php: already fixed in r47618 > * SpecialListfiles.php: already fixed in r48350 > Yes, the modifications are strange in that they are not necessarily intended to replace the MySQL/PostgreSQL code in the trunk. They're only useful to someone trying to run on SQL Server. The file missing from autoloader.php is an attachment to the bug report (DatabaseADODB.php). It doesn't get picked up when I make the diff because it didn't exist before. The extra lines from my defines.php are for my extra namespaces and are optional. I'm assuming the commented out line you're referring to is the attempt to insert an ID in the text table. It won't work in my text table because the column is an auto-number column. I could make a view to eliminate this issue if necessary. The link existence batches don't work with the ADODB database adapter and not doing them didn't change the desired outcome. I may alter the ADODB database adapter to allow them to work at some time, but I don't own that code or have commit access on that project. Those last two already fixed issues were actually ones that I asked for but couldn't make myself (see comment #13 to this bug), since I don't have commit access. If I remember correctly, Tim Starling made the fixes back then. In addition to the patch file, you would need the tables.sql attachment above, the databaseadodb.php attachment above, and a patch to the ADODB library (which I could place here too, I suppose).
I guess you should re-request commit access. I don't think you were denied because anyone wanted to deny you -- probably the request just got lost at some point, because granting commit access is one facet of the MediaWiki development process that seems to be particularly dysfunctional right now. If you still want it, I'd remind Brion and/or Tim every month or two if we don't get a saner system in place soon.
OS: win2003, MSSQL 2000 (8.0) first thanks for the patch, I've troubles w/ installation patched 1.14.0 w/ your patch, updated tables.sql , created db and user on mssql side then got nice installation page. but after filling all info - got back the installation page w/o any error. no tables were created.. any way to debug ? what can be done ?
(In reply to comment #63) > OS: win2003, MSSQL 2000 (8.0) > first thanks for the patch, > I've troubles w/ installation > patched 1.14.0 w/ your patch, updated tables.sql , created db and user on mssql > side then got nice installation page. > > but after filling all info - got back the installation page w/o any error. > > no tables were created.. > > any way to debug ? what can be done ? > The ADODB library needs patches to three files. I am uploading the necessary patch for ADODB 5.05 to this bug report right now, since that is the version I am currently using with my wiki. The current release of adodb is 5.08, so I will download and build a patch for that one too. In the mean time, if you get adodb505 from sourceforge and apply the patch, hopefully you will get past this point and the tables will be created.
Created attachment 6073 [details] Patch for the ADODB libarary, version 5.05 Minor patches to the ADODB library for PHP, version 5.05 so that it supports the MS SQL connection for MediaWiki.
After some fixes on php.ini (most important is to set a file for error log for not getting 500 errors), my installation is moving. first I found that tables.sql has to be in maintenance/sqlserver (and not in mssql dir). then after creating some tables it stops. refreshing the installation give this info: There are already MediaWiki tables in this database. Checking if updates are needed... ...ipblocks table does not exist, skipping new field patch ...ipblocks table does not exist, skipping new field patch Creating interwiki table: then it stop... could be that tables.sql that is provided here is broken ?
(In reply to comment #66) > After some fixes on php.ini (most important is to set a file for error log for > not getting 500 errors), my installation is moving. > first I found that tables.sql has to be in maintenance/sqlserver (and not in > mssql dir). then after creating some tables it stops. > > > refreshing the installation give this info: > There are already MediaWiki tables in this database. Checking if updates are > needed... > ...ipblocks table does not exist, skipping new field patch > ...ipblocks table does not exist, skipping new field patch > Creating interwiki table: > > then it stop... > > could be that tables.sql that is provided here is broken ? > There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql has an entry for ipblocks.
(In reply to comment #67) > (In reply to comment #66) > > After some fixes on php.ini (most important is to set a file for error log for > > not getting 500 errors), my installation is moving. > > first I found that tables.sql has to be in maintenance/sqlserver (and not in > > mssql dir). then after creating some tables it stops. > > > > > > refreshing the installation give this info: > > There are already MediaWiki tables in this database. Checking if updates are > > needed... > > ...ipblocks table does not exist, skipping new field patch > > ...ipblocks table does not exist, skipping new field patch > > Creating interwiki table: > > > > then it stop... > > > > could be that tables.sql that is provided here is broken ? > > > > There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql > has an entry for ipblocks. > but DatabaseADODB.php has this on line 1360: dbsource( "../maintenance/sqlserver/tables.sql", $this); And for some reason the table creation fail at the middle, these table were created: * user2 * user_newtalk * user_groups * text * templatelinks * revision2 * pagelinks * page2 * imagelinks * categorylinks * archive And these views were created: * page * revision * user There are missing about 20-30 other tables...
(In reply to comment #68) > (In reply to comment #67) > > (In reply to comment #66) > > > After some fixes on php.ini (most important is to set a file for error log for > > > not getting 500 errors), my installation is moving. > > > first I found that tables.sql has to be in maintenance/sqlserver (and not in > > > mssql dir). then after creating some tables it stops. > > > > > > > > > refreshing the installation give this info: > > > There are already MediaWiki tables in this database. Checking if updates are > > > needed... > > > ...ipblocks table does not exist, skipping new field patch > > > ...ipblocks table does not exist, skipping new field patch > > > Creating interwiki table: > > > > > > then it stop... > > > > > > could be that tables.sql that is provided here is broken ? > > > > > > > There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql > > has an entry for ipblocks. > > > but DatabaseADODB.php has this on line 1360: > dbsource( "../maintenance/sqlserver/tables.sql", $this); > > And for some reason the table creation fail at the middle, these table were > created: > * user2 > * user_newtalk > * user_groups > * text > * templatelinks > * revision2 > * pagelinks > * page2 > * imagelinks > * categorylinks > * archive > > And these views were created: > * page > * revision > * user > > There are missing about 20-30 other tables... > I am starting to fix this. The problem is that the way the tables are initially created has changed since I created my wiki. I'll have to update ./maintenance/updaters.php and add a SQL Server case to the function archive($name) so that it looks in "$IP/maintenance/archives/sqlserver". Right now it is trying to use the MySQL archives (since the only other option currently is postgress. I'll have to populate that directory under maintenance. If you just run the SQL in ./maintenance/sqlserver/tables.sql, you should get all the current tables.
Created attachment 6091 [details] MS SQL Server Script There were a couple of mistakes in the SQL Server script, but that wasn't the root of the problem, since the install was attempting to run a bunch of patch SQL scripts from the MySQL directory. Replace the tables.sql with this updated one. The root of the problem is addressed by a change to updaters.inc, which I'll upload next.
Created attachment 6092 [details] Replacement updaters.inc to dbsource SQL Server script Replace ./maintenance/updaters.inc with this modestly changed one. If the database type is 'adodb' it will dbsource the MS SQL Server Script rather than try to run the patch scripts from ./maintenance/archives. No patching is necessary anyway, since there won't be an exisiting SQL Server wiki database. I just tested this and the MS SQL Server Script on a fresh box, and it's working for me.
(In reply to comment #66) > After some fixes on php.ini (most important is to set a file for error log for > not getting 500 errors), my installation is moving. > first I found that tables.sql has to be in maintenance/sqlserver (and not in > mssql dir). then after creating some tables it stops. > > > refreshing the installation give this info: > There are already MediaWiki tables in this database. Checking if updates are > needed... > ...ipblocks table does not exist, skipping new field patch > ...ipblocks table does not exist, skipping new field patch > Creating interwiki table: > > then it stop... > > could be that tables.sql that is provided here is broken ? > This problem is fixed in the updated tables.sql I just uploaded. You are right, there was an error in the SQL for creating the interwiki table. I stumbled on this because it was a type-conversion error rather than a syntax error. A couple of columns had to be varchar rather than varbinary.
(In reply to comment #72) > (In reply to comment #66) > > After some fixes on php.ini (most important is to set a file for error log for > > not getting 500 errors), my installation is moving. > > first I found that tables.sql has to be in maintenance/sqlserver (and not in > > mssql dir). then after creating some tables it stops. > > > > > > refreshing the installation give this info: > > There are already MediaWiki tables in this database. Checking if updates are > > needed... > > ...ipblocks table does not exist, skipping new field patch > > ...ipblocks table does not exist, skipping new field patch > > Creating interwiki table: > > > > then it stop... > > > > could be that tables.sql that is provided here is broken ? > > > This problem is fixed in the updated tables.sql I just uploaded. You are right, > there was an error in the SQL for creating the interwiki table. I stumbled on > this because it was a type-conversion error rather than a syntax error. A > couple of columns had to be varchar rather than varbinary. > Wonderful! the installation was smooth and all the tables were created. LocalSettings.php exist also. now after moving LocalSettings.php to the right place I get a timeout on main page, changed max_execution_time on php.ini to 300 sec (!) and still it get a timeout. this is what I get on the error_log: [06-May-2009 12:46:00] PHP Notice: Undefined index: REQUEST_METHOD in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\WebRequest.php on line 410 [06-May-2009 12:46:00] PHP Notice: Undefined index: SERVER_PROTOCOL in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\OutputHandler.php on line 117 [06-May-2009 12:46:09] PHP Notice: Undefined index: REQUEST_METHOD in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\WebRequest.php on line 410 [06-May-2009 12:51:09] PHP Fatal error: Maximum execution time of 300 seconds exceeded in C:\php\adodb5\adodb.inc.php on line 3593
Adding 'tracking' keyword.
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Created attachment 8841 [details] MS SQL Server Script for MediaWiki 1.16.5 At the Microsoft SQL Server JumpIn Camp, I updated the script to support MediaWiki 1.16.5. I tested on SQL Server 2008, SQL Server Denali, and SQL Server Azure. It also still works on SQL Server 2005. The code now uses Microsoft's PDO driver for SQL Server rather than ADODB, but the SQL script is not affected by that.
Created attachment 8842 [details] includes\db\DatabaseMSPDO.php Database subclass for use with Microsoft SQL Server PDO driver. This class works with both traditional SQL Server connections as well as with SQL Server Azure. It doesn't exactly obsolete DatabaseADODB.php, although I suppose that practically, it does.
So that "MSSQL native" thing is obsolete and everybody should use PDO? Should we throw away our current MSSQL backend that bitrots for a year now? A few quick comments on attached code: 1) We don't accept patches with new functionality against released versions. Only trunk. And for such complex things, we don't accept patches at all. Instead, we give commit access to people interested in continuously maintaining these. 2) Database class is a legacy alias for DatabaseMysql, inherit from DatabaseBase. 3) Code duplication in constructor and newFromParams(). 4) wfProfileIn("dbconnect-$server") is superfuluous, no heavy operations between it and parent wfProfileIn(__METHOD__). 5) Due to (2), you have to resort to overloading query() and performing messy manual SQL rewrites, don't do that. Change SQL abstraction functions instead. 6) Same thing for copy-paste inheritance of virtually every function. 7) Manual magic quotes fighting in strencode() and qstr() is not needed, magic_quotes_gpc is handled in WebRequest while installer refuses to install with magic_quotes_runtime on. 8) Lots of commented-out code. 9) Code doesn't follow our coding conventions outlined at http://www.mediawiki.org/wiki/Manual:Coding_conventions
(In reply to comment #78) > So that "MSSQL native" thing is obsolete and everybody should use PDO? Should > we throw away our current MSSQL backend that bitrots for a year now? A few > quick comments on attached code: > 1) We don't accept patches with new functionality against released versions. > Only trunk. And for such complex things, we don't accept patches at all. > Instead, we give commit access to people interested in continuously maintaining > these. > 2) Database class is a legacy alias for DatabaseMysql, inherit from > DatabaseBase. > 3) Code duplication in constructor and newFromParams(). > 4) wfProfileIn("dbconnect-$server") is superfuluous, no heavy operations > between it and parent wfProfileIn(__METHOD__). > 5) Due to (2), you have to resort to overloading query() and performing messy > manual SQL rewrites, don't do that. Change SQL abstraction functions instead. > 6) Same thing for copy-paste inheritance of virtually every function. > 7) Manual magic quotes fighting in strencode() and qstr() is not needed, > magic_quotes_gpc is handled in WebRequest while installer refuses to install > with magic_quotes_runtime on. > 8) Lots of commented-out code. > 9) Code doesn't follow our coding conventions outlined at > http://www.mediawiki.org/wiki/Manual:Coding_conventions Thanks for those comments. I'll endeavor to fix those issues. As to MSSQL native, I never have used that. I haven't had any luck getting that or PDO for SQL Server working on Windows XP, so I used the ADODB library in the past. I started on the PDO driver by making a copy of my old ADODB driver code, so that explains some of the legacy stuff that now needs to be cleaned up. MSSQL native and PDO work fine for me on Windows 7 and on Windows Azure, and with SQL Azure. ADODB doesn't do as well with SQL Azure, so right now I either use ADODB on XP with SQL Server 2005 or 2008 or I use PDO on Windows 7 or Azure with SQL Server 2005, 2008, Denali, or Azure
-need-review, already reviewed.
DJ, did you have time to work on these issues?
DJ Bauch, would you have time to work on aforementioned issues with your patches?
WONTFIX per reason I outlined 22093 comment 30
Reopening per bug 22093 comment 31
I did some work to support SQL Server access from Linux this summer (https://bugs.php.net/bug.php?id=64338), so you don't have to have a Windows box to implement support for SQL Server. Azure access should work just fine.
