Saturday, August 9, 2014

Lessons, Surprises, Mistakes III. - AMS

Next set of lessons, surprises and mistakes; this time about Azure Mobile Services (AMS).

Download location
Maybe I am blind but I cannot find a download link for the actual version of MobileServices.Web.js script. WinJs versions are easily available via nuget. Digging into tutorials I have found a location with all Web versions. Comparing it with nuget page, the latest available Web version is 1.2.2 (although WinJs is already at 1.2.3):

Update 24.1.2015: There is a changelog from which you can see that at this moment, the latest Javascript SDK is 1.2.5.

Shared Scripts
There is a possibility to manually upload a script to shared folder on server:
azure mobile script upload shared/helper.js
You can then use it via require in other scripts:
var helper = require('../shared/helper');

Unfortunately, the scripts there are periodically deleted. It turned out that manual upload is not supported and you can only use this feature if you enable source control. See more info on forum.

Stuck scheduled tasks
I've managed to get the scheduled AMS task into state where it ignored all my updates. It ran the old script version no matter what I did. Even deleting the task and creating a new one with different name did not help. I asked on forum and got quickly an advice how to work around the issue by restarting the whole mobile services.

Wrong authorization
Although I require an authorized user when accessing all AMS endpoints, it took me a while to spot an error in my implementation. I did not refuse cashier operations from unknown users. You couldn't do it from UI, of course. But any script kiddie could download the POS, get the application id, authorize with any google account, and send as many transactions/shifts as wanted. The fix was simple - only allow server operations to known users.

SQL Tables Initialization
I let AMS to create my tables. It was a mistake. Only when I started to have performance issues with getting report data, I realized what types are used for the data. For example, as JavaScript does not have integers but only floats, all integer values were stored in float columns. Not very fast and index-able… I have changed type of some columns but next time I will create proper SQL init script.

SQL query optimizations
I had not written complex SQL query for a long time so the first version of the report query was quite embarrassing. There were too many inner queries and other issues. After a consultation with my friend, I speeded up the query by
  • Use null id trick to select the whole row with max value
  • Use union all instead of union because all sub-queries are distinct
  • Converting float columns to integer columns
  • Index on date column did not help because server still clustered index on __createdAt column which has very similar content to the date columns

No comments: