Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Choosing a database for a new project

by newrisedesigns (Curate)
on Oct 15, 2002 at 20:26 UTC ( #205502=perlquestion: print w/replies, xml ) Need Help??

newrisedesigns has asked for the wisdom of the Perl Monks concerning the following question:

I began a side project this past week which involves taking pictures of my college campus and having the pictures online.

I plan on having a lot (at least 1000) and would like to have some sort of search method on the pictures. I'd like to use some sort of database, either mySQL or Berkeley_DB (both of which are on my webserver) that can be easily searched using one or more criteria to return relevant photos.

My table looks like this (although more columns may be added):

ID Number Filename Description Location Date Time of Day Weather Int/Ext
1 pdrm0001.jpg Shot of Bozorth Hall entrance Bozorth Hall 2002-09-15 Afternoon Cloudy Exterior

What methods could I use to store this information, and have it easily accessible to the public through a CGI script?

I have two ideas currently, and would welcome any other data storage suggestions

One: Use mySQL to construct a database with the following information.

  • I have 25MB of SQL storage on the webserver
  • Easily updateable, searchable
  • Reliable, wouldn't be a homerolled solution
  • I have absolutely no idea how to start the thing, and know just enough to search the database (any suggestions for a good Perl/SQL tutorial?)
  • Not sure how easy I could back it up, or modify it on my machine and upload it

Two: Use a few Berkeley_DBs to make each category. (One DB called Weather.db, which has key value pairs of ID_Number => Weather_Condition)

  • I could start this today and get it mostly done without needing to learn something new
  • I could set it up on my machine and upload it after I know it works
  • Could easily get out of hand
  • All information is not in one place

I would really like to learn mySQL, however I'd like this project to get off the ground soon. I might not have 1000 pictures yet, but I'd like people to see the work in progress.

Any suggestions or votes on which why this should be done would be most greatly appreciated.

John J Reiser

Replies are listed 'Best First'.
Re: Choosing a database for a new project
by fruiture (Curate) on Oct 15, 2002 at 20:42 UTC

    My recommendation: use a real SQL database if it's available. You'll need to write some administration scripts for easy manipulating the database, but that's not too hard (Maybe only submitting raw SQL Queries to the database).

    Working with databases is extremely easy with Perl, thanks to the DBI. The DBI manpage is at least a good tutorial on the module only. I've read "Programming the Perl DBI", so I'm unable to give you other ressources, for i've never needed any apart from the book and the manpage...

    Some more Pros for mySQL:

    • fast
    • extensible
    • Learning SQL is good and you're likely to need it sometime, so learn it now...
    • DBI is a powerfull tool. Learn to know it now and you'll be able to use it in future projects.
    • The time you spend now learning the stuff will be spared as soon as you need the features a flat BerkeleyDB can't give you.

      When I was hired for my first Perl job, I only knew what SQL looked like -- not how to use it. Within a couple of days, I was up to speed enough to pull reports from Oracle, MySQL, and that other proprietary database, all using Perl's DBI. The SQL language is very straightforward, making it possible for absolute novices to get things done now and save the subtleties for later.

      Wasn't it Larry himself who said, "What you don't know won't hurt you... much?"

Re: Choosing a database for a new project
by dws (Chancellor) on Oct 15, 2002 at 20:39 UTC
    I plan on having a lot (at least 1000) and would like to have some sort of search method on the pictures. I'd like to use some sort of database, either mySQL or Berkeley_DB (both of which are on my webserver) that can be easily searched using one or more criteria to return relevant photos.

    Unless you need experience using a database more than you need "soon", you'll be better off using a flat file. One thousand records isn't very much (if you have about 80 characters of data per line, that's only 80K). The overhead of searching a relatively small flat file is going to be a lot less than the overhead imposed by a database.

    I would go simple at first. Use a comma-delimited file, and get at it through DBI via DBD::CSV (or some other DBD:: that supports text files). This will give you the programmatic experience of using a database. Then slip MySQL in undereath at a later date.

Re: Choosing a database for a new project
by perrin (Chancellor) on Oct 15, 2002 at 20:32 UTC
    BerkeleyDB has great performance and is very easy to use, but when you want to search multiple criteria that really points to a SQL database. I'd say go ahead and learn MySQL. It's worth knowing.
Re: Choosing a database for a new project
by penguinfuz (Pilgrim) on Oct 16, 2002 at 02:04 UTC
    Your project sounds quite interesting, and the database structure you have listed looks like a very good start. I would suggest adding a field for the filename location as well, which should be especially helpful If you're building dynamic pages. While taking a flat-file approach may get you "off the ground" quickly, at some point your image collection will most likely benefit from a proper db backend, IMHO.

    Easily updateable...
    Like dropping new images into a directory and letting the database be automatically updated? Some information would be easy to auto-update, like ID Number, Filename, and Filename Location, then maybe an email reminder that you have new images to attend to, which could offer a link to a simple web form showing only the new pictures with input boxes for the Description, Location, etc... After you've taken care of that work, the images will be viewable in the wild.

    Another point of automation is with page generation. I would suggest putting as much logic as possible into the database structure, so the CGI that builds the pages has less work to do. Putting the 'brains' in the database also allows you to easily modify the storage structure without worrying too much about your scripts. Consider the difference between a script that queries your database for hard coded field names, and another which first queries for all field names (and virtually any other structual information) and works from there, for example.

    A great source of information for this type of stuff is the book "MySQL and Perl for the Web". Authored by the same guy who wrote other great MySQL books!

    Not sure how easy I could back it up...
    Very easy with MySQL... A quick and dirty script to backup your individual databases is here.

    I am very keen to know how this project progresses. Cheers.
Re: Choosing a database for a new project
by rozallin (Curate) on Oct 15, 2002 at 21:05 UTC
    I'm learning MySQL myself and though I haven't done anything as relatively complex as your project and have stuck more with PHP than Perl, I feel nevertheless that you'd find the following resources helpful:
    • man DBI - Perl DBI Interface tutorial; a good introduction to the generic database interface.
    • The MySQL Documentation Index has the MySQL Manual as well as links to articles that provides a pretty good introduction and foundation for beginners of MySQL.
    I'm sure other monks can suggest good resources, and I wish you good luck with learning MySQL. Have fun!

    -- Rozallin J. Thompson
    The Webmistress who doesn't hesitate to use strict;

A MySQL Book Recommendation
by Kozz (Friar) on Oct 15, 2002 at 21:51 UTC
    If you're keen on learning MySQL with perl, you could do worse than to check out MySQL, author Paul DuBois, published by New Riders.

    I've got the book, and use it as a reference nearly every day. I've found it invaluable in teaching me the MySQL basics as well as serving as a reference for those functions / operations I don't use very often.

    It's got a chapter on the APIs for C, PHP, and Perl. Great book. Did I mention I like it? ;)

      I'll second that. An awesome book, head and shoulders above the rest.
Re: Choosing a database for a new project
by mojotoad (Monsignor) on Oct 15, 2002 at 21:43 UTC
    Why not classify the photos under arbitrary "qualities". Location, weather, etc., would be examples of such qualities. Others might include the names of people present in the photo, architectural style, trivia, history, odors, film speed, camera type, whatever.

    By making the possible qualities arbitrary, you can add new categories as you go along, perhaps categories you had not considered. Then you have the luxury of executing ranked searches, such as "Chemistry Building, Outdoors, 2002, Summer" with leading qualities carrying more weight in the search/sort.

    Once you figure all that out, then you can put a beautiful front-end on the thing for web browsing.

    And then I can copy it because you open sourced it! I have a ton of photos I need to organize in this way.

    Like how that works? ;-)


Re: Choosing a database for a new project
by runrig (Abbot) on Oct 15, 2002 at 20:49 UTC
Re: Choosing a database for a new project
by clairudjinn (Beadle) on Oct 16, 2002 at 04:31 UTC
    I would opt for keeping things simple until they need to be complex; in the meantime you've got a working system and can be educating yourself on a more complex build. On a related tangent, I recommend O'Reilly's "Programming the Perl DBI". It covers everything from simple flatfile databases to SQL-based interfaces (and when you would use what) with a lot of examples high-lighting very useful methods and idioms, as well as what amounts to a decent primer in SQL strewn throughout. Luck, clairudjinn
Re: Choosing a database for a new project
by BoyPlankton (Novice) on Oct 16, 2002 at 03:19 UTC
    If you decide to go down the DB File path, one option that you can definitely consider would be using the MLDBM module. The MLDBM module lets you store a multi-level hash structure in a single level tied hash.

    Basically it would eliminate the need to store everything in seperate DB Files. Of course, the tradeoff is that it becomes really slow with large datasets. However, I think that 1000 records would be small enough.

Re: Choosing a database for a new project
by tachyon (Chancellor) on Oct 16, 2002 at 22:47 UTC

    Mark Jason Dominus has an excellent tutorial on Perl, SQL and DBI at A Short Guide to DBI




Re: Choosing a database for a new project
by Ryszard (Priest) on Oct 16, 2002 at 09:43 UTC
    I've got something that is pretty much exactly what you want here without any kind of RDBMS backend.

    While I didnt write the core functionality, i use it and it works really well just using the FS. The application was written only using core modules so it is easily transportable. It was written extensible enuff to add new object types easily (ie document, photo, mp3, others). You can add all your fields to each leaf node.

    Its also got a fully indexed search engine which uses a hash it brings up off the disk (not a tied hash). Its also smart enuff, so when creating the index it does an incremental build.

    I guess what i'm saying is, you may not really need a database.. :-)

Re: Choosing a database for a new project
by Aristotle (Chancellor) on Oct 16, 2002 at 14:26 UTC
    While no reply to your question, you will most certainly be interested to know about GIFT - the GNU Image Finding Tool. It's a pretty advanced project, take a look.

    Makeshifts last the longest.

Re: Choosing a database for a new project
by joe++ (Friar) on Oct 16, 2002 at 15:28 UTC
    I have absolutely no idea how to start the thing, and know just enough to search the database (any suggestions for a good Perl/SQL tutorial?)
    Take a look at Introduction to Structured Query Language - this was my favourite introduction to SQL for years (and still very good IMHO).

    Update: This isn't about Perl, but SQL, which I guess is appreciated nonetheless...

    Cheers, Joe

Re: Choosing a database for a new project
by heezy (Monk) on Oct 16, 2002 at 17:45 UTC

    I'm in the process of creating an online photo library very similar to this, but with the options of allowing select people to upload files into the site.

    My solution to the problem was to use the filenames themselves as a database.

    By naming the files in a certain way the data can then be stored along with the file (great when people want to save a file because it actually explains what it is)

    I didn't have quite as many fields as you but it could still be done by naming the files in the following way

    [ID]-[DESCRIPTION]-[LOCATION]-[yyyymmdd]-[TIMEOFDAY]-[WEATHER]-[INT/EX +T] example... 0001-Daves_flat-London-20021016-A-S-E.jpg represents.. Daves Flat, London, 16th Oct 2002, Afternoon, Sunny, Exte +rior

    It's just a different way of looking at the problem.

    This solution has limitations, for example in the description I have limited it to a-z0-9 only with _ being used for spaces and then replaced with a space at runtime if the information is displayed

    Just a different perspective on the problem I thought I would share with everyone


Re: Choosing a database for a new project
by bart (Canon) on Oct 17, 2002 at 09:28 UTC
    Go with MySQL and DBI. Just get some help from somebody out there, to set up a MySQL user for you, and create an empty database for you. Let him make sure you have enough user rights to create tables and delete all tables you create. Just because you need to learn to use MySQL, there's no need to start with learning how to admin MySQL!

    There's no need for you to start up MySQL. If you have it at your disposal, it will already be running. All you have to do, is connect to it, through DBI.

    Backing up the database is easy enough using mysqldump, assuming you have command line access:

    mysqldump --add-drop-table -u user -p database >database.sql
    "user" is your user name, "database" the database name, "database.sql" the text file with the dump that will be created, which is a text file with nothing but SQL commands, enough to recreate the whole contents. This command line will prompt you for the password. You can type the password right after the "-p" (no space), but that way it will stay in your shell's command line history, which is a bit of a security risk. (MySQL takes care to overwrite the password in the command line that appears in "ps".)

    Restoring the database can be as simple as:

    mysql -u user -p database <database.sql
    It's also an easy way to make a possibly modified copy of an existing database. It's also a way to learn SQL, in a hands-on way, because you know that this is valid SQL.

    For administering the database, like creating or modifying tables, I would recommend some database client software, like mysqltool in Perl. It works very well as a plain CGI script. All you have to do is make the stuff under "htdocs" visible in your web space, if CGI scripts can run anywhere, and modify index.cgi so that it points to your "mysqltool.conf" file with an absolute path. (Why this file is under "htdocs", I'm not sure, but it sure doesn't belong there. So move it out of your web space first.)

    A colleague of mine, who's deeply into PHP, simply loves phpMyAdmin, which is similar and written in PHP. It's just, I can't get it to work. I've seen him use it. It looks impressive. Both are free. Perhaps your site already has one of those installed, which you could use, so ask for it. If you like neither, there's more at MySQL's site.

    As an aside, may I point you to the SET and ENUM types of field, for your classification. ENUM allows you to store one of just a few possibilities ("at most" some 65000), for example "CLOUDY" for wheater. They're stored in the table as integers, but allow for easier access. SET is similar, it uses a bitmap for the field with at most 64 flags, which you can combine in any way you like.

Re: Choosing a database for a new project
by rir (Vicar) on Oct 16, 2002 at 19:02 UTC
    If you are going to keep the pictures outside the database
    you may want to help your filesystem. One way:

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://205502]
Approved by fglock
Front-paged by krisahoch
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-05-18 00:04 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (68 votes). Check out past polls.