nos-sql-store

SQL session store for IBI Data's node-starter

Requirements

You need to set up the sessions table and all stored procedures to keep it updated. Run the following script (edit your database name in the USE statement)

USE [<your_db_name_here>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sessions](
	[sid] [varchar](255) NOT NULL,
	[session] [varchar](max) NOT NULL,
	[expires] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - all method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_all]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		*
	FROM
		[sessions]


END


-- grant exec on [proc_sessions_all] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - clear method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_clear]
	@sid VARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	TRUNCATE TABLE [sessions]


END


-- grant exec on [proc_sessions_clear] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - destroy method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_destroy]
	@sid VARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM [sessions] WHERE [sid] = @sid


END


-- grant exec on [proc_sessions_destroy] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - get method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_get]
	@sid VARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT TOP 1
		[session]
	FROM
		[sessions]
	WHERE
		[sid] = @sid


END


-- grant exec on [proc_sessions_get] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - length method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_length]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		COUNT([sid]) AS howmany
	FROM
		[sessions]


END


-- grant exec on [proc_sessions_length] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - set method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_set]
	@sid VARCHAR(255),
	@session VARCHAR(MAX),
	@expires DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	MERGE INTO [sessions] WITH (HOLDLOCK) S
	USING (VALUES(@sid, @session)) AS NS ([sid], [session]) ON (S.[sid] = NS.[sid])
	WHEN MATCHED THEN
		UPDATE SET
			S.[session] = @session,
			S.[expires] = @expires
	WHEN NOT MATCHED THEN
		INSERT ([sid], [session], [expires])
		VALUES (@sid, @session, @expires);

END


-- grant exec on [proc_sessions_set] to webuser
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - touch method
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_touch]
	@sid VARCHAR(255),
	@expires DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE [sessions] SET [expires] = @expires WHERE [sid] = @sid

END


-- grant exec on [proc_sessions_touch] to webuser
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author			Justin Williamson
-- Date:			4/15/2021
-- Description:		Sessions - delete all expired
-- =============================================
CREATE PROCEDURE [dbo].[proc_sessions_autoremove]
AS
BEGIN
	DELETE FROM [sessions] WHERE expires < GETDATE()
END


-- grant exec on [proc_sessions_autoremove] to webuser