1 package org.inigma.utopia.sync;
2
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5 import java.sql.Timestamp;
6 import java.util.Calendar;
7 import java.util.List;
8
9 import javax.sql.DataSource;
10
11 import org.inigma.utopia.Army;
12 import org.inigma.utopia.Coordinate;
13 import org.inigma.utopia.Kingdom;
14 import org.inigma.utopia.Military;
15 import org.inigma.utopia.Personality;
16 import org.inigma.utopia.Province;
17 import org.inigma.utopia.Race;
18 import org.inigma.utopia.Rank;
19 import org.inigma.utopia.Relation;
20 import org.inigma.utopia.Science;
21 import org.inigma.utopia.Stance;
22 import org.inigma.utopia.Survey;
23 import org.inigma.utopia.utils.CalendarUtils;
24 import org.springframework.beans.factory.annotation.Autowired;
25 import org.springframework.dao.EmptyResultDataAccessException;
26 import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
27 import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
28
29 public class DataSyncTemplate extends SimpleJdbcTemplate {
30 private static final String EXISTS_KINGDOM = "SELECT id FROM kingdom WHERE id=? ";
31 private static final String EXISTS_PROVINCE = "SELECT id FROM province WHERE id=? ";
32 private static final String EXISTS_SCIENCE = "SELECT id FROM science WHERE id=? ";
33 private static final String EXISTS_SURVEY = "SELECT id FROM survey WHERE id=? ";
34 private static final String EXISTS_MILITARY = "SELECT id FROM military WHERE id=? ";
35
36 private static final String SELECT_SYNC_KINGDOM = "SELECT id, last_update, kingdom, island, name, relation, stance, "
37 + "war_count, war_win, war_nwdiff "
38 + "FROM kingdom "
39 + "WHERE last_update > ? ";
40 private static final String SELECT_SYNC_PROVINCE = "SELECT id, kingdom_id, last_update, name, acres, gender, leader, "
41 + "networth, race, rank, personality, peasants, gold, food, runes, trade, thieves, wizards, "
42 + "soldiers, offspecs, defspecs, elites, horses, prisoners, offense, defense "
43 + "FROM province "
44 + "WHERE last_update > ? "
45 + "OR id IN (SELECT province_id FROM science WHERE last_update > ? "
46 + " UNION ALL "
47 + " SELECT province_id FROM survey WHERE last_update > ? "
48 + " UNION ALL "
49 + " SELECT province_id FROM military WHERE last_update > ? )";
50
51 private static final String SELECT_KINGDOM = "SELECT id, last_update, kingdom, island, name, relation, stance, "
52 + "war_count, war_win, war_nwdiff "
53 + "FROM kingdom "
54 + "WHERE kingdom = ? "
55 + "AND island = ? ";
56 private static final String SELECT_KINGDOM_BY_ID = "SELECT id, last_update, kingdom, island, name, relation, stance, "
57 + "war_count, war_win, war_nwdiff "
58 + "FROM kingdom "
59 + "WHERE id = ? ";
60 private static final String INSERT_KINGDOM = "INSERT INTO kingdom(id, last_update, kingdom, island, name, relation, "
61 + "stance, war_count, war_win, war_nwdiff) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
62 private static final String UPDATE_KINGDOM = "UPDATE kingdom SET last_update=?, name=?, relation=?, stance=?, "
63 + "war_count=?, war_win=?, war_nwdiff=? "
64 + "WHERE id=? "
65 + "AND last_update < ?";
66
67 private static final String SELECT_PROVINCE_BY_NAME = "SELECT id, kingdom_id, last_update, name, acres, gender, leader, "
68 + "networth, race, rank, personality, peasants, gold, food, runes, trade, thieves, wizards, "
69 + "soldiers, offspecs, defspecs, elites, horses, prisoners, offense, defense "
70 + "FROM province "
71 + "WHERE name=? ";
72 private static final String INSERT_PROVINCE = "INSERT INTO province(id, kingdom_id, last_update, name, acres, "
73 + "gender, leader, networth, race, rank, personality, peasants, gold, food, runes, trade, thieves, wizards, "
74 + "soldiers, offspecs, defspecs, elites, horses, prisoners, offense, defense) "
75 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
76 private static final String UPDATE_PROVINCE = "UPDATE province "
77 + "SET kingdom_id=?, last_update=?, name=?, acres=?, "
78 + "gender=?, leader=?, networth=?, race=?, rank=?, personality=?, peasants=?, gold=?, food=?, runes=?, "
79 + "trade=?, thieves=?, wizards=?, soldiers=?, offspecs=?, defspecs=?, elites=?, horses=?, prisoners=?, "
80 + "offense=?, defense=? "
81 + "WHERE id=? "
82 + "AND last_update < ?";
83
84 private static final String SELECT_SCIENCE = "SELECT id, province_id, last_update, alchemy, tools, housing, food, "
85 + " military, crime, channeling "
86 + "FROM science "
87 + "WHERE province_id=? ";
88 private static final String INSERT_SCIENCE = "INSERT INTO science(id, province_id, last_update, alchemy, tools, "
89 + " housing, food, military, crime, channeling) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
90 private static final String UPDATE_SCIENCE = "UPDATE science SET last_update=?, alchemy=?, tools=?, housing=?, "
91 + "food=?, military=?, crime=?, channeling=? "
92 + "WHERE id=? "
93 + "AND last_update < ? ";
94
95 private static final String SELECT_SURVEY = "SELECT id, province_id, last_update, efficiency, barren, homes, farms, "
96 + "mills, banks, training_grounds, barracks, armories, forts, guard_stations, hospitals, guilds, towers, "
97 + "thief_dens, watchtowers, libraries, schools, stables, dungeons "
98 + "FROM survey "
99 + "WHERE province_id=?";
100 private static final String INSERT_SURVEY = "INSERT INTO survey(id, province_id, last_update, efficiency, barren, "
101 + "homes, farms, mills, banks, training_grounds, barracks, armories, forts, guard_stations, hospitals, guilds, "
102 + "towers, thief_dens, watchtowers, libraries, schools, stables, dungeons) "
103 + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
104 private static final String UPDATE_SURVEY = "UPDATE survey SET last_update=?, efficiency=?, barren=?, homes=?, "
105 + "farms=?, mills=?, banks=?, training_grounds=?, barracks=?, armories=?, forts=?, guard_stations=?, "
106 + "hospitals=?, guilds=?, towers=?, thief_dens=?, watchtowers=?, libraries=?, schools=?, stables=?, dungeons=? "
107 + "WHERE id=? "
108 + "AND last_update < ? ";
109
110 private static final String SELECT_MILITARY = "SELECT id, province_id, last_update, offense, defense, raw_ind "
111 + "FROM military "
112 + "WHERE province_id=? ";
113 private static final String INSERT_MILITARY = "INSERT INTO military(id, province_id, last_update, offense, defense, "
114 + "raw_ind) VALUES(?, ?, ?, ?, ?, ?) ";
115 private static final String UPDATE_MILITARY = "UPDATE military SET last_update=?, offense=?, defense=?, raw_ind=? "
116 + "WHERE id=? "
117 + "AND last_update < ? ";
118 private static final String SELECT_ARMY = "SELECT id, military_id, generals, soldiers, offspecs, defspecs, elites, "
119 + "horses, spoils, eta "
120 + "FROM army "
121 + "WHERE military_id=? ";
122 private static final String INSERT_ARMY = "INSERT INTO army(id, military_id, generals, soldiers, offspecs, "
123 + "defspecs, elites, horses, spoils, eta) "
124 + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
125 private static final String DELETE_ARMIES = "DELETE FROM army WHERE military_id=? ";
126
127 @Autowired
128 public DataSyncTemplate(DataSource ds) {
129 super(ds);
130 }
131
132 public Kingdom getKingdom(int kingdom, int island) {
133 try {
134 return queryForObject(SELECT_KINGDOM, kingdomMapper, kingdom, island);
135 } catch (EmptyResultDataAccessException e) {
136 Kingdom k = new Kingdom(new Coordinate(kingdom, island));
137 Calendar update = CalendarUtils.getCalendar();
138 update.setTimeInMillis(0);
139 k.setLastUpdate(update);
140 return k;
141 }
142 }
143
144 public Province getProvinceByName(String name) {
145 try {
146 return queryForObject(SELECT_PROVINCE_BY_NAME, provinceMapper, name);
147 } catch (EmptyResultDataAccessException e) {
148 Province province = new Province();
149 province.setName(name);
150 province.getLastUpdate().setTimeInMillis(0);
151 return province;
152 }
153 }
154
155 public Kingdom getKingdomById(String id) {
156 return queryForObject(SELECT_KINGDOM_BY_ID, kingdomMapper, id);
157 }
158
159 public List<Kingdom> getKingdomSyncList(Calendar lastUpdate) {
160 return query(SELECT_SYNC_KINGDOM, kingdomMapper, lastUpdate);
161 }
162
163 public List<Province> getProvinceSyncList(Calendar lastUpdate) {
164 return query(SELECT_SYNC_PROVINCE, provinceMapper, lastUpdate, lastUpdate, lastUpdate, lastUpdate);
165 }
166
167 public boolean updateKingdom(Kingdom kingdom) {
168 int updated = 0;
169 try {
170 queryForObject(EXISTS_KINGDOM, String.class, kingdom.getId());
171 updated = update(UPDATE_KINGDOM, kingdom.getLastUpdate(), kingdom.getName(), kingdom.getRelation().toString(),
172 kingdom.getStance().toString(), kingdom.getWarCount(), kingdom.getWarWins(),
173 kingdom.getWarNetworthDiff(), kingdom.getId(), kingdom.getLastUpdate());
174 } catch (EmptyResultDataAccessException e) {
175 updated = update(INSERT_KINGDOM, kingdom.getId(), kingdom.getLastUpdate(),
176 kingdom.getLocation().getKingdom(), kingdom.getLocation().getIsland(), kingdom.getName(),
177 kingdom.getRelation().toString(), kingdom.getStance().toString(), kingdom.getWarCount(),
178 kingdom.getWarWins(), kingdom.getWarNetworthDiff());
179 }
180 return updated > 0;
181 }
182
183 public boolean updateProvince(Province province) {
184 int updated = 0;
185 Coordinate coord = province.getCoordinate();
186 Calendar update = province.getLastUpdate();
187 Kingdom kingdom = getKingdom(coord.getKingdom(), coord.getIsland());
188 if (kingdom.getLastUpdate().getTimeInMillis() == 0) {
189 kingdom.setLastUpdate(province.getLastUpdate());
190 updateKingdom(kingdom);
191 }
192 try {
193 queryForObject(EXISTS_PROVINCE, String.class, province.getId());
194 updated = update(UPDATE_PROVINCE, kingdom.getId(), update, province.getName(), province.getAcres(),
195 province.isGender(), province.getLeader(), province.getNetworth(), province.getRace().toString(),
196 province.getRank().toString(), province.getPersonality().toString(), province.getPeasants(),
197 province.getGold(), province.getFood(), province.getRunes(), province.getTradeBalance(),
198 province.getThieves(), province.getWizards(), province.getSoldiers(), province.getOffspecs(),
199 province.getDefspecs(), province.getElites(), province.getHorses(), province.getPrisoners(),
200 province.getOffense(), province.getDefense(), province.getId(), update);
201 } catch (EmptyResultDataAccessException e) {
202 updated = update(INSERT_PROVINCE, province.getId(), kingdom.getId(), update, province.getName(),
203 province.getAcres(), province.isGender(), province.getLeader(), province.getNetworth(),
204 province.getRace().toString(), province.getRank().toString(), province.getPersonality().toString(),
205 province.getPeasants(), province.getGold(), province.getFood(), province.getRunes(),
206 province.getTradeBalance(), province.getThieves(), province.getWizards(), province.getSoldiers(),
207 province.getOffspecs(), province.getDefspecs(), province.getElites(), province.getHorses(),
208 province.getPrisoners(), province.getOffense(), province.getDefense());
209 }
210 return updated > 0;
211 }
212
213 public boolean updateMilitary(Military military) {
214 int updated = 0;
215 String provinceId = military.getProvince().getId();
216 Calendar update = military.getLastUpdate();
217 try {
218 queryForObject(EXISTS_MILITARY, String.class, military.getId());
219 updated = update(UPDATE_MILITARY, update, military.getOffense(), military.getDefense(), military.isRaw(),
220 military.getId(), update);
221 } catch (EmptyResultDataAccessException e) {
222 updated = update(INSERT_MILITARY, military.getId(), provinceId, update, military.getOffense(),
223 military.getDefense(), military.isRaw());
224 }
225 update(DELETE_ARMIES, military.getId());
226 for (Army army : military.getArmies()) {
227 update(INSERT_ARMY, army.getId(), military.getId(), army.getGenerals(), army.getSoldiers(),
228 army.getOffspecs(), army.getDefspecs(), army.getElites(), army.getHorses(), army.getSpoils(),
229 army.getReturnTime());
230 }
231 return updated > 0;
232 }
233
234 public boolean updateScience(Science science) {
235 int updated = 0;
236 String provinceId = science.getProvince().getId();
237 Calendar update = science.getLastUpdate();
238 try {
239 queryForObject(EXISTS_SCIENCE, String.class, science.getId());
240 updated = update(UPDATE_SCIENCE, update, science.getAlchemy(), science.getTools(), science.getHousing(),
241 science.getFood(), science.getMilitary(), science.getCrime(), science.getChanneling(),
242 science.getId(), update);
243 } catch (EmptyResultDataAccessException e) {
244 updated = update(INSERT_SCIENCE, science.getId(), provinceId, update, science.getAlchemy(),
245 science.getTools(), science.getHousing(), science.getFood(), science.getMilitary(),
246 science.getCrime(), science.getChanneling());
247 }
248 return updated > 0;
249 }
250
251 public boolean updateSurvey(Survey survey) {
252 int updated = 0;
253 String provinceId = survey.getProvince().getId();
254 Calendar update = survey.getLastUpdate();
255 try {
256 queryForObject(EXISTS_SURVEY, String.class, survey.getId());
257 updated = update(UPDATE_SURVEY, update, survey.getEfficiency(), survey.getBarren(), survey.getHomes(),
258 survey.getFarms(), survey.getMills(), survey.getBanks(), survey.getTrainingGrounds(),
259 survey.getBarracks(), survey.getArmories(), survey.getForts(), survey.getGuardStations(),
260 survey.getHospitals(), survey.getGuilds(), survey.getTowers(), survey.getThievesDens(),
261 survey.getWatchtowers(), survey.getLibraries(), survey.getSchools(), survey.getStables(),
262 survey.getDungeons(), survey.getId(), update);
263 } catch (EmptyResultDataAccessException e) {
264 updated = update(INSERT_SURVEY, survey.getId(), provinceId, update, survey.getEfficiency(),
265 survey.getBarren(), survey.getHomes(), survey.getFarms(), survey.getMills(), survey.getBanks(),
266 survey.getTrainingGrounds(), survey.getBarracks(), survey.getArmories(), survey.getForts(),
267 survey.getGuardStations(), survey.getHospitals(), survey.getGuilds(), survey.getTowers(),
268 survey.getThievesDens(), survey.getWatchtowers(), survey.getLibraries(), survey.getSchools(),
269 survey.getStables(), survey.getDungeons());
270 }
271 return updated > 0;
272 }
273
274 private Calendar getCalendar(Timestamp ts) {
275 Calendar time = CalendarUtils.getCalendar();
276 time.setTimeInMillis(ts.getTime());
277 return time;
278 }
279
280 private ParameterizedRowMapper<Kingdom> kingdomMapper = new ParameterizedRowMapper<Kingdom>() {
281 public Kingdom mapRow(ResultSet rs, int rowNum) throws SQLException {
282 Coordinate coord = new Coordinate();
283 coord.setKingdom(rs.getInt("kingdom"));
284 coord.setIsland(rs.getInt("island"));
285 Kingdom kingdom = new Kingdom(coord);
286 kingdom.setId(rs.getString("id"));
287 kingdom.setLastUpdate(getCalendar(rs.getTimestamp("last_update")));
288 kingdom.setName(rs.getString("name"));
289 kingdom.setRelation(Relation.valueOf(rs.getString("relation")));
290 kingdom.setStance(Stance.valueOf(rs.getString("stance")));
291 kingdom.setWarCount(rs.getInt("war_count"));
292 kingdom.setWarWins(rs.getInt("war_win"));
293 kingdom.setWarNetworthDiff(rs.getInt("war_nwdiff"));
294 return kingdom;
295 }
296 };
297
298 private ParameterizedRowMapper<Province> provinceMapper = new ParameterizedRowMapper<Province>() {
299 public Province mapRow(ResultSet rs, int rowNum) throws SQLException {
300 Kingdom kingdom = getKingdomById(rs.getString("kingdom_id"));
301 Province province = new Province(rs.getString("name"), kingdom.getLocation());
302 province.setId(rs.getString("id"));
303 province.setKingdomId(rs.getString("kingdom_id"));
304 Timestamp timestamp = rs.getTimestamp("last_update");
305 province.getLastUpdate().setTimeInMillis(timestamp.getTime());
306 province.setAcres(rs.getInt("acres"));
307 province.setGender(rs.getBoolean("gender"));
308 province.setName(rs.getString("name"));
309 province.setLeader(rs.getString("leader"));
310 province.setNetworth(rs.getInt("networth"));
311 province.setRace(Race.valueOf(rs.getString("race")));
312 province.setRank(Rank.valueOf(rs.getString("rank")));
313 province.setPersonality(Personality.valueOf(rs.getString("personality")));
314 province.setPeasants(rs.getInt("peasants"));
315 province.setGold(rs.getInt("gold"));
316 province.setFood(rs.getInt("food"));
317 province.setRunes(rs.getInt("runes"));
318 province.setTradeBalance(rs.getInt("trade"));
319 province.setThieves(rs.getInt("thieves"));
320 province.setWizards(rs.getInt("wizards"));
321 province.setSoldiers(rs.getInt("soldiers"));
322 province.setOffspecs(rs.getInt("offspecs"));
323 province.setDefspecs(rs.getInt("defspecs"));
324 province.setElites(rs.getInt("elites"));
325 province.setHorses(rs.getInt("horses"));
326 province.setPrisoners(rs.getInt("prisoners"));
327 province.setOffense(rs.getInt("offense"));
328 province.setDefense(rs.getInt("defense"));
329
330 try {
331 Science science = queryForObject(SELECT_SCIENCE, scienceMapper, province.getId());
332 science.setProvince(province);
333 province.setScience(science);
334 } catch (EmptyResultDataAccessException ignore) {
335 province.getScience().getLastUpdate().setTimeInMillis(0);
336 }
337
338 try {
339 Survey survey = queryForObject(SELECT_SURVEY, surveyMapper, province.getId());
340 survey.setProvince(province);
341 province.setSurvey(survey);
342 } catch (EmptyResultDataAccessException ignore) {
343 province.getSurvey().getLastUpdate().setTimeInMillis(0);
344 }
345
346 try {
347 Military military = queryForObject(SELECT_MILITARY, militaryMapper, province.getId());
348 military.setProvince(province);
349 province.setMilitary(military);
350 } catch (EmptyResultDataAccessException ignore) {
351 province.getMilitary().getLastUpdate().setTimeInMillis(0);
352 }
353
354 return province;
355 }
356 };
357
358 private ParameterizedRowMapper<Army> armyMapper = new ParameterizedRowMapper<Army>() {
359 public Army mapRow(ResultSet rs, int rowNum) throws SQLException {
360 Army army = new Army();
361 army.setId(rs.getString("id"));
362 army.setGenerals(rs.getInt("generals"));
363 army.getReturnTime().setTimeInMillis(rs.getTimestamp("eta").getTime());
364 army.setSoldiers(rs.getInt("soldiers"));
365 army.setOffspecs(rs.getInt("offspecs"));
366 army.setDefspecs(rs.getInt("defspecs"));
367 army.setElites(rs.getInt("elites"));
368 army.setHorses(rs.getInt("horses"));
369 army.setSpoils(rs.getInt("spoils"));
370 return army;
371 }
372 };
373
374 private ParameterizedRowMapper<Military> militaryMapper = new ParameterizedRowMapper<Military>() {
375 public Military mapRow(ResultSet rs, int rowNum) throws SQLException {
376 Military military = new Military();
377 military.setId(rs.getString("id"));
378 military.getLastUpdate().setTimeInMillis(rs.getTimestamp("last_update").getTime());
379 military.setOffense(rs.getInt("offense"));
380 military.setDefense(rs.getInt("defense"));
381 military.setRaw(rs.getBoolean("raw_ind"));
382 military.setArmies(query(SELECT_ARMY, armyMapper, military.getId()));
383 for (Army army : military.getArmies()) {
384 army.setMilitary(military);
385 }
386 return military;
387 }
388 };
389
390 private ParameterizedRowMapper<Science> scienceMapper = new ParameterizedRowMapper<Science>() {
391 public Science mapRow(ResultSet rs, int rowNum) throws SQLException {
392 Science science = new Science();
393 science.setId(rs.getString("id"));
394 science.getLastUpdate().setTimeInMillis(rs.getTimestamp("last_update").getTime());
395 science.setAlchemy(rs.getInt("alchemy"));
396 science.setTools(rs.getInt("tools"));
397 science.setHousing(rs.getInt("housing"));
398 science.setFood(rs.getInt("food"));
399 science.setMilitary(rs.getInt("military"));
400 science.setCrime(rs.getInt("crime"));
401 science.setChanneling(rs.getInt("channeling"));
402 return science;
403 }
404 };
405
406 private ParameterizedRowMapper<Survey> surveyMapper = new ParameterizedRowMapper<Survey>() {
407 public Survey mapRow(ResultSet rs, int rowNum) throws SQLException {
408 Survey survey = new Survey();
409 survey.setId(rs.getString("id"));
410 survey.getLastUpdate().setTimeInMillis(rs.getTimestamp("last_update").getTime());
411 survey.setEfficiency(rs.getFloat("efficiency"));
412 survey.setBarren(rs.getInt("barren"));
413 survey.setHomes(rs.getInt("homes"));
414 survey.setFarms(rs.getInt("farms"));
415 survey.setMills(rs.getInt("mills"));
416 survey.setBanks(rs.getInt("banks"));
417 survey.setTrainingGrounds(rs.getInt("training_grounds"));
418 survey.setBarracks(rs.getInt("barracks"));
419 survey.setArmories(rs.getInt("armories"));
420 survey.setForts(rs.getInt("forts"));
421 survey.setGuardStations(rs.getInt("guard_stations"));
422 survey.setHospitals(rs.getInt("hospitals"));
423 survey.setGuilds(rs.getInt("guilds"));
424 survey.setTowers(rs.getInt("towers"));
425 survey.setThievesDens(rs.getInt("thief_dens"));
426 survey.setWatchtowers(rs.getInt("watchtowers"));
427 survey.setLibraries(rs.getInt("libraries"));
428 survey.setSchools(rs.getInt("schools"));
429 survey.setStables(rs.getInt("stables"));
430 survey.setDungeons(rs.getInt("dungeons"));
431 return survey;
432 }
433 };
434 }