<ul>
* Implementation of System.Data.SqlClient based on
- the PostgreSQL C API.
+ the <a href="http://www.postgresql.org/idocs/">PostgreSQL C API</a>
- * Once the System.Data.SqlClient code is functional and
- is usable by other people, we willl move it to
+ * Once the <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a>
+ code is functional and is usable by other people, we willl move it to
System.Data.PostgreSQL, and will convert the existing
System.Data.SqlClient to be just a wrapper around
System.Data.PostgreSQL.
</ul>
- <b>Step 2:</b> OleDB:
+ <b>Step 2:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOleDb.asp?frame=true">System.Data.OleDb</a> Provider:
+
<ul>
- * On Unix systems: System.Data.OleDb will use LibGDA as its
- engine.
+ * On Unix systems: System.Data.OleDb will use the
+ <a href="http://www.gnome-db.org/">LibGDA</a>
+ engine.
- LibGDA is the data access engine that is used by
- Gnome-Db (only libgda, not libgnomedb at all).
+ LibGDA is a data access engine like ADO/OLE-DB, but for Unix. It is
+ used by Gnome-Db and libgnomedb. There is work under way to get libgda
+ working under Windows by the Gnome-Db developers.
* On Windows systems: System.Data.OleDb will use OLE-DB as
- its engine.
+ its engine. It may have the option of using libgda too.
</ul>
- <b>Step 3:</b> System.Data.SqlClient Providers:
+ <b>Step 3:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:
<ul>
- * System.Data.SqlClient will then become a generic
- proxy for binding to other SQL implementations other
- than PostgreSQL (MySQL on Unix/Windows; MS SQL on
- Window; Interbase on Unix/Windows). Others are welcomed.
+ * <p>System.Data.SqlClient will then become a managed
+ provider for Microsoft SQL Server, both on Windows and
+ Linux, to be compatible with applications written
+ for the Microsoft .NET Development Framework.
+
+ <p>Once Step 1 has been completed and the PostgreSQL
+ provider support has been moved to its own place at
+ System.Data.PostgreSQL, we will use
+ <a href="http://www.freetds.org/">FreeTDS</a> as the basis
+ for providing access to Microsoft SQL Server databases.
+
+ FreeTDS is a C API for Unix and Windows that implements
+ the TDS (Tabular Data Stream) protocol used in accessing
+ Microsoft SQL Server and Sybase databases.
+ </ul>
+
+ <b>Step 4:</b> <a href="http://msdn.microsoft.com/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml&frame=true">System.Data.Odbc</a> Provider:
+
+ <ul>
+ * We will create a .NET Managaed Provider for ODBC
+ in System.Data.Odbc for those using ODBC.
+ On Unix and Windows, <a href="http://www.unixodbc.org/">unixODBC</a> will be used.
+
+ unixODBC works on Unix and Windows. Providers exist for
+ Oracle, Microsoft SQL Server and Sybase via FreeTDS,
+ MySQL, PostgreSQL, IBM DB2, Interbase, and others.
+
</ul>
- <b>Step 4:</b> Other System.Data providers:
+ <b>Step 5:</b> Other System.Data providers:
<ul>
- * The idea in MS .NET System.Data seems to be to have
+ * The idea in Microsoft .NET System.Data seems to be to have
a managed provider for each supported DBMS. So, apart
from System.Data.OleDb (generic) and System.Data.SqlClient,
- we'll need to have System.Data.ODBC, System.Data.MySQL,
- System.Data.Oracle, System.Data.PostgreSQL, etc. Others,
+ we'll need to have System.Data.MySQL,
+ System.Data.Oracle, and System.Data.PostgreSQL. Others,
of course, are welcomed.
</ul>
* Current Status
- We are able to do simple CREATE TABLE, DROP TABLE, INSERT, and
+ <p>We are still working on Step 1, but we are planning the other steps.
+ If you have any ideas, let us know.
+
+ <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
- We are also able to do simple aggregate functions,
- ie, count(), sum(), avg(), min(), and
- max() in a simple SELECT SQL query using ExecuteSecalar() now.
+ <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.
- We are also able to retrieve data with a simple SELECT SQL query
+ <p>We are also able to retrieve data with a simple SELECT SQL query
using ExecuteReader() which returns a SqlDataReader. We are able to
- GetSchemaTable() get the meta data about the table columns. We are able
- to Read() to get each row from the result set. We are able to get
+ use GetSchemaTable() to get the meta data about the table columns.
+ We are able to Read() to get each row from the result set. We are able to get
String data (char, bpchar (character), text, varchar), Int16 (int2 or smallint),
- and Int32 (int4 or integer), Int64 (int8 or bigint). More data types will
- follow later on.
+ Int32 (int4 or integer), Int64 (int8 or bigint), DateTime (time, date, timestamp),
+ Boolean (boolean), Single (float), and Double (double).
+ Other More data types will follow later on.
- Parameters are not working nor has stored procedures been tested - but they will be.
+ <p>Parameters and stored procedures have not been tested and most likely
+ do not work.
+
+ <p>A lot of functionality in System.Data is missing, but the
+ infrastructure is starting to come together.
- A lot of functionality in System.Data is missing, but the infrastructure is
- starting to come together.
+ <p>Need help on the DataSet/DataAdaptor/DataTable/DataRelation/XML
+ functionality so we can integrate with
+ the ASP.NET controls and Windows.Forms.
- To compile that test program, you need:
+ <P>Need to add XML support in System.Data.
+
+ <p>The System.Data.dll gets built with the rest of the class library.
+ To compile the System.Data.dll assembly separately, you need:
- <b>On Linux</b>
+ <b>On Unix</b>
<ul>
- * update your mono sources.
+ * update your mono sources. Be sure you have latest mcs.exe
+ and .dll's, since there have been many fixes needed for
+ compilation on Linux.
- * get the .dll's and mcs.exe built on windows, and put them on your
- linux machine.
+ * compile System.Data.dll:
- * compile the test program along with the System.Data.Common and
- System.Data.SqlClient files.
+ cd mcs/class/System.Data
+ mcs --target library -o System.Data.dll @list
+ </ul>
+
+ <b>On Windows</b>
+
+ <ul>
+ * update your mono sources. Be sure you have latest mcs.exe
+ and .dll's. You can use the same method as Linux, or you can use NAnt.
+
+ * To use NAnt:
+
+ cd mcs/class/System.Data
+ ../../nant/NAnt.exe
+
+ This will automatically copy the System.Data.dll to Test.
+ If you need to do a clean for the System.Data.dll assembly,
+ cd mcs/class/System.Data and run ../../nant/NAnt.exe clean
</ul>
* Testing
- In order to test System.Data.SqlClient, you will need to have
+ <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.
-
- Why? Because it is open source, has a client
- library that is easy to use, PostgreSQL is easy to install
- using the Cygwin install program, not difficult to setup after
+
+ <p>Why? Because it is open source, has a client
+ library that is easy to use, PostgreSQL on Windows 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.
-
- If you plan on using a remote PostgreSQL DBMS Server,
+
+ <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".
- Installation instructions for PostgreSQL DBMS:
+<pre>
+"host=localhost;dbname=test;user=postgres"
+</pre>
+
+ <p>Installation instructions for PostgreSQL DBMS:
- <b>On Linux</b>
+ <b>On Unix</b>
<ul>
- * TODO
+ * 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\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 >logfile 2>&1 &\r
+ createdb test\r
+ 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 AUTH_ARGUMENT
+
+ 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>.
- * It easier to install PostgreSQL on Linux than Windows.
</ul>
<b>On Windows</b>
<ul>
- * Use the cygwin installer to install the PostgreSQL DBMS. It is
+ * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to install the PostgreSQL DBMS. It is
found in the database category.
- * Read the file postgres-x.x.README at /usr/doc/Cygwin and read
+ * <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.
- The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
+ * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
- * Once cygwin has installed PostgreSQL on your computer,
+ * <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\FAQ_MSWIN
-
- The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
-
- Important notes from this file are:
+ in /usr/doc/postgres-x.x
+
+ * <p>Important notes from this file are:
- * Point 2. - Install the latest cygipc package,
- available at
- http://www.neuro.gatech.edu/users/cwilson/cygutils/V1.1/cygipc/
+ <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.
- 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
- * Point 3. 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:
- My own note, 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:
- 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;
+ <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.
- * Point 4. Start the ipc-daemon that came with the cygipc package. There
- are two ways to do this: run it from the command line as:
- ipc-daemon &
-
- 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
- To install ipc-daemon as a service,
- you just have to run 'ipc-daemon --install-as-service' (--remove-as-service)
- and then run 'net start ipc-daemon'
+<pre>
+net start ipc-daemon
+</pre>
+ </ul>
- * Read the installation.html file
- at /usr/doc/postgresql-x.x/html/installation.html
+ <p>Read the installation.html file
+ at /usr/doc/postgresql-x.x/html/installation.html
- In this file, you will run the following commands:
+ <p>You will see in this file that you will need to
+ run the following commands:
- * mkdir /usr/local/pgsql/data\r
- * initdb -D /usr/local/pgsql/data\r
- * postmaster -D /usr/local/pgsql/data >logfile 2>&1 &\r
- * createdb test\r
- * psql test
-
- 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.
+ <p>
+<pre>
+mkdir /usr/local/pgsql/data\r
+initdb -D /usr/local/pgsql/data\r
+postmaster -D /usr/local/pgsql/data >logfile 2>&1 &\r
+createdb test\r
+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.
- psql is a command-line PostgreSQL client tool to
- enter and run SQL commands and queries.
+ <p>psql is a command-line PostgreSQL client tool to
+ enter and run SQL commands and queries.
- If there is no database user named postgres, create a user named
- postgres with the following SQL command in the client tool psql:
- plsql test
- create user postgres with password 'fun2db';
- The only reason I say this is so you can easily use the System.Data tests
- without having to change the database, userid, etc.
+ <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>
- In the path mcs/class/System.Data/Test
+ <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.
- To use it to test System.Data, you
+
+ <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>
- dbname is the database, ie, test
- host is the hostname of the PostgreSQL DBMS Server to connect to
- user is the username, ie, someuser
- password is the password, ie, mypass1234
-
- The connection string is in OLE-DB connection string format. Internally,
+ <p>The connection string is in OLE-DB connection string format. Internally,
SqlConnection converts this to the PostgreSQL connection string format.
- OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
- PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
+ <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:
- mcs PostgresTest.cs -r System.Data
+ <p>
+<pre>
+mcs PostgresTest.cs -r System.Data.dll
+</pre>
+
+ <p>
To run using mint, do:
- mint PostgresTest.exe
- To run using mono, do:
- mono PostgresTest.exe
+ <p>
+<pre>
+mint PostgresTest.exe
+</pre>
- You should get something like:
+ <p>
+ To run using mono, do:
+<pre>
+mono PostgresTest.exe
+</pre>
-Administrator@DANPC ~/mono/mcs/class/System.Data/Test\r
-$ mcs PostgresTest.cs -r System.Data\r
+ <p>You should get something like:
+
+<p>
+<pre>
+ 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: table "mono\r
+ _postgres_test" does not exist\r
+ <Stack Trace>\r
\r
-Administrator@DANPC ~/mono/mcs/class/System.Data/Test\r
-$ mint PostgresTest.exe\r
- Postgres provider specific tests...\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
+ Get Schema.\r
+ dt.Columns.Count: 12\r
+ * Column Name: boolean_value\r
+ MaxLength: 1\r
+ Type: System.Boolean\r
+ * Column Name: int2_value\r
+ MaxLength: 2\r
+ Type: System.Int16\r
+ * Column Name: int4_value\r
+ MaxLength: 4\r
+ Type: System.Int32\r
+ * Column Name: bigint_value\r
+ MaxLength: 8\r
+ Type: System.Int64\r
+ * Column Name: float_value\r
+ MaxLength: 4\r
+ Type: System.Single\r
+ * Column Name: double_value\r
+ MaxLength: 8\r
+ Type: System.Double\r
+ * Column Name: char_value\r
+ MaxLength: -1\r
+ Type: System.String\r
+ * Column Name: varchar_value\r
+ MaxLength: -1\r
+ Type: System.String\r
+ * Column Name: text_value\r
+ MaxLength: -1\r
+ Type: System.String\r
+ * Column Name: time_value\r
+ MaxLength: 8\r
+ Type: System.DateTime\r
+ * Column Name: date_value\r
+ MaxLength: 4\r
+ Type: System.DateTime\r
+ * Column Name: timestamp_value\r
+ MaxLength: 8\r
+ Type: System.DateTime\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.141593\r
+ Col 6: char_value: Mono.Data!\r
+ Col 7: varchar_value: It was not me!\r
+ Col 8: text_value: We got data!\r
+ Col 9: time_value: Monday, 01 January 1 21:13:14\r
+ Col 10: date_value: Tuesday, 29 February 2000 00:00:00\r
+ Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11\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.141593\r
+ Col 6: char_value: This is a char\r
+ Col 7: varchar_value: This is a varchar\r
+ Col 8: text_value: This is a text\r
+ Col 9: time_value: Monday, 01 January 1 21:13:14\r
+ Col 10: date_value: Tuesday, 29 February 2000 00:00:00\r
+ Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11\r
+ Rows: 2\r
+ Clean up...\r
+ Drop table...\r
+ OK\r
+ RESULT: 0\r
\r
- Drop table:\r
-Error (don't worry about this one)\r
- Create table with all supported types:\r
-OK\r
- Insert values for all known types:\r
-OK\r
- Select values from the database:\r
- Get Schema.\r
-dt.Columns.Count: 6\r
-* Column Name: int2_value\r
- MaxLength: 2\r
- Type: System.Int16\r
-* Column Name: int4_value\r
- MaxLength: 4\r
- Type: System.Int32\r
-* Column Name: bigint_value\r
- MaxLength: 8\r
- Type: System.Int64\r
-* Column Name: char_value\r
- MaxLength: -1\r
- Type: System.String\r
-* Column Name: varchar_value\r
- MaxLength: -1\r
- Type: System.String\r
-* Column Name: text_value\r
- MaxLength: -1\r
- Type: System.String\r
-Row 0:\r
- Col 0: int2_value - -22\r
- Col 1: int4_value - 1048000\r
- Col 2: bigint_value - 123456789012345\r
- Col 3: char_value - This is a char\r
- Col 4: varchar_value - This is a varchar\r
- Col 5: text_value - This is a text\r
-Rows: 1\r
-Clean up...\r
- Drop table...\r
-OK\r
+</pre>\r
\r
projects / mono.git / blobdiff