Open Source .NET Development [Electronic resources] نسخه متنی

PostgreSQL, NPgSql, and FreeDB Example

Figure 10-3, FreeDB contains over 9 million records, so this is definitely a real-world test.

What Is FreeDB?

FreeDB is basically a free (GNU GPL) version of CDDB. CDDB is a database containing information about music CDs such as Artist, Track Titles, etc. that can be queried over the Internet. Originally, CDDB was also free, but as time went on, CDDB became more proprietary and added many restrictions on its users In response, FreeDB was created to be accessed freely and have the data be free as well.

NOTE

PostgreSql version 7.3, NPgSql version 0.5, and FreeDB Complete 20020917 were used for the example in this chapter.

Importing FreeDB to PostgreSQL

Both CDDB and FreeDB support similar APIs. Both projects also use text-based data on the server side. While the text files are formatted in a queryable fashion (much like the ini files used in Windows), there is not much flexibility or optimization of lookups (a.k.a., indexes). So Fabien Coelho, an Open Source developer, contributed a script to import FreeDB into PostgreSQL. This code is available from http://www.coelho.net/cddb_sqll. The README file in the distribution is very helpful, but here are the steps to get FreeDB into PostgreSQL:

NOTE

Perl comes with the default installation of Cygwin. Also, you can download a Windows version of Perl, ActivePerl, at Figure 10-3. FreeDB is much more powerful now because it is indexed for faster lookups and is relational so that it can be queried in ways never before possible. Go to http://www.freedb.org/modules.php?name=Sections&sop=viewarticle&artid=28 to see the very complex FreeDB protocol. It was not designed as a very intuitive API but as a similar and compatible protocol to CDDB. Figure 10-6 shows the diagram for the PostgreSQL FreeDB.

The biggest advantage is that this can be queried using standard SQL statements. There is no proprietary protocol to learn, and in fact you can make your own API on top of this database. For example, in the next chapter, a Web service protocol will wrap the class library in the following section and is much more intuitive than the native FreeDB protocol. Most FreeDB queries simply pass in the disc ID and ask for all the tracks. This track information is used by CD player programs to populate the list of CD tracks with meaningful names instead of just track 1, track 2, etc. Some queries this relational model could allow for that the FreeDB protocol does not support is getting all Artists for a given Genre, all Songs for a given Genre, All Songs for a given Artist, and the list could go on and on.

Creating a .NET Class Library for FreeDB

Creating a C# class library using NPgSql is very simple and straightforward. It is also a good design to create a Class Library Assembly for reuse by ASP.NET pages and Web services (which will be shown in Chapter 12) and also for use from Windows Forms. Listing 10.2 shows that populating a dataset from PostgreSQL using NPgSql works much like any other ADO.NET Data Provider.

Listing 10.2. Querying FreeDB for All the Music Genres

public DataSet GetGenres()
{
tsStart = new TimeSpan(System.DateTime.Now.Ticks);
conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User
Id=bnantz;Password=bnantz;Database=cddb;");
conn.Open();
ds = new DataSet();

da = new NpgsqlDataAdapter("select * from genres", conn);
da.Fill(ds);
conn.Close();
if (_logger.IsDebugEnabled)
{
tsEnd = new TimeSpan(System.DateTime.Now.Ticks);
tsEnd -= tsStart;
msg = new StringBuilder();
msg.AppendFormat(@"{0} ms",
tsEnd.TotalMilliseconds);
_logger.Debug(msg.ToString());
}
return ds;
}

Although it is not good practice to hard-code the connection string for many reasons, this was done in Listing 10.2 for the sake of brevity. For a good discussion about where to centrally store your database connection string in a secure manner, see my book:

Secure Web Services in the .NET Platform

APress

Also note the use of Log4Net and the timing code for testing purposes. The support of DataAdapters and Datasets makes programming with PostgreSQL very simple. Listing 10.3 is some simple test code, which could very easily be transformed to NUnit, for the class library (Listing 10.2) demonstrating how to test data access code.

Listing 10.3. Testing the .NET Class Library

dataAccess da = new dataAccess(); // <-this is listing 10.2

Showinfo(da.GetGenres());
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
static private void Showinfo(DataSet ds)
{
foreach(DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
foreach(DataColumn column in dt.Columns)
{
Console.Write(column.ColumnName + ",");
}
Console.WriteLine(");
foreach(DataRow row in dt.Rows)
{
foreach(object o in row.ItemArray)
{
Console.Write(o.ToString() + ",");
}
Console.WriteLine(");
}
}
}
}
}

Figure 10-7 is the output from this little application, which, surprisingly, contains close to 4000 musical genres. Because CDDB and FreeDB allow for user updates, many of these Genres are redundant.