SQL::Statement unusable in major Linux distributions

[H.Merijn Brand]

On Mon, 26 Oct 2009 11:09:09 +0000, Paul Beardsell <paul at Beardsell.com>
wrote:

> 2009/10/26 H.Merijn Brand <h.m.brand at xs4all.nl> writes, in part,
> 
> > CSV as a database is not the way to create applications
> > that need speed or need low memory footprints or need
> > full ANSI-SQL support. If you need either of those, do
> > convert CSV to SQLite or postgresql (or another database
> > of your preference that does support all of your needs).
> >
> > In the DBI/DBD world,
> > * CSV does not support types (beyond NULL, integer and char)
> > * CSV does not support links (referential integrity)
> > * CSV does not support speed
> > * CSV does not support data dictionary persistence
> >
> 
> I do not think that DBD::CSV should be written off so lightly as that.  A
> database is a merely a collection of structured data.  CSV provides
> structure.  A database management system is the set of tools around that.  I
> use grep, awk, sed, cut and join.  And SQL::Statement.  And vim.  DBD::CSV
> is not just merely a database import tool as you use it.

All of the mentioned tools have restrictions. Accept that
SQL::Statement (and DBD::CSV) also have restrictions

> I like RI (of course) and I am prepared to manage it in the application.
> Dictionary persistence?  See the first line of my CSV tables!

CSV has NO way to keep the (maximum) string length of (var)char fields
CSV has no concept of telling the maximum length of numerical fields
CSV has no way to tell a difference between integer and float fields

all of that unless you tell DBD::CSV time and time again on each new
session. For me data dictionary persistence is a major reason not to
use DBD::CSV for anything more serious than data(base) migrations and
analizing tools. And - being the keeper of Text::CSV_XS - I am REALLY
fond of the CSV data format. I will stick to my opinion that CSV has no
structure beyond rows and columns. RI is not enforced by the data, it
might be enforced by your code, but that is not inherent to the
underlying structure. Quite a bit different that what RI in PostgreSQL
or Oracle has to offer.

> Up to several 1000 rows, performance can be very high in SQL::Statement +
> DBD::CSV (well, in its earlier incarnations!)

I use DBD::CSV with up to 100_000's of rows, that makes quite a
difference.

And I don't use it just to export to another database, I also use it to
fire queries on that, which is a lot easier than grep and awk. Here
DBD::CSV serves my purpose very well. And what *I* need is exactly what
SQL::Statement is now working towards. Not functions or triggers, but
reliable aggregate functions like count (*), max (*), having and group
by. Those, esp in combination with NULL values were very unreliable,
and support for that have vastly improved.

> I don't care for types much and the argument rages on in strongly vs weakly
> typed languages.  We like Perl, don't we?

/me fiercly nods

> Referential integrity is a property of the data, not of the DBMS.

Yes, but *enforced* by the database. CSV has nothing of that kind (and
will probably never will).

> RI can be enforced by the DBMS but that is a different thing entirely!

No, it is something to rely on when writing applications.

> CSV does support links.  See man 1 join.  And SQL::Statement+DBD::CSV does
> joins too.

Those are software solutions from a programmer pov. CSV does NOT
support links from the data structure point of view.

> By extention of your argument much of the recent changes to SQL::Statement
> is merely aping other DBMSs and there is no real benefit.

I extremely disagree. Major internal rewriting has been done to enable
new features of fix real bugs. Don't underestimate time and effort (and
thought) put in these modules. We never intend to break anything.

> Triggers/stored procedures/user defined functions?  Really, just use
> Postgress/MySQL/Oracle.  Here I agree.
> 
> Paul Beardsell


-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/