Another issue is always what rdbms is available and well known.

I hope, you understand my english, too :-)

Best regards,
perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

I wouldn't say that "a really big amount of data" is the only reason to use Oracle:
1. Scalability/Performance - 9i can use clusters better than any of the others
2. Scalability/Number of Users - every version from 7.0 up can use MTS (and more in newer versions) for servicing extreme numbers of users (10K's)
3. Reliability - 9i (or even 8i) almost completely transparent (ie no need to reconnect) failover
4. Hardware independence - everything from a Win95 (or if you really know what you're doing ... DOS) to Mainframes via virtaully every flavour of *nix can support an Oracle database
5. Features - stored procs in PL/SQL and Java, pre-compiled SQL (aka cached queries) and PL/SQL (in 9i), Views, snapshots, full text indexing, web based administration, triggers (on startup, login and shutdown not just insert/update/delete), full audit facilities, security (built in encryption for starters)
6. Metalinks/Technet - a *useful* knowledgebase
7. and it ships with Perl, Apache and mod_perl :)

I don't work for Oracle, and the phone support is pretty much summed up as "upgrade!", but with everything from Personal Oracle Lite to Enterprise Edition freely available for download it's very easy to get started.

Just my 2p worth :)

rdfield

I think what you need to do is ask: What is the right database for me to use?

Perl has drivers that allow it to connect with MySQL, MSQL, ODBC and many others. You are using DBI and DBD to connect, after that, the type of DB you are using is irrelavent from perl's standpoint.

From here, concentrate on the database for your needs.

Do you need a transactional DB?
Do you need a relational DB?
Do you need speed over functions?
Is price an issue?
What do most of your clients use?
What is the size of the company or job?
Will you DB be heavily used to monetary transactions?
What OS does the DB support?

I have been using MySQL with perl for almost a year. I have also used perl to access ODBC databases and perl does the job well. I think you may be asking the wrong question.

I use MySQL for the following reasons:

-It is free for me and cheap for my clients.
-It is easy to install and easy to manage.
-It works on many platforms, but works very well on linux.
-It is very fast.

Oracle is great for large companies and has functions for pretty well anything you could want. However, you will pay for this with dollars and speed. It is transactional and fits in very well for e-commerce situations where losing a transaction or corrupting a transaction can prove costly.

MySQL works well for non-transaction based applications and in small to mid sized companies. It is fast and designed to be so. It has few functions compared to ORacle. MySQl is cheap... I could go on, but you need to determine what you need and then create a proposal table to see which DB scores the best

Simon

Why isn't MySQL good for transactions? I've not worked with any databases, but would like to get into it. Only reason would be for e-commerce solutions.
And if MySQL isn't good for transactions, is PostgreSQL?
Just wanting to get a feel of what database system I should start fiddling around with when I get the free time to do so.

Thanks,
Avi

If you need advanced features, I would also recommend PostgreSQL. It's open source, reasonably well-known, and contains a lot of the standard features (stored procedures, views, triggers, etc.) Perl support is very good. However, my experience is that it doesn't necessarily deal well with huge data sets .. for that you probably want Oracle.

One thing to consider if you're a major Perl geek: MySQL either has or is developing a "native" programming language (similar to PL/SQL or PL/PGSQL) but based on Perl. I haven't worked with this myself, so I can't vouch for it, but it's a data point to consider.

Hope this helps.

PostgreSQL has PL/Perl: Perl Procedural Language

Of course it seems unbiased, if you're using it as a source of information. But, it states things that are widely disputed in the One True Free Database War. My only point is, there is so much BS on the topic published by all involved parties, that the only opinion anybody should regard as unbiased is their own. (assuming they're non-partisan...)
--
Snazzy tagline here

# invoke with <script_name> /var/lib/mysql/your_database
chdir $ARGV[0]
@names = <*.*>;

foreach (@names) {
        my $t = $_;
        if ($t =~ s/\.my([id])/\.MY\U$1/) {
                rename($_,$t);
        }
}
[download]

I know this is getting OT, but how does MS SQL Server 2000 (or 7, for that matter) have limited datatypes?

Chris
M-x auto-bs-mode

My issue was with the lack of more date time datatypes out of the box. I know you can create your own datatypes. I must admit I didn't get indepth into MS SQL 2000, I am sure it is a wonderful product.

Well, you'll probably be using DBI, so it takes care of all the different 'interactions.' (DBI is a generic database tool that handles all the complex stuff between your code and the DB ... search for it on CPAN).

MySQL and PostgreSQL are Open Source databases (so, they're free). MySQL is fast and PostgreSQL is powerful. Most folks that I've talked/chatted/emailed recommend PostgreSQL.

I'd stay away from msSQL, Access and Oracle becasue of the price. PostgreSQL should do what you need it to.

Check out this link: http://www.commandprompt.com/ppbook/

It's the O'Reilly PostgreSQL book online (it's a legal copy by the authors ... part of the Open Publication License).

Hope this helps!

PostgreSQL supports an interpretted language called PL/Perl for writing stored procedures. What this is, is a perl interpretter running inside the plperl module, running the stored Perl inside a Safe.pm sandbox. The nice thing is, it's all open source, so you just edit the PL/Perl source to add functions to what can be used in the sandbox. Then, you can let the database itself dispatch your code for you. This is nice for a few reasons:

• It moves database related processes off of your web/other servers, and places them on your database server. There is no good reason for large database accesses to slow down a website if you have seperate servers for them, but this is often the case.
• It allows you to change your Perl database code without touching the clients. So if you have 34 servers accessing your database server, you only change the code in 1 place. (unless you're changing the agreed schema)
• It allows for a higher level of specialization for each piece of code, encouraging the practice of building a system out of small bricks with well defined tasks.

The oracle databases i've used are in the order of 1Tb with several tables of 55m+ rows. The main thing you really have to be careful of is the way you construct your queries, rather than how you execute them via perl. Make sure you make use of explain plan to tune your queries, especially if your data is "worthy".

The main down side with Oracle is the damn cost. Its *really* expensive. Cost aside, it does scale very well, there are lots of resources out there for it, from skilled professionals, to tutorials, to a very active community.

After a little research, I chose postgres over mysql maily because of the transaction based approach. I use it on my desktop for some web stuff, information management and generally small stuff. I've also used postgres as a base to develop some Oracle web apps. Some of the stuff requires some small changes (ie the date functions, O=sysdate P=now(), and the table creation stuff wrt specific datatypes, constraint syntax et al)... All very minor stuff.

Postgres as survived being thrown across harddisks, disk failures and reinstallations, without losing a byte of data. I must say I treat it pretty badly, and has survived very well. Given the stability and the cost (free) it was a real boon for me to get/use and learn.

As most people have already said, your question could be better rephrased as, "Which database is the best for me?". The only thing I say, would be it must be SQL 92 complient (or use it as a base). I say this becuase, you dont want to go down the road of learning (or inventing) some vendor specifc language (if you choose an RDBMS)

I dont know if anyone has mentioned OLAP databases here yet. There are a couple out there, Oracle Express, Essbase, however i'm not sure of their interfacability to perl. I've mentioned them because they work really well with specific types of data. For example, i've used Essbase for financial type data (management and financial accounting). Altho' i didnt use perl, they suited the data perfectly.

I guess the point is keep an open mind. Perl/RDBMS may not be the answer to your problem.

#!/usr/bin/perl
use Data::Dumper; #Use the Data::Dumper module
use Fcntl ':flock'; #Need the flock module also

&dbfile("open", "database"); #open database for reading & writing

#You can do any data struture you want with hash %db
$db{'hash'} = "hash_value";
push @{$db{'array'}}, "array_value";

&dbfile("close", "database"); #save and close database

#If this is a CGI script, uncomment the following line:
#print "Content-type: text/html\n\n";

&dbfile("open", "database");
print "Hey look at the value in \$db{'hash'}: $db{'hash'} \n";
print "And what's in \$db{'array'}[0]?: $db{'array'}[0]\n";
&dbfile("close", "database");

#Sub - opens/closes databases
sub dbfile {
if ($_[0] eq "open") { #open a database
open LOCKFILE, ">>lock.lck" or die "Error! Could not lock database!";
flock LOCKFILE, LOCK_EX or die "Error! Could not lock database!";
do "$_[1].dat";
}

if ($_[0] eq "close") { #save & close database
$Data::Dumper::Purity = 1;
$Data::Dumper::Indent = 0;
open FILE, ">$_[1].dat";
print FILE Data::Dumper->Dump([\%db], ['*db']);
close FILE;
close LOCKFILE;
%db = ();
}

#mmm... not opening database or closing.
#What are you trying to do?!
if ($_[0] ne "open" && $_[0] ne "close") { die "Error! Wrong parameter
+ passed to db_file()!"; }
}
[download]

It is missing some stuff such as strict, making sure you don't open 2 databases, try to close a database that does not exist, or even making sure that you save to the same database that you opened (though this is not really a bug; it would allow you to copy a database), etc. But it gives you a general idea, so I figure it is a good enough of an example.

Also, here is the contents of file "database.dat" from my example. You'll notice I have taken out indentation to get rid of the extra space needed for all the spacing.:

%db = ('array' => ['array_value'],'hash' => 'hash_value');
[download]

If you don't have or can't install one of the relational systems discussed elsewhere here, you might instead look at DBD::SQLite, which includes a lightweight SQL database in the distribution. It's extremely fast and works great as long as you're not using it in a simultaneous read/write multiuser fashion, It also provides an upgrade path to a relational system if you decide to upgrade.

Chris
M-x auto-bs-mode

What everone has stated above are valid issues...it's WHAT you want to do with it that is important.

If you are just looking for something that reads data from a flat file or even a custom file structure, DBI with DBD::AnyData works great (and is free). It has support for using user-defined FORMATs to access various types of file structures: CVS, Pipe, Tab, Fixed, HTML Table, MP3, Weblog, Password, etc. AnyData also support custom FORMATs, if you wanted to go that far. You can create a new FORMAT and AnyData could access the data structure based on the info in the format file.

Speed-wise I am not too sure about it. Most of my data bases are less than 1000 records in size so speed is not a BIG issue yet ;) I'm in the process of timing outputs from AnyData and MySQL...I'll try to post my results when I'm done.

======================
Sean Shrum
http://www.shrum.net

(Added with slight edits)
> tell me some good and bad facts about databases

• MySQL: Fast, free, nice tools / Toy Database
• M$ SQL Server: Hard Core, nice tools, cheaper than Oracle / Evil, only on NT
• Access: Easy to use, comes with Office / crippled by M$, evil, only on Windows
• Oracle: Can handle anything, looks great on the resume / Fat, bloated, ancient code base, Expensive as hell, pain in the ass to admin, not FreeBSD friendly
• PostgreSQL: Fast, free, Real RDBMS / Designed for Unixesque systems, hard to find Hosting

()-()                                                      ()-()
 \"/    DON'T   BLAME   ME,   I  VOTED   FOR    PACO!       \"/
  `                                                          ` 

Gee, I think we can detect which way you're bias inclines you without much difficulty.

For my 2 centidollars, I use MySQL on Linux with much success. I have found it to be very fast, even with large datasets, although my idea of large is much smaller than some of these TB cowboys. I mostly work in lowly GB. ;-)

---

"Non sequitur. Your facts are un-coordinated." - Nomad

My comments concerning MySQL stem from a few things:
• Experience working with High Traffic sites that had some painful crash and burns using MySQL. (Back your ass up, baby!)
• The fact that MySQL cuts a few RDBMS corners that I consider important in order to get a speed boost.

I think most of my fellow commentors got the point, there is no good and bad as such at least in software development, it depends almost entirely on the context you want to use the database. For the "persistent storage" relevant context of your application you can formulate your requirements to a "database management system". My fellow commentors have outlined many possible requirements to "persistency": efficiency, cost, standardization, host-language integration, portability, current access, volumnes, data integrety upon failure, atomicity of transactions with the database, what you actually want to do with the storage, why are storing it anyway, what are your query requirements etc.

Even you say for example Oracle, you will have formulate your requirements, since many database management systems cover a wide range of application profiles and therefore need/can to be customized to your specific requirements. This often not even the easiest thing to do.

One requirement is also how and with which language(s) you want to access the "persistent storage". Since your have written to Perl Monks, i assume you want to access the "persistent storage" with Perl. Here you have also a wide range of options, all very valid applied in the "right" context.

You have "perl managed persistency options" for "streaming" perl data, like FreezeThaw, Data::Dumper and Storable. All great for what they do.

You have "database interfaces" for perl for various different database technologies, relational and others:

You can write your own "persistency option" with good old files:

All most probably great options depending on the context, i would not want evaluate, without knowing your requirements .....i am aware that these are not so much "database" options, but options accessing "persistent" storage, but maybe that input will also help to find the "right" database

Hopefully you can provide more info about your application, or you already know which database is best given that it works with Perl..

One note I should make about Mysql and PostgreSql. Yahoo runs on Mysql; it is not a toy database. Some people think it is a "toy" because it was not originally ACID compliant, however 1) it is a very cost-effective way to do lots of reads, and 2) there are now one or more table types which handle transactions. PostgreSql is (for me) harder to use however it feels like a cheap Oracle, meant in a good sense; it has very nice advanced features and it has in the past year or so become much more stable and much faster. Mysql and PostgreSql are in a war about stats and also the code of each is continually improving, for example Mysql's transaction support and also replication is new.

They have different philosophies and may be better for you depending on your needs. But which free db you pick doesn't really matter as far as Perl is concerned. Many people use Mysql or PostreSql it seems. Unless you already know that you must use Oracle, I'd stay away from it since there is quite a lot of learning and expense involved. MS SQL seems quite nice if you are a Windows-only house, however I am very unimpressed with the way MS dealt with a bug in it originally (the one that lost nuclear data), and with Microsoft in general. I'm not saying they're evil.. but I think if you happened to come across some kind of a bug you would get it fixed more quickly with some other group, or you might be able to fix it yourself or hire someone to do so, since you have the source code.

Finally, if you stay away from using db-specific features, you can use DBI (or maybe DBIx) to abstract data from the database you use. Theoretically this means you can switch databases painlessly. All I know is I once switched from MySql to Oracle and it wasn't painless! ;)

Anyway, databases are a big subject but I probably would recommend keeping things simple so that you can change databases some time if you like, and (assuming you are not a db professional) start with something that combines a lot of power with little effort like Mysql. Or PostgreSql if you want, many programmers find great satisfaction and fulfillment in it. Good luck!

A caveat on 'works perfectly with Perl', MS Access will only work with Perl on Win32 via DBD::ODBC or Win32::ODBC - there is, as far as I know, no way to use Access databases on Unix - you can of course use DBD::Proxy to a small server proxy on a windows machine if you really must.

/J\

You've probably received more comments than you want or need.  However, I will call your attention to a recent article in e-week (part of Ziff-Davis and connected to PC Magazine) comparing the top database programs:  Oracle, MS SQL, mySQL, and a few others.  Oracle and mySQL seemed to do the best when compared on performance, and when you consider the basically free price of mySQL, it did the best.  However, there were some differences that are worth knowing that don't necessarily make any one database the absolute best, as many Perl Monks have indicated above.

This e-week article was based on the results of an extensive comparison that they did amongst these databases.  They pushed the databases to extreme levels: for instance, they tested them for four weeks, retrieving something like up to 600 pages of data per minute for eight continuous hours per test to see if each database could hold up and to see how many pages of data they could retrieve per minute.

It's not a long article, but the process they went through and the results of their intensive testing is quite amazing.  It's a good background article for anyone who works with databases to read.

That's Spenser, with an "s" like the detective.

Most of the comments are right. Define your needs, fit the definition with the tool that fits best.

However, there is one thing that is mostly missing from almost all of the posts that ought to be very heavily emphasized

In most cases, when you first create your application, stick to only ANSI standard SQL. Stay away from the proprietary stuff from the database. Unless you want the pain of changing over at some time in the future (or the pain of being stuck with what you have). There are situations where this is the wrong path, but those situations tend to be self-evident (like must use Brand A ERP in same db engine, or your DBAs are incapable of handling a simple DB)

I normally start with a mysql database (its free, runs great and provides most everything a project needs) unless transactions were required (though that may no longer be a problem since transactions are included in some MySql table types). Then we do the project. Lower entry cost is a wonderful thing. If the needs of the project change in the future (never seen an exception unless the project dies), then you can reevaluate the situation and switch. If you have coded using non-standard stuff, then you will have big issues.

Now, I use MySql because it is what I am most comfortable with, but PostgreSQL does a darn good job as well. I just am not as experienced with it.

So unless you have a standard in company to work with (as mentioned above), I start with a freebie and build a project from there to minimize costs. Once a project starts showing its value, then I might look at a non-free db if there are issues that are not being handled.

I wish you luck!