[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Artanis] Foreign Key for Migrations
|
From: |
Jonathan Schmeling |
|
Subject: |
Re: [Artanis] Foreign Key for Migrations |
|
Date: |
Sun, 14 Apr 2019 17:53:38 +0000 |
Yup; that was exactly my question. Thanks a ton! Works great, now.
Gotcha; sounds good. I'm building stuff with Artanis, currently, so I'll
definitely report any bugs I find.
Speaking of, I think the SQL command for creating a table for models may
be generating an extra comma; the table I needed the multiple primary
keys got created, just fine, but a previous model that used to just work
is now throwing up errors. Here's what I have in the model:
-------------------------code------------------------------
(create-artanis-model ; DO NOT REMOVE THIS LINE!!!
PEOPLE
(ID auto (#:primary-key #:not-null #:unique))
(USERNAME char-field (#:maxlen 32 #:not-null #:unique))
(E_MAIL char-field (#:maxlen 256 #:not-null))
(PASSWORD char-field (#:maxlen 500 #:not-null))
(SALT char-field (#:maxlen 256 #:not-null))
(NAME char-field (#:maxlen 32 #:not-null #:default ""))
(SUMMARY char-field (#:maxlen 500 #:not-null #:default ""))
(CREATED_AT big-integer (#:not-null))
(CONFIRMATION_TOKEN char-field (#:maxlen 128 #:not-null))
(PUBLIC char-field (#:maxlen 10000 #:not-null))
(PRIVATE char-field (#:maxlen 10000 #:not-null)))
-------------------------end-------------------------------
and Artanis keeps throwing up this error:
web_1 | ;;; note: source file /myapp/app/models/PEOPLE.scm
web_1 | ;;; newer than compiled
/root/.cache/guile/ccache/2.2-LE-8-3.A/myapp/app/models/PEOPLE.scm.go
web_1 | ;;; note: auto-compilation is enabled, set GUILE_AUTO_COMPILE=0
web_1 | ;;; or pass the --no-auto-compile argument to disable.
web_1 | ;;; compiling /myapp/app/models/PEOPLE.scm
web_1 | ;;; compiled
/root/.cache/guile/ccache/2.2-LE-8-3.A/myapp/app/models/PEOPLE.scm.go
web_1 | SQL: create table PEOPLE (ID serial UNIQUE NOT NULL PRIMARY
KEY,USERNAME varchar(32) NOT NULL UNIQUE,E_MAIL varchar(256) NOT
NULL,PASSWORD varchar(500) NOT NULL,SALT varchar(256) NOT NULL,NAME
varchar(32) NOT NULL DEFAULT "" ,SUMMARY varchar(500) NOT NULL DEFAULT
"" ,CREATED_AT integer(64) NOT NULL,CONFIRMATION_TOKEN varchar(128)
NOT NULL,PUBLIC varchar(10000) NOT NULL,PRIVATE varchar(10000) NOT
NULL,) engine=InnoDB;
web_1 | Backtrace:
web_1 | 13 (apply-smob/1 #<catch-closure 55b656075560>)
web_1 | In ice-9/boot-9.scm:
web_1 | 705:2 12 (call-with-prompt _ _ #<procedure
default-prompt-handle…>)
web_1 | In ice-9/eval.scm:
web_1 | 619:8 11 (_ #(#(#<directory (guile-user) 55b6560ce140>)))
web_1 | In /usr/local/bin/art:
web_1 | 42:12 10 (_ _ _)
web_1 | In artanis/commands/work.scm:
web_1 | 135:8 9 (work . _)
web_1 | In artanis/artanis.scm:
web_1 | 320:2 8 (run #:host _ #:port _ #:debug _ #:use-db? _
#:db-proto …)
web_1 | In unknown file:
web_1 | 7 (run-hook #<hook 0 55b656298570 init-work>)
web_1 | In artanis/commands/work.scm:
web_1 | 119:2 6 (init-work)
web_1 | 73:2 5 (try-load-app)
web_1 | In srfi/srfi-1.scm:
web_1 | 640:9 4 (for-each #<procedure 55b6565bb200 at
artanis/mvc/mode…> …)
web_1 | In ice-9/boot-9.scm:
web_1 | 2312:4 3 (save-module-excursion _)
web_1 | 3822:12 2 (_)
web_1 | In app/models/PEOPLE.scm:
web_1 | 4:0 1 (_)
web_1 | In artanis/fprm.scm:
web_1 | 400:8 0 (_ PEOPLE _ #:if-exists? _ #:engine _ #:dump _ # _)
web_1 |
web_1 | artanis/fprm.scm:400:8: Throw to key `artanis-err' with args
`(500 #<procedure DB-query (conn sql #:key check?)> "failed reason:
`~a'~%" "You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ') engine=InnoDB' at line 1")'.
web_1 | Loading conf/artanis.conf...done.
web_1 | Loading server engine 'ragnarok' ...
web_1 | Loading server engine 'ragnarok' ...
web_1 | User wants to use Database, initializing...
web_1 | connection pools are initilizing...<mysql>
danihub:dani:hubadub:tcp:db:3306
web_1 | <mysql> danihub:dani:hubadub:tcp:db:3306
web_1 | <mysql> danihub:dani:hubadub:tcp:db:3306
web_1 | <mysql> danihub:dani:hubadub:tcp:db:3306
web_1 | <mysql> danihub:dani:hubadub:tcp:db:3306
web_1 | DB pool init ok!
web_1 | Now the size of connection pool is 5.
web_1 | DB init done!
web_1 | Initilizing session backend `SIMPLE'...
web_1 | Session with SIMPLE backend init done!
web_1 | Loading models...
web_1 | show tables like 'FOLLOWERS';
web_1 | [WARN] No cache for migration of `FOLLOWERS'
web_1 | Run `art migrate up FOLLOWERS', then try again!
web_1 | show tables like 'PEOPLE';
web_1 | Creating table `PEOPLE' defined in model......show tables like
'PEOPLE';
web_1 | create table PEOPLE (ID serial UNIQUE NOT NULL PRIMARY
KEY,USERNAME varchar(32) NOT NULL UNIQUE,E_MAIL varchar(256) NOT
NULL,PASSWORD varchar(500) NOT NULL,SALT varchar(256) NOT NULL,NAME
varchar(32) NOT NULL DEFAULT "" ,SUMMARY varchar(500) NOT NULL DEFAULT
"" ,CREATED_AT integer(64) NOT NULL,CONFIRMATION_TOKEN varchar(128)
NOT NULL,PUBLIC varchar(10000) NOT NULL,PRIVATE varchar(10000) NOT
NULL,) engine=InnoDB;
I remembered you saying that auto automatically throws in #:primary-key
and thought that maybe it was tripping over me using that on the ID
column but removing the #:primary-key designation for ID still had the
same error.
Jonathan
On 4/14/19 12:29 AM, Nala Ginrut wrote:
> Hi Jonathan!
>
> Jonathan Schmeling writes:
>
>> Thanks a ton for the explanation!
>>
>> Still model-related, how might one pick two columns as primary keys for
>> models?
> I'm not very sure I understand your question. I guess you're asking
> about how to specify multi-primary-keys in migration/models. If so, then
> you should try this (we take migration as an example, but models are
> similar):
>
> -------------------------code------------------------------
> (migrate-create
> (create-table
> 'person
> '(id big-integer)
> '(name char-field (#:not-null #:maxlen 10))
> '(age tiny-integer (#:not-null))
> '(email char-field (#:maxlen 20))
> #:primary-keys '(name id)))
> -------------------------end-------------------------------
>
> Please notice that you can only specify all primiary-keys in
> #:primary-keys, if you also specify "id" as "auto" type, then it'll will
> be expand to primary-key either. Then the SQL will throw exception. And
> this is not the mechanism of Artanis, it's SQL.
>
> BTW, I feel embarrass that this #:primary-keys feature was actually
> missing in Artanis although I thought I've done it. Then I realized the
> problem when you mention it. ;-)
> I've fixed it in commit 812197ea77f048a, please pull and try.
>
>> And, – on that note – I know you mentioned foreign relational mapping
>> still needing some work to be developed. I don't know how useful I can
>> be (most of Artanis's source code still goes over my head, a bit) but,
>> if I can be of help, I'd love to give back what I can.
> For the design of non-foreign-keys, I'm still researching to find a
> better way. If you want to help, I think in this moment, try to use
> Artanis and report will be great contributions. Because I can't cover
> every corner cases, although I've done many features, I can't guarrentee
> they work as expected, it requires more tests and reports from users.
>
>
> Anyway, thank you very much!
> Best regards.
>
>
>> Jonathan
>>
>> On 3/24/19 1:46 AM, Nala Ginrut wrote:
>>> Hi Jonathan!
>>>
>>> Jonathan Schmeling writes:
>>>
>>>
>>>> In the mean time, what is the difference between the migration and
>>>> models since I have a feeling I don't fully understand. Is it just that
>>>> models will handle creating the tables, now, rather than migrations but
>>>> migrations are still useful for creating dummy/test data in the tables?
>>> Actually, migration and models are sharing the same implemtation
>>> mechanism in the low-level. But there're 2 differences:
>>> 1. They're slightly different in syntax, migration doesn't use macros of
>>> Scheme, so you have to add quote-sign "'" explicitly. This is trivial.
>>>
>>> 2. They're different for the purpose. Migration is a tool to migrate DB
>>> data, or easily handling when you're still designing your tables. You
>>> may change the definition as you wish, and run it arbitrarily. The
>>> tables in migration will not be recognized by Artanis, it's just for
>>> creating/dropping or transferring data.
>>>
>>> Models are the mapping to the existing tables after you confirmed your
>>> design. And should be cautiously changed, because I will add
>>> verification mechanism in the future. That is to say, the definition
>>> that you put in model will be recognized by Artanis. If you've defined a
>>> table in model, it will be created if it doesn't exist yet, but if it
>>> exists, Artanis will verify its schema with your definition to make
>>> sure that the table you want.
>>>
>>> Best regards.
>>>
>>>
>>>> Jonathan
>>>>
>>>> On 3/18/19 12:53 PM, Nala Ginrut wrote:
>>>>> Jonathan Schmeling writes:
>>>>>
>>>>>> web_1 | app/models/PEOPLE.scm:4:0: In procedure module-lookup: Unbound
>>>>>> variable: PEOPLE
>>>>> For this issue, please update to the latest commit, I just fixed few
>>>>> hours ago. However, you shouldn't encounter this issue since I've told
>>>>> you to define the people table in app/models/PEOPLE.scm.
>>>>> So even after fix, it will throw an error to force you define PEOPLE.
>>>>>
>>>>> PS: Define it in the migration is useless here, since migration and
>>>>> model are different things.
>>>>>
>>>>>
>>>>>> web_1 | Backtrace:
>>>>>> web_1 | 0 (primitive-load "/myapp/app/controllers/#{}#.scm")
>>>>>> web_1 |
>>>>>> web_1 | ERROR: In procedure primitive-load:
>>>>>> web_1 | In procedure open-file: No such file or directory:
>>>>>> "/myapp/app/controllers/#{}#.scm"
>>>>> This issue is unrelated to models, it means you've created a controller
>>>>> whose name is a null string. I don't know how you made it, but you need to
>>>>> check your code.
>>>>>
>>>>>> But I'm not certain what to make of this error, unfortunately.
>>>>>>
>>>>>> Jonathan
>>>>>> On Sunday, March 17, 2019, 2:49:49 PM CDT, Nala Ginrut <address@hidden>
>>>>>> wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi Jonathan!
>>>>>> I've realized that there's bug in models after I fixed some bugs in
>>>>>> migration before.
>>>>>> Now I think it's fixed in a88c7e189a6a6273399e6e713be407ef55938f65
>>>>>>
>>>>>> Please notice that you're forced to define the table in
>>>>>> define-artanis-model from now on, if you just leave it as
>>>>>> (define-artanis-model people) after drawing a model, then
>>>>>> art command will throw error.
>>>>>>
>>>>>> Here's an example:
>>>>>> ----------------code------------------
>>>>>> (create-artanis-model
>>>>>> people
>>>>>> (id auto (#:not-null #:primary-key))
>>>>>> (first_name char-field (#:maxlen 30 #:not-null))
>>>>>> (last_name char-field (#:maxlen 30 #:not-null)))
>>>>>> ----------------end-------------------
>>>>>>
>>>>>> And another notion is that the syntax is slightly different from the
>>>>>> mogration, in migration, you should use symbol explicitly:
>>>>>> -----------------code---------------------
>>>>>> (create-table
>>>>>> 'people
>>>>>> '(id auto (#:not-null #:primary-key))
>>>>>> '(first_name char-field (#:maxlen 30 #:not-null))
>>>>>> '(last_name char-field (#:maxlen 30 #:not-null)))
>>>>>> -----------------end----------------------
>>>>>>
>>>>>> But in Models, the list is quasiquote, so you can use unquote-sign, say
>>>>>> "," to escape the content if you like.
>>>>>>
>>>>>> Please try it, and don't hasitate to report bugs. ;-)
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>>
>>>>>> Jonathan Schmeling writes:
>>>>>>
>>>>>>> Thanks so much for the response! Ahhh; I did wonder if Artanis had
>>>>>>> anything had anything like that. That is really nice to work with.
>>>>>>>
>>>>>>> So I have a table named PEOPLE; so I ran art draw model PEOPLE and that
>>>>>>> reated a file named PEOPLE.scm which contains
>>>>>>>
>>>>>>> ;; Model PEOPLE definition of myapp
>>>>>>> ;; Please add your license header here.
>>>>>>> ;; This file is generated automatically by GNU Artanis.
>>>>>>> (create-artanis-model PEOPLE) ; DO NOT REMOVE THIS LINE!!!
>>>>>>>
>>>>>>> I then added to one of my controllers (use-modules (app models PEOPLE))
>>>>>>> and running Artanis and visiting endpoints in that controller didn't
>>>>>>> throw any errors.
>>>>>>>
>>>>>>> So then I added a call similar to the one you had that uses ($PEOPLE …)
>>>>>>> in the controller; however, then I get the error In procedure
>>>>>>> module-lookup: Unbound variable: $PEOPLE when I visit the endpoint that
>>>>>>> uses the function call.
>>>>>>>
>>>>>>> Did I miss something?
>>>>>>>
>>>>>>> Jonathan
>>>>>>> On Saturday, March 16, 2019, 3:31:19 PM CDT, Nala Ginrut
>>>>>>> <address@hidden<mailto:address@hidden>> wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Hi Jonathan!
>>>>>>> Sorry it's my mistake to neglect this part in the doc.
>>>>>>> You don't have to take care of the DB connection management by
>>>>>>> yourself. You may just use `art draw model person' to create the named
>>>>>>> mapping to the "person" table, and there's an exported relational
>>>>>>> mapping object named "$person", you may import (app models person) in
>>>>>>> other controller to use it. It's based on FPRM, but the table name has
>>>>>>> already been wrapped into the closure:
>>>>>>> https://www.gnu.org/software/artanis/manual/manual.html#orgeff2a47
>>>>>>>
>>>>>>> And maybe read its implementation is clearer to you:
>>>>>>> https://gitlab.com/NalaGinrut/artanis/blob/master/artanis/mvc/model.scm#L226
>>>>>>>
>>>>>>> The usage should look like this:
>>>>>>>
>>>>>>> ($person 'set #:name "nala" #:age 100)
>>>>>>>
>>>>>>> BTW, please don't forget to config your DB in conf/artanis.conf first.
>>>>>>>
>>>>>>> Best regards.
>>>>>>>
>>>>>>>
>>>>>>> Jonathan Schmeling writes:
>>>>>>>
>>>>>>>> Gotcha. That makes a lot of sense. Thanks a ton for explaining!
>>>>>>>>
>>>>>>>> Database related, is there a way to query the database from one of the
>>>>>>>> controller -define methods (like (article-define show …) in the online
>>>>>>>> documentation)? Using the function map-table-from-DB causes an error
>>>>>>>> and attaching #:conn #t, like with the get function, also causes an
>>>>>>>> error. Doing something like (:conn rc "SELECT * FROM PEOPLE") seems to
>>>>>>>> work (or, at least, not throw an error) but I'm not sure what gets
>>>>>>>> returned. The doc. says the returned value is described at
>>>>>>>> https://www.gnu.org/software/artanis/manual/manual.html#org8ba121f but
>>>>>>>> that section says "TODO".
>>>>>>>> As always, any help is always super appreciated!
>>>>>>>>
>>>>>>>> Jonathan
>>>>>>>>
>>>>>>>> On 2/17/19 12:19 PM, Nala Ginrut wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>> Hi Jonathan!
>>>>>>>> I'm glad you asked this question.
>>>>>>>> The answer is actually written in the fprm.scm code comments that the
>>>>>>>> relational mapping in Artanis is not going to provide database level
>>>>>>>> foreign keys. The solution is to provide higher-abstract-level table
>>>>>>>> operations as a replacement to save users to time to handle by
>>>>>>>> themselves.
>>>>>>>>
>>>>>>>> The pros and cons of foreign keys are discussed a lot in the
>>>>>>>> internet. The modern ORM may choose to drop foreign keys and provide
>>>>>>>> higher layer for it.
>>>>>>>>
>>>>>>>>
>>>>>>>> Unfortunately, this feature has not been implemented yet because of my
>>>>>>>> time schedule.
>>>>>>>>
>>>>>>>> Of course, this issue is still open, the original plan is more
>>>>>>>> ambitious. I think it's the time to add more features to the relational
>>>>>>>> mapping framework.
>>>>>>>> Welcome to discuss if you like.
>>>>>>>>
>>>>>>>> Best regards.
>>>>>>>>
>>>>>>>> Jonathan Schmeling writes:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> I'm not sure if it's been asked before but I couldn't find any mention
>>>>>>>> in the doc. – is it feasible to define foreign keys for the
>>>>>>>> create-table
>>>>>>>> function? I want to reference another table's primary keys from a
>>>>>>>> particular table, to link them.
>>>>>>>>
>>>>>>>> Jonathan
>>>>> --
>>>>> GNU Powered it
>>>>> GPL Protected it
>>>>> GOD Blessed it
>>>>> HFG - NalaGinrut
>>>>> Fingerprint F53B 4C56 95B5 E4D5 6093 4324 8469 6772 846A 0058
>>> --
>>> GNU Powered it
>>> GPL Protected it
>>> GOD Blessed it
>>> HFG - NalaGinrut
>>> Fingerprint F53B 4C56 95B5 E4D5 6093 4324 8469 6772 846A 0058
>