Foliotek Developer Blog

LiveZilla On Azure App Service

LiveZilla Azure App Service

This guide will walk you through the basics of setting up LiveZilla to run on PaaS service such as Azure App Service. In my case I had to migrate an existing LiveZilla instance running on a VM and run it on Azure App Service. This guide does require you to jump into the LiveZilla config files and update a few things. The LiveZilla version I was using was I'm not going to go into every detail of Azure / App Service you should already have some experience on this front for you to be successful setting this up.

  • MAKE SURE You Disable Slow Query Log and General Log otherwise you will max out your disk space QUOTA, this might be a an issue only because the feature is still in preview I'm not sure. See below.

LiveZilla Database Import

  • Log into your physical VM MySQL Database you will have to export 3-4 tables at a time and import them into PHPMyAdmin, the reason for this is currently Azure PHPMyAdmin has a max upload size of 8MB you cannot change this by modifying maxupload size in configs. I would recommend not zipping them as this also seems to crash PHPMyAdmin, its a little brittle Its a static setting from Azure at the writing of this article.
  • Once you have each table imported into your new LiveZilla DB in PHPMyAdmin you will now modify the connection strings.These are all Base64 encoded. This will help you view / edit the values.
  • All in all once the DB is in App Service its very fast, and a huge improvement over ClearDB (which by the way runs like a complete turd)
Parse Azure MySQL Environmental Variable

App Service Instance | Edit File: site/wwwroot/_config/ add this below line 39.

//Loop Through And Pull Settings From Azure ENV Variable
    foreach ($_SERVER as $key => $value) {
    if (strpos($key, "MYSQLCONNSTR_localdb") !== 0) {
    //Set Base 64 Version
    $_CONFIG[0]["gl_db_host"] = base64_encode(preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value));
    $_CONFIG[0]["gl_db_name"] = base64_encode("livezilla");
    $_CONFIG[0]["gl_db_user"] = base64_encode(preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value));
    $_CONFIG[0]["gl_db_pass"] = base64_encode(preg_replace("/^.*Password=(.+?)$/", "\\1", $value));

        $connectstr_dbhost = preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value);
        $connectstr_dbname = preg_replace("/^.*Database=(.+?);.*$/", "\\1", $value);
        $connectstr_dbusername = preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value);
        $connectstr_dbpassword = preg_replace("/^.*Password=(.+?)$/", "\\1", $value);

If you run into DB connection issues search through all the LiveZilla code and look for other places the connection string is used, replace the value with the above parsed out values, make sure you Base64 encode everything.

Cropping Images with Javascript and Croppie.js

Profile images are a popular thing on web sites. If you sign into a site, chances are you probably have a profile image for that site.

Sure, there are services like gravatar, but let's face it - not all of your users are going to have a gravatar account. That's why it's important to make profile images easy to configure for your users.

We rely heavily on images in Foliotek, not just for profile images, but also background images on Identity Pages. So a flexible solution was a must, as well.

The solution needed to be able to crop images into a square or a circle, needed to work on mobile devices, and needed to be simple to understand. We're not trying to provide the user with a photoshop equivalent of image manipulation. We just wanted to allow them to zoom in on a certain part of an image, and only return that cropped image.'s important to make profile images easy to configure...

Enter Croppie.js

That's why Foliotek built Croppie.js. In this post, I'm going to demonstrate how easy it is to set up croppie to allow users to crop images on your site.

How to use Croppie

First, you need to grab the croppie.js and croppie.css files from the Github Repository. Add them to your site like this:

<link href="path/to/croppie.css" rel="Stylesheet" />  
<!-- Your Html Here -->  
<script src="croppie.js"></script>  

If you have them installed, you can also use bower install croppie or npm install croppie to grab those files.

Next, you'll need an element to house your croppie element and all the components that go along with it. A single div should suffice.

<div class="my-croppie-element"></div>  

Now let's write some javascript to initialize our croppie instance. For simplicity purposes, I'll use jQuery, but Croppie isn't jQuery dependent. You can write anything you see below without jQuery.

var $element = $('.my-croppie-element');  
    viewport: {
        width: 100,
        height: 100,
        type: 'circle'
    boundary: {
        width: 350,
        height: 350

The simplest way to describe the difference beteween the boundary and the viewport is this: The boundary is the outer container of the croppie. The viewport is the portion of the image that will be cropped.

Now we have our croppie instantiated. If you load your page, you'll see an empty croppie. That's because we haven't told our croppie which image we're cropping. Let's do that now.

$element.croppie('bind', 'path/to/my/image.jpg');

bob image

Now we have an image bound to our croppie, and we can drag and zoom around on this image. You can zoom with the mouse wheel, or if you're on a mobile device you can pinch zoom. But this isn't useful yet unless we can get the resulting image that the user crops.

To do that we need to call the result method on our croppie instance. There are two different types of results that can be returned. One of which is an html result. This will return a div with our image inside of it. The div's overflow is hidden, and the image is positioned and scaled in such a way that the cropped result is the only thing visible.

The result type that we're going to use (and is probably going to be used most of the time), is canvas. This will draw the cropped image to a canvas, and using canvas.toDataURL(), will return a base64 image of the resulting image. Let's see how it's done by putting our result in a separate img tag.

<img id="result-image" />  
$element.croppie('result', 'canvas').then(function (result) {
   $('#result-image').attr('src', result);

That's it. That's really all you need to crop images with croppie.js. There are several different ways to customize croppie to fit your needs, check out the documentation to see all of them.

To be continued

Next time I'll show how to send the base64 image to your server, and save it for use at a later time.

Computing Color-Based Representation of Performance

Initial Work

I have been working on trying to visually represent numerical data of how a particular user/student has performed, and I thought I would share some of my thoughts. There are many different scales in our system with which a student may be scored. For instance, a metric may have 5 levels of scoring:

  • Does not meet expectations: 1 point
  • Approaches expectations: 2 points
  • Meets expectations: 3 points
  • Exceeds expectations: 4 points
  • Exemplary: 5 points

The maximum amount of points is 5 and the minimum is 1. One way to represent this is to show a miniature rubric of the levels with the student’s level selected. This just a simple table with a td for each level. Mousing over the td shows the level’s name. This table is specified at a 100 px width.

Adding Color

However, just having a colored indicator for what was selected seemed a little bland, so I began trying to envision how an appropriate color could be added. Red and Green are commonly used to associate bad to good, so I decided to try to compute appropriate greens and reds to represent the student’s score. The all green (#0F0) and all red (#F00) seem a little too much, so I thought I would set the range between what I will refer to as “strong green” and “strong red” (#0A0 and #A00).

*For anyone not familiar with this representation, it is the RGB/Red Green Blue value in hexadecimal with an integer range of 0 to 255 for each. “A” = 160 an “F” = 255. *

The Math

So, how do you represent the different levels by color? Essentially, the amount of green will start at zero and increase to “A” (160) and the amount of red will start at “A” (160) and decrease to zero. The blue will remain at zero no matter what.

In the case above, I initially came up with this computation:

Green = ( (Points Earned) / (Max Points Possible) ) * 160
= ( 4 / 5 ) * 160
= 80% green
= 128 (80 in hex)

Red = 160 – Green
= 32 (20 in hex)

Computed Color = #280 (20% of max red, 80% of max green, no blue)

The Mathematical Error

However, this does not work completely right, specifically due to the fact that the scale does not begin at zero. Thus, while the highest score will compute to the maximum green, the lowest score will not compute to no green, but to 20% of the maximum amount of green.

To fix this, I realized I needed to essentially make the minimum score equal to zero. This can be easily done by subtracting the minimum points possible from BOTH the Points Earned and the Max Points Possible.

Green = ( (Points Earned) – (Min Points Possible) ) / ( (Max Points Possible) – (Min Points Possible) ) * 160
= ( (4 – 1) / (5 – 1) ) * 160
= ( 3 / 4 ) * 160
= 75% green
= 120 (78 in hex)

Red = 160 – Green
= 40 (28 in hex)

Computed Color = #280 (20% of max red, 80% of max green, no blue)

Thus, there are five possibilities: 0% Green, 25% Green, 50% Green, 75% Green and 100% Green. The inclusive zero is the key here.

The Colorful Grid

This allows for a more colorful grid. I have provided one grid with a single score selected and then one with each of them, just to show the different colors:

Expanding to Aggregate Scores

We also needed a way to represent aggregate data that would not have specific set of defined levels. For instance, if a user had 20 scores from varying metrics, how could this be represented?

What I did was again computed the total number of points and the total earned points. Then I again adjusted for the minimum number of points possible in cases where the metrics do not start at zero.

I enlarged the width of the table for this to stretch across the entire screen. I’ll make it 500 px for this example. This table also has just three cells, with the first and third being essentially spacers and the second cell representing the score. I realized I only needed to compute the width of the first cell. The second cell will be a fixed width of 30 px, and then the third cell will take up the remaining space, if any.

The math for computing the color will still be the same. The key thing is to compute the size of the first column. If the table width is 500 px and the width of the score cell is 30 px, then there is only a total range of 0 to 470 pixels where that cell can be placed on the range. When it is placed at 470 pixels, that is the maximum it can be, as it will fill the remainder of the space on the table.

The Math for Placement

So, let’s assume that the total number of points a student received was 215 out of a total possible of 250. The minimum points earned will be 15. Adjusting the scale, this would indicate 200 out of 235 points earned. We can easily see, then, that our math should give us a width of the first cell of 400 pixels, since 235 is half of 470. (Yeah, I made it easy. I know. But this way we can validate the math.)

Placement = ( (Total Points Earned) – (Min Points Possible) ) / ( (Max Points Possible) – (Min Points Possible) ) * 470
= ( 215 – 15 ) / ( 250 – 15 ) * 470
= 200 / 235 * 470
= 400

Green = ( (Points Earned) – (Min Points Possible) ) / ( (Max Points Possible) – (Min Points Possible) ) * 160
= ( (215 – 15) / (250 – 15) ) * 160
= ( 200 / 235 ) * 160
= 85% green
= 136 (88 in hex)

Red = 160 – Green
= 24 (18 in hex)

Thus, the table looks like this:


So, I thought that was pretty interesting, but it gives a fairly clear visual representation of a student’s performance.

Page Level Web Application Database Optimization

Do you know what queries are required to run for a single page load in your application? If not, perhaps you should.

That is a great question I wish I would have asked myself a long time ago, but didn’t know to ask it.

Over the past several years, I have enjoyed the challenge of database query optimization. I work for a small company, and no one else seems to have much of an interest in this sort of thing, so that means I get to do it. It is certainly interesting to see how a small rewrite of a query or a new index can achieve significant results. However, I recently began looking at optimization from a different perspective–that of the individual page.

How many queries are required to load a single page? How many should it take?

To identify expensive queries that need optimization, I ran a SQL Profiler session that would catch queries with Reads greater than 1000, 5000 or maybe 10000. Or maybe more than 500 CPU. However, one day I decided to see all the queries that ran on a given page load, and I set the Read threshold all the way down to 1. I also set up the web application running on my machine to point to a test database and restricted the Profiler session accordingly.

From SQL Server Management Studio:

  • Tools > SQL Server Profiler
  • Enter Database credentials, then click Connect
  • Event Selection tab
  • Check “Show all events” and “Show all columns”
  • Column Filters - DatabaseName: TestDBName
  • HostName: ComputerName (ws###)
  • Reads: 1
  • Run

After clicking “run” and loading the first page, I was literally stunned.

One page was running far more queries than I had ever imagined. But why? I started looking through the results. The column “TextData” shows the first 100 or so characters, and then the window on the bottom shows the full query. Suffice it to say that there were many opportunities for optimization.

What I Learned

Repetitive Queries

The first thing I noticed was that on many of the page loads, there were many redundant queries. The page would query the database for the User or Company multiple times, sometimes one right after the other. None of this was intended. The impact of certain ways of doing things, however, was never realized in terms of the load on the database.

One of the biggest things to optimize are things like side menus, top menus, or footers–things that are displayed on every page. Any inefficiencies in these will be hit every time anyone loads any page. That also means, though, that any increased inefficiency can have a huge impact. If 100 pages are hit every second, and there are 20 queries that can be eliminated from each page load, you have just eliminated 1000 queries a second.

Many of these queries are small, to be sure. It takes very little if any CPU and only a few reads to retrieve a User from the User table. However, the cumulative increased traffic can be significant.

In many cases, I realized that I could just pass the user to a method that accessed a particular property on the row instead of getting the user inside the method itself. Or I could just pass in the property itself. Alternatively, I could create a page-level (or control-level [.ascx]) variable for User or Company or other tables used in many places.

private DataAccess.User _currentUser;  
public DataAccess.UserCurrentOrganization  
        if (_currentUser== null)  
            _currentUser= DataAccess.User.Get(LoggedInUserID);  
        return _currentUser;  

Unused Queries

It took some time, but I matched every query to the line of code that generated it. I found a few places where a query was called, but then the result was never used. This, of course, is bound to happen in a large application as modifications are introduced. But again, removing these for every page load can certainly help.

Unnecessary Queries

I also encountered a few places where unnecessary queries were being run. For instance, upon login, the system should check the expiration date of the user’s registration. That was the intent of the code. However, at some point, the code was moved and ended up getting run on every page load. So, the code was necessary on login, but not on every page. I never would have known it ran on every page had I not seen it for myself in the SQL Profiler session.

Final Thoughts

It is, indeed, rewarding to identify a very expensive query and optimize it down to something very small. Yet, I’ve learned that there are not only gigantic weeds. Sometimes there are millions of smaller weeds. It is better to get rid of both.

Simplifying Manual Insertion of Static Data from one Database to another

Problem Description

We’ve all run into the issue. You have created a look-up table on one database, perhaps a test database. Now it’s time to take it live, and you need to port all the rows from the table in the test database into the corresponding table on the live database. Sure, you could use SQL Data Compare or various tools like that, but this table only has 10 rows, so is it really worth the trouble?

In my case, I had to port data into two live database instances, so I didn’t want to have to go through a long process. We do have SQL Compare tools, but need to upgrade them for SQL Server 2012, and I didn’t want to have to wait.

Of course, if the databases were on the same server, then copying between servers is pretty simple. However, if the databases are separated by type (i.e. Oracle and SQL Server), then that is much more difficult.

Let me use a standard User table as an example for simplicity, although that really does not fit with what you would use this for.

The Brute Force Method

What I initially did was to just select everything from one table, paste it into a new query window in SQL Server Management Studio, then add commas and tick marks to insert each row manually.

 SELECT * FROM Users  
 UserID Email FirstName MiddleName LastName IsImpeached  
 1 George Washington 0  
 2 John Adams 0  
 3 Thomas Jefferson 0  
 4 James Madison 0  
 5 James Monroe 0  
 6 John Q. Adams 0  
 7 Andrew Jackson 0  
 8 Martin Van Buren 0  
 9 William Henry Harrison 0  

Next, I started creating the INSERT statement. This necessitated the insertion of a lot of commas and ticks and parentheses. This is tedious, to be sure, but it does work, and it isn’t all that bad for a limited number of rows.

 INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (1, '', 'George', '', 'Washington', 0)  
 2 John Adams 0  
 3 Thomas Jefferson 0  
 4 James Madison 0  
 5 James Monroe 0  
 6 John Q. Adams 0  
 7 Andrew Jackson 0  
 8 Martin Van Buren 0  
 9 William Henry Harrison 0  

After that, I could copy the Insert statement from the beginning to the first parenthesis after the VALUES keyword and then add all the commas and ticks. Still tedious, but not all that bad if you’re just wanting to complete the task manually.

A Slightly Less Taxing Brute Force Method

It occurred to me somewhere in this process that there is a fairly easy way to make this brute force method slightly less taxing, and I don’t know why this hadn’t occurred to me before. Perhaps it is one of those things that does not seem obvious until you see it for the first time. Anyway, the main effort comes in adding all the parentheses, commas and ticks, so why not have the system generate that for us?

All I did was to modify the original select query as follows:

 SELECT '(' + CAST(UserID AS VARCHAR(25)) + ', '" + Email + "', '" + FirstName + "', '" + MiddleName + "', '" + LastName + "', ' + CAST(IsImpeached AS CHAR(1)) + ')'  
 FROM Users
 (1, '', 'George', '', 'Washington', 0)  
 (2, '', 'John', '', 'Adams', 0)  
 (3, '', 'Thomas', '', 'Jefferson', 0)  
 (4, '', 'James', '', 'Madison', 0)  
 (5, '', 'James', '', 'Monroe', 0)  
 (6, '', 'John', 'Q.', 'Adams', 0)  
 (7, '', 'Andrew', '', 'Jackson', 0)  
 (8, '', 'Martin', '', 'Van Buren', 0)  
 (9, '', 'William', 'Henry', 'Harrison', 0)  

As you can see, this reduces the effort for the brute force method quite significantly, even to the point that it might not be able to be called the brute force method anymore. All that has to be done is to copy the the part of the INSERT statement that precedes the actual values.

Now I'm Just Being Lazy

Someone once suggested that innovation sometimes comes just because we are lazy. I know what that means–we're not really lazy, we just want to do more interesting things, so we automate the simple. It just occurred to me that this could be simplified even more by including the "INSERT INTO… VALUES prefix" into the SELECT statement. This tactic eliminates all manual addition of commas, parentheses, ticks and everything else. Then all I have to do is copy all the rows, paste them into a query window and run it.

 SELECT 'INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached )  
 VALUES (' +  
 CAST(UserID AS VARCHAR(25)) + ', '" + Email + "', '" + FirstName + "', '"  
 + MiddleName + "', '" + LastName + "', ' + CAST(IsImpeached AS CHAR(1)) + ')'  
 + '  
 FROM Users  
 INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (1, '', 'George', '', 'Washington', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (2, '', 'John', '', 'Adams', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (3, '', 'Thomas', '', 'Jefferson', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (4, '', 'James', '', 'Madison', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (5, '', 'James', '', 'Monroe', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (6, '', 'John', 'Q.', 'Adams', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (7, '', 'Andrew', '', 'Jackson', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (8, '', 'Martin', '', 'Van Buren', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (9, '', 'William', 'Henry', 'Harrison', 0)


So, obviously, if you have SQL Data Comparison tools the automate this, that is preferable. However, if that is tedious to set up, or if you don’t have access to such tools, this can make copying rows for small tables much less tedious.


You will probably have to add "SET IDENTITY_INSERT Users ON" at the beginning and "SET IDENTITY_INSERT Users OFF" at the end.

A few of my other favorite posts