public void CreateEmptyAthleteRecord()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
var command = conn.CreateCommand();
// note that AthleteID is not auto-incremented
command.CommandText = @"
INSERT INTO [Athlete] (AthleteID,EMail,Name,IsPro,Gender,Country,MetroID,TimeModified,TimeCreated,SnookerAbout)
VALUES
(0,'','',0,0,'',0,@TimeModified,@TimeCreated,'')
";
DateTime now = DateTimeHelper.GetUtcNow();
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@TimeModified", now));
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@TimeCreated", now));
command.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new Exception("Failed to create Athlete record. Message: " + TraceHelper.ExceptionToString(exc));
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
int PureImageCache.DeleteOlderThan(DateTime date, int?type)
{
int affectedRows = 0;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s"));
if (type.HasValue)
{
com.CommandText += " and Type = " + type;
}
affectedRows = com.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("DeleteOlderThan: " + ex);
#endif
Debug.WriteLine("DeleteOlderThan: " + ex);
}
return(affectedRows);
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch (Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return(ret);
}
public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
{
_databaseFullPath = databaseFullPath;
_destructorType = destructorType;
_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
if (!File.Exists(_databaseFullPath))
{
SQLiteConnection.CreateFile(_databaseFullPath);
Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
}
else
{
Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
}
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
Log.Debug("About to create or check for the Entities table in the EntityStore database.");
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
}
}
}
public MemoryMappedFileRepository(ChunkDataRepositoryCollection Repo, string Path, string Name)
: base(Repo)
{
if (Repo == null)
throw new ArgumentNullException ("Repo");
if (Path == null)
throw new ArgumentNullException ("Path");
if (Name == null)
throw new ArgumentNullException ("Name");
publicChunks = new SortedSet<ChunkDescriptor> (new ChunkDescriptor.Comparer_Hash());
path = Path;
name = Name;
string FPath = Path + System.IO.Path.DirectorySeparatorChar + Name;
bool Exists = System.IO.File.Exists (FPath);
if (!Exists)
System.IO.File.Create (FPath);
Base = new Mono.Data.Sqlite.SqliteConnection ("Data Source=" + FPath);
if (!Exists) {
try{
id = Guid.NewGuid();
Base.Open();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE CHUNKS(" +
"ID BLOB(64) NOT NULL, " +
"HAS_DEPENDENDANTS BIT NOT NULL, " +
"Path TEXT(400) NOT NULL)",Base))
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE ATTRIBUTES(" +
"NAME TEXT(128) PRIMARY KEY NOT NULL, " +
"VALUE BLOB(1024) NOT NULL)",Base))
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"CREATE TABLE DEPENDENCIES(" +
"ID BLOB(64) NOT NULL, " +
"DependantIDs BLOB(896) NOT NULL)",Base)) //default sqlite3 page size = 1024 = 64 for ID + 40 for everything sqlite needs + 920 which is a bit bigger than 14 Dependencies(896).
Comm.ExecuteNonQuery();
using(Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand (
"INSERT INTO ATTRIBUTES(" +
"NAME, " +
"VALUE) VALUES('ID', @p0)",Base)){
Comm.Parameters.AddWithValue("p0", ID.ToByteArray());
Comm.ExecuteNonQuery();
}
}
catch(Exception ex){
if(Base != null) {
if(Base.State == System.Data.ConnectionState.Open) {
Base.Close();
}
Base.Dispose();
}
if(System.IO.File.Exists (FPath))
System.IO.File.Delete(FPath);
throw ex;
}
}
else
Base.Open();
}
private void upgradeDataTablesIfNecessary()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
if (!this.checkIfColumnExists(conn, "Score", "ExtraData"))
{
this.createColumn(conn, "Score", "ExtraData", "ntext");
}
}
catch (Exception exc)
{
throw new Exception("Failed to update data table(s)", exc);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
{
_databaseFullPath = databaseFullPath;
_destructorType = destructorType;
_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
if (!File.Exists(_databaseFullPath))
{
SQLiteConnection.CreateFile(_databaseFullPath);
Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
}
else
{
Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
}
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
Log.Debug("About to create or check for the Entities table in the EntityStore database.");
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
}
}
}
public override bool TableExists(string tableName)
{
object result;
string sql = string.Format("select count(name) from sqlite_master where name = '{0}'", tableName);
using (var cn = new SQLiteConnectionAlias(this.ConnectionString))
{
using (var cmd = new SQLiteCommandAlias(sql, cn))
{
cn.Open();
try
{
result = cmd.ExecuteScalar();
}
catch (SQLiteExceptionAlias)
{
throw;
}
finally
{
cn.Close();
}
}
}
return((System.Int64)result > 0);
}
public void dbSetCommand(String dbPathFile, String commands)
{
bool exists = System.IO.File.Exists(dbPathFile);
if (exists)
{
try
{
Mono.Data.Sqlite.SqliteConnection connection =
new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbPathFile);
connection.Open();
Mono.Data.Sqlite.SqliteCommand dbcmd = connection.CreateCommand();
dbcmd.CommandText = commands;
dbcmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteCommand: " + ex.Message);
}
}
else
{
showMessage("O db " + dbPathFile + ", não foi encontrado.");
}
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = Properties.Resources.IpCacheCreateDb;
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = Properties.Resources.IpCacheCreateDb;
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return ret;
}
// public T Create <T>(T model) where T : class
// {
// using (var c = new Mono.Data.Sqlite.SqliteConnection(Database.ConnectionString)) {
// c.Insert<T>(model);
// }
// return model;
// }
public void Create(Curso model)
{
using (var c = new Mono.Data.Sqlite.SqliteConnection(Database.ConnectionString)) {
c.Open();
var m = model.ToDynamic();
m.SetorId = model.Setor.Id;
(m as IDictionary<string, object>).Remove("Setor");
c.Insert((object)m, "Curso");
}
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteDataAdapter v_adapter = null;
System.Data.DataTable v_table;
Console.WriteLine("Exemplo SQLite usando DataAdapter");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando DataAdapter
v_adapter = new Mono.Data.Sqlite.SqliteDataAdapter("select * from estados", v_con);
// 4) instanciando DataTable
v_table = new System.Data.DataTable("RESULTADO");
// 5) alimentando DataTable
v_adapter.Fill(v_table);
// 6) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 7) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Console.WriteLine("Exemplo SQLite usando Command");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand(
//"insert into estados values (60, 'WI', 'William Ivanski')",
"delete from estados where codigo = 60",
v_con
);
// 4) executando Command
v_cmd.ExecuteNonQuery();
Console.WriteLine("Ok.");
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 5) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 6) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public byte[] GetEntity(string entityType, string entityKey, out string entityTag, out DateTime?lastModified)
{
using (Log.Scope(LogLevel.Debug, "GetEntity"))
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT entityTag, lastModified, entityBlob FROM Entities WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter()
{
DbType = DbType.String, ParameterName = "entityType", Value = entityType
});
cmd.Parameters.Add(new SQLiteParameter()
{
DbType = DbType.String, ParameterName = "entityKey", Value = entityKey
});
var reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (!reader.Read())
{
Log.Debug("Could not find Entity with EntityType '{0}' and EntityKey '{1}'", entityType, entityKey);
entityTag = null;
lastModified = null;
return(null);
}
else
{
entityTag = null;
if (!reader.IsDBNull(0))
{
entityTag = reader.GetString(0);
}
lastModified = null;
if (!reader.IsDBNull(1))
{
lastModified = reader.GetDateTime(1);
}
Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'. It had EntityTag '{2}' and was LastModified '{3}'", entityType, entityKey, entityTag, lastModified);
if (!reader.IsDBNull(2))
{
return(ReadBytes(reader, 2));
}
else
{
return(null);
}
}
}
}
}
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", _db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using (DbDataReader rd = com.ExecuteReader())
{
if (rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double)rd["Latitude"];
val.Longitude = (double)rd["Longitude"];
val.CacheTime = (DateTime)rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public BaseProxy()
{
lock (_lock)
{
if (initialized)
{
return;
}
if (!File.Exists(dbFilePath))
{
if (!IsRunningOnMono())
{
System.Data.SQLite.SQLiteConnection.CreateFile(dbFilePath);
if (!Directory.Exists("x86"))
{
Directory.CreateDirectory("x86");
SaveToDisk("NetStash.x86.SQLite.Interop.dll", "x86\\SQLite.Interop.dll");
}
if (!Directory.Exists("x64"))
{
Directory.CreateDirectory("x64");
SaveToDisk("NetStash.x64.SQLite.Interop.dll", "x64\\SQLite.Interop.dll");
}
using (System.Data.SQLite.SQLiteConnection cnn = (System.Data.SQLite.SQLiteConnection)GetConnection())
{
cnn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("CREATE TABLE \"Log\" ([IdLog] integer, [Message] nvarchar, PRIMARY KEY(IdLog));", cnn);
cmd.ExecuteNonQuery();
}
}
else
{
Mono.Data.Sqlite.SqliteConnection.CreateFile(dbFilePath);
using (Mono.Data.Sqlite.SqliteConnection cnn = (Mono.Data.Sqlite.SqliteConnection)GetConnection())
{
cnn.Open();
Mono.Data.Sqlite.SqliteCommand cmd = new Mono.Data.Sqlite.SqliteCommand("CREATE TABLE \"Log\" ([IdLog] integer, [Message] nvarchar, PRIMARY KEY(IdLog));", cnn);
cmd.ExecuteNonQuery();
}
}
}
initialized = true;
}
}
public static MySqlConnection GetConnection()
{
MySqlConnection db = null;
try {
string conn_string = WebConfigurationManager.ConnectionStrings["CommunityAddinRepoConnectionString"].ConnectionString;
db = new MySqlConnection (conn_string);
db.Open ();
return db;
} catch (Exception ex) {
if (db != null)
db.Close ();
throw new Exception ("Database connection failed", ex);
}
}
public static MySqlConnection GetConnection()
{
MySqlConnection db = null;
try {
string conn_string = WebConfigurationManager.ConnectionStrings["CommunityAddinRepoConnectionString"].ConnectionString;
db = new MySqlConnection(conn_string);
db.Open();
return(db);
} catch (Exception ex) {
if (db != null)
{
db.Close();
}
throw new Exception("Database connection failed", ex);
}
}
//dbPathFile --> path file do banco de dados
//tablename --> nome da tabela
//resultCol --> coluna da tabela que sera retornada Ex."Nome"
//refCol --> coluna de referencia para comparação Ex."Senha"
//refData --> dado que sera comparado com a coluna de referencia Ex."123"
public String dbGetCommand(String dbPathFile, String tableName, String resultCol, String refCol, String refData)
{
int i = 0;
String resultModel = "";
String strQuery = "SELECT * FROM " + tableName;
ArrayList readArray = new ArrayList();
try
{
Mono.Data.Sqlite.SqliteConnection connection =
new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbPathFile);
connection.Open();
Mono.Data.Sqlite.SqliteCommand dbcmd = connection.CreateCommand();
dbcmd.CommandText = strQuery;
//SqliteDataReader rdr = cmd.ExecuteReader()
Mono.Data.Sqlite.SqliteDataReader rdr = dbcmd.ExecuteReader();
while (rdr.Read())
{
i++;
//if(rdr[refCol].ToString() == refData){
// resultModel = rdr[resultCol].ToString();
//}
if (rdr[refCol].ToString() == refData)
{
resultModel = rdr[resultCol].ToString();
}
}
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteDataReader: " + ex.Message);
}
return(resultModel);
}
private void btnTestSqlite_Click(object sender, EventArgs e)
{
try
{
string dbPath = Path.GetDirectoryName(Application.ExecutablePath);
var db = new Mono.Data.Sqlite.SqliteConnection("URI=file:" + Path.Combine(dbPath, "test.db"));
db.Open();
db.Close();
MessageBox.Show("ok");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public IDictionary <string, byte[]> GetAllEntities(string entityType)
{
using (Log.Scope(LogLevel.Debug, "GetAllEntities"))
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT entityKey, entityBlob FROM Entities WHERE entityType=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter()
{
DbType = DbType.String, ParameterName = "entityType", Value = entityType
});
var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var dict = new Dictionary <string, byte[]>(8);
while (reader.Read())
{
string entityKey = null;
if (!reader.IsDBNull(0))
{
entityKey = reader.GetString(0);
}
Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
if (reader.IsDBNull(1))
{
continue;
}
var bytes = ReadBytes(reader, 1);
dict.Add(entityKey, bytes);
}
return(dict);
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class.
/// </summary>
public InMemorySQLiteDatabase()
{
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
};
ConnectionString = connectionStringBuilder.ToString();
connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString);
sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sharedConnection.Open();
sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true);
}
public DatabaseFactory(IScriptDiscovery scriptDiscovery)
{
dbFilePath = Path.Combine(Directory.GetCurrentDirectory(), "box9database.sqlite");
database = () =>
{
if (bool.Parse(ConfigurationManager.AppSettings["MonoSqlite"]))
{
var conn = new Mono.Data.Sqlite.SqliteConnection(string.Format("Data Source={0};", dbFilePath));
conn.Open();
return(conn);
}
else
{
var conn = new SQLiteConnection(string.Format("Data Source={0};", dbFilePath));
conn.Open();
return(conn);
}
};
this.scriptDiscovery = scriptDiscovery;
Initialize();
}
/// <summary>
/// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class.
/// </summary>
/// <param name="name">The name.</param>
public TemporarySQLiteDatabase(string name)
{
dataSourcePath = Path.Combine(Environment.CurrentDirectory, name);
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = name,
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
};
sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sqLiteConnection.Open();
sharedConnection = new SharedConnection(sqLiteConnection);
sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true);
}
/// <summary>
/// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class.
/// </summary>
public InMemorySQLiteDatabase()
{
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
};
ConnectionString = connectionStringBuilder.ToString();
connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString);
sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sharedConnection.Open();
sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true);
}
public byte[] GetEntity(string entityType, string entityKey, out string entityTag, out DateTime? lastModified)
{
using (Log.Scope(LogLevel.Debug, "GetEntity"))
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT entityTag, lastModified, entityBlob FROM Entities WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
var reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (!reader.Read())
{
Log.Debug("Could not find Entity with EntityType '{0}' and EntityKey '{1}'", entityType, entityKey);
entityTag = null;
lastModified = null;
return null;
}
else
{
entityTag = null;
if (!reader.IsDBNull(0))
entityTag = reader.GetString(0);
lastModified = null;
if (!reader.IsDBNull(1))
lastModified = reader.GetDateTime(1);
Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'. It had EntityTag '{2}' and was LastModified '{3}'", entityType, entityKey, entityTag, lastModified);
if (!reader.IsDBNull(2))
return ReadBytes(reader, 2);
else
return null;
}
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class.
/// </summary>
/// <param name="name">The name.</param>
public TemporarySQLiteDatabase(string name)
{
dataSourcePath = Path.Combine(Environment.CurrentDirectory, name);
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = name,
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
};
sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sqLiteConnection.Open();
sharedConnection = new SharedConnection(sqLiteConnection);
sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true);
}
public static void Setup(bool useLocalFiles)
{
DataDirectory = new DirectoryInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Gablarski"));
if (!DataDirectory.Exists)
{
DataDirectory.Create();
}
if (useLocalFiles)
{
KeyFile = new FileInfo("gablarski.key");
DbFile = new FileInfo("gablarski.db");
}
else
{
KeyFile = new FileInfo(Path.Combine(DataDirectory.FullName, "gablarski.key"));
DbFile = new FileInfo(Path.Combine(DataDirectory.FullName, "gablarski.db"));
}
#if !XAMARIN
var builder = new SQLiteConnectionStringBuilder();
builder.DataSource = DbFile.FullName;
db = new SQLiteConnection(builder.ToString());
#else
KeyFile = new FileInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), KeyFile.Name));
DbFile = new FileInfo(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), DbFile.Name));
if (!DbFile.Exists)
{
SQLiteConnection.CreateFile(DbFile.FullName);
}
db = new SQLiteConnection("Data Source=" + DbFile.FullName);
#endif
db.Open();
CreateDbs();
}
public bool DeleteEntity(string entityType, string entityKey)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "DELETE FROM Entities WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter()
{
DbType = DbType.String, ParameterName = "entityType", Value = entityType
});
cmd.Parameters.Add(new SQLiteParameter()
{
DbType = DbType.String, ParameterName = "entityKey", Value = entityKey
});
var affectedCount = cmd.ExecuteNonQuery();
Log.Debug("Deleted Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
return(affectedCount == 1);
}
}
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if(Created)
{
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if(Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return ret;
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if(!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if(ret)
{
using(SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if(cn1.State == System.Data.ConnectionState.Open)
{
using(SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if(cn2.State == System.Data.ConnectionState.Open)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
using(SQLiteTransaction tr = cn2.BeginTransaction())
{
try
{
List<long> add = new List<long>();
using(SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using(SQLiteDataReader rd = cmd.ExecuteReader())
{
while(rd.Read())
{
long id = rd.GetInt64(0);
using(SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using(SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if(!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach(long id in add)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch(Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using(SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch(Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return ret;
}
private void createDataTables()
{
// note: sqlite tables always have a hidden "rowid" field
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());
conn.Open();
var command = conn.CreateCommand(); // note that AthleteID is not auto-incremented
command.CommandText = @"
CREATE TABLE [Athlete] (
[AthleteID] int PRIMARY KEY NOT NULL,
[EMail] ntext,
[Name] ntext,
[IsPro] bit,
[DOB] date NULL,
[Gender] int,
[Country] ntext,
[MetroID] int,
[Picture] ntext,
[FacebookId] ntext,
[TimeCreated] datetime,
[TimeModified] datetime,
[SnookerAbout] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Result] (
[ResultID] integer PRIMARY KEY NOT NULL,
[AthleteID] int NOT NULL,
[ResultTypeID] int,
[Time] float NULL,
[Distance] float NULL,
[Count] int NULL,
[Count2] int NULL,
[Date] date NULL,
[Notes] ntext,
[TimeModified] datetime,
[Guid] nvarchar(50),
[IsDeleted] bit NOT NULL,
[VenueID] int NULL,
[OpponentAthleteID] int NULL,
[OpponentConfirmation] int,
[Type1] int NULL,
[Details1] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Score] (
[ScoreID] integer PRIMARY KEY NOT NULL,
[AthleteAID] int NOT NULL,
[AthleteBID] int NOT NULL,
[Date] date NOT NULL,
[IsUnfinished] int NOT NULL,
[TimeModified] date NOT NULL,
[Guid] nvarchar(50),
[IsDeleted] bit NOT NULL,
[SportID] int NOT NULL,
[VenueID] int NULL,
[Type1] int NULL,
[OpponentConfirmation] int,
[PointsA] int NOT NULL,
[PointsB] int NOT NULL,
[InnerPoints1A] int NOT NULL,
[InnerPoints1B] int NOT NULL,
[InnerPoints2A] int NOT NULL,
[InnerPoints2B] int NOT NULL,
[InnerPoints3A] int NOT NULL,
[InnerPoints3B] int NOT NULL,
[InnerPoints4A] int NOT NULL,
[InnerPoints4B] int NOT NULL,
[InnerPoints5A] int NOT NULL,
[InnerPoints5B] int NOT NULL,
[InnerPoints6A] int NOT NULL,
[InnerPoints6B] int NOT NULL,
[InnerPoints7A] int NOT NULL,
[InnerPoints7B] int NOT NULL,
[InnerPoints8A] int NOT NULL,
[InnerPoints8B] int NOT NULL,
[InnerPoints9A] int NOT NULL,
[InnerPoints9B] int NOT NULL,
[InnerPoints10A] int NOT NULL,
[InnerPoints10B] int NOT NULL,
[ExtraData] ntext
)";
command.ExecuteNonQuery();
command.CommandText = @"
CREATE TABLE [Singular] (
[ID] int,
[DateDbCreated] datetime,
[MyAthleteID] int,
[AccessToken] ntext,
[UserWantsToBeGuest] int
)";
command.ExecuteNonQuery();
command.CommandText = @"
INSERT INTO [Singular] (ID,DateDbCreated,MyAthleteID,AccessToken,UserWantsToBeGuest) VALUES (0,@Date,0,'',0)
";
command.Parameters.Add(new Mono.Data.Sqlite.SqliteParameter("@Date", DateTime.Now.ToUniversalTime()));
command.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new Exception("Failed to create tables. Message: " + TraceHelper.ExceptionToString(exc));
}
finally
{
if (conn != null)
{
conn.Close();
}
}
CreateEmptyAthleteRecord();
}
PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if (!string.IsNullOrEmpty(attachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type);
using (DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if (GMapProvider.TileImageProxy != null)
{
ret = GMapProvider.TileImageProxy.FromArray(tile);
}
}
tile = null;
}
rd.Close();
}
}
if (!string.IsNullOrEmpty(detachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if (!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if (ret)
{
using (SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if (cn1.State == System.Data.ConnectionState.Open)
{
using (SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if (cn2.State == System.Data.ConnectionState.Open)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
using (SQLiteTransaction tr = cn2.BeginTransaction())
{
try
{
List <long> add = new List <long>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
long id = rd.GetInt64(0);
using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using (SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if (!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach (long id in add)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch (Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public void Open(string absoluteFilePath)
{
try
{
ConnectionStringBuilder connstr = new ConnectionStringBuilder();
connstr.DataSource = absoluteFilePath;
conn = new Connection();
conn.ConnectionString = connstr.ToString();
conn.Open();
cmdCommand = new Command(
"SELECT [Command] FROM [Command] WHERE [Name]=:name AND [Class]=:class",
conn);
cmdCommand.Parameters.Add(":name", DbType.Binary);
cmdCommand.Parameters.Add(":class", DbType.Binary);
textCommand = new Command(
"SELECT [Content] FROM [Text] WHERE [Name]=:name AND [Language]=:language AND [Class]=:class",
conn);
textCommand.Parameters.Add(":name", DbType.Binary);
textCommand.Parameters.Add(":language", DbType.Binary);
textCommand.Parameters.Add(":class", DbType.Binary);
troubleCodeCommand = new Command(
"SELECT [Content], [Description] FROM [TroubleCode] WHERE [Code]=:code AND [Language]=:language AND [Class]=:class",
conn);
troubleCodeCommand.Parameters.Add(":code", DbType.Binary);
troubleCodeCommand.Parameters.Add(":language", DbType.Binary);
troubleCodeCommand.Parameters.Add(":class", DbType.Binary);
liveDataCommand = new Command(
"SELECT [ShortName], [Content], [Unit], [DefaultValue], [CommandName], [CommandClass], [Description], [Index] FROM [LiveData] WHERE [Language]=:language AND [Class]=:class",
conn);
liveDataCommand.Parameters.Add(":language", DbType.Binary);
liveDataCommand.Parameters.Add(":class", DbType.Binary);
}
catch (Exception ex)
{
Close();
throw new DatabaseException(
String.Format("Cannot open vehicle database! file path = \"{0}\", error message: {1}",
absoluteFilePath, ex.Message));
}
}
public MemoryMappedFileRepository(ChunkDataRepositoryCollection Repo, string Path, string Name)
: base(Repo)
{
if (Repo == null)
{
throw new ArgumentNullException("Repo");
}
if (Path == null)
{
throw new ArgumentNullException("Path");
}
if (Name == null)
{
throw new ArgumentNullException("Name");
}
publicChunks = new SortedSet <ChunkDescriptor> (new ChunkDescriptor.Comparer_Hash());
path = Path;
name = Name;
string FPath = Path + System.IO.Path.DirectorySeparatorChar + Name;
bool Exists = System.IO.File.Exists(FPath);
if (!Exists)
{
System.IO.File.Create(FPath);
}
Base = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + FPath);
if (!Exists)
{
try{
id = Guid.NewGuid();
Base.Open();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE CHUNKS(" +
"ID BLOB(64) NOT NULL, " +
"HAS_DEPENDENDANTS BIT NOT NULL, " +
"Path TEXT(400) NOT NULL)", Base))
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE ATTRIBUTES(" +
"NAME TEXT(128) PRIMARY KEY NOT NULL, " +
"VALUE BLOB(1024) NOT NULL)", Base))
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"CREATE TABLE DEPENDENCIES(" +
"ID BLOB(64) NOT NULL, " +
"DependantIDs BLOB(896) NOT NULL)", Base)) //default sqlite3 page size = 1024 = 64 for ID + 40 for everything sqlite needs + 920 which is a bit bigger than 14 Dependencies(896).
Comm.ExecuteNonQuery();
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand(
"INSERT INTO ATTRIBUTES(" +
"NAME, " +
"VALUE) VALUES('ID', @p0)", Base)){
Comm.Parameters.AddWithValue("p0", ID.ToByteArray());
Comm.ExecuteNonQuery();
}
}
catch (Exception ex) {
if (Base != null)
{
if (Base.State == System.Data.ConnectionState.Open)
{
Base.Close();
}
Base.Dispose();
}
if (System.IO.File.Exists(FPath))
{
System.IO.File.Delete(FPath);
}
throw ex;
}
}
else
{
Base.Open();
}
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using(DbDataReader rd = com.ExecuteReader())
{
if(rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double)rd["Latitude"];
val.Longitude = (double)rd["Longitude"];
val.CacheTime = (DateTime)rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
public bool PutEntity(string entityType, string entityKey, byte[] entityBlob, string entityTag = null, DateTime? lastModified = null, bool alwaysOverwrite = true)
{
using (Log.Scope(LogLevel.Debug, "PutEntity"))
{
bool worked = false;
SQLiteConnection connection;
using (connection = new SQLiteConnection(_connectionString))
{
connection.Open();
SQLiteCommand cmd;
using (cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType});
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
var someType = cmd.ExecuteScalar();
var foundCount = (long)someType;
if (foundCount > 0)
{
if (alwaysOverwrite)
{
using (cmd = connection.CreateCommand())
{
cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
if (cmd.ExecuteNonQuery() > 0)
{
worked = true;
Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
}
}
}
else
{
using (cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=? AND entityTag=? AND lastModified=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified });
if ((int)cmd.ExecuteScalar() > 0)
{
using (cmd = connection.CreateCommand())
{
cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
if (cmd.ExecuteNonQuery() > 0)
{
worked = true;
Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
}
}
}
}
}
}
else
{
using (cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO Entities (entityType, entityKey, entityBlob, entityTag, lastModified) VALUES (?, ?, ?, ?, ?)";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified });
if (cmd.ExecuteNonQuery() > 0)
{
worked = true;
Log.Debug("Put (insert) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
}
}
}
}
}
return worked;
}
}
public IDictionary<string, byte[]> GetAllEntities(string entityType)
{
using (Log.Scope(LogLevel.Debug, "GetAllEntities"))
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT entityKey, entityBlob FROM Entities WHERE entityType=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var dict = new Dictionary<string, byte[]>(8);
while(reader.Read())
{
string entityKey = null;
if (!reader.IsDBNull(0))
entityKey = reader.GetString(0);
Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
if (reader.IsDBNull(1))
continue;
var bytes = ReadBytes(reader, 1);
dict.Add(entityKey, bytes);
}
return dict;
}
}
}
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Mono.Data.Sqlite.SqliteDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo SQLite usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if(!string.IsNullOrEmpty(attachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type);
using(DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if(rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if(GMapProvider.TileImageProxy != null)
{
ret = GMapProvider.TileImageProxy.FromArray(tile);
}
}
tile = null;
}
rd.Close();
}
}
if(!string.IsNullOrEmpty(detachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
int PureImageCache.DeleteOlderThan(DateTime date, int? type)
{
int affectedRows = 0;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s"));
if(type.HasValue)
{
com.CommandText += " and Type = " + type;
}
affectedRows = com.ExecuteNonQuery();
}
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("DeleteOlderThan: " + ex);
#endif
Debug.WriteLine("DeleteOlderThan: " + ex);
}
return affectedRows;
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return ret;
}
public bool DeleteEntity(string entityType, string entityKey)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "DELETE FROM Entities WHERE entityType=? AND entityKey=?";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType });
cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey });
var affectedCount = cmd.ExecuteNonQuery();
Log.Debug("Deleted Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey);
return affectedCount == 1;
}
}
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if (File.Exists(file))
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
bool?NoCacheTimeColumn = null;
try
{
using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using (DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch (Exception ex)
{
if (ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return(ret);
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable<List<GpsLog>> GetRoutesFromMobileLog(string gpsdLogFile, DateTime? start, DateTime? end, double? maxPositionDilutionOfPrecision)
{
#if SQLite
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
if(start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if(end.HasValue)
{
if(!start.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if(maxPositionDilutionOfPrecision.HasValue)
{
if(!start.HasValue && !end.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "(PositionDilutionOfPrecision <= @p3)";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using(DbDataReader rd = cmd.ExecuteReader())
{
List<GpsLog> points = new List<GpsLog>();
long lastSessionCounter = -1;
while(rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if(log.SessionCounter - lastSessionCounter != 1 && points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Add(log);
lastSessionCounter = log.SessionCounter;
}
if(points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return null;
#endif
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return ret;
}
public void Run()
{
_running = true;
try {
if (!LoadConfig (_config)) {
throw new ConfigFileInitializedException ();
}
ushort bindUdp = (ushort)_config.GetValue<int> (ConfigFields.NetBindUdp);
ushort bindTcp = (ushort)_config.GetValue<int> (ConfigFields.NetBindTcp);
int gwBindTcp = _config.GetValue<int> (ConfigFields.GwBindTcp);
_url = string.Format ("http://127.0.0.1:{0}/", gwBindTcp);
using (IDatagramEventSocket dgramSock = UdpSocket.CreateIPv4 ())
using (TcpListener listener = new TcpListener ()) {
dgramSock.Bind (new IPEndPoint (IPAddress.Any, bindUdp));
listener.Bind (new IPEndPoint (IPAddress.Any, bindTcp));
listener.ListenStart ();
CreateDatabaseConnectionDelegate create_session_db = delegate () {
IDbConnection connection = new Mono.Data.Sqlite.SqliteConnection ();
connection.ConnectionString = "Data Source=http-session.sqlite,DateTimeFormat=Ticks,Pooling=False";
connection.Open ();
return connection;
};
using (Interrupters ints = new Interrupters ())
using (Node node = new Node (ints, dgramSock, listener, "database.sqlite", bindUdp, bindTcp))
using (WebApp app = new WebApp (node, ints))
using (SessionMiddleware mid1 = new SessionMiddleware (create_session_db, app))
using (HttpServer.CreateEmbedHttpServer (mid1, null, true, true, _config.GetValue<bool> (ConfigFields.GwBindAny), gwBindTcp, 16)) {
InitNodeList initNodeList = new InitNodeList (node.PortOpenChecker);
_app = app;
_node = node;
_startupWaitHandle.Set ();
if (Started != null) {
try {
Started (this, EventArgs.Empty);
} catch {}
}
initNodeList.Load ();
app.ExitWaitHandle.WaitOne ();
initNodeList.Save ();
app.CreateStatisticsXML ().Save ("statistics-" + DateTime.Now.ToString ("yyyyMMddHHmmss") + ".xml");
_waitHandle.Set ();
}
}
} finally {
_running = false;
_startupWaitHandle.Set ();
}
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if(File.Exists(file))
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
bool? NoCacheTimeColumn = null;
try
{
using(DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using(DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch(Exception ex)
{
if(ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if(NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return ret;
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if (Created)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if (Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return(ret);
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch(Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return ret;
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", _db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return(ret);
}
public void Open()
{
connection.Open();
//CreateStandardTables();
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable <List <GpsLog> > GetRoutesFromMobileLog(string gpsdLogFile, DateTime?start, DateTime?end, double?maxPositionDilutionOfPrecision)
{
#if SQLite
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
if (start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if (end.HasValue)
{
if (!start.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if (maxPositionDilutionOfPrecision.HasValue)
{
if (!start.HasValue && !end.HasValue)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "(PositionDilutionOfPrecision <= @p3)";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using (DbDataReader rd = cmd.ExecuteReader())
{
List <GpsLog> points = new List <GpsLog>();
long lastSessionCounter = -1;
while (rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if (log.SessionCounter - lastSessionCounter != 1 && points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Add(log);
lastSessionCounter = log.SessionCounter;
}
if (points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return(null);
#endif
}
public static void Main(string[] args)
{
var options = new Options();
CommandLine.ICommandLineParser cmdParser =
new CommandLine.CommandLineParser(new CommandLine.CommandLineParserSettings(System.Console.Error));
if (cmdParser.ParseArguments(args, options)) {
string connectionString = string.Format("URI=file:{0}", options.Database);
#if (NET)
var connection = new System.Data.SQLite.SQLiteConnection(connectionString);
#else
var connection = new Mono.Data.Sqlite.SqliteConnection(connectionString);
#endif
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"CREATE TABLE IF NOT EXISTS at (id INTEGER PRIMARY KEY NOT NULL,name VARCHAR,surname VARCHAR,year INTEGER,gender CHAR,time VARCHAR)";
command.ExecuteNonQuery();
var repo = new AthleteRepository(command);
switch (options.Action) {
case Action.Module: {
// 10mm d=> 28pt
// 15mm => 42pt
//float marginLeft, float marginRight, float marginTop, float marginBottom
var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36);
iTextSharp.text.pdf.PdfWriter.GetInstance(document,
new System.IO.FileStream("./module.pdf", System.IO.FileMode.Create));
document.Open();
var builder = new ModuleBuilder(document, options.YearEdition, 10);
for (int page = 0; page < 10; page++) {
builder.AddPage();
}
document.Close();
break;
}
case Action.Insert: {
System.Console.WriteLine("Drop all results?[y/N]?");
string yes = System.Console.ReadLine();
if (yes == "y") {
FileHelpers.FileHelperEngine<Athlete> engine = new FileHelpers.FileHelperEngine<Athlete>();
Athlete[] athletes = engine.ReadFile(options.Input);
repo.DeleteAll();
foreach (var a in athletes) {
System.Console.WriteLine(a.Name);
repo.Insert(a);
}
}
break;
}
case Action.CreateList:
case Action.CreateResult: {
string catFileName = GetCatFileName(options);
string reportFileName = GetReportFileName(options);
var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36);
iTextSharp.text.pdf.PdfWriter.GetInstance(document,
new System.IO.FileStream(reportFileName, System.IO.FileMode.Create));
document.Open();
IBuilder builder = null;
if (options.Action == Action.CreateList) {
builder = new ListBuilder(document);
} else {
builder = new PdfBuilder(document);
}
Category[] cats = GetCategories(catFileName);
foreach (Category cat in cats) {
if (log.IsDebugEnabled) log.Debug("parse" + cat.Id);
builder.BeginReport(cat.Title, options.YearEdition);
var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition));
foreach (Athlete athlete in athletes) {
builder.Add(athlete);
}
builder.EndReport();
}
document.Close();
break;
}
case Action.Interactive: {
Category[] cats = GetCategories(GetCatFileName(options));
var parser = new TimeParser();
foreach (Category cat in cats) {
System.Console.WriteLine("========{0}=========", cat.Id);
var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition));
foreach (Athlete athlete in athletes) {
System.Console.Write("{0:00} {1}\t{2}({3}):", athlete.Id, athlete.Surname, athlete.Name, athlete.Gender);
string time = string.Empty;
string fmt = string.Empty;
do {
time = System.Console.ReadLine();
fmt = parser.Parse(time);
if (!string.IsNullOrEmpty(fmt) ) {
System.Console.WriteLine(fmt);
repo.UpdateTime(athlete.Id, fmt);
} else {
if (time != "s") {
System.Console.WriteLine("invalid..");
}
}
} while (string.IsNullOrEmpty(fmt) && time != "s");
}
}
break;
}
}
connection.Close();
}
}
