Добавить версионирование схемы БД и заблокировать прод-очистку БД
This commit is contained in:
AidarKC
parent
e764a713c4
commit
c8fa4a01a1
@ -1,2 +1,2 @@
|
||||
client.version=1.2.2
|
||||
server.version=1.2.2
|
||||
client.version=1.2.3
|
||||
server.version=1.2.3
|
||||
|
||||
29
build.gradle
29
build.gradle
@ -191,25 +191,20 @@ tasks.register('deployServer', JavaExec) {
|
||||
dependsOn testClasses
|
||||
}
|
||||
|
||||
tasks.register('deployServerWithBackupCleanAndTests', JavaExec) {
|
||||
tasks.register('deployServerWithBackupCleanAndTests') {
|
||||
group = "!!deployment"
|
||||
description = "DANGER: deploy + backup data + clean data + restart + run IT tests (с обязательным подтверждением)"
|
||||
description = "BLOCKED: удаление БД на проде запрещено, используйте только миграции"
|
||||
|
||||
classpath = sourceSets.test.runtimeClasspath
|
||||
mainClass = "test.it.IT_DeployBackupCleanAndRunRemoteMain"
|
||||
|
||||
dependsOn shadowJar
|
||||
systemProperty "it.remoteHost", System.getProperty("it.remoteHost", "194.87.0.247")
|
||||
systemProperty "it.remoteUser", System.getProperty("it.remoteUser", "user")
|
||||
systemProperty "it.remoteDir", System.getProperty("it.remoteDir", "/home/user/docker/shine-server")
|
||||
systemProperty "it.remoteDataDir", System.getProperty("it.remoteDataDir", "/home/user/docker/shine-server/data")
|
||||
systemProperty "it.remoteBackupDir", System.getProperty("it.remoteBackupDir", "/home/user/docker/shine-server/backup")
|
||||
systemProperty "it.service", System.getProperty("it.service", "shine-server")
|
||||
systemProperty "it.localJar", System.getProperty("it.localJar", "build/libs/shine-server.jar")
|
||||
systemProperty "it.wsUri", System.getProperty("it.wsUri", "wss://shineup.me/ws")
|
||||
|
||||
standardInput = System.in
|
||||
dependsOn testClasses
|
||||
doLast {
|
||||
def msg = """
|
||||
[BLOCKED] Удаление базы данных на продакшен-сервере отключено.
|
||||
Причина: в базе уже есть пользовательские сообщения.
|
||||
Дальше используйте только миграции схемы БД.
|
||||
Задача остановлена намеренно.
|
||||
""".stripIndent().trim()
|
||||
println msg
|
||||
throw new GradleException(msg)
|
||||
}
|
||||
}
|
||||
|
||||
tasks.register('deployServerNoCleanNoTests', JavaExec) {
|
||||
|
||||
@ -24,6 +24,9 @@ import java.sql.Statement;
|
||||
*/
|
||||
public final class DatabaseInitializer {
|
||||
|
||||
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
|
||||
public static final int SCHEMA_VERSION_1 = 1;
|
||||
|
||||
private DatabaseInitializer() {}
|
||||
|
||||
/* ===================== TEXT (msg_type=1) ===================== */
|
||||
@ -101,7 +104,15 @@ public final class DatabaseInitializer {
|
||||
}
|
||||
}
|
||||
|
||||
public static void ensureSchemaV1Structure(String jdbcUrl) throws SQLException {
|
||||
createSchema(jdbcUrl, false);
|
||||
}
|
||||
|
||||
private static void createSchema(String jdbcUrl) throws SQLException {
|
||||
createSchema(jdbcUrl, true);
|
||||
}
|
||||
|
||||
private static void createSchema(String jdbcUrl, boolean initializeVersionRow) throws SQLException {
|
||||
try {
|
||||
Class.forName("org.sqlite.JDBC");
|
||||
} catch (ClassNotFoundException e) {
|
||||
@ -586,6 +597,24 @@ public final class DatabaseInitializer {
|
||||
ON signed_message_session_delivery (session_id, delivered);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE TABLE IF NOT EXISTS db_schema_version (
|
||||
id INTEGER NOT NULL PRIMARY KEY CHECK (id = 1),
|
||||
schema_version INTEGER NOT NULL,
|
||||
updated_at_ms INTEGER NOT NULL
|
||||
);
|
||||
""");
|
||||
|
||||
if (initializeVersionRow) {
|
||||
st.executeUpdate("""
|
||||
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
|
||||
VALUES (1, 1, CAST(strftime('%s','now') AS INTEGER) * 1000)
|
||||
ON CONFLICT(id) DO UPDATE SET
|
||||
schema_version = excluded.schema_version,
|
||||
updated_at_ms = excluded.updated_at_ms;
|
||||
""");
|
||||
}
|
||||
|
||||
DatabaseTriggersInstaller.createAllTriggers(st);
|
||||
}
|
||||
}
|
||||
|
||||
@ -14,6 +14,7 @@ import java.sql.Statement;
|
||||
public final class SqliteDbController {
|
||||
|
||||
private static volatile SqliteDbController instance;
|
||||
private static final int LATEST_SCHEMA_VERSION = DatabaseInitializer.SCHEMA_VERSION_1;
|
||||
|
||||
private final String jdbcUrl;
|
||||
|
||||
@ -38,7 +39,7 @@ public final class SqliteDbController {
|
||||
}
|
||||
|
||||
this.jdbcUrl = "jdbc:sqlite:" + dbPath;
|
||||
ensureSchemaUpgrades();
|
||||
ensureSchemaMigrations();
|
||||
}
|
||||
|
||||
public static SqliteDbController getInstance() {
|
||||
@ -70,42 +71,91 @@ public final class SqliteDbController {
|
||||
// no-op
|
||||
}
|
||||
|
||||
private void ensureSchemaUpgrades() {
|
||||
private void ensureSchemaMigrations() {
|
||||
int currentVersion = getCurrentSchemaVersion();
|
||||
|
||||
while (currentVersion < LATEST_SCHEMA_VERSION) {
|
||||
int nextVersion = currentVersion + 1;
|
||||
applyMigration(nextVersion);
|
||||
currentVersion = nextVersion;
|
||||
}
|
||||
}
|
||||
|
||||
private void applyMigration(int targetVersion) {
|
||||
switch (targetVersion) {
|
||||
case 1 -> migrateToV1();
|
||||
default -> throw new RuntimeException("Unknown DB migration target version: " + targetVersion);
|
||||
}
|
||||
}
|
||||
|
||||
private void migrateToV1() {
|
||||
try {
|
||||
DatabaseInitializer.ensureSchemaV1Structure(jdbcUrl);
|
||||
} catch (SQLException e) {
|
||||
throw new RuntimeException("DB migration to v1 failed (base schema)", e);
|
||||
}
|
||||
|
||||
try (Connection c = DriverManager.getConnection(jdbcUrl);
|
||||
Statement st = c.createStatement()) {
|
||||
|
||||
c.setAutoCommit(false);
|
||||
try {
|
||||
st.execute("PRAGMA foreign_keys = OFF");
|
||||
|
||||
ensureReactionsStateTable(st);
|
||||
ensureMessageViewsStateTable(st);
|
||||
|
||||
if (!tableExists(c, "connections_state")) {
|
||||
createConnectionsStateTable(st);
|
||||
} else if (needsConnectionsStateUpgrade(c)) {
|
||||
if (tableExists(c, "connections_state") && needsConnectionsStateUpgrade(c)) {
|
||||
rebuildConnectionsStateTable(st);
|
||||
}
|
||||
ensureChannelNamesStateTable(st);
|
||||
ensureChannelNamesDescriptionColumn(c, st);
|
||||
ensureConnectionsIndexes(st);
|
||||
ensureReactionsIndexes(st);
|
||||
ensureMessageViewsIndexes(st);
|
||||
ensureChannelNamesIndexes(st);
|
||||
ensureSignedMessageReceiptUniq(c, st);
|
||||
|
||||
ensureChannelNamesDescriptionColumn(c, st);
|
||||
ensureSignedMessageReceiptUniq(c, st);
|
||||
DatabaseTriggersInstaller.createAllTriggers(st);
|
||||
setSchemaVersion(c, 1);
|
||||
|
||||
st.execute("PRAGMA foreign_keys = ON");
|
||||
c.commit();
|
||||
} catch (Exception e) {
|
||||
try { c.rollback(); } catch (Exception ignored) {}
|
||||
throw new RuntimeException("DB schema upgrade failed", e);
|
||||
throw new RuntimeException("DB migration to v1 failed", e);
|
||||
} finally {
|
||||
try { c.setAutoCommit(true); } catch (Exception ignored) {}
|
||||
}
|
||||
} catch (SQLException e) {
|
||||
throw new RuntimeException("DB schema upgrade failed", e);
|
||||
throw new RuntimeException("DB migration to v1 failed", e);
|
||||
}
|
||||
}
|
||||
|
||||
private int getCurrentSchemaVersion() {
|
||||
try (Connection c = DriverManager.getConnection(jdbcUrl)) {
|
||||
if (!tableExists(c, DatabaseInitializer.DB_SCHEMA_VERSION_TABLE)) {
|
||||
return 0;
|
||||
}
|
||||
|
||||
try (var ps = c.prepareStatement("""
|
||||
SELECT schema_version
|
||||
FROM db_schema_version
|
||||
WHERE id = 1
|
||||
LIMIT 1
|
||||
""");
|
||||
ResultSet rs = ps.executeQuery()) {
|
||||
if (rs.next()) {
|
||||
return rs.getInt(1);
|
||||
}
|
||||
}
|
||||
return 0;
|
||||
} catch (SQLException e) {
|
||||
throw new RuntimeException("Cannot read DB schema version", e);
|
||||
}
|
||||
}
|
||||
|
||||
private static void setSchemaVersion(Connection c, int version) throws SQLException {
|
||||
try (var ps = c.prepareStatement("""
|
||||
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
|
||||
VALUES (1, ?, CAST(strftime('%s','now') AS INTEGER) * 1000)
|
||||
ON CONFLICT(id) DO UPDATE SET
|
||||
schema_version = excluded.schema_version,
|
||||
updated_at_ms = excluded.updated_at_ms
|
||||
""")) {
|
||||
ps.setInt(1, version);
|
||||
ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
|
||||
Loading…
Reference in New Issue
Block a user