[Haskelldb-users] more on last_insert_id()

[Jeremy S. <jer...@li...>]

Here is a way to get last_insert_id() working for mysql. The nice
thing about this solution is it can be used without modifying the
library code at all. It is based, in part, on test/current_time.hs .


First I defined a module LastInsertID.hs

-->

module LastInsertID where

import Database.HaskellDB.HDBRec
import Database.HaskellDB.HDBRecUtils
import Database.HaskellDB.BoundedString
import System.Time (CalendarTime)
import Database.HaskellDB.Query (Expr, Table, Attr, baseTable)
import Database.HaskellDB.DBSpec
import Database.HaskellDB.FieldType


---------------------------------------------------------------------------
-- Table
---------------------------------------------------------------------------
lastinsertid :: Table
 ((HDBRecCons Last_insert_id (Expr Integer) HDBRecTail))

lastinsertid = baseTable "lastinsertid()" $
	       hdbMakeEntry Last_insert_id

---------------------------------------------------------------------------
-- Fields
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- Bug_id Field
---------------------------------------------------------------------------

data Last_insert_id = Last_insert_id

instance FieldTag Last_insert_id where fieldName _ = "lastInsertID"

last_insert_id :: Attr Last_insert_id Integer

last_insert_id = mkAttr Last_insert_id

<--

Then in my Main.hs I imported LastInsertId and I defined:

-- | similar to doInsert, except it returns LAST_INSERT_ID()
doInsertLastID :: forall r. (ShowRecRow r, ToPrimExprs r) => Table r -> (HDBRecTail -> r) -> IO Integer
doInsertLastID table record = 
    mysqlConnect opts $ \db -> do insert db table record
				  (r:_) <- dbQuery db lastInsertIDQ
					   ((Rel 0 ["lastInsertID"]) :: Rel ((HDBRecCons Last_insert_id (Expr Integer) HDBRecTail)))
				  return (Row r!last_insert_id)
	where lastInsertIDQ = Project [("lastInsertID", BinExpr (OpOther "last_insert_id()") (ConstExpr "") (ConstExpr ""))] Empty


now I can do something like:

filebug assigned_to short_desc product component =
    do i <- doInsertLastID B.bugs ( B.bug_id << sqlnull #
				    B.groupset << constant 0 #
				    B.assigned_to << constant assigned_to #
				    B.bug_file_loc << constant Nothing #
				    B.bug_severity << constant "normal" #
				    B.bug_status << constant "NEW" #
				    B.creation_ts << sqlnow #
				    B.delta_ts << sqlnow #
				    B.short_desc << constJust short_desc #
				    B.op_sys << constant "other" #
				    B.priority << constant "P1" #
				    B.product << constant product #
				    B.rep_platform << constJust "PC" #
				    B.reporter << constant assigned_to #
				    B.version << constant "Marlin" #
				    B.component << constant component #
				    B.resolution << constant "" #
				    B.target_milestone << constant "---" #
				    B.qa_contact << constant 0  #
				    B.status_whiteboard << constant "" #
				    B.votes << constant 0 #
				    B.keywords << constant "" #
				    B.lastdiffed << sqlnow #
				    B.everconfirmed << constant 1 #
				    B.reporter_accessible << constant 1 #
				    B.cclist_accessible << constant 1
				  )
       return i


NOTE: I originally named the field 'last_insert_id' which worked fine
under mysql 4.1.X but not under mysql 3.23.X. 

OTHER NOTE: yes, MySQL 4.1.X is required if you need sub-selects, but
since I was just doing a bunch of inserts and simple selects, 3.23
works fine.

FINAL NOTE: This is all a hack and should probably be hidden from the
user more elegantly.

Jeremy Shaw.