public static void Increment_PropertyGet_MatchesCtorArg() { const int increment = 100; var sequence = new DatabaseSequence("test", 1, increment, Option <decimal> .None, Option <decimal> .None, true, 1); Assert.That(sequence.Increment, Is.EqualTo(increment)); }
public static void Cache_PropertyGet_MatchesCtorArg() { const int cacheSize = 20; var sequence = new DatabaseSequence("test", 1, 1, Option <decimal> .None, Option <decimal> .None, true, cacheSize); Assert.That(sequence.Cache, Is.EqualTo(cacheSize)); }
public static void MinValue_PropertyGet_MatchesCtorArg() { const int minValue = 100; var sequence = new DatabaseSequence("test", minValue, 1, Option <decimal> .Some(minValue), Option <decimal> .None, true, 1); Assert.That(sequence.MinValue.UnwrapSome(), Is.EqualTo(minValue)); }
public static void Name_PropertyGet_MatchesCtorArg() { var sequenceName = new Identifier("test"); var sequence = new DatabaseSequence(sequenceName, 1, 1, Option <decimal> .None, Option <decimal> .None, true, 0); Assert.That(sequence.Name, Is.EqualTo(sequenceName)); }
public async Task SerializeDeserialize_WhenSequenceRoundTripped_ExportsAndParsesWithoutError() { var sequence = new DatabaseSequence( "test_sequence_name", 1, 10, Option <decimal> .Some(-10), Option <decimal> .Some(1000), true, 20 ); var sequences = new[] { sequence }; var tables = Array.Empty <IRelationalDatabaseTable>(); var views = Array.Empty <IDatabaseView>(); var synonyms = Array.Empty <IDatabaseSynonym>(); var routines = Array.Empty <IDatabaseRoutine>(); var db = new RelationalDatabase( new IdentifierDefaults(null, null, "main"), new VerbatimIdentifierResolutionStrategy(), tables, views, sequences, synonyms, routines ); var json = await Serializer.SerializeAsync(db).ConfigureAwait(false); var importedDb = await Serializer.DeserializeAsync(json).ConfigureAwait(false); db.Should().BeEquivalentTo(importedDb); }
public static void Start_PropertyGet_MatchesCtorArg() { const int start = 100; var sequence = new DatabaseSequence("test", start, 1, Option <decimal> .None, Option <decimal> .None, true, 1); Assert.That(sequence.Start, Is.EqualTo(start)); }
public static List <DatabaseSequence> Sequences(DataTable dt) { List <DatabaseSequence> list = new List <DatabaseSequence>(); var sequenceKeyMap = new SequenceKeyMap(dt); foreach (DataRow row in dt.Rows) { DatabaseSequence seq = new DatabaseSequence(); seq.Name = row[sequenceKeyMap.Key].ToString(); seq.SchemaOwner = row[sequenceKeyMap.OwnerKey].ToString(); if (!string.IsNullOrEmpty(sequenceKeyMap.MinValueKey)) { seq.MinimumValue = GetNullableDecimal(row[sequenceKeyMap.MinValueKey]); } if (!string.IsNullOrEmpty(sequenceKeyMap.MaxValueKey)) { seq.MaximumValue = GetNullableDecimal(row[sequenceKeyMap.MaxValueKey]); } if (!string.IsNullOrEmpty(sequenceKeyMap.IncrementKey)) { seq.IncrementBy = GetNullableInt(row[sequenceKeyMap.IncrementKey]) ?? 1; } list.Add(seq); } return(list); }
private string GetSequenceName(DatabaseSequence sequence) { switch (sequence) { case DatabaseSequence.TransactionNumber: return(Sequences.TransactionNumber); } throw new ArgumentException("Invalid database sequence", nameof(sequence)); }
public static void ToString_WhenInvoked_ReturnsExpectedString(string schema, string localName, string expectedOutput) { var sequenceName = Identifier.CreateQualifiedIdentifier(schema, localName); var sequence = new DatabaseSequence(sequenceName, 1, 1, Option <decimal> .None, Option <decimal> .None, true, 1); var result = sequence.ToString(); Assert.That(result, Is.EqualTo(expectedOutput)); }
private void GetSequences() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT name, is_cycling, CAST(minimum_value AS bigint) as [minimum_value], CAST(maximum_value AS bigint) as [maximum_value], CAST(start_value AS bigint) as [start_value], CAST(increment AS int) as [increment], TYPE_NAME(user_type_id) as [type_name], OBJECT_SCHEMA_NAME(object_id) AS [schema_name] FROM sys.sequences"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var sequence = new DatabaseSequence { Database = _databaseModel, Schema = reader.GetValueOrDefault <string>("schema_name"), Name = reader.GetValueOrDefault <string>("name"), StoreType = reader.GetValueOrDefault <string>("type_name"), IsCyclic = reader.GetValueOrDefault <bool?>("is_cycling"), IncrementBy = reader.GetValueOrDefault <int?>("increment"), StartValue = reader.GetValueOrDefault <long?>("start_value"), MinValue = reader.GetValueOrDefault <long?>("minimum_value"), MaxValue = reader.GetValueOrDefault <long?>("maximum_value") }; Logger.SequenceFound( DisplayName(sequence.Schema, sequence.Name), sequence.StoreType, sequence.IsCyclic, sequence.IncrementBy, sequence.StartValue, sequence.MinValue, sequence.MaxValue); if (string.IsNullOrEmpty(sequence.Name)) { Logger.SequenceNotNamedWarning(); continue; } if (_defaultSequenceMinMax.ContainsKey(sequence.StoreType)) { var defaultMin = _defaultSequenceMinMax[sequence.StoreType][0]; sequence.MinValue = sequence.MinValue == defaultMin ? null : sequence.MinValue; sequence.StartValue = sequence.StartValue == defaultMin ? null : sequence.StartValue; var defaultMax = _defaultSequenceMinMax[sequence.StoreType][1]; sequence.MaxValue = sequence.MaxValue == defaultMax ? null : sequence.MaxValue; } _databaseModel.Sequences.Add(sequence); } } }
public void TestPostgreSqlColumnDefaults() { //MigrationGenerator does not output default values for columns //https://github.com/martinjw/dbschemareader/issues/11 //arrange var gen = new DdlGeneratorFactory(SqlType.PostgreSql).MigrationGenerator(); gen.IncludeSchema = false; var pkSeqName = "Seq_PK_Generator"; var pkSeq = new DatabaseSequence() { Name = pkSeqName, MinimumValue = 1, IncrementBy = 1, }; //gen.AddSequence(pkSeq).Replace(";", " CACHE;"); var newTable = new DatabaseTable { Name = "TestTable" }; var idColumn = newTable.AddColumn("Id", DbType.Int64); idColumn.AddPrimaryKey("PK_TestTable"); idColumn.DefaultValue = $"nextval('{pkSeqName}')"; var summaryColumn = newTable.AddColumn("Summary", DbType.String); summaryColumn.Length = 100; //act var ddl = gen.AddTable(newTable); //assert //expected /* * CREATE SEQUENCE "Seq_PK_Generator" INCREMENT BY 1 MINVALUE 1 CACHE; * * CREATE TABLE "TestTable" * ( * "Id" BIGINT NOT NULL, --default value missing * "Summary" VARCHAR (100) NOT NULL * ); * ALTER TABLE "TestTable" ADD CONSTRAINT "PK_TestTable" PRIMARY KEY ("Id"); */ Assert.IsTrue(ddl.IndexOf("BIGINT NOT NULL DEFAULT nextval('Seq_PK_Generator'),", StringComparison.OrdinalIgnoreCase) != -1, "default value should be included"); }
private void CreateResult(ResultType resultType, DatabaseSequence sequence, string script) { var result = new CompareResult { SchemaObjectType = SchemaObjectType.Sequence, ResultType = resultType, Name = sequence.Name, SchemaOwner = sequence.SchemaOwner, Script = script }; _results.Add(result); }
public string AddSequence(DatabaseSequence sequence) { //amazingly SQLServer Denali has the same syntax as Oracle. http://msdn.microsoft.com/en-us/library/ff878091%28v=SQL.110%29.aspx var sb = new StringBuilder(); sb.Append("CREATE SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) + " INCREMENT BY " + sequence.IncrementBy); //min/max are optional- if they look like defaults, no need to write them out if (sequence.MinimumValue.HasValue && sequence.MinimumValue != 0) { sb.Append(" MINVALUE " + sequence.MinimumValue); } if (sequence.MaximumValue.HasValue && sequence.MaximumValue != 999999999999999999999999999M) { sb.Append(" MAXVALUE " + sequence.MaximumValue); } sb.Append(";"); return(sb.ToString()); }
/// <summary> /// Handle DBSequence element /// Called by ReadSequenceCollection (xml hierarchy) /// </summary> /// <param name="reader">XmlReader that is only valid for the scope of the single DBSequence element</param> private void ReadDbSequence(XmlReader reader) { reader.MoveToContent(); string id = reader.GetAttribute("id"); if (id != null) { var dbSeq = new DatabaseSequence { Length = Convert.ToInt32(reader.GetAttribute("length")), Accession = reader.GetAttribute("accession") }; if (reader.ReadToDescendant("cvParam")) { dbSeq.ProteinDescription = reader.GetAttribute("value"); //.Split(' ')[0]; } m_database.Add(id, dbSeq); } reader.Close(); }
public static async Task AnalyseSequences_GivenSequenceWithNameContainingReservedKeyword_ProducesMessages() { var rule = new ReservedKeywordNameRule(CreateFakeDialect(), RuleLevel.Error); var sequenceName = new Identifier("SELECT"); var sequence = new DatabaseSequence( sequenceName, 1, 1, 1, 100, true, 10 ); var sequences = new[] { sequence }; var hasMessages = await rule.AnalyseSequences(sequences).AnyAsync().ConfigureAwait(false); Assert.That(hasMessages, Is.True); }
public static async Task AnalyseSequences_GivenSequenceWithNameContainingWhitespace_ProducesMessages() { var rule = new WhitespaceNameRule(RuleLevel.Error); var sequenceName = new Identifier(" test "); var sequence = new DatabaseSequence( sequenceName, 1, 1, 1, 100, true, 10 ); var sequences = new[] { sequence }; var hasMessages = await rule.AnalyseSequences(sequences).AnyAsync().ConfigureAwait(false); Assert.That(hasMessages, Is.True); }
public async Task SerializeDeserialize_WhenSequenceRoundTripped_PreservesJsonStructure() { var sequence = new DatabaseSequence( "test_sequence_name", 1, 10, Option <decimal> .Some(-10), Option <decimal> .Some(1000), true, 20 ); var sequences = new[] { sequence }; var tables = Array.Empty <IRelationalDatabaseTable>(); var views = Array.Empty <IDatabaseView>(); var synonyms = Array.Empty <IDatabaseSynonym>(); var routines = Array.Empty <IDatabaseRoutine>(); var db = new RelationalDatabase( new IdentifierDefaults(null, null, "main"), new VerbatimIdentifierResolutionStrategy(), tables, views, sequences, synonyms, routines ); var json = await Serializer.SerializeAsync(db).ConfigureAwait(false); var importedDb = await Serializer.DeserializeAsync(json).ConfigureAwait(false); var reExportedJson = await Serializer.SerializeAsync(importedDb).ConfigureAwait(false); Assert.Multiple(() => { Assert.That(reExportedJson, Is.Not.Null); Assert.That(reExportedJson, Is.Not.Empty); Assert.That(reExportedJson, Is.EqualTo(json)); }); }
public static void Generate_GivenValidSequence_ReturnsExpectedConfiguration() { var nameTranslator = new VerbatimNameTranslator(); var dbContextBuilder = new EFCoreDbContextBuilder(nameTranslator, "test"); var tables = Array.Empty <IRelationalDatabaseTable>(); var views = Array.Empty <IDatabaseView>(); var sequence = new DatabaseSequence( "test_sequence", 3, 20, Option <decimal> .Some(0), Option <decimal> .Some(100), true, 2 ); var sequences = new[] { sequence }; var result = dbContextBuilder.Generate(tables, views, sequences); Assert.That(result, Is.EqualTo(ExpectedSequenceTestResult).Using(LineEndingInvariantStringComparer.Ordinal)); }
public void FindOracleAutoNumberTrigger() { //arrange var schema = new DatabaseSchema(null, SqlType.Oracle); var table = schema.AddTable("Test"); var id = table.AddColumn <int>("Id").AddPrimaryKey(); id.IsAutoNumber = true; table.AddColumn <string>("Name").AddLength(200); var databaseTrigger = new DatabaseTrigger { Name = "Test_INS_TRG", TriggerEvent = "INSERT", TriggerBody = @"BEGIN SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL; END;", TriggerType = "BEFORE EACH ROW", }; table.Triggers.Add(databaseTrigger); var databaseSequence = new DatabaseSequence { IncrementBy = 1, MinimumValue = 0, Name = "Test_SEQ" }; schema.Sequences.Add(databaseSequence); //act var result = OracleSequenceTrigger.FindTrigger(table); //assert Assert.IsNotNull(result); Assert.IsNotNull(result.DatabaseTrigger); Assert.IsNotNull(result.DatabaseSequence); Assert.AreEqual(databaseSequence, result.DatabaseSequence); Assert.AreEqual(databaseTrigger, result.DatabaseTrigger); }
public string AddSequence(DatabaseSequence sequence) { return(_migration.AddSequence(sequence)); }
public string DropSequence(DatabaseSequence sequence) { return(_migration.DropSequence(sequence)); }
/// <summary> /// Get the next value for a database sequence. /// </summary> public Int64 GetNextSequenceValue(DatabaseSequence sequence) { return(context.Database.SqlQuery <Int64>($"SELECT NEXT VALUE FOR {GetSequenceName(sequence)};").FirstOrDefault()); }
protected virtual DropSequenceOperation Drop(DatabaseSequence sequence) => new DropSequenceOperation { Name = sequence.Name, Schema = sequence.Schema };
void GetSequences() { using (var command = new NpgsqlCommand(GetSequencesQuery, _connection)) using (var reader = command.ExecuteReader()) { while (reader.Read()) { // If the sequence is OWNED BY a column which is a serial, we skip it. The sequence will be created implicitly. if (!reader.IsDBNull(10)) { var ownerSchema = reader.GetValueOrDefault <string>("owner_schema"); var ownerTable = reader.GetValueOrDefault <string>("owner_table"); var ownerColumn = reader.GetValueOrDefault <string>("owner_column"); DatabaseTable ownerDatabaseTable; DatabaseColumn ownerDatabaseColumn; if (_tables.TryGetValue(TableKey(ownerTable, ownerSchema), out ownerDatabaseTable) && _tableColumns.TryGetValue(ColumnKey(ownerDatabaseTable, ownerColumn), out ownerDatabaseColumn) && ownerDatabaseColumn.ValueGenerated == ValueGenerated.OnAdd) { // Don't reverse-engineer sequences which drive serial columns, these are implicitly // reverse-engineered by the serial column. continue; } } var sequence = new DatabaseSequence { Schema = reader.GetValueOrDefault <string>("sequence_schema"), Name = reader.GetValueOrDefault <string>("sequence_name"), StoreType = reader.GetValueOrDefault <string>("data_type"), StartValue = reader.GetValueOrDefault <long>("start_value"), MinValue = reader.GetValueOrDefault <long>("minimum_value"), MaxValue = reader.GetValueOrDefault <long>("maximum_value"), IncrementBy = reader.GetValueOrDefault <int>("increment"), IsCyclic = reader.GetValueOrDefault <bool>("is_cyclic") }; if (!_tableSelectionSet.Allows(sequence.Schema, "")) { continue; } if (sequence.StoreType == "bigint") { long defaultStart, defaultMin, defaultMax; if (sequence.IncrementBy > 0) { defaultMin = 1; defaultMax = long.MaxValue; Debug.Assert(sequence.MinValue.HasValue); defaultStart = sequence.MinValue.Value; } else { defaultMin = long.MinValue + 1; defaultMax = -1; Debug.Assert(sequence.MaxValue.HasValue); defaultStart = sequence.MaxValue.Value; } if (sequence.StartValue == defaultStart) { sequence.StartValue = null; } if (sequence.MinValue == defaultMin) { sequence.MinValue = null; } if (sequence.MaxValue == defaultMax) { sequence.MaxValue = null; } } else { Logger.Logger.LogWarning($"Sequence with datatype {sequence.StoreType} which isn't the expected bigint."); } _databaseModel.Sequences.Add(sequence); } } }
public string DropSequence(DatabaseSequence sequence) { return("DROP SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) + ";"); }
/// <summary> /// Set the next value for a database sequence. /// </summary> public void SetNextSequenceValue(DatabaseSequence sequence, Int64 newValue) { context.Database.ExecuteSqlCommand($"ALTER SEQUENCE {GetSequenceName(sequence)} RESTART WITH {newValue};"); }
void SetSequenceStartMinMax(DatabaseSequence sequence, Version postgresVersion) { long defaultStart, defaultMin, defaultMax; if (sequence.StoreType == "smallint") { if (sequence.IncrementBy > 0) { defaultMin = 1; defaultMax = short.MaxValue; defaultStart = sequence.MinValue.Value; } else { // PostgreSQL 10 changed the default minvalue for a descending sequence, see #264 defaultMin = postgresVersion >= new Version(10, 0) ? short.MinValue : short.MinValue + 1; defaultMax = -1; defaultStart = sequence.MaxValue.Value; } } else if (sequence.StoreType == "integer") { if (sequence.IncrementBy > 0) { defaultMin = 1; defaultMax = int.MaxValue; defaultStart = sequence.MinValue.Value; } else { // PostgreSQL 10 changed the default minvalue for a descending sequence, see #264 defaultMin = postgresVersion >= new Version(10, 0) ? int.MinValue : int.MinValue + 1; defaultMax = -1; defaultStart = sequence.MaxValue.Value; } } else if (sequence.StoreType == "bigint") { if (sequence.IncrementBy > 0) { defaultMin = 1; defaultMax = long.MaxValue; defaultStart = sequence.MinValue.Value; } else { // PostgreSQL 10 changed the default minvalue for a descending sequence, see #264 defaultMin = postgresVersion >= new Version(10, 0) ? long.MinValue : long.MinValue + 1; defaultMax = -1; Debug.Assert(sequence.MaxValue.HasValue); defaultStart = sequence.MaxValue.Value; } } else { _logger.Logger.LogWarning($"Sequence with datatype {sequence.StoreType} which isn't the expected bigint."); return; } if (sequence.StartValue == defaultStart) { sequence.StartValue = null; } if (sequence.MinValue == defaultMin) { sequence.MinValue = null; } if (sequence.MaxValue == defaultMax) { sequence.MaxValue = null; } }
/// <summary> /// This is an internal API that supports the Entity Framework Core infrastructure and not subject to /// the same compatibility standards as public APIs. It may be changed or removed without notice in /// any release. You should only use it directly in your code with extreme caution and knowing that /// doing so can result in application failures when updating to a new Entity Framework Core release. /// </summary> protected virtual SequenceBuilder VisitSequence([NotNull] ModelBuilder modelBuilder, [NotNull] DatabaseSequence sequence) { Check.NotNull(modelBuilder, nameof(modelBuilder)); Check.NotNull(sequence, nameof(sequence)); if (string.IsNullOrEmpty(sequence.Name)) { _reporter.WriteWarning(DesignStrings.SequencesRequireName); return(null); } Type sequenceType = null; if (sequence.StoreType != null) { sequenceType = _scaffoldingTypeMapper.FindMapping( sequence.StoreType, keyOrIndex: false, rowVersion: false) ?.ClrType; } if (sequenceType != null && !Sequence.SupportedTypes.Contains(sequenceType)) { _reporter.WriteWarning(DesignStrings.BadSequenceType(sequence.Name, sequence.StoreType)); return(null); } var builder = sequenceType != null ? modelBuilder.HasSequence(sequenceType, sequence.Name, sequence.Schema) : modelBuilder.HasSequence(sequence.Name, sequence.Schema); if (sequence.IncrementBy.HasValue) { builder.IncrementsBy(sequence.IncrementBy.Value); } if (sequence.MaxValue.HasValue) { builder.HasMax(sequence.MaxValue.Value); } if (sequence.MinValue.HasValue) { builder.HasMin(sequence.MinValue.Value); } if (sequence.StartValue.HasValue) { builder.StartsAt(sequence.StartValue.Value); } if (sequence.IsCyclic.HasValue) { builder.IsCyclic(sequence.IsCyclic.Value); } return(builder); }
protected virtual bool AcceptSequence(DatabaseSequence sequence) => true;
IEnumerable <DatabaseSequence> GetSequences( NpgsqlConnection connection, IList <DatabaseTable> tables, Func <string, string> schemaFilter) { using (var command = connection.CreateCommand()) { command.CommandText = @" SELECT sequence_schema, sequence_name, data_type, start_value::bigint, minimum_value::bigint, maximum_value::bigint, increment::int, CASE WHEN cycle_option = 'YES' THEN TRUE ELSE FALSE END AS is_cyclic, ownerns.nspname AS owner_schema, tblcls.relname AS owner_table, attname AS owner_column FROM information_schema.sequences JOIN pg_namespace AS seqns ON seqns.nspname = sequence_schema JOIN pg_class AS seqcls ON seqcls.relnamespace = seqns.oid AND seqcls.relname = sequence_name AND seqcls.relkind = 'S' LEFT OUTER JOIN pg_depend AS dep ON dep.objid = seqcls.oid AND deptype='a' LEFT OUTER JOIN pg_class AS tblcls ON tblcls.oid = dep.refobjid LEFT OUTER JOIN pg_attribute AS att ON attrelid = dep.refobjid AND attnum = dep.refobjsubid LEFT OUTER JOIN pg_namespace AS ownerns ON ownerns.oid = tblcls.relnamespace"; if (schemaFilter != null) { command.CommandText += "\nWHERE " + schemaFilter("sequence_schema"); } using (var reader = command.ExecuteReader()) { while (reader.Read()) { // If the sequence is OWNED BY a column which is a serial, we skip it. The sequence will be created implicitly. if (!reader.IsDBNull(10)) { var ownerSchema = reader.GetValueOrDefault <string>("owner_schema"); var ownerTable = reader.GetValueOrDefault <string>("owner_table"); var ownerColumn = reader.GetValueOrDefault <string>("owner_column"); var ownerDatabaseTable = tables .FirstOrDefault(t => t.Name == ownerTable && t.Schema == ownerSchema); if (ownerDatabaseTable == null) { // The sequence is owned by a table that isn't being scaffolded because it was excluded // from the table selection set. Skip the sequence. continue; } var ownerDatabaseColumn = ownerDatabaseTable .Columns .FirstOrDefault(t => t.Name == ownerColumn); if (ownerDatabaseTable != null && ownerDatabaseColumn?.ValueGenerated == ValueGenerated.OnAdd) { // Don't reverse-engineer sequences which drive serial columns, these are implicitly // reverse-engineered by the serial column. continue; } } var sequence = new DatabaseSequence { Schema = reader.GetValueOrDefault <string>("sequence_schema"), Name = reader.GetValueOrDefault <string>("sequence_name"), StoreType = reader.GetValueOrDefault <string>("data_type"), StartValue = reader.GetValueOrDefault <long>("start_value"), MinValue = reader.GetValueOrDefault <long>("minimum_value"), MaxValue = reader.GetValueOrDefault <long>("maximum_value"), IncrementBy = reader.GetValueOrDefault <int>("increment"), IsCyclic = reader.GetValueOrDefault <bool>("is_cyclic") }; SetSequenceStartMinMax(sequence, connection.PostgreSqlVersion); yield return(sequence); } } } }