Postgres arrays

Posts   
 
    
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 07-Oct-2017 21:12:24   

Hi,

How would one deal with (database first) postgres column of integer[] type? Designer happily sets its type as "string".

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 08-Oct-2017 10:43:30   

Ah, they are not supported according to docs. BTW using LLBLGenPro framework. I wonder whether there is a workaround of some type.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Oct-2017 09:09:05   

They're currently not supported indeed. One of the leftovers of v2 of npgsql, but also it's a bit of a complex thing to support (as every type can also form an array of that type).

At the moment I don't know of a workaround either, other than perhaps a typeconverter, but I think saving the data will be a problem (as it's likely, but haven't tested this, the type of the parameter isn't set properly when saving).

I've added a workitem to support these types in the future

Frans Bouma | Lead developer LLBLGen Pro
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 09-Oct-2017 09:32:38   

That's what I thought. Luckily, only a column is an array. I'll just convert that column to a string (with comma delimited values) and handle it with a typeconverter for the time being. I'd be nice if arrays become first class citizens eventually. you have already something similar with byte[] types.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Oct-2017 10:06:19   

mihies wrote:

That's what I thought. Luckily, only a column is an array. I'll just convert that column to a string (with comma delimited values) and handle it with a typeconverter for the time being. I'd be nice if arrays become first class citizens eventually. you have already something similar with byte[] types.

Yes, byte[] are a known type, but the rest is a large(r) set of types, so I have to think about either supporting int[], string[], short[] etc. vs. the root type (int, string, short) and a flag telling it's an array, for the typeshortcuts. But it'll likely be the former.

Frans Bouma | Lead developer LLBLGen Pro
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 09-Oct-2017 11:14:51   

Arrays feel more natural so I vote for former simple_smile

PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 23-Apr-2020 15:07:40   

Hi Miha,

Did you get LLBLGen working with the Postgresql arrays?

I tried using the TypeConverter approach (String to int-array), but I seem to be doing something wrong, because I can't use the typeconverter on a string field. (Allthough I see the converter being picked up by LLBLGen when I open the LLBLGen-project file)

Kind regards,

Peter

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Apr-2020 16:56:49   

Could you post the code of the typeconverter? Perhaps you defined the types in reverse order and therefore the designer doesn't accept it as a valid type converter for a string typed field?

Frans Bouma | Lead developer LLBLGen Pro
PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 23-Apr-2020 17:43:50   

Hi Frans,

That is possible. I used the BooleanNumericConverter as an inspiration, but I must have done something wrong.


[Description("Converter for mapping a property with a .NET type string onto integer-array database column")]
    public class StringArrayConverter : TypeConverter
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="StringArrayConverter"/> class.
        /// </summary>
        public StringArrayConverter()
        {
        }

        /// <summary>
        /// Returns whether this converter can convert an object of the given type to the type of this converter.
        /// </summary>
        /// <param name="context">Ignored</param>
        /// <param name="sourceType">A <see cref="T:System.Type"/> that represents the type you want to convert from.</param>
        /// <returns>
        ///  <see langword="true "/>if this converter can perform the conversion; otherwise, <see langword="false"/>.
        /// </returns>
        /// <remarks>Accepted types are: </remarks>
        public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType) => sourceType.FullName == "System.Int32[]";

        /// <summary>
        /// Returns whether this converter can convert the object to the specified type.
        /// </summary>
        /// <param name="context">Ignored</param>
        /// <param name="destinationType">A <see cref="T:System.Type"/> that represents the type you want to convert to.</param>
        /// <returns>
        ///  <see langword="true "/>if this converter can perform the conversion; otherwise, <see langword="false"/>.
        /// </returns>
        /// <remarks>Accepted types are: System.Int32[]</remarks>
        public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType) => destinationType.FullName == "System.Int32[]";

        /// <summary>
        /// Converts the given object to the type of this converter (Boolean).
        /// </summary>
        /// <param name="context">Ignored</param>
        /// <param name="culture">Ignored</param>
        /// <param name="value">The <see cref="T:System.Object"/> to convert.</param>
        /// <returns>
        /// An <see cref="T:System.Object"/> that represents the converted value, which is of type boolean.
        /// </returns>
        /// <exception cref="T:System.NotSupportedException">The conversion could not be performed.</exception>
        public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
        {
            if (value is int[] array)
            {
                return "{" + string.Join(",", array) + "}";
            }
            return null;
        }

        /// <summary>
        /// Converts the given value object to the specified type
        /// </summary>
        /// <param name="context">Ignored</param>
        /// <param name="culture">Ignored</param>
        /// <param name="value">The <see cref="T:System.Object"/> to convert.</param>
        /// <param name="destinationType">The <see cref="T:System.Type"/> to convert the <paramref name="value"/> parameter to.</param>
        /// <returns>
        /// An <see cref="T:System.Object"/> that represents the converted value. The value will be 1 if <paramref name="value"/> is true, otherwise 0
        /// </returns>
        /// <exception cref="T:System.ArgumentNullException">The <paramref name="destinationType"/> parameter is <see langword="null"/>.</exception>
        /// <exception cref="T:System.NotSupportedException">The conversion could not be performed.</exception>
        public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
        {
            switch (value)
            {
                case null:
                    return null;
                case string s when s.StartsWith("{") && s.EndsWith("}"):
                    s = s.Substring(1, s.Length - 2);
                    return s.Length > 0
                        ? s.Split(',').Select(int.Parse).ToArray()
                        : Array.Empty<int>();
                default:
                    return null;
            }
        }


        /// <summary>
        /// Creates an instance of the Type that this <see cref="T:System.ComponentModel.TypeConverter"/> is associated with (string)
        /// </summary>
        /// <param name="context">ignored.</param>
        /// <param name="propertyValues">ignored.</param>
        /// <returns>
        /// An <see cref="T:System.Object"/> of type string. It always returns an empty string for this converter.
        /// </returns>
        public override object CreateInstance(ITypeDescriptorContext context, System.Collections.IDictionary propertyValues) => string.Empty;
    }

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 23-Apr-2020 18:45:11   
  1. CreateInstance should create native instance (int[]) and not string.
  2. I suggest you to compare types in CanConvert* methods, i.e. sourceType == typeof(string)
  3. sourceType and destinationTypes are database types, string in your case.
  4. Basically switch From and To, it's the other way round

HTH

PeterVD
User
Posts: 15
Joined: 16-Jan-2007
# Posted on: 24-Apr-2020 08:33:05   

Hi Mika,

Thanks for your response.

I switched the From/To and so. I can register the typeconverter in Project:Settings:Conventions:Entity Model:Type Conversions. But I still can't put it on the array column. (Defined as a string .NET Type in Field mappings)

Maybe the simplest solution is to make it a Json field. Seems like a good compromise. to me wink

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Apr-2020 10:04:10   

CanConvertFrom checks the DB Type CanConvertTo checks the Model type. CreateInstance should return an instance of the Model type. Those three methods are called by the designer. The ConvertFrom/To are called at runtime.

So CanConvertFrom should check for Int32[]. It does. CanConvertTo should check for String, it checks for Int32[], so you have to change that.

So something like:


[Description("Converter for mapping a property with a .NET type string onto integer-array database column")]
public class StringArrayConverter : TypeConverter
{
    public StringArrayConverter()   {   }

    public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType) => sourceType?.Equals(typeof(Int32[]));

    public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType) => destinationType?.Equals(typeof(string));

    public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
    {
        if (value is int[] array)
        {
            return "{" + string.Join(",", array) + "}";
        }
        return null;
    }

    public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
    {
        switch (value)
        {
            case null:
                return null;
            case string s when s.StartsWith("{") && s.EndsWith("}"):
                s = s.Substring(1, s.Length - 2);
                return s.Length > 0
                    ? s.Split(',').Select(int.Parse).ToArray()
                    : Array.Empty<int>();
            default:
                return null;
        }
    }


    public override object CreateInstance(ITypeDescriptorContext context, System.Collections.IDictionary propertyValues) => string.Empty;
}

Frans Bouma | Lead developer LLBLGen Pro