Python Script to Count Tables, Columns and Rows in SQLite Database

One of my colleagues asked me for some metrics regarding the size of our project’s database to put in a report, so I wrote a short little script Python to summarize the dimensions of SQLite tables. It prints a list of the tables in the database, along with the number of columns, rows and cells in each.

The command line is as follows:

python.exe DatabaseSizeSummary.py database.sqlite output.txt

Output is to a specified output file, or to standard out if no file is specified. It’s tab delimited for easy Excel import. Here is a sample of the output text:

TableName	Columns	Rows	Cells
Boreholes	25	820	20500
Canals	28	14	392
Pipelines	25	785	19625
Reservoirs	27	387	10449
Siphons	23	23	529

Number of Tables:	5
Total Number of Columns:	128
Total Number of Rows:	2029
Total Number of Cells:	51495

And here is the same output with a little bit of Excel formatting:

output screenshot

Interesting code includes getting the list of tables:

tableListQuery = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY Name"
cursor.execute(tableListQuery)
tables = map(lambda t: t[0], cursor.fetchall())

and getting the number of columns in each table:

columnsQuery = "PRAGMA table_info(%s)" % table
cursor.execute(columnsQuery)
numberOfColumns = len(cursor.fetchall())

You can download the script here, and if you add any nifty features, do share!

PS: This was the first time I wanted to share a code file on my blog, and I didn’t know what the best way to do so was. I tried Google Drive, but couldn’t get a direct download link to the file. I also considered my Bitbucket account, but ended up using the public folder of my Dropbox account. However, I don’t like the URL much, and I’m sure there is a more generally accepted way of doing this. What do you use to share code files on your blog?

Update: I’m trying out gist.github.com, so below is a version controlled, editable version of the script. Thanks to @simondlr and @konradblum for telling me about this awesome service – you can read more about it’s integration with WordPress here: en.support.wordpress.com/gist/

UPDATE 2013-02-28: I figured out that pysqlite2 is deprecated and has been replaced by the sqlite3 library, which is bundled with Python 2.5 and up by default, and thus requires no download. I’ve removed the links to pysqlite2 and modified the downloadable script to use sqlite3 instead.

 

Posted in Software Development | Tagged , , , , | 1 Comment

Generating Smarter Visual Studio Code Snippets Using T4 Templates

The last formal tip in The Pragmatic Programmer urges the reader to “Sign Your Work”, and states that, at the very least, each code file should have the author’s name and the creation date as a comment at the top. I was looking for ways to do this routinely in Visual Studio, and came up with a trick where the ends probably no longer justifies the means, but maybe somebody else can use this concept for something more complex than generating header comments.

Visual Studio Snippets

Right-clicking in a code file in Visual Studio allows you to Insert Snippet. Default Snippets include things like constructors and class definition boilerplates. You can also create your own snippets – one way that I’ve used before is the Snippet Editor originally by Bill McCarthy. The simplest way is to highlight some code in Visual Studio, right-click and select Export as Snippet. XML code defining your Snippet is generated and stored, by default, in the Code Snippets folder in your Visual Studio documents folder.

I wanted to have a Snippet for inserting simple author headers like these into my code files:

// <author>Pieter Muller</author>
// <date>2012-11-09</date>

However, Snippets cannot execute method calls, and so I had no way of retrieving the current date for export. This is where the T4 templates come in…

T4 Templates

T4 Templates offer built-in code generation in Visual Studio. The templates support a great deal of flexibility, as they can access a number of data sources, and allow the execution of .NET code while generating output. They are frequently used to dynamically generate database interaction code depending on a schema. I’m not an expert, but if you would like to be one, Oleg Sych’s tutorials here would probably be a good place to start. Scott Hanselman has also blogged about them here and here.

The T4 code that I am interested in, is this:

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".txt" #>
//    <author>Pieter Muller</author>
//    <date><#=DateTime.Now.ToString("yyyy-MM-dd")#></date>

This generates exactly the header I want, using DateTime to get the current date. However, there is a catch – T4 Templates are intended to output entire files, and I could not find a way to insert a snippet of T4 output in a code file, except to copy & paste, despite asking about it on StackOverflow

Using T4 to Generate a Snippet

The solution I came up with is to write a T4 template that doesn’t generate the desired code directly, but instead generates a Snippet that can insert the desired code. The T4 code then writes the generated Snippet definition to the Visual Studio Snippet folder, allowing me to right-click and Insert Snippet wherever I want.

First, write the header comment the way you want it, then right-click and Export as Snippet. Change the name to something sensible, and save the result. Open the saved file in a text editor. For simplicity, I removed the unused XML elements, and was left with this:

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>Author and Date Header</Title>
      <Author>user</Author>
    </Header>
    <Snippet>
      <Code Language="csharp"><![CDATA[// <author>Pieter Muller</author>
// <date>2012-11-09</date>]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Next, right click your project in the Solution Explorer, click Add, select New Item, and add a Text Template to your project. Paste the Snippet XML code from above into the template.

Note that the xml tag in the Snippet definition’s first line will confuse the T4 template parser, so we need to replace it with a manual WriteLine call, which we wrap in <##> tags, as shown in line 3 below.

We also replace the hard coded date with a call to DateTime.Now, shown below in line 15, this time wrapped in <#= #> tags. Your T4 file should now look like this:

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".txt" #>
<# WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>"); #>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>Author and Date Header</Title>
      <Author>user</Author>
    </Header>
    <Snippet>
      <Code Language="csharp"><![CDATA[// <author>Pieter Muller</author>
// <date><#=DateTime.Now.ToString("yyyy-MM-dd")#></date>]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

You can test this by opening the .txt file nested under the template file in the Solution Explorer – the result should be identical to your original Snippet XML.

Using T4 to Copy the Snippet Code

Lastly, we add another set of commands inside <# #> tags at the bottom of the T4 Template, that reads the output file and writes it to the Snippets folder. Unfortunately, this requires some hard coded paths – if you have any ideas on improving this, let me know:

<#  
  System.IO.StreamReader sr = new System.IO.StreamReader(@"C:\Users\Pieter\Documents\Visual Studio 2010\Projects\SnippetDemo\SnippetDemo\AuthorAndDateHeaderSnippetGenerator.txt");
  string sourceText = sr.ReadToEnd();
  sr.Close();

  System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\Users\Pieter\Documents\Visual Studio 2010\Code Snippets\Visual C#\My Code Snippets\AuthorAndDateHeader.snippet");
  sw.WriteLine(sourceText);
  sw.Close();
#>

Keep in mind that you have to compile the T4 Template twice to push changes to the Snippet – the first run generates the Snippet definition, and the second run writes the first run’s output to the Snippets folder.

The complete T4 Template is shown below. Have fun, and if you use this for something interesting, please leave a comment!

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".txt" #>
<# WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>"); #>
  <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <CodeSnippet Format="1.0.0">
      <Header>
        <SnippetTypes>
          <SnippetType>Expansion</SnippetType>
        </SnippetTypes>
        <Title>Author and Date Header</Title>
        <Author>user</Author>
      </Header>
      <Snippet>
        <Code Language="csharp">
          <![CDATA[// <author>Pieter Muller</author>
// <date><#=DateTime.Now.ToString("yyyy-MM-dd")#></date>]]>
        </Code>
    </Snippet>
    </CodeSnippet>
  </CodeSnippets>
<#  
  System.IO.StreamReader sr = new System.IO.StreamReader(@"C:\Users\Pieter\Documents\Visual Studio 2010\Projects\SnippetDemo\SnippetDemo\AuthorAndDateHeaderSnippetGenerator.txt");
  string sourceText = sr.ReadToEnd();
  sr.Close();

  System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\Users\Pieter\Documents\Visual Studio 2010\Code Snippets\Visual C#\My Code Snippets\AuthorAndDateHeader.snippet");
  sw.WriteLine(sourceText);
  sw.Close();
#>
Posted in Software Development | Tagged , , , , , , , | Leave a comment

Replacing a delegate with an inline lambda expression: Improvements on an MS Exams Custom WPF Control example

I was dusting off my WPF skills when I took a look at the Custom WPF Control example in this book: MCTS Self-Paced Training Kit (Exam 70-511): Windows Application Development with Microsoft .NET Framework 4

The example, in Chapter 5 Lesson 3, is a simple step-by-step exercise to create a basic Digital Clock WPF Control. While trying it out, I made two modifications to the code that I think improves the style.

Here is the code as given by the book:

public class DigitalClockControl : Control
{
	public static readonly DependencyProperty TimeProperty;
	System.Timers.Timer myTimer = new System.Timers.Timer();
	delegate void SetterDelegate();
	
	static DigitalClockControl()
	{
		DefaultStyleKeyProperty.OverrideMetadata(typeof(DigitalClockControl),
			new FrameworkPropertyMetadata(typeof(DigitalClockControl)));
	
		FrameworkPropertyMetadata metadata = new FrameworkPropertyMetadata();
	
		TimeProperty = DependencyProperty.Register("Time",
			typeof(string),
			typeof(DigitalClockControl), metadata);
	}
	
	public DigitalClockControl()
	{
		myTimer.Elapsed += new System.Timers.ElapsedEventHandler(MyTimeElapsedHandler);
		myTimer.Interval = 1000;
		myTimer.Start();
	
		this.DataContext = this;
	}
	   
	void TimeSetter()
	{
		SetValue(TimeProperty, DateTime.Now.ToLongTimeString());
	} 
		
	void MyTimeElapsedHandler(object sender, System.Timers.ElapsedEventArgs e)
	{
		Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Normal,
			new SetterDelegate(TimeSetter));
	}
}

Switching from a Setter to a Property

I’m not fond of the TimeSetter() method in line 28. I much prefer Properties to Getters and Setters – in this case you need only declare the Setter:

public string Time { set { SetValue(TimeProperty, value); } }

Changing the Invoke call to use the Property

Since we have now removed the TimeSetter() method, we must change the Dispatacher.Invoke() call in line 35 to assign the desired value directly to the Time property. The neat thing here is that we don’t need the delegate declaration in line 5 anymore:

delegate void SetterDelegate();

We now remove the delegate declaration and put the functionality of the old TimeSetter() method in an inline lambda expression right in the Invoke() call. The Invoke() method still wants a delegate parameter though, so we have to cast the lambda expression to an Action instance:

Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Normal,
	(Action)(() => this.Time = DateTime.Now.ToLongTimeString()));

Final Code

Below is the resulting code, without the delegate and using a property instead of a setter method. The results are exactly the same, the only difference is the way it is implemented. And even if the implementation style does not matter to you, it’s still cool to know you could change it if you wanted to. (Oh and lambda’s are way cooler than delegate declarations!)

public class DigitalClockControl : Control
{
	public static readonly DependencyProperty TimeProperty;
	System.Timers.Timer myTimer = new System.Timers.Timer();
	
	static DigitalClockControl()
	{
		DefaultStyleKeyProperty.OverrideMetadata(typeof(DigitalClockControl),
			new FrameworkPropertyMetadata(typeof(DigitalClockControl)));
	
		FrameworkPropertyMetadata metadata = new FrameworkPropertyMetadata();
	
		TimeProperty = DependencyProperty.Register("Time",
			typeof(string),
			typeof(DigitalClockControl), metadata);
	}
	
	public DigitalClockControl()
	{
		myTimer.Elapsed += new System.Timers.ElapsedEventHandler(MyTimeElapsedHandler);
		myTimer.Interval = 1000;
		myTimer.Start();
	
		this.DataContext = this;
	}
	
	public string Time { set { SetValue(TimeProperty, value); } } 
	
	void MyTimeElapsedHandler(object sender, System.Timers.ElapsedEventArgs e)
	{
		Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Normal,
			(Action)(() => this.Time = DateTime.Now.ToLongTimeString()));
	}
}
Posted in Software Development | Tagged , , | Leave a comment

Rounding doubles in C#

Don’t trust Math.Round(), and a workaround

Turns out Math.Round() can give you some nasty surprises when applied to doubles:

double value = 0.575;
value = Math.Round(value, 2, MidpointRounding.AwayFromZero);
Console.WriteLine(value); // Prints 0.57 instead of 0.58

The application I’m working on does a lot of complicated calculations of construction costs. The inputs all come from a SQLite database, where they are stored as float values, and once I have them in my C# application, I do all my calculations with doubles. I know decimals are recommended when working with financial values, but if half your values are actually regular doubles and the other half are financials, constantly casting and converting can be frustrating, so I decided to just use doubles. I probably won’t make that choice again, but it works OK. Where it gets a bit messy, is all the way at the end, when rounding the results of some complicated calculation to dollars and cents. There are a couple of articles out on the web discussing why this is a problem and detailing the risks. The short version is that many decimals values cannot be perfectly represented in floating point, and thus the stored values are slightly off. There are some nice examples of this happening in the Python tutorial page Floating Point Arithmetic: Issues and Limitations. In my application, this only becomes an issue when I want to round a value that has a 5 in the third decimal position. For example, rounding 10.005 to two decimals should give 10.01, but if the closest floating point representation of 10.005 is actually 10.004999999999, then it will round down to 10.00, not up to 10.01, as expected. I knew about this problem, and implemented the following code to handle my rounding for me:

///
/// Applies rounding to the given value.
/// Rounding is applied twice, first 5 digits deeper than the desired number of digits,
/// to fix calculation rounding.
///
///The value.
///The desired number of decimal digits.
/// Rounded value.
public static double ApplyRounding(double value, int digits)
{
    // First rounding is to fix rounding errors,
    // by changing things like 0.99499999999999 to 0.995
    value = Math.Round(value, digits + 5, MidpointRounding.AwayFromZero);
    // Round value to specified number of digits
    value = Math.Round(value, digits, MidpointRounding.AwayFromZero);
    return value;
}

However, this had a major bug. It worked fine for 99% of cases, but when I finally got around to running some integration tests yesterday, I found a 1 cent error on one of my results. The problem with the code above is that it still fails if the result of line 11 cannot be represented as a double. As in my original example, 0.575 is expected to round up to 0.58, but rounds down to 0.57.

So, I upgraded the ApplyRounding() method with a cast to decimal before starting the rounding process. This allows the first and second rounding calls to have perfect accuracy, and I get the expected result:

///
/// Applies rounding to the given value.
/// Rounding is applied twice, first 5 digits deeper than the desired number of digits,
/// to fix calculation rounding.
///
///The value.
///The desired number of decimal digits.
/// Rounded value.
public static double ApplyRounding(double value, int digits)
{
    // Cast to decimal before rounding:
    decimal decValue = (decimal)value;

    // First round is to fix rounding errors by changing things like 0.99499999999999 to 0.995
    decValue = Math.Round(decValue, digits + 5, MidpointRounding.AwayFromZero);
    // Round value to specified number of digits
    decValue = Math.Round(decValue, digits, MidpointRounding.AwayFromZero);

    // Cast rounded value back to double:
    return (double)decValue;
}

Though this fixed the problem, it does feel like a bit of a workaround. Next time I’ll probably use decimals all the way through.

Posted in Software Development | Tagged , | Leave a comment

Building the source of Seven Kingdoms Ancient Adversaries on Ubuntu Linux

I recently discovered that Seven Kingdoms: Ancient Adversaries had gone Open Source! This innovative RTS was one of my primary sources of sleep depravation when I was in high school. I remember lugging my computer (tower, CRT monitor, all of it) over to my friend’s house by foot, linking our machines with a home made serial cable, and playing the whole night through.

While the objective of the game, conquest, is hardly original, the game made great use of diplomacy, economy, loyalty and even espionage to offer gameplay mechanics all together different from the usual “gather gold, build army, swarm opponent” methodology.

The game’s source code was (most kindly) released by Enlight Software in 2009 and now lives over at 7kfans.com. Those guys have done an excellent job of modernising the code, and it now runs on both Windows and Linux systems.

I decided to grab the source and build it, for a number of reasons:

  • I have an interest in game development, and this offers an opportunity to peek inside the code of a game I already know. Most of all I am eager to see how the game’s Artificial Intelligence is implemented!
  • It offers the opportunity to polish up on a number of my rusty or underdeveloped skills. The source is in a git repository, which I rarely use (I mainly use SVN). I miss playing around in Linux and those skills are oxidising fast. Furthermore, the code is in C++ which I have never really used, so this is a great chance to learn.
  • It’s a way for me to get familiar with open source projects – maybe I could even contribute a patch or two.

Building from source is hard if you haven’t done it before

I underestimated the complexity of downloading and building this code – mostly because it is something I have never done before. Also, my current Ubuntu installation has barely been used, and I haven’t done any development on it at all. So I was going to need to install a lot of tools.

The 7kfans guys offer a wiki page to get you started on the code, but I suspect they have been doing this a while, and I would have appreciated a more thorough guide. So, now that I have figured it out and got the game running (yay!), I am writing this guide for the reference of anyone else who wants to try.

I cannot guarantee that these steps will always be required, and I also cannot guarantee that these steps will always be sufficient. However, this is what it took for me to get the game built on a (practically) virgin Ubuntu 12.04 netbook.

If I missed something, or if I’m hopelessly confused without realising it, please enlighten me in the comments below!

Getting the source code

The code is in a git repository here, with instructions over here. If you don’t have git on your system, install it with:

sudo apt-get install git-core

Switch to the folder where you want the source, and clone the source code repository using:

git clone git://www.7kfans.com/7kaa.git

Getting all the dependencies ready

It took me some trial and error, but here are the dependencies I needed to install for the build to work. Remember that I started with a machine that didn’t even have a compiler on it, so if you’ve used your machine for development before, you likely have a couple of these already.

First off, you’ll need a C++ compiler:

sudo apt-get install g++

The project uses Autoconf to generate the configuration scripts for the build:

sudo apt-get install autoconf

The code uses the SDL and OpenAL libraries for cross platform sound, graphics and network support:

sudo apt-get install libsdl1.2-dev
sudo apt-get install libopenal-dev
sudo apt-get install libsdl-net1.2-dev

Lastly, you’ll need the Make utility to put it all together:

sudo apt-get install make

Running the build

Run the following script in the root folder of the source tree:

./autogen.sh

This script removes any previous configuration and runs Autoconf to generate configurations for the impending build operation, and to perform some other wizardry that I still have to figure out.

autogen.sh will leave you with a configure script, which you can optionally run to check that you are in fact ready for the build:

./configure

Check towards the end of the output to see if anything is missing. If there is something, please post the solution below, so I can add it here! Smile

Finally, you can type make to compile the source, and if you are happy that it compiles, the following will both compile and install the game:

sudo make install

And you’re done! Type 7kaa to launch the game, and grab a beer (or coffee, if you are so inclined) to celebrate!

Posted in General Technical | Tagged , , , , | 7 Comments

“I need to be bold, need to jump in the cold water…” (Joshua Radin)

When I was in high school, my family hosted an exchange student, a girl from Finland, about my age. We became quite close, and on the icy cold (by South African standards, not Finish standards!) winter morning when she had to say goodbye to all her South African friends, the melancholy hung like a dark cloud over our house. On an impulsive schoolboy whim, I devised a diversion – I dared my friend to jump into the cold swimming pool with me before we set off to school together for the last time. This distraction served us well – our brooding sadness was washed away by the icy water and the nervous laughter of excitement it stirred in us.

I’ve been brooding on the idea of starting a blog for a long time. There are a number of reasons for this. First, I have been only a reader for a long time – now I want to contribute. Another is my hope that sharing my technological experiments and musings may lead to input from others, others in a community I would love to collaborate in. And hopefully that collaboration will lead to more discovery, perhaps even move nervous laughter of excitement!

The point I am trying to make with this rather random story is this – I have made the jump into the cold water. I don’t know exactly what I’m going to do once I’m in it, but now my feet are wet, and I’m excited. Herewith my brave foray into the world of technical blogging – or any blogging for that matter. A start has to be made at some point, and skill is best learned by exercise. So bear with me as I figure out what I want to say, how I want to say it, and how to best manipulate WordPress to present it as I see fit.

About Me

Absurdly, I originally started this post while sitting in the passenger seat of a 4×4 driving through the Caprivi region in the northern part of Namibia, surrounded by small clusters of the mud and wood huts characteristic of this area, and spotting the occasional elephant (no jokes). I realise that this location is more often the inspiration for African Safari adventure blogs than passionate descriptions of algorithms, but these contradictions are part of living in Namibia.

I was born and raised in South Africa, beautiful Stellenbosch to be exact. When I was about 10 years old, my eldest sister, having just started studying electronic engineering, attempted to teach me some PASCAL. My father, himself a seasoned electronic engineer, soon intervened and suggested BASIC as a more appropriate programming language for a child of my age, barely able to speak a regular language other than my native Afrikaans at the time.

I fell in love with programming right away. The ability to tell the machine what to do, and to have it perform tedious calculations I would be far too lazy to do myself, provided hours of pleasure! I went on to study Electronic Engineering with Computer Science at the University of Stellenbosch, and during my postgrad, I started to realise that I enjoyed programming a lot more than the rest of my engineering work. When I shared this epiphany with a friend, she pointed out something I had failed to notice for a long time – whenever I posted a really excited work related status update on Facebook, it was because I had finally gotten some terrific piece of code to work.

One would think that at this point my career choice would be pretty obvious. However, a diversion! I met this girl… Long story short, I ended up moving to her native Namibia and marrying her. In fact, I am playing Scrabble with her at this very moment on the porch of our Windhoek home! So that worked out really well for me.

Namibia has a lot to offer. Wide open spaces, more biltong than could possible be good for you, some of the most picturesque environments I have ever seen, and a wonderfully relaxed lifestyle with a ludicrous number of public holidays (especially in May). However, it does not offer much in the line of professional software development career opportunities. I ended up working as a consulting electrical engineer at a small firm, where I have been for 3 years. It is this position that has afforded me the wonderful opportunity to be driving around in the Caprivi strip with a netbook on my lap. And it’s been good – I’ve seen massive stretches of one of the most interesting African countries (in my opinion), and I’ve had the chance to experience its bizarre blend of German, Afrikaans and African cultures.

But its time for a change. I am hoping to make a shift into a more software centred work environment – overseas if need be. And this blog is a medium for me to chronicle my progress as I attempt to broaden my technological horizons with this dream constantly in my sights.

That concludes my introduction – my next post promises to be much more technical, and somewhat more daunting to write. But at least now I got my feet wet – the swimming part can’t be that difficult…

Posted in Life in General | Leave a comment