/// <summary>
/// Sqls the delete.
/// </summary>
/// <returns>
/// The delete.
/// </returns>
/// <param name='type'>
/// Type.
/// </param>
public string sqlDelete(Type type) {
StringBuilder sql = new StringBuilder();
PropertyInfo[] properties = type.GetProperties();
ModelList<string> whereFields = new ModelList<string>();
sql.Append("DELETE FROM " + this.sqlEscapeTable(type.Name));
for(int i = 0; i < properties.Length; i++) {
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])properties[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
if(primaryKeys.Length > 0 && properties[i].PropertyType.GetInterface("IModelizable") == null) {
whereFields.Add(this.sqlEscapeField(properties[i].Name) + " = @" + properties[i].Name);
}
}
sql.Append(" WHERE ");
sql.Append("(" + string.Join(",", whereFields.ToArray()) + ")");
return sql.ToString();
}
/// <summary>
/// Sqls the update.
/// </summary>
/// <returns>
/// The update.
/// </returns>
/// <param name='type'>
/// Type.
/// </param>
public string sqlUpdate(Type type) {
StringBuilder sql = new StringBuilder();
PropertyInfo[] properties = type.GetProperties();
ModelList<string> sqlFields = new ModelList<string>();
ModelList<string> sqlNames = new ModelList<string>();
ModelList<string> whereFields = new ModelList<string>();
sql.Append("UPDATE " + this.sqlEscapeTable(type.Name));
for(int i = 0; i < properties.Length; i++) {
TemplatizeAttribute[] attributes = (TemplatizeAttribute[])properties[i].GetCustomAttributes(typeof(TemplatizeAttribute), true);
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])properties[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
if(attributes.Length > 0 && attributes[0].Templatize && primaryKeys.Length == 0 && properties[i].PropertyType.GetInterface("IModelizable") == null) {
sqlNames.Add(properties[i].Name);
sqlFields.Add(this.sqlEscapeField(properties[i].Name) + " = @" + properties[i].Name);
} else if(primaryKeys.Length > 0 && properties[i].PropertyType.GetInterface("IModelizable") == null) {
whereFields.Add(this.sqlEscapeField(properties[i].Name) + " = @" + properties[i].Name);
}
}
sql.Append(" SET " + string.Join(",", sqlFields.ToArray()) + "");
sql.Append(" WHERE ");
sql.Append("(" + string.Join(",", whereFields.ToArray()) + ")");
return sql.ToString();
}
public string sqlInsertBasicForeign(Type type1, string basicName, Type type2) {
StringBuilder sql = new StringBuilder();
PropertyInfo[] properties1 = type1.GetProperties();
PropertyInfo[] properties2 = type2.GetProperties();
ModelList<string> fields = new ModelList<string>();
ModelList<string> parameters = new ModelList<string>();
string name = type1.Name + "_" + type2.Name;
for(int i = 0; i < properties1.Length; i++) {
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])properties1[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
if(primaryKeys.Length > 0) {
fields.Add(this.sqlEscapeField(type1.Name + "_" + properties1[i].Name));
parameters.Add("@" + type1.Name + "_" + properties1[i].Name);
}
}
fields.Add(this.sqlEscapeField(basicName));
parameters.Add("@" + basicName);
sql.Append("INSERT INTO " + this.sqlEscapeTable(name));
sql.Append("(" + string.Join(",", fields.ToArray()) + ") VALUES (" + string.Join(",", parameters.ToArray()) + ")");
return sql.ToString();
}
public string sqlInsert(Type type, ModelList<PropertyInfo> parameters) {
StringBuilder sql = new StringBuilder();
ModelList<string> sqlFields = new ModelList<string>();
ModelList<string> sqlNames = new ModelList<string>();
sql.Append("INSERT INTO " + this.sqlEscapeTable(type.Name));
for(int i = 0; i < parameters.Count; i++) {
TemplatizeAttribute[] attributes = (TemplatizeAttribute[])parameters[i].GetCustomAttributes(typeof(TemplatizeAttribute), true);
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])parameters[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
if((primaryKeys.Length > 0 && primaryKeys[0].AutoIncrement == false) || (primaryKeys.Length == 0 && attributes.Length > 0)) {
if(parameters[i].PropertyType.GetInterface("IModelizable") == null) {
sqlNames.Add(this.sqlEscapeField(parameters[i].Name));
sqlFields.Add("@" + parameters[i].Name);
}
}
}
sql.Append("(" + string.Join(",", sqlNames.ToArray()) + ")");
sql.Append(" VALUES ");
sql.Append("(" + string.Join(",", sqlFields.ToArray()) + ")");
sql.Append(";");
sql.Append(this.getInsertedId());
return sql.ToString();
}
/// <summary>
/// Sqls the create foreign basic table.
/// </summary>
/// <returns>
/// The create foreign basic table.
/// </returns>
/// <param name='type1'>
/// Type1.
/// </param>
/// <param name='type2'>
/// Type2.
/// </param>
public string sqlCreateForeignBasicTable(Type type1, string basicName, Type type2) {
StringBuilder sql = new StringBuilder();
PropertyInfo[] properties1 = type1.GetProperties();
ModelList<string> fields = new ModelList<string>();
ModelList<string> keys = new ModelList<string>();
for(int i = 0; i < properties1.Length; i++) {
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])properties1[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
if(primaryKeys.Length > 0) {
string sqlType = this.convertTypeToSql(properties1[i].PropertyType);
string name = this.sqlEscapeField(type1.Name + "_" + properties1[i].Name);
fields.Add(name + " " + sqlType + " NOT NULL");
keys.Add(name);
}
}
string sqlBasicType = this.convertTypeToSql(type2);
//basicName = this.sqlEscapeField(basicName);
fields.Add(this.sqlEscapeField(basicName) + " " + sqlBasicType + " NOT NULL");
if(keys.Count > 0) {
fields.Add("KEY (" + string.Join(",", keys.ToArray()) + ")");
}
sql.Append("CREATE TABLE IF NOT EXISTS " + this.sqlEscapeTable(type1.Name + "_" + basicName) + " (");
sql.Append(string.Join(",", fields.ToArray()));
sql.Append(") ENGINE=InnoDB DEFAULT CHARSET=utf8");
return sql.ToString();
}
/// <summary>
/// Sqls the create table.
/// </summary>
/// <returns>
/// The create table.
/// </returns>
/// <param name='type'>
/// Type.
/// </param>
public string sqlCreateTable(Type type) {
StringBuilder sql = new StringBuilder();
PropertyInfo[] properties = type.GetProperties();
ModelList<string> sqlFields = new ModelList<string>();
ModelList<string> primaryFields = new ModelList<string>();
ModelList<string> indexFields = new ModelList<string>();
ModelList<string> uniqueFields = new ModelList<string>();
ModelList<string> foreignFields = new ModelList<string>();
//ModelList<string> keys = new ModelList<string>();
ModelList<string> createdModelsName = new ModelList<string>();
sql.Append("CREATE TABLE IF NOT EXISTS " + this.sqlEscapeTable(type.Name) + " (");
for(int i = 0; i < properties.Length; i++) {
TemplatizeAttribute[] attributes = (TemplatizeAttribute[])properties[i].GetCustomAttributes(typeof(TemplatizeAttribute), true);
PrimaryKeyAttribute[] primaryKeys = (PrimaryKeyAttribute[])properties[i].GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
IndexAttribute[] indexes = (IndexAttribute[])properties[i].GetCustomAttributes(typeof(IndexAttribute), true);
//ForeignKeyAttribute[] foreigns = (ForeignKeyAttribute[])properties[i].GetCustomAttributes(typeof(ForeignKeyAttribute), true);
if(primaryKeys.Length > 0) {
primaryFields.Add(this.sqlEscapeField(properties[i].Name));
} else if(indexes.Length > 0 && indexes[0].Unique) {
uniqueFields.Add(this.sqlEscapeField(properties[i].Name));
} else if(indexes.Length > 0) {
indexFields.Add(this.sqlEscapeField(properties[i].Name));
}
if(attributes.Length > 0) {
string sqlType = this.convertTypeToSql(properties[i].PropertyType);
if(primaryKeys.Length > 0 && primaryKeys[0].AutoIncrement) {
sqlFields.Add(this.sqlEscapeField(properties[i].Name) + " " + sqlType + " NOT NULL AUTO_INCREMENT");
} else {
if(properties[i].PropertyType.GetInterface("IModelizable") != null && !createdModelsName.Contains(properties[i].PropertyType.ToString())) {
if(properties[i].PropertyType.GetInterface("IList") != null) {
if(properties[i].PropertyType.GetGenericArguments()[0].GetInterface("IModelizable") != null) {
foreignFields.Add(this.sqlCreateTable(properties[i].PropertyType.GetGenericArguments()[0]));
foreignFields.Add(this.sqlCreateForeignTable(type, properties[i].PropertyType.GetGenericArguments()[0]));
} else {
foreignFields.Add(this.sqlCreateForeignBasicTable(type, properties[i].Name, properties[i].PropertyType.GetGenericArguments()[0]));
}
} else {
foreignFields.Add(this.sqlCreateTable(properties[i].PropertyType));
foreignFields.Add(this.sqlCreateForeignTable(type, properties[i].PropertyType));
}
createdModelsName.Add(properties[i].PropertyType.ToString());
} else {
sqlFields.Add(this.sqlEscapeField(properties[i].Name) + " " + sqlType + " NOT NULL");
}
}
}
}
if(primaryFields.Count > 0) {
sqlFields.Add("PRIMARY KEY (" + string.Join(",", primaryFields.ToArray()) + ")");
}
if(uniqueFields.Count > 0) {
sqlFields.Add("UNIQUE KEY (" + string.Join(",", uniqueFields.ToArray()) + ")");
}
if(indexFields.Count > 0) {
sqlFields.Add("KEY (" + string.Join(",", indexFields.ToArray()) + ")");
}
sql.Append(string.Join(",", sqlFields.ToArray()));
sql.Append(") ENGINE=InnoDB DEFAULT CHARSET=utf8");
if(foreignFields.Count > 0) {
sql.Append(";" + string.Join(";", foreignFields.ToArray()));
}
return sql.ToString();
}
