cubicweb #1809383 CWAttributeAddOp crash on sql server [validation pending]

I have a migration script which crashes on MS SQL.

The script does add_cube('worker'), which creates the etype CWWorker and with the current version triggers CWAttributeAddOp for

last_ping = Datetime(required=True, default='NOW',
                     description='date of the last ping sent by the Worker')

This fails with sql server:

2011-07-05 12:42:42 - (cubicweb.sources.system) CRITICAL: sql: u'UPDATE cw_CWWorker SET cw_last_ping=%(default)s'
 args: {'default': u'NOW'}
dbms message: '22007'

2207 stands for:

DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting datetime from character string. (241) (SQLExecDirectW)')

And then things go very bad because we try to revert_precommit_event and DROP TABLE cw_CWWorker and this fails because the transaction creating the table has been rollbacked...

The problem is that CWAttributeAddOp uses:

# set default value, using sql for performance and to avoid
# modification_date update
if default:
    session.system_sql('UPDATE %s SET %s=%%(default)s' % (table, column),
                       {'default': default})

which fails with sql server because:

  1. NOW is not understood by sql server, so we should probably use lgdb to get the appropriate keyword (which requires a fix in lgdb as the correct version, getDate() is not in there)
  2. the substituded value for this in sql server cannot be passed in a subsitution, it has to be inlined, otherwise you get 22007 again
priorityimportant
typebug
done in3.14.3
load0.000
load left0.000
closed by<not specified>