Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Prefer strict tables in SQLite

evanhahn.com

Post reply on HN
Print view View original

141–150 of 188 posts

Re: Prefer strict tables in SQLite

#141

Post by mort96 »

petilon wrote: post #54
e2le wrote: post #21

Earlier quoted context omitted.

> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html

Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.

I don't understand what this has to do with strict mode? Yes those types should exist, but the workaround is to use an integer column for bool and a text column for date, whether or not you're using strict mode.

Re: Prefer strict tables in SQLite

#142

Post by ivanjermakov »

dfabulich wrote: post #79

https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default). > rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows. That doesn't line up with my experience. (In particular,…

Wow, SQLite not believing in fail-fast systems is disappointng. Never knew SQLite was JS of SQL!

Re: Prefer strict tables in SQLite

#143

Post by _flux »

petilon wrote: post #78
frollogaston wrote: post #49

Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.

> https://sqlite.org/flextypegood.html Both the advantages and disadvantages section is missing key arguments. Key argument in favor of flexible typing: Easy to evolve schema. When you are using SQLite to store data in your embedded database and your requirements change, do you want to create a new database and migrate data each time? Evolving the schema in-place is much easier, and if your application is the only on…

I can see that it would be difficult to make changes to db schema types, if there are multiple separate applications with their own release schedule. But this affects schema structure as well, not just its types. I can't even describe how difficult it would be to write apps that work in that scenario—and test it against data that could be written into the db by any previous version of the app. If you truly have this kind of situation at hand, perhaps a document database would be a better fit.

Additionally, how often are there multiple apps with different release schedules written using Sqlite? I would expect overwhelmingly large number of its use cases would be a single application working on it.

Re: Prefer strict tables in SQLite

#144

Post by astrobe_ »

nine_k wrote: post #73
ezekiel68 wrote: post #71

Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to…

The difference is that when you add all these mechanisms yourself, you can do it differently than TCP does, sometimes to a great effect: see QUIC and HTTP/3. OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.

Yep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack.

Datagram is also quite often more fit for applications than streams, because many applications are message oriented. The Websocket protocol acknowledges that even though over TCP. But that's more a bonus point than a strong reason to choose UDP over TCP, one can always recreate packets/messages on top of TCP. It's a bit goofy though, because TCP uses IP packets.

A lot of online games with significant real-time constrains and many-to-many connections gladly use UDP - and similarly, video conference services also use it. Smaller protocols like DNS and NTP as well.

There are other arguments beside real-time streaming with acceptable data loss, see [1] and the "end-to-end argument" paper it links in particular.

Choosing UDP and ending up recreating some of its reliability and flow control features is not a "Uh, Oh..." moment. It's normally a deliberate choice. Sometimes you do need custom wheels [2].

[1] https://deepplum.com/post-b/

[2] https://en.wikipedia.org/wiki/Mecanum_wheel

Re: Prefer strict tables in SQLite

#145

Post by masfuerte »

thaumasiotes wrote: post #113
panzi wrote: post #104

Earlier quoted context omitted.

I also want to be able to debug the database in an SQL console with nice looking date-times.

You can already do that: https://sqlite.org/lang_datefunc.html

Just to state the obvious, you can create a view which uses these functions to make your integer date/times readable, and then debugging is easy.

Re: Prefer strict tables in SQLite

#146

Post by ncruces »

masklinn wrote: post #137
ncruces wrote: post #42

Earlier quoted context omitted.

The downside is that you loose the place to store the metadata that a column is supposed to store a date. Which is why I prefer not to use them.

A comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify. Domain types would be the best fix, but I do not think legacy tables are the second best.

A comment is not something I can get programmatically through `sqlite3_column_decltype` or `sqlite3_table_column_metadata`.

I can use either API to trigger bool and time handling in my Go driver.

https://github.com/ncruces/go-sqlite3/blob/main/driver/drive...

Re: Prefer strict tables in SQLite

#147

Post by echoangle »

chasil wrote: post #96
3eb7988a1663 wrote: post #23

Earlier quoted context omitted.

'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type. Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc

When you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2. I have done this many times. Function-based indexes are necessary if they must be searched.

Why would you do that? To save some bytes in storage?

Re: Prefer strict tables in SQLite

#148

Post by inigyou »

thaumasiotes wrote: post #111
alwa wrote: post #62

Earlier quoted context omitted.

Is it better to learn that it exists through surprise , by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?

> Is it better to learn that it exists through surprise , by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance? The premise of your question doesn't work. If one person is going to learn about the option and a second one isn't, the better system for that to occur in is the one in which more people end up with correct behavior. In this case, that mea…

How do you feel about Apple breaking all the apps on every major update?

Re: Prefer strict tables in SQLite

#149

Post by chasil »

echoangle wrote: post #147
chasil wrote: post #96

Earlier quoted context omitted.

When you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2. I have done this many times. Function-based indexes are necessary if they must be searched.

Why would you do that? To save some bytes in storage?

The largest table where I've done this had over 50 of these booleans.

That's one 64-bit number, or 50 different columns.

Re: Prefer strict tables in SQLite

#150

Post by inigyou »

echelon wrote: post #110
redman25 wrote: post #63

Earlier quoted context omitted.

A large portion of the tests are closed source unfortunately which would make it tough to create a port.

What the hell? This is the first I'm learning of this. Why did they do that? Is it owned by a private company?

That's one way they make money. That's the reason sqlite exists.
Post reply on HN