ORM foreign keys and more

[manson]

Hi!

1. Does foreign keys can be bound only to an integers pk fields? The User model has an Id (int) and Uuid (string). This is good that uuid exists, because I don't use integer primary keys for a long time as they are mostly useless in distributed systems and even in a regular non-distributed apps because apply many limitations with data manipulation. So, I have a simple structure that includes this fields:

	Id        	int     `orm:"pk"`
	Uuid      	string  `orm:"size:40"`
	UserId  	string  `orm:"size:40;fk:users.uuid:cascade:donothing"` 

I just want to bind this model to a User model but not to Id field rather than to Uuid field (see UserId field), but when adding this record by using Admin panel, I get an error like "SQL logic error: foreign key mismatch - "sell_units" referencing "users" ". Why? User model has the same string Uuid field (40 length) to which my UserId referencing and in korm the mask (table:field:delete:update) suppose to use any field to bind to.

2. Can the Uuid be a primary key? As I said, integer Id is useless to me almost all the time, so I would use string uuid for it. In this example I had to add an integer Id pk because Id field is not available when adding record in admin UI (it is hidden) and thus is empty if not specified and later this record cannot be deleted becayse Id is empty.

[kamalshkeir]

hi manson, this is happening because i didn't define uuid in user model as unique key, foreign keys in sql can be binded only to unique columns, so it should work with the user email which is unique, and i should add like for email, an iunique to the uuid.
btw i should update kago orm to use korm instead, many to many not handled in kago orm

[kamalshkeir]

Can the Uuid be a primary key? As I said, integer Id is useless to me almost all the time, so I would use string uuid for it. In this example I had to add an integer Id pk because Id field is not available when adding record in admin UI (it is hidden) and thus is empty if not specified and later this record cannot be deleted becayse Id is empty.

No, even if Ids may sound useless, sql databases using them heavily to find data faster, to paginate , ... , because it is incrementing and ordered, but uuid is not ordered, so keep and id or user_id PK for all your models, and you can add a unique or iunique tag to make it bindable for foreign keys for any other field

[kamalshkeir]

You can update kago to v1.2.6, i added iunique the the uuid, it should be insensitive unique field now if you migrate again, and so it will work a8e7db7#diff-063513fd1fcd84db28b78aae01c0a690d7510c329e79e6b733743eebc25fe06a.

but be aware that i have moved orm drivers outside kago to trim go.mod dependencies, so you will need to see this part in the readme https://github.com/kamalshkeir/kago#drivers-moved-outside-this-package-to-not-get-them-all-in-your-gomod-file

[kamalshkeir]

Many To Many added to kago orm too v1.2.7 https://github.com/kamalshkeir/kago#many-to-many-example

[manson]

Thanks! I got it. Will try all this changes.
Meanwhile the orm integrated into kago automatically reads database name from env file before connecting. But examples of korm shows explicit setting of the database parameters, i.e. korm.New(korm.SQLITE, "db"). What is the best way to use env setting for korm (not orm in kago)? And one more thing - as far as I remember (I may be mistaken), when I create kago app, it automatically creates the database that is set in env. Will this influence the korm usage of the same database file if I later initialize korm with the same parameters? I think orm should be removed from kago in favor of korm not to produce misunderstanding.

[kamalshkeir]

Thanks! I got it. Will try all this changes.
Meanwhile the orm integrated into kago automatically reads database name from env file before connecting. But examples of korm shows explicit setting of the database parameters, i.e. korm.New(korm.SQLITE, "db"). What is the best way to use env setting for korm (not orm in kago)? And one more thing - as far as I remember (I may be mistaken), when I create kago app, it automatically creates the database that is set in env. Will this influence the korm usage of the same database file if I later initialize korm with the same parameters? I think orm should be removed from kago in favor of korm not to produce misunderstanding.

you can use https://github.com/kamalshkeir/kenv to load envs directly to struct config, and for korm and orm , both don't make use of the database, so you can open any database sql, like an old one, only struct tags will change orm or korm

[manson]

Hi!
Unable to use korm only if I want to use it and to have an admin dashboard. Admin dashboard works only with internal orm package that is a separate copy of korm. So if only using korm (and use korm.AutoMigrate) I cannot use admin dashboard - my models there are empty. So I should either use only internal kago.orm package without korm or set orm.AutoMigrate and korm.AutoMigrate both. Something is wrong here. Suppose it is a good idea to initialize app like kago.New(myKormInstance) if I want to use already initialized database connection or leave kago.New() if I want to use an internal orm.

[manson]

Super! Will check it today

[manson]

Database creating and migration with columns types and indexes now works correct. Thanks! As for the triggers - will try it later

[manson]

(logo is cute) :-)

[manson]

What about database multithreading usage? Is it safe to work with sqlite from different threads?

[kamalshkeir]

yep, it should be completely safe

[kamalshkeir]

@manson , i added these hooks, they will work only with the korm Insert Set Delete AddRelated DeleteRelated and Drop:

OnInsert(func(database, table string, data map[string]any) error {
		fmt.Println("inserting into", database, table, data)
		return nil
	})

	OnSet(func(database, table string, data map[string]any) error {
		fmt.Println("set into", database, table, data)
		return nil
	})

	OnDelete(func(database, table, query string, args ...any) error {})

	OnDrop(func(database, table string) error {})

[kamalshkeir]

i will try to return inserted data using sql, if it work , this will avoid fetching data after insert

[manson]

if data not inserted you will get an error explaining why - no, in this case, the hook will not just rise. Although, error log should be written to find the tails.
why you need to check if data inserted correctly - no. If data saved, it is considered to be correct. The point is if it is inserted I might need this whole object to... clone it, send it somewhere or to do else things (send by email some info from this or do whatever). I did not use sqlite much before but most sql drives returns inserted record ID, so if it is possible, replacement of returning the record only id would be nice as well, you may return this id.
this mean that i need to get the inserted object after each insert - only if hook is set. If not - there is no need to do so. Or instead you may return only inserted id in this hook. As I understand, the only hard thing is with insertion and ID of inserted record - in all other cases the object (updated or inserted) is in your hand. Of course, if this is not a bulk (by query) update or insert or delete. In this case, I'm a bit confused how these hooks should work...
Anyway if it is hard to implement or fails for some reason, I do not insist on this functionality (although I need it), I will work around this somehow ///

[kamalshkeir]

okay, i got your point, so i manage to return the inserted id for all db dialects, it was causing problem for mysql because mysql doesn't have RETURNING keyword, and for postgres because golang database/sql LastInsertId() support all dialect except postgres , but now it's working well for all of them, i have the id :)

so now, what i need to do, is to get the inserted object , the hook will take an id int and data any because data can be struct or map no ?, so on receive you will cast it ?

[kamalshkeir]

Although, error log should be written to find the tails. : do you mean to a file ?
i never used this feature in a logger because i always run my application on production using systemd or supervisor or nginx or caddy, all of them can write you application output to a log file, then i use grafana loki to scrap my log file and make them available directly in the dashboard to query in, you can do it the same way using docker docker inspect --format='{{.LogPath}}' containername because docker write logs to single file also

i have created a test user for you if you want to check how it look like:
go to https://mon.kamalshkeir.dev/
username : test
pass: test2023

using it this way allow you to correlate your metrics with your logs, you can query logs at certain time, when cpu usage go above 80% for example, it allow you also to have alerts on pretty much any application like slack , discord, whatsapp ....

[manson]

now it's working well for all of them, i have the id :) - super! :-) Waiting for a final implementation

so now, what i need to do, is to get the inserted object , the hook will take an id int and data any because data can be struct or map no ?, so on receive you will cast it ? - I dont know how to do this best in this situation. I think map is ok for universality or you may suggest some better way

do you mean to a file ? - I mean the standard way kago does this. In reality, it is an application responsibility to check if data saves well. At least it can catch any exceptions or errors at this stage, so no AFTER hooks needs if it fails.

i have created a test user for you if you want to check how it look like: - looks great!

[manson]

Meanwhile, I found some other issue :-) Don't think I should open new issue in discussions, it is small and can be added here.
It is always a problem to work with times from different parts of the planet. I constantly face this in my projects. So here it is.

1. Standard fields UpdatedAt and CreatedAt (time.Time) works well in dashboard and shows the correct value in structs being read from databse. But if I add a custom time.Time field LastCallAt and fill it in a dashboard vie dropdown calendar, it shows up there differently from those fields and :
   
   And when I query it from the korm it gives me this value: 0001-01-01 00:00:00 +0000 UTC

2. Not issue but question - how can I use nullable properties in struct that is used with korm? I know I can define fields as pointers to a simple type (int, string, time) and null from database can normally reflect to a null pointer. But looks like with korm it doesn't work. Am I right or I missed something? I have constant exceptions when korm unable to reflect nullable fields from database to a struct in code. And exception is not as informative as needs - I spend an hour to find out what is going on here :-)

[kamalshkeir]

most deeds are done and health came to a normal state

Great news bro :), take care of yourself

had to write down and finish the long awaited next chapter for my book I write more than a year

Wow, i didn't know that you write, a book about coding ?

[manson]

Wow, i didn't know that you write, a book about coding ?

No :-) More than 10 years I write science fiction and fantasy novels. I have about 14 books released. But it is all in Russian. So, this is the favorite activity in my spare time :-) Some enthusiasts tried to translate them to english (my english is not well for books) but they did not go beyond a few chapters - it is a difficult work. And since last year it has no sense to do this at all...

[kamalshkeir]

Wow, really impressive man, very nice hobby, keep doing it

Some enthusiasts tried to translate them to english (my english is not well for books) but they did not go beyond a few chapters

Did you try ChatGPT for translating ? , i am ready to try also if you want , my Russian is not good also but with the help of technologies everything possible :p

[manson]

Did you try ChatGPT for translating ? , i am ready to try also if you want , my Russian is not good also but with the help of technologies everything possible :p

I doubt that ChatGPT will translate better than specialized AI translators trained in translation. Basically, my first book is conditionally translated - the first 11 chapters were translated by an enthusiast, and starting from chapter 12 - by a DeepL translator (without my proofreading, therefore somewhat clumsy). If you are interested, you can look here . I even, basically, began to bring it all into a digestible form, but then this war began, we were cut off the opportunity to earn money in the West (I planned to post a book in English on Amazon) - you can't withdraw what you earned, so I postponed this case for the time being as unpromising for the near future.

My thoughts about the translation. In fact, several enthusiastic people tried to translate, mostly Russian speakers living in America, Canada, Poland. But they were not enough for a long time - this is a difficult matter, in fact, you have to write the book yourself in your own language. Knowing several languages does not mean that you can translate a work of art beautifully. The best translations are usually made by writers or in some way related to writing. But, since I am a programmer, I still believe in the victory of AI at least in this area, especially since it is expensive to pay for a high-quality translation to a professional and I cannot afford it. :-) And even started doing something, but apparently it's not fate yet.

Briefly about the book, this is a series of 14 books about the adventures of a programmer in a fantasy world, where he tries to understand magic from the point of view of logic, algorithmistics and programming, and he even succeeds. There I logically substantiate all the types of magic known to us - ordinary, sorcery, astral. It explains who witches are, how they work. What is the astral. What are egregors. But the basis for all this is informational magic, describing how the world works. At least a few physics readers helped me to make it all look real and scientifically reliable. It still happens that some psychics write to me and say that what I have described is almost true, although I invented it all and logically deduced it from what I read or see around.

That's the way things are. If you get interested, look at the link above. But in any case, the translation is not relevant for me now, since it will not allow me to earn money (although initially I wrote the book not for making money, but for the soul), and there are so many cases here, locally.

But anyway - thanks for the offer. :-)

[kamalshkeir]

I understand. This is neat !, congratulations, so proud to have people like you around me, which you lot of good luck

[manson]

@kamalshkeir
Just a note - when creating bool field if not specify "default: false" (default value) in case if wish to add new conditions (I wanted to index it - korm:"index" it does not create field and rises exception. In case of simple usage Field bool or with defaults Field bool `korm:"default: false; index"` it works.

[kamalshkeir]

@kamalshkeir
Just a note - when creating bool field if not specify "default: false" (default value) in case if wish to add new conditions (I wanted to index it - korm:"index" it does not create field and rises exception. In case of simple usage Field bool or with defaults Field bool `korm:"default: false; index"` it works.

hi @manson , okay, i will check it tonight, Thank you

[kamalshkeir]

@manson go get -u github.com/kamalshkeir/[email protected]