SqlHydra
SqlHydra copied to clipboard
sqlhydra-npgsql generation tool doesn't pick up "array" columns
It appears that for schema like following:
create table draft_item
(
topic text not null,
id text not null,
timestamp timestamptz not null,
sequence bigint not null,
payload jsonb not null,
errors text[] not null,
constraint pk_draft_items primary key (id, topic)
);
It simply skips over array type errors
, generating the following:
[<CLIMutable>]
type draft_item =
{ topic: string
id: string
timestamp: System.DateTime
sequence: int64
[<SqlHydra.ProviderDbType("Jsonb")>]
payload: string }
SqlHydra supports the standard primitive parameter types that are common to all db providers.
However, a few of the more specialized param types have been implemented by users (json, jsonb for npgsql), so I think the infrastructure should be in place to add support for more of these provider specific types like text[]
.
You can see all the types that are supported for Npgsql here.
Adding support for arrays would require the following changes:
- Adding a mapping in NpgsqlDataTypes.fs
- should map
text[]
to a .NETstring[]
- provider db type to
NpgsqlDbType.Array | NpgsqlDbType.Text
(I think)
- should map
- Adding a new table to the npgsql test .sql files that includes an array type for testing
- Adding an integration test
Welp, I'm trying to implement this feature myself. However, after forking the repo and to opening the src
directory using VS Code's remote dev container, I get the following build errors:
# error 1
No package oracle-database-preinstall-18c available.
# error 2
sed: can't read /etc/security/limits.d/oracle-database-preinstall-18c.conf: No such file or directory
I'm on an arm-based M1 Macbook Air. I've had trouble in the past building Docker containers because of my laptop's architecture.
I'd love to see the array
type in SQLHydra though 🥲
Oh no! Orace is such a pain.
TBH, I haven't used the vs code + remote dev containers setup in a long time.
I just run docker-compose up
.
If you just want to do stuff with postgres, I think you could just do docker-compose up postgresql
.
https://github.com/JordanMarr/SqlHydra/wiki/Contributing#using-visual-studio-or-rider
provider db type to NpgsqlDbType.Array | NpgsqlDbType.Text (I think)
Is that a bitwise OR operator? In F#, it's |||
.
Currently running into a syntax error. I'll see if I can figure it out :D.
Lol this works
Adding a new table to the npgsql test .sql files that includes an array type for testing
@JordanMarr Are you referring to: ./src/.devcontainer/postgresql/install.sql
?
Yup, you just need to update install.sql and then rebuild:
Updating PostgreSQL DB Schema:
- Add table to:
src/.devcontainer/postgresql/install.sql
- Run
docker-compose up --build postgresql
from thesrc/.devcontainer
folder.
Testing
- To regenerate the npgsql types in the Tests project using your changes to
SqlHydra.Npgsql
, go tosrc/Tests
folder and run:
dotnet run --project ..\SqlHydra.Npgsql --framework net6.0 -- sqlhydra-npgsql-net6.toml
(You can run the same for net5.0
if you have it installed, or just skip that if you don't).
- Add a few tests to exercise it.
LMK if you get stuck on the implementation after you get the DB updated.
SqlHydra.Npgsql v1.0.4 is released with support for text[] and integer[].
The change to the mappings ended up being fairly simple in the end: https://github.com/JordanMarr/SqlHydra/blob/f0e2496c35e1f7c6a76573d005da688f8f039554/src/SqlHydra.Npgsql/NpgsqlDataTypes.fs#L50-L56 Please try it out!
Super thanks for this @JordanMarr . Will check it out and report if any issues. 👍🏼
Yes, thank you @JordanMarr!