- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
VBA upgraded to dotnet... hmm... [:$]
Admin
> thirty-four interconnected spreadsheets and six separate databases
Bah, at least they use databases; another department in the company I work for uses 1000+ spreadsheets, one for each customer, then the front-end spreadsheet gets it's numbers by iterating over all the files in the "customers" directory...
Admin
I don't know about NASA or IBM, but I know Google doesn't use MySQL, it uses Pigeons:
http://www.google.com/technology/pigeonrank.html
Admin
lol, great link ;) Though regarding Google using MySQL, I do know they at least did once, I'm not sure if they still do however.
Admin
Google did/does use AdSense.
http://xooglers.blogspot.com/2005/12/lets-get-real-database.html
Take the comments with a samall boulder of NaCl though.
Admin
None of the comments were doctored. They are straight from the original VBA source. I actually pulled them from an e-mail (I save them forever) we sent to each other laughing about how bad these spelling errors were. In fact, because spreadsheets are treated as binary objects (including the VBA code), it was hard for me to find some of the real gems. For instance, we got called about a bug one day and when we delved into the code, we found a branch of 200+ if/elseif statements that we replaced with 4 or 5 lines of code.
Admin
"A database system, to me, is something to store only, and that's it, it's like a container. If you want checks, why don't you put them into the input treatment code? and in the the writedb() too?"
First, no dbms worth its salt is just "a container". It supports and enforces ACID properties for transactions. Your basic dbms is already doing a lot to ensure the integrity of the data, so thinking of it as a dumb-store of data is wrong from the get-go.
Second, the database engine is the last line of defense for the data. I agree that it doesn't make sense for there to be a lot of business logic running on the database server (though I know some believe otherwise, which is okay). However, the database engine is the last place and arguably the best place where little details like referential integrity, uniqueness and so on can be checked.
While you can put that logic in the client, it is often more expensive (how do you propose a client app quickly check for uniqueness in a column containing millions of distinct values?). It also only secures the data for that client. If another client comes along that doesn't enforce (say) referential integrity, or doesn't do it correctly, poof: there goes your data integrity.
Put that logic on the database server and you guarantee that it will be enforced for every client.
Admin
I know these are hack database for hack jobs, but Guess What -- nine times out of ten, for small to medium agencies, Access-like tools solve the problem. I don't like it either, but that's the goddamn truth.
Sure, it works for now. And then, the agency grows. And grows. And the Access Database gets hacked up, by people who did not originally design the applet. Relationships get dirtier and dirtier, performance tanks, everybody starts screaming about how it just "doesn't work right" and the cost of porting it to something better and more 'enterprise' is prohibitive.
No way I want to be part of that cycle!
Admin
No.
Admin
Oh, and I guess in communist countries, competence is always rewarded.
After you fall of the turnip truck and have been around the block a few times you will realize that ass-kissing and self-promotion are rewarded. Competence is only a bonus.
Admin
i would say i don't have too much experiance with everything
but when i see tables in databases like that i start to cringe, what are people thinking.
Admin
U know what frightens me the most? :
Admin
Well I am consultant who. . .
I hope you're a Russian.
English speakers would have said
"well, I am a consultant who spends most of his working life... "
Actually, Access is a pretty good way to obuscate data. . . which would give a cryptographer a leg up. . .
Frankly, Access is a beautiful pension program for the people who wrote the Access software. Data conversion programs typically say "The file you are trying to convert is an Access databse.. . . sorry
Once you've committed your date to Access, you are the property of "the Devil". (actually, I'm an athiest; you can insert your own devil/ogre/nastyguy)
Admin
Uh, if this hypothetical agency is too cheap to upgrade to an enterprise class system when it's necessary, what makes you assume they'd be willing to outlay this at construction time?
There's nothing wrong with the dime store solution -- so long as it stays in the dime store. My old company had software that was everything bad you could say about Access and VB, rolled into one monstrous system laden with hacks. Support was a nightmare. We had the brainpower and motivation to bring the system to .NET.
The biggest hindrance to upgrades? The majority of our customers were 1 to 3 station shops with no synchronous access, and they absolutely loved the program. It was quick, it did what they needed, and since it was just one file, it was easy to backup.
Admin
MySQL for low-cost web solutions. Access for when the IT dept won't give me the time of day.
I'm sure Oracle must have its uses... ^o) - can someone burn me a copy? I'll trial it on my 486 test server.
Admin
You dolt! Everyone knows you spell it d31337!
/Ryan
Admin
Admin
I bet you mean "We English programmers...."
Admin
Heck, I thought I was being somewhat original... I should have read the whole thread first.
Otto, if you're going to port your application over to MSSQL, why wouldn't you have prototyped it using MSDE instead of Access? The only time I've programatically used Access, and I think it was an appropriate choice, was when I had a "client" who wanted a light-weight app that could be used to run a "gameshow" type of application for a conference. This was wholely appropriate for the task since Access was already installed and I wouldn't be able to configure MSDE for this client since they lived in a different state.
I've used Access (and written enough VBA code to wrap the logic) a couple of times for small tasks with an equally small dataset. In one instance, I needed to quickly generate reports and wanted a simple front end form for loading the data... Access was ideally suited for the task. In the other instance, I worked for a small NROTC unit (as a student worker), and we used Access with some VBA duct tape to help the NROTC staff keep simple records on the Midshipmen that were in the program at the time.
Could I have used something else, sure, but the task didn't need it and anything else would have been overkill. These are the sort of projects that Access is actually good at. It isn't something I would base business logic or a website on (actually now I can think of a couple more times I did use it ironically for websites, but they were quick and dirty internal tools that only a handful of folks including myself were using).
Admin
Sounds like you need a good refactoring tool.....
Admin
Ohh, that reminds me, a company I used to work for used Excel to handle invoicing. One sheet per-invoice with all the formatting (including the company logo, copy & pasted to create a new "invoice". Of course, Excel starts to choke after something like 60 sheets I think it was, so I think they just created another workbook and kept going. </shudder>
Admin
Really? That's weird...
Admin
Just because IBM uses MySQL doesn't mean they COUNT on it for anything.
I've used MySQL, I've used PostgreSQL, and played with Oracle. After using the (free!) PostgreSQL, I'll never consider MySQL for anything, ever, again.
Software isn't a product, it's an intellectual entity based on the culture of its inception. Good quality software is so not because it's lucky, but because its design and implementation are steeped in a culture of careful consideration and attention to best practices.
PostgreSQL has that culture, and Oracle has that culure, but MySQL's culture can be best compared to a 3-week-old piece of green hot dog. I can't speak for the others, I've not used them.
I routinely use PostgreSQL in highly demanding environments where data integrity is critically important, where it's normal to spend a huge amount of time ensuring that invalid values are simply not possible, and then enforcing those constraints in the database with extensive constraints and transactions in order to catch logic bugs. In this environment, if foreign keys and constraints (including check constraints) aren't spot on, I get bit, hard, by people with lots more power than myself.
MySQL, even at version 5, is still a chump database. Oracle is obviously "enterprise grade" but generally comes with an enterprise price tag. PostgreSQL offers enterprise-grade quality at MySQL prices, and I've never regretted betting 100% of my efforts on it!
PS: When will WTF get preview? When will WTF captcha work first time around?
Admin
Oh the irony.
Admin
1. Never. That dead and rotting leg has been amputated.
2. After you register
Admin
Even the Microsoft guys say Access is an abortion. They recommend SQL Server Express Edition if your application needs a database.
SQL Server Express is free and freely redistributable with your application, it only has a few limitations compared to the full one, none of which would be encountered on small to medium sized systems.
Admin
Sounds like good design.
Admin
confirms my suspicion that all failed programmers become managers
Admin
The story at http://www.google.com/technology/pigeonrank.html is an April Fools' day article. In the pages own words:
<font color="#cc0000" size="-1">This page was posted for April Fool's Day - 2002.
The Xloogers blog claims they did use MySQL initially but later on switched to an unnamed commercial database.
It's worthy of note that they said MySQL worked well but lacked features. Although I agree with the designation of MySQL as not being enterprise class, I am not sure I could win an argument with such a high-powered user as Yahoo.
</font>
Admin
I've ever learn that data, logic and ui should be as separate as possible. A database system, to me, is something to store only, and that's it, it's like a container. If you want checks, why don't you put them into the input treatment code? and in the the writedb() too?
What about the case of a database that has people accessing it from two different applications. Would you re-implement your business logic in both places?
No, business logic should not be enforced in the UI at all. Business Logic needs to reside as close to the data as possible for the maximum level of data reliability. Otherwise an unscrupulous hacker could write his own application (or just use Toad) and modify the data to fit his whim. Imagine if an online retailer allowed for this kind of thing and someone changed the state of their order to "payment received" even though no payment existed in the collections portion of the database.
Admin
Yeah,
Thet whazz meee!
Youu dohn lihk ma speeling or my cooding?
<grin>
Admin
Way to climb Mt. Obvious.
Admin
I don't think it's all that weird. Indeed, at your leisure, perhaps while relaxing by a weir in the nearest river, take some time to seize a dictionary, wherein you will find many such heinous exceptions to what we laughingly call a "rule".
Admin
That's pronounced "wayrd."
--Rank
Admin
Ummm no - he means "U.S. English Programmers" as in English-speaking programmers in the United States.
Admin
And if he did a good job, we would never have heard of him. And, he wouldn't have been promated to a mangeler.
Admin
I find that between sqlite on the low and, and postgresql on the high end, there is no need for Access or MySql in my world.
Note that my world does not (currently anyway) scale up to enterprize where Oracle might be useful.
If you write your abstractions correctly (and there are a ton out there that do this for you), your can even make your application run with any of the above as a backend - though you will have to fiddle with the sql a little for each backup.
Admin
Nobody's talking about implementing the business logic in the UI. Ever heard of something called "three-tier architecture"? You have a middle tier between the UI and the DB that's mainly responsible for business logic, and all clients have to go through that layer. NOTHING except that middle tier and the DB admins get direct SQL-level access to the DB.
That's pretty much how all serious enterprise apps today are built, because the alternative is to do EVERYTHING that's important inside the DB, including access control. You want each single customer to get a user account on your DB? Because without a middle tier that would be the only way to make sure they can e.g. cancel orders, but only their own.
The DB vendors have tried hard to make it possible to have all business logic in the DB itself, and DB people tend to believe that's the only way to do it properly, but in the real world, PL/SQL is not a language in which you want to maintain a large codebase. Any why should you want to run more stuff than strictly necessary on DB servers you have to pay through the nose for on a per-MHz basis?
Admin
I had the horrific experience of maintaining a "system" like this. The experience I had was MUCH MUCH worse. The "application" we had seemed to have an MS access database for every possible scenario.
Every client had its own database. There were two offices in the country. One office refused to upgrade to Access 2002 so there were two versions of the "application," the previous being in Office 97. The "developer" also didn't feel the need to maintain identical code for both (i.e. all of the code that would have run in an Access 97 database would also run in 2000 but there were differences in almost every single routine for inexplicable reasons). There were probably 200 databases all cross-linked.
Both versions had a switchboard that was coded to, upon startup, cycle through the linked tables to the client databases and re-point them to tables in the client database for the one the user had selected. The "programmer" had felt that it was not necessary to do error handling, hence the "On Error Resume Next" which resulted in a fun experience where the users claimed that their notes on a particular entity in the system would vanish. Needless to say, occasionally this routine would get through half of the linked tables and refresh them and then totally crap out on the rest of them. This would result in half of the client tables being linked to the switchboard "application" for one client and the other half linked to whatever they were linked to before.
Every user had their own copy of the switchboard "application" regardless of where it was run. In one office every user had a copy installed on their desktop and a batch file in their startup folder on their computer to copy the newest copy of the "application" into their directory (meaning that there was no real way to guarantee that the newest version of the app with bug fixes would even get deployed). In the other office the monstrosity was deployed to a terminal server and copied the same way into the user's home directory. Being 200mb in size this "application" tended to eat a lot of disk space on this machine. Some of the client databases were over 1gb in size. You heard correctly, a gigabyte in an Access database. They went corrupt routinely and had to be repaired or restored from backup.
I had been hired to replace this horror with a brand new system. I was sent out to one of the offices to meet the creator/maintainer/necromancer that birthed this thing from the depths of hell so as to try and get an understanding of how it all "worked" (so I could replace it with something that actually did work). He quit on my second day of asking him questions but not before telling me a story about how he'd accidentally wiped out all of the primary keys in one of the client databases one day and that he'd managed to find enough pieces of unique information in each record to restore them from some previous backup copy he'd made of the database before anyone noticed.
I and the other two devs hired to replace the system inherited it and all of its problems in "maintenance mode" only until we could get the problems under control, they assured me. It generated its own problems routinely. There was no escaping it. Six months later they let me go (I was a contractor and I was already working on a 3 month extension), due to the fact that they were not doing very well financially for some funny reason, and that I'd clearly lost all motivation to work - nay - live.
No matter how much garbage I see posted on The Daily WTF I can honestly always say I've seen worse. I only hope that many of you never meet the "system" I am referring to. I'll offer condolances to your next of kin if you do.
Admin
pffff!!!!
You owe me a new keyboard! ROFL!
Admin
Yahoo runs almost ALL of their forward-facing databases on MySQL/FreeBSD. On the backed for various things they run lots of MS SQL Server because T-SQL is the lazy man's query language.
Admin
Someone probably came up to him and said:
"Hey there, Mr. Developer, we just need this one simple little program that does such-and-such...."
That's how they get me.
Admin
<font size="1" style="font-family: verdana;">
<font size="2">Okay, so the rule has a lot of exceptions. But fer chrissake, "receive" isn't one, and it's covered by the first part of the rule that everyone knows! (I omitted the remainder, "... and when sounding like A as in 'neighbor' and 'weigh'", which addresses "heinous" and a majority of other exceptions.) And I would wager a small amount that "receive" is the most common application of the first part of the rule, </font></font><font size="1" style="font-family: verdana;"><font size="2">which probably covers 90% or more of all cases</font></font><font size="1" style="font-family: verdana;"><font size="2"> anyway.
A friend of mine used to add a corollary: "I before E, unless it's weird". You could easily add your own extensions to help remember other exceptions. For example: "... and when sounding like 'eezhur' as in 'seizure' and 'leisure'". And I don't want to hear from any Brits about how we pronounce "leisure" wrong, either. "Colour", my arse. I mean ass.
</font></font>
Admin
I'll give you the download link where U can find Oracle Warez 4 fr33, dud3.
http://www.oracle.com/technology/software/products/database/oracle10g/index.html
You can easily download the Oracle database for free, if you need it for development. There will even be a free "express" edition, which may be used for productive use - but it's limited in terms of CPU, maximum database size, memory usage etc. (Just like similar offers from MS, Sybase)
Admin
Not that things were any better in non-capitalistic countries ...
Admin
And if you want to import good old fashioned text-files, wether csv or fixed, you have to use that ultra slick middle layer, right? Performance doesn't seem to be an issue in some organizations.
And that is so bad? I knwo of some serious enterprise apps, that's doing exactly that thing. Needless to say that there's also enterprise grade databases at work.
And I ask again, what's wrong with this approach? At least you can also implement things like fine grained acces control on the database layer and nobody (aside from the system/sys-account) can bypass that. The alternative? Everything in the middle-layer which will cost you performance at the very least.
From my point of view, they succeeded. And please, do tell, where PL/SQL ist not up to the task to maintain a large codebase?
Since you don't seem to have the slightest idea on the current license policies from the major database vendors, I rest my case.
l.
*oneofthoseDBpeople*
Admin
I beg your pardon? T-SQL is, if at all, a stored procedure language, and at that, not very good, compared to others.
l.
Admin
I didn't mean to say T-SQL was great, I meant to say that it's "EASY TO LEARN", which could by extension mean "DOESN'T DO A WHOLE LOT FOR YOU WHEN COMPARED TO THE SWISS ARMY KNIFE THAT IS PL/SQL". It's easier to do some things with SQL Server than MySQL so that's what they do.
However, I stand behind the assertions (mine and others) that Yahoo! uses mysql for just about everything -- I have several friends there and interviewed with them myself. It's an impressive setup, and a testament to the open-source model. FreeBSD with home-grown package mgmt, in-house builds of apache and mysql, scripting done primarily in PHP.
Admin
That would probably make 99% of all existing report tools obsolete. In fact, in many cases, read access to the DB is available to tools outside the middle tier.
What you are describing here is a web application, e.g. an online shop. I aggree that 3-tier is the right architecture for web applications.
But: not all "serious enterprise apps" are online shops. A warehouse management system is definitely a "serious enterprise app", but it has a limited number of users - mostly those people working in the warehouse - and it is definitely not a problem to give every user an account in the database.
But even if the users share one database account, why would that make it impossible to do access control? The way Oracle handles privileges, one can easily setup a system where normal users have only access to some stored procedures and read-only views. Those stored procedure do the updates that the users are not allowed to do themself. (Kind-of works like the s-bit for executables on unix systems). It's not difficult (although obviously kind of redundant) to let all users share the same database account and implement an control system with PL/SQL. The important point is: Even if the users can directly access the database with a tool like TOAD, they can not do anything more than they could through the normal user interface.
What is a "large codebase" for you? Do 300K lines of code count? PL/SQL is in my opinion at least not worse, probably even slightly better, to write business logic. Obviously it limits the scalablity of the system if everything is done in the DB. Obviously you sell your soul to Oracle if you depend heavily on PL/SQL. If you can live with that, it can deliver well working systems just like many other architectures.
There is some truth in this argument, but on the other hand, sometimes it simply doesn't matter. Eighter the system is small enough to still run on a single Server with 1-4 CPUs or money constraints are not that important. I think the performance penalty for running stored procedures instead of code in a middle tier is often not that much.
Business logic often looks like that (the following is not real-life code, just an example):
procedure bookTransaction(p_accountID number, p_amount number) is
v_balance number;
v_limit number;
begin
if amount<0 then
select balance, limit into v_balance, v_limit from account
where accountID = p_accountID;
if v_balance+p_amount<-v_limit then
raise limit_exceded;
end if;
end if;
update account set balance=balance+p_amount
where accountID = p_accountID;
insert into transactionlog ( transactionDate, accountId, amount) values ( sysdate, p_accountId, p_amount);
end;
In this example, most code is database access anyway. The few IFs hardly make a difference in terms of speed. On the other hand, in the stored procedure, it's easy and natural to select only those columns from the table that are needed. The persistence frameworks (like Hibernate) typically used in 3-tier apps probably select all columns, since they cannot guess which columns will be required by the application. This might cause a small performance penalty, especially when the middle tier is not on the same server, but connected through the network. Of course the middle tier can do some caching, but this is a bit like replicating the database's work.
Admin
Funnily enough that commercial database they used turned out to be a huge headache and they apparently went back to MySQL later.