Verify SQLite table schema

schema, sql, sqlite

Solution

Possible solutions: `PRAGMA schema_version;` or `PRAGMA user_version;` commands.

There's a few variants of using them for verifying table's scheme, but I'm sure you will find appropriate way

Problem

Before working with table I need to verify that table's schema fully corresponds with executing code. I know that there's a command `.schema tablename` the result of which can be hashed and compared with actual schema's hash. The problem is that stored schema contains all the excessive chars used when the table was created (repeating tabs, spaces, new line chars), and if I'll change the number of spaces in the schema inside the next version of my code, the table will not be recognized. I don't want to parse every column with all of it's properties manually. I can't just remove repeated spaces by split&join or regexp, because sometimes they're not repeated but still excessive (for example, near opening bracket). It looks like a very common task - verifying the table's schema - but I can't see the way to implement that efficiently. UPD One more possible way I am thinking about: creating a temporary table and compare it's schema with the target table's using some internal sqlite functions. Can it work?

Original source