Slightly safer way to use pseudo/hidden columns?

debug-ito commented 9 years ago

In SQLite3, we can use rowid pseudo-column and last_insert_rowid() function to query the last inserted record.

To build such a query with HRR, I tried

lastInsertRecord :: Table r -> Pi r v -> Relation () v
lastInsertRecord tab selector = relation $ do
  record <- query $ table tab
  let rowid = unsafeProjectSql "rowid"
      last_rowid = unsafeProjectSql "last_insert_rowid()"
  wheres $ rowid .=. last_rowid
  return (record ! selector)

This gives me a Relation like:

>>> lastInsertRecord tableOfSample id'
SELECT ALL T0.sample_id AS f0, T0.val AS f1 FROM MAIN.sample T0 WHERE (rowid = last_insert_rowid())

This works. However, because rowid is a pseudo-column of sample table, it should be T0.rowid in the above query.

So, is there any way to specify rowid as a pseudo-column of a table?

khibino commented 8 years ago

Thanks for your reporting.

I added the support of Register monad for INSERT statement in https://github.com/khibino/haskell-relational-record/commit/be1e318fc848173a950596b329c8aa6140a2b2fb , https://github.com/khibino/haskell-relational-record/commit/7d35bd3119b768d705197ddfba084bf5a2f102da and https://github.com/khibino/haskell-relational-record/commit/002bbcbdc058f188d32fee25ec096369dba7e604 .

And, I updated the example of constant INSERT using Register monad. ( https://github.com/khibino/haskell-relational-record/commit/bd2455f171e098431843d00a397a48f45fc3ddc1 )

debug-ito commented 8 years ago

Great work! Thank you.

But I think the commits you mentioned are related to #20. Not this issue.

khibino / haskell-relational-record

Slightly safer way to use pseudo/hidden columns? #22