mcs/class/FirebirdSql.Data.Firebird/Test/BaseTest.cs

   1 /*
   2  *  Firebird ADO.NET Data provider for .NET and Mono
   3  *
   4  *     The contents of this file are subject to the Initial
   5  *     Developer's Public License Version 1.0 (the "License");
   6  *     you may not use this file except in compliance with the
   7  *     License. You may obtain a copy of the License at
   8  *     http://www.firebirdsql.org/index.php?op=doc&id=idpl
   9  *
  10  *     Software distributed under the License is distributed on
  11  *     an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
  12  *     express or implied.  See the License for the specific
  13  *     language governing rights and limitations under the License.
  14  *
  15  *  Copyright (c) 2002, 2004 Carlos Guzman Alvarez
  16  *  All Rights Reserved.
  17  */
  18
  19 using NUnit.Framework;
  20 using System;
  21 using System.Text;
  22 using System.Configuration;
  23 using System.Collections;
  24 using System.Security.Cryptography;
  25
  26 namespace FirebirdSql.Data.Firebird.Tests
  27 {
  28         public class BaseTest
  29         {
  30                 #region Fields
  31
  32                 private FbConnection    connection;
  33                 private FbTransaction   transaction;
  34                 private bool                    withTransaction;
  35
  36                 #endregion
  37
  38                 #region Properties
  39
  40                 public FbConnection Connection
  41                 {
  42                         get { return connection; }
  43                 }
  44
  45                 public FbTransaction Transaction
  46                 {
  47                         get { return transaction; }
  48                         set { transaction = value; }
  49                 }
  50
  51                 #endregion
  52
  53                 #region Constructors
  54
  55                 public BaseTest()
  56                 {
  57                         this.withTransaction = false;
  58                 }
  59
  60                 public BaseTest(bool withTransaction)
  61                 {
  62                         this.withTransaction = withTransaction;
  63                 }
  64
  65                 #endregion
  66
  67                 #region NUnit Methods
  68
  69                 [SetUp]
  70                 public virtual void SetUp()
  71                 {
  72                         string cs = this.BuildConnectionString();
  73
  74                         CreateDatabase(cs);
  75                         CreateTables(cs);
  76                         InsertTestData(cs);
  77                         CreateProcedures(cs);
  78                         CreateTriggers(cs);
  79
  80                         this.connection = new FbConnection(cs);
  81                         this.connection.Open();
  82
  83                         if (this.withTransaction)
  84                         {
  85                                 this.transaction = this.connection.BeginTransaction();
  86                         }
  87                 }
  88
  89                 [TearDown]
  90                 public virtual void TearDown()
  91                 {
  92                         if (this.withTransaction)
  93                         {
  94                                 try
  95                                 {
  96                                         transaction.Commit();
  97                                 }
  98                                 catch
  99                                 {
 100                                 }
 101                         }
 102                         connection.Close();
 103                 }
 104
 105                 #endregion
 106
 107                 #region Database Creation Methods
 108
 109                 private static void CreateDatabase(string connectionString)
 110                 {
 111                         FbConnection.CreateDatabase(
 112                                 connectionString,
 113                                 Convert.ToInt32(ConfigurationSettings.AppSettings["PageSize"]),
 114                                 Boolean.Parse(ConfigurationSettings.AppSettings["ForcedWrite"]),
 115                                 true);
 116                 }
 117
 118                 private static void CreateTables(string connectionString)
 119                 {
 120                         FbConnection connection = new FbConnection(connectionString);
 121                         connection.Open();
 122
 123                         StringBuilder commandText = new StringBuilder();
 124
 125                         // Table for general purpouse tests
 126                         commandText.Append("CREATE TABLE TEST (");
 127                         commandText.Append("INT_FIELD        INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
 128                         commandText.Append("CHAR_FIELD       CHAR(30),");
 129                         commandText.Append("VARCHAR_FIELD    VARCHAR(100),");
 130                         commandText.Append("BIGINT_FIELD     BIGINT,");
 131                         commandText.Append("SMALLINT_FIELD   SMALLINT,");
 132                         commandText.Append("DOUBLE_FIELD     DOUBLE PRECISION,");
 133                         commandText.Append("FLOAT_FIELD          FLOAT,");
 134                         commandText.Append("NUMERIC_FIELD    NUMERIC(15,2),");
 135                         commandText.Append("DECIMAL_FIELD    DECIMAL(15,2),");
 136                         commandText.Append("DATE_FIELD       DATE,");
 137                         commandText.Append("TIME_FIELD       TIME,");
 138                         commandText.Append("TIMESTAMP_FIELD  TIMESTAMP,");
 139                         commandText.Append("CLOB_FIELD       BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
 140                         commandText.Append("BLOB_FIELD       BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
 141                         commandText.Append("IARRAY_FIELD     INTEGER [0:3],");
 142                         commandText.Append("SARRAY_FIELD     SMALLINT [0:4],");
 143                         commandText.Append("LARRAY_FIELD     BIGINT [0:5],");
 144                         commandText.Append("FARRAY_FIELD     FLOAT [0:3],");
 145                         commandText.Append("BARRAY_FIELD     DOUBLE PRECISION [1:4],");
 146                         commandText.Append("NARRAY_FIELD     NUMERIC(10,6) [1:4],");
 147                         commandText.Append("DARRAY_FIELD     DATE [1:4],");
 148                         commandText.Append("TARRAY_FIELD     TIME [1:4],");
 149                         commandText.Append("TSARRAY_FIELD    TIMESTAMP [1:4],");
 150                         commandText.Append("CARRAY_FIELD     CHAR(21) [1:4],");
 151                         commandText.Append("VARRAY_FIELD     VARCHAR(30) [1:4],");
 152                         commandText.Append("BIG_ARRAY        INTEGER [1:32767],");
 153                         commandText.Append("EXPR_FIELD       COMPUTED BY (smallint_field * 1000),");
 154                         commandText.Append("CS_FIELD         CHAR(1) CHARACTER SET UNICODE_FSS,");
 155                         commandText.Append("UCCHAR_ARRAY     CHAR(10) [1:10] CHARACTER SET UNICODE_FSS);");
 156
 157                         FbCommand command = new FbCommand(commandText.ToString(), connection);
 158                         command.ExecuteNonQuery();
 159                         command.Dispose();
 160
 161                         connection.Close();
 162                 }
 163
 164                 private static void CreateProcedures(string connectionString)
 165                 {
 166                         FbConnection connection = new FbConnection(connectionString);
 167                         connection.Open();
 168
 169                         StringBuilder commandText = new StringBuilder();
 170
 171                         // SELECT_DATA
 172                         commandText = new StringBuilder();
 173
 174                         commandText.Append("CREATE PROCEDURE SELECT_DATA  \r\n");
 175                         commandText.Append("RETURNS ( \r\n");
 176                         commandText.Append("INT_FIELD INTEGER, \r\n");
 177                         commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
 178                         commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
 179                         commandText.Append("AS \r\n");
 180                         commandText.Append("begin \r\n");
 181                         commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
 182                         commandText.Append("DO \r\n");
 183                         commandText.Append("SUSPEND; \r\n");
 184                         commandText.Append("end;");
 185
 186                         FbCommand command = new FbCommand(commandText.ToString(), connection);
 187                         command.ExecuteNonQuery();
 188                         command.Dispose();
 189
 190                         // GETRECORDCOUNT
 191                         commandText = new StringBuilder();
 192
 193                         commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
 194                         commandText.Append("RETURNS ( \r\n");
 195                         commandText.Append("RECCOUNT SMALLINT) \r\n");
 196                         commandText.Append("AS \r\n");
 197                         commandText.Append("begin \r\n");
 198                         commandText.Append("for select count(*) from test into :reccount \r\n");
 199                         commandText.Append("do \r\n");
 200                         commandText.Append("suspend; \r\n");
 201                         commandText.Append("end\r\n");
 202
 203                         command = new FbCommand(commandText.ToString(), connection);
 204                         command.ExecuteNonQuery();
 205                         command.Dispose();
 206
 207                         // GETVARCHARFIELD
 208                         commandText = new StringBuilder();
 209
 210                         commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
 211                         commandText.Append("ID INTEGER)\r\n");
 212                         commandText.Append("RETURNS (\r\n");
 213                         commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
 214                         commandText.Append("AS\r\n");
 215                         commandText.Append("begin\r\n");
 216                         commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
 217                         commandText.Append("do\r\n");
 218                         commandText.Append("suspend;\r\n");
 219                         commandText.Append("end\r\n");
 220
 221                         command = new FbCommand(commandText.ToString(), connection);
 222                         command.ExecuteNonQuery();
 223                         command.Dispose();
 224
 225                         // GETASCIIBLOB
 226                         commandText = new StringBuilder();
 227
 228                         commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
 229                         commandText.Append("ID INTEGER)\r\n");
 230                         commandText.Append("RETURNS (\r\n");
 231                         commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
 232                         commandText.Append("AS\r\n");
 233                         commandText.Append("begin\r\n");
 234                         commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
 235                         commandText.Append("do\r\n");
 236                         commandText.Append("suspend;\r\n");
 237                         commandText.Append("end\r\n");
 238
 239                         command = new FbCommand(commandText.ToString(), connection);
 240                         command.ExecuteNonQuery();
 241                         command.Dispose();
 242
 243                         // DATAREADERTEST
 244                         commandText = new StringBuilder();
 245
 246                         commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
 247                         commandText.Append("RETURNS (\r\n");
 248                         commandText.Append("content VARCHAR(128))\r\n");
 249                         commandText.Append("AS\r\n");
 250                         commandText.Append("begin\r\n");
 251                         commandText.Append("content = 'test';\r\n");
 252                         commandText.Append("end\r\n");
 253
 254                         command = new FbCommand(commandText.ToString(), connection);
 255                         command.ExecuteNonQuery();
 256                         command.Dispose();
 257
 258                         connection.Close();
 259                 }
 260
 261                 private static void CreateTriggers(string connectionString)
 262                 {
 263                         FbConnection connection = new FbConnection(connectionString);
 264                         connection.Open();
 265
 266                         StringBuilder commandText = new StringBuilder();
 267
 268                         // new_row
 269                         commandText = new StringBuilder();
 270
 271                         commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
 272                         commandText.Append("AFTER INSERT POSITION 0\r\n");
 273                         commandText.Append("AS\r\n");
 274                         commandText.Append("BEGIN\r\n");
 275                         commandText.Append("POST_EVENT 'new row';\r\n");
 276                         commandText.Append("END");
 277
 278                         FbCommand command = new FbCommand(commandText.ToString(), connection);
 279                         command.ExecuteNonQuery();
 280                         command.Dispose();
 281
 282                         // update_row
 283
 284                         commandText = new StringBuilder();
 285
 286                         commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
 287                         commandText.Append("AFTER UPDATE POSITION 0\r\n");
 288                         commandText.Append("AS\r\n");
 289                         commandText.Append("BEGIN\r\n");
 290                         commandText.Append("POST_EVENT 'updated row';\r\n");
 291                         commandText.Append("END");
 292
 293                         command = new FbCommand(commandText.ToString(), connection);
 294                         command.ExecuteNonQuery();
 295                         command.Dispose();
 296
 297                         connection.Close();
 298                 }
 299
 300                 private static void InsertTestData(string connectionString)
 301                 {
 302                         FbConnection connection = new FbConnection(connectionString);
 303                         connection.Open();
 304
 305                         StringBuilder commandText = new StringBuilder();
 306
 307                         commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
 308                         commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
 309
 310                         FbTransaction   transaction = connection.BeginTransaction();
 311                         FbCommand               command         = new FbCommand(commandText.ToString(), connection, transaction);
 312
 313                         try
 314                         {
 315                                 // Add command parameters
 316                                 command.Parameters.Add("@int_field"                     , FbDbType.Integer);
 317                                 command.Parameters.Add("@char_field"            , FbDbType.Char);
 318                                 command.Parameters.Add("@varchar_field"         , FbDbType.VarChar);
 319                                 command.Parameters.Add("@bigint_field"          , FbDbType.BigInt);
 320                                 command.Parameters.Add("@smallint_field"        , FbDbType.SmallInt);
 321                                 command.Parameters.Add("@float_field"           , FbDbType.Double);
 322                                 command.Parameters.Add("@double_field"          , FbDbType.Double);
 323                                 command.Parameters.Add("@numeric_field"         , FbDbType.Numeric);
 324                                 command.Parameters.Add("@date_field"            , FbDbType.Date);
 325                                 command.Parameters.Add("@time_Field"            , FbDbType.Time);
 326                                 command.Parameters.Add("@timestamp_field"       , FbDbType.TimeStamp);
 327                                 command.Parameters.Add("@clob_field"            , FbDbType.Text);
 328                                 command.Parameters.Add("@blob_field"            , FbDbType.Binary);
 329
 330                                 command.Prepare();
 331
 332                                 for (int i = 0; i < 100; i++)
 333                                 {
 334                                         command.Parameters["@int_field"].Value          = i;
 335                                         command.Parameters["@char_field"].Value         = "IRow " + i.ToString();
 336                                         command.Parameters["@varchar_field"].Value      = "IRow Number " + i.ToString();
 337                                         command.Parameters["@bigint_field"].Value       = i;
 338                                         command.Parameters["@smallint_field"].Value     = i;
 339                                         command.Parameters["@float_field"].Value        = (float)(i + 10)/5;
 340                                         command.Parameters["@double_field"].Value       = Math.Log(i, 10);
 341                                         command.Parameters["@numeric_field"].Value      = (decimal)(i + 10)/5;
 342                                         command.Parameters["@date_field"].Value         = DateTime.Now;
 343                                         command.Parameters["@time_field"].Value         = DateTime.Now;
 344                                         command.Parameters["@timestamp_field"].Value= DateTime.Now;
 345                                         command.Parameters["@clob_field"].Value         = "IRow Number " + i.ToString();
 346                                         command.Parameters["@blob_field"].Value         = Encoding.Default.GetBytes("IRow Number " + i.ToString());
 347
 348                                         command.ExecuteNonQuery();
 349                                 }
 350
 351                                 // Commit transaction
 352                                 transaction.Commit();
 353                         }
 354                         catch (FbException)
 355                         {
 356                                 transaction.Rollback();
 357                                 throw;
 358                         }
 359                         finally
 360                         {
 361                                 command.Dispose();
 362                                 connection.Close();
 363                         }
 364                 }
 365
 366                 #endregion
 367
 368                 #region ConnectionString Building methods
 369
 370                 public string BuildConnectionString()
 371                 {
 372                         return this.BuildConnectionString(false);
 373                 }
 374
 375                 public string BuildConnectionString(bool pooling)
 376                 {
 377                         FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
 378
 379                         cs.UserID               = ConfigurationSettings.AppSettings["User"];
 380                         cs.Password             = ConfigurationSettings.AppSettings["Password"];
 381                         cs.Database             = ConfigurationSettings.AppSettings["Database"];
 382                         cs.DataSource   = ConfigurationSettings.AppSettings["DataSource"];
 383                         cs.Port                 = Int32.Parse(ConfigurationSettings.AppSettings["Port"]);
 384                         cs.Charset              = ConfigurationSettings.AppSettings["Charset"];
 385                         cs.Pooling              = pooling;
 386                         cs.ServerType   = Int32.Parse(ConfigurationSettings.AppSettings["ServerType"]);
 387
 388                         return cs.ToString();
 389                 }
 390
 391                 public string BuildServicesConnectionString()
 392                 {
 393                         return this.BuildServicesConnectionString(true);
 394                 }
 395
 396                 public string BuildServicesConnectionString(bool includeDatabase)
 397                 {
 398                         FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
 399
 400                         cs.UserID = ConfigurationSettings.AppSettings["User"];
 401                         cs.Password = ConfigurationSettings.AppSettings["Password"];
 402                         if (includeDatabase)
 403                         {
 404                                 cs.Database = ConfigurationSettings.AppSettings["Database"];
 405                         }
 406                         cs.ServerType = Convert.ToInt32(ConfigurationSettings.AppSettings["ServerType"]);
 407
 408                         return cs.ToString();
 409                 }
 410
 411                 #endregion
 412
 413                 #region Methods
 414
 415                 public int GetId()
 416                 {
 417                         RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
 418
 419                         byte[] buffer = new byte[4];
 420
 421                         rng.GetBytes(buffer);
 422
 423                         return BitConverter.ToInt32(buffer, 0);
 424                 }
 425
 426                 #endregion
 427         }
 428 }