Esempio n. 1
0
        protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys_Old(IEnumerable <CacheTableSourceInfo> tableSources)
        {
            string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
            string tableNames  = "";

            try {
                tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
                IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();
                //on mysql table_schema always equal to constraint_schema so we can shortcut to optimize
                string query = string.Format(@"SELECT t.TABLE_NAME, 
                                                      c.CONSTRAINT_NAME, c.COLUMN_NAME, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME, 
                                                      r.DELETE_RULE
                                               FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
                                                 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON c.CONSTRAINT_NAME = t.CONSTRAINT_NAME 
                                                 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME = t.CONSTRAINT_NAME
                                               WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' 
                                                 AND t.TABLE_SCHEMA = {0} 
                                                 AND c.CONSTRAINT_SCHEMA = {0} 
                                                 AND r.CONSTRAINT_SCHEMA = {0} 
                                                 AND t.TABLE_NAME  IN (" + tableNames + @")", paramPrefix + "schema");

                using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
                    IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
                    //----------------- Caché --------------------
                    // Caché does not look at the parameter name, it assigns parameters in the order they appear, one per question mark "?"
                    // So the above query expects 3 parameters, although it is the same Caché expects 3, so add 3 parameters.
                    DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSources.First().Database.Name);
                    cmd.CommandTimeout = QueryTimeout;
                    using (IDataReader reader = cmd.ExecuteReader()) {
                        while (reader.Read())
                        {
                            string tableName            = (string)reader["TABLE_NAME"];
                            string foreignKeyName       = (string)reader["CONSTRAINT_NAME"];
                            string columnName           = (string)reader["COLUMN_NAME"];
                            string referencedColumnName = (string)reader["REFERENCED_COLUMN_NAME"];
                            string referencedTableName  = (string)reader["REFERENCED_TABLE_NAME"];
                            bool   isCascadeDelete      = "CASCADE".EqualsIgnoreCase((string)reader["DELETE_RULE"]);

                            CacheTableSourceInfo tableSource = tableSources.First(t => t.Name.EqualsIgnoreCase(tableName));

                            string                     qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
                            ITableSourceInfo           referencedTableSource        = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
                            ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
                            foreignKeys.Add(foreignKeyInfo);
                        }
                    }


                    return(foreignKeys);
                }
            }
            catch (Exception e) {
                OSTrace.Error(string.Format("Failed to retrieve foreign key information from database. Tables: {0}", tableNames), e);
                return(new List <ITableSourceForeignKeyInfo>());
            }
        }
Esempio n. 2
0
        protected IEnumerable <ITableSourceForeignKeyInfo> GetForeignKeys(IEnumerable <CacheTableSourceInfo> tableSources)
        {
            string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
            string tableNames  = "";

            try {
                tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
                IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();

                using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
                    foreach (CacheTableSourceInfo tableSource in tableSources)
                    {
                        string query = string.Format(
                            @"SELECT t.TABLE_NAME, c.CONSTRAINT_NAME, c.COLUMN_NAME, c.REFERENCED_TABLE_NAME, 
                                     c.REFERENCED_COLUMN_NAME, r.DELETE_RULE
                              FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t ON c.CONSTRAINT_NAME = t.CONSTRAINT_NAME 
                                INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME = t.CONSTRAINT_NAME
                              WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' 
                                AND t.TABLE_SCHEMA = {0} 
                                AND c.CONSTRAINT_SCHEMA = {0} 
                                AND r.CONSTRAINT_SCHEMA = {0} 
                                AND t.TABLE_NAME = {1}", paramPrefix + "schema", paramPrefix + "table");

                        IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
                        DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSource.Database.Name);
                        DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "table", DbType.String, tableSource.Name);
                        cmd.CommandTimeout = QueryTimeout;
                        using (IDataReader reader = cmd.ExecuteReader()) {
                            while (reader.Read())
                            {
                                string tableName            = (string)reader["TABLE_NAME"];
                                string foreignKeyName       = (string)reader["CONSTRAINT_NAME"];
                                string columnName           = (string)reader["COLUMN_NAME"];
                                string referencedColumnName = (string)reader["REFERENCED_COLUMN_NAME"];
                                string referencedTableName  = (string)reader["REFERENCED_TABLE_NAME"];
                                bool   isCascadeDelete      = "CASCADE".EqualsIgnoreCase((string)reader["DELETE_RULE"]);

                                string                     qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
                                ITableSourceInfo           referencedTableSource        = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
                                ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
                                foreignKeys.Add(foreignKeyInfo);
                            }
                        }

                        // Also check if there are relationships defined on columns, these also get added as a foreign key
                        // If a relationship is found check if this column does not already have a foreign key, if it has then
                        // do not add another foreign key.
                        // If a column references another object the type will not start with %Library, so we check for all runtimetypes
                        // taht do not start with %, this indicates reference to another object. Further we only want to know about references to other
                        // persistent objects.
                        string queryRelationships = string.Format(
                            @"SELECT P.SqlFieldName AS COLUMN_NAME, P.RuntimeType AS RUNTIME_TYPE, P.Collection, 
                                     P.Relationship AS RELATIONSHIP, P.Cardinality,  
                                     P.Parent, P.SqlListDelimiter, P.SqlListType, P.Name AS ColumnName 
                               FROM %Dictionary.CompiledClass C, %Dictionary.CompiledProperty P 
                               WHERE P.parent = C.ID 
                                 AND NOT P.RuntimeType %STARTSWITH '%' 
                                 AND C.SqlSchemaName = {0}
                                 AND C.SqlTableName  = {1}", paramPrefix + "schema", paramPrefix + "table");

                        IDbCommand cmdRelationships = DatabaseServices.ExecutionService.CreateCommand(conn, queryRelationships);
                        DatabaseServices.ExecutionService.CreateParameter(cmdRelationships, paramPrefix + "schema", DbType.String, tableSource.Database.Name);
                        DatabaseServices.ExecutionService.CreateParameter(cmdRelationships, paramPrefix + "table", DbType.String, tableSource.Name);
                        cmdRelationships.CommandTimeout = QueryTimeout;

                        using (IDataReader readerRel = cmdRelationships.ExecuteReader()) {
                            while (readerRel.Read())
                            {
                                string runtimeType = (string)readerRel["RUNTIME_TYPE"];
                                // Check if the Runtime Type is that of a persistent object
                                bool   isPersistentType    = false;
                                string queryPersistentType = string.Format(
                                    @"SELECT ID, Name, ClassType FROM %Dictionary.CompiledClass WHERE Name = '" + runtimeType + @"'");
                                IDbCommand cmdPersistentType = DatabaseServices.ExecutionService.CreateCommand(conn, queryPersistentType);
                                using (IDataReader readerPersistent = cmdPersistentType.ExecuteReader()) {
                                    if (readerPersistent.Read())
                                    {
                                        string classType = (string)readerPersistent["ClassType"];
                                        isPersistentType = classType.CompareTo("persistent") == 0 ? true : false;
                                    }
                                }

                                if (isPersistentType)
                                {
                                    // Check if there is such a foreign key
                                    string referencedTableName = ExtractTableNameFromQualifiedName(runtimeType);
                                    string columnName          = (string)readerRel["COLUMN_NAME"];
                                    int    idx = foreignKeys.IndexOf(t => t.ColumnName.EqualsIgnoreCase(columnName));

                                    if (idx < 0)
                                    {
                                        // Add a foreign key constraint which is named this column's name pre-pended with "FK_"[referenced table name]
                                        // and the referenced column is the "ID" column of the referenced table.
                                        string                     foreignKeyName = "FK_" + referencedTableName + "_" + columnName;
                                        string                     qualifiedReferencedTableName = GetQualifiedIdentifier(tableSource.Database.Name, referencedTableName);
                                        ITableSourceInfo           referencedTableSource        = new CacheTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
                                        ITableSourceForeignKeyInfo foreignKeyInfo = new CacheTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, "ID", false);
                                        foreignKeys.Add(foreignKeyInfo);
                                    }
                                }
                            }
                        }
                    } // foreach
                }

                return(foreignKeys);
            }
            catch (Exception e) {
                OSTrace.Error(string.Format("Failed to retrieve foreign key information from database. Tables: {0}", tableNames), e);
                return(new List <ITableSourceForeignKeyInfo>());
            }
        }