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));
}