-* PostgreSQL Provider\r
-\r
- <p>Exists in Mono.Data.PostgreSql now.\r
-
- <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
+* PostgreSQL Data Provider
+
+<ul>
+ <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql
+
+ <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
+ client/server database management system.
+
+ <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
+ and libpq.so on Linux.
+</ul>
+
+* Current Status
+
+<ul>
+ <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.
- <p>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
+ <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
to get the next result set.
- <p>We are also able to do simple aggregate functions,
+ <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.
- <p>We are also able to retrieve data with a simple SELECT SQL query
+ <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.
- <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;" + \r
- "select * from pg_tables;" + \r
- "select * from pg_database";\r
-
- reader = selectCommand.ExecuteReader ();
-
- do {\r
- // get the DataTable that holds\r
- // the schema\r
- DataTable dt = rdr.GetSchemaTable();\r
-\r
- if(rdr.RecordsAffected != -1) {\r
- // Results for \r
- // SQL INSERT, UPDATE, DELETE Commands \r
- // have RecordsAffected >= 0\r
- Console.WriteLine("Result is from \r
- a SQL Command \r
- (INSERT,UPDATE,DELETE). \r
- Records Affected: " + \r
- rdr.RecordsAffected);\r
- }\r
- else if (dt == null)\r
- Console.WriteLine("Result is from \r
- a SQL Command \r
- not (INSERT,UPDATE,DELETE). \r
- Records Affected: " + \r
- rdr.RecordsAffected);\r
- else {\r
- // Results for\r
- // SQL not INSERT, UPDATE, nor DELETE\r
- // have RecordsAffected = -1\r
- Console.WriteLine("Result is from a \r
- SQL SELECT Query. \r
- Records Affected: " + \r
- rdr.RecordsAffected);\r
- \r
- // Results for a SQL Command \r
- (CREATE TABLE, SET, etc)\r
- // will have a null reference returned \r
- from GetSchemaTable()\r
- // \r
- // Results for a SQL SELECT Query\r
- // will have a DataTable returned \r
- from GetSchemaTable()\r
-\r
- results++;\r
- Console.WriteLine("Result Set " + \r
- results + "...");\r
- \r
- // number of columns in the table\r
- Console.WriteLine(" Total Columns: " +\r
- dt.Columns.Count);\r
-\r
- // display the schema\r
- foreach (DataRow schemaRow \r
- in dt.Rows) {\r
- foreach (DataColumn schemaCol \r
- in dt.Columns)\r
- Console.WriteLine(\r
- schemaCol.ColumnName + \r
- " = " + \r
- schemaRow[schemaCol]);\r
- Console.WriteLine();\r
- }\r
-\r
- int nRows = 0;\r
- string output, \r
- metadataValue, \r
- dataValue;\r
- // Read and display the rows\r
- Console.WriteLine("Gonna do a \r
- Read() now...");\r
- while(rdr.Read()) {\r
- Console.WriteLine(" Row " + \r
- nRows + ": ");\r
- \r
- for(c = 0; \r
- c < rdr.FieldCount; \r
- c++) {\r
- // column meta data \r
- DataRow dr = dt.Rows[c];\r
- metadataValue = \r
- " Col " + \r
- c + ": " + \r
- dr["ColumnName"];\r
- \r
- // column data\r
- if(rdr.IsDBNull(c) == true)\r
- dataValue = " is NULL";\r
- else\r
- dataValue = \r
- ": " + \r
- rdr.GetValue(c);\r
- \r
- // display column meta \r
- // data and data\r
- output = metadataValue + \r
- dataValue; \r
- Console.WriteLine(output);\r
- }\r
- nRows++;\r
- }\r
- Console.WriteLine(\r
- " Total Rows: " + \r
- nRows);\r
- } \r
- } while(rdr.NextResult());\r
- Console.WriteLine("Total Result sets: " + \r
- results);\r
- \r
- rdr.Close();\r
- }
-
-</pre>
-
- <p>We are able to get
+ <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.
- <p>Rows that are returned which contain columns that are NULL are handled now.
+ <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.
- <p>Calling PostgreSQL stored procedures works. It does not work perfectly.
+ <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.
- <p>Below, I have some sample code you can
+ <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.
- <p>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
+ <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.
-
-<pre>
- static string GetDatabaseServerVersion (PgSqlConnection cnc)
- {
- PgSqlCommand 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
+ <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.
- <p>A lot of functionality in System.Data is missing, but the
+ <li>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,
+ <li>A lot of Exceptions need to be thrown for various exceptions. However,
PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
implemented.
- <p>Tim Coleman and Rodrigo Moya got the beginnings of the
+ <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.
- Below, I show a snippets from the test:
-
-<pre>
- string connectionString;
- string sqlQuery;
- PgSqlDataAdapter adapter;
- DataSet dataSet = null;
-
- connectionString =
- "host=localhost;" +
- "dbname=test;" +
- "user=postgres";
-
- sqlQuery = "select * from pg_tables";
-
- adapter = new PgSqlDataAdapter (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>
+</ul>
* Testing the PostgreSQL Provider
a linux user 'postgres' created and initdb ran. Or maybe not.
<pre>
- su\r
- adduser postgres\r
- mkdir /usr/local/pgsql/data\r
- chown postgres /usr/local/pgsql/data\r
- su - postgres\r
- initdb -D /usr/local/pgsql/data\r
- postmaster -i -D /usr/local/pgsql/data\r
- createdb test\r
+ 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>
<p>
<pre>
-mkdir /usr/local/pgsql/data\r
-initdb -D /usr/local/pgsql/data\r
-postmaster -D /usr/local/pgsql/data\r
-createdb test\r
+mkdir /usr/local/pgsql/data
+initdb -D /usr/local/pgsql/data
+postmaster -D /usr/local/pgsql/data
+createdb test
psql test
</pre>
to PgSqlConnection, then you need to run mcs like:
<pre>
- mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \\r
- PostgresTest.cs \\r
+ mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
+ PostgresTest.cs \
-r System.Data.dll \
-r Mono.Data.PostgreSqlClient.dll
</pre>
<p>
<pre>
-\r
- danmorg@DANPC ~/mono/mcs/class/System.Data/Test\r
- $ mcs PostgresTest.cs -r System.Data.dll\r
-\r
- danmorg@DANPC ~/mono/mcs/class/System.Data/Test\r
- $ mono PostgresTest.exe\r
- Postgres provider specific tests...\r
-\r
- Drop table:\r
- Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR: \r
- table "mono_postgres_test" does not exist\r
- <Stack Trace>\r
-\r
- Create table with all supported types:\r
- OK\r
- Insert values for all known types:\r
- OK\r
- Update values:\r
- OK\r
- Insert values for all known types:\r
- OK\r
- Aggregate: count(*)\r
- Agg Result: 2\r
- Aggregate: min(text_value)\r
- Agg Result: This is a text\r
- Aggregate: max(int4_value)\r
- Agg Result: 1048000\r
- Aggregate: sum(int4_value)\r
- Agg Result: 1048003\r
- Select values from the database:\r
- Result is from a SELECT SQL Query. Records Affected: -1\r
- Result Set 1...\r
- Total Columns: 28\r
- ColumnName = boolean_value\r
- ColumnOrdinal = 1\r
- ColumnSize = 1\r
- NumericPrecision = 0\r
- NumericScale = 0\r
- IsUnique = False\r
- IsKey =\r
- BaseCatalogName =\r
- BaseColumnName = boolean_value\r
- BaseSchemaName =\r
- BaseTableName =\r
- DataType = System.Boolean\r
- AllowDBNull = False\r
- ProviderType = 16\r
- IsAliased = False\r
- IsExpression = False\r
- IsIdentity = False\r
- IsAutoIncrement = False\r
- IsRowVersion = False\r
- IsHidden = False\r
- IsLong = False\r
- IsReadOnly = False\r
-\r
- ...\r
-\r
- ColumnName = null_timestamp_value\r
- ColumnOrdinal = 28\r
- ColumnSize = 8\r
- NumericPrecision = 0\r
- NumericScale = 0\r
- IsUnique = False\r
- IsKey =\r
- BaseCatalogName =\r
- BaseColumnName = null_timestamp_value\r
- BaseSchemaName =\r
- BaseTableName =\r
- DataType = System.DateTime\r
- AllowDBNull = False\r
- ProviderType = 1184\r
- IsAliased = False\r
- IsExpression = False\r
- IsIdentity = False\r
- IsAutoIncrement = False\r
- IsRowVersion = False\r
- IsHidden = False\r
- IsLong = False\r
- IsReadOnly = False\r
-\r
- Gonna do a Read() now...\r
- Row 0:\r
- Col 0: boolean_value: False\r
- Col 1: int2_value: 5\r
- Col 2: int4_value: 3\r
- Col 3: bigint_value: 9\r
- Col 4: float_value: 3.141590\r
- Col 5: double_value: 3.14159\r
- Col 6: numeric_value: 123456789012.345\r
- Col 7: char_value: Mono.Data!\r
- Col 8: varchar_value: It was not me!\r
- Col 9: text_value: We got data!\r
- Col 10: point_value: (1,0)\r
- Col 11: time_value: 01/01/1 21:13:14\r
- Col 12: date_value: 02/29/2000 00:00:00\r
- Col 13: timestamp_value: 02/29/2004 14:00:11\r
- Col 14: null_boolean_value is NULL\r
- Col 15: null_int2_value is NULL\r
- Col 16: null_int4_value is NULL\r
- Col 17: null_bigint_value is NULL\r
- Col 18: null_float_value is NULL\r
- Col 19: null_double_value is NULL\r
- Col 20: null_numeric_value is NULL\r
- Col 21: null_char_value is NULL\r
- Col 22: null_varchar_value is NULL\r
- Col 23: null_text_value is NULL\r
- Col 24: null_point_value is NULL\r
- Col 25: null_time_value is NULL\r
- Col 26: null_date_value is NULL\r
- Col 27: null_timestamp_value is NULL\r
- Row 1:\r
- Col 0: boolean_value: True\r
- Col 1: int2_value: -22\r
- Col 2: int4_value: 1048000\r
- Col 3: bigint_value: 123456789012345\r
- Col 4: float_value: 3.141590\r
- Col 5: double_value: 3.14159\r
- Col 6: numeric_value: 123456789012.345\r
- Col 7: char_value: This is a char\r
- Col 8: varchar_value: This is a varchar\r
- Col 9: text_value: This is a text\r
- Col 10: point_value: (1,0)\r
- Col 11: time_value: 01/01/1 21:13:14\r
- Col 12: date_value: 02/29/2000 00:00:00\r
- Col 13: timestamp_value: 02/29/2004 14:00:11\r
- Col 14: null_boolean_value is NULL\r
- Col 15: null_int2_value is NULL\r
- Col 16: null_int4_value is NULL\r
- Col 17: null_bigint_value is NULL\r
- Col 18: null_float_value is NULL\r
- Col 19: null_double_value is NULL\r
- Col 20: null_numeric_value is NULL\r
- Col 21: null_char_value is NULL\r
- Col 22: null_varchar_value is NULL\r
- Col 23: null_text_value is NULL\r
- Col 24: null_point_value is NULL\r
- Col 25: null_time_value is NULL\r
- Col 26: null_date_value is NULL\r
- Col 27: null_timestamp_value is NULL\r
- Total Rows Retrieved: 2\r
- Total Result sets: 1\r
- Call ExecuteReader with a SQL Command. \r
- (Not INSERT,UPDATE,DELETE\r
- ).\r
- Result is from a SQL Command not (INSERT,UPDATE,DELETE). \r
- Records Affected: -1\r
- Total Result sets: 0\r
- Call ExecuteReader with a SQL Command. \r
- (Is INSERT,UPDATE,DELETE)\r
- .\r
- Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1\r
- Total Result sets: 0\r
- Calling stored procedure version()\r
- Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5\r
- Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, \r
- compiled by GCC 2.9\r
- 5.3-5\r
- Clean up...\r
- Drop table...\r
- OK\r
- RESULT: 0\r
-\r
-</pre>\r
-\r
+
+ 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>
+
+* Action Plan
+
+ <p>Eventually replace the PostgreSQL data provider in Mono
+ with <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>.
+ Npgsql is a .Net Data Provider for PostgreSQL which implements
+ the <a href="http://developer.postgresql.org/docs/postgres/protocol.html">PostgreSQL Frontend/Backend Protocol</a>.
+ Npgsql is implemented in 100% C#. This provider was created by Francisco Figueiredo jr.
+ and has many programmers developing the provider.
projects / mono.git / blobdiff