Handy: Update SQL, List of all the needed SQL updates since Antrix

[[Shon3m]]

Hello, Since i see alot of people are intrested in a list of needed updates to keep their ascent database up to date, I've made a list.
Please note me if there is anything wrong, missing let me know and i'll fix it.
I'll keep this list up to date aswell
World DB
Rev 1918

DROP TABLE IF EXISTS `item_randomprop_groups`;
  CREATE TABLE `item_randomprop_groups` (
    `entry_id` int(30) NOT NULL,
    `randomprops_entryid` int(30) NOT NULL,
    `chance` float NOT NULL,
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  DROP TABLE IF EXISTS `item_randomsuffix_groups`;
  CREATE TABLE `item_randomsuffix_groups` (
    `entry_id` int(30) NOT NULL,
    `randomsuffix_entryid` int(30) NOT NULL,
    `chance` float NOT NULL,
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1743

CREATE TABLE `spell_proc_data` (
   `name_hash` int(30) NOT NULL,
   `proc_chance` int(30) NOT NULL,
   `forced_proc_flags` int(30) NOT NULL default '-1',
   PRIMARY KEY  (`name_hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1730

alter table creature_spawns add column channel_spell int(30) not null default 0;
  alter table creature_spawns add column channel_target_sqlid int(30) not null default 0;
  alter table creature_spawns add column channel_target_sqlid_creature int(30) not null default 0;

Rev 1603

alter table creature_names change displayid male_displayid int(30) not null;
   alter table creature_names add column female_displayid int(30) not null after male_displayid;
   alter table creature_names add column unknown_int1 int(30) not null default 0 after female_displayid;
   alter table creature_names add column unknown_int2 int(30) not null default 0 after unknown_int1;
   alter table creature_names change unk2 unknown_float1 float(0) not null;
   alter table creature_names change unk3 unknown_float2 float(0) not null;
   alter table creature_proto add column fly_speed float(0) default "14.0" not null;
   alter table creature_proto add column extra_a9_flags int(30) default 0 not null;

Rev 1542

 
ALTER TABLE creature_proto ADD walk_speed FLOAT DEFAULT "2.5" NOT NULL AFTER death_state;
   ALTER TABLE creature_proto ADD run_speed FLOAT DEFAULT "8" NOT NULL AFTER walk_speed;

Rev 1472

 
INSERT INTO totemspells (spellId, spellToCast1, spellToCast2, spellToCast3) VALUES (2894, 32982, 0, 0);
   INSERT INTO totemspells (spellId, spellToCast1, spellToCast2, spellToCast3) VALUES (2062, 33663, 0, 0);

Rev 1143

CREATE TABLE `zoneguards` (
     `zoneId` int(10) unsigned NOT NULL,
     `hordeEntry` int(10) unsigned default NULL,
     `allianceEntry` int(10) unsigned default NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   -- Example
   INSERT INTO `zoneguards` VALUES ('12', '0', '68');

Rev 1132

alter table creature_proto change health minhealth int(30) unsigned not null;
       alter table creature_proto add column maxhealth int(30) unsigned not null after minhealth;
       update creature_proto set maxhealth=minhealth;
       alter table creature_proto change level minlevel int(30) unsigned not null;
       alter table creature_proto add column maxlevel int(30) unsigned not null after minlevel;
       update creature_proto set maxlevel=minlevel;
       alter table creature_proto add column invisibility_type int(30) unsigned not null;
       alter table creature_proto add column death_state int(30) unsigned not null;

Rev 1041

 ALTER TABLE creatureloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE fishingloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE itemloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE objectloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE pickpocketingloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE prospectingloot MODIFY column percentchance float(0) NOT NULL default 0;
       ALTER TABLE skinningloot MODIFY column percentchance float(0) NOT NULL default 0;
 
       alter table creatureloot add column heroicpercentchance float(0) default 0;
       alter table creatureloot add column mincount int(30) default 1;
       alter table creatureloot add column maxcount int(30) default 1;
 
       alter table fishingloot add column heroicpercentchance float(0) default 0;
       alter table fishingloot add column mincount int(30) default 1;
       alter table fishingloot add column maxcount int(30) default 1;
 
       alter table objectloot add column heroicpercentchance float(0) default 0;
       alter table objectloot add column mincount int(30) default 1;
       alter table objectloot add column maxcount int(30) default 1;
 
       alter table itemloot add column heroicpercentchance float(0) default 0;
       alter table itemloot add column mincount int(30) default 1;
       alter table itemloot add column maxcount int(30) default 1;
 
       alter table prospectingloot add column heroicpercentchance float(0) default 0;
       alter table prospectingloot add column mincount int(30) default 1;
       alter table prospectingloot add column maxcount int(30) default 1;
 
       alter table pickpocketingloot add column heroicpercentchance float(0) default 0;
       alter table pickpocketingloot add column mincount int(30) default 1;
       alter table pickpocketingloot add column maxcount int(30) default 1;
 
       alter table skinningloot add column heroicpercentchance float(0) default 0;
       alter table skinningloot add column mincount int(30) default 1;
       alter table skinningloot add column maxcount int(30) default 1;

Rev 941

CREATE TABLE `creature_staticspawns` (
`id` int(30) NOT NULL AUTO_INCREMENT,
`entry` int(30) NOT NULL,
`map` int(11) NOT NULL DEFAULT '0',
`x` float NOT NULL,
`y` float NOT NULL,
`z` float NOT NULL,
`0` float NOT NULL,
`movetype` int(11) NOT NULL DEFAULT '0',
`displayid` int(11) NOT NULL,
`factionid` int(11) NOT NULL DEFAULT '0',
`flags` int(30) NOT NULL DEFAULT '0',
`bytes` int(30) NOT NULL DEFAULT '0',
`bytes2` int(30) NOT NULL DEFAULT '0',
`emote_state` int(11) NOT NULL DEFAULT '0',
`respawnNpcLink` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `gameobject_staticspawns` (
`id` int(30) NOT NULL AUTO_INCREMENT,
`entry` int(30) NOT NULL,
`map` int(11) NOT NULL DEFAULT '0',
`x` float NOT NULL,
`y` float NOT NULL,
`z` float NOT NULL,
`facing` float NOT NULL,
`0` float NOT NULL,
`01` float NOT NULL,
`02` float NOT NULL,
`03` float NOT NULL,
`state` int(11) NOT NULL DEFAULT '0',
`flags` int(30) NOT NULL DEFAULT '0',
`faction` int(11) NOT NULL DEFAULT '0',
`scale` float NOT NULL,
`respawnNpcLink` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Rev 896

 alter table worldmap_info add column required_quest int(30) not null default 0 after lvl_mod_a;
       alter table worldmap_info add column required_itemid int(30) not null default 0 after lvl_mod_a;

Rev 736

 ALTER TABLE  `creature_proto` ADD COLUMN `money` INT (30) DEFAULT '0' NOT NULL  AFTER `boss`;

Rev 484

 CREATE TABLE `reputation_creature_onkill` (
         `creature_id` int(30) NOT NULL,
         `faction_change_alliance` int(30) NOT NULL,
         `faction_change_horde` int(30) NOT NULL,
         `change_value` int(30) NOT NULL,
         `rep_limit` int(30) NOT NULL,
         KEY `index` (`creature_id`)
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
       alter table quests add RewRepLimit int(11) default 0 not null after RewRepValue2;

Rev 231

UPDATE totemspells SET spellToCast1= 5729 WHERE spellId=5730

Character DB:
Rev2068

 CREATE TABLE `playeritems_external` (
  `ownerguid` int(10) unsigned NOT NULL DEFAULT '0',
  `guid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `entry` int(10) unsigned NOT NULL DEFAULT '0',
  `creator` int(10) unsigned NOT NULL DEFAULT '0',
  `count` int(10) unsigned NOT NULL DEFAULT '0',
  `charges` int(10) unsigned NOT NULL DEFAULT '0',
  `flags` int(10) unsigned NOT NULL DEFAULT '0',
  `randomprop` int(10) unsigned NOT NULL DEFAULT '0',
  `randomsuffix` int(30) NOT NULL,
  `itemtext` int(10) unsigned NOT NULL DEFAULT '0',
  `durability` int(10) unsigned NOT NULL DEFAULT '0',
  `containerslot` int(11) DEFAULT '-1' COMMENT '',
  `slot` tinyint(4) NOT NULL DEFAULT '0',
  `enchantments` longtext COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`guid`),
  UNIQUE KEY `guid` (`guid`),
  KEY `ownerguid` (`ownerguid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
alter table mailbox add column external_attached_item_guid bigint(20) not null default 0 after attached_item_guid;

Rev 1923

 
alter table playeritems add column randomsuffix int(30) default 0 after 
randomprop;

Rev 1844

ALTER TABLE playerpets DROP COLUMN autocastspell;
       UPDATE playerpets SET actionbar="";

Rev 1786

alter table characters add column custom_faction int(30) default 0 not null after gender;

Rev 1649

CREATE TABLE `arenateams` (
   `id` int(30) NOT NULL,
   `type` int(30) NOT NULL,
   `leader` int(30) NOT NULL,
   `name` varchar(150) NOT NULL,
   `emblemstyle` int(40) NOT NULL,
   `emblemcolour` bigint(40) NOT NULL,
   `borderstyle` int(40) NOT NULL,
   `bordercolour` bigint(40) NOT NULL,
   `backgroundcolour` bigint(40) NOT NULL,
   `rating` int(30) NOT NULL,
   `data` varchar(150) NOT NULL,
   `ranking` int(30) NOT NULL,
   `player_data1` varchar(60) NOT NULL,
   `player_data2` varchar(60) NOT NULL,
   `player_data3` varchar(60) NOT NULL,
   `player_data4` varchar(60) NOT NULL,
   `player_data5` varchar(60) NOT NULL,
   `player_data6` varchar(60) NOT NULL,
   `player_data7` varchar(60) NOT NULL,
   `player_data8` varchar(60) NOT NULL,
   `player_data9` varchar(60) NOT NULL,
   `player_data10` varchar(60) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1602

 
alter table characters change lastDailyReset honorPointsToAdd INT(10) NOT NULL; 
  update characters set honorPointsToAdd = 0;
  alter table characters change charterId arenaPoints INT(10) NOT NULL;
  update characters set arenaPoints = 0;

Rev 1598

 
CREATE TABLE `server_settings` (
   `setting_id` varchar(200) NOT NULL,
   `setting_value` int(50) NOT NULL,
   PRIMARY KEY  (`setting_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1566

CREATE TABLE `charters` (
   `charterId` int(30) NOT NULL,
   `charterType` int(30) NOT NULL default '0',
   `leaderGuid` int(20) unsigned NOT NULL default '0',
   `guildName` varchar(32) NOT NULL default '',
   `itemGuid` bigint(40) unsigned NOT NULL default '0',
   `signer1` int(10) unsigned NOT NULL default '0',
   `signer2` int(10) unsigned NOT NULL default '0',
   `signer3` int(10) unsigned NOT NULL default '0',
   `signer4` int(10) unsigned NOT NULL default '0',
   `signer5` int(10) unsigned NOT NULL default '0',
   `signer6` int(10) unsigned NOT NULL default '0',
   `signer7` int(10) unsigned NOT NULL default '0',
   `signer8` int(10) unsigned NOT NULL default '0',
   `signer9` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`charterId`),
   UNIQUE KEY `leaderGuid` (`leaderGuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1393

 
CREATE TABLE `banned_names` (
    `name` varchar(30) NOT NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Rev 1387

 
alter table playeritems drop column guid;
     alter table playeritems add column guid bigint(10) NOT NULL auto_increment primary key after ownerguid;

Rev 1361

DROP TABLE `playerpets`;
 
  CREATE TABLE `playerpets` (
    `ownerguid` bigint(20) NOT NULL default '0',
    `petnumber` int(11) NOT NULL default '0',
    `name` varchar(21) NOT NULL default '',
    `entryid` bigint(20) NOT NULL default '0',
    `data` longtext NOT NULL,
    `xp` int(11) NOT NULL default '0',
    `active` tinyint(1) NOT NULL default '0',
    `level` int(11) NOT NULL default '0',
    `happiness` int(11) NOT NULL default '0',
    `actionbar` varchar(200) NOT NULL default '',
    `happinessupdate` int(11) NOT NULL default '0',
    `summon` int(11) NOT NULL default '0',
    `autocastspell` int(11) NOT NULL default '0',
    `loyaltypts` int(11) NOT NULL default '0',
    `loyaltyupdate` int(11) NOT NULL default '0'
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Also

 
QueryResult * result = WorldDatabase.Query("SELECT COUNT(*) FROM banned_names WHERE name = '%s'", name.c_str());

getting error when trying to add the newest one
something about syntax error near line 5

 
DROP TABLE IF EXISTS `item_randomprop_groups`;
CREATE TABLE `item_randomprop_groups` (
`entry_id` int(30) NOT NULL,
`randomprops_entryid` int(30) NOT NULL,
`chance` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `item_randomsuffix_groups`;
CREATE TABLE `item_randomsuffix_groups` (
`entry_id` int(30) NOT NULL,
`randomsuffix_entryid` int(30) NOT NULL,
`chance` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Will Guy's Enjoy Props Go To Walla Over At Ascent Forums

[Spurven]

Repost i think

[[Shon3m]]

lol o well >.< i did look for it on forums never found it so...posted it
also u can't see ur a lazy leecher....

[tonks1]

nice job +rep

[Gastricpenguin]

Yea its a repost, JulianX Made this like exact post

[tonks1]

here ya go i did some snoopin and found it if ya need proof

http://www.mmowned.com/forums/ascent...revisions.html

Code:
DROP TABLE IF EXISTS `item_randomprop_groups`;
CREATE TABLE `item_randomprop_groups` (
`entry_id` int(30) NOT NULL,
`randomprops_entryid` int(30) NOT NULL,
`chance` float NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `item_randomsuffix_groups`;
CREATE TABLE `item_randomsuffix_groups` (
`entry_id` int(30) NOT NULL,
`randomsuffix_entryid` int(30) NOT NULL,
`chance` float NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
is new tho