if (dbUri == null || dbUri.isEmpty()) {
getConfig().set("uri", "mysql://localhost/exampledb");
getConfig().set("user", "databaseuser");
getConfig().set("password", "databasepassword");
manager.getConfiguration().save();
throw new PermissionBackendException("SQL connection is not configured, see config.yml");
}
dbDriver = dbUri.split(":", 2)[0];
this.ds = new BasicDataSource();
String driverClass = getDriverClass(dbDriver);
if (driverClass != null) {
this.ds.setDriverClassName(driverClass);
}
this.ds.setUrl("jdbc:" + dbUri);
this.ds.setUsername(dbUser);
this.ds.setPassword(dbPassword);
this.ds.setMaxActive(20);
this.ds.setMaxWait(200); // 4 ticks
this.ds.setValidationQuery("SELECT 1 AS dbcp_validate");
this.ds.setTestOnBorrow(true);
InputStream queryLocation = getClass().getResourceAsStream("/sql/" + dbDriver + "/queries.properties");
if (queryLocation != null) {
try {
this.queryCache = new SQLQueryCache(queryLocation, DEFAULT_QUERY_CACHE);
} catch (IOException e) {
throw new PermissionBackendException("Unable to access database-specific queries", e);
}
} else {
this.queryCache = DEFAULT_QUERY_CACHE;
}
try (SQLConnection conn = getSQL()) {
conn.checkConnection();
} catch (Exception e) {
if (e.getCause() != null && e.getCause() instanceof Exception) {
e = (Exception) e.getCause();
}
throw new PermissionBackendException("Unable to connect to SQL database", e);
}
getManager().getLogger().info("Successfully connected to SQL database");
addSchemaUpdate(new SchemaUpdate(2) {
@Override
public void performUpdate() throws PermissionBackendException {
// Change encoding for all columns to utf8mb4
// Change collation for all columns to utf8mb4_general_ci
try (SQLConnection conn = getSQL()) {
conn.prep("ALTER TABLE `{permissions}` DROP KEY `unique`, MODIFY COLUMN `permission` TEXT NOT NULL").execute();
conn.prep("ALTER TABLE `{permissions}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci").execute();
conn.prep("ALTER TABLE `{permissions_entity}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci").execute();
conn.prep("ALTER TABLE `{permissions_inheritance}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci").execute();
} catch (SQLException | IOException e) {
throw new PermissionBackendException(e);
}
}
});
addSchemaUpdate(new SchemaUpdate(1) {
@Override
public void performUpdate() throws PermissionBackendException {
try (SQLConnection conn = getSQL()) {
PreparedStatement updateStmt = conn.prep("entity.options.add");
ResultSet res = conn.prepAndBind("SELECT `name`, `type` FROM `{permissions_entity}` WHERE `default`='1'").executeQuery();
while (res.next()) {
conn.bind(updateStmt, res.getString("name"), res.getInt("type"), "default", "", "true");
updateStmt.addBatch();
}
updateStmt.executeBatch();
// Update tables
conn.prep("ALTER TABLE `{permissions_entity}` DROP COLUMN `default`").execute();
} catch (SQLException | IOException e) {
throw new PermissionBackendException(e);
}
}
});
addSchemaUpdate(new SchemaUpdate(0) {
@Override
public void performUpdate() throws PermissionBackendException {
try (SQLConnection conn = getSQL()) {
// TODO: Table modifications not supported in SQLite
// Prefix/sufix -> options
PreparedStatement updateStmt = conn.prep("entity.options.add");
ResultSet res = conn.prepAndBind("SELECT `name`, `type`, `prefix`, `suffix` FROM `{permissions_entity}` WHERE LENGTH(`prefix`)>0 OR LENGTH(`suffix`)>0").executeQuery();
while (res.next()) {
String prefix = res.getString("prefix");
if (!prefix.isEmpty() && !prefix.equals("null")) {
conn.bind(updateStmt, res.getString("name"), res.getInt("type"), "prefix", "", prefix);
updateStmt.addBatch();
}
String suffix = res.getString("suffix");
if (!suffix.isEmpty() && !suffix.equals("null")) {
conn.bind(updateStmt, res.getString("name"), res.getInt("type"), "suffix", "", suffix);
updateStmt.addBatch();
}
}
updateStmt.executeBatch();
// Data type corrections
// Update tables
conn.prep("ALTER TABLE `{permissions_entity}` DROP KEY `name`").execute();
conn.prep("ALTER TABLE `{permissions_entity}` DROP COLUMN `prefix`, DROP COLUMN `suffix`").execute();
conn.prep("ALTER TABLE `{permissions_entity}` ADD CONSTRAINT UNIQUE KEY `name` (`name`, `type`)").execute();
conn.prep("ALTER TABLE `{permissions}` DROP KEY `unique`").execute();
conn.prep("ALTER TABLE `{permissions}` ADD CONSTRAINT UNIQUE `unique` (`name`,`permission`,`world`,`type`)").execute();
} catch (SQLException | IOException e) {
throw new PermissionBackendException(e);
}
}
});
this.setupAliases();
this.deployTables();