Database

Introduction

A database is collection of data typically stored as a set of tables that each contain a series of rows and columns. Structured Query Language (SQL) is an interpreted language used to perform create, replace, update and delete (CRUD) operations on tables in a database. The database abstraction layer in uMod provides a generic interface to asynchronously interact with multiple databases.

MySQL and SQLite

The uMod database client supports MySQL and SQLite. Both databases use SQL but have slight variations such as different data types and method names. In most cases two sets of queries will be required in order to support both databases. The databases a plugin supports should be specified in the plugin documentation.

Open a connection

Via a plugin

Plugins may connect to databases with custom connection strings.

Database.Open(new uMod.Common.Database.ConnectionInfo
{
    Type = uMod.Common.Database.ConnectionType.SQLite,
    Name = "MyConnection",
    ConnectionString = "Data Source=umod/data/myPlugin.db;Version=3;"
}).Done((connection) => {
    // connection successful
    connection.Execute(/* SQL */);
}, (exception) => {
    Logger.Report("Database connection failed", exception);
});

Via configuration

Database connections may be configured globally and accessed by name in plugins.

umod/config/database.toml (default)

default = "sqlite"
timeout = 30

# Sqlite embedded relational database
[sqlite]
driver = "sqlite"
database = "umod.db"
persistent = true

# MySQL remote relational database
[mysql]
driver = "mysql"
database = "umod"
host = "localhost"
port = 3306
username = "user"
password = "password"
protocol = "socket"
charset = "utf8"
persistent = true

Add a new connection...

[myConnection]
driver = "sqlite"
database = "myPlugin.db"
persistent = true

Using the named connection...

Database.Open("myConnection").Done((connection) => {
    // connection successful
    connection.Execute(/* SQL */);
}, (exception) => {
    Logger.Report("Database connection failed", exception);
});

Close a connection

connection.Close();

Query a table

Retrieve a list of data easily with a SELECT statement

[Model]
public class ExampleData
{
    public string field_one;
    public string field_two;
}

connection.Query<List<ExampleData>>("SELECT * FROM example_table")
    .Done((results) => {
        if (results == null) return;
        
        StringBuilder newString = new StringBuilder();
        newString.AppendLine(" id\tfield1\tfield2");

        // Iterate through resulting records
        foreach (ExampleData entry in results)
        {
            newString.AppendFormat(" {1}\t{2}\n", entry.field_one, entry.field_two);
        }

        Logger.Info(newString.ToString());
    });

Modeling and Eager-Loading

The object-relational mapper will make a best effort attempt to map the query result(s) to the associated models (such as ExampleData above). One-To-One and One-To-Many relationships are implemented using multi-mapping and JOIN clauses and must be represented formally as annotated fields within the model class.

Non-query

A non-query is a query that modifies data does not retrieve data or have return behavior.

connection.Execute("INSERT INTO example_table (field_one, field_two) VALUES ('hello', 'world');");

Parameterization

It is easy to parameterize queries and absolutely necessary when entering input supplied by the user.

ExampleData exampleRowData = new ExampleData
{
    field_one = "hello",
    field_two = "world"
};

connection.Execute("INSERT INTO example_table (field_one, field_two) VALUES (@field_one, @field_two);", exampleRowData);

Multiple parameterized statements

Perform the same query multiple times using multiple sets of data.

List<ExampleData> exampleData = new List<ExampleData>();

exampleData.Add(exampleRowData);
exampleData.Add(exampleRowData);

connection.Execute("INSERT INTO example_table (field_one, field_two) VALUES (@field_one, @field_two);", exampleData);

Scalar queries

A scalar query returns a single primitive result.

Count

connection.ExecuteScalar<long>("SELECT COUNT(*) FROM example_table;")
    .Done(delegate(long count) {
        Logger.Info($"example_table has {count} rows");
    });

Rows affected

List<ExampleData> exampleData = new List<ExampleData>();

exampleData.Add(exampleRowData);
exampleData.Add(exampleRowData);

connection.Execute<long>("INSERT INTO example_table (field_one, field_two) VALUES (@field_one, @field_two);", exampleData);
    .Done(delegate(long count) {
        Logger.Info($"Inserted {count} rows");
    });

Last inserted key

In some cases it may be important to retrieve the primary key of the row that was just inserted. This can be accomplished by batching together two queries, the first to insert the data and the second to receive the last inserted key as a scalar value.

SQLite

string sql = "INSERT INTO example_table (field_one, field_two) VALUES (@field_one, @field_two);SELECT last_insert_rowid();";
connection.ExecuteScalar<long>(sql, exampleData).Done(delegate(long key)
{
    exampleData.key = key;
});

MySQL

string sql = "INSERT INTO example_table (field_one, field_two) VALUES (@field_one, @field_two);SELECT LAST_INSERT_ID();";
connection.ExecuteScalar<long>(sql, exampleData).Done(delegate(long key)
{
    Logger.Info($"Last inserted key was: {key}");
});

Transactions

A transaction is a set of queries that are batched together in a single operation. If a failure occurs in any query then the entire batch is reversed or rolled back. Transactions are particularly useful in maintaining atomicity or preventing malformed/incomplete data from entering the database.

connection.Transaction()
  .Execute("INSERT INTO my_table (field_one, field_two) VALUES ('hello', 'world');")
  .Execute("Invalid Query")
  .Commit();

Because "Invalid Query" fails, the previous INSERT query is rolled back.

Migrations

A migration is a set of SQL queries that create, modify, or delete database tables. Each migration must implement at least two methods: Up and Down. Every database that uses migrations will contain a table called umod_migrations that tracks which migrations have run.

// The migration attribute may specify a friendly migration name and/or a migration version
[Migration("my_migration", "1.0")]
class MyMigration : Migration
{
    public MyMigration(uMod.Database.Connection connection) : base(connection)
    {
    }

    protected override void Up()
    {
        Execute("CREATE TABLE IF NOT EXISTS my_table (field_one TEXT, field_two TEXT, key INTEGER PRIMARY KEY AUTOINCREMENT);");
    }

    protected override void Down()
    {
        Execute("DROP TABLE my_table;");
    }
}

Run all migrations

Database.Open("myConnection").Done((connection) => {
    // connection successful
    Database.Migrate(connection, uMod.Database.MigrationDirection.Up);
}, Logger.Report);

Run specific migration

// Up
connection.Migrate<MyMigration>(uMod.Database.MigrationDirection.Up);

// Down
connection.Migrate<MyMigration>(uMod.Database.MigrationDirection.Down);

Once a plugin migration is created and the plugin is released, existing migrations should not be modified. Instead create a new migration which modifies the existing database schematic assuming the previous migration represents the state of the database.

Database-specific migrations

MySQL and SQLite have different syntax and will sometimes require different queries to scaffold the same table.

One option is to check the connection type in the migration.

protected override void Up()
{
    switch (ConnectionType)
    {
        case uMod.Common.Database.ConnectionType.MySQL:
            // Execute MySQL queries
            break;
        case uMod.Common.Database.ConnectionType.SQLite:
            // Execute SQLite queries
            break;
    }
}

Alternatively, two sets of migrations can be implemented.

[Migration("my_migration", "1.0", Type = uMod.Common.Database.ConnectionType.MySQL)]
class MyMySQLMigration : Migration
{
    public MyMySQLMigration(Connection connection) : base(connection)
    {
    }
            
    protected override void Up()
    {
        // Execute MySQL queries
    }
    
    protected override void Down()
    {
        // Execute MySQL queries
    }
}

[Migration("my_migration", "1.0", Type = uMod.Common.Database.ConnectionType.SQLite)]
class MySQLiteMigration : Migration
{
    public MyMySQLMigration(Connection connection) : base(connection)
    {
    }
    
    protected override void Up()
    {
        // Execute SQLite queries
    }
    
    protected override void Down()
    {
        // Execute MySQL queries
    }
}

Optimization

1. Fewer queries is always better than more queries. For values that update frequently, it's best to cache those variables in memory and gate queries according to some threshold criteria. For example, instead of sending a query every time a player's state changes, store the state changes and create a few queries which set the state of all players who are in that state (e.g. proper use of the IN clause).
2. Merge queries if possible and take advantage of eager-loading via JOIN clauses.
3. Ensure commonly queried cardinal relationship fields are marked as indexes.