Add some kind of db driver concept (protocol-based)

[awkay]

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.

[anb]

I'm working on sqlite support and schema generation is indeed a bit different:

• Can't add a PRIMARY KEY once a table has been created
• Can't create a table without columns
• Doesn't support sequences
• Doesn't support UUID, VARCHAR, BOOLEAN, BIGINT, TIMESTAMP

I also see that it doesn't support SET CONSTRAINTS ALL DEFERRED and array_agg (which someone mentioned in another issue about sql server)

I also see that you've been working on better Postgres support, but you seem to have created another ns for that.

What would you like to see ? Different namespaces for each database types (H2, postgres, sqlite etc.) Or some kind of protocol like the vendor adapter that you did for the constraints ?

[awkay]

Honestly I created the branch because I only ever use postgresql, and just needed to be able to make that work well for a project I'm doing. I don't have the time to maintain a general SQL driver system, or to even design it. There are a number of issues that you'll run into:

• The schema generation tries to evolve things with add column (if not exists). This isn't perfect, but it works ok. The migrations are a better idea. Doing schema is just a pain. Then there's the difference on types, so arity, as you saw, can be a problem.
• Writing the code to encode a form save diff into a sequence of transactions is also a difficult problem to do well. Constraints typically mean that you have to mess around with statement order, and sometimes turn off constraints when there's any kind of dependency cycle (which people should avoid, but can easily be encoded).
• Then as you mentioned the type support. Some kind of type mapping and auto encode/decode are necessary in some cases.

Given all of those, it might actually be easier to just make different namespaces, refactor what code is actually reusable in all of them, extract the db specific stuff, and make the new nses.

Of course, that refactoring needs to use all-new nses, so we don't make a breaking change to the lib. All the existing nses need to work as-is. So the refactored code would probably just be copied to a new place (if it needs to change, and referenced if not).

[anb]

Thanks for the pointers. I don't want to be too ambitious, so I'll just make it work for sqlite in a new ns first and then we'll see.