*** empty log message ***

svn path=/trunk/mono/; revision=8128

1 files changed, 616 insertions, 0 deletions

diff --git a/web/postgresql b/web/postgresql
new file mode 100644
index 00000000000..9c857baa15e
--- /dev/null
+++ b/web/postgresql
@@ -0,0 +1,616 @@
+* 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>

+