Hi,

I'm trying to find a tool that can implement additional layer wrapping around the SQLite file. The wrapper is for:

1. Validation (type,min,max,regex) during UPDATE and INSERT commands.
2. backup & Roll-back options.
3. Some kind of visual interface for update.
4. Validation during SELECT. Using default, if invalid.

1 is the most important feature. 4 is the least important.

Do you have any suggestion?

I don't have an answer on all your requirements.

1 Validation (type,min,max)

I suppose with min and max you mean a range constraint on possible values. That and type can be done by SQLite itself by defining proper column constraints in the schema, and to some extent, with STRICT tables.

2 backup & Roll-back options.

An SQLite database is a file plus (often) a journal file. Any backup/restore/version control mechanism for files will also work for SQLite.

3 Some kind of visual interface for update.

4 Validation during SELECT. Using default, if invalid.

If I needed that, I would write a simple web application.

ad 1) If validation of input values is not done in the presentation layer (user interface), then it should be done in the schema via appropriate constraints

ad 2) There is a backup api, which you could use to save consistent copies of your database that you can easily revert to. You can only rollback the single currently active transaction, albeit to specific sub-transactions.

ad 3) There are numerous GUI tools for viewing and altering SQLite databases.

ad 4) This is properly done in the same place as no 1. If you have proper constraints in the schema, then there can be no "invalid values" and defaults should also be defined if different from NULL.

Per-row validation sounds like a CHECK clause to me, unless you need to cross-reference with other rows and/or tables to do the validation when you'd instead want a TRIGGER (as trigger programs have far fewer restrictions). Either way, putting validation like that in is totally normal, a standard thing to do with a database. Naturally, I advise testing that things do the right thing with validation prior to going to production; it's easy to make silly errors in validation code and you should make sure that failures produce comprehensible errors as they can otherwise be very difficult to debug.

For tools to access it, there are many existing GUI tools or you can write your own that is custom to your application. There are many languages with bindings to SQLite; you have freedom to do what you wish.

As your processes seem to be failing at validation, you need to add it to the schema. CHECK constraints and BEFORE TRIGGERS are the way to go.