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

181–188 of 188 posts

Re: Prefer strict tables in SQLite

#181

Post by spopejoy »

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,…

I was actually expecting that article to say something about performance. In the 90s telecom DBs removed all constraints including primary key from their ingestion tables for speed, and in general constraints are for OLAP not OLTP.

Having said that, given sqlite's tiny type universe I can't imagine TC would be at all slow.

Re: Prefer strict tables in SQLite

#182

Post by Rendello »

akoboldfrying wrote: post #118
Rendello wrote: post #80

Earlier quoted context omitted.

Even foreign keys weren't available before 2009, and are still not enabled by default: https://sqlite.org/foreignkeys.html

Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.

The SQLite docs have a great "quirks" page [1], which contains this telling quote:

> The original implementation of SQLite sought to follow Postel's Law which states in part "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. More recently, people have come to prefer software that is strict in what it accepts, so as to more easily find errors.

> There are now millions of applications that take advantage of SQLite's flexible and forgiving design choices. We cannot change SQLite to follow the current preference toward strict and dogmatic behavior without breaking those legacy applications.

1. https://sqlite.org/quirks.html

Re: Prefer strict tables in SQLite

#183

Post by [deleted] »

win311fwg wrote: post #180
frollogaston wrote: post #176

Earlier quoted context omitted.

Even with a statically typed language, two separate codepaths or code versions could write to the same tables and disagree on the types.

How? Consider the following pseudocode: Schema: CREATE TABLE foo ( bar INTEGER ); Program: fn main() { stmt := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); } else { db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer. } } If a different code version comes along and, say, changes the schema then: Schema: CREATE TABLE foo ( bar TEXT ); Program: fn main() { stmt…

[deleted]

Re: Prefer strict tables in SQLite

#184

Post by frollogaston »

win311fwg wrote: post #180
frollogaston wrote: post #176

Earlier quoted context omitted.

Even with a statically typed language, two separate codepaths or code versions could write to the same tables and disagree on the types.

How? Consider the following pseudocode: Schema: CREATE TABLE foo ( bar INTEGER ); Program: fn main() { stmt := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); } else { db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer. } } If a different code version comes along and, say, changes the schema then: Schema: CREATE TABLE foo ( bar TEXT ); Program: fn main() { stmt…

One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important.

  CREATE TABLE foo (
    bar INTEGER
  );
money_adder.c inserts string:

  sqlite3_stmt *stmt;
  const char *sql = "INSERT INTO foo (bar) VALUES (?);";
  ...
  sqlite3_bind_text(stmt, 1, "4.5", -1, SQLITE_STATIC);
money_viewer.c selects int:

  sqlite3_stmt *stmt;
  const char *sql = "SELECT bar FROM foo;";
  ...
  int bar_int = sqlite3_column_int(stmt, 0);  // bar_int = 4

Re: Prefer strict tables in SQLite

#185

Post by win311fwg »

frollogaston wrote: post #184
win311fwg wrote: post #180

Earlier quoted context omitted.

How? Consider the following pseudocode: Schema: CREATE TABLE foo ( bar INTEGER ); Program: fn main() { stmt := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); } else { db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer. } } If a different code version comes along and, say, changes the schema then: Schema: CREATE TABLE foo ( bar TEXT ); Program: fn main() { stmt…

One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important. CREATE TABLE foo ( bar INTEGER ); money_adder.c inserts string: sqlite3_stmt *stmt; const char *sql = "INSERT INTO foo (bar) VALUES (?);"; ... sqlite3_bind_text(stmt, 1, "4.5", -1, SQLITE_STAT…

> but your codebase is big and you forget what type something is.

Why does that matter? You don't have to rely on human memory. You went to all the trouble to define the types so you don't have to remember. Your static analysis will tell you that the types are incompatible.

> money_adder.c inserts string

This fails analysis. `bar` is defined as an integer. money_adder.c will not ever get the point of inserting a string as your infrastructure will halt the build pipeline long before you ever get to the point of running the program.

You must have accidentally replied to the wrong comment at some point? It is technically true that you can write software without type analysis, but that's clearly not applicable to our discussion about using type analysis.

> You're also updating the code while keeping the same SQLite file

In the real world where migrations are necessary it is prudent to validate that any already persisted data is structured as expected, applying any necessary migrations if there is a mismatch, but when used as SQLite was primarily designed you only need to do that once at initialization, not every single time you touch the data. Once you have validated that the file's schema matches the schema defined at compile time then the static truths hold.

Re: Prefer strict tables in SQLite

#188

Post by zvrba »

fauigerzigerk wrote: post #160
zvrba wrote: post #127

Earlier quoted context omitted.

I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar). Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc. It's a PITA…

>It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload. Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code. You could of course have a JSON schema somewhere, but in my experience the whole point of representing the schema as data in the database is to support (limited) end-user driven schema changes. I…

> Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.

The point is that "dynamism" isn't needed in this case. It's used to define fixed parameter sets for different components, so those components could just as well define a class and use json to (de)serialize it.

IOW: Defining new fields, classes and values is worthless unless the underlying component can interpret them. So code changes are needed anyway, and then class is way better and safer to use.

Post reply on HN