/**
* ShowCaseStandalone
* Copyright (C) 2012 Kellerkindt <copyright at kellerkindt.com>
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package com.kellerkindt.scs.storage;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import org.bukkit.World;
import com.kellerkindt.scs.ShowCaseStandalone;
import com.kellerkindt.scs.interfaces.ShopHandler;
import com.kellerkindt.scs.interfaces.StorageHandler;
import com.kellerkindt.scs.interfaces.WorldProvider;
import com.kellerkindt.scs.internals.Storage;
import com.kellerkindt.scs.shops.Shop;
import com.kellerkindt.scs.utilities.Properties;
/**
* @author Sorklin <sorklin at gmail.com>
*/
@Deprecated // as long as lists cannot be saved, not updated
public class SQLShopStorage implements StorageHandler {
private static final String SQL_TABLE_NAME_INDEX = "scs_index";
private static final String SQL_TABLE_NAME_STORAGES = "scs_storages";
private static final String SQL_COLUMN_USID = "usid";
private static final String SQL_COLUMN_TYPE = "type";
private static final String SQL_COLUMN_NAME = "name";
private static final String SQL_COLUMN_VALUE = "value";
private static final String SQL_COLUMN_VERSION = "version";
private static final String TYPE_STRING = "string";
private static final String TYPE_DOUBLE = "double";
private static final String TYPE_INTEGER = "integer";
private static final String TYPE_BOOLEAN = "boolean";
private static final String TYPE_STORAGE = "storage";
private static final String SQL_SELECT_INDEX = "SELECT * FROM "+SQL_TABLE_NAME_INDEX;
// private static final String SQL_SELECT_STORAGES = "SELECT * FROM "+SQL_TABLE_NAME_STORAGES;
private static final String SQL_SELECT_STORAGES_WHERE = "SELECT * FROM "+SQL_TABLE_NAME_STORAGES+ " WHERE %1 = ?";
private static final String SQL_DELETE_WHERE = "DELETE FROM %1 WHERE %2 = ?";
private static final String SQL_INSERT_INDEX = "INSERT INTO "+SQL_TABLE_NAME_INDEX +" ("+SQL_COLUMN_USID+", "+SQL_COLUMN_VERSION+") VALUES (?, ?)";
private static final String SQL_INSERT_STORAGE = "INSERT INTO "+SQL_TABLE_NAME_STORAGES+" ("+SQL_COLUMN_USID+", "+SQL_COLUMN_TYPE+", "+SQL_COLUMN_NAME+", "+SQL_COLUMN_VALUE+") VALUES (?, ?, ?, ?)";
private static final String SQL_CREATE_INDEX = "CREATE TABLE IF NOT EXISTS "+SQL_TABLE_NAME_INDEX +" ( id integer auto_increment, "+SQL_COLUMN_USID+" TEXT, "+SQL_COLUMN_VERSION+" integer, PRIMARY KEY (id))";
private static final String SQL_CREATE_STORAGES = "CREATE TABLE IF NOT EXISTS "+SQL_TABLE_NAME_STORAGES +" ( id integer auto_increment, "+SQL_COLUMN_USID+" TEXT, "+SQL_COLUMN_TYPE+" TEXT, "+SQL_COLUMN_NAME+" TEXT, "+SQL_COLUMN_VALUE+" TEXT, PRIMARY KEY (id))";
private static final String SPLIT_STORAGE_VERSION_BY = ":";
private static final int VALID_TIMEOUT = 100;
private HashMap<String, Storage> storages = new HashMap<String, Storage>();
private List<UUID> changed = new ArrayList<UUID>();
private ShowCaseStandalone scs = null;
private Connection connection = null;
private String url = null;
private String username = null;
private String password = null;
public SQLShopStorage (ShowCaseStandalone scs, String url, String username, String password) throws SQLException {
this.scs = scs;
this.url = url;
this.username = username;
this.password = password;
connect();
}
/**
* Connects to the SQLServer
* @throws SQLException
*/
private void connect () throws SQLException {
// ServerConfig serverConfig = new ServerConfig();
// scs.getServer().configureDbConfig(serverConfig);
//
// String url = serverConfig.getDataSourceConfig().getUrl();
// String username = serverConfig.getDataSourceConfig().getUsername();
// String password = serverConfig.getDataSourceConfig().getPassword();
connection = DriverManager.getConnection(url, username, password);
}
private void checkConnection () throws IOException {
try {
if (connection == null || connection.isClosed() || !connection.isValid(VALID_TIMEOUT))
connect();
if (connection == null || connection.isClosed())
throw new IOException("Can not connect");
} catch (SQLException sqle) {
throw new IOException(sqle);
}
}
public void load() throws IOException {
checkConnection();
try {
Statement statement = connection.createStatement();
statement.execute(SQL_CREATE_INDEX );
statement.execute(SQL_CREATE_STORAGES );
ResultSet result = statement.executeQuery(SQL_SELECT_INDEX);
HashMap<UUID, Integer> uuids = new HashMap<UUID, Integer>();
// load uuids
while (result.next()) {
int version = result.getInt(SQL_COLUMN_VERSION);
String string = result.getString(SQL_COLUMN_USID);
/*
* Since version 6 the sha1 key
* is replaced by a UUID
*/
if (version >= 6) {
uuids.put(UUID.fromString(string), version);
} else {
uuids.put(UUID.randomUUID(), version);
}
}
// load storages
for (UUID uuid : uuids.keySet()) {
Storage storage = loadStorage(uuid, uuids.get(uuid));
if (storage != null) {
storage = update(storage, storage.getVersion(), Properties.storageVersion);
addRaw(storage);
} else {
changed.add(uuid); // so its deleted the next time
}
}
} catch (SQLException sqle) {
throw new IOException(sqle);
}
}
private Storage loadStorage (UUID uuid, int version) {
try {
PreparedStatement statement = connection.prepareStatement(getPreparedQuery(SQL_SELECT_STORAGES_WHERE, SQL_COLUMN_USID));
statement.setString(1, uuid.toString());
ResultSet result = statement.executeQuery();
HashMap<String, String> storages = new HashMap<String, String>(); // variable name, id:version
Storage storage = new Storage(version, uuid);
while (result.next()) {
String type = result.getString(SQL_COLUMN_TYPE);
String name = result.getString(SQL_COLUMN_NAME);
String value = result.getString(SQL_COLUMN_VALUE);
if (TYPE_STRING.equalsIgnoreCase(type)) {
// if (value != null && !value.equals("\0x00"))
// storage.setString(name, value.replaceAll("\0x0|\000|\0x3|\003|0x8|\008", " "));
storage.setString(name, value);
}
else if (TYPE_DOUBLE.equalsIgnoreCase(type))
storage.setDouble(name, Double.parseDouble(value));
else if (TYPE_INTEGER.equalsIgnoreCase(type))
storage.setInteger(name, Integer.parseInt(value));
else if (TYPE_BOOLEAN.equalsIgnoreCase(type))
storage.setBoolean(name, Boolean.parseBoolean(value));
else if (TYPE_STORAGE.equalsIgnoreCase(type))
storages.put(name, value);
}
for (String name : storages.keySet()) {
String s = storages.get(name);
String id = s.split(SPLIT_STORAGE_VERSION_BY)[0];
int ver = Integer.parseInt( s.split(SPLIT_STORAGE_VERSION_BY)[1] );
UUID uu = null;
/*
* Since version 6 the sha1 key
* is replaced by a UUID
*/
if (ver >= 6) {
uu = UUID.fromString(id);
} else {
uu = UUID.randomUUID();
}
storage.setStorage(name, loadStorage(uu, ver));
}
storage.resetHasChanged();
return storage;
} catch (Exception e) {
scs.log(Level.WARNING, "Couldn't load storage with usid="+uuid, false);
e.printStackTrace();
}
return null;
}
/**
* Creates a query which was specially prepared
* @param where
* @param value
* @return
*/
private String getPreparedQuery (String query, String ... values) {
for (int i = 0; i < values.length; i++)
query = query.replace("%"+(i+1), values[i]);
return query;
}
public Storage get(String usid) {
return storages.get(usid);
}
public void add(Collection<Storage> storages) {
for (Storage s : storages)
add(s);
}
public void add(Storage storage) {
if (!storages.containsValue(storage))
changed.add(storage.getUUID());
addRaw(storage);
}
public void addRaw (Storage storage) {
storages.put(storage.getUUIDAsString(), storage);
}
public boolean remove(Storage storage) {
return remove(storage.getUUID());
}
public boolean remove(UUID uuid) {
changed.add(uuid);
return (storages.remove(uuid) != null);
}
public void clear() {
storages.clear();
changed.clear();
}
public Storage update(Storage storage, int oldVersion, int newVersion) throws IOException {
switch (oldVersion) {
}
storage.setVersion(newVersion);
return storage;
}
public int size() {
return storages.size();
}
private void saveStorage (Storage storage, boolean setIndex) throws SQLException {
PreparedStatement statement = null;
// set up index
if (setIndex) {
statement = connection.prepareStatement(SQL_INSERT_INDEX);
statement.setString (1, storage.getUUIDAsString());
statement.setInt (2, storage.getVersion());
statement.execute();
}
// default statement for next few requests
statement = connection.prepareStatement(SQL_INSERT_STORAGE);
statement.setString(1, storage.getUUIDAsString());
// strings
for (String key : storage.getStringKeys()) {
statement.setString(2, TYPE_STRING);
statement.setString(3, key);
statement.setString(4, storage.getString(key));
statement.execute();
}
// doubles
for (String key : storage.getDoubleKeys()) {
statement.setString(2, TYPE_DOUBLE);
statement.setString(3, key);
statement.setString(4, ""+storage.getDouble(key));
statement.execute();
}
// integers
for (String key : storage.getIntegerKeys()) {
statement.setString(2, TYPE_INTEGER);
statement.setString(3, key);
statement.setString(4, ""+storage.getInteger(key));
statement.execute();
}
// booleans
for (String key : storage.getBooleanKeys()) {
statement.setString(2, TYPE_BOOLEAN);
statement.setString(3, key);
statement.setString(4, ""+storage.getBoolean(key));
statement.execute();
}
// save storage-ids
for (String key : storage.getStorageKeys()) {
Storage st = storage.getStorage(key);
statement.setString(2, TYPE_STORAGE);
statement.setString(3, key);
statement.setString(4, st.getUUID()+SPLIT_STORAGE_VERSION_BY+st.getVersion());
statement.execute();
}
// save storages
for (String key : storage.getStorageKeys())
saveStorage(storage.getStorage(key), false);
}
private void deleteStorage (UUID uuid) throws SQLException {
PreparedStatement statement = null;
statement = connection.prepareStatement(getPreparedQuery(SQL_DELETE_WHERE, SQL_TABLE_NAME_INDEX, SQL_COLUMN_USID));
statement.setString(1, uuid.toString());
statement.execute();
statement = connection.prepareStatement(getPreparedQuery(SQL_DELETE_WHERE, SQL_TABLE_NAME_STORAGES, SQL_COLUMN_USID));
statement.setString(1, uuid.toString());
statement.execute();
}
@Override
public void flush() throws IOException {
List<UUID> successfull = new ArrayList<UUID>();
for (Storage s : storages.values())
if (s.hasChanged())
changed.add(s.getUUID());
// is it closed?
checkConnection();
for (UUID uuid : changed) {
try {
// delete, for re-create
deleteStorage(uuid);
// removed? --> no re-creation
if (!storages.containsKey(uuid))
continue;
Storage storage = storages.get(uuid);
saveStorage(storage, true);
storage.resetHasChanged();
successfull.add(uuid);
} catch (Exception e) {
scs.log(Level.INFO, "Couldn't save "+uuid+", trying again later", false);
e.printStackTrace();
}
}
for (UUID uuid : successfull)
changed.remove(uuid);
}
/**
* @see com.kellerkindt.scs.interfaces.StorageHandler#load(com.kellerkindt.scs.interfaces.ShopHandler)
*/
@Override
public void load(ShopHandler handler) throws IOException {
// the loaded shops
List<Shop> shops = new ArrayList<Shop>();
for (Storage storage : this.storages.values()) {
try {
shops.add(ShopImporter.loadShop(storage, new WorldProvider() {
@Override
public World getWorld(String name) {
return scs.getServer().getWorld(name);
}
@Override
public World getWorld(UUID uuid) {
return scs.getServer().getWorld(uuid);
}
}, scs.getServer().getVersion()));
} catch (Exception e) {
// dirty...
e.printStackTrace();
}
}
// add them all
handler.addAll(shops, true);
}
/**
* @see com.kellerkindt.scs.interfaces.StorageHandler#save(com.kellerkindt.scs.interfaces.ShopHandler)
*/
@Override
public void save(ShopHandler handler) throws IOException {
// NOT SUPPORTED
throw new UnsupportedOperationException();
}
}