diff options
author | Daniel Morgan <monodanmorg@yahoo.com> | 2002-10-10 08:07:26 +0400 |
---|---|---|
committer | Daniel Morgan <monodanmorg@yahoo.com> | 2002-10-10 08:07:26 +0400 |
commit | 9f4c90c68d7053960b42743719f84af6b40b928e (patch) | |
tree | 0bf068496a8c566f8c171d3c382406a804f41e6e /web/postgresql | |
parent | 5f70f7b839a0db01690cc119fdfd25a9daa47434 (diff) |
*** empty log message ***
svn path=/trunk/mono/; revision=8128
Diffstat (limited to 'web/postgresql')
-rw-r--r-- | web/postgresql | 616 |
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>
+
|