EQEmulator Forums

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)
-   Development::Development (https://www.eqemulator.org/forums/forumdisplay.php?f=590)
-   -   Database Scripts - Question About Good Practise (https://www.eqemulator.org/forums/showthread.php?t=43475)

Baltros101 11-23-2021 04:24 PM

Database Scripts - Question About Good Practise
 
Hi all. I have a question about good practise when writing mysql scripts to update the database. Let's say I want to write a script that makes thousands of changes to a table or multiple tables. An example of this would be if I wanted to change the levels that classes can use their spells or something. If I have a script a that contains entries like:

Code:

UPDATE spells SET behaviour1=value1,behaviour2=value2 WHERE spellid=123;
UPDATE spells SET behaviour1=value3,behaviour2=value4 WHERE spellid=234;

... then if the schema changes later on I could run into problems and have to edit thousands of lines. It also be more difficult to maintain because I won't know what all the IDs represent etc. It feels intuitive to me to write a stored proc to handle all the database calls for me. Something like:

Code:

CREATE PROCEDURE UPDATE_THE_SPELLS(
        IN spellShortName VARCHAR(255),
        IN behaviour1 VARCHAR(255),
        IN behaviour2 VARCHAR(255)
)
BEGIN
        SET @spellid = (SELECT id FROM spellIdTable WHERE shortName=@spellShortName);
        UPDATE spells SET behaviour1=@behaviour1,behaviour2=@behaviour2 WHERE spellid=@spellid;
END

UPDATE_THE_SPELLS(myCoolSpell,value1,value2);
UPDATE_THE_SPELLS(anotherCoolSpell,value3,value4);

So, in future if the schema changes all I have to do is update the stored proc rather than thousands of UPDATE or INSERT lines. Also, each line should be more readable because I can use names rather than IDs.

Is this good practise? Is there a better way to go about this?


All times are GMT -4. The time now is 03:48 AM.

Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.