Need Current vwBotCharacterMobs table

[Nibiuno]

Does anyone have a current vwBotCharacterMobs table thats working with the 'laston' column? I need to fix my database to get RoF2 fully working.

Im getting an error about the 'laston' column not existing when you login to my test server (Test Server for Imperium), and not sure how to set that column up, or what Im missing SQL update wise since I ran the utility and updated the db.

[dagulus2]

This is what I have, no idea if its as upto date as it could be:

select _utf8'C' AS `mobtype`,
`c`.`id` AS `id`,
`c`.`name` AS `name`,
`c`.`class` AS `class`,
`c`.`level` AS `level`,
`c`.`last_login` AS `timelaston`,
`c`.`zone_id` AS `zoneid`
from `character_data` `c`
union all
select _utf8'B' AS `mobtype`,
`b`.`BotID` AS `id`,
`b`.`Name` AS `name`,
`b`.`Class` AS `class`,
`b`.`BotLevel` AS `level`,
0 AS `timelaston`,
0 AS `zoneid`
from `bots` `b`

[Nibiuno]

I have the same thing, and EMU on the latest code update as of last night throws that SQL error when you login. Weird.

[dagulus2]

Are you sure its asking for a 'laston' column, not 'timelaston' ?

[Uleat]

`c`.`last_login` AS `timelaston`,
`c`.`zone_id` AS `zoneid`
from `character_data` `c`

Are you on Linux, by chancef?

[dagulus2]

No very much on Windows.

[Nibiuno]

So that fixed it by editing the view so it displayed those fields. I also had an issue with the botbuffs table not having a column for dot_rune, added that, and that's fixed.


I guess some bot stuff was updated and there wasnt a SQL update or something.

[Uleat]

Bots are not a part of the primary deployment. Unfortunately, they are filius nullius...

I'll take a look at the `dot_rune` column at my next opportunity.

[Nibiuno]

Originally Posted by Uleat

Bots are not a part of the primary deployment. Unfortunately, they are filius nullius...

I'll take a look at the `dot_rune` column at my next opportunity.

I'm working on them but Im not nearly as good as any of you guys at programming. I will let you know once I get them working better on RoF2.

[rencro]

I'll take a look at the `dot_rune` column at my next opportunity.

Uleat, have a look here, this is sql/git/required/2014_02_20_buff_update.sql

-- UPDATE BUFF TABLES
ALTER TABLE `character_buffs` CHANGE `death_save_chance` `dot_rune` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `merc_buffs` CHANGE `DeathSaveSuccessChance` `dot_rune` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `botbuffs` CHANGE `DeathSaveSuccessChance` `dot_rune` INT(10) NOT NULL DEFAULT '0';

ALTER TABLE `character_buffs` CHANGE `death_save_aa_chance` `caston_x` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `merc_buffs` CHANGE `CasterAARank` `caston_x` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `botbuffs` CHANGE `CasterAARank` `caston_x` INT(10) NOT NULL DEFAULT '0';

ALTER TABLE `character_buffs` ADD `caston_y` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `merc_buffs` ADD `caston_y` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `botbuffs` ADD `caston_y` INT(10) NOT NULL DEFAULT '0';

ALTER TABLE `character_buffs` ADD `caston_z` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `merc_buffs` ADD `caston_z` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `botbuffs` ADD `caston_z` INT(10) NOT NULL DEFAULT '0';

ALTER TABLE `character_buffs` ADD `ExtraDIChance` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `merc_buffs` ADD `ExtraDIChance` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `botbuffs` ADD `ExtraDIChance` INT(10) NOT NULL DEFAULT '0';

ALTER TABLE `spells_new` CHANGE `not_reflectable` `reflectable` INT(11) NOT NULL DEFAULT '0';

Unfortunately if someone builds a db after that date they would have the wrong bot_buffs info as the "load_bots.sql" does not have this updated info in it.

From load_bots.sql as of 2-7-2015:

CREATE TABLE `botbuffs` (
	`BotBuffId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`BotId` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`SpellId` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`CasterLevel` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`DurationFormula` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`TicsRemaining` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`PoisonCounters` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`DiseaseCounters` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`CurseCounters` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`CorruptionCounters` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`HitCount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`MeleeRune` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`MagicRune` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`DeathSaveSuccessChance` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`CasterAARank` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`Persistent` TINYINT(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`BotBuffId`),
	KEY `FK_botbuff_1` (`BotId`),
	CONSTRAINT `FK_botbuff_1` FOREIGN KEY (`BotId`) REFERENCES `bots` (`BotID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

i.e. this issue has been here for a bit, but the new logging erros to gmsay ect is now catching it, pretty spiffy that logging stuff!!!!

EDIT.. from bot.cpp

std::string query = StringFormat("SELECT SpellId, CasterLevel, DurationFormula, TicsRemaining, "
                                    "PoisonCounters, DiseaseCounters, CurseCounters, CorruptionCounters, "
                                    "HitCount, MeleeRune, MagicRune, dot_rune, caston_x, Persistent, "
                                    "caston_y, caston_z, ExtraDIChance FROM botbuffs WHERE BotId = %u",
                                    GetBotID());

[Uleat]

Rencro, thanks for that!

I'll take that and see if anything else needs tweaking as well and put it in an update script.


We don't want load_bots to be the only source for the update because that would require a drop_bots to apply the change.

[Huppy]

Originally Posted by Nibiuno

Does anyone have a current vwBotCharacterMobs table thats working with the 'laston' column? I need to fix my database to get RoF2 fully working.

Im getting an error about the 'laston' column not existing when you login to my test server (Test Server for Imperium), and not sure how to set that column up, or what Im missing SQL update wise since I ran the utility and updated the db.

Not sure if this helps, but I went looking for a vwBotCharacterMobs table, because the world bootup was giving error that it didn't exist, but showing all was updated. I found this table and have no problems using UF client;

DROP VIEW IF EXISTS `vwBotCharacterMobs`;
CREATE VIEW `vwBotCharacterMobs` AS
select _utf8'C' AS `mobtype`,
`c`.`id` AS `id`,
`c`.`name` AS `name`,
`c`.`class` AS `class`,
`c`.`level` AS `level`,
`c`.`last_login` AS `timelaston`,
`c`.`zone_id` AS `zoneid` 
from `character_data` `c` 
union all
select _utf8'B' AS `mobtype`,
`b`.`BotID` AS `id`,
`b`.`Name` AS `name`,
`b`.`Class` AS `class`,
`b`.`BotLevel` AS `level`,
0 AS `timelaston`,
0 AS `zoneid` 
from `bots` `b`;
 
DELIMITER $$
 
DROP FUNCTION IF EXISTS `GetMobType` $$
CREATE FUNCTION `GetMobType` (mobname VARCHAR(64)) RETURNS CHAR(1)
BEGIN
    DECLARE Result CHAR(1);
 
    SET Result = NULL;
 
    IF (select count(*) from character_data where name = mobname) > 0 THEN
      SET Result = 'C';
    ELSEIF (select count(*) from bots where Name = mobname) > 0 THEN
      SET Result = 'B';
    END IF;
 
    RETURN Result;
END $$
 
DELIMITER ;
 
 
DROP VIEW IF EXISTS `vwGroups`;
CREATE VIEW `vwGroups` AS
  select g.groupid as groupid,
GetMobType(g.name) as mobtype,
g.name as name,
g.charid as mobid,
ifnull(c.level, b.BotLevel) as level
from group_id as g
left join character_data as c on g.name = c.name
left join bots as b on g.name = b.Name;
 
DROP VIEW IF EXISTS `vwBotGroups`;
CREATE VIEW `vwBotGroups` AS
select g.BotGroupId,
g.BotGroupName,
g.BotGroupLeaderBotId,
b.Name as BotGroupLeaderName,
b.BotOwnerCharacterId,
c.name as BotOwnerCharacterName
from botgroup as g
join bots as b on g.BotGroupLeaderBotId = b.BotID
join character_data as c on b.BotOwnerCharacterID = c.id
order by b.BotOwnerCharacterId, g.BotGroupName;

I'm new to this whole git/cmake system. But I like it so far. I was running a 4 year old server/db version until this week.

[Iadas]

I have a simple-minded question here. With the vwbotcharactermobs view listed at top, would you add it to my own database like this?

CREATE VIEW 'vwbotcharactermobs' (
select _utf8'C' AS `mobtype`,
`c`.`id` AS `id`,
`c`.`name` AS `name`,
`c`.`class` AS `class`,
`c`.`level` AS `level`,
`c`.`last_login` AS `timelaston`,
`c`.`zone_id` AS `zoneid`
from `character_data` `c`
union all
select _utf8'B' AS `mobtype`,
`b`.`BotID` AS `id`,
`b`.`Name` AS `name`,
`b`.`Class` AS `class`,
`b`.`BotLevel` AS `level`,
0 AS `timelaston`,
0 AS `zoneid`
from `bots` `b`
)

[Iadas]

I found out where to add the view using HeidiSQL and inserted the view. I am able to summon a bot now, but this is the error I get now once it's spawned:

[MySQL Error] 1054: Unknown column 'caston_x' in 'field list
--Select spelld, CasterLevel, DurationForumla, TicRemaining, PoisonCounters,
DiseaseCounters, CurseCounters, CorruptionCounters, HitCount, MeleeRune, Dot_Rune, caston_x, Persistent, caston_y, caston_z, ExtraDiChance FROM
botbuffs WHERE botId = 1
[MySQL Error] 1054: Unknown Column 'caston_x' in 'field_list
--INSERT INTO botbuffs (BotId, SpellId, CasterLevel, DurationForumla, TicsRemaining,
PoisonCounters, DiseaseCounters, CurseCounters, CorruptionCounters, HitCount,
MeleeRune, MagicRune, Dot_Rune, caston_x, Persistent, caston_y, caston_z,
ExtraDiChance) VALUES (1,756,2,11,0,0,0,0,0,0,0,0,0,0,0,0,0);

I am able to invite the bot into my group, but when I try to send it to attack using #Bot Group Attack, it'll run up to the mob and then just run back.