- who created the row
- when did they create it
- who last modified it
- when did they last modify it
What I want is to have something that checks if any of those columns are not being filled out. I want this test to run after the scripts run, and then again after the suite runs. But I think that if tables are added / taken away this will be something that will not be maintained, so I want something reflexive. If you didn't notice, I like things that don't have to be constantly maintained to work.
Back in my early days of Java dev work, Peter and I worked on an app that got the meta data from a sybase db using a sybase specific call. Now, that call was specific and ugly, and I really don't want to do something like that. Now, to our credit, that part of the app was already done when Pete and I started to work on it. Tonight I found something that looks promising: DatabaseMetaData.getColumns. This allows you to pass it patterns and it then returns a result set that you can go through and pick out the info that you want. From this I think that I'll be able to pull out all the columns that have the names that I want and be able to build and run queries that check if those values have been filled out.
I'll update on how it goes. ;-) Now on to packing....
Why don't you just add a clause to those columns saying they can't be NULL? Then if someone tries to insert a row with NULL in any of those columns, the insert will fail.
ReplyDeleteIn SQL this is done in the table definition:
[COLUMN_NAME] [TYPE] NOT NULL
you know what, I have no idea why the dba's didn't build it that way. That WOULD make a lot more sense.
ReplyDeleteOne PITA thing about using EJB's and NOT NULL's is that you have to pass in those values as part of the constructor. So at this point, I don't think that I want to change and re-work that stuff.
Humm... I'm not sure that I want to do this exploratory metadata thing anymore...
Thanks.
"One PITA thing about using EJB's and NOT NULL's is that you have to pass in those values as part of the constructor. So at this point, I don't think that I want to change and re-work that stuff."
ReplyDeleteWhat does this mean?
BTW, if you explain the sitation to the DBA he might change it! You can fill any existing empty fields with a really impossibly old date, if that's the problem.
ahhh.. the anti-COOP.. so many pleasant memories... or well, many memories anyways.
ReplyDeleteSo Jim, based on your above comment, I take it that you don't have direct control of the DB schema then right?
Could you possibly just run simple queries looking for the null values that you don't want to find and then raise an error if you get a result back?
Ah, sorry about that Ryan. With entity EJB's, on creation of an object any values in the db that are "not null", you have to pass in with the constructor of the bean. This is because on creation, the row that corresponds to that object will be written to the db. So, if you don't have the value in the object, the db will throw an error.
ReplyDeleteSo, if you have an EJB of type Account, if you wanted to make a new account you'd have to do something like this:
Accounts cash = AccountsHome.create(cashPrimeKey);
now, if Accounts has 5 "not null" columns, you have do something like this:
Accounts cash = AccountsHome.create(cashPrimeKey, req1, req2, req3, req4, req5);
It's really just semantics 'cause either way you'd have to set the values. It just means you have really long (and ugly) constructors.
We don't really have *direct* control of the schema. The dba's are really great though and have lots of helpful / great suggestions and get back to us very quickly with any work done. It's actually cool 'cause there is ONE central db group that handles tons of different apps. They are very skilled, and they actually did our whole db design. (We did it quickly in "prototype" mode, they fixed it up.) At work they are pushing for a whole centralized model to cut down on redundancy and overhead.
Yes Pete, making simple queries and checking for null was what this whole post was about. But what I wanted to do is figure out the tables with specific column names at run time. That way if we added a new table and forgot to add the values, BAM! It would show up in the test case right away.
Okay, I think that I am rambling and I'm tried... too much dust stirred up by throwing stuff out. I'm off to bed.
The dates can be read from the system clock at runtime and do not have to be passed to the constructor. Just the person creating or modifying that row.
ReplyDeleteAs for the DBA problem: it's kind of ironic that centralized DBA's are supposed to be more efficient, when it creates inefficiencies like this.
You don't have direct control of the schema, but the schema is obviously not right ... those columns should be NOT NULL. If you explain to the DBA why, he could change it for you and avoid the need for this new stuff entirely.
Depending on how you go about doing things, you may not have to pass all that stuff into the constructor. Time of creation and last update can be figured out by the program, no need for parameters there. As for who created it, and who changed it, well, that may be a bit different depending on how things are done. But it really just corresponds to one extra field in your constructor.
ReplyDeleteYes, I believe that you WOULD have to pass them to the constructor unless you are going to overwrite generated code. EJB's are a lot different than POJO. It's more like CORBA.
ReplyDeleteI don't think that I explained myself well. I find the dba's here VERY efficient. At this point, the change would mean a lot more work on my side than the dba's.
With deadlines approaching fast, I don't think that we have the time to make that change because of the work involved on our side. It SHOULD be made of course, but I can't see it happening in the next couple of weeks. :-|
I didn't meant to imply that they *personally* are inefficient, just that they cause inefficiencies like this. Given they are grouped together to be more efficient, I thought it was ironic.
ReplyDeleteAnyhow, I get the impression from your last comment that a change to NOT NULL columns would break things on your end. Definitely not the right thing to do at the end of a release cycle. You might want to do the db change at the beginning of the next iteration.
Am I also correct in summarizing that what you're ultimately trying to do is catch programming errors? By scanning the database, you make sure that the application code is inserting values into the columns you'd rather not be NULL.
Yes, you are correct in that summary.
ReplyDeleteI thought that my blogging was making my communications skills better, but I am thinking twice now. Ah well. Practice, practice, profit!
;-)