Document DB Schemas and create scripts

[Pooch11]

Currently there is no indication of DB Schemas used within the project, even though Repository.cs makes calls to the DB during startup and it is difficult to deduce what fields are in each table and their respective datatypes.

The docs should provide entry-level SQL/MySQL script which would create the necessary tables and columns (and data types) needed to spin up the project.

So far I have encountered the following tables;

• hotspots (Id , Zone, Description, Faction, MinimumLevel, Waypoints ..)
• npcs
• blacklistedmobs
• travelpaths

TODO:

• Enumerate Tables
• Enumerate Fields & data types
• Create SQL script to make the required tables programmatically (these can be external snippets)

[DrewKestell]

This is a hobby project that I provide no official support for. Please feel free to create these scripts and submit a PR.

[Pooch11]

Still a WIP as far as Primary/Foreign keys and some datatype adjustments but these are the minimum requirements to get the project running without crashing upon startup due to unknown data bindings. There may also seems to be an indication that a SpellsDB table will be implemented in the future.

CREATE TABLE hotspots(
id int,
zone text,
description nvarchar(4000),
faction nvarchar(4000),
minimumLevel int,
waypoints nvarchar(4000),
innKeeper boolean,
innkeeperId int,
ammoVendor boolean,
ammoVendorId int,
repairVendor boolean,
repairVendorId int,
safeforGrinding boolean,
travelpathId int
);

CREATE TABLE blacklistedmobs(
id int,
name nvarchar(4000),
guid nvarchar(4000)
);

CREATE TABLE travelpaths(
id int,
name nvarchar(50),
waypoints nvarchar(4000)
);

CREATE TABLE commands (
id int,
player nvarchar(50),
command nvarchar(50),
args nvarchar(50)
);

CREATE TABLE reportsignatures(
id int,
player nvarchar(50),
commandId int
)

CREATE TABLE npcs (
id int,
name nvarchar(50),
isInnKeeper boolean,
sellsAmmo boolean,
repairs boolean,
quest boolean,
horde boolean,
alliance boolean,
positionx float,
positiony float,
positionz float,
zone text);

[ozjohnson]

@Pooch11 Did you make any more progress on your sql scripts? I'm using Azure/MS SQL which doesn't appear to have the boolean data type (I'm relatively new to SQL). I changed all boolean values to int, but when trying to add an NPC (for example), I'm getting an error. stackoverflow suggests using a BIT type, so I might try that to see if it helps. EDIT: As expected, BIT type didn't fix anything.

Here is the error I get when trying to add an NPC (but similar error for any UI operation):

Using this script (modified from yours to allow for MS SQL on Azure - tried with both int and bit as replacement for boolean)

CREATE TABLE hotspots(
id int,
zone text,
description nvarchar(4000),
faction nvarchar(4000),
minimumLevel int,
waypoints nvarchar(4000),
innKeeper bit,
innkeeperId int,
ammoVendor bit,
ammoVendorId int,
repairVendor bit,
repairVendorId int,
safeforGrinding bit,
travelpathId int
);

CREATE TABLE blacklistedmobs(
id int,
name nvarchar(4000),
guid nvarchar(4000)
);

CREATE TABLE travelpaths(
id int,
name nvarchar(50),
waypoints nvarchar(4000)
);

CREATE TABLE commands (
id int,
player nvarchar(50),
command nvarchar(50),
args nvarchar(50)
);

CREATE TABLE reportsignatures(
id int,
player nvarchar(50),
commandId int
)

CREATE TABLE npcs (
id int,
name nvarchar(50),
isInnKeeper bit,
sellsAmmo bit,
repairs bit,
quest bit,
horde bit,
alliance bit,
positionx float,
positiony float,
positionz float,
zone text);

Would be curious to hear if you've got it working and which WoW client you're using (I've only managed to get it started on TBC - but not functional).

[Pooch11]

I was using this for vanilla, so be sure to look in Repository.cs file for any changes that could be called from the TBC version. I'm not aware.
That's where I derived the SQL statements to tables.
As far as the conversion to MySQL to AzureMS SQL that looks like the right (and most agreed upon) approach, you could add a default 0 perhaps. Otherwise try tinyint(1)

Were the tables created in your database with no errors? Do they exist? The Exception you're getting has to do with the number of columns or the name, I'm not sure about the typing. 🤔

My bootstrapper also starts to work, but fails under certain class loads/conditions. It also fails to load the player, I suspect from the spells ID table which is needs to be populated. Other times its a lot of warden spam, but that may be a different issue all together due to different clients

[ozjohnson]

Thanks @Pooch11. I started out trying to run the bot against 1.12.1, but it would throw an exception on the EnumerateVisibleObjects function (System.AccessViolationException - attempted to read or write protected memory) as soon as I would login the character, causing wow.exe to exit. Drew mentioned that he didn't think Classic was functional currently and there are a number of TODOs throughout the Classic code segments. He said he'd take a look at the Warden code if/when he has time. I also got the Warden PageScan and MemScan failures found in the WardenDisabler.cs.

I switched to TBC (cmangos) as he mentioned it is working, and was able to login without the exception. The bot UI is functioning, however as soon as I try to select any of the required fields (for example, setting the NPC attributes), it throws the SQL client exceptions from the output above.

The tables are exist in Azure and I can insert data into them via Azure Data Studio. I've tried adding data via the bot UI for a couple of different functions (blacklistedmobs, addnpcs, travelpaths), but each gives me the same exception (column name/number mismatch). I could be misreading it, but this seems to tell me that it's actually reaching the database and is seeing that the bot insert is expecting the table format to be different (data type or number of columns).

From what I can tell, the repository.cs is using a common schema for all three wow versions.

I'll try your suggestion re the different data types and see if that helps.

My preference is also getting the 1.12.1 version working but it seems like its a bit farther off in terms of completion and my level of understanding is novice level, so thought I'd try something that seems to be relatively complete.

EDIT: I've made each column nullable and changed the Boolean data types to tinyint as per your suggestion but still getting the same error. I've also tried it with the blacklistedmobs after changing it to all NULLABLE and get the same error, which is odd because the function only passes a single value from what I can see in the log: AddBlacklistedMob(UInt64 guid) to a table that has 3 columns: id, name, guid.

[DrewKestell]

Hey guys, I'm going to try to take a look at this over the weekend. I just created a Discord server to make it easier for folks to coordinate if you want to make contributions - feel free to join here.

[DrewKestell]

Thanks for the work on this guys. I added a script "SqlSchema.SQL" to the repo root that will create all table dependencies. You should be able to use either Azure SQL, or a local install of SQL.