Re: [Artanis] Foreign Key for Migrations

[Nala Ginrut]

Hi Jonathan!

I've fixed it in 10488f4d1996.

I've removed some useless APIs, so if you encouter any issues when run
`art work`, please touch your model/migration files to let Guile recompile those
code again.

BTW, MariaDB will complain to use a big char-field, please consider to
use "text" type to hold big string.

Best regards.


Jonathan Schmeling writes:

> 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
>>


-- 
GNU Powered it
GPL Protected it
GOD Blessed it
HFG - NalaGinrut
Fingerprint F53B 4C56 95B5 E4D5 6093 4324 8469 6772 846A 0058

signature.asc
Description: PGP signature