AUTO_INCREMENT Part 2

Back in June I discussed how Oracle databases don’t have an AUTO_INCREMENT type. In the process of this discussion I showed a way around this. While I like this method in certain situations, in others it has several flaws that really bug me. First off and foremost if you are using your table as a log tracking the progress of a process than you’ll have to reset the sequence every time you restart the process. Well, I guess that assumes you, like myself, truncate or delete * from that table every time you run the process. Either way, I think that should be an unnecessary step. It’s sort of like having an UPDATED_ON column and actually feeding it the SYSDATE every time you do an insert. Why not just set DEFAULT SYSDATE when you create the table?

Now my second method for replacing the AUTO_INCREMENT type has it’s flaws as well, but it works quite well for the log style table I described above. This time we won’t use a SEQUENCE at all though. Instead we’ll simply use the rowcount(*).

CREATE OR REPLACE TRIGGER state_trg
BEFORE INSERT
ON scheme.state_table
FOR EACH ROW
BEGIN
SELECT rowcount(*)+1 INTO :NEW.column_pk FROM state_table;
END;

Now before you go crazy and start adding this to all kinds of tables be careful, this method isn’t perfect either. First if you ever delete from the table without truncating or deleting * you’ll (hopefully) get a unique constraint violation error. Hopefully why will be obvious. Again, the code has it’s uses, it’s up to you decide when and where it’s a good or bad idea.

Enjoy the Penguins!

24″ HP Monitor vs Fonts

What on earth is it about monitors that makes them all render my fonts differently??

At work I have twin DELL 19″ 3:4 LCD displays. At home a 24″ HP 16:9 LCD and they seem to render things completely differently. It’s enough to drive me nuts. One thing of note though is I only really seem to have trouble with this using gVim. Is the font rendering code in vim so old that switching monitors ruins the font??

Enjoy the Penguins!

The AUTO_INCREMENT type

At work I was working (imagine that) on creating a new procedure in the database to escheat checks based on certain rules. Explaining what exactly was going on would be getting into to much accounting for my taste so we’ll continue on to the real problem. So as the first step in this process I created a table with the rules in it. The period of time a check stays out before being escheated varies by state so each state had a row in the table. Now, I had several columns, the full state name (West Virginia), the postal code for the state (WV) and number years for each type of payment (bonus, royality, etc). Now the problem here is I didn’t want to have to insert a key for each row, I find that stupid personally. If all the primary key is in this case is just an enumeration of the rows then I see no need for me to come up with that number, it should be calculated and applied by the database.

In MySQL this would be easy. You can simply set your primary key field as type AUTO_INCREMENT and be done with it. Now when you insert a row into the table you simply leave the PK field out of your insert statement and the key is automatically calculated and inserted for you.

In Oracle though, this is not so easy. First off and foremost there is no AUTO_INCREMENT type. You have ENUMs, which are handy, but not what we want. So how do you get around this? Well you have to do what I consider a hack. First I created the table using proper types and setting my PK. [I stress my use of keys and types because I see a lot of people not doing that and it seriously irks me.] Then I created a SEQUENCE. I gave it a silly, yet descriptive, name.

CREATE SEQUENCE state_seq
START WITH 1
INCREMENT BY 1;

So far so good. I then created a trigger to go with my sequence. So every time a row was inserted into table the trigger would insert the next number in my sequence with it.

CREATE OR REPLACE TRIGGER state_trg
BEFORE INSERT
ON scheme.state
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT state_seq.nextval INTO :NEW.column_pk FROM dual;
END;

Now, looking back at MySQL wouldn’t it just be easier to have AUTO_INCREMENT in Oracle? They make millions (if not billions) every year off this stuff and they can’t put this and command recall into SQLPlus? Really?

Enjoy the Penguins!

See Also: Auto_Increment Part 2

Is Object Oriented always the correct strategy?

Being the age I am, and given the training I’ve received, Object Oriented is really the only way I know. Another way to program was never an option for me. Now the semantics of which language is and which language isn’t is immaterial here. C and C++ may not be purely OO languages but you still use them in an OO fashion. All (err most) of the OO principles are there: inheritance, abstraction, and even polymorphism. But I have recently, with my current job, run into a dilemma. What if a language, despite the ability, really shouldn’t be used in a OO fashion?

My problem stems from PL/SQL. We have an interface between two databases at work written in PL/SQL. So far so good. The problem is though is that the interface is written in such a fashion that, along with Oracle’s massively helpful error messages, it is almost impossible to debug quickly and easily. And as far as I can tell, it all stems from the OO fashion in which it was written. Now, unlike most modern languages, there is very little OO in PL/SQL as far as I’m concerned. You have encapsulation and modularity, but I don’t see much abstraction for example. It appears to me that despite Oracle’s best efforts their attempt to make PL/SQL OO have been (overly) appreciated but not worth it. Whats wrong with old fashion procedural programming? What do I even need OO for with PL/SQL? I have a massive data set that needs to be worked. I need to filter through it, pull what I need, discard what I don’t. And then fill a table with what’s left. Not hard. Do 1 then 2 then 3. Even when written in English (as opposed to code) it’s all very un-OO.

If you want specifics I can elaborate in another post. For now though I need to wash and wax my car.

Enjoy the Penguins!

Lazy Linux

Do you ever get tired of putting a lot of effort in to Linux? Get tired of waiting for things to compile? Grow wearing of trying to figure out why something fails to compile?

I do. Right now my computer has, of all things, Fedora 10 on it. Why? Not because it’s awesome, that’s for sure. Though if you have to go precompiled, easy to install, and easy to use, it more or less tops the list in my book.

I don’t know. I love Gentoo, and though I haven’t tried it yet, I’m sure the distributional offspring of paludis, Exherbo, is a wonderful setup as well. It’s just that sometimes I don’t care to tinker anymore. I don’t want to configure. I don’t want to setup. I just want to turn it on. Know it will boot. Use it. Then turn it off. Sometimes I don’t even care if GCC is installed. Just let me surf the net.

Yet, at the end of the day, I feel guilty for this. It’s almost as if I have betrayed myself by succumbing to my lazy whims. Linux has become as much of a constant work of art for me as much as it is a toy or even just a tool. I feel compelled to not just use, but to improve. For me “improve” rarely means code. I spend all day at work writing PL/SQL…. hardly something that translates into FOSS. I’m currently writing a program in C. But it is slow coming. I admire people who write these incredibly complicated programs seemingly in their spare time. I assume that most of the creators and major contributors to paludis have full time jobs. I know some of them do. I consider a full time college student a full time job too. I work 40 hours a week. I come home tired. Ready for a nap. It’s hard for me to fire up the desktop or even open the lid on my Mac and start programming. It’s almost to the point I’d rather donate money than code myself…

Enjoy the Penguins! (especially if you write them)

Setting-up Oracle

Setting-up Oracle (at least under Windows) is the most horrible feature I’ve ever seen in my life. Why is it so bad? How can a company that can afford to buy Sun make such crappy software?

Okay. I realize that Oracle’s databases are some of, if not, the best in the world. But it absurd what you have to go through to install the client and everything else you need just to work with one. At work, like a lot of companies, we use Oracle, of course, but not only do we use it, we use like 4 different versions of it. So that means I have to have, at least for now, two different versions of Oracle client installed on my work PC.

For all of you who have never had to do this, each version of the client, assuming you do a complete install is roughly 900MB a pop. Each install requires a different “home” directory and each creates it’s own set of registry keys (we use Windows XP of course). So far so good I guess, except for the size, except that the installers never work. For example, if you need version 7 along with a later version, you have to install version 7 first or it ruin your other clients.

So today, after realizing I had magically gained somewhere upward of 5 different homes and only two functioning clients (9.0.4 and 10.0.1) I decided to blow it all away and start fresh. It seemed like perfect timing as well since we’re upgrading our main DB to 10g which meant my default client, 9.0.4, wouldn’t work anymore.

So after doing all of that, which ended up being very painful because Oracle really buries itself in your registry, I installed both clients. Naturally, after installing my new 10g client along with 9.04 (I still need it for other databases) I naturally encountered errors. Logging in through various means I encountered the following two errors: ORA – 12222 and ORA – 12538. So after about an hour going through Google trying to find the answer I finally figured it out buried deep in a forum post the guy with the problem totally ignored.

The first install apparently setup two ORACLE_HOME environment variables, one for the User and one for the System. After deleting both, both errors went away.

All of that and Toad for Oracle, latest version, still crashes on me on a regular basis.

Enjoy the Penguins!

Programmer’s Pinky

I don’t have carpel tunnel *knock on wood* but I have discovered something else. Programmer’s pinky. I was formally trained how to type. As I assume most people are these days who attend school. But like most people, I assume, my typing isn’t perfect. I don’t hit all the keys with the right fingers. My form isn’t perfect I suppose but I still manage a respectable 70 wpm last time I checked, so I’m not really worried about it. On the same hand though the other day I was converting some SQL files from our old database to out new one and I was using ctl + c, ctl + v, ctl + x, etc. so much that my pinky was actually sore the next day. The problem stems alot from the fact that I always use my left pinky for the Ctrl key no matter which side of keyboard the other key is on. I guess that’s one habit I’m going to have to retrain myself on.

Enjoy the Penguins!

Entity Managment

I was looking through the internets the other day and it occurred to me that there is no open source software out there devoted to this. What is entity management? Well, it’s simply keeping track of what you own, what you lease out, what you rent, and what you sell. Power companies have to lease out land a lot of times because they don’t necessarily own the land the power poles are on. Obviously gas companies are in a very similar situation. Even companies you don’t expect to need such software might. Large banks for example might lease the land the bank on. I know a local car wash doesn’t actually own the lot the wash is on, just the car wash itself. Why doesn’t this software exist? My guess is simply because it’s boring. Who would want to and why? It’s like writing medical records software or something. How boring is that?

But in light of this, I’ve decided to give it a go. Why not? Screw it, I can code. I can write software as crappy as anyone else on the internets. In fact, I’ve already come with a basic database layout using MySQL. To be quite honest though I’m not a fan of MySQL thus far and I might find myself quickly switching to Postgre. I think the SQL i’ve written thus far will probably easily work in either, it’s not exactly complicated stuff at this point.

I haven’t published any code or even given this potential project a name yet, but I might later. What is it they say, “release early, release often.”

Enjoy Penguins!

Coding in Open Source

Do you ever want to contribute to a project or even start or your own? Obviously you do. Why else would you be reading a blog devoted to Linux. Given that then do you even find yourself with absolutely zero passion left because the task is so daunting, or the program you would to contribute to has tens of thousands of lines of code? Yeah… that’s totally me on regular basis. Can I code? Yeah. I can make programs do all kinds of neat things. Do I really want to spend weeks figuring out your code? No. Do I want to spend weeks just writing back end “boiler” code to start my own project? No. Sort of makes you hate programming doesn’t it?

Proposed Small PC

I recently posted that I wanted a new PC. Well, I want a desktop anyway. My Apple laptop is still in excellent shape, especially since I dropped 4G of RAM in it. Anyway, I have built a computer for myself on Newegg and saved it as a public wish list. Something to consider before viewing. I will be reusing my current hard drive along with my current CD/DVD burner. Outside of that I think I’ve got everything there I need.

NewEgg Wish List
(If that stupid link doesn’t work blame newegg)

Suggestions?

Enjoy the Penguins!