mcs/class/System.Data/Test/PostgresTest.cs

   1 /* PostgresTest.cs - based on the postgres-test.c in libgda
   2  *
   3  * Copyright (C) 2002 Gonzalo Paniagua Javier
   4  * Copyright (C) 2002 Daniel Morgan
   5  *
   6  * ORIGINAL AUTHOR:
   7  *      Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
   8  * PORTING FROM C TO C# AUTHOR:
   9  *      Daniel Morgan <danmorg@sc.rr.com>
  10  *
  11  * Permission was given from the original author, Gonzalo Paniagua Javier,
  12  * to port and include his original work in Mono.
  13  *
  14  * The original work falls under the LGPL, but the port to C# falls
  15  * under the X11 license.
  16  *
  17  * This program is free software; you can redistribute it and/or
  18  * modify it under the terms of the GNU General Public License as
  19  * published by the Free Software Foundation; either version 2 of the
  20  * License, or (at your option) any later version.
  21  *
  22  * This program is distributed in the hope that it will be useful,
  23  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  24  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  25  * Library General Public License for more details.
  26  *
  27  * You should have received a copy of the GNU General Public
  28  * License along with this program; see the file COPYING.  If not,
  29  * write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
  30  * Boston, MA 02111-1307, USA.
  31  */
  32
  33 using System;
  34 using System.Data;
  35 using Mono.Data.PostgreSqlClient;
  36
  37 namespace Test.Mono.Data.PostgreSqlClient {
  38
  39         class PostgresTest {
  40
  41                 // execute SQL CREATE TABLE Command using ExecuteNonQuery()
  42                 static void CreateTable (IDbConnection cnc) {
  43
  44                         IDbCommand createCommand = cnc.CreateCommand();
  45
  46                         createCommand.CommandText =
  47                                 "create table mono_postgres_test (" +
  48                                 "boolean_value boolean, " +
  49                                 "int2_value smallint, " +
  50                                 "int4_value integer, " +
  51                                 "bigint_value bigint, " +
  52                                 "float_value real, " +
  53                                 "double_value double precision, " +
  54                                 "numeric_value numeric(15, 3), " +
  55                                 "char_value char(50), " +
  56                                 "varchar_value varchar(20), " +
  57                                 "text_value text, " +
  58                                 "point_value point, " +
  59                                 "time_value time, " +
  60                                 "date_value date, " +
  61                                 "timestamp_value timestamp, " +
  62                                 "null_boolean_value boolean, " +
  63                                 "null_int2_value smallint, " +
  64                                 "null_int4_value integer, " +
  65                                 "null_bigint_value bigint, " +
  66                                 "null_float_value real, " +
  67                                 "null_double_value double precision, " +
  68                                 "null_numeric_value numeric(15, 3), " +
  69                                 "null_char_value char(50), " +
  70                                 "null_varchar_value varchar(20), " +
  71                                 "null_text_value text, " +
  72                                 "null_point_value point, " +
  73                                 "null_time_value time, " +
  74                                 "null_date_value date, " +
  75                                 "null_timestamp_value timestamp " +
  76                                 ")";
  77
  78                         createCommand.ExecuteNonQuery ();
  79                 }
  80
  81                 // execute SQL DROP TABLE Command using ExecuteNonQuery
  82                 static void DropTable (IDbConnection cnc) {
  83
  84                         IDbCommand dropCommand = cnc.CreateCommand ();
  85
  86                         dropCommand.CommandText =
  87                                 "drop table mono_postgres_test";
  88
  89                         dropCommand.ExecuteNonQuery ();
  90                 }
  91
  92                 // execute stored procedure using ExecuteScalar()
  93                 static object CallStoredProcedure (IDbConnection cnc) {
  94
  95                         IDbCommand callStoredProcCommand = cnc.CreateCommand ();
  96                         object data;
  97
  98                         callStoredProcCommand.CommandType =
  99                                 CommandType.StoredProcedure;
 100                         callStoredProcCommand.CommandText =
 101                                 "version";
 102
 103                         data = callStoredProcCommand.ExecuteScalar ();
 104
 105                         return data;
 106                 }
 107
 108                 // execute SQL INSERT Command using ExecuteNonQuery()
 109                 static void InsertData (IDbConnection cnc) {
 110
 111                         IDbCommand insertCommand = cnc.CreateCommand();
 112
 113                         insertCommand.CommandText =
 114                                 "insert into mono_postgres_test (" +
 115                                 "boolean_value, " +
 116                                 "int2_value, " +
 117                                 "int4_value, " +
 118                                 "bigint_value, " +
 119                                 "float_value, " +
 120                                 "double_value, " +
 121                                 "numeric_value, " +
 122                                 "char_value, " +
 123                                 "varchar_value, " +
 124                                 "text_value, " +
 125                                 "time_value, " +
 126                                 "date_value, " +
 127                                 "timestamp_value, " +
 128                                 "point_value " +
 129                                 ") values (" +
 130                                 "'T', " +
 131                                 "-22, " +
 132                                 "1048000, " +
 133                                 "123456789012345, " +
 134                                 "3.141592, " +
 135                                 "3.1415926969696, " +
 136                                 "123456789012.345, " +
 137                                 "'This is a char', " +
 138                                 "'This is a varchar', " +
 139                                 "'This is a text', " +
 140                                 "'21:13:14', " +
 141                                 "'2000-02-29', " +
 142                                 "'2004-02-29 14:00:11.31', " +
 143                                 "'(1,0)' " +
 144                                 ")";
 145
 146                         insertCommand.ExecuteNonQuery ();
 147                 }
 148
 149                 // execute a SQL SELECT Query using ExecuteReader() to retrieve
 150                 // a IDataReader so we retrieve data
 151                 static IDataReader SelectData (IDbConnection cnc) {
 152
 153                         IDbCommand selectCommand = cnc.CreateCommand();
 154                         IDataReader reader;
 155
 156                         // FIXME: System.Data classes need to handle NULLs
 157                         //        this would be done by System.DBNull ?
 158                         // FIXME: System.Data needs to handle more data types
 159                         /*
 160                         selectCommand.CommandText =
 161                                 "select * " +
 162                                 "from mono_postgres_test";
 163                         */
 164
 165                         selectCommand.CommandText =
 166                                 "select " +
 167                                 "boolean_value, " +
 168                                 "int2_value, " +
 169                                 "int4_value, " +
 170                                 "bigint_value, " +
 171                                 "float_value, " +
 172                                 "double_value, " +
 173                                 "numeric_value, " +
 174                                 "char_value, " +
 175                                 "varchar_value, " +
 176                                 "text_value, " +
 177                                 "point_value, " +
 178                                 "time_value, " +
 179                                 "date_value, " +
 180                                 "timestamp_value, " +
 181                                 "null_boolean_value, " +
 182                                 "null_int2_value, " +
 183                                 "null_int4_value, " +
 184                                 "null_bigint_value, " +
 185                                 "null_float_value, " +
 186                                 "null_double_value, " +
 187                                 "null_numeric_value, " +
 188                                 "null_char_value, " +
 189                                 "null_varchar_value, " +
 190                                 "null_text_value, " +
 191                                 "null_point_value, " +
 192                                 "null_time_value, " +
 193                                 "null_date_value, " +
 194                                 "null_timestamp_value " +
 195                                 "from mono_postgres_test";
 196
 197                         reader = selectCommand.ExecuteReader ();
 198
 199                         return reader;
 200                 }
 201
 202                 // Tests a SQL Command (INSERT, UPDATE, DELETE)
 203                 // executed via ExecuteReader
 204                 static IDataReader SelectDataUsingInsertCommand (IDbConnection cnc) {
 205
 206                         IDbCommand selectCommand = cnc.CreateCommand();
 207                         IDataReader reader;
 208
 209                         // This is a SQL INSERT Command, not a Query
 210                         selectCommand.CommandText =
 211                                 "insert into mono_postgres_test (" +
 212                                 "boolean_value, " +
 213                                 "int2_value, " +
 214                                 "int4_value, " +
 215                                 "bigint_value, " +
 216                                 "float_value, " +
 217                                 "double_value, " +
 218                                 "numeric_value, " +
 219                                 "char_value, " +
 220                                 "varchar_value, " +
 221                                 "text_value, " +
 222                                 "time_value, " +
 223                                 "date_value, " +
 224                                 "timestamp_value, " +
 225                                 "point_value " +
 226                                 ") values (" +
 227                                 "'T', " +
 228                                 "-22, " +
 229                                 "1048000, " +
 230                                 "123456789012345, " +
 231                                 "3.141592, " +
 232                                 "3.1415926969696, " +
 233                                 "123456789012.345, " +
 234                                 "'This is a char', " +
 235                                 "'This is a varchar', " +
 236                                 "'This is a text', " +
 237                                 "'21:13:14', " +
 238                                 "'2000-02-29', " +
 239                                 "'2004-02-29 14:00:11.31', " +
 240                                 "'(1,0)' " +
 241                                 ")";
 242
 243                         reader = selectCommand.ExecuteReader ();
 244
 245                         return reader;
 246                 }
 247
 248                 // Tests a SQL Command not (INSERT, UPDATE, DELETE)
 249                 // executed via ExecuteReader
 250                 static IDataReader SelectDataUsingCommand (IDbConnection cnc) {
 251
 252                         IDbCommand selectCommand = cnc.CreateCommand();
 253                         IDataReader reader;
 254
 255                         // This is a SQL Command, not a Query
 256                         selectCommand.CommandText =
 257                                 "SET DATESTYLE TO 'ISO'";
 258
 259                         reader = selectCommand.ExecuteReader ();
 260
 261                         return reader;
 262                 }
 263
 264
 265                 // execute an SQL UPDATE Command using ExecuteNonQuery()
 266                 static void UpdateData (IDbConnection cnc) {
 267
 268                         IDbCommand updateCommand = cnc.CreateCommand();
 269
 270                         updateCommand.CommandText =
 271                                 "update mono_postgres_test " +
 272                                 "set " +
 273                                 "boolean_value = 'F', " +
 274                                 "int2_value    = 5, " +
 275                                 "int4_value    = 3, " +
 276                                 "bigint_value  = 9, " +
 277                                 "char_value    = 'Mono.Data!'   , " +
 278                                 "varchar_value = 'It was not me!', " +
 279                                 "text_value    = 'We got data!'   " +
 280                                 "where int2_value = -22";
 281
 282                         updateCommand.ExecuteNonQuery ();
 283                 }
 284
 285                 // used to do a min(), max(), count(), sum(), or avg()
 286                 // execute SQL SELECT Query using ExecuteScalar
 287                 static object SelectAggregate (IDbConnection cnc, String agg) {
 288
 289                         IDbCommand selectCommand = cnc.CreateCommand();
 290                         object data;
 291
 292                         Console.WriteLine("Aggregate: " + agg);
 293
 294                         selectCommand.CommandType = CommandType.Text;
 295                         selectCommand.CommandText =
 296                                 "select " + agg +
 297                                 "from mono_postgres_test";
 298
 299                         data = selectCommand.ExecuteScalar ();
 300
 301                         Console.WriteLine("Agg Result: " + data);
 302
 303                         return data;
 304                 }
 305
 306                 // used internally by ReadData() to read each result set
 307                 static void ReadResult(IDataReader rdr, DataTable dt) {
 308                                                 \r
 309                         // number of columns in the table\r
 310                         Console.WriteLine("   Total Columns: " +\r
 311                                 dt.Rows.Count);\r
 312 \r
 313                         // display the schema\r
 314                         foreach (DataRow schemaRow in dt.Rows) {\r
 315                                 foreach (DataColumn schemaCol in dt.Columns)\r
 316                                         Console.WriteLine(schemaCol.ColumnName + \r
 317                                                 " = " + \r
 318                                                 schemaRow[schemaCol]);\r
 319                                 Console.WriteLine();\r
 320                         }\r
 321 \r
 322                         int nRows = 0;\r
 323                         int c = 0;\r
 324                         string output, metadataValue, dataValue;\r
 325                         // Read and display the rows\r
 326                         Console.WriteLine("Gonna do a Read() now...");\r
 327                         while(rdr.Read()) {\r
 328                                 Console.WriteLine("   Row " + nRows + ": ");\r
 329                                         \r
 330                                 for(c = 0; c < rdr.FieldCount; c++) {\r
 331                                         // column meta data \r
 332                                         DataRow dr = dt.Rows[c];\r
 333                                         metadataValue = \r
 334                                                 "    Col " + \r
 335                                                 c + ": " + \r
 336                                                 dr["ColumnName"];\r
 337                                                 \r
 338                                         // column data\r
 339                                         if(rdr.IsDBNull(c) == true)\r
 340                                                 dataValue = " is NULL";\r
 341                                         else\r
 342                                                 dataValue = \r
 343                                                         ": " + \r
 344                                                         rdr.GetValue(c);\r
 345                                         \r
 346                                         // display column meta data and data\r
 347                                         output = metadataValue + dataValue;                                     \r
 348                                         Console.WriteLine(output);\r
 349                                 }\r
 350                                 nRows++;\r
 351                         }\r
 352                         Console.WriteLine("   Total Rows Retrieved: " + \r
 353                                 nRows);
 354                 }
 355
 356                 // Used to read data from IDataReader after calling IDbCommand:ExecuteReader()
 357                 static void ReadData(IDataReader rdr) {
 358
 359                         int results = 0;
 360                         if(rdr == null) {
 361
 362                                 Console.WriteLine("IDataReader has a Null Reference.");
 363                         }
 364                         else {
 365                                 do {\r
 366                                         DataTable dt = rdr.GetSchemaTable();\r
 367                                         if(rdr.RecordsAffected != -1) {\r
 368                                                 // Results for \r
 369                                                 // SQL INSERT, UPDATE, DELETE Commands \r
 370                                                 // have RecordsAffected >= 0\r
 371                                                 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);\r
 372                                         }\r
 373                                         else if(dt == null)\r
 374                                                 // Results for\r
 375                                                 // SQL Commands not INSERT, UPDATE, nor DELETE\r
 376                                                 // have RecordsAffected == -1\r
 377                                                 // and GetSchemaTable() returns a null reference\r
 378                                                 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);\r
 379                                         else {\r
 380                                                 // Results for\r
 381                                                 // SQL SELECT Queries\r
 382                                                 // have RecordsAffected = -1\r
 383                                                 // and GetSchemaTable() returns a reference to a DataTable\r
 384                                                 Console.WriteLine("Result is from a SELECT SQL Query.  Records Affected: " + rdr.RecordsAffected);\r
 385                 \r
 386                                                 results++;\r
 387                                                 Console.WriteLine("Result Set " + results + "...");\r
 388 \r
 389                                                 ReadResult(rdr, dt);\r
 390                                         }\r
 391 \r
 392                                 } while(rdr.NextResult());\r
 393                                 Console.WriteLine("Total Result sets: " + results);\r
 394                         \r
 395                                 rdr.Close();
 396                         }
 397                 }
 398
 399                 /* Postgres provider tests */
 400                 static void DoPostgresTest (IDbConnection cnc) {
 401
 402                         IDataReader reader;
 403                         Object oDataValue;
 404
 405                         Console.WriteLine ("\tPostgres provider specific tests...\n");
 406
 407                         /* Drops the gda_postgres_test table. */
 408                         Console.WriteLine ("\t\tDrop table: ");
 409                         try {
 410                                 DropTable (cnc);
 411                                 Console.WriteLine ("OK");
 412                         }
 413                         catch (PgSqlException e) {
 414                                 Console.WriteLine("Error (don't worry about this one)" + e);
 415                         }
 416
 417                         try {
 418                                 /* Creates a table with all supported data types */
 419                                 Console.WriteLine ("\t\tCreate table with all supported types: ");
 420                                 CreateTable (cnc);
 421                                 Console.WriteLine ("OK");
 422
 423                                 /* Inserts values */
 424                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 425                                 InsertData (cnc);
 426                                 Console.WriteLine ("OK");
 427
 428                                 /* Update values */
 429                                 Console.WriteLine ("\t\tUpdate values: ");
 430                                 UpdateData (cnc);
 431                                 Console.WriteLine ("OK");
 432
 433                                 /* Inserts values */
 434                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 435                                 InsertData (cnc);
 436                                 Console.WriteLine ("OK");
 437
 438                                 /* Select aggregates */
 439                                 SelectAggregate (cnc, "count(*)");
 440                                 // FIXME: still having a problem with avg()
 441                                 //        because it returns a decimal.
 442                                 //        It may have something to do
 443                                 //        with culture not being set
 444                                 //        properly.
 445                                 //SelectAggregate (cnc, "avg(int4_value)");
 446                                 SelectAggregate (cnc, "min(text_value)");
 447                                 SelectAggregate (cnc, "max(int4_value)");
 448                                 SelectAggregate (cnc, "sum(int4_value)");
 449
 450                                 /* Select values */
 451                                 Console.WriteLine ("\t\tSelect values from the database: ");
 452                                 reader = SelectData (cnc);
 453                                 ReadData(reader);
 454
 455                                 /* SQL Command via ExecuteReader/SqlDataReader */
 456                                 /* Command is not INSERT, UPDATE, or DELETE */
 457                                 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE).");
 458                                 reader = SelectDataUsingCommand(cnc);
 459                                 ReadData(reader);
 460
 461                                 /* SQL Command via ExecuteReader/SqlDataReader */
 462                                 /* Command is INSERT, UPDATE, or DELETE */
 463                                 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE).");
 464                                 reader = SelectDataUsingInsertCommand(cnc);
 465                                 ReadData(reader);
 466 \r
 467                                 // Call a Stored Procedure named Version()\r
 468                                 Console.WriteLine("\t\tCalling stored procedure version()");\r
 469                                 object obj = CallStoredProcedure(cnc);\r
 470                                 Console.WriteLine("Result: " + obj);\r
 471 \r
 472                                 Console.WriteLine("Database Server Version: " + \r
 473                                         ((PgSqlConnection)cnc).ServerVersion);\r
 474 \r
 475                                 /* Clean up */
 476                                 Console.WriteLine ("Clean up...");
 477                                 Console.WriteLine ("\t\tDrop table...");
 478                                 DropTable (cnc);
 479                                 Console.WriteLine("OK");
 480                         }
 481                         catch(Exception e) {
 482                                 Console.WriteLine("Exception caught: " + e);
 483                         }
 484                 }
 485
 486                 [STAThread]\r
 487                 static void Main(string[] args) {\r
 488                         Console.WriteLine("Tests Start.");\r
 489                         Console.WriteLine("Creating PgSqlConnectioin...");\r
 490                         PgSqlConnection cnc = new PgSqlConnection ();\r
 491 \r
 492                         // possible PostgreSQL Provider ConnectionStrings\r
 493                         //string connectionString =
 494                         //      "Server=hostname;" +
 495                         //      "Database=database;" +
 496                         //      "User ID=userid;" +
 497                         //      "Password=password";\r
 498                         // or\r
 499                         //string connectionString =
 500                         //      "host=hostname;" +
 501                         //      "dbname=database;" +
 502                         //      "user=userid;" +
 503                         //      "password=password";
 504 \r
 505                         string connectionString =
 506                                 "host=localhost;" +
 507                                 "dbname=test;" +
 508                                 "user=postgres";
 509                         \r
 510                         Console.WriteLine("Setting ConnectionString: " +\r
 511                                 connectionString);\r
 512                         cnc.ConnectionString =  connectionString;\r
 513 \r
 514                         Console.WriteLine("Opening database connection...");\r
 515                         cnc.Open();\r
 516 \r
 517                         Console.WriteLine("Do Tests....");\r
 518                         DoPostgresTest(cnc);
 519
 520                         Console.WriteLine("Close database connection...");
 521                         cnc.Close();
 522
 523                         Console.WriteLine("Tests Done.");
 524                 }
 525         }
 526 }