Good question. If web2py is using unicode and going for internationalization,
then NVARCHAR is the way to code. It stores all text as unicode, but it
requires twice the storage space of VARCHAR because it uses two bytes to encode
each character.
And I have to ask: what is the motivation for using CHAR(1) over BIT for
boolean fields?
On Mar 2, 2011, at 12:49 PM, Massimo Di Pierro wrote:
> Makes sense. The name is not the issue. The issue is what should the
> mssql2005 types be:
> types = {
> 'boolean': 'BIT',
> 'string': 'VARCHAR(%(length)s)',
> 'text': 'TEXT',
> 'password': 'VARCHAR(%(length)s)',
> 'blob': 'IMAGE',
> 'upload': 'VARCHAR(%(length)s)',
> 'integer': 'INT',
> 'double': 'FLOAT',
> 'decimal': 'NUMERIC(%(precision)s,%(scale)s)',
> 'date': 'DATETIME',
> 'time': 'CHAR(8)',
> 'datetime': 'DATETIME',
> 'id': 'INT IDENTITY PRIMARY KEY',
> 'reference': 'INT, CONSTRAINT %(constraint_name)s FOREIGN KEY
> (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %
> (on_delete_action)s',
> 'reference FK': ', CONSTRAINT FK_%(constraint_name)s FOREIGN KEY (%
> (field_name)s) REFERENCES %(foreign_key)s ON DELETE %
> (on_delete_action)s',
> 'reference TFK': ' CONSTRAINT FK_%(foreign_table)s_PK FOREIGN
> KEY (%(field_name)s) REFERENCES %(foreign_table)s (%(foreign_key)s) ON
> DELETE %(on_delete_action)s',
> 'list:integer': 'TEXT',
> 'list:string': 'TEXT',
> 'list:reference': 'TEXT',
> }
>
>
> or
>
> types = {
> 'boolean': 'CHAR(1)',
> 'string': 'NVARCHAR(%(length)s)',
> 'text': 'NTEXT',
> 'password': 'NVARCHAR(%(length)s)',
> 'blob': 'IMAGE',
> 'upload': 'NVARCHAR(%(length)s)',
> 'integer': 'INT',
> 'double': 'FLOAT',
> 'decimal': 'NUMERIC(%(precision)s,%(scale)s)',
> 'date': 'DATETIME',
> 'time': 'CHAR(8)',
> 'datetime': 'DATETIME',
> 'id': 'INT IDENTITY PRIMARY KEY',
> 'reference': 'INT, CONSTRAINT %(constraint_name)s FOREIGN KEY
> (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %
> (on_delete_action)s',
> 'reference FK': ', CONSTRAINT FK_%(constraint_name)s FOREIGN KEY (%
> (field_name)s) REFERENCES %(foreign_key)s ON DELETE %
> (on_delete_action)s',
> 'reference TFK': ' CONSTRAINT FK_%(foreign_table)s_PK FOREIGN
> KEY (%(field_name)s) REFERENCES %(foreign_table)s (%(foreign_key)s) ON
> DELETE %(on_delete_action)s',
> 'list:integer': 'NTEXT',
> 'list:string': 'NTEXT',
> 'list:reference': 'NTEXT',
> }
>
> and should the string representation in SQL be 'value' or N'value' ?
>
>
>
> On Mar 2, 11:38 am, Ross Peoples <[email protected]> wrote:
>> Actually thinking about this a bit more, mssql should be used for MSSQL
>> 2000, and mssql2005 instead of mssql2, and mssql3. The mssql will be
>> compatible with 2000, 2005, and 2008, while mssql2005 will be compatible
>> with 2005, 2008, and up. What do you think about that?
>>
>> On Mar 2, 2011, at 10:52 AM, Massimo Di Pierro wrote:
>>
>>
>>
>>
>>
>>
>>
>>> Let's create a mssql3 that does it by the book (or do you think MS
>>> will keep changing the API?)
>>
>>> On Mar 2, 9:11 am, Ross Peoples <[email protected]> wrote:
>>>> The only I problem I see is that MSSQL 2000 will not understand
>>>> varchar(max). For reference, there is a StackOverflow question that relates
>>>> to
>>>> this:http://stackoverflow.com/questions/737755/varcharmax-ms-sql-server-20...
