sqlite ip address storage

Question

I'm trying to store IP Address and a PHP's session id, to "uniquely" identify a user, to manage a user queue to control a (one) hardware device through internet with a token. When a user has the token, has permission to control the device for X time (2 minutes). Then I need also a timestamp, the time in which the user has asked for the token.

I found problems to find the correct field type for the IP Address and the timestamp in SQlite.

I'm working with PHP, so it would be desirable an easy way to retrieve a queue from the database matching a "text" cookie session id and a IP, using timestamp to order and filter.

What's the better way to storage them in a SQlite database?

Should I use an integer or text field? and there are functions to work with those types?

Solution

SQLite is weak type, but supports type affinity. At all SQLite only support a small range of "column types" ("type affinities")

INTEGER
REAL
NUMERIC
TEXT
BLOB
NONE

However, in your case you can choose: You can store a timestamp as UNIX-timestamp in INTEGER, or as datetime formated string in TEXT. See the section "1.2 Date and Time Datatype" in the document provided by the link above. There are Date And Time Functions to help you handle this kind of data.

The IP can be stored as INTEGER after converting it into one: ip2long(). Or you store it as TEXT too. I suggest to use the former one.

Comments

[Jiri Otoupal イり オトウパー]

This is not what he asked, obviously everybody that uses sqlite knows these types. But he asked how to save IP address, yes you can save it as every type. But it will be slow.

[KingCrunch]

Don't mix up the prolog with the actual answer: "The IP can be stored as INTEGER after converting it into one: ip2long(). Or you store it as TEXT too. I suggest to use the former one.". However, one can argue, that I missed to mentioned, that the actual use-case may be more relevant. For example, if You need the IP only to read and print it somewhere (no index), the TEXT-format is more convenient without much performance loss and without conversion.