From 1624d1d57db3cfde3b4f42a55580f5a1e742f28e Mon Sep 17 00:00:00 2001 From: ultramage Date: Sat, 26 Jul 2008 20:45:57 +0000 Subject: Merged the /loginmerge branch (topic:192754) * the login server storage, ipban and logging systems have been abstracted and now provide a common interface; the rest has been merged into a single login server core (no more login/login_sql duplicity) * storage systems are now added via compiler options (WITH_SQL / WITH_TXT) * multiple storage engines can be compiled in at the same time, and the config option account.engine defines which one will be used. * due to MySQL autoincrement limitations, accounts with id '0' will not be supported; account IDs from this point on should start from '1'. * login_log() functions now again record IP addresses in dotted format, not as 4-byte integers (undo from r6868). * removed config options that defined column names in the login table * removed `memo` and `error message` columns from login db/savefile * moved `loginlog` table to the logs database * added sql files upgrade_svn12975.sql and upgrade_svn12975_log.sql * due to changes to the login table layout, I added an !optional! sql file (upgrade_svn12975_view.sql) that will provide a certain degree of backwards compatibility with existing software; read the instructions inside carefully! * moved third-party includes/libs to a separate directory * updated project files / makefiles Changed the way GM levels are handled * removed conf/gm_account.txt * added the gm level column to the txt savefile (after 'email' column) * gm level information is now transferred along with account data For open problems see bugreport:1889. git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@13000 54d463be-8e91-2dee-dedb-b68131a5f0ec --- sql-files/logs.sql | 12 ++++++++++++ sql-files/main.sql | 26 +++++--------------------- sql-files/upgrade_svn12975.sql | 24 ++++++++++++++++++++++++ sql-files/upgrade_svn12975_log.sql | 20 ++++++++++++++++++++ sql-files/upgrade_svn12975_view.sql | 17 +++++++++++++++++ 5 files changed, 78 insertions(+), 21 deletions(-) create mode 100644 sql-files/upgrade_svn12975.sql create mode 100644 sql-files/upgrade_svn12975_log.sql create mode 100644 sql-files/upgrade_svn12975_view.sql (limited to 'sql-files') diff --git a/sql-files/logs.sql b/sql-files/logs.sql index 09895263b..b46b34355 100644 --- a/sql-files/logs.sql +++ b/sql-files/logs.sql @@ -113,3 +113,15 @@ CREATE TABLE `chatlog` ( INDEX (`src_accountid`), INDEX (`src_charid`) ) ENGINE=MyISAM AUTO_INCREMENT=1 ; + +#Database: log +#Table: loginlog +CREATE TABLE `loginlog` ( + `time` datetime NOT NULL default '0000-00-00 00:00:00', + `ip` varchar(15) NOT NULL default '', + `user` varchar(23) NOT NULL default '', + `rcode` tinyint(4) NOT NULL default '0', + `log` varchar(255) NOT NULL default '', + INDEX (`ip`) +) ENGINE=MyISAM ; + diff --git a/sql-files/main.sql b/sql-files/main.sql index 1dd36bbc1..5b9f1a313 100644 --- a/sql-files/main.sql +++ b/sql-files/main.sql @@ -421,17 +421,15 @@ CREATE TABLE `login` ( `account_id` int(11) unsigned NOT NULL auto_increment, `userid` varchar(23) NOT NULL default '', `user_pass` varchar(32) NOT NULL default '', - `lastlogin` datetime NOT NULL default '0000-00-00 00:00:00', `sex` enum('M','F','S') NOT NULL default 'M', - `logincount` mediumint(9) unsigned NOT NULL default '0', `email` varchar(39) NOT NULL default '', `level` tinyint(3) NOT NULL default '0', - `error_message` smallint(11) unsigned NOT NULL default '0', - `connect_until` int(11) unsigned NOT NULL default '0', - `last_ip` varchar(100) NOT NULL default '', - `memo` smallint(11) unsigned NOT NULL default '0', - `ban_until` int(11) unsigned NOT NULL default '0', `state` int(11) unsigned NOT NULL default '0', + `unban_time` int(11) unsigned NOT NULL default '0', + `expiration_time` int(11) unsigned NOT NULL default '0', + `logincount` mediumint(9) unsigned NOT NULL default '0', + `lastlogin` datetime NOT NULL default '0000-00-00 00:00:00', + `last_ip` varchar(100) NOT NULL default '', PRIMARY KEY (`account_id`), KEY `name` (`userid`) ) ENGINE=MyISAM AUTO_INCREMENT=2000000; @@ -472,20 +470,6 @@ CREATE TABLE `sc_data` ( KEY (`char_id`) ) ENGINE=MyISAM; --- --- Table structure for table `loginlog` --- - -DROP TABLE IF EXISTS `loginlog`; -CREATE TABLE `loginlog` ( - `time` datetime NOT NULL default '0000-00-00 00:00:00', - `ip` int(10) unsigned NOT NULL default '0', - `user` varchar(23) NOT NULL default '', - `rcode` tinyint(4) NOT NULL default '0', - `log` varchar(255) NOT NULL default '', - INDEX (`ip`) -) ENGINE=MyISAM; - -- -- Table structure for table `mail` -- diff --git a/sql-files/upgrade_svn12975.sql b/sql-files/upgrade_svn12975.sql new file mode 100644 index 000000000..a8f5e3818 --- /dev/null +++ b/sql-files/upgrade_svn12975.sql @@ -0,0 +1,24 @@ +START TRANSACTION; + +-- delete columns +ALTER TABLE `login` DROP `error_message`; +ALTER TABLE `login` DROP `memo`; + +-- rename columns +ALTER TABLE `login` CHANGE `connect_until` `expiration_time` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0'; +ALTER TABLE `login` CHANGE `ban_until` `unban_time` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0'; + +-- reorder columns +ALTER TABLE `login` MODIFY `sex` enum('M','F','S') NOT NULL default 'M' AFTER `user_pass`; +ALTER TABLE `login` MODIFY `email` varchar(39) NOT NULL default '' AFTER `sex`; +ALTER TABLE `login` MODIFY `level` tinyint(3) NOT NULL default '0' AFTER `email`; +ALTER TABLE `login` MODIFY `state` int(11) unsigned NOT NULL default '0' AFTER `level`; +ALTER TABLE `login` MODIFY `unban_time` int(11) unsigned NOT NULL default '0' AFTER `state`; +ALTER TABLE `login` MODIFY `expiration_time` int(11) unsigned NOT NULL default '0' AFTER `unban_time`; +ALTER TABLE `login` MODIFY `logincount` mediumint(9) unsigned NOT NULL default '0' AFTER `expiration_time`; + +-- change ip format +ALTER TABLE `loginlog` CHANGE `ip` `ip` VARCHAR( 15 ) NOT NULL default ''; +UPDATE `loginlog` SET `ip` = inet_ntoa(`ip`); + +COMMIT; diff --git a/sql-files/upgrade_svn12975_log.sql b/sql-files/upgrade_svn12975_log.sql new file mode 100644 index 000000000..c6900e606 --- /dev/null +++ b/sql-files/upgrade_svn12975_log.sql @@ -0,0 +1,20 @@ +-- NOTE: change `ragnarok` and `log` to whatever your database names are. + +START TRANSACTION; + +CREATE TABLE `log`.`loginlog` ( + `time` datetime NOT NULL default '0000-00-00 00:00:00', + `ip` varchar( 15 ) NOT NULL , + `user` varchar( 23 ) NOT NULL default '', + `rcode` tinyint( 4 ) NOT NULL default '0', + `log` varchar( 255 ) NOT NULL default '', + KEY `ip` ( `ip` ) +) ENGINE = MYISAM; + +INSERT INTO `log`.`loginlog` + SELECT * + FROM `ragnarok`.`loginlog` ; + +DROP TABLE `ragnarok`.`loginlog` ; + +COMMIT; diff --git a/sql-files/upgrade_svn12975_view.sql b/sql-files/upgrade_svn12975_view.sql new file mode 100644 index 000000000..8d5d5906b --- /dev/null +++ b/sql-files/upgrade_svn12975_view.sql @@ -0,0 +1,17 @@ +-- The statements below will create a 'view' (virtual table) that mimics +-- the previous `login` table layout. You can use this hack to make your db +-- compatible with older eathena servers or control panels. +-- Note: also adjust the account.sql.account_db setting in login_athena.conf. +-- Note: if your CP does not have a config setting for the `login` table name +-- you'll have to either modify its code or do some table renaming. + +START TRANSACTION; + +-- create dummy columns, needed to make the view insertable +ALTER TABLE `login` ADD `error_message` SMALLINT UNSIGNED NOT NULL; +ALTER TABLE `login` ADD `memo` SMALLINT UNSIGNED NOT NULL; + +-- create the view +CREATE VIEW `login_view` ( `account_id`, `userid`, `user_pass`, `lastlogin`, `sex`, `logincount`, `email`, `level`, `error_message`, `connect_until`, `last_ip`, `memo`, `ban_until`, `state` ) AS SELECT `account_id`, `userid`, `user_pass`, `lastlogin`, `sex`, `logincount`, `email`, `level`, `error_message`, `expiration_time`, `last_ip`, `memo`, `unban_time`, `state` FROM `login`; + +COMMIT; -- cgit v1.2.3-60-g2f50