MySQL DATETIME vs TIMESTAMP and the year 2038

Judging from MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/datetime.html, it sounds best to use DATETIME rather than TIMESTAMP if you want your database to be future proof. After all 2038 is only 18+ years away.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Btw, another issue that sounds problematic, at least conceptually, is that one can use the CURRENT_TIMESTAMP to initialize auto-updated columns with the current datetime (aka NOW()), but since the TIMESTAMP type is supposed to be up to 2038, wonder why the CURRENT_TIMESTAMP would be a difference (aka return a value that would overflow TIMESTAMP columns, but still work fine – aka return the accurate datetime – for DATETIME columns after the year 2038).

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

At the very least, I’d avoid mixing those data types in the same database, for consistency reasons.

Fix: jQuery’s jqGrid search UI custom styling

Lately, I’ve got the task of maintaining/extending an ASP.net MVC web application that is using jQuery’s jqGrid for data grids on its UI. First thing I noticed was how confusing the search UI on the grid’s header was:

Those symbols on the left-side of each column’s searchbox are for the type of search (e.g. contains, doesn’t contain, equals, starts with, doesn’t start with, ends with, doesn’t end with).

Bit too many options and using programming-related symbols that probably intimidate several users in my opinion:

But the worse is the “x” button (that clears the searchbox) on the right of each searchbox, that combined with the search-type symbols makes the whole search bar look like some strange mathematical expression.

So using the browser dev tools (F12) and some CSS rules I quickly restyled that search bar to make it more appealing UI/UX wise:

Added a border around the “x” button that clears the searchbox and offseted using a negative margin so that the searchbox and it fuse together visually on their sides. Also made the search-type symbol (that opens the search-type selection popup when clicked) of lighter color. It may look a bit-like some disabled thing like that, but at least it should confuse average users less with its use of technical symbols like that.

Just need to add the rules above at the ASP.net MVC app’s Site.css (probably to be found at the Content subfolder of the webapp) and remember to press F5/Refresh in one’s browser in case the old styling still appears due to caching.

Update #1:

I noticed on older versions of Windows (other than Windows 10 that is) that bevels were showing at the text inputs, leading to this ugly effect:

So I had to add some more rules to remove the bevel borders and use a consistent border color.

Removing the bevels seemed to also remove the inner padding of the text inputs, so added a padding of 2px and some box-sizing rules to make sure the padding doesn’t affect the input’s size.

/* OS-independent styling for input and textarea borders */
textarea,
input[type="text"],
input[type="password"] {
    border-style: solid;
    border-width: 1px;
    border-color: gray;
    padding: 2px;
    -webkit-box-sizing: border-box; /* Safari/Chrome, other WebKit */
    -moz-box-sizing: border-box; /* Firefox, other Gecko */
    box-sizing: border-box; /* Opera/IE 8+ */
}

Update #2:

After recently updating some jQuery related NuGet packages in that ASP.net project, I noticed the [x] button was showing a bit higher up at the bottom compared to the search box. The fix to that was to add padding-bottom: 1px; at the CSS declaration for clearsearchclass in Content/Site.css

a.clearsearchclass {
    border-width: 1px;
    border-style: solid;
    /* border-left-style: none; */
    margin-left: -3px;
    padding-bottom: 1px; /* seems to be needed with newer jQuery.UI */
    background: whitesmoke;
    border-color: gray;
}

HowTo: Bind ASP.net control to list of files or folders

At ClipFlair Gallery metadata input pages for Activities and Clips I had to bind an ASP.net control to a list of files and folders respectively and although I found a Folder Contents DataSource control, it didn’t cover my needs (like filtering of a folder contents).

I just contributed my solution using .NET Anonymous Types and LINQ (assumes a using System.Linq clause) to:

http://stackoverflow.com/questions/1331793/bind-repeater-to-a-list-of-files-and-or-folders

private string path = HttpContext.Current.Server.MapPath("~/activity");

   protected void Page_Load(object sender, EventArgs e)
   {      
     if (!IsPostBack) { //only at page 1st load
       listItems.DataSource = 
         Directory.EnumerateFiles(path, "*.clipflair")
                  .Select(f => new { Filename=Path.GetFileName(f) });

       listItems.DataBind(); //must call this
     }
   }

The above snippet gets all *.clipflair files from ~/activity folder of a web project

Update: using EnumerateFiles (availabe since .NET 4.0) instead of GetFiles since this is more efficient with LINQ queries. GetFiles would return a whole array of filenames in memory before LINQ had a chance to filter it.

The following snippet shows how to use multiple filters, which GetFiles/EnumerateFiles don’t support themselves:

private string path = HttpContext.Current.Server.MapPath("~/image");
private string filter = "*.png|*.jpg";

protected void Page_Load(object sender, EventArgs e)
{
  _listItems = listItems; 
  
  if (!IsPostBack)
  {
    listItems.DataSource =
      filter.Split('|').SelectMany(
        oneFilter => Directory.EnumerateFiles(path, oneFilter)
                     .Select(f => new { Filename = Path.GetFileName(f) })
      );

    listItems.DataBind(); //must call this

    if (Request.QueryString["item"] != null)
      listItems.SelectedValue = Request.QueryString["item"];
                          //must do after listItems.DataBind
  }
}

The snippet below shows how to get all directories from /~video folder and also filters them to select only directories that contain a .ism file (Smooth Streaming content) with the same name as the directory (e.g. someVideo/someVideo.ism)

using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;

namespace ClipFlair.Gallery
{
 public partial class VideoMetadataPage : System.Web.UI.Page
 {

  private string path = HttpContext.Current.Server.MapPath("~/video");

  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack) { //only at page 1st load
      listItems.DataSource = 
       Directory.GetDirectories(path)
        .Where(f =>(Directory.EnumerateFiles(f,
                              Path.GetFileName(f)+".ism").Count()!=0))
        .Select(f => new { Foldername = Path.GetFileName(f) });
      //when having a full path to a directory don't use Path.GetDirectoryName 
      //(gives parent directory),
      //use Path.GetFileName instead to extract the name of the directory

      listItems.DataBind(); //must call this
    }
  }

 

The examples above are from a DropDownList, but it’s the same logic with any ASP.net control that supports Data Binding (note I’m calling Foldername the data field at the 2nd snippet and Filename at the 1st one, but could use any name, need to set that in the markup):

        <asp:DropDownList ID="listItems" runat="server" AutoPostBack="True" 
          DataTextField="Foldername" DataValueField="Foldername" 
          OnSelectedIndexChanged="listItems_SelectedIndexChanged"
          />

HowTo: format XML output of DataContractSerializer

based on the other samples posted at StackOverflow on how to format XML created by DataContractSerializer, that use XmlWriter, here’s a version (from ClipFlair source code) that works with streams (and Ionic.Zip library in specific).

It also shows how the code is when you don’t apply formatting (using conditional compilation).  Just comment out the #define (prefix it with //) to make it write unformatted XML.

 

#define WRITE_FORMATTED_XML

using System.Xml;

namespace ClipFlair.Windows
{

  public partial class BaseWindow : FloatingWindow
  {

    //...

    #if WRITE_FORMATTED_XML
    private static XmlWriterSettings XML_WRITER_SETTINGS = 
            new XmlWriterSettings() { Indent=true, IndentChars="  "};
    #endif

    //...

    public virtual void SaveOptions(ZipFile zip, string zipFolder = "") 
                 //THIS IS THE CORE SAVING LOGIC
    {
      if (SavingOptions != null) SavingOptions(this, null); //notify any listeners

      View.Busy = true;
      try
      {
        ZipEntry optionsXML = 
        zip.AddEntry(zipFolder + "/" + View.GetType().FullName + ".options.xml",
          new WriteDelegate((entryName, stream) =>
          {
            DataContractSerializer serializer = 
                      new DataContractSerializer(View.GetType()); 
                              //assuming current View isn't null
            #if WRITE_FORMATTED_XML
            using (XmlWriter writer = XmlWriter.Create(stream, XML_WRITER_SETTINGS))
              serializer.WriteObject(writer, View);
            #else
            serializer.WriteObject(stream, View);
            #endif
          }));
      }
      catch (Exception e)
      {
        MessageBox.Show("ClipFlair options save failed: " + e.Message); 
      }
      finally
      {
        View.Busy = false; //in any case (error or not) clear the Busy flag
      }

      if (SavedOptions != null) SavedOptions(this, null); //notify any listeners
    }

    //...

  }

}