private void CreateColumn(ref DatabaseTable table, Property property)
{
if (property.Name == properties[0])
{
table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(property.Name)
: property.Name,
GetTypeFromString(property.Type))
.AddLength(255)
.AddPrimaryKey();
}
else if (entities.Contains(property.Type))
{
table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(property.Name)
: property.Name,
GetTypeFromString(property.Type))
.AddLength(255)
.AddForeignKey(CreateTable(GetClassByName(property.Type)).Name);
}
else
{
table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(property.Name)
: property.Name,
GetTypeFromString(property.Type))
.AddPrecisionScale(18, 5)
.AddLength(255);
}
}
private void CreateColumn(ref DatabaseTable table, Property property)
{
if (!string.IsNullOrEmpty(property.ManyToOne))
{
var column = table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(property.Name)
: property.Name,
GetTypeFromString(property.Type))
.AddLength(255)
.AddForeignKey(CreateTable(GetClassByName(property.Type)).Name);
if (property.IsUnique)
{
column.AddUniqueKey();
}
}
else
{
var column = table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(property.Name)
: property.Name,
GetTypeFromString(property.Type))
.AddPrecisionScale(18, 5)
.AddLength(255);
if (property.IsUnique)
{
column.AddUniqueKey();
}
}
}
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");
}
public void TestIndexNotNeededForPrimaryKey()
{
//arrange
var settings = new CodeWriterSettings();
settings.CodeTarget = CodeTarget.PocoEntityCodeFirst;
settings.WriteCodeFirstIndexAttribute = true;
var classBuilder = new ClassBuilder();
var table = new DatabaseTable {
Name = "Test"
};
table.AddColumn <int>("Id").AddPrimaryKey().AddIndex("PK_TEST")
.AddColumn <string>("Category").AddNullable()
.AddColumn <string>("Name").AddNullable().AddIndex("IX_NAME")
;
var idColumn = table.PrimaryKeyColumn;
var target = new DataAnnotationWriter(true, settings);
//act
target.Write(classBuilder, idColumn);
var result = classBuilder.ToString().Trim(); //ignore lines
//assert
Assert.IsTrue(result.IndexOf("[Index", StringComparison.OrdinalIgnoreCase) == -1, "Should be just[Key]");
}
public void TestIndexMultiColumnUnique()
{
//arrange
var settings = new CodeWriterSettings();
settings.CodeTarget = CodeTarget.PocoEntityCodeFirst;
settings.WriteCodeFirstIndexAttribute = true;
var classBuilder = new ClassBuilder();
var table = new DatabaseTable {
Name = "Test"
};
var nameColumn = table.AddColumn <int>("Id").AddPrimaryKey()
.AddColumn <string>("Category").AddNullable().AddIndex("IX_NAME")
.AddColumn <string>("Name").AddNullable().AddIndex("IX_NAME")
;
table.Indexes.First().IsUnique = true;
var target = new DataAnnotationWriter(true, settings);
//act
target.Write(classBuilder, nameColumn);
var result = classBuilder.ToString().Trim(); //ignore lines
//assert
Assert.AreEqual("[Index(\"IX_NAME\", 2, IsUnique = true)]", result);
}
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);
}
private static DatabaseTable CreateTable()
{
var table = new DatabaseTable();
table.Name = "Test";
table.AddColumn("A", DbType.Int32).AddPrimaryKey("PK_TEST")
.AddColumn("B", DbType.Int32)
.AddColumn("C", DbType.String).AddLength(10).AddNullable();
return(table);
}
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);
}
/// <summary>生成建表脚本
/// 生成建表脚本
/// </summary>
/// <param name="databaseSchema"></param>
/// <returns></returns>
private string AddTableScript(DatabaseSchema databaseSchema)
{
DatabaseTable table = databaseSchema.AddTable(txtTableName.Text.Trim());
table.Description = txtTableDescription.Text.Trim();
foreach (DataGridViewRow dr in grdTable.Rows)
{
if (dr.Cells[colColumnName.Name].Value == null)
{
continue;
}
DatabaseColumn databaseColumn = GenDatabaseColumn(dr, table);
table.AddColumn(databaseColumn);
}
var migration = new DdlGeneratorFactory(m_dbType).MigrationGenerator();
return(migration.AddTable(table));
}
private void ReadProperty(XElement property, DatabaseTable table)
{
var name = (string)property.Attribute("Name");
var type = (string)property.Attribute("Type");
var nullable = (bool?)property.Attribute("Nullable") ?? true;
var maxLength = (int?)property.Attribute("MaxLength");
var precision = (int?)property.Attribute("Precision");
var scale = (int?)property.Attribute("Scale");
var defaultValue = (string)property.Attribute("DefaultValue");
var storeGeneratedPattern = (string)property.Attribute("StoreGeneratedPattern");
if (type.EndsWith("(max)", StringComparison.OrdinalIgnoreCase))
{
type = type.Substring(0, type.Length - 5);
maxLength = -1;
}
//timestamp is a varbinary(8) computed
if (type == "binary" && maxLength == 8 && (string)property.Attribute("StoreGeneratedPattern") == "Computed")
{
type = "timestamp";
maxLength = null;
}
var column = table.AddColumn(name, type);
column.DefaultValue = defaultValue;
column.Length = maxLength;
column.Precision = precision;
column.Scale = scale;
column.Nullable = nullable;
if (storeGeneratedPattern == "Identity")
{
column.IsIdentity = true;
}
FixColumn(column);
}
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 DatabaseTable CreateTable(ClassType _class)
{
var name = PrefixedText(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(_class.Name)
: _class.Name
);
DatabaseTable table = schema.FindTableByName(name);
if (table != null)
{
return(table);
}
List <Operation> keys = _class.Operations.Where(o => o is Property && o.IsIdentity).ToList <Operation>();
table = schema.AddTable(name);
foreach (var key in keys)
{
if (!string.IsNullOrEmpty(key.ManyToOne))
{
table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(key.Name)
: key.Name,
GetTypeFromString(key.Type))
.AddForeignKey(CreateTable(GetClassByName(key.Type)).Name)
.AddLength(255)
.AddPrimaryKey();
}
else
{
table.AddColumn(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(key.Name)
: key.Name,
GetTypeFromString(key.Type))
.AddLength(255)
.AddPrimaryKey();
}
}
foreach (var property in _class.Operations.Where(o => o is Property && !o.IsIdentity).ToList <Operation>())
{
try
{
CreateColumn(ref table, (Property)property);
}
catch (ArgumentException e)
{
System.Windows.Forms.MessageBox.Show(
string.Format("{0}, {1} - {2}", _class.Name, property.Name, e.Message),
"Error",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
throw;
}
}
return(table);
}
private DatabaseTable CreateTable(ClassType _class)
{
var name = PrefixedText(
useLowercaseUnderscored
? LowercaseAndUnderscoredWord(_class.Name)
: _class.Name
);
DatabaseTable table = schema.FindTableByName(name);
if (table != null)
{
return(table);
}
properties = new List <string>();
foreach (Operation operation in _class.Operations)
{
if (operation is Property)
{
properties.Add(operation.Name);
}
}
List <Property> compositeKey = new List <Property>();
int index = 0;
if (entities.Contains(_class.Operations.ToList()[0].Type))
{
for (; index <= (_class.Operations.Count() - 1); index++)
{
if (_class.Operations.ToList()[index] is Property)
{
Property property = (Property)_class.Operations.ToList()[index];
if (entities.Contains(property.Type))
{
compositeKey.Add(property);
}
else
{
break;
}
}
}
}
table = schema.AddTable(name);
if (compositeKey.Count > 1)
{
foreach (var key in compositeKey)
{
table.AddColumn(key.Name, GetTypeFromString(key.Type))
.AddForeignKey(CreateTable(GetClassByName(key.Type)).Name)
.AddLength(255)
.AddPrimaryKey();
}
}
else
{
index = 0;
}
for (; index <= (_class.Operations.Count() - 1); index++)
{
if (_class.Operations.ToList()[index] is Property)
{
Property property = (Property)_class.Operations.ToList()[index];
try
{
CreateColumn(ref table, property);
}
catch (ArgumentException e)
{
System.Windows.Forms.MessageBox.Show(
string.Format("{0}, {1} - {2}", _class.Name, property.Name, e.Message),
"Error",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
throw;
}
}
}
return(table);
}
