MySQL

Using MySQL in Plugins

There are a lot of boring things in this article, so I crossed them out, so if you are in a hurry or if you feel bored reading them, skip them. But you are encouraged to try to read them.

Introduction: What is MySQL?

MySQL is a popular open-source database server that implements SQL (Structured Query Language). Let's skip the boring definition. It serious doesn't matter.

What matters is:

1. MySQL is a server. It is of course based on files (all databases in this world eventually store things in files, unless they don't run on normal operating systems), but we don't need to know about the details about the files. We just need to know about the server part - MySQL is a server, which means that you have to connect to it through networking. Even if the MySQL server is hosted on the same machine, you still connect to it using localhost, which is still a kind of network socket, although no external networking is involved.
2. MySQL is a database, which means that MySQL stores data. (Sounds like rubbish, but you must remember this) (And it is often abused as a communicator rather than a database; but fine, abuse it as you want) (And yes, I abuse it too, and there isn't really anything wrong that it is abused) (I know, just ignore my obsession here 😵)
3. MySQL is a language. SQL means Structured Query Language.

• SQL is a language that:
  • defines data. Don't get confused 😕. Defining data means you define the data structure. Basically, it tells the server what data you are going to put in the database. You will understand more about the structure in the next section about tables.
  • manipulates data. This means, you process the actual data. You store data into the database. You ask the database for data. You delete data from the database.
• Therefore, the most important part of using MySQL in PocketMine plugins is the query part.

4. MySQL is a structured language. How do you structure things? You order them. You classify them by type. You put similar things together. In short, you put them in tables.

MySQL tables

There are two types of things that we store in a MySQL database (probably more, but I only know of two):

• tables
• functions/procedures

I rarely use functions/procedures. I don't think you would frequently use them in plugins either. They're more useful for the user. I'm not planning to discuss about them in this article. So let's concentrate on tables.

Consider the following information:

1. PEMapModder originally had 10 coins and 10 gems.
2. shogchips originally had 10 coins and 10 gems.
3. PEMapModder got 15 gems from people who ask him for plugins that are never coming 😛
4. shogchips spent 5 coins for buying shock chips 🍟 sorry couldn't find emoji for chips; fries are similar
5. ...

As time goes on, it gets really complicated, so we would store the statement 1 in a table like this:

Name	Coins	Gems
PEMapModder	10	10

A new player called shogchips registered, so let's append a row at the end of the table:

Name	Coins	Gems
PEMapModder	10	10
shogchips	10	10

If we consider statements 3 and 4:

Name	Coins	Gems
PEMapModder	10	25
shogchips	5	10

Even if the data continue to grow, it is still very convenient to track the final amount of coins and gems for each player. What's more, we can also find other interesting data such as the average coins, number of accounts, etc.

This is what "structured" means. And this is the principle of MySQL (and other SQL-family databases like SQLite).

Rows

For people who aren't sure, a horizontal set of data is a row. In the table above, data related to "PEMapModder" is one row. Column is a vertical set of data. In the table above, all names are one column; all coins are another column.

The plugin often automatically adds (INSERT) rows to the table or DELETE rows from the table, or UPDATE the values in different rows of the table. But it rarely adds a column, because adding a column will affect all data.

What is so different between adding a row and adding a column? The difference is, adding a column is data definition, and adding a row is data manipulation.

You can think each table as a building (an inverted one). The highest row (the row in bold font that defines what the data below are about) is the ground. Each row is a floor. It is easy (fine, I know it isn't easy because you need to transport the building materials up; but we all play Minecraft, and it is easy in the game. Happy? ☺️) to add a floor at the top, but it is (comparatively) much harder to add a room in the floor plan, because you need to do that for every building; and to add a floor, you just need to add a few rooms (don't talk to me about floating islands in Minecraft!).

But why must we consider the highest row as the ground? Why not the leftmost column? Because this is the definition of a table. This is inarguable, and you'd better accept that fact. (If you insist to argue, just think about why bar charts are usually vertical) (You see more horizontal bar charts than vertical ones? Blame the people who pretend to be eco-friendly)

Cells

Each cell in a row contains one datum. 😨 Don't be scared; datum is just the singular noun of data. 😌

In order to make MySQL load data faster, MySQL wants you to define what data you would be storing in each cell. Of course, all cells in a column are about the same thing (but for different rows), so they should have the same data type. Is it a date? Is it a number? Or is it a printable text? Or a binary blob of data?

I'm not going to waste my own time explaining data types to detail, since we already have an existing excellent manual that explains about MySQL data types. Just use it for reference whenever you need it. Here is a list of my most frequently used data types, with descending order:

• INT (for storing numbers)
• VARCHAR (for almost every text, such as player name, player IP, etc.)
• TINYINT (for storing very small data flags that are usually a 2-digit integer or -1)
• TIMESTAMP (for storing dates, obviously)
• BIGINT (for storing dates, when I don't want to use TIMESTAMP for various reasons)
• BINARY (for storing password hash) (note that BINARY isn't as easy to use as you expected, so you may want to use VARCHAR/CHAR instead after running bin2hex or base64_encode.

Why is there no array type? It is because a datum is a datum, and it is singular. 😑 You can of course use a VARCHAR, then implode an array into a string with a delimiter like , before storing into database. If you simply need to store a group of data and take the whole chunk of data out later on, this is OK. But what if you want to manipulate the data directly on the database (through SQL)? There are a few cases when you want to. I'm listing a few examples I experienced:

• IP address history. Initially I stored IP address with a , as delimiter. Soon I realized that it is not very good; when I try to find out things like how many IP addresses have been used by the player, who is using the same IP, etc., things are getting troublesome.
• Friend list. I saved the list of friends of a user PEMapModder in a string like this: shogchips,sekjun9878. Then what happens when PEMapModder wants to remove shogchips as friend? It is easy to delete shoghicps from PEMapModder's friend list, but we need to delete PEMapModder from shogchips's friend list at the same time. How could we do this? This is possible to solve through the MySQL SUBSTRING() function, but what if I need to delete all friends? Or another case is, what if I want to delete the 21st friend of each player onward (this indeed happened once at @LegionPE; luckily I didn't use strings at that time)? It would be a lot of trouble. If we download data one by one, manipulate them with PHP code and upload them one by one, it would create excessive lag on the server (suppose this frequently happens).

So how can we solve these problems? We create another table.

Compare these two tables:

users:

Username	Coins	IP history
PEMapModder	10	1.2.3.4,2.3.4.5,3.4.5.6
shogchips	5	127.0.0.1,192.168.0.5

Versus

users:

Username	Coins
PEMapModder	10
shogchips	5

iphistory:

Username	IP
PEMapModder	1.2.3.4
PEMapModder	2.3.4.5
PEMapModder	3.4.5.6
shogchips	127.0.0.1
shogchips	192.168.0.5

In the second table, an individual row holds one IP address rather than one user, so it is more convenient to get a value. The question is, wouldn't this make it difficult to download the data? When we talk about subqueries later, you would realize that it is still simply possible.

What is special about MySQL in plugins?