haskell - How to let default values come from the database? -
why user object still have nothing createdat , updatedat? why fields not getting assigned database?
share [mkpersist sqlsettings, mkmigrate "migrateall"] [persistlowercase| user email string createdat utctime maybe default=current_time updatedat utctime maybe default=current_time deriving show |] main = runsqlite ":memory:" $ runmigration migrateall userid <- insert $ user "saurabhnanda@gmail.com" nothing nothing liftio $ print userid user <- userid case user of nothing -> liftio $ putstrln ("coulnt find userid=" ++ (show userid)) u -> liftio $ putstrln ("user=" ++ (show user)) output:
userkey {unuserkey = sqlbackendkey {unsqlbackendkey = 1}} user=just (user {useremail = "saurabhnanda@gmail.com", usercreatedat = nothing, userupdatedat = nothing})
(edit: see solution below using triggers)
the problem: default values don't override explicitly setting column null
per sqlite docs:
the default clause specifies default value use column if no value explicitly provided user when doing insert.
the issue when persistent doing insert, it's specifying createdat , updatedat columns null:
[debug#sql] insert "user"("email","created_at","updated_at") values(?,?,?); [persisttext "saurabhnanda@gmail.com",persistnull,persistnull] to reach conclusion, modified snippet add sql logging (i copied source of runsqlite , changed log stdout). used file instead of in-memory database open database in sqlite editor , verify default values being set.
-- pragmas , imports taken snippet in yesod book. of them may superfluous. {-# language emptydatadecls #-} {-# language flexiblecontexts #-} {-# language gadts #-} {-# language generalizednewtypederiving #-} {-# language multiparamtypeclasses #-} {-# language overloadedstrings #-} {-# language quasiquotes #-} {-# language templatehaskell #-} {-# language typefamilies #-} import database.persist import database.persist.sqlite import database.persist.th import control.monad.io.class (liftio) import data.time import control.monad.trans.resource import control.monad.logger import control.monad.io.class import data.text share [mkpersist sqlsettings, mkmigrate "migrateall"] [persistlowercase| user email string createdat utctime maybe default=current_time updatedat utctime maybe default=current_time deriving show |] runsqlite2 :: (monadbasecontrol io m, monadio m) => text -- ^ connection string -> sqlpersistt (loggingt (resourcet m)) -- ^ database action -> m runsqlite2 connstr = runresourcet . runstdoutloggingt . withsqliteconn connstr . runsqlconn main = runsqlite2 "bar.db" $ runmigration migrateall userid <- insert $ user "saurabhnanda@gmail.com" nothing nothing liftio $ print userid user <- userid case user of nothing -> liftio $ putstrln ("coulnt find userid=" ++ (show userid)) u -> liftio $ putstrln ("user=" ++ (show user)) here's output get:
max@maximilians-mbp /tmp> stack runghc sqlite.hs run outside project, using implicit global project config using resolver: lts-3.10 implicit global project's config file: /users/max/.stack/global/stack.yaml migrating: create table "user"("id" integer primary key,"email" varchar not null,"created_at" timestamp null default current_time,"updated_at" timestamp null default current_time) [debug#sql] create table "user"("id" integer primary key,"email" varchar not null,"created_at" timestamp null default current_time,"updated_at" timestamp null default current_time); [] [debug#sql] insert "user"("email","created_at","updated_at") values(?,?,?); [persisttext "saurabhnanda@gmail.com",persistnull,persistnull] [debug#sql] select "id" "user" _rowid_=last_insert_rowid(); [] userkey {unuserkey = sqlbackendkey {unsqlbackendkey = 1}} [debug#sql] select "email","created_at","updated_at" "user" "id"=? ; [persistint64 1] user=just (user {useremail = "saurabhnanda@gmail.com", usercreatedat = nothing, userupdatedat = nothing}) edit: solution using triggers:
you can implement created_at , updated_at columns triggers. approach has nice advantages. there's no way updated_at enforced default value anyway, need trigger if want database (and not application) manage it. triggers solve updated_at being set when executing raw sql queries or batch updates. here's solution looks like:
create trigger set_created_and_updated_at after insert on user begin update user set created_at=current_timestamp, updated_at=current_timestamp user.id = new.id; end create trigger set_updated_at after update on user begin update user set updated_at=current_timestamp user.id = new.id; end now output expected:
[debug#sql] insert "user"("email","created_at","updated_at") values(?,?,?); [persisttext "saurabhnanda@gmail.com",persistnull,persistnull] [debug#sql] select "id" "user" _rowid_=last_insert_rowid(); [] userkey {unuserkey = sqlbackendkey {unsqlbackendkey = 1}} [debug#sql] select "email","created_at","updated_at" "user" "id"=? ; [persistint64 1] user=just (user {useremail = "saurabhnanda@gmail.com", usercreatedat = 2016-02-12 16:41:43 utc, userupdatedat = 2016-02-12 16:41:43 utc}) the main downside trigger solution it's slight hassle set triggers.
Comments
Post a Comment