public void It_logs_warning_for_bad_foreign_key_principal_table()
        {
            // will fail because the referenced table, Parent, is not found
            var sql = @"CREATE TABLE Children (
                            Id INT PRIMARY KEY,
                            ParentId INT,
                            FOREIGN KEY (ParentId) REFERENCES Parent (Id)
                        );";

            GetModel(sql);

            Assert.Contains("Debug: " +
                            SqliteDesignStrings.PrincipalTableNotFound(0, "Children", "Parent"),
                            _logger.FullLog);
        }
        private void GetForeignKeys()
        {
            foreach (var dependentTable in _databaseModel.Tables)
            {
                using (var fkList = _connection.CreateCommand())
                {
                    fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";

                    var tableForeignKeys = new Dictionary <int, ForeignKeyModel>();

                    using (var reader = fkList.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var id = reader.GetValueOrDefault <int>("id");
                            var principalTableName = reader.GetValueOrDefault <string>("table");
                            var fromColumnName     = reader.GetValueOrDefault <string>("from");
                            var toColumnName       = reader.GetValueOrDefault <string>("to");
                            var deleteAction       = reader.GetValueOrDefault <string>("on_delete");
                            var fkOrdinal          = reader.GetValueOrDefault <int>("seq");

                            Logger.LogTrace(SqliteDesignStrings.FoundForeignKeyColumn(
                                                dependentTable.Name, id, principalTableName, fromColumnName,
                                                toColumnName, deleteAction, fkOrdinal));

                            ForeignKeyModel foreignKey;
                            if (!tableForeignKeys.TryGetValue(id, out foreignKey))
                            {
                                TableModel principalTable;
                                if (!_tables.TryGetValue(principalTableName, out principalTable))
                                {
                                    Logger.LogTrace(SqliteDesignStrings.PrincipalTableNotFound(
                                                        id, dependentTable.Name, principalTableName));
                                    continue;
                                }

                                foreignKey = new ForeignKeyModel
                                {
                                    Table          = dependentTable,
                                    PrincipalTable = principalTable,
                                    OnDelete       = ConvertToReferentialAction(deleteAction)
                                };
                            }

                            var fkColumn = new ForeignKeyColumnModel
                            {
                                Ordinal = fkOrdinal,
                                Column  = _tableColumns[ColumnKey(dependentTable, fromColumnName)]
                            };

                            ColumnModel toColumn;
                            if (!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
                            {
                                Logger.LogTrace(SqliteDesignStrings.PrincipalColumnNotFound(
                                                    id, dependentTable.Name, toColumnName, principalTableName));
                                continue;
                            }
                            fkColumn.PrincipalColumn = toColumn;

                            foreignKey.Columns.Add(fkColumn);

                            if (!tableForeignKeys.ContainsKey(id))
                            {
                                tableForeignKeys.Add(id, foreignKey);
                            }
                        }
                    }

                    foreach (var foreignKey in tableForeignKeys)
                    {
                        dependentTable.ForeignKeys.Add(foreignKey.Value);
                    }
                }
            }
        }