7 ms·

SQLite 3.38 Released

65 points by crawshaw 5y ago

solarmist 5y ago
Nice to see JSON support no longer needs compile time enabling.
tiffanyh 5y ago
> Enhancements to date and time functions… What’s the rationale for SQLite not having a date/time data type?
- anamexis 5y ago
  I think it's just part of SQLite's broader design philosophy of having a minimal set of core datatypes and leaning heavily on dynamic typing. There's a bit more info here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
  - SQLite 5y ago
    I like anamexis's explanation better than my own (that I was typing in concurrently). :-) I'm going to upvote his...
    - hitekker 5y ago
      Too late, Doctor Hipp. I've already upvoted you all.
      - deleted 5y ago
        [deleted]
- SQLite 5y ago
  No date/time data type will meet everybody's requirements. Some date/time formats will have a limited range, or won't have enough precision, and on the other hand, some date/time type implementations will give lots of range and precision but then end up taking up too much space on disk. It is not possible to please everyone. It seems better to provide basic low-level datatypes (integer, 64-bit IEEE float, string, BLOB) and let the developer choose whatever date/time representation best meets the needs of the application.
  - pdimitar 5y ago
    I get where you're coming from but a lot of people will be happy with date/time types that are compatible with SQL Server or PostgreSQL.
  - lenkite 5y ago
    There are standard ANSI SQL DATE, TIME, TIMESTAMP and INTERVAL data types. SQLite is not truly standards compliant by missing these truly essential column types which make it deeply inconvenient for use in enterprise apps. And also for apps that need pluggable/switchable backend SQL storages.
  - SigmundA 5y ago
    By this reasoning why supply integer and 64 bit float since they don't cover all requirements and could instead be encoded in strings or blobs just like dates? Why have strings too, just have a binary type? A date/time data type is very practical even if it doesn't cover all use cases which is why almost all relational database engines have one or more. I suggest something that supports ISO 8601 encoded efficiently similar to SQL Server datetimeoffset which requires 10 bytes for maximum precision. JSON is also very annoying for not having a date/time type as well!
  - electroly 5y ago
    It is certainly possible, though, to please most people. The date types in other competing SQL engines are widely used; I don't think you get off the hook by just listing a couple of different requirements that people have and saying that it's hard and throwing up your hands. DATETIMEOFFSET is not possible to reasonably implement in SQLite using any of the built-in types because you need special equality semantics that understand how to convert time zone offsets. You can't just pack it into a string or a number and hope for the best; comparisons between values don't work out. There's no way to pack it into a string format in such a way that a regular string comparison will provide chronological ordering and correct equality semantics. I have built an entire T-SQL layer on top of SQLite and I had to omit DATETIMEOFFSET support entirely. There's no good way to do it with the tools that SQLite provides. Please let me know if I've missed a way; I would like to add this to my extended SQLite language. DATETIMEOFFSET is the gold standard in the SQL Server world and it is a gaping hole in my compatibility layer. I have also built a virtual table module that links remote SQL Server tables into SQLite. I had to discard the time zone information for DATETIMEOFFSETs in the remote table and show it in SQLite as a UTC time only in string format. This is strictly worse than what we have in SQL Server, and the gap cannot be crossed with clever application-side code.
- deleted 5y ago
  [deleted]
bob1029 5y ago
> Added the sqlite3_error_offset() interface, which can sometimes help to localize an SQL error to a specific character in the input SQL text, so that applications can provide better error messages. So, once I get my Sqlite provider updated to 3.38, I will have the ablity to highlight the naughty token directly in the user's sql entry box? This could be revolutionary for our internal admin tool UX. If I can flip everything following the offset to some error style class, the user will be able to instantly zoom to what the database is complaining about. In larger queries, this would be even more valuable as locating an offending magic string can take a lot longer.
eatonphil 5y ago
Just opened a PR against mattn/go-sqlite3 to support it and tested the new JSON syntax support in DataStation. This is fantastic! https://github.com/mattn/go-sqlite3/pull/1019 https://github.com/mattn/go-sqlite3/pull/1019
Loranubi 5y ago
Great! Hopefully when Python updates its sqlite library in a few years, it will finally have JSON support. So far it was commonly built without it.
- JonathanBeuys 5y ago
  Isn't Python just sending queries as text to sqlite? How would it prevent you from sending queries that include json functions?
  - TheFlyingFish 5y ago
    SQLite is an embedded database rather than client-server, meaning runs within the Python interpreter. It's compiled (or at least linked) into the interpreter binary, meaning the version is determined at compile time. A given build of the Python interpreter has only one version of SQLite present in it.
- simonw 5y ago
  In the meantime you can use this to get a more modern SQLite in Python without having to replace SQLite yourself: https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3 On Linux you can use "pip install pysqlite3-binary" to get a compiled built version of it.
simonw 5y ago
> Use a Bloom filter to speed up large analytic queries. I'd love to know more about this one! Examples of queries it can speed up would be fascinating.
- johnisgood 5y ago
  Same!

65 points by crawshaw 5y ago

solarmist 5y ago

Nice to see JSON support no longer needs compile time enabling.

tiffanyh 5y ago

> Enhancements to date and time functions… What’s the rationale for SQLite not having a date/time data type?

anamexis 5y ago
I think it's just part of SQLite's broader design philosophy of having a minimal set of core datatypes and leaning heavily on dynamic typing. There's a bit more info here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- SQLite 5y ago
  I like anamexis's explanation better than my own (that I was typing in concurrently). :-) I'm going to upvote his...
  - hitekker 5y ago
    Too late, Doctor Hipp. I've already upvoted you all.
    - deleted 5y ago
      [deleted]
SQLite 5y ago
No date/time data type will meet everybody's requirements. Some date/time formats will have a limited range, or won't have enough precision, and on the other hand, some date/time type implementations will give lots of range and precision but then end up taking up too much space on disk. It is not possible to please everyone. It seems better to provide basic low-level datatypes (integer, 64-bit IEEE float, string, BLOB) and let the developer choose whatever date/time representation best meets the needs of the application.
- pdimitar 5y ago
  I get where you're coming from but a lot of people will be happy with date/time types that are compatible with SQL Server or PostgreSQL.
- lenkite 5y ago
  There are standard ANSI SQL DATE, TIME, TIMESTAMP and INTERVAL data types. SQLite is not truly standards compliant by missing these truly essential column types which make it deeply inconvenient for use in enterprise apps. And also for apps that need pluggable/switchable backend SQL storages.
- SigmundA 5y ago
  By this reasoning why supply integer and 64 bit float since they don't cover all requirements and could instead be encoded in strings or blobs just like dates? Why have strings too, just have a binary type? A date/time data type is very practical even if it doesn't cover all use cases which is why almost all relational database engines have one or more. I suggest something that supports ISO 8601 encoded efficiently similar to SQL Server datetimeoffset which requires 10 bytes for maximum precision. JSON is also very annoying for not having a date/time type as well!
- electroly 5y ago
  It is certainly possible, though, to please most people. The date types in other competing SQL engines are widely used; I don't think you get off the hook by just listing a couple of different requirements that people have and saying that it's hard and throwing up your hands. DATETIMEOFFSET is not possible to reasonably implement in SQLite using any of the built-in types because you need special equality semantics that understand how to convert time zone offsets. You can't just pack it into a string or a number and hope for the best; comparisons between values don't work out. There's no way to pack it into a string format in such a way that a regular string comparison will provide chronological ordering and correct equality semantics. I have built an entire T-SQL layer on top of SQLite and I had to omit DATETIMEOFFSET support entirely. There's no good way to do it with the tools that SQLite provides. Please let me know if I've missed a way; I would like to add this to my extended SQLite language. DATETIMEOFFSET is the gold standard in the SQL Server world and it is a gaping hole in my compatibility layer. I have also built a virtual table module that links remote SQL Server tables into SQLite. I had to discard the time zone information for DATETIMEOFFSETs in the remote table and show it in SQLite as a UTC time only in string format. This is strictly worse than what we have in SQL Server, and the gap cannot be crossed with clever application-side code.
deleted 5y ago
[deleted]

bob1029 5y ago

> Added the sqlite3_error_offset() interface, which can sometimes help to localize an SQL error to a specific character in the input SQL text, so that applications can provide better error messages. So, once I get my Sqlite provider updated to 3.38, I will have the ablity to highlight the naughty token directly in the user's sql entry box? This could be revolutionary for our internal admin tool UX. If I can flip everything following the offset to some error style class, the user will be able to instantly zoom to what the database is complaining about. In larger queries, this would be even more valuable as locating an offending magic string can take a lot longer.

eatonphil 5y ago

Just opened a PR against mattn/go-sqlite3 to support it and tested the new JSON syntax support in DataStation. This is fantastic! https://github.com/mattn/go-sqlite3/pull/1019 https://github.com/mattn/go-sqlite3/pull/1019

Loranubi 5y ago

Great! Hopefully when Python updates its sqlite library in a few years, it will finally have JSON support. So far it was commonly built without it.

JonathanBeuys 5y ago
Isn't Python just sending queries as text to sqlite? How would it prevent you from sending queries that include json functions?
- TheFlyingFish 5y ago
  SQLite is an embedded database rather than client-server, meaning runs within the Python interpreter. It's compiled (or at least linked) into the interpreter binary, meaning the version is determined at compile time. A given build of the Python interpreter has only one version of SQLite present in it.
simonw 5y ago
In the meantime you can use this to get a more modern SQLite in Python without having to replace SQLite yourself: https://github.com/coleifer/pysqlite3 https://github.com/coleifer/pysqlite3 On Linux you can use "pip install pysqlite3-binary" to get a compiled built version of it.

simonw 5y ago

> Use a Bloom filter to speed up large analytic queries. I'd love to know more about this one! Examples of queries it can speed up would be fascinating.

johnisgood 5y ago
Same!