That's some really bad process. Whoever thought that was a good idea was much more at fault.

It was a startup with 2 developers, including myself. I think I was 18 or 19, this would have been the very late 90s. It was "my" process. Although source control existed at that time, it was not very common. Also, we had no product, so it wasn't like I brought down a "production" site or something, we were at the very beginning stages of the thing.

I'm writing a Java program that stores data objects in a (SQL) database, and each of these objects has a unique ID, stored as PK in a table in the DB

How would you guys recommend doing this such that whenever a new object gets created, it definitely gets handed an unused ID, and can thus be created and written into the db?

I would typically leave the ID unset until the object is saved, and let the database do it (via automated primary key generated, or a sequence)

If you really must have the ID at the time the object is created (but before it's saved), you could manually fetch a sequence value.

If that's not feasible then you're down to somewhat probabilistic approaches. Like maybe I'd generate an in memory sequence of unique random numbers and hand them out to new objects as needed. I would treat this as a temporary ID and replace when it was saved to the database

As a way to retrieve the minimum unused id from the DB itself, I'm tempted to use a window function lead() if your DB supports them
I'm a total noob and haven't tested the query, though. I should learn to test any code that I publish.

The same certainly can be done using EXISTS, but that way bores me and I'm not sure which is faster.

That's a really bad idea. This is literally what sequences are for. Imagine the following

1. I select minimum id from database, and increment it by 1
2. someone else selects minimum id from database, and increments it by 1
(we both now have the same value)
3. I save my object
4. he (tries) to save his object (it will fail, or produce a duplicate key, depending on how your table is configured)

In comparison, with a sequence, whenever you select from it, it increments *itself*, so whenever you select from it, you get a guaranteed unique id.

Thanks for the tip, I'm not used to the situation when a DB has many users, so of course I'm not knowledgeable about such pitfalls yet

oops meant to post this in the other thread (homework)

but thanks, I'll look into the idea of having the ID unset until object is written to db. Seems like a good idea and I don't see why it shouldn't work in my case

I'm using SQLite btw, and just asked my TA about this, he wasn't sure if SQLite supports this kind of automated key generation (apparently SQLite is built for speed )
He suggested having the DB do it automatically, or if not possible, have a counter that reads the last line from the DB and assigns next possible id. I think that could cause the kind of problem RustyBrooks just mentioned though. Maybe I will have to change DB's if SQLite doesn't support this automation

should just be able to use a serial statement in the SQL file. then whenever a new object is inserted into the database, it automatically gets the next number in line.

you wouldnt need any Java code I dont think.

I have only used postgres though.

It varies from database to database, but in sqlite, if you have a primary key field and insert a NULL into it, it'll autogenerate a key (and in my case using python/sqlite3, the "lastrowid" element contains the ID of the new row)

Also I believe in sqlite's *specific* case, you can not have more than one person accessing the database. So in theory you can select max(id) + 1 and use it, but I don't particularly recomment it.

Still, SQLite has autoincrement, which is however not recommended (because of performance issues) except when it's really necessary.

SQLite automatically assigns ROWID (a row number) during insertion which is almost always the maximum currently used id plus one.

And there's actually no big problem with assigning max(id)+1 because it doesn't take much space to store a 6-8-byte id (SQLite stores an 8-byte ROWID anyway), just my aesthetic taste as a mathematician required that I have a set of ids that follow one another with few gaps and so I wanted to make sure that the gaps (caused by frequent deletion of rows) are filled in during inserts, with my above elaborate queries

I just had a great meeting with some of the groups I’ll be working with in my new architect role. They’re actually enthusiastic about learning node and are desperate to pick my brain - much better than the reluctant/skeptical groups before.

okay thanks guys,

indeed inserting NULL into the id field generates a key. So right now I'm creating an object with id = 0, and then writing it into the database except that instead of writing the objects id I write NULL into the database. For the record this method assigns the maximum current id + 1, regardless of whether any smaller id's have been freed up. so it doesn't "reuse" id's

It feels improper to create an object with id = 0 and then immediately write it into the database with id as NULL. I'm only creating the object to be able to use it's parameters to build the sql query that saves it, it never actually gets used unless it's read from the database first. So I'm thinking I shouldn't create the object first, but rather just write what would be it's parameters immediately into the database.

Oh cool, these are actual product teams that you'll be working to guide from the standpoint of technology choice and architecture basically?

Belated congrats to suzzer99 on the new role and on the big demand for his brain

I understand why Americans quit poker but I don't get why switching to a real job has seemingly become fashionable among ROW grinders as well. Or at least it seems harder to me to earn the same hourly (say, $25) by programming as by playing ROW online poker. Or maybe I'm too wary about the programming route (exclusively freelance, for lack of better local options), having never dabbled in it. Thoughts?

See b. jones recent link to a blog on poker and programming in the bootcamp thread

Well, maybe the difference is that I care mainly about the short term, when the threat of competent bots is not yet big.

I'm looking rather for the easiest way to earn $50K lifetime (and downshift afterwards) than the easiest way to earn $2M lifetime and spend most of it on things with little subjective value.

^^ totally opposite experience for me. Programminmg >>> poker for money, stress level, and even hours. I'm probably an outlier though.

Funnily enough, there's so little pressure of money on me that I wonder why poker stresses me at all. I guess that the main sources of stress for me are the urgency (the 'requirement' to act fast, once in 3-5 seconds due to multitabling) and the frequency of making mistakes (I mean mistakes in terms of Galfond bucks, not the card distribution). But in programming, mistakes are often even more costly, and they're made all the time too (debugging is a big part of the whole process).

My self-esteem is so low, e.g. I've just tried watching a video on how to create a login form in C# in Visual Studio and it looks easy, and it is indeed easy for most people, but I have a fear of something going fundamentally wrong and me having to spend hours to find an elusive bug.

I've only been at this programming stuff a couple months and, imo, the bolded is what it's all about.

@iamdevloper

slow or not, using lastrow_id whatever they wrote on the metal is going to be faster than whatever hand-rolled solution you came up with.

Also, trying to keep your incremented IDs clean is bad business. I won't get into why here, but it's a terrible practice and one that may cause serious issues later on.

I think so. They have some node projects they're trying to get off the ground. It's the big customer facing site that is dragging their feet.

This. I literally pounded my desk and yelled dozens of time every session playing SNGs. I even got a note from the neighbors after one particularly bad extended obscenity outburst.

I do not have the emotional makeup for poker as a living.

I bet you regret all those smashed mx revolutions now