The Database Schema

If you are implementing a web frontend for the LibertyBans database, or otherwise need to connect to the database schema, some things are helpful to know.

Terminology: Throughout this page the adjective terms "standard-compatible", "SQL standard", and "standard" are used to refer to SQL syntax, features, or behavior which is in accordance with the ANSI SQL standard.

Notice: This page documents the schema of LibertyBans 1.0.0. The schema for 0.8.x is similar, but more minimal, and somewhat less normalized: some of the information here does not apply.

Requirements

LibertyBans needs to run using MariaDB/MySQL or PostgreSQL. Local storage options (HSQLDB) do not work because they only allow connections from the same process.

Recommendations

Since PostgreSQL and MariaDB/MySQL are quite different in terms of vendor-specific syntax, it is strongly suggested to maintain standard SQL in your code. That way, your program will work on whichever database users happen to choose.

Types

Binary UUIDs

All UUIDs are stored as BINARY(16). This differs from some other plugins which store UUIDs as text.

• To convert from CHAR to BINARY, use HEX:
  • Note that this requires a UUID without dashes.
  • Example: SELECT HEX('ed5f12cd600745d9a4b9940524ddaecf') FROM DUAL
• To convert from BINARY to CHAR, use UNHEX:
  • The returned string will not have dashes.
  • Example: SELECT UNHEX(uuid) FROM libertybans_names WHERE name = ?
• For PostgreSQL, you will need to use encode and decode instead of HEX and UNHEX.

Binary IP Addresses

IP addresses are stored as VARBINARY(16). For an IPv4 address, 4 bytes are used; for IPv6, the entire 16 bytes are used.

On the JVM, you can use InetAddress.getByAddress(bytes) to obtain an InetAddress.

Tables

You may use any database tool of choice to see the tables created.

Table Information

Details about certain tables and their columns are described here. Some columns in certain tables either do not have an immediately clear meaning, or require additional information to use correctly.

Although MariaDB and PostgreSQL support dialect-specific forms of creating comments on columns, LibertyBans does not use non-standard commenting features. Instead, the meanings of these columns are documented here.

• For the table libertybans_punishments, notable columns:
  • id - When inserting into this column, values should be retrieved from the sequence libertybans_punishment_ids
  • type - The punishment type. 0 for bans, 1 for mutes, 2 for warns, 3 for kicks.
  • start - a unix timestamp, in seconds, of when the punishment was created
  • end - a unix timestamp, in seconds, of when the punishment will end
• For tables of the form libertybans_<type> (the type is 'bans', 'mutes', 'warns', or 'history'):
  • The tables bans, mutes, and warns store active punishments for their respective punishment types.
  • The history table stores all punishments, active and inactive. It is also the only place where kicks are located, since kicks are never active.
  • Columns:
    • id - foreign key pointing to the punishment id in libertybans_punishments
    • victim - foreign key pointing to the victim id in libertybans_victims
  • Constraints: For the bans and mutes tables, the victim must be unique. The reason is that there can only be one active ban or active mute for any particular victim.
• For the table libertybans_victims:
  • The data in this table is used by the tables storing punishments.
  • Columns:
    • id - When inserting into this column, values should be retrieved from the sequence libertybans_victim_ids
    • data and data_extra - The meaning of these columns is dependent on the value of the type column. Together, they comprise the data representing a victim. They are either UUIDs or IP addresses.
    • type - the kind of victim which is punished. Possible values and their meanings:
      • 0 - a UUID. data will be a binary UUID. Corresponds to VictimType.PLAYER from the Java API.
      • 1 - an IP address. data will be a binary IP address. Corresponds to VictimType.ADDRESS from the Java API.
      • 2 - a composite UUID and IP address combination. data will be a binary UUID and data_extra will be a binary IP address. Corresponds to VictimType.COMPOSITE from the Java API.
• For the tables libertybans_names and libertybans_addresses:
  • updated - a unix timestamps, in seconds, of when the record was created or last updated. In other words, this tells you how up-to-date the entry is.

Sequences

You may be familiar with AUTO_INCREMENT in MariaDB/MySQL, SERIAL in PostgreSQL, or GENERATED BY DEFAULT AS IDENTITY in other RDMSes.

The standard-compatible¹ alternative to these dialect-specific features is sequences.

• For MariaDB/MySQL, use the standard NEXT VALUE FOR to obtain the next value for a sequence.
• For PostgreSQL, NEXTVAL has to be used, because PostgreSQL does not support the standard syntax.

LibertyBans has the following sequences:

• libertybans_punishments_ids - used to generate punishment IDs

_{1 - While GENERATED BY DEFAULT AS IDENTITY is also standard SQL, it is sadly not supported by MariaDB/MySQL. Therefore, sequences are used.}

Views

if you didn't notice the views, I highly suggest you look at them too. The "simple_" views will help you avoid writing repetitive JOINs.

The views are perfect for when you need easily displayable information.

Finding player names for UUIDs

Use the latest_names view to look up the most recent known name for a UUID.

Other Information

Notes on implementation details

• TINYINT and UUID data types cannot be used because they are non-standard.
• Index names need to be unique at the database level, due to PostgreSQL