3 ms·

This is nice. Very nice. But to be honest, having used SQLite in many hobby/toy projects for ~15 years, I have never ever encountered a type error with it. Stri

by krylon 5y ago

This is nice. Very nice. But to be honest, having used SQLite in many hobby/toy projects for ~15 years, I have never ever encountered a type error with it. Stricter handling of types is a good idea, IMHO, but if the alternative had been, say, a reasonable type to represent date/time values, I would have chosen the latter.

MrBuddyCasino 5y ago
Is your usual language strictly typed?
- krylon 5y ago
  These days, I am a Go person, so mostly yes, but I used to be a Python, Perl, and Ruby person, and I did not encounter any problems there, either. I think if you expect your database to use certain types, you write your code accordingly. Why would you ever want to insert a BLOB into an INTEGER column? Some kind of data validation/sanitizing is necessary anyway, the database engine being strict about it is just an additional layer of protection, but it shouldn't be one's first line of defense against mistakes. IMHO.
  - jatone 5y ago
    you and I might not. but bugs happen. and sqlite being so permissive can aggravate the issue.
    - krylon 5y ago
      Like I said, I welcome the option to enforce stricter type checking, and I definitely will make use of it once it becomes available. OTOH, if type safety is a concern, one can already enforce it using CHECK constraints or triggers. But yeah, it's like a seat belt in a car - in order to benefit from it, you have to actually put it on.
QuadrupleA 5y ago
Same here, haven't ran into any problems with the loose typing. I personally love sqlite's dead simple type system though, and its date handling compared to mysql & postgres - a lot of power in a few simple primitives - would hate to see it add a million new types.
gwd 5y ago
Where I ran into an error was when I developed a new technique by running Python from a Jupyter notebook, and then moved everything over to Golang once I'd got the basics of the algorithm down. Python didn't care that it got occasionally got back a 'float' rather than an 'int'; Golang did. (So I had to make a custom type unmarshaller that could handle both incoming types.) EDIT: The fact that Golang is type-checked should make most of the issues here moot (since the only way to get the wrong type would be to have a malformed INSERT query, and Golang would choke the next time one of those rows showed up in a query); but it would mean I wouldn't have to be quite so paranoid when frobbing about manually with the sqlite3 command-line tool, for instance.
- wruza 5y ago
  Golang did That really depends on how “golang” fetches data: https://www.sqlite.org/c3ref/column_blob.html https://www.sqlite.org/c3ref/column_blob.html C API makes it clear that if you request an int, you get an int, no matter what. The only way it could care is a client/wrapper library that looked at a column type, called a specific accessor function and then tried to push that value into non-compatible golang structure field(?). It doesn’t make the database incompatible, it’s just a wrapper with a poorly thought out type consistency. At C level, there is no such issue.
nooorofe 5y ago
> I have never ever encountered a type error with it The problem is opposite - not having type error. The simplest example probably is import from CSV with incorrect column order. Using other DB you get an error, SQLite will just import everything.

by krylon 5y ago

MrBuddyCasino 5y ago

Is your usual language strictly typed?

krylon 5y ago
These days, I am a Go person, so mostly yes, but I used to be a Python, Perl, and Ruby person, and I did not encounter any problems there, either. I think if you expect your database to use certain types, you write your code accordingly. Why would you ever want to insert a BLOB into an INTEGER column? Some kind of data validation/sanitizing is necessary anyway, the database engine being strict about it is just an additional layer of protection, but it shouldn't be one's first line of defense against mistakes. IMHO.
- jatone 5y ago
  you and I might not. but bugs happen. and sqlite being so permissive can aggravate the issue.
  - krylon 5y ago
    Like I said, I welcome the option to enforce stricter type checking, and I definitely will make use of it once it becomes available. OTOH, if type safety is a concern, one can already enforce it using CHECK constraints or triggers. But yeah, it's like a seat belt in a car - in order to benefit from it, you have to actually put it on.

QuadrupleA 5y ago

Same here, haven't ran into any problems with the loose typing. I personally love sqlite's dead simple type system though, and its date handling compared to mysql & postgres - a lot of power in a few simple primitives - would hate to see it add a million new types.

gwd 5y ago

Where I ran into an error was when I developed a new technique by running Python from a Jupyter notebook, and then moved everything over to Golang once I'd got the basics of the algorithm down. Python didn't care that it got occasionally got back a 'float' rather than an 'int'; Golang did. (So I had to make a custom type unmarshaller that could handle both incoming types.) EDIT: The fact that Golang is type-checked should make most of the issues here moot (since the only way to get the wrong type would be to have a malformed INSERT query, and Golang would choke the next time one of those rows showed up in a query); but it would mean I wouldn't have to be quite so paranoid when frobbing about manually with the sqlite3 command-line tool, for instance.

wruza 5y ago
Golang did That really depends on how “golang” fetches data: https://www.sqlite.org/c3ref/column_blob.html https://www.sqlite.org/c3ref/column_blob.html C API makes it clear that if you request an int, you get an int, no matter what. The only way it could care is a client/wrapper library that looked at a column type, called a specific accessor function and then tried to push that value into non-compatible golang structure field(?). It doesn’t make the database incompatible, it’s just a wrapper with a poorly thought out type consistency. At C level, there is no such issue.

nooorofe 5y ago

> I have never ever encountered a type error with it The problem is opposite - not having type error. The simplest example probably is import from CSV with incorrect column order. Using other DB you get an error, SQLite will just import everything.