Caching with SQL Server

The GoTo.Bible application supports caching of provider API calls using Microsoft SQL Server, MySQL, or In-Memory Caching.

Table Structure

CREATE TABLE [dbo].[Cache](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
	PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

User Secret Configuration

The following configuration can be using with SQL LocalDB, assuming you have already created a database called GoToBible:

dotnet user-secrets set "Providers:Cache:DatabaseProvider" "mssql"
dotnet user-secrets set "Providers:Cache:ConnectionString" "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=GoToBible;Integrated Security=True;"
dotnet user-secrets set "Providers:Cache:SchemaName" "dbo"
dotnet user-secrets set "Providers:Cache:TableName" "Cache"

Setup the database

See https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb for help.

Microsoft SQL Server 2019 LocalDB

"C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SqlLocalDB.exe" create GoToBible
"C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SqlLocalDB.exe" start GoToBible

Windows Application Settings Configuration

If caching is being configured by the GoTo.Bible Windows app, the connection string is stored in the application configuration file (%LOCALAPPDATA%\Conglomo\GoToBible.Windows_StrongName_iyzagyb45p0rit0gsgf0v3s4zgb3fcuo\1.0.0.0\user.config), with the table name GoToBible:

Table Structure

CREATE TABLE [dbo].[GoToBible](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
	PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO