Feature request: Support for setting a default collation

[magnusbakken]

I'm trying to use SQLite in integration tests for a codebase where the DB is normally SQL Server. In our SQL Server setup, all collations are case-insensitive by default. As in SQL Server, I've gathered that case-insensitivity in SQLite can be enforced using a column-level collation. The challenge I have is that SQLite doesn't appear to have a notion of a default collation.

I could decorate all my string properties with the CollationAttribute from this project, but I would prefer not to introduce a dependency on SQLite.CodeFirst in the project that contains the entity classes, because SQLite will only be used for integration tests. It would also be far easier to do this if it was possible to set a default collation, rather than decorating every string property with CollationAttribute.

Probably the most elegant thing would if it was possible to do it with some sort of CaseInsensitiveStringsConvention, which would assign the specified collation for all fields of type string. I think this is possible in the most recent version of EF Core, but not in EF6.

I made a proof of concept of my idea in #162. If there already exists a good way to do this currently, I'm happy to adopt that instead.

[msallin]

https://github.com/msallin/SQLiteCodeFirst/releases/tag/v1.7.0.34 & https://www.nuget.org/packages/SQLite.CodeFirst/1.7.0.34

[Ahmed-Abdelhameed]

@msallin It looks like you have to create a custom initializer that directly inherits SqliteInitializerBase to be able to use this (e.g., you can't use SqliteCreateDatabaseIfNotExists or even create a custom initializer that is derived from it). Was that intentional?

[msallin]

Hi @Ahmed-Abdelhameed

You may use SqliteSqlGenerator or SqliteDatabaseCreator instead of the initializer. Can you elaborate on your use case/scenario?

[Ahmed-Abdelhameed]

Hi @msallin, thanks for your reply!

I don't really need to do any other customizations when creating the database, so rather than using SqliteDatabaseCreator, I could just create my own custom CreateDatabaseIfNotExists initializer with the desired collation. It's just the fact that the logic already exists in the SqliteCreateDatabaseIfNotExists class, so I thought that exposing the collation (e.g., via a constructor param) in the supported initializers might come in handy. Currently, it's only exposed in the base class (i.e., SqliteInitializerBase) because it's in a protected constructor, so I'm not sure if that was an oversight or if there's actually a good reason to make that constructor protected and not expose the collation in any other way.

P.S. I had made a typo in my original comment, so it might've been unclear. I just fixed that (it was missing the word "can't").

[msallin]

Ah, I understand. I do not remember what I thought when I wrote the code, but I can't think of a reason it should be like it is. I'll change it in the near future.