jdev - 2024-04-17

sql noob question: how do you add a relation to an existing message in your db? like a reaction or a message

i know the basics of SQL but i am struggling with the mental model

also apparently there is an object variant of sql

search "SQL Join" ✏

you have a message table, and a reaction table, both tables have a message-id column, with SQL Join you can tell the database to "join" both tables and return data from both table in one result

ah and i see join just filters doesnt deletes. makes sense

heh thats why ids in group chats are so important

MSavoritias (fae,ve), another key word you might want to look up is "foreign key"

Okay, two words ;)

ah that makes sense with the relational terminology now

Foreign keys are one of those things that are a needed concept but in real life the implementation is almost never used because it's a huge footgun, and some major DBS even lie about having an implementation lol

so i manually join everything? :( ....

No, joins are very useful but unrelated to the foreign key implementation that some dbs give you

By implementation I mean actually declaring foreign keys and doing cascading deletes and such

I started to work on a db which will store the messages as raw data and use db trigger to build a kind of "functional"-view of a chat. It's just a prototyp, not sure if it will be a good idea. Anyway, I can share the ddl script.

moparisthebest: I mean, this is an area of hot contest I guess? I use foreign keys all the time, very useful. But I know there are some out there who don't even use DB side validations and prefer to do race conditions in the code instead 🤷‍♂️

wtf. i would expect nothing less than a db to be functional/atomic completely

it either works or it doesnt

I mostly agree, but there was a movement around when I first graduated to get rid of all that "pesky db stuff" and reimplement it all in the app instead. I think we're seeing a swing back in the other direction these days but those people are still around of course

MSavoritias (fae,ve), singpolyma: for decades MySQL/mariadb allowed you to define foreign keys but then just ignored them, for one example, I haven't checked how it does it now

I believe there are still significant caveats when database clusters are involved, up to and including just can't do it

moparisthebest: for sure. Bad DBs is a different but related problem I guess

It's also a design decision, if you enable them then you have to delete and insert in a very specific order, and be aware what cascading might do etc

Right I'm just pointing out many bad DBs exist in the wild and that's likely a big reason you won't see them used in real things

in sqlite you need to enable foreign keys on a per session basis

so the db can either ignore them or will honor them

in Gajim we didnt use foreign keys, and now with the new DB Layout we will use them The cascade on delete is nice

about xml:lang, can i depend on at least one body not having a xml:lang? and that is the xml:lang that is set on the stream?

I don't think so

ok i think body is the wrong example

my question is about a element that the server sends

not a user

https://xmpp.org/extensions/xep-0317.html

say hats for example, so i request existing hats

how do i chose what to display to the user?

now you will say the lang the user uses, but how can i be sure that that lang code is the same lang code the server uses

in this example server sets lang to en-us

what if my client uses "en"

sounds like a bit complicated process where i need to map multiple language codes and normalize them to something

I'd look for recommendations or library support, it shouldn't be a completely uncommon problem right?

I would expect web frameworks to have things to select a language based on the Accept-Language header, perhaps some of those are useful or can serve as inspiration?

flow, yes, it is

xml:lang was complicate wasn't it, there was something like en-us.foobar

so ideally you have some logic to find the closest matching lang

Exact match or longest prefix match maybe?

split into tokens, don't pick whatever starts with e

Cascade on delete is nice until you don't want it and oops... It's an especially large foot gun for new devs coming in 😁

looking at https://www.w3.org/International/articles/language-tags/#rfc, the values seems to be easy tokenizable

so just split by '-' and use the one with the longest prefix matches?

Zash, this is for nbxmpp the Gajim xmpp lib

actually I think smack has something for that

so i split and take the first token

sounds like a plan

so if your preference is cs-CZ you would look for an exact match, then cs-*, then cs, then give up and pick something

RFC 5646 (<https://datatracker.ietf.org/doc/html/rfc5646>) and RFC 4647 (<https://datatracker.ietf.org/doc/html/rfc4647>) are what to look at

you can get quite far without actually doing the RFC 5646 dance and just implementing the algorithm in RFC 4647 on strings

lovetox, I have an implementation in aioxmpp, I'm happy to donate it to gajim under whichever FOSS license you choose

moparisthebest, FWIW, as much as I despise mariadb, it has improved and with InnoDB, you can have clustering and properly enforced constraints, we're running a couple clusters in prod.

(yes, MyISAM was and is terrible, no questions there, but nobody uses that anymore)

also apparently OpenStack isn't a real thing? it uses both mysql and foreign keys a lot

Our DBAs have still mandated no FKs in prod, I think because of cluster reasons but I haven't asked in years... We've certainly always been on InnoDB though

thanks, jonas’ will check it out later

jonas’, can you point me to a file, on codeberg i see no search field

i found a LanguageTag class, but it says > This may be a fully RFC5646 compliant implementation some day, but for now it is only very simplistic stub

ah i think i found it, there are some lookup/filter methods

lovetox, exactly

the LanguageTag is just a stub, but the matchers should be RFC 4647 compliant, and that's what you're gonna need

Wasup

I credited my acc with some coin but it did not reflect, why?

Wrong place