April foolishness

So, I've been working on Unit Tests in my schema. I have copied over some test data and built out the structure and written some code to setup and tear down as I have discussed previously. Well, as often happens at work, I had some shifting priorities and the Unit Tests fell to the back burner for a bit. Well, I was able to get back to them in full force today. The problem I ran into after several hours of writing code, running tests, etc. is that I had totally jacked up a couple of sequences. Sequences are number generators used for primary keys in tables. If your table is TAB and your primary key is TAB_ID then usually you will have a sequence called either SEQ_TAB_ID or TAB_ID_SEQ depending on your naming convention. My Unit Tests use the same method that the App does to enter records. In this case it is a shit ton of stored procedures. Almost every table, but not every table because that would be fucking stupid, has a procedure to insert a row and another procedure to delete a row. I imagine that the App does not use the delete procedures much or at all. But, I sure can use them to clean up after myself. Curtis knows what I'm talking about. Lulz.

Anyway, since this is all in my schema and I pulled some real test data, but not all and recreated the objects and what not, I did a silly thing. Now a normal well adjusted developer might have come up with a cohesive plan to do this, but I was feeling frisky and decided to hit the ground running so to speak. I jumped right in there and pulled over the data, then I manually created a couple of the sequences that I need for the testing. Here is the appropriate way to create the above mentioned sequence in a pristine environment:
CREATE SEQUENCE SEQ_TAB_ID START WITH 1 INCREMENT BY 1;
There are some other options but nobody gives a fuck about them so I'm moving on. These two are a must. You tell the sequence its first value and you tell it how many to add to the first value to get the the next value. This was a crucial mistake. What I should have done was find out the max id in the data that I had pulled over and given the sequences an incremented value of that to start with. So, let's say that the maximum value of my TAB_ID  is 698226. I may have pulled over the last month's worth of test transactions to play with or something like that. But, my sequence started at 1. So, the first few tests worked because those values were available in the table. (A big aside here: we have talked about this in the past, I believe and you may already know this but, a primary key in a table if it has a primary key constraint must be unique. So, what happened was I pulled over records starting at 1000 to 600,000 say and I set my sequence to start at 1. As soon as the sequence has been run 999 times, I'm going to have a bad time. That is what happened. I began to get unique constraint errors. Well, I figured out my mistake but I again failed to act appropriately. I'm not sure if I'm just a big giant fucktard or if I've been around them too much but either way, I need to step my fucking game up. If sequences were blocking a shot, I'd be that Ware kid.

An appropriate action at this point would have been to drop the existing sequence and recreate it as I should have done in the beginning. Ah, fuck that. I'm a bro-grammer, right? I do this shit my way. I'm from the streets, bitch. Fuck you and your 'industry standards'. I'll have none of it. I'm keeping it real. My solution might be a legitimate move if you are working in a database that might be getting live action. You know, testing in MOTHERFUCKING PROD as we so often do. Well, I bumped up the increment by to 13000 and I selected some motherfucking nextvals. Soon, enough I got to where I needed to be. And all is right with the world. Got my mind on my DB and my DB on my mind, yo. Not really, I had getting the fuck up out of here on my mind. I had shit to do. I bolted and left it the way it was. That's right...Left. It. The. Way. It. Was. So, the next day, I didn't even get logged in before my priorities changed yet again. Shit breaks. Other shit comes up from time to time and the shit just gets stirred around. It's like a big shitty pot of shitty spaghetti sauce that you have stir in a shitty manner with your shitty little wooden spoon every few shitty minutes. It's - well, it's shitty, I guess, is what I'm trying to say. I worked unhappily on my new priority for a week or more. Shit didn't go well. (Is anyone else sensing a theme here?) I eventually got that priority as far along as I could get it and got back to this one again. So, today, I'm testing merrily away. I'm like the little Unit Tester that could. I got this. Sadly, no. I got shit. (There it is again - it's like Bad Wolf but shittier.) I ran several tests and  wrote some code and ran some tests and wrote some code. Well, I eventually added a new insert and when I tested that one, it shit all over the place. TOAD was like the bathroom in Trainspotting. Ick. After a bit more than a bit of investigation, I'd say it was a squared bit, I discovered that my recently added table had the TAB_ID declared as a NUMBER(9,0); which means the largest available TAB_ID could be 999999999 whereas my main TAB table had the TAB_ID declared as NUMBER; which means there is no upper bound (technically there is but let's not get all tangential) so I had apparently been testing this thing like a MOFO because my MAX(TAB_ID) was 389548277499. My sequence had been adding 13000 to the id every time a test was ran. Did I mention that I test several records during each test. Why, that is a test within a test. Yo Dawg. I heard you like tests...It is TESTCEPTION. Every test I ran ended up inflating the TAB_ID by 65000.

MFW I realized what I had done. I chuckled a little. If I didn't laugh at myself I'd be all emo and that wouldn't work out well at all. I have no hair to die jet black, can't afford a complete wardrobe overhaul, and if I'm going to be cutting anybody it is going to be other people because fuck them, amiright? I have found that other people are three of the top three things wrong with the world today. Snooki, Mindy Kaling, and Dianne Feinstein are all other people. Thanks, Obama.

Well, I hope my pain may have served to amuse and perhaps enlighten you all a little bit. And to be honest, sometimes off the beaten path is where you want to go. You might find a change of perspective beneficial. Of course, you might find a ton of shit. You know what, I just realized why the nuns at my grade school were such ball-busting bitches. Because the painful lessons are the ones that teach you the most. Of course, those dumb cunts can't make every lesson the most painful of your life and reasonably expect it to work. If all you know is pain, it loses its meaning, its poignancy and its teaching ability.There has to be a little downtime from the punishment. You can only taint-punch a hooker so much before she gets all mouthy...with the 'when you untie me, I'm going to fuck you up', or 'please don't kill me', or my favorite 'you've just lost your family discount cuz'. But, I digress. Again. I digress all over the place. I even got some digress on the curtains once.  That was a trip. Literally. I actually tripped over the roll of duct tape, or maybe it was the shovel handle. Either way, I fell into the motel curtains mid digression and ended up with a mild abrasion on the People's Digressor.

Next time or at least coming soon, I'll cover some basic Oracle functions to make life easier. Apparently, I'm a lousy friend and kind of a dick for not doing that sooner. The REPLACE function would have been useful knowledge for someone.