How to check if a Sqlite3 Database is valid?

Question

I was testing and seeing what happens if android create a database with wrong input values.

For example, if I unintentionally typed NOOT instead of NOT or INTEGET instead of INTEGER, I expected SQL to point out a return error saying that the SQL keywords were incorrect. Or at least that the entry is invalid.

But that’s not what happens. Look at this snippet:

sqlite> CREATE TABLE asd (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, breed TEXT NOT NULL, gender INTEGET NOT NULL DEFAULT 0, weight INTEGER NOT NULL DEFAULT 0);
sqlite> .schema asd
CREATE TABLE asd (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, breed TEXT NOT NULL, gender INTEGET NOT NULL DEFAULT 0, weight INTEGER NOT NULL DEFAULT 0);
sqlite>

See that gender I purposely entered the data type as INTEGET instead of INTEGER and it seems SQL accepted.

Another example:

CREATE TABLE qwe (_id INTEGET PRIMARY KEY);

sqlite> INSERT INTO qwe (_ID) VALUES (0);
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM qwe;
_id       
----------
0         
sqlite> 

See that again, instead of using INTEGER I put INTEGET and SQL does not report error. He adds the information, even though in my view, a kind of invalid data.

My question is, if SQL processes error entries, how do I check if my database is valid when creating applications?

Answer 1

How can you read on FAQ, this is a feature and not a BUG of Sqlite. Different from other databases, such as Mysql for example, the Sqlite does not use rigid typing.

Instead, a generic typing system is used that is associated to its value and not necessarily to the column type.

This allows the system to use what they call type affinity. This system will convert table values (see example below).

Example:

sqlite> CREATE TABLE asd (_id INTEGER);
sqlite>
sqlite> INSERT INTO asd VALUES ("a");
sqlite>
sqlite> INSERT INTO asd VALUES (123);
sqlite>
sqlite> INSERT INTO asd VALUES (456.789);
sqlite>
sqlite> SELECT * FROM asd;
        a
        123
        456.789
sqlite>
sqlite> SELECT typeof (_id) FROM asd;
        text
        integer
        real
sqlite>

^{Reference: https://www.sqlite.org/datatype3.html}

How to check if my database is valid when creating apps?

As I mentioned in the first prayer, its structure will always be valid for the Sqlite, but if you want a more rigid control, it is necessary to do a validation, just that. You can use regex to validate the table structure and use comparisons before entering a record in the table.