Compiling with Bots

[Fluff]

Hi,

I recently returned to the community here, I have been visiting off and on for what seems like over a decade to check in on progress. I recently set up a private Linux server with the instructions on the wiki (which btw- were excellent, thank you). Initially I tried to compile with bots but I kept getting an error when trying to source the load_bots.sql.

I tried to determine the problem but there seems to be a missing character_ table which the script needs to make a view and some sprocs. After some investigation I learned this table is being broken out from a blob currently as part of improvements.
http://www.eqemulator.org/forums/sho...ght=character_

Is getting bots to work as simply as adjusting this script to use the new character_* tables or are code changes needed as well? Has anyone already done this?

Thanks


**REFERENCE load_bots.sql examples**

DROP VIEW IF EXISTS `vwBotCharacterMobs`;
CREATE VIEW `vwBotCharacterMobs` AS
select 'C' as mobtype,
c.id,
c.name,
c.class,
c.level,
c.timelaston,
c.zoneid
from character_ as c
union all
select '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 as b;


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_ 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 $$

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_ 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_ as c on b.BotOwnerCharacterID = c.id
order by b.BotOwnerCharacterId, g.BotGroupName;

[Uleat]

I would change all of your `character_` references to `character_old` in your local load_bots.sql (or a copy of it.)

That should let you install the script.

Then, starting the server should catch the invalid reference and convert to the new views and function.


EDIT: I believe there are only 3 views and the `GetMobType` function that are affected by this change.

[Fluff]

Thanks for the suggestion. This is a newly built server sourced from the PEQ 9-25-14 DB. In this database I do not have a character_old table present.

[Uleat]

Hmm... Did you delete it or has world not performed the auto-conversion yet?


IF `character_data` exists, and you can't reference the "old" table, then you will need to change your load_bots views to
match the fields listed here: https://github.com/EQEmu/Server/comm...54c307f25b2534

Don't forget to add the two '_utf8' casts.


If the default 'peq' now has the post blob conversion `character_data`, then I will update the scripts file(s) as soon as I get a chance.

[Fluff]

I didn't drop the table. The PEQ DB from 9-25 and 10-11 both have a character_data table. I'll see if I can create the view based on the code you linked.

Thanks.

[Uleat]

Yeah, sorry about that...

Someone had mentioned the load_bots.sql needs to be updated at some point..but, I was waiting for the default peq to switch...

I was not aware (my fault) that it had been updated.


I'll update the sql tonight and copy the old one to a deprecated folder for reference.

[Uleat]

Ok, I updated the eqemu repo's load/drop bots script files and added a load_bots_old.sql to the 'deprecated' folder of bot scripts.


- IF you are using a post-player profile blob conversion database, you will need to use load_bots.sql

- IF you are using a pre-player profile blob conversion database, you will need to use load_bots_old.sql


Use this query to determine what version you have, if you are unsure:

SHOW TABLES LIKE 'character_data';

If you get a hit, then you have the new version..no hit is the old version.


There is an auto-conversion process in the server code to convert the required schema when it's updated.


ref: https://github.com/EQEmu/Server/comm...570d3f2a28a5e8


The 'peq' distro has the old script..and should for the time being..at least until any issues have been identified and worked out here first.

[Fluff]

Thanks Uleat, I'll snap shot my server and give it a try today or tomorrow.

[akathanam]

Slight problem with the updated load_bots.sql:

The views vwbotcharactermobs, vwgroups, vwbotgroups, vwguildmembers are created lower-case, whereas the code references them with mixed case, this does not work on linux (at least, in my case, or i am just to stupid...).

[Uleat]

No..I used my windows database for verification!


I made the assumption that tables were all lowercase in MySQL because they are always created in lower case regardless of mixed or not..whereas,
functions/procedures allow mixed case.


Gimme a few minutes and I'll commit a change for that.

(The 'Linux Ninja' strikes again!)

[Uleat]

Nope..I totally missed what you said about views there and it wasn't a table issue...


The code and the scripts have been updated to their correct names.

There is also a script to fix any auto-converted databases located in the repo.

ref: https://github.com/EQEmu/Server/blob...e_View_Fix.sql


Totally my fault... I used what I saw in the database for 'tags' when I was writing that auto-covert snippet..and it just crept over into
the script files through cut-and-paste :/

[Fluff]

I had to manually delete all the bot tables probably because of the mix of attempts in the past, but after doing so I was able to source the script and compile and now have working bots on Linux.

Thanks Uleat.

[Uleat]

I do apologize for that..and thanks for bringing it to light!


I coded the script to add back the primary key that was dropped when bots are added in `guild_members`.

That may have been what was killing the script...

Do you remember if that was the error you were getting with drop_bots? ('duplicate primary key')

[Fluff]

It did break there, but when I ran drop_bots.sql it also didn't drop any of the tables due to the same problem.

It is trying to drop the primary key (which didn't exist) not add a duplicate though (in load_bots.sql).

[Uleat]

Ok, I added a note in 'drop_bots.sql' to run an ALTER TABLE `guild_members` DROP PRIMARY KEY; query since this is the script that we absolutely want to run.

If I can figure out how to do an 'IF EXISTS' on that, I'll update the code..otherwise, it will take a little user intervention until such time.