fulcrologic / fulcro-rad-sql Goto Github PK

Support an ::sql/native-id? true setting that mirrors RAD Datomic's functionality. I'm hoping that most SQL databases have a way to pre-fetch IDs like PostgreSQL does (where you can just pull the next from a serial). If not, there will probably need to be some post-processing of each insert to retrieve the assigned ID as you go. Should design this into the general save logic so a "driver" can be plugged in without having to use a diff algorithm.

Things like schema generation and such need to do different things based on the real database type. It would be nice to have a protocol-based driver that could be used to isolate these deps.

It would be nice to have some integration tests that do NOT run automatically (tagged on specification with :integration, and skipped via metadata setting in tests.edn). That way we could at least manually run them to check compatibility with diff dbs.

I mean, since it is just a HikariCP list of settings, this could be a single set of tests with just diff CP settings that can be applied via a JVM option like -Dconfig=config/config-test-mysql.edn where those EDN files live (with a default) on the test classpath only.

The one caveat is that test setup/seeding might vary a bit (i.e. drop current test schema, run auto-schema generation, seed)...but most should be very very similar if not identical.

(->
  ...
  (your/save-middleware)
  (sql/wrap-transaction))

would allow multiple SQL databases to safely work together, and if possible would defer FK checks until the end of the transaction (not sure that is std SQL).

Remember that the env contains connection-pools, which is a map from db name to db. Should be able to use with-transaction and replace all of those in that map in env to pass on to next handler.

to-many-join-column-query uses array_agg to fetch references with cardinality :many. It returns one row with id and array of referenced ids. Unfortunately some SQL databases does not implement array_agg. [1] [2]

How can the solution look like?

1. Move GROUP_BY from SQL to clojure group-by
2. Move to-many into VendorAdapters, use array_agg for adapters which support it and
   a. use string_agg in SQL and str/split in Clojure and use attributes to distinguish between numbers and uuids
   b. go fancy and use XML paths as suggested in [2]

My personal taste is to prefer simple solution over magic and advanced features. I might be wrong but the overhead of moving group-by to Clojure should not be large. You get the same data, but you have to process more rows in resulting dataset.

2a can be tricky and must rely on attribute definition to distinguish numbers and uuids.
2b is too fancy for me.

What kind of patch you would like to see?

[1] https://www.sqlshack.com/implement-array-like-functionality-sql-server/
[2] https://jeremy-dohmann.medium.com/performing-array-aggregations-in-microsoft-sql-server-with-xml-path-9a36a7fe33be

My client uses (Azure) SQL Server so I want to add support in fulcro-rad-sql.

One of the issues I encountered was during insertion of new rows. It fails due to SQL syntax differences. It seems that ANSI standard is NEXT VALUE FOR sequence while PostgreSQL syntax is NEXTVAL(seq). Surprisingly MS SQL Server follows ANSI here.

Patch and more issues will follow.

Would like tests to be runnable in CI without having to spin up databases. use in-memory database for tests. The demo integration should also default to H2.

The type coercion uses a build-fn (search in code). Make that support more.

Holes in current implementation include:

• Many standard SQL types (though many of those have holes in RAD transit read/write as well)
• No support for cardinality many on non-ref. Datomic supports this naturally, and SQL can do it with arrays.
• Testing and fixing all of the types listed in the current type map work would be great