Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

What databases are monks using?

by jptxs (Curate)
on Dec 17, 2000 at 07:39 UTC ( [id://47103]=perlmeditation: print w/replies, xml ) Need Help??

Searching through my pile of news and magazines, I was reading a lot about databases this weekend. I work with Oracle, but I try to keep up on events in the other DBs' worlds so I can see what's coming down the pipe or starting to catch up. It got me wondering about what the monks out there see. A few questions you can choose to answer just to see what's out there:

  • What database do you use most often? Is it because you want to or some other reason? Is there a particular reason it stands out for you if it's because you want to?
  • What database have you had the best/worst experience with? How and why?
  • This one's a bit tougher: Where do you draw the line between work the database should do and you should do? i.e. do you use a lot of stored procedures/database code or do you simply extract data and hack at it in perl? Why do you draw the line where you do?

I'll start. I use Oracle almost exclusively because it's what I know best hands down and it's free because my company develops Oracle tools and we probably have more licences through our partner program than employees working here. It's fast, efficient and has many many good interfaces and tools - mostly from us in the tools part =). I also would more than likely be in deep do-do if I didn't use it, but that's not really a problem.

My best experiences have been with Oracle, but, as you can guess from the last answer, the deck is stacked. I have had my share of bad experience with Oracle on Linux actually - installation was a nightmare b/f v816 and the OCI layer on Linux still isn't quite right. My worst experiences were with the antideluvian version of Informix (v6? it's been a while, i'm not sure which it was now). I had to use this at my job at a non-profit as it was all we had. Not fun, unsupported and down most of the time.

I draw the line differently depending on the project. If it can all be done in the DB and I just need perl to get it out to the web or collect the variables, I do it all in the DB. But I also have very little middle ground - which is bad more than likely. I don't try and mix too much. If the job cannot be done mostly in the DB I tend to just hack about in perl and use simple SQL instead of doing procedure calls. Bad habit, but true.

What are your thoughts?

"A man's maturity -- consists in having found again the seriousness one had as a child, at play." --Nietzsche

Replies are listed 'Best First'.
(kudra) Re: What databases are monks using?
by kudra (Vicar) on Dec 17, 2000 at 14:54 UTC
    I've had to work with mysql far more than I would ever choose to, because I've mostly worked for small companies, and that's what they usually have. At my last job they didn't have a database yet and the boss had no real preferences other than low cost/free, so we were able to get Postgres instead. At my current job they use mysql, but we're developing an application that needs foreign keys and atomic transactions, so I'll probably get something else to play with soon.

    Despite the fact that I like Postgres, some of my most frusterating experiences have involved Postgres and large objects.

Re: What databases are monks using?
by lhoward (Vicar) on Dec 17, 2000 at 18:51 UTC
    Lets see. I've used the following DB's with perl:
    • Oracle - because this is what was already there
    • Sybase - same reason
    • Solid - same reason
    • MS Sql Server - same reason
    • MySQL - because I wanted a free/fast DB
    • Postgres - because its free and has better Unicode support than MySQL.
    • Keyed files on a Tandem (like dbm files) using middleware that presented it as an ODBC data source - To get access to the data on this "legacy" platform from scripts running on a Unix box.
    IMHO, oracle takes too much work to admin. If you have a good DBA around to install/configure/feed it I guess my experiences with it would be better. Sybase is ok. Solid is cool because it takes almost 0 work to admin once it is set up. MySQL is one of my favorites, but I miss some features that it lacks like subqueries and views. Postgres is my current "favorite" because it gives me everything that MySQL does with more "cool features", but I've only been messing with it for 2 months so far. The tandem ODBC middleware product wasn't fun to work with, but it was great because of what it made possible: a web interface to a lecacy DB.

    One feature that I want in a DB that only some support (and this is kind of nit-picky): case sensitive table and column names. My OS and programming language are case sensitive, I think my DB should be the same way.

    I generally avoid stored procs or database specific features. The main reason for this is to make it easy to move from one DB to another easily. If I do use them I "hide" them behind a perl abstraction layer, so I can move the functionality from the stored proc to perl if I want to move to a DB that doesn't support stored procs (or that just handles them diffrently).

Re: What databases are monks using?
by 2501 (Pilgrim) on Dec 17, 2000 at 11:16 UTC
    I have found that in the Real World TM I never get a choice of the DB I get to use. The three main reasons being:
    1.) Monetary constraints. The basic thought on this one being "A database is a database Why use database X when we already own Y ?"
    2.) I jump into the project and it already has a DB.
    3.) A project leader asserts their authori-tae and makes the decision so that they could claim more responsibility for success.

    I do think this could be an interesting discussion. I am particularly interested in people who have worked with thunderstone. I haven't had a chance to use it but I have heard mixed reviews.
Re: What databases are monks using?
by turnstep (Parson) on Dec 17, 2000 at 20:39 UTC
    What database do you use most often? Is it because you want to or some other reason? Is there a particular reason it stands out for you if it's because you want to?

    I think most of use the database that our employer already has in place, and few have the luxury of "choosing" one. (Of course, you can choose where to work based in part on what database they have). I tend to "choose" mySQL for my personal needs because of it's price, but would probably use Oracle if money was not an issue.

    To answer the main question, I currently use Oracle and Sybase mostly, and greatly prefer Oracle for the most part. I disagree with one of the statements above however - I vastly prefer case-insensitive databases, particularly ones that I have not designed, because it's real easy to make a typo when saying something like:

    select Foo_Bar from ReceivableTransaction_Logs_Nightly

    (No, I wouldn't have come up with such horrendous naming conventions, but it's not often you work in a legacy-free workplace :)

    Besides, SQL isn't case sensitive, so why not make the database objects the same way. :)

Re: What databases are monks using?
by Hrunting (Pilgrim) on Dec 18, 2000 at 04:46 UTC
    Heh, here's a smattering of some DBases no one's mentioned:

    Conetic cBooks
    This is a database specifically designed for billing purposes. It's not relational. It's not SQL. It doesn't have anything written for DBI. You access contents of its tables using either some closed-source command-line programs they give you or through a grace language that is a bastard hybrid of Pascal and C. In short, it's not pretty. Our company has a module that we use that works as well as one could hope, but this database is my biggest reason for wanting to learn XS.

    DB2
    IBM's relational database is supposed to be as good as Oracle's server while being a lot cheaper. I actually don't have any experience with it, since we're currently deciding whether to go with it or Oracle. I'm personally leaning towards DB2 cause I'm fairly DB agnostic. I've been doing things with mySQL for years now that we really shouldn't be doing.

    FileMaker Pro
    May this never happen to me again.

      I've had a little DB2 experience and it's a solid DB. It has all the features you could ever want and *awesome* support. I've never used it with Perl, though so I'd be interested to know how well the DBI covers it. Of course, it goes without saying that I'm talking about DB2/UDB - which is the open system version. DB2 for OS/390 is by far the gold standard. Not even one open systems DB can touch its solid performance and reliability standards. But it's like Ferris says, "If you have the means, I highly recommend picking one up."

      "A man's maturity -- consists in having found again the seriousness one had as a child, at play." --Nietzsche
        I think DB2 was the first DB I used with perl. DBI, perl and DB2 so far has been my favorite. I have found that DB2 was much more "forgiving" in terms of dealing with a slow programmer then anything else I have encountered since then.
        I used it on a OS/400 system. The queries I was doing were intense. The data was spread between 2 tables and millions (literally) of records. I used quite a few multi tier queries (a query which subqueries, which subqueries, etc.) and it handled it like a champ. My only problem being determining the difference between DB performance and what was just raw hardware horsepower from the AS/400 I was using.
        either way...perl, DBI, and DB2 was a pleasure to work with.
      FileMaker Pro
      May this never happen to me again.

      I'll echo that one. My very first database experience was adapting an old FileMaker Pro database that one group at my college was using- that was one of the most frustrating jobs I've had. yuck.

      Thankfully, now I just use oracle at work. We have a lot of perl modules already written that are specific to what I work on, so it's super easy to use- I just need to call the right functions.

Re: What databases are monks using?
by extremely (Priest) on Dec 18, 2000 at 06:18 UTC
    I've used Sybase, MSSQL, MySQL, Postgres, and seemingly every .{1,2}?DBM in the fricking universe. Sybase has never failed me, tho I often failed it. MySQL is great till you advance past its abilities and for some projects it is indispensible. Prosgres is fast, free and has a bit more meat on the bone than MySQL. MSSQL I rate up there excepting only the first two letters. =) Just make sure you go though the options and turn off the MS weirdness modes.

    I also tend to do things in Perl that I should do in SQL. It pays off when you may switch DBs but it kills you when you start to spread out and have to keep copying rules and tests about. Put the rules in Modules and you wind up adding a million methods to the Module or turning it into a spaghetti-calling nightmare with all the logic hid from you.

    I suggest if it is time to fix your habits that you move the data correctness rules to the DB first. Don't get fancy, just setup case controls and ranges and such. Ease up on contorted tainting and start testing your inserts for failure. I was really pleased with myself for ust going that far =)

    --
    $you = new YOU;
    honk() if $you->love(perl)

Re: What databases are monks using?
by mothra (Hermit) on Dec 18, 2000 at 19:13 UTC

    What database do you use most often? Is it because you want to or some other reason? Is there a particular reason it stands out for you if it's because you want to?

    • SQL Server -- At my last job where I was the DBA this was the database they used. I hesitate to use the term "DBA" because there was very little work necessary to maintain the system.
    • Sybase -- At my current job, where I am not the DBA, but a Powerbuilder developer.

    Again, in both cases, the DB's were there before me so that's what I used. :)

    What database have you had the best/worst experience with? How and why?

    I'm not the DBA at my current job so I can't say how good/bad Sybase is to maintain, but SQL Server was definitely easy to manage at my last job (perhaps because it was a small company, with 3 programmers and the database was for internal use only, I dunno :).

    This one's a bit tougher: Where do you draw the line between work the database should do and you should do? i.e. do you use a lot of stored procedures/database code or do you simply extract data and hack at it in perl? Why do you draw the line where you do?

    This might be specific to working in Powerbuilder, but we like to do all of our SQL coding in stored procs, so that we don't have to recompile the app everytime we want to change a mistake we made in one of our queries!

Re: What databases are monks using?
by TStanley (Canon) on Dec 17, 2000 at 20:27 UTC
    Most of my experience has been with a database called Solid, which can be found at SolidTech.com. I have also worked some with Oracle and Informix.

    TStanley
    There can be only one!
Re: What databases are monks using?
by jepri (Parson) on Dec 18, 2000 at 04:19 UTC
    I've been forced to use Oracle without being the DBA, it's alright, but I have a tendency to do simple queries and pull the data to my program, where I then process it.

    When I have to set up and use databases I much prefer postgres since I find it the easiest free one to configure and admin.

    ____________________
    Jeremy
    I didn't believe in evil until I dated it.

Re: What databases are monks using?
by PsychoSpunk (Hermit) on Dec 18, 2000 at 10:52 UTC
    I use MS SQL Server 6.5 most often. But, I also use Oracle 8.0 and MS SQL Server 7.0. In one regard, I consider this highly fortunate since I get exposure to the differences in each DBMS, and in the communication layers of the DBI with each of them. In another, it makes it highly difficult to write optimized code for a single architecture (since I want the single piece of code to be able to do the same work with each type of database).

    My own worst experience is with MS SQL Server 7.0, as opposed to my more favorable experiences with 6.5. Oracle can be "difficult" at times. 7.0 gave me my worst experience when I first attempted to connect to it via the DBI. I ultimately ended up using DBD::ODBC, only because there were mitigating circumstances with the boxen I was using that would not work with DBD::Sybase / FreeTDS and a failed attempt with DBD::FreeTDS. This was done on site, which is two states away, and as such, hindered my overall progress but garnered me some support from within their IT department. Being unrelentless is very impressive to government employees, who (pardon my generalization if you are a government employee) tend to be unrelentless until 5:00 and then go to happy hour and only get back to being unrelentless around 8:00 the next morning.

    The database should do all the work it should do. How's that for vague? Seriously, I recall this discussion recently, but have neither the patience or the general clue where this was discussed. What seemed to be determined in this discussion is that certain tasks make more sense dependent on the purpose of the system. If your system is dealing with tables that are small, grab them all into a hash structure according to how you need them via the database (no conclusion was drawn on whether sorting made more sense in the DBMS or Perl). But if the system is dealing with large sets of data, then it is best to let the database do what it does (keep data in a fairly organized fashion) and only rely on Perl to do the immediate task.

    I guess I fall into the latter category, even with small systems. I consider myself a minimalist when it comes to coding, and so if I need X from the database, I don't grab Y for good measure. Grabbing Y is inherently not good measure if you only need X.

    The line is thus drawn based on the fact that my systems need speed as their overall bright point. I don't expect to have an accountant taking my money when I go to deposit my check in the bank. Likewise, I don't expect Perl to be worried about more data than is necessary. (slightly bad analogy, and I'm calling myself on it before anyone else can.) But my point is while Perl could handle it all, there's overhead in the duplication process. And while an accountant could do the job of a teller, it reduces the overall workload that s/he can perform without the added responsibility. So, in a sense, my code is only as good as the other programmers at work, since I have to trust that none of the other code they produce results in unnecessary overhead with duplication. That way the actual system running all of this code can be optimally performing its tasks and reflect that with all of the code that it runs.

    I'm going to get off of my soapbox now, and leave this as it is. I think I've given a fair enough look into how I approach the situation, and I am happy that this thread came up. ++ for jptxs for asking.

    ALL HAIL BRAK!!!

Re: What databases are monks using?
by agoth (Chaplain) on Dec 18, 2000 at 15:51 UTC
    • DB2 - on OS/390, headache to set up but good when going.
    • Oracle - by far the setup I prefer to use. speed, data dictionary, transactions and stored procedures
    • SQL Server - only when I have to
    • mySQL - when we have clients with tight budgets and non-serious systems
    Using stored procs and transaction/rollback wherever I can.
Re: What databases are monks using?
by MadraghRua (Vicar) on Dec 19, 2000 at 05:06 UTC
    As well as the above, I have also used AceDB - a scientific, pseudo-object database used by folk looking at worms, flies and other organisms. Lincoln Stein has written AcePerl modules to allow easy Perl scripting and interaction with the database. It can be a pain to learn as you get what you don't pay for, but is useful when you get used to it.

    MadraghRua
    yet another biologist hacking perl....

Re: What databases are monks using?
by royalanjr (Chaplain) on Dec 18, 2000 at 20:38 UTC
    I am using Postgres because that is what came with RedHat and I have heard it's pretty good. I am still at the learning stage with perl, so it is meeting my meager needs at the moment.

    Roy Alan

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://47103]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2024-03-29 00:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found