Re: match an IP address
От | Craig Ringer |
---|---|
Тема | Re: match an IP address |
Дата | |
Msg-id | 48D86CF6.2040907@postnewspapers.com.au обсуждение исходный текст |
Ответ на | Re: match an IP address (Tino Wildenhain <tino@wildenhain.de>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: