Open borisdj opened 5 years ago
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();
}
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?
Database | GUID Column | SequentialGuidType Value |
---|---|---|
Microsoft SQL Server | uniqueidentifier | SequentialAtEnd |
MySQL | char(36) | SequentialAsString |
Oracle | raw(16) | SequentialAsBinary |
PostgreSQL | uuid | SequentialAsString |
SQLite | varies | varies |
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.
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
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.
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.
For SqlServer Sequential Guid creation I have:
How to configure the same for binary order ?