Wednesday, July 29, 2015

Finding a date range in SQL





    At work, we use log4net, and we have the appender (or logging output location) set up to be AdoNetAppender, so thus it logs to a SQL database. In the Log table, there is a column called [Date], and it has the sql type of datetime.


    Often, when querying the Log table, you only want to view the most recent dates. lets say within the last week. You could always ORDER BY [Date] DESC of course, but suppose we wanted more control than that, such as only last week's dates.

    The SQL keywords (and functions) that are relevant here are BETWEEN, GETDATE and DATEADD.

    Here is the SQL code:


 SELECT
     [ID],[Date],[Thread],[Level],[Logger],[Message],[Exception]
 FROM
     [DatabaseName].[dbo].[Log]
 WHERE
     [Date] BETWEEN
      DATEADD(dd, -7, GETDATE())
      AND
      DATEADD(dd,  1, GETDATE())
 ORDER BY

     [Date] DESC


    The BETWEEN keyword should be pretty self-explanatory, as should the GETDATE function. The secret sauce here lies within the DATEADD function.

    The SQL function DATEADD has this signature: DATEADD (datepart, number, date) 

    The DATEADD function adds a number to a component of DATETIME, in this case, days. This number can be negative to subtract time from a DATETIME, as is the case with our example. The datepart parameter is what determines what component of the DATETIME we are adding to. You can add as much as a year, or as little as a nanosecond (what, no picoseconds? *laugh*). Microsoft's Transact-SQL MSDN page for DATEADD supplies the following table for datepart:

DATEPART
ABBREVIATIONS
year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
weekday
dw, w
hour
hh
minute
mi, n
second
ss, s
millisecond
ms
microsecond
mcs
nanosecond
ns


    In the example, I am subtracting 7 days from the current date. If you are making a stored procedure, this variable can be replaced with a parameter:


 CREATE PROCEDURE [dbo].[sp_GetLogEntriesRange]
     @RangeInDays int
 AS
 BEGIN
     DECLARE @DaysToAdd int
     SET @DaysToAdd = (0 - @RangeInDays)

     SELECT
      [ID],[Date],[Thread],[Level],[Logger],[Message],[Exception]
     FROM
      [DatabaseName].[dbo].[Log]
     WHERE
      [Date] BETWEEN
       DATEADD(dd, @DaysToAdd, GETDATE())
       AND
       DATEADD(dd,  1, GETDATE())
     ORDER BY
      [Date] DESC
 END


    Enjoy, I hope this helps!

Saturday, June 20, 2015

Lazy IEnumerable file reader class: When to inherit from IDispose?




    Need to read just the few couple lines from a gigantic data file? Or maybe you need a forward-only, load-only-what-you-need file reader pattern? Here is a construct I have been toying with, its a class that treats a file stream like an IEnumerable.

    Note added 8/1/15: TODO: Add constructor overload that accepts the starting filepointer position, optional ending filepointer position.

    This has the benefit of not using any resources if you never use it, allows you to incrementally read gigantic files without loading it all into memory (you might need to call the StreamReader.DiscardBufferedData() method every once in a while), and because its IEnumerable, you can write queries against it that are lazy; they don't actually execute until the run-time actually NEEDS it, such as calling the IEnumerable.ToList() or 'Count()' extensions, for example. Be careful with ToList() if the file is a gigabyte or more, as calling ToList() will cause the whole thing to be read right then.

    If instead you just need to iterate through each line only until you find what you are looking for, or use Linq and a predicate to search for a particular line that satisfies a condition, then this pattern will save your application from having the load the whole thing in memory:

public class EnumerableFileReader
{
    public FileInfo File { get { return _file; } }
    public bool FileExists { get { return _file.Exists; } }

    private FileInfo _file;

    public EnumerableFileReader(string fileLocation)
        : this(new FileInfo(fileLocation))
    {
    }

    public EnumerableFileReader(FileInfo file)
    {
        if (!file.Exists)
        {
            throw new FileNotFoundException();
        }

        _file = file;
    }

    public IEnumerable FileLines()
    {
        if (!FileExists) yield break;

        string line;
        //long internalBufferSize = 0;

        using (StreamReader reader = _file.OpenText())
        {
            while ((line = reader.ReadLine()) != null)
            {
                //if (internalBufferSize++ > 90000) {   reader.DiscardBufferedData(); internalBufferSize = 0; }
                yield return line;
            }
        }

        yield break;
    }
}

    It struck me that it might be a good idea to make the class inherit from IDisposable, so the StreamReader doesn't get left around in memory, holding a file open. Indeed; all those yield keywords make it look like the stream object will just hang around there if FileLines is never called again to finish the enumeration. However, it turns out this is probably not necessary but the answer is, as you might expect: IT DEPENDS. It depends... on how you are going to use the class. Looking into the subject, I discovered that when you use the yield keyword, the compiler generates a nested class which implements the IEnumerable, IEnumerator and IDisposable interfaces and stores all context data for you under the hood. I'm not going to drop the IL (or CIL) here, but if you are curious, just open up your IEnumerable class in ILSpy. Just make sure you change the language in the drop-down box at the top from C# to IL, otherwise it will be hidden.

    So just when is our class disposed of? Well anytime you explicitly call Dispose on the Enumerator or the Stream, which one might expect. However, this will dispose of a lot more than just the stream or the enumerator alone, but all of the associated constructs that are generated around the enumerator/yield pattern. to be disposed of. Dispose will also be called at the end of enumeration. This includes when you run out of things to enumerate, any time you use the yield break or return keyword, or the program flow leaves the using statement surrounding the Stream. Here is something I didn't know: Dispose is also called when you call the IEnumerable.First() or FirstOrDefault() extension. Also, any time you use a foreach loop around the IEnumerator, the object will get disposed after you are done looping or after you break or leave the loop.

  
So, in short: As long as you're using LINQ extensions or foreach loops, the disposal is taken care of for you automatically. However, if you are manually calling the Enumerator().MoveNext() on the method, then you need to call dispose yourself, or rather, implement the IDisposable pattern.

    Being able to use EnumerableFileReader in a using statement/disposable pattern would likely be expected of a file reader class. You could have your dispose method set a boolean flag and then call FileLines(), and add an if statement in the while look of your FileLines() method that will yield break when the dispose flag is set to true, but cleaning up properly can be tricky if your IEnumerator has more than one or two return yield statements. I would instead suggest that that we use one of the tricks we just leaned above and just have our Dispose() function call .FirstOrDefault() on the FileLines() method:


public class EnumerableFileReader : IDisposable
{
[...]

    public void Dispose()
    {
        FileLines().FirstOrDefault();
    }

[...]
}


Saturday, June 6, 2015

Humorous Software Licence Agreement #2


See also: Humorous Software Licence Agreement #1
Please note: I do not know what kind of legal protection against liability this licence will actually afford you. Please confer with an actual lawyer before attempting to use this in any legal capacity.




Software Usage License Agreement
Copyright (C) [copyright holders]
All rights reserved.

 * WITH NO GUARANTEES OF ANY KIND
THIS SOFTWARE IS PROVIDED:
 * AS-IS
 * NOT AS IT IS'NT
 * WITH NO WARRANTY OF ANY KIND
 * WITH NO PROMISES OF ANY KIND
 * WITH NO ASSURANCES OF ANY KIND
 * WITH A YAHOO TOOLBAR ATTACHED IF YOU DOWNLOADED IT FROM CNET

THIS INCLUDES, BUT IS NOT LIMITED TO:
 * WARRANTIES OF MERCHANTABILITY
 * GUARANTEE OF FITNESS FOR A PARTICULAR PURPOSE
 * ASSURANCES THAT IT WORKS, OR EVER WORKED, OR MIGHT WORK ANYTIME IN THE FUTURE
 * ASSURANCES THAT IT WILL NOT CAUSE YOU PSYCHOLOGICAL TRAUMA OR MENTAL HARM
 * PROMISES THAT IT WILL NOT CAUSE YOU TOTAL FINANCIAL RUIN
 * PROMISES THAT IT WILL NOT CAUSE YOU BODILY HARM OR PERSONAL INJURY
 * PROMISES THAT IT WILL NOT CAUSE YOUR SERVERS TO IGNITE IN FLAME
 * ASSURANCES THAT IT WILL NOT END THE HUMAN RACE
 * ASSURANCES THAT IT WILL NOT ANNIHILATE SPACE-TIME, AND THEREFORE EXISTENCE ITSELF
 * ASSURANCES THAT THESE WORDS ACTUALLY EXIST AND ARE NOT, IN FACT, AN ILLUSION
 
THEREFORE ANY PARTY THAT CHOOSES TO USE THIS SOFTWARE DOES SO AT THEIR OWN RISK AND
THEREFORE ASSUMES ANY RISK THAT IS DIRECTLY RELATED TO, CAUSED BY, OR ASSOCIATED WITH THE POSSESSION, READING, UNDERSTANDING, PRINTING, BURNING, USE OF, OR INTERACTION WITH THIS SOFTWARE
THEREFORE IT IS OF THE OPINION OF THE COPYRIGHT HOLDER/AUTHOR THAT THE SOFTWARE SHOULD NOT USED, BY ANYONE, FOR ANY PURPOSE, BUT DO NOT FORBID IT, AND FURTHERMORE, GRANT IT

It is hereby granted, to you, the rights to view it, use it, copy it, modify it, publish it, distribute it, sub-license it, or sell it for any purpose you see fit, subject to the following conditions:
 * The above copyright notice and this permission notice shall be included in all copies or substantial portions of the software.

UNDER NO CIRCUMSTANCES AND IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT, COURSE OF BUSINESS, LATE-NIGHT DRUNKEN PRANKS, OR ACT OF,
OR OTHERWISE, ARISING FROM, OUT OF, OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE

Want to read more? I wrote a Humorous Software Licence Agreement #1 post with a similar-themed software licence.

Friday, April 24, 2015

Install C# on Raspberry Pi 2 with Mono



Intro

We can run C# on the Raspberry Pi 2! This has made the raspberry pi very valuable in my eyes. You can use Visual Studio to compile a .exe and then RUN IT on The Pi using Mono.

What I liked about The Pi: Getting it working was dead simple. I just formatted an SD card in FAT 32, downloading and extracting a 500mb .zip file and copying the contents of the extracted folder onto the SD card. After you've accomplished that, go take a beer break, you deserved it *whew*.

I was able to just plug in the cables, put in the SD card and I running Linux on The Pi. I was using the HDMI and Ethernet ports, however. If you wish to use the tiny, headphone-jack-looking component video or a USB wireless adapter however, there will be additional steps.


Problems / Gotchas 

Winforms may not play nice with Mono when on the Raspberry pi. Specifically, the problem manifests when you attempt to use a TextBox on your form, but who uses those? This bug  is supposedly fixed in the latest releases, but I have yet to get it to work by just updating my mono. It is very likely that I have to REBUILD mono from the latest source on the pi, which can take several hours. I have not tested this. The bug tracker for this says it has to do with with the pi's 'hard floats', which is referring to hardware floating point calculations.



The hardware is not exactly what I would call stable. Sometimes it does not POST (the BIOS is a binary blob, so does a pi really have a POST?). Anyways, ensure your USB charger that plugs into the wall that can supply plenty of power. The websites say 700ma at a minimum. I find using a 700ma charger is not sufficient. I use a charger that can push 2A, and I dont see many problems.




How does this work? 

A project called Mono. The Mono team has implemented a Common Language Runtime (CLR) that runs in the Linux and Mac environments and is coded to the ECMA-335 Common Language Infrastructure (CLI) AND covers functions and classes in the .NET framework, going all the way to 4.0 with some 4.5! Its open source, of course, and also has a compiler that follows the ECMA-334 C# Language Specification to turn C# code into CLI intermediate code that can be ran in windows as well. Remember, the CLI is just a specification that any language could (theoretically) be compiled to CLI.




Installing Mono C# on Raspberry Pi

This step was pretty straight forward as well. The instructions below are if you installed the NOOBS package which uses Raspian. If you have a different flavor of Linux, the commands below might be different.

You should already have sudo installed on your raspb pi. Prefixing a command with sudo allows you to run processes under a higher privilege without having to log into root. Also if you didnt know, the default username/password to log in the first time is pi/raspberry. If you prefer, you can type 'startx' to bring up the xwindows GUI. The commands below need to be entered into a terminal, which can be brought up within the GUI.

Fist, you want to update your Raspian to the latest version. To do that, you have to have the raspberry pi hooked up the the internet. If you have an ethernet cable and the router uses DHCP, plugging in the ethernet will be sufficient. Then, at the console simply enter the below line into the console:

sudo apt-get update

That may take a few minutes to complete and might prompt you to hit Y/N regarding the size of the package.

sudo apt-get install mono-runtime

This will also will take a few minutes to complete and might prompt you to hit Y/N regarding the size of the package.

After you've accomplished that, go have another beer because that was hard work!




Mono can now be used to run .NET executables:

mono Test.exe

Or, if your program requires elevated rights:

sudo mono Test.exe


Mono also features a REPL (read eval print loop). To access it, type 'csharp'. You can use your favorite text editor to make .cs files and compile them. My recommendation is, of course, to use Microsoft's Visual Studio. You can build and compile an application with MSVS in windows, and just transfer the executable by SCP or thumb-drive to the raspberry pi, which is pretty slick. Hats off to the developer(s) of Mono.


Saturday, April 18, 2015

Hindenbugs, Heisenbugs and other types of software bugs




Different Types of Software Bugs


    Sorry, I haven't gotten around to creating a new post in close to a month (yikes!). I have just been super busy at work and have been trying to complete about 3 out of several not-quite-finished personal projects that I will be releasing on GitHub and blogging about

Heisenbug--
    1) A software bug that disappears or alters its behavior by the action of attempting to debug it.
    Possible sources: The effect of debuggers on the run-time code or JIT can be one source. Another might arise because of process scheduling or threading and race conditions. Or both, as stepping through the code with a debugger will affect the timing of threads.
    Derivation: Werner Heisenberg and his Uncertainty Principle.


Mandelbug--
    1) A bug whose causes are so complex it defies repair, or makes its behavior appear chaotic or even non-deterministic. Other symptoms include core dumps that, when viewed in an editor, form complex but repeating patterns or designs of characters and symbols.
    Possible sources: Operating system environment or configuration, the presence or absence of file system objects, time or scheduling-dependent code. Also, a very complicated state machine esp. one with more than one variable that hold state information and where transitions may not exists for every possible permutation of one state to another.
    Derivation: Benoît Mandelbrot and his research in fractal geometry.


Schrödinbug--
    1) A bug that manifests itself in running software after a programmer notices that the code should never have worked in the first place.
    Possible sources: A combination of naughty coding practices such as coding by coincidence and something else that allows for unexpected behavior such as a the effect of custom configurations or compiler optimizations, code obfuscation, or the JIT or on code. Also, a dependency on some arcane piece of technology that no one truly understands or employment of a black-box system or library, or some ancient, dinosaur system with a highly specific configuration that is so fragile that no-one dares update it because said updates could likely or is known to break functionality and where even a reboot is considered hairy.
    Derivation: Named for Erwin Schrödinger and his thought experiment.


Bohrbug--
    1) In contrast to the Heisenbug, the Bohrbug, is a "good, solid bug", easy to hunt down, or easily predicted from the description, esp. when a bug is the result of a classical programming mistake, or 'a rookie move'.
    Possible sources: Failing to initialize a variable or class to anything other than null, failure to RTFM, misuse of pointers or general abuse of memory, not coding defensively, coding by coincidence or not truly understanding how (or why) your code works.
    Derivation: Named after the physicist Niels Bohr and his rather simple atomic model.


Hindenbug--
    1) A bug with catastrophic consequences, esp. one that actually causes the server to burst into bright, hot flames.
    Possible sources: Code that dynamically builds and executes SQL scripts, esp. scripts that make use of the DELETE FROM command, code that is self modifying or replicates, or code that controls large machines or a physical system such as pumps, belts, cooling systems, aggregate pulverizer, fans, or any sort of thing with large, rotating blades. Also, any software that controls, monitors, tests, or is in any way whatsoever, wired up to a tactical nuclear defense system. In fact, lets just include anything with 'nuclear' or 'pulverize' in the name.
    Derivation: Refers to the Hindenburg disaster. The Hindenburg Blimp was, in turn, named after Paul von Hindenburg, the then-president of Germany from 1925->1934.



    Kishor S. Trivedi has a great talk/slideshow about Software Faults, Failures and Their Mitigations. He posits that it is not realistic to write software that is 100% bug free, or have 100% up-time. He displays the downtime in terms of minutes per year (MPY?) of several reliable corporations to support his claims, and it is true that as far as I am aware,there have not been any software ever written that did not have some downtime, even NASA.
    However, Trivedi shares a quote by one E. W. Dijkstra: "Testing shows the presence, not the absence, of bugs". Aww, logic. Now that's something I can get behind. Indeed; It is impossible to provably show that any software (of sufficient complexity) is absolutely error-free.
    Following this, he suggests we should not strive for the virtually impossible task building fault-free software, but rather aim to build instead fault-tolerant software.



Software Faults -> Software Errors -> Software Failures

Software Faults lead to Software Errors that lead to Software Failures.

Trivedi defines faults, errors and failures:
      -Software Failure occurs when the delivered service no longer complies with the desired output.
      -Software Error is that part of the system state which is liable to lead to subsequent failure.
      -Software Fault is adjudged or hypothesized cause of an error.Faults are the cause of errors that may lead to failures



    So, how does one test their tactical nuclear defense system code? VERY carefully, and it probably wouldn't hurt to comment out the Launch() method as well.