PgShortArray Converter and Attribute missing

cthames · October 10, 2019, 5:22am

Found out today that we previously missed the PostgreSQL Short Array converters and attributes.

Note: Using [PgSqlIntArray] attribute seems to work as a work around for now, but doesn’t automatically pick up short[] typed props and need to use the attribute. This is with PostgreSQL v11 so unsure if that wouldn’t work on older versions of PostgreSQL.

The following seemed to work.

// https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/ServiceStack.OrmLite.PostgreSQL/Converters/PostgreSqlArrayConverters.cs

public class PostgreSqlShortArrayConverter : PostgreSqlArrayConverterBase<short>
{
    public override string ColumnDefinition => "short[]";
}

// https://github.com/ServiceStack/ServiceStack/blob/master/src/ServiceStack.Interfaces/DataAnnotations/CustomFieldAttribute.cs

public class PgSqlShortArrayAttribute : CustomFieldAttribute
{
    public PgSqlShortArrayAttribute() : base("short[]") { }
}

And add RegisterConverter<>
// https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/ServiceStack.OrmLite.PostgreSQL/PostgreSQLDialectProvider.cs

// Line 63
RegisterConverter<short[]>(new PostgreSqlShortArrayConverter());

cthames · October 10, 2019, 5:46am

Also, thought we somehow missed arrays for timestamp[] and timestamptz[] but noticed that the Attribute doesn’t have “Array” in the name. Not sure if that is a concern.

Line 62 PgSqlTimestampAttribute and Line 67 PgSqlTimestampTzAttribute

github.com

ServiceStack/ServiceStack/blob/v5.7/src/ServiceStack.Interfaces/DataAnnotations/CustomFieldAttribute.cs#L62


    public class PgSqlDoubleArrayAttribute : CustomFieldAttribute
    {
        public PgSqlDoubleArrayAttribute() : base("double precision[]") { }
    }


    public class PgSqlDecimalArrayAttribute : CustomFieldAttribute
    {
        public PgSqlDecimalArrayAttribute() : base("numeric[]") { }
    }


    public class PgSqlTimestampAttribute : CustomFieldAttribute
    {
        public PgSqlTimestampAttribute() : base("timestamp[]") { }
    }


    public class PgSqlTimestampTzAttribute : CustomFieldAttribute
    {
        public PgSqlTimestampTzAttribute() : base("timestamp with time zone[]") { }
    }
}

mythz · October 10, 2019, 11:32am

This support for short[] as well as new PgSqlTimestampArrayAttribute and PgSqlTimestampTzArrayAttribute attributes are now available on MyGet.

cthames · October 12, 2019, 3:14am

@mythz looks the following needs to be added to the Dictionary<> NativeTypes

{ "short[]", NpgsqlDbType.Array | NpgsqlDbType.Short },

github.com

ServiceStack/ServiceStack.OrmLite/blob/v5.7/src/ServiceStack.OrmLite.PostgreSQL/PostgreSQLDialectProvider.cs#L562


        var modelDef = GetModel(typeof(T));
        var pkName = NamingStrategy.GetColumnName(modelDef.PrimaryKey.FieldName);
        return !Normalize
            ? $" RETURNING \"{pkName}\""
            : " RETURNING " + pkName;
    }


    return "; " + SelectIdentitySql;
}


public static Dictionary<string, NpgsqlDbType> NativeTypes = new Dictionary<string, NpgsqlDbType> {
    { "json", NpgsqlDbType.Json },
    { "jsonb", NpgsqlDbType.Jsonb },
    { "hstore", NpgsqlDbType.Hstore },
    { "text[]", NpgsqlDbType.Array | NpgsqlDbType.Text },
    { "integer[]", NpgsqlDbType.Array | NpgsqlDbType.Integer },
    { "bigint[]", NpgsqlDbType.Array | NpgsqlDbType.Bigint },
    { "real[]", NpgsqlDbType.Array | NpgsqlDbType.Real },
    { "double precision[]", NpgsqlDbType.Array | NpgsqlDbType.Double },
    { "numeric[]", NpgsqlDbType.Array | NpgsqlDbType.Numeric },
    { "timestamp[]", NpgsqlDbType.Array | NpgsqlDbType.Timestamp },

mythz · October 12, 2019, 4:01am

NpgsqlDbType.Short does not exist, looks like npgsql uses NpgsqlDbType.Smallint instead.

cthames · October 12, 2019, 4:32am

Oops… Transposed PostgreSQL with C#. Good catch mythz!