diff options
Diffstat (limited to 'web/postgresql')
| -rw-r--r-- | web/postgresql | 365 |
1 files changed, 365 insertions, 0 deletions
diff --git a/web/postgresql b/web/postgresql new file mode 100644 index 00000000000..90c3f4f6009 --- /dev/null +++ b/web/postgresql @@ -0,0 +1,365 @@ +* PostgreSQL and Mono + + When it comes to Mono and PostgreSQL, there are many ways + you can access your data. + +* Data Providers + + There are many ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a>: + + There are two providers created specifically for PostgreSQL included with Mono: + +<ul> + + <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a> + <ul> + <li>included with Mono</li> + + <li>a .NET Managed Data Provider for PostgreSQL</li> + + <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.PostgreSqlClient (DEPRECATED) + <ul> + + <li>Deprecated in favor of Npgsql. No longer included in a release of Mono.</li> + + </ul> + </li> + + <li><a href="http://sourceforge.net/projects/mysqlnet/">ByteFX.Data</a> has a provider for PostgreSQL too, but I do not know how well it works with Mono.</li> + + <li>There is another .NET data provider for PostgreSQL named <a href="http://sourceforge.net/projects/pgsqlclient/">PgSqlClient</a>, but I do not know if it works with Mono.</li> + + <li>If none of the above providers meet your needs. There is the ODBC and OLEDB providers included with Mono.</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> + +** 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> +</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>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 been replaced Mono.Data.PostgreSqlClient as the provider of + choice to use. Mono.Data.PostgreSqlClient is deprecated and is no longer included in + Mono releases. Please use Npgsql for PostgreSQL data access.</li> + + <li>Implement new features of PostgreSQL.</li> + </ul> + +</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>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 = new NpgsqlConnection(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 = (string) reader["firstname"]; + string LastName = (string) 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> +<pre> + mcs TestExample.cs -r System.Data.dll \ + -r Npgsql.dll +</pre> + </ul> + </li> + <li>Running the Example: +<pre> +mono TestExample.exe +</pre> +</li> +</ul> + + +** Installation instructions for PostgreSQL DBMS: + <p><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> + + |