Additional types

[pidu]

Using glorp, I am trying to specify platform serial or platform bigint and realising that neither exists. The serial type in my experience seems pretty standard for primary key use. Would it be hard to add support for these? I understand that array support may be a bit more involved but is also rather more rare in use than serial types.

[svenvc]

Hmm, that is indeed not good.

The list of supported/implemented types can be found in P3Converter class>>#typeMap and since int2, int4 and int8 are already supported, it can't be too hard to add support for more integer types. All that is probably needed is to find the corresponding type oid and add entries. You can also find that type oid from the error you are now seeing.

Could you try that yourself ?

[pidu]

I haven't really grokked how glorp and p3 work yet to understand what's going on. But a serial is just an integer (int4) with an autoincrementing sequence attached. Likewise a bigserial is just a int8 with a sequence. I had a look in pg_catalog.pg_type but found no references to serial or bigserial there. For bigint it's really just a synonym so one could use int8 instead.

But when describing a table column as begin of type platform serial using glorp and then creating the table I end up with a column without any autoincrementing sequence.

[svenvc]

You have to make a difference between P3 and Glorp.

P3 is a PostgreSQL client that allows you to execute textual SQL queries and process the results converting it into native types.

Glorp is an object-rdbm mapper on top of some DB client. The small class https://github.com/svenvc/P3/blob/master/P3-Glorp/P3DatabaseDriver.class.st is all there is to Glorp from the perspective of P3.

I did the following in psql command line against posgresql 9.6

create table test_1 (s smallint, i integer, b bigint, id serial);
insert into test_1 values (1, 2, 3);
insert into test_1 values (1, 2, 3);
select * from test_1;
s | i | b | id
---+---+---+----
1 | 2 | 3 | 1
1 | 2 | 3 | 2

In Pharo with P3 loaded, I did

(P3Client new url: 'psql://sven@localhost') in: [ :client |
[ client query: 'select * from test_1' ] ensure: [ client close ] ].

And got the correct results back. The data being

#(#(1 2 3 1) #(1 2 3 2))

So from the P3 standpoint all these types are supported and work correctly, AFAICT.

It seems you have some issues with Glorp. Those should be asked elsewhere, I would start with the Pharo mailing lists. Make sure to add some executable code.

Did you try running the Glorp unit tests on your setup ? When I did that a while ago, most of them were OK (like 80/90 %).

[pidu]

I do understand the general difference between Glorp and P3. I just assumed that glorp was somehow using the P3 driver for the sql statement generation when creating tables. I could have made that clearer from the get go but I saw no reference to the big* or *serial types in P3Converter supportedTypes and just assumed that the failure to create a database sequence along with the integer column was that of the driver.

I ran across this in Glorp-Platform: PostgreSQLPlatform >> serial:

^ self typeNamed: #serial ifAbsentPut: [GlorpSerialType new typeString: 'integer' ]

I interpreted that as a fallback and assumed that was just checking the capability of the driver. I was apparently mistaken. Sorry about the noise.

Entirely unrelated to this. Are you planning on adding support for prepare statements? SQL-injection is a nasty thing.

[svenvc]

If you tell your Glorp session #logging: true and open a Transcript you can see all actual SQL statements being executed.

But Glorp is pretty complex.

I have used platform serial myself and it worked as expected (longer ago, for example https://medium.com/concerning-pharo/reddit-st-in-10-cool-pharo-classes-1b5327ca0740) - I would be quite surprised if it did no longer work or if it broke, but everything is possible.

With an OO-RDBMS mapper you do not have to be scared of SQL injection I think. With pure P3 or SQL in classic statements, you just have to handle your arguments carefully. I have no immediate plans to add prepared statements.

I am closing this issue now, you will probably get more/better help on the pharo mailing lists.