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