aidar/SHiNE-server
SHA256
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Add handoff task document for PWA, chats, and connections

Browse Source
This commit is contained in:
ai5590
2026-04-05 12:13:05 +03:00
parent cf5460c5c7
commit bc8f4a0582
46 changed files with 1671 additions and 106 deletions
Download Patch File Download Diff File Expand all files Collapse all files
shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+48
View File
@@ -328,6 +328,54 @@ public final class DatabaseInitializer {
ON message_stats (to_login);
""");
// 9) direct_messages
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS direct_messages (
message_id TEXT NOT NULL PRIMARY KEY,
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
text TEXT NOT NULL,
created_at_ms INTEGER NOT NULL,
FOREIGN KEY (from_login) REFERENCES solana_users(login),
FOREIGN KEY (to_login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_direct_messages_to_login
ON direct_messages (to_login, created_at_ms);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_direct_messages_from_login
ON direct_messages (from_login, created_at_ms);
""");
// 10) user_push_tokens
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS user_push_tokens (
token_id TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
session_id TEXT NOT NULL,
provider TEXT NOT NULL,
token TEXT NOT NULL,
platform TEXT,
user_agent TEXT,
updated_at_ms INTEGER NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login
ON user_push_tokens (login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login_session
ON user_push_tokens (login, session_id);
""");
DatabaseTriggersInstaller.createAllTriggers(st);
}
}
shine-server-db/src/main/java/shine/db/dao/ConnectionsStateDAO.java
+29 -1
View File
@@ -125,4 +125,32 @@ public final class ConnectionsStateDAO {
}
return out;
}
}
public void upsertRelation(Connection c,
String login,
int relType,
String toLogin,
String toBchName,
Integer toBlockNumber,
byte[] toBlockHash) throws SQLException {
String sql = """
INSERT INTO connections_state (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(login, rel_type, to_login) DO UPDATE SET
to_bch_name=excluded.to_bch_name,
to_block_number=excluded.to_block_number,
to_block_hash=excluded.to_block_hash
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setInt(2, relType);
ps.setString(3, toLogin);
ps.setString(4, toBchName);
if (toBlockNumber == null) ps.setNull(5, java.sql.Types.INTEGER); else ps.setInt(5, toBlockNumber);
ps.setBytes(6, toBlockHash);
ps.executeUpdate();
}
}
}
shine-server-db/src/main/java/shine/db/dao/DirectMessagesDAO.java
+53
View File
@@ -0,0 +1,53 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.DirectMessageEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
public final class DirectMessagesDAO {
private static volatile DirectMessagesDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private DirectMessagesDAO() {}
public static DirectMessagesDAO getInstance() {
if (instance == null) {
synchronized (DirectMessagesDAO.class) {
if (instance == null) instance = new DirectMessagesDAO();
}
}
return instance;
}
public void insert(DirectMessageEntry entry) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO direct_messages (
message_id, from_login, to_login, text, created_at_ms
) VALUES (?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getMessageId());
ps.setString(2, entry.getFromLogin());
ps.setString(3, entry.getToLogin());
ps.setString(4, entry.getText());
ps.setLong(5, entry.getCreatedAtMs());
ps.executeUpdate();
}
}
}
public boolean existsFromTo(String fromLogin, String toLogin) throws Exception {
try (Connection c = db.getConnection()) {
String sql = "SELECT 1 FROM direct_messages WHERE from_login = ? AND to_login = ? LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, fromLogin);
ps.setString(2, toLogin);
return ps.executeQuery().next();
}
}
}
}
shine-server-db/src/main/java/shine/db/dao/PushTokensDAO.java
+116
View File
@@ -0,0 +1,116 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.PushTokenEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public final class PushTokensDAO {
private static volatile PushTokensDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private PushTokensDAO() {}
public static PushTokensDAO getInstance() {
if (instance == null) {
synchronized (PushTokensDAO.class) {
if (instance == null) instance = new PushTokensDAO();
}
}
return instance;
}
public void upsert(PushTokenEntry entry) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO user_push_tokens (
token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(token_id) DO UPDATE SET
login=excluded.login,
session_id=excluded.session_id,
provider=excluded.provider,
token=excluded.token,
platform=excluded.platform,
user_agent=excluded.user_agent,
updated_at_ms=excluded.updated_at_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getTokenId());
ps.setString(2, entry.getLogin());
ps.setString(3, entry.getSessionId());
ps.setString(4, entry.getProvider());
ps.setString(5, entry.getToken());
ps.setString(6, entry.getPlatform());
ps.setString(7, entry.getUserAgent());
ps.setLong(8, entry.getUpdatedAtMs());
ps.executeUpdate();
}
}
}
public List<PushTokenEntry> listByLogin(String login) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
FROM user_push_tokens
WHERE login = ?
ORDER BY updated_at_ms DESC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
List<PushTokenEntry> out = new ArrayList<>();
while (rs.next()) {
PushTokenEntry e = new PushTokenEntry();
e.setTokenId(rs.getString("token_id"));
e.setLogin(rs.getString("login"));
e.setSessionId(rs.getString("session_id"));
e.setProvider(rs.getString("provider"));
e.setToken(rs.getString("token"));
e.setPlatform(rs.getString("platform"));
e.setUserAgent(rs.getString("user_agent"));
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
out.add(e);
}
return out;
}
}
}
}
public List<PushTokenEntry> listByLoginAndSession(String login, String sessionId) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
FROM user_push_tokens
WHERE login = ? AND session_id = ?
ORDER BY updated_at_ms DESC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setString(2, sessionId);
try (ResultSet rs = ps.executeQuery()) {
List<PushTokenEntry> out = new ArrayList<>();
while (rs.next()) {
PushTokenEntry e = new PushTokenEntry();
e.setTokenId(rs.getString("token_id"));
e.setLogin(rs.getString("login"));
e.setSessionId(rs.getString("session_id"));
e.setProvider(rs.getString("provider"));
e.setToken(rs.getString("token"));
e.setPlatform(rs.getString("platform"));
e.setUserAgent(rs.getString("user_agent"));
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
out.add(e);
}
return out;
}
}
}
}
}
shine-server-db/src/main/java/shine/db/entities/DirectMessageEntry.java
+24
View File
@@ -0,0 +1,24 @@
package shine.db.entities;
public class DirectMessageEntry {
private String messageId;
private String fromLogin;
private String toLogin;
private String text;
private long createdAtMs;
public String getMessageId() { return messageId; }
public void setMessageId(String messageId) { this.messageId = messageId; }
public String getFromLogin() { return fromLogin; }
public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public String getText() { return text; }
public void setText(String text) { this.text = text; }
public long getCreatedAtMs() { return createdAtMs; }
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
}
shine-server-db/src/main/java/shine/db/entities/PushTokenEntry.java
+36
View File
@@ -0,0 +1,36 @@
package shine.db.entities;
public class PushTokenEntry {
private String tokenId;
private String login;
private String sessionId;
private String provider;
private String token;
private String platform;
private String userAgent;
private long updatedAtMs;
public String getTokenId() { return tokenId; }
public void setTokenId(String tokenId) { this.tokenId = tokenId; }
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getSessionId() { return sessionId; }
public void setSessionId(String sessionId) { this.sessionId = sessionId; }
public String getProvider() { return provider; }
public void setProvider(String provider) { this.provider = provider; }
public String getToken() { return token; }
public void setToken(String token) { this.token = token; }
public String getPlatform() { return platform; }
public void setPlatform(String platform) { this.platform = platform; }
public String getUserAgent() { return userAgent; }
public void setUserAgent(String userAgent) { this.userAgent = userAgent; }
public long getUpdatedAtMs() { return updatedAtMs; }
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
}