Wednesday, April 30, 2014
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.
Thursday, February 6, 2014
Yo Dawg, I heard you like writing SQL & Goddamnit, that didn't go as planned.
Today was a roller coaster of a day. I had an interesting success and a stupid yet interesting rookie mistake. It started off my needing to make a change to someone else's script that I have to run during the next deploy.
Interesting Success
As a little background here, we are deploying changes to one of our multitenant applications that relies on one of our old silo'd applications. So we would be generating a few hundred update statements from one database and then applying them to several different databases. So, I would have to either gain access to each of these individual databases and parse out the output of the script and run only the applicable statements OR manually edit the results to add the appropriate schema designation to the resulting scripts, which is tedious and prone to error. Or, I could figure out a way to fix the statement at the time of generation.
Most of my SQL Writing SQL efforts rely upon the data dictionary and its massive library of tables and views to make magic happen. The script that I was given needed to be run in a somewhat alien application but have the resulting SQL ran in several instances of one of the applications I am intimately familiar with (assuming you consider dry anal rape being intimately familiar).
The problem was that I was expecting to be able to pull the OWNER from the data dictionary ALL_TABLES view or some such thing but the script that I was given was just using Application data tables and was not relying on the data dictionary. This threw a wrench into my plans and the heat was getting turned up also because the deploy is this Sunday at the un-FSM-ly hour of 5 AM - did I mention the dry anal rape, yeah. So, in a panic I started investigating and gaining familiarity with the quasi-alien database. Running some select statements and looking very closely at the ones that were being used in the script I was given. The gentleman that provided the script was nice enough to add in a comment to break up the statements into groups based on their location such as --Portal1. However, I need the schema designation for Portal1 not the words Portal1. So, during my investigation I found that the table that holds the Portal Name Portal1, etc. also holds a URL for the API. Sure enough, the schema designation was mostly included in the URL.
For example: Portal1's URL would be http://foo-p1.xyz.com/blah/blah/woof/woof... and the schema designation would be foop1. So, I needed to parse the URL and pull out foo-p1...foo-pn, n times. I combined the REPLACE, SUBSTR and INSTR functions to do my work. The resulting column in the select statement turned out to be something like this:
replace( substr(portal_table.portal_url, 9, instr(portal_table.portal_url, '.') -9), '-') AS schema_designation
I was then able to modify the output to include || schema_designation || '.' || right before each table name is specified in the output. For example:
dbms_output('update ' || schema_designation ||'.foo set bar = 1;');
so the resulting statement becomes
update foop1.foo set bar = 1;
which can be executed from any schema on the same SID or Service Name as foop1 as long as the appropriate permissions are in place. This worked out really well and saved me a bunch of terrible data entry or logging into too many schemas to run the scripts.
Stupid but Interesting Rookie Mistake
So, did you know that you can join to the same table twice in a single statement? So, let's say we have a simple employee table called emp that has 3 columns: id, name, manager_id. Manager_id refers to the ID column of the emp record for that employee's manager. Now, lets say we want to run a report that lists each employee and their manager. We would have to join to emp twice, right? It would look something like this:
select employee.name as employee, manager.name as manager
from emp employee
join emp manager on employee.manager_id = manager.id;
If our table has data such as:
ID NAME MANAGER_ID
1 Tom NULL
2 Dick 1
3 Harry 2
4 Peter 2
5 Paul 3
6 Mary 4
The results would look something like:
EMPLOYEE MANAGER
Dick Tom
Harry Dick
Peter Dick
Paul Harry
Mary Peter
So, that is a good simple example of how to join to the same table twice. Interesting huh?
Did you know that you can join to the same table twice in different ways while aliasing the table the same way? Neither did I. Why would you ever want to do that? It just sounds bad.
So, anyway, I was recently tasked with fixing a report. It was joining to the table foo on Bar_id and it should have joined on Blah_id. Also, blah should have joined to bar and not woof. So, the statement looked like
select * from woof
join bar on woof.woof_id = bar.woof_id
join foo on foo.bar_id = bar.bar_id
join blah on woof.woof_id = blah.woof_id;
What it needed to look like was:
select * from woof
join bar on woof.woof_id = bar.woof_id
join blah on woof.bar_id = blah.bar_id;
join foo on foo.blah_id = blah.blah_id;
What I accidentally wrote was:
select * from woof
join bar on woof.woof_id = bar.woof_id
join foo foo on foo.bar_id = bar.bar_id
join blah on woof.bar_id = blah.bar_id;
join foo foo on foo.blah_id = blah.blah_id;
So, notice what I did there? It is pretty easy to spot in this simple example but in the convoluted query I was writing, it was a little harder to spot. So, I kept the bad join and added the good join. The results were subtly wrong. It just started showing twice the records that it should have. When you run for a large time frame, you get a healthy set of data back. It is easy to not notice that there are dupes in there. I mean, the bad join flew in production since the dawn of time, or at least since the report was initially built.
I screwed the pooch on this one. The deploy is happening Sunday so either broken stuff is getting pulled out of the deploy or broken stuff is getting deployed and we have to fix it later. Either way, feelsbadman.jpg.
I now, have to come up with a method to trap for this insanity in a Unit Test. The weird thing is that if a record is doubled, its values would all be legit values so, it passed my current test. The QA people assigned to test this were a bit inexperienced so the bug almost made all the way in unnoticed. The real kicker is that the only reason I found it was because some user was supposed to be testing this in UAT but was testing in production so said it wasn't fixed and pushed back. I was so happy when I found out that she was testing in prod that I started to gloat for a second, but then I noticed the output of the report I ran trying to recreate her results. Oh noes!
The good news is that the fix is super quick. The testing will be a little more involved from both the Unit Test perspective and from the QA perspective.
So, I guess it was a push today? My awesomeness is perfectly equaled by my derpity derp. And that concludes this installment. I just realized I didn't say fuck or cunt very much. I wonder if I'm coming down with something? Laters.
Interesting Success
As a little background here, we are deploying changes to one of our multitenant applications that relies on one of our old silo'd applications. So we would be generating a few hundred update statements from one database and then applying them to several different databases. So, I would have to either gain access to each of these individual databases and parse out the output of the script and run only the applicable statements OR manually edit the results to add the appropriate schema designation to the resulting scripts, which is tedious and prone to error. Or, I could figure out a way to fix the statement at the time of generation.
Most of my SQL Writing SQL efforts rely upon the data dictionary and its massive library of tables and views to make magic happen. The script that I was given needed to be run in a somewhat alien application but have the resulting SQL ran in several instances of one of the applications I am intimately familiar with (assuming you consider dry anal rape being intimately familiar).
The problem was that I was expecting to be able to pull the OWNER from the data dictionary ALL_TABLES view or some such thing but the script that I was given was just using Application data tables and was not relying on the data dictionary. This threw a wrench into my plans and the heat was getting turned up also because the deploy is this Sunday at the un-FSM-ly hour of 5 AM - did I mention the dry anal rape, yeah. So, in a panic I started investigating and gaining familiarity with the quasi-alien database. Running some select statements and looking very closely at the ones that were being used in the script I was given. The gentleman that provided the script was nice enough to add in a comment to break up the statements into groups based on their location such as --Portal1. However, I need the schema designation for Portal1 not the words Portal1. So, during my investigation I found that the table that holds the Portal Name Portal1, etc. also holds a URL for the API. Sure enough, the schema designation was mostly included in the URL.
For example: Portal1's URL would be http://foo-p1.xyz.com/blah/blah/woof/woof... and the schema designation would be foop1. So, I needed to parse the URL and pull out foo-p1...foo-pn, n times. I combined the REPLACE, SUBSTR and INSTR functions to do my work. The resulting column in the select statement turned out to be something like this:
replace( substr(portal_table.portal_url, 9, instr(portal_table.portal_url, '.') -9), '-') AS schema_designation
I was then able to modify the output to include || schema_designation || '.' || right before each table name is specified in the output. For example:
dbms_output('update ' || schema_designation ||'.foo set bar = 1;');
so the resulting statement becomes
update foop1.foo set bar = 1;
which can be executed from any schema on the same SID or Service Name as foop1 as long as the appropriate permissions are in place. This worked out really well and saved me a bunch of terrible data entry or logging into too many schemas to run the scripts.
Stupid but Interesting Rookie Mistake
So, did you know that you can join to the same table twice in a single statement? So, let's say we have a simple employee table called emp that has 3 columns: id, name, manager_id. Manager_id refers to the ID column of the emp record for that employee's manager. Now, lets say we want to run a report that lists each employee and their manager. We would have to join to emp twice, right? It would look something like this:
select employee.name as employee, manager.name as manager
from emp employee
join emp manager on employee.manager_id = manager.id;
If our table has data such as:
ID NAME MANAGER_ID
1 Tom NULL
2 Dick 1
3 Harry 2
4 Peter 2
5 Paul 3
6 Mary 4
The results would look something like:
EMPLOYEE MANAGER
Dick Tom
Harry Dick
Peter Dick
Paul Harry
Mary Peter
So, that is a good simple example of how to join to the same table twice. Interesting huh?
Did you know that you can join to the same table twice in different ways while aliasing the table the same way? Neither did I. Why would you ever want to do that? It just sounds bad.
So, anyway, I was recently tasked with fixing a report. It was joining to the table foo on Bar_id and it should have joined on Blah_id. Also, blah should have joined to bar and not woof. So, the statement looked like
select * from woof
join bar on woof.woof_id = bar.woof_id
join foo on foo.bar_id = bar.bar_id
join blah on woof.woof_id = blah.woof_id;
What it needed to look like was:
select * from woof
join bar on woof.woof_id = bar.woof_id
join blah on woof.bar_id = blah.bar_id;
join foo on foo.blah_id = blah.blah_id;
What I accidentally wrote was:
select * from woof
join bar on woof.woof_id = bar.woof_id
join foo foo on foo.bar_id = bar.bar_id
join blah on woof.bar_id = blah.bar_id;
join foo foo on foo.blah_id = blah.blah_id;
So, notice what I did there? It is pretty easy to spot in this simple example but in the convoluted query I was writing, it was a little harder to spot. So, I kept the bad join and added the good join. The results were subtly wrong. It just started showing twice the records that it should have. When you run for a large time frame, you get a healthy set of data back. It is easy to not notice that there are dupes in there. I mean, the bad join flew in production since the dawn of time, or at least since the report was initially built.
I screwed the pooch on this one. The deploy is happening Sunday so either broken stuff is getting pulled out of the deploy or broken stuff is getting deployed and we have to fix it later. Either way, feelsbadman.jpg.
I now, have to come up with a method to trap for this insanity in a Unit Test. The weird thing is that if a record is doubled, its values would all be legit values so, it passed my current test. The QA people assigned to test this were a bit inexperienced so the bug almost made all the way in unnoticed. The real kicker is that the only reason I found it was because some user was supposed to be testing this in UAT but was testing in production so said it wasn't fixed and pushed back. I was so happy when I found out that she was testing in prod that I started to gloat for a second, but then I noticed the output of the report I ran trying to recreate her results. Oh noes!
The good news is that the fix is super quick. The testing will be a little more involved from both the Unit Test perspective and from the QA perspective.
So, I guess it was a push today? My awesomeness is perfectly equaled by my derpity derp. And that concludes this installment. I just realized I didn't say fuck or cunt very much. I wonder if I'm coming down with something? Laters.
Sunday, December 15, 2013
Do your Due Diligence
Warning: I was feeling super stabby when I was writing this. If you have delicate sensibilities enter at your own risk. As Corey Taylor says: if I offended you, you needed it.
I might have posted on this topic once before or perhaps touched on it while covering another topic. I normally stick to SQL and PL/SQL goodness but I'm fucking pissed and I need to vent. I usually only post when I do something wicked awesome or some asshat pisses me off. What is the term for a collection of asshats? A Bing? A Sharepoint of Asshats, perhaps? Hmm. A douche maybe? Asshats are like Frat boys in that they run together in packs and they delight in ruining other people's days. Sometimes developers, analysts, project managers or other work teams do the same thing. Perhaps they don't realize they are being asshats and perhaps they even think they are being helpful or working hard. That is usually not the case.
Virtually no one has 100% code coverage in their due diligence efforts. It if fucking difficult to stay vigilant in this. You see the signs for a certain condition that you've seen a dozen times before so you jump to the conclusion and don't fully investigate the issue. Snap judgments are fucking awful. They are almost never correct. They are often wildly off the mark. Look, I get it. You've answered tickets or investigate issue for 10 weeks or 10 years and you know what you are doing. Sure. We all feel that way some of the time. But calm the fuck down. If everyone you rush to talk to looks like they just stepped in a big pile of dog shit, slow your motherfucking roll. You are a problem. You are not adding value. You are derailing someones efforts on something. Every little interruption is a loss of several minutes trying to get back into the zone or trying to remember exactly where you were before someone tapped on your wall or your shoulder.
Interruptions are a necessary evil and they are bound to happen to a certain degree. But make EVERY EFFORT to limit the times that you bring bullshit to someone. If you know how to fucking do it and you have the ability to fucking do it and you have the access to fucking do it, FUCKING DO IT YOURSELF! WTF? If you think something is wrong, how many times did you check it? Are you fucking sure? Are you confident enough to go to the CEO or the VP and tell them your thoughts? If not, then double or triple check it yourself. And then, send someone else an email or an IM that they can get to in their own time. Not every goddamn thing is an urgent thing. Not every goddamn thing has to be done right this motherfucking second.
And this is one of my worst pet peeves. If everything you are working on is a fucking stop the presses all hands on deck kind of thing in your mind, yet every single time someone else needs to interrupt you, you are too busy and it'll be a minute or two, you've got delusions of grandeur or some shit like that. Not everything you fucking do is important. Not every time you feel the need to interrupt someone about something do you really need to do that. But for damn sure, if you do that, you best answer my fucking questions in a timely fucking manner when I come to you with something urgent. Goddammit!
We are (or should be) all trying to add value to the organization, find and resolve issues quickly, and prevent bugs from going into production. Every time you interrupt someone, you are increasing the probability that some setting won't get set right or some task won't get 100% completion or the turnaround on that particular task is going to run long. Be very careful when doing this. And reciprocate. If you tell someone this has to been done right now, tell them fucking why. Not just because you are working on it right now. That's not a good enough reason to derail someone else's productivity. Manage your interactions with people and err on the side of caution.
I've heard managers and people say they have an open door policy. I don't have a door but if I did it would be shut and have a do not disturb sign on it. I like to be left alone so that I can focus and dive deep on things. Some of this shit is really complicated stuff. If I am just about to get to the bottom of some issue and someone taps me on the shoulder or on my wall, it better be fucking important. I like to think that I'm a friendly person and I am almost always willing to help someone but your lack of proper planning doesn't mean your problem is urgent for me. If you have triple checked your issue and have done all you can do, send me an IM or an email and let me know. I take periodic controlled breaks from my work to read email and I usually will read IM when I notice them and calculate the need for immediate response against how close I am to finishing something or getting to a decent break point. Allowing me to manage my interactions and interruptions is crucial to stabilizing my mood and minimizing the errors in my work.
Some of you may be reading this and thinking, "This motherfucker is throwing stones in his glass house!" Well, there may be some truth to that. I don't always succeed in sussing out the next thing to to and I can rely on others a little too heavily sometimes. But most of you who know me personally have seen me stand around quietly until a conversation ends before asking a question and most of you have seen me apologize profusely for the need for the interruption. We all do it sometimes. Most of us endeavor to minimize the need for it and most of us struggle with finding a balance.
You may be wondering why I am so stabby about this right now. Well, I was interrupted a few times the last two weeks because people forgot how the calendar works or some such bullshit. Maybe you need a sheet of paper and maybe you need to jot down notes and think about things a little bit. If you are asking someone how the calendar works, don't try to save face. Own up to the fact that you were dumb or hurried or just didn't think. Honesty is a good thing. I have used that. I told someone this week that if I had more time I could probably have found this thing I was looking up, but I was on the phone with a customer and knew that the people I was bothering could find it quicker and we wouldn't make the customer wait long for me to find it. Maybe that's not cool, but it makes sense to me. There's a reason for my urgency that is something more than that I just feel like I want to get this done and that's enough to make me interrupt someone else's productivity.
Some of you may be thinking, well we are a team and that means I get to interrupt you whenever I want. I say that working in a team means that you need to respect your teammates feelings. Maybe not always putting them above your own, but giving them equal footing. Your teammates will notice your balanced approach and maybe not look like they stepped in dog shit every time you talk to them. If the dog shit face continues, check your shoes, perhaps shower more often or considering changing your soap.
For those of you that need step by step instructions:
1. Check if everything you see is dark and smelly.
2. Remove your head from your ass.
3. Really think about what you are going to ask someone to do for you or to help you with.
4. Do as much of the task yourself that you are able to do.
5. How urgent is it really?
6. If it can wait, approach the teammate via electronic means. Approach directly only if really super urgent.
7. State your perceived urgency and when the deadline is.
8. Don't say ASAP unless it is a prod down situation or a customer waiting on the phone.
9. Remember all of the times you've asked for someone's urgent help.
10. Respond quickly when someone else approaches you with an urgent request.
11. Say thank you!
If you can't figure out which person you work with is the worst offender here, either you work in a utopian environment or you are the biggest asshat at your office. Take immediate steps to stop wearing your own ass as a hat. For fuck's sake, we are all adults here. Act like it. When you fuck up, admit it, say sorry and move on. Excuses are for the weak. But above all else, pull your own weight and treat your teammates decent. This completes my psychotic ramblings for today.
I might have posted on this topic once before or perhaps touched on it while covering another topic. I normally stick to SQL and PL/SQL goodness but I'm fucking pissed and I need to vent. I usually only post when I do something wicked awesome or some asshat pisses me off. What is the term for a collection of asshats? A Bing? A Sharepoint of Asshats, perhaps? Hmm. A douche maybe? Asshats are like Frat boys in that they run together in packs and they delight in ruining other people's days. Sometimes developers, analysts, project managers or other work teams do the same thing. Perhaps they don't realize they are being asshats and perhaps they even think they are being helpful or working hard. That is usually not the case.
Virtually no one has 100% code coverage in their due diligence efforts. It if fucking difficult to stay vigilant in this. You see the signs for a certain condition that you've seen a dozen times before so you jump to the conclusion and don't fully investigate the issue. Snap judgments are fucking awful. They are almost never correct. They are often wildly off the mark. Look, I get it. You've answered tickets or investigate issue for 10 weeks or 10 years and you know what you are doing. Sure. We all feel that way some of the time. But calm the fuck down. If everyone you rush to talk to looks like they just stepped in a big pile of dog shit, slow your motherfucking roll. You are a problem. You are not adding value. You are derailing someones efforts on something. Every little interruption is a loss of several minutes trying to get back into the zone or trying to remember exactly where you were before someone tapped on your wall or your shoulder.
Interruptions are a necessary evil and they are bound to happen to a certain degree. But make EVERY EFFORT to limit the times that you bring bullshit to someone. If you know how to fucking do it and you have the ability to fucking do it and you have the access to fucking do it, FUCKING DO IT YOURSELF! WTF? If you think something is wrong, how many times did you check it? Are you fucking sure? Are you confident enough to go to the CEO or the VP and tell them your thoughts? If not, then double or triple check it yourself. And then, send someone else an email or an IM that they can get to in their own time. Not every goddamn thing is an urgent thing. Not every goddamn thing has to be done right this motherfucking second.
And this is one of my worst pet peeves. If everything you are working on is a fucking stop the presses all hands on deck kind of thing in your mind, yet every single time someone else needs to interrupt you, you are too busy and it'll be a minute or two, you've got delusions of grandeur or some shit like that. Not everything you fucking do is important. Not every time you feel the need to interrupt someone about something do you really need to do that. But for damn sure, if you do that, you best answer my fucking questions in a timely fucking manner when I come to you with something urgent. Goddammit!
We are (or should be) all trying to add value to the organization, find and resolve issues quickly, and prevent bugs from going into production. Every time you interrupt someone, you are increasing the probability that some setting won't get set right or some task won't get 100% completion or the turnaround on that particular task is going to run long. Be very careful when doing this. And reciprocate. If you tell someone this has to been done right now, tell them fucking why. Not just because you are working on it right now. That's not a good enough reason to derail someone else's productivity. Manage your interactions with people and err on the side of caution.
I've heard managers and people say they have an open door policy. I don't have a door but if I did it would be shut and have a do not disturb sign on it. I like to be left alone so that I can focus and dive deep on things. Some of this shit is really complicated stuff. If I am just about to get to the bottom of some issue and someone taps me on the shoulder or on my wall, it better be fucking important. I like to think that I'm a friendly person and I am almost always willing to help someone but your lack of proper planning doesn't mean your problem is urgent for me. If you have triple checked your issue and have done all you can do, send me an IM or an email and let me know. I take periodic controlled breaks from my work to read email and I usually will read IM when I notice them and calculate the need for immediate response against how close I am to finishing something or getting to a decent break point. Allowing me to manage my interactions and interruptions is crucial to stabilizing my mood and minimizing the errors in my work.
Some of you may be reading this and thinking, "This motherfucker is throwing stones in his glass house!" Well, there may be some truth to that. I don't always succeed in sussing out the next thing to to and I can rely on others a little too heavily sometimes. But most of you who know me personally have seen me stand around quietly until a conversation ends before asking a question and most of you have seen me apologize profusely for the need for the interruption. We all do it sometimes. Most of us endeavor to minimize the need for it and most of us struggle with finding a balance.
You may be wondering why I am so stabby about this right now. Well, I was interrupted a few times the last two weeks because people forgot how the calendar works or some such bullshit. Maybe you need a sheet of paper and maybe you need to jot down notes and think about things a little bit. If you are asking someone how the calendar works, don't try to save face. Own up to the fact that you were dumb or hurried or just didn't think. Honesty is a good thing. I have used that. I told someone this week that if I had more time I could probably have found this thing I was looking up, but I was on the phone with a customer and knew that the people I was bothering could find it quicker and we wouldn't make the customer wait long for me to find it. Maybe that's not cool, but it makes sense to me. There's a reason for my urgency that is something more than that I just feel like I want to get this done and that's enough to make me interrupt someone else's productivity.
Some of you may be thinking, well we are a team and that means I get to interrupt you whenever I want. I say that working in a team means that you need to respect your teammates feelings. Maybe not always putting them above your own, but giving them equal footing. Your teammates will notice your balanced approach and maybe not look like they stepped in dog shit every time you talk to them. If the dog shit face continues, check your shoes, perhaps shower more often or considering changing your soap.
For those of you that need step by step instructions:
1. Check if everything you see is dark and smelly.
2. Remove your head from your ass.
3. Really think about what you are going to ask someone to do for you or to help you with.
4. Do as much of the task yourself that you are able to do.
5. How urgent is it really?
6. If it can wait, approach the teammate via electronic means. Approach directly only if really super urgent.
7. State your perceived urgency and when the deadline is.
8. Don't say ASAP unless it is a prod down situation or a customer waiting on the phone.
9. Remember all of the times you've asked for someone's urgent help.
10. Respond quickly when someone else approaches you with an urgent request.
11. Say thank you!
If you can't figure out which person you work with is the worst offender here, either you work in a utopian environment or you are the biggest asshat at your office. Take immediate steps to stop wearing your own ass as a hat. For fuck's sake, we are all adults here. Act like it. When you fuck up, admit it, say sorry and move on. Excuses are for the weak. But above all else, pull your own weight and treat your teammates decent. This completes my psychotic ramblings for today.
Friday, September 6, 2013
Interesting behavior of the column default mechanism
This isn't going to blow anyone's socks off or stun anyone like a taser to the face, but this bit me in the ass this week so I am sharing while it is fresh in my mind and to hopefully save someone some pain in the future.
Oracle provides functionality to put in a default value in a column. This week, we had an issue where a partner was passing some information but missing a field that is relatively unimportant but a downstream process was depending on. A brilliant solution would be using the oracle column default mechanism...or so I thought. Let's say the Table involved is ADDRESS and the column is COUNTRY_CODE. Some new downstream process expects every row to have a country code filled in, but the table allows nulls in the column. Well, you can still use default. Here is how you configure a default.
alter table address modify (country_code default 'US');
Pretty straightforward really. It doesn't matter if the column is nullable or not. It works either way. Well, depending on how you access the table. And there lies the rub this time. You can insert a row and/or update a row and the mechanism works but you can insert a row and/or update a row and the mechanism does not work, depending on the syntax of the statement.
When inserting a row, one can specify the column list before the values statement to specify a subset of the overall table's column list or provide the values in a different order than how they are specified in the table. You do this such as:
insert into address( address_id, addr, addr2, city, state, zip, country_code) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210', null);
Or
insert into address( address_id, addr, addr2, city, state, zip) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210');
The kicker is that the first insert will not utilize the default mechanism while the second one will. In plainer terms, if your insert or update specifies a column with a default value on it, the default value will not get populated.
Similarly,
update address set addr2 = 'Apt C', country_code = null where address_id = 1;
...allows the null value to enter the table in the country_code column. But,
update address set addr2 = 'Apt C' where address_id = 1;
...would set the country_code to 'US' if it was previously null on the record.
If this behaved in a different manner, a tiny database script could have prevented changing application code and freed up application developers to work on other bugs and features. However, because the application code interacts with the table by including the country_code column in the insert and update whether or not there is a value specified for them, we cannot use the default mechanism.
I only had a little bit of egg on my face and frustration this week, after suggesting the default column value mechanism as a solution and subsequently finding out its application is dependent on the column list in the statement. If your application code is intelligent enough to dynamically list the columns being inserted or changed or your database activity is controlled by stored procedures so that the application would call I_ADDRESS or ADD_ADDRESS procedures to insert a record, you could dynamically choose your column list depending on the values passed to the procedure and utilize the default value mechanism.
Well, that is a short and sweet little post this week. I guess they can't all be earth shattering and brilliant. Use the default mechanism with care and it will help you.
Oracle provides functionality to put in a default value in a column. This week, we had an issue where a partner was passing some information but missing a field that is relatively unimportant but a downstream process was depending on. A brilliant solution would be using the oracle column default mechanism...or so I thought. Let's say the Table involved is ADDRESS and the column is COUNTRY_CODE. Some new downstream process expects every row to have a country code filled in, but the table allows nulls in the column. Well, you can still use default. Here is how you configure a default.
alter table address modify (country_code default 'US');
Pretty straightforward really. It doesn't matter if the column is nullable or not. It works either way. Well, depending on how you access the table. And there lies the rub this time. You can insert a row and/or update a row and the mechanism works but you can insert a row and/or update a row and the mechanism does not work, depending on the syntax of the statement.
When inserting a row, one can specify the column list before the values statement to specify a subset of the overall table's column list or provide the values in a different order than how they are specified in the table. You do this such as:
insert into address( address_id, addr, addr2, city, state, zip, country_code) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210', null);
Or
insert into address( address_id, addr, addr2, city, state, zip) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210');
The kicker is that the first insert will not utilize the default mechanism while the second one will. In plainer terms, if your insert or update specifies a column with a default value on it, the default value will not get populated.
Similarly,
update address set addr2 = 'Apt C', country_code = null where address_id = 1;
...allows the null value to enter the table in the country_code column. But,
update address set addr2 = 'Apt C' where address_id = 1;
...would set the country_code to 'US' if it was previously null on the record.
If this behaved in a different manner, a tiny database script could have prevented changing application code and freed up application developers to work on other bugs and features. However, because the application code interacts with the table by including the country_code column in the insert and update whether or not there is a value specified for them, we cannot use the default mechanism.
I only had a little bit of egg on my face and frustration this week, after suggesting the default column value mechanism as a solution and subsequently finding out its application is dependent on the column list in the statement. If your application code is intelligent enough to dynamically list the columns being inserted or changed or your database activity is controlled by stored procedures so that the application would call I_ADDRESS or ADD_ADDRESS procedures to insert a record, you could dynamically choose your column list depending on the values passed to the procedure and utilize the default value mechanism.
Well, that is a short and sweet little post this week. I guess they can't all be earth shattering and brilliant. Use the default mechanism with care and it will help you.
Friday, August 23, 2013
More on the Data Dictionary and SQL Writing SQL
It has been quite a while since I posted here.I guess you could say I took the summer off. I have had a lot going on over the last few months both at work and at home. Things seem to be starting to settle back down now. My daughter is back in school and the most of our summer plans have been completed.
So, shall we dig back in? The last few weeks at work I have had the need to use the Data Dictionary in new ways. As we have touched on before, you can use the Data Dictionary to manage large tasks and let the database do some of the work for you. And you can use SQL to write SQL. We will cover SQL Writing SQL first. A basic (and silly) example would be:
SELECT 'insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, '''
|| KEY
|| ''', '''
|| value
|| ''' from target_schema.my_source where description = ''My New Bad Ass my_source'';' mySqlWritingSql
FROM my_table
WHERE my_source_id = 1521;
What the above select statement returns is this:
MYSQLWRITINGSQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key1', 'value1' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key2', 'value2' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key3', 'value3' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key4', 'value4' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key5', 'value5' from target_schema.my_source where description = 'My New Bad Ass my_source';
SELECT 'create table '
|| table_name
|| ' as select * from xyzstage.'
|| table_name
|| ' where 1 = 2;'
it
FROM all_tables
WHERE owner = 'XYZSTAGE'
AND table_name IN (SELECT table_name
FROM all_tables
WHERE owner = 'XYZSTAGE'
MINUS
SELECT table_name FROM user_tables);
create table table1 as select * from xyzstage.table1 where 1 = 2;
create table table2 as select * from xyzstage.table2 where 1 = 2;
create table table3 as select * from xyzstage.table3 where 1 = 2;
create table table4 as select * from xyzstage.table4 where 1 = 2;
create table table5 as select * from xyzstage.table5 where 1 = 2;
I added the where 1 = 2 clause so that I don't migrate data. I really only need the seed data for the look up tables and I will acquire that later. This is just about grabbing the structures that are missing. Now, my schema looks almost exactly like the XYZStage schema. Let's call the one holdout I’ve found so far CONFIG. This table in XYZ has a counterpart in ABC also called CONFIG. However, these were developed in different silos and diverged on column names. The CONFIG table in my schema is the one for ABC. My solution is to append the columns from XYZ onto the one for ABC. I use different Data Dictionary tables for this:
SELECT 'alter table '
|| table_name
|| ' add '
|| column_name
|| ' '
|| data_type
|| DECODE (data_type,
'NUMBER', ';',
'DATE', ';',
'(' || data_length || ');')
it
FROM all_tab_cols
WHERE owner = 'XYZSTAGE'
AND table_name = 'CONFIG'
AND column_name IN
(SELECT column_name
FROM all_tab_cols
WHERE owner = 'XYZSTAGE' AND table_name = 'CONFIG'
MINUS
SELECT column_name
FROM user_tab_cols
WHERE table_name = 'CONFIG');
As we have discussed before, the || is used to concatenate strings and column values and build a SQL statement. The DECODE function is a native SQL function that we have covered before but will revisit briefly. In this case, we look at the value of the DATA_TYPE column for each row and include the DATA_LENGTH information for those that need it. The dictionary views we use are ALL_TAB_COLS and USER_TAB_COLS. These list the column information for tables for the schema owner and for all of the other schemas that the schema owner has access to reach. The results look like this:
alter table CONFIG add CREATE_DATE DATE;
alter table CONFIG add LAST_UPDATE DATE;
alter table CONFIG add LAST_UPDATE_ID NUMBER;
alter table CONFIG add CONFIG_TYPE VARCHAR2(64);
So, shall we dig back in? The last few weeks at work I have had the need to use the Data Dictionary in new ways. As we have touched on before, you can use the Data Dictionary to manage large tasks and let the database do some of the work for you. And you can use SQL to write SQL. We will cover SQL Writing SQL first. A basic (and silly) example would be:
Select ‘select x from dual;’ from
dual;
Your output here would be:
'SELECTXFROMDUAL;'
-------------------
select x from dual;
A
more practical example would be this: let’s say that we have a key/value table with some
data in one schema and we want to put it in a different schema but those
schemas reside on different hosts. Here is our statement:
SELECT 'insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, '''
|| KEY
|| ''', '''
|| value
|| ''' from target_schema.my_source where description = ''My New Bad Ass my_source'';' mySqlWritingSql
FROM my_table
WHERE my_source_id = 1521;
What the above select statement returns is this:
MYSQLWRITINGSQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key1', 'value1' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key2', 'value2' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key3', 'value3' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key4', 'value4' from target_schema.my_source where description = 'My New Bad Ass my_source';
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key5', 'value5' from target_schema.my_source where description = 'My New Bad Ass my_source';
As you can probably tell, the double pipe operator (||) is the concatenate operator in SQL. Each of these returned rows is a syntactically
correct insert statement that we can run on the target host. When writing these for yourself, you can test an
insert and rollback. If you do this and want to test, be sure you are not doing
an autocommit type of transaction. DDL’s are autocommit, DML’s are not. DDL’s
are Create, Alter, Drop, etc. DML’s are Insert, Update, Delete, etc.
Another recent scenario where this type of
statement can help is this: I have been developing Unit Tests for some of our products, however one of them lacks the stored procedures to handle look ups and to insert and remove data since we use a tool for this. At the database level where my tests will be running, I need these so I have been developing a package that contains the add and delete procedures and the functions to look up necessary values. I have also had to modify my schema to match this product's model since I do the unit testing in my own schema. Over the years I have acquired some of the structures that I need but not all. I could have dropped everything and pulled in the whole schema but I thought it would be a fun exercise to pull the delta. This
touches on the Data Dictionary at this point, however, so let me give you some
background on this. You may remember some of this from an earlier post.
The Oracle
Data Dictionary is a set of views owned by SYS that is automatically installed
with Oracle. These are views into the objects that store all of the database
users and all of the objects owned by those users. There are three types of
views and generally each view has a counterpart in the other two types. The
types are:
USER_%
ALL_%
DBA_%
The
distinction between these are: the USER_ views deal only with the objects owned
by the logged on USER so if you are logged in as schema1 the objects in the
USER_% views are all owned by schema1 whereas the ALL_% views deal with ALL of
the objects that the logged on USER has access to see so if schema1 as granted
SELECT to schema2 (a read only user) then when you are logged in as schema2 you
should be able to access some details about schema1 objects from the ALL_%
views; and finally, the DBA_% views are for use by DBA’s and those with higher
levels of access – they show other system information that most users do not
require.
So,
let’s take a look at a real world example. My schema and the project schema are co-located on the host so it makes this a bit easier. I had a portion of - let's call this project XYZ - the XYZ data model in my schema and needed to isolate
the objects I was missing. I wrote the following:
SELECT 'create table '
|| table_name
|| ' as select * from xyzstage.'
|| table_name
|| ' where 1 = 2;'
it
FROM all_tables
WHERE owner = 'XYZSTAGE'
AND table_name IN (SELECT table_name
FROM all_tables
WHERE owner = 'XYZSTAGE'
MINUS
SELECT table_name FROM user_tables);
This
statement uses ALL_TABLES and USER_TABLES. I select the table name from all
tables where owner is xyzstage and subtract the tables that I own. Then I use
sql writing sql to generate the statements to create all of the table objects I
am missing. The results look like this:
create table table1 as select * from xyzstage.table1 where 1 = 2;
create table table2 as select * from xyzstage.table2 where 1 = 2;
create table table3 as select * from xyzstage.table3 where 1 = 2;
create table table4 as select * from xyzstage.table4 where 1 = 2;
create table table5 as select * from xyzstage.table5 where 1 = 2;
I added the where 1 = 2 clause so that I don't migrate data. I really only need the seed data for the look up tables and I will acquire that later. This is just about grabbing the structures that are missing. Now, my schema looks almost exactly like the XYZStage schema. Let's call the one holdout I’ve found so far CONFIG. This table in XYZ has a counterpart in ABC also called CONFIG. However, these were developed in different silos and diverged on column names. The CONFIG table in my schema is the one for ABC. My solution is to append the columns from XYZ onto the one for ABC. I use different Data Dictionary tables for this:
SELECT 'alter table '
|| table_name
|| ' add '
|| column_name
|| ' '
|| data_type
|| DECODE (data_type,
'NUMBER', ';',
'DATE', ';',
'(' || data_length || ');')
it
FROM all_tab_cols
WHERE owner = 'XYZSTAGE'
AND table_name = 'CONFIG'
AND column_name IN
(SELECT column_name
FROM all_tab_cols
WHERE owner = 'XYZSTAGE' AND table_name = 'CONFIG'
MINUS
SELECT column_name
FROM user_tab_cols
WHERE table_name = 'CONFIG');
As we have discussed before, the || is used to concatenate strings and column values and build a SQL statement. The DECODE function is a native SQL function that we have covered before but will revisit briefly. In this case, we look at the value of the DATA_TYPE column for each row and include the DATA_LENGTH information for those that need it. The dictionary views we use are ALL_TAB_COLS and USER_TAB_COLS. These list the column information for tables for the schema owner and for all of the other schemas that the schema owner has access to reach. The results look like this:
alter table CONFIG add CREATE_DATE DATE;
alter table CONFIG add LAST_UPDATE DATE;
alter table CONFIG add LAST_UPDATE_ID NUMBER;
alter table CONFIG add CONFIG_TYPE VARCHAR2(64);
You
might be thinking, if the output of the statements is an executable statement,
can you execute the executable statements in an executable statement executable
sort of executable way? Executably. Here is an anonymous block I wrote to do
that very thing but for a different table. I had already ran the above to alter
the table so I created CONFIG2 based off of the XYZSTAGE schema and ran the below.
Here it is:
DECLARE
CURSOR col_cur
IS
SELECT 'alter table '
|| table_name
|| '2 add '
|| column_name
|| ' '
|| data_type
|| DECODE (data_type,
'NUMBER', ' ',
'DATE', ' ',
'(' || data_length || ')')
it
FROM all_tab_cols
WHERE owner = 'XYZSTAGE'
AND table_name = 'CONFIG'
AND column_name IN
(SELECT column_name
FROM all_tab_cols
WHERE owner = 'XYZSTAGE' AND table_name = 'CONFIG'
MINUS
SELECT column_name
FROM user_tab_cols
WHERE table_name = 'CONFIG2');
BEGIN
FOR fix_config2 IN col_cur
LOOP
EXECUTE IMMEDIATE fix_config2.it;
END LOOP;
END;
The purpose
of this particular solution is to effectively write and execute the fix all at
the same time. The real lesson here is how to wrap the SQL Writing SQL statement in a Cursor in an anonymous block and execute the results in a loop.
The one caveat here is that the semi-colon cannot be in the statement. This has
a few things we haven’t covered yet or perhaps recently.
First,
an anonymous block is like an un-stored and unnamed procedure. If you are just
calling another procedure, you can write an anonymous block like BEGIN myproc();
END; or if you have to declare variables it looks like DECLARE var1 integer := 1;
BEGIN myproc(var1); END; or something like that.
Next, we
have the cursor. Most of us know the cursor as the little blinky thing when
typing but in Oracle a cursor is a pointer to a private SQL area that stores
information about the processing of a DML statement or in this case a DDL
statement. In this case, we are going to access that area via a for loop. The
for loop goes like this: for <row_name> in <cursor_name> loop <do
your stuff> end loop; and in this case the stuff we are doing is executing
the SQL returned for each row via an EXECUTE IMMEDIATE
<row_name>.<cursor column alias>; which uses EXECUTE IMMEDIATE to
implement native dynamic SQL.
All of
this combines to let us do some really powerful stuff. I am not saying you can
take over the world with this but I am not saying you can’t either. But, with
great power comes great responsibility. You can do some serious damage with
this stuff. Practice locally and copy tables as backups so you can revert to a
working database if things go south. Cover your ass.
Alright, I think that is more than enough information for now. Take a stab at this stuff and see how it works for you.
Friday, May 31, 2013
Oracle Flashback
So, I overheard a conversation this week and discovered a really interesting and potentially useful feature called Oracle Flashback. Depending on how your DBA's have setup the database you may or may not be able to hit the ground running with this feature. Let's say we were updating or deleting records in our table and did something we really didn't want to do, such as update or delete the wrong record. Depending on the complexity of your database, you may be able to replace or manually undo the mistake but in an OLTP system more than likely you are pretty much hosed. I've done it. Many have done it. It's often viewed as a badge of courage. You learn to pay closer attention.
But, let's say you've done something by accident and committed before you noticed your error and you really need to recreate the data the way it was prior to your little whoopsie. Well, in comes Oracle like a messiah - no, a superhero. It saves the day with Oracle Flashback. There is a system package called DBMS_FLASHBACK that you would need execute on from your DBA and they would have to have setup the UNDO_RETENTION and UNDO_MANAGEMENT properties accordingly ahead of time. If not, you are going to have a bad time.
Anyway, here's the scenario: you get tasked with deleting a record from a table called RECIPE. You tried grandma's recipe for potato salad and it tastes like ass and cat food. You want to get rid of that motherfucker before someone ends up dead. In your haste, you forget to qualify your delete statement and up deleting the whole table and you committed. Oh noes!?!?! What do?!?!?! Well, you would write something like:
CREATE TABLE ORACLE_SAVE_MY_ASS_PLS AS
SELECT * FROM (
SELECT * FROM RECIPE
)
AS OF TIMESTAMP SYSDATE - 0.125;
This will create a table that is a copy of recipe as of 3 hours ago. If your UNDO_RETENTION is set for a day or more, you might be able to fix something you fucked up yesterday. But if you need to fix something you just did a few moments ago and have made other changes previous to that that were legit, you'd have to alter the number you subtract from SYSDATE to zero in on your change. As a reminder, SYSDATE is the function that returns the time stamp from the database server for when it is run. You can subtract or add a number to SYSDATE to go back or forward in time. Now, obviously for our current purposes we cannot go forward in time, that doesn't make sense. But, often systems will add 30, 60 or 90 days to SYSDATE to set a reminder in the database or to control some future functionality, like password expiration.
So far, I have yet to use this little feature to save my bacon (mmm, bacon!) but I can really see it's usefulness. A caveat here would still be TRUNCATE. This will not work if you TRUNCATE the table. TRUNCATE does not record the action in the UNDO logs and you will get dry anal raped by TRUNCATE. Use this wisely. Be cautious and use sparingly. You best know what you are about son.
I will leave you hear. I've slacked off a bit lately on posting and I'm trying to get back on the proverbial whore, err, horse or horse-faced whore. Whatever. I need to post more frequently is what I'm trying to say. And I have the sudden urge to watch Sex and the City with the sound off and my pants down. Ah, the Chris Noth is a handsome motherfucker.
Friday, May 3, 2013
Finally approaching warp speed with Unit Tests or More about this DBDeveloper's past than you probably care to know
I haven't posted for a bit. I've been busy at home and busy at work. The last few sprints at work have been grueling and ball-busting. Sprints are the Agile process's coding increments. Ours are currently broken into two weeks. Tasks are divided into small enough granules that can be completed in that two weeks or (the one that seems to be happening more and more) broken up in the the smallest pieces that are logically or easily done. My latest task is changing four reports and logically, any one of those could be completed and released independent of the others, but that would mean creating four separate issues to track and overhead for people so it is lumped together. I may or may not get these done in two weeks. I have recently had an issue drag out for several sprints. Let me tell you it fucking sucks. In no uncertain terms, it is a terrible feeling to go into a meeting and have people looking at you like what the fuck have you been doing? Why isn't this done? Well, my excuses have been, scope creep, under pointing stories ( issues are also sometimes called stories and they are pointed to give a weight of difficulty or time involved), stories still at an epic level, and the length of time and the monumental amount of work to arrive at a functional unit test at the database level. Some of these need to be addressed more in our planning meetings or retrospectives where we plan out what to work on and talk about what worked well and what didn't work well looking back. I will bust my hump on this current task and potentially get it done on time, but I need to bring up the self-defeating behavior to the group and discuss methods to minimize this.
The one thing that has seemed to be improving is the unit test portion. A year or more ago, I had not a fucking clue what a unit test was. I've been out of school for about 15 years now, and I must say that I haven't kept up with everything or much of anything really going on in the industry at large. I've had a lot of my personal time consumed by mitigating poor life choices and the like. I've gone through bankruptcy, divorce, the birth of my children, the betrayal of trust by once close friends, and the death of family and friends, just to name a few. Boo fucking hoo, amiright? Really, I was never very industrious and usually gravitated towards spending time doing things that weren't healthy for me. I'm not very career-centric, at least, historically speaking. I've just meandered through: should probably go to college (went to college), should probably graduate (graduated), should probably get a job in my field (got job in my field), should probably get a better job in my field (got better job in my field), etc.
So, now (and I realize I am digressing like a motherfucking boss - wait for it...) I find myself surrounded by people that are very cognizant of their skill sets growing stale and the industry moving forward in leaps and bounds. Since I am a very impressionable guy and easily bow to peer pressure, I have started branching out and learning more, etc. Of course, it helped that I finally started to get my personal shit together. I haven't had my ass kicked in 16 years or so and I haven't had a death threat in 7 or 8. I might be a real boy now, Geppetto! Anyway, since my personal life is doing good, my only distractions at home are hobbies, the family and the usual stuff that every "normal" person deals with, I have some time that I can safely devote to self-improvement (and not just on the tech side of things).
Anyway, back to not knowing what the fuck a unit test was. Given my penchant for doing only things that were at a minimum unproductive and usually detrimental to my health, sanity, employment, etc., I was completely clueless to the advancement of testing and quality control and what not. If my schooling had touched on any of this, I had quickly forgotten it in a booze and stripper fueled haze. I logically understand the need for good testing and find that unit tests and test based development in theory are wonderful things, but in real life, can be quite the undertaking for a database guy. In application code unit tests, programmers can stub out or easily simulate the tangential and/or dependent pieces that your current code needs to interact with to work correctly. This simulation is much more difficult at the database level. For one thing, how do you simulate a record existing in a table? If the procedure you are testing interacts with a record in a table, you pretty much have to have a record in the goddamn table. This, coupled with the advice of a non-database programmer that "you cannot depend on any record existing prior to your test" that I religiously followed because, like most religious zealots, I didn't think about it. Zing! I swallowed it hook line and sinker. My first unit test was a fucking mess. It was a Gary Busey in drag kind of ugly conglomeration of things that shouldn't be where they were and are just repulsive to think about. I quickly learned the uselessness of assuming that nothing exists prior and adding everything. Many of our supporting tables have a strictly controlled list of values, lacking even sequences to populate the primary keys. And, if my goal is to test how a certain construct affects data in a real world scenario, creating some of these type records from scratch simply do not make sense.
About, halfway through my second test, I figured out that I needed a hybrid approach. I needed to be able to assume that some things were in fact already present in the database to appropriately test certain constructs. I pursued the idea of this hybrid like a queer dog pursues a Prius, lightly skipping along with chapped lips and sore knees and humming a Justin Bieber tune. I think I failed to mention that my first test took 99% of the development time. It vastly dwarfed the real actual coding. The part that added benefit to the application and eventually eased the suffering of the end user, at least to a degree, was over quite quickly and I struggled with the "this test isn't going to help things" mindset for a while. Without the mandate that all code needed to have unit tests, I probably would have dropped it. After the hybrid approach was considered, that test still took quite a long time to develop but I felt pride that I improved the process a bit.
My next test resulted in even more improvement on the process. I wrapped the procedures with stripped down procedures and adding error handling and surfaced issues with more actionable information. I really felt it starting to gel. I have finally reached the point where the amount of work to write the unit test didn't dwarf the value adding portion of the work. It was still a 75/25 split or so but it seemed more logical this time around and I felt more capable of actually completing my work on time. I actually met a self imposed deadline today. Its been a while since I've hit one. It feels good man. Awwwww yeahhhhhhhh!
I am hopeful that I can shift the ratio more in my favor in the next iteration and gain even more speed in developing tests and confirming that shit works before it hits production. It is only a matter of time before the infrastructure side of the house has their ducks in some kind of arrangement that looks sort of row-ish and the focus of the issues we are having as a company becomes more application development centric. They probably will not give a shit about why it takes me so long to code these tests, they probably will just ignore any reasons whether valid or bullshit. I'd better get to where I can knock out a unit test in a much faster time. Maybe not premature ejaculation type times. Definitely not Miculek target shooting times. But, something better than 3 times the duration of the real development. I'll get there. With every one, I gain a further nuanced understanding of the inner workings of this thugged out crazy ass database. I don't know if you know this, but one of the database we have is completely fucked up. It is like Lindsay Lohan on Spring Break. It makes no sense, looks like shit, and probably forgot its Plan B. Someone is making a stop at the clinic.
In closing, Unit Tests are a good thing. You should make sure your shit works. Some of us are less perfect than others but none of us are infallible to typos and faulty logic. We cannot know our shit is bulletproof. It isn't. Even if it is, it is better to prove it by shooting at it than just making magnanimous claims like, "My Shit Doesn't Stink!" I say, "NO." and "FUCK YOU!" and "YOUR SHIT STINKS! ALONG WITH YOUR BREATH, YOUR FEET, MOST OF YOUR OPINIONS, AND YOUR MOTHER"S COOKING. SHE CAN SURE SUCK A DICK THOUGH..." But don't get mad, so does mine. I miss you mom.
Perhaps I've said too much. But, no matter. I came here to write a post and fuck bitches and my post is finished.
The one thing that has seemed to be improving is the unit test portion. A year or more ago, I had not a fucking clue what a unit test was. I've been out of school for about 15 years now, and I must say that I haven't kept up with everything or much of anything really going on in the industry at large. I've had a lot of my personal time consumed by mitigating poor life choices and the like. I've gone through bankruptcy, divorce, the birth of my children, the betrayal of trust by once close friends, and the death of family and friends, just to name a few. Boo fucking hoo, amiright? Really, I was never very industrious and usually gravitated towards spending time doing things that weren't healthy for me. I'm not very career-centric, at least, historically speaking. I've just meandered through: should probably go to college (went to college), should probably graduate (graduated), should probably get a job in my field (got job in my field), should probably get a better job in my field (got better job in my field), etc.
So, now (and I realize I am digressing like a motherfucking boss - wait for it...) I find myself surrounded by people that are very cognizant of their skill sets growing stale and the industry moving forward in leaps and bounds. Since I am a very impressionable guy and easily bow to peer pressure, I have started branching out and learning more, etc. Of course, it helped that I finally started to get my personal shit together. I haven't had my ass kicked in 16 years or so and I haven't had a death threat in 7 or 8. I might be a real boy now, Geppetto! Anyway, since my personal life is doing good, my only distractions at home are hobbies, the family and the usual stuff that every "normal" person deals with, I have some time that I can safely devote to self-improvement (and not just on the tech side of things).
Anyway, back to not knowing what the fuck a unit test was. Given my penchant for doing only things that were at a minimum unproductive and usually detrimental to my health, sanity, employment, etc., I was completely clueless to the advancement of testing and quality control and what not. If my schooling had touched on any of this, I had quickly forgotten it in a booze and stripper fueled haze. I logically understand the need for good testing and find that unit tests and test based development in theory are wonderful things, but in real life, can be quite the undertaking for a database guy. In application code unit tests, programmers can stub out or easily simulate the tangential and/or dependent pieces that your current code needs to interact with to work correctly. This simulation is much more difficult at the database level. For one thing, how do you simulate a record existing in a table? If the procedure you are testing interacts with a record in a table, you pretty much have to have a record in the goddamn table. This, coupled with the advice of a non-database programmer that "you cannot depend on any record existing prior to your test" that I religiously followed because, like most religious zealots, I didn't think about it. Zing! I swallowed it hook line and sinker. My first unit test was a fucking mess. It was a Gary Busey in drag kind of ugly conglomeration of things that shouldn't be where they were and are just repulsive to think about. I quickly learned the uselessness of assuming that nothing exists prior and adding everything. Many of our supporting tables have a strictly controlled list of values, lacking even sequences to populate the primary keys. And, if my goal is to test how a certain construct affects data in a real world scenario, creating some of these type records from scratch simply do not make sense.
About, halfway through my second test, I figured out that I needed a hybrid approach. I needed to be able to assume that some things were in fact already present in the database to appropriately test certain constructs. I pursued the idea of this hybrid like a queer dog pursues a Prius, lightly skipping along with chapped lips and sore knees and humming a Justin Bieber tune. I think I failed to mention that my first test took 99% of the development time. It vastly dwarfed the real actual coding. The part that added benefit to the application and eventually eased the suffering of the end user, at least to a degree, was over quite quickly and I struggled with the "this test isn't going to help things" mindset for a while. Without the mandate that all code needed to have unit tests, I probably would have dropped it. After the hybrid approach was considered, that test still took quite a long time to develop but I felt pride that I improved the process a bit.
My next test resulted in even more improvement on the process. I wrapped the procedures with stripped down procedures and adding error handling and surfaced issues with more actionable information. I really felt it starting to gel. I have finally reached the point where the amount of work to write the unit test didn't dwarf the value adding portion of the work. It was still a 75/25 split or so but it seemed more logical this time around and I felt more capable of actually completing my work on time. I actually met a self imposed deadline today. Its been a while since I've hit one. It feels good man. Awwwww yeahhhhhhhh!
I am hopeful that I can shift the ratio more in my favor in the next iteration and gain even more speed in developing tests and confirming that shit works before it hits production. It is only a matter of time before the infrastructure side of the house has their ducks in some kind of arrangement that looks sort of row-ish and the focus of the issues we are having as a company becomes more application development centric. They probably will not give a shit about why it takes me so long to code these tests, they probably will just ignore any reasons whether valid or bullshit. I'd better get to where I can knock out a unit test in a much faster time. Maybe not premature ejaculation type times. Definitely not Miculek target shooting times. But, something better than 3 times the duration of the real development. I'll get there. With every one, I gain a further nuanced understanding of the inner workings of this thugged out crazy ass database. I don't know if you know this, but one of the database we have is completely fucked up. It is like Lindsay Lohan on Spring Break. It makes no sense, looks like shit, and probably forgot its Plan B. Someone is making a stop at the clinic.
In closing, Unit Tests are a good thing. You should make sure your shit works. Some of us are less perfect than others but none of us are infallible to typos and faulty logic. We cannot know our shit is bulletproof. It isn't. Even if it is, it is better to prove it by shooting at it than just making magnanimous claims like, "My Shit Doesn't Stink!" I say, "NO." and "FUCK YOU!" and "YOUR SHIT STINKS! ALONG WITH YOUR BREATH, YOUR FEET, MOST OF YOUR OPINIONS, AND YOUR MOTHER"S COOKING. SHE CAN SURE SUCK A DICK THOUGH..." But don't get mad, so does mine. I miss you mom.
Perhaps I've said too much. But, no matter. I came here to write a post and fuck bitches and my post is finished.
Subscribe to:
Posts (Atom)