Using Memory Sqlite for testing, but having customSQL on model for SQLServer

ServiceStack.OrmLite
1

I am trying to create some unit tests in my SS project. I am using some CustomFieldAttributes on my model, for example so set VARCHAR(MAX) on a field.
Now in my Unit Test I want to use the :memory: provider with Sqlite. However, it gives me now errors on this fields when trying to create these tables.
Can I “hint” in the attribute that it is only for a specific provider?

2

Data models that use RDBMS-specific features can only be used in that RDBMS. If you need to support multiple RDBMS’s only use RDBMS-agnostic attributes, e.g. for specifying Max text you can instead use:

[StringLength(StringLengthAttribute.MaxText)]
public string Text { get; set; }
3

Would it be an idea to:

  • or extend the CustomFieldAttribute to optionally include a provider/dialect?
  • a hook into the provider towards GetColumnDefinition?

I am currently going towards the last path by creating a new class with base SqliteOrmLiteDialectProvider and then overriding the GetColumnDefinition method; inhere I will check upon the attribute and replace it with something more specific towards Sqlite.

I understand the idea, but this is specific to testing, so maybe more support towards this would be nice.

4

Attributes can only hold const values, so you can’t include an object instance, best you can do is include the name of the DialectProvider. Attributes don’t have the concept of applying to selected providers which would complicate the API surface with awkward overloads. The way we supported custom SQL fragments for different RDBMS’s were to use OrmLite Variables.

I’ve also added support for replacing variable placeholders in the [CustomField] attribute so another way of specifying a Max Text attribute can be done with:

[CustomField(OrmLiteVariables.MaxText)]
public string MaxText { get; set; }

Where MaxText is a variable placeholder surrounded with {} like {MAX_TEXT} which gets replaced with the Dialect-specific value in DialectProvider.Variables dictionary.

MaxText is pre-populated by default, but you could register custom variables for different DialectProviders with:

SqlServerDialect.Provider.Variables["{MAX_TEXT}"] = "VARCHAR(MAX)";
SqliteDialect.Provider.Variables["{MAX_TEXT}"] = "VARCHAR(1000000)";

This change is available from v5.1.1 that’s now available on MyGet.

5

Ok! That’s a nice addition.
However, it use more than just MaxText or SystemUTC. For example, I have VARCHAR and NVARCHAR in combination with size and MAX values (I basically use 4 different attributes for it). Would it be possible to use
[CustomField("{MY_STUFF}")] and add that variable to the Variables dictionary?

6

Right, you can use your own variables, e.g:

[CustomField("{MY_STUFF}")] 
public string Text { get; set; }

And register what {MY_STUFF} should be replaced with in each provider:

SqlServerDialect.Provider.Variables["{MY_STUFF}"] = "...";
SqliteDialect.Provider.Variables["{MY_STUFF}"] = "...";
```
7

Great stuff! Looking forward to use it.

8

One more thing… would it be possible (future) to have parameters as well? For example to add a length, precision, etc?

9

No, the SQL and template solution is opaque, any modifiers can be included as part of the name.

We already have the [Decimal] attribute for an RDBMS agnostic way to define real number with custom length and precision. [CustomField] should be rarely used for data types that can’t be expressed with existing attributes.

10

I was thinking about VARCHAR, NVARCHAR difference for example. Not sure if NVARCHAR is recognized by Sqlite.

11

Again, the variable substitution is opaque. If you want different substitution, register a different variable. You can use the OrmLiteVariables.MaxTextUnicode for places where you want unicode, e.g:

[CustomField(OrmLiteVariables.MaxTextUnicode)]
public string Text { get; set; }

Where it will use a Unicode data type for RDBMS’s that support it, or a standard text field for RDBMS’s that don’t. OrmLiteVariables are going to remain a simple variable substitution as they are, use a different variable name for any customizations/variations you want to support, you can apply any custom logic with how the variables are registered with each DialectProvider.