No.549
Is SQL retarded? Or am I retarded? I'm ready to switch to another database system because SQL is making it so fucking hard to do such simple and fundamental things. I can't believe this fucking trash is the worldwide database standard.
I need to do something like pic related. Basically there's categories and entries, each entry needs to have a value based on how manyth item it is in their given category. The category also needs to have a counter for how many entries it has. The problem is that there's no way to increment the category counter and then read it in a single query, in fact it doesn't even seem possible in 2 (you need UPDATE->SELECT->INSERT). The problem is that there's a delay between each query, and if there's multiple requests then multiple people are going to increment the value before anyone can read it, thus a number gets skipped and multiple entries will have the same value. Even if I managed to get the correct incremented value, the inserts might happen in the incorrect order (undesirable but I could deal with it). I can't count the entries or read the max value because some might have been deleted, and it doesn't actually solve the problem anyway because there's no way to do mathematical min/max operations on the category counter. I can't use auto-increment on the entry number because all the entries need to be in the same table. There will be potentially so many "categories" that keeping them in memory and doing the updates there is not viable unless I make some caching system that complicates everything.
It's almost literally impossible to increment a value and then know what that value ends up being. How is this acceptable? Or is there some esoteric method for accomplishing this that google doesn't want me to know about? Is there some specific SQL database that lets you do it (I'm currently using MariaDB)?
____________________________
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.550
>>549
>The problem is that there's no way to increment the category counter and then read it in a single query, in fact it doesn't even seem possible in 2 (you need UPDATE->SELECT->INSERT). The problem is that there's a delay between each query, and if there's multiple requests then multiple people are going to increment the value before anyone can read it, thus a number gets skipped and multiple entries will have the same value
Lookup TRANSACTIONS in whatever Database System you're using. "SQL" is just a query language.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.551
>>549
>The category also needs to have a counter for how many entries it has.
Also instead of updating counters, perhaps you can just use count statements?
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.552
>>549
You're retarded: SQL is fine for relational data, but you're trying to do something that's not really relational and you're also being stupid about it.
Stop treating the database as a C array and read some theory, because the counter thing can be done pretty easily as a single ACID transaction.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.553
>>551
>The category also needs to have a counter
The category also NEEDS to have a counter. But like I said, I can't just count entries. Some might have been deleted, and since the queries aren't sequential someone's going to overwrite it with a smaller value since you can't do counter=max(counter,newItemValue).
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.554
>>552
Eat shit retard. It needs the fucking counter whether you like it or not.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.555
>>554
>is retarded
>can't read
>gets mad
Why are you even here?
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.559
>>549
>Is SQL retarded?
SQL is NOT retarded, it's a reliable, sturdy, well tested technology that offers you bulletproof integrity. the problem with SQL is, that is was such a hallmark standard for such a long time that developers didn't even think about using something else. Bulletproof integrity and relational thinking is not always needed and neither the best solution to every problem. If you develop a more or less simple web application, chances are you don't need all the features of SQL but instead want it to be fast and easy to develop for. Or you may want to distribute shit to a cluster. Then you might wanna use a NoSQL system. In many, many cases it just doesn't fucking matter and everything will work as long as the dev is comfortable with it.
But saying SQL is retarded is retarded.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.560
>>549
Considering your problem, you could handle this in the application layer. But of course it would be nicer to handle it in the DB design iself. Can't you just put everything you need into a transaction that locks the data until all operations are exectuted?
Also be careful with aggregations like count() when it comes to performance.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.568
>>555
Isn't that nice, you give the anon a perfect answer then he calls you retarded and tells you to eat shit. I suspect he's a low IQ simian becoming frustrated with WHITE technology.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.756
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.758
>>555
I was already getting this fucking obnoxious "don't do what you need to do and do this other thing instead" response, so I lost the patience to go reading about "acid transactions" which also sounded like doing something other than having a counter value.
I did look up transactions >>550 >>560 which seemed like they could work (particularly grouping statements with START TRANSACTION and COMMIT), but could not get it to work because just like DELIMITER, it's giving me a syntax error no matter what I do even if I literally copypaste the sample code from the MariaDB documentation. That's also why I can't experiment with triggers, since I need to change the delimiter and I can't. And thanks to genius software design it's not giving me any indication whatsoever about what the problem is other than there being a syntax error somewhere out there.
>>559
To be fair I don't think a noSQL database will solve this particular problem.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.759
>>758
Oh and returning a value after insert/update is not possible either. That's one of the first things I tried, supposedly it should be possible in some SQL databases, but not for me.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.760
>>758
>getting this fucking obnoxious "don't do what you need to do and do this other thing instead" response
You can't get transactions working, so I don't think you're in a position to sperg out and call others obnoxious. You are in all likelihood doing it wrong.
Drop the emotional responses, and just read a book on database design.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.762
>>760
I literally just told you that none of the solutions are working, like iterally not working, "erroring out" kind of not working, including the ones given in this thread.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.788
>>758
>as soon as there is a single dumb or unhelpful post in the thread I become enraged and lose the ability to read
>next week I remember I'm retarded, so I come back to the same thread hoping someone will sponfeed me a solution
If you can't get the code examples working there's no point in trying to do anything more complicated, I'll leave it at that.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.789
>Is SQL retarded? Or am I retarded?
It's you that is retarded.
Here is a retard proof way to achieve what you want.
Crate two tables, Category and Entry. The Entry table references the Category table.
You do not store a counter for the categories. Use the count function.
You also do not store an index for the entries. Simply include an auto incrementing int in the Entry table. To get the index of an Entry filter the entries by the category, sort by the auto incrementing int, then use the row_number function.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.794
>>788
Except he didn't even receive unhelpful replies. The first is to look into transactions to solve the problem of interleaving updates. The second was a suggestion to use count, because his idea is obviously wrong.
>>762
There is a Kenyan man who has been trying to build his home made airplane for over a decade. He doesn't understand any of the fundamentals, so despite his considerable efforts, his contraption is never going to fly. This Kenyan man is like you.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.796
>>788
I agree, there's no point using such a shitty database that can't process it's own syntax nor tell me which part of it is wrong.
>>789
>You do not store a counter for the categories
Aaand you fail. Do me a favor and fuck off as well.
>>794
>use count
>his idea is obviously wrong
Feel free to join the anon above.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.798
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.806
>>549
>I don't know how to inner join
L M A O
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.808
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
No.810
>>796
Problem exists between computer and chair, in other words ERROR ID:107
Do the world a favour and off yourself.
Disclaimer: this post and the subject matter and contents thereof - text, media, or otherwise - do not necessarily reflect the views of the 8kun administration.
