Field name problem

[soegaard]

The following program provokes an error:
; prepare: no such column: GITHUB
I suspect that (somewhere) there an "deta field name to sql fieldname" conversion missing.

#lang racket
(require deta db threading)

(define db
  (sqlite3-connect #:database "bug.db"
                   #:mode     'create))

(define-schema github-user
  ([id         id/f        #:primary-key #:auto-increment]
   [user-id    integer/f]            ; racket-stories user-id   
   [github-id  integer/f   #:unique] ; github user id
   [login      string/f    #:unique] ; github login (username)
   [real-name  string/f]             
   [email      string/f]              
   [avatar-url string/f]
   [blog-url   string/f]))


(create-table! db 'github-user)

(lookup db (~> (from github-user #:as gu)
               (where (= github-id 123))))

the full error:

; prepare: no such column: GITHUB
;   error code: 1
; Context:
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:466:0 handle-status*
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:422:8
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:225:4 prepare1* method in connection%
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/private/query.rkt:23:2
;  /Applications/Racket v7.4/collects/db/private/generic/functions.rkt:90:0 compose-statement
;  /Applications/Racket v7.4/share/pkgs/db-lib/db/private/generic/functions2.rkt:64:0
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/query.rkt:254:0 unpack219
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/query.rkt:267:0 lookup
;  "/Users/soegaard/tmp/bug.rkt":1:1 [running body]
; [Due to errors, REPL is just module language, requires, and stub definitions]

[Bogdanp]

I think the issue is that you haven't prefixed github-id with gu in your query. This works:

(lookup db (~> (from github-user #:as gu)
               (where (= gu.github-id 123))))

[soegaard]

You are right. Is it possible to generate a better error message? tor. 26. sep. 2019 kl. 18.10 skrev Bogdan Popa <[email protected]>:

I think the issue is that you haven't prefixed github-id with gu in your query. This works: (lookup db (~> (from github-user #:as gu) (where (= gu.github-id 123)))) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#5?email_source=notifications&email_token=AADQXRLWEQXP2XKWU2FUALTQLTNHVA5CNFSM4I24AFUKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7WD35Q#issuecomment-535576054>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADQXRLZKFVOZFITJYN5AKLQLTNHVANCNFSM4I24AFUA> .

-- -- Jens Axel Søgaard

[Bogdanp]

Yes, I think we can detect cases like this, where the identifier contains a dash. However, I don't think this can be detected in general.

[soegaard]

Actually for the user table I can do:

(define (get-user/id id)
  (lookup db (~> (from user #:as u) (where (= id ,id)))))

That is, it works even when I don't write u.id.
By analogy (where (= github-id 123)))) ought to work too.

[Bogdanp]

By analogy (where (= github-id 123)))) ought to work too.

I would say it's the reverse.

(= id ,id)

IMO, this and any use of an unqualified column name should error out, but it doesn't because the parser for query expressions is not smart enough to distinguish between cases where an identifier refers to the name of a column and cases where an identifier refers to some other identifiers within the database. I could make the parser error out whenever a bare identifier is found anywhere except in function name position, but then stuff like

(cast 1 text)

would also fail to parse (on text) so we'd have to come up with some other way to refer to things that are not columns or functions or special-case casting. The latter would work, but I'm sure that are other cases where you might want to refer to functions or types by name that I can't think of right now.

The easiest solution would be to have things continue working as they do now and just replace -s with _s within identifiers. I think that's what I'll do for now, but I'd recommend always qualifying column names in case I find a solution to the problem as a whole that I like somewhere down the line and break this.

[Bogdanp]

The easiest solution would be to have things continue working as they do now and just replace -s with _s within identifiers.

I spoke too soon and didn't take... well... -, along with a couple other operators like array-slice, into consideration.

I think we might be stuck with this behavior for the time being. I'll have to make a list of all the situations in which bare identifiers can be used and then see if I can't come up with a better solution.