SHA256
411 lines
17 KiB
Java
411 lines
17 KiB
Java
package shine.db;
|
|
|
|
import java.io.IOException;
|
|
import java.io.InputStream;
|
|
import java.nio.charset.StandardCharsets;
|
|
import java.sql.Connection;
|
|
import java.sql.DriverManager;
|
|
import java.sql.ResultSet;
|
|
import java.sql.SQLException;
|
|
import java.sql.Statement;
|
|
import java.util.ArrayList;
|
|
import java.util.List;
|
|
|
|
/**
|
|
* PostgreSQL runtime schema bootstrapper for SHiNE server.
|
|
*/
|
|
public final class DatabaseInitializer {
|
|
|
|
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
|
|
public static final int SCHEMA_VERSION_1 = 1;
|
|
public static final int SCHEMA_VERSION_2 = 2;
|
|
public static final int SCHEMA_VERSION_3 = 3;
|
|
public static final int SCHEMA_VERSION_4 = 4;
|
|
public static final int SCHEMA_VERSION_5 = 5;
|
|
public static final int SCHEMA_VERSION_6 = 6;
|
|
public static final int SCHEMA_VERSION_7 = 7;
|
|
public static final int SCHEMA_VERSION_8 = 8;
|
|
public static final int SCHEMA_VERSION_9 = 9;
|
|
public static final int SCHEMA_VERSION_10 = 10;
|
|
public static final int SCHEMA_VERSION_11 = 11;
|
|
public static final int SCHEMA_VERSION_12 = 12;
|
|
public static final int SCHEMA_VERSION_13 = 13;
|
|
public static final int SCHEMA_VERSION_14 = 14;
|
|
public static final int SCHEMA_VERSION_15 = 15;
|
|
public static final int SCHEMA_VERSION_16 = 16;
|
|
public static final int SCHEMA_VERSION_17 = 17;
|
|
public static final int SCHEMA_VERSION_18 = 18;
|
|
public static final int SCHEMA_VERSION_19 = 19;
|
|
public static final int SCHEMA_VERSION_20 = 20;
|
|
public static final int SCHEMA_VERSION_21 = 21;
|
|
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
|
|
public static final String POSTGRES_MIGRATION_V2_RESOURCE = "postgres/migration_v2.sql";
|
|
public static final String POSTGRES_MIGRATION_V3_RESOURCE = "postgres/migration_v3.sql";
|
|
public static final String POSTGRES_MIGRATION_V4_RESOURCE = "postgres/migration_v4.sql";
|
|
public static final String POSTGRES_MIGRATION_V5_RESOURCE = "postgres/migration_v5.sql";
|
|
public static final String POSTGRES_MIGRATION_V6_RESOURCE = "postgres/migration_v6.sql";
|
|
public static final String POSTGRES_MIGRATION_V7_RESOURCE = "postgres/migration_v7.sql";
|
|
public static final String POSTGRES_MIGRATION_V8_RESOURCE = "postgres/migration_v8.sql";
|
|
public static final String POSTGRES_MIGRATION_V9_RESOURCE = "postgres/migration_v9.sql";
|
|
public static final String POSTGRES_MIGRATION_V10_RESOURCE = "postgres/migration_v10.sql";
|
|
public static final String POSTGRES_MIGRATION_V11_RESOURCE = "postgres/migration_v11.sql";
|
|
public static final String POSTGRES_MIGRATION_V12_RESOURCE = "postgres/migration_v12.sql";
|
|
public static final String POSTGRES_MIGRATION_V13_RESOURCE = "postgres/migration_v13.sql";
|
|
public static final String POSTGRES_MIGRATION_V14_RESOURCE = "postgres/migration_v14.sql";
|
|
public static final String POSTGRES_MIGRATION_V15_RESOURCE = "postgres/migration_v15.sql";
|
|
public static final String POSTGRES_MIGRATION_V16_RESOURCE = "postgres/migration_v16.sql";
|
|
public static final String POSTGRES_MIGRATION_V17_RESOURCE = "postgres/migration_v17.sql";
|
|
public static final String POSTGRES_MIGRATION_V18_RESOURCE = "postgres/migration_v18.sql";
|
|
public static final String POSTGRES_MIGRATION_V19_RESOURCE = "postgres/migration_v19.sql";
|
|
public static final String POSTGRES_MIGRATION_V20_RESOURCE = "postgres/migration_v20.sql";
|
|
public static final String POSTGRES_MIGRATION_V21_RESOURCE = "postgres/migration_v21.sql";
|
|
|
|
private DatabaseInitializer() {}
|
|
|
|
public static final short TEXT_POST = 10;
|
|
public static final short TEXT_EDIT_POST = 11;
|
|
public static final short TEXT_REPLY = 20;
|
|
public static final short TEXT_EDIT_REPLY = 21;
|
|
public static final short TEXT_RATING = 30;
|
|
public static final short TEXT_REPOST = 50;
|
|
public static final short TEXT_CHANNEL_META = 90;
|
|
public static final short TEXT_ENTRYPOINT = 100;
|
|
public static final short TEXT_EXERCISE = 110;
|
|
public static final short TEXT_SERVICE = 120;
|
|
public static final short TEXT_COURSE = 130;
|
|
|
|
public static final short REACTION_LIKE = 1;
|
|
public static final short REACTION_UNLIKE = 2;
|
|
|
|
public static final short CONNECTION_CLOSE_FRIEND = 10;
|
|
public static final short CONNECTION_UNCLOSE_FRIEND = 11;
|
|
|
|
public static final short CONNECTION_FRIEND = 14;
|
|
public static final short CONNECTION_UNFRIEND = 15;
|
|
|
|
public static final short CONNECTION_CONTACT = 20;
|
|
public static final short CONNECTION_UNCONTACT = 21;
|
|
|
|
// FOLLOW на публичный канал используется; user-to-user FOLLOW legacy. DO NOT REMOVE.
|
|
public static final short CONNECTION_FOLLOW = 30;
|
|
public static final short CONNECTION_UNFOLLOW = 31;
|
|
|
|
// Родственные связи пока не используются в UI. DO NOT REMOVE.
|
|
public static final short CONNECTION_SPOUSE = 40;
|
|
public static final short CONNECTION_UNSPOUSE = 41;
|
|
|
|
public static final short CONNECTION_PARENT = 50;
|
|
public static final short CONNECTION_UNPARENT = 51;
|
|
|
|
public static final short CONNECTION_CHILD = 52;
|
|
public static final short CONNECTION_UNCHILD = 53;
|
|
|
|
public static final short CONNECTION_SIBLING = 54;
|
|
public static final short CONNECTION_UNSIBLING = 55;
|
|
|
|
// Legacy 60/61: новый UI не использует. DO NOT REMOVE.
|
|
public static final short CONNECTION_KNOWN_PERSON = 60;
|
|
public static final short CONNECTION_UNKNOWN_PERSON = 61;
|
|
|
|
public static final short CONNECTION_SHINE_CONFIRMED = 70;
|
|
public static final short CONNECTION_SHINE_UNCONFIRMED = 71;
|
|
|
|
// Пока не используется в UI. DO NOT REMOVE.
|
|
public static final short CONNECTION_SHINE_SEEN = 74;
|
|
public static final short CONNECTION_SHINE_UNSEEN = 75;
|
|
|
|
public static final short CONNECTION_OFFICIAL_ACCOUNT_CONFIRMED = 80;
|
|
public static final short CONNECTION_OFFICIAL_ACCOUNT_UNCONFIRMED = 81;
|
|
|
|
public static void ensurePostgresSchemaInitialized(String jdbcUrl,
|
|
String user,
|
|
String password) throws SQLException {
|
|
try {
|
|
Class.forName("org.postgresql.Driver");
|
|
} catch (ClassNotFoundException e) {
|
|
throw new RuntimeException("PostgreSQL JDBC driver not found", e);
|
|
}
|
|
|
|
try (Connection conn = openConnection(jdbcUrl, user, password)) {
|
|
if (!postgresSchemaVersionTableExists(conn)) {
|
|
runSqlScript(conn, POSTGRES_SCHEMA_RESOURCE);
|
|
return;
|
|
}
|
|
int currentVersion = readCurrentSchemaVersion(conn);
|
|
if (currentVersion < SCHEMA_VERSION_2) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V2_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_2;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_3) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V3_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_3;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_4) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V4_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_4;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_5) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V5_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_5;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_6) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V6_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_6;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_7) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V7_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_7;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_8) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V8_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_8;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_9) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V9_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_9;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_10) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V10_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_10;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_11) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V11_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_11;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_12) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V12_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_12;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_13) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V13_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_13;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_14) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V14_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_14;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_15) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V15_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_15;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_16) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V16_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_16;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_17) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V17_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_17;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_18) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V18_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_18;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_19) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V19_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_19;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_20) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V20_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_20;
|
|
}
|
|
if (currentVersion < SCHEMA_VERSION_21) {
|
|
runSqlScript(conn, POSTGRES_MIGRATION_V21_RESOURCE);
|
|
currentVersion = SCHEMA_VERSION_21;
|
|
}
|
|
}
|
|
}
|
|
|
|
private static Connection openConnection(String jdbcUrl, String user, String password) throws SQLException {
|
|
if (user == null || user.isBlank()) {
|
|
return DriverManager.getConnection(jdbcUrl);
|
|
}
|
|
return DriverManager.getConnection(jdbcUrl, user, password == null ? "" : password);
|
|
}
|
|
|
|
private static boolean postgresSchemaVersionTableExists(Connection conn) throws SQLException {
|
|
String sql = """
|
|
SELECT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.tables
|
|
WHERE table_schema = current_schema()
|
|
AND table_name = ?
|
|
)
|
|
""";
|
|
try (var ps = conn.prepareStatement(sql)) {
|
|
ps.setString(1, DB_SCHEMA_VERSION_TABLE);
|
|
try (ResultSet rs = ps.executeQuery()) {
|
|
return rs.next() && rs.getBoolean(1);
|
|
}
|
|
}
|
|
}
|
|
|
|
private static int readCurrentSchemaVersion(Connection conn) throws SQLException {
|
|
String sql = """
|
|
SELECT schema_version
|
|
FROM db_schema_version
|
|
WHERE id = 1
|
|
""";
|
|
try (Statement st = conn.createStatement();
|
|
ResultSet rs = st.executeQuery(sql)) {
|
|
if (!rs.next()) {
|
|
return SCHEMA_VERSION_1;
|
|
}
|
|
return rs.getInt(1);
|
|
}
|
|
}
|
|
|
|
private static void runSqlScript(Connection conn, String resourcePath) throws SQLException {
|
|
String sqlScript = loadClasspathResource(resourcePath);
|
|
List<String> statements = splitSqlStatements(sqlScript);
|
|
|
|
boolean previousAutoCommit = conn.getAutoCommit();
|
|
conn.setAutoCommit(true);
|
|
try (Statement st = conn.createStatement()) {
|
|
for (String statement : statements) {
|
|
String trimmed = statement.trim();
|
|
if (!trimmed.isEmpty()) {
|
|
st.execute(trimmed);
|
|
}
|
|
}
|
|
} finally {
|
|
conn.setAutoCommit(previousAutoCommit);
|
|
}
|
|
}
|
|
|
|
private static String loadClasspathResource(String resourcePath) {
|
|
try (InputStream in = DatabaseInitializer.class.getClassLoader().getResourceAsStream(resourcePath)) {
|
|
if (in == null) {
|
|
throw new RuntimeException("Resource not found: " + resourcePath);
|
|
}
|
|
return new String(in.readAllBytes(), StandardCharsets.UTF_8);
|
|
} catch (IOException e) {
|
|
throw new RuntimeException("Failed to read resource: " + resourcePath, e);
|
|
}
|
|
}
|
|
|
|
static List<String> splitSqlStatements(String sqlScript) {
|
|
List<String> statements = new ArrayList<>();
|
|
StringBuilder current = new StringBuilder();
|
|
boolean inSingleQuote = false;
|
|
boolean inLineComment = false;
|
|
boolean inBlockComment = false;
|
|
String dollarQuoteTag = null;
|
|
|
|
for (int i = 0; i < sqlScript.length(); i++) {
|
|
char ch = sqlScript.charAt(i);
|
|
|
|
if (inLineComment) {
|
|
current.append(ch);
|
|
if (ch == '\n') {
|
|
inLineComment = false;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (inBlockComment) {
|
|
current.append(ch);
|
|
if (ch == '*' && i + 1 < sqlScript.length() && sqlScript.charAt(i + 1) == '/') {
|
|
current.append('/');
|
|
i += 1;
|
|
inBlockComment = false;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (dollarQuoteTag != null) {
|
|
if (startsWithAt(sqlScript, i, dollarQuoteTag)) {
|
|
current.append(dollarQuoteTag);
|
|
i += dollarQuoteTag.length() - 1;
|
|
dollarQuoteTag = null;
|
|
} else {
|
|
current.append(ch);
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (inSingleQuote) {
|
|
current.append(ch);
|
|
if (ch == '\'') {
|
|
if (i + 1 < sqlScript.length() && sqlScript.charAt(i + 1) == '\'') {
|
|
current.append('\'');
|
|
i += 1;
|
|
} else {
|
|
inSingleQuote = false;
|
|
}
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if (ch == '-' && i + 1 < sqlScript.length() && sqlScript.charAt(i + 1) == '-') {
|
|
current.append("--");
|
|
i += 1;
|
|
inLineComment = true;
|
|
continue;
|
|
}
|
|
|
|
if (ch == '/' && i + 1 < sqlScript.length() && sqlScript.charAt(i + 1) == '*') {
|
|
current.append("/*");
|
|
i += 1;
|
|
inBlockComment = true;
|
|
continue;
|
|
}
|
|
|
|
if (ch == '\'') {
|
|
inSingleQuote = true;
|
|
current.append(ch);
|
|
continue;
|
|
}
|
|
|
|
String tag = readDollarQuoteTag(sqlScript, i);
|
|
if (tag != null) {
|
|
current.append(tag);
|
|
i += tag.length() - 1;
|
|
dollarQuoteTag = tag;
|
|
continue;
|
|
}
|
|
|
|
if (ch == ';') {
|
|
String statement = current.toString().trim();
|
|
if (!statement.isEmpty()) {
|
|
statements.add(current.toString());
|
|
}
|
|
current.setLength(0);
|
|
continue;
|
|
}
|
|
|
|
current.append(ch);
|
|
}
|
|
|
|
if (!current.isEmpty()) {
|
|
String statement = current.toString().trim();
|
|
if (!statement.isEmpty()) {
|
|
statements.add(current.toString());
|
|
}
|
|
}
|
|
return statements;
|
|
}
|
|
|
|
private static boolean startsWithAt(String sqlScript, int index, String token) {
|
|
return sqlScript.regionMatches(index, token, 0, token.length());
|
|
}
|
|
|
|
private static String readDollarQuoteTag(String sqlScript, int index) {
|
|
if (sqlScript.charAt(index) != '$') {
|
|
return null;
|
|
}
|
|
|
|
int end = index + 1;
|
|
while (end < sqlScript.length()) {
|
|
char current = sqlScript.charAt(end);
|
|
if (current == '$') {
|
|
return sqlScript.substring(index, end + 1);
|
|
}
|
|
if (!(Character.isLetterOrDigit(current) || current == '_')) {
|
|
return null;
|
|
}
|
|
end++;
|
|
}
|
|
return null;
|
|
}
|
|
}
|