Binary order for PostgreSql and Sqlite what Provider or Strategy to use.

[borisdj]

For SqlServer Sequential Guid creation I have:

public static class SeqGuid
{
    private static ICombProvider SqlNoRepeatCombs = new SqlCombProvider(new UnixDateTimeStrategy(), new UtcNoRepeatTimestampProvider().GetTimestamp);

    public static Guid Create()
    {
        return SqlNoRepeatCombs.Create();
    }
}

How to configure the same for binary order ?

[richardtallent]

Hi Boris,

This is untested, but something like this should work. I've set the IncrementMs lower since the UnixDateTimeStrategy has 1ms resolution and thus we don't need to bump the timestamp as much when there is a collision as we have to with the embedded MSSQL datetime value.

public static class SeqGuid {
  private static readonly ICombProvider provider;
  static SeqGuid() {
    var s = new UnixDateTimeStrategy();
    var t = new UtcNoRepeatTimestampProvider() { IncrementMs = 2 };
    provider = new PostgreSqlCombProvider(s, t.GetTimestamp);
  }
  public static Guid Create() => provider.Create();
}

[borisdj]

Thx
Additionaly
PostgreSqlCombProvider is for PostgreSql which uses SequentialAsString.
Sqlite when storing Guid in BLOB uses SequentialAsBinary, which is different.
I have tested it with Guid from this class SequentialGuid
Only have replaces lines 91:

var totalBytes = guidBytes.Concat(sequenceBytes).ToArray();

with

byte[] totalBytes = null;
switch (GuidType)
{
    case SequentialGuidType.SequentialAsString:
        totalBytes = sequenceBytes.Concat(guidBytes).ToArray();
        Array.Reverse(totalBytes, 0, 4);
        Array.Reverse(totalBytes, 4, 2);
        break;
    case SequentialGuidType.SequentialAtEnd:
        totalBytes = guidBytes.Concat(sequenceBytes).ToArray();
        break;
    case SequentialGuidType.SequentialAsBinary:
        totalBytes = sequenceBytes.Concat(guidBytes).ToArray();
        break;
}

Where SequentialGuidType is enum:

public enum SequentialGuidType
{
    /// <summary>
    /// The GUID should be sequential when formatted using the
    /// <see cref="Guid.ToString()" /> method. Best for PostgreSQL.
    /// </summary>
    SequentialAsString,

    /// <summary>
    /// The GUID should be sequential when formatted using the
    /// <see cref="Guid.ToByteArray" /> method.
    /// </summary>
    SequentialAsBinary,

    /// <summary>
    /// The sequential portion of the GUID should be located at the end
    /// of the Data4 block. Best for MsSQL.
    /// </summary>
    SequentialAtEnd
}

This way I get correct order in Sqlite.

Does your lib. have an BinaryCombProvider or SqliteCombProvider or how to get similar result?

Orders:

Database	GUID Column	SequentialGuidType Value
Microsoft SQL Server	uniqueidentifier	SequentialAtEnd
MySQL	char(36)	SequentialAsString
Oracle	raw(16)	SequentialAsBinary
PostgreSQL	uuid	SequentialAsString
SQLite	varies	varies

[richardtallent]

Good to know! I don't have a SQLiteCombProvider, but adding one sounds like an excellent idea, especially since that will also provide Oracle compatibility (based on your chart, I haven't used Oracle in over 20 years).

I'll re-open and mark this as an enhancement opportunity.

[fubar-coder]

I created a GIST with the original articles converted to Markdown, because the original article isn't available anymore: https://gist.github.com/fubar-coder/393cb9d41db707bd8730df0bd2a92d06

[marchy]

Did you ever end up creating a Binary provider to support Sqlite?

We do all our new feature development on Sqlite until the model stabilizes (faster iteration as there is no need for migrations etc.), and only in the last stretch add the migrations and indexes needed for full DB persistence (MSSQL, Postgres etc.) etc.

Would be great to have the third provider added to support the trio of SQLite/Postgres/MSSQL out of the box.

[richardtallent]

I'm open to it, if someone can do the work in the form of a PR with tests.

I've barely ever touched sqlite, and don't have the free time currently to dump into what it would take to add and test another provider.