TA5 Message Store DB creation statements

These statements may change and this post may not be updated promptly
dated 2021-10-13

MySQL:

-- ==================================================================================================
-- 
--  ThinkAutomation 5 Server Database Creation Script - MySQL
-- 
--  Revision:               2020.0.1
--  Author:                 Parker Software
--  Created On:             16/16/2020
--  Last Change Date:       25/08/2020
--  Changed By:             SP
-- 
-- ==================================================================================================
-- 

-- --------------------------------------------------------------------------------------------------
-- TABLES
-- --------------------------------------------------------------------------------------------------

-- --------------------------------------------------------------
-- Solutions
-- --------------------------------------------------------------

DELIMITER $$

CREATE TABLE Solutions 
(
	Id CHAR(24) NOT NULL,
	Name VARCHAR(500) NOT NULL,
	ToRemove BOOLEAN DEFAULT 0,
	Json LONGTEXT,
CONSTRAINT PK_Solutions PRIMARY KEY (Id)
)
$$

-- --------------------------------------------------------------
-- Message Sources
-- --------------------------------------------------------------
CREATE TABLE MessageSources
(
	Id CHAR(24) NOT NULL,
	SolutionId CHAR(24) NOT NULL,
	Name VARCHAR(500) NOT NULL,
	ToRemove BOOLEAN DEFAULT 0,
	Json LONGTEXT,
CONSTRAINT PK_MessageSources PRIMARY KEY (Id)
)
$$

-- --------------------------------------------------------------
-- Automations
-- --------------------------------------------------------------
CREATE TABLE Automations
(
	Id CHAR(24) NOT NULL,
	SolutionId CHAR(24) NOT NULL,
	Name VARCHAR(500) NOT NULL,
	ToRemove BOOLEAN DEFAULT 0,
	Json LONGTEXT,
CONSTRAINT PK_Automations PRIMARY KEY (Id)
)
$$
 
-- --------------------------------------------------------------
-- Message Store
-- --------------------------------------------------------------
CREATE TABLE MessageStore
(
	Id CHAR(24) NOT NULL ,
	SolutionId CHAR(24) NOT NULL ,
	MessageSourceId CHAR(24) NOT NULL ,
	IncomingUid VARCHAR(250) NOT NULL ,
	Subject VARCHAR(3000),
	SentFrom VARCHAR(3000),
	SentTo VARCHAR(3000),
	Importance CHAR(1) DEFAULT 'N',
	MessageDate DATETIME DEFAULT NOW(),
	MessageFlags INTEGER DEFAULT 0,
	MessageSize INTEGER DEFAULT 0,
	AutomationId CHAR(24) ,
	AutomationPending BOOLEAN DEFAULT 1,
	AutomationSuccess BOOLEAN DEFAULT 0,
	AutomationError VARCHAR(1000),
	AutomationReturnValue VARCHAR(1000),
	AutomationDate DATETIME DEFAULT NOW(),
	AutomationExecutionTime INTEGER DEFAULT 0,
	Attachments BOOLEAN DEFAULT 0,
	AttachmentNames VARCHAR(3000),
	ValidatePending SMALLINT DEFAULT 0,
	ValidateExpires DATETIME,
	Mime LONGTEXT,
CONSTRAINT PK_MessageStore PRIMARY KEY (Id)
)
$$

CREATE UNIQUE INDEX IX_MessageStore1 ON MessageStore(MessageSourceId,IncomingUid)
$$
 
CREATE INDEX IX_MessageStore2 ON MessageStore(MessageSourceId)
$$
 
CREATE INDEX IX_MessageStore3 ON MessageStore(AutomationId)
$$
 
CREATE INDEX IX_MessageStore4 ON MessageStore(SolutionId,MessageDate)
$$
 
-- --------------------------------------------------------------
-- Validation Temporary Storage
-- --------------------------------------------------------------
CREATE TABLE ValidationTemp
(
	Id CHAR(24) NOT NULL ,
	MessageStoreId CHAR(24) NOT NULL ,
	AutomationId CHAR(24),
	Dated DATETIME DEFAULT NOW(),
	ValidateActionId CHAR(24),
	ValidateJson TEXT,
	ValidateJsonResults TEXT,
CONSTRAINT PK_ValidationTemp PRIMARY KEY (Id)
)
$$

CREATE UNIQUE INDEX IX_ValidationTemp1 ON ValidationTemp(MessageStoreId)
$$

CREATE INDEX IX_ValidationTemp2 ON ValidationTemp(AutomationId)
$$
 
-- --------------------------------------------------------------
-- Message Source Log
-- --------------------------------------------------------------
CREATE TABLE MessageSourceLog 
(
	Id CHAR(24) NOT NULL,
	MessageSourceId CHAR(24) NOT NULL ,
	NodeName VARCHAR(25),
	DateStamp DATETIME DEFAULT NOW(),
	Error BOOLEAN DEFAULT 0,
	Line VARCHAR(3000),
CONSTRAINT PK_MessageSourceLog PRIMARY KEY (Id)	
)
$$

-- --------------------------------------------------------------
-- Automation Log
-- --------------------------------------------------------------
CREATE TABLE AutomationLog
(
	Id CHAR(24) NOT NULL ,
	MessageStoreId CHAR(24) NOT NULL ,
	AutomationId CHAR(24) NOT NULL ,
	NodeName VARCHAR(25) ,
	DateStamp DATETIME DEFAULT NOW(),
	Error BOOLEAN DEFAULT 0,
	LineNumber INTEGER DEFAULT 0,
	Line VARCHAR(3000),
CONSTRAINT PK_AutomationLog PRIMARY KEY (Id)
)
$$

CREATE INDEX IX_AutomationLog1 ON AutomationLog(MessageStoreId)
$$
 
-- --------------------------------------------------------------
-- Outbox
-- --------------------------------------------------------------
CREATE TABLE Outbox 
(
	Id CHAR(24) NOT NULL,
	MessageStoreId CHAR(24),
	SolutionId CHAR(24),
	AutomationId CHAR(24),
	ActionId CHAR(24),
	DateAdded DATETIME DEFAULT NOW(),
	ScheduledDate DATETIME DEFAULT NOW(),
	Scheduled BOOLEAN DEFAULT 0,
	Status TINYINT UNSIGNED DEFAULT 0 ,
	SendTo VARCHAR(3000) NOT NULL,
	ReplyTo VARCHAR(3000),
	SendFrom VARCHAR(3000),
	Subject VARCHAR(3000),
	Sending BOOLEAN DEFAULT 0,
	SendType INTEGER DEFAULT 0,
	RetryDate DATETIME DEFAULT NOW() ,
	Retries SMALLINT DEFAULT 0 ,
	Message LONGTEXT ,
CONSTRAINT PK_Outbox PRIMARY KEY (Id)
)
$$

CREATE INDEX IX_Outbox1 ON Outbox(ScheduledDate)
$$
 
-- --------------------------------------------------------------
-- Sent Items
-- --------------------------------------------------------------
CREATE TABLE SentItems 
(
	Id INTEGER AUTO_INCREMENT NOT NULL,
	SolutionId CHAR(24) NOT NULL,
	AutomationId CHAR(24) NOT NULL,
	MessageStoreId CHAR(24) NOT NULL,
	DateSent DATETIME DEFAULT NOW(),
	SentTo VARCHAR(3000) NOT NULL,
	SentFrom VARCHAR(3000),
	SentType INTEGER DEFAULT 0,
	Subject VARCHAR(3000),
	Scheduled BOOLEAN DEFAULT 0,
	Importance INTEGER DEFAULT 0,
	Attachments TINYINT DEFAULT 0,
	Success BOOLEAN DEFAULT 0,
	Result VARCHAR(250),
	MessageSize INTEGER DEFAULT 0,
	Message LONGTEXT ,
CONSTRAINT PK_SentItems PRIMARY KEY (Id)
)
$$

CREATE INDEX IX_SentItems1 ON SentItems(MessageStoreId)
$$
 
CREATE INDEX IX_SentItems2 ON SentItems(AutomationId)
$$
 
-- --------------------------------------------------------------
-- Day Summary
-- --------------------------------------------------------------
CREATE TABLE DaySummary
(
	Id INTEGER AUTO_INCREMENT NOT NULL,
	SolutionId CHAR(24) NOT NULL,
	Dated DATETIME DEFAULT NOW() NOT NULL,
	MessagesInLast DATETIME,
    MessagesIn INTEGER DEFAULT 0,
    MessagesOut INTEGER DEFAULT 0,
    MessagesStarted INTEGER DEFAULT 0,
    MessagesFailed INTEGER DEFAULT 0,
    MessagesFailedLast DATETIME ,
    MessagesCompleted INTEGER DEFAULT 0,
    MessagesCompletedLast DATETIME,
    BytesIn BIGINT DEFAULT 0,
    LastError VARCHAR(1000),
CONSTRAINT PK_DaySummary PRIMARY KEY (Id)
)
$$

CREATE INDEX IX_DaySummary1 ON DaySummary(SolutionId,Dated)
$$
 
-- --------------------------------------------------------------
-- Web Callbacks
-- --------------------------------------------------------------
CREATE TABLE WebCallBacks
(
	Id VARCHAR(100) NOT NULL,
	SolutionId CHAR(24) NOT NULL,
	AutomationId CHAR(24) NOT NULL,
	ActionId CHAR(24) NOT NULL,
	MessageStoreId CHAR(24),
	DateSubmitted DATETIME DEFAULT NOW() NOT NULL,
	DateRequested DATETIME DEFAULT NOW() NOT NULL,
	DateExpires DATETIME DEFAULT NOW() NOT NULL,
	Status INTEGER DEFAULT 0,
	ContentType VARCHAR(100),
	RequestParameters LONGTEXT,
	Response LONGTEXT,
CONSTRAINT PK_WebCallBacks PRIMARY KEY(Id)
)
$$
 
-- --------------------------------------------------------------
-- Variable Store
-- --------------------------------------------------------------
CREATE TABLE VariableStore (
	Id INTEGER AUTO_INCREMENT NOT NULL,
	ParentId VARCHAR(24) NOT NULL,
	NodeName VARCHAR(25),
	Name VARCHAR(250) NOT NULL,
	Dated DATETIME DEFAULT NOW(),
	DataType INTEGER DEFAULT 0,
	Value LONGTEXT,
CONSTRAINT PK_VariableStore PRIMARY KEY (Id)
)
$$

CREATE UNIQUE INDEX IX_VariableStore1 ON VariableStore(ParentId,NodeName,Name)
$$
 
-- --------------------------------------------------------------
-- Delete Message
-- --------------------------------------------------------------
CREATE PROCEDURE sp_DeleteMessage (p_Id CHAR(24))
BEGIN
  DECLARE v_Result int DEFAULT 0;
  DELETE FROM AutomationLog WHERE MessageStoreId=p_Id;
  DELETE FROM SentItems WHERE MessageStoreId=p_Id;
  DELETE FROM MessageStore WHERE Id = p_Id;
  SET v_Result=FOUND_ROWS();
  SELECT v_Result;
END
$$

-- --------------------------------------------------------------
-- Delete MessageSource
-- --------------------------------------------------------------
CREATE PROCEDURE sp_DeleteMessageSource (p_Id CHAR(24))
BEGIN
  DELETE FROM MessageStore WHERE MessageSourceId=p_Id;
  DELETE FROM MessageSourceLog WHERE MessageSourceId=p_id;
  DELETE FROM VariableStore WHERE ParentId=p_Id;
  DELETE FROM MessageSources WHERE Id = p_Id;
END
$$

-- --------------------------------------------------------------
-- Delete Automation
-- --------------------------------------------------------------
CREATE PROCEDURE sp_DeleteAutomation (p_Id CHAR(24))
BEGIN
  DELETE FROM WebCallBacks WHERE AutomationId=p_Id;
  DELETE FROM AutomationLog WHERE AutomationId=p_Id;
  DELETE FROM MessageStore WHERE AutomationId=p_Id;
  DELETE FROM SentItems WHERE AutomationId=p_Id;
  DELETE FROM ValidationTemp WHERE AutomationId=p_Id;
  DELETE FROM VariableStore WHERE ParentId=p_Id;
  DELETE FROM Outbox WHERE AutomationId=p_Id;
  DELETE FROM Automations WHERE Id = p_Id;
END
$$

-- --------------------------------------------------------------
-- Space Used
-- --------------------------------------------------------------
CREATE PROCEDURE sp_spaceused() 
BEGIN 
  SELECT table_schema AS database_name, CONCAT(ROUND(SUM(data_length + index_length + data_free)/ 1024 / 1024, 2), '(MB)') AS database_size, CONCAT(ROUND(SUM(data_free) / 1024 /1024, 2), '(MB)') AS unallocated_space 
  FROM information_schema.TABLES 
  WHERE table_schema = DATABASE() 
  GROUP BY table_schema; 
			
  SELECT CONCAT(ROUND(SUM(data_length + index_length + data_free) / 1024 /1024, 2), '(MB)') AS 'database_size', CONCAT(ROUND(SUM(data_length) / 1024 /1024, 2), '(MB)') AS data, CONCAT(ROUND(SUM(index_length) / 1024 /1024, 2), '(MB)') AS index_size, CONCAT(ROUND(SUM(data_free) / 1024 /1024, 2), '(MB)') AS unused 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = DATABASE(); 
		
END 
$$

DELIMITER ;

SQL Server:

--==================================================================================================
--
--  ThinkAutomation 5 Server Database Creation Script
--
--  Revision:               2020.0.1
--  Author:                 Parker Software
--  Created On:             24/01/2020
--  Last Change Date:       30/06/2020
--  Changed By:             SP
--
--==================================================================================================
--

----------------------------------------------------------------------------------------------------
-- TABLES
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------
-- Solutions (backup)
----------------------------------------------------------------
CREATE TABLE Solutions 
(
	[Id] CHAR(24) NOT NULL,
	[Name] VARCHAR(500) NOT NULL,
	[ToRemove] BIT DEFAULT 0,
	[Json] VARCHAR(MAX),
CONSTRAINT PK_Solutions PRIMARY KEY ([Id])
)
GO

----------------------------------------------------------------
-- Message Sources (backup)
----------------------------------------------------------------
CREATE TABLE MessageSources
(
	[Id] CHAR(24) NOT NULL,
	[SolutionId] CHAR(24) NOT NULL,
	[Name] VARCHAR(500) NOT NULL,
	[ToRemove] BIT DEFAULT 0,
	[Json] VARCHAR(MAX),
CONSTRAINT PK_MessageSources PRIMARY KEY ([Id])
)
GO

----------------------------------------------------------------
-- Automations(backup)
----------------------------------------------------------------
CREATE TABLE Automations
(
	[Id] CHAR(24) NOT NULL,
	[SolutionId] CHAR(24) NOT NULL,
	[Name] VARCHAR(500) NOT NULL,
	[ToRemove] BIT DEFAULT 0,
	[Json] VARCHAR(MAX),
CONSTRAINT PK_Automations PRIMARY KEY ([Id])
)
GO

----------------------------------------------------------------
-- Message Store
----------------------------------------------------------------
CREATE TABLE MessageStore
(
	[Id] CHAR(24) NOT NULL ,
	[SolutionId] CHAR(24) NOT NULL ,
	[MessageSourceId] CHAR(24) NOT NULL ,
	[IncomingUid] VARCHAR(250) COLLATE Latin1_General_CS_AS NOT NULL ,
	[Subject] NVARCHAR(3000),
	[SentFrom] VARCHAR(3000),
	[SentTo] VARCHAR(3000),
	[Importance] CHAR(1) DEFAULT 'N',
	[MessageDate] DATETIME DEFAULT GETDATE(),
	[MessageFlags] INTEGER DEFAULT 0,
	[MessageSize] INTEGER DEFAULT 0,
	[AutomationId] CHAR(24) ,
	[AutomationPending] BIT DEFAULT 1,
	[AutomationSuccess] BIT DEFAULT 0,
	[AutomationError] VARCHAR(3000),
	[AutomationReturnValue] NVARCHAR(3000),
	[AutomationDate] DATETIME DEFAULT GETDATE(),
	[AutomationExecutionTime] INTEGER DEFAULT 0,
	[Attachments] BIT DEFAULT 0,
	[AttachmentNames] NVARCHAR(3000),
	[ValidatePending] SMALLINT DEFAULT 0,
	[ValidateExpires] DATETIME,
	[Mime] VARCHAR(MAX),
CONSTRAINT PK_MessageStore PRIMARY KEY ([Id])
)
GO

CREATE UNIQUE INDEX IX_MessageStore1 ON MessageStore([MessageSourceId],[IncomingUid])
GO

CREATE INDEX IX_MessageStore2 ON MessageStore([MessageSourceId])
GO

CREATE INDEX IX_MessageStore3 ON MessageStore([AutomationId])
GO

CREATE INDEX IX_MessageStore4 ON MessageStore([SolutionId],[MessageDate]) 
GO

----------------------------------------------------------------
-- Validation Temporary Storage
----------------------------------------------------------------
CREATE TABLE ValidationTemp
(
	[Id] INTEGER IDENTITY NOT NULL ,
	[MessageStoreId] CHAR(24) NOT NULL ,
	[AutomationId] CHAR(24),
	[Dated] DATETIME DEFAULT GETDATE(),
	[ValidateActionId] CHAR(24),
	[ValidateJson] VARCHAR(MAX),
	[ValidateJsonResults] VARCHAR(MAX),
CONSTRAINT PK_ValidationTemp PRIMARY KEY ([Id])
)
GO

CREATE UNIQUE INDEX IX_ValidationTemp1 ON ValidationTemp([MessageStoreId])
GO

CREATE INDEX IX_ValidationTemp2 ON ValidationTemp([AutomationId])
GO

----------------------------------------------------------------
-- Message Source Log
----------------------------------------------------------------
CREATE TABLE MessageSourceLog 
(
	[Id] CHAR(24) NOT NULL,
	[MessageSourceId] CHAR(24) NOT NULL ,
	[NodeName] VARCHAR(25),
	[DateStamp] DATETIME DEFAULT GETDATE(),
	[Error] BIT DEFAULT 0,
	[Line] VARCHAR(1000),
CONSTRAINT PK_MessageSourceLog PRIMARY KEY ([Id])	
)
GO

----------------------------------------------------------------
-- Automation Log
----------------------------------------------------------------
CREATE TABLE AutomationLog
(
	[Id] CHAR(24) NOT NULL ,
	[MessageStoreId] CHAR(24) NOT NULL ,
	[AutomationId] CHAR(24) NOT NULL ,
	[NodeName] VARCHAR(25) ,
	[DateStamp] DATETIME DEFAULT GETDATE(),
	[Error] BIT DEFAULT 0,
	[LineNumber] INTEGER DEFAULT 0,
	[Line] VARCHAR(1000),
CONSTRAINT PK_AutomationLog PRIMARY KEY ([Id])
)
GO

CREATE INDEX IX_AutomationLog1 ON AutomationLog([MessageStoreId])
GO

----------------------------------------------------------------
-- Outbox
----------------------------------------------------------------
CREATE TABLE Outbox 
(
	[Id] CHAR(24) NOT NULL,
	[MessageStoreId] CHAR(24),
	[SolutionId] CHAR(24),
	[AutomationId] CHAR(24),
	[ActionId] CHAR(24),
	[DateAdded] DATETIME DEFAULT GETDATE(),
	[ScheduledDate] DATETIME DEFAULT GETDATE(),
	[Scheduled] BIT DEFAULT 0,
	[Status] TINYINT DEFAULT 0 ,
	[SendTo] VARCHAR(3000) NOT NULL,
	[ReplyTo] VARCHAR(1000),
	[SendFrom] VARCHAR(3000),
	[Subject] NVARCHAR(3000),
	[Sending] BIT DEFAULT 0,
	[SendType] INTEGER DEFAULT 0,
	[RetryDate] DATETIME DEFAULT GETDATE() ,
	[Retries] SMALLINT DEFAULT 0 ,
	[Message] VARCHAR(MAX) ,
CONSTRAINT PK_Outbox PRIMARY KEY ([Id])
)
GO

CREATE INDEX IX_Outbox1 ON Outbox([ScheduledDate])
GO

----------------------------------------------------------------
-- Sent Items
----------------------------------------------------------------
CREATE TABLE SentItems 
(
	[Id] INTEGER IDENTITY NOT NULL,
	[SolutionId] CHAR(24) NOT NULL,
	[AutomationId] CHAR(24) NOT NULL,
	[MessageStoreId] CHAR(24) NOT NULL,
	[DateSent] DATETIME DEFAULT GETDATE(),
	[SentTo] VARCHAR(3000) NOT NULL,
	[SentFrom] VARCHAR(3000),
	[SentType] INTEGER DEFAULT 0,
	[Subject] NVARCHAR(3000),
	[Scheduled] BIT DEFAULT 0,
	[Importance] INTEGER DEFAULT 0,
	[Attachments] BIT DEFAULT 0,
	[Success] BIT DEFAULT 0,
	[Result] VARCHAR(250),
	[MessageSize] INTEGER DEFAULT 0,
	[Message] VARCHAR(MAX) ,
CONSTRAINT PK_SentItems PRIMARY KEY ([Id])
)
GO

CREATE INDEX IX_SentItems1 ON SentItems([MessageStoreId])
GO

CREATE INDEX IX_SentItems2 ON SentItems([AutomationId])
GO

----------------------------------------------------------------
-- Day Summary
----------------------------------------------------------------
CREATE TABLE DaySummary
(
	[Id] INTEGER IDENTITY NOT NULL,
	[SolutionId] CHAR(24) NOT NULL,
	[Dated] SMALLDATETIME DEFAULT GETDATE() NOT NULL,
	[MessagesInLast] DATETIME,
	[MessagesIn] INTEGER DEFAULT 0,
	[MessagesOut] INTEGER DEFAULT 0,
	[MessagesStarted] INTEGER DEFAULT 0,
	[MessagesFailed] INTEGER DEFAULT 0,
	[MessagesFailedLast] DATETIME ,
	[MessagesCompleted] INTEGER DEFAULT 0,
	[MessagesCompletedLast] DATETIME,
	[BytesIn] BIGINT DEFAULT 0,
	[LastError] VARCHAR(1000),
CONSTRAINT PK_DaySummary PRIMARY KEY ([Id])
)
GO

CREATE INDEX IX_DaySummary1 ON DaySummary([SolutionId],[Dated])
GO

----------------------------------------------------------------
-- Web Callbacks
----------------------------------------------------------------
CREATE TABLE WebCallBacks
(
	[Id] VARCHAR(100) NOT NULL,
	[SolutionId] CHAR(24) NOT NULL,
	[AutomationId] CHAR(24) NOT NULL,
	[ActionId] CHAR(24) NOT NULL,
	[MessageStoreId] CHAR(24),
	[DateSubmitted] DATETIME DEFAULT GETDATE() NOT NULL,
	[DateRequested] DATETIME DEFAULT GETDATE() NOT NULL,
	[DateExpires] DATETIME DEFAULT GETDATE() NOT NULL,
	[Status] INTEGER DEFAULT 0,
	[ContentType] VARCHAR(100),
	[RequestParameters] VARCHAR(MAX),
	[Response] VARCHAR(MAX),
CONSTRAINT PK_WebCallBacks PRIMARY KEY([Id])
)
GO

----------------------------------------------------------------
-- Variable Store
----------------------------------------------------------------
CREATE TABLE VariableStore 
(
	[Id] INTEGER IDENTITY NOT NULL,
	[ParentId] VARCHAR(24) NOT NULL,
	[NodeName] VARCHAR(25),
	[Name] VARCHAR(250) NOT NULL,
	[Dated] DATETIME DEFAULT GETDATE(),
	[DataType] INTEGER DEFAULT 0,
	[Value] NVARCHAR(MAX),
CONSTRAINT PK_VariableStore PRIMARY KEY([Id])
)
GO

CREATE UNIQUE INDEX IX_VariableStore1 ON VariableStore([ParentId],[NodeName],[Name])
GO

----------------------------------------------------------------
-- Delete Message
----------------------------------------------------------------
CREATE PROCEDURE sp_DeleteMessage
  @Id CHAR(24)
AS
BEGIN
  DECLARE @Result int = 0
  DELETE FROM AutomationLog WHERE MessageStoreId=@Id;
  DELETE FROM SentItems WHERE MessageStoreId=@Id;
  DELETE FROM MessageStore WHERE Id = @Id;
  SELECT @Result=@@RowCount;
END
RETURN @Result;
GO

----------------------------------------------------------------
-- Delete MessageSource
----------------------------------------------------------------
CREATE PROCEDURE sp_DeleteMessageSource
  @Id CHAR(24)
AS
BEGIN
  SET NOCOUNT ON;
  DELETE FROM MessageStore WHERE MessageSourceId=@Id;
  DELETE FROM MessageSourceLog WHERE MessageSourceId=@Id;
  DELETE FROM VariableStore WHERE ParentId=@Id;
  DELETE FROM MessageSources WHERE Id = @Id;
END
GO

----------------------------------------------------------------
-- Delete Automation
----------------------------------------------------------------
CREATE PROCEDURE sp_DeleteAutomation
  @Id CHAR(24)
AS
BEGIN
  SET NOCOUNT ON;
  DELETE FROM WebCallBacks WHERE AutomationId=@Id;
  DELETE FROM AutomationLog WHERE AutomationId=@Id;
  DELETE FROM MessageStore WHERE AutomationId=@Id;
  DELETE FROM SentItems WHERE AutomationId=@Id;
  DELETE FROM ValidationTemp WHERE AutomationId=@Id;
  DELETE FROM VariableStore WHERE ParentId=@Id;
  DELETE FROM Outbox WHERE AutomationId=@Id;
  DELETE FROM Automations WHERE Id = @Id;
END
GO

SQLite:

-- ==================================================================================================
-- 
--  ThinkAutomation 5 Server Database Creation Script - SQLite
-- 
--  Revision:               2020.0.1
--  Author:                 Parker Software
--  Created On:             16/06/2020
--  Last Change Date:       16/06/2020
--  Changed By:             SP
-- 
-- ==================================================================================================
-- 

-- --------------------------------------------------------------------------------------------------
-- TABLES
-- --------------------------------------------------------------------------------------------------

-- --------------------------------------------------------------
-- Solutions
-- --------------------------------------------------------------
CREATE TABLE Solutions 
(
  [Id] CHAR(24) NOT NULL,
  [Name] VARCHAR(500) NOT NULL,
  [ToRemove] BOOLEAN DEFAULT 0,
  [Json] TEXT,
  CONSTRAINT PK_Solutions PRIMARY KEY ([Id])
);

-- --------------------------------------------------------------
-- Message Sources
-- --------------------------------------------------------------
CREATE TABLE MessageSources
(
  [Id] CHAR(24) NOT NULL,
  [SolutionId] CHAR(24) NOT NULL,
  [Name] VARCHAR(500) NOT NULL,
  [ToRemove] BOOLEAN DEFAULT 0,
  [Json] TEXT,
  CONSTRAINT PK_MessageSources PRIMARY KEY ([Id])
);

-- --------------------------------------------------------------
-- Automations
-- --------------------------------------------------------------
CREATE TABLE Automations
(
  [Id] CHAR(24) NOT NULL,
  [SolutionId] CHAR(24) NOT NULL,
  [Name] VARCHAR(500) NOT NULL,
  [ToRemove] BOOLEAN DEFAULT 0,
  [Json] TEXT,
  CONSTRAINT PK_Automations PRIMARY KEY ([Id])
);

-- --------------------------------------------------------------
-- Message Store
-- --------------------------------------------------------------
CREATE TABLE MessageStore
(
  [Id] CHAR(24) PRIMARY KEY ,
  [SolutionId] CHAR(24) NOT NULL ,
  [MessageSourceId] CHAR(24) NOT NULL ,
  [IncomingUid] VARCHAR(250) NOT NULL ,
  [Subject] TEXT,
  [SentFrom] TEXT,
  [SentTo] TEXT,
  [Importance] CHAR(1) DEFAULT 'N',
  [MessageDate] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [MessageFlags] INTEGER DEFAULT 0,
  [MessageSize] INTEGER DEFAULT 0,
  [AutomationId] CHAR(24) ,
  [AutomationPending] BOOLEAN DEFAULT 1,
  [AutomationSuccess] BOOLEAN DEFAULT 0,
  [AutomationError] TEXT,
  [AutomationReturnValue] TEXT,
  [AutomationDate] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [AutomationExecutionTime] INTEGER DEFAULT 0,
  [Attachments] BOOLEAN DEFAULT 0,
  [AttachmentNames] TEXT,
  [ValidatePending] SMALLINT DEFAULT 0,
  [ValidateExpires] DATETIME,
  [Mime] TEXT
);
CREATE UNIQUE INDEX IX_MessageStore1 ON MessageStore([MessageSourceId],[IncomingUid]);
CREATE INDEX IX_MessageStore2 ON MessageStore([MessageSourceId]);
CREATE INDEX IX_MessageStore3 ON MessageStore([AutomationId]);
CREATE INDEX IX_MessageStore4 ON MessageStore([SolutionId],[MessageDate]);
 
-- --------------------------------------------------------------
-- Validation Temporary Storage
-- --------------------------------------------------------------
CREATE TABLE ValidationTemp
(
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT ,
  [MessageStoreId] CHAR(24) NOT NULL ,
  [AutomationId] CHAR(24),
  [Dated] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [ValidateActionId] CHAR(24),
  [ValidateJson] TEXT,
  [ValidateJsonResults] TEXT
);
CREATE UNIQUE INDEX IX_ValidationTemp1 ON ValidationTemp([MessageStoreId]);
CREATE INDEX IX_ValidationTemp2 ON ValidationTemp([AutomationId]);
 
-- --------------------------------------------------------------
-- Message Source Log
-- --------------------------------------------------------------
CREATE TABLE MessageSourceLog 
(
  [Id] CHAR(24) NOT NULL,
  [MessageSourceId] CHAR(24) NOT NULL ,
  [NodeName] VARCHAR(25),
  [DateStamp] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [Error] BOOLEAN DEFAULT 0,
  [Line] VARCHAR(1000)
);

-- --------------------------------------------------------------
-- Automation Log
-- --------------------------------------------------------------
CREATE TABLE AutomationLog
(
  [Id] CHAR(24) NOT NULL ,
  [MessageStoreId] CHAR(24) NOT NULL ,
  [AutomationId] CHAR(24) NOT NULL ,
  [NodeName] VARCHAR(25) ,
  [DateStamp] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [Error] BOOLEAN DEFAULT 0,
  [LineNumber] INTEGER DEFAULT 0,
  [Line] VARCHAR(1000)
);
CREATE INDEX IX_AutomationLog1 ON AutomationLog([MessageStoreId]);
 
-- --------------------------------------------------------------
-- Outbox
-- --------------------------------------------------------------
CREATE TABLE Outbox 
(
  [Id] CHAR(24) PRIMARY KEY,
  [MessageStoreId] CHAR(24),
  [SolutionId] CHAR(24),
  [AutomationId] CHAR(24),
  [ActionId] CHAR(24),
  [DateAdded] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [ScheduledDate] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [Scheduled] BOOLEAN DEFAULT 0,
  [Status] TINYINT DEFAULT 0 ,
  [SendTo] TEXT NOT NULL,
  [ReplyTo] TEXT,
  [SendFrom] TEXT,
  [Subject] TEXT,
  [Sending] BOOLEAN DEFAULT 0,
  [SendType] INTEGER DEFAULT 0,
  [RetryDate] DATETIME DEFAULT CURRENT_TIMESTAMP ,
  [Retries] SMALLINT DEFAULT 0 ,
  [Message] TEXT
);
CREATE INDEX IX_Outbox1 ON Outbox([ScheduledDate]);
 
-- --------------------------------------------------------------
-- Sent Items
-- --------------------------------------------------------------
CREATE TABLE SentItems 
(
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
  [SolutionId] CHAR(24) NOT NULL,
  [AutomationId] CHAR(24) NOT NULL,
  [MessageStoreId] CHAR(24) NOT NULL,
  [DateSent] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [SentTo] TEXT NOT NULL,
  [SentFrom] TEXT,
  [SentType] INTEGER DEFAULT 0,
  [Subject] TEXT,
  [Scheduled] BOOLEAN DEFAULT 0,
  [Importance] INTEGER DEFAULT 0,
  [Attachments] TINYINT DEFAULT 0,
  [Success] BOOLEAN DEFAULT 0,
  [Result] VARCHAR(250),
  [MessageSize] INTEGER DEFAULT 0,
  [Message] TEXT 
);
CREATE INDEX IX_SentItems1 ON SentItems([MessageStoreId]);
CREATE INDEX IX_SentItems2 ON SentItems([AutomationId]);
 
-- --------------------------------------------------------------
-- Day Summary
-- --------------------------------------------------------------
CREATE TABLE DaySummary
(
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
  [SolutionId] CHAR(24) NOT NULL,
  [Dated] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [MessagesInLast] DATETIME,
  [MessagesIn] INTEGER DEFAULT 0,
  [MessagesOut] INTEGER DEFAULT 0,
  [MessagesStarted] INTEGER DEFAULT 0,
  [MessagesFailed] INTEGER DEFAULT 0,
  [MessagesFailedLast] DATETIME ,
  [MessagesCompleted] INTEGER DEFAULT 0,
  [MessagesCompletedLast] DATETIME,
  [BytesIn] BIGINT DEFAULT 0,
  [LastError] VARCHAR(1000)
);
CREATE INDEX IX_DaySummary1 ON DaySummary([SolutionId],[Dated]);
 
-- --------------------------------------------------------------
-- Web Callbacks
-- --------------------------------------------------------------
CREATE TABLE WebCallBacks
(
  [Id] VARCHAR(100) NOT NULL PRIMARY KEY,
  [SolutionId] CHAR(24) NOT NULL,
  [AutomationId] CHAR(24) NOT NULL,
  [ActionId] CHAR(24) NOT NULL,
  [MessageStoreId] CHAR(24),
  [DateSubmitted] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [DateRequested] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [DateExpires] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [Status] INTEGER DEFAULT 0,
  [ContentType] VARCHAR(100),
  [RequestParameters] TEXT,
  [Response] TEXT
);
 
-- --------------------------------------------------------------
-- Variable Store
-- --------------------------------------------------------------
CREATE TABLE VariableStore (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
  [ParentId] VARCHAR(24) NOT NULL,
  [NodeName] VARCHAR(25),
  [Name] VARCHAR(250) NOT NULL,
  [Dated] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [DataType] INTEGER DEFAULT 0,
  [Value] TEXT
);
CREATE UNIQUE INDEX IX_VariableStore1 ON VariableStore([ParentId],[NodeName],[Name]);