Is it just me, or is Sqlite3 goofy?

[mensanator at aol.com]

Magnus Lycka wrote:
> While I can understand your frustration, I think it is
> important to think about the tone in our postings here.
> Hydrocephalus is one of the most common birth defects,
> and it's not terribly unlikely that someone who reads
> this has a family member or someone else in his proximity
> who suffers from this condition.

Ok, I appologize. No more Larry the Cable Guy humor.
I'll stick with Monty Python and Douglas Adams.

>
> mensanator at aol.com wrote:
> > "Fixed"? Up until now, I didn't think it was possible for
> > crackpot theories to be implemented in computer science.
> > This is absolutely the craziest thing I've ever heard.
>
> Still, many people with lots of experience in databases
> use it, and prefer it for certain kinds of applications.
> All systems have limitations and deviations, and those
> limitations and deviations are stated more clearly for
> SQLite than for most commercial products at least.

But they're not stated up front. The deviations from
standard SQL are extreme enough to warrant mentioning
in the Python docs.

> The
> market leader Oracle still can't store empty strings in
> VARCHAR fields for instance. They are silently converted
> to NULL. I'm pretty sure that has been in clear violation
> to the official spec since 1986 at least.

But does Oracle claim the specification is a bug?

>
> As far as I understand, noone here is forcing you to use
> SQLite,

As long as it's included in the standard library, I'm going
to use it. There is nothing wrong with the idea of a "lite"
database. It is very misleading, though, to claim it's SQL.

> and with your long experience of MS Access I'd
> expect you to be fairly used to "almost SQL"... It's
> some time since I used Jet/Access now, but I had much
> more problems with that than I've had with SQLite.
>
> SQLite is built in Tcl, by someone who appreciates the
> way Tcl works, with its weak typing. I don't think Tcl's
> type handling is nearly as clever as Python's, but I
> think it's a good thing that Python's standard lib finally
> has a DB-API compliant module, and while I would have
> preferred something that was closer to standard SQL, I
> don't know of a better candidate than SQLite.
>
> It's good that it's usable without a server setup, and
> that it's very light weight. A Jet engine is obviously
> not an option, and I would have preferred SQLite even
> if Jet was open source and worked on all platforms.
> (Well, if JET *was* open source, I suspect it would
> have been fixed by now.) It's possible that one could
> have used the embedded version of Firebird instead, but
> in my experience that's not nearly as lean or easy to
> deploy.
>
> With your long experience of Access and SQL Server I'm
> sure you know well that any attempt to build a working
> database application requires extensive knowledge of
> the backend to understand its peculiarities and
> limitations.
>
> The list of software projects where not quite competent
> developers built Access applications that worked ok in
> small scale tests and failed catastrophically in real
> life is looong...
>
> Of course, if you've stayed with one vendor for 15 years,
> I can imagine that you've forgotten how long it took you
> Having worked with half a dozen backends or so, I'm no
> longer surprised that SQL can be interpreted in so many
> ways... I agree that SQLite is unique in it's approach
> to typing, but if you are aware of this, it's really not
> a big problem.

Ok, I'm now aware of it.

I'm aware that all my WHERE clauses will have to be
modified to allow for text that may show up in my numeric
fields.

I'm aware that all my ORDER BY clauses will have to
be modified for the same reason.

And I'm aware that INNER JOIN doesn't even work in
theory with dynamic typed fields and can't even be
worked around like the WHERE and ORDER BY clauses
can.

And given a typical query I use:

SELECT tblLocations.SiteID,
       tblSites.SiteName,
       tblLocations.IEPALocationNumber,
       tblZones.Zone,
       tblSampleEvent.SampleEventCode,
       tblSampleAnalyses.SampleDate,
       tblSamples.SampleMatrixID,
       tblSamples.SampleNumber,
       tblRefQAQCTypes.QAQCType,
       tblResults.LabSampleNumber,
       tblRefParameterGroups.ParameterGroupCode,
       tblSampleAnalyses.AnalysisID,
       tblRefAnalyses.AnalysisTypeCode,
       tblRefParameters.ParameterReportLabel,
       tblRefParameters.CASNumber,
       tblResults.Result,
       tblResults.Qualifier,
       tblRefUnitOfMeasure.Unit,
       Val(Format$(IIf(([tblResults].[unitid]=5) Or
                       ([tblResults].[unitid]=7),
                        [result]/1000,[result]),
                         "0.0000"))
       AS the_ppm_result,
       IIf([tblResults].[unitid]=7,"mg/kg",
           IIf([tblResults].[unitid]=5,"mg/L",
           [unit]))
       AS the_ppm_unit,
       Val(Format$(IIf(([tblResults].[unitid]=5) Or
                       ([tblResults].[unitid]=7),
                        [quantitationlimit]/1000,
                        [quantitationlimit]),"0.0000"))
       AS the_ppm_dl,
       IIf(IsNull([qualifier]) Or
                 ([qualifier] Not Like "*U*"),1,0)
       AS detect,
       IIf([detect] And
           [the_ppm_result]>[tbl_GW_classi],"1","0")
       AS x,
       IIf([detect] And
           [the_ppm_result]>[tbl_GW_classi],"!","_")
       AS xflag,
       Master_Lookup.Tbl_pH_range,
       Master_Lookup.Tbl_GW_units,
       Master_Lookup.Tbl_GW_ClassI,
       tblResults.Comment,
       IIf([detect],[the_ppm_result],[the_ppm_result]/2)
       AS detected_result,
       IIf([detect],[the_ppm_result],Null)
       AS detected_max_result
FROM tblSites
INNER JOIN ((tblSampleEvent
INNER JOIN (tblRefQAQCTypes
INNER JOIN ((tblSamples
INNER JOIN tblLocations
ON tblSamples.TrueLocation = tblLocations.LocationID)
INNER JOIN tblZones
ON tblLocations.LocationID = tblZones.LocationID)
ON tblRefQAQCTypes.QAQCID = tblSamples.QAQCID)
ON tblSampleEvent.EventID = tblSamples.EventID)
INNER JOIN ((tblRefAnalyses
INNER JOIN tblSampleAnalyses
ON tblRefAnalyses.AnalysisID = tblSampleAnalyses.AnalysisID)
INNER JOIN (tblRefUnitOfMeasure
INNER JOIN ((tblRefParameterGroups
INNER JOIN (tblRefParameters
LEFT JOIN Master_Lookup
ON tblRefParameters.CASNumber = Master_Lookup.Tbl__B_cas)
ON tblRefParameterGroups.ParameterGroupID =
tblRefParameters.ParameterGroupID)
INNER JOIN tblResults
ON tblRefParameters.ParameterID = tblResults.ParameterID)
ON tblRefUnitOfMeasure.UnitID = tblResults.UnitID)
ON tblSampleAnalyses.SampleAnalysisID = tblResults.SampleAnalysisID)
ON tblSamples.SampleID = tblSampleAnalyses.SampleID)
ON tblSites.SiteID = tblLocations.SiteID
WHERE (((tblLocations.SiteID)<51)
AND (Not (tblLocations.IEPALocationNumber)="G116")
AND ((tblZones.Zone)="UMAquifer")
AND ((tblSampleEvent.SampleEventCode) Like "200[2-6]Q*")
AND ((tblSamples.SampleMatrixID)=1)
AND ((tblSampleAnalyses.AnalysisID)>1)
AND ((tblResults.Qualifier) Is Null)
AND ((Master_Lookup.Tbl_pH_range)="pH 6.9 to 7.24" Or
     (Master_Lookup.Tbl_pH_range) Is Null)
AND ((tblSamples.QAQCID)=1 Or (tblSamples.QAQCID)=4))
OR  (((tblLocations.SiteID)<51)
AND (Not (tblLocations.IEPALocationNumber)="G116")
AND ((tblZones.Zone)="UMAquifer")
AND ((tblSampleEvent.SampleEventCode) Like "200[2-6]Q*")
AND ((tblSamples.SampleMatrixID)=1)
AND ((tblSampleAnalyses.AnalysisID)>1)
AND ((tblResults.Qualifier) Not Like "*Z*" And
     (tblResults.Qualifier) Not Like "*R*")
AND ((Master_Lookup.Tbl_pH_range)="pH 6.9 to 7.24" Or
     (Master_Lookup.Tbl_pH_range) Is Null)
AND ((tblSamples.QAQCID)=1 Or
     (tblSamples.QAQCID)=4))
ORDER BY tblLocations.SiteID,
         tblLocations.IEPALocationNumber,
         tblSampleEvent.SampleEventCode,
         tblRefParameterGroups.ParameterGroupCode,
         tblRefParameters.ParameterReportLabel,
         tblSampleAnalyses.SampleDate;

you're saying I don't have a big problem?