public DBTableNode(EJ.DBTable table, 数据表Node parent)
: base(parent)
{
AllTableNodes.Add(this);
this.Table = table;
this.Table.PropertyChanged += Table_PropertyChanged;
}
/// <summary>
/// 生成一般的model类
/// </summary>
/// <param name="db"></param>
/// <param name="nameSpace"></param>
/// <param name="table"></param>
/// <returns></returns>
public string[] BuildSimpleTable(EJDB db, string nameSpace, EJ.DBTable table)
{
var columns = db.DBColumn.Where(m => m.TableID == table.id).ToList();
string[] codes = new string[1];
codes[0] = BuildSimpleTable(db, nameSpace, table, columns);
return(codes);
}
public CreateTableAction(EJ.DBTable table, EJ.DBColumn[] columns
, IndexInfo[] idxConfigs)
{
this.Table = table;
this.Columns = columns;
this.IDXConfigs = idxConfigs;
}
public void CreatTable()
{
EJ.DBTable table = new EJ.DBTable()
{
Name = "Table1",
id = 1,
};
EJ.DBColumn[] columns = new EJ.DBColumn[3];
columns[0] = new EJ.DBColumn()
{
Name = "Id",
IsPKID = true,
IsAutoIncrement = true,
dbType = "int",
};
columns[1] = new EJ.DBColumn()
{
Name = "Name",
dbType = "varchar",
length = "50"
};
columns[2] = new EJ.DBColumn()
{
Name = "Age",
dbType = "int",
defaultValue = "10"
};
EJ.IDXIndex[] idxConfigs = new EJ.IDXIndex[1];
idxConfigs[0] = new EJ.IDXIndex()
{
Keys = "Name,Age",
IsUnique = true,
};
var invokeDB = EntityDB.DBContext.CreateDatabaseService($"data source=\"{AppDomain.CurrentDomain.BaseDirectory}\\test.sqlite\"", EntityDB.DatabaseType.Sqlite);
var action = new EF_CreateTable_Action(table, columns, idxConfigs);
invokeDB.DBContext.BeginTransaction();
try
{
action.Invoke(invokeDB);
}
catch
{
throw;
}
finally
{
invokeDB.DBContext.RollbackTransaction();
}
}
public DataViewer(EJ.DBTable table)
{
InitializeComponent();
bindingNavigatorAddNewItem.Enabled = true;
bindingNavigatorDeleteItem.Enabled = true;
checkedListBox1.Items.Add("全选", true);
m_Table = table;
this.Text = table.Name;
var history = histories.FirstOrDefault(m => m.DBID == m_Table.DatabaseID && m.TableName == m_Table.Name);
if (history != null)
{
notSelecteds = history.NotSelecteds;
}
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos)
{
//db.ExecSqlString("drop table if exists " + table.Name.ToLower() + "");
string sqlstr;
sqlstr = @"
CREATE TABLE """ + table.Name.ToLower() + @""" (
";
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (i > 0)
{
sqlstr += ",\r\n";
}
string sqltype = getSqlType(column);
if (string.IsNullOrEmpty(column.length) == false)
{
if (sqltype.Contains("("))
{
sqltype = sqltype.Substring(0, sqltype.IndexOf("("));
}
sqltype += "(" + column.length + ")";
}
sqlstr += "\"" + column.Name.ToLower() + "\" " + sqltype;
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (column.IsPKID == true)
{
sqlstr += " PRIMARY KEY ";
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
foreach (var column in columns)
{
if (column.IsAutoIncrement == true)
{
setColumn_IsAutoIncrement(db, column, table.Name.ToLower(), true);
}
}
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var config in indexInfos)
{
createIndex(db, table.Name.ToLower(), config);
}
}
}
static void BuildSimpleTable(EJDB db, NamespaceCode namespaceCode, EJ.DBTable table, List <EJ.DBColumn> columns)
{
var pkcolumn = columns.FirstOrDefault(m => m.IsPKID == true);
CodeItem classCode = new CodeItem($"public class {table.Name} :Way.Lib.DataModel");
namespaceCode.AddItem(classCode);
classCode.Comment = table.caption;
//构造函数
classCode.AddItem(new CodeItem($"public {table.Name}()").AddString(""));
foreach (var column in columns)
{
FieldCodeItem fieldCodeItem = new FieldCodeItem($"_{column.Name}");
classCode.AddItem(fieldCodeItem);
PropertyCodeItem proCodeItem = new PropertyCodeItem(column.Name);
classCode.AddItem(proCodeItem);
proCodeItem.Comment = column.caption;
string dataType = GetLinqTypeString(column.dbType, column.CanNull.GetValueOrDefault() || column.IsAutoIncrement == true);
string eqString = "";
if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
if (column.EnumDefine.Trim().StartsWith("$"))
{
var target = column.EnumDefine.Trim().Substring(1).Split('.');
if (column.CanNull == true)
{
dataType = target[0] + "_" + target[1] + "Enum?";
}
else
{
dataType = target[0] + "_" + target[1] + "Enum";
}
}
else
{
string[] enumitems = column.EnumDefine.Replace("\r", "").Split('\n');
CodeItem codeEnum = new CodeItem($"public enum {table.Name}_{column.Name}Enum:int");
namespaceCode.AddItem(codeEnum);
CodeItem codeEnumField = new CodeItem();
for (int i = 0; i < enumitems.Length; i++)
{
var code = enumitems[i].Trim();
if (code.Length == 0)
{
continue;
}
if (code.StartsWith("//"))
{
if (code.Length > 2)
{
if (codeEnumField.Comment.Length > 0)
{
codeEnumField.Comment += "\r\n";
}
codeEnumField.Comment += code.Substring(2);
}
}
else
{
codeEnumField.Body = code;
codeEnum.AddItem(codeEnumField);
codeEnumField = new CodeItem();
}
}
if (column.CanNull == true)
{
dataType = table.Name + "_" + column.Name + "Enum?";
}
else
{
dataType = table.Name + "_" + column.Name + "Enum";
}
}
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
if (column.defaultValue.Trim().Length > 0)
{
eqString = column.defaultValue.Trim();
if (dataType == "String")
{
if (eqString.StartsWith("'") && eqString.EndsWith("'") && eqString.Length > 1)
{
eqString = eqString.Substring(1, eqString.Length - 2);
}
eqString = "\"" + eqString + "\"";
}
else if (dataType == "System.Nullable<Decimal>" || dataType == "Decimal")
{
eqString = eqString + "m";
}
else if (dataType == "System.Nullable<float>" || dataType == "float")
{
eqString = eqString + "f";
}
else if (dataType == "System.Nullable<Boolean>" || dataType == "Boolean")
{
if (eqString == "1")
{
eqString = "true";
}
else if (eqString == "0")
{
eqString = "false";
}
}
else if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
eqString = "(" + dataType + ")(" + eqString + ")";
}
eqString = "=" + eqString;
}
}
fieldCodeItem.FieldType = dataType;
proCodeItem.PropertyType = dataType;
proCodeItem.Modification = "public virtual";
proCodeItem.ItemForGet.AddString($"return _{column.Name};");
proCodeItem.ItemForSet.AddItem(new CodeItem($"if ((_{column.Name} != value))")
.AddString($"var original = _{column.Name};")
.AddString($"_{column.Name} = value;")
.AddString($"OnPropertyChanged(\"{column.Name.Trim()}\",original,value);")
);
}
var classProperties = db.classproperty.Where(m => m.tableid == table.id).ToArray();
foreach (var pro in classProperties)
{
try
{
var foreign_table = db.DBTable.FirstOrDefault(m => m.id == pro.foreignkey_tableid);
if (pro.iscollection == false)
{
var column = db.DBColumn.FirstOrDefault(m => m.id == pro.foreignkey_columnid);
classCode.AddItem(new PropertyCodeItem(pro.name)
{
PropertyType = foreign_table.Name,
Modification = "public virtual"
});
}
else
{
//与其他表多对一
classCode.AddItem(new PropertyCodeItem(pro.name)
{
PropertyType = $"ICollection<{foreign_table.Name}>",
Modification = "public virtual"
});
}
}
catch
{
}
}
}
/// <summary>
/// 生成一般的model类
/// </summary>
/// <param name="db"></param>
/// <param name="nameSpace"></param>
/// <param name="table"></param>
/// <returns></returns>
public void BuildSimpleTable(EJDB db, NamespaceCode namespaceCode, EJ.DBTable table)
{
var columns = db.DBColumn.Where(m => m.TableID == table.id).ToList();
BuildSimpleTable(db, namespaceCode, table, columns);
}
static void BuildTable(EJDB db, NamespaceCode namespaceCode, EJ.DBTable table, List <EJ.DBColumn> columns, List <string> foreignKeys)
{
var pkcolumn = columns.FirstOrDefault(m => m.IsPKID == true);
CodeItem classCode = new CodeItem($"public class {table.Name} :Way.EntityDB.DataItem");
namespaceCode.AddItem(classCode);
classCode.Comment = table.caption;
Dictionary <string, CodeItem> otherClassCode = new Dictionary <string, CodeItem>();
var discriminatorColumn = columns.FirstOrDefault(m => m.IsDiscriminator == true && !string.IsNullOrEmpty(m.EnumDefine?.Trim()));
ClassName[] classNames = null;
if (discriminatorColumn != null)
{
classNames = ParseNames(discriminatorColumn.EnumDefine).ToArray();
classCode.Attributes.Add(@"[TableConfig( AutoSetPropertyNameOnInsert = """ + discriminatorColumn.Name + @""" , AutoSetPropertyValueOnInsert=(" + table.Name + "_" + discriminatorColumn.Name + @"Enum)0)]");
foreach (var classnameitem in classNames)
{
var myClsCodeItem = new CodeItem($"public class {classnameitem.Name} :{((classnameitem.BaseName == null) ? table.Name : classnameitem.BaseName)}");
namespaceCode.AddItem(myClsCodeItem);
myClsCodeItem.Comment = classnameitem.Comment;
myClsCodeItem.Attributes.Add(@"[TableConfig(AutoSetPropertyNameOnInsert = """ + discriminatorColumn.Name + @""" , AutoSetPropertyValueOnInsert=" + table.Name + "_" + discriminatorColumn.Name + @"Enum." + classnameitem.Name + @")]");
otherClassCode[classnameitem.Name] = myClsCodeItem;
myClsCodeItem.AddString("");
}
}
else
{
classCode.Attributes.Add("[TableConfig]");
}
classCode.Attributes.Add($"[Table(\"{table.Name.ToLower()}\")]");
classCode.Attributes.Add("[Way.EntityDB.DataItemJsonConverter]");
CodeItem curClassCodeItem = null;
foreach (var column in columns)
{
if (discriminatorColumn != null && classNames != null && !string.IsNullOrEmpty(column.ClassName?.Trim()) && columns.Any(m => m.IsDiscriminator == true) &&
otherClassCode.ContainsKey(column.ClassName))
{
curClassCodeItem = otherClassCode[column.ClassName];
}
else
{
curClassCodeItem = classCode;
}
PropertyCodeItem columnCodeItem = new PropertyCodeItem(column.Name);
columnCodeItem.Modification = "public virtual";
string caption = column.caption == null ? "" : column.caption;
if (caption.Contains(","))
{
caption = caption.Substring(0, caption.IndexOf(","));
}
else if (caption.Contains("，"))
{
caption = caption.Substring(0, caption.IndexOf("，"));
}
string dataType = GetLinqTypeString(column.dbType, column.CanNull.GetValueOrDefault() || column.IsAutoIncrement == true);
string eqString = "";
if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
if (column.EnumDefine.Trim().StartsWith("$"))
{
var target = column.EnumDefine.Trim().Substring(1).Split('.');
if (column.CanNull == true)
{
dataType = target[0] + "_" + target[1] + "Enum?";
}
else
{
dataType = target[0] + "_" + target[1] + "Enum";
}
}
else
{
string[] enumitems = column.EnumDefine.Replace("\r", "").Split('\n');
CodeItem codeEnum = new CodeItem($"public enum {table.Name}_{column.Name}Enum:int");
namespaceCode.AddItem(codeEnum);
CodeItem codeEnumField = new CodeItem();
for (int i = 0; i < enumitems.Length; i++)
{
var code = enumitems[i].Trim();
if (code.Length == 0)
{
continue;
}
if (code.StartsWith("//"))
{
if (code.Length > 2)
{
if (codeEnumField.Comment.Length > 0)
{
codeEnumField.Comment += "\r\n";
}
codeEnumField.Comment += code.Substring(2);
}
}
else
{
codeEnumField.Body = code;
codeEnum.AddItem(codeEnumField);
codeEnumField = new CodeItem();
}
}
if (column.CanNull == true)
{
dataType = table.Name + "_" + column.Name + "Enum?";
}
else
{
dataType = table.Name + "_" + column.Name + "Enum";
}
}
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
if (column.defaultValue.Trim().Length > 0)
{
eqString = column.defaultValue.Trim();
if (dataType == "String")
{
if (eqString.StartsWith("'") && eqString.EndsWith("'") && eqString.Length > 1)
{
eqString = eqString.Substring(1, eqString.Length - 2);
}
eqString = "\"" + eqString + "\"";
}
else if (dataType == "System.Nullable<Decimal>" || dataType == "Decimal")
{
eqString = eqString + "m";
}
else if (dataType == "System.Nullable<float>" || dataType == "float")
{
eqString = eqString + "f";
}
else if (dataType == "System.Nullable<Boolean>" || dataType == "Boolean")
{
if (eqString == "1")
{
eqString = "true";
}
else if (eqString == "0")
{
eqString = "false";
}
}
else if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
eqString = "(" + dataType + ")(" + eqString + ")";
}
eqString = "=" + eqString;
}
}
if (column.dbType.Contains("char"))
{
try
{
columnCodeItem.Attributes.Add($"[MaxLength({Convert.ToInt32(column.length)})]");
}
catch
{
}
}
if (column.IsPKID == true)
{
columnCodeItem.Attributes.Add("[Key]");
}
if (column.IsAutoIncrement == true)
{
columnCodeItem.Attributes.Add("[DatabaseGenerated(DatabaseGeneratedOption.Identity)]");
}
if (column.CanNull == false)
{
columnCodeItem.Attributes.Add("[DisallowNull]");
//columnCodeItem.Attributes.Add("[Required]");
}
if (!string.IsNullOrEmpty(column.caption))
{
columnCodeItem.Attributes.Add($"[Display(Name = \"{column.caption.Replace("\r","").Replace("\n"," ").Replace("\"", "\\\"")}\")]");
}
columnCodeItem.Attributes.Add($"[Column(\"{column.Name.ToLower()}\")]");
columnCodeItem.PropertyType = dataType;
columnCodeItem.Comment = column.caption;
curClassCodeItem.AddString($"{dataType} _{column.Name + eqString};");
curClassCodeItem.AddItem(columnCodeItem);
columnCodeItem.ItemForGet.AddString($"return _{column.Name};");
columnCodeItem.ItemForSet.AddString($"if ((_{column.Name} != value))");
columnCodeItem.ItemForSet.AddString("{");
columnCodeItem.ItemForSet.AddString($" SendPropertyChanging(\"{column.Name.Trim()}\",_{column.Name.Trim()},value);");
columnCodeItem.ItemForSet.AddString($" _{column.Name} = value;");
columnCodeItem.ItemForSet.AddString($" SendPropertyChanged(\"{column.Name.Trim()}\");");
columnCodeItem.ItemForSet.AddString("}");
}
var classProperties = db.classproperty.Where(m => m.tableid == table.id).ToArray();
foreach (var pro in classProperties)
{
try {
var foreign_table = db.DBTable.FirstOrDefault(m => m.id == pro.foreignkey_tableid);
var column = db.DBColumn.FirstOrDefault(m => m.id == pro.foreignkey_columnid);
if (pro.iscollection == false)
{
if (column != null && column.TableID == table.id)
{
var procodeitem = new CodeItem($"public virtual {foreign_table.Name} {pro.name} {{ get; set; }}");
classCode.AddItem(procodeitem);
if (foreignKeys.Contains($"{table.Name}->{column.Name}") == false)
{
foreignKeys.Add($"{table.Name}->{column.Name}");
procodeitem.Attributes.Add(@"[ForeignKey(""" + column.Name + @""")]");
}
}
else
{
//与其他表一对一
var procodeitem = new CodeItem($"public virtual {foreign_table.Name} {pro.name} {{ get; set; }}");
classCode.AddItem(procodeitem);
}
}
else
{
if (column != null)
{
//与其他表多对一
var procodeitem = new CodeItem($"public virtual ICollection<{foreign_table.Name}> {pro.name} {{ get; set; }}");
classCode.AddItem(procodeitem);
if (foreignKeys.Contains($"{foreign_table.Name}->{column.Name}") == false)
{
foreignKeys.Add($"{foreign_table.Name}->{column.Name}");
procodeitem.Attributes.Add(@"[ForeignKey(""" + column.Name + @""")]");
}
}
}
}
catch
{
}
}
}
public void BuildTable(EJDB db, NamespaceCode namespaceCode, EJ.DBTable table, List <string> foreignKeys)
{
var columns = db.DBColumn.Where(m => m.TableID == table.id).ToList();
BuildTable(db, namespaceCode, table, columns, foreignKeys);
}
public void ChangeTable(EntityDB.IDatabaseService database, string oldTableName, string newTableName, EJ.DBColumn[] addColumns, EJ.DBColumn[] changedColumns, EJ.DBColumn[] deletedColumns, Func <List <EJ.DBColumn> > getColumnsFunc, IndexInfo[] _indexInfos)
{
oldTableName = oldTableName.ToLower();
newTableName = newTableName.ToLower();
List <IndexInfo> indexInfos = new List <IndexInfo>(_indexInfos);
bool needToDeleteTable = (deletedColumns.Length > 0 || changedColumns.Length > 0);
if (needToDeleteTable)
{
string changetoname = oldTableName + "_2";
while (Convert.ToInt32(database.ExecSqlString("select count(*) from sqlite_master where type='table' and name='" + changetoname + "'")) > 0)
{
changetoname = changetoname + "_2";
}
//删除索引
deleteAllIndex(database, oldTableName);
database.ExecSqlString("ALTER TABLE [" + oldTableName + "] RENAME TO [" + changetoname + "]");
oldTableName = changetoname;
EJ.DBTable dt = new EJ.DBTable()
{
Name = newTableName,
};
//PRAGMA table_info([project]) 用name type
var allColumns = getColumnsFunc();
for (int i = 0; i < allColumns.Count; i++)
{
var columnid = allColumns[i].id;
if (deletedColumns.Any(m => m.id == columnid) || changedColumns.Any(m => m.id == columnid))
{
allColumns.RemoveAt(i);
i--;
}
}
allColumns.AddRange(addColumns);
allColumns.AddRange(changedColumns);
CreateTable(database, dt, allColumns.ToArray(), _indexInfos);
//获取原来所有字段
List <string> oldColumnNames = new List <string>();
List <string> newColumnNames = new List <string>();
using (var dtable = database.SelectTable("select * from [" + oldTableName + "] limit 0,1"))
{
foreach (var c in dtable.Columns)
{
if (deletedColumns.Count(m => m.Name.ToLower() == c.ColumnName.ToLower()) == 0)
{
var newName = changedColumns.FirstOrDefault(m => m.BackupChangedProperties.Count(p => p.Key == "Name" && ((DataValueChangedItem)p.Value).OriginalValue.ToSafeString().ToLower() == c.ColumnName.ToLower()) > 0);
oldColumnNames.Add("[" + c.ColumnName.ToLower() + "]");
if (newName != null)
{
newColumnNames.Add("[" + newName.Name.ToLower() + "]");
}
else
{
newColumnNames.Add("[" + c.ColumnName.ToLower() + "]");
}
}
}
}
string oldfields = oldColumnNames.ToArray().ToSplitString();
string newfields = newColumnNames.ToArray().ToSplitString();
if (oldColumnNames.Count > 0)
{
//把旧表数据拷贝到新表
database.ExecSqlString("insert into [" + newTableName.ToLower() + "] (" + newfields + ") select " + oldfields + " from [" + oldTableName.ToLower() + "]");
}
database.ExecSqlString("DROP TABLE [" + oldTableName.ToLower() + "]");
}
else
{
var need2dels = checkIfIdxChanged(database, oldTableName.ToLower(), indexInfos);
foreach (string delName in need2dels)
{
deleteIndex(database, oldTableName.ToLower(), delName.ToLower());
}
if (oldTableName.ToLower() != newTableName.ToLower())
{
database.ExecSqlString("ALTER TABLE [" + oldTableName.ToLower() + "] RENAME TO [" + newTableName.ToLower() + "]");
}
foreach (var column in addColumns)
{
#region 新增字段
var dbtype = getSqliteType(column);
string sql = "alter table [" + newTableName.ToLower() + "] add [" + column.Name.ToLower() + "] " + dbtype;
if (column.IsPKID == true)
{
sql += " PRIMARY KEY ";
}
if (column.IsAutoIncrement == true)
{
sql += " AUTOINCREMENT ";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sql += " NOT";
}
sql += " NULL ";
if (dbtype.Contains("char") || dbtype.Contains("text"))
{
sql += " COLLATE NOCASE ";
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sql += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
database.ExecSqlString(sql);
#endregion
}
foreach (var config in indexInfos)
{
string keyname = newTableName.ToLower() + "_ej_" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString("_").ToLower();
string type = "";
if (config.IsUnique || config.IsClustered)
{
type += "UNIQUE ";
}
//if (config.IsClustered)
//{
// throw new Exception("sqlite暂不支持定义聚集索引");
//}
database.ExecSqlString("CREATE " + type + " INDEX " + keyname + " ON [" + newTableName.ToLower() + "](" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower() + ")");
}
}
}
public EF_CreateTable_Action(EJ.DBTable table, EJ.DBColumn[] columns, EJ.IDXIndex[] idxConfigs)
{
this.Table = table.ToJsonString().ToJsonObject <EJ.DBTable>();
this.Columns = columns.ToJsonString().ToJsonObject <EJ.DBColumn[]>();
this.Indexes = idxConfigs.ToJsonString().ToJsonObject <EJ.IDXIndex[]>();
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos)
{
//db.ExecSqlString("drop table if exists `" + table.Name + "`");
string sqlstr;
sqlstr = @"
CREATE TABLE `" + table.Name.ToLower() + @"` (
";
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (i > 0)
{
sqlstr += ",\r\n";
}
string sqltype = GetSqlType(column.dbType);
if (string.IsNullOrEmpty(column.length) == false)
{
if (sqltype.Contains("("))
{
sqltype = sqltype.Substring(0, sqltype.IndexOf("("));
}
sqltype += "(" + column.length + ")";
}
sqlstr += "`" + column.Name.ToLower() + "` " + sqltype;
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (column.IsAutoIncrement == true)
{
sqlstr += " AUTO_INCREMENT ";
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
}
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (column.IsPKID == true)
{
sqlstr += ",\r\nPRIMARY KEY (`" + column.Name.ToLower() + "`)";
}
}
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var config in indexInfos)
{
string type = "";
if (config.IsUnique || config.IsClustered)
{
type += "UNIQUE ";
}
//if (config.IsClustered)
// throw new Exception("MySql不支持定义聚集索引");
string keyname = table.Name.ToLower() + "_ej_" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString("_").ToLower();
sqlstr += (",\r\n" + type + " KEY `" + keyname + "`(" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString(",", "`{0}`").ToLower() + ")");
}
}
sqlstr += ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
db.ExecSqlString(sqlstr);
}
public EF_DeleteTable_Action(EJ.DBTable table, EJ.IDXIndex[] indexInfos)
{
this.Table = table.ToJsonString().ToJsonObject <EJ.DBTable>();
this.Indexes = indexInfos.ToJsonString().ToJsonObject <EJ.IDXIndex[]>();
}
static string BuildSimpleTable(EJDB db, string nameSpace, EJ.DBTable table, List <EJ.DBColumn> columns)
{
var pkcolumn = columns.FirstOrDefault(m => m.IsPKID == true);
StringBuilder result = new StringBuilder();
StringBuilder enumDefines = new StringBuilder();
result.Append(@"
/// <summary>
/// " + table.caption + @"
/// </summary>
public class " + table.Name + @" :Way.Lib.DataModel
{
/// <summary>
///
/// </summary>
public " + table.Name + @"()
{
}
");
foreach (var column in columns)
{
string caption = column.caption == null ? "" : column.caption;
if (caption.Contains(","))
{
caption = caption.Substring(0, caption.IndexOf(","));
}
else if (caption.Contains("，"))
{
caption = caption.Substring(0, caption.IndexOf("，"));
}
string dataType = GetLinqTypeString(column.dbType);
string att = ",DbType=\"" + column.dbType;
if (string.IsNullOrEmpty(column.length))
{
if (column.dbType.Contains("char"))
{
att += "(50)";
}
}
else
{
if (column.dbType.Contains("char"))
{
att += "(" + column.length + ")";
}
}
att += "\"";
if (column.IsPKID == true)
{
//,AutoSync= AutoSync.OnInsert ,IsPrimaryKey=true,IsDbGenerated=true
att += " ,IsPrimaryKey=true";
}
if (column.IsAutoIncrement == true)
{
att += ",IsDbGenerated=true";
}
if (column.CanNull == false)
{
att += ",CanBeNull=false";
}
string eqString = "";
if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
if (column.EnumDefine.Trim().StartsWith("$"))
{
var target = column.EnumDefine.Trim().Substring(1).Split('.');
dataType = "System.Nullable<" + target[0] + "_" + target[1] + "Enum>";
}
else
{
string[] enumitems = column.EnumDefine.Replace("\r", "").Split('\n');
enumDefines.Append(@"
/// <summary>
///
/// </summary>
public enum " + table.Name + "_" + column.Name + @"Enum:int
{
");
StringBuilder enumComments = new StringBuilder();
for (int i = 0; i < enumitems.Length; i++)
{
var code = enumitems[i].Trim();
if (code.Length == 0)
{
continue;
}
if (code.StartsWith("//"))
{
if (code.Length > 2)
{
enumComments.AppendLine("/// " + code.Substring(2));
}
}
else
{
enumDefines.Append(@"
/// <summary>
" + enumComments + @"/// </summary>
");
enumComments.Clear();
enumDefines.AppendLine(code);
}
}
enumDefines.Append("}\r\n");
dataType = "System.Nullable<" + table.Name + "_" + column.Name + "Enum>";
}
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
if (column.defaultValue.Trim().Length > 0)
{
eqString = column.defaultValue.Trim();
if (dataType == "String")
{
if (eqString.StartsWith("'") && eqString.EndsWith("'") && eqString.Length > 1)
{
eqString = eqString.Substring(1, eqString.Length - 2);
}
eqString = "\"" + eqString + "\"";
}
else if (dataType == "System.Nullable<Decimal>")
{
eqString = eqString + "m";
}
else if (dataType == "System.Nullable<float>")
{
eqString = eqString + "f";
}
else if (dataType == "System.Nullable<Boolean>")
{
if (eqString == "1")
{
eqString = "true";
}
else if (eqString == "0")
{
eqString = "false";
}
}
else if (!string.IsNullOrEmpty(column.EnumDefine) && column.dbType == "int")
{
eqString = "(" + dataType + ")(" + eqString + ")";
}
eqString = "=" + eqString;
}
}
result.Append(@"
" + dataType + @" _" + column.Name + eqString + @";
/// <summary>
/// " + column.caption + @"
/// </summary>
public virtual " + dataType + @" " + column.Name + @"
{
get
{
return this._" + column.Name + @";
}
set
{
if ((this._" + column.Name + @" != value))
{
var original = this._" + column.Name + @";
this._" + column.Name + @" = value;
this.OnPropertyChanged(""" + column.Name.Trim() + @""",original,value);
}
}
}
");
}
var classProperties = db.classproperty.Where(m => m.tableid == table.id).ToArray();
foreach (var pro in classProperties)
{
try
{
var foreign_table = db.DBTable.FirstOrDefault(m => m.id == pro.foreignkey_tableid);
if (pro.iscollection == false)
{
var column = db.DBColumn.FirstOrDefault(m => m.id == pro.foreignkey_columnid);
if (column.TableID == table.id)
{
result.Append(@"
public virtual " + foreign_table.Name + @" " + pro.name + @" { get; set; }
");
}
else
{
//与其他表一对一
result.Append(@"
public virtual " + foreign_table.Name + @" " + pro.name + @" { get; set; }
");
}
}
else
{
//与其他表多对一
result.Append(@"
public virtual ICollection<" + foreign_table.Name + @"> " + pro.name + @" { get; set; }
");
}
}
catch
{
}
}
result.Append("}}\r\n");
result.Insert(0, @"namespace " + nameSpace + @"{
" + enumDefines);
return(result.ToString());
}
static void Test(EJ.Databases dbconfig)
{
IDatabaseDesignService dbservice = EntityDB.Design.DBHelper.CreateDatabaseDesignService((EntityDB.DatabaseType)(int) dbconfig.dbType);
EntityDB.IDatabaseService db = null;
dbservice.Drop(dbconfig);
try
{
dbservice.Create(dbconfig);
db = EntityDB.DBContext.CreateDatabaseService(dbconfig.conStr, (EntityDB.DatabaseType)(int) dbconfig.dbType);
List <EJ.DBColumn> allColumns = new List <EJ.DBColumn>();
List <EntityDB.Design.IndexInfo> allindexes = new List <EntityDB.Design.IndexInfo>();
#region CreateTable
if (true)
{
EJ.DBTable tableUser = new EJ.DBTable();
tableUser.Name = "User";
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Name",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
CreateTableAction _CreateTableAction = new CreateTableAction(tableUser, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
DeleteTableAction _delaction = new DeleteTableAction(tableUser.Name);
_delaction.Invoke(db);
//再次创建
_CreateTableAction.Invoke(db);
}
#endregion
allColumns.Clear();
allindexes.Clear();
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C1",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C2",
dbType = "varchar",
length = "50",
defaultValue = "abc"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C3",
dbType = "int",
defaultValue = "8"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Text1",
dbType = "text",
});
//索引
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C1" },
IsUnique = true,
});
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C2", "C3" },
});
EJ.DBTable table = new EJ.DBTable();
table.Name = "Test";
#region CreateTable
if (true)
{
CreateTableAction _CreateTableAction = new CreateTableAction(table, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region 测试自增长id
if (true)
{
Way.EntityDB.CustomDataItem dataitem = new EntityDB.CustomDataItem("test", "id", null);
dataitem.SetValue("c1", "C1");
dataitem.SetValue("c2", "C2");
dataitem.SetValue("c3", 3);
db.Insert(dataitem);
if (dataitem.GetValue("id") == null)
{
throw new Exception("测试自增长id失败");
}
db.ExecSqlString("delete from test");
}
#endregion
#region ChangeTable1
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[2];
newcolumns[0] = (new EJ.DBColumn()
{
Name = "N0",
dbType = "varchar",
length = "30",
defaultValue = "t'b"
});
newcolumns[1] = (new EJ.DBColumn()
{
Name = "N1",
dbType = "int",
defaultValue = "18"
});
EJ.DBColumn[] changedColumns = new EJ.DBColumn[2];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "C3");
changedColumns[0].Name = "C3_changed";
changedColumns[0].dbType = "varchar";
changedColumns[0].defaultValue = "1'a";
changedColumns[0].CanNull = false;
changedColumns[0].length = "88";
changedColumns[1] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[1].IsAutoIncrement = false;
changedColumns[1].IsPKID = false;
changedColumns[1].CanNull = true;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[1];
deletecolumns[0] = allColumns.FirstOrDefault(m => m.Name == "C2");
allColumns.Remove(deletecolumns[0]);
allindexes.Clear();
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "N0", "C3_changed" },
IsUnique = true,
IsClustered = true
});
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test2", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test2";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region ChangeTable2
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[0];
EJ.DBColumn[] changedColumns = new EJ.DBColumn[1];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[0].IsAutoIncrement = true;
changedColumns[0].IsPKID = true;
changedColumns[0].CanNull = false;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[0];
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test3", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test3";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
db.DBContext.Dispose();
}
}
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns
, IndexInfo[] IDXConfigs)
{
string sqlstr;
sqlstr = @"
CREATE TABLE [" + table.Name.ToLower() + @"] (
";
foreach (EJ.DBColumn column in columns)
{
var dbtype = GetSqlServerType(column);
sqlstr += "[" + column.Name.ToLower() + "] [" + dbtype + "]";
if (dbtype.IndexOf("char") >= 0)
{
if (!string.IsNullOrEmpty(column.length))
{
sqlstr += " (" + column.length + ")";
}
else
{
sqlstr += " (50)";
}
}
else
{
if (!string.IsNullOrEmpty(column.length))
{
sqlstr += " (" + column.length + ")";
}
}
if (column.IsAutoIncrement == true)
{
sqlstr += " IDENTITY (1, 1)";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " CONSTRAINT [DF_" + table.Name.ToLower() + "_" + column.Name.ToLower() + "] DEFAULT ('" + defaultValue.Replace("'", "''") + "')";
}
sqlstr += ",";
}
if (sqlstr.EndsWith(","))
{
sqlstr = sqlstr.Remove(sqlstr.Length - 1);
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
foreach (var column in columns)
{
if (column.IsPKID == true)
{
//设为主键
db.ExecSqlString("alter table [" + table.Name.ToLower() + "] add constraint PK_" + table.Name.ToLower() + "_" + column.Name.ToLower() + " primary key ([" + column.Name.ToLower() + "])");
}
}
if (IDXConfigs != null && IDXConfigs.Length > 0)
{
foreach (var c in IDXConfigs)
{
createIndex(db, table.Name.ToLower(), c);
}
}
}
public void CreateTable(EntityDB.IDatabaseService db, EJ.DBTable table, EJ.DBColumn[] columns, IndexInfo[] indexInfos)
{
string sqlstr;
sqlstr = @"
CREATE TABLE [" + table.Name.ToLower() + @"] (
";
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
if (i > 0)
{
sqlstr += ",\r\n";
}
string dbtype = getSqliteType(column);
sqlstr += "[" + column.Name.ToLower() + "] " + getSqliteType(column) + "";
if (column.IsPKID == true)
{
sqlstr += " PRIMARY KEY ";
}
if (column.IsAutoIncrement == true)
{
sqlstr += " AUTOINCREMENT ";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sqlstr += " NOT";
}
sqlstr += " NULL ";
if (dbtype.Contains("char") || dbtype.Contains("text"))
{
sqlstr += " COLLATE NOCASE ";//查询时忽略大小写
}
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sqlstr += " DEFAULT '" + defaultValue.Replace("'", "''") + "'";
}
}
sqlstr += ")";
db.ExecSqlString(sqlstr);
if (indexInfos != null && indexInfos.Length > 0)
{
foreach (var config in indexInfos)
{
string keyname = table.Name.ToLower() + "_ej_" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString("_").ToLower();
string type = "";
if (config.IsUnique || config.IsClustered)
{
type += "UNIQUE ";
}
//if (config.IsClustered)
//{
// throw new Exception("sqlite暂不支持定义聚集索引");
//}
db.ExecSqlString("CREATE " + type + " INDEX " + keyname + " ON [" + table.Name.ToLower() + "](" + config.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower() + ")");
//CREATE UNIQUE INDEX index_t1 ON t1(a, b, c};
// 第二种：
// CREATE UNIQUE INDEX index_a_t1 ON t1(a);
//DROP INDEX IF EXISTS testtable_idx;
}
}
}