Exemplo n.º 1
0
        static void Main(string[] args)
        {
            // open a dummy connection to satisfy licensing requirements
            var oracleConnection = Environment.GetEnvironmentVariable("ORACLE_CONNECTION");
            var devartLicenseKey = Environment.GetEnvironmentVariable("DEVART_LICENSE_KEY");
            var connection       = new OracleConnection($"{oracleConnection};License Key={devartLicenseKey}");

            connection.Open();

            var sql = @"
;

CREATE TABLE foo(
  new_col CHAR(2 BYTE) NOT NULL,
  CONSTRAINT countries_pk PRIMARY KEY (new_col)
);
            ";

            var script = new OracleScript(sql);

            foreach (OracleSqlStatement statement in script.Statements)
            {
                Console.WriteLine("--------------");
                Console.WriteLine(statement.Text);
            }
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="BaseIntegratedTest" /> class.
        /// </summary>
        /// <param name="tempDbNamePrefix">Prefix for temp database name.</param>
        protected BaseIntegratedTest(string tempDbNamePrefix)
        {
            if (!(tempDbNamePrefix != null))
            {
                throw new ArgumentNullException();
            }
            if (!(tempDbNamePrefix != string.Empty))
            {
                throw new ArgumentException();
            }
            if (!(tempDbNamePrefix.All(char.IsLetterOrDigit)))
            {
                throw new ArgumentException();
            }
            _tempDbNamePrefix = tempDbNamePrefix;
            _databaseName     = _tempDbNamePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "_" + Guid.NewGuid().ToString("N");
            if (!string.IsNullOrWhiteSpace(PostgresScript))
            {
                if (!(tempDbNamePrefix.Length <= 12))                // Max length is 63 (-18 -32).
                {
                    throw new ArgumentException();
                }
                if (!(char.IsLetter(tempDbNamePrefix[0])))           // Database names must have an alphabetic first character.
                {
                    throw new ArgumentException();
                }
                using (var conn = new NpgsqlConnection(ConnectionStringPostgres))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand(string.Format("CREATE DATABASE \"{0}\" ENCODING = 'UTF8' CONNECTION LIMIT = -1;", _databaseName), conn))
                        cmd.ExecuteNonQuery();
                }
                using (var conn = new NpgsqlConnection($"{ConnectionStringPostgres};Database={_databaseName}"))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand("CREATE EXTENSION postgis;", conn)
                    {
                        CommandTimeout = 60
                    })
                        cmd.ExecuteNonQuery();

                    string[] commands = PostgresScript.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var command in commands)
                    {
                        using (var cmd = new NpgsqlCommand(command, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }

                    _dataServices.Add(CreatePostgresDataService($"{ConnectionStringPostgres};Database={_databaseName}"));
                }
            }
            if (!string.IsNullOrWhiteSpace(MssqlScript))
            {
                if (!(tempDbNamePrefix.Length <= 64))// Max is 128.
                {
                    throw new ArgumentException();
                }
                using (var connection = new SqlConnection(ConnectionStringMssql))
                {
                    connection.Open();
                    using (var command = new SqlCommand($"CREATE DATABASE {_databaseName} COLLATE Cyrillic_General_CI_AS", connection))
                        command.ExecuteNonQuery();
                }
                using (var connection = new SqlConnection($"{ConnectionStringMssql};Database={_databaseName}"))
                {
                    connection.Open();
                    using (var command = new SqlCommand(MssqlScript, connection))
                    {
                        command.CommandTimeout = 180;
                        command.ExecuteNonQuery();
                    }
                    _dataServices.Add(CreateMssqlDataService($"{ConnectionStringMssql};Database={_databaseName}"));
                }
            }
            if (!string.IsNullOrWhiteSpace(OracleScript))
            {
                if (!(tempDbNamePrefix.Length <= 8))                // Max length is 30 (-18 -4).
                {
                    throw new ArgumentException();
                }

                using (var connection = new OracleConnection(ConnectionStringOracle))
                {
                    connection.Open();
                    using (var command = connection.CreateCommand())
                    {
                        // "CREATE USER" privileges required.
                        _tmpUserNameOracle  = tempDbNamePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "_" + new Random().Next(9999);
                        command.CommandText = $"CREATE USER {_tmpUserNameOracle} IDENTIFIED BY {_tmpUserNameOracle} DEFAULT TABLESPACE users  quota unlimited on users  TEMPORARY TABLESPACE temp";
                        command.ExecuteNonQuery();

                        // "CREATE SESSION WITH ADMIN OPTION" privileges required.
                        command.CommandText = $"GRANT CREATE SESSION TO {_tmpUserNameOracle}";
                        command.ExecuteNonQuery();
                        command.CommandText = $"GRANT CREATE TABLE TO {_tmpUserNameOracle}";
                        command.ExecuteNonQuery();
                    }
                }
                using (var connection = new OracleConnection($"{ConnectionStringOracleDataSource};User Id={_tmpUserNameOracle};Password={_tmpUserNameOracle};"))
                {
                    connection.Open();
                    using (var command = connection.CreateCommand())
                    {
                        foreach (var cmdText in OracleScript.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
                        {
                            command.CommandText = cmdText.Trim();
                            if (!string.IsNullOrWhiteSpace(command.CommandText))
                            {
                                command.ExecuteNonQuery();
                            }
                        }
                        _dataServices.Add(CreateOracleDataService($"{ConnectionStringOracleDataSource};User Id={_tmpUserNameOracle};Password={_tmpUserNameOracle};"));
                    }
                }
            }
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="BaseIntegratedTest" /> class.
        /// </summary>
        /// <param name="tempDbNamePrefix">Prefix for temp database name.</param>
        protected BaseIntegratedTest(string tempDbNamePrefix)
        {
            Configuration            configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            string                   connectionStringsConfigSectionName = "connectionStrings";
            ConnectionStringsSection connectionStringsSection           = (ConnectionStringsSection)configuration.GetSection(connectionStringsConfigSectionName);

            if (!(tempDbNamePrefix != null))
            {
                throw new ArgumentNullException();
            }
            if (!(tempDbNamePrefix != string.Empty))
            {
                throw new ArgumentException();
            }
            if (!tempDbNamePrefix.All(char.IsLetterOrDigit))
            {
                throw new ArgumentException();
            }
            _tempDbNamePrefix = tempDbNamePrefix;
            _databaseName     = _tempDbNamePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "_" + Guid.NewGuid().ToString("N");
            if (!string.IsNullOrWhiteSpace(PostgresScript) && ConnectionStringPostgres != poolingFalseConst)
            {
                if (!(tempDbNamePrefix.Length <= 12)) // Max length is 63 (-18 -32).
                {
                    throw new ArgumentException();
                }
                if (!char.IsLetter(tempDbNamePrefix[0])) // Database names must have an alphabetic first character.
                {
                    throw new ArgumentException();
                }
                using (var conn = new NpgsqlConnection(ConnectionStringPostgres))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand(string.Format("CREATE DATABASE \"{0}\" ENCODING = 'UTF8' CONNECTION LIMIT = -1;", _databaseName), conn))
                        cmd.ExecuteNonQuery();
                }

                using (var conn = new NpgsqlConnection($"{ConnectionStringPostgres};Database={_databaseName}"))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand(PostgresScript, conn))
                        cmd.ExecuteNonQuery();
                    string connStr = $"{ConnectionStringPostgres};Database={_databaseName}";
                    PostgresDataService dataService = CreatePostgresDataService(connStr);
                    _dataServices.Add(dataService);

                    InitAuditService(dataService);

                    connectionStringsSection.ConnectionStrings.Add(new ConnectionStringSettings($"{dataService.AuditService.AppSetting.AppName}_{dataService.AuditService.AppSetting.AuditConnectionStringName}", connStr));
                }
            }

            if (!string.IsNullOrWhiteSpace(MssqlScript) && ConnectionStringMssql != poolingFalseConst)
            {
                if (!(tempDbNamePrefix.Length <= 64))// Max is 128.
                {
                    throw new ArgumentException();
                }
                using (var connection = new SqlConnection(ConnectionStringMssql))
                {
                    connection.Open();
                    using (var command = new SqlCommand($"CREATE DATABASE {_databaseName} COLLATE Cyrillic_General_CI_AS", connection))
                        command.ExecuteNonQuery();
                }

                using (var connection = new SqlConnection($"{ConnectionStringMssql};Database={_databaseName}"))
                {
                    connection.Open();
                    using (var command = new SqlCommand(MssqlScript, connection))
                    {
                        command.CommandTimeout = 180;
                        command.ExecuteNonQuery();
                    }

                    string           connStr     = $"{ConnectionStringMssql};Database={_databaseName}";
                    MSSQLDataService dataService = CreateMssqlDataService(connStr);
                    _dataServices.Add(dataService);
                    InitAuditService(dataService);
                    connectionStringsSection.ConnectionStrings.Add(new ConnectionStringSettings($"{dataService.AuditService.AppSetting.AppName}_{dataService.AuditService.AppSetting.AuditConnectionStringName}", connStr));
                }
            }

            if (!string.IsNullOrWhiteSpace(OracleScript) && ConnectionStringOracle != poolingFalseConst)
            {
                if (!(tempDbNamePrefix.Length <= 8)) // Max length is 30 (-18 -4).
                {
                    throw new ArgumentException();
                }

                using (var connection = new OracleConnection(ConnectionStringOracle))
                {
                    connection.Open();
                    using (var command = connection.CreateCommand())
                    {
                        // "CREATE USER" privileges required.
                        var doWhile = true;
                        while (doWhile)
                        {
                            _tmpUserNameOracle  = tempDbNamePrefix + "_" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "_" + new Random().Next(9999);
                            command.CommandText = $"CREATE USER {_tmpUserNameOracle} IDENTIFIED BY {_tmpUserNameOracle} DEFAULT TABLESPACE users  quota unlimited on users  TEMPORARY TABLESPACE temp";
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (OracleException ex)
                            {
                                Thread.Sleep(1000);
                                if (ex.Message.Contains("conflicts with another user or role name "))
                                {
                                    continue;
                                }
                                throw;
                            }

                            doWhile = false;
                        }

                        // "CREATE SESSION WITH ADMIN OPTION" privileges required.
                        command.CommandText = $"GRANT CREATE SESSION TO {_tmpUserNameOracle}";
                        command.ExecuteNonQuery();
                        command.CommandText = $"GRANT CREATE TABLE TO {_tmpUserNameOracle}";
                        command.ExecuteNonQuery();
                    }
                }

                using (var connection = new OracleConnection($"{ConnectionStringOracleDataSource};User Id={_tmpUserNameOracle};Password={_tmpUserNameOracle};"))
                {
                    connection.Open();
                    using (var command = connection.CreateCommand())
                    {
                        foreach (var cmdText in OracleScript.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
                        {
                            command.CommandText = cmdText.Trim();
                            if (!string.IsNullOrWhiteSpace(command.CommandText))
                            {
                                command.ExecuteNonQuery();
                            }
                        }

                        string            connStr     = $"{ConnectionStringOracleDataSource};User Id={_tmpUserNameOracle};Password={_tmpUserNameOracle};";
                        OracleDataService dataService = CreateOracleDataService(connStr);
                        _dataServices.Add(dataService);
                        InitAuditService(dataService);
                        connectionStringsSection.ConnectionStrings.Add(new ConnectionStringSettings($"{dataService.AuditService.AppSetting.AppName}_{dataService.AuditService.AppSetting.AuditConnectionStringName}", connStr));
                    }
                }
            }

            configuration.Save();
            ConfigurationManager.RefreshSection(connectionStringsConfigSectionName);
        }