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)

Cells

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