0
2

[–] PuttItOut 0 points 2 points (+2|-0) ago 

I'm willing to accept I was wrong as my emotions where high when I realized collation actually doesn't matter in PG.

As far as this pull request goes, we do not need to use citext on the voat_users.sql as we use the Core Identity and they handle this via using NORMALIZED__ columns in areas that matter.

So we shouldn't change this schema if I'm thinking correctly.

@logos_ethos

0
1

[–] logos_ethos 0 points 1 point (+1|-0) ago  (edited ago)

Collation does matter. It defaults to UTF-8, which is appropriate for web usage. It is also used for sorting, as different locales have different sort orders for their symbols. This affects indexes and comparison operations.

@FuzzyWords

Personally, I would still use citext just to retain the exact behavior of the other database software that you use. Maybe it works now, but you might add something later that would introduce different behavior on the database software that you support.

So I just took a look at Entity Framework. Like I said before, I do not know .Net very well. I am not seeing positive things about Entity Framework and citext. All of the complaints are very old, so maybe the situation has improved.

If the unit tests do not go well, then I can look into the non-citext methods, which will solve bugs as they come in. It will probably leave a few bugs that we cannot find right away. SQL at the application level will have to change, and I do not know Entity Framework well enough to fix that by myself. I will be glad to work with anyone who wants to work with me on it.

0
1

[–] logos_ethos 0 points 1 point (+1|-0) ago  (edited ago)

@FuzzyWords @PuttItOut

Again, I am not sure how Entity Framework might deal with citext, but I found a few things to try:

It seems like https://msdn.microsoft.com/en-us/library/system.data.entity.modelconfiguration.configuration.stringcolumnconfiguration.hascolumntype(v=vs.113).aspx can be used to accept citext:

This seems to set it globally, which means that you probably want to use citext only (no varchar/text) for strings.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Properties<string>()
        .Configure(s => s.HasColumnType("public.citext") );

    base.OnModelCreating(modelBuilder);
}

Or for each column:

[Column(TypeName = "public.citext")]

You may need to delete the "public." and set search_path to 'public,dbo' That might be the sane thing to do anyway, as there will probably be other things in the public schema that you want to use and do not want to specify the full path to each time that you use them.

If Entity Framework insists on putting a length on strings (if HasMaxLength cannot be removed some how), I can fork the citext extension to make it accept a length. However, you need to be running your own PostgreSQL server if you want to use custom extensions.

I might be able to setup a Windows cloud server to help test the EF stuff. I might need help though.