diff options
Diffstat (limited to 'web/postgresql')
-rw-r--r-- | web/postgresql | 578 |
1 files changed, 578 insertions, 0 deletions
diff --git a/web/postgresql b/web/postgresql new file mode 100644 index 00000000000..89e614fa2b4 --- /dev/null +++ b/web/postgresql @@ -0,0 +1,578 @@ +* 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
+ through NpgsqlCommand.ExecuteNonQuery() method.</li>
+ <li>You can send queries like, select count(*) from table, select version()
+ with NpgsqlCommand.ExecuteScalar() method.</li>
+ <li>There is logging support. (Thanks Dave Page)
+ To use it, place code like that in your program:</li>
+
+<pre>
+ // Enable logging.
+ NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel.
+ NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile.
+</pre>
+
+ <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>
+ <li>There is a winforms test suite (Thanks Dave Page).</li>
+ <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>
+ <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>
+ <li>Thanks Ulrich Sprick for all discussion and ideas.</li>
+ + </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> + |