Re: DBD::File insert/update

On Wed, Jun 09, 2010 at 02:45:18PM +0000, Jens Rehsack wrote:
> On 06/09/10 14:36, Tim Bunce wrote:
> >>
> >>The interface of the per-table API doesn't allow that :(
> >>That's exactly the reason why I thought it's required to warn about that.
> >
> >Can it be expressed as "this is regarded as a bug and is likely to change"?
> 
> It's a bug, it's a bug by design and yes, this is likely to change
> (in distant future).
> This behavior is listed under "GOTCHAS AND WARNINGS" (which seems to
> be Jeffs place for describing "BUGS AND LIMITATIONS".

It would be good to explicitly say it's a bug and it's likely to change.

> >>When I took over SQL::Statement and Tux got DBD::CSV, we talked each
> >>other and discovered that an per-table API for indices is missing.
> >>
> >>This is one goal I want to reach when developing SQL::Statement 2.0 - but
> >>it will be a long road.
> >>
> >>>The hash/DBM style databases should be modeled as two column tables
> >>>with a unique constraint on the key column.
> >>
> >>The table-API just knows: fetch_row, push_row, push_names and some
> >>optimized routines to allow update/delete specific rows.
> >
> >Insert could do a fetch_row first and fail if a row is found.
> 
> It couldn't trivialy, because it must do a full table scan for it
> and create a subquery which allows to match. And, while this might
> be possible for SQL::Statement, I currently have no idea how to do
> it for DBI::SQL::Statement. But let me sleep over it (I'm sure I will
> find a way).
> 
> >Update could do a fetch_row first and fail if the row is not found.
> 
> This should happen. I try it and report.
> 
> >Apart from the performance hit, what's the problem?
> 
> I rate a full table scan on INSERT not as a minor performance hit.
> OTOH it's better to be slow than inconsistent.

Sure, but I was thinking specifically of "hash/DBM style databases".

It's perfectly understandable (and NOT a bug) that things like CSV files
don't support unique constraints.

On the other hand, databases built on hashes DO have a unique constraint
and silently discarding rows (on inserts) or inserting rows (on updates)
is a bug.

Tim.