PostgreSQL SQL conveniences

For years with Sybase I chafed at having to create "temp tables" with intermediate results in preparation for the final query. PostgreSQL eliminates the need for temp tables because it allows a subquery to follow the FROM of a SELECT. Evidently Oracle allows something similar too.

Another PostgreSQL convenience is generate_series. Sometimes in a SQL query one needs a list of all possible integers in a range. In Sybase, again, this has to be done with a temp table. But in PostgreSQL, one can SELECT FROM generate_series.

PostgreSQL SQL rich syntax obviates the need for stored procedures in these situations.

Computing energy from FFT

The straightforward way to compute energy of a signal is just summing the squares in the time domain. But it can also be computed from the FFT, or more properly speaking the spectrum, which is the square root of the sum of the real part squared and the imaginary part squared. The tricky part is realizing two things:

1. Typically only the positive half of a spectrum is displayed, so if you want to compare apples to apples (i.e. amounts of energy in the same units), you have to add in the negative spectrum as well. Since the spectrum of a real-valued function is symmetric, this just means doubling the energy from the positive spectrum.

2. The DC component of the spectrum (0 Hz) appears just once in the spectrum, so it doesn't have to be doubled.

Thus we have:

Energy = Sum of squares in time domain = DC component squared + 2 * (sum of squares of positive spectrum)

Windows 2000 and Virtual PC

Surprised to find that Windows Update still works with Windows 2000. Evidently Microsoft "Extended Support" is good through July, 2010. Also surprised to see that Microsoft Virtual PC 2007 "add-ins" support Windows 2000, including mapping drives to the local hard drive.

This means that those who need to test software compatibility with Windows 2000 should create a fully patched virtual hard drive image under Virtual PC sometime prior to July, 2010 when Windows Update goes down for Windows 2000.

dotConnect for PostgreSQL

Don't even think about using PostgreSQL with Visual Studio without purchasing dotConnect.

http://www.devart.com/dotconnect/postgresql/

The alternatives are npgsql, which doesn't integrate with the Visual Studio data designer, and ODBC, which is slow. And although ODBC allows some use of the Visual Studio data designer, it has trouble with parametrized queries.

Also, don't bother with the free version of dotConnect -- it doesn't include the Visual Studio data designer integration. And, if you can, don't bother with the 30-day trial versions either as it's difficult to convert projects over from the trial version to the registered version.

log4net in VS2008 desktop app

The log4net documentation advises one to modify app.config XML file to add a log4net stanza. However, under Visual Studio 2008, this causes an error message

Could not find schema information for the element ‘log4net’

There is a lot of advice on the web on how to avoid this compilation message for web apps, but not for desktop apps. The easiest way in a desktop app (and possibly also for a web app) is to replace in the C# code

XmlConfigurator.Configure()

with

XmlConfigurator.Configure(new MemoryStream(
    (new System.Text.ASCIIEncoding()).GetBytes(
    "<log4net>" +
    "    <appender name=\"ConsoleAppender\" type=\"log4net.Appender.ConsoleAppender\" >" +
    "        <layout type=\"log4net.Layout.PatternLayout\">" +
    "            <param name=\"Header\" value=\"[Header]\r\n\" />" +
    "            <param name=\"Footer\" value=\"[Footer]\r\n\" />" +
    "            <param name=\"ConversionPattern\" value=\"%d [%t] %-5p %c %m%n\" />" +
    "        </layout>" +
    "    </appender>" +
    "    <root>" +
    "        <level value=\"DEBUG\" />" +
    "        <appender-ref ref=\"ConsoleAppender\" />" +
    "    </root>" +
    "</log4net>")));

Or whatever was in your <log4net> stanza in your app.config

PostgreSQL silent installation

I'm bundling PostgreSQL as part of a desktop application installation, only to find out what was the recommended silent installation procedure under PostgreSQL 8.3 -- using the MSI file -- has been removed from PostgreSQL 8.4. Evidently, you're supposed to use command line utilities to effect silent installation now -- and it's not entirely documented. After some trial and error, I determined that the following sequence will silently install PostgreSQL 8.4:

1. mkdir C:\Program Files\PostgreSQL\8.4
2. net user postgres password /ADD
3. initdb --username=postgres C:\MyDB
4. pg_ctl register -D C:\MyDB
5. net start PostgreSQL
6. psql -U postgres -f MyDDL.sql

Looking for business 3D? Use WPF

The world of 3D on Windows for business or scientific purposes has been in flux for many years. DirectX was intended for games and besides was for unmanaged code while Microsoft has been pushing for development to be managed code (and rumors circulate that the version of Windows after Vista won't even run unmanaged code natively). Then there was Managed DirectX (MDX), version 2.0 of which was suddenly abandoned just as it was about to get out of beta. Then Microsoft came out with XNA Game Development Studio at the end of 2007. This had three problems. First, it was for Visual Studio 2005 instead of Visual Studio 2008. OK, maybe we can live with that. Second, it was geared toward games, with the framework expecting the application to always and continuously compute the next frame. Well, I guess we can work around that. Third, it wasn't compatible with WinForms (or any other managed platform for desktops). It was on their list of features to add, but they ran behind schedule and nixed it. Big problem.

The correct and best way today to develop 3D applications for business and scientific domains on Windows is to use WPF (Windows Presentation Foundation), which is part of .NET 3.x. WPF exploits 3D acceleration hardware like DirectX and is fully accessible to C# desktop applications. And it's compatible with Visual Studio 2008 -- in fact WPF seems to be the primary reason Visual Studio 2008 was even built.

That's a screenshot of a WPF application I'm working on. The cylinder and metal cans were mathematically computed on the fly in C#, because they're such simple shapes. The hammer and chisel were designed in Blender 3D and exported to XAML. Using standard Windows mouse interaction code, the cylinder, cans, and hammer can be dragged around.