Convert date/datetimes to text when storing

[quinnj]

see discussion: https://discourse.julialang.org/t/datetimes-and-sqlite-jl-encoding/16412/3

[Seelengrab]

I've tried to do this manually by defining

SQLite.sqlreturn(context, val::DateTime) = SQLite.sqlreturn(context, string(val))

as is described for BigInt in the docs, but when retrieving the inserted values I get the following error:

julia> SQLite.Query(db, "SELECT * FROM tmp") |> DataFrame
ERROR: TypeError: in typeassert, expected Union{Missing, String}, got DateTime
Stacktrace:
 [1] sqlitevalue(::Type{Union{Missing, String}}, ::Ptr{Nothing}, ::Int64) at /home/<snip>/.julia/packages/SQLite/yKARA/src/SQLite.jl:220
 [2] getvalue(::SQLite.Query{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}, ::Int64, ::Type{Union{Missing, String}}) at /home/<snip>/.julia/packages/SQLite/yKARA/src/tables.jl:38
 [3] macro expansion at /home/<snip>/.julia/packages/SQLite/yKARA/src/tables.jl:0 [inlined]
 [4] generate_namedtuple(::Type{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}, ::SQLite.Query{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}) at /home/<snip>/.julia/packages/SQLite/yKARA/src/tables.jl:43
 [5] iterate at /home/<snip>/.julia/packages/SQLite/yKARA/src/tables.jl:53 [inlined]
 [6] iterate at ./iterators.jl:139 [inlined]
 [7] iterate at ./iterators.jl:138 [inlined]
 [8] buildcolumns at /home/<snip>/.julia/packages/Tables/IT0t3/src/fallbacks.jl:93 [inlined]
 [9] columns at /home/<snip>/.julia/packages/Tables/IT0t3/src/fallbacks.jl:169 [inlined]
 [10] #DataFrame#404(::Bool, ::Type, ::SQLite.Query{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}) at /home/<snip>/.julia/packages/DataFrames/Iyo5L/src/other/tables.jl:34
 [11] DataFrame(::SQLite.Query{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}) at /home/<snip>/.julia/packages/DataFrames/Iyo5L/src/other/tables.jl:25 [12] |>(::SQLite.Query{NamedTuple{(:id, :date),Tuple{Union{Missing, Int64},Union{Missing, String}}}}, ::Type) at ./operators.jl:813
 [13] top-level scope at none:0

Here's an MWE to reproduce:

using SQLite, Dates, DataFrames

SQLite.sqlreturn(context, val::DateTime) = SQLite.sqlreturn(context, string(val))

db = SQLite.DB(":memory:")
SQLite.execute!(db, "CREATE TABLE IF NOT EXISTS tmp(id INTEGER PRIMARY KEY, value TEXT NOT NULL);")
SQLite.Query(db, "INSERT INTO tmp VALUES(?,?)", values=[1,Dates.now()])
SQLite.Query(db, "SELECT * FROM tmp") |> DataFrame                         # this errors

My julia version:

julia> versioninfo()
Julia Version 1.1.0
Commit 80516ca (2019-01-21 21:24 UTC)
Platform Info:
  OS: Linux (x86_64-linux-gnu)
  CPU: Intel(R) Core(TM) i7-6600U CPU @ 2.60GHz
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-6.0.1 (ORCJIT, skylake)
Environment:
  JULIA_NUM_THREADS = 4

My best guess is that the type assertion (rightfully so) throws since the deserialization returns a DateTime, but an SQL column with type TEXT is only ever expected to return either missing or a String at the moment. I haven't checked if it would be enough to remove the type assertion. The same error also appears when using the example from the docs, i.e. with BigInt.

[tlnagy]

@Seelengrab Your assumption was correct, the way to fix this is to ensure the SQL column type is BLOB. I ran into this trying to store Measurements in a SQLite database since Measurement{Float64} is considered a "Float." I had to dig through the source, but defining the following lines was sufficient for me to able to get around the TypeError on deserialization

SQLite.bind!(stmt::SQLite.Stmt, i::Int, val::Measurement) = SQLite.bind!(stmt, i, SQLite.sqlserialize(val))
SQLite.sqlitetype(::Type{T}) where {T<:Union{Missing, Measurement{Float64}}} = "BLOB"

[Seelengrab]

Oh that's a good find! I wonder if that could simply be the default fallback, though this might be problematic if more than one non-default struct is serialized that way..

[quinnj]

By default, non-standard types will use serialize and deserialize to BLOB columns

[dlakelan]

Hi was just burned by this confusion this morning while writing a tutorial. Do we have a simple solution? Right now I'm just converting my Dates to string before writing the table.