Jump to content

SQL Guidelines


AriDEV

Recommended Posts

  • Assistant Admin

HOW TO GIVE YOUR CONTRIBUTION TO THE SKYFIRE DATABASE PROJECT:

It is most important that suggested fixes in this section do not take an enormous amount of time to check and test, otherwise they are useless if they take up as much time as writing them from scratch by a Dev (hence also a waste of time for you suggesting the fix).

METHOD:

All the fixes should be done in a "New Ticket" on our tracker: http://www.projectskyfire.org/bugtracker

Before being able to a do a ticket you need to register, it's simple: http://www.projectskyfire.org/bugtracker/signup_page.php

If any aspect of the following method is not PRECISELY followed, your ticket might be deleted.

DO NOT WASTE DEVS' TIME AND YOUR TIME:

  • Use the search function to see if the fix you want to provide hasn't already been posted or already accepted and comitted. Use multiple words search if necessary, do not stop at the first attempt just because the search engine does not return any result. (Search both on the forums and on the tracker)
  • Always state the SkyFire Rev you are using, make sure you are using the latest SFDB release and state any addon you are using.
  • Cross-check a bug you found with a clean world DB. Any custom addition or change you may have done on your DB could cause your bug, you never know.
  • Always state the source of your fixes (website, offy account, another project, etc) and provide as many links or details as possible. Always pretend that the Dev reading doesn't know the quest/npc/etc you are trying to fix.

MAKE YOUR FIX USEFUL:

  • Always comment your queries for better understanding with the -- format.
  • The ticket will not support too many queries. Attach a link to a sql file to your ticket whenever necessary.
  • DO NOT leave more then 1 space between values or field names.
  • Any INSERT INTO query must be preceded by a DELETE query to prevent import errors. Always write the fields in the query to better trace any structure change and adapt your fix to newer core revs.
  • If you are modifying an existing line, use an UPDATE query. We do not delete and re-insert a full line just to modify a few fields. Be as precise as you can to make sure your query updates ONLY the line expected.
  • If you are writing a text, make sure you use '' instead of ' when writing an apostrophe or it will give an import error
  • If you are providing an SQL query fix, please advise if it has been tested or not (be honest). Others might then test it for you and give feedback, this is a Community work! If you have tested your own work.
  • DO NOT CHANGE ANY WDB FIELD!

TICKET EXAMPLE:

Category: Loot

Summary: [iTEM] Broken Longbow (Patch/Fix)

Priority: Normal

Severity: Minor

Description: Missing from Abjurist Belmara's loot

Core: b77ae2fd2d7dd81d110674e8ef265db56090dd9d

Database: SFDB 11_06_04

Addons: Anticheat 1

Abjurist Belmara should drop Broken Longbow but doesn't.

Additional Information:

Patch/Fix: http://projectskyfire.org/pastebin/index.php?show=24

Source: http://www.wowhead.com/item=25406

CONCLUSION:

Use the wiki to learn more about the DB structure. Although new things are added more quickly than the wiki is updated to explain them, it is very very useful.

We will be very harsh if we realise that a suggested fix is just a copy-paste from another project without linking the original post. We do not tolerate leeching. Credits must always be given.

Finally, thank you, because if you are reading this it means you really care about helping and wish not to waste the Devs' time!

EXAMPLES:

Note: This may not be a working code, it is used for this thread as an example.

Notice how table and field names are not included in `` symbols, and how values are written plainly, only texts are included in '' symbols.

  • Delete before insert into and always put fields in the query.

Wrong:

INSERT INTO creature_loot_template VALUES (19546,25406,24,0,1,1,0,0,0);[/SQL]

Correct:

[SQL]
-- Add item Broken Longbow to Abjurist Belmara loot in Netherstorm
DELETE FROM creature_loot_template WHERE entry=19546 AND item=25406;
INSERT INTO creature_loot_template (entry, item, chanceOrQuestChance, lootmode, groupid, mincountOrRef, maxcount, lootcondition, condition_value1, condition_value2) VALUES
(19546, 25406, 24, 1, 0, 1, 1, 0, 0, 0);[/SQL]

  • Make sure your update queries are precise. You might and up changing more lines then you thought of.

Wrong:

[SQL]UPDATE creature_loot_template SET ChanceOrQuestChance=100 WHERE item=25406;[/SQL]

Correct:

[SQL]
-- Changing droprate for Broken Longbow dropped by Abjurist Belmara from 24 to 100.
UPDATE creature_loot_template SET ChanceOrQuestChance=100 WHERE entry=19546 AND item=25406;[/SQL]

  • Remember to use '' instead of a single apostrophe when writing texts, or there will be import errors.

Wrong:

[SQL]UPDATE page_text SET text='Hello Morgan, you shouldn't be here' WHERE entry=15;[/SQL]

Correct:

[SQL]-- Correcting text for item William's Shipment.
UPDATE page_text SET text='Hello Morgan, you shouldn''t be here' WHERE entry=15;[/SQL]

SIDENOTES:

Remember that a query must always end with a semicolon( ; ).

In general, always import your own queries into you DB to check if there are any import errors and also any startup errors!

Various authors (H3ad3r, AriDEV)

Template (TrinityCore)

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.