Saturday, February 19, 2005
OLE DB Providers, CVS & VCRs
What a joy it is to work with software that not only does exactly what you expect it to do, but is also easy to work with. My latest activities have been relating to the convergence of a system for MS-SQL to PostgeSQL.
I am not completely free of the ball and chain of SQL Server, but the closer I get to that bright light of a real RDBMS, the more excited I get. I had never experienced importing / exporting to and from MS-SQL prior to this week. I wasn't worried per se, but it was unfamiliar to me. I am quite familiar with the workings of this now.
I have installed PostgreSQL 8.0.1 on my Windows machine. This happens to install the OLE DB provider as well (nice). So now I have the option of exporting directly to a PG database. It was quite easy to setup the DSN for my local machine and start chugging away. There are a couple of tricks to the DSN. By default bools are represented as char fields, you can simply uncheck this option. You can also set some other configuration options regarding bigints, text datatypes etc.
The ODBC provider is not the problem area. Once again due to the non standard MS way of doing things it causes problems. "uniqueidentifier" columns. You may as well forget about it. You can transform them into text datatypes, but you'll be pooched after that. There is apparently a package kicking around that adds "uniqueidentifier" as a type to postgres. It relies on the libuuid library, which isn't available on windows. Why one would want that datatype, other than for a slow conversion is beyond me. Sequences are available and 10^23142341 better than a global unique binary value.
Another pitfall when exporting is the fact the MS-SQL handles case sensitivity in a non standard way. According to the standards SQL is case insensitive. PG handles fields, and tables inside of double quotes to be case sensitive, but the basic useage converts to all lower case. I believe the spec states to handle all uppercase. I'm not sure about that. Regardless ... this causes problems on the table data movement because a column named someID would have been created as someid. But when the data is moved from SQL it's coming from someID and into someID ... which isn't available. You basically have to go slow, and transform all of your column names to be lowercase for table creation.
The other option is to re-create the schema first, and then just append the data into an existing table. From what I have read on the internet this provides much greater results. You can transform data, and shift columns as needed. This may be the way to go when you are moving more critical data. Since my schemas are rather small, and the system is in develeopment, I just dumped the SQL scripts, and removed all of the MS-SQL crap in them (like square brackets [], and dbo designations, and specifying COLLATE blah blah, Latin1_ blah blah for var chars etc.). I think this helps you realize how lacking in features, and datatypes other DBs are compared to PostgreSQL. Not Boolean, numerous date / time types, Text, and a heap of others.
<time days="6" / >
That was me being funny with XML. Alright ... not so funny. I wrote the first half of this in a draft six days ago. Since I'm leaving Sooke to head back to Calgary tomorrow or the next, I decided to sum up my week here before I check out.
I continued on with my PostgeSQL fun this week. I must say that my research was indeed correct, in that loading your schemas prior to data export yielded a much greater success ratio. It was so much easier to just append some conformed data onto an existing table. You could drop out coplumns as needed, and things seem to go smoothly.
Everyone else seems to be really getting on board with the PG niftyness. It just goes to show you how much people who have worked with bad tools, appreciate the good ones. They'll never go back now. buwhahahahahaha!
We have the developers set up with Apache, PHP & PostgeSQL all running on windows, and the Mac OS X 10.3 server setup and running the same. It took a few different packages, and a server re-install, but we are chuggin along like the old chicago line all the same. This setup is so much cleaner, and nicer to work with than before. IMHO. It also means I could do development in Linux if I wanted, as the tools are all OS and freely available. I did notice that apache released a windows installer for 2.0.53 this last week. When I went it was still unavailable, therefore I still have Apache 1.3.33 on my machine. Not to worry ... it works just fine and danady.
Mik and I spent some considerable time setting up a CVS repository. Every seems to be clicking into how it works, and everything is going smoothly. We even tried out Tortoise CVS for windows ... which is pretty cool BTW. http://www.tortoisecvs.org. I still install the win32 binary from http://cvshome.org myself. I'm an old school command line junkie. :-D I configured CVSSPam which is a set of ruby scripts to send out retty CVS commit log emails. I works quite well, and Chris Lang just asked me to take him off the list, as he got 44 CVS commit emails today. That's what you get for having busy developers I guess. ;-)
As for my time in Victoria this round ... it was wonderful. Beautiful weather and I got a lot of cool things done. Marshall and the girls have had the misfortune recently of having _two_ VCRs and a DVD player that all do not work. That makes it kind of hard to watch the taped episode of 24 from Monday night. I went out and the good soul that I am bought them a Phillips DVD / VCR combo player. Needless to say it is a _HUGE_ hit. They love me. Marshall has almost finished building the new room in the basement. He did a smashing job on it. Owen helped with the Dry Wall, and Johnny is painting today. So it was a group effort. I even mudded a little bit last night. Lets just say I should stick to programming. drywalling really isn;'t my forte. ;-)
Cheers,
See you back in Calgary! ΒΆ 10:04:00 PM
I am not completely free of the ball and chain of SQL Server, but the closer I get to that bright light of a real RDBMS, the more excited I get. I had never experienced importing / exporting to and from MS-SQL prior to this week. I wasn't worried per se, but it was unfamiliar to me. I am quite familiar with the workings of this now.
I have installed PostgreSQL 8.0.1 on my Windows machine. This happens to install the OLE DB provider as well (nice). So now I have the option of exporting directly to a PG database. It was quite easy to setup the DSN for my local machine and start chugging away. There are a couple of tricks to the DSN. By default bools are represented as char fields, you can simply uncheck this option. You can also set some other configuration options regarding bigints, text datatypes etc.
The ODBC provider is not the problem area. Once again due to the non standard MS way of doing things it causes problems. "uniqueidentifier" columns. You may as well forget about it. You can transform them into text datatypes, but you'll be pooched after that. There is apparently a package kicking around that adds "uniqueidentifier" as a type to postgres. It relies on the libuuid library, which isn't available on windows. Why one would want that datatype, other than for a slow conversion is beyond me. Sequences are available and 10^23142341 better than a global unique binary value.
Another pitfall when exporting is the fact the MS-SQL handles case sensitivity in a non standard way. According to the standards SQL is case insensitive. PG handles fields, and tables inside of double quotes to be case sensitive, but the basic useage converts to all lower case. I believe the spec states to handle all uppercase. I'm not sure about that. Regardless ... this causes problems on the table data movement because a column named someID would have been created as someid. But when the data is moved from SQL it's coming from someID and into someID ... which isn't available. You basically have to go slow, and transform all of your column names to be lowercase for table creation.
The other option is to re-create the schema first, and then just append the data into an existing table. From what I have read on the internet this provides much greater results. You can transform data, and shift columns as needed. This may be the way to go when you are moving more critical data. Since my schemas are rather small, and the system is in develeopment, I just dumped the SQL scripts, and removed all of the MS-SQL crap in them (like square brackets [], and dbo designations, and specifying COLLATE blah blah, Latin1_ blah blah for var chars etc.). I think this helps you realize how lacking in features, and datatypes other DBs are compared to PostgreSQL. Not Boolean, numerous date / time types, Text, and a heap of others.
<time days="6" / >
That was me being funny with XML. Alright ... not so funny. I wrote the first half of this in a draft six days ago. Since I'm leaving Sooke to head back to Calgary tomorrow or the next, I decided to sum up my week here before I check out.
I continued on with my PostgeSQL fun this week. I must say that my research was indeed correct, in that loading your schemas prior to data export yielded a much greater success ratio. It was so much easier to just append some conformed data onto an existing table. You could drop out coplumns as needed, and things seem to go smoothly.
Everyone else seems to be really getting on board with the PG niftyness. It just goes to show you how much people who have worked with bad tools, appreciate the good ones. They'll never go back now. buwhahahahahaha!
We have the developers set up with Apache, PHP & PostgeSQL all running on windows, and the Mac OS X 10.3 server setup and running the same. It took a few different packages, and a server re-install, but we are chuggin along like the old chicago line all the same. This setup is so much cleaner, and nicer to work with than before. IMHO. It also means I could do development in Linux if I wanted, as the tools are all OS and freely available. I did notice that apache released a windows installer for 2.0.53 this last week. When I went it was still unavailable, therefore I still have Apache 1.3.33 on my machine. Not to worry ... it works just fine and danady.
Mik and I spent some considerable time setting up a CVS repository. Every seems to be clicking into how it works, and everything is going smoothly. We even tried out Tortoise CVS for windows ... which is pretty cool BTW. http://www.tortoisecvs.org. I still install the win32 binary from http://cvshome.org myself. I'm an old school command line junkie. :-D I configured CVSSPam which is a set of ruby scripts to send out retty CVS commit log emails. I works quite well, and Chris Lang just asked me to take him off the list, as he got 44 CVS commit emails today. That's what you get for having busy developers I guess. ;-)
As for my time in Victoria this round ... it was wonderful. Beautiful weather and I got a lot of cool things done. Marshall and the girls have had the misfortune recently of having _two_ VCRs and a DVD player that all do not work. That makes it kind of hard to watch the taped episode of 24 from Monday night. I went out and the good soul that I am bought them a Phillips DVD / VCR combo player. Needless to say it is a _HUGE_ hit. They love me. Marshall has almost finished building the new room in the basement. He did a smashing job on it. Owen helped with the Dry Wall, and Johnny is painting today. So it was a group effort. I even mudded a little bit last night. Lets just say I should stick to programming. drywalling really isn;'t my forte. ;-)
Cheers,
See you back in Calgary! ΒΆ 10:04:00 PM
