package com.comandante.stickypunch;
import com.google.common.base.Optional;
import com.comandante.stickypunch.api.model.WebPushStore;
import com.comandante.stickypunch.api.model.WebPushUser;
import com.comandante.stickypunch.api.model.WebPushUserBuilder;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class SQLiteWebPushStore implements WebPushStore {
private static final Logger log = LogManager.getLogger(SQLiteWebPushStore.class);
private final WebPushStoreConfiguration webPushStoreConfiguration;
private final Connection conn;
public SQLiteWebPushStore(WebPushStoreConfiguration webPushStoreConfiguration) throws SQLException {
this.webPushStoreConfiguration = webPushStoreConfiguration;
conn = DriverManager.getConnection(webPushStoreConfiguration.sqLiteDatabaseConnectUrl);
}
public void createWebPushUserTable() throws SQLException {
Statement statement = conn.createStatement();
final String createSql = "CREATE TABLE if not exists webPushUser (" +
"userId char(36) NOT NULL, " +
"websitePushId char(255), " +
"deviceToken char(64), " +
"isActive INTEGER NOT NULL," +
"isActiveTimestamp INTEGER NOT NULL," +
"PRIMARY KEY (userId), " +
"UNIQUE (deviceToken))";
statement.executeUpdate(createSql);
}
public void dropWebPushUserTable() throws SQLException {
Statement statement = conn.createStatement();
final String dropSql = "DROP TABLE if exists webPushUser";
statement.executeUpdate(dropSql);
}
public Optional<WebPushUser> get(String userId) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("SELECT userId, websitePushId, deviceToken, isActive, isActiveTimestamp FROM webPushUser WHERE userId=?");
stmt.setString(1, userId);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return Optional.absent();
}
String deviceToken = rs.getString("deviceToken");
String websitePushId = rs.getString("websitePushId");
Long isActiveTimestamp = rs.getLong("isActiveTimestamp");
boolean isActive = rs.getInt("isActive") > 0;
return Optional.of(new WebPushUserBuilder()
.setUserId(userId)
.setDeviceToken(deviceToken)
.setWebsitePushId(websitePushId)
.setActive(isActive)
.setIsActiveTimestamp(isActiveTimestamp)
.build());
}
public Optional<WebPushUser> getDeviceTokenAndUserId(String userId, String deviceToken) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("SELECT userId, websitePushId, deviceToken, isActive, isActiveTimestamp FROM webPushUser WHERE userId=? AND deviceToken=?");
stmt.setString(1, userId);
stmt.setString(2, deviceToken);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return Optional.absent();
}
String token = rs.getString("deviceToken");
String websitePushId = rs.getString("websitePushId");
Long isActiveTimestamp = rs.getLong("isActiveTimestamp");
boolean isActive = rs.getInt("isActive") > 0;
return Optional.of(new WebPushUserBuilder()
.setUserId(userId)
.setDeviceToken(token)
.setWebsitePushId(websitePushId)
.setActive(isActive)
.setIsActiveTimestamp(isActiveTimestamp)
.build());
}
public Optional<WebPushUser> getLastUpdatedDeviceToken(String deviceToken) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("SELECT userId, websitePushId, deviceToken, isActive, isActiveTimestamp FROM webPushUser WHERE deviceToken=? AND isActiveTimestamp=(SELECT MAX(isActiveTimestamp) where deviceToken=?)");
stmt.setString(1, deviceToken);
stmt.setString(2, deviceToken);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return Optional.absent();
}
String userId = rs.getString("userId");
String token = rs.getString("deviceToken");
String websitePushId = rs.getString("websitePushId");
Long isActiveTimestamp = rs.getLong("isActiveTimestamp");
boolean isActive = rs.getInt("isActive") > 0;
return Optional.of(new WebPushUserBuilder()
.setUserId(userId)
.setDeviceToken(token)
.setWebsitePushId(websitePushId)
.setActive(isActive)
.setIsActiveTimestamp(isActiveTimestamp)
.build());
}
public List<WebPushUser> getAll() throws SQLException {
Statement statement = conn.createStatement();
final String getSql = "SELECT userId, websitePushId, deviceToken, isActive, isActiveTimestamp FROM webPushUser WHERE deviceToken IS NOT NULL";
List<WebPushUser> retList = new ArrayList<WebPushUser>();
ResultSet rs = statement.executeQuery(getSql);
while (rs.next()) {
String userId = rs.getString("userId");
String token = rs.getString("deviceToken");
String websitePushId = rs.getString("websitePushId");
Long isActiveTimestamp = rs.getLong("isActiveTimestamp");
boolean isActive = rs.getInt("isActive") > 0;
retList.add(new WebPushUserBuilder()
.setUserId(userId)
.setDeviceToken(token)
.setWebsitePushId(websitePushId)
.setActive(isActive)
.setIsActiveTimestamp(isActiveTimestamp)
.build());
}
return retList;
}
public void save(WebPushUser webPushUser) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(
"REPLACE INTO webPushUser(userId, websitePushId, deviceToken, isActive, isActiveTimestamp) VALUES(?,?,?,?,?)");
stmt.setString(1, webPushUser.getUserId().get());
if (webPushUser.getWebsitePushId().isPresent()) {
stmt.setString(2, webPushUser.getWebsitePushId().get());
}
if (webPushUser.getDeviceToken().isPresent()) {
stmt.setString(3, webPushUser.getDeviceToken().get());
}
if (webPushUser.getActive().isPresent()) {
stmt.setInt(4, webPushUser.getActive().get() ? 1 : 0);
}
if (webPushUser.getActiveTimestamp().isPresent()) {
stmt.setLong(5, webPushUser.getActiveTimestamp().get());
}
stmt.executeUpdate();
}
@Override
public void updateWebPushUser(WebPushUser webPushUser) throws Exception {
this.save(webPushUser);
}
@Override
public Optional<WebPushUser> getWebPushUserMostRecentlyUpdatedByDeviceToken(String deviceToken) {
Optional<WebPushUser> webPushUserOptional = Optional.absent();
try {
webPushUserOptional = this.getLastUpdatedDeviceToken(deviceToken);
} catch (SQLException e) {
log.error("Unable to retrieve webPushUser.", e);
}
return webPushUserOptional;
}
@Override
public Optional<WebPushUser> getWebPushUsersByUserId(String userId) {
Optional<WebPushUser> webPushUserOptional = Optional.absent();
try {
webPushUserOptional = this.get(userId);
} catch (SQLException e) {
log.error("Unable to retrieve webPushUser.", e);
}
return webPushUserOptional;
}
@Override
public Optional<WebPushUser> getWebPushUsersByUserIdAndDeviceToken(String userId, String deviceToken) {
Optional<WebPushUser> webPushUserOptional = Optional.absent();
try {
webPushUserOptional = this.getDeviceTokenAndUserId(userId, deviceToken);
} catch (SQLException e) {
log.error("Unable to retrieve webPushUser.", e);
}
return webPushUserOptional;
}
@Override
public Optional<List<WebPushUser>> getWebPushUsers() {
List<WebPushUser> webPushUsers = null;
try {
webPushUsers = this.getAll();
} catch (SQLException e) {
log.error("Unable to retrieve webPushUsers.", e);
}
if (webPushUsers.size() <= 0) {
return Optional.absent();
}
return Optional.of(webPushUsers);
}
}