public void Uses_comparer()
 {
     var namer = new CSharpUniqueNamer<TableModel>(t => t.Name);
     var table1 = new TableModel { Name = "A B C" };
     var table2 = new TableModel { Name = "A_B_C" };
     Assert.Equal("A_B_C", namer.GetName(table1));
     Assert.Equal("A_B_C1", namer.GetName(table2));
 }
Пример #2
0
 public static void ParseTableDefinition(TableModel table, string sql)
 {
     var statements = ParseStatements(sql).ToList();
     var i = 0;
     for (; i < statements.Count; i++)
     {
         var firstWord = statements[i].Split(' ', '(')[0];
         if (_constraintKeyWords.Contains(firstWord))
         {
             break; // once we see the first constraint, stop looking for params
         }
         ParseColumnDefinition(table, statements[i]);
     }
     for (; i < statements.Count; i++)
     {
         ParseConstraints(table, statements[i]);
     }
 }
Пример #3
0
        public static void ParseColumnDefinition(TableModel table, string statement)
        {
            var paramName = UnescapeString(SafeSplit(statement, ' ').First());
            var column = table.Columns.FirstOrDefault(c => c.Name.Equals(paramName, StringComparison.OrdinalIgnoreCase));
            if (column == null)
            {
                return;
            }

            if (statement.IndexOf(" UNIQUE", StringComparison.OrdinalIgnoreCase) > 0)
            {
                var indexInfo = table.Indexes.FirstOrDefault(i =>
                    i.Columns.SingleOrDefault()?.Name.Equals(column.Name, StringComparison.OrdinalIgnoreCase) == true);

                if (indexInfo != null)
                {
                    indexInfo.IsUnique = true;
                }
            }
        }
        private void GetTables()
        {
            var command = _connection.CreateCommand();
            command.CommandText = "SELECT schema_name(t.schema_id) AS [schema], t.name FROM sys.tables AS t " +
                                  $"WHERE t.name <> '{HistoryRepository.DefaultTableName}'";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var table = new TableModel
                    {
                        SchemaName = reader.GetString(0),
                        Name = reader.GetString(1)
                    };

                    if (_tableSelectionSet.Allows(table.SchemaName, table.Name))
                    {
                        _databaseModel.Tables.Add(table);
                        _tables[TableKey(table)] = table;
                    }
                }
            }
        }
        public void Unique_foreign_key()

        {
            var parentTable = new TableModel { Name = "Parent", Columns = { IdColumn } };
            var childrenTable = new TableModel { Name = "Children", Columns = { IdColumn } };
            childrenTable.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = childrenTable,
                Columns = { childrenTable.Columns[0] },
                PrincipalTable = parentTable,
                PrincipalColumns = { parentTable.Columns[0] },
                OnDelete = Migrations.ReferentialAction.NoAction
            });

            var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });

            var children = (EntityType)model.FindEntityType("Children");

            var fk = Assert.Single(children.GetForeignKeys());
            Assert.True(fk.IsUnique);
            Assert.Equal(DeleteBehavior.Restrict, fk.DeleteBehavior);
        }
 private static string TableKey(TableModel table) => TableKey(table.Name, table.SchemaName);
 private static string ColumnKey(TableModel table, string columnName) => TableKey(table) + ".[" + columnName + "]";
        public void Unique_index_foreign_key()
        {
            var table = new TableModel
            {
                Name = "Friends",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "BuddyId", DataType = "long", IsNullable = false }
                }
            };
            table.Indexes.Add(new IndexModel { Columns = { table.Columns[1] }, IsUnique = true });
            table.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = table,
                Columns = { table.Columns[1] },
                PrincipalTable = table,
                PrincipalColumns = { table.Columns[0] }
            });

            var model = _factory.Create(new DatabaseModel { Tables = { table } }).FindEntityType("Friends");

            var fk = Assert.Single(model.GetForeignKeys());

            Assert.True(fk.IsUnique);
            Assert.Equal(model.FindPrimaryKey(), fk.PrincipalKey);
        }
        public void Unique_index_composite_foreign_key()
        {
            var parentTable = new TableModel
            {
                Name = "Parent",
                Columns =
                {
                    new ColumnModel { Name = "Id_A", DataType = "long", PrimaryKeyOrdinal = 1 },
                    new ColumnModel { Name = "Id_B", DataType = "long", PrimaryKeyOrdinal = 2 }
                }
            };
            var childrenTable = new TableModel
            {
                Name = "Children",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "ParentId_A", DataType = "long" },
                    new ColumnModel { Name = "ParentId_B", DataType = "long" }
                }
            };
            childrenTable.Indexes.Add(new IndexModel { IsUnique = true, Columns = { childrenTable.Columns[1], childrenTable.Columns[2] } });
            childrenTable.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = childrenTable,
                Columns = { childrenTable.Columns[1], childrenTable.Columns[2] },
                PrincipalTable = parentTable,
                PrincipalColumns = { parentTable.Columns[0], parentTable.Columns[1] }
            });

            var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
            var parent = model.FindEntityType("Parent");
            var children = model.FindEntityType("Children");

            var fk = Assert.Single(children.GetForeignKeys());

            Assert.True(fk.IsUnique);
            Assert.Equal(parent.FindPrimaryKey(), fk.PrincipalKey);
        }
        public void It_loads_self_referencing_foreign_key()

        {
            var table = new TableModel
            {
                Name = "ItemsList",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "ParentId", DataType = "long", IsNullable = false }
                }
            };
            table.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = table,
                Columns = { table.Columns[1] },
                PrincipalTable = table,
                PrincipalColumns = { table.Columns[0] }
            });

            var model = _factory.Create(new DatabaseModel { Tables = { table } });
            var list = model.FindEntityType("ItemsList");

            Assert.NotEmpty(list.GetReferencingForeignKeys());
            Assert.NotEmpty(list.GetForeignKeys());

            var principalKey = list.FindForeignKeys(list.FindProperty("ParentId")).SingleOrDefault().PrincipalKey;
            Assert.Equal("ItemsList", principalKey.DeclaringEntityType.Name);
            Assert.Equal("Id", principalKey.Properties[0].Name);
        }
        public void It_logs_warning_for_bad_foreign_key()
        {
            var parentTable = new TableModel
            {
                Name = "Parent",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "NotPkId", DataType = "long", PrimaryKeyOrdinal = null }
                }
            };
            var childrenTable = new TableModel
            {
                Name = "Children",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "ParentId", DataType = "long" }
                }
            };
            childrenTable.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = childrenTable,
                Columns = { childrenTable.Columns[1] },
                PrincipalTable = parentTable,
                PrincipalColumns = { parentTable.Columns[1] }
            });

            _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });

            Assert.Contains("Warning: " +
                            RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
                                childrenTable.ForeignKeys[0].DisplayName, "NotPkId", "Parent"),
                _logger.FullLog);
        }
Пример #12
0
 public static void ParseConstraints(TableModel table, string statement)
 {
     var constraint = statement.Split(' ', '(')[0];
     if (constraint.Equals("UNIQUE", StringComparison.OrdinalIgnoreCase))
     {
         ParseInlineUniqueConstraint(table, statement);
     }
 }
        public void Composite_foreign_key()

        {
            var parentTable = new TableModel
            {
                Name = "Parent",
                Columns =
                {
                    new ColumnModel { Name = "Id_A", DataType = "long", PrimaryKeyOrdinal = 1 },
                    new ColumnModel { Name = "Id_B", DataType = "long", PrimaryKeyOrdinal = 2 }
                }
            };
            var childrenTable = new TableModel
            {
                Name = "Children",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "ParentId_A", DataType = "long" },
                    new ColumnModel { Name = "ParentId_B", DataType = "long" }
                }
            };
            childrenTable.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = childrenTable,
                Columns = { childrenTable.Columns[1], childrenTable.Columns[2] },
                PrincipalTable = parentTable,
                PrincipalColumns = { parentTable.Columns[0], parentTable.Columns[1] },
                OnDelete = Migrations.ReferentialAction.SetNull
            });

            var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });

            var parent = (EntityType)model.FindEntityType("Parent");

            var children = (EntityType)model.FindEntityType("Children");

            Assert.NotEmpty(parent.GetReferencingForeignKeys());

            var fk = Assert.Single(children.GetForeignKeys());
            Assert.False(fk.IsUnique);
            Assert.Equal(DeleteBehavior.SetNull, fk.DeleteBehavior);

            var principalKey = fk.PrincipalKey;

            Assert.Equal("Parent", principalKey.DeclaringEntityType.Name);
            Assert.Equal("Id_A", principalKey.Properties[0].Name);
            Assert.Equal("Id_B", principalKey.Properties[1].Name);
        }
        public void Foreign_key()

        {
            var parentTable = new TableModel { Name = "Parent", Columns = { IdColumn } };
            var childrenTable = new TableModel
            {
                Name = "Children",
                Columns =
                {
                    IdColumn,
                    new ColumnModel { Name = "ParentId", DataType = "long", IsNullable = true }
                }
            };
            childrenTable.ForeignKeys.Add(new ForeignKeyModel
            {
                Table = childrenTable,
                Columns = { childrenTable.Columns[1] },
                PrincipalTable = parentTable,
                PrincipalColumns = { parentTable.Columns[0] },
                OnDelete = Migrations.ReferentialAction.Cascade
            });

            var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });

            var parent = (EntityType)model.FindEntityType("Parent");

            var children = (EntityType)model.FindEntityType("Children");

            Assert.NotEmpty(parent.GetReferencingForeignKeys());
            var fk = Assert.Single(children.GetForeignKeys());
            Assert.False(fk.IsUnique);
            Assert.Equal(DeleteBehavior.Cascade, fk.DeleteBehavior);

            var principalKey = fk.PrincipalKey;

            Assert.Same(parent, principalKey.DeclaringEntityType);
            Assert.Same(parent.GetProperties().First(), principalKey.Properties[0]);
        }
        public void Indexes_and_alternate_keys()
        {
            var table = new TableModel
            {
                Name = "T",
                Columns =
                {
                    new ColumnModel { Name = "C1", DataType = "long", PrimaryKeyOrdinal = 1 },
                    new ColumnModel { Name = "C2", DataType = "long" },
                    new ColumnModel { Name = "C3", DataType = "long" }
                }
            };
            table.Indexes.Add(new IndexModel { Name = "IDX_C1", Columns = { table.Columns[0] }, IsUnique = false });
            table.Indexes.Add(new IndexModel { Name = "UNQ_C2", Columns = { table.Columns[1] }, IsUnique = true });
            table.Indexes.Add(new IndexModel { Name = "IDX_C2_C1", Columns = { table.Columns[1], table.Columns[0] }, IsUnique = false });
            table.Indexes.Add(new IndexModel { /*Name ="UNQ_C3_C1",*/ Columns = { table.Columns[2], table.Columns[0] }, IsUnique = true });
            var info = new DatabaseModel { Tables = { table } };

            var entityType = (EntityType)_factory.Create(info).GetEntityTypes().Single();

            Assert.Collection(entityType.GetIndexes(),
                indexColumn1 =>
                    {
                        Assert.False(indexColumn1.IsUnique);
                        Assert.Equal("IDX_C1", indexColumn1.Relational().Name);
                        Assert.Same(entityType.FindProperty("C1"), indexColumn1.Properties.Single());
                    },
                uniqueColumn2 =>
                    {
                        Assert.True(uniqueColumn2.IsUnique);
                        Assert.Same(entityType.FindProperty("C2"), uniqueColumn2.Properties.Single());
                    },
                indexColumn2Column1 =>
                    {
                        Assert.False(indexColumn2Column1.IsUnique);
                        Assert.Equal(new[] { "C2", "C1" }, indexColumn2Column1.Properties.Select(c => c.Name).ToArray());
                    },
                uniqueColumn3Column1 =>
                    {
                        Assert.True(uniqueColumn3Column1.IsUnique);
                        Assert.Equal(new[] { "C3", "C1" }, uniqueColumn3Column1.Properties.Select(c => c.Name).ToArray());
                    }
                );

            Assert.Collection(entityType.GetKeys().Where(k => !k.IsPrimaryKey()),
                single =>
                    {
                        Assert.Equal("UNQ_C2", single.Relational().Name);
                        Assert.Same(entityType.FindProperty("C2"), single.Properties.Single());
                    },
                composite => { Assert.Equal(new[] { "C3", "C1" }, composite.Properties.Select(c => c.Name).ToArray()); });
        }
Пример #16
0
        public static void ParseInlineUniqueConstraint(TableModel table, string statement)
        {
            var start = statement.IndexOf('(') + 1;
            var paramChunk = statement.Substring(start, statement.LastIndexOf(')') - start);
            var columns = SafeSplit(paramChunk, ',')
                .Select(UnescapeString)
                .ToList();

            var index = table.Indexes.FirstOrDefault(i =>
                {
                    if (!i.Name.StartsWith("sqlite_autoindex")
                        || !i.Table.Name.Equals(table.Name, StringComparison.OrdinalIgnoreCase))
                    {
                        return false;
                    }
                    return columns.All(prop => i.Columns.Any(p => p.Name.Equals(prop, StringComparison.OrdinalIgnoreCase)));
                });

            if (index != null)
            {
                index.IsUnique = true;
            }
        }
        private void GetSqliteMaster()
        {
            var command = _connection.CreateCommand();
            command.CommandText = "SELECT type, name, sql, tbl_name FROM sqlite_master ORDER BY type DESC";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var type = reader.GetString(0);
                    var name = reader.GetString(1);
                    var sql = reader.GetValue(2) as string; // can be null
                    var tableName = reader.GetString(3);

                    if (type == "table"
                        && name != "sqlite_sequence"
                        && _tableSelectionSet.Allows(name))
                    {
                        var table = new TableModel
                        {
                            Name = name
                        };
                        _databaseModel.Tables.Add(table);
                        _tables.Add(name, table);
                        _tableDefinitions[name] = sql;
                    }
                    else if (type == "index"
                             && _tables.ContainsKey(tableName))
                    {
                        var table = _tables[tableName];

                        table.Indexes.Add(new IndexModel
                        {
                            Name = name,
                            Table = table
                        });

                        _indexDefinitions[name] = sql;
                    }
                }
            }
        }
 private static string ColumnKey(TableModel table, string columnName) => "[" + table.Name + "].[" + columnName + "]";