public function __construct(MyPlot $plugin, $cacheSize = 0)
{
parent::__construct($plugin, $cacheSize);
$this->db = new SQLite3($this->plugin->getDataFolder() . "plots.db");
$this->db->exec("CREATE TABLE IF NOT EXISTS plots\n (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT, X INTEGER, Z INTEGER, name TEXT,\n owner TEXT, helpers TEXT, biome TEXT)");
$this->sqlGetPlot = $this->db->prepare("SELECT id, name, owner, helpers, biome FROM plots WHERE level = :level AND X = :X AND Z = :Z");
$this->sqlSavePlot = $this->db->prepare("INSERT OR REPLACE INTO plots (id, level, X, Z, name, owner, helpers, biome) VALUES\n ((select id from plots where level = :level AND X = :X AND Z = :Z),\n :level, :X, :Z, :name, :owner, :helpers, :biome);");
$this->sqlSavePlotById = $this->db->prepare("UPDATE plots SET name = :name, owner = :owner, helpers = :helpers, biome = :biome WHERE id = :id");
$this->sqlRemovePlot = $this->db->prepare("DELETE FROM plots WHERE level = :level AND X = :X AND Z = :Z");
$this->sqlRemovePlotById = $this->db->prepare("DELETE FROM plots WHERE id = :id");
$this->sqlGetPlotsByOwner = $this->db->prepare("SELECT * FROM plots WHERE owner = :owner");
$this->sqlGetPlotsByOwnerAndLevel = $this->db->prepare("SELECT * FROM plots WHERE owner = :owner AND level = :level");
$this->sqlGetExistingXZ = $this->db->prepare("SELECT X, Z FROM plots WHERE (\n level = :level\n AND (\n (abs(X) == :number AND abs(Z) <= :number) OR\n (abs(Z) == :number AND abs(X) <= :number)\n )\n )");
}
public function __construct(MyPlot $plugin, $cacheSize = 0, $cacheAll = false)
{
parent::__construct($plugin, $cacheSize, $cacheAll);
$mysqlhost = $plugin->getConfig()->get("mysqlhost");
$mysqluser = $plugin->getConfig()->get("mysqluser");
$mysqlpass = $plugin->getConfig()->get("mysqlpass");
$mysqldb = $plugin->getConfig()->get("mysqldb");
$this->db = new \mysqli($mysqlhost, $mysqluser, $mysqlpass, $mysqldb);
$this->db->query("CREATE TABLE IF NOT EXISTS `plots`\n (id INTEGER PRIMARY KEY auto_increment, \n level TEXT, \n X INTEGER, \n Z INTEGER, \n name TEXT, \n owner TEXT, \n helpers TEXT, \n biome TEXT);");
@$this->db->query("CREATE INDEX `XZ` ON `plots` (X,Z);");
// upgrade database without locked column
@$this->db->query("ALTER TABLE `plots` ADD `locked` INTEGER;");
$queryName = "GetPlot";
// sii level, X, Z
$sql = "SELECT id, name, owner, helpers, biome, locked FROM plots WHERE level = ? AND X = ? AND Z = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "GetPlotById";
// i id
$sql = "SELECT id, X, Z, level, name, owner, helpers, biome, locked FROM plots WHERE id = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "GetAllPlots";
$sql = "SELECT id, X, Z, level, name, owner, helpers, biome, locked FROM plots;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "SavePlot";
// siisiissss level, X, Z, level, X, Z, name, owner, helpers, biome
$sql = "\n REPLACE INTO plots (id, level, X, Z, name, owner, helpers, biome, locked) VALUES\n ((select id from plots AS tmp where level = ? AND X = ? AND Z = ?),\n ?, ?, ?, ?, ?, ?, ?, ?);";
$this->checkPreparedStatement($queryName, $sql);
// only to be used when we have a full cache ($this->cacheAll = true _
$queryName = "SaveNewPlot";
// siisiissss level, X, Z, level, X, Z, name, owner, helpers, biome
$sql = "\n INSERT INTO plots (level, X, Z, name, owner, helpers, biome, locked) VALUES\n ( ?, ?, ?, ?, ?, ?, ?, ?);";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "SavePlotById";
// ssssi
$sql = "UPDATE plots SET name = ?, owner = ?, helpers = ?, biome = ?, locked = ? WHERE id = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "RemovePlot";
// sii
$sql = "DELETE FROM plots WHERE level = ? AND X = ? AND Z = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "RemovePlotById";
// i
$sql = "DELETE FROM plots WHERE id = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "getPlotsByOwner";
// s
$sql = "SELECT id, name, owner, helpers, biome, X, Z, level, locked\n FROM plots WHERE owner = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "getPlotsByOwnerAndLevel";
// ss
$sql = "SELECT id, name, owner, helpers, biome, X, Z, level, locked\n FROM plots WHERE owner = ? AND level = ?;";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "GetExistingXZ";
// siiii level, size x 4
$sql = "SELECT X, Z FROM plots WHERE (\n level = ?\n AND (\n (abs(X) = ? AND abs(Z) <= ?) OR\n (abs(Z) = ? AND abs(X) <= ?)\n )\n )";
$this->checkPreparedStatement($queryName, $sql);
$queryName = "GetFreeXZ";
$sql = "\n SELECT X, Z,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z LIMIT 1)) AS `xm1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z LIMIT 1)) AS `xp1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z - 1 LIMIT 1)) AS `zm1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z + 1 LIMIT 1)) AS `zp1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z + 1 LIMIT 1)) AS `xp1zp1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) AS `xm1zm1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) AS `xp1zm1`,\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z + 1 LIMIT 1)) AS `xm1zp1`\n FROM `plots` WHERE\n (\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z - 1 LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z + 1 LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z + 1 LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) OR\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z + 1 LIMIT 1))\n ) \n AND\n (\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z - 1 LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X AND sub1.Z=plots.Z + 1 LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z + 1 LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X + 1 AND sub1.Z=plots.Z - 1 LIMIT 1)) +\n isnull ((SELECT id FROM plots AS sub1 WHERE sub1.X=plots.X - 1 AND sub1.Z=plots.Z + 1 LIMIT 1))\n ) < 8 \n AND level = ? \n AND ( (`plots`.`X` < (? - 1)) OR (`plots`.`X` > (? + 1)) )\n AND ( (`plots`.`Z` < (? - 1)) OR (`plots`.`Z` > (? + 1)) )\n ORDER BY ABS(X)+ABS(Z) ASC\n LIMIT 1;\n ";
$this->checkPreparedStatement($queryName, $sql);
if ($this->cacheAll) {
$this->plugin->getLogger()->info("Preloading ALL plot data");
$this->getAllPlots();
$this->plugin->getLogger()->info("Loaded plots - count is " . count($this->cache));
}
}