mono/web/postgresql

* Status of the PostgreSQL ADO.NET Provider

	<p> Still exists in System.Data.SqlClient and needs to be moved to Mono.Data.PostgreSQL.

	<p>What follows below is Status information for the PostgreSQL ADO.NET provider.
		
	<p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
	DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.  
	
	<p>We can execute multiple queries and do a NextResult() in SqlDataReader()
	to get the next result set.  
	
	<p>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.  
	
	<p>We are also able to retrieve data with a simple SELECT SQL query 
	using ExecuteReader() which returns a SqlDataReader.  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.  
	
	<p>Here is a sample of code that is based on PostgresTest.cs and
	TestSqlDataReader.cs tests:
<pre>
 
 static void SelectData (IDbConnection cnc) {
	
	IDbCommand selectCommand = cnc.CreateCommand();
	IDataReader reader;

	selectCommand.CommandType = CommandType.Text;
	selectCommand.CommandText = 
		"select * from pg_user;" + 
		"select * from pg_tables;" + 
		"select * from pg_database";

	reader = selectCommand.ExecuteReader ();

	do {
		// get the DataTable that holds
		// the schema
		DataTable dt = rdr.GetSchemaTable();

		if(rdr.RecordsAffected != -1) {
			// Results for 
			// SQL INSERT, UPDATE, DELETE Commands 
			// have RecordsAffected >= 0
			Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);
		}
		else if (dt == null)
				Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);
		else {
			// Results for
			// SQL not INSERT, UPDATE, nor DELETE
			// have RecordsAffected = -1
			Console.WriteLine("Result is from a SQL SELECT Query.  Records Affected: " + rdr.RecordsAffected);
		
			// Results for a SQL Command (CREATE TABLE, SET, etc)
			// will have a null reference returned from GetSchemaTable()
			// 
			// Results for a SQL SELECT Query
			// will have a DataTable returned from GetSchemaTable()

			results++;
			Console.WriteLine("Result Set " + results + "...");
                        			
			// number of columns in the table
			Console.WriteLine("   Total Columns: " +
				dt.Columns.Count);

			// display the schema
			foreach (DataRow schemaRow in dt.Rows) {
				foreach (DataColumn schemaCol in dt.Columns)
					Console.WriteLine(schemaCol.ColumnName + 
						" = " + 
						schemaRow[schemaCol]);
				Console.WriteLine();
			}

			int nRows = 0;
			string output, metadataValue, dataValue;
			// Read and display the rows
			Console.WriteLine("Gonna do a Read() now...");
			while(rdr.Read()) {
				Console.WriteLine("   Row " + nRows + ": ");
					
				for(c = 0; c < rdr.FieldCount; c++) {
					// column meta data 
					DataRow dr = dt.Rows[c];
					metadataValue = 
						"    Col " + 
						c + ": " + 
						dr["ColumnName"];
						
					// column data
					if(rdr.IsDBNull(c) == true)
						dataValue = " is NULL";
					else
						dataValue = 
							": " + 
							rdr.GetValue(c);
				
					// display column meta data and data
					output = metadataValue + dataValue;					
					Console.WriteLine(output);
				}
				nRows++;
			}
			Console.WriteLine("   Total Rows: " + 
				nRows);
		}	
	} while(rdr.NextResult());
	Console.WriteLine("Total Result sets: " + results);
			
	rdr.Close();
 }

</pre>
	
	<p>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.
	
	<p>Rows that are returned which contain columns that are NULL are handled now. 
	The SqlDataReader method IsDBNull() needs to be called to determine 
	if a field IS NULL before trying to read data from that field.
	
	<p>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.  
	
	<p>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.
	
	<p>ExecuteScalar() is a lightweight method in class SqlCommand that only returns
	one row and one column as one object - even if there is more than row or column.
	
<pre>
 static string GetDatabaseServerVersion (SqlConnection cnc) 
 {			 
	SqlCommand cmd = cnc.CreateCommand ();
	string data;

	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = "version";
							
	data = (string) cmd.ExecuteScalar ();

	return data;
 }
</pre>
			
	<p>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.
	
	<p>A lot of functionality in System.Data is missing, but the 
	infrastructure is starting to come together.
	
	<p>A lot of Exceptions need to be thrown for various exceptions.  However,
	SqlException, SqlErrorCollection, and SqlError have been partially
	implemented.
	
	<p>Tim Coleman and Rodrigo Moya got the beginnings of the
	SqlDataAdapter/DataSet/DataTable/DataRow to work.  Currently, 
	the SqlDataAdapter 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.
	Below, I show a snippets from the test:
	
<pre>
 string connectionString;
 string sqlQuery;
 SqlDataAdapter adapter;
 DataSet dataSet = null;

 connectionString =
	"host=localhost;" +
	"dbname=test;" +
	"user=postgres";
						
 sqlQuery = "select * from pg_tables";

 adapter = new SqlDataAdapter (sqlQuery, 
			connectionString);
 				
 dataSet = new DataSet ();

 adapter.Fill (dataSet);
			
 if (dataSet != null) {
	foreach (DataRow row in dataSet.Tables["Table"].Rows)
		Console.WriteLine("tablename: " + row["tablename"]);
 }
</pre>	

* Testing the PostgreSQL ADO.NET Provider

	<p>In order to test System.Data.SqlClient, you will need to have
	access to a remote PostgreSQL DBMS, or you will have to install 
	one locally.  PostgreSQL is the DBMS used for the initial 
	implementation of System.Data.SqlClient.
		
	<p>Why?  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>
"host=localhost;dbname=test;user=postgres"
</pre>
		
	<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/OBSOLETE/V1.1/cygipc/index.html">CygIPC</a> package.
			  			  
				<p>The cygipc package contains the ipc-daemon 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 PostgreSQL test program named
	PostgreTest.cs.  Thanks goes to Gonzalo for creating the original
	PostgreSQL test.
	
	<p>To use it to test System.Data, you
	modify the file to your PostgreSQL database
	connection requirements:

	<p>
	<ul>
		<li><b>dbname</b> database, ie., test</li>
		<li><b>host</b> hostname of the PostgreSQL DBMS Server to connect to, ie., localhost</li>
		<li><b>user</b> username, ie., someuser</li>
		<li><b>password</b> password, ie., mypass1234</li>
	</ul>
	
	<p>The connection string is in OLE-DB connection string format.  Internally,
	SqlConnection converts this to the PostgreSQL connection string format.
	
	<p>
<pre>
    OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
</pre>

	<p>
	Note that OLE-DB includes the semicolons while PostgreSQL's connection
	string does not.

	<p>
    To compile the PostgresTest.cs program, do:
    
    <p>
<pre>
mcs PostgresTest.cs -r System.Data.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>Below, I show how the output from PostgresTest.  I have omitted a lot
    of the meta data for the columns except two columns.  The classes
    used were from System.Data.SqlClient and were used to connect to a
    PostgreSQL database and retrieve data.

<p>
<pre>   

danmorg@DANPC ~/mono/mcs/class/System.Data/Test
$ mcs PostgresTest.cs -r System.Data.dll

danmorg@DANPC ~/mono/mcs/class/System.Data/Test
$ mono PostgresTest.exe
        Postgres provider specific tests...

                Drop table:
Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:  table "mono
_postgres_test" does not exist
 <Stack Trace>

                Create table with all supported types:
OK
                Insert values for all known types:
OK
                Update values:
OK
                Insert values for all known types:
OK
Aggregate: count(*)
Agg Result: 2
Aggregate: min(text_value)
Agg Result: This is a text
Aggregate: max(int4_value)
Agg Result: 1048000
Aggregate: sum(int4_value)
Agg Result: 1048003
                Select values from the database:
Result is from a SELECT SQL Query.  Records Affected: -1
Result Set 1...
   Total Columns: 28
ColumnName = boolean_value
ColumnOrdinal = 1
ColumnSize = 1
NumericPrecision = 0
NumericScale = 0
IsUnique = False
IsKey =
BaseCatalogName =
BaseColumnName = boolean_value
BaseSchemaName =
BaseTableName =
DataType = System.Boolean
AllowDBNull = False
ProviderType = 16
IsAliased = False
IsExpression = False
IsIdentity = False
IsAutoIncrement = False
IsRowVersion = False
IsHidden = False
IsLong = False
IsReadOnly = False

 ...

 ColumnName = null_timestamp_value
 ColumnOrdinal = 28
 ColumnSize = 8
 NumericPrecision = 0
 NumericScale = 0
 IsUnique = False
 IsKey =
 BaseCatalogName =
 BaseColumnName = null_timestamp_value
 BaseSchemaName =
 BaseTableName =
 DataType = System.DateTime
 AllowDBNull = False
 ProviderType = 1184
 IsAliased = False
 IsExpression = False
 IsIdentity = False
 IsAutoIncrement = False
 IsRowVersion = False
 IsHidden = False
 IsLong = False
 IsReadOnly = False

 Gonna do a Read() now...
   Row 0:
    Col 0: boolean_value: False
    Col 1: int2_value: 5
    Col 2: int4_value: 3
    Col 3: bigint_value: 9
    Col 4: float_value: 3.141590
    Col 5: double_value: 3.14159
    Col 6: numeric_value: 123456789012.345
    Col 7: char_value: Mono.Data!
    Col 8: varchar_value: It was not me!
    Col 9: text_value: We got data!
    Col 10: point_value: (1,0)
    Col 11: time_value: 01/01/1 21:13:14
    Col 12: date_value: 02/29/2000 00:00:00
    Col 13: timestamp_value: 02/29/2004 14:00:11
    Col 14: null_boolean_value is NULL
    Col 15: null_int2_value is NULL
    Col 16: null_int4_value is NULL
    Col 17: null_bigint_value is NULL
    Col 18: null_float_value is NULL
    Col 19: null_double_value is NULL
    Col 20: null_numeric_value is NULL
    Col 21: null_char_value is NULL
    Col 22: null_varchar_value is NULL
    Col 23: null_text_value is NULL
    Col 24: null_point_value is NULL
    Col 25: null_time_value is NULL
    Col 26: null_date_value is NULL
    Col 27: null_timestamp_value is NULL
   Row 1:
    Col 0: boolean_value: True
    Col 1: int2_value: -22
    Col 2: int4_value: 1048000
    Col 3: bigint_value: 123456789012345
    Col 4: float_value: 3.141590
    Col 5: double_value: 3.14159
    Col 6: numeric_value: 123456789012.345
    Col 7: char_value: This is a char
    Col 8: varchar_value: This is a varchar
    Col 9: text_value: This is a text
    Col 10: point_value: (1,0)
    Col 11: time_value: 01/01/1 21:13:14
    Col 12: date_value: 02/29/2000 00:00:00
    Col 13: timestamp_value: 02/29/2004 14:00:11
    Col 14: null_boolean_value is NULL
    Col 15: null_int2_value is NULL
    Col 16: null_int4_value is NULL
    Col 17: null_bigint_value is NULL
    Col 18: null_float_value is NULL
    Col 19: null_double_value is NULL
    Col 20: null_numeric_value is NULL
    Col 21: null_char_value is NULL
    Col 22: null_varchar_value is NULL
    Col 23: null_text_value is NULL
    Col 24: null_point_value is NULL
    Col 25: null_time_value is NULL
    Col 26: null_date_value is NULL
    Col 27: null_timestamp_value is NULL
   Total Rows Retrieved: 2
 Total Result sets: 1
                Call ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE
 ).
 Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: -1
 Total Result sets: 0
                Call ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE)
 .
 Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: 1
 Total Result sets: 0
                Calling stored procedure version()
 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.9
 5.3-5
 Clean up...
                Drop table...
 OK
 RESULT: 0

</pre>