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 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();
}
protected void fillCbFlagWithAllFlagTypes()
{
dbConn.Open ();
String getAllFlagTypesQ = "select * from FlagTypes;";
try {
SQLiteCommand cmd = new SQLiteCommand(getAllFlagTypesQ, dbConn);
SQLiteDataReader reader = cmd.ExecuteReader();
while(reader.Read()){
cbFlag.AppendText (reader["title"].ToString());
}
} catch (SQLiteException e){
Console.Write (e.ToString());
}
dbConn.Close ();
}
static void AppendObjectFilter(DbMap map, MySqlCommand cmd, object obj, StringBuilder sql)
{
int n = 0;
foreach (var prop in map)
{
if (map.KeyFields.Length != 0 && !map.KeyFields.Contains(prop.Key))
{
continue;
}
if (n > 0)
{
sql.Append(" AND ");
}
sql.Append(prop.Key).Append("=@__v" + n);
cmd.Parameters.AddWithValue("@__v" + n, prop.Value.GetValue(obj, null));
n++;
}
}
public static T ReadSettings <T> (this DbConnection gdb) where T : new ()
{
T obj = new T();
DbMap map = GetMap(typeof(T));
MySqlConnection db = (MySqlConnection)gdb;
Dictionary <string, PropertyInfo> readProps = new Dictionary <string, PropertyInfo> (map);
using (MySqlCommand cmd = db.CreateCommand()) {
cmd.CommandText = "SELECT * FROM `" + map.Table + "`";
using (DbDataReader dr = cmd.ExecuteReader()) {
while (dr.Read())
{
string fname = (string)dr["Key"];
string val = dr["Value"] as string;
PropertyInfo prop;
if (map.TryGetValue(fname, out prop))
{
object cval;
if (prop.PropertyType.IsEnum)
{
cval = Enum.Parse(prop.PropertyType, val);
}
else
{
cval = Convert.ChangeType(val, prop.PropertyType);
}
prop.SetValue(obj, cval, null);
readProps.Remove(fname);
}
}
}
}
foreach (PropertyInfo prop in readProps.Values)
{
DataMemberAttribute att = (DataMemberAttribute)Attribute.GetCustomAttribute(prop, typeof(DataMemberAttribute), true);
if (att != null && att.DefaultValue != null)
{
prop.SetValue(obj, att.DefaultValue, null);
}
}
return(obj);
}
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 static IEnumerable <T> SelectObjects <T> (this DbConnection gdb, string sql, params object[] args) where T : new ()
{
List <T> res = new List <T> ();
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(typeof(T));
if (sql == "*")
{
// Select all
sql = "SELECT * FROM `" + map.Table + "`";
}
else if (sql.StartsWith("*"))
{
// Select with a where
sql = "SELECT * FROM `" + map.Table + "` WHERE " + sql.Substring(1);
}
else if (sql == ".")
{
// Select by id
if (map.KeyFields.Length != 1)
{
throw new NotSupportedException();
}
sql = "SELECT * FROM `" + map.Table + "` WHERE " + map.KeyFields[0] + " = {0}";
}
GenerateSqlCommand(cmd, sql, args);
using (DbDataReader dr = cmd.ExecuteReader()) {
while (dr.Read())
{
res.Add(ReadObject <T> (map, dr));
}
}
}
return(res);
}
public static void WriteSettings <T> (this DbConnection gdb, T obj) where T : new ()
{
DbMap map = GetMap(typeof(T));
MySqlConnection db = (MySqlConnection)gdb;
foreach (KeyValuePair <string, PropertyInfo> prop in map)
{
object val = prop.Value.GetValue(obj, null);
if (val == null)
{
DataMemberAttribute att = (DataMemberAttribute)Attribute.GetCustomAttribute(prop.Value, typeof(DataMemberAttribute), true);
if (att != null)
{
val = att.DefaultValue;
}
}
if (val != null)
{
val = val.ToString();
}
using (MySqlCommand cmd = db.CreateCommand()) {
GenerateSqlCommand(cmd, "UPDATE `" + map.Table + "` SET `Value`={0} WHERE `Key`={1}", val, prop.Key);
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
continue;
}
}
using (MySqlCommand cmd = db.CreateCommand()) {
// New property. It has to be inserted
GenerateSqlCommand(cmd, "INSERT INTO `" + map.Table + "` (`Key`,`Value`) VALUES ({0},{1})", prop.Key, val);
cmd.ExecuteNonQuery();
}
}
}
//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 List <String> dbGetCommand(String dbPathFile, String tableName, String resultCol, String refCol, String refData)
{
int i = 0;
List <String> resultModel = new List <String>();
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;
Mono.Data.Sqlite.SqliteDataReader rdr = dbcmd.ExecuteReader();
while (rdr.Read())
{
i++;
if (rdr[refCol].ToString() == refData)
{
resultModel.Add(rdr[resultCol].ToString());//[i] = rdr[resultCol].ToString();
}
}
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteDataReader: " + ex.Message);
}
return(resultModel);
}
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 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);
}
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;
}
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 SQLiteCommand(SQLiteConnection connection)
{
__command = new Mono.Data.Sqlite.SqliteCommand (connection.__connection);
}
public SqliteDataAdapter(Mono.Data.Sqlite.SqliteCommand cmd)
{
}
private bool _checkIfInputDataIsInDb()
{
string link = entryLink.Text.Trim();
if (link.Length > 0) {
string lookupCheckQ = "select count(*) as numberOfRows from Links where link='" + link + "';";
try {
SQLiteCommand cmd = new SQLiteCommand (lookupCheckQ, dbConn);
dbConn.Open ();
SQLiteDataReader reader = cmd.ExecuteReader ();
if (reader.Read ()) {
Console.WriteLine (reader ["numberOfRows"]);
if(Convert.ToInt32( reader["numberOfRows"]) >0){
dbConn.Close ();
return true;
} else {
dbConn.Close ();
return false;
}
} else {
dbConn.Close ();
return false;
}
} catch (Exception e) {
dbConn.Close ();
Console.WriteLine (e.ToString ());
return false;
}
} else {
return false;
}
}
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 override ChunkData Pull(ChunkDescriptor Chunk)
{
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand ("SELECT PATH FROM CHUNKS WHERE ID = @p0", Base)) {
Comm.Parameters.AddWithValue("p0", Chunk.Hash);
Mono.Data.Sqlite.SqliteDataReader DR = Comm.ExecuteReader ();
if(DR.Read())
{
return new File(Chunk, this, DR.GetString(0));
}
return null;
}
}
public override void Push(ChunkDescriptor Chunk)
{
if (Chunk == null)
throw new ArgumentNullException ("Chunk");
string NName = Chunk.Hash.ToHexadecimal ();
string FPath = path +System.IO.Path.DirectorySeparatorChar + NName;
Stream ChunkBytes = Chunk.GetRawData();
new File (Chunk, this, FPath, ChunkBytes);
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand ("INSERT INTO Files (ID, Name, Hash, Attributes) VALUES (@p0,@p1,@p2,@p3)", Base)) {
Comm.Parameters.AddWithValue("p0", Chunk.Hash);
Comm.Parameters.AddWithValue("p1", NName);
Comm.Parameters.AddWithValue("p2", null);
Comm.ExecuteNonQuery ();
}
}
protected override object[] Select(string entityName, IEnumerable <FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences, bool filterReferences, IDbConnection connection)
{
if (!m_entities.HasEntity(entityName))
{
throw new EntityNotFoundException(entityName);
}
UpdateIndexCacheForType(entityName);
//var genType = typeof(List<>).MakeGenericType(objectType);
//var items = (System.Collections.IList)Activator.CreateInstance(genType);
var items = new List <object>();
SqlEntityInfo entity = m_entities[entityName];
var isDynamicEntity = entity is DynamicEntityInfo;
if (connection == null)
{
connection = GetConnection(false);
}
SQLiteCommand command = null;
try
{
//Deprecated
//CheckOrdinals(entityName);
OnBeforeSelect(m_entities[entityName], filters, fillReferences);
var start = DateTime.Now;
bool tableDirect;
command = GetSelectCommand <SQLiteCommand, SQLiteParameter>(entityName, filters, firstRowOffset, fetchCount, out tableDirect);
command.Connection = connection as SQLiteConnection;
int searchOrdinal = -1;
// ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
// TODO: we need to ensure that the search value does not exceed the length of the indexed
// field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (results.HasRows)
{
var ordinals = GetOrdinals(entityName, results);
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = ordinals[matchField];
}
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
object item = null;
object rowPK = null;
if (isDynamicEntity)
{
var dynamic = new DynamicEntity(entity as DynamicEntityInfo);
foreach (var pair in ordinals)
{
if (entity.Fields[pair.Key].DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
pair.Key, entityName));
}
dynamic[pair.Key] = entity.Deserializer(dynamic, pair.Key, results[pair.Value]);
}
else
{
dynamic[pair.Key] = results[pair.Value];
}
}
item = dynamic;
}
else if (entity.CreateProxy == null)
{
if (entity.DefaultConstructor == null)
{
item = Activator.CreateInstance(entity.EntityType);
}
else
{
item = entity.DefaultConstructor.Invoke(null);
}
foreach (var field in Entities[entityName].Fields)
{
var value = results[ordinals[field.FieldName]];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = entity.Deserializer.Invoke(item, field.FieldName, value);
field.PropertyInfo.SetValue(item, @object, null);
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs <TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var valueAsTimeSpan = new TimeSpan((long)value);
field.PropertyInfo.SetValue(item, valueAsTimeSpan, null);
}
else if ((field.IsPrimaryKey) && (value is Int64) && (field.PropertyInfo.PropertyType.Equals(typeof(Int32))))
{
// SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if ((value is Int64) || (value is double))
{
// Work Around SQLite underlying storage type
if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
{
field.PropertyInfo.SetValue(item, Convert.ToUInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(Int32)))
{
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
{
field.PropertyInfo.SetValue(item, Convert.ToDecimal(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
{
field.PropertyInfo.SetValue(item, Convert.ToSingle(value), null);
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
//Check if it is reference key to set, not primary.
ReferenceAttribute attr = referenceFields.Where(
x => x.ReferenceField == field.FieldName).FirstOrDefault();
if (attr != null)
{
rowPK = value;
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
else
{
item = entity.CreateProxy(results, ordinals);
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false, filterReferences, connection);
}
items.Add(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
OnAfterSelect(m_entities[entityName], filters, fillReferences, DateTime.Now.Subtract(start), command.CommandText);
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
return(items.ToArray());
}
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));
}
}
protected void newLinkDialog_btnOk(object sender, EventArgs e)
{
if (!_checkIfInputDataIsInDb ()) {
//TODO check if Link exists in db before addning it to Links-table
string title = entryTitle.Text.Trim();
string link = entryLink.Text;
if (title.Length > 0 && link.Length > 0) {
if (cbFlag.ActiveText != null) {
string insertLinkQ = "insert into Links (title, link, flag) " +
"values ('" + title + "', '" + link + "', " +
"(select flagid from FlagTypes where title='" + cbFlag.ActiveText + "'));";
try {
SQLiteCommand cmd = new SQLiteCommand (insertLinkQ, dbConn);
dbConn.Open ();
//try to insert a userdefine link.
if (cmd.ExecuteNonQuery () > 0) {
this.Destroy (); //kill newLinkDialog
} else {
labelStatus.Text = "Error: Link is NOT saved!";
}
dbConn.Close ();
} catch (SQLiteException e2) {
Console.Write (e2.ToString ());
}
} else {
cbFlag.GrabFocus ();
cbFlag.ShowNow ();
labelStatus.Text = "You need to choose a flag!";
}
} else {
labelStatus.Text = "Need add title and link";
}
} else {
entryLink.GrabFocus ();
labelStatus.Text = "This link exists!";
}
}
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 SQLiteCommand(SQLiteConnection connection)
{
__command = new Mono.Data.Sqlite.SqliteCommand(connection.__connection);
}
public Statement(SQLiteCommand command, string query)
{
Command = command;
Command.CommandType = CommandType.Text;
Command.CommandText = query;
}
private IEnumerable <object> Select(string entityName, Type objectType, IEnumerable <FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences)
{
Debug.WriteLineIf(TracingEnabled, "+Select");
if (entityName == null)
{
throw new EntityNotFoundException(objectType);
}
if (!Entities.Contains(entityName))
{
if (DiscoverDynamicEntity(entityName) == null)
{
yield return(null);
}
}
UpdateIndexCacheForType(entityName);
var items = new List <object>();
var connection = GetConnection(false);
SQLiteCommand command = null;
try
{
CheckOrdinals(entityName);
bool tableDirect;
command = GetSelectCommand <SQLiteCommand, SQLiteParameter>(entityName, filters, out tableDirect);
command.Connection = connection as SQLiteConnection;
command.Transaction = CurrentTransaction as SQLiteTransaction;
int searchOrdinal = -1;
// ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
// TODO: we need to ensure that the search value does not exceed the length of the indexed
// field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (results.HasRows)
{
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = results.GetOrdinal(matchField);
}
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
bool fieldsSet;
object item = CreateEntityInstance(entityName, objectType, Entities[entityName].Fields, results, out fieldsSet);
object rowPK = null;
if (!fieldsSet)
{
foreach (var field in Entities[entityName].Fields)
{
MethodInfo mi = null;
if (!field.PropertyInfo.CanWrite)
{
// get a private accessor?
mi = field.PropertyInfo.GetSetMethod(true);
// not settable, so skip
if (mi == null)
{
continue;
}
}
var value = results[field.Ordinal];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (fillReferences)
{
// get serializer
var itemType = item.GetType();
var deserializer = GetDeserializer(itemType);
if (deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = deserializer.Invoke(item, new object[] { field.FieldName, value });
field.PropertyInfo.SetValue(item, @object, null);
}
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.IsTimespan)
{
// SQLite doesn't support "timespan" - and date/time must be stored as text, real or 64-bit integer (see the SQLite docs for more details)
// here we'll pull TimeSpans (since they can be negative) as an offset from a base date
var storeDate = (DateTime)value;
var storeTime = storeDate - new DateTime(1980, 1, 1);
field.PropertyInfo.SetValue(item, storeTime, null);
}
else if (field.DataType == DbType.DateTime)
{
field.PropertyInfo.SetValue(item, Convert.ToDateTime(value), null);
}
else if ((field.IsPrimaryKey) && (value is Int64))
{
if (field.PropertyInfo.PropertyType.Equals(typeof(int)))
{
// SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else
{
field.PropertyInfo.SetValue(item, Convert.ToInt64(value), null);
}
}
else if ((value is Int64) || (value is double))
{
// SQLite is "interesting" in that its 'integer' has a strong affinity toward 64-bit, so int and uint properties
// end up as 64-bit fields. Decimals have a strong affinity toward 'double', so float properties
// end up as 'double'. Even more fun is that a decimal value '0' will come back as an int64
// When we query those back, we must convert to put them into the property or we crash hard
object setval;
if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
{
setval = Convert.ToUInt32(value);
}
else if ((field.PropertyInfo.PropertyType.Equals(typeof(Int32))) || (field.PropertyInfo.PropertyType.Equals(typeof(Int32?))))
{
setval = Convert.ToInt32(value);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
{
setval = Convert.ToDecimal(value);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
{
setval = Convert.ToSingle(value);
}
else if (field.PropertyInfo.PropertyType.IsEnum)
{
setval = Enum.ToObject(field.PropertyInfo.PropertyType, value);
}
else
{
setval = value;
}
if (mi == null)
{
field.PropertyInfo.SetValue(item, setval, null);
}
else
{
mi.Invoke(setval, null);
}
}
else
{
var t = value.GetType();
field.PropertyInfo.SetValue(item, value, null);
}
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false);
}
// changed from
// items.Add(item);
yield return(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
Debug.WriteLineIf(TracingEnabled, "-Select");
}
