Thanks for everybodys comments.

To give you some background, which may make things a bit clearer, here's what's going on.

The system as it currently stands has a DBM 'backend'. The user interface to each 'object' is basically a CGI object.

You call the param method on the object to set/get data, and a 'save' method saves the data away for this object into the DBM file.

It's actually rather elegant, has scaled beyong all belief (around 120000 objects, in a 17Gb DBM file!), it's quick, well indexed (searches are mostly VERY quick) and it 'just works'.

The current system means that is you want an object to have some new fields, just call param, and they get saved away. It's very free-form in that respect.

However it's stopped scaling because of locking. The frontend to this is a web-based application. Since each write needs exclusive access to the single DBM file, you can imagine this causes grief.

So enter SQL. I have the task of making a bunch of pretty-much freeform CGI objects fit into an SQL model.

The only thing I know is that each object will (should!) have a 'Module' attribute. This tells me what sort of object it is.

Basically Module equates to the table name, but I made it smarter than that. For instance, there are other common fields, so I share them in a single table (will make searches much more sane).

Actually I'm quite proud of the configuration scheme I've defined to map the CGI module/attributes to SQL table/column. So far it's working.

(BTW, there is a whole world of pain with making the SQL also cope with the fact that some of the CGI attributes might have list values - this feature is used in the current system extensively).

So hope this makes more sense now. Basically, when I 'save' a CGI object:

• Decide if it's a new or existing object
• For each attrib/value pair, map to an SQL table/column
• For each table/colum we need to update/insert, do a single update/insert

It's all fun :-)

So I think at the end of the day, I will have to bite the bullet and make perl do the date manipulation, if I want to include this 'date' function.

I didn't want to because:

• SQL can do it, it's already there and it knows how to do date calculations very well
• I'm trying to speed this system up, so I'm leary of adding code I don't strictly speaking need.

Another road I've considered since reading all these posts, is to (at conversion time) do a simple SELECT, just to convert the date.

That's not a bad option, some SQL overhead, plus I need to pass my $dbh handle around a bit more. But doable.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.