# pacman -S mariadb mariadb-clients luarocks
# mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
# systemctl start mariadb
# luarocks install luasql-mysql MYSQL_INCDIR=/usr/include/mysql

Prepare Databases

The pfQuest extractor supports VMaNGOS and CMaNGOS databases. By default, VMaNGOS is used vanilla and CMaNGOS is used for TBC. For CMaNGOS translations, the Mangos-Extras project is used.

Create Users And Permissions

mariadb <<< '
    DROP DATABASE IF EXISTS `pfquest`;
    DROP DATABASE IF EXISTS `vmangos`;
    DROP DATABASE IF EXISTS `cmangos-tbc`;
    DROP DATABASE IF EXISTS `turtle`;

    CREATE USER 'mangos'@'localhost' IDENTIFIED BY 'mangos';

    CREATE DATABASE `pfquest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES, EXECUTE, ALTER ROUTINE, CREATE ROUTINE ON `pfquest`.* TO 'mangos'@'localhost';

    CREATE DATABASE `vmangos` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES, EXECUTE, ALTER ROUTINE, CREATE ROUTINE ON `vmangos`.* TO 'mangos'@'localhost';

    CREATE DATABASE `cmangos-tbc` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES, EXECUTE, ALTER ROUTINE, CREATE ROUTINE ON `cmangos-tbc`.* TO 'mangos'@'localhost';

    CREATE DATABASE `turtle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES, EXECUTE, ALTER ROUTINE, CREATE ROUTINE ON `turtle`.* TO 'mangos'@'localhost';
'

Import Client Data

Import the game client data SQL files:

mariadb -u mangos -p"mangos" pfquest < ../client-data.sql

Vanilla (VMaNGOS)

Manually download the latest VMaNGOS Database and unzip it.

mariadb -u mangos -p"mangos" vmangos < mangos.sql

Clone the VMaNGOS core repository to obtain all SQL updates.

git clone https://github.com/vmangos/core.git

cd core/sql/migrations
for file in *_world.sql; do mariadb -u mangos -p"mangos" vmangos < $file; done
cd -

Create _loc10 entries to VMaNGOS translation tables for ptBR:

mariadb -u mangos -p"mangos" vmangos <<< '
    # select database
    USE vmangos;

    # locales_creature
    ALTER TABLE locales_creature ADD name_loc10 varchar(100);
    ALTER TABLE locales_creature ADD subname_loc10 varchar(100);

    # locales_gameobject
    ALTER TABLE locales_gameobject ADD name_loc10 varchar(100);

    # locales_item
    ALTER TABLE locales_item ADD name_loc10 varchar(100);
    ALTER TABLE locales_item ADD description_loc10 varchar(255);

    # locales_quest
    ALTER TABLE locales_quest ADD Title_loc10 TEXT;
    ALTER TABLE locales_quest ADD Details_loc10 TEXT;
    ALTER TABLE locales_quest ADD Objectives_loc10 TEXT;
    ALTER TABLE locales_quest ADD ObjectiveText1_loc10 TEXT;
    ALTER TABLE locales_quest ADD ObjectiveText2_loc10 TEXT;
    ALTER TABLE locales_quest ADD ObjectiveText3_loc10 TEXT;
    ALTER TABLE locales_quest ADD ObjectiveText4_loc10 TEXT;
    ALTER TABLE locales_quest ADD OfferRewardText_loc10 TEXT;
    ALTER TABLE locales_quest ADD RequestItemsText_loc10 TEXT;
    ALTER TABLE locales_quest ADD EndText_loc10 TEXT;
'

Use the current ptBR localizations from the vmangos core repo and patch them into _loc10 entries.

cd core/sql/translations/ptBR
git checkout .
sed -i 's/`name`/`name_loc10`/g' *.sql
sed -i 's/`subname`/`subname_loc10`/g' *.sql
sed -i 's/`description`/`description_loc10`/g' *.sql
sed -i 's/`Title`/`Title_loc10`/g' *.sql
sed -i 's/`Details`/`Details_loc10`/g' *.sql
sed -i 's/`Objectives`/`Objectives_loc10`/g' *.sql
sed -i 's/`ObjectiveText1`/`ObjectiveText1_loc10`/g' *.sql
sed -i 's/`ObjectiveText2`/`ObjectiveText2_loc10`/g' *.sql
sed -i 's/`ObjectiveText3`/`ObjectiveText3_loc10`/g' *.sql
sed -i 's/`ObjectiveText4`/`ObjectiveText4_loc10`/g' *.sql
sed -i 's/`OfferRewardText`/`OfferRewardText_loc10`/g' *.sql
sed -i 's/`RequestItemsText`/`RequestItemsText_loc10`/g' *.sql
sed -i 's/`EndText`/`EndText_loc10`/g' *.sql

sed -i 's/`creature_template`/`locales_creature`/' *.sql
sed -i 's/`gameobject_template`/`locales_gameobject`/' *.sql
sed -i 's/`item_template`/`locales_item`/' *.sql
sed -i 's/`quest_template`/`locales_quest`/' *.sql

mariadb -u mangos -p"mangos" vmangos < creature_template.sql
mariadb -u mangos -p"mangos" vmangos < gameobject_template.sql
mariadb -u mangos -p"mangos" vmangos < item_template.sql
mariadb -u mangos -p"mangos" vmangos < quest_template.sql
cd -

TurtleWoW (Optional)

Obtain and download the latest TurtleWoW database and unzip it.

mariadb -u mangos -p"mangos" turtle < turtle/*.sql

The Burning Crusade (CMaNGOS)

Clone the latest CMaNGOS TBC database and the translations of the Mangos-Extras project:

git clone https://github.com/cmangos/mangos-tbc.git
git clone https://github.com/cmangos/tbc-db.git
git clone https://github.com/mangosone/database.git

mariadb -u mangos -p"mangos" cmangos-tbc < mangos-tbc/sql/base/mangos.sql
mariadb -u mangos -p"mangos" cmangos-tbc < tbc-db/Full_DB/TBCDB_1.10.0_ReturnOfTheVengeance.sql
for file in tbc-db/Updates/*.sql; do mariadb -u mangos -p"mangos" cmangos-tbc < "$file"; done
for file in mangos-tbc/sql/updates/mangos/*.sql; do mariadb -u mangos -p"mangos" cmangos-tbc < "$file"; done
mariadb -u mangos -p"mangos" cmangos-tbc < mangos-tbc/sql/base/dbc/original_data/Spell.sql
mariadb -u mangos -p"mangos" cmangos-tbc < mangos-tbc/sql/base/dbc/cmangos_fixes/Spell.sql
mariadb -u mangos -p"mangos" cmangos-tbc < tbc-db/ACID/acid_tbc.sql

sed -i "/locales_command/d" database/Translations/1_LocaleTablePrepare.sql
mariadb -u mangos -p"mangos" cmangos-tbc < database/Translations/1_LocaleTablePrepare.sql
for file in database/Translations/1_LocaleTablePrepare.sql database/Translations/Translations/*/*.sql; do echo "$file"; mariadb -u mangos -p"mangos" cmangos-tbc < "$file"; done

Optimize Database Performance

Run the following commands to improve extractor performance by indexing the sql entries:

mariadb <<< '
    use 'vmangos';
    # creatures
    CREATE INDEX idx_cse_guid ON creature_spawn_entry(guid);
    CREATE INDEX idx_cse_entry ON creature_spawn_entry(entry);
    CREATE INDEX idx_guid_map_position ON creature(guid, map, position_x, position_y);
    # gameobjects
    CREATE INDEX idx_gse_guid ON gameobject_spawn_entry(guid);
    CREATE INDEX idx_gse_entry ON gameobject_spawn_entry(entry);
    # items
    CREATE INDEX idx_got_data1 ON gameobject_template(data1);
    CREATE INDEX idx_golt_entry ON gameobject_loot_template(entry);
    CREATE INDEX idx_npcvt_entry ON npc_vendor_template(entry);
    CREATE INDEX idx_ct_entry ON creature_template(Entry);

    use 'turtle';
    # creatures
    CREATE INDEX idx_cse_guid ON creature_spawn_entry(guid);
    CREATE INDEX idx_cse_entry ON creature_spawn_entry(entry);
    CREATE INDEX idx_guid_map_position ON creature(guid, map, position_x, position_y);
    # gameobjects
    CREATE INDEX idx_gse_guid ON gameobject_spawn_entry(guid);
    CREATE INDEX idx_gse_entry ON gameobject_spawn_entry(entry);
    # items
    CREATE INDEX idx_got_data1 ON gameobject_template(data1);
    CREATE INDEX idx_golt_entry ON gameobject_loot_template(entry);
    CREATE INDEX idx_npcvt_entry ON npc_vendor_template(entry);
    CREATE INDEX idx_ct_entry ON creature_template(Entry);

    use 'cmangos-tbc';
    # creatures
    CREATE INDEX idx_cse_guid ON creature_spawn_entry(guid);
    CREATE INDEX idx_cse_entry ON creature_spawn_entry(entry);
    CREATE INDEX idx_guid_map_position ON creature(guid, map, position_x, position_y);
    # gameobjects
    CREATE INDEX idx_gse_guid ON gameobject_spawn_entry(guid);
    CREATE INDEX idx_gse_entry ON gameobject_spawn_entry(entry);
    # items
    CREATE INDEX idx_got_data1 ON gameobject_template(data1);
    CREATE INDEX idx_golt_entry ON gameobject_loot_template(entry);
    CREATE INDEX idx_npcvt_entry ON npc_vendor_template(entry);
    CREATE INDEX idx_ct_entry ON creature_template(Entry);

    use 'pfquest';
    # worldmap
    CREATE INDEX idx_wma_vanilla_sizes ON pfquest.WorldMapArea_vanilla(x_min, x_max, y_min, y_max);
    CREATE INDEX idx_wma_vanilla_mapid ON pfquest.WorldMapArea_vanilla(mapID);
    CREATE INDEX idx_wma_vanilla_area ON pfquest.WorldMapArea_vanilla(areatableID);
    CREATE INDEX idx_wma_tbc_sizes ON pfquest.WorldMapArea_tbc(x_min, x_max, y_min, y_max);
    CREATE INDEX idx_wma_tbc_mapid ON pfquest.WorldMapArea_tbc(mapID);
    CREATE INDEX idx_wma_tbc_area ON pfquest.WorldMapArea_tbc(areatableID);
'

Run the Extractor

Start the database extractor

$ make

Optional: Build Client-Data

Copy CSVs to DBC/

You additionally need to extract the dbc files from your gameclients. Those can be obtained via the ad tool within the CMaNGOS tools. The DBC files then need to be converted into .csv and placed as followed:

$ ls -1 DBC/
deDE  enUS  esES  frFR  koKR  ruRU  zhCN

$ ls -1 DBC/deDE
AreaTable.dbc.csv
SkillLine.dbc.csv
WorldMapArea.dbc.csv

Required DBCs

WorldMapArea.dbc [enUS]

Required to obtain the map-sizes which are used for

calculating the minimap offset
calculating the objects possible maps during extraction

Lock.dbc [enUS]

Used to get a list of all skill requirements which are used during the meta-list extraction

AreaTable.dbc [all]

The AreaTable.dbc is used to build the zones table. The zone table is required to tell the gameclient which map should be shown when searching for an object. It's basically a map-id to mape-name translation table.

SkillLine.dbc [all]

The SkillLine.db is used to build the professions table. The profession table is required to check the players professions against quest requirements. It's basically a profession-id to profession-name translation table.