3 ms·

How? Consider the following pseudocode: Schema: CREATE TABLE foo ( bar INTEGER ); Program: fn main() { stmt := "INSERT INTO foo

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 := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); // Static analysis fails here. Input is not text. } else { db.exec(stmt, ["Baz"]); } } Perhaps what you are imagining is when the data is provided externally, where the target schema isn't known at compile time? That is a possible use-case, but not what SQLite was primarily designed for and not what we are talking about. If that is what you need that is what strict tables are there for. Different tools for different jobs.

deleted 3mo ago
[deleted]
frollogaston 3mo ago
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
- win311fwg 3mo ago
  > 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.

deleted 3mo ago

[deleted]

frollogaston 3mo ago

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

win311fwg 3mo ago
> 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.