Support MS SQL databases

It looks like Peter Heinrich has added a patch for this ...
http://drupaldigest.com/development#comment-138

I won't be able to test in until mid-to-late October, but I'm eager to give it a go.

Just correctly updated the version number. This would really be for 6.x-dev but there is not tag yet. As per http://groups.drupal.org/node/1496 I think we should keep the discussion, patch going here to get this rolling for D6!

I took a look at some existing MS SQL code and saw that it needs a lot of work. I'm marking this postponed for now, but if anyone with more MS SQL know-how is welcome to take a stab. :)

Okay, I'll stop being a wuss...sortof. Here is a half-done drupal.mssql.inc. I'm just going to post this in increments just in case any one else feels the need to add to it. Note: this is unworking, half-done code...do not try and use it. I did somewhat of a port of Souvent22's code, but it could use some more eyes. I'm trying to keep track of // TODO:'s in there so we know what needs work.

Attachment	Size
database.mssql.inc	15.61 KB

Another note, this was copied over from database.mysql.inc to get a good starting point so there is still some mysql calls in there that need mssql equivalents. Any one out there with better MS SQL knowledge than I?

Changed to version 6.x-dev.

Updated patch for HEAD. Still A LOT of work needing to be done, doesn't even install yet. Just want to keep the progress here in case anyone wishes to help out.

http://drupal.org/node/98835 relates to the installer not working with backslashes for db host name.

Attachment	Size
mssql.patch	22.95 KB

Posting to track thread

RobRoy,

I've had back and ofrht discussions about 1 patch vs multiple, with more people leaning towards multiple patches. But I agree, this should be 1 patch.

So, should I use your current patch, merge with my patch, and roll out another "1 patch to rule them all" ?

Hey Souvent22,

I took the mysql code from HEAD and changed stuff over that way. I'm not sure how you went about it, but the base of my code might be fresher and more in sync with HEAD. It may be a better starting point from that regard, but I've not looked at your new code, just the old stuff. I agree that one patch is the way to go.

Thanks for the work on this, when I get some time I'll try and do a bit of a merge. Keep posting your patch as you make progress so we can both work off it.

:)

Also, I have very limited knowledge of MS SQL, so you'll have to let me know the best way to go about things from a MS SQL perspective.

Merged patch. "1 patch to rule them all" :) .

Attachment	Size
mssql_install_driver_merged.patch	50.17 KB

Update about the above "merged" patch.
With that patch against head, the install "runs". You will see table show up perfectly (kind of) in SQL Server. However, the problems mentioned on the drupal groups site still persist, which are (snippet from the groups):

NOTE: This has been tested with:

• PHP 5.1.6
• SQL Server Standard 2005
• ntwdblib.dll v. 2000.80.194.0 (you will need this updated version and not the one that comes with your php install)

So, it's current status, it runs and creates the db nicely. But, there are some bugs to be had:

• the system table for some reason has the status column set to 0 except for system.module for some reason
• when an error happens, nothing it thrown back because the connection immediatley closes. perhaps we need to look at a better way to trap and handel errors. (the system just sits until execution timeout)
• after manipulating the system table, i had to modify the blocks.module sql call because it calls a "distinct" on the page column (which is a text datatype) and throws an error cause mssql says its not comparable, so i took off the distinct modifier for now, and i'm investigating that SQL statement.

Hopefully, now that we have 1 "merged" patch, this effort and move forward faster. :).

Full working version.
This installed in my system many times with no errors or noticies.

This patch addresses 2 issues:

1. A bad SQL statement crashes the system and causes a time out.
I have addressed this by just placing a @.
Statements that produce an error are just catalouged into the watchdog.

2. The system table seems to not have enabled any modules.

The problem with all the 'statuses' being 0 was in the DDL of the system table. the status was set to an int field (rightly so), however, drupal (in module.inc) checks with $existing === '0' (meaning a char, and it must EXACTLY be a char). So a simple change to the DDL, and voila.

I have a tested this patch many times, and it works great. I will only put my stamp of approval on this patch if you are running SQL Server 2005 (if you wnat to test, sql server 2005 express is free and pretty robust for a free DB). you must also be using the latest SQL Server client. See the comments above for a link and more information.

Note: You will have to make a change in your blocks.moule. Remove "DISTINCT" from 2 places (it's only in 2 places), or your blocks will not show. This is a bug that I am addressing in another patch. The sql in blocks.module needs to be changed.

Attachment	Size
database_mssql_merge.patch	51.92 KB

Check this out to abstract the DISTINCT part: http://api.drupal.org/api/HEAD/function/db_distinct_field

Will check the other stuff later. Thanks for working on this!

There was a small issue in the way the MS SQL escapse strings (', ", etc.) vs. other DB's. Alter the drivers escape string function. Works very nicely now.

Attachment	Size
database_mssql_merge_0.patch	52.2 KB

RobRoy,

Ah thanks. However, the query would still not work because it is the general "logic" that MS SQL doesn't like. In MS SQL's view, you have a full text field (pages) which you are asking the DB compare to other rows and see if it's "distinct". Well, since a text field can be, 2 GB (i'll have to check my figures, but pretty large), it says, no way, your logic is in correct. so the query needs to be re-written.

Unfortunatly, MS-SQL doesn't allow "selective distinction". Like, SELECT DISTINCT (nid, node) type FROM node;

This can be solved though IF you are running SQL Server 2005.

So a decision needs to be made...support SQL Server 2000+, or 2005+?
With 2005 there is a varchar(max) data type that acts just like mysql's, and will allow quiries like i mentioned above.

So, what does everyone think?

What I mean is that we should use http://api.drupal.org/api/HEAD/function/db_distinct_field in blocks.module or wherever else that has a hardcoded DISTINCT. And then in our mssql db_distinct_field function we can deal with it however we want. Or am I missing something? We should file separate issues for those though.

This is getting there. It needs some code cleanup

- Whitespace removal
- Structure the CREATE TABLES consistently (some are way indented some aren't, I'd use the indents like in locale.install.)
- Some mysql references in includes/database.mssql.inc
- We need to handle errors more gracefully and I'm not sure the best way to do it as I commented out that error code in _db_query

I'm just writing down tasks as I see them.

I think we should just target 2005. We want to make this as clean as possible and we don't need to go that far back.

Killing your strong tag here. :)

Hmmm...not sure how to fix that.

Sorry 'bout that. Hopefully that fixes it. :)

Re-Rolling becuase my other 2 patches were working aginst a fresh HEAD, thus re-rolled, tested, and this works against HEAD.

Attachment	Size
database_mssql_merge_1.patch	51.82 KB

looks good ... you should not have to regex for LIMIT queries and rewrite them. Those queries all run through db_query_range(). Just rewrite that function to use TOP and then delete the gnarly regex from _db_query().

Thanks moshe. I'll probabley start tackling db_query_range at the begging of next week.

Re-Rolling patch. The blocks table was missing the 'title' field.

Attachment	Size
database_mssql_merge_2.patch	51.85 KB

@Souvent22 - you mention that a change to blocks.module is needed for this patch and that this is handled in a different issue. Where is that issue? I'd like to move it along.

I'm a bit sad that to drop MSSQL 2000 support. I know it is ancient, but enterprises upgrade their DB platform very very slowly. If possible, could someone elaborate on the problem. Maybe we can patch Drupal to behave nicer.

Moshe,

MS SQL 2005 has new 'Large Data Type' columns. It is best explained by a snippet I pulled from informit.com. However, since most people will not be doing Transactions/Stored Procs/Functions with PHP->MS-SQL and for that matter Drupal->MS-SQL, these columns could be revereted back to TEXT data types instead of varchar(max) data types.

For a full understanding, go to the article ref link and read the first page about the handeling of the new data types. This is just a small snippet. The full article talks about how in SQL Server 7->2000, you had to span large text columns over many columns, and this was not pretty. There are some MSDN articles on this change also.

Article Ref: http://www.informit.com/articles/article.asp?p=327394&seqNum=6&rl=1

Large Value Data Types

In SQL Server 2000 (and 7) the maximum size for VARCHAR and VARBINARY was 8,000 and for NVARCHAR 4,000. If you had data that potentially exceeded that size, you needed to use the TEXT, NTEXT, or IMAGE data types (known as Large Object data types, or LOBs). This was always a hassle because they were hard to work with, in both retrieval and action statements.

This situation changes in SQL Server 2005 with the introduction of the MAX specifier. This specifier allows storage of up to 231 bytes of data, and for Unicode it is 230 bytes. When you use the VARCHAR(MAX) or NVARCHAR(MAX) data type, the data is stored as character strings, whereas for VARBINARY(MAX) it is stored as bytes. These three data types are commonly known as Large Value data types. The following code shows the use of these data types in action....

Note: The bytes for the new varchar(max) etc. columns are 2³¹ bytes and 2³⁰ bytes for unicode, not 230 bytes. I forgot to use the "sup" tag.

Hm. Perhaps I should split the .instlal files, and make a new db-type. mssql, mssql2005 (like mysql, mysqli).

I have currently updated (but haven't rolled out) the db patch so that your mssql version shows up on the admin/status page. I'll roll out a patch after I get some of the MS-SQL server stats to show up. I would like some community input as to what stats people would like to see (if there are any other MS-SQL developers out there). You may have to give read-access to the Drupal account to some tables in the sys schema. But, the stats avail. from MS-SQL server FAR out weight the number of stats for MySQL (mysql ~ 250 stat vars, mssql ~ thousands stat vars). So...what do you guys want to see on the stat page? I can' just dump out a thousand stats on the sql page. :).

Right now, the major ones I think are:

• Threads open
• Current Quries

I don't want to put too much on the page. I want it to be a quick diagnosis tool. Major tuining must be done at the console or using SQL Server tools. This screen should be for looking at symptons of a problem, and not for final diagnosis (I think).

thanks for the clarification.

could we define those fields conditionally depending on the mssql version? i don't see why not, since all this schema creation is done in PHP. so older versions will use type=TEXT. Is there core code that will fail under TEXT?

@moshe

You're right, i think a simple preg_replace would do the trick, and I can check the version. I'll try and get that in this week.
afaik, having text will not break any core modules. This was more a 'big-picture' call, meaning if you're working with Drupal tables on the other side (in the Visual Studio/T-SQL/MS enviroment), that varchar(max) etc. would be preferred. However, this is a mute point because if you have sql-server 7.0 or 2000, then obviously you're not going to be able to use the varchar(max) datatypes.

I need to make myself a "TODO" though to add to the system_update. Perhaps under the admin/sql, there should be a way to update your tables if you upgrade from 7.0/2000 -> 2005, to alter the neccessary tables columns to the better/new datatypes.

I should also start a hand book in the Drupal books section so that people can ont what to look for when/if they are trying to use Drupal/PHP+MS-SQL. There are a few ref. articles (performance, installation, etc.) in which I'd like to keep the links to in a central place.

Update:

• Search module did not work becuase MS-SQL does not have a GREATEST function. This patch creates a UDF (User Defined Function) GREATEST that implements this during the installation of mssql. The code for is can be found in the system.install.
• On the admin screen, one can now see the version of MS-SQL they are running
• Currently varchar(n) and *not* varchar(max) is used, or text. This may change during the final roll out

If you are testing this, until I/we (community) come to a final decision, if you are not using SQL Server 2005, you will have to change varchar(max) to varchar(4000) instead. This is just for now. This will change and the driver will automatically select the correct column type for your db. I have no time to write the reg-ex for this now, if someone wants to roll one, I wouldn't hold them back. :)

Attachment	Size
mssql_install_driver_merged_0.patch	55.53 KB

Update,

uploads do not seem to work with this mssql patch, and i don't know why. They show up, but they don't get copied to the files directory, nor does the node get upated. very strange. Can anyone verify?

Keeping up with head.

Had to re-roll because patch would not apply due to changes from HEAD.

Tested this patch against a clean install.

Attachment	Size
mssql_install_driver_merged_1.patch	52.06 KB

Note: This patch will work with SQL Server 2000. text was changed to varchar(4000). I doubt that a block would have enough chracters in it's page 'limiting' area to reach 4000. I could be wrong.

*Important Rollout*

I found out all the patches before this one are kind of useless. The sessions table had the _session_ column set to varchar(1024). Thus, uploads were not working, and i was getting some quirky thinking going on. This fixes that by setting the column to 'text'.

* Uploads fixed with this patch
* Sessions table fixed with this patch

Attachment	Size
mssql_install_driver_merged_2.patch	52.14 KB

Added 'title' field to blocks table install. Synced against head.

Attachment	Size
mssql_install_driver_merged_3.patch	52.19 KB

- Split verisoin so the string isn't so long, and added versions back in. (some how dissapeared)

Attachment	Size
mssql_install_driver_merged_4.patch	120.86 KB

I've attempted to adapt the MySQL LIMIT function to work with SQL Server 2000 with no luck. After several attempts, I could not create a generic enough MSSQL query to accomodate all MySQL queries using LIMIT. My only solution was to post-process the recordset using mssql_array_seek in function _db_query:

$last_result = mssql_query( $query, $active_db);

if (isset($offset) && $offset > 0 && $last_result && $last_result != '1' && mssql_num_rows($last_result) > $offset) {
mssql_data_seek($last_result,$offset);
}

But I hate to hack it up this way... any ideas? SQL Server 2005 offers a solution with a new paging function, but for the rest of us stuck with 2000, it's not so easy. I really urge everyone working on this to make sure it's 2000+ compatible :)

don't bother with regex. you just have to get db_query_range() to work right. thats where the LIMIT stuff is generated. drupal already abstracted this for us.

He's right. I'm all over the place with projects and I had meant to reply earlier. So this is going to be a quick explination;
SQL Server, Oracle, and most other large 'enterprise' DB's implement the SQL:2003 ANSI standard, which does not define a LIMIT type clause.

Instead it defines this:
SELECT
ROW_NUMBER() OVER(ORDER BY nid ASC), nid, title
FROM node

I have already wreslted with this, I just havne't had time to finalize and test it, but this is what I believe needs to happen.

1. All db_range_queries need to have an ORDER BY clause (we'll deal with those that don't later)
2. Take in the query, say: SELECT nid, title, created FROM node ORDER BY created DESC WHERE type = 'story' ($from = 10, $count = 20)
3. Pop off the ORDER BY clause (they can not appear in the ROW_NUMBER clause
4. Reformat the query as follows:

(the below comments are NOT optimal, but taking bad SQL statements into account)
5. If no ORDER BY is given then find the first column that has an index
6. if no column has an index, use an arbitrary column

So something to that effect. I've used this ROW_NUMBER query on a sub-query with 102,000+ rows, and rows very fast, just as fast as LIMIT so it seems, Although, I have no numbers to back this up.

But just wanted to get that out there to see what people thought, and how to use some regexs to split the SQL up as needed to re-write it in this format.

When I say he's right, i meant that yes, we should do all this in db_query_range as moshe says.

With SQL 2005, it supports the SQL:2003 ROW_NUMBER(), however 2000 doesn't. I'm thinking that we are going to have to put our heads togather and create a Stored Procedure for SQL 2000 servers, like DRUPAL_PAGER, and have the server handel it server-side in the proc (because you'll prob. need cursors), and then spit back the subset as a result-set.

sounds good, earnest ... note that wikipedia describes an alternate syntax for LIMIT is MSSQL. See http://en.wikipedia.org/wiki/Comparison_of_SQL_syntax#Limiting_results_o....

i'm ok with a sproc for this in sql2000 (or sql2005, for that matter)

Moshe, that is the alternate syntax I used, which ended up not working for certain queries - it had something to do with ordering by the same column multiple times, but I can't recall the exact error. Like I said, I ended up pulling the entire recordset and seeking to the offset with PHP. I considered the sproc, but that too seems like a hack, albiet a better one than mine. This is really the biggest hurdle to jump for getting Drupal to work with MSSQL 2000. Once this is overcome, everything else *should* go more smoothly....

I dont' think a sproc is a hack of a solution to this problem. If anything, it's a better solution because the SQL engine will have pre-compiled and pre-examined the SQL statement for the best execution process.

I think the main thing is getting the bits of information that we need split up in the SQL clause. Meaning, the main parts to any solution are (and I may be wrong, I'm throwing this out there):

1. The SELECT clause and what's being selected
2. The ORDER BY clause so we know how to order results
3. The WERE clause so we know how to filter them.

You mentioned that you had to pull the entire recordset, and use a cursor (which cursors are expensive as we all know). I think the better solution is rewriting the SQL statement into one of the fomrats that has been discussed here, and letting the SQL engine filter the results for you. It's much faster and less expensive.

So with that said, it comes down to 2 options (even if we go with a sproc or not):
1. Rewrite the SQL statement
2. Use a cursor (which we could do client side, but i wouldn't want to)

I agree that the sproc is the best solution thus far. My thoughts:

Create a temp table and select use a cursor to grab the desired rows specified in LIMIT. This would work, but we'd need to determine the columns and column types from the original query to populate the temp table with.

The parameters of that I can think of would be:

1. @query = the query without the LIMIT clause at the end
2. @columns = columns being selected (comma delimited)
3. @limit = the limit¹
4. @offset = the offset^1,2

¹optional
²if offset is not specified, then the issue is becomes simply SELECT TOP @limit FROM (@query) [AS] derived_table and I think we could avoid creating the temp table all together

For example:

SELECT w.wid x, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY x DESC LIMIT 5, 50

Would become:

Then, the maximum number of rows we'd need to pull would be the offset + limit - 55. So,

1. Adjust @query to SELECT TOP (offset + limit)
2. Create the temp table with the appropriate cols - i'll need help doing this. We'd need to include an id column (id_col). I think there are some stored procedures in the master db that can help with this.
3. Insert all the records from the query into the temp table.
4. SELECT * FROM #temp1 WHERE id_col > OFFSET

I'm sure I'm missing some important steps, but this is one way I would go about it. Once we decide on the right way, I'd be interested in contributing.

What do you all think?

Re-rolling against RC1

Attachment	Size
mssql_install_driver_merged_5.patch	28.92 KB

Re-rolling.

I am posing another patch for watchdog (see the groups.drupal.org/enterprise ). Other than that, as long as your SQL is in good format (e.g. no over-lapping columns. Bad: "SELECT n.*, u.* FROM node n INNER JOIN users u ON n.nid = u.uid" (you're already getting the uid from node, why are you getting it twice if say you want the users name). Good: "SELECT n.*, u.name FROM .....".

Note: This is currently only compatible with SQL Server 2005. If someone wants to take a look at how I implemented it, and suggest a SQL Server 2000 implementation, or create a SP from SQL Server 2000, it would go along way to moving this forward.

Updates

• Fixed db_query_range function
• Began implementing better error handeling from SQL Server (sometimes you get a "Statement terminated" when the real error is a FK constraint failure.
• Minor fixes here and there. Too many to list.

If your SQL is good, I consider this patch alpha...perhaps even beta stable. 3rd party verification is needed.

- Souvent22

Attachment	Size
mssql_install_driver_merged_6.patch	60.16 KB

SQL Clean up reference. This SQL statement in watchdog currently breaks in the SQL Server Drupal driver's db_query_range function: http://drupal.org/node/105540 . This will prob. be fixed soon. Until then, apply this referenced patch, and all is well.

Has this patch been tested with Drupal 5.0?

+1

Subscribing

Tested against 5.2.

REMEMBER: You will need the updated ntwdblib.dll (I'm currently running verion 2000.80.194.0). The one that comes in your PHP install/directly is WAY outdated and will not work. Search the web to download the dll. I'll prob. post it on my blog sometime so it's readily available. You'll also need to obviously enable mssql support.

Attachment	Size
mssql_install_driver_merged_6_0.patch	60.13 KB

Subscribing :-)

subscribing too... :)

another subscriber

So I need to know what the interest level in this patch is and if i should put an effort to get this included into 6.0 ? What does everyone think?

i am very interested. whomever maintains this DB will immediately become the MS Drupal guru which will likely be a prestigous and lucrative position if thats desired. My .02.

@Souvent22: YES, put into 6.0, please :-)

Merged a few items from the clients patch into this patch. Also cleaned up a lot of my comments and took out misc. functions. Fixed temporary tables. Also added a fix for these PHP "bugs":

http://bugs.php.net/bug.php?id=26996
http://bugs.php.net/bug.php?id=25777

I tested this about 4 times and it installed fine. So i think this is ready for some review now.

- Earnest

Attachment	Size
mssql_install_driver_merged_6_0_0.patch	62.99 KB

excellent, earnest. that bug #26996 was what finally drove me away from maintaining mssql in the first place. the last straw, if you will.

i checked you code and i saw varchar(max). This is incompatible with MSSQL 2000!? On the other side you are not using the new MsSQL 2005 range function or i overseen it... this should be a very big performance hit but incompatible with mssql2k.

Will this core extension support mssql2000 or only mssql2005? should the database.mssql*.inc not named like the sql version? this looks to me - regarding the range function - more advantages then disadvantages, while it make clear what version is required and there need no conditions inside - if version X then X1 elseif version Z then Z1... not sure what is the prefered way. Doing a SELECT @@version or xp_msver 'ProductVersion' seems required to distinguish between the versions... but i'm not sure how performant it is to select the version for every (range) request...

However using MsSQL 2005 and not using the range functions sounds not good to me...

Hass,

Regarding the range function, are you speaking of table partitioning? 'cause that's a whole other conversation.

Regarding VARCHAR(MAX), yes, i know it's not sql 2000 compatible. In the thread I had made a call out about support 2000 vs 2005 (e.g. varchar(max) vs TEXT).
It is *much* easier to do 2005 with varchar(max) than the TEXT column. however, I do plan on going back and making a mssql7 version after this one is stabilized. hm, i suppose i could change this to mssql9 to differentiate, but i wanted to call this one mssql because 2005 is, IMHO, Microsoft's first "real definitive DB release". it supports most of the industry standard SQL practices without most of the quircks that were found in 2000. not that 2000 is a bad product, but i believe this sql code will be usable for at least 2 more SQL Server releases.
I could do a SELECT @@version. Doing the xp_* functions isn't usually best practice as those are usually secured away and not avail to "regular" SQL Server users/logins. However, all in all there are only a few "quirks" (all beit some what major), but 90% of the code of database.mssql.inc will be usable on the 2000 port, so I think a simple switch will be fine and we'll be able to just have one database.mssql.inc. Really, the problem with the 2000 port is the TEXT field AFIK. That's what really sets the 2 things apart.....regarding Drupal support anyway. There are many instances where people have had to resort to concatenanted varchar(2000), or varchar(x) fields because TEXT is just a b@#!$ to work with. But right now, i vote get the 2005 support. I know it's a 50/50 thing out there as a lot of companies use 2000 still; although many i talk to are "staging" there 2005 rollout because 2005 adds so many "must have" features. but if I can get 2005 stable and in, i'll start porting back to 2000 immediatley. we have 2.5-3 months before the code freeze. i'm thinking perhaps this will get in in a month, so that leaves me 2 months to port 2000. i think that's a good timeline.
Thoughts?

Oh, one more big note about sql 2000 support.....db_query_range. 2005 has the ROW_NUMBER function, which is not avail in 2000. This will have to be some type of temp table function, but that's kind of slow. I do have a solution though started in the patch though (it's the reason it's in there right now). There's a function called "find key" or something like that. The thought process goes like this:

- One way to do paging is if you know a one-dimensional cardinality about the table (e.g. primary key, or unique key). You can use this to do a "double-mirror" technique using TOP to page the information (see below for short explanation of technique). However, this information (key) is not passed to us by the user (unless we get something in the ORDER BY clause, but this is not guranteed). So, the function looks up the table information of one of the tables and looks for the primary key of the table, and then we can use that to page the table.

*Double-mirror
Let S = start row
Let E = end row
Let T = total tuple set (num rows)
Flip sql (order backwards) and grab the top (T - E + (S-E)) rows, order set forwards
From that tuple set (sub-query), grap top (S-E) rows

*update i shouldn't say one-dimensional key, but you need to be working/ordering on a key, else performance goes to hell.

*update i shouldn't say one-dimensional key, but you need to be working/ordering on a key/index, else performance goes to hell.

I'm still interested in this as well. Souvent, would you mind listing the features of your patch that you know to be INcompatible with SQL Server 2000 (e.g., VARCHAR(MAX), ROW_COUNT, etc.). That way some of us can get Drupal running on 2000 and then work to help you with some of the major incompatibilities (e.g, Paging).

Thanks for everything you've done so far... I think that an addition like this to the core would be real helpful for those using SQL Server as well as the Drupal project itself (working into the 'mainstream').

*Update: ROW_NUMBER I mean, not ROW_COUNT

SELECT  SERVERPROPERTY('productversion') looks better for the version check...

regarding "range" i'd like to correct me. i mean ROW_NUMBER.

no matter - if only mssql2005 is currently supported, we have bought a sql 2005 - 2 weeks ago :-) and for me i don't need 2000 support anymore, but i'm sure - not every company like to upgrade for drupal only. cpu licenses have high investment costs... However, having MSSQL on the new features list for the next release will be something that will push drupal spreading... :-).

Just wanted to chime in and show my support for this work. Would be great to have MSSQL support in Drupal 6. I don't have a MSSQL server to test with, so it will be up to you guys to mark this RTBC. I'll do a code review though, so make sure it's clean and elegant. ;-)

Hass, Yes, I do use ROW_NUMBER. If you check the patch, it's used during db_query_range.

I'll post a listing of "incompatible sql2000" features just as an FYI. Perhaps only in the code, and snippet in the Handbook just as a "TODO" for a sql2000 backport.

Once 6 gets back to being usable (my last check-out a few days ago left it some what un-usable, so i'm waiting), i'll tidy up the admin screen for sql server to show some db stats, and then post back for review.

Speaking of stats, anyone/everyone want to chime in on what they'd like to see? Right now i'm thinking:
- num procs
- num current threads
- num quries
- db size
- log size
- last backup
- backup method (FULL, SIMPLE, INCREMENTAL)
- errors
- memory usage
- Service pack level of OS
- Service pack level of SQL Server

I also think I'm going to call this mssql9 instead of mssql. just cause it's easier to track version numbers (sql2000 = 7.0, 2005 = 9.0, etc.) that the "Year Versioning" that we are accustomed to, which is really for marketing; unless there is some objection.

i think support for mssql in general and specific for 2000 = 8.0 will possibly not implemented by other module developers. If they use simple and not so highly optimized query's it's ok to have only one mssql code for all versions (today we know). if we worry all module developers about all DB versions we won't get this inside the modules i think... same discussion should come up for mysql 4 and 5... and it looks like this has been solved with one file / logic.

Aside, why are you not using with (nolock) (read-uncommitted)? This is a VERY big performance hit... and it prevents deadlocks on high stress sites... we got much of them in past and changed all SQL code for this. Now the site works very well under high load and every recurring developer mistake tells us again - this is a simple must have. So i can say for sure - every SELECT should have with (nolock) added for best performance and solving possibly deadlock reasons.

Abstract/Background:
For those who are totally un familiar with SQL Server. WITH (NOLOCK) is something you can add to a query (SELECT, UPDATES) so that the server ignores any and all TABLE locks and wider (page-locks, extent locks, and lower are respected). So if you have a transaction that has a table locked or some query that's updating a table and it's going to take 1 min. to do so, if you hit the table with the WITH (NOLOCK) modifier, you will get results instantly w/o having to wait for the table to be unloced. e.g. SELECT nid, title FROM node WITH(NOLOCK) WHERE nid > 10;

In our production site, we currently use WITH (NOLOCK). And you are correct, it's a GREAT feature. However, it is also what I consider and "application feature". Meaning, it is not really general enough in that you do not have to know about the application to use it.

To show why this is important, lets take and example, but you must remember this:

All SQL statements are run through the _db_query function.

So, lets take an example, take db_next_val. If use WITH (NOLOCK), you will most certainly get a collision of ID's because the SQL statements ignore table locks and see that (for example) 32 is the higest value, thus they will both return 33.
The site we run runs many many quires a second (on the order of 800 spking to 1500 a second). On the site we wanted the speed of WITH (NOLOCK). So we implemented a random ID number generation technique and dropping the sequential table creation (I can post on our method if anyone is interested).

It should be noted that we did run into DEADLOCK conditions under very high load (only during heavy content creation). So switched to random-number-generation and all was good.

I had tried to add WITH (NOLOCK) also to the _db_query_range function. However, that introduced problems but I can't remember what they were. Pehraps it was that WITH(NOLOCK) can't go in a sub-query, but i don't think that's it. I'd have to go back through my docs to find the specific reason why. But, perhaps we could setup _db_query to take an extra var that sets the lock condition, like _db_query($sql, $debug = FALSE, $lock_condition = MSSQL_WITH_NO_LOCK), and db_next_val will call _db_query($sql, FALSE, MSSQL_TRANS_DEFAULT), meaning it will honor the transactions set lock condition (meaning just run normally as to how the server and/or environment is setup) by not using any LOCK modifiers.

So, I agree, WITH (NOLOCK) is one of the features that leaves MySQL behind; talked and demonstrated it at the last Drupal meet up. But, it's the Drupal arch. that makes it somewhat of a problem because WITH(NOLOCK) is something to use when you know your application. IMHO. Thoughts?

i wonder how you are able to use with(nolock) in an UPDATE... i think this is not possible.

Yes, the sequences table is a possible problem. it should be solved in a different way. maybe a regex that does not insert with(nolock) inside if table is sequences or if function is db_next_id... or something similar. ok, if there are auto_increment fields we don't have to think about this problem :-). the other way should be a positive table list...

Regarding lock conditions i'm sorry - you are wrong. MySQL and some other DB's have this feature, too. I know for sure this is possible in ColdFusion with the DataDirect JDBC Drivers... it's a config setting only and MySQL have the same read-uncommitted performance boost. So this feature should be named more general for e.g. read-uncommitted or something shortened telling what is really the technical background...

however - in general - all should be read-uncommitted aside of minor pieces requires a table lock and we should ask why and if this is really required or cannot be changed to boost drupal...

OT: if drupal.org will run read-uncommitted with their mysql server THIS site should be really faster, too... and drupal.org get's everyday slower and slower... adding a comment here is no fun.

OT: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

Hass,

Yes, this is true for InnoDB which implements most of the SQL-92 transaction level standards, however the majority of people run MyISAM, and most of the high-load sites run a combination of MyISAM + InnoDB tables. MS-SQL only has one storage engine type, so this is not as much of a problem. Again, it's a lock feature for when you know your application (thus you'd have to alter certain tables to InnoDB to get the diff. isolation levels that InnoDB gives).

also, yes, nolock is for select statements only, gets dropped/ignored when using other statements.

postgres and most all other db's support locking hints in some form/fashion. changing this behavior should be talked about in another thread though; it's beyond ths scope of this patch (meaning it's a change in the methodology of the db_layer. this patch is to add an addtl layer using the current methodology). I'll keep this in mind when rolling out my next patch though.

I also know that one of my modules (timesheet.module) requires/uses InnoDB tables, and thus it's select statements need to 1. be in a transaction, and 2. would NOT want to use the read-uncommitted mode during some sql statements. Changing this would break this. I don't know of any other modules in which this would be a problem though.

As i know - Drupal 5 requires InnoDB or creates InnoDB Tables only, isn't it? So you are correct for past... but not today.

drupal 5 uses InnoDB, but doesn't require it afaik.

again though, that's a seperate issue in my mind though (the locking issue).

For both 2000 and 2005, has anyone run into this issue? In short, it's a bug that prevents one from pulling more than 255 characters from SQL Server into an object/array using php_mssql.dll/IIS/PHP. I'm about to see if this is an issue with Drupal (I've noticed node_revision.teaser is stored in varchar(4000), so technically it should be cut off to 255 characters if queried). Does anyone have experience with this bug and how to get around it?

I believe the issue with that is the the ntwlib that they are using. I bet (although it did not specify in the bug report) they are using the default one that comes from the PECL extentions. This one is very outdated. If you use the updated one you should have no problem. I currently am having no issues. I can give you a link to the updated ntwlib if need be. I'm going to post it on my blog when I get a chance for easy access, as anyone connecting to a sql2005 db will have to use the updated ntwlib. Perhaps with Zend doing Zend-Core with MS, they'll release an updated version...but i doubt it as i believe they are going ODBC with the Zend-Core for IIS.

Although, something did just dawn on me. I am currently using varchar, where I believe i should be using nvarchar. nvarchar allows for the storing of uni-code data; but it takes twice as much space as varchar. I'm going to do some investigating on the issue, and see what the best course is and how much it matters.

*Update - I've confirmed that this IS an issue, at least with SQL 2000. I created a page that had over 255 characters of body content, and the preview showed only 254 characters rather than up to the 4000 the database can store for a node preview. This is not an issue with text-type columns, only varchar as far as I can tell, so this may not pose a huge problem for the rest of the system. Again, this is using the php_mssql.dll with an IIS install of PHP.

Souvent22, I'm using version 2000.80.194.0, which I believe is the latest. I had also tried nvarchar, but that didn't seem to make any difference.

pcorbett,

Hm. Ok, i'll take a look into this issue. Very strange. Yes, that is the most up-to-date version of ntwlib.

updating status to need work

- Earnest

A while back I solved the Limit problem using this method and it should work on 2000+

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&...

Here is the example they use.

Well that’s my 2 cents.

@Souvent22
Sorry about that I think I opened the follow up form before you posted then posted after you re-updating the issue.

Hm, ok, I think I have a solution. I don't totally like it though because I do not know how it will effect performance.

With that said, a work around is:

SELECT CONVERT(MyVarCharField as TEXT) MyVarCharField FROM [Table]

Of course, we will have to keep information about which columns in the tables are varchar fields (this is easy to aquire from the system/model table and could be cached unless a schema change is detected).

Some fields are currently TEXT (body of node_revisions, session table data field) and will not be cast.

Off the top of my head, I don't think this would be a large noticeable performance hit because (this is pure speculation) upon compiling the SQL statement, the output would just be mapped different, and then an error is thus thrown if the data type does not match. e.g. If I don't cast the mapping is varchar => varchar, and the varchar output handler handles/checks the output. varchar => text, the text handler handles the output. Again, this is just a random thought w/o anything to back it up. I'll work on getting some research/numbers to support or disporve this.

But for now, that's the only option I see. Thoughts?

It also should be noted that on the linux platform using the FreeTDS library 8.0+, it seems that this VARCHAR problem is not an issue.

Many threads have verified this, but can anyone here on the Drupal thread verify?

I think you're right, this is only a Windows/IIS issue with the php_mssql.dll driver. The FreeTDS driver is supposed to be much better, but as far as I know, it is only working with PHP v5.1.6 so far...

Not sure if there was a reason for this, but the menu table creation script seems to be off. When I go to access the menu settings, I receive the following error:

This is the SQL in system.install for MSSQL:

And this is the SQL for MySQL:

Looks like the description and weight columns are gone - is there a reason for this?

To make the menu settings page work for me, I needed to allow nulls for menu.page_arguments and menu.access_arguments (not sure why they didn't default to allow nulls upon install). Also, I needed to add a weight column of smallint type (to allow for negative numbers).

the schema may have changed latley. i haven't checked. i'll try and re-roll a patch tommorrow. thanks for bringing this up.

The discrepancy may be due to the fact that I am referencing the mysql install script for Drupal 5 and not 6. My goal is to use this before 6 is released. Do you think that it would be difficult to get this working for Drupal 5?

Re-sync with 6.0.

- updated local table (more languages table)

I did not have time to go through all 80 tables and dare I say how many fields to make sure that there were no other major schema changes. This patch is also untested. i'll test it when I get a chance, or if someone wants to take it on, please feel free to post any problems you hvae with it.

As far as 5.0, I do have a 5.0 patch ready to go and stable that I can release. I remember someone asked about a 5.0 patch. Let me know if you're interested in it. The DB-Engine will remain un-changed, it's only the schema's that are really different from 5.0 to 6.

Attachment	Size
mssql_install_driver_merged_6_0_1.patch	63.15 KB

I'm interested in your 5 patch, is it the one that you posted a bit earlier this year? Feel free to post your latest one. I'm not running a copy of 6 right now, but when I am I'd be happy to help test for you.

I'm currently trying to install the latest drupal release version with the above latest patch. I'm running the following:
- Win2k3 SP2 IIS6
- SQL Server Express 2005
- PHP 5.2.1 with ntwdblib.dll latest version 2000.80.194.0

I've created a database call odex, with a user called odex_db who is given the db_owner

When I first got to install.php, I had the page and keyed in the details. There after, the page went to install.php?profile=default. However, the page is totally empty. No database was created, no error message at all.

When I tried to redo it again, it seems that some process is locking up includes\bootstrap.inc and install.php is locked by some process. It seems to be stuck there. After I restarted the IIS process, the directory and files mentioned could then be deleted.

Anyone can provide help?

Further to my details above, I noticed the second time round that the patching was slightly off as seen below. Is there anything that I should be worried about?

patching file modules/locale/locale.install
Hunk #1 succeeded at 73 with fuzz 2 (offset -8 lines).
patching file includes/install.inc
Hunk #3 succeeded at 337 (offset 6 lines).
patching file modules/search/search.install
patching file modules/profile/profile.install
patching file modules/book/book.install
patching file modules/poll/poll.install
patching file modules/contact/contact.install
patching file modules/aggregator/aggregator.install
patching file modules/drupal/drupal.install
patching file modules/system/system.install
Hunk #1 succeeded at 1068 (offset -42 lines).
patching file modules/statistics/statistics.install
patching file modules/forum/forum.install
patching file includes/install.mssql.inc
patching file includes/database.mssql.inc

Regarding http://drupal.org/node/74308#comment-214179 ... the issue is with PDO ... if you use OLE_DB, ADODB or ODBC, you can by-pass this issue.