Kick/ban log and bans in database

[Matthias]

Why should we keep the old method intact? Also, I'm currently really busy with school, but I might have some time later this week to try & get this done. Nice work !

[MrBondt]

So that Nuwani keeps working until that's updated. Also to be able to switch back to the old way in case we missed something crucial/something fucks up/fill in the blank.

[MrBondt]

Updated the import script a bit so that only new items are inserted into the database and immediately dumped the script in a crontab. It will now update the database every day, just after midnight. This will stay in place until the gamemode supports the database as well.

The recommended query to insert a new log item to the database is as follows:

Code:

INSERT INTO samp_logs (log_date, log_type, log_address, log_user_name, log_user_id, log_subject_name, log_subject_id, log_reason) VALUES (NOW(), "kick", 703236693, "MrBondt", 11464, "Matthias", 42659, "Have nice day");

Please do not hesitate to ask me anything. It's important this gets implemented correctly, so we all need to understand what's going on.

[Matthias]

I have holidays now, but every day is completely (and by completely I mean either I sleep somewhere else or somebody sleeps here) filled up until tuesday, so I'll try to look at it then, sorry for the delay.

[MrBondt]

Bump.

I'd love to see this being implemented sometime soon

[Russell]

I wrote a parser which normalizes all kblog.txt entries (well, after discarding 0.03 in a more sensible format. This should be used to import the entries when the time is there.

[MrBondt]

I.. had already done that. Sorry for not making that clear enough. It's actually already running for months on the gameserver. I'll attach my script as well.

[MrBondt]

I've revised the table structures for the ban and log tables which I already made once and pasted in this topic.

Code:

DROP TABLE IF EXISTS bans;
CREATE TABLE IF NOT EXISTS bans (
  ban_id int(4) NOT NULL AUTO_INCREMENT,
  log_id int(4) NOT NULL,
  ip_address int(4) NOT NULL,
  PRIMARY KEY (ban_id),
  KEY ip_address (ip_address),
  KEY log_id (log_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Code:

DROP TABLE IF EXISTS logs;
CREATE TABLE IF NOT EXISTS logs (
  log_id int(4) NOT NULL AUTO_INCREMENT,
  log_date datetime NOT NULL,
  log_type enum('warn','kick','ban','unban','note','banip') NOT NULL,
  user_id int(4) DEFAULT NULL,
  nickname varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  subject_user_id int(4) DEFAULT NULL,
  subject_nickname varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  description varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (log_id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Are we going to support rangebans too? And if so, how? I did include a possibility for this in the bans table (ip_address_end). The idea is that the beginning of the range is stored in ip_address and the end in ip_address_end. There's ban_type to see if it's a normal ban or a rangeban (might be unneeded since you can check if ip_address_end is null). If we're going to do this, should we include namebans (and/or accountbans) too? Rangebans and nickbans are possible future improvements, as well as automatic expiration time for bans.

In the logs table, I included both the nickname and user_id for both the person doing the action (kick, ban, note, etc) as well as the person the log is for (subject_nickname and subject_user_id). This way we can link the logs easily to the user, as well as see what the nicknames were at the time (since we don't have a nickname history currently). The imported logs will not have the user ids linked since the user id cannot be accurately determined.

I think the rest is pretty self-explanatory. What do you think?

[Jay]

Perhaps we can add support for hostmask banning, eg *.some-isp.com.  We can use this threaded plugin for reverse DNS lookups.

EDIT: I also suggest including an optional profile ID field. This makes it possible to link banning to player accounts so we can automatically re-ban them if they attempt to login. This can also introduce communication with the forum and make it easier to ban a player from all of our services if need be.

[MrBondt]

I've updated my import script to the new structure. I've ran an initial import and added a cronjob to the "samp" user's crontab so that new entries in kblog.txt are stored in the database as well. We have to make sure we don't forget to remove this when the new ban manager is live

Attached is the updated script.