Welcome to the Functional Programming Zulip Chat Archive. You can join the chat here.
I'm running a database migration from MySQL -> Postgresql. I want to retain the ID values so I used Persistent's insertKey function to set the IDs when inserting records. However, when the application decides to insert a row(during normal operation, not as part of the data migration), it gets an error since apparently the primary key sequence doesn't get updated when you use insertKey. Is there a simple way to fix this w/ persistent or should I just query the max ID and fix the sequence with some rawsql?
Related: Can I grab all the EntityDefs for a TH-generated schema? That'll let me just fix every ID sequence instead of having to hardcode the sequence names and manually listing the tables that I use insertKey on...
Ended up with this:
fixIdSequences :: SqlPersistT IO ()
fixIdSequences = void $
fixIdSequence @Category >>
fixIdSequence @Product >>
fixIdSequence @Page >>
fixIdSequence @Customer >>
fixIdSequence :: forall e. (PersistEntity e) => SqlPersistT IO [Single Int]
unDBName $ tableDBName @e undefined
unDBName $ fieldDBName $ persistIdField @e
( "SELECT setval("
<> " pg_get_serial_sequence(?,?), "
<> " COALESCE(MAX(" <> key <> "), 1), "
<> " MAX(" <> key <> ") IS NOT null) "
<> "FROM \"" <> table <> "\"")
[PersistText table, PersistText key]
Wasn't sure how to get unquoted strings or double-quoted table names into rawSql :confused:
Does persistent not have prepared statements? Woof
Well preparing the statement with PersistText renders key_field as 'key_field' & I wasn't sure how to make it be just key_field w/o the single quotes
And didn't care enough to investigate further.
And table needs to be rendered as "table" instead of 'table'
It works and it's a script that only gets run once so :shrug:
iirc, in postgresql-simple, quoted object names such as tables use ?? and values use ?... maybe persistent uses the same convention?
Nope, it sees ?? as two ? that are side by side
It uses ?? in the field list for SELECT when you want to return an entire entity.