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);
}
protected void CreateStore(Boolean UpdateStore)
{
if (StoreExists)
{
if (!UpdateStore)
{
throw new StoreAlreadyExistsException();
}
}
else
{
#if (!WINDOWS_PHONE)
SQLiteConnection.CreateFile(FileName);
#endif
}
var connection = GetConnection(true);
try
{
foreach (var entity in this.Entities)
{
CreateTable(connection, entity);
}
}
finally
{
DoneWithConnection(connection, true);
}
}
public static bool UpdateObject <T> (this DbConnection gdb, T obj)
{
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(obj.GetType());
StringBuilder sql = new StringBuilder("UPDATE `");
sql.Append(map.Table).Append("` SET ");
foreach (var f in map)
{
if (f.Key == map.IdentityField)
{
continue;
}
string fp = "@_" + f.Key;
sql.Append(f.Key).Append('=').Append(fp).Append(',');
object val = f.Value.GetValue(obj, null);
/* if (val is Enum)
* val = Convert.ChangeType (val, typeof(int));*/
cmd.Parameters.AddWithValue(fp, val);
}
sql.Remove(sql.Length - 1, 1);
sql.Append(" WHERE ");
AppendObjectFilter(map, cmd, obj, sql);
cmd.CommandText = sql.ToString();
return(cmd.ExecuteNonQuery() > 0);
}
}
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 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();
}
}
}
void createColumn(Mono.Data.Sqlite.SqliteConnection conn, string tableName, string columnName, string type)
{
var command = conn.CreateCommand();
command.CommandText = "ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + type;
command.ExecuteNonQuery();
}
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.");
}
}
}
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 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 bool _isDisposing; // To detect redundant calls
/// <summary>
/// Initializes a new instance of the <see cref="LiteDbContext" /> class.
/// </summary>
/// <param name="databaseFilePath">The database file path.</param>
/// <param name="enabledLog">if set to <c>true</c> [enabled log].</param>
protected LiteDbContext(string databaseFilePath, bool enabledLog = true)
{
EnabledLog = enabledLog;
_contextType = GetType();
LoadEntitySets();
databaseFilePath = Path.GetFullPath(databaseFilePath);
var databaseExists = File.Exists(databaseFilePath);
#if NET461
Connection = new Mono.Data.Sqlite.SqliteConnection($"URI=file:{databaseFilePath}");
#else
var builder = new SqliteConnectionStringBuilder
{
DataSource = databaseFilePath,
};
Connection = new SqliteConnection(builder.ToString());
#endif
Connection.Open();
if (databaseExists == false)
{
"DB file does not exist. Creating.".Debug(nameof(LiteDbContext));
CreateDatabase();
$"DB file created: '{databaseFilePath}'".Debug(nameof(LiteDbContext));
}
UniqueId = Guid.NewGuid();
}
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 void Init()
{
connection = new SQLiteConnection {
ConnectionString = "Data Source=" + "save.db"
};
Open();
}
public static DbDataReader ExecuteSelect(this DbConnection gdb, string sql, params object[] args)
{
MySqlConnection db = (MySqlConnection)gdb;
MySqlCommand cmd = db.CreateCommand();
GenerateSqlCommand(cmd, sql, args);
return(cmd.ExecuteReader());
}
/// <summary>
/// Creates the database.
/// </summary>
public void Create()
{
var filePath = new FileInfo(dataSourcePath);
if (!filePath.Exists)
{
SQLiteConnection.CreateFile(dataSourcePath);
}
}
public void CreateDatabaseFile(string dbFile)
{
this.DBFile = dbFile;
var connection = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbFile);
BooruApp.BooruApplication.Database.CreateDatabase(connection);
BooruApp.BooruApplication.Settings.Set("last_used_db", dbFile);
}
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 void InsertObject <T> (this DbConnection gdb, T obj)
{
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(obj.GetType());
StringBuilder sql = new StringBuilder("INSERT INTO `");
sql.Append(map.Table).Append("` (");
foreach (string f in map.Keys)
{
if (f != map.IdentityField)
{
sql.Append(f).Append(',');
}
}
sql[sql.Length - 1] = ')';
sql.Append(" VALUES (");
foreach (var f in map)
{
if (f.Key == map.IdentityField)
{
continue;
}
string fp = "@_" + f.Key;
sql.Append(fp).Append(',');
cmd.Parameters.AddWithValue(fp, f.Value.GetValue(obj, null));
}
sql[sql.Length - 1] = ')';
if (map.IdentityField != null)
{
sql.Append("; SELECT @@IDENTITY");
}
cmd.CommandText = sql.ToString();
if (map.IdentityField == null)
{
cmd.ExecuteNonQuery();
}
else
{
using (DbDataReader dr = cmd.ExecuteReader()) {
if (dr.Read())
{
PropertyInfo prop = map[map.IdentityField];
object val = Convert.ChangeType(dr[0], prop.PropertyType);
prop.SetValue(obj, val, null);
}
else
{
throw new Exception("Insertion failed");
}
}
}
}
}
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;
}
private void CreateDbIfNotExists()
{
string path = Path.Combine(this.DBDirectory, this.DBName);
if (!File.Exists(path))
{
Directory.CreateDirectory(this.DBDirectory);
SQLiteConnectionAlias.CreateFile(Path.Combine(this.DBDirectory, this.DBName));
}
}
// 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");
}
}
/// <summary>
/// Creates the database.
/// </summary>
public void Create()
{
#if !NETCORE
var filePath = new FileInfo(dataSourcePath);
if (!filePath.Exists)
{
SQLiteConnection.CreateFile(dataSourcePath);
}
#endif
}
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 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 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 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 newLinkDialog()
{
this.Build ();
this.SetPosition (Gtk.WindowPosition.Center);
dbConn = new SQLiteConnection ("Data Source="+approot+"LAdd.db, Version=3");
//TODO make set password / change password dialog / window ... dbConn
fillCbFlagWithAllFlagTypes ();
fillInputsFromClipboard ();
/* retry getting title if value enter in entryLink widget*/
entryLink.Activated += entryLink_activated;
entryLink.GrabFocus ();
Gtk.Clipboard.Get (Gdk.Selection.Clipboard).OwnerChange += onClipboardOwnerChange;
}
public static void DeleteObject <T> (this DbConnection gdb, T obj)
{
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(obj.GetType());
StringBuilder sql = new StringBuilder("DELETE FROM `");
sql.Append(map.Table).Append("` WHERE ");
AppendObjectFilter(map, cmd, obj, sql);
Console.WriteLine(sql);
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
}
}
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 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;
}
}
private bool checkIfColumnExists(Mono.Data.Sqlite.SqliteConnection conn, string tableName, string columnName)
{
var command = conn.CreateCommand();
command.CommandText = string.Format("PRAGMA table_info({0})", tableName);
var reader = command.ExecuteReader();
int nameIndex = reader.GetOrdinal("Name");
while (reader.Read())
{
if (reader.GetString(nameIndex).Equals(columnName))
{
return(true);
}
}
return(false);
}
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);
}
}
/// <summary>
/// Deletes the database.
/// </summary>
public void Dispose()
{
var filePath = new FileInfo(dataSourcePath);
if (!filePath.Exists)
{
return;
}
sharedConnection.Dispose();
sqLiteConnection.Dispose();
SQLiteConnection.ClearAllPools();
// SQLite requires all created sql connection/command objects to be disposed
// in order to delete the database file
GC.Collect(2, GCCollectionMode.Forced);
System.Threading.Thread.Sleep(100);
File.Delete(dataSourcePath);
}
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);
}
/// <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 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;
}
}
}
}
}
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 SQLiteConnection(string connectionString)
{
__connection = new Mono.Data.Sqlite.SqliteConnection (connectionString);
}
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;
}
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 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 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));
}
}
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 SQLiteConnection()
{
__connection = new Mono.Data.Sqlite.SqliteConnection ();
}
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;
}
}
}
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();
}
public static IDbConnection GetConnection(string provider, string cstring)
{
IDbConnection cn = null;
switch (provider.ToLower())
{
case "sql":
// can't connect unless information provided;
// when user wants to set the connection programmatically this they should do this
if (cstring.Length > 0)
cn = new SqlConnection(cstring);
break;
case "odbc":
cn = new OdbcConnection(cstring);
break;
case "oledb":
cn = new OleDbConnection(cstring);
break;
case "filedirectory":
cn = new fyiReporting.Data.FileDirConnection(cstring);
break;
case "xml":
cn = new fyiReporting.Data.XmlConnection(cstring);
break;
case "webservice":
cn = new fyiReporting.Data.WebServiceConnection(cstring);
break;
case "weblog":
cn = new fyiReporting.Data.LogConnection(cstring);
break;
case "text":
cn = new fyiReporting.Data.TxtConnection(cstring);
break;
case "itunes":
cn = new fyiReporting.Data.iTunesConnection(cstring);
break;
#if EnableBundleLinux
// See properties -> Build -> Conditional compilation symbols
case "sqlite":
cn = new Mono.Data.Sqlite.SqliteConnection(cstring);
break;
case "postgresql":
cn = new Npgsql.NpgsqlConnection(cstring);
break;
#endif
default:
if (SqlEntries == null){ // if never initialized; we should init
RdlEngineConfigInit();
}
System.Console.WriteLine("Attempt to find provider");
SqlConfigEntry sce = SqlEntries[provider] as SqlConfigEntry;
if (sce == null || sce.CodeModule == null)
{
if (sce != null && sce.ErrorMsg != null){ // error during initialization??
throw new Exception(sce.ErrorMsg);
}
break;
}
System.Console.WriteLine("Provider Create Instance");
object[] args = new object[] { cstring };
Assembly asm = sce.CodeModule;
object o = asm.CreateInstance(sce.ClassName, false,
BindingFlags.CreateInstance, null, args, null, null);
if (o == null)
throw new Exception(string.Format(Strings.RdlEngineConfig_Error_UnableCreateInstance, sce.ClassName, provider));
cn = o as IDbConnection;
break;
}
return cn;
}
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 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 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 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 SQLiteConnection(SQLiteConnection conn)
{
__connection = new Mono.Data.Sqlite.SqliteConnection (conn.__connection);
}
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;
}
}
