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 seriously 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 :dizzy_face:)
3. MySQL uses SQL. SQL is a language. SQL means Structured Query Language.

• SQL is a language that:
  • defines data. Don't get confused :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 structured. 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 :stuck_out_tongue:
4. shogchips spent 5 coins for buying shock chips :fries: 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? :relaxed:) 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. :fearful: Don't be scared; datum is just the singular noun of data. :relieved:

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. :expressionless: 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?

MySQL is a server. This means that you need to connect through the Internet to use it. This is an advantage, but this is also a disadvantage. If the database server is currently busy, network congested, or other common inevitable things happened, a query would take a long time to complete. But wait, you want a result. How can PHP continue processing your code if it doesn't have a result for you? So, the program (more precisely, the thread you are running in) suspends. Before MySQL server returns a result and you received it, you will be stuck at the line of code that you send a query.

Therefore, a high-quality plugin should use threads to handle MySQL queries, which is the main point of this wiki.

What is threading?

Let's use an analogy in a restaurant. We have a waitress called Alice. We have a chef called Bob. And we have two customers called Cindy and David.

The whole thing happens like this:

1. Cindy enters the restaurant.
2. Cindy orders a toast.
3. Alice is in charge of the order.
4. Alice tells Bob to prepare a toast.
5. David walks in.
6. Cindy is an inexperienced waitress, but she's the only waitress in the restaurant.
7. Cindy doesn't serve David, because she was serving Alice, and she was doing nothing but waiting for Bob to get the toast ready.
8. David gets angry because he is ignored.
9. Cindy gets angry because she is ignored as well; Alice is waiting for Bob and ignores Cindy and David.

The analogy goes like this:

Analogy	Fact
Alice	PocketMine Server (especially the plugin using MySQL)
Bob	MySQL database server
Cindy	Player who does something that triggers the plugin into making a database query
David	Other players (actually including Cindy)
A toast	A request that triggers a MySQL query
Gets angry	Sees a laggy server; server TPS drops because the plugin is blocking the thread
Alice's attention	main thread

Now, what if Alice does not put all her attention on Bob? That is, what if Alice tells Bob to prepare a toast, continues serving Cindy and David, and checks if Bob has the toast ready every several seconds?

Analogy	Fact
Puts attention away from Bob	Starts a thread that is in charge of the query
Serving Cindy and David	Server does not get blocked by MySQL queries; continues to run smoothly
Checks if the toast is ready	Server communicates with the thread to see if it has finished