How to check if a SQLite3 Database is valid?

Question

How to check if a SQLite3 Database is valid?

Navigation

#1 by (3 votes)

2

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

For example, if I inadvertently typed NOOT instead of NOT or INTEGET instead of INTEGER, I expected SQL to point to a return error saying that the SQL key words were incorrect. Or at least that the input is invalid.

But that's not what happens. See 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 what gender I purposely inserted the data type as INTEGET instead of INTEGER and apparently 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 once again, instead of using INTEGER I put INTEGET and SQL does not report error. It adds information, even though it is, in my view, an invalid data type.

My question is, if SQL handles entries with errors, how can I check if my database is valid when creating applications?

sql sqlite3

asked by anonymous 25.02.2018 / 21:15

1 answer

How to normalize this small database? "New" autoload PHP 7

score 3 · Answer 1

As you can read on the FAQ , this is a feature and not a BUG from SQLite . Unlike other databases such as MySQL for example, SQLite does not use a rigid typing.

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

This allows the system to use what they call type affinity . This system will convert the 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: link

How do I check if my database is valid when creating applications?

As I mentioned in the first sentence, your structure will always be valid for SQLite , but if you want tighter control, you need to do a validation, just that. You can use regex to validate the table structure and use comparisons before inserting a record into the table.