README
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