2 // Mono.Data.PostgreSqlClient.PgSqlConnection.cs
5 // Rodrigo Moya (rodrigo@ximian.com)
6 // Daniel Morgan (danmorg@sc.rr.com)
7 // Tim Coleman (tim@timcoleman.com)
9 // (C) Ximian, Inc 2002
10 // (C) Daniel Morgan 2002
11 // Copyright (C) Tim Coleman, 2002
14 // SQL and concepts were used from libgda 0.8.190 (GNOME Data Access)
15 // http://www.gnome-db.org/
16 // with permission from the authors of the
17 // PostgreSQL provider in libgda:
18 // Michael Lausch <michael@lausch.at>
19 // Rodrigo Moya <rodrigo@gnome-db.org>
20 // Vivien Malerba <malerba@gnome-db.org>
21 // Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
25 // Permission is hereby granted, free of charge, to any person obtaining
26 // a copy of this software and associated documentation files (the
27 // "Software"), to deal in the Software without restriction, including
28 // without limitation the rights to use, copy, modify, merge, publish,
29 // distribute, sublicense, and/or sell copies of the Software, and to
30 // permit persons to whom the Software is furnished to do so, subject to
31 // the following conditions:
33 // The above copyright notice and this permission notice shall be
34 // included in all copies or substantial portions of the Software.
36 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
37 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
38 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
39 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
40 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
41 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
42 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
45 // use #define DEBUG_SqlConnection if you want to spew debug messages
46 // #define DEBUG_SqlConnection
49 using System
.Collections
;
50 using System
.Collections
.Specialized
;
51 using System
.ComponentModel
;
53 using System
.Data
.Common
;
54 using System
.Runtime
.InteropServices
;
57 namespace Mono
.Data
.PostgreSqlClient
{
60 /// Represents an open connection to a SQL data source
62 public sealed class PgSqlConnection
: Component
, IDbConnection
,
65 // FIXME: Need to implement class Component,
66 // and interfaces: ICloneable and IDisposable
70 private PostgresTypes types
= null;
71 private IntPtr pgConn
= IntPtr
.Zero
;
73 // PGConn (Postgres Connection)
74 private string connectionString
= "";
75 // OLE DB Connection String
76 private string pgConnectionString
= "";
77 // PostgreSQL Connection String
78 private PgSqlTransaction trans
= null;
79 private int connectionTimeout
= 15;
80 // default for 15 seconds
82 // connection parameters in connection string
83 private string host
= "";
84 // Name of host to connect to
85 private string hostaddr
= "";
86 // IP address of host to connect to
87 // should be in "n.n.n.n" format
88 private string port
= "";
89 // Port number to connect to at the server host
90 private string dbname
= ""; // The database name.
91 private string user
= ""; // User name to connect as.
92 private string password
= "";
93 // Password to be used if the server
94 // demands password authentication.
95 private string options
= "";
96 // Trace/debug options to be sent to the server.
97 private string tty
= "";
98 // A file or tty for optional
99 // debug output from the backend.
100 private string requiressl
= "";
101 // Set to 1 to require
102 // SSL connection to the backend.
103 // Libpq will then refuse to connect
104 // if the server does not
105 // support SSL. Set to 0 (default) to
106 // negotiate with server.
109 private ConnectionState conState
= ConnectionState
.Closed
;
112 private PgSqlDataReader rdr
= null;
113 private bool dataReaderOpen
= false;
114 // FIXME: if true, throw an exception if SqlConnection
115 // is used for anything other than reading
116 // data using SqlDataReader
118 private string versionString
= "Unknown";
120 private bool disposed
= false;
126 // A lot of the defaults were initialized in the Fields
128 public PgSqlConnection () {
133 public PgSqlConnection (String connectionString
) {
134 SetConnectionString (connectionString
);
137 #endregion // Constructors
141 protected override void Dispose(bool disposing
) {
145 // release any managed resources
147 // release any unmanaged resources
150 this.disposed
= true;
153 base.Dispose(disposing
);
158 // [ClassInterface(ClassInterfaceType.AutoDual)]
164 #endregion // Destructors
166 #region Public Methods
168 IDbTransaction IDbConnection
.BeginTransaction () {
169 return BeginTransaction ();
172 public PgSqlTransaction
BeginTransaction () {
173 return TransactionBegin (); // call private method
176 IDbTransaction IDbConnection
.BeginTransaction (IsolationLevel
178 return BeginTransaction (il
);
181 public PgSqlTransaction
BeginTransaction (IsolationLevel il
) {
182 return TransactionBegin (il
); // call private method
185 // PostgreSQL does not support named transactions/savepoint
186 // nor nested transactions
188 public PgSqlTransaction
BeginTransaction(string transactionName
) {
189 return TransactionBegin (); // call private method
193 public PgSqlTransaction
BeginTransaction(IsolationLevel iso
,
194 string transactionName
) {
195 return TransactionBegin (iso
); // call private method
199 public void ChangeDatabase (string databaseName
) {
200 throw new NotImplementedException ();
203 object ICloneable
.Clone() {
204 throw new NotImplementedException ();
208 public void Close () {
209 if(dataReaderOpen
== true) {
210 // TODO: what do I do if
211 // the user Closes the connection
212 // without closing the Reader first?
218 IDbCommand IDbConnection
.CreateCommand () {
219 return CreateCommand ();
222 public PgSqlCommand
CreateCommand () {
223 PgSqlCommand sqlcmd
= new PgSqlCommand ("", this);
229 public void Open () {
230 if(dbname
.Equals(""))
231 throw new InvalidOperationException(
233 else if(conState
== ConnectionState
.Open
)
234 throw new InvalidOperationException(
235 "ConnnectionState is already Open");
237 ConnStatusType connStatus
;
239 // FIXME: check to make sure we have
240 // everything to connect,
241 // otherwise, throw an exception
243 pgConn
= PostgresLibrary
.PQconnectdb
244 (pgConnectionString
);
246 // FIXME: should we use PQconnectStart/PQconnectPoll
247 // instead of PQconnectdb?
248 // PQconnectdb blocks
249 // PQconnectStart/PQconnectPoll is non-blocking
251 connStatus
= PostgresLibrary
.PQstatus (pgConn
);
252 if(connStatus
== ConnStatusType
.CONNECTION_OK
) {
253 // Successfully Connected
259 String errorMessage
= PostgresLibrary
.
260 PQerrorMessage (pgConn
);
261 errorMessage
+= ": Could not connect to database.";
263 throw new PgSqlException(0, 0,
265 host
, "SqlConnection", 0);
269 #endregion // Public Methods
271 #region Internal Methods
273 // Used to prevent SqlConnection
274 // from doing anything while
275 // SqlDataReader is open.
276 // Open the Reader. (called from SqlCommand)
277 internal void OpenReader(PgSqlDataReader reader
)
279 if(dataReaderOpen
== true) {
280 // TODO: throw exception here?
286 dataReaderOpen
= true;
290 // Used to prevent SqlConnection
291 // from doing anything while
292 // SqlDataReader is open
293 // Close the Reader (called from SqlCommand)
294 // if closeConnection true, Close() the connection
295 // this is based on CommandBehavior.CloseConnection
296 internal void CloseReader(bool closeConnection
)
297 { if(closeConnection
== true)
300 dataReaderOpen
= false;
303 #endregion // Internal Methods
305 #region Private Methods
307 void SetupConnection() {
309 conState
= ConnectionState
.Open
;
311 // FIXME: load types into hashtable
312 types
= new PostgresTypes(this);
315 versionString
= GetDatabaseServerVersion();
317 // set DATE style to YYYY/MM/DD
318 IntPtr pgResult
= IntPtr
.Zero
;
319 pgResult
= PostgresLibrary
.PQexec (pgConn
, "SET DATESTYLE TO 'ISO'");
320 PostgresLibrary
.PQclear (pgResult
);
321 pgResult
= IntPtr
.Zero
;
324 string GetDatabaseServerVersion()
326 PgSqlCommand cmd
= new PgSqlCommand("select version()",this);
327 return (string) cmd
.ExecuteScalar();
330 void CloseDataSource () {
331 // FIXME: just a quick hack
332 if(conState
== ConnectionState
.Open
) {
334 if(trans
.DoingTransaction
== true) {
340 conState
= ConnectionState
.Closed
;
341 PostgresLibrary
.PQfinish (pgConn
);
342 pgConn
= IntPtr
.Zero
;
346 void SetConnectionString (string connectionString
) {
347 this.connectionString
= connectionString
;
348 StringBuilder postgresConnectionString
= new StringBuilder ();
350 connectionString
+= ";";
351 NameValueCollection parameters
= new NameValueCollection ();
353 if (connectionString
== String
.Empty
)
356 bool inQuote
= false;
357 bool inDQuote
= false;
359 string name
= String
.Empty
;
360 string value = String
.Empty
;
361 StringBuilder sb
= new StringBuilder ();
363 foreach (char c
in connectionString
) {
369 inDQuote
= !inDQuote
;
372 if (!inDQuote
&& !inQuote
) {
373 if (name
!= String
.Empty
&& name
!= null) {
374 value = sb
.ToString ();
375 parameters
[name
.ToUpper ().Trim ()] = value.Trim ();
378 value = String
.Empty
;
379 sb
= new StringBuilder ();
385 if (!inDQuote
&& !inQuote
) {
386 name
= sb
.ToString ();
387 sb
= new StringBuilder ();
398 SetProperties (parameters
);
401 private void SetProperties (NameValueCollection parameters
) {
402 StringBuilder postgresConnectionString
= new StringBuilder ();
405 foreach (string name
in parameters
) {
406 value = parameters
[name
];
416 // set DataSource property
430 case "NETWORK ADDRESS" :
434 case "INITIAL CATALOG" :
437 // set Database property
454 string valuePair
= name
.ToLower() + "=" + value;
455 postgresConnectionString
.Append (valuePair
+ " ");
458 this.pgConnectionString
= postgresConnectionString
.ToString ();
461 private PgSqlTransaction
TransactionBegin () {
462 // FIXME: need to keep track of
463 // transaction in-progress
464 trans
= new PgSqlTransaction ();
465 // using internal methods of SqlTransaction
466 trans
.SetConnection (this);
472 private PgSqlTransaction
TransactionBegin (IsolationLevel il
) {
473 // FIXME: need to keep track of
474 // transaction in-progress
475 trans
= new PgSqlTransaction ();
476 // using internal methods of SqlTransaction
477 trans
.SetConnection (this);
478 trans
.SetIsolationLevel (il
);
486 #region Public Properties
489 public ConnectionState State
{
495 public string ConnectionString
{
497 return connectionString
;
500 SetConnectionString (value);
504 public int ConnectionTimeout
{
506 return connectionTimeout
;
510 public string Database
{
516 public string DataSource
{
522 public int PacketSize
{
524 throw new NotImplementedException ();
528 public string ServerVersion
{
530 return versionString
;
534 #endregion // Public Properties
536 #region Internal Properties
538 // For System.Data.SqlClient classes
539 // to get the current transaction
540 // in progress - if any
541 internal PgSqlTransaction Transaction
{
547 // For System.Data.SqlClient classes
548 // to get the unmanaged PostgreSQL connection
549 internal IntPtr PostgresConnection
{
555 // For System.Data.SqlClient classes
556 // to get the list PostgreSQL types
557 // so can look up based on OID to
558 // get the .NET System type.
559 internal ArrayList Types
{
565 // Used to prevent SqlConnection
566 // from doing anything while
567 // SqlDataReader is open
568 internal bool IsReaderOpen
{
570 return dataReaderOpen
;
574 #endregion // Internal Properties
579 PgSqlInfoMessageEventHandler InfoMessage
;
582 StateChangeEventHandler StateChange
;
586 #region Inner Classes
588 private class PostgresTypes
{
589 // TODO: create hashtable for
590 // PostgreSQL types to .NET types
591 // containing: oid, typname, SqlDbType
593 private Hashtable hashTypes
;
594 private ArrayList pgTypes
;
595 private PgSqlConnection con
;
597 // Got this SQL with the permission from
598 // the authors of libgda
599 private const string SEL_SQL_GetTypes
=
600 "SELECT oid, typname FROM pg_type " +
601 "WHERE typrelid = 0 AND typname !~ '^_' " +
602 " AND typname not in ('SET', 'cid', " +
603 "'int2vector', 'oidvector', 'regproc', " +
604 "'smgr', 'tid', 'unknown', 'xid') " +
607 internal PostgresTypes(PgSqlConnection sqlcon
) {
610 hashTypes
= new Hashtable();
613 private void AddPgType(Hashtable types
,
614 string typname
, DbType dbType
) {
616 PostgresType pgType
= new PostgresType();
618 pgType
.typname
= typname
;
619 pgType
.dbType
= dbType
;
621 types
.Add(pgType
.typname
, pgType
);
624 private void BuildTypes(IntPtr pgResult
,
625 int nRows
, int nFields
) {
630 for(r
= 0; r
< nRows
; r
++) {
631 PostgresType pgType
=
634 // get data value (oid)
635 value = PostgresLibrary
.
640 pgType
.oid
= Int32
.Parse(value);
642 // get data value (typname)
643 value = PostgresLibrary
.
647 pgType
.typname
= String
.Copy(value);
648 pgType
.dbType
= PostgresHelper
.
654 pgTypes
= ArrayList
.ReadOnly(pgTypes
);
657 internal void Load() {
658 pgTypes
= new ArrayList();
659 IntPtr pgResult
= IntPtr
.Zero
; // PGresult
661 if(con
.State
!= ConnectionState
.Open
)
662 throw new InvalidOperationException(
663 "ConnnectionState is not Open");
665 // FIXME: PQexec blocks
666 // while PQsendQuery is non-blocking
667 // which is better to use?
668 // int PQsendQuery(PGconn *conn,
669 // const char *query);
671 // execute SQL command
672 // uses internal property to get the PGConn IntPtr
673 pgResult
= PostgresLibrary
.
674 PQexec (con
.PostgresConnection
, SEL_SQL_GetTypes
);
676 if(pgResult
.Equals(IntPtr
.Zero
)) {
677 throw new PgSqlException(0, 0,
678 "No Resultset from PostgreSQL", 0, "",
679 con
.DataSource
, "SqlConnection", 0);
682 ExecStatusType execStatus
;
684 execStatus
= PostgresLibrary
.
685 PQresultStatus (pgResult
);
687 if(execStatus
== ExecStatusType
.PGRES_TUPLES_OK
) {
691 nRows
= PostgresLibrary
.
694 nFields
= PostgresLibrary
.
697 BuildTypes (pgResult
, nRows
, nFields
);
700 PostgresLibrary
.PQclear (pgResult
);
701 pgResult
= IntPtr
.Zero
;
706 errorMessage
= PostgresLibrary
.
707 PQresStatus(execStatus
);
709 errorMessage
+= " " + PostgresLibrary
.
710 PQresultErrorMessage(pgResult
);
713 PostgresLibrary
.PQclear (pgResult
);
714 pgResult
= IntPtr
.Zero
;
716 throw new PgSqlException(0, 0,
718 con
.DataSource
, "SqlConnection", 0);
723 public ArrayList List
{