GenieFramework / GenieFramework/SearchLight.jl
If primary key is not an integer, inserting items fails
- Dominant language
- Julia
- Stars
- 149
- Forks
- 18
- PR merge metrics
- No merged PRs in 30d
Description
**Describe the bug**
When setting the `PRIMARY KEY` as something other than the default `INTEGER`, `INSERT`s fail. For example:
In the `db/migrations/*_create_table_users.jl` `up()` function,
```
function up()
create_table(:users) do
[
column(:id, :string, "PRIMARY KEY")
column(:name, :string)
]
end
add_index(:users, :id)
add_index(:users, :name)
end
```
And in an example `app/resources/users/Users.jl` file,
```
module Users
using SearchLight
using Genie.Sessions
export User
mutable Struct User <: Abstract Model
### INTERNALS
_table_name::String
_id::String
### FIELDS
id::DbId
name::String
end
### CONSTRUCTOR
User(;
### FIELDS
id = Sessions.id(), # Genie.Sessions.id() returns a session id string
name = ""
) = User ("users", "id", id, name)
function seed()
CurrentUsers = [
("sarah"),
("evelyn")
]
for u in CurrentUsers
User(name = u[1]) |> SearchLight.save!
end
end
```
**Error stacktrace**
```
julia> using Users
julia> Users.seed()
[ Info: UPDATE users SET "id" = '3a75e15db783a092cbc7746b7453c3efff3d3fce10404e8a7600ffdcd37e2229', "name" = 'sarah' WHERE users.id = '3a75e15db783a092cbc7746b7453c3efff3d3fce10404e8a7600ffdcd37e2229'
0.075448 seconds (88.17 k allocations: 4.479 MiB)
[ Info: ; SELECT CASE WHEN last_insert_rowid() = 0 THEN 3a75e15db783a092cbc7746b7453c3efff3d3fce10404e8a7600ffdcd37e2229 ELSE last_insert_rowid() END AS id
ERROR: SQLite.SQLiteException("unrecognized token: \"3a75e15db783a092cbc7746b7453c3efff3d3fce10404e8a7600ffdcd37e2229\"")
Stacktrace:
[1] sqliteerror(::SQLite.DB) at /home/sarah/.julia/packages/SQLite/dZY3j/src/SQLite.jl:15
[2] macro expansion at /home/sarah/.julia/packages/SQLite/dZY3j/src/consts.jl:21 [inlined]
[3] sqliteprepare at /home/sarah/.julia/packages/SQLite/dZY3j/src/SQLite.jl:103 [inlined]
[4] SQLite.Stmt(::SQLite.DB, ::String) at /home/sarah/.julia/packages/SQLite/dZY3j/src/SQLite.jl:90
[5] #Query#16(::Array{Any,1}, ::Bool, ::Bool, ::Type{SQLite.Query}, ::SQLite.DB, ::String) at /home/sarah/.julia/packages/SQLite/dZY3j/src/tables.jl:102
[6] Query at /home/sarah/.julia/packages/SQLite/dZY3j/src/tables.jl:102 [inlined]
[7] macro expansion at ./util.jl:155 [inlined]
[8] query(::String, ::Bool, ::SQLite.DB) at /home/sarah/.julia/packages/SearchLight/ardQn/src/database_adapters/SQLiteDatabaseAdapter.jl:211
[9] #query#1(::Bool, ::Bool, ::typeof(SearchLight.Database.query), ::String) at /home/sarah/.julia/packages/SearchLight/ardQn/src/Database.jl:163
[10] #query at ./none:0 [inlined]
[11] #query#54 at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:1230 [inlined]
[12] query at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:1230 [inlined]
[13] #_save!!#46(::Symbol, ::Bool, ::Array{Symbol,1}, ::typeof(SearchLight._save!!), ::User) at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:458
[14] #_save!! at ./none:0 [inlined]
[15] #save!!#43(::Symbol, ::Bool, ::Array{Symbol,1}, ::typeof(SearchLight.save!!), ::User) at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:426
[16] #save!! at ./none:0 [inlined]
[17] #save!#42 at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:423 [inlined]
[18] save! at /home/sarah/.julia/packages/SearchLight/ardQn/src/SearchLight.jl:423 [inlined]
[19] |> at ./operators.jl:854 [inlined]
[20] seed() at /home/sarah/lib/iqcc-server/iqcc-api/app/resources/users/Users.jl:37
[21] top-level scope at REPL[2]:1
```
The error appears to be occurring [HERE](https://github.com/GenieFramework/SearchLight.jl/blob/75da317b0a845dd6b0efbd32324744dfa3221347/src/database_adapters/SQLiteDatabaseAdapter.jl#L253) because the data type of the primary key is not checked, is assumed to be an integer, and hence when it is not an integer, the value is not escaped and the "unrecognized token" error is thrown.
**To reproduce**
See above.
**Expected behavior**
The data type of the primary key should be checked and the query should be made according to the datatype, e.g. the `id` should be escaped if the data type is string.
**Additional context**
```julia> versioninfo()
Julia Version 1.3.1
Commit 2d5741174c (2019-12-30 21:36 UTC)
Platform Info:
OS: Linux (x86_64-pc-linux-gnu)
CPU: Intel(R) Xeon(R) CPU E5-2678 v3 @ 2.50GHz
WORD_SIZE: 64
LIBM: libopenlibm
LLVM: libLLVM-6.0.1 (ORCJIT, haswell)
Environment:
JULIA_REVISE = auto
```
```pkg> st
Status `~/lib/iqcc-server/iqcc-api/Project.toml`
[c43c736e] Genie v0.24.2
[e6f89c97] LoggingExtras v0.4.0
[295af30f] Revise v2.5.0
[340e8cb6] SearchLight v0.17.0 #master (https://github.com/GenieFramework/SearchLight.jl.git)
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.