Show
Ignore:
Timestamp:
11/19/08 13:27:52 (17 years ago)
Author:
yumileroy
Message:

[svn] * SQL cleanup
* Removed unneeded revision SQL, since everyone will be starting off at a single revision.
* Left the characters SQL in case someone needs to convert a MaNGOS characters DB
* Final dumps of realmd / world / and characters DB schemas

Original author: runningnak3d
Date: 2008-10-14 12:30:35-05:00

Files:
1 modified

Legend:

Unmodified
Added
Removed

  • trunk/sql/characters.sql

    r44 r45  
    33-- Host: localhost    Database: characters 
    44-- ------------------------------------------------------ 
    5 -- Server version       5.0.45-Debian_1ubuntu3.1-log 
     5-- Server version       5.0.34-log 
    66 
    77/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 
    … …  
    142142/*!40000 ALTER TABLE `bugreport` DISABLE KEYS */; 
    143143/*!40000 ALTER TABLE `bugreport` ENABLE KEYS */; 
    144 UNLOCK TABLES; 
    145  
    146 -- 
    147 -- Table structure for table `characters` 
    148 -- 
    149  
    150 DROP TABLE IF EXISTS `characters`; 
    151 CREATE TABLE `characters` ( 
    152   `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier', 
    153   `account` int(11) unsigned NOT NULL default '0' COMMENT 'Account Identifier', 
    154   `data` longtext, 
    155   `name` varchar(12) NOT NULL default '', 
    156   `race` tinyint(3) unsigned NOT NULL default '0', 
    157   `class` tinyint(3) unsigned NOT NULL default '0', 
    158   `position_x` float NOT NULL default '0', 
    159   `position_y` float NOT NULL default '0', 
    160   `position_z` float NOT NULL default '0', 
    161   `map` int(11) unsigned NOT NULL default '0' COMMENT 'Map Identifier', 
    162   `dungeon_difficulty` tinyint(1) unsigned NOT NULL DEFAULT '0', 
    163   `orientation` float NOT NULL default '0', 
    164   `taximask` longtext, 
    165   `online` tinyint(3) unsigned NOT NULL default '0', 
    166   `cinematic` tinyint(3) unsigned NOT NULL default '0', 
    167   `totaltime` int(11) unsigned NOT NULL default '0', 
    168   `leveltime` int(11) unsigned NOT NULL default '0', 
    169   `logout_time` bigint(20) unsigned NOT NULL default '0', 
    170   `is_logout_resting` tinyint(3) unsigned NOT NULL default '0', 
    171   `rest_bonus` float NOT NULL default '0', 
    172   `resettalents_cost` int(11) unsigned NOT NULL default '0', 
    173   `resettalents_time` bigint(20) unsigned NOT NULL default '0', 
    174   `trans_x` float NOT NULL default '0', 
    175   `trans_y` float NOT NULL default '0', 
    176   `trans_z` float NOT NULL default '0', 
    177   `trans_o` float NOT NULL default '0', 
    178   `transguid` bigint(20) unsigned NOT NULL default '0', 
    179   `gmstate` int(11) unsigned NOT NULL default '0', 
    180   `stable_slots` tinyint(1) unsigned NOT NULL default '0', 
    181   `at_login` int(11) unsigned NOT NULL default '0', 
    182   `zone` int(11) unsigned NOT NULL default '0', 
    183   `death_expire_time` bigint(20) unsigned NOT NULL default '0', 
    184   `taxi_path` text, 
    185   PRIMARY KEY  (`guid`), 
    186   KEY `idx_account` (`account`), 
    187   KEY `idx_online` (`online`), 
    188   KEY `idx_name` (`name`) 
    189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System'; 
    190  
    191 -- 
    192 -- Dumping data for table `characters` 
    193 -- 
    194  
    195 LOCK TABLES `characters` WRITE; 
    196 /*!40000 ALTER TABLE `characters` DISABLE KEYS */; 
    197 /*!40000 ALTER TABLE `characters` ENABLE KEYS */; 
    198144UNLOCK TABLES; 
    199145 
    … …  
    420366  `prepositional` varchar(12) NOT NULL default '', 
    421367  PRIMARY KEY  (`id`), 
    422   KEY owner_key (`owner`) 
     368  KEY `owner_key` (`owner`) 
    423369) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 
    424370 
    … …  
    517463  `friend` int(11) unsigned NOT NULL default '0' COMMENT 'Friend Global Unique Identifier', 
    518464  `flags` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Friend Flags', 
    519   `note` varchar(48) NOT NULL DEFAULT '' COMMENT 'Friend Note', 
     465  `note` varchar(48) NOT NULL default '' COMMENT 'Friend Note', 
    520466  PRIMARY KEY  (`guid`,`friend`,`flags`), 
    521467  KEY `guid` (`guid`), 
    … …  
    588534  `guid` int(11) unsigned NOT NULL default '0', 
    589535  `ticket_text` text, 
    590   `ticket_lastchange` TIMESTAMP ON  UPDATE  CURRENT_TIMESTAMP  NOT  NULL  DEFAULT CURRENT_TIMESTAMP, 
     536  `ticket_lastchange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
    591537  PRIMARY KEY  (`ticket_id`) 
    592538) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System'; 
    … …  
    618564  `tut7` int(11) unsigned NOT NULL default '0', 
    619565  PRIMARY KEY  (`account`,`realmid`), 
    620   KEY acc_key (`account`) 
     566  KEY `acc_key` (`account`) 
    621567) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System'; 
    622568 
    … …  
    628574/*!40000 ALTER TABLE `character_tutorial` DISABLE KEYS */; 
    629575/*!40000 ALTER TABLE `character_tutorial` ENABLE KEYS */; 
     576UNLOCK TABLES; 
     577 
     578-- 
     579-- Table structure for table `characters` 
     580-- 
     581 
     582DROP TABLE IF EXISTS `characters`; 
     583CREATE TABLE `characters` ( 
     584  `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier', 
     585  `account` int(11) unsigned NOT NULL default '0' COMMENT 'Account Identifier', 
     586  `data` longtext, 
     587  `name` varchar(12) NOT NULL default '', 
     588  `race` tinyint(3) unsigned NOT NULL default '0', 
     589  `class` tinyint(3) unsigned NOT NULL default '0', 
     590  `position_x` float NOT NULL default '0', 
     591  `position_y` float NOT NULL default '0', 
     592  `position_z` float NOT NULL default '0', 
     593  `map` int(11) unsigned NOT NULL default '0' COMMENT 'Map Identifier', 
     594  `dungeon_difficulty` tinyint(1) unsigned NOT NULL default '0', 
     595  `orientation` float NOT NULL default '0', 
     596  `taximask` longtext, 
     597  `online` tinyint(3) unsigned NOT NULL default '0', 
     598  `cinematic` tinyint(3) unsigned NOT NULL default '0', 
     599  `totaltime` int(11) unsigned NOT NULL default '0', 
     600  `leveltime` int(11) unsigned NOT NULL default '0', 
     601  `logout_time` bigint(20) unsigned NOT NULL default '0', 
     602  `is_logout_resting` tinyint(3) unsigned NOT NULL default '0', 
     603  `rest_bonus` float NOT NULL default '0', 
     604  `resettalents_cost` int(11) unsigned NOT NULL default '0', 
     605  `resettalents_time` bigint(20) unsigned NOT NULL default '0', 
     606  `trans_x` float NOT NULL default '0', 
     607  `trans_y` float NOT NULL default '0', 
     608  `trans_z` float NOT NULL default '0', 
     609  `trans_o` float NOT NULL default '0', 
     610  `transguid` bigint(20) unsigned NOT NULL default '0', 
     611  `gmstate` tinyint(3) unsigned NOT NULL default '0', 
     612  `stable_slots` tinyint(1) unsigned NOT NULL default '0', 
     613  `at_login` int(11) unsigned NOT NULL default '0', 
     614  `zone` int(11) unsigned NOT NULL default '0', 
     615  `death_expire_time` bigint(20) unsigned NOT NULL default '0', 
     616  `taxi_path` text, 
     617  PRIMARY KEY  (`guid`), 
     618  KEY `idx_account` (`account`), 
     619  KEY `idx_online` (`online`), 
     620  KEY `idx_name` (`name`) 
     621) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System'; 
     622 
     623-- 
     624-- Dumping data for table `characters` 
     625-- 
     626 
     627LOCK TABLES `characters` WRITE; 
     628/*!40000 ALTER TABLE `characters` DISABLE KEYS */; 
     629/*!40000 ALTER TABLE `characters` ENABLE KEYS */; 
    630630UNLOCK TABLES; 
    631631 
    … …  
    666666-- 
    667667 
    668 CREATE TABLE `game_event_condition_save` (                     
    669  `event_id` mediumint(8) unsigned NOT NULL, 
    670  `condition_id` mediumint(8) unsigned NOT NULL default '0', 
    671  `done` float default '0', 
    672  PRIMARY KEY  (`event_id`,`condition_id`) 
    673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
     668DROP TABLE IF EXISTS `game_event_condition_save`; 
     669CREATE TABLE `game_event_condition_save` ( 
     670  `event_id` mediumint(8) unsigned NOT NULL, 
     671  `condition_id` mediumint(8) unsigned NOT NULL default '0', 
     672  `done` float default '0', 
     673  PRIMARY KEY  (`event_id`,`condition_id`) 
     674) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
     675 
     676-- 
     677-- Dumping data for table `game_event_condition_save` 
     678-- 
     679 
     680LOCK TABLES `game_event_condition_save` WRITE; 
     681/*!40000 ALTER TABLE `game_event_condition_save` DISABLE KEYS */; 
     682/*!40000 ALTER TABLE `game_event_condition_save` ENABLE KEYS */; 
     683UNLOCK TABLES; 
    674684 
    675685-- 
    … …  
    677687-- 
    678688 
     689DROP TABLE IF EXISTS `game_event_save`; 
    679690CREATE TABLE `game_event_save` ( 
    680  `event_id` mediumint(8) unsigned NOT NULL, 
    681  `state` tinyint(3) unsigned NOT NULL default '1', 
    682  `next_start` timestamp NOT NULL default '0000-00-00 00:00:00', 
    683  PRIMARY KEY  (`event_id`) 
    684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
     691  `event_id` mediumint(8) unsigned NOT NULL, 
     692  `state` tinyint(3) unsigned NOT NULL default '1', 
     693  `next_start` timestamp NOT NULL default '0000-00-00 00:00:00', 
     694  PRIMARY KEY  (`event_id`) 
     695) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
     696 
     697-- 
     698-- Dumping data for table `game_event_save` 
     699-- 
     700 
     701LOCK TABLES `game_event_save` WRITE; 
     702/*!40000 ALTER TABLE `game_event_save` DISABLE KEYS */; 
     703/*!40000 ALTER TABLE `game_event_save` ENABLE KEYS */; 
     704UNLOCK TABLES; 
     705 
     706-- 
     707-- Table structure for table `group_instance` 
     708-- 
     709 
     710DROP TABLE IF EXISTS `group_instance`; 
     711CREATE TABLE `group_instance` ( 
     712  `leaderGuid` int(11) unsigned NOT NULL default '0', 
     713  `instance` int(11) unsigned NOT NULL default '0', 
     714  `permanent` tinyint(1) unsigned NOT NULL default '0', 
     715  PRIMARY KEY  (`leaderGuid`,`instance`), 
     716  KEY `instance` (`instance`) 
     717) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
     718 
     719-- 
     720-- Dumping data for table `group_instance` 
     721-- 
     722 
     723LOCK TABLES `group_instance` WRITE; 
     724/*!40000 ALTER TABLE `group_instance` DISABLE KEYS */; 
     725/*!40000 ALTER TABLE `group_instance` ENABLE KEYS */; 
     726UNLOCK TABLES; 
     727 
     728-- 
     729-- Table structure for table `group_member` 
     730-- 
     731 
     732DROP TABLE IF EXISTS `group_member`; 
     733CREATE TABLE `group_member` ( 
     734  `leaderGuid` int(11) unsigned NOT NULL, 
     735  `memberGuid` int(11) unsigned NOT NULL, 
     736  `assistant` tinyint(1) unsigned NOT NULL, 
     737  `subgroup` smallint(6) unsigned NOT NULL, 
     738  PRIMARY KEY  (`leaderGuid`,`memberGuid`) 
     739) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Groups'; 
     740 
     741-- 
     742-- Dumping data for table `group_member` 
     743-- 
     744 
     745LOCK TABLES `group_member` WRITE; 
     746/*!40000 ALTER TABLE `group_member` DISABLE KEYS */; 
     747/*!40000 ALTER TABLE `group_member` ENABLE KEYS */; 
     748UNLOCK TABLES; 
    685749 
    686750-- 
    … …  
    718782UNLOCK TABLES; 
    719783 
    720 -- ---------------------------- 
    721 -- Table structure for group_instance 
    722 -- ---------------------------- 
    723 DROP TABLE IF EXISTS `group_instance`; 
    724 CREATE TABLE `group_instance` ( 
    725   `leaderGuid` int(11) unsigned NOT NULL default '0', 
    726   `instance` int(11) unsigned NOT NULL default '0', 
    727   `permanent` tinyint(1) unsigned NOT NULL default '0', 
    728   PRIMARY KEY  (`leaderGuid`,`instance`), 
    729   KEY `instance` (`instance`) 
    730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
    731  
    732 -- 
    733 -- Dumping data for table `group_instance` 
    734 -- 
    735  
    736 LOCK TABLES `group_instance` WRITE; 
    737 /*!40000 ALTER TABLE `group_instance` DISABLE KEYS */; 
    738 /*!40000 ALTER TABLE `group_instance` ENABLE KEYS */; 
    739 UNLOCK TABLES; 
    740  
    741 -- 
    742 -- Table structure for table `group_member` 
    743 -- 
    744  
    745 DROP TABLE IF EXISTS `group_member`; 
    746 CREATE TABLE `group_member` ( 
    747   `leaderGuid` int(11) unsigned NOT NULL, 
    748   `memberGuid` int(11) unsigned NOT NULL, 
    749   `assistant` tinyint(1) unsigned NOT NULL, 
    750   `subgroup` smallint(6) unsigned NOT NULL, 
    751   PRIMARY KEY  (`leaderGuid`,`memberGuid`) 
    752 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Groups'; 
    753  
    754 -- 
    755 -- Dumping data for table `group_member` 
    756 -- 
    757  
    758 LOCK TABLES `group_member` WRITE; 
    759 /*!40000 ALTER TABLE `group_member` DISABLE KEYS */; 
    760 /*!40000 ALTER TABLE `group_member` ENABLE KEYS */; 
    761 UNLOCK TABLES; 
    762  
    763784-- 
    764785-- Table structure for table `guild` 
    … …  
    830851  `item_guid` int(11) unsigned NOT NULL default '0', 
    831852  `item_entry` int(11) unsigned NOT NULL default '0', 
    832   PRIMARY KEY  (`guildid`,`tabid`,`slotid`), 
     853  PRIMARY KEY  (`guildid`,`TabId`,`SlotId`), 
    833854  KEY `guildid_key` (`guildid`) 
    834855) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
    … …  
    904925  `NewRank` tinyint(2) NOT NULL COMMENT 'New rank(in case promotion/demotion)', 
    905926  `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time' 
    906 ) ENGINE = InnoDB DEFAULT CHARSET = latin1 COMMENT 'Guild Eventlog'; 
     927) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Guild Eventlog'; 
    907928 
    908929-- 
    … …  
    978999 
    9791000-- 
     1001-- Table structure for table `has_logged_in_before` 
     1002-- 
     1003 
     1004DROP TABLE IF EXISTS `has_logged_in_before`; 
     1005CREATE TABLE `has_logged_in_before` ( 
     1006  `guid` int(11) unsigned NOT NULL default '0', 
     1007  PRIMARY KEY  (`guid`) 
     1008) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ImpConfig check'; 
     1009 
     1010-- 
     1011-- Dumping data for table `has_logged_in_before` 
     1012-- 
     1013 
     1014LOCK TABLES `has_logged_in_before` WRITE; 
     1015/*!40000 ALTER TABLE `has_logged_in_before` DISABLE KEYS */; 
     1016/*!40000 ALTER TABLE `has_logged_in_before` ENABLE KEYS */; 
     1017UNLOCK TABLES; 
     1018 
     1019-- 
    9801020-- Table structure for table `instance` 
    9811021-- 
    … …  
    10021042UNLOCK TABLES; 
    10031043 
    1004 -- ---------------------------- 
    1005 -- Table structure for instance_reset 
    1006 -- ---------------------------- 
     1044-- 
     1045-- Table structure for table `instance_reset` 
     1046-- 
     1047 
    10071048DROP TABLE IF EXISTS `instance_reset`; 
    10081049CREATE TABLE `instance_reset` ( 
    … …  
    12241265  PRIMARY KEY  (`petitionguid`,`playerguid`) 
    12251266) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Guild System'; 
     1267 
    12261268-- 
    12271269-- Dumping data for table `petition_sign` 
    … …  
    12361278-- Table structure for table `saved_variables` 
    12371279-- 
     1280 
    12381281DROP TABLE IF EXISTS `saved_variables`; 
    1239 CREATE TABLE `saved_variables` (                                                                              
    1240     `NextArenaPointDistributionTime` timestamp NOT NULL 
     1282CREATE TABLE `saved_variables` ( 
     1283  `NextArenaPointDistributionTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 
    12411284) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Variable Saves'; 
    12421285 
    … …  
    12591302/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 
    12601303 
    1261 -- Dump completed on 2008-01-10 11:37:06 
     1304-- Dump completed on 2008-10-14 13:27:52