The function juliatype not cover fully? The type name completely follows the table creating statement.

[biluohc]

# src/SQLite.jl:257

function juliatype(handle, col)
    t = SQLite.sqlite3_column_decltype(handle, col)
    if t != C_NULL
        T = juliatype(unsafe_string(t))
        T !== Any && return T
    end
    x = SQLite.sqlite3_column_type(handle, col)
    if x == SQLite.SQLITE_BLOB
        val = SQLite.sqlitevalue(Any, handle, col)
        return typeof(val)
    else
        return juliatype(x)
    end
end

juliatype(x::Integer) = x == SQLITE_INTEGER ? Int : x == SQLITE_FLOAT ? Float64 : x == SQLITE_TEXT ? String : Any
juliatype(x::String) = x == "INTEGER" ? Int : x in ("NUMERIC","REAL") ? Float64 : x == "TEXT" ? String : Any

CREATE TABLE prices (
    id  INTEGER primary key AUTOINCREMENT not null,
    name Text,
    price Float NOT NULL,
    create_dt datetime not null default  (datetime('now','localtime'))
)
julia> 

julia> SQLite.sqlite3_column_decltype(sqq.stmt.handle, 1) |> unsafe_string
"INTEGER"

julia> SQLite.sqlite3_column_decltype(sqq.stmt.handle, 2) |> unsafe_string
"Text"

julia> SQLite.sqlite3_column_decltype(sqq.stmt.handle, 3) |> unsafe_string
"Float"

julia> SQLite.sqlite3_column_decltype(sqq.stmt.handle, 4) |> unsafe_string
"datetime"


julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 0)
5

julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 1)
5

julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 2)
5

julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 3)
5

julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 4)
5

julia> SQLite.sqlite3_column_type(sqq.stmt.handle, 5)
5

julia> sqct |> print
CREATE TABLE prices (
    id  INTEGER primary key AUTOINCREMENT not null,
    name text,
    price float NOT NULL,
    create_dt datetime not null default  (datetime('now','localtime'))
)
julia> SQLite.sqlite3_column_decltype(sqf.stmt.handle, 1) |> unsafe_string
"INTEGER"

julia> SQLite.sqlite3_column_decltype(sqf.stmt.handle, 2) |> unsafe_string
"text"

julia> SQLite.sqlite3_column_decltype(sqf.stmt.handle, 3) |> unsafe_string
"float"

julia> SQLite.sqlite3_column_decltype(sqf.stmt.handle, 4) |> unsafe_string
"datetime"

[quinnj]

Sorry, what's the issue/problem here? It's not clear what you're showing or what's wrong or what needs to change.

[biluohc]

juliatype(x::String) = x == "INTEGER" ? Int : x in ("NUMERIC","REAL") ? Float64 : x == "TEXT" ? String : Any

I mean is that this function can only handle the four strings "INTEGER", "NUMERIC", "REAL" and "TEXT", and does not consider case, other types such as "Text" or "datetime" will output ʻAny`

[noobymcnoob]

I have this problem where my datetime format stored in the DB is Y-m-d H:M:S.ssssss (i.e., microseconds.) Is there a supported way to extract this into a Julia DateTime? I'm OK truncating the microseconds. Manually parsing the string columns is prone to be buggy.

[tasgon]

I'm also noticing that the function seems to be missing some types:

┌ Warning: Unsupported SQLite type timestamp
└ @ SQLite /home/<user>/.julia/packages/SQLite/LkiSf/src/SQLite.jl:386
┌ Warning: Unsupported SQLite type float
└ @ SQLite /home/<user>/.julia/packages/SQLite/LkiSf/src/SQLite.jl:386
┌ Warning: Unsupported SQLite type char(2)
└ @ SQLite /home/<user>/.julia/packages/SQLite/LkiSf/src/SQLite.jl:386

[quinnj]

@alyst ^

[alyst]

Recently I've updated juliatype(::String) to recognize non-uppercase version of the declared types.
I've also added the warning that @tasgon is seeing, so we can track which types are not supported, and fix that.

I'm quite busy at the moment, so maybe somebody can submit a PR adding juliatype() support for FLOAT, CHAR(\d+) and TIMESTAMP.
In theory, if juliatype(::String) fails with the declared type, then juliatype(::Integer) should take over and use the stored type (which could only be one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT and SQLITE_BLOB), but it's worth double-checking if it works.

[felipenoris]

Yea, that last release was a breaking change for me, but I couldn't identity the origin besides the juliatype update.

[quinnj]

Closing this as I think we've covered the cases mentioned; if there are additional type conversion issues, please comment here or open a new issue.