Archive for the ‘database’ Category.

UnitTest MsSql database using Slacker, SlackerRunner

Our open source MsSql UnitTest framework Slacker and SlackerRunner was featured on Microsoft Channel 9. Now it’s easier than ever to UnitTest your database and add it into your CI,CD build pipeline.

Watch Eric Kang from Microsoft explain in detail what you need and how it works.

Databases are for …

Something I read recently reminded me of this.

At one point I was freelancing in New York city. This guy calls me up and wants to see if I can come over and take a look at this pogram “one of his coworkers” wrote. I’m like sure what’s going on with it, well I just want to make sure best practices were followed, we also seem to have some memory problems. Mind you this was early Java days and the program was written in Java. I show up in his office and he takes me to this laptop “the other guys laptop” I looked around but never saw the other guy. The IDE was open and the project loaded, lets take a look. I poked around some, the code seemed ok, the flow was fine.

But then I saw it, and I go hmmmm…. yea, what is it ? do you realize that all the data from the database is loaded into memory, right here. That’s what it’s doing, right ? Yes, that way it’s super fast…. but here is the thing when we have small amount of data the program works fine, but now I have to put it into production and we are using much more data. I tried my best holding a straight face and talking with him about databases and how they are great for querying for data. I left his office and promised to write up some suggestions about the code, how to make it better. I think he gave me a copy of the code to analyze. I wrote a list of 10 or so things and emailed it over to him. Needless to say the first suggestion was about not loading all the data from the database into memory !

A few days later I get an email from him which apperently he sent to everybody that had come over to evaluate the code, 5 or so consultants. He was asking for a short term fix, as the program needed to be demonstrated to management and he was still getting these memory errors when running with the bigger set of data. One of the consultants suggested a solution, increase the memory size for the JVM. It seems to have worked fine as I never got another email from him again, it’s great when you can find resonable fixes to your problems.

PostgreSQL geo locations with PostGIS

I been playing with some geo location data. Naturally when working with the data one needs to save it to a database for later retrieval and selection. If you happen to be using Postgres your in luck. You can add the PostGIS extension that will make your database OpenGIS compatible.
The most important thing is to have geo location aware database when dealing with geo data. That means that you can select objects using proximity right out of the database. It makes easy to figure out how far away different locations are, what are the nearest locations etc. If you don’t have geo location aware database you would have to figure out the proximity in code. Of course that would mean a lot of calculations and CPU cycles.

How I set it up on my Ubuntu / Mint linux box. First install PostGIS on the box then you want to add it to your database as below.

createlang plpgsql yourdatabase
cd /usr/share/postgresql/8.4/contrib/postgis-1.5
psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Then make sure PostGIS has been added to the database.

SELECT PostGIS_full_version()
1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Couple examples of usage.

To see how far away from my place ( Boulder ) some locations in the database are.
SELECT Distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) FROM location_geo;
Then get the five closest ones to my place
SELECT * FROM location_geo ORDER BY distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) LIMIT 5;

You can even set boundaries, use polygons etc.

Java Postgres connection sample

I had to setup Postgres on my linux box, configure and make sure it was working. Since that was the case I started the install, then wrote a very simple Java program to connect and retrieve some data from one of the database tables. The install is simple and pretty easy to google. Once your Postrgres database is up and running you should be able to connect to it. You will need a java Postgres database driver, the one I downloaded was the postgresql-9.1-901.jdbc4.jar then add the jar to your project. And finally to make sure your database is accessible from a program you can use the sample below.

//
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

// Simple test class to prove connection to Postgres database
public class Main
{

        public Main()
                { super(); }

        public static void main(String[] args)
        {
       
                System.out.println(” Postgres Test”);
                Connection myConn = null;
               
                try
                {
                        // The driver
                        Class.forName(“org.postgresql.Driver”);
                        System.out.println(“PostgreSQL JDBC Driver Registered!”);
               
                        myConn = DriverManager.getConnection( “jdbc:postgresql://127.0.0.1:5432/testDb”,
                                        “testUser”, “myPassword” );

                        // Connected ?
                        if (myConn != null)
                                System.out.println(“Successfully connected to Postgres Database”);
   
                        // Get data from the database
                Statement stGetCount = myConn.createStatement();
            ResultSet rs = stGetCount.executeQuery(“SELECT * from  cars”);
           
            while( rs.next() )
            {
                System.out.println(“result=” + rs.getString(1) + “,” + rs.getString(2) + “,” + rs.getString(3) );
            }
        }
        catch( Exception ex )
        {
            ex.printStackTrace();
        }
        finally
        {
                try{myConn.close();}catch(Exception IDontCare ){}
        }

        }
       
}  // EOC
 

MySql in C#

First download the Mysql ODBC driver from the download page.

Then download the .net connector, at this writing it was Mysql Connector/Net 5.2

You can choose to download only the binaries, extract and add /bin/MySql.Data.dll to your project. A help file for the classes is found at /Docs/MySql.Data.chm of the download.

With the dll added to the project, it’s very simple to connect to a MySql database as the snippet below.

using MySql.Data.MySqlClient;

namespace test
{
    class testit
    {
        private MySqlConnection mConn = null;
        private const string msConnStr = “SERVER=myserv.com” +
            “;DATABASE=somedbase;UID=theuser;PWD=thepassword”;

        public void test()
        {
            try
            {
                // Setup and open acceess to the database
                mConn = new MySqlConnection(msConnStr);
                mConn.Open();
                // simple count select
                MySqlCommand command = new MySqlCommand(“select count(*) from cars”, mConn);
                long ret = (long)command.ExecuteScalar();
                // how many rows in the table
                Console.WriteLine(“cars count=” + ret );
            }
            catch (Exception ex)
            {
                Console.WriteLine(“err=” + ex);
            }
            finally
            {
                if( mConn != null ) mConn.Close();
            }
        }

SQLite in C#

As one of my readers pointed out, SQLite is a small database fit for programs that need only minimal database activity looking for small footprint database. It’s used by Firefox / google chrome browser among others.

First let’s take a look at creating a small test SQLite database.  From the command prompt start up SQLite command line tool.

>sqlite3 test.db
sqlite>CREATE TABLE cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, model text, year integer );
sqlite>insert into cars( model, year ) values( “Ford 350”, 2007 );
sqlite>insert into cars( model, year ) values( “Buick Skylark”, 1953 );
sqlite>insert into cars( model, year ) values( “Honda Civic”, 2002 );
sqlite>Select * from cars;
1|Ford 350|2007
2|Buick Skylark|1953
3|Honda Civic|2002
sqlite>.quit

You can activate the help by issuing .help
sqlite>.help

With the database created lets access it from C#. First you need some kind of a .net SQLite wrapper library. I choose to use system.data.SQLite it pretty much wraps the whole SQLite engine, it also works under Mono. All you need is their dll, add it to your project and reference the SQLite namespace. When you ship just include the System.Data.SQLite.DLL and your all set.

using System.Data.SQLite;

// Connection to the database
using( DbConnection conn = new SQLiteConnection(“Data Source=test.db”))
using( DbCommand cmd = conn.CreateCommand())
{
    // Open the database
    conn.Open();
    // Insert to the table
    cmd.CommandText = “insert into cars( model, year ) values( ‘Hummer’, 2000 )”;
    // exec
    cmd.ExecuteNonQuery();

    // Select from the table
    cmd.CommandText = “SELECT * FROM cars”;
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while( reader.Read() )
        {
            Console.WriteLine(“id=” + reader[0] +
                   “, model=” + reader[1] + “, year=” + reader[2]);
        }
    }
}

The SQLite dll is about 820k that’s the only software you need when you deploy. Along with the database, unless you want to create the database on the fly. The upside is that now you have a neat little database at your disposal. It’s fast and has a small footprint, which is what we were looking for.

But… yes there is always one of those. It turns out the Mono version is totally another distro, so don’t expect to fold this in your C# progam and expect it to run under Mono also. See further details on that here.