Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re: DBI quoting when I don't want it to

by Tardis (Pilgrim)
on Aug 22, 2002 at 02:11 UTC ( #191904=note: print w/replies, xml ) Need Help??

in reply to DBI quoting when I don't want it to

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.

  • Comment on Re: DBI quoting when I don't want it to

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://191904]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2018-08-17 09:44 GMT
Find Nodes?
    Voting Booth?
    Asked to put a square peg in a round hole, I would:

    Results (176 votes). Check out past polls.