public void TriggerWithNoQuotes()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
dt.Triggers.Add(new DatabaseTrigger
{
//with spaces, line breaks
TriggerBody = @"BEGIN
SELECT MY_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;"
});
//act
osr.PostProcessing(dt);
//assert
Assert.IsTrue(dt.HasAutoNumberColumn);
}
public void NoTriggers()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
//act
osr.PostProcessing(dt);
//assert
Assert.IsFalse(dt.HasAutoNumberColumn);
}
public void TriggerWithQuotes()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
dt.Triggers.Add(new DatabaseTrigger
{
//generated by SqlDeveloper
TriggerBody = @"CREATE OR REPLACE TRIGGER ""DB"".""MYTRIGGER"" before insert on ""DB"".""TABLE1"" for each row begin if inserting then if :NEW.""ID"" is null then select MY_SEQ.nextval into :NEW.""ID"" from dual; end if; end if; end;"
});
//act
osr.PostProcessing(dt);
//assert
Assert.IsTrue(dt.HasAutoNumberColumn);
}
private Tables LoadTables(GenerateContext context)
{
var cmd = context.Command;
if (string.IsNullOrWhiteSpace(cmd.ProviderName))
{
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// db provider must be provided");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return(new Tables());
}
if (string.IsNullOrWhiteSpace(cmd.ConnectionString))
{
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// connection string must be provided.");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return(new Tables());
}
DbProviderFactory _factory;
try
{
_factory = DbProviderFactories.GetFactory(cmd.ProviderName);
}
catch (Exception x)
{
var error = x.Message.Replace("\r\n", "\n").Replace("\n", " ");
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to load provider `{0}` - {1}", cmd.ProviderName, error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return(new Tables());
}
try
{
Tables result;
using (var conn = _factory.CreateConnection())
{
conn.ConnectionString = cmd.ConnectionString;
conn.Open();
SchemaReader reader = null;
if (_factory.GetType().Name == "MySqlClientFactory")
{
reader = new MySqlSchemaReader();
context.EscapeSqlIdentifier = sqlIdentifier => $"`{sqlIdentifier}`";
}
else if (_factory.GetType().Name == "SqlCeProviderFactory")
{
reader = new SqlServerCeSchemaReader();
}
else if (_factory.GetType().Name == "NpgsqlFactory")
{
reader = new PostGreSqlSchemaReader();
context.EscapeSqlIdentifier = sqlIdentifier => $"\"{sqlIdentifier}\"";
}
else if (_factory.GetType().Name == "OracleClientFactory")
{
reader = new OracleSchemaReader();
context.EscapeSqlIdentifier = sqlIdentifier => $"\"{sqlIdentifier.ToUpperInvariant()}\"";
}
else
{
reader = new SqlServerSchemaReader();
}
reader.outer = _outer;
result = reader.ReadSchema(conn, _factory);
// Remove unrequired tables/views
for (int i = result.Count - 1; i >= 0; i--)
{
if (cmd.SchemaName != null && string.Compare(result[i].Schema, cmd.SchemaName, true) != 0)
{
result.RemoveAt(i);
continue;
}
if (!cmd.IncludeViews && result[i].IsView)
{
result.RemoveAt(i);
continue;
}
if (Helpers.StartsWithAny(result[i].ClassName, cmd.ExcludePrefix))
{
result.RemoveAt(i);
continue;
}
}
conn.Close();
var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
foreach (var t in result)
{
t.ClassName = cmd.ClassPrefix + t.ClassName + cmd.ClassSuffix;
foreach (var c in t.Columns)
{
c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
// Make sure property name doesn't clash with class name
if (c.PropertyName == t.ClassName)
{
c.PropertyName = "_" + c.PropertyName;
}
}
}
return(result);
}
}
catch (Exception x)
{
var error = x.Message.Replace("\r\n", "\n").Replace("\n", " ");
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to read database schema - {0}", error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return(new Tables());
}
}
public string Write()
{
if (!_table.HasAutoNumberColumn)
{
return(null);
}
var txt = WriteExistingTrigger();
if (txt != null)
{
return(txt);
}
var autoNumberColumn = _table.Columns.First(x => x.IsAutoNumber);
if (autoNumberColumn.IdentityDefinition != null)
{
if (_table.DatabaseSchema != null &&
_table.DatabaseSchema.Provider.IndexOf("Oracle", StringComparison.OrdinalIgnoreCase) != -1)
{
//this is an Oracle schema with identity, so assume it's Oracle 12+ identity
return(null);
}
}
if (OracleSchemaReader.LooksLikeAutoNumberDefaults(autoNumberColumn.DefaultValue))
{
return(null);
}
var identityColumn = autoNumberColumn.Name;
string sequenceName = _table.Name + "_SEQUENCE";
int i = 0;
var schema = _table.DatabaseSchema;
if (schema != null)
{
while (FindSequenceName(schema, sequenceName))
{
i++;
sequenceName = _table.Name + "_SEQUENCE" + i;
}
}
string triggerName = _table.Name + "_PK_TRIGGER";
i = 0;
while (FindTriggerName(triggerName))
{
i++;
triggerName = _table.Name + "_PK_TRIGGER" + i;
}
var sb = new StringBuilder();
sb.AppendLine("-- sequence for " + _table.Name);
sb.AppendLine(WriteSequence(sequenceName));
sb.AppendLine();
sb.AppendLine("-- auto-increment trigger for " + _table.Name);
sb.AppendLine(WriteTrigger(triggerName, sequenceName, identityColumn));
return(sb.ToString());
}
