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

91–100 of 188 posts

Re: Prefer strict tables in SQLite

#91

Post by somat »

rogerbinns wrote: post #13
tuvix wrote: post #4

Earlier quoted context omitted.

I’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)

SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to…

If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb.

I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.

Re: Prefer strict tables in SQLite

#92

Post by coldtea »

rjrjthtrjrj wrote: post #89
win311fwg wrote: post #60

Earlier quoted context omitted.

> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting…

2004 called and they want their ignorant bad takes back

They also want this type of joke and misunderstanding SQLite purpose to get "macho programmer" points back.

Re: Prefer strict tables in SQLite

#94

Post by jrapdx3 »

InsideOutSanta wrote: post #56
poidos wrote: post #26

Earlier quoted context omitted.

Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.

The developer's intention is that anything can go in there. You would only inherit a project where everything was ANY if anything could go anywhere. With SQLite's default behavior, anything can always go anywhere, so the type definitions are at best semi-accidentally observed by the code, and at worst completely misleading. You have no idea which of the two the developer intended. I get the impression that this SQLit…

IIRC SQLite originated as a Tcl extension. In Tcl at the user level "everything is a string" or a number. So it's logical SQLite would accept values as a string or number. Interestingly a Tcl function defines its own semantics, an input value means whatever the function says it means, perhaps a timestamp. SQLite inherited these attributes, and as many commenters observe, SQLite largely continues to work that way.

Implies documentation is crucial. Fortunately SQLite's documentation is among the best out there.

Re: Prefer strict tables in SQLite

#95

Post by simonw »

> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.

This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this:

  uvx sqlite-utils transform data.db mytable --strict
Or in Python:

  import sqlite_utils

  db = sqlite_utils.Database("data.db")
  db.table("mytable").transform(
    strict=True
  )
Release notes for 4.1 here: https://sqlite-utils.datasette.io/en/stable/changelog.html#v...

Here are the relevant docs:

- Using table.transform(strict=True): https://sqlite-utils.datasette.io/en/stable/python-api.html#...

- The sqlite-utils transform command: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

Re: Prefer strict tables in SQLite

#96

Post by chasil »

3eb7988a1663 wrote: post #23
mort96 wrote: post #11

Earlier quoted context omitted.

Yeah it's a really weird design decision. Why would I want the database to let me accidentally insert the wrong type? SQLite is mostly great but its philosophy towards type safety leaves something to be desired. I once had to clean up in a project where someone had accidentally stored the strings '1' and '0' in a Boolean column in code deployed to thousands of devices; not fun. Another thing I dislike is the lack of…

'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.

Re: Prefer strict tables in SQLite

#97

Post by ahartmetz »

azornathogron wrote: post #69
ahartmetz wrote: post #34

Earlier quoted context omitted.

That quote leaves open whether SQLite "pretended" to support foreign keys by allowing to create tables with them, but didn't implement them. Otherwise, I don't see the compatibility problem.

Yes. From the release notes: 2002-06-17 (2.5.0), "Parse (but do not implement) foreign keys." At one point there was also a tool which would generate trigger rules to enforce foreign key constraints. (2008 Oct 15 (3.6.4), Added the source code and documentation for the genfkey program for automatically generating triggers to enforce foreign key constraints)

Thank you. Strange decisions, but not completely baffling then.

Re: Prefer strict tables in SQLite

#98

Post by SoftTalker »

somat wrote: post #91
rogerbinns wrote: post #13

Earlier quoted context omitted.

SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to…

If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb. I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.

OpenLDAP also originally used BerkeleyDB

Re: Prefer strict tables in SQLite

#99

Post by sheept »

panzi wrote: post #50
jll29 wrote: post #9

I'd like to see STRICT as the default. That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!

Well, I would also like a proper datetime/timestamp datatype that isn't just a string.

Why not store them as integers? Representing dates is a UI responsibility

Re: Prefer strict tables in SQLite

#100

Post by frollogaston »

rjrjthtrjrj wrote: post #89
win311fwg wrote: post #60

Earlier quoted context omitted.

> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting…

2004 called and they want their ignorant bad takes back

Reddit called and said they're missing a guy
Post reply on HN