Kick/ban log and bans in database

[MrBondt]

Lately I've been working more and more around the limitations of the !why command by using shell commands from IRC. Of course, this could be fixed by reinstating the ban portal, but in the current setup this is actually not an easy task. I know this because I've seriously tried to fix it, a long long time ago. (I see you asking: why did nothing happen after that? Answer: because this is LVP.)

Anyway. I don't intend to continue with riddling the LVP channels with my work-around skills, so therefore I'm starting this topic to initiate a change in key architectures. Everything will be saved in the database. Everything. I'm saying everything. So if you still find something that's stored in a file and I didn't mention anything about that in this post, reply.

Bans will be stored in the database. The kick/ban log (kblog.txt) will be stored in the database as well. This allows automatic synchronization to the web server, as well as automatic back-ups. Especially the synchronization part is interesting here, like for instance, for restoring the ban portal.

Now, obviously, a change like this isn't done in a day or two. That's why I want to do this in steps. Here goes.

1. MrBondt creates a database structure and imports all current items.
   
2. The gamemode is updated to also save kicks and bans to the database. That's right, a hybrid solution.
   
3. MrBondt updates Nuwani to use the database.
   
4. MrBondt (or some other web dev, if we still have others) implements the ban portal into the website.
   

When everything is found to be working fine, we can disable kblog.txt and stuff.

As you can see, most work is on my side, however, I need the help from one or two gamemode developers. As I work fulltime now, I won't have much time to work on this. I also want to work on some other projects in my free time, and sometimes do something else aside from programming. So this is pretty much a very long term project.

Discuss.

[Matthias]

I have no problems helping with updating the gamemode, if you want I could also help with the online ban log (even though I'm not able to write PHP as beautiful as you can :p, but it'll work).

And do you want this to go live together with LVP 2.94 or beforehand? I would prefer a seperate update for this.

[MrBondt]

Well, first I want to think up a proper database design and cook some import script. So until that's done (at least the database design), you won't have anything to work with anyway.

When's it done? Well, not soonish. Expect it the soonest 2 weeks from now. I don't know when LVP 2.94 is meant to go live however, but we can always wait.

[Matthias]

I could try to make the import script once I have the database structure if you want.

[Richard]

Just started a Crew Committee meeting and this came up. Any update on this?

[MrBondt]

Code:

DROP TABLE IF EXISTS samp_bans;
CREATE TABLE IF NOT EXISTS samp_bans (
  ban_id int(4) NOT NULL AUTO_INCREMENT,
  ban_log_id int(4) NOT NULL,
  ban_date datetime NOT NULL,
  ban_type enum('single','range') CHARACTER SET latin1 NOT NULL,
  ban_address int(4) NOT NULL,
  ban_address_end int(4) NOT NULL,
  PRIMARY KEY (ban_id),
  KEY ban_address (ban_address),
  KEY ban_address_end (ban_address_end),
  KEY ban_log_id (ban_log_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table 'samp_logs'
--

DROP TABLE IF EXISTS samp_logs;
CREATE TABLE IF NOT EXISTS samp_logs (
  log_id int(4) NOT NULL AUTO_INCREMENT,
  log_date datetime NOT NULL,
  log_type enum('warn','kick','ban','unban','note','banip','sexy','fucked') CHARACTER SET latin1 NOT NULL,
  log_address int(4) DEFAULT NULL,
  log_user_name varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  log_user_id int(4) DEFAULT NULL,
  log_subject_name varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  log_subject_id int(4) DEFAULT NULL,
  log_reason varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (log_id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The structure has changed a bit while I was trial-and-erroring the import.

The preferred way to insert new rows into samp_logs is as follows:

• log_id - None, this is automatically incremented.
  
• log_date - The current date, should be obvious.
  
• log_type - The type of log item, one of 'warn','kick','ban','unban','note','banip'.
  
• log_address - The IP address belonging to the log item. May be NULL in some cases (for example a note or a warn).
  
• log_user_name - The nickname of the person adding the log item (typically an administrator).
  
• log_user_id - The ID of the person adding the log item. May be NULL in some cases (like when adding a note or unbanning from IRC).
  
• log_subject_name - The nickname of the person getting raped.
  
• log_subject_id - The ID of the person getting a log item. May be NULL, for example when unregistered.
  
• log_reason - The thing we came for: the actual message.

[MrBondt]

I'm currently working on an import script for the old log items. Pretty far already, will finish tomorrow.

[Kase]

Cool, let me know! I'll be on IRC

[MrBondt]

I finished the import. I've also made a couple of changes to the table structure, which I've outlined in my edited post above.

About the import:

• I didn't bother with looking up IDs for subject names or user names in order to get the data in there sooner.
  
• IP addresses are missing from entries of date 2009-11-29 20:52:09 and older since we started storing them after that date.
  
• Items of date 2009-02-24 00:50:08 and older did not include the year number. These have been derived by just counting down with the month number.

[MrBondt]

http://forum.sa-mp.nl/index.php?topic=30167.0

Things to do on relative short term now:

• Update gamemode to save log items to the database, while keeping the old method intact as well.
  
• Update Nuwani to use the database. No way of having a hybrid solution here though, so Nuwani will switch completely from the get-go.