StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So I played around with this a bit based on the comments here. What I came up with is counting up a counter in a simple field. In my app I have code snippet entities with a Views property.</p> <p>When a snippet is viewed a method filters out (white list) just what should hopefully be browsers:</p> <pre><code>public bool LogSnippetView(string snippetId, string ipAddress, string userAgent) { if (string.IsNullOrEmpty(userAgent)) return false; userAgent = userAgent.ToLower(); if (!(userAgent.Contains("mozilla") || !userAgent.StartsWith("safari") || !userAgent.StartsWith("blackberry") || !userAgent.StartsWith("t-mobile") || !userAgent.StartsWith("htc") || !userAgent.StartsWith("opera"))) return false; this.Context.LogSnippetClick(snippetId, IpAddress); } </code></pre> <p>The stored procedure then uses a separate table to temporarily hold the latest views which store the snippet Id, entered date and ip address. Each view is logged and when a new view comes in it's checked to see if the same IP address has accessed this snippet within the last 2 minutes. if so nothing is logged.</p> <p>If it's a new view the view is logged (again SnippetId, IP, Entered) and the actual Views field is updated on the Snippets table.</p> <p>If it's not a new view the table is cleaned up with any views logged that are older than 4 minutes. This should result in a minmal number of entries in the View log table at any time.</p> <p>Here's the stored proc:</p> <pre><code>ALTER PROCEDURE [dbo].[LogSnippetClick] -- Add the parameters for the stored procedure here @SnippetId AS VARCHAR(MAX), @IpAddress AS VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; -- check if don't allow updating if this ip address has already -- clicked on this snippet in the last 2 minutes select Id from SnippetClicks WHERE snippetId = @SnippetId AND ipaddress = @IpAddress AND DATEDIFF(minute, Entered, GETDATE() ) < 2 IF @@ROWCOUNT = 0 BEGIN INSERT INTO SnippetClicks (SnippetId,IpAddress,Entered) VALUES (@SnippetId,@IpAddress,GETDATE()) UPDATE CodeSnippets SET VIEWS = VIEWS + 1 WHERE id = @SnippetId END ELSE BEGIN -- clean up DELETE FROM SnippetClicks WHERE DATEDIFF(minute,Entered,GETDATE()) > 4 END END </code></pre> <p>This seems to work fairly well. As others mentioned this isn't perfect but it looks like it's good enough in initial testing.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload