FWD » Core Development » Database

Ovidiu Maxiniuc wrote:

The order in which strings are sorted in p4gl is detected by the attached Progress procedure.

Nice. Please be sure to run this both on windev01 and lindev01, to make sure there are no surprises between them.

Is it possible to create new collations/locales for Windows itself (as we do on Linux) and then use these with SQL Server, or does SQL Server only work with its pre-packaged list of available collations?

Reading [[http://technet.microsoft.com/en-us/library/ms175194(v=sql.110).aspx]], I understand that SQL Server has a built-in set of collations that match those of windows. They are different resources, unlike PostgreSQL that uses the resources form Linux OS.

I googled a lot in search for 'custom', 'new' or 'user-defined' collations for SQL server and I could not find anything except questions of others.

Looking over the distribution and character groups treated by 4GL as equals, the correct collation should only have the _AI (accents ignored) sorting style because all accented letters are treated as equals by Progress sorting. On the other hand, _CS (case sensitivity) is irrelevant, as we handle this in application, using computed columns. _KS (Japanese kana characters, Hiragana and Katakana) sort style and _WS (multibyte, width-sensitive) are to be ignored as long as the imported data is (at least for the customer) encoded in ISO8859-15 code page (8-bit, single-byte coded character set).

To workaround this issue, I wonder if we could create some kind of mapping to be used just before persisting the character columns. Of course, they need to be mapped back in order to be correctly processed in application. But, in order that this idea o work, we need some MSSQL collation that has the same groups of equal characters (accented ones).

Please choose from the available collations the ones you feel are the closest, practical matches for Progress' collation behavior. I understand they will not be perfect matches. Please document the key differences for those few. We will let the customer decide which is most appropriate for them, based on their specific requirements, knowing that there will be known limitations that may affect the processing of their application.

I have run some scans over the database dump from lincon01, indexing the occurrences for each character in strings. I was hoping to discover that the customer is not using the whole set of Latin-9 (ISO-8859-1) but rather a subset of the range of 256. This would have allowed to pick on of the available collations to match the Progress collation for that subset.
As I understand that this is only a sample database (possible not very representative) this is only a case-study. Possibly, this is part of the solution, to choose the appropriate collation for each installation based on the character distribution.

• First of all, the first 64 characters (including white-spaces, digits and punctuation) are sorted in their strict natural order, which make it compatible with BIN -ary collations of MSSQL for this subset.
• Then, the following 64 characters up to 127 are also sorted (when case-sensible) also in their natural order. This includes the both uppercase and lowercase chars and the punctuation in the region. What is different from the 1 ^st subset is that the order is not strict in the way that accented forms of the letters are inserted with their unaccented forms to which they are considered equals (as you know, in Progress, À, Á, Â, Ã and Ä are all equals to A - but not Å, nor Æ). In this regard, most SQL_Latin1_General collations do the correct sorting except for Å and Æ that are sorted next to A.
• The characters starting from 128 are of two kind:
  - accented letters. These were discussed above.
  - other characters (the rest 67 of them). These are really messed up and I could not find any logical order. They are sorted last in Progress (after 127 in ASC order). In MSSQL, SQL_Latin1_General collations sort them usually somewhere before A and BIN collations usually sort them last.
• A special mention should be added for the \0 character. While most of MSSQL collations will sort it as 1 ^st, Progress collation is very weired, from my testing, it occurs between lowercase y and z. This is debatable however. Depending on how the database engine sees it. Most likely, MSSQL treats it as a normal character and sort it fist for most collations. On the other hand, I believe for Progress, the \0 character is 'invisible' so the next character of the string was used for sorting.

As conclusion, there is no MSSQL collation to satisfy Progress way of sorting. If the strings from database would not contain accented and other character from the second half of the set some *BIN collation would just work. If the strings would would not contain digits and punctuation up to A char(65), there are some Latin_General collations that might work.

The last idea that I have in mind and think of as a possible solution is to use a dedicated sorting column (perhaps the same computed column we are using now). This column would contain the initial string morphed using the table from com.goldencode.p2j.spi.ISO_8859_1Collator. Each occurrence of À, Á, Â, Ã and Ä will be replaced with the equal A, and so on, the second half of the ASCII table would be permuted to fit the order from the points table.
The problems here is to maintain this structure with data updates (maybe a native database trigger), and to force SQL to always use this column for sorting when the initial char value is used in some fancy expressions.

These are two useful references for Windows Custom Locales and Working with Custom Locales

It is definitely possible to implement a custom locale in Windows. Microsoft does this so that customers that aren't otherwise supported can support themselves. Although there is some text saying that sorting uses only "Microsoft-supplied sorts", I believe this is referencing that the core sorting APIs in WIN32 are not replaceable. We will need to use the Locale Builder tool (see below) which is provided to customize locales (or create a new one) to see what is possible. I would be very surprised if it wasn't possible to control/customize the collation tables inside the custom locale. The APIs in WIN32 can't be replaced, but by changing the tables the ordering can be controlled and customized. It should be possible otherwise custom locales would be impossible.

This reference explains SQLServer Collation and Unicode Support information. Based on SQLServer 2016, it is possible to use Windows locales and also something they call Binary locales. These are both different from the SQLServer locales which are now only used for compatibility with older SQLServer versions. It seems we have some new options.

Locale Builder 2.0 can be used to customize locales.