Procedures / Functions

[chameleon].[Cache.SetItem]

This stored-procedure adds an item to cache (chameleon.Cache table).

		  CREATE OR ALTER PROCEDURE [chameleon].[Cache.SetItem]
(
	@key		varchar(200),
	@value		nvarchar(max),
	@options	varchar(500)	-- example: { "duration": number }
)
		

[chameleon].[Cache.GetItem]

This stored-procedure is used to read an item from cache (chameleon.Cache table).

		  CREATE OR ALTER PROCEDURE [chameleon].[Cache.GetItem]
(
	@key		varchar(200),
	@value		nvarchar(max) out
)
		

Example:

		  exec chameleon.[Cache.SetItem] 'my-item', N'This is a test', '{ "duration": 10500 }'
	
declare @value nvarchar(max)

exec chameleon.[Cache.GetItem] 'my-item', @value out

print @value
		

[chameleon].[Cache.GetKey]

This function generates a key for current request based on its context-id. It uses querystring and routing data. It does this just for GET request, since requests with other HTTP methods like POST, mainly change something on server-side, hence, server's response should not be cached.

		  CREATE OR ALTER FUNCTION [chameleon].[Cache.GetKey]
(
	@context_id	int
)
		

In order to generate a key, chameleon.[Cache.GetKey] sorts querystring parameters by name and concatenates them. Then sorts routing parameters by name and concatenates them as well. Finally concatenates the two strings and generates a base64 value for the final string.

Since the generated key depends on querystring and routing parameters, any change in the number of parameters or their values, results in a new key. However, changing the order of parameters or case-sensitivity of their names does not change key.

Middlewares

• CacheSetMiddleware: This middleware is activated whenever a previous middleware in the pipeline asks that the response it provided should be cached. It does this by checking whether chameleon._requestData contains an entry named cache.
• CacheGetMiddleware: This middleware resides in the first stages of the pipeline and prvides response of current request from cache if it is able to find an entry in chameleon.Cache table for current request.

CacheGetMiddleware uses chameleon.[Cache.GetKey] UDF in order to find cached item key for current request. The UDF in turn uses querystring and routing data in order to generate a key. If CacheGetMiddleware finds a valid item in the cache for current request, it uses its value for the response of the request and ends the pipeline.

In order to learn more about CacheSetMiddleware click here.

In order to learn more about CacheGetMiddleware click here

Caching Database Records

Caching provided by Chameleon can be used for database records as well. The catch is, when records are updated we should clear cache (remove their entry). This is a little tricky as it is explained here.

Suppose we want to provide caching for products in a dbo.Products table.

We have an api /api/product/123 that is mapped to a USP_api_Product_get SPROC after routing. We make use of JsonResult helper to return the products in JSON format.

		  CREATE OR ALTER PROCEDURE dbo.USP_api_Product_get
(
	@context_id	int
)
AS
BEGIN
	SET NOCOUNT ON

	declare id			int
	declare @result	nvarchar(max)

	set @id = chameleon.[Route.GetValue](@context_id, 'id', '0')
	set @result = (select * from dbo.Products where id = @id for josn path, WITHOUT_ARRAY_WRAPPER)

	exec chameleon.[Cache.SetItem] '', @result, '{ "duration": 600 }'
	exec chm.JsonResult @context_id, @result
END
		

Using chameleon.[Cache.SetItem], we also cache result for 10 minutes (600 seconds).

We did not specify a key for chameleon.[Cache.SetItem], so, the stored-procedure uses [chameleon].[Cache.GetKey] by default to get a key for the cached data.

Now, next time another request for /api/product/123 arrives, the response will be served through cache using CacheGetMiddleware.

Everything works nice and fine. There is a problem though.

If we update the product 123 or delete it, the /api/product/123 GET request still returns the cached response, (for the rest 10 minutes until the cached item expires).

We need a mechanism to find cached item for product 123 so that we either remove it when we are updating our product or update it as well with the update we apply in product 123.

This requires an intermediary table that maps a cache key to a record.

The chameleon.CacheKeyMapping is provided for the same purpose.

chameleon.CacheKeyMapping

This table provides a simple mapping between cached items and database records. Each mapping is defined based on 4 pieces of information:

• cached item key
• entity name
• entity id
• usage

Structure/schema of this table is as follows:

Back to the example we mentioned in the previous section, we can change USP_api_Product_get as below and add mapping between product 123 and its cache key.

		  CREATE OR ALTER PROCEDURE dbo.USP_api_Product_get
(
	@context_id	int
)
AS
BEGIN
	SET NOCOUNT ON

	declare id			int
	declare @result	nvarchar(max)
	declare @cacheKey	varchar(200)
	declare @entityId	int
	
	set @id = chameleon.[Route.GetValue](@context_id, 'id', '0')
	set @result = (select * from dbo.Products where id = @id for josn path, WITHOUT_ARRAY_WRAPPER)
	set @entityId = chameleon.GetEntityId('Product')
	set @cacheKey = chameleon.[Cache.GetKey](@context_id)
	if not exists (select 1 from chameleon.CacheKeyMapping
					where 1 = 1
							and entityId = @entityId
							and cacheKey = @cacheKey
							and mappedKey = @id
					)
	begin
		exec chameleon.[Cache.SetItem] @cacheKey, @result, '{ "duration": 600 }'
	
		insert into chameleon.CacheKeyMapping
		(
			cacheKey,
			entityId,
			mappedKey
		)
		values
		(
			@cacheKey,
			@entityId,
			@id
		)
	end
	
	exec chm.JsonResult @context_id, @result
END
		

Then, in another stored-procedure where we change a product, we can remove its cached item.

		  CREATE OR ALTER PROCEDURE dbo.USP_Product_update
(
	@context_id	int
)
AS
BEGIN
	SET NOCOUNT ON

	declare @id			int
	declare @cacheKey		varchar(200)
	declare @entityId	int
	...

	set @entityId = chameleon.GetEntityId('Product')
	select @cacheKey = cacheKey from chameleon.CacheKeyMapping
	where 1 = 1
		and entityId = @entityId
		and mappedKey = @id
	
	if @cacheKey is not null
		delete from chameleon.Cache
		where
			key = @cacheKey and

	...
END
		

Here, having the id of a product, we find its cached item key in chameleon.CacheKeyMapping and then remove cached item from chameleon.Cache. So, the next request for /api/product/123 will get correct and updated response.