Re: match an IP address

Tino Wildenhain wrote:
> Phoenix Kiula wrote:
>>>> my IP addresses are stored in a TEXT type field. that field can
>>>> actually
>>>> contain usernames like 'joao' or 'scott' and it can contain IP
>>>> addresses....
>>
>>
>>
>> I think this is common DB design on many websites that have registered
>> user IDs.
>
> Is it? Name one! Sounds like crappy design to me.
>
>> My humble suggestion would be to make another column in the table
>> called "user_registered" or something. Make this an int(1). If the
>> user is registered, value is 1, otherwise 0.
>
> Maybe "registration" (aka authentication) is mixed up with simple
> session handling?
>
>> To update your existing data onetime, run an SQL update query looking
>> for IP pattern with 3 dots (which is likely IP address and not user
>> id) or by the INET conditions in previous suggestions.
>
> It could also just be a fdn (hostname) - still I'm a bit puzzled
> how a username and an IP-address can get into the same field.

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