-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
postgresql
578 lines (471 loc) · 18.1 KB
/
postgresql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
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>