<commit_before>﻿/*
DROP TABLE [dbo].[Dispatch];
DROP TABLE [dbo].[Commits];
DROP TABLE [dbo].[Streams];
*/

CREATE TABLE [dbo].[Streams]
(
       [StreamId] [uniqueidentifier] NOT NULL CHECK ([StreamId] != 0x0),
       [Name] [nvarchar](256) NOT NULL,
       [HeadRevision] [bigint] NOT NULL CHECK ([HeadRevision] > 0),
       [SnapshotRevision] [bigint] NOT NULL CHECK ([SnapshotRevision] >= 0) DEFAULT(0),
       CONSTRAINT [PK_Streams] PRIMARY KEY CLUSTERED ([StreamId])
)

CREATE TABLE [dbo].[Commits]
(
       [StreamId] [uniqueidentifier] NOT NULL,
       [CommitId] [uniqueidentifier] NOT NULL CHECK ([CommitId] != 0x0),
       [StreamRevision] [bigint] NOT NULL CHECK ([StreamRevision] > 0),
       [CommitSequence] [bigint] NOT NULL CHECK ([CommitSequence] > 0),
       [SystemSequence] [bigint] IDENTITY(1,1) NOT NULL,
       [Headers] [varbinary](MAX) NULL CHECK ([Headers] IS NULL OR DATALENGTH([Headers]) > 0),
       [Payload] [varbinary](MAX) NOT NULL CHECK (DATALENGTH([Payload]) > 0),
       [Snapshot] [varbinary](MAX) NULL CHECK ([Snapshot] IS NULL OR DATALENGTH([Snapshot]) > 0),
       CONSTRAINT [PK_Commits] PRIMARY KEY CLUSTERED ([StreamId], [CommitSequence])
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits] ON [dbo].[Commits] ([StreamId], [CommitId])
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_Revisions] ON [dbo].[Commits] ([StreamId], [StreamRevision])

CREATE TABLE [dbo].[Dispatch]
(
       [DispatchId] [bigint] IDENTITY(1,1) NOT NULL,
       [StreamId] [uniqueidentifier] NOT NULL,
       [CommitSequence] [bigint] NOT NULL,
       CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED ([DispatchId])
)

ALTER TABLE [dbo].[Commits] WITH CHECK ADD CONSTRAINT [FK_Commits_Streams] FOREIGN KEY([StreamId])
REFERENCES [dbo].[Streams] ([StreamId])
ALTER TABLE [dbo].[Commits] CHECK CONSTRAINT [FK_Commits_Streams]

ALTER TABLE [dbo].[Dispatch] WITH CHECK ADD CONSTRAINT [FK_Dispatch_Commits] FOREIGN KEY([StreamId], [CommitSequence])
REFERENCES [dbo].[Commits] ([StreamId], [CommitSequence])
ALTER TABLE [dbo].[Dispatch] CHECK CONSTRAINT [FK_Dispatch_Commits]

GO
CREATE TRIGGER [dbo].[PreventChangesToCommits] ON [dbo].[Commits] FOR UPDATE
AS BEGIN

       IF (UPDATE([StreamId])
       OR UPDATE([CommitId])
       OR UPDATE([StreamRevision])
       OR UPDATE([CommitSequence])
       OR UPDATE([SystemSequence])
       OR UPDATE([Headers])
       OR UPDATE([Payload]))
       BEGIN
              RAISERROR('Commits cannot be modified.', 16, 1)
              ROLLBACK TRANSACTION
       END

END;<commit_msg>Added trigger for production databases to prevent accidental deletion of commits.<commit_after>﻿/*
DROP TABLE [dbo].[Dispatch];
DROP TABLE [dbo].[Commits];
DROP TABLE [dbo].[Streams];
*/

CREATE TABLE [dbo].[Streams]
(
       [StreamId] [uniqueidentifier] NOT NULL CHECK ([StreamId] != 0x0),
       [Name] [nvarchar](256) NOT NULL,
       [HeadRevision] [bigint] NOT NULL CHECK ([HeadRevision] > 0),
       [SnapshotRevision] [bigint] NOT NULL CHECK ([SnapshotRevision] >= 0) DEFAULT(0),
       CONSTRAINT [PK_Streams] PRIMARY KEY CLUSTERED ([StreamId])
)

CREATE TABLE [dbo].[Commits]
(
       [StreamId] [uniqueidentifier] NOT NULL,
       [CommitId] [uniqueidentifier] NOT NULL CHECK ([CommitId] != 0x0),
       [StreamRevision] [bigint] NOT NULL CHECK ([StreamRevision] > 0),
       [CommitSequence] [bigint] NOT NULL CHECK ([CommitSequence] > 0),
       [SystemSequence] [bigint] IDENTITY(1,1) NOT NULL,
       [Headers] [varbinary](MAX) NULL CHECK ([Headers] IS NULL OR DATALENGTH([Headers]) > 0),
       [Payload] [varbinary](MAX) NOT NULL CHECK (DATALENGTH([Payload]) > 0),
       [Snapshot] [varbinary](MAX) NULL CHECK ([Snapshot] IS NULL OR DATALENGTH([Snapshot]) > 0),
       CONSTRAINT [PK_Commits] PRIMARY KEY CLUSTERED ([StreamId], [CommitSequence])
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits] ON [dbo].[Commits] ([StreamId], [CommitId])
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_Revisions] ON [dbo].[Commits] ([StreamId], [StreamRevision])

CREATE TABLE [dbo].[Dispatch]
(
       [DispatchId] [bigint] IDENTITY(1,1) NOT NULL,
       [StreamId] [uniqueidentifier] NOT NULL,
       [CommitSequence] [bigint] NOT NULL,
       CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED ([DispatchId])
)

ALTER TABLE [dbo].[Commits] WITH CHECK ADD CONSTRAINT [FK_Commits_Streams] FOREIGN KEY([StreamId])
REFERENCES [dbo].[Streams] ([StreamId])
ALTER TABLE [dbo].[Commits] CHECK CONSTRAINT [FK_Commits_Streams]

ALTER TABLE [dbo].[Dispatch] WITH CHECK ADD CONSTRAINT [FK_Dispatch_Commits] FOREIGN KEY([StreamId], [CommitSequence])
REFERENCES [dbo].[Commits] ([StreamId], [CommitSequence])
ALTER TABLE [dbo].[Dispatch] CHECK CONSTRAINT [FK_Dispatch_Commits]

GO
CREATE TRIGGER [dbo].[PreventChangesToCommits] ON [dbo].[Commits] FOR UPDATE
AS BEGIN

       IF (UPDATE([StreamId])
       OR UPDATE([CommitId])
       OR UPDATE([StreamRevision])
       OR UPDATE([CommitSequence])
       OR UPDATE([SystemSequence])
       OR UPDATE([Headers])
       OR UPDATE([Payload]))
       BEGIN
              RAISERROR('Commits cannot be modified.', 16, 1);
              ROLLBACK TRANSACTION;
       END;

END;

/* NOTE: This trigger is recommended for production systems and may get in the way during development
         as it is designed to prevent removal of commits from EventStore's durable storage. */
GO
CREATE TRIGGER [dbo].[PreventDeletionOfCommits] ON [dbo].[Commits] FOR DELETE
AS BEGIN

	RAISERROR('Commits are immutable and cannot be deleted.', 16, 1);
	ROLLBACK TRANSACTION;

END;
{'fraction_non_alphanumeric': 0.1335628851032983, 'fraction_numerical': 0.008517578832910475, 'mean_word_length': 4.221381267738884, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3785791', 'n_tokens_mistral': 1935, 'n_tokens_neox': 1761, 'n_words': 517}