Just a few minor tips, from someone who picked up DBI
and MySQL earlier this year and is starting to get
comfortable with it...
- For any given database, you're going to have code in
various places that needs to get a database handle,
so write yourself a function that connects to the
database and returns a handle. Throw this function
in your own module (if you're comfortable with
modules) or just in a db.pl that you can
include
(if you want to learn module-writing another time).
This consolidates in one place not just the connect
itself but also the various config info for the db
in question (the MySQL username and so on). You'll
save yourself a lot of copying and pasting.
- Some of your code will call that function directly
and use the handle to do various special things
in SQL directly (create tables, get
a read-lock for backup purposes, do a special
query, whatever), but *most* of the time you will
be doing one of four things: retrieving a specific
record, adding a record, updating a record, or
getting a list of records that match a certain
value in a certain field. So write functions
that do these four things, and throw them in your
module or include file. This will save you yet more
copying and pasting. Write these functions in
such a way that they don't have to be modified to
work with different tables, different databases.
- Give every table in your database an id field that's
NOT NULL PRIMARY KEY AUTO_INCREMENT so that you can
pass this id around and know that it uniquely
identifies a specific record within the table.
Your function for getting a specific record can
take this id number and the table name
and return the record as a hashref (if you are
reasonably comfortable with references) or just
as a hash (if you want to deal with learning about
references later). The function that creates a new
record should assign NULL to this field so that it
will be assigned automatically. DBD::mysql provides
a way for you to get the id number that was assigned
(see the documentation on search.cpan.org),
so your function can return that number if desired.
Call this id field by the same name ("id" will do
nicely) for every table, and save your memory for
other stuff.
- Your tables will change. You will add fields to them.
I learned this the hard way. You do NOT want to
change all your code every time you do that. (Trust
me on this.) So, don't hardcode lists of fields,
like I did at first. That way lies frustration.
Instead, pass your records around as hashrefs (or as
hashes, if you aren't comfortable with references
just yet), and that way the code that's getting or
setting one field doesn't have to concern itself
with what other fields there are.
$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}}
split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/