On 2018/06/29 6:25 PM, Bob Friesenhahn wrote:
On Fri, 29 Jun 2018, David Burgess wrote:
"This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values//...
This is an important feature of SQLite. In hindsight, an excellent
decision.
It is sloppy and absent additional constraints and checks added to the
schema, it puts a burden on the consumer of the data to assure that it
is the correct type.
It is not so clear that it was an excellent decision.
If the application requires an integer value, why do you think that it
is appropriate to pass it the string 'xyzzy'?
Unlike other SQL Engines that operates in a User-interaction-space,
SQLite operates in an application-interaction space. It is part and
parcel of the application (or at least via a library that services
direct API calls), and so where user-interactive databases have Type
constraints as a way to force those from outside your app, it comes with
penalties. To name a few:
- it has to define very many kinds of variable types to cope with the
different constraint needs, and
- add more over time, which apart from being hefty, doesn't support the
future-proof file format that SQLite does (that's why you need to run
upgrade programs when installing a new MySQL DB, for example),
- Once committed to a type, changing the nature of data that can be
added requires a Schema change (not too hard if your DB is one server
computer, but what if it runs in a billion separate devices?).
- Extending the last point - this is even true if you want to do as
simple an exercise as allowing 40-char strings where you previously had
30-char strings, because the client thought they knew their own requirement.
While this all does serve to protect you from external users adding
non-conformant data, in SQLite's case the DB is application-interactive,
and apart from solving all of the above, any data that gets pushed to
your DB file typically is either generated by the application, inputted
via the application, or at a minimum vetted/controlled by the
application. Type-constraining here would merely protect the programmer
against him/herself. I know this is not entirely without merit, but a
feature I would happily forego when weighed against even just one of the
points made in the previous paragraph.
At least, enough-so in my mind to defend David's assessment of "an
excellent decision".
What it definitely doesn't do, is create a security vulnerability or
system-level risk. (i.e. Any potential risk it does create is one that
can easily be mitigated by code in your own application)
Maybe not the perfect solution, but I'd say a good alternative to the
other good-but-non-perfect Types employed in other Engines.
I hope that answers your question somewhat,
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
