Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Development

Development::Development Forum for development topics and for those interested in EQEMu development. (Not a support forum)

Reply
 
Thread Tools Display Modes
  #1  
Old 11-23-2021, 04:24 PM
Baltros101
Fire Beetle
 
Join Date: Oct 2021
Location: UK
Posts: 19
Default 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?
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 03:08 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3