Database Schema Changes with Postgresql

I’ve been playing with linux recently and really enjoying it.  Quite quickly however I began looking for a good database to use in linux.  The obvious choice if you look around the internets is mysql.  Its the easy one to use, most people use it and as such there’s a ton of documentation etc..  Well for those that know me, the easiest and simplest way is not always what I choose.  So I began to look at postgresql.  PostgreSQL has more in common with databases like Oracle or DB2 than mysql, for some this is a minus but for me that’s a definite plus.  I’ve a lot of oracle experience and skills that can be applied to postgresql quite nicely.  One thing I absolutely love in postgres & oracle is the create or replace functionality for functions.  Gone are the days of if object_id(my_function) is not null…  now I can just create or replace my_function.  That’s killer in my opinion.  So now I’ve got my database (postgresql 9.0) and its time to create some tables.  This was a bit of a dilemma for me, in my native environment (oracle or sql server) I create database change scripts that safely and reentrantly ( you can run repeatedly w/o error) create the objects that are needed in a transactional manner.  But how to do this in postgresql?  Well this post is about what I’ve figured out, and how I’m currently solving that problem.

First, take a look at my postgresql github repository.  What I wanted was a way to have controlled, scripted database changes that were transactional.  They would either entirely succeed or entirely fail and leave the database unmodified.  Also the individual script files would have to have a way of saying I am change #X and I need change #Y to be present first.

Below is how I’ve accomplished this.  What this does is it applies database change 1.0.0 to a database that is of change 0.0.0  (see dbc_0.0.0 in the github rep for how schemaversion, and the initial record get put in place).  Hopefully this is of some help to someone.

create or replace function dbc_1_0_0() returns void as
_old_major integer := 0;
_old_minor integer := 0;
_old_revision integer := 0;
_major integer := 1;
_minor integer := 0;
_revision integer := 0;
_schemaname varchar := 'my-application-name';
if exists(select 1 from schemaversion where major = _old_major and minor = _old_minor and revision = _old_revision and schemaname = _schemaname and current_version = true) then
create sequence user_id_seq;
create table users
userid int8 default nextval('user_id_seq') not null,
email varchar(64) not null,
password varchar(128) not null,
fullname varchar(64) not null,
created_date timestamptz not null,
modified_date timestamptz not null,
constraint pk_users_userid primary key (userid)
update schemaversion set current_version = false where major = _old_major and minor = _old_minor and revision = _old_revision and schemaname = _schemaname;
insert into schemaversion
(_major,_minor,_revision,_schemaname,current_timestamp, true);
select 'Missing prerequisite schema update ' || _schemaname || 'version ' || _major || '.' || _minor || '.' || _revision;
end if;
when others then
raise exception 'caught exception - (%) - when applying update %.%.% to % ', SQLERRM, _major,_minor,_revision,_schemaname;
language 'plpgsql';
select dbc_1_0_0();
drop function dbc_1_0_0();


1 Comment

Massive Data Access Library

Today I got introduced to Massive.  Massive is a data access library written by @robconery.  My co workers @jaymed and @mgroves introduced me to it, and I’ll admit I was somewhat reluctant to give it a shot.  I’m normally of the opinion that the simplest DAL is just pure simple SqlCommands, SqlConnections and a stored procedure call.  However after using Massive for a few hours, I’m really excited about this new tool in my tool chest.  Of course massive has been out for a while, I’m quite slow to the party sometimes.

Massive is great though, I love how in a single line of code I can have data back.  Alternatively I can update the table, or run any other query that I want.  This library if it did nothing else makes integration testing a breeze.  Usually for my tests I prefer to stub out a DAL repository via the interface/repository pattern.  The tests get a FakeSomethingRepository to use that uses List<TheObject> as its in memory store.  What I love about massive is that my unit tests which are really simple because they can test DataRepository.SomeList  can quickly become Integration tests with almost no additional complexity.  For me that’s Huge.  I’m trying a new thing, and that’s not to check in any code that doesn’t have full unit test coverage and Massive is making that happen in a Massive way.  (couldn’t resist)

I’m still learning about these Dynamic things, but a bit of learning never hurt anyone and I know @mgroves will help me out when I get confused.

@robconery If we ever meet, I owe you a beer! Massive is awesome!

If you’re not familiar with Massive, I urge you to check it out here.

1 Comment

P90X First Day

Started my first day of p90X today!  I’m doing the lean track first, I’ll then work though the classic and then maybe the Double or Dual track if I’m really brave.  Its kind of rough getting up earlier so we can work out in the morning but its going to be well worth it in the end.  Today’s workout was core synergistics.  Tomorrow is Cardio X.  I won’t blog after every one, but every so often.  So far my only complaint is that they should have added a “1st” time version or selection where they took an extra minute to explain everything how to do, alternatives etc..  Otherwise I find myself having to look up to see and that often disrupts the exercise.  This might just be a problem because my TV is mounted up high though.


Leave a comment

Nix is Whispering

Here’s another sample of my writing.  Similar disclaimer, this was written while trying to go through the exercises from 3AM Epiphany and while trying to develop an evil character.  This is a work of fiction, this character is not nice.

3AM Epiphany – Challenge 2 – Write a fragment of a story that is made up entirely of imperative commands, e.g. “Do this; do that; contemplate the rear end of the woman who is walking out of your life.” This exercise will be a sort of second-person narration.

Wordcount: 500 (+/- 10%)

You’re getting very sleepy.  Very tired, just lay down on the bed now and rest your head.  That’s right just close your eyes now.  Shh baby don’t fret, don’t stir.  Sleep little one, rest your eyes and drink deeply of your dreams.  That’s right, perfect sleep soundly.  Now listen, but remain asleep little one.

There’s nothing to worry about.  Let your fears fall beside you.  There’s no need for your guard to be up, you’re safely asleep in your bed what could hurt you here.  Just relax and sleep deeply my darling one.  That’s a good boy.

You must sleep, you must rest, you have a big game coming up tomorrow.  It’s the championship game for you and your team, you’ve worked hard for it and must be ready.

But are you really ready?  Do you think you’re really strong enough and quick enough for this game?  Think of all the times you’ve thrown the ball only to see it intercepted by the other team.  Quite a few times if you think about it, think about it now – so many interceptions.  Or maybe think about the times you have fumbled and the other team has gotten the ball, how many games you have been responsible for the team losing.  Quite a few, if you think about it.

Think about the other team.  They have been so dominant in their wins, and your team has been well, less so.  You barely made it to this game in fact.  Do you really think you can defeat them?  Think about whether your team is infact strong enough and good enough to win the game tomorrow.  Maybe they are, maybe they are not.  But are you strong enough to be the deciding factor?  You wouldn’t want to cause your team to lose the game would you?

How could you live with that, how would it feel to know that you were the reason that all your teammates were disappointed and forever known as losers?  You couldn’t live with that could you?  Try to think about it, try to imagine in your dream what it would be like.  There are so many of your teammates that have promising futures ahead of them – if they win this game.  Is that something you could deal with, is that something you could hold in your conscience?

Tell the coach you cannot play.  Tell the coach that your arm hurts or that you feel sick.  You can tell him anything but you can’t play you can’t be the reason your team fails.  Do not be the one who steals your teammate’s futures from them.  You don’t want to be that, the source of their future struggles and failures.  You can’t play.  There is no way you can win if you play.  Tell the coach that, tell the coach to pull you from the game.  You’re not ready.

Now Wake Up!  Wake up this moment.  Its important for you to awaken, wasn’t there something you were going to do?  Don’t you have someone you must call?  Tell that person what you believe, tell them what you now understand.

, ,

Leave a comment

Adventures in Linux Ubuntu 10.10

I recently decided to get back into working with Linux.  My previous experience was running Redhat 6.2 and then 7.0 to support an Oracle 8/9 Server.   My first instinct was to install CentOS on my spare laptop – No VM for me! Well I did get CentOS 5x installed and configured with wireless it was an extreme fight.  I decided on a whim to dump the install and go with Ubuntu mainly because that’s what Rob Conery was using on his Tekpub videos.   I was astonished that installing ubuntu was a 1000x easier than was Centos.  I also loved the install process for the Server version and plan to work more with the Ubuntu Cloud offering soon.

The biggest and most exciting thing for me was that the ubuntu install instantly picked up the wireless card in my laptop.  This was huge as it took hours for me to get Centos to pick it up and had to go through many hoops and a lot more googling than I would have expected.  Now this isn’t hugely unexpected as CENTOS is meant to be an enterprise os and not a desktop operating system.  Mostly this was #ScottFail for not using the right tool for the job from the start.  That being said, I can’t state how awesomely pain free the process was.  I’m an ubuntu convert and for me that’s saying a lot.  I have a habit of sticking with one brand, what can I say? Marketing folks must love me.


, , , ,


Exciting Technologies CUDA

One of the most exciting new technologies that I’ve worked with in the past year is definitely CUDA.  CUDA is a technology that allows for general purpose programming code – typically in c or c++ – to be compiled for and run on GPU devices.  What that means is the graphics card that’s in your high end laptop or desktop can now run general purpose code as well as the graphics code that its already running.

The potential for CUDA is amazing, it allows for massively parallel processing on the potentially hundreds of cores that are available on modern GPUs.  The laptop I’m writing this post on for instance has a NVIDIA 360M card which has 96 processing cores.  That’s compared to the 4 cores in the i7 chip that’s on the motherboard.  The cores are not truly general purpose, they can’t do everything that a modern CPU core can and they prefer to do work in one or more jobs that can be split across the cores.  Math and Physics simulations work extremely well.   While traditional CUDA is based in C there’s also a library called Thrust that allows for C++ programmers to get in the mix too.  Thrust provides very easy ways of transferring data from main memory to device memory as well as some awesome classes for things like map reduce.

There are a couple of uses that I would like to personally explore with CUDA.  Read only database querying.  I would love to be able to as a sample or research process create a dialect of SQL or a sub-set that allowed me to process simple traditional database queries on a CUDA capable device.  While there are a number of companies doing this sort of work, and probably this is something someone could buy instead of build, I think this would be a great chance to learn by doing.  Imagine if I had a table that was approximately 1GB in size with each row being about 128 bytes, that would be somewhere around 8M records in the table.   This works best if the individual records are numerical based, in otherwords large volumes of text aren’t a perfect fit.  In this case however, each of the 96 cores would have to process only ~90K records whereas the 4 cores of the cpu would have to process ~2M records.  While the table can be indexed in a traditional database system if the query patterns are known in advance, it certainly is exciting thinking about how a large volume of work can be spread against a number of cores  using CUDA.

Why use CUDA for something like this?  Why use it for filtering large sets of data?  Well let’s say that its a lot more than 1GB, let’s also say that the native format of the data is some form of binary structure.  To load 8M rows of data into a database it takes a non-trivial amount of time, and if the dataset is constantly being updated that’s a tax you’ll have to pay for every update.  Whereas a program written to leverage CUDA could likely query it directly and without that tax.   Also, this machine is just a laptop you could relatively inexpensively put together a machine with literally thousands of cores.  Imagine now that you had 2000 cores, with the same 8M rows, that’s only ~4K rows per core to filter.  Now that could be much faster.

This is a technology that I’m interested in and learning more about.  I’m sure that I’ve covered no new ground with this post and experts will probably be bored.  But perhaps there’s someone out there that wasn’t aware of CUDA or is just getting into it?  How are you finding it?  What have you found works, what doesn’t?

, ,

Leave a comment

This is Awesome

Is it just me or does this really happen?

Love Dilbert.

Leave a comment

Dying days of winter


I’m sitting here at the lake looking out the window at the fresh snow, nearly two feet of it – and the Yankee game is on the tv.   It’s the dying days of winter, the time when the snow cold and ice can no longer hold their grip.  The time when warm days wrestle with cold for control of the climate.  Cold cannot win, winter is too long in the tooth, its numbered days are down to a precious few.

I always dread this part of the year, when spring has yet to rise and the winter is fading fast.   Something about the cold always held an allure for me.  We’re headed quickly into the time of heat and humidity.  Not my favorite time of year.

But before it goes one last sunny winter picture.


Leave a comment

Music Isn’t it Interesting

Last night I attended a family dinner with three different parts of the family (parents and two different aunts + assorted cousins).  It was definitely a lot of fun, I don’t have a huge family and don’t get too many evenings like this.  I enjoyed it.  During the evening music was playing, what music played and when it played formed the idea for this blog post.

My cousin was on a mission to play her father’s ipod, and to play the songs that her father liked.  I sensed some of that was not as much a music thing and more a father daughter thing.  That’s totally cool, but I still found it interesting the songs they chose.  They went on and on about songs from the 60’s and 70’s that they loved.  Now our tastes in music are a bit different to say the least.  I think 60s and 70s = Zeppelin and Black Sabbath.  But to them it was other bands that I’d heard of but not really “heard” a lot.  Most of the songs were classic “oldies” quick and easy to listen to songs about sock-hops and other friendly concepts.  Easy to listen to.. Easy Listening.  I guess that’s one reason our music tastes differ.

Its not that they have bad taste, well bad taste per-se I guess is more accurate, its probably that we just listen very differently.  I’ve been playing guitar now for about four years, and a lot of what I listen to has to do with my guitar playing.  I like songs that have a big emphasis on guitars, and ideally a solo or two.   So bands like Zeppelin and Black Sabbath are perfect for me, I especially love the part in the live version of Stairway where Jimmy Page just gets lost in his solo.  He’s in his own world, the listeners just happen to be there witnessing the tonal magic as it’s weaved.

This post is about discovery and opinions.  I’m saying that to me, for my ears Zeppelin and Sabbath are better than the music my Uncle and cousin were listening to.  However I’m also listening as a guitar player.  Zeppelin’s The Song Remains the Same that was recorded in NYC in the 70’s is magic for me because I can relate to the sensation of playing like that – read that as I can dream of playing like that, you won’t see me in MSG anytime soon.  Just watch the dvd or blu ray of this show and you’ll see what I mean.  Zeppelin is on the money on this night,  playing like its just the band.  I’m coming from a different place when listening, the music is speaking to me differently.

I think for many music means memories.  You hear a song for the first time and whatever is going on in your life at that time is encoded in the memory of the song.  So in the future when you hear that song again you relive that memory – whether painful or joyus.  And while true for me as well, I often find that if I turn up the volume in the car just a few notches past socially acceptable levels: I can get totally lost in the music, I can get lost in the story that the guitar players are trying to tell with their six strings.

There’s an amazing amount of incredible music to listen to this way.  Many Heavy Metal bands that have a melodic component are incredibly musical.  Their songs are rich with music theory and the application their of.  These songs are not easy to listen to, far from it.  They require participation.  Thats what I love about them.    But not everyone agrees.

My uncle also said that he refuses to listen to any music recorded after 1978.  Gasp.. Horror.. Shock…  No music after ’78?  Do you realize that you’ve just eliminated all of Metallica’s music?  As a huge metallica fan that’s unfathomable, but there’s so many other incredible songs in that time period too..  How many of us have danced and partied to 80s songs while in college?  For him for my uncle songs and music is about memories.  Its not about the musical theory, the chord progressions, passing tones or modes & scales at play.  And that’s ok, its interesting and its fascinating the different reasons and ways that music speaks to us.

I write to understand.  Not to inform.  Writing is a way that I process my thoughts.  So If you’re now wondering what was my point, what did I want you know?  Well I’m excited that you’re reading;  thank you.  I think if anything for this post my message is this.  Pick a song, any song and listen to it with the volume turned up.  Preferably a song that you’d normally not listen to.  If you’re not a metal fan, try a metallica song.  Try my favorite: Fade To Black.

Leave a comment

Snow on the Lake

Snow Day on Lake George

Snow Day on Lake George

This morning I woke up to a fresh snowfall here at the Lake.  You can just barely make out the mountains in the distance.  The specs on the ice in the distance are ice fishermen, the open water is where people have bubblers up to protect their docks from the ice.  I thought it looked pretty cool – and in this case literally.

Leave a comment