New problem with postgres int array colums
kamiwa wrote:
Otis wrote:
What I don't understand is that it previously did work? Who did the int[] -> string conversion? The thing is that we don't support array typed fields, so the driver falls back to the default type, varchar, so the generated code simply uses string. I checked whether Npgsql had a connection string argument to make this work but this isn't the case. I also can't find any breaking change for npgsql or other change in this area.
If this has worked before, then something did the int[]-> string conversion but it wasn't us. I also can't find anything related to this. Something changed, but we didn't change anything in this area.
I don't understand it either. I just guessed that it did work before, because in the plenty of postgres databases that we target here with LLBLGen ORM, there are quite a few tables that do use more exotic types than primitives, and it's unlikely that those tables weren't used in code.
But unlikely of course doesn't mean that it is impossible. So let us assume for now, that it has always been like this.
Things don't magically change tho 
Perhaps it's a postgresql setting, I don't know, to be honest, nor what to do in this case, as I don't see a solution, as all we do is pipe through the value we get from the datareader.
Let me come back to using a TypeConverter:
As I've just stated in the other thread,
The problem with the TypeConverter approach is, that if you write a type converter that converts from string to int[] and vice versa, you would need to be able to pick System.Int32[] (aka int[]) as .NET datatype for the entity's corresponding property. Unfortunatly appart from byte[] there is no array type supported in the LLBLGen Designer GUI.
What is it, that I'm not seeing?
Byte[] is a type that's specified in one of the drivers, so it's in the list of types, but you can return any type in the type converter and it's used as-is. If your type converter's type is int[] (so it instantiates an int[]) the designer will set the field to that type. Saving it will likely be a problem as npgsql requires special settings on the parameter for arrays (IIRC).
But I'm still baffled that it has worked at all. Somewhere there has been a magical string.Join(",", arrayValue) call
Oh 
of course, I overlooked the fact the field type in the DB is a string too.... 
One workaround could be to use a type converter to convert string <->string and internally in ConvertFrom pass the value as-is if it's a string, and if it's an int[], return string.Join(",", value);. This will keep the field a string field in the entity, and will allow you to set the type converter on the mapping as the database field is a string (according to the meta-data, as the driver thinks it is a string), and at runtime it will make sure the value is converted to a string from the int[] it gets from the datareader.
I've checked all code in the drivers and runtime for postgresql but nothing changed in the last couple of versions so that can't be it. I've asked Shay Rojansky (Lead dev of npgsql) if he knows something related to this, e.g. whether it was a feature in npgsql that was recently removed or has to be explicitly enabled. Will keep you posted what I hear back...
Otis wrote:
Oh
ConvertFrompass the value as-is if it's a string, and if it's an int[], returnstring.Join(",", value);. This will keep the field a string field in the entity, and will allow you to set the type converter on the mapping as the database field is a string (according to the meta-data, as the driver thinks it is a string), and at runtime it will make sure the value is converted to a string from the int[] it gets from the datareader.I've checked all code in the drivers and runtime for postgresql but nothing changed in the last couple of versions so that can't be it. I've asked Shay Rojansky (Lead dev of npgsql) if he knows something related to this, e.g. whether it was a feature in npgsql that was recently removed or has to be explicitly enabled. Will keep you posted what I hear back...
In that case I rather stick with the StringObjectConverter. This has the adavantage that I get the int[] from the NpgSqlReader in the the getter of the Property and it seems that when you change the ElementFieldMapping in the PersistenceInfoProvider from
this.AddElementFieldMapping("SiteAuthorNameEntity", "CostelloDistinctSiteAuthorIds", "CostelloDistinctSiteAuthorIds", false, "Varchar", 10485760, 0, 0, false, "", new SD.LLBLGen.Pro.CustomTypeConverters.StringObjectConverter(), typeof(System.String), 3);
to
this.AddElementFieldMapping("SiteAuthorNameEntity", "CostelloDistinctSiteAuthorIds", "CostelloDistinctSiteAuthorIds", false, "int[]", 10485760, 0, 0, false, "", new SD.LLBLGen.Pro.CustomTypeConverters.StringObjectConverter(), typeof(System.String), 3);
you can actually set the value with an int[] value and the adapter will even save it.
At least last night I succeed in doing so. Will have to check, whether this still works, because last night I dived into the source code of the SD.LLBLGen.Pro.DQE.PostgreSql and SD.LLBLGen.Pro.ORMSupportClasses and made two changes that simply blocked assign a type to the DbParameter when the value for the parameter is an array. NpgSql driver ist then clever enough to detect the correct parameter type.
I'll keep you posted, whether the string->object converter in combination with the change in the PersistenceInfoProvider (VarChar => Int[]) and without the changes in the LLBLGen framework does work.
I think that changing "VarChar" to "int[]" in the PersistenceInfoProvider works, because your code doesn't find a matching DbType type and thus doesn't assign a Type to the parameter either.
The int[] to string way is definetly no solution, as this leads to a crash in NpgSql with the message being that the type of the field is an int[] array which can't be updated with a Texthandler (or something alike).
All things considered I indeed think that property was never read. The string route is one that's dictated because the driver falls back to varchar (as it doesn't recognize the type) and of course far from ideal.
Changing the persistence info provider by hand is not really ideal, as it's generated code. Detecting the array as a type is done in PostgresqlDBDriver :: ConvertStringToDBType, however this isn't straightforward as there are no entries in the type mapping tables for an int array. 
Adding it on our side opens a big can of worms as array typed fields can be used in queries in theory and we don't have the APIs for that, hence we held off that boat.
Otis wrote:
All things considered I indeed think that property was never read. The string route is one that's dictated because the driver falls back to varchar (as it doesn't recognize the type) and of course far from ideal.
Changing the persistence info provider by hand is not really ideal, as it's generated code. Detecting the array as a type is done in PostgresqlDBDriver :: ConvertStringToDBType, however this isn't straightforward as there are no entries in the type mapping tables for an int array.
Adding it on our side opens a big can of worms as array typed fields can be used in queries in theory and we don't have the APIs for that, hence we held off that boat.
Well: If you've got a can of worms and a boat, go fishing!
Heh 
Good point.
So back to the problem at hand, we can't 'enable' it on the designer level for you at this stage, so I'm afraid it's back to 'not supported' territory. Not what you wanted to hear I guess, but other than that I don't think I have a lot of other options to offer.
Done It's shared static info with no locks whatsoever (as it's considered readonly), so be sure to run this method before any persistence taking place.
Otis wrote:
Done
Not enough coffee yet! What?
Otis wrote:
Done
Got it!
Done <= Refers to the thread being moved to public area
It's shared static info with no locks whatsoever (as it's considered readonly), so be sure to run this method before any persistence taking place. <= Hint for using the code.
Too old to rock'n'roll but too young to die.
Heh well, as long as everything's known, it's alright
In the meantime I've started trying to tweak the PostgresqlDBDriver to my needs.
A few posts further above
Otis wrote:
Detecting the array as a type is done in PostgresqlDBDriver :: ConvertStringToDBType, however this isn't straightforward as there are no entries in the type mapping tables for an int array.
==> ... as there are no entries in the type mapping tables for an int array.
In the LLBLGen Designer you rely on the RetrieveTableAndFieldMetaData and RetrieveViewAndFieldMetaData methods in the PostgreSqlSchemaRetriever class from the PostgreSqlDbDriver project in order to get the columns and column types of a table you're using the following query:
SELECT
c.*,
COL_DESCRIPTION(t.oid, c.ordinal_position) AS column_comment
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN pg_class t
ON c.table_name = t.relname
INNER JOIN pg_namespace s
ON c.table_schema = s.nspname AND s.oid = t.relnamespace
WHERE
table_schema = '{0}' AND
table_name = :tableName
ORDER BY
ordinal_position ASC;
Then you get the type of the column from the content in the data_type colum from INFORMATION_SCHEMA.COLUMNS.
While it is true, that any colum of type array will only have ARRAY as the assigned type in this column, you can additionally look at the udt_name column which will tell you exactly of what child-type the array is.
For an array of text, the udt_name column will state _text. For an array of int4, the udt_name column will state _int4.
So in order to detect the complete type of an Array or a Range column you need both columns.
Type declaration in NpgSQL ADO driver follows the same rule.
From https://www.npgsql.org/doc/types/basic.html#write-mappings
Note: When using Range and Array, bitwise-or NpgsqlDbType.Range or NpgsqlDbType.Array with the child type. For example, to construct the NpgsqlDbType for a int4range, write NpgsqlDbType.Range | NpgsqlDbType.Integer. To construct the NpgsqlDbType for an int[], write NpgsqlDbType.Array | NpgsqlDbType.Integer.
So in order to get the the complete type I've modified the sql query to:
SELECT
table_catalog,
table_schema,
table_name,
column_name,
ordinal_position,
column_default,
is_nullable,
case WHEN data_type = 'ARRAY' then 'ARRAY' || udt_name else data_type END as data_type,
character_maximum_length,
character_octet_length,
numeric_precision,
numeric_precision_radix,
numeric_scale,
datetime_precision,
interval_type,
interval_precision,
character_set_catalog,
character_set_schema,
character_set_name,
collation_catalog,
collation_schema,
collation_name,
domain_catalog,
domain_schema,
domain_name,
udt_catalog,
udt_schema,
udt_name,
scope_catalog,
scope_schema,
scope_name,
maximum_cardinality,
dtd_identifier,
is_self_referencing,
is_identity,
identity_generation,
identity_start,
identity_increment,
identity_maximum,
identity_minimum,
identity_cycle,
is_generated,
generation_expression,
is_updatable,
COL_DESCRIPTION(t.oid, c.ordinal_position) AS column_comment
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN pg_class t
ON c.table_name = t.relname
INNER JOIN pg_namespace s
ON c.table_schema = s.nspname AND s.oid = t.relnamespace
WHERE
table_schema = 'public' AND
table_name = 'CostelloDistinctSiteAuthorAggregation'
ORDER BY
ordinal_position ASC;
Let's see where this road will take me.
Nice!