public void Test_FieldType_IsParsed_User_String() { DatabaseTypeInfo ti = _parser.GetTypeInfo(typeof(TestItemDifferentFieldType)); Assert.IsTrue(ti.DataFields[0].DataFieldType == Core.Attributes.FieldType.UserString); Assert.IsTrue(ti.DataFields[0].DataFieldString == "varchar(1000)"); }
/// <summary> /// Returns a command for creating a new table /// </summary> /// <param name="ti">The type to create a table for</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddTableCommand(DatabaseTypeInfo ti) { List <IDbCommand> toReturn = new List <IDbCommand>(); StringBuilder sb = new StringBuilder(); StringBuilder pFields = new StringBuilder(); SqlCommand cmd = new SqlCommand(); sb.AppendFormat("CREATE TABLE {0}.{1} (", ti.Schema, ti.TableName); for (int i = 0; i < ti.DataFields.Count; i++) { DataFieldInfo dfi = ti.DataFields[i]; if (i > 0) { sb.Append(","); } if (dfi.PrimaryKey) { if (pFields.Length > 0) { pFields.Append(","); } pFields.Append(dfi.FieldName); if (dfi.PropertyType == typeof(int) && ti.PrimaryKeys.Count == 1) { sb.AppendFormat("{0} {1} NOT NULL IDENTITY(1,1) ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } else { sb.AppendFormat("{0} {1} NOT NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } } else { sb.AppendFormat("{0} {1} NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } if (dfi.PrimaryKeyType != null) { SqlCommand fk = new SqlCommand(); fk.CommandText = GetForeignKeySql(dfi, ti, TypeParser.GetTypeInfo(dfi.PrimaryKeyType)); toReturn.Add(fk); } } sb.Append(") ON [PRIMARY];"); if (pFields.Length > 0) { SqlCommand pKey = new SqlCommand(); pKey.CommandText = string.Format("ALTER TABLE {0}.{1} ADD CONSTRAINT PK_{3}_{4} PRIMARY KEY CLUSTERED ({2})", ti.Schema, ti.TableName, pFields.ToString(), ti.UnEscapedSchema, ti.UnescapedTableName); toReturn.Insert(0, pKey); } cmd.CommandText = sb.ToString(); toReturn.Insert(0, cmd); return(toReturn); }
/// <summary> /// Returns a command for creating a new table /// </summary> /// <param name="ti">The type to create a table for</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddTableCommand(DatabaseTypeInfo ti) { StringBuilder sb = new StringBuilder(); StringBuilder pFields = new StringBuilder(); StringBuilder contrain = new StringBuilder(); MySqlCommand cmd = new MySqlCommand(); sb.AppendFormat("CREATE TABLE {0} (", ResolveTableName(ti, false)); for (int i = 0; i < ti.DataFields.Count; i++) { DataFieldInfo dfi = ti.DataFields[i]; if (i > 0) { sb.Append(","); } if (dfi.PrimaryKey) { if (pFields.Length > 0) { pFields.Append(","); } pFields.AppendFormat("{0}", dfi.FieldName); if (dfi.PropertyType == typeof(int) && ti.PrimaryKeys.Count == 1) { sb.AppendFormat("{0} {1} NOT NULL AUTO_INCREMENT ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } else { sb.AppendFormat("{0} {1} NOT NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } } else { sb.AppendFormat("{0} {1} NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } if (dfi.PrimaryKeyType != null && StorageEngine == MySql.StorageEngine.InnoDB) { DatabaseTypeInfo pkType = TypeParser.GetTypeInfo(dfi.PrimaryKeyType); contrain.AppendFormat(_createFKSQL, ResolveTableName(ti, false), pkType.UnescapedTableName, pkType.PrimaryKeys.First().EscapedFieldName, TranslateFkeyType(dfi.ForeignKeyType), dfi.EscapedFieldName); } } if (pFields.Length > 0) { sb.AppendFormat(",PRIMARY KEY ({0})", pFields.ToString()); } sb.Append(contrain.ToString()); sb.AppendFormat(") ENGINE = {0}", StorageEngine.ToString()); cmd.CommandText = sb.ToString(); yield return(cmd); }
/// <summary> /// Returns a command for inserting one object /// </summary> /// <param name="item">The object to insert</param> /// <returns></returns> public override IDbCommand GetInsertCommand(object item) { DatabaseTypeInfo ti = TypeParser.GetTypeInfo(item.GetType()); IDbCommand cmd = base.GetInsertCommand(item); if (!ti.IsCompilerGenerated) { if (ti.PrimaryKeys.Count == 1 && !ti.PrimaryKeys[0].SetOnInsert) { if (ti.PrimaryKeys[0].PropertyType == typeof(string)) { cmd.CommandText = cmd.CommandText.Replace("VALUES", "output inserted.* VALUES"); } else {//this will only work with int keys cmd.CommandText = cmd.CommandText.Replace(";", "; select SCOPE_IDENTITY() as " + ti.PrimaryKeys[0].EscapedFieldName); } } } return(cmd); }
/// <summary> /// Returns a command for adding a column to a table /// </summary> /// <param name="type">The type to add the column to</param> /// <param name="dfi">The column to add</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddColumnCommnad(DatabaseTypeInfo type, DataFieldInfo dfi) { List <IDbCommand> toReturn = new List <IDbCommand>(); if (dfi.PrimaryKey) { throw new DataStoreException("Adding a primary key to an existing table is not supported"); } else { SqlCommand scmd = new SqlCommand(); scmd.CommandText = string.Format("ALTER TABLE {0} ADD {1} {2} NULL;", ResolveTableName(type), dfi.EscapedFieldName, TranslateTypeToSql(dfi)); toReturn.Add(scmd); if (dfi.PrimaryKeyType != null) { SqlCommand fk = new SqlCommand(); fk.CommandText = GetForeignKeySql(dfi, type, TypeParser.GetTypeInfo(dfi.PrimaryKeyType)); toReturn.Add(fk); } } return(toReturn); }
/// <summary> /// Returns a command for adding a column to a table /// </summary> /// <param name="type">The type to add the column to</param> /// <param name="dfi">The column to add</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddColumnCommnad(DatabaseTypeInfo type, DataFieldInfo dfi) { if (dfi.PrimaryKey) { throw new DataStoreException("Adding a primary key to an existing table is not supported"); } else { MySqlCommand scmd = new MySqlCommand(); StringBuilder sb = new StringBuilder(); sb.AppendFormat("ALTER TABLE {0} ADD {1} {2} NULL", ResolveTableName(type, false), dfi.EscapedFieldName, TranslateTypeToSql(dfi)); if (dfi.PrimaryKeyType != null) { DatabaseTypeInfo pkType = TypeParser.GetTypeInfo(dfi.PrimaryKeyType); sb.Append(","); sb.AppendFormat(_addFKSql, ResolveTableName(type, false), ResolveTableName(pkType, false), dfi.EscapedFieldName, pkType.PrimaryKeys.First().EscapedFieldName, TranslateFkeyType(dfi.ForeignKeyType)); } sb.Append(";"); scmd.CommandText = sb.ToString(); yield return(scmd); } }
/// <summary> /// Returns a command for creating a new table /// </summary> /// <param name="ti">The type to create a table for</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddTableCommand(DatabaseTypeInfo ti) { StringBuilder sb = new StringBuilder(); StringBuilder pFields = new StringBuilder(); SQLiteCommand cmd = new SQLiteCommand(); StringBuilder fKey = new StringBuilder(); sb.AppendFormat("CREATE TABLE {0} (", ResolveTableName(ti, false)); for (int i = 0; i < ti.DataFields.Count; i++) { DataFieldInfo dfi = ti.DataFields[i]; if (i > 0) { sb.Append(","); } if (dfi.PrimaryKey) { sb.AppendFormat("{0} {1}", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); if (dfi.PropertyType == typeof(int)) { sb.Append(" PRIMARY KEY AUTOINCREMENT"); } else { if (pFields.Length > 0) { pFields.Append(","); } pFields.Append(dfi.EscapedFieldName); } } else { sb.AppendFormat("{0} {1}", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); if (dfi.PrimaryKeyType == null) { sb.Append(" NULL"); } } if (dfi.PrimaryKeyType != null) { if (fKey.Length > 0) { fKey.Append(","); } fKey.Append(GetForeignKeySql(dfi, ti, TypeParser.GetTypeInfo(dfi.PrimaryKeyType))); } } if (fKey.Length > 0) { sb.AppendFormat(", {0}", fKey.ToString()); } if (pFields.Length > 0) { sb.AppendFormat(", PRIMARY KEY({0})", pFields); } sb.Append(");"); cmd.CommandText = sb.ToString(); yield return(cmd); }
/// <summary> /// Returns a command for creating a new table /// </summary> /// <param name="ti">The type to create a table for</param> /// <returns></returns> public override IEnumerable <IDbCommand> GetAddTableCommand(DatabaseTypeInfo ti) { StringBuilder sb = new StringBuilder(); StringBuilder pFields = new StringBuilder(); StringBuilder contrain = new StringBuilder(); NpgsqlCommand cmd = new NpgsqlCommand(); sb.AppendFormat("CREATE TABLE {0} (", ResolveTableName(ti, false)); for (int i = 0; i < ti.DataFields.Count; i++) { DataFieldInfo dfi = ti.DataFields[i]; if (i > 0) { sb.Append(","); } if (dfi.PrimaryKey) { if (pFields.Length > 0) { pFields.Append(","); } pFields.AppendFormat("{0}", dfi.EscapedFieldName); if (dfi.PropertyType == typeof(int) && ti.PrimaryKeys.Count == 1) { sb.AppendFormat("{0} serial NOT NULL ", dfi.EscapedFieldName); } else { sb.AppendFormat("{0} {1} NOT NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } } else { sb.AppendFormat("{0} {1} NULL ", dfi.EscapedFieldName, TranslateTypeToSql(dfi)); } if (dfi.PrimaryKeyType != null) { if (contrain.Length > 0) { contrain.Append(","); } DatabaseTypeInfo pkType = TypeParser.GetTypeInfo(dfi.PrimaryKeyType); contrain.AppendFormat(_createFKSQL, Guid.NewGuid().ToString(), dfi.EscapedFieldName, ResolveTableName(pkType, false), pkType.PrimaryKeys.First().EscapedFieldName, TranslateFkeyType(dfi.ForeignKeyType)); } } if (pFields.Length > 0) { sb.AppendFormat(", CONSTRAINT \"{0}\" PRIMARY KEY ({1})", Guid.NewGuid().ToString(), pFields.ToString()); } if (contrain.Length > 0) { if (pFields.Length > 0) { sb.Append(","); } sb.Append(contrain.ToString()); } sb.Append(");"); cmd.CommandText = sb.ToString(); yield return(cmd); }
/// <summary> /// Returns the name of the table (schema.table) /// </summary> /// <param name="type">The type</param> /// <returns></returns> public override string ResolveTableName(Type type) { return(ResolveTableName(TypeParser.GetTypeInfo(type), false)); }