search

tomyeh / postgresql

Dart Postgresql database library.
https://quire.io
BSD 2-Clause "Simplified" License
15 stars 7 forks source link

Parametrized lists, ±infinity date #13

Closed edlman closed 3 years ago

edlman commented 4 years ago

This PR add support for array of parameters and for ±infinity date. '-infinity' is represented in Dart as minimum possible DateTime DateTime.utc(-271821,04,20), 'infinity' is represented as maximum possible DateTime DateTime.utc(275760,09,13)

tomyeh commented 4 years ago

  1. Infinity date time: could you put a link in the comment pointing PG's doc about the definitions of these values?

  2. encodeArray: Sorry, I don't accept it. As mentioned in other pull requests, it is error prone (e.g., the list is empty). Also, I prefer to leave it for supporting PG's array type.

edlman commented 4 years ago
  1. I'm sorry, I gave a wrong name to min/max values. I've changed it from pgMin/MaxDateTime to dartMin/MaxDateTime. These values are specified in Dart DateTime doc https://api.dart.dev/stable/2.10.2/dart-core/DateTime-class.html
  2. I thought how to implement the list/array the best way. Proposed solution looks as the best to me as it allows to use the Dart list in pgsql list (e.g. select * from table where id in (@arr)) as well in pgsql array (e.g. select * from table where id = any(array[@arr]::type[]). You're right that in case of empty list the query select * from table where id in () will fail with ERROR 42601 syntax error at or near ")" but I propose to let the user (developer) to test and prevent such call. It's just like another SQL syntax error. The user can always use id = any(array[@arr]::type[]) instead of id in (@arr) which is little bit longer but it's error proof.
tomyeh commented 3 years ago

Sorry, I still don't like the implementation of array. Also, it doesn't support the update/insert statement well. In theory, it shall be as simple as

insert into Foo value(@name, @array)

In this case, the value has to be encoded with curly braces. As I mentioned, array shall be PostgreSQL's array type. We can assume it is always used in a select statement.

If it is really what you want, you can override the type converter in your environment, which is actually what I did -- we have some custom way for handling data.

tomyeh commented 3 years ago

  1. I enhanced TypeConverter to support the array type.

  2. To convert infinity to a DateTime object, please override DefaultTypeConverter.decodeDateTime instead.

    • Reason: PostgreSQL's date time range is much more than Dart can represent, so it is better to leave to application to override.