public void ImplicitIndex()
{
var db = new TestDb();
db.CreateTable <NoAttributes>(CreateFlags.ImplicitIndex);
TableMapping mapping = db.GetMapping <NoAttributes>();
TableMapping.Column column = mapping.Columns[2];
Assert.AreEqual("IndexedId", column.Name);
Assert.IsTrue(column.Indices.Any());
}
public void WithoutImplicitMapping()
{
var db = new TestDb();
db.CreateTable <NoAttributes>();
TableMapping mapping = db.GetMapping <NoAttributes>();
Assert.IsNull(mapping.PK);
TableMapping.Column column = mapping.Columns[2];
Assert.AreEqual("IndexedId", column.Name);
Assert.IsFalse(column.Indices.Any());
Assert.Throws(typeof(AssertionException), () => CheckPK(db));
}
public void CreateVeryAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable <VeryAdvancedTable>();
TableMapping mapping = db.GetMapping <VeryAdvancedTable>();
var sql = mapping.GetCreateSql();
var correct =
@"CREATE TABLE [VeryAdvancedTable] (
[Id] integer NOT NULL CHECK (Id <= 25),
[DiffName] varchar(255) COLLATE RTrim,
[IsWorking] integer NOT NULL DEFAULT(1),
[AnotherId] integer NOT NULL,
CONSTRAINT PK_MyPrimaryKey
PRIMARY KEY ([AnotherId], [Id] Desc),
CHECK (Id <= 10)
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual("Id <= 10", mapping.Checks.First());
Assert.AreEqual(2, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual("AnotherId", mapping.PrimaryKey.Columns[0].Name);
Assert.AreEqual("Id", mapping.PrimaryKey.Columns[1].Name);
TableMapping.Column idCol = mapping.Columns.First(c => c.Name == "Id");
Assert.AreEqual("Id <= 25", idCol.Checks.First());
Assert.AreEqual("PK_MyPrimaryKey", idCol.PrimaryKey.Name);
Assert.AreEqual(Direction.Desc, idCol.PrimaryKey.Direction);
TableMapping.Column difCol = mapping.Columns.First(c => c.Name == "DiffName");
Assert.AreEqual(255, difCol.MaxStringLength);
Assert.AreEqual(Collation.RTrim, difCol.Collation);
TableMapping.Column workinCol = mapping.Columns.First(c => c.Name == "IsWorking");
Assert.IsFalse(workinCol.IsNullable);
Assert.AreEqual("1", workinCol.DefaultValue);
Assert.IsFalse(mapping.Columns.Any(c => c.Name == "IgnoredColumn"));
}
public IEnumerable <T> ExecuteDeferredQuery <T>(TableMapping map)
{
if (_conn.Trace)
{
Debug.WriteLine("Executing Query: " + this);
}
var stmt = Prepare();
try
{
var cols = new TableMapping.Column[SQLite3.ColumnCount(stmt)];
for (int i = 0; i < cols.Length; i++)
{
var name = SQLite3.ColumnName16(stmt, i);
cols[i] = map.FindColumn(name);
}
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
var obj = Activator.CreateInstance(map.MappedType);
for (int i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
{
continue;
}
var colType = SQLite3.ColumnType(stmt, i);
var val = ReadCol(stmt, i, colType, cols[i].ColumnType);
cols[i].SetValue(obj, val);
}
OnInstanceCreated(obj);
yield return((T)obj);
}
}
finally
{
SQLite3.Finalize(stmt);
}
}
/// <summary>
/// Delete all messages from this folder.
/// </summary>
internal void DeleteAll()
{
TableMapping map = CIX.DB.GetMapping <CIXMessage>();
if (map != null)
{
TableMapping.Column column = map.FindColumnWithPropertyName("TopicID");
StringBuilder deleteCmd = new StringBuilder();
if (IsRootFolder)
{
int[] subFolders = SubFolderIDs();
if (subFolders.Length == 0)
{
return;
}
deleteCmd.AppendFormat("delete from {0} where ", map.TableName);
for (int c = 0; c < subFolders.Length; ++c)
{
if (c > 0)
{
deleteCmd.Append(" or ");
}
deleteCmd.AppendFormat("{0}={1}", column.Name, subFolders[c]);
}
}
else
{
deleteCmd.AppendFormat("delete from {0} where {1}={2}", map.TableName, column.Name, ID);
}
lock (CIX.DBLock)
{
CIX.DB.Execute(deleteCmd.ToString());
}
}
Invalidate();
}
/// <summary>
/// Get all changes fro SQLite Database
/// </summary>
/// <param name="schema">All Tables</param>
/// <param name="lastModifiedDate">Changes since this date</param>
internal IEnumerable<SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate)
{
List<SQLiteOfflineEntity> lstChanges = new List<SQLiteOfflineEntity>();
using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
{
try
{
foreach (var ty in schema.Collections)
{
// Get mapping from my type
var map = manager.GetMapping(ty);
// Create query to select changes
var querySelect = SQLiteConstants.SelectChanges;
var columnsDcl = new List<String>();
string pkColumnName = string.Empty;
// Foreach columns, create the tsql command to execute
foreach (var c in map.Columns)
{
if (!c.IsPK)
columnsDcl.Add("[s].[" + c.Name + "]");
// If it's the PK, add it from Tracking (because of deleted items not in real table
if (c.IsPK)
{
columnsDcl.Add("[t].[" + c.Name + "]");
pkColumnName = c.Name;
}
}
var decl = string.Join(",\n", columnsDcl.ToArray());
querySelect = String.Format(querySelect, map.TableName, pkColumnName, decl);
// Prepare command
using (var stmt = connection.Prepare(querySelect))
{
try
{
// Set Values
BindParameter(stmt, 1, lastModifiedDate);
// Get mapping form the statement
var cols = new TableMapping.Column[map.Columns.Length];
// Foreach column, get the property in my object
for (int i = 0; i < cols.Length; i++)
{
var name = stmt.ColumnName(i);
var c = map.FindColumn(name);
if (c != null)
cols[i] = map.FindColumn(name);
}
// While row is available
//while (await stmt.StepAsync().AsTask().ConfigureAwait(false))
while (stmt.Step() == SQLiteResult.ROW)
{
// Create the object
SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType);
for (int i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
continue;
// Read the column
var val = ReadCol(stmt, i, cols[i].ColumnType);
// Set the value
cols[i].SetValue(obj, val);
}
// Read the Oem Properties
var newIndex = map.Columns.Count();
obj.ServiceMetadata = new OfflineEntityMetadata();
obj.ServiceMetadata.IsTombstone = (Boolean)ReadCol(stmt, newIndex, typeof(Boolean));
obj.ServiceMetadata.Id = (String)ReadCol(stmt, newIndex + 1, typeof(String));
obj.ServiceMetadata.ETag = (String)ReadCol(stmt, newIndex + 2, typeof(String));
String absoluteUri = (String)ReadCol(stmt, newIndex + 3, typeof(String));
obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri);
lstChanges.Add(obj);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
stmt.Reset();
stmt.ClearBindings();
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
return lstChanges;
}
/// <summary>
/// 将表格转换成字段
/// </summary>
/// <typeparam name="TKey">表格主键作为字典的 key </typeparam>
/// <typeparam name="TValue">表格每一条数据作为字典的 value </typeparam>
/// <param name="func"></param>
/// <returns></returns>
public Dictionary <TKey, TValue> TableAsDictionary <TKey, TValue>(Func <TValue, TKey> func) where TValue : new()
{
#if RUNINSERVER
var e = TableAsList <TValue>();
int n = e.Count;
Dictionary <TKey, TValue> dir = new Dictionary <TKey, TValue>(n);
for (var i = 0; i < n; i++)
{
var value = e[i];
dir.Add(func(value), value);
}
return(dir);
#else
var map = GetMapping(typeof(TValue));
var connection = _connection;
var str = typeof(TValue).Name;
connection.CommandText = "select * from " + str;
connection.Prepare();
var n = connection.GetColumnCont();
var dir = new Dictionary <TKey, TValue>(n);
try
{
var cols = new TableMapping.Column[n];
for (var i = 0; i < n; i++)
{
var name = connection.ColumnName16(i);
var col = map.FindColumn(name);
cols[i] = col;
if (col == null)
{
Debug.LogError("table:" + str + " ,col:" + name + " is Null");
throw new Exception();
}
}
var t = map.MappedType;
while (connection.Step() == SQLite3.Result.Row)
{
var obj = Activator.CreateInstance(t);
for (var i = 0; i < n; i++)
{
var col = cols[i];
var val = connection.ReadCol(i, col.ColumnType);
col.FieldInfo.SetValue(obj, val);
}
var tv = (TValue)obj;
var tk = func(tv);
dir.Add(tk, tv);
}
}
catch (Exception e)
{
Debug.LogErrorFormat("{0}\n : {1}", str, e);
}
finally
{
connection.FinalizeStmt();
}
return(dir);
#endif
}
public List <T> TableAsList <T>() where T : new()
{
var map = GetMapping(typeof(T));
var str = typeof(T).Name;
var query = "select * from " + str;
#if !RUNINSERVER
var connection = _connection;
connection.CommandText = query;
connection.Prepare();
var n = connection.GetColumnCont();
var list = new List <T>(n);
try
{
var cols = new TableMapping.Column[n];
for (var i = 0; i < n; i++)
{
var name = connection.ColumnName16(i);
var col = map.FindColumn(name);
cols[i] = col;
if (col == null)
{
Debug.LogError("table:" + str + " ,col:" + name + " is Null");
throw new Exception();
}
}
var t = map.MappedType;
while (connection.Step() == SQLite3.Result.Row)
{
var obj = Activator.CreateInstance(t);
for (var i = 0; i < n; i++)
{
var col = cols[i];
var val = connection.ReadCol(i, col.ColumnType);
col.FieldInfo.SetValue(obj, val);
}
list.Add((T)obj);
}
}
catch (Exception e)
{
Debug.LogErrorFormat("{0}\n : {1}", str, e);
}
finally
{
connection.FinalizeStmt();
}
return(list);
#else
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = _connection;
cmd.CommandText = query;
//cmd.Prepare();
Debug.Log("query:" + query);
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
int n = reader.FieldCount;
Debug.Log("FieldCount:" + n + " reader.StepCount:" + reader.StepCount);
//cmd.
List <T> list = new List <T>(n);
try
{
var cols = new TableMapping.Column[n];
for (int i = 0; i < cols.Length; i++)
{
var name = reader.GetName(i);
cols[i] = map.FindColumn(name);
//if (cols[i] == null)
Debug.LogError("table:" + typeof(T).Name + " ,col:" + name + " " + cols[i].Name);
}
//for (int j=0;j<reader.StepCount;j++)
while (reader.Read())
{
//Debug.Log("int read:"+ cols.Length);
var obj = Activator.CreateInstance(map.MappedType);
for (int i = 0; i < cols.Length; i++)
{
//if (cols[i] == null)
// continue;
var colType = reader.GetFieldType(i);
object val = reader.GetValue(i);; // _connection.ReadCol(i, colType, cols[i].ColumnType);
//Debug.Log("val:" + i + " " + val);
if (colType == typeof(double))
{
//val = 2.5f;//reader.GetFloat(i);
float vv = (float)((double)val);
cols[i].FieldInfo.SetValue(obj, vv);
}
else
{
cols[i].FieldInfo.SetValue(obj, val);
}
}
list.Add((T)obj);
}
}
catch (Exception e)
{
Debug.LogErrorFormat("{0}\n : {1}", typeof(T).Name, e);
}
finally
{
//_connection.Close();
}
return(list);
#endif
}
