Example #1
0
        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();
            }
        }
Example #2
0
        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();
            }
        }
Example #3
0
        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();
            }
        }