Re: match an IP address

My post at the bottom.

On 9/23/08, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:

>
> Dodgy forum software. Lots of it uses an IP address as a fake username for
> unregistered users, rather than doing the sensible thing and tracking both
> IP address and (if defined) username.
>
> How I'd want to do this if I was designing the setup from scratch would
> probably be:
>
> --
> -- Track user identies
> --
> CREATE TABLE user (
> user_id SERIAL PRIMARY KEY,
> user_name VARCHAR NOT NULL,
> user_retired BOOLEAN
> -- and whatever else you want to keep track of about them
> );
>
> -- This index enforces unique user names across active users,
> -- permitting re-use of usernames for since deleted users without
> -- losing information about the previous user of that name's activity
> -- or losing the inability to differentiate between the old and new
> -- owners of that name.
> --
> -- I'd revoke DELETE rights on `user' or use a rule that rewrote DELETE
> -- to an UPDATE user SET user_retired = 't'.
> --
>
> CREATE UNIQUE INDEX user_active_name
> ON user(user_name)
> WHERE (NOT user_retired)
>
> -- Now, say your problem described in your post is with user activity
> -- logging:
>
> CREATE TABLE access_log (
> -- blah blah
> user_id INTEGER REFERENCES user(user_id),
> access_ip cidr NOT NULL
> );
>
> CREATE INDEX access_log_ip ON access_log(access_ip);
>
> CREATE INDEX access_user
> ON access_log(user_id)
> WHERE (user_id NOT NULL);
>
>
>
> In other words: always store the IP address, and if the user involved was a
> registered user store a reference to their user ID as well. Store a
> reference to a globally unique user identity number rather than the user
> name, and permit reuse of user names without losing information about
> distinct username owners.
>
> If you wanted you could use string user IDs and do away with the synthetic
> "user_id" key I've used above, but I suspect you'd regret it down the track.
>
> If you wanted to look up activity that might be identifed by IP address or
> by username, a query like this would do the trick and would simulate the
> behaviour your forum software is used to, including the ability of a user to
> create a username that's an IP address to throw the whole thing into chaos:
>
> SELECT * FROM access_user
> WHERE COALESCE(user_id, to_char(access_ip)) = matchstring;
>
> --
> Craig Ringer
>

No. You have no idea what the design is for. Not forum crap.

What happens when you need to store in a table the activity log?

ACTIVITY_ID
USER_STAMP (currently user_id or ip for registered and unregistered resp.)

The idea of storing IP for users is already being done. So what?
Everytime they "do" something, you do not store their IP. Why would
you? Just store their user id. For unregistered ones however, we store
the IP because there is nothing else. There is no user ID for them.
What's your logic for getting a user ID for unregistered guys --
invent one automagically?

Finally, this SQL:

WHERE COALESCE(user_id, to_char(access_ip)) = matchstring;

Ever tried this crap on a table of 10 million records on a live
website, where this query is happening at 3000 times per second? No
such function schtick will match the raw speed of a simpler indexed
query. Or did you mean my index should contain the COALESCE already?

Tino, I wasn't talking about sessions. This is not about session IDs.
A session ID is useless the next time a certain IP address "does
something" on the site. You want a commonality for non-registered
users across many different sessions. (Apart from the fact that
session IDs are usually long hashes which take up space in the table
and in the index)