@Override public String getDeleteRowsSqlForSegments(int numSegments) { StringBuilder stringBuilder = new StringBuilder("DELETE FROM "); stringBuilder.append(getTableName()); // Note the timestamp or is surrounded with parenthesis stringBuilder.append(" WHERE "); stringBuilder.append(config.segmentColumnName()); stringBuilder.append(" IN (?"); for (int i = 1; i < numSegments; ++i) { stringBuilder.append(",?"); } stringBuilder.append(")"); return stringBuilder.toString(); }
@Override public String getCountNonExpiredRowsSqlForSegments(int numSegments) { StringBuilder stringBuilder = new StringBuilder("SELECT COUNT(*) FROM "); stringBuilder.append(getTableName()); // Note the timestamp or is surrounded with parenthesis stringBuilder.append(" WHERE ("); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" > ? OR "); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" < 0) AND "); stringBuilder.append(config.segmentColumnName()); stringBuilder.append(" IN (?"); for (int i = 1; i < numSegments; ++i) { stringBuilder.append(",?"); } stringBuilder.append(")"); return stringBuilder.toString(); }
@Override public String getLoadNonExpiredRowsSqlForSegments(int numSegments) { StringBuilder stringBuilder = new StringBuilder("SELECT "); stringBuilder.append(config.dataColumnName()); stringBuilder.append(", "); stringBuilder.append(config.idColumnName()); stringBuilder.append(" FROM "); stringBuilder.append(getTableName()); // Note the timestamp or is surrounded with parenthesis stringBuilder.append(" WHERE ("); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" > ? OR "); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" < 0) AND "); stringBuilder.append(config.segmentColumnName()); stringBuilder.append(" IN (?"); for (int i = 1; i < numSegments; ++i) { stringBuilder.append(",?"); } stringBuilder.append(")"); return stringBuilder.toString(); }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { // Assumes that config.idColumnName is the primary key if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("%1$s ON DUPLICATE KEY UPDATE %2$s = VALUES(%2$s), %3$s = VALUES(%3$s)", getInsertRowSql(), config.dataColumnName(), config.timestampColumnName()); } else { upsertRowSql = String.format("%1$s ON DUPLICATE KEY UPDATE %2$s = VALUES(%2$s), %3$s = VALUES(%3$s), %4$s = VALUES(%4$s)", getInsertRowSql(), config.dataColumnName(), config.timestampColumnName(), config.segmentColumnName()); } } return upsertRowSql; } }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s (%2$s, %3$s, %4$s) KEY(%4$s) VALUES(?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s (%2$s, %3$s, %4$s, %5$s) KEY(%4$s) VALUES(?, ?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
@Override public String getInsertRowSql() { if (insertRowSql == null) { if (metaData.isSegmentedDisabled()) { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName()); } else { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName(), config.segmentColumnName()); } } return insertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("INSERT OR REPLACE INTO %s (%s, %s, %s) VALUES (?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("INSERT OR REPLACE INTO %s (%s, %s, %s, %s) VALUES (?, ?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; } }
@Override public String getInsertRowSql() { if (insertRowSql == null) { if (metaData.isSegmentedDisabled()) { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return insertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT * FROM TABLE (VALUES (?,?,?))) AS tmp(%4$s, %3$s, %2$s) " + "ON t.%4$s = tmp.%4$s " + "WHEN MATCHED THEN UPDATE SET (t.%2$s, t.%3$s) = (tmp.%2$s, tmp.%3$s) " + "WHEN NOT MATCHED THEN INSERT (t.%4$s, t.%3$s, t.%2$s) VALUES (tmp.%4$s, tmp.%3$s, tmp.%2$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT * FROM TABLE (VALUES (?,?,?,?))) AS tmp(%4$s, %3$s, %2$s, %5$s) " + "ON t.%4$s = tmp.%4$s " + "WHEN MATCHED THEN UPDATE SET (t.%2$s, t.%3$s, t.%5$s) = (tmp.%2$s, tmp.%3$s, tmp.%5$s) " + "WHEN NOT MATCHED THEN INSERT (t.%4$s, t.%3$s, t.%2$s, t.%5$s) VALUES (tmp.%4$s, tmp.%3$s, tmp.%2$s, tmp.%5$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s) AS tmp " + "ON (t.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET t.%2$s = tmp.%2$s, t.%3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT VALUES (tmp.%4$s, tmp.%2$s, tmp.%3$s)", this.getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s, ? %5$s) AS tmp " + "ON (t.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET t.%2$s = tmp.%2$s, t.%3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT VALUES (tmp.%4$s, tmp.%2$s, tmp.%3$s, tmp.%5$s)", this.getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; } }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s from dual) tmp ON (t.%2$s = tmp.%2$s) " + "WHEN MATCHED THEN UPDATE SET t.%3$s = tmp.%3$s, t.%4$s = tmp.%4$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s)", this.getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s, ? %5$s from dual) tmp ON (t.%2$s = tmp.%2$s) " + "WHEN MATCHED THEN UPDATE SET t.%3$s = tmp.%3$s, t.%4$s = tmp.%4$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s, %5$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s, tmp.%5$s)", this.getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s " + "USING (VALUES (?, ?, ?)) AS tmp (%2$s, %3$s, %4$s) " + "ON (%2$s = tmp.%2$s) " + "WHEN MATCHED THEN UPDATE SET %3$s = tmp.%3$s, %4$s = tmp.%4$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s " + "USING (VALUES (?, ?, ?, ?)) AS tmp (%2$s, %3$s, %4$s, %5$s) " + "ON (%2$s = tmp.%2$s) " + "WHEN MATCHED THEN UPDATE SET %3$s = tmp.%3$s, %4$s = tmp.%4$s, %5$s = tmp.%5$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s, %5$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s, tmp.%5$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { // As SQL Server does not handle a merge atomically, we must acquire the table lock here otherwise it's possible // for deadlocks to occur. if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE %1$s WITH (TABLOCK) " + "USING (VALUES (?, ?, ?)) AS tmp (%2$s, %3$s, %4$s) " + "ON (%1$s.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET %2$s = tmp.%2$s, %3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s);", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE %1$s WITH (TABLOCK) " + "USING (VALUES (?, ?, ?, ?)) AS tmp (%2$s, %3$s, %4$s, %5$s) " + "ON (%1$s.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET %2$s = tmp.%2$s, %3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT (%2$s, %3$s, %4$s, %5$s) VALUES (tmp.%2$s, tmp.%3$s, tmp.%4$s, %5$s);", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
public void createTable(Connection conn) throws PersistenceException { if (cacheName == null || cacheName.trim().length() == 0) throw new PersistenceException("cacheName needed in order to create table"); String ddl; if (metaData.isSegmentedDisabled()) { ddl = String.format("CREATE TABLE %1$s (%2$s %3$s NOT NULL, %4$s %5$s NOT NULL, %6$s %7$s NOT NULL, PRIMARY KEY (%2$s))", getTableName(), config.idColumnName(), config.idColumnType(), config.dataColumnName(), config.dataColumnType(), config.timestampColumnName(), config.timestampColumnType()); } else { ddl = String.format("CREATE TABLE %1$s (%2$s %3$s NOT NULL, %4$s %5$s NOT NULL, %6$s %7$s NOT NULL, %8$s %9$s NOT NULL, PRIMARY KEY (%2$s))", getTableName(), config.idColumnName(), config.idColumnType(), config.dataColumnName(), config.dataColumnType(), config.timestampColumnName(), config.timestampColumnType(), config.segmentColumnName(), config.segmentColumnType()); } if (log.isTraceEnabled()) { log.tracef("Creating table with following DDL: '%s'.", ddl); } executeUpdateSql(conn, ddl); }
@Override public void start() throws PersistenceException { if (config.createOnStart()) { Connection conn = null; try { conn = connectionFactory.getConnection(); if (!tableExists(conn)) { createTable(conn); } createIndex(conn, timestampIndexExt, config.timestampColumnName()); if (!metaData.isSegmentedDisabled()) { createIndex(conn, segmentIndexExt, config.segmentColumnName()); } } finally { connectionFactory.releaseConnection(conn); } } }