sqlhydra-npgsql generation tool doesn't pick up "array" columns

[Swoorup]

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 }

[JordanMarr]

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 .NET string[]
  • provider db type to NpgsqlDbType.Array | NpgsqlDbType.Text (I think)
• Adding a new table to the npgsql test .sql files that includes an array type for testing
• Adding an integration test

[EverybodyKurts]

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 🥲

[JordanMarr]

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

[kurt-mueller-osumc]

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.

[kurt-mueller-osumc]

Lol this works

[kurt-mueller-osumc]

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?

[JordanMarr]

Yup, you just need to update install.sql and then rebuild:

Updating PostgreSQL DB Schema:

1) Add table to: src/.devcontainer/postgresql/install.sql 2) Run docker-compose up --build postgresql from the src/.devcontainer folder.

Testing

3) To regenerate the npgsql types in the Tests project using your changes to SqlHydra.Npgsql, go to src/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).

4) Add a few tests to exercise it.

LMK if you get stuck on the implementation after you get the DB updated.

[JordanMarr]

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!

[Swoorup]

Super thanks for this @JordanMarr . Will check it out and report if any issues. 👍🏼

[kurt-mueller-osumc]

Yes, thank you @JordanMarr!