Moodle

Use nvarchar(max) instead of ntext for MSSQL 2005

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Deferred
  • Affects Version/s: 1.8
  • Fix Version/s: 1.9
  • Component/s: Database SQL/XMLDB
  • Labels:
    None
  • Environment:
    MSSQL 2005
  • Database:
    Microsoft SQL
  • Affected Branches:
    MOODLE_18_STABLE
  • Fixed Branches:
    MOODLE_19_STABLE

Description

See http://msdn2.microsoft.com/en-us/library/ms178158.aspx

The main reason is that ntext fields can't be compared properly so queries with UNIONs fail (for example, the big query in get_my_courses in lib/datalib.php, you get an error message like "The ntext data type cannot be selected as DISTINCT because it is not comparable."). nvarchar(max) is new in MSSQL 2005, so it won't be compatible with earlier versions.

Attachments

  1. Text File
    nojoin.patch
    23/Jun/07 8:34 AM
    5 kB
    Alan Trick
  2. Text File
    ntext_to_varcharmax.patch
    12/Jul/07 11:59 PM
    0.4 kB
    Josh Benner

Issue Links

This issue will be resolved by:
MDL-11270 Analyse the impact and roadmap to move, under MSSQL, from ntext to nvarchar(max) Major Open

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Alan Trick added a comment -

Note: I've made a workaround to make get_my_courses working by executing 3 separate SQL queries. If anyone is having problems with this issue and wants the workaround, let me now.

Show
Alan Trick added a comment - Note: I've made a workaround to make get_my_courses working by executing 3 separate SQL queries. If anyone is having problems with this issue and wants the workaround, let me now.
Hide
Permalink
Alan Trick added a comment -

Here's the patch I did in case anyone needs it.

Show
Alan Trick added a comment - Here's the patch I did in case anyone needs it.
Hide
Permalink
Josh Benner added a comment - - edited

A better solution is to have Moodle generate the database using nvarchar(max) instead of ntext.

According to http://msdn2.microsoft.com/en-us/library/ms187993.aspx , the ntext field type is deprecated in favor of nvarchar(max), which will operate as normal in queries where comparison of the field is required.

I have attached a patch that modifies the database generation rules for MSSQL (and MSSQL_N, since it just overrides) database generators such that Moodle 'TEXT' fields are created as 'NVARCHAR(MAX)'.

If you are currently operating a database, we had success modifying the table field type of mdl_course.summary in Management Studio with no loss of data.

Show
Josh Benner added a comment - - edited A better solution is to have Moodle generate the database using nvarchar(max) instead of ntext. According to http://msdn2.microsoft.com/en-us/library/ms187993.aspx , the ntext field type is deprecated in favor of nvarchar(max), which will operate as normal in queries where comparison of the field is required. I have attached a patch that modifies the database generation rules for MSSQL (and MSSQL_N, since it just overrides) database generators such that Moodle 'TEXT' fields are created as 'NVARCHAR(MAX)'. If you are currently operating a database, we had success modifying the table field type of mdl_course.summary in Management Studio with no loss of data.
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Hi,

while nvarchar seems to be the future... there are a lot of people running MSSQL < 2005.

The get_my_courses() behaviour was fixed some weeks ago (MDL-9381) and we must survive with ntexts at least until 2.0 (where a lot of DB improvements like prepared statements, placeholders and so on, will provide better and easier cross-db).

I leave this open pointing to 2.0 to avoid forgetting it.

Ciao

Show
Eloy Lafuente (stronk7) added a comment - Hi, while nvarchar seems to be the future... there are a lot of people running MSSQL < 2005. The get_my_courses() behaviour was fixed some weeks ago (MDL-9381) and we must survive with ntexts at least until 2.0 (where a lot of DB improvements like prepared statements, placeholders and so on, will provide better and easier cross-db). I leave this open pointing to 2.0 to avoid forgetting it. Ciao
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Hi,

I'm closing this now, pointing to MDL-11270 as new target to talk about the varchar(max) thing.

Note that we must provide one viable migration path for current installations apart from changing slightly the XMLDB stuff.

Ciao

Show
Eloy Lafuente (stronk7) added a comment - Hi, I'm closing this now, pointing to MDL-11270 as new target to talk about the varchar(max) thing. Note that we must provide one viable migration path for current installations apart from changing slightly the XMLDB stuff. Ciao

People

Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.