Difference between revisions of "Basic database primer"

Latest revision as of 02:32, 3 April 2013

This page is meant to sort of explain the ropes of navigating a database, and kind of give a rough primer for SQL novices so that they can learn to crawl, then walk, and then maybe even dance a little. I'm going to assume you're using Navicat, since you're not familiar with a database and that's what Bluekirby0's guides recommend.

Lesson One: Backups, backups, backups

First off, a sort of warning with SQL. There's no "undo" function. If you change something, it's changed. If you don't remember what it used to be, there's not really a way to get that back. If you're doing something risky like editing character tables, click on "Tables" in the tree on the left side, and select the accounts and char tables. Right click and select "Dump Tables." This will let you make an SQL file that has a copy of your database, in case you make a mistake. Using this file is similar to updating your database normally, except you'll have to manually drop (delete) the tables before you can put the old data back into them.

Secondly, there's two types of tables: indexed and unindexed. Indexed tables will have one value that must always be unique. In the case of Darkstar, this will be things like itemId, npcid, and mobid. Unindexed tables can have duplicate values, and are also slower to query, although the speed difference may not be noticeable. Some examples of unindexed tables are item_mods, char_titles, and char_vars. These tables have their uses, but generally if you can store data in one or the other, and the data is going to be persistent and frequent like which outpost warps a character has unlocked, you should use indexed tables.

Thirdly, you can save queries. I have a "Customizations" query saved that changes a number of things so that I can quickly rechange any tables that get updated. This way, the inventory clutter in dynamis will go back to stacking, I'll have Sekkanoki at L40, and I can use Judge's Cap and Judge's Belt to do haste tests.

Fourthly, a lot of people forget about triggers.sql. That particular file made some changes to the chars table (among others) so that if a character is deleted, their entries in all the other character related tables are cleared out. Just remember that this can and will happen if you delete anything from chars.

Fifthly, comments. You can use these when necessary to keep track of what something means, or just as little post it notes. A comment has to be prefixed by two dashes, and may need a space before and after the dash, depending on usage. Here's an example of comments in use:

-- Missing mIDs

-- Certain

UPDATE `item_armor` SET mID = '327' WHERE itemId IN (10452,10502,10602,10878,11971); -- Rubeus

-- Confident

UPDATE `item_armor` SET mID = '16' WHERE itemId = 11525; -- Maestria Mask

You can also use comments if you want to temporarily remove a line from usage, but you don't want to delete the line from your query permanently.

Lesson Two: The SELECT Query

Next, an introduction to building queries.

The most basic query, and the one you'll use the most, is a SELECT query. A SELECT query fetches data from a data source, and lists it out for you. A good rule of thumb is to use a SELECT query to see what you'll be changing or deleting before you do it. It's also a safe way to introduce yourself to SQL, since nothing is being changed. The syntax is:

SELECT columns FROM data source WHERE conditions;

The capitalization isn't necessary for the query to work, but it can make queries easier to read, once you're used to them. The semi-colon at the end is required, however. You can use a grave (above tab and next to the number 1, often called the "tilde key") instead of quotes in some places, and also around table_names. However, you can't always use single quotes and double quotes in those situations.

columns is a list of the columns you want to see. Alternatively, you can just use an asterisk (*) to view all columns.

data source is a table, or multiple tables, that you want to get data from.

conditions are things that help you narrow down your search. There's actually quite a few of these that you can use, which I'll go into.

... WHERE column = "value";

This is the most basic of conditionals. An example would be WHERE itemId = 17440; to bring up the Kraken Club. You could also use WHERE mobname = "Serket"; Note that numbers do not require quotation marks, but words will require either single or double quotes.

... WHERE column LIKE "%value%";

The percentage sign acts as a wild card, so "Kraken_%", "12%", or "%Behemo%" would all be the sort of thing you could use.

... WHERE column IN (value1, value2, value3...);

This lets you specify multiple values that can be matched. For example, WHERE itemId IN (15270, 15543); would return the Walahra Turban and Rajas Ring.

... WHERE column BETWEEN value1 AND value2;

Here you can specify a range of values. WHERE npcid BETWEEN 1000 AND 3000; would return all NPCs with an ID between 1000 and 3000.

You can also do a complex query, such as WHERE mobname LIKE "Goblin_%" AND zoneid = 154; to specify any mobname starting with "Goblin_" in zone 154 (Dragon's Aery.)

So, let's put that all together. Here's a query I used to find armor that was missing the appearance information:

SELECT * FROM item_armor WHERE MId = 0 AND slot NOT IN (1,2,3,4,8,512,1024,6144,24576,32768) ORDER BY level ASC;

The MId is the appearance for a piece of armor. 0 will have no appearance at all. Also notice the "NOT" used to negate the second query. That means to return results that DON'T match what you're specifying. Finally, you haven't seen ORDER BY yet. You can use this to sort your results by a column, either ASCending or DESCending. In this case, I'm sorting by level from 1 to 99.

Lesson Three: Complex Selects

Now to introduce you to queries that use multiple tables. You might have wondered why we use itemId in pretty much every table that has items in it. This makes it easy to get matching information from multiple tables. Here's an example I've been using pretty often lately:

SELECT npcid, npc_list.name, zone_settings.name, zone_settings.zoneid, pos_x, pos_y, pos_z, animation, hex(look) FROM npc_list, zone_settings WHERE npc_list.zoneid = zone_settings.zoneid AND npc_list.zoneid = 14;

You'll notice this query is a lot longer than what you've seen so far. This query fetches a few columns from the npc_list table, along with the name of the zone for convenience. That's literally the only reason I included zone_settings, in this case.

The second thing you may have noticed is that I didn't use * this time. npc_list has a lot of columns, and some of them I didn't care about, and * would have brought in all of them, along with all of zone_settings. This also lets me control the order of the columns. As part of this, I used npc_list.name to specify I wanted the name column in the npc_list table, since zone_settings also has a name column.

The third thing you should see is that I used multiple tables after "FROM." That's how the whole thing works, and you can really use as many tables as you can logically connect here.

Speaking of logically connected, the fourth thing to see is "WHERE npc_list.zoneid = zone_settings.zoneid." When you're querying multiple tables, you need a way to logically connect the two so that the data can all be retrieved. In this case, that part of the query tells Navicat that npc_list's zoneid should match zone_setting's zoneid. And there's why we use itemId on so many tables; we can connect them that way.

The bonus, fifth thing to learn here is hex(look). This is just telling Navicat that the data in the "look" column should be displayed as hexadecimal. Since that's what it's stored in anyway, that actually makes it visible to you, instead of a mostly blank column.

Just how complex can this get?

SELECT mobname, mob_droplist.dropid, mob_droplist.itemid, item_basic.name, type, rate, zoneid, pos_x, pos_y, pos_z FROM mob_spawn_points, mob_groups, mob_droplist, item_basic WHERE mob_spawn_points.groupid = mob_groups.groupid AND mob_groups.dropid = mob_droplist.dropid AND mob_droplist.itemid = item_basic.itemid AND mob_droplist.itemid = 1044;

This particular query will return all mobs that drop item 1044 (oztroja_coffer_key).

Lesson Four: Advanced SELECT Concepts

What if we SELECTed results... from results of another SELECT? You can totally do that, and I'm terrible at it. So if you want to play around, here's a query to spend some time fiddling with.

SELECT mobname, mob_droplist.dropid, item_basic.name, mob_droplist.itemid, type, rate, zoneid, pos_x, pos_y, pos_z FROM (SELECT mobname, zoneid, pos_x, pos_y, pos_z, dropid FROM mob_groups, mob_spawn_points WHERE mob_spawn_points.groupid = mob_groups.groupid AND mobname = "thunder_elemental" GROUP BY mob_groups.groupid) AS groups, mob_droplist, item_basic WHERE groups.dropid = mob_droplist.dropid AND mob_droplist.itemid = item_basic.itemid;

This query will retrieve the drops for any sets of the mob Thunder_Elemental. What I mean by that is that you'll only get one Thunder_Elemental for Sanctuary of Zi'Tah, although you'll get multiple rows because that mob has multiple drops.

I can only offer you two points here. First, the "AS unique_name" is necessary whenever you do this trick. Second, the GROUP BY near the end. This will return only one row for each result in the specified column(s).

Lesson Five: INSERT

INSERT queries put new data into a table. If the table is indexed and you try to use a duplicate index, the query will fail. There's two ways to INSERT: one at a time, or in bulk. One at a time looks like this:

INSERT INTO `npc_list` VALUES ('17277171', '_3e0', '0', '184.516', '-7.146', '14.297', '1', '40', '40', '9', '0', '0', '0', '4099', 0x0200000000000000000000000000000000000000, '0', '122');

INSERT INTO `npc_list` VALUES ('17277172', '_3e1', '0', '-184.516', '-7.146', '14.298', '1', '40', '40', '9', '0', '0', '0', '4099', 0x0200000000000000000000000000000000000000, '0', '122');

Because no columns are specified, it just starts inserting the data into each column, in order. If you come up short, then the columns are filled with their default value (if applicable.) If you have too many, your INSERT fails.

INSERT INTO `item_mods` (itemId, modId, value) VALUES (1,1,1), (2,2,2), (3,3,3), ... (1000,1000,1000);

I used an ellipsis to shorten things, but this query would insert 1000 rows into the item_mods table... or fail. These queries can actually be quite a bit faster than single inserts.

Lesson Six: UPDATE and DELETE

UPDATEs and DELETEs are easier to understand once you've got a grasp on SELECT. They do pretty much the same thing, except instead of listing results, they actually change or delete rows. Here's an example of each:

UPDATE weapon_skills SET skilllevel = 276 WHERE name IN ("ascetics_fury","atonement","kings_justice","insurgency","drakesbane","tachi_rana");

DELETE FROM item_mods WHERE itemid IN (13215, 13606) AND modid = 167;

There's only two things to keep in mind here. The first is that there's no "are you sure?" prompt. The second is that if you don't specify a condition, the query runs against everything. UPDATE item_basic SET name = 'kraken_club'; will rename everything to kraken_club. DELETE FROM chars; just deleted all of your characters, and thanks to the triggers on chars, it took out your inventory, levels, and more.

Lesson Seven: Abusing Defaults to Customize Your Server

Short lesson. Most values have default values, and sometimes they have these values for a reason. For example, char_points.windurst_supply has a default value of 0. That particular column stores which conquest teleports have been unlocked. And there's no reason you can't use Design Table to change that default to 12582880, which unlocks all Outpost Warps :)

Another example would be char_jobs.genkai, with a default of 50. This sets every new player's level cap to 50, until they've done quests with Maat. If you change that default to 75, then no new players will have to deal with Maat.

The only thing to consider in this situation is that some players might not want those enhancements. By placing them on an NPC, the player gets a choice between those spoils of war, and vanilla gameplay. However, it's your server, so make the choices you think are right.

Lesson Eight: REPLACE Query

The REPLACE query can be used to quickly copy records from a working copy of one table to a final copy. The syntax is as follows:

REPLACE INTO item_armor SELECT * FROM item_armor_copy;