You are viewing a single comment's thread.

view the rest of the comments →

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.

0
2

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

So I've imported the schema and run the tests, and they all failed with:

The field '<some field here>' has a type currently unknown to Npgsql (OID 340359). You can retrieve it as a string by marking it as unknown, please see the FAQ.

The code above is using EF6 while we are using EF Core, similar but different. If I am going to use this I can not annotate models because I would have to use conditional compilation and I do not want to add this complexity to Voat's code base.

This seems that once we find an alternate way to perform the following line we can actually use this technique.

modelBuilder.Properties<string>()
        .Configure(s => s.HasColumnType("public.citext") );

Attn @FuzzyWords

0
1

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

We are looking into this. I have a few concerns but we are still going to see if this is possible without significant changes.

Very much appreciate all your help.

Code should be easy to download and run if you have a .net core setup working.