Exemplo n.º 1
0
        private void GetInsertData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
        {
            var memoryRepository = new MemoryCatalogueRepository();

            var    sytnaxHelper     = server.GetQuerySyntaxHelper();
            string tableName        = _tableInfo.Name;
            string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");

            var whereStatement = "";

            foreach (ColumnInfo pk in _pks)
            {
                whereStatement += string.Format("{0}.{1} = {2}.{1} AND ", tableName, pk.GetRuntimeName(), archiveTableName);
            }

            var qb = new QueryBuilder(null, null, new[] { _tableInfo });

            qb.TopX = _batchSize;
            qb.AddColumnRange(_tableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());

            //where
            var filter1 = new SpontaneouslyInventedFilter(memoryRepository, null, SpecialFieldNames.DataLoadRunID + " = " + _dataLoadRunID, "DataLoadRunID matches", null, null);
            var filter2 =
                new SpontaneouslyInventedFilter(memoryRepository, null,
                                                string.Format(@" not exists (
select 1 from {0} where {1} {2} < {3}
)", archiveTableName, whereStatement, SpecialFieldNames.DataLoadRunID, _dataLoadRunID),
                                                "Record doesn't exist in archive", null, null);

            qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { filter1, filter2 }, FilterContainerOperation.AND);

            Inserts = new DataTable();
            FillTableWithQueryIfUserConsents(Inserts, qb.SQL, checkNotifier, server);
        }
Exemplo n.º 2
0
        /// <inheritdoc/>
        public void DoImport(out TableInfo tableInfoCreated, out ColumnInfo[] columnInfosCreated)
        {
            string tableName;
            string databaseName;
            var    querySyntaxHelper = _server.GetQuerySyntaxHelper();

            tableName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);

            if (_type == DatabaseType.MicrosoftSQLServer || _type == DatabaseType.PostgreSql)
            {
                tableName += "." + (_importFromSchema ?? querySyntaxHelper.GetDefaultSchemaIfAny()) + ".";
            }
            else if (_type == DatabaseType.MySql || _type == DatabaseType.Oracle)
            {
                tableName += ".";
            }
            else
            {
                throw new NotSupportedException("Unknown Type:" + _type);
            }

            tableName   += querySyntaxHelper.EnsureWrapped(_importTableName);
            databaseName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);

            DiscoveredColumn[] discoveredColumns = _server.ExpectDatabase(_importDatabaseName)
                                                   .ExpectTable(_importTableName, _importFromSchema, _importTableType)
                                                   .DiscoverColumns();

            TableInfo parent = new TableInfo(_repository, tableName)
            {
                DatabaseType = _type,
                Database     = databaseName,
                Server       = _importFromServer,
                Schema       = _importFromSchema,
                IsView       = _importTableType == TableType.View
            };

            parent.SaveToDatabase();

            List <ColumnInfo> newCols = new List <ColumnInfo>();

            foreach (DiscoveredColumn discoveredColumn in discoveredColumns)
            {
                newCols.Add(CreateNewColumnInfo(parent, discoveredColumn));
            }

            tableInfoCreated   = parent;
            columnInfosCreated = newCols.ToArray();

            //if there is a username then we need to associate it with the TableInfo we just created
            if (!string.IsNullOrWhiteSpace(_username))
            {
                DataAccessCredentialsFactory credentialsFactory = new DataAccessCredentialsFactory(_repository);
                credentialsFactory.Create(tableInfoCreated, _username, _password, _usageContext);
            }
        }
Exemplo n.º 3
0
        /// <summary>
        /// Update the database <paramref name="server"/> to redact the <paramref name="failure"/>.
        /// </summary>
        /// <param name="server">Where to connect to get the data, can be null if <see cref="RulesOnly"/> is true</param>
        /// <param name="failure">The failure to redact/create a rule for</param>
        /// <param name="usingRule">Pass null to create a new rule or give value to reuse an existing rule</param>
        public void Update(DiscoveredServer server, Failure failure, IsIdentifiableRule usingRule)
        {
            //theres no rule yet so create one (and add to RedList.yaml)
            if (usingRule == null)
            {
                usingRule = Add(failure, RuleAction.Report);
            }

            //if we are running in rules only mode we don't need to also update the database
            if (RulesOnly)
            {
                return;
            }

            var syntax = server.GetQuerySyntaxHelper();

            //the fully specified name e.g. [mydb]..[mytbl]
            string tableName = failure.Resource;

            var tokens = tableName.Split('.', StringSplitOptions.RemoveEmptyEntries);

            var db = tokens.First();

            tableName = tokens.Last();

            if (string.IsNullOrWhiteSpace(db) || string.IsNullOrWhiteSpace(tableName) || string.Equals(db, tableName))
            {
                throw new NotSupportedException($"Could not understand table name {failure.Resource}, maybe it is not full specified with a valid database and table name?");
            }

            db        = syntax.GetRuntimeName(db);
            tableName = syntax.GetRuntimeName(tableName);

            DiscoveredTable table = server.ExpectDatabase(db).ExpectTable(tableName);

            //if we've never seen this table before
            if (!_primaryKeys.ContainsKey(table))
            {
                var pk = table.DiscoverColumns().SingleOrDefault(k => k.IsPrimaryKey);
                _primaryKeys.Add(table, pk);
            }

            using (var con = server.GetConnection())
            {
                con.Open();

                foreach (var sql in UpdateStrategy.GetUpdateSql(table, _primaryKeys, failure, usingRule))
                {
                    var cmd = server.GetCommand(sql, con);
                    cmd.ExecuteNonQuery();
                }
            }
        }
Exemplo n.º 4
0
        public DiscoveredTable Discover()
        {
            var server = new DiscoveredServer(MappingConnectionString, MappingDatabaseType);

            var idx = MappingTableName.LastIndexOf('.');
            var tableNameUnqualified = MappingTableName.Substring(idx + 1);

            idx = MappingTableName.IndexOf('.');
            if (idx == -1)
            {
                throw new ArgumentException($"MappingTableName did not contain the database/user section:'{MappingTableName}'");
            }

            var databaseName = server.GetQuerySyntaxHelper().GetRuntimeName(MappingTableName.Substring(0, idx));

            if (string.IsNullOrWhiteSpace(databaseName))
            {
                throw new ArgumentException($"Could not get database/username from MappingTableName {MappingTableName}");
            }

            return(server.ExpectDatabase(databaseName).ExpectTable(tableNameUnqualified));
        }
Exemplo n.º 5
0
        public virtual string CreateTrigger(ICheckNotifier notifier, int timeout = 30)
        {
            if (!_primaryKeys.Any())
            {
                throw new TriggerException("There must be at least 1 primary key");
            }

            //if _Archive exists skip creating it
            bool skipCreatingArchive = _archiveTable.Exists();

            //check _Archive does not already exist
            foreach (string forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })
            {
                if (_columns.Any(c => c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))
                {
                    throw new TriggerException("Table " + _table + " already contains a column called " + forbiddenColumnName + " this column is reserved for Archiving");
                }
            }

            bool b_mustCreate_validFrom     = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));
            bool b_mustCreate_dataloadRunId = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) && _createDataLoadRunIdAlso;

            //forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table
            if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)
            {
                throw new TriggerException("Cannot create trigger because table contains " + SpecialFieldNames.ValidFrom + " but not " + SpecialFieldNames.DataLoadRunID + " (ID must be placed before valid from in column order)");
            }

            //must add validFrom outside of transaction if we want SMO to pick it up
            if (b_mustCreate_dataloadRunId)
            {
                _table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true, timeout);
            }

            var syntaxHelper     = _server.GetQuerySyntaxHelper();
            var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
            var nowFunction      = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);

            //must add validFrom outside of transaction if we want SMO to pick it up
            if (b_mustCreate_validFrom)
            {
                _table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, timeout);
            }

            //if we created columns we need to update _column
            if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom)
            {
                _columns = _table.DiscoverColumns();
            }

            string sql = WorkOutArchiveTableCreationSQL();

            if (!skipCreatingArchive)
            {
                using (var con = _server.GetConnection())
                {
                    con.Open();

                    var cmdCreateArchive = _server.GetCommand(sql, con);

                    cmdCreateArchive.ExecuteNonQuery();

                    _archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true, timeout);
                    _archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true, timeout);
                    _archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true, timeout);
                }
            }

            return(sql);
        }
Exemplo n.º 6
0
        public override int UploadImpl(DataTable dt)
        {
            //don't run an insert if there are 0 rows
            if (dt.Rows.Count == 0)
            {
                return(0);
            }

            var syntaxHelper = _server.GetQuerySyntaxHelper();
            var tt           = syntaxHelper.TypeTranslater;

            //if the column name is a reserved keyword e.g. "Comment" we need to give it a new name
            Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName);

            int affectedRows = 0;

            var mapping = GetMapping(dt.Columns.Cast <DataColumn>());

            var dateColumns = new HashSet <DataColumn>();

            var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})",
                                    string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')),
                                    string.Join(",", mapping.Keys.Select(c => parameterNames[c]))
                                    );


            using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection))
            {
                //send all the data at once
                cmd.ArrayBindCount = dt.Rows.Count;

                foreach (var kvp in mapping)
                {
                    var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value);
                    p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType);

                    if (p.DbType == DbType.DateTime)
                    {
                        dateColumns.Add(kvp.Key);
                    }
                }

                var values = new Dictionary <DataColumn, List <object> >();

                foreach (DataColumn c in mapping.Keys)
                {
                    values.Add(c, new List <object>());
                }


                foreach (DataRow dataRow in dt.Rows)
                {
                    //populate parameters for current row
                    foreach (var col in mapping.Keys)
                    {
                        var val = dataRow[col];

                        if (val is string && string.IsNullOrWhiteSpace((string)val))
                        {
                            val = null;
                        }
                        else
                        if (val == null || val == DBNull.Value)
                        {
                            val = null;
                        }
                        else if (dateColumns.Contains(col))
                        {
                            if (val is string s)
                            {
                                val = (DateTime)DateTimeDecider.Parse(s);
                            }
                            else
                            {
                                val = Convert.ToDateTime(dataRow[col]);
                            }
                        }


                        values[col].Add(val);
                    }
                }

                foreach (DataColumn col in mapping.Keys)
                {
                    var param = cmd.Parameters[parameterNames[col]];
                    param.Value = values[col].ToArray();
                }

                //send query
                affectedRows += cmd.ExecuteNonQuery();
            }
            return(affectedRows);
        }
Exemplo n.º 7
0
        private void GetUpdatetData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
        {
            var sytnaxHelper = server.GetQuerySyntaxHelper();

            string tableName        = _tableInfo.Name;
            string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");

            var whereStatement = string.Join(" AND ", _pks.Select(pk => string.Format("{0}.{1} = {2}.{1} ", tableName, pk.GetRuntimeName(), archiveTableName)));

            //hold onto your hats ladies and gentlemen, we start by selecting every column twice with a cross apply:
            //once from the main table e.g. Col1,Col2,Col3
            //then once from the archive e.g. zzArchivezzCol1, zzArchivezzCol2, zzArchivezzCol3 -- notice this is a query alias not affecting anything underlying
            //this lets us then fill 2 DataTables from the combo table we get back with absolute assurity of same row semantically by primary key

            var sql = "";

            switch (sytnaxHelper.DatabaseType)
            {
            case DatabaseType.MicrosoftSQLServer:
                sql = @"
--Records which appear in the archive
SELECT top {0}
{6},
{7}
FROM    {1} 
CROSS APPLY
        (
        SELECT  TOP 1 {2}.*
        FROM    {2}
        WHERE  
		 {3}
		 order by "         + SpecialFieldNames.ValidFrom + @" desc
        ) Archive
where
{1}.{4} = {5}";
                break;

            case DatabaseType.Oracle:
            case DatabaseType.MySql:


                sql  = @"
/*Records which appear in the archive*/
SELECT
{6},
{7}
FROM    
{1}
Join
{2} Archive on " + whereStatement.Replace(archiveTableName, "Archive") + @"
 AND
 Archive.hic_validFrom = (select max(" + SpecialFieldNames.ValidFrom + @") from {2} s where " + whereStatement.Replace(archiveTableName, "Archive").Replace(tableName, "s") + @")
 where
  {1}.{4} = {5}

";
                sql += sytnaxHelper.HowDoWeAchieveTopX(_batchSize).SQL;
                break;

            default:
                throw new ArgumentOutOfRangeException();
            }


            sql = string.Format(sql,
                                _batchSize,                                                        //{0}
                                tableName,                                                         //{1}
                                archiveTableName,                                                  //{2}
                                whereStatement,                                                    //{3}
                                SpecialFieldNames.DataLoadRunID,                                   //{4}
                                _dataLoadRunID,                                                    //{5}
                                GetSharedColumnsSQL(tableName),                                    //{6}
                                GetSharedColumnsSQLWithColumnAliasPrefix("Archive", "zzArchivezz") //{7}
                                );

            DataTable dtComboTable = new DataTable();

            FillTableWithQueryIfUserConsents(dtComboTable, sql, checkNotifier, server);

            Updates_New      = new DataTable();
            Updates_Replaced = new DataTable();

            //add the columns from the combo table to both views
            foreach (DataColumn col in dtComboTable.Columns)
            {
                if (!col.ColumnName.StartsWith("zzArchivezz", StringComparison.InvariantCultureIgnoreCase))
                {
                    Updates_New.Columns.Add(col.ColumnName, col.DataType);
                    Updates_Replaced.Columns.Add(col.ColumnName, col.DataType);
                }
            }

            foreach (DataRow fromRow in dtComboTable.Rows)
            {
                var newRow      = Updates_New.Rows.Add();
                var replacedRow = Updates_Replaced.Rows.Add();

                foreach (DataColumn column in dtComboTable.Columns)
                {
                    if (column.ColumnName.StartsWith("zzArchivezz", StringComparison.InvariantCultureIgnoreCase))
                    {
                        replacedRow[column.ColumnName.Substring("zzArchivezz".Length)] = fromRow[column];
                    }
                    else
                    {
                        newRow[column.ColumnName] = fromRow[column];
                    }
                }
            }
        }