-
-
Save devlead/8745afdc180f506a6440 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE dbo.Events | |
DROP TABLE dbo.Streams | |
CREATE TABLE dbo.Streams( | |
StreamId CHAR(40) NOT NULL, | |
StreamIdOriginal NVARCHAR(1000) NOT NULL, | |
StreamIdInternal INT IDENTITY(1,1) NOT NULL, | |
IsDeleted BIT NOT NULL DEFAULT ((0)), | |
CONSTRAINT PK_Streams PRIMARY KEY CLUSTERED (StreamIdInternal) | |
); | |
CREATE UNIQUE NONCLUSTERED INDEX IX_Streams_StreamId ON dbo.Streams (StreamId); | |
CREATE TABLE dbo.Events( | |
StreamIdInternal INT NOT NULL, | |
[Checkpoint] int IDENTITY(1,1) NOT NULL, | |
EventId UNIQUEIDENTIFIER NOT NULL, | |
SequenceNumber INT NOT NULL, | |
Created DATETIME NOT NULL, | |
[Type] NVARCHAR(128) NOT NULL, | |
JsonData NVARCHAR(max) NOT NULL, | |
JsonMetadata NVARCHAR(max), | |
CONSTRAINT PK_Events PRIMARY KEY CLUSTERED ([Checkpoint]), | |
CONSTRAINT FK_Events_Streams FOREIGN KEY (StreamIdInternal) REFERENCES dbo.Streams(StreamIdInternal) | |
); | |
CREATE UNIQUE NONCLUSTERED INDEX [IX_Events_StreamIdInternal_SequenceNumber] ON [dbo].[Events] ([StreamIdInternal], [SequenceNumber]); | |
-- ExpectedVersion.NoStream | |
-- Will be inserting 1 - N events(row) in a transactions. Just using 3 here for demo. | |
DECLARE @sequenceNumber INT = 0; | |
DECLARE @streamId CHAR(40) = 'stream-1'; | |
CREATE TABLE #Events ( | |
EventId UNIQUEIDENTIFIER default(NEWID()) NULL , | |
SequenceNumber int identity(0,1) NOT NULL , | |
Created DATETIME default(GETDATE()) NULL , | |
[Type] NVARCHAR(128) NOT NULL , | |
JsonData NVARCHAR(max) NULL , | |
JsonMetadata NVARCHAR(max) NULL | |
) | |
INSERT INTO #Events | |
( | |
[Type] , | |
JsonData , | |
JsonMetadata | |
) VALUES | |
('type1', '\"data1\"', '\"meta1\"'), | |
('type2', '\"data2\"', '\"meta2\"'), | |
('type3', '\"data3\"', '\"meta3\"') | |
-- Actual SQL statement of interest | |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | |
BEGIN TRANSACTION CreateStream | |
DECLARE @count AS INT; | |
DECLARE @streamIdInternal AS INT; | |
SELECT @count = COUNT(*) FROM [dbo].[Streams] WHERE [StreamId]=@streamId; | |
PRINT @count; | |
BEGIN | |
-- Could generate this at runtime ; but the paramaterization feels icky | |
INSERT INTO dbo.Streams (StreamId, StreamIdOriginal) VALUES (@streamId, @streamId); | |
SELECT @streamIdInternal = SCOPE_IDENTITY(); | |
INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata) | |
SELECT @streamIdInternal, | |
SequenceNumber, | |
EventId, | |
Created, | |
[Type], | |
JsonData, | |
JsonMetadata | |
FROM #Events | |
END | |
SELECT @streamIdInternal | |
COMMIT TRANSACTION CreateStream | |
DROP TABLE #Events | |
SELECT * FROM dbo.Streams | |
SELECT * FROM dbo.Events |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment