/// <summary>
        /// Defines a new use case in which the given <see cref="AggregateConfiguration"/> will be turned into an SQL query and used to generate rows
        /// that will be released into the pipeline.  The source is fixed the destination and middle components are open.
        /// </summary>
        /// <param name="aggregateConfiguration">The aggregate query that will be run to generate the rows</param>
        /// <param name="constrainByCohort">Only applies if <see cref="AggregateConfiguration"/> is a patient index table, specifying a cohort will only commit rows
        /// in which the patient id appears in the cohort</param>
        /// <param name="table">The destination table in which to put the matched records.
        /// <para> (table does not have to exist yet, you can use <see cref="DiscoveredDatabase.ExpectTable"/> to obtain a reference to a non existant table)</para></param>
        public CreateTableFromAggregateUseCase(AggregateConfiguration aggregateConfiguration, ExtractableCohort constrainByCohort, DiscoveredTable table)
        {
            if (constrainByCohort == null)
            {
                var src = new AggregateConfigurationTableSource();
                src.PreInitialize(aggregateConfiguration, new ThrowImmediatelyDataLoadEventListener());
                src.TableName  = table.GetRuntimeName();
                ExplicitSource = src;
            }
            else
            {
                AddInitializationObject(constrainByCohort);

                var src = new PatientIndexTableSource();
                src.PreInitialize(aggregateConfiguration, new ThrowImmediatelyDataLoadEventListener());
                src.PreInitialize(constrainByCohort, new ThrowImmediatelyDataLoadEventListener());
                src.TableName  = table.GetRuntimeName();
                ExplicitSource = src;
            }

            AddInitializationObject(aggregateConfiguration);
            AddInitializationObject(aggregateConfiguration.Repository);
            AddInitializationObject(table.Database);

            GenerateContext();
        }
        public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
        {
            SqlCommand cmd;

            switch (tableToDrop.TableType)
            {
            case TableType.View:
                if (connection.Database != tableToDrop.Database.GetRuntimeName())
                {
                    connection.ChangeDatabase(tableToDrop.GetRuntimeName());
                }

                if (!connection.Database.ToLower().Equals(tableToDrop.Database.GetRuntimeName().ToLower()))
                {
                    throw new NotSupportedException("Cannot drop view " + tableToDrop + " because it exists in database " + tableToDrop.Database.GetRuntimeName() + " while the current current database connection is pointed at database:" + connection.Database + " (use .ChangeDatabase on the connection first) - SQL Server does not support cross database view dropping");
                }

                cmd = new SqlCommand("DROP VIEW " + tableToDrop.GetRuntimeName(), (SqlConnection)connection);
                break;

            case TableType.Table:
                cmd = new SqlCommand("DROP TABLE " + tableToDrop.GetFullyQualifiedName(), (SqlConnection)connection);
                break;

            case TableType.TableValuedFunction:
                DropFunction(connection, (DiscoveredTableValuedFunction)tableToDrop);
                return;

            default:
                throw new ArgumentOutOfRangeException();
            }

            using (cmd)
                cmd.ExecuteNonQuery();
        }
Exemple #3
0
        public void TestTableInfo(bool usenamer)
        {
            string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
            var    sw       = new StreamWriter(filename);

            sw.WriteLine("name,name2");
            sw.WriteLine("Bob,Munchousain");
            sw.WriteLine("Franky,Hollyw9ood");

            sw.Flush();
            sw.Close();
            sw.Dispose();

            TableInfo ti;

            ColumnInfo[] cols;
            Import(_table, out ti, out cols);

            var attacher = new AnySeparatorFileAttacher();

            attacher.Initialize(LoadDirectory, _database);
            attacher.Separator   = ",";
            attacher.FilePattern = "bob*";
            attacher.TableToLoad = ti;

            INameDatabasesAndTablesDuringLoads namer = null;

            if (usenamer)
            {
                _table.Rename("AAA");
                namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(_database, "AAA");
            }

            var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, namer), ti);

            var exitCode = attacher.Attach(job, new GracefulCancellationToken());

            Assert.AreEqual(ExitCodeType.Success, exitCode);

            using (var con = _database.Server.GetConnection())
            {
                con.Open();
                var r = _database.Server.GetCommand("Select name,name2 from " + _table.GetRuntimeName(), con).ExecuteReader();
                Assert.IsTrue(r.Read());
                Assert.AreEqual("Bob", r["name"]);
                Assert.AreEqual("Munchousain", r["name2"]);

                Assert.IsTrue(r.Read());
                Assert.AreEqual("Franky", r["name"]);
                Assert.AreEqual("Hollyw9ood", r["name2"]);
            }

            attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());

            File.Delete(filename);
        }
        public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
        {
            List <DiscoveredColumn> columns = new List <DiscoveredColumn>();
            var tableName = discoveredTable.GetRuntimeName();

            using (DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand(
                       @"SELECT * FROM information_schema.`COLUMNS` 
WHERE table_schema = @db
  AND table_name = @tbl", connection.Connection))
            {
                cmd.Transaction = connection.Transaction;

                var p = new MySqlParameter("@db", MySqlDbType.String);
                p.Value = discoveredTable.Database.GetRuntimeName();
                cmd.Parameters.Add(p);

                p       = new MySqlParameter("@tbl", MySqlDbType.String);
                p.Value = discoveredTable.GetRuntimeName();
                cmd.Parameters.Add(p);

                using (DbDataReader r = cmd.ExecuteReader())
                {
                    if (!r.HasRows)
                    {
                        throw new Exception("Could not find any columns for table " + tableName + " in database " + database);
                    }

                    while (r.Read())
                    {
                        var toAdd = new DiscoveredColumn(discoveredTable, (string)r["COLUMN_NAME"], YesNoToBool(r["IS_NULLABLE"]));

                        if (r["COLUMN_KEY"].Equals("PRI"))
                        {
                            toAdd.IsPrimaryKey = true;
                        }

                        toAdd.IsAutoIncrement = r["Extra"] as string == "auto_increment";
                        toAdd.Collation       = r["COLLATION_NAME"] as string;

                        //todo the only way to know if something in MySql is unicode is by r["character_set_name"]


                        toAdd.DataType = new DiscoveredDataType(r, TrimIntDisplayValues(r["COLUMN_TYPE"].ToString()), toAdd);
                        columns.Add(toAdd);
                    }

                    r.Close();
                }
            }


            return(columns.ToArray());
        }
Exemple #5
0
 /// <summary>
 /// Returns true if the TableInfo is a reference to the discovered live table (same database, same table name, same server)
 /// <para>By default servername is not checked since you can have server aliases e.g. localhost\sqlexpress could be the same as 127.0.0.1\sqlexpress</para>
 /// </summary>
 /// <param name="discoveredTable">Pass true to also check the servername is EXACTLY the same (dangerous due to the fact that servers can be accessed by hostname or IP etc)</param>
 /// <param name="alsoCheckServer"></param>
 /// <returns></returns>
 public bool Is(DiscoveredTable discoveredTable, bool alsoCheckServer = false)
 {
     return(GetRuntimeName().Equals(discoveredTable.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase) &&
            GetDatabaseRuntimeName().Equals(discoveredTable.Database.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase) &&
            DatabaseType == discoveredTable.Database.Server.DatabaseType &&
            (!alsoCheckServer || discoveredTable.Database.Server.Name.Equals(Server, StringComparison.CurrentCultureIgnoreCase)));
 }
Exemple #6
0
        /// <summary>
        /// Creates the <paramref name="tableTemplate"/> in the database location <paramref name="expectedTable"/>.
        /// </summary>
        /// <param name="expectedTable"></param>
        /// <param name="tableTemplate"></param>
        public void CreateTable(DiscoveredTable expectedTable, ImageTableTemplate tableTemplate)
        {
            expectedTable.Database.CreateTable(expectedTable.GetRuntimeName(), tableTemplate.GetColumns(expectedTable.Database.Server.DatabaseType));

            if (!expectedTable.Exists())
            {
                throw new Exception("Table did not exist after issuing create statement!");
            }
        }
Exemple #7
0
        public TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true)
        {
            _server       = table.Database.Server;
            _table        = table;
            _archiveTable = _table.Database.ExpectTable(table.GetRuntimeName() + "_Archive", table.Schema);
            _columns      = table.DiscoverColumns();
            _primaryKeys  = _columns.Where(c => c.IsPrimaryKey).ToArray();

            _createDataLoadRunIdAlso = createDataLoadRunIDAlso;
        }
        /// <summary>
        /// Returns the table name suitable for being passed into OBJECT_ID including schema if any
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private string GetObjectName(DiscoveredTable table)
        {
            var objectName = table.GetRuntimeName();

            if (table.Schema != null)
            {
                return(table.Schema + "." + objectName);
            }

            return(objectName);
        }
        protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
        {
            string oldName = "[" + discoveredTable.GetRuntimeName() + "]";

            if (!string.IsNullOrWhiteSpace(discoveredTable.Schema))
            {
                oldName = "[" + discoveredTable.Schema + "]." + oldName;
            }

            return(string.Format("exec sp_rename '{0}', '{1}'", oldName, newName));
        }
Exemple #10
0
        /// <summary>
        /// Returns the table name suitable for being passed into OBJECT_ID including schema if any
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private string GetObjectName(DiscoveredTable table)
        {
            var syntax = table.GetQuerySyntaxHelper();

            var objectName = syntax.EnsureWrapped(table.GetRuntimeName());

            if (table.Schema != null)
            {
                return(syntax.EnsureWrapped(table.Schema) + "." + objectName);
            }

            return(objectName);
        }
Exemple #11
0
        public void Add(DiscoveredTable discoveredTable)
        {
            if (items.Any(i => i.Tag.Equals(discoveredTable)))
            {
                return;
            }

            var snip = new SubstringAutocompleteItem(discoveredTable.GetRuntimeName());

            snip.MenuText   = discoveredTable.GetRuntimeName();        //name of table
            snip.Text       = discoveredTable.GetFullyQualifiedName(); //full SQL
            snip.Tag        = discoveredTable;                         //record object for future reference
            snip.ImageIndex = GetIndexFor(discoveredTable, RDMPConcept.TableInfo.ToString());


            AddUnlessDuplicate(snip);

            DiscoveredColumn[] columns = null;
            try
            {
                if (discoveredTable.Exists())
                {
                    columns = discoveredTable.DiscoverColumns();
                }
            }
            catch (Exception)
            {
                //couldn't load nevermind
            }

            if (columns != null)
            {
                foreach (var col in columns)
                {
                    Add(col);
                }
            }
        }
        public ArbitraryTableExtractionUICollection(DiscoveredTable table) : this()
        {
            _table = table;
            _arguments.Add(ServerKey, _table.Database.Server.Name);
            _arguments.Add(DatabaseKey, _table.Database.GetRuntimeName());
            _arguments.Add(TableKey, _table.GetRuntimeName());
            DatabaseType = table.Database.Server.DatabaseType;

            _arguments.Add(DatabaseTypeKey, DatabaseType.ToString());


            Username = table.Database.Server.ExplicitUsernameIfAny;
            Password = table.Database.Server.ExplicitPasswordIfAny;
        }
Exemple #13
0
 /// <summary>
 /// Prepares to import a reference to the <paramref name="table"/> as <see cref="TableInfo"/> and <see cref="ColumnInfo"/> in the RDMP <paramref name="catalogueRepository"/>
 /// </summary>
 /// <param name="catalogueRepository"></param>
 /// <param name="table"></param>
 /// <param name="usageContext"></param>
 public TableInfoImporter(ICatalogueRepository catalogueRepository, DiscoveredTable table, DataAccessContext usageContext = DataAccessContext.Any)
     : this(catalogueRepository,
            table.Database.Server.Name,
            table.Database.GetRuntimeName(),
            table.GetRuntimeName(),
            table.Database.Server.DatabaseType,
            table.Database.Server.ExplicitUsernameIfAny,
            table.Database.Server.ExplicitPasswordIfAny,
            usageContext,
            table.Schema,
            table.TableType)
 {
     _usageContext = DataAccessContext.Any;
     InitializeBuilder();
 }
Exemple #14
0
        private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
        {
            var from = tableInfo.Discover(DataAccessContext.DataLoad);

            //create a RAW table schema called TableName_Isolation
            var cloner = new TableInfoCloneOperation(null, null, LoadBubble.Live);

            cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);

            if (!toCreate.Exists())
            {
                throw new Exception(string.Format("Table '{0}' did not exist after issuing create command", toCreate));
            }

            //Add the data load run id
            toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
        }
Exemple #15
0
        private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
        {
            var from = tableInfo.Discover(DataAccessContext.DataLoad);

            //create a RAW table schema called TableName_Isolation
            var cloner = new TableInfoCloneOperation(new HICDatabaseConfiguration(toCreate.Database.Server), tableInfo, LoadBubble.Live, _job ?? (IDataLoadEventListener) new ThrowImmediatelyDataLoadEventListener());

            cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);

            if (!toCreate.Exists())
            {
                throw new Exception($"Table '{toCreate}' did not exist after issuing create command");
            }

            //Add the data load run id
            toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
        }
Exemple #16
0
        public override void WriteItems(DataTable items)
        {
            StripWhiteSpace(items);

            items.TableName = _reportName;

            if (!_tbl.Exists())
            {
                _tbl.Database.CreateTable(_tbl.GetRuntimeName(), items);
            }
            else
            {
                using (var insert = _tbl.BeginBulkInsert())
                {
                    insert.Upload(items);
                }
            }
        }
        public void DeleteRows(DiscoveredTable tbl, ColumnInfo[] primaryKeys, IDataLoadEventListener listener)
        {
            string join = string.Join(" AND ", primaryKeys.Select(k => "t1." + k.GetRuntimeName() + "=t2." + k.GetRuntimeName()));


            string t1DotColumn = "t1." + ColumnToResolveOn.GetRuntimeName(_loadStage);
            string t2DotColumn = "t2." + ColumnToResolveOn.GetRuntimeName(_loadStage);

            //FYI - we are considering whether to delete records from table {0}

            string deleteConditional =
                PreferNulls?
                //delete rows {0} where {0} is not null and {1} is null - leaving only the null records {1}
                string.Format("({0} IS NOT NULL AND {1} IS NULL)", t1DotColumn, t2DotColumn):
                string.Format("({1} IS NOT NULL AND {0} IS NULL)", t1DotColumn, t2DotColumn);

            deleteConditional += " OR ";

            deleteConditional += PreferLargerValues
                                 //delete rows {0} where {0} less than {1} - leaving only the greater records {1}
                ? string.Format("({0} <> {1} AND {0} < {1})", t1DotColumn, t2DotColumn)
                : string.Format("({0} <> {1} AND {1} < {0})", t1DotColumn, t2DotColumn);



            var sql = string.Format(@"DELETE t1 FROM {0} t1
  JOIN {0} t2
  ON {1}
  AND ({2})",
                                    tbl.GetRuntimeName(), join, deleteConditional);

            using (var con = tbl.Database.Server.GetConnection())
            {
                con.Open();
                using (var cmd = tbl.Database.Server.GetCommand(sql, con))
                {
                    cmd.CommandTimeout = Timeout;

                    int affectedRows = cmd.ExecuteNonQuery();
                    listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Deleted " + affectedRows + " rows"));
                }
            }
        }
Exemple #18
0
        private void CreateIndex(DiscoveredTable table, DiscoveredColumn onColumn, string configurationName)
        {
            string pkCreationSql = "ALTER TABLE " + table.GetRuntimeName() + " ADD CONSTRAINT PK_" + table.GetRuntimeName() + " PRIMARY KEY CLUSTERED (" + onColumn.GetRuntimeName() + ")";

            try
            {
                var server = table.Database.Server;
                using (var con = server.GetConnection())
                {
                    con.Open();

                    var cmd = server.GetCommand(pkCreationSql, con);
                    cmd.CommandTimeout = Timeout;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Failed to create unique primary key on the results of AggregateConfiguration " + configurationName + ".  The SQL that failed was:" + Environment.NewLine + pkCreationSql, e);
            }
        }
        public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
        {
            List <DiscoveredColumn> columns = new List <DiscoveredColumn>();
            var tableName = discoveredTable.GetRuntimeName();

            DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand("SHOW FULL COLUMNS FROM `" + database + "`.`" + tableName + "`", connection.Connection);

            cmd.Transaction = connection.Transaction;

            using (DbDataReader r = cmd.ExecuteReader())
            {
                if (!r.HasRows)
                {
                    throw new Exception("Could not find any columns for table " + tableName + " in database " + database);
                }

                while (r.Read())
                {
                    var toAdd = new DiscoveredColumn(discoveredTable, (string)r["Field"], YesNoToBool(r["Null"]));

                    if (r["Key"].Equals("PRI"))
                    {
                        toAdd.IsPrimaryKey = true;
                    }

                    toAdd.IsAutoIncrement = r["Extra"] as string == "auto_increment";
                    toAdd.Collation       = r["Collation"] as string;

                    toAdd.DataType = new DiscoveredDataType(r, TrimIntDisplayValues(r["Type"].ToString()), toAdd);
                    columns.Add(toAdd);
                }

                r.Close();
            }

            return(columns.ToArray());
        }
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
        {
            string sql = @"SELECT 
u.CONSTRAINT_NAME,
u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME,
u.REFERENCED_TABLE_SCHEMA,
u.REFERENCED_TABLE_NAME,
u.REFERENCED_COLUMN_NAME,
c.DELETE_RULE
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME
WHERE
  u.REFERENCED_TABLE_SCHEMA = @db AND
  u.REFERENCED_TABLE_NAME = @tbl";

            var cmd = new MySqlCommand(sql, (MySqlConnection)connection);

            var p = new MySqlParameter("@db", MySqlDbType.String);

            p.Value = table.Database.GetRuntimeName();
            cmd.Parameters.Add(p);

            p       = new MySqlParameter("@tbl", MySqlDbType.String);
            p.Value = table.GetRuntimeName();
            cmd.Parameters.Add(p);

            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var fkName = r["CONSTRAINT_NAME"].ToString();

                    DiscoveredRelationship current;

                    //could be a 2+ columns foreign key?
                    if (toReturn.ContainsKey(fkName))
                    {
                        current = toReturn[fkName];
                    }
                    else
                    {
                        var pkDb        = r["REFERENCED_TABLE_SCHEMA"].ToString();
                        var pkTableName = r["REFERENCED_TABLE_NAME"].ToString();

                        var fkDb        = r["TABLE_SCHEMA"].ToString();
                        var fkTableName = r["TABLE_NAME"].ToString();

                        var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
                        var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);

                        //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
                        var deleteRuleString = r["DELETE_RULE"].ToString();

                        CascadeRule deleteRule = CascadeRule.Unknown;

                        if (deleteRuleString == "CASCADE")
                        {
                            deleteRule = CascadeRule.Delete;
                        }
                        else if (deleteRuleString == "NO ACTION")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "RESTRICT")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "SET NULL")
                        {
                            deleteRule = CascadeRule.SetNull;
                        }
                        else if (deleteRuleString == "SET DEFAULT")
                        {
                            deleteRule = CascadeRule.SetDefault;
                        }

                        current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                        toReturn.Add(current.Name, current);
                    }

                    current.AddKeys(r["REFERENCED_COLUMN_NAME"].ToString(), r["COLUMN_NAME"].ToString(), transaction);
                }
            }

            return(toReturn.Values.ToArray());
        }
Exemple #21
0
        public string GetCode()
        {
            var columns = _table.DiscoverColumns();

            if (!columns.Any(c => c.GetRuntimeName().Equals("ID")))
            {
                throw new CodeGenerationException("Table must have an ID automnum column to become an IMapsDirectlyToDatabaseTable class");
            }

            StringBuilder classStart = new StringBuilder();

            classStart.Append("public class " + _table.GetRuntimeName() + ": DatabaseEntity");

            bool isINamed = columns.Any(c => c.GetRuntimeName() == "Name");

            if (isINamed)
            {
                classStart.Append(",INamed");
            }

            classStart.AppendLine();
            classStart.AppendLine("{");

            StringBuilder databaseFields = new StringBuilder();

            databaseFields.AppendLine("\t#region Database Properties");
            databaseFields.AppendLine();

            StringBuilder databaseProperties = new StringBuilder();

            StringBuilder constructors = new StringBuilder();

            constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository/*, TODO Required Construction Properties For NEW*/)");
            constructors.AppendLine(@"  {
        repository.InsertAndHydrate(this,new Dictionary<string, object>()
        {
            //TODO Any parameters here as key value pairs
        });

        if (ID == 0 || Repository != repository)
            throw new ArgumentException(""Repository failed to properly hydrate this class"");
    }");


            constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository, DbDataReader r): base(repository, r)");
            constructors.AppendLine("\t{");

            foreach (var col in columns.Where(c => c.GetRuntimeName() != "ID"))
            {
                string setCode;
                var    type         = GetCSharpTypeFor(col, out setCode);
                var    propertyName = col.GetRuntimeName();
                var    fieldString  = col.GetRuntimeName();

                //cammel case it
                fieldString = "_" + fieldString.Substring(0, 1).ToLower() + fieldString.Substring(1);

                databaseFields.AppendLine("\tprivate " + type + " " + fieldString + ";");

                databaseProperties.AppendLine("\tpublic " + type + " " + propertyName);
                databaseProperties.AppendLine("\t{");
                databaseProperties.AppendLine("\t\tget { return " + fieldString + ";}");
                databaseProperties.AppendLine("\t\tset { SetField(ref " + fieldString + ", value);}");
                databaseProperties.AppendLine("\t}");

                constructors.AppendLine("\t\t" + propertyName + " = " + setCode);
            }

            databaseFields.AppendLine("\t#endregion");
            databaseFields.AppendLine();

            constructors.AppendLine("\t}");

            if (isINamed)
            {
                constructors.AppendLine("\t" + @"public override string ToString()
    {
        return Name;
    }");
            }

            return(classStart.ToString() + databaseFields + databaseProperties + constructors + "}");
        }
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
        {
            var toReturn = new Dictionary <string, DiscoveredRelationship>();

            string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema";

            using (DbCommand cmd = table.GetCommand(sql, connection))
            {
                if (transaction != null)
                {
                    cmd.Transaction = transaction.Transaction;
                }

                var p = cmd.CreateParameter();
                p.ParameterName = "@table";
                p.Value         = table.GetRuntimeName();
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                p = cmd.CreateParameter();
                p.ParameterName = "@schema";
                p.Value         = table.Schema ?? "dbo";
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                p = cmd.CreateParameter();
                p.ParameterName = "@database";
                p.Value         = table.Database.GetRuntimeName();
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                using (DataTable dt = new DataTable())
                {
                    var da = table.Database.Server.GetDataAdapter(cmd);
                    da.Fill(dt);

                    foreach (DataRow r in dt.Rows)
                    {
                        var fkName = r["FK_NAME"].ToString();

                        DiscoveredRelationship current;

                        //could be a 2+ columns foreign key?
                        if (toReturn.ContainsKey(fkName))
                        {
                            current = toReturn[fkName];
                        }
                        else
                        {
                            var pkdb        = r["PKTABLE_QUALIFIER"].ToString();
                            var pkschema    = r["PKTABLE_OWNER"].ToString();
                            var pktableName = r["PKTABLE_NAME"].ToString();

                            var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema);

                            var fkdb        = r["FKTABLE_QUALIFIER"].ToString();
                            var fkschema    = r["FKTABLE_OWNER"].ToString();
                            var fktableName = r["FKTABLE_NAME"].ToString();

                            var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema);

                            var         deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"]);
                            CascadeRule deleteRule    = CascadeRule.Unknown;


                            if (deleteRuleInt == 0)
                            {
                                deleteRule = CascadeRule.Delete;
                            }
                            else if (deleteRuleInt == 1)
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleInt == 2)
                            {
                                deleteRule = CascadeRule.SetNull;
                            }
                            else if (deleteRuleInt == 3)
                            {
                                deleteRule = CascadeRule.SetDefault;
                            }


                            /*
                             * https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017
                             *
                             * 0=CASCADE changes to foreign key.
                             * 1=NO ACTION changes if foreign key is present.
                             * 2 = set null
                             * 3 = set default*/

                            current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                            toReturn.Add(current.Name, current);
                        }

                        current.AddKeys(r["PKCOLUMN_NAME"].ToString(), r["FKCOLUMN_NAME"].ToString(), transaction);
                    }
                }
            }

            return(toReturn.Values.ToArray());
        }
        public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
        {
            List <DiscoveredColumn> toReturn = new List <DiscoveredColumn>();

            using (DbCommand cmd = discoveredTable.GetCommand("use [" + database + @"];
SELECT  
sys.columns.name AS COLUMN_NAME,
 sys.types.name AS TYPE_NAME,
  sys.columns.collation_name AS COLLATION_NAME,
   sys.columns.max_length as LENGTH,
   sys.columns.scale as SCALE,
    sys.columns.is_identity,
    sys.columns.is_nullable,
   sys.columns.precision as PRECISION,
sys.columns.collation_name
from sys.columns 
join 
sys.types on sys.columns.user_type_id = sys.types.user_type_id
where object_id = OBJECT_ID(@tableName)", connection.Connection, connection.Transaction))
            {
                var p = cmd.CreateParameter();
                p.ParameterName = "@tableName";
                p.Value         = GetObjectName(discoveredTable);
                cmd.Parameters.Add(p);

                using (var r = cmd.ExecuteReader())
                    while (r.Read())
                    {
                        bool isNullable = Convert.ToBoolean(r["is_nullable"]);

                        //if it is a table valued function prefix the column name with the table valued function name
                        string columnName = discoveredTable is DiscoveredTableValuedFunction
                            ? discoveredTable.GetRuntimeName() + "." + r["COLUMN_NAME"]
                            : r["COLUMN_NAME"].ToString();

                        var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable);
                        toAdd.IsAutoIncrement = Convert.ToBoolean(r["is_identity"]);

                        toAdd.DataType  = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd);
                        toAdd.Collation = r["collation_name"] as string;
                        toReturn.Add(toAdd);
                    }
            }

            if (!toReturn.Any())
            {
                throw new Exception("Could not find any columns in table " + discoveredTable);
            }

            //don't bother looking for pks if it is a table valued function
            if (discoveredTable is DiscoveredTableValuedFunction)
            {
                return(toReturn.ToArray());
            }

            var pks = ListPrimaryKeys(connection, discoveredTable);

            foreach (DiscoveredColumn c in toReturn)
            {
                if (pks.Any(pk => pk.Equals(c.GetRuntimeName())))
                {
                    c.IsPrimaryKey = true;
                }
            }


            return(toReturn.ToArray());
        }
Exemple #24
0
        protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table)
        {
            var server = table.Database.Server;

            job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to run Coalese on table " + table));

            var allCols = table.DiscoverColumns();

            var pkColumnInfos = tableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).Select(c => c.GetRuntimeName()).ToArray();
            var nonPks        = allCols.Where(c => !pkColumnInfos.Contains(c.GetRuntimeName())).ToArray();
            var pks           = allCols.Except(nonPks).ToArray();

            if (!pkColumnInfos.Any())
            {
                throw new Exception("Table '" + tableInfo + "' has no IsPrimaryKey columns");
            }

            if (allCols.Length == pkColumnInfos.Length)
            {
                job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping Coalesce on table " + table + " because it has no non primary key columns"));
                return;
            }

            //Get an update command for each non primary key column
            Dictionary <string, Task <int> > sqlCommands = new Dictionary <string, Task <int> >();

            foreach (DiscoveredColumn nonPk in nonPks)
            {
                sqlCommands.Add(GetCommand(table, pks, nonPk), null);
            }

            server.EnableAsync();

            using (var con = table.Database.Server.GetConnection())
            {
                con.Open();

                if (CreateIndex)
                {
                    var idxCmd = server.GetCommand(string.Format(@"CREATE INDEX IX_PK_{0} ON {0}({1});", table.GetRuntimeName(), string.Join(",", pks.Select(p => p.GetRuntimeName()))), con);
                    idxCmd.CommandTimeout = Timeout;
                    idxCmd.ExecuteNonQuery();
                }

                foreach (var sql in sqlCommands.Keys.ToArray())
                {
                    var cmd = server.GetCommand(sql, con);
                    cmd.CommandTimeout = Timeout;
                    sqlCommands[sql]   = cmd.ExecuteNonQueryAsync();
                }

                Task.WaitAll(sqlCommands.Values.ToArray());
            }

            int affectedRows = sqlCommands.Values.Sum(t => t.Result);

            job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Coalesce on table '" + table + "' completed (" + affectedRows + " rows affected)"));
        }
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
                                                                       IManagedTransaction transaction = null)
        {
            string sql = @"
SELECT a.table_name
     , a.column_name
     , a.constraint_name
     , c.owner
     , c.delete_rule
     , c.r_owner
     , c_pk.table_name      r_table_name
     , c_pk.constraint_name r_pk
     , cc_pk.column_name    r_column_name
  FROM all_cons_columns a
  JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
  JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
  JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            AND cc_pk.position = a.position)
 WHERE c.constraint_type = 'R'
AND  UPPER(c.r_owner) =  UPPER(:DatabaseName)
AND  UPPER(c_pk.table_name) =  UPPER(:TableName)";


            var cmd = new OracleCommand(sql, (OracleConnection)connection);

            var p = new OracleParameter(":DatabaseName", OracleDbType.Varchar2);

            p.Value = table.Database.GetRuntimeName();
            cmd.Parameters.Add(p);

            p       = new OracleParameter(":TableName", OracleDbType.Varchar2);
            p.Value = table.GetRuntimeName();
            cmd.Parameters.Add(p);

            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var fkName = r["constraint_name"].ToString();

                    DiscoveredRelationship current;

                    //could be a 2+ columns foreign key?
                    if (toReturn.ContainsKey(fkName))
                    {
                        current = toReturn[fkName];
                    }
                    else
                    {
                        var pkDb        = r["r_owner"].ToString();
                        var pkTableName = r["r_table_name"].ToString();

                        var fkDb        = r["owner"].ToString();
                        var fkTableName = r["table_name"].ToString();

                        var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
                        var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);

                        //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
                        var deleteRuleString = r["delete_rule"].ToString();

                        CascadeRule deleteRule = CascadeRule.Unknown;

                        if (deleteRuleString == "CASCADE")
                        {
                            deleteRule = CascadeRule.Delete;
                        }
                        else if (deleteRuleString == "NO ACTION")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "RESTRICT")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "SET NULL")
                        {
                            deleteRule = CascadeRule.SetNull;
                        }
                        else if (deleteRuleString == "SET DEFAULT")
                        {
                            deleteRule = CascadeRule.SetDefault;
                        }

                        current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                        toReturn.Add(current.Name, current);
                    }

                    current.AddKeys(r["r_column_name"].ToString(), r["column_name"].ToString(), transaction);
                }
            }

            return(toReturn.Values.ToArray());
        }
        public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
        {
            var server = discoveredTable.Database.Server;

            List <DiscoveredColumn> columns = new List <DiscoveredColumn>();
            var tableName = discoveredTable.GetRuntimeName();

            DbCommand cmd = server.Helper.GetCommand(@"SELECT *
FROM   all_tab_cols
WHERE  table_name = :table_name AND owner =:owner AND HIDDEN_COLUMN <> 'YES'
", connection.Connection);

            cmd.Transaction = connection.Transaction;

            DbParameter p = new OracleParameter("table_name", OracleDbType.Varchar2);

            p.Value = tableName;
            cmd.Parameters.Add(p);

            DbParameter p2 = new OracleParameter("owner", OracleDbType.Varchar2);

            p2.Value = database;
            cmd.Parameters.Add(p2);

            using (var r = cmd.ExecuteReader())
            {
                if (!r.HasRows)
                {
                    throw new Exception("Could not find any columns for table " + tableName +
                                        " in database " + database);
                }

                while (r.Read())
                {
                    var toAdd = new DiscoveredColumn(discoveredTable, (string)r["COLUMN_NAME"], r["NULLABLE"].ToString() != "N")
                    {
                        Format = r["CHARACTER_SET_NAME"] as string
                    };
                    toAdd.DataType = new DiscoveredDataType(r, GetSQLType_From_all_tab_cols_Result(r), toAdd);
                    columns.Add(toAdd);
                }
            }

            //get auto increment information
            cmd             = new OracleCommand("select table_name,column_name from ALL_TAB_IDENTITY_COLS WHERE table_name = :table_name AND owner =:owner", (OracleConnection)connection.Connection);
            cmd.Transaction = connection.Transaction;

            p       = new OracleParameter("table_name", OracleDbType.Varchar2);
            p.Value = tableName;
            cmd.Parameters.Add(p);

            p2       = new OracleParameter("owner", OracleDbType.Varchar2);
            p2.Value = database;
            cmd.Parameters.Add(p2);

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var colName = r["column_name"].ToString();
                    var match   = columns.Single(c => c.GetRuntimeName().Equals(colName, StringComparison.CurrentCultureIgnoreCase));
                    match.IsAutoIncrement = true;
                }
            }


            //get primary key information
            cmd             = new OracleCommand(@"SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = :table_name AND cols.owner = :owner
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position", (OracleConnection)connection.Connection);
            cmd.Transaction = connection.Transaction;


            p       = new OracleParameter("table_name", OracleDbType.Varchar2);
            p.Value = tableName;
            cmd.Parameters.Add(p);


            p2       = new OracleParameter("owner", OracleDbType.Varchar2);
            p2.Value = database;
            cmd.Parameters.Add(p2);

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    columns.Single(c => c.GetRuntimeName().Equals(r["COLUMN_NAME"])).IsPrimaryKey = true;    //mark all primary keys as primary
                }
            }

            return(columns.ToArray());
        }
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
                                                                       IManagedTransaction transaction = null)
        {
            string sql = $@"select c.constraint_name
    , x.table_schema as foreign_table_schema
    , x.table_name as foreign_table_name
    , x.column_name as foreign_column_name
    , y.table_schema 
    , y.table_name 
    , y.column_name 
    , delete_rule
from information_schema.referential_constraints c
join information_schema.key_column_usage x
    on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
    on y.ordinal_position = x.position_in_unique_constraint
    and y.constraint_name = c.unique_constraint_name
where 
    y.table_name='{table.GetRuntimeName()}' AND
    y.table_schema='{table.Schema??PostgreSqlSyntaxHelper.DefaultPostgresSchema}'
order by c.constraint_name, x.ordinal_position";


            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var cmd = table.GetCommand(sql, connection, transaction?.Transaction))
            {
                //fill data table to avoid multiple active readers
                using (DataTable dt = new DataTable())
                {
                    using (var da = new NpgsqlDataAdapter((NpgsqlCommand)cmd))
                        da.Fill(dt);

                    foreach (DataRow r in dt.Rows)
                    {
                        var fkName = r["constraint_name"].ToString();

                        DiscoveredRelationship current;

                        //could be a 2+ columns foreign key?
                        if (toReturn.ContainsKey(fkName))
                        {
                            current = toReturn[fkName];
                        }
                        else
                        {
                            var pkDb        = table.Database.GetRuntimeName();
                            var pkSchema    = r["table_schema"].ToString();
                            var pkTableName = r["table_name"].ToString();

                            var fkDb        = pkDb;
                            var fkSchema    = r["foreign_table_schema"].ToString();
                            var fkTableName = r["foreign_table_name"].ToString();

                            var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName, pkSchema);
                            var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName, fkSchema);

                            CascadeRule deleteRule = CascadeRule.Unknown;

                            var deleteRuleString = r["delete_rule"].ToString();

                            if (deleteRuleString == "CASCADE")
                            {
                                deleteRule = CascadeRule.Delete;
                            }
                            else if (deleteRuleString == "NO ACTION")
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleString == "RESTRICT")
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleString == "SET NULL")
                            {
                                deleteRule = CascadeRule.SetNull;
                            }
                            else if (deleteRuleString == "SET DEFAULT")
                            {
                                deleteRule = CascadeRule.SetDefault;
                            }

                            current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                            toReturn.Add(current.Name, current);
                        }

                        current.AddKeys(r["column_name"].ToString(), r["foreign_column_name"].ToString(), transaction);
                    }
                }
            }

            return(toReturn.Values.ToArray());
        }
        public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
        {
            List <DiscoveredColumn> toReturn = new List <DiscoveredColumn>();

            string sqlColumns = @"SELECT *
                FROM information_schema.columns
            WHERE table_schema = @schemaName
            AND table_name   = @tableName;";

            using (DbCommand cmd =
                       discoveredTable.GetCommand(sqlColumns, connection.Connection, connection.Transaction))
            {
                var p = cmd.CreateParameter();
                p.ParameterName = "@tableName";
                p.Value         = discoveredTable.GetRuntimeName();
                cmd.Parameters.Add(p);

                var p2 = cmd.CreateParameter();
                p2.ParameterName = "@schemaName";
                p2.Value         = string.IsNullOrWhiteSpace(discoveredTable.Schema) ? PostgreSqlSyntaxHelper.DefaultPostgresSchema : discoveredTable.Schema;
                cmd.Parameters.Add(p2);


                using (var r = cmd.ExecuteReader())
                    while (r.Read())
                    {
                        bool isNullable = string.Equals(r["is_nullable"], "YES");

                        //if it is a table valued function prefix the column name with the table valued function name
                        string columnName = discoveredTable is DiscoveredTableValuedFunction
                            ? discoveredTable.GetRuntimeName() + "." + r["column_name"]
                            : r["column_name"].ToString();

                        var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable);
                        toAdd.IsAutoIncrement = string.Equals(r["is_identity"], "YES");

                        toAdd.DataType  = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd);
                        toAdd.Collation = r["collation_name"] as string;
                        toReturn.Add(toAdd);
                    }
            }



            if (!toReturn.Any())
            {
                throw new Exception("Could not find any columns in table " + discoveredTable);
            }

            //don't bother looking for pks if it is a table valued function
            if (discoveredTable is DiscoveredTableValuedFunction)
            {
                return(toReturn.ToArray());
            }

            var pks = ListPrimaryKeys(connection, discoveredTable);

            foreach (DiscoveredColumn c in toReturn)
            {
                if (pks.Any(pk => pk.Equals(c.GetRuntimeName())))
                {
                    c.IsPrimaryKey = true;
                }
            }


            return(toReturn.ToArray());
        }
 protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
 {
     return(string.Format("RENAME TABLE `{0}` TO `{1}`;", discoveredTable.GetRuntimeName(), newName));
 }
 public string GetTabName()
 {
     return("View " + _table.GetRuntimeName());
 }