small fixes

[mono.git] / web / postgresql

* PostgreSQL Data Provider

 There are two ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a> in Mono:

<ul>

        <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
                <ul>
                        <li>a .NET Managed Data Provider for PostgreSQL
                        
                        <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.PostgreSQL (deprecated)
                <ul>
                        <li>Deprecated in favor of Npgsql</li>
                        
                        <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql</li>
        
                        <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
                                client/server database management system.</li>
        
                        <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
                                and libpq.so on Linux.</li>
                </ul>
        </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>

 Below, see separate Testing sections for Npgsql and Mono.Data.PostgreSqlClient.

** 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 \r
                                through NpgsqlCommand.ExecuteNonQuery() method.</li>\r
                        <li>You can send queries like, select count(*) from table, select version()\r
                                with NpgsqlCommand.ExecuteScalar() method.</li>\r
                        <li>There is logging support. (Thanks Dave Page)\r
                                To use it, place code like that in your program:</li>\r
\r
<pre>      \r
      // Enable logging.\r
          NpgsqlEventLog.Level = LogLevel.Debug;            // LogLevel.\r
          NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";   // LogFile.\r
</pre>\r
    \r
                        <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>\r
                        <li>There is a winforms test suite (Thanks Dave Page).</li>\r
                        <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>\r
                        <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>\r
                        <li>Thanks Ulrich Sprick for all discussion and ideas.</li>\r

                </ul>
        </li>
        <li>Mono.Data.PostgreSQL status
        <ul>
        
        <li>Deprecated in favor of Npgsql</li>
        
        <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
        DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.</li>
        
        <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
        to get the next result set.</li>
        
        <li>We are also able to do simple aggregate functions, 
        ie, count(), sum(), min(), and max() 
        in a simple SELECT SQL query using the ExecuteScalar() now.</li>
        
        <li>We are also able to retrieve data with a simple SELECT SQL query 
        using ExecuteReader() which returns a PgSqlDataReader.  We are able to
        use GetSchemaTable() to get the meta data about the table columns.  
        We are able     to Read() to get each row from the result set.</li>
        
        <li>We are able to get 
        String data (char, character, text, varchar), Int16 (smallint),
        Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
        Boolean (boolean), Single (float), and Double (double).
        More data types will come later.  Note, the types that do work still 
        need thorough testing.</li>
        
        <li>Rows that are returned which contain columns that are NULL are handled now. 
        The PgSqlDataReader method IsDBNull() needs to be called to determine 
        if a field IS NULL before trying to read data from that field.</li>
        
        <li>Calling PostgreSQL stored procedures works.  It does not work perfectly.  
        It may not
        even work to specification - yet.  If you want to test it yourself, look at
        TestSqlDataReader.cs or PostgresTest.cs in
        mcs/class/System.Data/Test.</li>
        
        <li>Below, I have some sample code you can
        use to call a PostgreSQL stored procedure named "version".  This stored
        procedure returns a string containing the PostgreSQL server version.  Notice
        the CommandType is StoredProcedure and the method ExecuteScalar() is called.</li>
        
        <li>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
        one row and one column as one object - even if there is more than row or column.</li>
                        
        <li>We have the beginnings of Parameters support PostgreSQL.  Only
        Input Parameters are currently supported.  Output, Input/Output,
        and Return parameters still need to be done.</li>
        
        <li>A lot of Exceptions need to be thrown for various exceptions.  However,
        PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
        implemented.</li>
        
        <li>Tim Coleman and Rodrigo Moya got the beginnings of the
        PgSqlDataAdapter/DataSet/DataTable/DataRow to work.  Currently, 
        the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
        See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.</li>
        
        <li>Works in the SQL# command-line version
                        and the GTK# version on Linux.  It only works in SQL# command-line version
                        on Windows.</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>Mono.Data.PostgreSqlClient even though deprecated can still 
                accept bug fixes. This is because other areas, such as, ASP.NET examples
                may still use this provider.</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 replaced Mono.Data.PostgreSqlClient as the provider of
                choice to use.  However, Mono.Data.PostgreSqlClient will remain in a
                deprecated state until nobody uses it anymore - then it can be removed</li>
                
                <li>Implement more of PostgreSQL 7.3 features in Npgsql</li>
        </ul>

** Testing Mono.Data.PostgreSqlClient

        <ul>
                * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
        access to a remote PostgreSQL DBMS, or you will have to install 
        one locally.  PostgreSQL was the first ADO.NET provider created in Mono.
                
        <p>Why use PostgreSQL?  Because it is free software, has a client 
        library that is easy to use, PostgreSQL is easy to install on
        Unix and Windows (using the Cygwin install program), not difficult to setup after
        installation, and it runs under: Linux, 
        Windows (via cygwin and ipc-daemon), Unix, and
        others.  This allowed us to create the
        System.Data functionality in Mono much quicker.
                
        <p>If you plan on using a remote PostgreSQL DBMS Server,
        than you will need to have the PostgreSQL client software on your
        local computer that includes libpq.so (pq.dll on Windows).
        
        <p>The System.Data tests use this connection string to connect
        to the PostgreSQL database named "test" at host "localhost" as
        user "postgres".

<pre>
"Server=localhost;Database=test;User ID=postgres;Password=fun2db"
      (or)
"host=localhost;dbname=test;user=postgres;password=fun2db"
</pre>
</ul>
                
        <p>Installation instructions for PostgreSQL DBMS:

        <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>
        
        <p>In the path mcs/class/System.Data/Test
        there is a test for Mono.Data.PostgreSqlClient named
        PostgreTest.cs.  Thanks goes to Gonzalo for creating the original
        PostgreSQL test.

        <p>
    To compile the PostgresTest.cs program, do:
    
    <p>
<pre>
 mcs PostgresTest.cs \
    -r System.Data.dll \
    -r Mono.Data.PostgreSqlClient.dll
</pre>

        <p>If there are compile errors, such as, can not convert IDbConnection
        to PgSqlConnection, then you need to run mcs like:

<pre>
 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
    PostgresTest.cs \
    -r System.Data.dll \
    -r Mono.Data.PostgreSqlClient.dll
</pre>
    
    <p>
    To run using mint, do:
    
    <p>
<pre>
mint PostgresTest.exe
</pre>
    
    <p>
    To run using mono, do:
<pre>
mono PostgresTest.exe
</pre>  

        <p>C# Example for Mono.Data.PostgreSqlClient:
<pre>
 using System;
 using System.Data;
 using Mono.Data.PostgreSqlClient;
 
 public class Test 
 {
    public static void Main(string[] args)
    {
       string connectionString = 
          "Server=localhost;" +
          "Database=test;" +
          "User ID=postgres;" +
          "Password=fun2db;";
       IDbConnection dbcon;
       dbcon = new PgConnection(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 = reader["firstname"];
            string LastName = 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>
                <li>Build on Linux:
<pre>
        mcs TestExample.cs -r System.Data.dll \
            -r Mono.Data.PostgreSqlClient.dll
</pre>
                </li>
                <li>Build on Windows via Cygwin:
<pre>
        mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
             TestExample.cs \
             -lib:C:/cygwin/home/MyHome/mono/install/lib \
             -r System.Data.dll -r Mono.Data.PostgreSqlClient.dll
</pre>
                </li>
        </ul>
        </li>
        <li>Running the Example:
<pre>
mono TestExample.exe
</pre>
</li>
</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>Read the Testing notes for Mono.Data.PostgreSqlClient too
        
        <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.Open();
       dbcon = new NpgsqlConnection(connectionString);
       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 = reader["firstname"];
            string LastName = 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>
                <li>Build on Linux:
<pre>
        mcs TestExample.cs -r System.Data.dll \
            -r Npgsql.dll
</pre>
                </li>
                <li>Build on Windows via Cygwin:
<pre>
        mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
             TestExample.cs \
             -lib:C:/cygwin/home/MyHome/mono/install/lib \
             -r System.Data.dll -r Npgsql.dll
</pre>
                </li>
        </ul>
        </li>
        <li>Running the Example:
<pre>
mono TestExample.exe
</pre>
</li>
</ul>