SqlServer default value mapping

[Mahesh]

Hi all,

Hope everyone is doing well.

Wanted to check something regarding debezium-sqlserver usage for CDC. Noticed that the bootstrap of the connectors manages mapping of default values based on schema definition. However, in case of sqlserver it looks like there are 2 things that wanted to get some clarification on and whether it needs a change to debezium-sqlserver or anything on our end.

Source: Client's SqlServer instance we don't have control over

Cannot parse column default value '(getdate())' to type 'datetime'. Expression evaluation is not supported.

This seems due to lack of functional defaults support in sqlserver part of debezium. Have noticed similar defaults being handled in oracle connector. Is this a matter of adding cases where the value expression is of functional defaults? If that's not the root cause, any help to get around the issue is much appreciated.

Cannot parse column default value '(0)' to type 'money'. Expression evaluation is not supported.

Regarding this, I've noticed that there was support introduced for literal types to deal with both single and double parenthesis during parsing so that defaults are extracted correctly. However, looks like for other types, like "money" in this instance only deals with double parenthesis and the value we are getting from schema is a single one. Because of which it's failing. Happy to be corrected on that if this is being interpreted wrongly. Otherwise, what would be way forward to manage parsing of that correctly.

For both the above cases, had a look at the source code and if they require changes to debezium-sqlserver codebase, happy to raise a PR. Also, any help with next steps to proceed on this would be much appreciated.

Cheers,

Mahesh.

 

[jiri.p...@gmail.com]

Hi,

a) I don't think Oracle connector handles functional defaults. The biggest issue with such values is the evaluation of the expression. IMHO it is a bit complicated and that's the reason why we opted for ignoring it. In relation to wrokaround it might make sense to introduce a SMT that would allow user to configure default values in schemas for defined tables/columns.

b) Please make sure to understand what is provided to the class. Is it '(0)' or '(((0)))'? In case of the latter we get in fact expression instead of constant. TBH I really don't recall why precise decimal types ae handled in a different way then the others. It might really be only an omission.

J.

[Mahesh]

Thanks for getting back so quick on this.

1) I was referring to this part of codebase where it handles temporal values accordingly, maybe not functional defaults. So, was wondering if something similar can be done for sqlserver. If that's somehow complicated for these to be handled for sqlserver, we will evaluate SMT

https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/OracleDefaultValueConverter.java#L189

2) Not sure I follow. I understand what is being read as part of the defaults and as mentioned in initial msg it is `(0)`. The reference for support of single or double parenthesis is based on the change done as part of https://issues.redhat.com/browse/DBZ-2698. which adds support for literal types but not decimal types. So, if there is no strong case for the way it is, does it make sense to extend that support for decimals too?