summaryrefslogtreecommitdiffstats
path: root/EssentialsProtect/src/net/ess3/protect/data/ProtectedBlockSQLite.java
blob: 65dbac213f1aeafd4d0e5dac6fb3affad19ffc05 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
package net.ess3.protect.data;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class ProtectedBlockSQLite extends ProtectedBlockJDBC
{
	public ProtectedBlockSQLite(final String url) throws PropertyVetoException
	{
		super("org.sqlite.JDBC", url);
	}
	private static final String QueryCreateTable =
								"CREATE TABLE IF NOT EXISTS EssentialsProtect ("
								+ "worldName TEXT ,playerName TEXT, "
								+ "x NUMERIC, y NUMERIC, z NUMERIC)";

	@Override
	protected PreparedStatement getStatementCreateTable(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryCreateTable);
	}
	private static final String QueryUpdateFrom2_0Table =
								"CREATE INDEX IF NOT EXISTS position ON EssentialsProtect ("
								+ "worldName, x, z, y)";

	@Override
	protected PreparedStatement getStatementUpdateFrom2_0Table(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryUpdateFrom2_0Table);
	}
	private static final String QueryDeleteAll = "DELETE FROM EssentialsProtect;";

	@Override
	protected PreparedStatement getStatementDeleteAll(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryDeleteAll);
	}
	private static final String QueryInsert =
								"INSERT INTO EssentialsProtect (worldName, x, y, z, playerName) VALUES (?, ?, ?, ?, ?);";

	@Override
	protected PreparedStatement getStatementInsert(final Connection conn, final String world,
												   final int x, final int y, final int z, final String playerName) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryInsert);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		ps.setString(5, playerName);
		return ps;
	}
	private static final String QueryPlayerCountByLocation =
								"SELECT COUNT(playerName), SUM(playerName = ?) FROM EssentialsProtect "
								+ "WHERE worldName = ? AND x = ? AND y = ? AND z = ? GROUP BY x;";

	@Override
	protected PreparedStatement getStatementPlayerCountByLocation(final Connection conn, final String world,
																  final int x, final int y, final int z, final String playerName) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryPlayerCountByLocation);
		ps.setString(1, playerName);
		ps.setString(2, world);
		ps.setInt(3, x);
		ps.setInt(4, y);
		ps.setInt(5, z);
		return ps;
	}
	private static final String QueryPlayersByLocation =
								"SELECT playerName FROM EssentialsProtect WHERE worldname = ? AND x = ? AND y = ? AND z = ?;";

	@Override
	protected PreparedStatement getStatementPlayersByLocation(final Connection conn, final String world,
															  final int x, final int y, final int z) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryPlayersByLocation);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		return ps;
	}
	private static final String QueryDeleteByLocation =
								"DELETE FROM EssentialsProtect WHERE worldName = ? AND x = ? AND y = ? AND z = ?;";

	@Override
	protected PreparedStatement getStatementDeleteByLocation(final Connection conn, final String world,
															 final int x, final int y, final int z) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryDeleteByLocation);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		return ps;
	}
	private static final String QueryAllBlocks =
								"SELECT worldName, x, y, z, playerName FROM EssentialsProtect;";

	@Override
	protected PreparedStatement getStatementAllBlocks(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryAllBlocks);
	}
}