kopia lustrzana https://github.com/dl2alf/AirScout
314 wiersze
12 KiB
C#
314 wiersze
12 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Data.SQLite;
|
|
using SQLiteDatabase;
|
|
using System.IO;
|
|
using ScoutBase.Core;
|
|
using ScoutBase.Database;
|
|
using System.Data;
|
|
|
|
namespace ScoutBase.Maps
|
|
{
|
|
public class DefaultDatabaseDirectory
|
|
{
|
|
public new string ToString()
|
|
{
|
|
// create default database directory name
|
|
string dir = Properties.Settings.Default.Database_Directory;
|
|
if (!String.IsNullOrEmpty(dir))
|
|
{
|
|
return dir;
|
|
}
|
|
// empty settings -_> create standard path
|
|
return Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "GMap.NET", "TileDBv5", "en");
|
|
}
|
|
}
|
|
|
|
|
|
public class MapData
|
|
{
|
|
static MapDatabase map = new MapDatabase();
|
|
public static MapDatabase Database
|
|
{
|
|
get
|
|
{
|
|
return map;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// Holds the Map information in a database structure.
|
|
/// </summary>
|
|
public class MapDatabase : ScoutBaseDatabase
|
|
{
|
|
|
|
public string TilesTableName = "Tiles";
|
|
public string TilesDataTableName = "TilesData";
|
|
|
|
public MapDatabase()
|
|
{
|
|
UserVersion = 5;
|
|
Name = "ScoutBase Map Database (GMaps.NET)";
|
|
Description = "The Scoutbase Map Database is containing map tiles and is serviced mainly from GMaps.NET library.\n" +
|
|
"This object provides an interface for direct reading from / writing to database.";
|
|
// add table description manually
|
|
TableDescriptions.Add(TilesTableName, "Holds map tile inoformation.");
|
|
TableDescriptions.Add(TilesDataTableName, "Holds map tile data.");
|
|
db = OpenDatabase("Data.gmdb", DefaultDatabaseDirectory(), Properties.Settings.Default.Database_InMemory);
|
|
// create tables with schemas if not exist
|
|
if (!TilesTableExists())
|
|
TilesCreateTable();
|
|
if (!TilesDataTableExists())
|
|
TilesDataCreateTable();
|
|
}
|
|
|
|
public string DefaultDatabaseDirectory()
|
|
{
|
|
// create default database directory name
|
|
string dir = Properties.Settings.Default.Database_Directory;
|
|
if (!String.IsNullOrEmpty(dir))
|
|
{
|
|
return dir;
|
|
}
|
|
// empty settings -_> create standard path
|
|
return Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "GMap.NET", "TileDBv5", "en");
|
|
}
|
|
|
|
|
|
#region Tiles
|
|
|
|
public bool TilesTableExists(string tablename = "")
|
|
{
|
|
// check for table name is null or empty --> use default tablename from type instead
|
|
string tn = tablename;
|
|
if (String.IsNullOrEmpty(tn))
|
|
tn = TilesTableName;
|
|
return db.TableExists(tn);
|
|
}
|
|
|
|
public bool TilesDataTableExists(string tablename = "")
|
|
{
|
|
// check for table name is null or empty --> use default tablename from type instead
|
|
string tn = tablename;
|
|
if (String.IsNullOrEmpty(tn))
|
|
tn = TilesDataTableName;
|
|
return db.TableExists(tn);
|
|
}
|
|
|
|
public void TilesCreateTable(string tablename = "")
|
|
{
|
|
lock (db.DBCommand)
|
|
{
|
|
// check for table name is null or empty --> use default tablename from type instead
|
|
string tn = tablename;
|
|
if (String.IsNullOrEmpty(tn))
|
|
tn = TilesTableName;
|
|
db.DBCommand.CommandText = "CREATE TABLE Tiles (id INTEGER NOT NULL PRIMARY KEY, X INTEGER NOT NULL, Y INTEGER NOT NULL, Zoom INTEGER NOT NULL, Type UNSIGNED INTEGER NOT NULL, CacheTime DATETIME)";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.Execute(db.DBCommand);
|
|
}
|
|
}
|
|
|
|
public void TilesDataCreateTable(string tablename = "")
|
|
{
|
|
lock (db.DBCommand)
|
|
{
|
|
// check for table name is null or empty --> use default tablename from type instead
|
|
string tn = tablename;
|
|
if (String.IsNullOrEmpty(tn))
|
|
tn = TilesDataTableName;
|
|
db.DBCommand.CommandText = "CREATE TABLE TilesData (id INTEGER NOT NULL PRIMARY KEY CONSTRAINT fk_Tiles_id REFERENCES Tiles(id) ON DELETE CASCADE, Tile BLOB NULL)";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.Execute(db.DBCommand);
|
|
}
|
|
}
|
|
|
|
public long TilesCount()
|
|
{
|
|
long count = (long)db.ExecuteScalar("SELECT COUNT(*) FROM " + TilesTableName);
|
|
if (count <= 0)
|
|
return 0;
|
|
return count;
|
|
}
|
|
|
|
public long TilesDataCount()
|
|
{
|
|
long count = (long)db.ExecuteScalar("SELECT COUNT(*) FROM " + TilesDataTableName);
|
|
if (count <= 0)
|
|
return 0;
|
|
return count;
|
|
}
|
|
|
|
public bool TileExists(int x, int y, int z, int type)
|
|
{
|
|
lock (db.DBCommand)
|
|
{
|
|
db.DBCommand.CommandText = "SELECT EXISTS (SELECT CacheTime FROM " + TilesTableName + " WHERE X = @x AND Y = @y AND Zoom = @z AND Type = @type)";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@x", x));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@y", y));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@z", z));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@type", type));
|
|
long result = (long)db.DBCommand.ExecuteScalar();
|
|
if (result > 0)
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public MapTileInfo TileFind(int id)
|
|
{
|
|
db.DBCommand.CommandText = "SELECT * FROM " + TilesTableName + " WHERE id = @id";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@id", id));
|
|
DataTable dt = db.Select(db.DBCommand);
|
|
if (dt != null)
|
|
{
|
|
DataRow row = dt.Rows[0];
|
|
int _id = System.Convert.ToInt32(row[0]);
|
|
int _x = System.Convert.ToInt32(row[1]);
|
|
int _y = System.Convert.ToInt32(row[2]);
|
|
int _zoom = System.Convert.ToInt32(row[3]);
|
|
int _type = System.Convert.ToInt32(row[4]);
|
|
DateTime _cachetime = ((DateTime)row[5]).ToLocalTime();
|
|
MapTileInfo m = new MapTileInfo(_id, _x, _y, _zoom, _type, _cachetime);
|
|
return m;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public MapTileInfo TileFind(int x, int y, int z, int type)
|
|
{
|
|
db.DBCommand.CommandText = "SELECT * FROM " + TilesTableName + " WHERE X = @x AND Y = @y AND Zoom = @z AND Type = @type";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@x", x));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@y", y));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@z", z));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@type", type));
|
|
DataTable dt = db.Select(db.DBCommand);
|
|
if (dt != null)
|
|
{
|
|
DataRow row = dt.Rows[0];
|
|
int _id = System.Convert.ToInt32(row[0]);
|
|
int _x = System.Convert.ToInt32(row[1]);
|
|
int _y = System.Convert.ToInt32(row[2]);
|
|
int _zoom = System.Convert.ToInt32(row[3]);
|
|
int _type = System.Convert.ToInt32(row[4]);
|
|
DateTime _cachetime = ((DateTime)row[5]).ToLocalTime();
|
|
MapTileInfo m = new MapTileInfo(_id, _x, _y, _zoom, _type, _cachetime);
|
|
return m;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public MapTileData TileDataFind(int id)
|
|
{
|
|
db.DBCommand.CommandText = "SELECT * FROM " + TilesDataTableName + " WHERE id = @id";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@id", id));
|
|
DataTable dt = db.Select(db.DBCommand);
|
|
if (dt != null)
|
|
{
|
|
MapTileData m = new MapTileData(System.Convert.ToInt32(dt.Rows[0][0]), (byte[])dt.Rows[0][1]);
|
|
return m;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public int TileInsert(int x, int y, int z, int type, DateTime cachetime, byte[] tile)
|
|
{
|
|
int id = -1;
|
|
// db.BeginTransaction();
|
|
try
|
|
{
|
|
id = System.Convert.ToInt32(db.ExecuteScalar("SELECT MAX(id) FROM " + TilesTableName)) + 1;
|
|
lock (db.DBCommand)
|
|
{
|
|
db.DBCommand.CommandText = "INSERT INTO " + TilesTableName + " (id, X, Y, Zoom, Type, CacheTime) VALUES (@id, @x, @y, @z, @type, @cachetime)";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@id", id));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@x", x));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@y", y));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@z", z));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@type", type));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@cachetime", cachetime));
|
|
db.ExecuteNonQuery(db.DBCommand);
|
|
db.DBCommand.CommandText = "INSERT INTO " + TilesDataTableName + " (id, Tile) VALUES (@id, @tile)";
|
|
db.DBCommand.Parameters.Clear();
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@id", id));
|
|
db.DBCommand.Parameters.Add(new SQLiteParameter("@tile", tile));
|
|
db.ExecuteNonQuery(db.DBCommand);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.ToString());
|
|
}
|
|
// db.Commit();
|
|
return id;
|
|
}
|
|
|
|
public int TileDeleteAll()
|
|
{
|
|
db.BeginTransaction();
|
|
int result;
|
|
lock (db.DBCommand)
|
|
{
|
|
db.DBCommand.CommandText = "DELETE FROM " + TilesTableName;
|
|
db.DBCommand.Parameters.Clear();
|
|
result = db.ExecuteNonQuery(db.DBCommand);
|
|
db.DBCommand.CommandText = "DELETE FROM " + TilesDataTableName;
|
|
db.DBCommand.Parameters.Clear();
|
|
result = db.ExecuteNonQuery(db.DBCommand);
|
|
}
|
|
db.Commit();
|
|
return result;
|
|
}
|
|
|
|
public int TileDelete(int id)
|
|
{
|
|
db.BeginTransaction();
|
|
int result;
|
|
lock (db.DBCommand)
|
|
{
|
|
db.DBCommand.CommandText = "DELETE FROM " + TilesTableName + " WHERE id = " + id.ToString();
|
|
db.DBCommand.Parameters.Clear();
|
|
result = db.ExecuteNonQuery(db.DBCommand);
|
|
db.DBCommand.CommandText = "DELETE FROM " + TilesDataTableName + " WHERE id = " + id.ToString();
|
|
db.DBCommand.Parameters.Clear();
|
|
result = db.ExecuteNonQuery(db.DBCommand);
|
|
}
|
|
db.Commit();
|
|
return result;
|
|
}
|
|
|
|
public List<MapTileInfo> TilesGetAll()
|
|
{
|
|
List<MapTileInfo> l = new List<MapTileInfo>();
|
|
DataTable dt = db.Select("SELECT * FROM " + TilesTableName);
|
|
if (dt != null)
|
|
{
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
int _id = System.Convert.ToInt32(row[0]);
|
|
int _x = System.Convert.ToInt32(row[1]);
|
|
int _y = System.Convert.ToInt32(row[2]);
|
|
int _zoom = System.Convert.ToInt32(row[3]);
|
|
int _type = System.Convert.ToInt32(row[4]);
|
|
DateTime _cachetime = ((DateTime)row[5]).ToLocalTime();
|
|
MapTileInfo m = new MapTileInfo(_id, _x, _y, _zoom, _type, _cachetime);
|
|
l.Add(m);
|
|
}
|
|
}
|
|
return l;
|
|
}
|
|
#endregion
|
|
|
|
}
|
|
}
|
|
|