Using codesmith (5.1) with postgresql for NHibernate

You could say that this post is a modification of (http://www.codesmithtools.com/video/nhibernate.html) when you don't have working visual studio integration and/or you want to use codesmith with Postgresql+NHibernate
This is how to use Codesmith professional version 5.1 without visual studio with Postgresql to generate nhibernate templates.

• First download the Npgsql (version Npgsql2.0.2-bin-ms.net.zip). Extract and copy the "Npgsql.dll" and Mono.Security.dll" files to GAC (c:\windows\assembly). Note: This version of npgsql was determined from the dll not found error emitted when your test connection in Codesmith add data source dialog. 
• Now open "CodeSmith Studio".
• In the "Schema Explorer" docking window click "Manage Data Sources". Next click "Add". 
• Name your datasource whatever you want. (hence referred to as "yourdatasource")
• Select PostgreSQLSchemaProvider.
• Put in the connection string.
• Your database connection is setup.  
• In the "template explorer" click on "create template folder shortcut" icon in the toolbar to create a shortcut to the folder you want to create the Codesmith project in. 
• right click the newly created "template folder shortcut" and select "New->Codesmith Project".
• Right click the newly create project and select "Add Output". Browsed and added the following template "C:\Users\Basiee\Documents\CodeSmith\Samples\v5.1\Templates\Frameworks\NHibernate\CSharp". Of course your username / version of codesmith / version of operating system come into play for this address. 
• Select your "Source Database" to be "yourdatasource" whatever you called it. 
• Modify your "assembly name" (required ... for your ease) as well as the namespaces (optional) to keep them consistent with the assembly name. 
• Now right click the project (in Template Explorer) and select "Generate Outputs".

Enjoy!

Best database modeling tool for PostgreSQL

First off:

• This tool is not free of cost (but not too expensive). No free tool came up the my requirements. Feel free to tell me if you know any. 
• By database I mean PostgreSQL .... of course :) 
• My requirements:
• Should connect directly to the database. With ability to reverse engineer the design from the database as well as the ability to send the design to the database. 
• Should be able to export the model to its own format and be able to generate a change script between two files generated from the same script. This will allow a database + file pair for each revision of the software where the table structure changes. 
• Should provide options for setting up naming conventions. 

I will not go into all the softwares I tried ... since to be honest I gave each software's trial version a limited run. But one came out on top as a CLEAR winner : 

ModelRight Professional 3.5

One note though. To get it to connect to PostgreSQL i needed to add the "bin" folder of my PostgreSQL installation in my windows "Path"

Note: I didn't run the windows installer for postgreSQL ... i used the method here . The postgreSQL windows installer might do this but I haven't verified.

ModelRight suggest that I could have also just copied "libpq.dll" to the installation folder of ModelRight (in my case C:\Program Files (x86)\ModelRight\ModelRight 3.5 Professional) but that did not work for me. 

PostgreSQL Portable

I just wanted a portable PostgreSQL (on windows) not for development but for distribution. This means that I only care about the data directory being portable.

Making a portable postgreSQL
To make the latest version portably. Heres what I had to do:

• Just downloaded the latest stable postgreSQL windows zip file (http://www.enterprisedb.com/postgresql-904-binaries-win32?ls=Crossover&type=Crossover)
• Extracted to a folder.
• Made a folder called "data" inside the "pgsql" folder and made sure current logged in user (administrator btw) had full rights to the folder.
• CDed to the pgsql\bin folder
• ran the command:
  initdb.exe -D "../data" -U postgres -W
• Type in the password you want for postgres superuser (lets say you put in password) and reenter to verify as prompted. 
• In the end of the above command I could read the following:
  Success. You can now start the database server using:
      "postgres" -D "../data"
  or
      "pg_ctl" -D "../data" -l logfile start
• All done. Now you can start the database using the following command (be sure to leave the command prompt open after this command because as soon as you close it the server will terminate):
  pg_ctl -D "../data" start
• You can shut down the database using the following command:
  pg_ctl -D "../data" stop

You can now connect to the database using pgadmn3 in the bin folder. If you do not need PostGIS support you are all set :) In case you do ... continue reading :)

Adding PostGIS support to the portable postgresql
I downloaded the latest version of postgis from here : PostGIS 1.5.2 release for PostgreSQL 9.0 for 32-bit Windows mentioned on this page.

• I extracted it so that my directory structure was as follows:

• Copy all the files from postgis to pgsql 
• Edit "makepostgisdb.bat"  as follows (the sections in bold italics are the ones I modified / added):

set PGPORT=5432

set PGHOST=localhost

set PGUSER=postgres

set PGPASSWORD=password

set THEDB=optimate

set PGBIN=.\bin\

"./bin/pg_ctl" -D "./data" stop

"./bin/pg_ctl" -D "./data" start

"%PGBIN%\psql"  -c "CREATE DATABASE %THEDB%"

"%PGBIN%\psql"  -d "%THEDB%" -c "CREATE LANGUAGE plpgsql"

"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\postgis.sql"

"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\spatial_ref_sys.sql"

"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\postgis_comments.sql"

"./bin/pg_ctl" -D "./data" stop

REM Uncomment the below line if this is a template database

REM "%PGBIN%\psql" -d "%THEDB%" -c "UPDATE pg_database SET datistemplate = true WHERE datname = '%THEDB%';GRANT ALL ON geometry_columns TO PUBLIC; GRANT ALL ON spatial_ref_sys TO PUBLIC"

pause

• Execute makepostgisdb.bat by double clicking it. Eventually you should get a screen like this:

• At this stage you have a new PostGIS enabled database (optimate) available to you!
• Finally copy the plugins.ini file from the pgsql folder to pgsql\pgAdmin III folder overriting the file already there. Now you have configured a cool Shp2pgsql-gui tool as a plugin for pgAdmin3 :)  You can verify it by running pgadmin3. You will see it in plugins as shown below:

Some useful bat files

Additionally here are some command files I use for postgreSQL (place in the main pgsql folder):

pgadmin.bat

"./bin/pgAdmin3.exe"

startserver.bat

"./bin/pg_ctl" -D "./data" start


stopserver.bat
"./bin/pg_ctl" -D "./data" stop
pause

Enjoy!
Updated on 24th June 2011 adding documentation for postgis + some useful bat files.