When I store information on my site I always use a flat-file database, and people keep telling me to use a proper database, but I actually have no idea where to start. I took a look at the already installed modules on my server, and I have AnyDBM_File, ODBM_File, NDBM_File, SDBM_File - but no idea how they work.
What should I do first, how do I learn how to use what I've got and what else do I need? (Please assume complete idiocy on my part and don't use too many long words or abbreviations) Thank you.
Re: Getting started with databases
by gmax (Abbot) on Oct 10, 2003 at 11:39 UTC
|
Learning databases is no trivial matter, despite what you may hear around.
There are three basic steps:
- Learning database theory.
- Learning SQL
- Learning database interface in your language of choice.
Database theory
There are several ways to learn database theory well. The best way is to attend a university course or a good course of comparable quality.
I see some hands rising, with predictable objections. "University courses are boring and full of negligible details that I would never need in real life."
Not so. Even if you perceive the course as abstract and unrelated to real life experience, database theory is one of the subjects that can have strict applicability to real cases. The problem is that it isn't easy to see the theory usefulness when examining small cases.
If you get acquainted with relational theory, and especially if you become used to apply relational concepts to real cases, then you'll see that you can understand the problems and find a solution.
Another method of learning database theory is through a good textbook. It depends on your attitude towards learning. There are people who can't grasp a concept from a book and need a good teacher in front of them, and there are people who can get the most complicated ideas from a book only.
Either way, here are a few titles that I can recommend:
As an alternative, there are several articles online. Check this address:
http://www.palslib.com/Fundamentals/Fundamentals.html.
I don't mean to discourage you. What you need to know to get yourself started is not that much. It would be enough to grasp the basic relational concepts of data separation, relations between entities, operations on data sets, and transactions.
The other important concepts ( referential integrity and normalization) are really needed only if you are designing your own database.
SQL
Once you understand enough database theory to know what you need to do with your data, then you must learn some Structured Query Language to communicate with your database.
There are several ANSI standards, but not all the database engine are fully compliant to the most recent ones. You can assume that if a DBMS claims that its SQL is ANSI-92 or ANSI-99 compliant they are worth a try.
Try to learn a neutral SQL first, before getting involved in a specific dialect. Some tutorials are available here and here.
Be careful of specific implementations from a database. Advanced features such as triggers, constraints, cursors, and stored procedures may differ substantially between different engines. Don't get dependent on a given features without checking that other engines support it as well.
Database interface
Finally, once you are confident that you can talk to a database and can get what you want, it's time to work on the programming interface.
For Perl, this means mostly learning the DBI. The original book is a bit outdated, but it is still a great source, which you can
integrate with the current documentation. Our Tutorials can give you a good start, but only if you know the basics first.
Update
A last piece of advice that maybe is the most important one. Whatever you decide to do, don't use any feature blindly, just because you've seen someone using it or because someone said it is "the right way." Try to understand WHY it's used, and only then add the feature to your bag of tricks.
HTH
| [reply] [d/l] |
|
| [reply] |
|
Nice overview. I was thinking about learning more about DBs and this looks like good advice.
Thanks, gmax!
| [reply] |
Re: Getting started with databases
by matthewb (Curate) on Oct 10, 2003 at 10:24 UTC
|
Sound advice above, but if you are just trying to find your feet I would suggest working through a DBI tutorial.
This one looks good, and should give you some idea of what is possible.
MB | [reply] |
Re: Getting started with databases
by Abigail-II (Bishop) on Oct 10, 2003 at 10:05 UTC
|
This isn't really a Perl question. If, and what kind of database you need, depends on the problem you want to solve,
and it's a largely language independent problem. If for instance I need to program a flight reserve system, I get
lots of records that need to be stored, retrieved quickly,
summaries need to be made, and it needs to be backupped
and be able to restored quickly. Then you need a relational
database (and a good one, not MySQL). If I just have some
key/value pairs that I want to remember from one invocation
of the program to another, one of the DBM files will do.
If I have some mostly static data, and I'm going to need most of the data each time the program is run, a flat file
will do fine.
So the answer is "it depends on your data". What structure
does it have, is it volatile, how do you want to search it,
how important is it, what's your backup solution, etc, etc.
Answer all these question, and then you can start looking
what database you need.
Abigail | [reply] |
Re: Getting started with databases
by zengargoyle (Deacon) on Oct 10, 2003 at 13:40 UTC
|
read the perldocs for the modules. these are nothing more than persistant (saved to disk) versions of the familiar hash with some extra restrictions.
quoth perdoc AnyDBM:
DBM Comparisons
Here's a partial table of features the different packages offer:
odbm ndbm sdbm gdbm bsd-db
---- ---- ---- ---- ------
Linkage comes w/ perl yes yes yes yes yes
Src comes w/ perl no no yes no no
Comes w/ many unix os yes yes no no no
Builds ok on !unix ? ? yes yes ?
Code Size ? ? small big big
Database Size ? ? small big? ok
Speed ? ? slow ok fast
FTPable no no yes yes yes
Easy to build N/A N/A yes yes ok
Size limits 1k 4k 1k none none
Byte-order independent no no no no yes
Licensing restrictions ? ? no yes no
the different dbm modules all work about the same, just have different file formats and limitations. AnyDBM is a wrapper to hide those differences from the programmer, it will let you use the same code to open an NDBM or SDBM or ODBM format file.
use Fcntl; # For O_RDWR, O_CREAT, etc.
use SDBM_File;
tie(%h, 'SDBM_File', 'filename', O_RDWR|O_CREAT, 0666)
or die "Couldn't tie SDBM file 'filename': $!; aborting";
# Now read and change the hash
$h{count} += 1;
print $h{count};
...
untie %h;
each time you run the code %h will be just like it was and you should see the counter go up.
DBM stores key => value pairs, the value has to be a scalar so:
$h{user} = "bob";
will work,
$h{prefs} = { color => 'red', font => 'large' };
won't work. there's a MLDBM module that will help you accomplish the second, or you can use Data::Dumper or YAML or FreezeThaw to convert your data structure into a string (that you store in the DBM, fetch later and unpack before using).
so a DBM is a simple key => value store that you can search/fetch by the key only. a RDBMS/SQL/Real Database
is a lot like a DBM with multiple values, and every value can be a key. the data is arranged in tuples (think ARRAY) but they're regular and have names for each position so you can think of them as HASHes. there are multiple tuples per table and multiple tables per database and multiple databases per server ...
server: foo
database: stuff
table: users
|name|age|sex|location|
|bob|12|M|Nuke York|
|mary|22|F|Lost Angles|
table: prefs
|name|color|font|
|bob|blue|large|
|mary|blue|tiny|
now you query with SQL which is almost understandable...
SELECT name FROM prefs WHERE font = 'tiny';
|name|
|mary|
SELECT name, color, sex FROM users, prefs
WHERE color = 'blue';
|name|color|sex|
|bob|blue|M|
|mary|blue|F|
you would likely get that second bit back in perl as something like:
$result => [
{
name => 'bob',
color => 'blue',
sex => 'M'
},
{
name => 'mary',
color => 'blue',
sex => 'F'
}
];
if the DBM provides enough funcionality for your purpose use it. if not install a simple SQL database like SQLite and work through the examples. then go an read some of the fine tutorials and/or books people are sure to mention. | [reply] [d/l] [select] |
|
| [reply] |
|
yes, i think it will make sense to katgirl. katgirl has a 'site' and currently stores data in a flat-file. that implies at least a bit of knowledge about CGI, reading/writing files, and storage of persistant data. definately above the threshold for understanding that a magical 'tie' can link a HASH to a file automatically.
not only that, but there wasn't another response that actually answered the OP question as to what those DBM modules that are already installed actually do. database class this, read these books, plan requirements, ... made everything sound so hard.
though i will admit to answering the question because katgirl is of the feline persuasion.
| [reply] |
|
I think so, yes.
zengargoyle says these are nothing more than persistant (saved to disk) versions of the familiar hash with some extra restrictions,
and I know for a fact katgirl knows what a hash is :)
MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!" | I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README). | ** The third rule of perl club is a statement of fact: pod is sexy. |
| [reply] |
Re: Getting started with databases
by jdtoronto (Prior) on Oct 10, 2003 at 16:04 UTC
|
I'm with Abigail-II, you dont 'need' a database if you can do what you need effectively and efficiently without one.
But hey, sooner or later you are going to come across a job where you will need one, so why not devise a simple project, or part of a project, and experiment. The tutorials in the Monastery are great.
Try things like DBD::CSV if yo ucan have DBI on the machine.
jdtoronto | [reply] |
|
Try things like DBD::CSV if you can have DBI on the machine.
Of course it all depends on the data you're using (like stated above), but when you want to go for DBD::CSV, I would like to advise looking at DBD::SQLite aswell. For my little project with only several hundred lines of data, I found an impressive speed increase from switching from DBD::CSV to DBD:SQLite. But then again ... maybe I'm just a really lousy DBA ;-)
--
B10m
EDIT: Removed shamefull plug for my website ...
| [reply] |
|
| [reply] [d/l] |
Re: Getting started with databases
by Cody Pendant (Prior) on Oct 12, 2003 at 04:56 UTC
|
Maybe what would be useful is you can give some examples of tasks you perform with your flat files, and which ones prompt people to tell you to use a proper database.
They may well be wrong, of course.
($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
=~y~b-v~a-z~s; print
| [reply] [d/l] |
|
|