From: Colin P. A. <co...@co...> - 2009-09-28 10:57:48
|
I've tried declaring a column of type CalendarTimeT. I inserted a record using the variable time, which I got like this: ct <- getClockTime time <- toCalendarTime ct as that seemd to be the way to produce a calendar time from reading the docs for System.Time. However, on reading the record back, I get: Server error: Convertible: error converting source data SqlString "2009-09-28 11:48:37" of type SqlValue to type Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z" What have I missed? -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-28 15:07:41
|
>>>>> "Colin" == Colin Paul Adams <co...@co...> writes: Colin> I've tried declaring a column of type CalendarTimeT. I Colin> inserted a record using the variable time, which I got like Colin> this: Colin> ct <- getClockTime time <- toCalendarTime ct Colin> as that seemd to be the way to produce a calendar time from Colin> reading the docs for System.Time. Colin> However, on reading the record back, I get: Colin> Server error: Convertible: error converting source data Colin> SqlString "2009-09-28 11:48:37" of type SqlValue to type Colin> Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using Colin> default format string "%Y-%m-%d %T%Q %z" Colin> What have I missed? I changed: time <- toCalendarTime ct to: let time = toUTCTime ct but I still get the same error. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-28 15:32:42
|
What driver and database are you using? On Mon, Sep 28, 2009 at 8:07 AM, Colin Paul Adams <co...@co...> wrote: >>>>>> "Colin" == Colin Paul Adams <co...@co...> writes: > > Colin> I've tried declaring a column of type CalendarTimeT. I > Colin> inserted a record using the variable time, which I got like > Colin> this: > > Colin> ct <- getClockTime time <- toCalendarTime ct > > Colin> as that seemd to be the way to produce a calendar time from > Colin> reading the docs for System.Time. > > Colin> However, on reading the record back, I get: > > Colin> Server error: Convertible: error converting source data > Colin> SqlString "2009-09-28 11:48:37" of type SqlValue to type > Colin> Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using > Colin> default format string "%Y-%m-%d %T%Q %z" > > Colin> What have I missed? > > I changed: > > time <- toCalendarTime ct > > to: > > let time = toUTCTime ct > > but I still get the same error. > -- > Colin Adams > Preston Lancashire > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry® Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9-12, 2009. Register now! > http://p.sf.net/sfu/devconf > _______________________________________________ > Haskelldb-users mailing list > Has...@li... > https://lists.sourceforge.net/lists/listinfo/haskelldb-users > |
From: Colin P. A. <co...@co...> - 2009-09-28 15:35:05
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> What driver and database are you using? Sqlite3 via HDBC. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-28 15:45:02
|
As far as I can tell, this is an issue with HDBC's Convertible class/package more than haskelldb. The instance of Convertible for the time type (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently trying to use the format string "%Y-%m-%d %T%Q %z" to parse the data. Notice %z - that is a time zone specifier, and it does not show up in the example value ("2009-09-28 11:48:37"). If you can manage to get your own instance of Convertible defined, you should be able to define your own format string to parse the data. Alternatively, if you can get SqlLite to include timezone information you can probably get the value to parse. Justin On Mon, Sep 28, 2009 at 8:34 AM, Colin Paul Adams <co...@co...> wrote: >>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: > > Justin> What driver and database are you using? > > Sqlite3 via HDBC. > -- > Colin Adams > Preston Lancashire > |
From: Colin P. A. <co...@co...> - 2009-09-30 05:55:41
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> As far as I can tell, this is an issue with HDBC's Justin> Convertible class/package more than haskelldb. The Justin> instance of Convertible for the time type Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to Justin> parse the data. Notice %z - that is a time zone Justin> specifier, and it does not show up in the example value Justin> ("2009-09-28 11:48:37"). Justin> If you can manage to get your own instance of Convertible Justin> defined, you should be able to define your own format Justin> string to parse the data. Alternatively, if you can get Justin> SqlLite to include timezone information you can probably Justin> get the value to parse. I suspect the problem is with the insert. For the SELECT, I can use showSQL to see the generated SQL. How can I find out what SQL the insert is generating? -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-28 16:26:18
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> As far as I can tell, this is an issue with HDBC's Justin> Convertible class/package more than haskelldb. The Justin> instance of Convertible for the time type Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to Justin> parse the data. Notice %z - that is a time zone Justin> specifier, and it does not show up in the example value Justin> ("2009-09-28 11:48:37"). Justin> If you can manage to get your own instance of Convertible Justin> defined, you should be able to define your own format Justin> string to parse the data. Alternatively, if you can get Justin> SqlLite to include timezone information you can probably Justin> get the value to parse. Which of the following will HaskellDB be using to CalendarTimeT with sqlite-HDBC? SqlLocalDate Day Local YYYY-MM-DD (no timezone) SqlLocalTimeOfDay TimeOfDay Local HH:MM:SS (no timezone) SqlZonedLocalTimeOfDay TimeOfDay TimeZone Local HH:MM:SS -HHMM. Converts to and from (TimeOfDay, TimeZone). SqlLocalTime LocalTime Local YYYY-MM-DD HH:MM:SS (no timezone) SqlZonedTime ZonedTime Local YYYY-MM-DD HH:MM:SS -HHMM. Considered equal if both convert to the same UTC time. SqlUTCTime UTCTime UTC YYYY-MM-DD HH:MM:SS SqlDiffTime NominalDiffTime Calendar diff between seconds. Rendered as Integer when converted to String, but greater precision may be preserved for other types or to underlying database. SqlPOSIXTime POSIXTime Time as seconds since midnight Jan 1 1970 UTC. Integer rendering as for SqlDiffTime. SqlEpochTime Integer DEPRECATED Representation of ClockTime or CalendarTime. Use SqlPOSIXTime instead. SqlTimeDiff Integer DEPRECATED Representation of TimeDiff. Use SqlDiffTime instead. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-28 16:59:59
|
On Mon, Sep 28, 2009 at 9:26 AM, Colin Paul Adams <co...@co...> wrote: > Which of the following will HaskellDB be using to CalendarTimeT with > sqlite-HDBC? > The type used is not determined by HaskellDB. Your application determines the type it expects, and the appropriate Convertible instance is selected. If that doesn't make sense, what I mean is the type signature of your result determines the type to which the value will be converted. For example, if your function is named "getTimestampRows", you can control the type converted to via it's signature: getTimestampRows :: IO [Record (Col1 (Expr Day)) ... )] or getTimestampRows :: IO [Record (Col1 (Expr ZonedTime)) ... )] or getTimestampRows :: IO [Record (Col1 (Expr UTCTime)) ... )] Does that help? > SqlLocalDate Day Local YYYY-MM-DD (no timezone) > SqlLocalTimeOfDay TimeOfDay Local HH:MM:SS (no timezone) > SqlZonedLocalTimeOfDay TimeOfDay TimeZone Local HH:MM:SS -HHMM. Converts to and from (TimeOfDay, TimeZone). > SqlLocalTime LocalTime Local YYYY-MM-DD HH:MM:SS (no timezone) > SqlZonedTime ZonedTime Local YYYY-MM-DD HH:MM:SS -HHMM. Considered equal if both convert to the same UTC time. > SqlUTCTime UTCTime UTC YYYY-MM-DD HH:MM:SS > SqlDiffTime NominalDiffTime Calendar diff between seconds. Rendered as Integer when converted to String, but greater precision may be preserved for other types or to underlying database. > SqlPOSIXTime POSIXTime Time as seconds since midnight Jan 1 1970 UTC. Integer rendering as for SqlDiffTime. > SqlEpochTime Integer DEPRECATED Representation of ClockTime or CalendarTime. Use SqlPOSIXTime instead. > SqlTimeDiff Integer DEPRECATED Representation of TimeDiff. Use > SqlDiffTime instead. > > -- > Colin Adams > Preston Lancashire > |
From: Colin P. A. <co...@co...> - 2009-09-28 17:20:13
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: The type used is not determined by HaskellDB. Your application Justin> determines the type it expects, and the appropriate Justin> Convertible instance is selected. If that doesn't make Justin> sense, what I mean is the type signature of your result Justin> determines the type to which the value will be Justin> converted. For example, if your function is named Justin> "getTimestampRows", you can control the type converted to Justin> via it's signature: Justin> getTimestampRows :: IO [Record (Col1 (Expr Day)) ... )] Justin> or Justin> getTimestampRows :: IO [Record (Col1 (Expr ZonedTime)) Justin> ... )] Justin> or Justin> getTimestampRows :: IO [Record (Col1 (Expr UTCTime)) Justin> ... )] Justin> Does that help? Not really, as I am not actually requesting that column (not intentioanlly, anyway). Probably I have got the project call wrong. My code looks like this: let q = do t <- DB.table IT.imageTable DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber) DB.order [DB.desc t IT.indexNumber] return t rs <- DB.query db q let nextIndex = case null rs of True -> 1 False -> 1 + (head rs DB.! IT.indexNumber) I can't say that I could understand the project function from the examples I saw. Mu intention was just to retrieve the indexNumber column, which is defined as an integerT in the FieldDesc. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-28 17:53:18
|
On Mon, Sep 28, 2009 at 10:20 AM, Colin Paul Adams <co...@co...> wrote: > Not really, as I am not actually requesting that column (not > intentioanlly, anyway). > Probably I have got the project call wrong. My code looks like this: > > let q = do > t <- DB.table IT.imageTable > DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber) > DB.order [DB.desc t IT.indexNumber] > return t > rs <- DB.query db q > let nextIndex = case null rs of > True -> 1 > False -> 1 + (head rs DB.! IT.indexNumber) > Rewrite your projection so you aren't returning t: > let q = do > t <- DB.table IT.imageTable > DB.order [DB.desc t IT.indexNumber] > DB.project (IT.indexNumber DB.<< t DB.! IT.indexNumber) Returning "t" projected every column in the table, which led to the conversion problem. DB.project should always be the last function called, as it filters the columns returned down to just the ones you want. I moved the order call above the project call so you can still get your results ordered like you want, but it doesn't interfere with the projection. If you ever need to look at the actual SQL generated, you can use showQuery. Does that do the trick? Justin |
From: Colin P. A. <co...@co...> - 2009-09-28 18:04:05
|
Justin> Returning "t" projected every column in the table, which Justin> led to the conversion problem. DB.project should always be Justin> the last function called, as it filters the columns Justin> returned down to just the ones you want. I moved the Justin> order call above the project call so you can still get Justin> your results ordered like you want, but it doesn't Justin> interfere with the projection. Justin> If you ever need to look at the actual SQL generated, you Justin> can use showQuery. Justin> Does that do the trick? Yes, that works. Thank-you very much. -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-29 20:01:34
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> If you ever need to look at the actual SQL generated, you Justin> can use showQuery. Well, I tried that, as I have finally reached the point where I need those timestamps, and I'm again getting the error: Server error: Convertible: error converting source data SqlString "2009-09-29 19:31:12" of type SqlValue to type Data.Time.LocalTime.LocalTime.ZonedTime: Cannot parse using default format string "%Y-%m-%d %T%Q %z" But showQuery gives me: Project [("thumbnail",AttrExpr "thumbnail1"),("uploadTime",AttrExpr "uploadTime1")] (Special (Top 1) (Special (Order [OrderExpr OpDesc (AttrExpr "uploadTime1")]) (Project [("indexNumber1",AttrExpr "indexNumber"),("caption1",AttrExpr "caption"),("thumbnail1",AttrExpr "thumbnail"),("preview1",AttrExpr "preview"),("original1",AttrExpr "original"),("uploadTime1",AttrExpr "uploadTime")] (Restrict (BinExpr OpIn (AttrExpr "indexNumber") (ListExpr [ConstExpr (IntegerLit 6)])) (BaseTable "imageTable" ["indexNumber","caption","thumbnail","preview","original","uploadTime"]))))) which isn't SQL. I think showSql sounds better - so I shall try it - and indeed it gives me: SELECT thumbnail1 as thumbnail, uploadTime1 as uploadTime FROM (SELECT indexNumber as indexNumber1, caption as caption1, thumbnail as thumbnail1, preview as preview1, original as original1, uploadTime as uploadTime1 FROM imageTable as T1 WHERE indexNumber IN (6)) as T1 ORDER BY uploadTime1 DESC LIMIT 1 This tells me that my constructed query is correct, but not what type it is going to get uploadTime as. Incidentally, if I just go into sqlite3 and make a query, it is obvious to me that the stored value is a local time (we are currently on UTC + 1 hour), without a time zone, which is fairly useless to me. What I want to do is to store UTC (it's the only way that ORDER BY clause is going to work correctly, given the possibility of international uploads). It seems I can do that by changing toCalendarTime to toUTCTime, but will the database know the difference? I just tried it. Now the time is UTC time OK. But the column definition is unchanged, of course. So I need to be able to say that the query should interpret the time as UTC. But how can I do that? -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-28 19:33:44
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> The type used is not determined by HaskellDB. Your application Justin> determines the type it expects, and the appropriate Justin> Convertible instance is selected. If that doesn't make Justin> sense, what I mean is the type signature of your result Justin> determines the type to which the value will be Justin> converted. For example, if your function is named Justin> "getTimestampRows", you can control the type converted to Justin> via it's signature: Justin> getTimestampRows :: IO [Record (Col1 (Expr Day)) ... )] Justin> or Justin> getTimestampRows :: IO [Record (Col1 (Expr ZonedTime)) Justin> ... )] Justin> or Justin> getTimestampRows :: IO [Record (Col1 (Expr UTCTime)) Justin> ... )] I'm lost here. HaskellDB is expecting a System.Time.CalendarTime (because I defined the column as a CalendarT - there was no other choice in the constructors from FireldDesc), but if I look at the documentation for System.Time, it says it is deprecated - look at Data.Time instead. If I look there, I see Data.Time.Clock.UTCTime, which I guess is what you are referring to in the line above. But if I try to write such a record with: ct <- getCurrentTime DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct) the compiler complains: Couldn't match expected type `System.Time.CalendarTime' against inferred type `UTCTime' In the second argument of `(<<-)', namely `ct' In the second argument of `(#)', namely `IT.uploadTime <<- ct' How do I control this with a signature? -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-28 19:44:00
|
On Mon, Sep 28, 2009 at 12:33 PM, Colin Paul Adams <co...@co...> wrote: > If I look there, I see Data.Time.Clock.UTCTime, which I guess is what > you are referring to in the line above. But if I try to write such a > record with: > > ct <- getCurrentTime > DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct) > > the compiler complains: > > Couldn't match expected type `System.Time.CalendarTime' > against inferred type `UTCTime' > In the second argument of `(<<-)', namely `ct' > In the second argument of `(#)', namely `IT.uploadTime <<- ct' > > How do I control this with a signature? Can you send the type definition for ImageTable (aliased as IT in your code)? That will help me answer your question. > -- > Colin Adams > Preston Lancashire > |
From: Colin P. A. <co...@co...> - 2009-09-29 00:52:14
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> On Mon, Sep 28, 2009 at 12:33 PM, Colin Paul Adams Justin> <co...@co...> wrote: >> If I look there, I see Data.Time.Clock.UTCTime, which I guess >> is what you are referring to in the line above. But if I try to >> write such a record with: >> >> ct <- getCurrentTime DB.insert db IT.imageTable (<snip> # >> IT.uploadTime <<- ct) >> >> the compiler complains: >> >> Couldn't match expected type `System.Time.CalendarTime' >> against inferred type `UTCTime' In the second argument >> of `(<<-)', namely `ct' In the second argument of `(#)', >> namely `IT.uploadTime <<- ct' >> >> How do I control this with a signature? Justin> Can you send the type definition for ImageTable (aliased Justin> as IT in your code)? That will help me answer your Justin> question. -- | Relative URIs for various sized images imageTable :: TInfo imageTable = TInfo {tname = "imageTable", cols = [indexColumn, captionColumn, thumbnailColumn, previewColumn, originalColumn, uploadTimeColumn]} captionColumn :: CInfo captionColumn = CInfo {cname = "caption", descr = (StringT, False)} indexColumn :: CInfo indexColumn = CInfo {cname = "indexNumber", descr = (IntegerT, False)} thumbnailColumn :: CInfo thumbnailColumn = CInfo {cname = "thumbnail", descr = (StringT, False)} previewColumn :: CInfo previewColumn = CInfo {cname = "preview", descr = (StringT, False)} originalColumn :: CInfo originalColumn = CInfo {cname = "original", descr = (StringT, True)} uploadTimeColumn :: CInfo uploadTimeColumn = CInfo {cname = "uploadTime", descr = (CalendarTimeT, False)} -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-29 13:29:47
|
>>>>> "Colin" == Colin Paul Adams <co...@co...> writes: >>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> Can you send the type definition for ImageTable (aliased Justin> as IT in your code)? That will help me answer your Justin> question. I assumed you meant the DBSpec difinition, but just in case you meant the generated module, here it is as well: {-# OPTIONS_GHC -fcontext-stack46 #-} -- NOTE: use GHC flag -fcontext-stack46 with this module if GHC < 6.8.1 --------------------------------------------------------------------------- -- Generated by DB/Direct --------------------------------------------------------------------------- module Database.ImageTable where import Database.HaskellDB.DBLayout --------------------------------------------------------------------------- -- Table type --------------------------------------------------------------------------- type ImageTable = (RecCons IndexNumber (Expr Integer) (RecCons Caption (Expr String) (RecCons Thumbnail (Expr String) (RecCons Preview (Expr String) (RecCons Original (Expr (Maybe String)) (RecCons UploadTime (Expr CalendarTime) RecNil)))))) --------------------------------------------------------------------------- -- Table --------------------------------------------------------------------------- imageTable :: Table ImageTable imageTable = baseTable "imageTable" $ hdbMakeEntry IndexNumber # hdbMakeEntry Caption # hdbMakeEntry Thumbnail # hdbMakeEntry Preview # hdbMakeEntry Original # hdbMakeEntry UploadTime --------------------------------------------------------------------------- -- Fields --------------------------------------------------------------------------- --------------------------------------------------------------------------- -- IndexNumber Field --------------------------------------------------------------------------- data IndexNumber = IndexNumber instance FieldTag IndexNumber where fieldName _ = "indexNumber" indexNumber :: Attr IndexNumber Integer indexNumber = mkAttr IndexNumber --------------------------------------------------------------------------- -- Caption Field --------------------------------------------------------------------------- data Caption = Caption instance FieldTag Caption where fieldName _ = "caption" caption :: Attr Caption String caption = mkAttr Caption --------------------------------------------------------------------------- -- Thumbnail Field --------------------------------------------------------------------------- data Thumbnail = Thumbnail instance FieldTag Thumbnail where fieldName _ = "thumbnail" thumbnail :: Attr Thumbnail String thumbnail = mkAttr Thumbnail --------------------------------------------------------------------------- -- Preview Field --------------------------------------------------------------------------- data Preview = Preview instance FieldTag Preview where fieldName _ = "preview" preview :: Attr Preview String preview = mkAttr Preview --------------------------------------------------------------------------- -- Original Field --------------------------------------------------------------------------- data Original = Original instance FieldTag Original where fieldName _ = "original" original :: Attr Original (Maybe String) original = mkAttr Original --------------------------------------------------------------------------- -- UploadTime Field --------------------------------------------------------------------------- data UploadTime = UploadTime instance FieldTag UploadTime where fieldName _ = "uploadTime" uploadTime :: Attr UploadTime CalendarTime uploadTime = mkAttr UploadTime -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-29 15:30:26
|
The generated module is what I wanted. Here is your table definition: > type ImageTable = > (RecCons IndexNumber (Expr Integer) > (RecCons Caption (Expr String) > (RecCons Thumbnail (Expr String) > (RecCons Preview (Expr String) > (RecCons Original (Expr (Maybe String)) > (RecCons UploadTime (Expr CalendarTime) RecNil)))))) Specifically, UploadTime is of tyep "CalendarTime". The error you got was: > ct <- getCurrentTime > DB.insert db IT.imageTable (<snip> # IT.uploadTime <<- ct) > > the compiler complains: > > Couldn't match expected type `System.Time.CalendarTime' > against inferred type `UTCTime' > In the second argument of `(<<-)', namely `ct' > In the second argument of `(#)', namely `IT.uploadTime <<- ct' The solution is to make "ct" have type CalendarTime, not UTCTime. HaskellDB uses the deprecated, but still in wide use, old-time package which exposes System.Time.CalendarTime. You can't get a CalendarTime for the current date direclty, but you can indirectly. Try: clockTime <- getClockTime ct <- toCalendarTime clockTime Or more succinctly: ct <- getClockTime >>= toCalendarTime Then you should be able to do your insert. Finally, you asked: > How do I control this with a signature? That's not relevant here as I was referring to the conversion of SQL types to Haskell values. Since DBDirect generates the code for your table, you are stuck with CalendarTime. DBDirect is just a convenience, though. You could make your own definition by hand. There is also the haskelldb-th package, which gives some Template Haskell functions for defining tables and columns. Just FYI. Justin |
From: Colin P. A. <co...@co...> - 2009-09-29 15:42:29
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> The solution is to make "ct" have type CalendarTime, not Justin> UTCTime. HaskellDB uses the deprecated, but still in wide Justin> use, old-time package which exposes Justin> System.Time.CalendarTime. You can't get a CalendarTime for Justin> the current date direclty, but you can indirectly. Try: Justin> clockTime <- getClockTime ct <- toCalendarTime clockTime So this is what I was originally doing. Justin> Or more succinctly: Justin> ct <- getClockTime >>= toCalendarTime Justin> Then you should be able to do your insert. Finally, you Justin> asked: >> How do I control this with a signature? Justin> That's not relevant here as I was referring to the Justin> conversion of SQL types to Haskell values. Since DBDirect Justin> generates the code for your table, you are stuck with Justin> CalendarTime. But the reason I tried changing to use UTCTime was in answer to my original problem, you said I could extract a UTCTime from it (you gave an example function). So I am puzzled as to how I can get a different type out from what I put in. But perhaps you thought I was doing the following: Justin> DBDirect is just a convenience, though. You could make Justin> your own definition by hand. One of the reasons I'm using HaskellDB is I want to be sure everything is consistent. Justin> haskelldb-th package, which gives some Template Haskell Justin> functions for defining tables and columns. Just FYI. I'll take a look. Thanks. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-29 15:59:23
|
On Tue, Sep 29, 2009 at 8:42 AM, Colin Paul Adams <co...@co...> wrote: > But the reason I tried changing to use UTCTime was in answer to my > original problem, you said I could extract a UTCTime from it (you gave > an example function). > So I am puzzled as to how I can get a different type out from what I > put in. > That's understandable and I don't think I was correct anyways. I mostly use HaskellDB to generate SQL which I execute elsewhere, so I'm not as familiar with extracting results in Haskell. Sorry for any confusion! Justin |
From: Colin P. A. <co...@co...> - 2009-09-30 07:48:00
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> As far as I can tell, this is an issue with HDBC's Justin> Convertible class/package more than haskelldb. The Justin> instance of Convertible for the time type Justin> (Data.Time.LocalTime.LocalTime.ZonedTime) is apparently Justin> trying to use the format string "%Y-%m-%d %T%Q %z" to Justin> parse the data. Notice %z - that is a time zone Justin> specifier, and it does not show up in the example value Justin> ("2009-09-28 11:48:37"). Justin> If you can manage to get your own instance of Convertible Justin> defined, you should be able to define your own format Justin> string to parse the data. Alternatively, if you can get Justin> SqlLite to include timezone information you can probably Justin> get the value to parse. I think it's a problem with the way haskelldb defines columns for CalendarTimeT. The reason I say this, is because I tried switching from sqlite3 to postgresql. It doesn't work there either. But this time, instead of the column being of type "timestamp", it is of type "timestamp without time zone", which is clearly wrong, as CalendarTime includes timezone information. I can probably bodge this with an SQL ALTER TABLE ... ALTER COLUMN .. statement to make this "timestamp with time zone", but I think haskelldb should be generating the correct types anyway (for sqlite3 this may not be possible). -- Colin Adams Preston Lancashire |
From: Colin P. A. <co...@co...> - 2009-09-30 08:04:15
|
>>>>> "Colin" == Colin Paul Adams <co...@co...> writes: Colin> I can probably bodge this with an SQL ALTER TABLE ... ALTER Colin> COLUMN .. statement to make this "timestamp with time Colin> zone", but I think haskelldb should be generating the Colin> correct types anyway (for sqlite3 this may not be Colin> possible). I can confirm that this bodge makes the problem disappear. -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-30 15:04:10
|
On Wed, Sep 30, 2009 at 12:47 AM, Colin Paul Adams <co...@co...> wrote: > The reason I say this, is because I tried switching from sqlite3 to > postgresql. It doesn't work there either. But this time, instead of > the column being of type "timestamp", it is of type "timestamp without > time zone", which is clearly wrong, as CalendarTime includes timezone > information. > I looked into this yesterday and found pretty much the same thing. The HaskellDB driver interface for database connections is too ridig and doesn't allow you to specify the type you want for a DateTime column, which in turn forces the driver implementor to decide on a parsing strategy. It's an unfortunate situation, but I'm glad you found a workaround. If you feel like digging into the library to improve the API I'd be glad to help. Justin |
From: Colin P. A. <co...@co...> - 2009-09-30 15:17:07
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> HaskellDB driver interface for database connections is too Justin> ridig and doesn't allow you to specify the type you want Justin> for a DateTime column, which in turn forces the driver Justin> implementor to decide on a parsing strategy. Justin> It's an unfortunate situation, but I'm glad you found a Justin> workaround. If you feel like digging into the library to Justin> improve the API I'd be glad to help. There's a few other areas I'd like to see developed two (such as being able to declare a column as auto-increment in DBSpec). So I will probably get invoved soon. When will 1.0 be released (approximately)? -- Colin Adams Preston Lancashire |
From: Justin B. <jgb...@gm...> - 2009-09-30 15:24:35
|
On Wed, Sep 30, 2009 at 8:16 AM, Colin Paul Adams <co...@co...> wrote: > So I will probably get invoved soon. > When will 1.0 be released (approximately)? > -- Before the end of the year is about the best I can say. > Colin Adams > Preston Lancashire > |
From: Colin P. A. <co...@co...> - 2009-10-01 15:31:36
|
>>>>> "Justin" == Justin Bailey <jgb...@gm...> writes: Justin> On Wed, Sep 30, 2009 at 12:47 AM, Colin Paul Adams Justin> <co...@co...> wrote: >> The reason I say this, is because I tried switching from >> sqlite3 to postgresql. It doesn't work there either. But this >> time, instead of the column being of type "timestamp", it is of >> type "timestamp without time zone", which is clearly wrong, as >> CalendarTime includes timezone information. >> I looked into this yesterday and found pretty much the same thing. The Justin> HaskellDB driver interface for database connections is too Justin> ridig and doesn't allow you to specify the type you want Justin> for a DateTime column, which in turn forces the driver Justin> implementor to decide on a parsing strategy. Justin> It's an unfortunate situation, but I'm glad you found a Justin> workaround. If you feel like digging into the library to Justin> improve the API I'd be glad to help. After a bit of thought, i don't think an API change is needed for this. It is just the code to generate the SQL for CREATE TABLE is presumably faulty. For CalendarTimeT columns it should generate a time of timestamp with time zone as CalendarTime is a zoned timestamp. I have just checked with sqlite3 that a create table statement like this works. Unfortunately I can't use the same bodge that I used with postgresql, as ALTER TABLE in sqlite3 does not support changing column types. But I'd like to experiment with changing the code generation for this column type. Can you tell me which module this occurs in? Of course, this will only work if all backends support this data type, but my understanding is that this is a standard SQL type. -- Colin Adams Preston Lancashire |