summaryrefslogtreecommitdiff
path: root/sql-files
diff options
context:
space:
mode:
Diffstat (limited to 'sql-files')
-rw-r--r--sql-files/logs.sql12
-rw-r--r--sql-files/main.sql26
-rw-r--r--sql-files/upgrade_svn12975.sql24
-rw-r--r--sql-files/upgrade_svn12975_log.sql20
-rw-r--r--sql-files/upgrade_svn12975_view.sql17
5 files changed, 78 insertions, 21 deletions
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;
@@ -473,20 +471,6 @@ CREATE TABLE `sc_data` (
) 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;