javascript, jQuery, markdown, user-content

Javascript Quick & Dirty Dynamic Numeric Evaluations From Dynamic Content

I’m writing a help page for a lottery order form to confirm to specific gaming standards. The page has to show odds for a given set of games that are offered on the order form. I made the decision to pull the content from the database, so that it can be edited from the admin site rather than us going in and editing it. I also decided that a Markdown syntax would restrict how the content was displayed yet allow a degree of power in how it was formatted.

However, there definitely was a problem in displaying things like pay tables. I had to insert a couple of tables into this content to show odds and payout factors for the games that were displayed. Here’s the table in Markdown (GFM-flavor) format. For those who don’t know what Markdown is, it is a method of writing content that is still very readable in its un-rendered form.

# Payout Factors for Different Bet Types

Game Type | Combination | Example Bet | Payout
 ------------ | ------------------------------------------- | -------------------- | --------
 Pick 2 | Straight: Any 2 digit # from 00 to 99 | 12 (exact order) | 90:1
 Pick 2 | Boxed (2-way; 2 unique digits) | 12 (any order) | 45:1
 Pick 3 | Straight: Any 3 digit # from 000 to 999 | 123 (exact order) | 900:1
 Pick 3 | Boxed (6-way; 3 unique digits) | 123 (any order) | 150:1
 Pick 3 | Boxed (3-way, 2 unique digits) | 334 (any order) | 300:1
 Pick 4 | Straight: Any 4 digit # from 0000 to 9999 | 1234 (exact order) | 9000:1
 Pick 4 | 24-Way Box (4 digit # with unique digits) | 1234 (any order) | 375:1
 Pick 4 | 12-Way Box (4 digit #, 3 unique digits) | 1233 (any order) | 750:1
 Pick 4 | 6-Way Box (4 digit #, 2 sets of 2 digits) | 3344 or 4343 (any order) | 1500:1
 Pick 4 | 4-Way Box (4 digit, 3 digits the same) | 1112 (any order) | 2250:1

The above renders into an H1 tag for the title and an HTML table for the table portion.

That’s all fine, but see where it says 90:1 for a straight bet? I don’t want a human to edit that number. Additionally, I don’t want to use an example number, since that would force the users to mentally calculate the payout factors themselves, and could possibly violate the standards we are implementing. Therefore, the actual payout factor values should be pulled from the database. There’s a number of different ways to do this, one being using something like a replacement token, e.g. {2BallPF}:1. But then, I gotta do something like {2BallPF/2}:1 for the boxed bet, which I would have to figure out on the server…. sounds like a huge pain. I could just let the users edit it every time mass payout factors are changed…

 Pick 2 | Straight: Any 2 digit # from 00 to 99 | 12 (exact order) | 70:1
Pick 2 | Boxed (2-way; 2 unique digits) | 12 (any order) | 45:1
Pick 3 | Straight: Any 3 digit # from 000 to 999 | 123 (exact order) | 700:1
Pick 3 | Boxed (6-way; 3 unique digits) | 123 (any order) | 150:1
Pick 3 | Boxed (3-way, 2 unique digits) | 334 (any order) | 300:1

NO. I can’t entrust the operators of the system to make these changes any time a payout factor update is done. And I definitely don’t want to manage that crap. It always happens on a Friday night when I am relaxing, or any time on Saturday or Sunday. I really value my free time, even if it’s to do absolutely nothing.

Would a framework like angular.js, react.js, durandal, etc. work for what I’m trying to do? Possibly, but I wasn’t interested in the learning curve (at this moment, mind you, although learning at least one or two of these frameworks sounds like fun), or the weight. All I wanted to do was something like take a token, replace it with its value, and evaluate anything as an expression. I thought to myself “man, a ‘data-‘ style attribute would be really nice about now”. I then remembered that Markdown allows HTML to be inserted.

So this is what I did! I made some example output in jsbin:

<!DOCTYPE html>
<html>
<head>
<script src="//code.jquery.com/jquery-1.11.1.min.js"></script>
 <meta charset="utf-8">
 <title>JS Bin</title>
</head>
<body>
<input type="hidden" id="max2" value="90" />
 <input type="hidden" id="max3" value="900" />
 <input type="hidden" id="max4" value="9000" />
 <input type="hidden" id="mx" value="6" />
 
 This is a test to see how:<br /><br />
 
 <span data-fix="{max2}">x</span>
 / <span data-fix="{mx}">16</span> = <span data-fix="{max2} / {mx}">y</span>.<br><br>
 
 <span data-fix="{max3}">x</span>
 / <span data-fix="{mx}">16</span> = <span data-fix="{max3} / {mx}">y</span>.<br><br>
 
 <span data-fix="{max4}">x</span>
 / <span data-fix="{mx}">16</span> = <input type="text" data-fix="{max4} / {mx}" value="?" />.<br><br>
 
</body>
</html>

So for elements that I want to dynamically render a mathematical value, I enclose them in a span tag with an attribute of data-fix. This contains the formula, which is actually something that eval() can parse in Javascript. A bit dangerous, if someone were to get into the admin and change a value to something destructive, but then they could do a heck of a lot more than just add stuff for eval() statements to hit.

A little javascript magic goes a long way:

$(function(){
 var regex = /\{[\w\d\-_]+?\}/gim;
 $("[data-fix]").each(function(){
 var that = $(this);
 var formula = that.attr("data-fix");
 //console.log("formula = " + formula);
 var rendered = formula.replace(regex, function(match, capture) {
 var s = match.replace("{", "").replace("}", "");
 //console.log("-- s = " + s);
 var elem = document.getElementById(s);
 //console.log("-- tag = " + elem.tagName);
 if (elem == null || elem === undefined)
 return "null";
 var value = $(elem).val() | $(elem).html();
 return value;
 });
 //console.log("rendered = " + rendered);
 var final = eval(rendered);
 //console.log("final = " + final);
 if (this.tagName.toLowerCase() == "input" || this.tagName.toLowerCase() == "select")
 that.val(final);
 else
 that.html(final);
 });
});

What’s going on here is that I’m grabbing all instances of any element that has the data-fix attribute, grabbing the “template” formula, doing any replacing in it as needed, doing an eval() on the final formula, and then inserting the value back into the element, one time.

Check out the JS Bin for more details and to play with it!

Uncategorized

Detecting changes in a table

Suppose I want to sync with my clients a bunch of information when the program has started and at specific intervals throughout the day. However, I don’t want the server to send over a huge list of all the information every single time it is requested, as it’s a waste of bandwidth and time. I should mention that a lot of clients I work with have spotty internet connections, so it’s important that the apps function well offline as well as transfer only the data that is needed to be transferred.)

How do I know that the client has the latest updated copy of data from the database? We do this by generating a checksum of the data that we would transfer. The client would send over its last known checksum value, or NULL if it does not have one stored, and the server only pushes data over if the checksum value passed back to the server is NULL or differs from the checksum the server generates. It’s ridiculously fast, at least for the data sets I send over – a few hundred to a few thousand records per set.

Here are some functions I created to get the data that needs to be synced:

CREATE FUNCTION FranchiseInfo_Franchises (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Items (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Staff (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Locations (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_PrepaidCards (@franchiseID INT) AS ...

Et cetera.

Each function returns columns of data that I push to the client. You don’t specifically NEED to use table-valued functions, but in this scenario it definitely makes it easier to do the following steps. (Specifically, if I had used just stored procs, I would need to copy and paste the same SQL used for the procs in the checksum procedure, which reduces maintainability.)

What we want is some sort of a checksum that would change when the data in the table has changed.

CREATE PROCEDURE FranchiseInfo_GetChecksums</p><p>(</p><p>@franchiseID INT<br />)<br />AS<br /> SELECT 'Franchise' AS DataType, (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Franchises(@franchiseID)) AS [Checksum]<br /> UNION SELECT 'Items', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Items(@franchiseID))<br /> UNION SELECT 'Locations', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Locations(@franchiseID))<br /> UNION SELECT 'PrepaidCards', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_PrepaidCards(@franchiseID))<br /> UNION SELECT 'Staff', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Staff(@franchiseID))<br /> ;

(Later I changed it so each one returned the checksum value in a column, so the procedure would return only one row with the checksum values. It was much easier to do that, rather than parse the row values. I’ll leave this as an exercise to the reader.)

BINARY_CHECKSUM() takes in a list of columns, in this case, the columns returned from the stored procedure. Yes, using * for columns is frowned upon but I don’t care, as it makes it easier to update the functions and know that it’s including that in the aggregation. CHECKSUM_AGG aggregates the other checksums into one checksum.

In my client code, I provide a list of the checksums I already have – as Nullable<long> / long? datatype, and if I have the checksum stored, I pass it in, otherwise, I leave the value as NULL. The server calls the function above and compares the checksums returned. If the checksum is different than what was passed in, then I call the appropriate procedure to return updated data to the client. This way, I’m only passing down the data set only if it has changed.

Uncategorized

Using a CTE in SQL to update and delete anything over then Nth row for a given grouping of fields, a.k.a. Building a Token Management System

I learned a new trick for dealing with deleting rows after the nth index from a table. First, some backstory:

I’m in the gaming industry with clients overseas, and our client has an online casino and lottery house that runs games like slots, blackjack and poker. To launch these games, we use a token system. The customer launches a URL with a GUID token, which is immediately swapped once the game loads. The client continues to request tokens to perform actions behind the scenes, and the token must continually change to prevent someone who got access to a token – either by random guess, or worse.

Token rotation systems aren’t the simplest thing in the world – the token system has to handle multiple tokens for a given customer, because if the client application sends a request to our server that is processed successfully, changing the token, but the client never gets a response, the client is left with an old token and cannot reference that particular customer anymore. The system I set up for this vendor manages 5 tokens simultaneously, with them expiring after 10 minutes. My token table looks like this:

ID	CreatedOn	CustomerID	GamingVendorID	Token	CountOfUses	LastUsedOn	IsExpired
321	2014-02-13 12:09:48.337	1151	4	73F636BC-F6C4-4301-83A3-3A24744E0C65	1	2014-02-13 11:59:48.347	0
324	2014-02-13 11:59:48.363	1151	4	12C33AB2-44CA-4946-9CDC-AE0934FA8DBC	0	NULL	0

However, in production there will be tons of tokens, and each vendor is configured to allow a maximum number of tokens simultaneously – this vendor has 5. Without some sort of cleanup routine, there will be a lot more than 5 unexpired tokens in the table. I have a stored procedure to clean up these excess tokens, but since it has to run on every call to validate and rotate a token, I can’t have it look over the data by customer ID and vendor ID, as it would be too slow. I also didn’t want to clean up just the requesting vendor’s and customer’s tokens and leave everything else alone; I wanted the table to have as few rows as possible. So, how can it be done with a single batch and no looping, temporary tables or cursors?

Enter the Common Table Expression (CTE):

WITH cteTokens AS
	(
		SELECT 
			t.ID,
			ROW_NUMBER() OVER (PARTITION BY g.ID, t.CustomerID ORDER BY t.CreatedOn DESC) AS RowNumber,
			g.[AllowedNumberOfTokensKeptBesidesCurrentToken] AS CountToKeep
		FROM CustomerRotatingTokens t
		LEFT OUTER JOIN GamingVendors g ON (g.ID = t.GamingVendorID)
		WHERE
			t.CountOfUses &gt;= g.[AllowedNumberOfTokenReuses]
			OR ([TokensExpireAfterNumberOfSeconds] IS NOT NULL AND DATEADD(SECOND, g.[TokensExpireAfterNumberOfSeconds], t.CreatedOn) &lt; GETDATE()) 	), 	cteTokensToExpire AS 	( 		SELECT ID FROM cteTokens WHERE RowNumber &gt; CountToKeep
	)
	UPDATE CustomerRotatingTokens SET IsExpired = 1 WHERE ID IN (SELECT ID FROM cteTokensToExpire);

Here’s an overview of the interesting parts of the code:

  • WITH cteTokens AS – this names our expression (think of it as a table alias)
  • ROW_NUMBER() OVER (PARTITION BY g.ID, t.CustomerID ORDER BY t.CreatedOn DESC) AS RowNumber – this adds a sequential row number to each row, starting at 1. If you specify ROW_NUMBER() OVER (ORDER BY t.CreatedOn DESC) (this is the bare minimum syntax needed to do this), it will add a sequential number to each row. We need to delete any tokens after the 5th newest token, so we need the tokens to be “grouped” by the vendor’s ID and the customer ID. That’s where the PARTITION part comes in – think if it almost like a GROUP BY statement so ROW_NUMBER() starts counting over when it reaches a new combination of those fields.
  • cteTokensToExpire AS – this next CTE selects a new table containing only those tokens whose index (the ROW_NUMBER()) is greater than what the vendor is allowed to keep – 5.
  • UPDATE… WHERE ID IN (SELECT ID FROM cteTokensToExpire) – expires any tokens that were selected by cteTokensToExpire.

Part of the client requirements is that we differentiate between invalid and expired tokens, so I can’t just delete them from the table immediately, but I have another CTE that blows out expired rows that are a bit older than when they expired.

The speed of the CTE is amazing. It’s most definitely faster than building a temporary table to aggregate the results and then expire and delete the rows, which would not have flown since this has to run a LOT.

Uncategorized

Wow. I learned something very important about SQL today.

Consider this SQL statement, containing a inline table-valued function (TVF)

SELECT *
FROM dbo.Games_StandardLotto_Summaries(
dbo.Date(DATEADD(D, -1, GETDATE())), 
dbo.EndOfDay(DATEADD(D, 1, GETDATE())), 
NULL, NULL, NULL);

It’s taking 17 seconds to run, and I cannot figure out WHY. I copied the guts of the TVF from a sql script I was tweaking for the good part of an hour. It’s a single line with CTEs that runs a fairly complex query in about a second or two – vast improvement from getting the same data from temp tables in 15-20 times the time. But now, it’s back to square one, it seems.

Unless…

DECLARE @sd DATETIME, @ed DATETIME;
SELECT @sd = dbo.Date(DATEADD(D, -1, GETDATE())), 
    @ed = dbo.EndOfDay(DATEADD(D, 1, GETDATE()));
SELECT * FROM dbo.Games_StandardLotto_Summaries(@sd, @ed, NULL, NULL, NULL);

…runs in under 1 second for me.

So what SQL is telling me here is that for TVFs, and I’m sure more than that, it uses the parameters by reference – meaning it executes the representation of the expression, rather than the value of the expression at the time the TVF is called.

The lesson learned here is that you should always declare and set your variables and use those to execute the TVF, rather than calling it with complex statements. As you can see above, I’m calling GETDATE(), then adding or subtracting a day from that value, and then running a scalar function to make it midnight or 11:59:59:999, twice per row minimum. Storing it into variables knocked the time down to next to nothing. Changing the SQL code above that calls the TVF resulted in a drop from 16.766 seconds to 0.275 seconds. That’s almost a 61x improvement!

Uncategorized

Shared Version Number Across All Projects In A Solution

Backstory: We have a terminal app that uses WCF services and an MVC web back-end. We want to show the version # of the terminal on the MVC page.

To do this, first create a SharedAssemblyInfo.cs in a solution folder, or in your main project file. It will contain no more than the following text:

// Solution version

using System.Reflection;
[assembly: AssemblyVersion(“0.1.1.6″)]
[assembly: AssemblyFileVersion(“0.1.1.6″)]

For each project that will be sharing this file,
(a) go to the /Properties/AssemblyInfo.cs file and comment out the version # stuff.
(b) Add an existing file to the project. Find the SharedAssemblyInfo.cs and click the dropdown next to “Add” and select “Add as link”.

Done!

If you are displaying a version number in your web project and System.Reflection.Assembly.GetExecutingAssembly() returns null, you need to use GetEntryAssembly().

If you find that the version # always returns 0.0.0.0 when using (System.Reflection.Assembly.GetexecutingAssembly() ?? System.Reflection.Assembly.GetEntryAssembly()).GetName().Version.ToString(), then get a type that is outside your Assembly (I have a Common project consumed by all assemblies) and get its version # from that, like this:

typeof(Some.Type.Outside.of.Web.Assembly).Assembly.GetName().Version.ToString()

Hope this helps!

Uncategorized

Updating an object with MVC

When editing an object (like an entity) in MVC, it returns a new one populated with your objects. If you try to save it using dataContext.Attach(), you may find that when saving it bombs on you. A great example of this would be something like a DateTime called .CreatedOn, since you wouldn’t let users edit it, would be the same value as new DateTime() which is year 0 or 1. When the object attempts to be persisted it won’t allow a date that low to be stored in a normal datetime field in SQL (datetime2, yes, but datetime should be sufficient in most cases) and bombs with a YSOD.

Maybe there’s a better way, but until I discover it or someone points it out to me, here’s what I did:

/// <summary>
/// Maps only the submitted changes in model state from one object to another.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="mapToObject"></param>
/// <param name="mapFromObject"></param>
public static void MapChangedProperties<T>(this ModelStateDictionary modelState, ref T mapToObject, T mapFromObject)
{
	var props = typeof(T).GetProperties();
	foreach (var item in modelState)
	{
		var prop = props.FirstOrDefault(p => p.Name == item.Key);
		if (prop != null)
		{
			prop.SetValue(mapToObject, prop.GetValue(mapFromObject, null), null);
		}
	}
}

To use, get the object out of the database and run this on it:

// "obj" is our returned model name, and we have a database connection open
if (ModelState.IsValid)
{
        dbObj = db.Objs.Single(o => o.ID = obj.ID);
        ModelState.MapChangedProperties(ref dbObj, obj);
        dbObj.ModifiedDT = DateTime.UtcNow;
        db.SaveChanges();
}
C#, EF Entity Framework, Reflection, UTC

Converting all date/time properties of an object graph to local time from UTC

Backstory: In a project I am working on, I am storing all times in the database as UTC. When I get the values from the database, I want to convert the times to local time to be displayed in my client. There’s many articles on this, that involve changing to local time on the server before shipping to client, which I didn’t care for (for example, hooking into the ObjectMaterialized event on the ObjectContext and using reflection). This had to be done on the client itself to take advantage of the client’s time zone.

I didn’t want to convert them all by hand, either. What if I added D/T properties later? If it’s nullable, then it’s an extra line of code to check that it’s not null first. What about an object graph (nested objects)? It sounded like a lot of work, until I cobbled together this code from a couple sources and reworked it a bit to do what I want:

	/// <summary>
	/// Since all dates in the DB are stored as UTC, this converts dates to the local time using the Windows time zone settings.
	/// </summary>
	public static class AllDateTimesAsUTC
	{

		/// <summary>
		/// Specifies that an object's dates are coming in as UTC.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="obj"></param>
		/// <returns></returns>
		public static T AllDatesAreUTC<T>(this T obj)
		{
			if (obj == null)
			{
				return default(T);
			}
			IterateDateTimeProperties(obj);
			return obj;
		}

		private static void IterateDateTimeProperties(object obj)
		{
			if (obj == null)
			{
				return;
			}
			var properties = obj.GetType().GetProperties();
			//Set all DaetTimeKinds to Utc
			foreach (var prop in properties)
			{
				var t = prop.PropertyType;
				if (t == typeof(DateTime) || t == typeof(DateTime?))
				{
					SpecifyUtcKind(prop, obj);
				}
				else if (t.IsEnumerable())
				{
					var vals = prop.GetValue(obj, null);
					if (vals != null)
					{
						foreach (object o in (IEnumerable)vals)
						{
							IterateDateTimeProperties(o);
						}
					}
				}
				else
				{
					var val = prop.GetValue(obj, null);
					if (val != null)
					{
						IterateDateTimeProperties(val);
					}
				}
			}
			//properties.ForEach(property => SpecifyUtcKind(property, obj));
			return; // obj;
		}

		private static void SpecifyUtcKind(PropertyInfo property, object value)
		{
			//Get the datetime value
			var datetime = property.GetValue(value, null);
			DateTime output;

			//set DateTimeKind to Utc
			if (property.PropertyType == typeof(DateTime))
			{
				output = DateTime.SpecifyKind((DateTime)datetime, DateTimeKind.Utc);
			}
			else if (property.PropertyType == typeof(DateTime?))
			{
				var nullable = (DateTime?)datetime;
				if (!nullable.HasValue) return;
				output = (DateTime)DateTime.SpecifyKind(nullable.Value, DateTimeKind.Utc);
			}
			else
			{
				return;
			}

			Debug.WriteLine("     ***** Converted date from {0} to {1}.", datetime, output);
			datetime = output.ToLocalTime();

			//And set the Utc DateTime value
			property.SetValue(value, datetime, null);
		}
		internal static Type[] ConvertibleTypes = {typeof(bool), typeof(byte), typeof(char),
   typeof(DateTime), typeof(decimal), typeof(double), typeof(float), typeof(int), 
   typeof(long), typeof(sbyte), typeof(short), typeof(string), typeof(uint), 
   typeof(ulong), typeof(ushort)};

		/// <summary>
		/// Returns true if this Type matches any of a set of Types.
		/// </summary>
		/// <param name="types">The Types to compare this Type to.</param>
		public static bool In(this Type type, params Type[] types)
		{
			foreach (Type t in types) if (t.IsAssignableFrom(type)) return true; return false;
		}

		/// <summary>
		/// Returns true if this Type is one of the types accepted by Convert.ToString() 
		/// (other than object).
		/// </summary>
		public static bool IsConvertible(this Type t) { return t.In(ConvertibleTypes); }

		/// <summary>
		/// Gets whether this type is enumerable.
		/// </summary>
		public static bool IsEnumerable(this Type t)
		{
			return typeof(IEnumerable).IsAssignableFrom(t);
		}

		/// <summary>
		/// Returns true if this property's getter is public, has no arguments, and has no 
		/// generic type parameters.
		/// </summary>
		public static bool SimpleGetter(this PropertyInfo info)
		{
			MethodInfo method = info.GetGetMethod(false);
			return method != null && method.GetParameters().Length == 0 &&
				 method.GetGenericArguments().Length == 0;
		}

	}

How do you use it? To change aall dates on an object sent to the client via WCF, this is how it’s done:

		void svc_ZOut_GetZOutSummaryCompleted(object sender, ZOut_GetZOutSummaryCompletedEventArgs e)
		{
			svc.ZOut_GetZOutSummaryCompleted -= new EventHandler<ZOut_GetZOutSummaryCompletedEventArgs>(svc_ZOut_GetZOutSummaryCompleted);
			svc = null;
			var whenDone = (Action<bool, ZOutResult>)e.UserState;
			if (e.Error != null)
			{
				FireOnExceptionRaised(e.Error);
				whenDone(false, null);
			}
			else
			{
				var res = e.Result.AllDatesAreUTC();
				FireOnSessionReceived(res.IsError, res.Session);
				if (res.IsError == true)
				{
					whenDone(false, null);
				}
				else
				{
					whenDone(true, res.Result);
				}
			}
		}

That’s it! It handles a whole object graph, DateTime and nullable DateTime obejcts.