I want to store IP Address using c++ in sqlite3 DB in hex format. I am using TEXT as the format for storing hex values. I want to perform a check on the value being inserted in the DB. The value range I want to check for is 0x00000000 - 0xFFFFFFFF. For INTEGER type you can check by - CHECK (num BETWEEN 0 AND 100). Is there any way to add check constraint for Hex Values?
If there is an smarter way to store IP address in SQLITE please share with me.
Thanks
I think you have two main choices: (a) store as hex (i.e. text) and check "hex conformity", or (b) store as integer and print it as hex when reading data.
There may be several reasons for preferring the one over the other, e.g. if the application actually provides and receives integer values. Anyway, some examples for option (a) and option (b).
Option (a) - store as text and check hex conformity
the probably simplest way is to use a check based on GLOB as remarked by CL:
value TEXT CONSTRAINT "valueIsHex" CHECK(value GLOB "0x[a-fA-F0-9][a-fA-F0-9][a-fA-F0-9][a-fA-F0-9][a-fA-F0-9][a-fA-F0-9][a-fA-F0-9][a-fA-F0-9]")
If logic goes beyond that supported by GLOB, you could install a user defined function, either a general regexp() function or a custom function for your specific needs. This could be the case, if you want to store a complete IP-address in one field and still want to do a conformity check. For help on general regexp() function, confer this SO answer. For help on user defined functions confer, for example, this StackOverflow answer.
Option (b) - store as int and print as hex
If your application is actually working with integers, then change your db schema to integer and add a check like:
value INTEGER CONSTRAINT "valueIsValid" CHECK(value <= 0xFFFFFFFF)
For convenience, you can still print the value as hex using a query (or defining a corresponding view) like the following:
select printf('%08X', value) from theTable
