sqlite ip address storage

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 f开发者_JAVA百科ield 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?

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.

Update: If you choose to use INTEGER, you will be limited to storing IPv4 addresses only, because SQLite can only store 64 bit integers, whereas IPv6 are 128 bit. However, because ip2long only works with IPv4 the range is not the only issue.