Setup Persistent DB types

[saurabhnanda]

[saurabhnanda]

@wz1000 do you want to add the following to this issue, or treat the separately:

• Enum support
• JSONB support
• Array support
• Building out audit logs?

[wz1000]

@saurabhnanda What do you mean by Enum support and Array support?

Also, why is JSONB support needed? Won't we be working with Haskell types and only convert to/from JSON while sending/recieving data?

[saurabhnanda]

What do you mean by Enum support and Array support?

Figuring out how Persistent can handle PG ENUMs and arrays. Check out the schema at https://github.com/vacationlabs/haskell-webapps/blob/master/ServantOpaleye/db/schema.sql to understand use-cases.

Also, why is JSONB support needed? Won't we be working with Haskell types and only convert to/from JSON while sending/recieving data?

JSONB is a datatype supported by PG. It is extremely useful in storing data that is structured, but the schema may not be well-known ahead of time. Possible use cases:

• storing the change-set in audit_logs.changes column
• storing user-defined product properties as general key-value pairs in products.properties column

[saurabhnanda]

Also, what about housekeeping columns, like created_at and updated_at, and how to deal with them in Persistent?

[wz1000]

Figuring out how Persistent can handle PG ENUMs and arrays

AFAIK, Persistent automatically converts Haskell Enums to PG enums. However, it doesn't support PG arrays.

[wz1000]

Also, what about housekeeping columns, like created_at and updated_at, and how to deal with them in Persistent?

They'll have to be dealt with manually, possibly with helper functions that take care of updating them.

[saurabhnanda]

However, it doesn't support PG arrays.

Do you want to spend time looking around some (unpublished) projects that solve this problem?

They'll have to be dealt with manually, possibly with helper functions that take care of updating them.

Here's a heads-up about this problem (because I've already spent some time with it):

• Persistent doesn't have a good way of dealing with non-nullable columns that have a default value specified by the DB
• If you make created_at and updated_at as NOT NULL then you have to always call getCurrentTime and specify those fields in the record before shipping them off to the respective Persistent function.
• This probably needs some Template Haskell to make it more palatable to use. Or, probably some generic programming.

[wz1000]

Do you want to spend time looking around some (unpublished) projects that solve this problem?

I think I can just work around it for now(one possible solution is using a bitfield with some lenses to replicate a [Bool]). If needed we can solve this later.

If you make created_at and updated_at as NOT NULL then you have to always call getCurrentTime and specify those fields in the record before shipping them off to the respective Persistent function.

This can be solved by defining custom update/insert functions that automatically manage those fields.

[saurabhnanda]

@wz1000 has this been closed by #30