Last modified: 2014-11-14 11:06:38 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 9767 - (mssql) Microsoft SQL Server/MSSQL support (tracking)
(mssql)
Microsoft SQL Server/MSSQL support (tracking)
Status: REOPENED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
unspecified
PC All
: Low normal with 8 votes (vote)
: ---
Assigned To: Nobody - You can work on this!
: tracking
Depends on: 22093 65808 71027 71207 72171 73390 15493 65757 65813 71024
Blocks: tracking
  Show dependency treegraph
 
Reported: 2007-05-02 19:00 UTC by D J Bauch
Modified: 2014-11-14 11:06 UTC (History)
14 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments
proposed file for maintenance/sqlserver/tables.sql (64.07 KB, text/plain)
2007-05-02 19:31 UTC, D J Bauch
Details
includes/DatabaseMssql.php new PHP file (55.35 KB, text/plain)
2007-05-02 19:34 UTC, D J Bauch
Details
includes\DatabaseADODB.php provides support for MS SQL Server (37.29 KB, text/plain)
2007-05-02 23:29 UTC, D J Bauch
Details
includes\DatabaseADODB.php with tabs -- provides support for MS SQL Server (37.62 KB, text/plain)
2007-05-04 03:26 UTC, D J Bauch
Details
This updated SQL script enables case sensitivy on MS SQL Server. (64.51 KB, text/plain)
2007-11-21 00:49 UTC, D J Bauch
Details
MS SQL Server script (69.45 KB, text/plain)
2008-07-18 00:22 UTC, D J Bauch
Details
includes\db\DatabaseADODB.php (44.07 KB, text/x-wiki)
2008-07-18 00:31 UTC, D J Bauch
Details
Patch for includes/specials/SpecialMostCategories.php (635 bytes, patch)
2008-07-18 01:06 UTC, D J Bauch
Details
Trial balloon number 2 (1.37 KB, patch)
2008-07-18 02:13 UTC, D J Bauch
Details
Adds DatabaseADODB.php to AutoLoader (545 bytes, patch)
2008-07-24 02:08 UTC, D J Bauch
Details
MS SQL Server script (69.90 KB, text/plain)
2008-08-19 18:53 UTC, D J Bauch
Details
MS SQL Server Script (70.07 KB, text/plain)
2008-09-19 21:29 UTC, D J Bauch
Details
MS SQL Server Script (70.25 KB, application/octet-stream)
2009-02-17 23:21 UTC, D J Bauch
Details
Patch for MediaWiki 1.1.4.0 to adapt to MS SQL support (49.02 KB, patch)
2009-04-08 19:45 UTC, D J Bauch
Details
MS SQL Server Script (71.40 KB, application/octet-stream)
2009-04-08 19:49 UTC, D J Bauch
Details
Patch for the ADODB libarary, version 5.05 (107.04 KB, patch)
2009-04-30 02:56 UTC, D J Bauch
Details
MS SQL Server Script (71.48 KB, application/octet-stream)
2009-05-05 22:04 UTC, D J Bauch
Details
Replacement updaters.inc to dbsource SQL Server script (63.51 KB, text/plain)
2009-05-05 22:08 UTC, D J Bauch
Details
MS SQL Server Script for MediaWiki 1.16.5 (72.74 KB, text/plain)
2011-07-29 18:34 UTC, D J Bauch
Details
includes\db\DatabaseMSPDO.php (46.98 KB, text/plain)
2011-07-29 18:46 UTC, D J Bauch
Details

Description D J Bauch 2007-05-02 19:00:02 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.
Comment 1 D J Bauch 2007-05-02 19:31:42 UTC
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.
Comment 2 D J Bauch 2007-05-02 19:34:14 UTC
Created attachment 3564 [details]
includes/DatabaseMssql.php new PHP file

This extends the Database class. Requires the ADODB extension to PHP.
Comment 3 Brion Vibber 2007-05-02 20:01:17 UTC
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.
Comment 4 D J Bauch 2007-05-02 20:30:07 UTC
(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. 
Comment 5 D J Bauch 2007-05-02 23:29:28 UTC
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.
Comment 6 Brion Vibber 2007-05-03 13:39:05 UTC
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.
Comment 7 D J Bauch 2007-05-04 03:26:30 UTC
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.
Comment 8 Jared 2007-06-21 14:18:02 UTC
Can I inquire as to the status of this bug as I am in need of MSSQL support
Comment 9 Brion Vibber 2007-06-21 15:51:41 UTC
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.
Comment 10 Steve 2007-09-12 09:20:05 UTC
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.
Comment 11 D J Bauch 2007-11-21 00:49:14 UTC
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.
Comment 12 chenxing 2008-02-18 07:43:18 UTC
I just want to subscribe to this bug.

Is adodb only available in Windows?
Comment 13 Joel 2008-04-08 20:27:37 UTC
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?
Comment 14 D J Bauch 2008-07-15 23:57:19 UTC
(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.
Comment 15 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-16 00:26:19 UTC
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.
Comment 16 Joel 2008-07-16 17:41:37 UTC
We'll (Microsoft) be contacting Brion and Tim soon. Yeah, read that again, Microsoft is going to commit open source code!
Comment 17 Roan Kattouw 2008-07-17 20:20:39 UTC
(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.
Comment 18 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-17 20:38:16 UTC
(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.
Comment 19 Roan Kattouw 2008-07-17 20:45:09 UTC
(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.
Comment 20 Joel 2008-07-17 20:55:30 UTC
FYI, we have a class that extends SearchEngine, we created a FixGroupBy123 function, and we use TS_ISO_8601 for dates in our version
Comment 21 D J Bauch 2008-07-18 00:19:22 UTC
(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.
Comment 22 D J Bauch 2008-07-18 00:22:45 UTC
Created attachment 5081 [details]
MS SQL Server script 

This replaces the previous version of the SQL script.
Comment 23 D J Bauch 2008-07-18 00:31:34 UTC
Created attachment 5082 [details]
includes\db\DatabaseADODB.php

Updated PHP code for the MS SQL Server MediaWiki version.
Comment 24 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 00:46:52 UTC
(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?
Comment 25 D J Bauch 2008-07-18 01:06:43 UTC
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?
Comment 26 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 01:34:11 UTC
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.
Comment 27 D J Bauch 2008-07-18 01:41:50 UTC
(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.
Comment 28 D J Bauch 2008-07-18 02:04:27 UTC
(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.
Comment 29 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 02:09:26 UTC
(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.
Comment 30 D J Bauch 2008-07-18 02:13:07 UTC
Created attachment 5084 [details]
Trial balloon number 2

Patch for includes/specials/SpecialNewimages created with TortoiseSVN Create Patch command. Is this the desired format?
Comment 31 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 02:17:06 UTC
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.)
Comment 32 D J Bauch 2008-07-18 02:43:06 UTC
(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.
Comment 33 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 18:17:30 UTC
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.
Comment 34 D J Bauch 2008-07-18 19:11:34 UTC
(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.

Comment 35 Joel 2008-07-18 19:15:43 UTC
(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
Comment 36 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-18 19:21:29 UTC
(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.
Comment 37 Joel 2008-07-18 19:29:08 UTC
(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.
Comment 38 D J Bauch 2008-07-24 02:08:19 UTC
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.
Comment 39 Joel 2008-07-24 17:26:16 UTC
(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
Comment 40 Joel 2008-07-29 18:53:07 UTC
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?
Comment 41 Aryeh Gregor (not reading bugmail, please e-mail directly) 2008-07-29 19:32:59 UTC
(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.
Comment 42 Garrett Serack 2008-07-29 20:03:58 UTC
> 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
Comment 43 D J Bauch 2008-07-29 22:39:21 UTC
(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?
Comment 44 Brion Vibber 2008-07-29 22:48:28 UTC
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?
Comment 45 Joel 2008-07-29 22:52:08 UTC
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
Comment 46 D J Bauch 2008-07-29 23:07:44 UTC
(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.
Comment 47 Garrett Serack 2008-07-30 17:10:31 UTC
(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
Comment 48 D J Bauch 2008-07-30 23:56:01 UTC
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...
Comment 49 Rocco 2008-08-13 20:38:27 UTC
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
Comment 50 Roan Kattouw 2008-08-13 20:52:57 UTC
(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.
Comment 51 D J Bauch 2008-08-19 18:51:27 UTC
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.
Comment 52 D J Bauch 2008-08-19 18:53:33 UTC
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.
Comment 53 D J Bauch 2008-09-19 21:29:21 UTC
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).
Comment 54 CP 2008-10-22 14:33:08 UTC
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!
Comment 55 Joel 2008-10-22 18:20:41 UTC
(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
Comment 56 D J Bauch 2008-10-22 18:27:24 UTC
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.
Comment 57 D J Bauch 2009-02-17 23:21:01 UTC
Created attachment 5825 [details]
MS SQL Server Script

Includes a change for the category table. cat_title must use COLLATE SQL_Latin1_General
Comment 58 D J Bauch 2009-04-08 19:45:14 UTC
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.
Comment 59 D J Bauch 2009-04-08 19:49:55 UTC
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.
Comment 60 Alexandre Emsenhuber [IAlex] 2009-04-08 20:15:18 UTC
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
Comment 61 D J Bauch 2009-04-09 03:09:32 UTC
(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).
Comment 62 Aryeh Gregor (not reading bugmail, please e-mail directly) 2009-04-13 00:29:29 UTC
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.
Comment 63 Yedidia Klein 2009-04-26 09:59:40 UTC
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 ?

Comment 64 D J Bauch 2009-04-30 02:53:37 UTC
(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.
Comment 65 D J Bauch 2009-04-30 02:56:16 UTC
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.
Comment 66 Yedidia Klein 2009-05-05 09:02:27 UTC
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 ?

Comment 67 Roan Kattouw 2009-05-05 09:05:17 UTC
(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.
Comment 68 Yedidia Klein 2009-05-05 10:15:34 UTC
(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...


Comment 69 D J Bauch 2009-05-05 18:56:53 UTC
(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.
Comment 70 D J Bauch 2009-05-05 22:04:50 UTC
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.
Comment 71 D J Bauch 2009-05-05 22:08:13 UTC
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.
Comment 72 D J Bauch 2009-05-05 22:12:27 UTC
(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.
Comment 73 Yedidia Klein 2009-05-06 10:04:05 UTC
(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



Comment 74 Andrew Garrett 2009-07-29 16:38:53 UTC
Adding 'tracking' keyword.
Comment 75 p858snake 2011-04-30 00:10:03 UTC
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Comment 76 D J Bauch 2011-07-29 18:34:40 UTC
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.
Comment 77 D J Bauch 2011-07-29 18:46:09 UTC
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.
Comment 78 Max Semenik 2011-07-29 22:06:11 UTC
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
Comment 79 D J Bauch 2011-07-29 22:35:30 UTC
(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
Comment 80 Max Semenik 2012-04-19 17:15:53 UTC
-need-review, already reviewed.
Comment 81 Sumana Harihareswara 2012-08-17 10:41:29 UTC
DJ, did you have time to work on these issues?
Comment 82 Andre Klapper 2013-07-24 14:48:44 UTC
DJ Bauch, would you have time to work on aforementioned issues with your patches?
Comment 83 Chad H. 2013-11-15 19:04:23 UTC
WONTFIX per reason I outlined 22093 comment 30
Comment 84 Mark A. Hershberger 2013-11-16 02:21:26 UTC
Reopening per bug 22093 comment 31
Comment 85 Mark A. Hershberger 2013-11-16 02:26:06 UTC
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.

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links