libasynql
3.2.0
Asynchronous MySQL access library for PocketMine plugins.
|
Asynchronous SQL access library for PocketMine plugins.
The basic use of libasynql has 5 steps:
config.yml
.onEnable()
.onDisable()
.To let the user choose what database to use, copy the following into your default config.yml
. Remember to change the default schema name under mysql
.
libasynql simplifies the process of initializing a database into a single function call.
The \poggit\libasynql\libasynql::create()
method accepts 3 parameters:
resources
folder) of the SQL files as the value. We are going to create them in the next step.It returns a \poggit\libasynql\DataConnector
object, which is the main query interface.
In case of error, a ConfigException or an SqlError will be thrown. If not caught by the plugin, this will go straight out of onEnable() and disable the plugin. Therefore, make sure to check isset($this->database)
before calling $this->database->close()
in onDisable().
In the resources file, create one file for each SQL dialect you are supporting, e.g. resources/sqlite.sql
and resources/mysql.sql
.
Write down all the queries you are going to use in each file, using the Prepared Statement File format.
Finally, we are prepared to use libasynql in code!
There are 4 query modes you can ues: GENERIC, CHANGE, INSERT and SELECT.
CREATE TABLE
statements.UPDATE
/DELETE
statements.INSERT INTO
query for a table with an AUTO_INCREMENT
key. You will receive the auto-incremented row ID.SELECT
statement, or reflection queries like EXPLAIN
and SHOW TABLES
. You will receive a SqlSelectResult
object that represents the columns and rows returned.They have their respective methods in DataConnector: executeGeneric
, executeChange
, executeInsert
, executeSelect
. They require the same parameters:
function(int $affectedRows)
function(int $insertId, int $affectedRows)
function(array $rows)
SqlError
object.A Prepared Statement File (PSF) contains the queries that a plugin uses. The content is valid SQL, so it is OK to edit with a normal SQL editor.
The PSF is annotated by "command lines", which start with -- #
, followed by the command symbol, then the arguments. Between the #
and the command symbol, there can be zero to infinite spaces or tabs; between the command symbol and the arguments, there can also be zero to infinite spaces or tabs. Between every two arguments, one to infinite spaces or tabs are required.
A PSF always starts with a dialect declaration.
!
Possible values: mysql
, sqlite
Queries may be organized by groups. Each group has an identifier name, and a group can be stacked under another. Groups and queries under a group will be prepended the parent group's identifier plus a period in their own identifiers.
For example, if a parent group declares an identifier foo
, and the child group/query declares an identifier bar
, the real identifier for the child group/query is foo.bar
.
Duplicate group identifier declarations are allowed, as long as the resultant queries do not have identical full identifiers.
{
}
The name of this group.
All characters except spaces and tabs are allowed, including periods.
Note that PSF is insensitive about spaces and tabs, so this variant is equivalent:
A query is declared like a group. A query does not need to belong to a group, because the query can declare the periods in its own identifier, which has equivalent effect as groups.
Child groups are not allowed in a query declaration. In other words, a {}
pair either has other group/query declarations inside, or has query text (and optionally variable declarations) inside. It cannot have both.
{
(same as group declaration)}
Same arguments as a group declaration.
A variable declaration declares the required and optional variables for this query. It is only allowed inside a query declaration.
:
The name of the variable. Any characters apart from spaces, tabs and colons are allowed. However, to comply with ordinary SQL editors, using "normal" symbols (e.g. variable names in other programming languages) is recommended.
The variable type. Possible values:
string
int
float
bool
If the variable is optional, it declares a default value.
This argument is not affected by spaces. It starts from the first non-space non-tab character after VAR_TYPE, and ends before the trailing space/tab characters of the line
string
defaultThere are two modes, literal string and JSON string.
If the argument starts with a "</tt> and ends with a <tt>"
, the whole argument will be parsed in JSON. Otherwise, the whole string is taken literally.
int
defaultA numeric value that can be parsed by (int)
cast, equivalent to intval
.
float
defaultA numeric value that can be parsed by (float)
cast, equivalent to floatval
.
bool
defaulttrue
, on
, yes
or 1
will result in true. Other values, as long as there is something, will result default false. (If there is nothing, the variable will not be optional)
Query text is not a command, but the non-commented part between the start and end commands of a query declaration.
Variables are interpolated in query text using the :var
format. Note that libasynql uses a homebrew algorithm for identifying the variable positions, so they might be inaccurate.
See mysql.sql and sqlite3.sql in the example plugin.