DateTime Intervals Versus DateTimes

I had wondered why some websites displayed date intervals (i.e. “5 days ago”, “4 hours ago”) for user posts instead of printing actual dates and times. Doing some new website work recently caused a revelation about that choice and how it solves many problems all at once.

Displaying a real date and time opens up a can of worms about what to display and how to display it. Date and time is fairly trivial as long as all users of a system are located in the same place. Each user thus shares a common timezone and expectation of what a date and time should look like.

Creating and maintaining a site for users across multiple timezones and borders is more problematic.  Should a date look like one of the following?

  • 12/5/2014
  • 5/12/2014
  • 2014/12/5
  • 2014-12-5
  • Dec. 5, 2014
  • On and on and on

Then there’s the problem of time.  What timezone should be stored and displayed?  The timezone of the user who submitted the content being viewed?  The timezone of the web server?  The timezone of the user viewing the content?

If you insist on displaying a proper local time to every user on your site will have to geolocate every user and then convert the stored time to their local representation.  The timezone is only part of the problem.  You also have formatting:

  • 1:45 PM
  • 13:45
  • 13:45:05

Using date intervals solves all of these problems.  All dates stored in the database are in UTC.  All displayed date and time references are expressed as the amount of time that has elapsed since that time.  It’s a simple date diff calculation between the current UTC time and the UTC time stored in the database.

The exact same display is shown to all users, anywhere on the planet.  No user or locale-specific computation must be done.

Posted in Programming | Leave a comment

Code First Foreign Keys With Different Names

The Entity Framework’s Code First scheme is handy way to do quick development of new websites. I recently had an issue with a foreign key relationship between a table I created and the ASP.Net Identity user table AspNetUsers. Typically, EF intelligently maps foreign key columns back to their parent key names. For example:

public class First
{
    [Key]
    public int FirstID;
    public int SecondID;

    // ...

    public virtual Second second { get; set; }
}

public class Second
{
    [Key]
    public int SecondID;
}

will properly result in a database schema where a foreign key relationship is set up between First and Second with SecondID as the foreign key field.

ASP.Net Identity presents a problem in that the primary key for the AspNetUser table is simply “Id”. I didn’t want a foreign key in my table simply called “Id” because this would not be very descriptive when looking at the code later. What happens when you don’t include a foreign key column but do add the reference to the code? Here’s the code:

    public class First
    {
        [Key]
        public int FirstID {get; set; }
        // I'd like this to be the foreign key to the AspNetUser table.
        public string UserID {get; set; }

        public virtual ApplicationUser AspNetUser { get; set; }
    }

And here’s a part of the resulting SQL database schema:

CREATE TABLE [dbo].[Firsts](
    [FirstID] [INT] IDENTITY(1,1) NOT NULL,
    [SecondID] [INT] NOT NULL,
        // Points TO nothing.
    [UserID] [nvarchar](MAX) NOT NULL,
        // FOREIGN KEY TO USER TABLE.
    [AspNetUser_Id] [nvarchar](128) NULL
...

Note that EF does create a foreign key as is necessary, but it prefixes the “Id” name with the table name, in this case “AspNetUsers_Id”. It’s nice that it works, but that’s not the name I wanted to use as the foreign key. There had to be some way to employ a foreign key whose name was not the same as the primary key. A Google search showed a couple of examples on how to do this. Unfortunately, many suggested using the EF fluent interface to update the entity context at create time. The code in some cases looked extremely hard to follow.

Eventually I stumbled upon a much simpler solution. By simply adding the ForeignKey attribute to the foreign key declaration EF will be able to properly associate the foreign and primary keys even if their names are not the same. All that’s required is that you spell out the table which the key references:

[Required, ForeignKey("AspNetUser")]
public string UserID { get; set; }

With this simple one line fix I was able to use the foreign key column name that I thought was easier to read, UserID.

ALTER TABLE [dbo].[Firsts] WITH CHECK
    ADD CONSTRAINT [FK_dbo.Firsts_dbo.AspNetUsers_UserID]
    FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
Posted in Programming | Tagged | Leave a comment

The Move

It’s been a while now, but Penny and I are officially Californians now. About a month ago we made the move from Austin, TX to Oakland, CA. The reasons for the move are really quite complex and drawn out – a boring story really. Certainly we had never expected to leave Texas right up until summer of last year when wheels were set into motion. Things took on a life of their own and here we are.

We’re settling in to the way things are here fairly well but things are still new enough to us that we can’t help but compare them to the “normal” way of doing things in Texas. The cats are mostly used to the new surroundings though Gigi does not like the smaller living area since that means closer proximity to the other cats. Our two day driving adventure with the cats in the back of our two cars actually went very well. Much better than expected.

One thing that we really like about Norther California is the abundance of hiking opportunities. We’ve started to hit the trails in the area and have been documenting some of them on Hiking Trailhead.

Posted in Family | Leave a comment

Migrating Code From SQL Server To MySql

After having worked exclusively with Microsoft’s SQL Server for some years now I was working on a project in which the customer wanted the data to be stored in their database of choice, MySql. My first thought was to utilize the ODBC client code in the .Net framework instead of my familiar SQL Client components. That should mean a drop in placement with just a few name changes.

My fears were raised a bit when I saw that the MySql folks had their own .Net libraries (referred to as connectors). Worse, when I tried to download just those libraries I was coaxed into creating an Oracle login account and download either a full suite of MySql products or all of the client libraries and tools. Not wanting another server on my laptop I went with the smaller, client only, option.

In a word my experience with the MySql client code has been wonderful. Here are the steps needed to take a simple program that’s working against SQL Server and get it up and running with a MySql database.

  1. Add a reference to the MySql.Data.dll library in your project.
  2. Add a using statement referring to “MySql.Data.MySqlClient” in your source file.
  3. Replace the SQL Server data commands with their MySql equivalents.

Here’s a snippet of code that was written for SQL Server:

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(sqlCommand, conn))
    {
        cmd.Parameters.AddWithValue("@param1", Param1);
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
...

And now in MySql it looks like:

using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
    conn.Open();
    using (MySqlCommand cmd = new MySqlCommand(sqlCommand, conn))
    {
        cmd.Parameters.AddWithValue("@param1", Param1);
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
...

That’s it! The code flow is exactly the same. Since the MySql libraries inherit from the same base .Net framework classes and interfaces it’s an effective drop in replacement for SQL Server.

Of course, SQL Server and MySql do not share the same quirks when it comes to SQL standards. Anything other than the simplest queries will have to be modified to function properly.

Amongst the tools installed with the libraries was MySql Workbench. I figured that my web provider’s web based tools for database creation and management would suffice. And they probably would have for my limited requirements. However, once I started up Workbench I quickly set aside my intention of doing the database management via the simpler web tools.

MySQL_Workbench

MySql Workbench is a very functional MySql-based replacement for everything that I’ve used SQL Server Management Studio to do with SQL Server. Though the feel and flow of operations is a bit different it’s not too terribly hard to get used to. Plus all of the functionality that you want from such as tool from schema development to adhoc query tuning is there.

Posted in Programming | Tagged | Leave a comment

Skipping Unit tests In MSTest

When creating a large number of test cases for a project there may be a number of cases that don’t apply for some particular scenario or platform. I didn’t want to mark a non-applicable test case as having passed just because it did not apply to the relevant platform and I certainly didn’t want to mark the test as having failed.

The meager MSTest documentation didn’t provide anything obvious that allowed me to ignore certain tests under some conditions. A quick Google search did turn up a simple solution though that worked perfectly. The Assert command can be used to mark the test as being not applicable.

if (!Helper.bSupportsDynamicTickets)
{
    Assert.Inconclusive("Not supported on this platform.");
    return;
}

Now, when the test is not pertinent it’s included in the TestExplorer’s “Skipped Tests” node.

Posted in Programming | Tagged , | Leave a comment

SQL Server Versions And Compatibility Levels

When working with a number of different SQL Server versions on various projects I’ve been a bit confused about what exactly I’ve been dealing with. I wanted a single table with all of the names, versions and compatibility levels all in one place. Here’s the result:

Release Version Number Compatibility Version
SQL Server 2000 8.x 80
SQL Server 2005 9.x 90
SQL Server 2008 10.0 100
SQL Server 2008 R2 10.5 100
SQL Server 2012 11.x 110

You can determine your version of SQL Server in a number of ways. Inside of SQL Server Management Studio the Database Engine’s node in object explorer should display the version number. Within a query window you can also run the following command:

Select @@version

A problem arises if you are working with a database in a shared hosting environment in that even though the engine hosting your database may be SQL Server 2008 R2 the compatibility level on your database may be set at SQL Server 2000. This allows for greater backwards compatibility with installed solutions even while the provider is able to update their servers. Find your database’s compatibility level by running the following command:

SELECT compatibility_level
FROM sys.databases WHERE name = '';

If you need additional functionality that your server provides but that is excluded due to the compatibility level of the database you can change this via this command:

ALTER DATABASE
SET COMPATIBILITY_LEVEL = 110;

If your database is in a hosted environment you’ll have to ask that your provider make this change.

Posted in Programming | Tagged | Leave a comment

Free Team Foundation Server Hosting

In a recent post I pointed out how having my hobby code in source control helped diagnose an issue when migrating from one OS and Visual Studio version to another.  But if you’re just playing around with some code at home and don’t have much time or money to devote to your fun there’s still no excuse to back up your work and track changes.  Microsoft has already provided a free development environment and a free SQL Database.  Recently they also started providing free source control.

Haven’t there been numerous options for this for some time now?  Yes.  However, they’ve had limited integration with Visual Studio and though not terribly difficult to learn and configure many just playing around with code may not have the time or inclination to try.

Microsoft’s Team Foundation Server has been around for years but has historically been something an organization had to host and support on their own servers.  Oh, and it was also very expensive.  Expensive enough that even companies that I’ve worked for shied away from it.  That changed when TFS was moved onto the cloud (using Windows Azure, of course) and Microsoft decided to craft a plan that allowed for a free tier of TFS hosting.  The free tier allows for unlimited projects and up to five developer accounts.  Unless you’re starting up a serious business with multiple employee developers this means it’s free!

How easy is it to get started?  Here’s a quick run through.  Note that the steps below don’t include the steps necessary to set up an account on the TFS server, but I didn’t want to delete my account there to show the steps, so you’ll have to manage on your own.  You’ll need a Windows Live ID to sign up, but it’s all very simple.

Once you’ve set up a TFS account you’ll be given a unique URL for accessing services (https://<your_account_name>.visualstudio.com/).  First you’ll need to create a project on the TFS server that will be used to track our source code.  Click on the “New Team Project” button and enter a name and description for the project.  We won’t worry about the process template value for this simple walk through.

NameProject

Let’s start up Visual Studio 2012 Express and create a quick project that we’ll track add to source control.  Right click on the solution in the Solution Explorer window and choose the “Add Solution to Source Control…” option.

ConnectToTeamFoundationServer

If this is your first time using TFS there will be no servers listed in the Connect to Team Foundation Server dialog, so press the “Servers…” button and lets add your account info:

AddTeamFoundationServer

If you’re not already authenticated with your account credentials you may be asked to provide them now.

SignIn

Now select your just added server in Connect to Team Foundation Server dialog and select the project that you added earlier:

ConnectToTeamFoundationServer2

Press Connect

AddSolutionToSourceControlDialog

And then press OK.

And, now you’ve set up source control for you solution!  But you might be wondering why nothing much seems to have changed and source code files don’t show up on the TFS server.  All of the files in the project are marked as “newly added”.  You can check this by hovering your cursor over the “+” mark next to some of the solution files.  They’re waiting to be checked in, which you can do by right clicking on the solution node in the Solution Explorer, and then selecting the “Check In…” option.

Any check in should include a comment.  After all, we are trying to keep track of what’s changed and why.  The Team Explorer will ask for this information.  Let’s keep it simple this time around:

PendingChanges

Press the “Check In” button and then click OK when it asks if you’re sure that you want to check in.

Now, you’re really done.  You’re code has been checked into the TFS servers in the cloud.  Go back to the Solution Explorer and note that all of the files have a lock icon next to them that indicate that the file has been checked in.  Go to your TFS URL and now you should be able to see all of the source files, including the comment included with the last check in and the user who did so.

In just a few minutes you have free, fully integrated source control for your project!

Changes are easy as well.  There’s no need to check out a source file.  Or use Windows shell extensions outside of Visual Studio to handle check ins.  Just edit a file as needed and test the results.  If I edit the Form1 dialog’s title, the affected files’ Solution Explorer’s icons include a check mark indicating that they are “checked out”.

CheckedOutFiles

Right click on a modified file and select “Compare…” to see the changes that were made.

Differences2

Repeat the step earlier to check in the changes and everything is in sync.  Easy!

TFS provides plenty of features in addition to simple source control that are well worth looking into.  For now, take a few minutes and get your code backed up and under control!

Posted in Programming | Tagged , | Leave a comment

CSS Content To The Rescue

Looking to get a bit of extra visibility for pages on my hiking websites I implemented support for Google’s Rich Snippets.  This feature allows for the markup of pages so that extra bits of information such as user ratings or photos can be included in Google search results.  On a page with a long list of results, the eye catching additions could lead to greater click through rates.

Google supports a couple of different microformats that webpage owners can mark up their pages so that the Google search engine can determine what should be added to their results.  My sites catalog outdoor activities such as hiking trails and allows users to rate their enjoyability.  Marking up the average rating allows Google to show this rating with graphic stars.  The HTML markup such as:

<span itemprop="rating" itemscope itemtype="http://data-vocabulary.org/Rating"> 
 <span class="rating">
    <span itemprop="average">4.33</span>
  </span>
</span>

is part of the markup that results in a Google search listing that looks like:

Google search result

That’s great!  So what’s the problem?  The problem is that Google does not just rely on markup to try and extract some meta data from the page in order to present them in the search results.  They also do some parsing on their own.  If you view one of the pages that I was trying to support you’ll note that I have an Amazon recommended item at the bottom of the page.  The writeup for this item includes the author(s)’ name preceded by the phrase “by “.  Even with no markup, to Google this is significant and they fetch the name as the author of the page!  Of course they are not the author of the page, but instead only of the item listed.  What to do?

I tried playing with some different text with varying degrees of success until I stumbled upon a solution via CSS, in particular through the content property.  I wanted the user visiting the page to see the author indicated by text similar to “by Robert Barron” but I didn’t want Google to see that.  The solution using CSS involves displaying the author’s name only with a CSS class designation:

<span class="AmazonAuth">Robert Barron</span>

The CSS markup indicates that it should be preceded by the value of content property:

.AmazonAuth:before
{
    content: "by ";
}

The CSS above indicates that before an element of the class AmazonAuth the content should be inserted.  When Google parses web pages for indexing it does not take the CSS markup into account, so the “by ” prefix does not exist as far as it’s concerned and does not cause it to falsely guess the page author’s name.

Posted in Programming | Tagged | Leave a comment

Beware Of Visual Studio Proxy Migrations

I recently moved a C# code project from Visual Studio 2010 on Windows 7 to Visual Studio 2012 on Windows 8.  Theoretically, this should have resulted in no problems and code that just works from the start.  But that’s not the way it turned out.

The project in question was a Web project that included proxy code used to interface with Amazon’s Product API.  Visual Studio 2012 started reporting the following error, “Cannot implicitly convert type ‘Exploricity.Amazon.ECS.ImageSet[][]‘ to ‘Exploricity.Amazon.ECS.ImageSet[]‘.”

Since I have the source code under version control I was able to determine what files Visual Studio modified and what those changes were.  I found that the Reference.cs file in the Amazon.ECS folder contained a ton of changes.  Most were innocuous generated code changes that reflect the version of various system assemblies:

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "4.0.30319.17929")]

But there was also this change:

public ImageSet[][] ImageSets

Visual Studio attempted to regenerate the proxy code and did so in such a way that broke it by making ImageSets a two dimensional array instead of keeping it as the single dimensional array it was before.

One of the takeaways from this is the importance of version control, even for small personal projects like this one was for me.  It made finding out what the problem was and resolving it a much easier task.

Posted in Programming | Tagged | Leave a comment

WordPress 3.5 on Windows Temp Directory Problem

I installed WordPress on a couple of different hosting accounts that I have on hand to try and see where I should place the final result.  On one of those accounts I was not able to update one of the plugins that was not up-to-date.  I also could not download any themes.

Some forum discussions that I’d come across indicated that this issue was primarily suffered by users who were hosting their site on a Windows machine.  This of course caused many helpful individuals to rail against Windows and recommend upgrading to a Unix hosting account without actually addressing the particular problem directly.  The fact that this issue started with version 3.5 only didn’t detract from their suggestion.

I finally found a useful post that pointed out the true cause of the issue and a simple workaround.  Version 3.5 changed the algorithm used to determine which directory to use for storing temp files as WordPress updates or download modules.  To restore the old behavior without having to approach one’s hosting provider for configuration changes one need only add or update the following line in the wp-config.php file:

define('WP_TEMP_DIR', ABSPATH . 'wp-content/');

Voila!  Everything works fine, even on my more restrictive hosting provider.

Posted in Programming | Tagged | Leave a comment