Genie Discord forum

I tried Union{Missing, String} in my model and wanted to pass missing. However, instead of inserting a null value, the string "missing" is inserted to database.

try with nothing instead of missing. I tested this code below and got the result you want

[ Info: INSERT INTO houses ("street", "size", "rooms") VALUES ('Barcelona', 100.0, NULL) [ Info: ; SELECT CASE WHEN last_insert_rowid() = 0 THEN -1 ELSE last_insert_rowid() END AS LAST_INSERT_ID true

Struct:

@kwdef mutable struct House <: AbstractModel
    id::DbId = DbId()
    street::String = "Random street"
    size::Float32 = 100.0
    rooms::Union{Int32,Nothing} = 4
end

Migration:

function up()
    create_table(:houses) do
        [
            pk()
            columns([
                :street => :string
                :size => :float
                :rooms => :int
            ])
        ]
    end

end

object creation:

h = House(street="Barcelona", size=100, rooms=nothing)
save(h)

Can also nothing be used as default value in struct model? So if the value is not passed to save function, a null value be inserted to database?

I think so, don't see why it wouldn't work

How to query them back? I get the following the error when trying to use find method.

MethodError: Cannot `convert` an object of type String to an object of type Nothing

Closest candidates are:
│      convert(::Type{Nothing}, ::Any) at some.jl:37
│      convert(::Type{T}, ::Any) where T>:Nothing at some.jl:36
│      convert(::Type{T}, ::Intervals.Interval{T}) where T at ~/.julia/packages/Intervals/MDxq7/src/interval.jl:240

just one thing. it is probably best to set nothing as default value as you asked. I had some issues when having something else

As for retrieving, this is how:

using SearchLight
using SearchLightSQLite
include("app/resources/houses/Houses.jl")
using .Houses

# edit db/connection.yml
SearchLight.Configuration.load() |> SearchLight.connect

h = House(street="Barcelona", size=200, rooms=nothing)
save(h)
find(House, SQLWhereExpression("size == ?", 200))

WARNING: replacing module Houses.
WARNING: using Houses.House in module Main conflicts with an existing identifier.
[ Info: INSERT  INTO houses ("street", "size", "rooms") VALUES ('Barcelona', 200.0, NULL)
[ Info: ; SELECT CASE WHEN last_insert_rowid() = 0 THEN -1 ELSE last_insert_rowid() END AS LAST_INSERT_ID
[ Info: SELECT "houses"."id" AS "houses_id", "houses"."street" AS "houses_street", "houses"."size" AS "houses_size", "houses"."rooms" AS "houses_rooms" FROM "houses" WHERE size == 200 ORDER BY houses.id ASC
1-element Vector{House}:
 House
| KEY            | VALUE     |
|----------------|-----------|
| id::DbId       | 1         |
| rooms::Nothing | nothing   |
| size::Float32  | 200.0     |
| street::String | Barcelona |
```

you can also do findone(House, id=1)

Here is my fieldtypes:

(DbId, Union{Nothing, String}, Union{Nothing, String}, Union{Nothing, Dates.DateTime}, Union{Nothing, Dates.DateTime}, Union{Nothing, String},...

And I inited all with nothing as mentioned. But I still get the conversion error when querying any data. Here is the error I faced when doing findone(StructName, id=2):

ERROR: MethodError: Cannot `convert` an object of type String to an object of type Nothing
Closest candidates are:
  convert(::Type{Nothing}, ::Any) at some.jl:37
  convert(::Type{T}, ::Any) where T>:Nothing at some.jl:36
  convert(::Type{T}, ::Intervals.AnchoredInterval{P, T}) where {P, T} at ~/.julia/packages/Intervals/MDxq7/src/anchoredinterval.jl:181
  ...

Why it tries to convert to the data to Nothing instead of Union{Nothing, String}?

this is not easy to solve without running the code, best I can do is guess. I'll share the code of my tests where saving/loading works, can you try running the null_values.jl file?

https://github.com/GenieFramework/CodeExamples/tree/main/src/3.database/null_values

I ran you example and it works. But when I added a row which the rooms column is not null, the same error message appears. Like so:

House(street="Barcelona", size=200, rooms=nothing) |> save
House(street="Barcelona", size=200, rooms="123") |> save

Yes I see it now, " Cannot convert an object of type Int64 to an object of type Nothing". Thanks for checking, I'll take a look and report back

@Pere Any updates on this?

Ok I know what's going on:

• When loading the database record with find, each field in the record is converted to the type of the default value in the struct
• In our example, the default value for roomsis nothingof type Nothing. Hence, the Int from the DB record will be converted to Nothing
• The conversion fails since convertcannot do it

A quick and dirty solution is to overload the convert method so that it returns the value for rooms

Base.convert(t::Type{Nothing}, x::Int64) = x

This is a MWE:

Base.convert(t::Type{Nothing}, x::Int64) = x

SearchLight.Configuration.load() |> SearchLight.connect

# h = House(street="Barcelona", size=400, rooms=missing)
# save(h)
h = House(street="Barcelona 2", size=400, rooms=nothing)
save(h)
h = House(street="Barcelona 3", size=400, rooms=4)
save(h)

# find(House, SQLWhereExpression("street ==? AND size == ?", "Barcelona", 400))
@show find(House, SQLWhereExpression("street ==? AND size == ?", "Barcelona 2", 400))
@show find(House, SQLWhereExpression("street ==? AND size == ?", "Barcelona 3", 400))

This is the result:

[ Info: SELECT "houses"."id" AS "houses_id", "houses"."street" AS "houses_street", "houses"."size" AS "houses_size", "houses"."rooms" AS "houses_rooms" FROM "houses" WHERE street =='Barcelona 2' AND size == 400 ORDER BY houses.id ASC
find(House, SQLWhereExpression("street ==? AND size == ?", "Barcelona 2", 400)) = House[House
| KEY            | VALUE       |
|----------------|-------------|
| id::DbId       | 2           |
| rooms::Nothing | nothing     |
| size::Float32  | 400.0       |
| street::String | Barcelona 2 |
]
[ Info: SELECT "houses"."id" AS "houses_id", "houses"."street" AS "houses_street", "houses"."size" AS "houses_size", "houses"."rooms" AS "houses_rooms" FROM "houses" WHERE street =='Barcelona 3' AND size == 400 ORDER BY houses.id ASC
find(House, SQLWhereExpression("street ==? AND size == ?", "Barcelona 3", 400)) = House[House
| KEY            | VALUE       |
|----------------|-------------|
| id::DbId       | 3           |
| rooms::Int64   | 4           |
| size::Float32  | 400.0       |
| street::String | Barcelona 3 |

Another interesting thing I found:

If the DB record has a NULLfield, findwill return a struct with the default value for the field instead of NULL. So, if we set rooms::Union{Nothing, Int} = 4, loading a House with NULLrooms will return a House with 4 rooms

I'll open an issue about this. also @DearRude, I've opened an issue about inserting missinginto the DB https://github.com/GenieFramework/SearchLight.jl/issues/67

New issue on this https://github.com/GenieFramework/SearchLight.jl/issues/68

Thank you for your extensive investigation.

Can also check for DateTime type? I'd added these lines to my initializers/converters.jl:

convert(t::Type{Nothing}, x::Int) = x
convert(t::Type{Nothing}, x::Float64) = x
convert(t::Type{Nothing}, x::String) = x
convert(t::Type{Nothing}, x::Bool) = x
convert(t::Type{Nothing}, x::DateTime) = x

When I try to findone on a object, I receive this error:

┌ Error: 2023-04-06 01:10:02 TypeError(:setfield!, "", Union{Nothing, Dates.DateTime}, "2022-11-14T20:39:39")
└ @ SearchLight ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:543
ERROR: TypeError: in setfield!, expected Union{Nothing, Dates.DateTime}, got a value of type String
Stacktrace:
 [1] to_model(m::Type{Sale}, row::DataFrames.DataFrameRow{DataFrames.DataFrame, DataFrames.Index}; skip_callbacks::Vector{Symbol})
   @ SearchLight ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:541
 [2] to_model
   @ ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:498 [inlined]
 [3] to_model!!
   @ ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:579 [inlined]
 [4] to_models(m::Type{Sale}, df::DataFrames.DataFrame)
   @ SearchLight ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:480
 [5] find (repeats 2 times)
   @ ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:92 [inlined]
 [6] find(m::Type{Sale}; order::SQLOrder, limit::SQLLimit, offset::Int64, where_conditions::Base.Pairs{Symbol, Int64, Tuple{Symbol}, NamedTuple{(:id,), Tuple{Int64}}})
   @ SearchLight ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:156
 [7] #findone#32
   @ ~/.julia/packages/SearchLight/Ps2Js/src/SearchLight.jl:166 [inlined]
 [8] top-level scope
   @ REPL[4]:1

@Pere Any updates on this? I have this problem on DateTime and Bool types as well which I can't write the right converter for.

Unfortunately we haven't had time to look into these issues. For the DateTime error, you're attempting to load a string from the databse and store it into a DateTime object, which fails.

Perhaps you could have it as a string in the struct and then parse it into a Date object? I know it's not ideal, but you could then define a getter like

get_date(s:MyStruct) = DateTime(s.date)

If you'd like to learn more about getters and setters, I recommend reading Tom Kwong's book Hands-On Design Patterns and Best Practices with Julia

I implemented a similar design but thought there is a cleaner way to do it. Thank you.