path: root/web/postgresql

* PostgreSQL and Mono

  When it comes to Mono and PostgreSQL, there are many ways
  you can access your data.

* Data Providers

 There are many ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a>:
 
 There are two providers created specifically for PostgreSQL included with Mono:

<ul>

	<li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
		<ul>
			<li>included with Mono</li>
			
			<li>a .NET Managed Data Provider for PostgreSQL</li>
			
			<li>Written in 100% C#</li>
			
			<li>does not require a client library</li>
			
			<li>works on Mono and Microsoft .NET</li>
			
			<li>created by Francisco Figueiredo jr. and has many developers working on it
			
			<li>works in the SQL# (command-line and GTK# GUI versions)</li>
			
			<li>in namespace Npgsql and assembly Npgsql and is found in mcs
			at mcs/class/Npgsql</li>
		</ul>
	</li>
	
	<li>Mono.Data.PostgreSqlClient (DEPRECATED)
		<ul>
	
			<li>Deprecated in favor of Npgsql.  No longer included in a release of Mono.</li>
			
		</ul>
	</li>
	
	<li><a href="http://sourceforge.net/projects/mysqlnet/">ByteFX.Data</a> has a provider for PostgreSQL too, but I do not know how well it works with Mono.</li>
	
	<li>There is another .NET data provider for PostgreSQL named <a href="http://sourceforge.net/projects/pgsqlclient/">PgSqlClient</a>, but I do not know if it works with Mono.</li>
	
	<li>If none of the above providers meet your needs.  There is the ODBC and OLEDB providers included with Mono.</li>
			
	<li>Bugs with Mono or the data provider should be reported 
	in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>.  If you
	do not have Bugzilla user account, it is free 
	and easy to 
	create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>


</ul>

** Current Status

<ul>
	<li>Npgsql
		<ul>
			<li>Builds and Runs on both Microsoft .NET and Mono.</li>
			<li>Works using SQL# (command-line and GTK# versions)</li>
			<li>You can send insert, update, delete queries 
				through NpgsqlCommand.ExecuteNonQuery() method.</li>
			<li>You can send queries like, select count(*) from table, select version()
				with NpgsqlCommand.ExecuteScalar() method.</li>
			<li>There is logging support. (Thanks Dave Page)
				To use it, place code like that in your program:</li>

<pre>      
      // Enable logging.
	  NpgsqlEventLog.Level = LogLevel.Debug;            // LogLevel.
	  NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";   // LogFile.
</pre>
    
			<li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>
			<li>There is a winforms test suite (Thanks Dave Page).</li>
			<li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>
			<li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>
			<li>Thanks Ulrich Sprick for all discussion and ideas.</li>

		</ul>
	</li>
</ul>

** Action Plan
	<ul>
		<li>More testing and fixing bugs</li>
		
		<li>Better error handling</li>
		
		<li>More Data Types to use</li>
		
		<li>Any features for Npgsql should be implemented in Npgsql's main cvs repository at
		gborg.postgresql.org.  Most bugs should be fixed in gborg.postgresql.org's cvs.
		Only bugs neccessary for building and running of Npgsql	on Mono can be done in Mono cvs, 
		but once applied they should be sent to Npgsql's mailing list
		at gborg.postgresql.org for inclusion into cvs there.  Whenever there is 
		a release of Npgsql (determined by Francisco Figueiredo jr. or a release
		of Mono (determined by Miguel de Icaza), then the Npgsql source 
		in gborg.postgresql.org's cvs will be used to update the Npgsql source in
		Mono's cvs. 
		</li>
	
		<li>Add any missing functionality to Npgsql. If this funtionality works on
		.NET but not on Mono, implement the missing features or fix the bugs in Mono</li>

		<li>Npgsql has been replaced Mono.Data.PostgreSqlClient as the provider of
		choice to use.  Mono.Data.PostgreSqlClient is deprecated and is no longer included in
		Mono releases.  Please use Npgsql for PostgreSQL data access.</li>
		
		<li>Implement new features of PostgreSQL.</li>
	</ul>

</ul>

** Testing Npgsql

<ul>
	<li>Have a working mono and mcs</li>
		
	<li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
	and make sure the binary assembly Npgsql.dll is installed in the same place that the
	mono class libraries are located.
	
	<li>C# Example for Npgsql:
<pre>
 using System;
 using System.Data;
 using Npgsql;
 
 public class Test 
 {
    public static void Main(string[] args)
    {
       string connectionString = 
          "Server=localhost;" +
          "Database=test;" +
          "User ID=postgres;" +
          "Password=fun2db;";
       IDbConnection dbcon;
       dbcon = new NpgsqlConnection(connectionString);
       dbcon.Open();
       IDbCommand dbcmd = dbcon.CreateCommand();
       // requires a table to be created named employee
       // with columns firstname and lastname
       // such as,
       //        CREATE TABLE employee (
       //           firstname varchar(32),
       //           lastname varchar(32));
       string sql = 
           "SELECT firstname, lastname " +
           "FROM employee";
       dbcmd.CommandText = sql;
       IDataReader reader = dbcmd.ExecuteReader();
       while(reader.Read()) {
            string FirstName = (string) reader["firstname"];
            string LastName = (string) reader["lastname"];
            Console.WriteLine("Name: " + 
                 FirstName + " " + LastName);
       }
       // clean up
       reader.Close();
       reader = null;
       dbcmd.Dispose();
       dbcmd = null;
       dbcon.Close();
       dbcon = null;
    }
 }
</pre>
	</li>
	<li>Building C# Example:
	<ul>
		<li>Save the example to a file, such as, TestExample.cs</li>
<pre>
	mcs TestExample.cs -r System.Data.dll \
	    -r Npgsql.dll
</pre>
	</ul>
	</li>
	<li>Running the Example:
<pre>
mono TestExample.exe
</pre>
</li>
</ul>

		
** Installation instructions for PostgreSQL DBMS:
	<p><b>On Unix</b>

	<ul>
		* Read the PostgreSQL Installation Instructions 
		at \usr\doc\postgresql-x.x.x\html\installation.html
		
		* Depending on your Unix system, 
		PostgreSQL maybe already installed, a database user 'postgres' created, 
		a linux user 'postgres' created and initdb ran.  Or maybe not.

<pre>
 su
 adduser postgres
 mkdir /usr/local/pgsql/data
 chown postgres /usr/local/pgsql/data
 su - postgres
 initdb -D /usr/local/pgsql/data
 postmaster -i -D /usr/local/pgsql/data
 createdb test
 psql test
</pre>
	
		* Make sure you have a database user named postgres.  It is best to install
		the PostgreSQL DBMS under linux user postgres.  When you run the postmaster,
		run it under the user postgres as well.  If this was not done, then you
		will need to create a user named postgres for the System.Data tests.

		* If you already installed PostgeSQL and you do not have a database
		user named postgres, then you can create user postgres using psql:
		
<pre>		
psql test
create user postgres with password 'fun2db';
</pre>
				
		* The postmaster must be run with -i option.
		
		* In the /usr/local/pgsql/data/pg_hba.conf file, you need
		to have the AUTH_TYPE set to md5.  You can read more on this at
		/usr/doc/postgresql-7.2.1/html/client-authentication.html 
		or wherever your
		PostgreSQL html docs are located.  See the 2nd line below,
		host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
		
<pre>
 # TYPE     DATABASE    IP_ADDRESS    MASK               AUTH_TYPE

 local      all                                          trust
 host       all         127.0.0.1     255.255.255.255    md5
</pre>

	* If you can not find your PostgreSQL documentation locally or you 
	did not install it, then you 
	can get it <a href="http://www.postgresql.org/idocs/">here</a>.

	</ul>

	<b>On Windows</b>

	<ul>
		* Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to 
		  install the PostgreSQL DBMS.  It is
		  found in the database category.
		  
		* <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read 
		  the requirements to install PostgreSQL.  Those requirements
		  are included with cygwin except cygipc.  A default installtion
		  of cygwin does not install everything you will need, so on the 
		  safe side, just include everything when installing cygwin.
		
		* <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
		
		* <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
		  read the file FAQ_MSWIN which is available 
		  in /usr/doc/postgres-x.x 
				  
		* <p>Important notes from this file are:
		  
		  <ul>
				<p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html">CygIPC</a> package.
				Cygwin includes a utility bunzip2 which can be used to unzip it.  Now, change to 
				the root directory by 
				typing "cd /" then 
				you can use "tar xvf cygipc.xxx.tar" to untar it 
				in the root directory in cygwin.
			  			  
				<p>The cygipc package contains the support to run ipc-daemon 
				that you will need 
				to run before you can
				run the PostgreSQL DBMS Server daemon (postmaster) or run
				initdb which initializes the PostgreSQL database.
			  
				<p><b>3.</b>  The Cygwin bin directory has to be placed in 
				the path before the Windows program directories, 
				for example, C:\cygwin\bin 
			  
				<p><b>My own note.</b>  In the Windows control panel, I set
				the environment variables PATH to my cygwin /usr/local/bin,
				/usr/bin, and /bin.  I also set my LD_LIBRARY_PATH to 
				/usr/local/lib and /usr/lib.  For example:
			  
				<p>
<pre>
PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
</pre>
							  
				<p><b>4.</b> Start the ipc-daemon that came with the cygipc 
				package.  There
				are two ways to do this: run it from the command line as:
			  
				<p>
<pre>
ipc-daemon &
</pre> 			  
				<p>or you can set it up as a Windows service.  See the 
				file cygrunsrv.README at /usr/doc/Cygwin on how to do this
				for ipc-daemon and postmaster.  Note the
				troubleshooting section at the end of 
				the cygrunsrv.README file.
			  
				<p>To install ipc-daemon as a service, 
				you just have to run
			  
				<p>
<pre>
ipc-daemon --install-as-service' (--remove-as-service) 
</pre>
			  
				<p>and then run
			  
<pre>
net start ipc-daemon
</pre>
			</ul>
			  
			<p>Read the installation.html file 
			at /usr/doc/postgresql-x.x/html/installation.html
		
			<p>You will see in this file that you will need to 
			run the following commands:
		  
			<p>
<pre>
mkdir /usr/local/pgsql/data
initdb -D /usr/local/pgsql/data
postmaster -D /usr/local/pgsql/data
createdb test
psql test  		
</pre>
		  
			<p>When you need to connect to the database, 
			you will need ipc-daemon and postmaster running.  Start ipc-daemon
			before any of the command above.  If you restart your computer, you
			need to start ipc-daemon and postmaster either manually or as a 
			service.
		  
			<p>psql is a command-line PostgreSQL client tool to 
			enter and run SQL commands and queries.
		  
			<p>If there is no database user named postgres, create a user named
			postgres with the following SQL command in the client tool psql:
		  
			<p>
<pre>
psql test
create user postgres with password 'fun2db';
</pre>
			<p>The only reason I say this is so you can easily use the System.Data tests
			without having to change the database, userid, etc.
	</ul>