public static void CreateColumn(string tableName, string name, FieldType type, bool isPrimaryKey, object defaultValue, bool isNullable = false, bool isUnique = false)
{
string pgType = DbTypeConverter.ConvertToDatabaseSqlType(type);
if (type == FieldType.AutoNumberField)
{
CreateAutoNumberColumn(tableName, name);
return;
}
using (var connection = DbContext.Current.CreateConnection())
{
NpgsqlCommand command = connection.CreateCommand("");
string canBeNull = isNullable && !isPrimaryKey ? "NULL" : "NOT NULL";
string sql = $"ALTER TABLE \"{tableName}\" ADD COLUMN \"{name}\" {pgType} {canBeNull}";
if (defaultValue != null && !(defaultValue is Guid && (Guid)defaultValue == Guid.Empty))
{
//var parameter = command.CreateParameter() as NpgsqlParameter;
//parameter.ParameterName = "@default_value";
//parameter.Value = defaultValue;
//parameter.NpgsqlDbType = DbTypeConverter.ConvertToDatabaseType(type);
//command.Parameters.Add(parameter);
if (type == FieldType.GuidField && isUnique)
{
sql += @" DEFAULT uuid_generate_v1() ";
}
else if (type == FieldType.DateField || type == FieldType.DateTimeField)
{
sql += @" DEFAULT now() ";
}
else if (type == FieldType.GeographyField)
{
if (!isNullable)
{
sql += " DEFAULT ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')";
}
}
else
{
var defVal = ConvertDefaultValue(type, defaultValue);
sql += $" DEFAULT {defVal}";
}
}
if (isPrimaryKey)
{
sql += $" PRIMARY KEY";
}
sql += ";";
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
private static void CreateAutoNumberColumn(string tableName, string name)
{
string pgType = DbTypeConverter.ConvertToDatabaseSqlType(FieldType.AutoNumberField);
using (var connection = DbContext.Current.CreateConnection())
{
NpgsqlCommand command = connection.CreateCommand($"ALTER TABLE \"{tableName}\" ADD COLUMN \"{name}\" {pgType};");
command.ExecuteNonQuery();
}
}
public static void CreateColumn(string tableName, string name, FieldType type, bool isPrimaryKey, object defaultValue, bool isNullable, bool isUnique,
bool overrideNulls = false, bool useCurrentTimeAsDefaultValue = false, bool generateNewId = false)
{
string pgType = DbTypeConverter.ConvertToDatabaseSqlType(type);
if (type == FieldType.AutoNumberField)
{
CreateAutoNumberColumn(tableName, name);
return;
}
using (var connection = DbContext.Current.CreateConnection())
{
NpgsqlCommand command = connection.CreateCommand("");
string canBeNull = isNullable && !isPrimaryKey ? "NULL" : "NOT NULL";
string sql = $"ALTER TABLE \"{tableName}\" ADD COLUMN \"{name}\" {pgType} {canBeNull}";
if (useCurrentTimeAsDefaultValue)
{
sql += @" DEFAULT now() ";
}
else if (generateNewId)
{
sql += @" DEFAULT uuid_generate_v1() ";
}
else
{
var defVal = ConvertDefaultValue(type, defaultValue);
if (defaultValue != null && overrideNulls)
{
string updateNullRecordsSql = $"UPDATE \"{tableName}\" SET \"{name}\" = {defVal} WHERE \"{name}\" IS NULL";
var updateCommand = connection.CreateCommand(updateNullRecordsSql);
updateCommand.ExecuteNonQuery();
}
sql += $" DEFAULT {defVal}";
}
if (isPrimaryKey)
{
sql += $" PRIMARY KEY";
}
sql += ";";
command.CommandText = sql;
command.ExecuteNonQuery();
}
}