PostgreSQL type handling

[kaare]

Would you consider a PR that moves pg type handling into its own class, e.g. DBDish::Pg::Types? The problem I'm aiming at is to decouple the types a bit and make it easier to override and extend them.

My itch is in two dimensions

• In some situations, it would be nice to have your own class handling some of the base types.
• PostgreSQL's types are rather dynamic. Each enum is for example a separate type.

That means that it should be possible to add new types and replace existing ones.

[jonathanstowe]

In principle yes, but I think it would be a better PR if it implemented this for all drivers, with possible a role defining the interface (say DBDish::Types or some such.) That way there is a model for doing this for yet to be implemented drivers and also it means that there will be a somewhat consistent interface for any DBDish implementation.

[kaare]

Hi Jonathan

In principle yes, but I think it would be a better PR if it implemented this for /all/ drivers, with possible a role defining the interface (say DBDish::Types or some such.) That way there is a model for doing this for yet to be implemented drivers and also it means that there will be a somewhat consistent interface for any DBDish implementation.

Yes, there could be an API for registering the type conversion method. I'm still thinking in Perl 5 and Moose, so I would lay it out as a hash attribute with methods to add, delete, override conversions.

But there would have to be a driver specific portion as well. PostgreSQL has for example oid to type mappings, and as I told, these depend on the individual database. So tjey would have to be modifiable as well.

If this is acceptable, I will work in that direction. In my spare time, so it may take some time to finish.

/kaare

[salortiz]

There is already some code in DBDish's guts in that direction:

At the DBDish (generic) level see StatementHandle's column-type attribute, that is initialized at prepare time with default values (the expected types), but can be overridden before fetch time to the desired return type, via column-types. And at the driver level, in Pg for example, the %oid-to-type Hash holds currently known types.

The, now unused, %SQLType-Conv Hash in DBIish::Common was the start of my attempt to abstract the missing pieces.

I hope to have some spare time in the future to continue that work

On Tue, Dec 20, 2016 at 12:47 PM, Kaare Rasmussen [email protected] wrote:

Hi Jonathan

In principle yes, but I think it would be a better PR if it implemented this for /all/ drivers, with possible a role defining the interface (say DBDish::Types or some such.) That way there is a model for doing this for yet to be implemented drivers and also it means that there will be a somewhat consistent interface for any DBDish implementation.

Yes, there could be an API for registering the type conversion method. I'm still thinking in Perl 5 and Moose, so I would lay it out as a hash attribute with methods to add, delete, override conversions.

But there would have to be a driver specific portion as well. PostgreSQL has for example oid to type mappings, and as I told, these depend on the individual database. So tjey would have to be modifiable as well.

If this is acceptable, I will work in that direction. In my spare time, so it may take some time to finish.

/kaare

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/perl6/DBIish/issues/78#issuecomment-268324157, or mute the thread https://github.com/notifications/unsubscribe-auth/AI8ruvKzdNJNeofjS4XhH-68XI5YwPuDks5rKCK_gaJpZM4LRp6x .

-- Salvador Ortiz

[kaare]

Hi Salvador

So you want to do this? I don't want to waste mine or your time on double work. We could also start a branch to experiment, or something.

There is already some code in DBDish's guts in that direction:

At the DBDish (generic) level see StatementHandle's column-type attribute, that is initialized at prepare time with default values (the expected types), but can be overridden before fetch time to the desired return type, via column-types. And at the driver level, in Pg for example, the %oid-to-type Hash holds currently known types.

oid-to-type is a constant. In reality there are far more types in any database. If you use extensions are have enums, you'd like to handle those.

The, now unused, %SQLType-Conv Hash in DBIish::Common was the start of my attempt to abstract the missing pieces.

I think the correct time to settle on the data types is just around connecting to the database. Could be before, if you're sure what you have. And after if you want to (somehow) detect which ones you have.

But I also want to be able to write my own handlers. Perhaps the Money type shouldn't be Str, or the Date type could be of the My::Own::Date class. Or I may just want to decorate the Int objects with some of my own methods.

/kaare

[jonathanstowe]

I still think that it should work the same for all the drivers, whilst Pg may have a richer set of types possible than some, people writing higher level software may want to provide the same types for all the drivers (with more emulation on some than others,) so driver specific methods for identifying the types would be good, differing methods for applying the types should be avoided.

So I'd say go for it, and we wcan work on it going forward.

[kaare]

I made a pull request here: https://github.com/perl6/DBIish/pull/83 The focus is on making it possible to control the type conversion dynamically.

[jonathanstowe]

I'll take a look later :)

[salortiz]

I have a few comments:

If in Perl6 a Type is a first class citizen, why reduce it to its Str name? In particular the fallback in your getter just doesn't work!

In Perl6 the common way to generate an instance of type Foo from a Str $value, is to use the coercer syntax Foo($value). So, the only two special cases are

1. When there isn't a coercer defined from Str (for example Date)
2. When we need to mangle $value before conversion.

IMO, your %Conversion Hash should be used only to cover those cases, and its get/set API seems to me as an anti-pattern (in Perl6)

[kaare]

Hi Salvador

I have a few comments:

If in Perl6 a Type is a first class citizen, why reduce it to its Str name? In particular the fallback in your getter just doesn't work!

If Type is a first class citizen, it should be able to be passed as an argument and used as a key in a hash. Can it?

In Perl6 the common way to generate an instance of type |Foo| from a |Str| $value, is to use the coercer syntax |Foo($value)|. So, the only two special cases are

1. When there isn't a coercer defined from Str (for example |Date|)
2. When we need to mangle $value before conversion.

3. When you want to return a non-builtin class. E.g. your own Foo::Date

IMO, your |%Conversion| Hash should be used only to cover those cases, and its |get|/|set| API seems to me as an anti-pattern (in Perl6)

What would be the point to change from what it is, then?

/kaare

[jonathanstowe]

Yes you can use (and indeed constrain) types as hash keys:

[jonathan@coriolanus p6-stomp]$ perl6 -e 'my %foo{Mu:U}; %foo<bar> = 1'
Type check failed in binding to key; expected Mu:U but got Str ("bar")
  in block <unit> at -e line 1

[jonathan@coriolanus p6-stomp]$ perl6 -e 'my %foo{Mu:U}; %foo{Int} = 1; say %foo.perl'
(my Any %{Mu:U} = Int => 1)

[kaare]

Yes you can use (and indeed constrain) types as hash keys:

Thanks, The Mu:U was what I couldn't figure out; the Str was always an in-betewen solution to just get something rolling. So now the type system behaves as I want it to, and is out of the POC and WIP fase.

The PR has been updated.

/kaare

[salortiz]

@kaare, in perl6 a "builtin" Type is indistinguishable from a non-builtin one.

And I still think that the setter/getter approach isn't really needed.

Something like:

role TypeConverter does Associative {
    has Callable %!Conversions{Mu:U} handles <AT-KEY EXISTS-KEY>;

    # The role implements the conversion
    method convert (::?CLASS:D: Str $datum, Mu:U $typ) {
	with %!Conversions{$typ} -> &converter {
	    converter($datum);
	} else { # Common case
	    $typ($datum);
	}
    }
}

Allows a perl6ish API:

my %Converter is TypeConverter;

%Converter{Str} = sub (Str $s) { $s.flip };

say %Converter.convert('hola', Str).perl;
say %Converter.convert('123', Int).perl;

[kaare]

@kaare https://github.com/kaare, in perl6 a "builtin" Type is indistinguishable from a non-builtin one.

I did in fact ask for something like that on the #perl6 irc channel, but didn't get an answer.

The PR is updated.

Also, the oid to type conversion can now be altered, to allow for private types. I'm working on a enum test, but due to an upcoming travel, I have limited resources atm.

/kaare

[jonathanstowe]

I like @salortiz suggestion, I think that it would make the interface easier to use by higher level abstractions (like say an Object Relational Mapper,)

[toolforger]

Am 07.01.2017 um 12:36 schrieb Jonathan Stowe:

I like @salortiz https://github.com/salortiz suggestion, I think that it would make the interface easier to use by higher level abstractions (like say an Object Relational Mapper,)

I agree to that, but I think the primary goal shouldn't be ORM (building an ORM is an exponentially uphill battle), but an SQL DSL like Jooq (http.jooq.org); an SQL DSL would be what you'd want to do for Perl6 anyway ;-)

[jonathanstowe]

I don't think we're concerned about what the "primary goal" might be here ;-) It's more about making what people might consider to be important or useful possible :)

[toolforger]

I don't think we're concerned about what the "primary goal" might be here ;-) It's more about making what people might consider to be important or useful possible :)

I agree that enablement is indeed the primary goal. Maybe I can provide a clearer description of the points I was trying to make:

• That while the obvious and well-known goal is enabling ORMs, there's also the goal of enabling SQL DSLs;
• That my personal experience with ORMs had led me to the conclusion that building one is not really worth the effort (YMMV), so in case of design conflicts between ORM enablement and SQL DSL enablement, I'd opt for the latter.

[jonathanstowe]

Sure, and we look forward to your contributions in this area, however this is about the merits or otherwise of @kaare's PR - there is an emergent design philosophy in DBIish whereby it should avoid higher level abstractions which are properly the place of other libraries yet should provide an idiomatic Perl 6 interface that enables those things to be made nicely.

I'm quite happy to have this discussion but this probably not the place to be going into it :)

[kaare]

Sure, and we look forward to your contributions in this area, however this is about the merits or otherwise of @kaare https://github.com/kaare's PR - there is an emergent design philosophy in DBIish whereby it should avoid higher level abstractions which are properly the place of other libraries yet should provide an idiomatic Perl 6 interface that enables those things to be made nicely.

I'm quite happy to have this discussion but this probably not the place to be going into it :)

My main concern is to be able to build on top of DBIish instead of reinventing the DB layer all over.

If people will build ORMs or just some local enhancements, it should be possible, and hopefully even easy to plug in on top.

Handling enums in PostgreSQL is one example. I expect to write a module specifically for that. It's not the domain of DBIish, but DBIish can't handle them right now. There are also lots of extensions for PostgreSQL with their own data types.

/kaare

[abraxxa]

SQL is a DSL for relational databases, why would one invent one on top of it?

For me it's still not clear what the goal of this PR is. Defining the RDBMS datatype to Perl 6 datatype conversion or something else? Imho the former should use builtin Perl 6c types which aren't configurable, a layer on top of DBIish (like an ORM) can still convert those to something else.

[toolforger]

Am 08.01.2017 um 19:17 schrieb Alexander Hartmaier:

SQL /is/ a DSL for relational databases, why would one invent one on top of it?

Because the DSL is not on top of SQL, it is to compose SQL from independently constructed snippets. String concatenation is far too unwieldy, unreadable, and unmaintainable to be an option for that. See http://jooq.org for examples of the best that can currently be done in Java for this task.

[kaare]

SQL /is/ a DSL for relational databases, why would one invent one on top of it?

Beats me. I translated to "Embedding SQL in the code itself", which really only leads to trouble IMHO. But I'm surely wrong.

For me it's still not clear what the goal of this PR is. Defining the RDBMS datatype to Perl 6 datatype conversion or something else? Imho the former should use builtin Perl 6c types which aren't configurable, a layer on top of DBIish (like an ORM) can still convert those to something else.

Hardwiring anything is bad in my book. Forcing a layer on top of DBIish for anyone who wants something other than the default is bad in my book, And not being able to handle the types of the underlying database is bad in my book.

/kaare

[abraxxa]

It's called 'standardization' which Perl 6 does finally for such basic types like DateTimes so you don't end up with multiple modules in the dependency chain doing the same thing slightly different.

If the type conversion is variable, code on top of DBIish might need to introspect it to not make false assumptions which is tricky and prone to errors.

A layer on top is nothing more than subclassing, is that bad too in your dead wood?

I'm not against exposing all datatypes of the supported RDBMS but I want the standard SQL ones map to the same, core 6c or DBDish defined types to being able to write RDBMS-portable code.

[toolforger]

Full database portability is really difficult to achieve. Date-time types can have massively different granularity across databases (and I doubt that any two databases have the same set of available granularities). Even strings aren't safe: Oracle stores empty strings as NULL (but handles them easily in PL/SQL blocks, I think the conversion happens when data is written to disk).

This is one of the very unpleasant aspect of dealing with multiple database backends. You're essentially restricted to integer and decimal-fraction types, which means you can't easily work with date-time types when doing manual SQLs.

In my book (wood or not), I'd probably want to offer both portable (and restrictive) mode, and full, nonportable RDBMS datatype pass-through, because there are use cases for both modes.

[abraxxa]

Date(Time) Perl 6 types should work transparent with the corresponding RDBMS types. Losing precision on storage might happen and should be documented. Is there than that which we could do?

[toolforger]

Am 09.01.2017 um 09:27 schrieb Alexander Hartmaier:

Date(Time) Perl 6 types should work transparent with the corresponding RDBMS types. Losing precision on storage might happen

Which is nonportable (sometimes critically so), but I guess I just got ticked off by the very idea of having a portable RDBMS app. (There are more sources of nonportability, most become worse in jerks and stutters as the DB size grows.)

and should be documented.

I think yes.

Is there than that which we could do?

Maybe recommend that driver writers set up unit tests for primitive type limits (minimum/maximum value, minimum/maximum granularity). Doable, since (AFAIK) all commercial RDBMSes have a free-as-beer developer edition. Still a lot of work to make such a setup automatically reproducible on other people's machines, hence recommendation instead of requirement.

[salortiz]

As the associated PR addresses the originally stated issue, I'll merge @kaare's current work and then push a few minor cosmetic changes on top of it (like remove the now unused arguments).

And as time permits, I'll change bundled drivers to make them aware of new TypeConverter mechanism.

[rbt]

dbh.Converter is sufficient for extracting data from the database but there needs to be another converter which runs during sth.execute() for importing data. Since these will probably use dbh assistance functions for encoding the data, it seems appropriate to tie them into the connection handle directly.

Consider the below example for round-tripping a measurement including relevant details into a record type.

/* SQL
CREATE DOMAIN units text check ('g', 'm', 'l');
CREATE TYPE measurement AS (numeric(6) value, unit units, accuracy_digits int2); 
*/

class Measurement {
	has $.value;
	has $.unit;
	has $.accuracy;
}

$dbh.Converter-retrieve = method (--> Measurement) {
	# Decodes to positional array. Yet to be written conversion, similar to array pg-array-str
	my @record = $dbh.pg-record-str(self);
	Measurement.new(value => @record[0], unit => @record[1], accuracy => @record[2]);
};
$dbh.dynamic-types{12345} = Measurement;  # pg_type.oid for measurement

$dbh.Converter-store = method (Measurement $m --> Str) {
	# Encodes to DB Record type. Yet to be written conversion
        # Takes record type and values.
	$dbh.pg-to-record('measurement', $m.value, $m.unit, $m.accuracy);
};

# This INSERT could build the record from 3 separate pieces but that's much harder for
# a variable length items like JSON, XML, HStore objects and requires every query to
# contain knowledge on how to build that record; or Measurement object to know how to
# escape for the database protocol.
$dbh.prepare('INSERT INTO m VALUES (?)');
$dbh.execute($measurement-object);

I would like to rename dbh.Converter to Converter-retrieve and add a new Converter-store. Since converters are poorly documented it doesn't seem like there would be very many users of it.

Any thoughts?